nordabiz/deployment_checklist.md

# Norda Biznes - Deployment Checklist

**Version:** 1.0
**Last Updated:** 2026-04-04
**Environment:** Production (OVH VPS inpi-vps-waw01, IP: 57.128.200.27)
**Audience:** DevOps, SysAdmins

---

## Overview

This checklist ensures safe, repeatable deployments to production with minimal risk of data loss or service disruption. All deployments must follow the procedures outlined below.

### Key Principles
- **Backup first:** Always backup before any database changes
- **Test locally:** Validate changes on DEV PostgreSQL (Docker) before PROD
- **Review SQL:** Never execute SQL without reviewing it first
- **Verify application:** Test application functionality after deployment
- **Document changes:** Keep rollback plan ready and documented
- **Use transactions:** Group related changes in SQL transactions

---

## Phase 0: Pre-Deployment Preparation (24 hours before)

### Code Review
- [ ] All code changes peer-reviewed and approved in Git
- [ ] No uncommitted changes in working directory
  ```bash
  git status  # Must be clean
  ```
- [ ] All code syntax validated
  ```bash
  python -m py_compile app.py
  python -m py_compile database.py
  python -m py_compile gemini_service.py
  python -m py_compile nordabiz_chat.py
  python -m py_compile search_service.py
  ```

### Database Review
- [ ] All SQL scripts reviewed and approved
  ```bash
  # Check files exist and have correct content
  ls -lh database/*.sql
  ls -lh *.sql  # Any SQL in root
  ```
- [ ] No destructive operations (DROP, TRUNCATE, CASCADE DELETE) without approval
- [ ] All schema changes tested on DEV PostgreSQL (Docker: localhost:5433) first

### Requirements & Dependencies
- [ ] `requirements.txt` up-to-date and committed
  ```bash
  cat requirements.txt
  # Verify versions are pinned (e.g., Flask==3.0.0, not Flask>=3.0)
  ```
- [ ] No new critical security vulnerabilities
  ```bash
  # Optional: pip-audit if available
  pip install pip-audit
  pip-audit requirements.txt
  ```

### Environment Configuration
- [ ] `.env` production variables prepared and tested
  ```bash
  # Verify required variables are set (don't display values)
  grep -c "DATABASE_URL\|GEMINI_API_KEY\|FLASK_SECRET_KEY" .env
  # Should return 3 (one of each)
  ```
- [ ] `.env` NOT committed to Git
  ```bash
  git status | grep ".env"  # Should be empty
  ```
- [ ] Secrets stored securely (LastPass, 1Password, vault)

### Access & Permissions
- [ ] SSH access to OVH VPS verified
  ```bash
  ssh maciejpi@57.128.200.27 "echo OK"
  ```
- [ ] PostgreSQL credentials verified (not displayed)
  ```bash
  ssh maciejpi@57.128.200.27 "sudo -u postgres psql -d nordabiz -c 'SELECT version();'"
  ```
- [ ] maciejpi user can execute deployment scripts
  ```bash
  ssh maciejpi@57.128.200.27 "sudo -l | grep -E 'systemctl|psql'"
  ```

### Backup Location
- [ ] Backup destination has adequate free space
  ```bash
  ssh maciejpi@57.128.200.27 "df -h /var/backups"
  # Minimum 2GB free recommended
  ```
- [ ] Backup location is accessible and writable

---

## Phase 1: Pre-Deployment Checks (1 hour before)

### Application Status
- [ ] Current application is running and healthy
  ```bash
  ssh maciejpi@57.128.200.27 "sudo systemctl status nordabiznes"
  # Status: active (running)
  ```
- [ ] Application logs show no recent errors
  ```bash
  ssh maciejpi@57.128.200.27 "sudo tail -50 /var/log/nordabiznes/*.log | grep -i error"
  # Should be empty or only non-critical errors
  ```
- [ ] Health check endpoint responding
  ```bash
  curl -s https://nordabiznes.pl/health | jq .
  # Should return {"status": "ok", "database": "connected"}
  ```

### Database Status
- [ ] PostgreSQL is running
  ```bash
  ssh maciejpi@57.128.200.27 "sudo systemctl status postgresql"
  # Status: active (running)
  ```
