Database Migration Guardian

Database Migration Guardian

Mission: Prevent database migration disasters through proactive schema validation and zero-downtime deployment patterns. Catches breaking changes before they reach production.

Activation Triggers

• Adding new Sequelize models
• Modifying existing model schemas
• Creating database indexes
• Foreign key relationship changes
• Production database updates
• "Migration failed" errors
• Performance optimization needs
• Data integrity issues

Framework Awareness

This skill understands:

• Sequelize ORM - Model definitions, migrations, sync patterns
• MySQL 8.0 - Constraints, indexes, storage engines
• Production Databases - Zero-downtime migrations, rollback strategies
• Docker - Database container management, volume persistence

Scan Methodology

1. Initial Context Gathering

Ask if not provided:

• "Show me your new/modified model file"
• "Show me existing migrations (if any)"
• "What's your deployment strategy?" (docker-compose, manual, CI/CD)
• "Is this for development or production?"
• "Do you have existing data in this table?"

2. Critical Migration Scan

Execute ALL checks in this section.

🔴 CRITICAL: Model Definition Validation

Historical Failure: Missing foreign key caused cascading delete of all user data

Scan for:

• Foreign keys have proper onDelete and onUpdate rules
• Required fields (allowNull: false) have defaults or are optional in creation
• Unique constraints on appropriate fields
• Indexes defined for foreign keys and frequently queried fields
• Data types match MySQL capabilities (BIGINT for large numbers, JSON for objects)
• Enums match across model and migration files
• Associations defined in both directions (hasMany/belongsTo)

Red flags:

// ❌ DANGEROUS - No cascade rule
user_id: {
  type: DataTypes.UUID,
  references: {
    model: 'users',
    key: 'id'
  }
  // Missing: onDelete: 'CASCADE' or 'SET NULL'
}

// ❌ DANGEROUS - Required field without default
email: {
  type: DataTypes.STRING,
  allowNull: false
  // Missing: defaultValue or Optional<> in creation interface
}

// ❌ DANGEROUS - No index on foreign key
user_id: {
  type: DataTypes.UUID,
  references: { model: 'users', key: 'id' }
}
// Missing: index definition

Optimization:

// ✅ SAFE - Proper foreign key with cascade
user_id: {
  type: DataTypes.UUID,
  allowNull: false,
  references: {
    model: 'users',
    key: 'id'
  },
  onDelete: 'CASCADE',  // Delete jobs when user deleted
  onUpdate: 'CASCADE'   // Update if user ID changes
}

// ✅ SAFE - Optional in creation, required in schema
interface ModelCreationAttributes
  extends Optional<ModelAttributes, 'id' | 'created_at'> {}

// ✅ SAFE - Index on foreign key
{
  indexes: [
    { fields: ['user_id'] },
    { fields: ['status'] },
    { fields: ['created_at'] }
  ]
}

🔴 CRITICAL: Migration Script Safety (MANDATORY ROLLBACK SCRIPTS)

Historical Failure: v1.1.0 deployment (Nov 9, 2025) had no documented rollback procedure for batch_jobs table - high risk of data loss if rollback was needed

Production Lesson Learned: Deployed major database migration without rollback script. While migration succeeded, there was NO way to undo changes if issues arose. This could have caused catastrophic data loss if deployment failed.

⚠️ MANDATORY: Every Migration MUST Have Rollback Script (NON-NEGOTIABLE)

Before Writing ANY Migration:

# RULE 1: Every up() migration MUST have a down() migration
□ Write down() migration FIRST before implementing up()
□ Test rollback procedure on local database
□ Document rollback steps in deployment guide
□ Estimate rollback time (must be <5 minutes for production)

# RULE 2: Rollback script must be COMPLETE
□ Reverses ALL changes from up() migration
□ Handles both DDL (schema) and DML (data) changes
□ Does NOT assume data still exists (handle missing tables/columns gracefully)
□ Logs rollback actions for audit trail

# RULE 3: Test rollback BEFORE production deployment
□ Run up() migration on test database
□ Immediately run down() migration
□ Verify database returned to original state
□ Check no orphaned data or constraints remain

