Migration Guide

Overview

This guide walks you through migrating existing organizations from the shared database to isolated tenant databases. ---

Prerequisites

✅ Phase 1 complete (foundation in place)
✅ Backup of shared database
✅ Test environment available
✅ TENANT_DB_MIGRATION currently set to false

---

Migration Process

Step 1: Provision Tenant Database

Single Organization: ``

bash
php tenant-db-setup/setup_wizard.php --scenario=managed --org-id=1


Multiple Organizations:

bash
Provision all organizations without tenant DBs

php admin/batch_provision.php --all
Or specific organizations

php admin/batch_provision.php --org-ids=1,2,3


---
Step 2: Test Connection

bash
php tenant-db-setup/test_connection.php --org-id=1 --verbose


Expected Output:


✓ Connection successful (15ms)
Connection Details:
  Host: localhost:3306
  Database: org_1_db
  User: org_1_user
  Hosting: managed
  Health: healthy
  Tables: 15


---
Step 3: Migrate Data (Dry Run)

bash
Test migration without making changes

php admin/migrate_org_data.php --org-id=1 --dry-run


Review Output:
Tables to be migrated

Row counts

Any warnings or errors

---
Step 4: Migrate Data (Live)

bash
Actual migration

php admin/migrate_org_data.php --org-id=1


Options:

bash
Migrate specific tables only

php admin/migrate_org_data.php --org-id=1 --tables=risk_register,vulnerabilities
Delete from shared DB after migration

php admin/migrate_org_data.php --org-id=1 --delete-after


---
Step 5: Verify Migration

bash
Basic verification

php admin/verify_migration.php --org-id=1
Detailed verification

php admin/verify_migration.php --org-id=1 --detailed
Verify specific table

php admin/verify_migration.php --org-id=1 --table=risk_register


Expected Output:


Verification Summary
============================================================
Tables verified: 10
Mismatches found: 0
✓ All verifications passed!
Data migration appears successful.


---
Step 6: Enable Multi-Tenant Mode (Pilot)

For Single Organization (Testing):
Edit

includes/config.php

php
// Enable only for specific org in session
if (isset($_SESSION['org_id']) && $_SESSION['org_id'] == 1) {
    define('TENANT_DB_MIGRATION', true);
} else {
    define('TENANT_DB_MIGRATION', false);
}


Test the Application:
Login as user from migrated organization

Test all modules

Verify data appears correctly

Check performance

---
Step 7: Monitor & Validate

Check Logs:

bash
tail -f logs/tenant-db-setup.log


Health Check:

bash
php tenant-db-setup/test_connection.php --all-orgs


Performance Test:
Compare query times

Monitor connection pool

Check memory usage

---
Step 8: Rollback (If Needed)

Immediate Rollback:

php
// In includes/config.php:
define('TENANT_DB_MIGRATION', false);


Application immediately reverts to shared database.
Data Rollback:

bash
Restore shared DB from backup

mysql -u root -p risk_app_ai < backup_before_migration.sql


---
Step 9: Full Deployment

Once Pilot is Successful:
Migrate Remaining Organizations:

bash
   php admin/batch_provision.php --all
   # Migrate each org
   for org_id in 2 3 4 5; do
       php admin/migrate_org_data.php --org-id=$org_id
       php admin/verify_migration.php --org-id=$org_id
   done


Enable Globally:

php
   // In includes/config.php:
   define('TENANT_DB_MIGRATION', true);


Monitor System:

   - Check all organizations
   - Verify no errors
   - Monitor performance
---
Step 10: Cleanup (Optional)

After Successful Migration:

bash
Delete migrated data from shared DB

php admin/migrate_org_data.php --org-id=1 --delete-after
Or manually:

DELETE FROM risk_register WHERE org_id = 1;
DELETE FROM vulnerabilities WHERE org_id = 1;
-- etc.


---
Troubleshooting

Migration Fails

Check:
Tenant DB connection working

Sufficient disk space

User permissions correct

No foreign key constraints blocking

Solution:

bash
Re-run with verbose output

php admin/migrate_org_data.php --org-id=1 --tables=


Data Mismatch

Check:

bash
php admin/verify_migration.php --org-id=1 --detailed


Solution:
Re-run migration for specific table

Check for concurrent modifications

Verify org_id filtering

Performance Issues

Check:
Connection pool size

Network latency (for remote DBs)

Query optimization

Solution:

`php // Increase connection pool UPDATE org_db_connections SET connection_pool_size = 20 WHERE org_id = 1; `


---
Best Practices

1. Always Test First

Use --dry-runbefore actual migration


Test on non-production org first

Verify before enabling globally

`2. Backup Everything`

`bash


Before migration

mysqldump -u root -p risk_app_ai > backup_$(date +%Y%m%d).sql


3. Migrate in Batches

Don't migrate all orgs at once

Monitor each batch

Allow time for validation

4. Keep Shared DB Intact

Don't delete until fully verified

Maintain for at least 30 days

Keep backups

5. Monitor Continuously

`bash


Set up monitoring

php tenant-db-setup/test_connection.php --all-orgs >> logs/health_check.log


---
Timeline

Recommended Schedule:
| Phase | Duration | Activity |
|-------|----------|----------|
| Week 1 | 2 days | Provision pilot org (1-2 orgs) |
| Week 1 | 1 day | Migrate pilot data |
| Week 1 | 2 days | Test & validate pilot |
| Week 2 | 3 days | Migrate batch 1 (10-20 orgs) |
| Week 2 | 2 days | Monitor & validate |
| Week 3 | 3 days | Migrate batch 2 (remaining orgs) |
| Week 3 | 2 days | Final validation |
| Week 4 | 5 days | Enable globally & monitor |
---
Support

For issues during migration:

Check logs: logs/tenant-db-setup.log

Run verification: php admin/verify_migration.php

Test connections: php tenant-db-setup/test_connection.php`

Review this guide

Contact system administrator