- [ ] Database is accessible
  ```bash
  ssh maciejpi@57.128.200.27 "sudo -u postgres psql -d nordabiz -c 'SELECT NOW();'"
  ```
- [ ] No long-running transactions
  ```bash
  ssh maciejpi@57.128.200.27 "sudo -u postgres psql -d nordabiz -c \"
    SELECT pid, usename, state, query
    FROM pg_stat_activity
    WHERE state != 'idle' AND duration > interval '5 minutes';\""
  # Should be empty
  ```
- [ ] Database size recorded
  ```bash
  ssh maciejpi@57.128.200.27 "sudo -u postgres psql -d nordabiz -c \"
    SELECT pg_size_pretty(pg_database_size('nordabiz'));\""
  # Record this value
  ```

### Traffic & Performance
- [ ] Application traffic is normal (not peak hours)
  - Peak hours: 9:00-11:00, 12:00-14:00, 17:00-19:00 (CEST)
  - Best deployment time: off-peak (11:00-12:00, 14:00-17:00)
- [ ] No ongoing data imports or batch jobs
  ```bash
  ssh maciejpi@57.128.200.27 "ps aux | grep -i 'python.*import'"
  # Should be empty
  ```

### Monitoring & Alerts
- [ ] Monitoring system is healthy (Zabbix)
- [ ] Alerts are NOT in critical state
- [ ] On-call team notified of deployment window

---

## Phase 2: Full Backup

### PostgreSQL Backup
- [ ] Full database backup
  ```bash
  BACKUP_FILE="$HOME/backup_before_deployment_$(date +%Y%m%d_%H%M%S).sql"
  ssh maciejpi@57.128.200.27 "sudo -u postgres pg_dump -d nordabiz" > "$BACKUP_FILE"
  # Verify backup was created
  ls -lh "$BACKUP_FILE"
  # Minimum size: >5MB (should contain all schema and data)
  ```

### Backup Verification
- [ ] Backup file is readable
  ```bash
  head -20 "$BACKUP_FILE"
  # Should show SQL DDL statements
  ```
- [ ] Backup can be restored (test on separate database)
  ```bash
  # Optional: Create test database and restore
  ssh maciejpi@57.128.200.27 "sudo -u postgres psql -c 'CREATE DATABASE nordabiz_test;'"
  ssh maciejpi@57.128.200.27 "sudo -u postgres psql -d nordabiz_test" < "$BACKUP_FILE"
  ssh maciejpi@57.128.200.27 "sudo -u postgres psql -d nordabiz_test -c 'SELECT COUNT(*) FROM companies;'"
  # Then drop test database: sudo -u postgres psql -c "DROP DATABASE nordabiz_test;"
  ```
- [ ] Backup copied to redundant location
  ```bash
  # Copy to backup server or cloud storage
  cp "$BACKUP_FILE" /var/backups/nordabiz/
  # Or: rsync to remote backup location
  ```

### Backup Documentation
- [ ] Backup filename and path recorded
  - Path: `$HOME/backup_before_deployment_YYYYMMDD_HHMMSS.sql`
  - Size: _______ MB
  - Checksum: `md5sum "$BACKUP_FILE"`

---

## Phase 3: Local Testing (Development Environment)

### Test Environment Setup
- [ ] DEV PostgreSQL (Docker) is running and accessible
  ```bash
  # Verify Docker PostgreSQL is running
  docker ps | grep nordabiz-postgres

  # Test connection
  docker exec nordabiz-postgres psql -U nordabiz_app -d nordabiz -c "SELECT 1;"
  ```

### Application Tests
- [ ] Unit tests pass
  ```bash
  python -m pytest tests/ -v
  # All tests: PASSED
  ```
- [ ] Integration tests pass
  ```bash
  python run_ai_quality_tests.py -q
  # Summary: X/X tests passed
  ```
- [ ] Application starts without errors
  ```bash
  python app.py &
  sleep 3
  curl http://localhost:5000/health
  # Response: 200 OK
  ```