Real Production Example (Nov 9, 2025):

// ❌ DANGEROUS: v1.1.0 deployment had NO rollback script documented
// Migration: 001_add_batch_processing.sql
// Created batch_jobs table with foreign keys
// Result: If deployment failed, NO documented way to rollback
// Risk: Data corruption, orphaned records, production downtime

// ✅ CORRECT: Every migration MUST have rollback script
// File: 001_add_batch_processing.sql (UP migration)
CREATE TABLE batch_jobs (
  id CHAR(36) PRIMARY KEY,
  user_id CHAR(36) NOT NULL,
  FOREIGN KEY fk_batch_jobs_user_id (user_id) REFERENCES users(id)
);

ALTER TABLE conversion_jobs ADD COLUMN batch_job_id CHAR(36);
ALTER TABLE conversion_jobs ADD FOREIGN KEY fk_conversion_jobs_batch_job_id
  (batch_job_id) REFERENCES batch_jobs(id);

// File: 001_add_batch_processing_ROLLBACK.sql (DOWN migration) ← MANDATORY
ALTER TABLE conversion_jobs DROP FOREIGN KEY fk_conversion_jobs_batch_job_id;
ALTER TABLE conversion_jobs DROP COLUMN batch_job_id;
DROP TABLE IF EXISTS batch_jobs;
-- Restore database to pre-migration state

What We Did Right (Nov 9):

• ✅ Created database backup BEFORE migration
• ✅ Tested migration on local database first
• ✅ Verified foreign keys post-migration

What We Should Have Done Better:

• ❌ No rollback SQL script prepared
• ❌ No automated rollback testing
• ❌ No rollback procedure in deployment documentation

Mandatory Rollback Script Structure:

// ========================================
// UP Migration (001_add_feature.ts)
// ========================================
export async function up(queryInterface: QueryInterface) {
  // Step 1: Create table
  await queryInterface.createTable('batch_jobs', { /* schema */ })

  // Step 2: Add indexes
  await queryInterface.addIndex('batch_jobs', ['user_id'])

  // Step 3: Add foreign keys
  await queryInterface.addConstraint('batch_jobs', {
    fields: ['user_id'],
    type: 'foreign key',
    name: 'fk_batch_jobs_user_id',
    references: { table: 'users', field: 'id' },
    onDelete: 'CASCADE'
  })
}

// ========================================
// DOWN Migration (MANDATORY - NEVER SKIP)
// ========================================
export async function down(queryInterface: QueryInterface) {
  // Reverse order of up() migration

  // Step 3 rollback: Remove foreign keys
  await queryInterface.removeConstraint('batch_jobs', 'fk_batch_jobs_user_id')

  // Step 2 rollback: Remove indexes (handled by dropTable)

  // Step 1 rollback: Drop table
  await queryInterface.dropTable('batch_jobs')

  console.log('✅ Rollback complete - database restored to pre-migration state')
}

Rollback Testing Checklist (MANDATORY Before Production):

# Step 1: Apply migration on test database
npm run migrate:up

# Step 2: Verify migration succeeded
mysql -e "SHOW TABLES; DESCRIBE batch_jobs;"

# Step 3: Immediately run rollback
npm run migrate:down

# Step 4: Verify complete rollback
mysql -e "SHOW TABLES;"  # batch_jobs should NOT exist
mysql -e "DESCRIBE conversion_jobs;"  # batch_job_id column should NOT exist

# Step 5: Re-apply migration (test idempotency)
npm run migrate:up

# Step 6: Document rollback time
echo "Rollback time: [X seconds] - acceptable for production"

Production Deployment Rollback Plan (Required Documentation):

# EMERGENCY: Rollback v1.1.0 batch processing feature

# Step 1: Stop application containers
docker stop pdflab-backend-prod pdflab-frontend-prod

# Step 2: Restore database from backup
docker exec -i pdflab-mysql-prod \
  mysql -u pdflab -p'password' pdflab_production \
  < /tmp/pdflab_backup_20251109_212730.sql

