schema-migration-safetylisted

# Schema Migration Safety Live data and running code make schema changes a deployment problem, not just a SQL problem: during every deploy, the *old* code version runs against the *new* schema. ## Expand–contract Never break in one step. Split every breaking change: 1. **Expand:** add the new column/table/index alongside the old. Deploy code that writes both, reads old. 2. **Migrate:** backfill data; switch reads to the new path. 3. **Contract:** only after the old path is provably unused, remove it — in a later release. A rename is a drop-and-add in disguise: same treatment. ## Rules - Every migration has a tested rollback, or is explicitly documented as irreversible and confirmed with the user. - No large data transformation inside a schema migration — backfill in batches as a separate job. A migration that locks a big table is an outage. - Adding NOT NULL or constraints to existing columns: check existing data first, or add with a default / as NOT VALID, then validate separately. - Index creation on large tables: use the non-blocking variant (e.g. `CREATE INDEX CONCURRENTLY` in Postgres). - Destructive operations (DROP, mass DELETE/UPDATE): state the affected row counts, confirm a verified backup exists, and get explicit confirmation before running. ## Smell test "Old app version + new schema: does every query still work?" If no, another expand step is missing.