neo/backend/scripts

Tenant Migration Scripts

This directory contains scripts for managing database migrations across all tenants in the multi-tenant platform.

Available Scripts

1. Create a New Migration

npm run migrate:make <migration_name>

Creates a new migration file in migrations/tenant/ directory.

Example:

npm run migrate:make add_status_field_to_contacts

2. Migrate a Single Tenant

npm run migrate:tenant <tenant-slug-or-id>

Runs all pending migrations for a specific tenant. You can identify the tenant by its slug or ID.

Example:

npm run migrate:tenant acme-corp
npm run migrate:tenant cm5a1b2c3d4e5f6g7h8i9j0k

3. Migrate All Tenants

npm run migrate:all-tenants

Runs all pending migrations for all active tenants in the system. This is useful when:

• You've created a new migration that needs to be applied to all tenants
• You're updating the schema across the entire platform
• You need to ensure all tenants are up to date

Output:

• Shows progress for each tenant
• Lists which migrations were applied
• Provides a summary at the end
• Exits with error code if any tenant fails

4. Rollback Migration (Manual)

npm run migrate:rollback

⚠️ Warning: This runs a rollback on the default database configured in knexfile.js. For tenant-specific rollbacks, you'll need to manually configure the connection.

Migration Flow

During New Tenant Provisioning

When a new tenant is created via the API, migrations are automatically run as part of the provisioning process:

1. Tenant database is created
2. TenantProvisioningService.runTenantMigrations() is called
3. All migrations in migrations/tenant/ are executed

For Existing Tenants

When you add a new migration file and need to apply it to existing tenants:

1. Create the migration:

   npm run migrate:make add_new_feature
   

2. Edit the generated migration file in migrations/tenant/

3. Test on a single tenant first:

   npm run migrate:tenant test-tenant
   

4. If successful, apply to all tenants:

   npm run migrate:all-tenants
   

Migration Directory Structure

backend/
├── migrations/
│   └── tenant/                    # Tenant-specific migrations
│       ├── 20250126000001_create_users_and_rbac.js
│       ├── 20250126000002_create_object_definitions.js
│       └── ...
├── scripts/
│   ├── migrate-tenant.ts         # Single tenant migration
│   └── migrate-all-tenants.ts    # All tenants migration
└── knexfile.js                   # Knex configuration

Security Notes

Database Password Encryption

Tenant database passwords are encrypted in the central database using AES-256-CBC encryption. The migration scripts automatically:

1. Fetch tenant connection details from the central database
2. Decrypt the database password using the DB_ENCRYPTION_KEY environment variable
3. Connect to the tenant database
4. Run migrations
5. Close the connection

Required Environment Variable:

DB_ENCRYPTION_KEY=your-32-character-secret-key!!

This key must match the key used by TenantService for encryption.

Troubleshooting

Migration Fails for One Tenant

If migrate:all-tenants fails for a specific tenant:

1. Check the error message in the output
2. Investigate the tenant's database directly
3. Fix the issue (manual SQL, data cleanup, etc.)
4. Re-run migrations for that tenant: npm run migrate:tenant <slug>
5. Once fixed, run migrate:all-tenants again to ensure others are updated

Migration Already Exists

Knex tracks which migrations have been run in the knex_migrations table in each tenant database. If a migration was already applied, it will be skipped automatically.

Connection Issues

If you see connection errors:

1. Verify the central database is accessible
2. Check that tenant database credentials are correct
3. Ensure DB_ENCRYPTION_KEY matches the one used for encryption
4. Verify the tenant's database server is running and accessible

Example Migration File

// migrations/tenant/20250126000006_add_custom_fields.js

exports.up = async function(knex) {
  await knex.schema.table('field_definitions', (table) => {
    table.boolean('is_custom').defaultTo(false);
    table.string('custom_type', 50).nullable();
  });
};

exports.down = async function(knex) {
  await knex.schema.table('field_definitions', (table) => {
    table.dropColumn('is_custom');
    table.dropColumn('custom_type');
  });
};

Best Practices

1. Always test on a single tenant first before running migrations on all tenants
2. Include rollback logic in your down() function
3. Use transactions for complex multi-step migrations
4. Backup production databases before running migrations
5. Monitor the output when running migrate:all-tenants to catch any failures
6. Version control your migration files
7. Document breaking changes in migration comments
8. Consider data migrations separately from schema migrations when dealing with large datasets

CI/CD Integration

In your deployment pipeline, you can automatically migrate all tenants:

# After deploying new code
npm run migrate:all-tenants

Or integrate it into your Docker deployment:

# In your Dockerfile or docker-compose.yml
CMD npm run migrate:all-tenants && npm run start:prod