add-db-migration

Add a database migration

During a rolling deploy the old code and the new code run against the same schema at the same time, and a migration that takes a strong lock on a busy table stalls every query behind it. So every migration must be compatible with the code on both sides of the deploy, take only brief locks, and be reversible or explicitly justified.

Steps

1. Read the conventions. Call search_lore for the tool (Flyway, Liquibase, Alembic, Prisma, Knex, Django, Active Record, golang-migrate), naming and numbering, whether migrations run in a transaction, and any zero-downtime ADR. Generate the file with the repo's generator; never edit a migration that may already have been applied elsewhere, add a new one.
2. Classify the change.
   • Additive and safe: new table, nullable column, column with a constant default on engines that store it as metadata (PostgreSQL 11+, MySQL 8 instant), a new index built online.
   • Needs expand/contract: rename or retype a column or table, add NOT NULL, split or merge columns, drop anything still read by deployed code.
   • Destructive or high-risk: dropping data, narrowing a type, rewriting a large table, a backfill that cannot be undone. Call mark_ticket_blocked with the specifics and the safe plan you propose; a human decides. (On a resumed delivery mark_ticket_blocked is refused — raise it with request_decision, the ticket_id and severity human_required instead, and do not commit the destructive migration: the runner still submits what is committed.)
3. Use expand → migrate → contract (parallel change) for anything not purely additive, one phase per ticket or deploy:
   • Expand: add the new column/table; the code writes both old and new and still reads old.
   • Migrate: backfill; switch reads to new; verify.
   • Contract: stop writing old; tell the ORM to ignore it; drop it in a later deploy. This ticket delivers only its phase. State in the evidence which phase this is and what the next one must do.
4. Keep locks short. Set a lock_timeout (a few seconds) so a blocked DDL fails instead of queueing all traffic behind it. On PostgreSQL: CREATE INDEX CONCURRENTLY (outside a transaction); foreign keys and CHECK constraints added NOT VALID then VALIDATE CONSTRAINT separately; NOT NULL via a validated CHECK first; unique constraints from a concurrently built unique index. On MySQL prefer ALGORITHM=INSTANT/INPLACE, LOCK=NONE. Never change a column type in place on a large table.
5. Backfill in batches, outside the schema transaction. Iterate by primary-key range (e.g. 1,000–10,000 rows), commit per batch, make it idempotent and resumable (WHERE new_col IS NULL), and throttle. New writes must already populate the new column (expand phase) so the backfill converges.
6. Write the down migration that restores the previous schema, or a comment stating why a true rollback is impossible and how to recover (e.g. restore from the old column still present).
7. Apply it against the database the repo's test setup creates (the runner scrubs *_URL variables such as DATABASE_URL; never point a migration at a shared or production database): run up, down, then up again, on a database that contains representative rows (an empty table hides NOT NULL, unique and type-cast failures).
8. Test the schema's behaviour per AC. A test that exercises each AC's own behaviour: the constraint rejects bad rows, the default applies, the backfill fills every row and is safe to run twice, the old and new code paths both work during the expand phase. Run the suite (the run-tests skill).
9. Evidence with the record-evidence skill: the migration file, the up/down/up output, the phase, and the test results.