### SQL Script Testing
- [ ] Each SQL script tested individually on DEV PostgreSQL (Docker)
  ```bash
  # For each .sql file:
  docker exec nordabiz-postgres psql -U nordabiz_app -d nordabiz -f /path/to/schema_change.sql

  # Or using stdin:
  cat database/schema_change.sql | docker exec -i nordabiz-postgres psql -U nordabiz_app -d nordabiz
  ```
- [ ] Verify data integrity after applying changes
  ```bash
  # Count records in key tables
  docker exec nordabiz-postgres psql -U nordabiz_app -d nordabiz -c "SELECT 'companies' AS table, COUNT(*) FROM companies;"
  docker exec nordabiz-postgres psql -U nordabiz_app -d nordabiz -c "SELECT 'users' AS table, COUNT(*) FROM users;"
  ```

---

## Phase 4: Production Deployment - SQL Execution

### Pre-SQL Execution
- [ ] Maintenance mode enabled (optional but recommended)
  ```bash
  ssh maciejpi@57.128.200.27 "
    # Temporarily disable non-critical endpoints
    # Or show 'maintenance' page
  "
  ```
- [ ] Current user count recorded
  ```bash
  ssh maciejpi@57.128.200.27 "sudo -u postgres psql -d nordabiz -c \"
    SELECT COUNT(DISTINCT session_key) FROM django_session WHERE expire_date > NOW();\""
  # Current active users: _______
  ```

### SQL Execution Order

**IMPORTANT:** Execute SQL scripts in this exact order within a transaction:

```bash
ssh maciejpi@57.128.200.27 << 'DEPLOY_EOF'

# Start deployment
echo "=== DEPLOYMENT STARTED at $(date) ==="
BACKUP_FILE="$HOME/backup_pre_deployment_$(date +%Y%m%d_%H%M%S).sql"

# Step 1: Full backup BEFORE any changes
echo "STEP 1: Creating backup..."
sudo -u postgres pg_dump -d nordabiz > "$BACKUP_FILE"
echo "✓ Backup: $BACKUP_FILE"

# Step 2: Begin transaction (all SQL changes in one transaction)
echo "STEP 2: Executing SQL migrations..."

# Execute schema migrations (in order of dependency)
sudo -u postgres psql -d nordabiz << 'SQL'
BEGIN;

-- 2.1 News tables migration (if not already applied)
\i /var/www/nordabiznes/database/migrate_news_tables.sql

-- 2.2 Search schema improvements (if applicable)
\i /var/www/nordabiznes/database/improve-search-schema.sql

-- 2.3 Search trigger fixes (if applicable)
\i /var/www/nordabiznes/database/fix-search-trigger.sql

-- 2.4 Data quality fixes (if applicable)
\i /var/www/nordabiznes/priority1_category_fixes.sql
\i /var/www/nordabiznes/priority1_keyword_updates.sql
\i /var/www/nordabiznes/priority2_services_insert.sql

-- 2.5 Any remaining migration scripts
-- \i /var/www/nordabiznes/remaining_services_insert.sql

-- Commit all changes atomically
COMMIT;
SQL

echo "✓ SQL migrations completed"

# Step 3: Verify data integrity
echo "STEP 3: Verifying data integrity..."
sudo -u postgres psql -d nordabiz << 'SQL'
-- Check for orphaned foreign keys
SELECT 'Checking foreign key integrity...' AS status;

-- Count key tables
SELECT COUNT(*) AS company_count FROM companies;
SELECT COUNT(*) AS user_count FROM users;
SELECT COUNT(*) AS news_count FROM company_news;
SELECT COUNT(*) AS notification_count FROM user_notifications;
SQL

# Step 4: Update indexes and statistics
echo "STEP 4: Optimizing database..."
sudo -u postgres psql -d nordabiz << 'SQL'
-- Update statistics for query planner
ANALYZE;

-- Vacuum to reclaim space and optimize
VACUUM ANALYZE;
SQL

echo "✓ Database optimized"

# Step 5: Application deployment
echo "STEP 5: Deploying application..."
cd /var/www/nordabiznes

# Deploy via rsync (no .git on OVH VPS)
# Run from LOCAL machine: rsync -avz --exclude='.git' --exclude='.env' --exclude='__pycache__' ./ maciejpi@57.128.200.27:/var/www/nordabiznes/

# Update dependencies
sudo /var/www/nordabiznes/venv/bin/pip install -q -r requirements.txt

# Validate Python syntax
/var/www/nordabiznes/venv/bin/python -m py_compile app.py

echo "✓ Application files updated"

# Step 6: Restart application
echo "STEP 6: Restarting application..."
sudo systemctl restart nordabiznes
sleep 3

# Verify application started
if sudo systemctl is-active --quiet nordabiznes; then
    echo "✓ Application is running"
else
    echo "✗ ERROR: Application failed to start"
    echo "ROLLING BACK DATABASE..."
    sudo -u postgres psql -d nordabiz < "$BACKUP_FILE"
    exit 1
fi

# Step 7: Post-deployment validation
echo "STEP 7: Post-deployment validation..."
sleep 2

# Health check
HEALTH=$(curl -s -w "%{http_code}" -o /dev/null https://nordabiznes.pl/health)
if [ "$HEALTH" = "200" ]; then
    echo "✓ Health check: OK"
else
    echo "✗ ERROR: Health check failed (HTTP $HEALTH)"
    exit 1
fi

# Check application logs for errors
if sudo tail -20 /var/log/nordabiznes/app.log 2>/dev/null | grep -i "ERROR\|CRITICAL\|FATAL"; then
    echo "⚠ WARNING: Check application logs for errors"
else
    echo "✓ Application logs look clean"
fi

echo ""
echo "=== DEPLOYMENT COMPLETED SUCCESSFULLY at $(date) ==="
echo "Backup location: $BACKUP_FILE"
echo "Next steps: Monitor logs, verify features, notify users"

DEPLOY_EOF
```