# Step 3: Start previous container versions
docker run -d --name pdflab-backend-prod [previous image]
docker run -d --name pdflab-frontend-prod [previous image]

# Step 4: Verify rollback
curl https://pdflab.pro/api/health  # Should return 200

# Estimated rollback time: 2-3 minutes
# Data loss: None (backup restored)

Scan for:

• Migration uses QueryInterface properly (not sequelize.sync())
• All columns from model are in migration CREATE TABLE
• Indexes created in migration match model definition
• Foreign keys in migration match model references
• Enum values match exactly between model and migration
• MANDATORY: down() migration exists and is COMPLETE
• MANDATORY: Rollback tested on local database
• MANDATORY: Rollback procedure documented
• Data migration strategy for existing rows (if modifying table)

Red flags:

// ❌ DANGEROUS - Using sync() instead of migrations
await sequelize.sync({ force: true })  // DELETES ALL DATA!

// ❌ DANGEROUS - Missing columns
await queryInterface.createTable('batch_jobs', {
  id: DataTypes.UUID,
  user_id: DataTypes.UUID
  // Missing: all other columns from model
})

// ❌ DANGEROUS - No rollback
export async function down(queryInterface: QueryInterface) {
  // Empty or missing - can't undo migration
}

Optimization:

// ✅ SAFE - Complete migration
export async function up(queryInterface: QueryInterface) {
  await queryInterface.createTable('batch_jobs', {
    id: { type: DataTypes.UUID, primaryKey: true },
    user_id: {
      type: DataTypes.UUID,
      allowNull: false,
      references: { model: 'users', key: 'id' },
      onDelete: 'CASCADE'
    },
    // ... ALL columns from model ...
  })

  // Add indexes
  await queryInterface.addIndex('batch_jobs', ['user_id'])
  await queryInterface.addIndex('batch_jobs', ['status'])
}

// ✅ SAFE - Rollback defined
export async function down(queryInterface: QueryInterface) {
  await queryInterface.dropTable('batch_jobs')
}

🟡 HIGH: Zero-Downtime Migration Patterns

Historical Issue: Adding NOT NULL column without default locked production table for 5 minutes

Scan for:

• Adding required columns uses multi-step migration
• Indexes created with ALGORITHM=INPLACE where possible
• Large data migrations happen in batches
• Backward-compatible changes (new code works with old schema)

Migration Patterns:

Adding Required Column (Multi-Step):

// Migration 1: Add column as nullable with default
await queryInterface.addColumn('users', 'new_field', {
  type: DataTypes.STRING,
  allowNull: true,  // Start as optional
  defaultValue: 'default_value'
})

// Deploy new code that handles both null and populated values

// Migration 2: Backfill data
await queryInterface.sequelize.query(
  'UPDATE users SET new_field = "value" WHERE new_field IS NULL'
)

// Migration 3: Make NOT NULL
await queryInterface.changeColumn('users', 'new_field', {
  type: DataTypes.STRING,
  allowNull: false
})

Removing Column (Multi-Step):

// Step 1: Deploy code that stops using column
// Step 2: Run migration to drop column
await queryInterface.removeColumn('users', 'old_field')
// Column already unused, no downtime

Renaming Column (Zero-Downtime):

// DON'T rename - it breaks old code
// Instead: Add new column → Dual write → Remove old column

// Migration 1: Add new column
await queryInterface.addColumn('users', 'new_name', {
  type: DataTypes.STRING,
  allowNull: true
})

// Update code to write to both columns
user.old_name = value
user.new_name = value

// Migration 2: Backfill
await queryInterface.sequelize.query(
  'UPDATE users SET new_name = old_name WHERE new_name IS NULL'
)

// Migration 3: Stop using old_name in code

// Migration 4: Drop old column
await queryInterface.removeColumn('users', 'old_name')

🟡 HIGH: Index Optimization

Historical Issue: Missing index on foreign key caused 10s query times

Scan for:

• All foreign keys have indexes
• Frequently queried columns have indexes
• Composite indexes for multi-column queries
• No redundant indexes (e.g., index on user_id AND (user_id, status))
• Index cardinality appropriate (high cardinality = good for indexing)

Index Strategy:

// Query: Get user's conversion jobs ordered by date
// SELECT * FROM conversion_jobs WHERE user_id = ? ORDER BY created_at DESC

// ❌ NO INDEX - Full table scan
indexes: []

// ❌ PARTIAL - Index on user_id only
indexes: [{ fields: ['user_id'] }]
// MySQL still needs to sort by created_at

// ✅ OPTIMAL - Composite index
indexes: [
  { fields: ['user_id', 'created_at'] }
  // MySQL can use index for WHERE and ORDER BY
]

// Query: Get jobs by status
// SELECT * FROM conversion_jobs WHERE status = 'completed'

// ❌ POOR - Low cardinality (only 5 statuses)
indexes: [{ fields: ['status'] }]
// Better to filter in application layer

// ✅ BETTER - Composite with high cardinality
indexes: [
  { fields: ['status', 'created_at'] }
  // Useful if you filter by status AND sort by date
]

🟠 MEDIUM: Data Integrity

Scan for:

• Timestamps enabled (timestamps: true)
• Soft deletes if needed (paranoid: true)
• Unique constraints on email, username, etc.
• Check constraints for valid values
• JSON validation for JSON fields
• Cascade deletes configured correctly

Data Integrity Patterns:

// ✅ Email uniqueness
email: {
  type: DataTypes.STRING,
  allowNull: false,
  unique: true,
  validate: {
    isEmail: true
  }
}

// ✅ Enum validation
status: {
  type: DataTypes.ENUM('pending', 'processing', 'completed', 'failed'),
  allowNull: false,
  defaultValue: 'pending'
}

// ✅ Soft deletes (paranoid)
{
  timestamps: true,
  paranoid: true  // Adds deletedAt, doesn't actually delete
}

// ✅ Check constraint (MySQL 8.0+)
progress: {
  type: DataTypes.INTEGER,
  allowNull: false,
  validate: {
    min: 0,
    max: 100
  }
}

3. Sequelize.sync() vs Migrations Decision

Ask user: "Are you using sequelize.sync() or migration files?"

Sequelize.sync() - Development Only:

// ✅ OK for development
await sequelize.sync({ force: false, alter: false })
// Creates tables if they don't exist

// ❌ NEVER in production
await sequelize.sync({ force: true })  // DELETES ALL DATA
await sequelize.sync({ alter: true })  // RISKY - can lose data

Migrations - Production:

// ✅ Production-safe
// Run migration files in order
// 001-create-users.ts
// 002-create-conversion-jobs.ts
// 003-create-subscriptions.ts
// 004-create-payment-logs.ts
// 005-create-batch-jobs.ts

// Each migration is version-controlled and rollback-safe

Recommendation:

• Development: sequelize.sync() is fine for rapid iteration
• Production: ALWAYS use migration files
• Transition: Write migrations for existing sync'd tables

4. Production Pre-Flight Checklist

Before deploying database changes:

Pre-Migration:

• Backup database (mysqldump or snapshot)
• Test migration on production data copy
• Verify rollback (down()) migration works
• Check migration locks table during execution
• Estimate migration time (run on staging with prod data volume)
• Plan maintenance window if needed (or use zero-downtime pattern)

During Migration:

• Monitor database connections (SHOW PROCESSLIST)
• Watch for lock waits (SELECT * FROM information_schema.innodb_locks)
• Have rollback plan ready
• Monitor application errors

Post-Migration:

• Verify row counts (SELECT COUNT(*) FROM new_table)
• Check foreign key relationships (SELECT * FROM table WHERE user_id NOT IN (SELECT id FROM users))
• Run EXPLAIN on critical queries to verify index usage
• Monitor slow query log

5. Common Migration Errors & Fixes

6. Migration Testing Strategy

Local Testing:

# Reset database
docker-compose down -v
docker-compose up -d

# Run migrations
cd backend
npx tsx src/scripts/run-migration.ts

