SQL Migration Safety Rules

--- description: Safety and naming rules for SQL schema migrations globs: "**/migrations/**" alwaysApply: false --- # Migration rules Assume every migration runs against a live production database with traffic. Write accordingly. ## Reversibility - Every migration ships with a working down/rollback. "Irreversible" is acceptable only for pure data backfills, and the down must then be an explicit no-op with a comment stating what is lost. - One logical change per migration file. A migration that adds a table AND alters an unrelated one cannot be rolled back independently — split it. - Never edit a migration that has been merged. Fix-forward with a new migration; editing history desyncs every environment that already ran it. - Migrations must be deterministic: no `NOW()` defaults baked into inserted rows, no environment-dependent branches, no reads of app config. ## Destructive operations require expand-and-contract Dropping or renaming a column/table while old code is live causes errors. Destructive ops are only allowed as the final "contract" step: 1. **Expand** — add the new column/table, nullable, no default rewrite. 2. **Backfill** — copy data in batches (≤ ~10k rows per statement, committed per batch) in a separate migration or script; never one giant `UPDATE` on a large table. 3. **Switch** — deploy code that reads/writes the new shape; add `NOT NULL` / constraints only after backfill completes (`NOT VALID` then `VALIDATE CONSTRAINT` on Postgres). 4. **Contract** — drop the old column at least one deploy later. Hard bans without a written backfill plan in the migration's comment header: `DROP TABLE`, `DROP COLUMN`, column renames, type narrowing (`varchar(255)` → `varchar(50)`), adding `NOT NULL` to an existing column. ## Naming - Files: `<sequential-or-timestamp>_<verb>_<object>.sql` — `0042_add_invoices_table.sql`, `0043_backfill_invoice_status.sql`. The name states what it does, not "update schema". - Indexes: `idx_<table>_<col1>_<col2>` (`idx_invoices_customer_id_created_at`). Unique: `uq_<table>_<cols>`. Never accept auto-generated names — they differ across environments and break future drops. - Foreign keys: `fk_<table>_<referenced_table>`; checks: `ck_<table>_<rule>` (`ck_invoices_amount_positive`). - Every FK column gets an explicit index in the same migration — FKs do not auto-index in Postgres, and unindexed FKs turn parent deletes into sequential scans. ## Locks and performance - Postgres: create and drop indexes `CONCURRENTLY` (outside a transaction) on any table that has production rows. - Never add a column with a volatile default plus `NOT NULL` in one statement on a big table; add nullable, backfill, then constrain. - `ALTER TABLE` statements that take `ACCESS EXCLUSIVE` locks get a `SET lock_timeout = '5s'` guard at the top of the migration so they fail fast instead of queueing behind long transactions. ## Misc - Explicit column lists always — `INSERT INTO t (a, b)`, never `INSERT INTO t VALUES`, and no `SELECT *` in backfills. - Timestamps are `timestamptz`, money is `numeric`, ids are `bigint`/`uuid` — flag any `timestamp`, `float` money, or `int` PK in review.