### SQL Execution - Alternative (Manual Steps)

If using separate SSH sessions, execute in this order:

```bash
# Session 1: Create backup
ssh maciejpi@57.128.200.27
BACKUP_FILE="$HOME/backup_pre_deployment_$(date +%Y%m%d_%H%M%S).sql"
sudo -u postgres pg_dump -d nordabiz > "$BACKUP_FILE"
echo "Backup saved to: $BACKUP_FILE"
exit

# Session 2: Execute SQL
ssh maciejpi@57.128.200.27
sudo -u postgres psql -d nordabiz << 'EOF'
BEGIN;
\i /var/www/nordabiznes/database/migrate_news_tables.sql
-- ... additional SQL ...
COMMIT;
EOF

# Session 3: Validate
ssh maciejpi@57.128.200.27
sudo -u postgres psql -d nordabiz -c "SELECT COUNT(*) FROM company_news;"
exit
```

### Post-SQL Verification
- [ ] All SQL executed without errors
  ```bash
  # Check for error messages in output
  # Should see: COMMIT (not ROLLBACK)
  ```
- [ ] Database size within expected range
  ```bash
  ssh maciejpi@57.128.200.27 "sudo -u postgres psql -d nordabiz -c \"
    SELECT pg_size_pretty(pg_database_size('nordabiz'));\""
  # Compare to pre-deployment size (should be similar ±10%)
  ```
- [ ] New tables/columns exist (if schema changes)
  ```bash
  ssh maciejpi@57.128.200.27 "sudo -u postgres psql -d nordabiz -c \"
    SELECT * FROM information_schema.tables
    WHERE table_name IN ('company_news', 'user_notifications');\""
  ```

---

## Phase 5: Application Deployment

### Code Deployment
- [ ] Application code deployed via rsync (no .git on OVH VPS)
  ```bash
  # Run from LOCAL machine (project root):
  rsync -avz --exclude='.git' --exclude='.env' --exclude='__pycache__' --exclude='venv' \
    ./ maciejpi@57.128.200.27:/var/www/nordabiznes/
  ```
- [ ] Python dependencies installed
  ```bash
  ssh maciejpi@57.128.200.27 "
    sudo /var/www/nordabiznes/venv/bin/pip install -q -r /var/www/nordabiznes/requirements.txt
  "
  ```
- [ ] Application syntax validated
  ```bash
  ssh maciejpi@57.128.200.27 "
    /var/www/nordabiznes/venv/bin/python -m py_compile /var/www/nordabiznes/app.py
    echo $?  # Should return 0 (success)
  "
  ```

