# Live Database Migration Procedure
**Date:** October 5, 2025  
**Target:** Migrate users, cases, clients, and all related data to new system

## ⚠️ CRITICAL - READ FIRST

### Migration Strategy

**PRESERVES (with backups):**
- ✅ Users and authentication
- ✅ Clients
- ✅ Cases
- ✅ Time entries
- ✅ Expenses
- ✅ Invoices
- ✅ Payments
- ✅ Case instructions

**DROPS & RECREATES (no data to preserve):**
- 🔄 Documents (no existing documents)
- 🔄 Document metadata
- 🔄 Modules (will reload from sql/modules.sql)
- 🔄 Other non-critical tables

**Migration is SAFE:** Creates backup tables before any changes.

## 🚀 Quick Start (TL;DR)

```bash
# 1. Backup
pg_dump -U postgres -d taf_db > backup_$(date +%Y%m%d_%H%M%S).sql

# 2. Run migration
psql -U postgres -d taf_db -f migrations/20251005_live_db_migration.sql

# 3. Populate modules
psql -U postgres -d taf_db -f sql/modules.sql

# 4. Done! Hard refresh browser (Ctrl+Shift+R)
```

---

## Pre-Migration Checklist

- [ ] **1. Schedule downtime window** (recommended: 15-30 minutes during low traffic)
- [ ] **2. Announce maintenance to users** (at least 24 hours in advance)
- [ ] **3. Create full database backup**
  ```bash
  pg_dump -U postgres -d taf_db > backup_$(date +%Y%m%d_%H%M%S).sql
  ```
- [ ] **4. Test migration on a copy** (restore backup to test database first)
- [ ] **5. Verify disk space** (ensure at least 2x current database size available)
- [ ] **6. Have rollback plan ready** (test restore procedure)

## Migration Steps

### Step 1: Pre-Migration Backup

```bash
cd /home/kevin_admin/projects/TAF

# Create backup directory
mkdir -p migrations/backups

# Full database backup
pg_dump -U postgres -d taf_db > migrations/backups/pre_migration_$(date +%Y%m%d_%H%M%S).sql

# Backup specific tables (28 critical tables)
pg_dump -U postgres -d taf_db \
  -t users -t clients -t cases -t time_entries -t expenses \
  -t invoices -t payments -t case_instructions \
  -t user_emails -t user_phones -t user_passwords \
  -t user_mfa_factors -t user_mfa_totp -t user_mfa_webauthn \
  -t user_recovery_codes -t user_identities -t user_2fa \
  -t user_passkeys -t user_tokens -t user_faces \
  -t user_group_memberships -t user_module_permissions \
  -t user_table_permissions -t user_column_permissions \
  -t user_org_memberships -t user_branch_memberships \
  -t user_settings -t user_board_settings \
  -t documents -t document_metadata \
  > migrations/backups/critical_tables_$(date +%Y%m%d_%H%M%S).sql

# Verify backup size
ls -lh migrations/backups/
```

### Step 2: Test Migration on Copy

```bash
# Create test database
createdb -U postgres taf_test

# Restore backup to test database
psql -U postgres -d taf_test < migrations/backups/pre_migration_*.sql

# Run migration on test database
psql -U postgres -d taf_test -f migrations/20251005_live_db_migration.sql

# Verify test results
psql -U postgres -d taf_test -c "SELECT COUNT(*) FROM users;"
psql -U postgres -d taf_test -c "SELECT COUNT(*) FROM cases;"
psql -U postgres -d taf_test -c "SELECT COUNT(*) FROM documents;"

# Check for errors
psql -U postgres -d taf_test -c "SELECT * FROM migration_log ORDER BY executed_at DESC LIMIT 1;"
```

### Step 3: Run Production Migration

**ONLY proceed if test migration succeeded!**

```bash
# Set maintenance mode (if you have one)
# echo "maintenance" > /var/www/html/status.txt

# IMPORTANT: Run from TAF project root directory
cd /home/kevin_admin/projects/TAF

# Run migration (it will automatically import TAFDB.pgsql first)
psql -U postgres -d taf_db -f migrations/20251005_live_db_migration.sql 2>&1 | tee migrations/migration_$(date +%Y%m%d_%H%M%S).log

# Check for errors in log
grep -i "error\|warning" migrations/migration_*.log
```

**What happens during migration:**
1. **Step 0**: Automatically runs `TAFDB.pgsql` to create any missing tables (user_*, modules, etc.)
2. **Step 1**: Creates backup tables for all 28 critical tables
3. **Step 2**: Drops and recreates documents/modules tables
4. **Steps 3-8**: Schema updates, validation, logging