# Verify tables created
mysql -u pdflab -p pdflab -e "SHOW TABLES"
mysql -u pdflab -p pdflab -e "DESCRIBE batch_jobs"

Staging Testing:

# Copy production data to staging
mysqldump --single-transaction production > prod_copy.sql
mysql staging < prod_copy.sql

# Run migration on staging
node run-migration.js

# Test application works with new schema
npm test

Production Rollout:

# Backup first
mysqldump --single-transaction pdflab > backup_$(date +%Y%m%d_%H%M%S).sql

# Run migration
node run-migration.js

# Verify
mysql -e "SELECT COUNT(*) FROM batch_jobs"

# Monitor
tail -f /var/log/mysql/slow-query.log

Output Format: Auto-Scan Report

═══════════════════════════════════════════════
🛡️ DATABASE MIGRATION GUARDIAN - SCAN RESULTS
═══════════════════════════════════════════════

📊 SCAN SCOPE
• Model: BatchJob
• Table: batch_jobs
• Migration: 005-create-batch-jobs.ts
• Strategy: Sequelize.sync() (development)

🚨 CRITICAL FINDINGS: 0
✅ No critical issues detected

⚠️  HIGH PRIORITY: 2
1. Missing index on frequently queried field
   • Field: status
   • Impact: Slow queries when filtering by status
   • Fix: Add index: { fields: ['status'] }

2. Foreign key without cascade rule
   • Field: user_id
   • Impact: Can't delete users with batch jobs
   • Fix: Add onDelete: 'CASCADE'

💡 OPTIMIZATIONS: 3
1. Composite index opportunity
   • Query: Get user's batches ordered by date
   • Current: Index on user_id only
   • Optimized: { fields: ['user_id', 'created_at'] }
   • Benefit: 10x faster queries

2. JSON field validation
   • Field: options
   • Current: No validation
   • Recommended: Add Joi/Zod schema validation

3. Soft deletes consideration
   • Current: Hard delete
   • Recommended: Add paranoid: true for audit trail

═══════════════════════════════════════════════
PRODUCTION READINESS
═══════════════════════════════════════════════
✅ Migration script complete
✅ Rollback defined
⚠️  Zero-downtime pattern needed for ALTER operations
✅ Indexes defined
⚠️  Test on production data copy recommended

RISK LEVEL: LOW
DEPLOYMENT RECOMMENDATION: Safe to deploy after addressing 2 high-priority issues

NEXT ACTIONS:
1. Add cascade rule to user_id foreign key
2. Add index on status field
3. Test migration on production data copy
4. Add composite index for performance

═══════════════════════════════════════════════

Quick Reference: Migration Commands

# Sequelize CLI (if using)
npx sequelize-cli migration:generate --name create-batch-jobs
npx sequelize-cli db:migrate
npx sequelize-cli db:migrate:undo

# Custom migration (PDFLab pattern)
npx tsx src/scripts/run-migration.ts

# MySQL verification
mysql -u pdflab -p pdflab -e "SHOW CREATE TABLE batch_jobs"
mysql -u pdflab -p pdflab -e "SHOW INDEX FROM batch_jobs"

# Check foreign keys
mysql -u pdflab -p pdflab -e "
  SELECT
    TABLE_NAME,
    COLUMN_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
  FROM information_schema.KEY_COLUMN_USAGE
  WHERE REFERENCED_TABLE_NAME = 'users'
"

Key Principles

1. Always use migrations in production - Never sync({ alter: true })
2. Foreign keys need cascade rules - Prevent orphaned records
3. Index foreign keys - Prevent slow joins
4. Test with production data - Catch constraint violations early
5. Zero-downtime migrations - Multi-step for breaking changes
6. Rollback plan - Every up() needs a down()
7. Backup first - Murphy's law applies to databases

When to Escalate

• Adding columns to tables with >1M rows
• Changing primary keys or foreign keys
• Renaming tables in production
• Complex data migrations requiring ETL
• Geo-distributed database changes
• Changing storage engines or collation