spb/ultra-sharp-agent-skills Public

1---2name: managing-database-migrations3description: Writes and manages versioned database schema migrations with safe rollbacks and zero-downtime deployment patterns. Use when the user asks to write, create, review, or fix a migration, add or drop a column/table/index, rename a column safely, run a backfill, or deploy a schema change without downtime, or mentions Alembic, Prisma Migrate, Rails migrations, or Flyway. Do not use for designing the target schema itself (designing-database-schemas) or for one-off production data fixes.4---56<!--7Author: Simon-Pierre Boucher8Contact: contact@spboucher.ai9-->1011# Managing Database Migrations1213## When to use / when NOT to use14- **Use for:** authoring, reviewing, sequencing, or fixing schema migrations; zero-downtime schema changes; backfills tied to schema changes; rollback strategy.15- **Do NOT use for:** deciding what the schema should look like (that is `designing-database-schemas`), ad-hoc data corrections, or backup/restore work (`backing-up-databases`).1617## Core rules18191. **One logical change per migration.** A migration that adds a table AND renames a column elsewhere cannot be partially rolled back.20   - ✅ `20260805_add_invoices_table.py` + `20260805_rename_users_phone.py`21   - ❌ `20260805_misc_schema_updates.py`222. **Applied migrations are immutable.** Never edit a migration that has run anywhere beyond your machine — write a new one that corrects it.233. **Every migration has a down path — or documents why not.** If `down` is impossible (data-destroying), state it in the migration header and require an explicit flag/confirmation to run.244. **Use the project's framework-native tool.** Detect it before writing anything: `alembic.ini` → Alembic, `prisma/schema.prisma` → Prisma Migrate, `db/migrate/` → Rails, `flyway.conf`/`sql/V*.sql` → Flyway. Only fall back to raw SQL files + a version table if the project has no tool.255. **Never mix schema changes and data changes in one migration.** Schema migrations run in DDL transactions; backfills are long-running DML. Split them: schema → backfill → schema.266. **Zero-downtime changes follow expand → migrate → contract.** The app must work with both old and new schema between steps.27   - ✅ add nullable column → deploy code writing both → backfill → add `NOT NULL` → remove old column later28   - ❌ `ALTER TABLE users RENAME COLUMN phone TO phone_number;` in one release297. **Long locks are outages.** On PostgreSQL: `CREATE INDEX CONCURRENTLY` (outside a transaction), add `NOT NULL` via `CHECK ... NOT VALID` + `VALIDATE CONSTRAINT`, batch backfills (see references).308. **Test the rollback, not just the migration.** `up` then `down` then `up` again on a scratch database must succeed before review.3132## Workflow33341. Detect the migration tool (rule 4) and read the two most recent migrations to match naming and style.352. Classify the change: additive (safe), destructive (needs expand/contract), or data-touching (needs a separate backfill migration).363. Write the migration(s) — smallest possible units, down paths included.374. For destructive or locking changes, write the deployment sequence as comments at the top of the migration (which app version must be live before/after).385. **Validate:** run `up` → `down` → `up` against a scratch/dev database and paste the tool's output. A migration is not done until this passes.3940## Edge cases & failure modes41- **No scratch DB available** → say so explicitly and mark the migration untested; never claim validation that didn't run.42- **Migration already applied and wrong** → write a corrective follow-up migration; never edit history (rule 2).43- **Backfill on a large table** → batch by primary-key range with pauses (see references); a single `UPDATE table SET ...` locks the table.44- **Divergent heads (two branches added migrations)** → merge with the tool's mechanism (e.g. `alembic merge`), never renumber existing files.45- **MySQL** → no transactional DDL: a failed migration leaves partial state; make each statement idempotent (`IF EXISTS`/`IF NOT EXISTS`).4647## References48Deeper recipes and lock-safe SQL: see [references/patterns.md](references/patterns.md)49