### Service Restart
- [ ] Application service restarted
  ```bash
  ssh maciejpi@57.128.200.27 "sudo systemctl restart nordabiznes"
  ```
- [ ] Service started successfully
  ```bash
  ssh maciejpi@57.128.200.27 "sudo systemctl is-active nordabiznes"
  # Expected: active
  ```
- [ ] Service status verified
  ```bash
  ssh maciejpi@57.128.200.27 "sudo systemctl status nordabiznes --no-pager | head -10"
  ```

### Initial Health Checks
- [ ] Application responds to requests
  ```bash
  curl -s -I https://nordabiznes.pl/ | head -5
  # HTTP/1.1 200 OK
  ```
- [ ] Health endpoint responds
  ```bash
  curl -s https://nordabiznes.pl/health | jq .
  ```
- [ ] No critical errors in logs
  ```bash
  ssh maciejpi@57.128.200.27 "
    sudo tail -30 /var/log/nordabiznes/app.log | grep -i 'ERROR\|CRITICAL'
  "
  # Should be empty or only non-critical warnings
  ```

---

## Phase 6: Validation & Testing

### Functional Testing (Manual)
- [ ] Homepage loads without errors
  - URL: https://nordabiznes.pl/
  - Expected: Company list displays, search bar visible
- [ ] Company detail page works
  - Test with: https://nordabiznes.pl/company/pixlab-sp-z-o-o
  - Expected: Company info, social media, news (if applicable) displays
- [ ] Search functionality works
  - Search for: "IT", "Budownictwo"
  - Expected: Results display with correct filters
- [ ] Chat assistant responds
  - Open /chat, ask: "Jakie firmy zajmują się IT?"
  - Expected: AI response with company list
- [ ] User authentication works
  - Login/logout functionality
  - Expected: Session maintained, logout clears session

### Database Queries
- [ ] New tables accessible
  ```bash
  ssh maciejpi@57.128.200.27 "sudo -u postgres psql -d nordabiz -c '
    SELECT * FROM company_news LIMIT 1;
    SELECT * FROM user_notifications LIMIT 1;'"
  ```
- [ ] Search indexes working
  ```bash
  ssh maciejpi@57.128.200.27 "sudo -u postgres psql -d nordabiz -c \"
    EXPLAIN ANALYZE
    SELECT * FROM companies
    WHERE name ILIKE '%pixlab%' LIMIT 10;\""
  # Should show "Index Scan" (not "Seq Scan")
  ```

### Performance Tests
- [ ] Page load time acceptable (<2 seconds for homepage)
  ```bash
  curl -w "@curl-format.txt" -o /dev/null -s https://nordabiznes.pl/
  # time_total should be < 2s
  ```
- [ ] Database query response time acceptable
  ```bash
  ssh maciejpi@57.128.200.27 "time sudo -u postgres psql -d nordabiz -c '
    SELECT * FROM companies WHERE category_id = 1 LIMIT 50;'"
  # real time should be < 100ms
  ```
- [ ] API endpoints respond within SLA
  ```bash
  # Test /api/companies endpoint
  curl -s https://nordabiznes.pl/api/companies | jq . | head -20
  ```

### Monitoring & Alerts
- [ ] Monitoring system updated (if applicable)
  - Zabbix checks enabled
  - Alert thresholds appropriate
- [ ] No new alerts triggered
  ```bash
  # Check Zabbix for any "Problem" status items
  ```
- [ ] Application metrics within normal range
  - CPU usage: <50%
  - Memory usage: <60%
  - Database connections: <20 of 100

---

## Phase 7: User Communication & Monitoring

### Notification
- [ ] Development team notified of successful deployment
- [ ] Operations team notified
- [ ] On-call engineer confirmed receipt
- [ ] Change log updated (if using JIRA, Confluence, etc.)

### Post-Deployment Monitoring (2 hours)
- [ ] Monitor application logs for errors
  ```bash
  ssh maciejpi@57.128.200.27 "
    tail -f /var/log/nordabiznes/app.log
  "
  # Watch for ERROR, CRITICAL, EXCEPTION
  ```
