standards-migration_skill

Overview

This skill standardizes how you create and manage database migrations to ensure safety, reversibility, and zero-downtime deployments. It enforces naming conventions, separation of schema and data changes, and clear rollback strategies. Use it to produce migration files that are testable, auditable, and safe to run in production environments.

How this skill works

The skill inspects migration tasks and applies rules: one logical change per migration, mandatory down/rollback implementations, timestamped descriptive filenames, and separation of schema and data migrations. It recommends safe patterns for adding/removing/renaming columns, index creation (concurrent/non-blocking), and batch idempotent data transforms. It also provides a checklist and red flags to halt unsafe changes.

When to use it

• Creating new migration files or modifying schema (db/migrate, alembic, sequelize, etc.)
• Adding, removing, or renaming tables or columns where backwards compatibility matters
• Writing data migrations that transform or backfill large datasets
• Creating or changing indexes on production-sized tables
• Planning zero-downtime deployments or enforcing migration naming and organization

Best practices

• Make every migration reversible; implement and test the down path immediately
• Keep one logical change per migration file to simplify review and rollback
• Separate schema (migrations/schema/) from data (migrations/data/) changes
• Use timestamped, descriptive filenames that state the migration intent
• Use concurrent/non-blocking index creation on large tables and provide defaults for NOT NULL adds
• Test migrations with production-like data and run up/down/up to verify repeatability

Example use cases

• Add a NOT NULL column to a large table: add with server_default, deploy, backfill, then remove default
• Rename a column safely: add new column, write to both, backfill, switch reads, then drop old column
• Create an index on a hot table: create concurrently to avoid write locks
• Implement a data backfill in batches with idempotent SQL to avoid long transactions
• Introduce a foreign key: add after data consistency is ensured in a separate migration