Create Migration

Use this skill when the user needs to add or review a database schema migration, backfill, or data repair step.

Core Rules

follow the repo's existing migration tool, naming, and ordering conventions
prefer rollout-safe additive changes before destructive changes — renaming or dropping a column/table in a single release is strictly prohibited
use the Expand-Contract (Parallel Change) pattern for destructive changes: Phase 1 (Expand: add new structure), Phase 2 (Backfill: dual-write / CDC), Phase 3 (Contract: remove old structure after full cutover)
every DDL migration must set SET lock_timeout = '2s'; to prevent queuing locks behind long-running queries from taking down connection pools
build indexes with CREATE INDEX CONCURRENTLY (PostgreSQL) or online DDL tools (gh-ost for MySQL) to avoid ACCESS EXCLUSIVE table locks on large tables
backfill historical data in small batches (500–2000 rows) with sleep intervals to keep replication lag and I/O load near zero
use feature flags for dual-read / shadow-read validation and instant rollback — percentage-based traffic cutover 1% → 10% → 50% → 100%
keep schema changes, backfills, and cleanup in separate releases when that reduces risk
make rollback behavior explicit when the repo supports rollback — document when reversal is partial or unsafe
verify code and schema compatibility across staged deployment; database migrations must be deployable before application code releases