- [ ] Monitor database load
  ```bash
  ssh maciejpi@57.128.200.27 "sudo -u postgres psql -d nordabiz -c \"
    SELECT pid, usename, state, query
    FROM pg_stat_activity
    WHERE datname = 'nordabiz' AND state != 'idle';\""
  # Should be minimal
  ```
- [ ] Monitor system resources
  ```bash
  ssh maciejpi@57.128.200.27 "top -b -n 1 | head -15"
  ```

### 24-Hour Follow-up
- [ ] No critical issues reported by users
- [ ] Application performance stable
- [ ] Error rate normal
- [ ] Database backup completed (if using automated backups)

---

## Phase 8: Rollback Plan (If Needed)

### Immediate Rollback Criteria
Rollback immediately if ANY of the following occur:
- [ ] Application crashes repeatedly (HTTP 500 errors)
- [ ] Database corruption detected
- [ ] Data loss detected
- [ ] Critical functionality broken (search, auth, chat)
- [ ] Performance degradation >50% (query time 5x slower)
- [ ] Security vulnerability discovered

### Rollback Procedure

```bash
#!/bin/bash
# EMERGENCY ROLLBACK SCRIPT

BACKUP_FILE="$1"  # Pass backup file path as argument

if [ -z "$BACKUP_FILE" ]; then
    echo "Usage: ./rollback.sh /path/to/backup_*.sql"
    exit 1
fi

echo "=== STARTING EMERGENCY ROLLBACK ==="
echo "Backup file: $BACKUP_FILE"
echo "Rollback time: $(date)"
echo ""

# Step 1: Stop application
echo "STEP 1: Stopping application..."
ssh maciejpi@57.128.200.27 "sudo systemctl stop nordabiznes"
sleep 3

# Step 2: Restore database
echo "STEP 2: Restoring database from backup..."
ssh maciejpi@57.128.200.27 "
    sudo -u postgres psql -d nordabiz << 'SQL'
-- Drop all changes
DROP TABLE IF EXISTS company_news CASCADE;
DROP TABLE IF EXISTS user_notifications CASCADE;
-- Add other cleanup as needed
SQL

    # Restore from backup
    sudo -u postgres psql -d nordabiz < $BACKUP_FILE
"

if [ $? -ne 0 ]; then
    echo "✗ ERROR: Database restore failed!"
    echo "Contact database administrator immediately"
    exit 1
fi

echo "✓ Database restored"

# Step 3: Restart application (previous version)
echo "STEP 3: Restarting application..."
# Re-deploy previous version via rsync from local backup, then:
ssh maciejpi@57.128.200.27 "sudo systemctl start nordabiznes"

sleep 3

# Step 4: Verify rollback successful
echo "STEP 4: Verifying rollback..."
HEALTH=$(curl -s -w "%{http_code}" -o /dev/null https://nordabiznes.pl/health)
if [ "$HEALTH" = "200" ]; then
    echo "✓ Rollback successful, application is running"
else
    echo "✗ WARNING: Application not responding, manual intervention needed"
fi

echo ""
echo "=== ROLLBACK COMPLETED ==="
echo "Post-incident actions:"
echo "1. Notify all stakeholders"
echo "2. Review deployment logs and identify root cause"
echo "3. Create incident report"
echo "4. Schedule post-mortem review"
```

### Rollback Execution
```bash
# Assuming backup file from Phase 2
./rollback.sh /home/maciejpi/backup_before_deployment_20260102_143000.sql
```

### Post-Rollback
- [ ] Application confirmed running
- [ ] Users notified of rollback
- [ ] Root cause identified
- [ ] Fixes implemented and re-tested
- [ ] Incident report filed (if required)

---

## Reference: Key Commands

### Health Checks
```bash
# Application health
curl -s https://nordabiznes.pl/health | jq .

# Database connection
ssh maciejpi@57.128.200.27 "sudo -u postgres psql -d nordabiz -c 'SELECT 1;'"

# Service status
ssh maciejpi@57.128.200.27 "sudo systemctl status nordabiznes"

# Log tailing
ssh maciejpi@57.128.200.27 "sudo tail -f /var/log/nordabiznes/app.log"

# Database statistics
ssh maciejpi@57.128.200.27 "sudo -u postgres psql -d nordabiz -c \"
  SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
  FROM pg_tables
  ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC LIMIT 10;\""
```

