Skill: Create Database Migration

Trigger

When the user needs to modify the database schema.

Steps

Step 1: Plan the Change

• Define what's changing and why
• Check for backward compatibility
• Identify affected queries and code
• Determine if data migration is needed

Step 2: Create Migration File

• Name: YYYYMMDDHHMMSS_descriptive_name.sql
• Write UP migration (apply change)
• Write DOWN migration (rollback)
• Include required columns: {{CONFIG.database.timestampFields.created}}, {{CONFIG.database.timestampFields.updated}}, {{CONFIG.database.softDeleteField}}

Step 3: Safety Checks

• No DELETE FROM statements (soft delete only)
• New columns have defaults or are nullable
• Indexes created with CONCURRENTLY for large tables
• No column renames (add new → migrate → drop old)
• Foreign keys have appropriate cascade rules

Step 4: Data Migration

If needed:

• Write data migration script
• Use batch processing (1000-5000 rows per batch)
• Make it idempotent (safe to re-run)
• Include progress logging

Step 5: Test Migration

• Apply to development database
• Verify data integrity
• Test rollback
• Test with production-like data volume
• Verify affected queries still work

Step 6: Update Code

• Update models/types to match new schema
• Update affected queries
• Run type check: {{CONFIG.testing.typeCheckCommand}}
• Run affected tests

If a step fails

Step	Failure	Recovery
Step 2	Syntax error in migration	Fix SQL; re-run
Step 4	Data migration fails mid-run	Script should be idempotent; fix and re-run; if partial data, document manual cleanup
Step 5	Test migration fails	Run DOWN migration to revert; fix UP migration; retest
Step 5	Rollback test fails	Fix DOWN migration; ensure DOWN fully reverts UP before retrying

Never apply migrations to production without testing UP and DOWN in a non-prod environment first.

Completion

Migration created, tested, and code updated. Ready for review.