### Step 4: Verify Migration

```bash
# Connect to database
psql -U postgres -d taf_db

# Run verification queries
SELECT COUNT(*) AS user_count FROM users;
SELECT COUNT(*) AS client_count FROM clients;
SELECT COUNT(*) AS case_count FROM cases;
SELECT COUNT(*) AS document_count FROM documents;

# Check backup tables were created (should be 28 tables)
SELECT tablename FROM pg_tables WHERE tablename LIKE '_backup_%' ORDER BY tablename;
SELECT COUNT(*) FROM _backup_users;
SELECT COUNT(*) FROM _backup_cases;
SELECT COUNT(*) FROM _backup_user_passwords;

# Verify new columns exist
SELECT column_name, data_type 
FROM information_schema.columns 
WHERE table_name = 'documents' AND column_name = 'parent_document_id';

SELECT column_name, data_type 
FROM information_schema.columns 
WHERE table_name = 'document_metadata' AND column_name IN ('metadata_key', 'metadata_value');

# Check migration log
SELECT * FROM migration_log ORDER BY executed_at DESC LIMIT 5;

\q
```

### Step 5: Application Testing

- [ ] **1. Start application servers**
  ```bash
  # If you stopped them
  sudo systemctl start apache2
  # or
  sudo systemctl start nginx
  ```

- [ ] **2. Clear application caches**
  ```bash
  # Clear PHP OPcache if using
  sudo systemctl restart php8.1-fpm
  
  # Clear browser caches or do hard refresh (Ctrl+Shift+R)
  ```

- [ ] **3. Test critical workflows**
  - [ ] Login as admin user
  - [ ] Login as regular user
  - [ ] Open Case Management module
  - [ ] Select a case
  - [ ] View Documents tab (should see folder browser)
  - [ ] Upload a document
  - [ ] Create a folder
  - [ ] Move document to folder
  - [ ] Preview a document
  - [ ] Download a document
  - [ ] Add time entries
  - [ ] Add expenses
  - [ ] Create invoice
  - [ ] Record payment
  - [ ] Test Back button

### Step 6: Monitor for Issues

**First 24 hours - Watch for:**
- Application errors in logs
- User-reported issues
- Slow queries
- Database connection issues

```bash
# Monitor logs
tail -f /var/log/apache2/error.log
# or
tail -f /var/log/nginx/error.log

# Monitor PostgreSQL logs
tail -f /var/log/postgresql/postgresql-*.log

# Check for slow queries
psql -U postgres -d taf_db -c "
SELECT query, calls, total_time, mean_time 
FROM pg_stat_statements 
ORDER BY total_time DESC 
LIMIT 10;
"
```

## Post-Migration Cleanup

**Wait at least 7 days before cleanup!**

After confirming everything works correctly:

```sql
-- Connect to database
psql -U postgres -d taf_db

-- Drop backup tables (all 28)
BEGIN;
-- Core tables
DROP TABLE IF EXISTS _backup_users CASCADE;
DROP TABLE IF EXISTS _backup_clients CASCADE;
DROP TABLE IF EXISTS _backup_cases CASCADE;
DROP TABLE IF EXISTS _backup_time_entries CASCADE;
DROP TABLE IF EXISTS _backup_expenses CASCADE;
DROP TABLE IF EXISTS _backup_invoices CASCADE;
DROP TABLE IF EXISTS _backup_payments CASCADE;
DROP TABLE IF EXISTS _backup_case_instructions CASCADE;

-- User authentication & permission tables (20 tables)
DROP TABLE IF EXISTS _backup_user_emails CASCADE;
DROP TABLE IF EXISTS _backup_user_phones CASCADE;
DROP TABLE IF EXISTS _backup_user_passwords CASCADE;
DROP TABLE IF EXISTS _backup_user_mfa_factors CASCADE;
DROP TABLE IF EXISTS _backup_user_mfa_totp CASCADE;
DROP TABLE IF EXISTS _backup_user_mfa_webauthn CASCADE;
DROP TABLE IF EXISTS _backup_user_recovery_codes CASCADE;
DROP TABLE IF EXISTS _backup_user_identities CASCADE;
DROP TABLE IF EXISTS _backup_user_group_memberships CASCADE;
DROP TABLE IF EXISTS _backup_user_module_permissions CASCADE;
DROP TABLE IF EXISTS _backup_user_table_permissions CASCADE;
DROP TABLE IF EXISTS _backup_user_column_permissions CASCADE;
DROP TABLE IF EXISTS _backup_user_org_memberships CASCADE;
DROP TABLE IF EXISTS _backup_user_branch_memberships CASCADE;
DROP TABLE IF EXISTS _backup_user_2fa CASCADE;
DROP TABLE IF EXISTS _backup_user_passkeys CASCADE;
DROP TABLE IF EXISTS _backup_user_tokens CASCADE;
DROP TABLE IF EXISTS _backup_user_faces CASCADE;
DROP TABLE IF EXISTS _backup_user_settings CASCADE;
DROP TABLE IF EXISTS _backup_user_board_settings CASCADE;
COMMIT;
```

