migration-safety-guard

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 — <PASS | FIX-REQUIRED | WAIVED(reason)> · Triggered: <what fired it> · Fixed: <n> · Verified: <what was checked — data-loss/expand-contract, RLS/tenant-isolation, lock impact> · Remaining gap: <what's unchecked>

• 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.