SQL Migration Safety Rules
Cursor.cursor/rules/*.mdcCoding
Migration rules requiring reversibility, expand-and-contract for destructive changes, deterministic index naming, and lock awareness.
Use when: Attach to migration directories in any SQL project to prevent irreversible schema changes and table-locking deploys against production data.
Get this skill
Add the skill file with the CLI, by hand, or over MCP.Save this to your project or home directory so Cursor can load it.
npx goodprompts add sql-migrations-rulesSaves to
./.cursor/rules/sql-migrations-rules.mdcnpx goodprompts add sql-migrations-rulesSaves to
./.cursor/rules/sql-migrations-rules.mdcSkill file
.cursor/rules/*.mdc3,155 characters---
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.