## Rollback Procedure

**If critical issues occur, rollback immediately:**

```bash
# Stop application
sudo systemctl stop apache2  # or nginx

# Connect to database
psql -U postgres -d taf_db

# Run rollback
BEGIN;

-- Drop current tables
DROP TABLE IF EXISTS users CASCADE;
DROP TABLE IF EXISTS clients CASCADE;
DROP TABLE IF EXISTS cases CASCADE;
DROP TABLE IF EXISTS time_entries CASCADE;
DROP TABLE IF EXISTS expenses CASCADE;
DROP TABLE IF EXISTS invoices CASCADE;
DROP TABLE IF EXISTS payments CASCADE;
DROP TABLE IF EXISTS documents CASCADE;
DROP TABLE IF EXISTS document_metadata CASCADE;
DROP TABLE IF EXISTS case_instructions CASCADE;

-- Restore from backup tables
ALTER TABLE _backup_users RENAME TO users;
ALTER TABLE _backup_clients RENAME TO clients;
ALTER TABLE _backup_cases RENAME TO cases;
ALTER TABLE _backup_time_entries RENAME TO time_entries;
ALTER TABLE _backup_expenses RENAME TO expenses;
ALTER TABLE _backup_invoices RENAME TO invoices;
ALTER TABLE _backup_payments RENAME TO payments;
ALTER TABLE _backup_documents RENAME TO documents;
ALTER TABLE _backup_document_metadata RENAME TO document_metadata;
ALTER TABLE _backup_case_instructions RENAME TO case_instructions;

COMMIT;

\q

# Restart application
sudo systemctl start apache2  # or nginx
```

## Alternative: Full Restore from File Backup

If backup tables are corrupted or missing:

```bash
# Stop application
sudo systemctl stop apache2

# Drop database
dropdb -U postgres taf_db

# Recreate database
createdb -U postgres taf_db

# Restore from file backup
psql -U postgres -d taf_db < migrations/backups/pre_migration_*.sql

# Restart application
sudo systemctl start apache2
```

## Troubleshooting

### Issue: Migration fails mid-way

**Solution:** Transaction will auto-rollback. Check error message in log file.

```bash
# Check the last few lines of the log
tail -50 migrations/migration_*.log

# Common issues:
# - Insufficient disk space: Free up space and retry
# - Permission denied: Grant proper permissions
# - Constraint violation: Check data integrity
```

### Issue: Application shows "Please select a record first" for documents

**Solution:** Hard refresh browser cache

```
Press Ctrl+Shift+R (Windows/Linux) or Cmd+Shift+R (Mac)
```

### Issue: Back button doesn't work

**Solution:** Clear browser cache and ensure JavaScript is loaded

```bash
# Check browser console (F12) for JavaScript errors
# If you see import errors, clear cache and hard refresh
```

### Issue: Slow query performance after migration

**Solution:** Analyze and vacuum tables

```sql
-- Connect to database
psql -U postgres -d taf_db

-- Analyze tables to update statistics
ANALYZE users;
ANALYZE clients;
ANALYZE cases;
ANALYZE documents;
ANALYZE document_metadata;

-- Vacuum to reclaim space
VACUUM ANALYZE;

-- Reindex if needed
REINDEX DATABASE taf_db;
```

## Support Contacts

- **Database Issues:** DBA Team
- **Application Issues:** Development Team
- **User Training:** Support Team

## Migration Checklist Summary

- [ ] Pre-migration backup completed
- [ ] Test migration successful
- [ ] Production migration completed
- [ ] Migration log verified
- [ ] Application testing passed
- [ ] Users notified of completion
- [ ] Monitoring in place (24-48 hours)
- [ ] Backup tables dropped (after 7 days)

## Notes

- Migration creates backup tables prefixed with `_backup_`
- All operations are within a transaction (can rollback)
- No data is deleted, only new columns/indexes added
- Module configurations updated automatically
- Migration is logged in `migration_log` table