### Monitoring Queries
```bash
# Active connections
ssh maciejpi@57.128.200.27 "sudo -u postgres psql -d nordabiz -c '
  SELECT datname, usename, count(*)
  FROM pg_stat_activity
  GROUP BY datname, usename;'"

# Long-running queries
ssh maciejpi@57.128.200.27 "sudo -u postgres psql -d nordabiz -c \"
  SELECT pid, usename, query, query_start
  FROM pg_stat_activity
  WHERE query != 'autovacuum'
  AND query_start < NOW() - interval '5 minutes';\""

# Index usage
ssh maciejpi@57.128.200.27 "sudo -u postgres psql -d nordabiz -c '
  SELECT schemaname, tablename, indexname, idx_scan
  FROM pg_stat_user_indexes
  ORDER BY idx_scan DESC LIMIT 20;'"
```

---

## Troubleshooting

### Issue: Application won't start after deployment
**Symptoms:** `systemctl status nordabiznes` shows "failed"

**Solution:**
1. Check logs: `sudo journalctl -xe -u nordabiznes | tail -50`
2. Check syntax: `python -m py_compile /var/www/nordabiznes/app.py`
3. Check database connection: `ssh maciejpi@57.128.200.27 "sudo -u postgres psql -d nordabiz -c 'SELECT 1;'"`
4. If database is issue, execute rollback script
5. If code is issue, revert Git commit and restart

### Issue: Database migration failed
**Symptoms:** SQL execution returned ROLLBACK or errors

**Solution:**
1. Check backup was created: `ls -lh $BACKUP_FILE`
2. Check migration syntax: Review .sql files for errors
3. If transaction rolled back, database is intact (no harm done)
4. Fix SQL errors and retry deployment
5. If critical, restore from backup and troubleshoot offline

### Issue: High CPU/Memory after deployment
**Symptoms:** Application slow, `top` shows high resource usage

**Solution:**
1. Check for runaway queries: `SELECT * FROM pg_stat_activity WHERE state != 'idle';`
2. Kill long-running queries: `SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid != pg_backend_pid();`
3. Check for memory leaks in application logs
4. If issue persists, rollback to previous version
5. Investigate root cause before re-deploying

---

## Deployment Sign-Off

After completing all phases, fill out this section:

```
Deployment Date: _____________
Deployed By: _____________
Reviewed By: _____________
Start Time: _____________
End Time: _____________
Total Duration: _____________

Backup Location: _____________
Backup Size: _____________
Backup Verified: [ ] Yes [ ] No

SQL Scripts Executed:
[ ] migrate_news_tables.sql
[ ] improve-search-schema.sql
[ ] fix-search-trigger.sql
[ ] priority1_category_fixes.sql
[ ] priority1_keyword_updates.sql
[ ] priority2_services_insert.sql
[ ] Other: _____________

Issues Encountered:
_________________________________________________________________

Resolution:
_________________________________________________________________

Post-Deployment Monitoring Period: ___/___/_____ to ___/___/_____

Approval:
- Development Lead: _________________ [ ] Approved
- Ops Lead: _________________ [ ] Approved
- Product Lead: _________________ [ ] Approved
```

---

## Additional Resources

- **Database Schema:** `/var/www/nordabiznes/database/schema.sql`
- **Migration Scripts:** `/var/www/nordabiznes/database/*.sql`
- **Application Logs:** `/var/log/nordabiznes/app.log`
- **PostgreSQL Logs:** `sudo journalctl -u postgresql --no-pager`
- **Production Server:** `57.128.200.27` (OVH VPS inpi-vps-waw01)
- **Deploy method:** rsync (no .git on VPS), NOT git pull
- **DB access:** `sudo -u postgres psql -d nordabiz` (.env is root-owned)

---

**Last Updated:** 2026-04-04
**Maintained By:** Norda Biznes Development Team
**Next Review:** 2026-07-04 (quarterly)