Agent skills
database-migration-guardian

Agent skill

database-migration-guardian

Prevents database migration failures in Sequelize/MySQL environments. Use when adding new models, modifying schemas, creating indexes, or deploying database changes. Catches foreign key errors, index conflicts, zero-downtime migration issues, data loss risks, and rollback problems. Validates migration scripts, suggests optimization, and ensures production safety.

View SKILL.md on GitHub Repository

Stars 163

Forks 31

Install this agent skill to your Project

npx add-skill https://github.com/majiayu000/claude-skill-registry/tree/main/skills/other/database-migration-guardian-skill

SKILL.md

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:

typescript

// ❌ 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:

typescript

// ✅ 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:

bash

# 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):

typescript

// ❌ 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:

typescript

// ========================================
// 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):

bash

# 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):

bash

# 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:

typescript

// ❌ 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:

typescript

// ✅ 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):

typescript

// 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):

typescript

// 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):

typescript

// 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:

typescript

// 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:

typescript

// ✅ 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:

typescript

// ✅ 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:

typescript

// ✅ 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

Error	Cause	Solution
`ER_DUP_FIELDNAME`	Column already exists	Check if migration ran before, use `IF NOT EXISTS`
`ER_BAD_NULL_ERROR`	Required field has null values	Backfill data before adding NOT NULL constraint
`ER_ROW_IS_REFERENCED`	Foreign key constraint violation	Can't delete parent row with child rows, use CASCADE
`ER_NO_REFERENCED_ROW`	Foreign key points to non-existent row	Data integrity issue, clean up orphaned records first
`ER_DUP_ENTRY`	Unique constraint violation	Remove duplicates before adding UNIQUE constraint
`Lock wait timeout exceeded`	Table locked during migration	Use smaller batches, add indexes offline

6. Migration Testing Strategy

Local Testing:

bash

# 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:

bash

# 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:

bash

# 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

bash

# 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

Always use migrations in production - Never sync({ alter: true })
Foreign keys need cascade rules - Prevent orphaned records
Index foreign keys - Prevent slow joins
Test with production data - Catch constraint violations early
Zero-downtime migrations - Multi-step for breaking changes
Rollback plan - Every up() needs a down()
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

Maintainer

majiayu000 Core maintainer

Source details

Full Name: majiayu000/claude-skill-registry
Branch: main
Path in repo: skills/other/database-migration-guardian-skill
License: MIT License

Featured Tools

Join Our Newsletter

Maintain .agent state files. Use at session start, after meaningful steps, and before concluding: read/update constitution/memory/focus/issues/baseline consistently.

163 31

Explore

Didn't find tool you were looking for?

Search AI Tools

Install this agent skill to your Project

SKILL.md

Database Migration Guardian

Activation Triggers

Framework Awareness

Scan Methodology

1. Initial Context Gathering

2. Critical Migration Scan

🔴 CRITICAL: Model Definition Validation

🔴 CRITICAL: Migration Script Safety (MANDATORY ROLLBACK SCRIPTS)

🟡 HIGH: Zero-Downtime Migration Patterns

🟡 HIGH: Index Optimization

🟠 MEDIUM: Data Integrity

3. Sequelize.sync() vs Migrations Decision

4. Production Pre-Flight Checklist

5. Common Migration Errors & Fixes

6. Migration Testing Strategy

Output Format: Auto-Scan Report

Quick Reference: Migration Commands

Key Principles

When to Escalate

Recommended Agent Skills

agent-ops-spec

agent-ops-state

agent-ops-spec

agent-ops-testing

agent-ops-testing

agent-ops-state