--- name: migration-safety-guard description: Blocks a schema migration from shipping with irreversible data loss, a new tenant-scoped table missing row-level security, or blocking DDL on a large/hot table — done in place instead of via expand-contract, an explicit RLS/tenant-isolation policy, or a non-locking DDL form. Best used reactively before writing or applying any migration. Use when the user says "write a migration", "add/alter/drop a column or table", "create index", "schema change", "ALTER TABLE", "DROP", or "CREATE TABLE". DO NOT USE for secret exposure (use no-secret-leak-guard) or general diff scope (use clean-diff-guard); this guard is specifically about migration data-loss, tenant-isolation, and lock safety. --- # migration-safety-guard A migration is the one kind of change that can destroy data or expose it across tenants the moment it runs — there is no "revert the deploy" for a `DROP COLUMN` that already ran against production data, and there is no "patch it later" for a new table that served cross-tenant rows for even one request. Both failure classes have shipped in the wild: the 2025 Lovable disclosure (CVE-2025-48757) was exactly a missing row-level-security policy on a Supabase/Postgres table, letting any authenticated user read every tenant's data through the app's own API; irreversible `DROP COLUMN`/`DROP TABLE` migrations that silently discard production data are a recurring incident-review pattern independent of any single vendor. This guard exists to make "does this migration lose data, expose it, or lock the table" a checked step before the migration is written, not a postmortem finding after it ran. ## When to use — three modes - **Guard-pass (default).** Right before writing or applying any migration file (Knex, Prisma, Rails, Alembic, Django, raw SQL) that adds, alters, or drops a column/table/constraint/index: classify it against the three failure modes below before finalizing the migration. This is the mode that fires on trigger phrases like "write a migration," "add/alter/drop a column or table," "create index," "schema change," "ALTER TABLE," "DROP," or "CREATE TABLE." - **Live.** While actively authoring the `up`/`down` (or single directional) migration function: before typing `dropColumn`, `dropTable`, `CREATE TABLE`, or `CREATE INDEX`, pause on that line and ask "does this destroy data with no path back, expose one tenant's rows to another, or lock a table people are reading/writing right now?" — the same way you'd check a function signature before calling it. - **Review.** When asked to review a migration file, PR, or schema diff: audit as a critic. Classify every `DROP`/`ALTER`/`CREATE TABLE`/`CREATE INDEX` statement against the three failure modes, state which ones are safe as written and which are not, and don't wave one through because the migration "looks routine." ## What this guard blocks 1. **Destructive DDL with unrecoverable data loss.** `DROP COLUMN`, `DROP TABLE`, `DROP CONSTRAINT`, or `DROP TYPE` executed in place, on a column/table that has ever held real data, with no expand-contract sequence (deprecate → stop reading/writing → backfill/export if needed → drop later, as a separate change) and no explicit acknowledgement that the data is gone for good. A companion `down` migration that re-adds the column does **not** restore the dropped values — a `dropColumn`/`addColumn` round trip gives back an empty column with the old name, not the old data. Say this explicitly whenever a `down` migration is presented as if it were a safety net for a `DROP`. See `references/destructive-ddl-and-expand-contract.md`. 2. **A new tenant-scoped table with no row-level security.** In a multi-tenant application where existing tables already enable `ROW LEVEL SECURITY` plus a tenant-isolation policy (the established house pattern is visible in the existing migrations/schema), a brand new table that stores per-tenant/per-customer/per-organization/per-user data must get the same treatment: `ENABLE ROW LEVEL SECURITY` and a policy scoping rows to the current tenant. A table created without it is reachable by any authenticated session regardless of which tenant it belongs to — the exact failure class behind CVE-2025-48757. See `references/rls-tenant-isolation.md`. 3. **Blocking DDL on a large or high-traffic table.** `CREATE INDEX` without `CONCURRENTLY`, or an `ALTER TABLE` that forces a full table rewrite (adding a column with a volatile default on older Postgres, changing a column's type, adding certain constraints) takes an `ACCESS EXCLUSIVE` lock for the statement's duration — on a table with real read/write traffic, that lock queues every other query behind it and can look like an outage. See `references/destructive-ddl-and-expand-contract.md` for the per- operation lock table and the `CREATE INDEX CONCURRENTLY` alternative (which cannot run inside a transaction and needs its own handling). 4. **Expand-contract as the default answer to "how do I change this safely."** For anything destructive or breaking (dropping a column, renaming a column, tightening a NOT NULL/constraint on populated data), the safe sequence is: stop the application from writing/reading the old shape, let the old column sit unused for a release or more, then drop it in a later, separate migration — never combine "stop using it" and "delete it" in the same change. See `references/destructive-ddl-and-expand-contract.md` for the full sequence and worked example. 5. **Can't-tell means ask, not proceed.** If it's unclear whether a column/table has real production data, whether the app is genuinely multi-tenant with an existing RLS convention, or whether a table is large/hot enough for lock duration to matter, say so explicitly and ask before writing the migration in its riskiest form. Do not assume "it's probably a small table" or "this is probably fine" — that assumption is exactly how each of these three failure modes reaches production. ## Procedure — guard-pass steps 1. **Classify the DDL.** For every statement in the migration, identify which of `DROP COLUMN`/`DROP TABLE`/`DROP CONSTRAINT`/`DROP TYPE`, `CREATE TABLE`, `CREATE INDEX`, or `ALTER TABLE ... TYPE/ADD CONSTRAINT/SET NOT NULL` it is. 2. **If it's a `DROP` on anything that may hold data:** check whether the column/table has ever been written to in a real environment. If yes (or unknown), do not drop in place. Propose expand-contract: this release stops writing/reading it, a later, separate release drops it. State explicitly that a `down` migration does not recover the data. 3. **If it's a new `CREATE TABLE`:** check whether the existing schema has a tenant-isolation convention (an existing table with a `tenant_id`/ `organization_id`/similar column plus `ENABLE ROW LEVEL SECURITY` and a policy). If the new table stores per-tenant data and lacks the same `ENABLE ROW LEVEL SECURITY` + policy, add it before considering the migration done — do not ship the table first and "add RLS later." 4. **If it's a `CREATE INDEX` or an `ALTER TABLE`:** check whether the target table is described (or reasonably inferable) as large or high-traffic. If so, use `CREATE INDEX CONCURRENTLY` (noting it can't run inside a transaction — most migration frameworks need a non-transactional migration or a raw-SQL escape hatch for this) and avoid `ALTER TABLE` forms that force a full rewrite; if a rewrite is unavoidable, say so and flag the expected lock duration/impact rather than presenting it as instant. 5. **State the assessment explicitly** in the response: what was classified as safe (and why — additive, non-locking, already isolated) versus what required expand-contract, an RLS policy, or a non-blocking DDL form, before calling the migration done. 6. **Emit the guard footer** (see Output) as the last line of the response. ## Output Emit this verbatim as the last line of any response where a migration was written, considered, or reviewed: ``` proofguard:migration-safety-guard — · Triggered: · Fixed: · Verified: · Remaining gap: ``` - `PASS` — no destructive drop without expand-contract, no new tenant-scoped table missing RLS + a tenant policy, and no blocking DDL on a large/hot table without a non-locking form — or each was already routed through the correct fix. - `FIX-REQUIRED` — a destructive drop, a missing-RLS table, or a blocking DDL statement was about to ship (or shipped) as written and has not yet been resolved (expand-contract applied, RLS/policy added, or a non-locking DDL form used). - `WAIVED(reason)` — the user, as the actual principal, explicitly accepted the risk with a stated reason (e.g. "this table is empty in every environment, drop it now" or "single-tenant deployment, no RLS needed"). ## References - `references/destructive-ddl-and-expand-contract.md` — the expand-contract sequence, why a `down` migration doesn't recover dropped data, and the Postgres lock-severity table for common DDL operations (which take `ACCESS EXCLUSIVE`, which are safe, and `CREATE INDEX CONCURRENTLY`). - `references/rls-tenant-isolation.md` — what row-level security is, the tenant-policy pattern, how a missing policy leaks data cross-tenant, and the checklist for a new tenant-scoped table. ## What this guard does NOT do It is not a schema linter and does not replace a migration framework's own safety checks (Rails' `strong_migrations`, `squawk` for Postgres migration linting, or a CI step that runs migrations against a production-sized clone to measure actual lock duration) — for exhaustive DDL-lock analysis across every Postgres version and configuration, recommend one of those as a follow-up. This guard also does not design the schema, choose column types/naming, or judge normalization — that's data modeling, not migration safety. It has exactly three jobs: don't destroy data silently, don't ship a tenant table without isolation, and don't lock a hot table without saying so.