db-migration-safety (M22) — the migration lint contract

M22 lints a candidate migration as a static artifact before execution. It is the contract the migration-safety auditor and the db-migration-writer agent both honor. Feeds Performance & Scale (Almacenamiento/operability category). A destructive irreversible migration is the sev-5 cap on the performance axis.

What it checks (the lint contract)

1. Reversibility — a usable down/rollback exists, or the change follows expand-contract

(add-new → backfill → switch → drop-old in separate deploys). A one-shot destructive step with no down is the highest-severity finding here.

1. Lock level — does the statement take a blocking lock on a hot table? In Postgres: adding a column

with a volatile default (pre-11), ALTER COLUMN TYPE, adding a CHECK/FK without NOT VALID, non-CONCURRENTLY index creation all take ACCESS EXCLUSIVE / block writes. Prefer CREATE INDEX CONCURRENTLY, ADD CONSTRAINT ... NOT VALID then VALIDATE CONSTRAINT.

1. Table rewrite — changes that force a full-table rewrite (certain ALTER COLUMN TYPE, adding a

NOT NULL column with a volatile default on old engines) → slow + long lock on large tables.

1. Destructive ops — DROP COLUMN, DROP TABLE, TRUNCATE, DROP NOT NULL reversals, dropping a

column still read by deployed code. Destructive + irreversible = sev 5 (caps performance).

1. NOT NULL without default added to a populated table → fails or locks; require a backfill plan.
2. Enum mutation — the durable concern: enum values cannot be removed or reordered (removal is

unsupported; renaming/reordering breaks dependents), so a changing set should be a lookup table, not a native enum. ALTER TYPE ... ADD VALUE is append-only and runs in a transaction on PG12+ (only older PG forbade it mid-txn). Flag enum changes; suggest lookup tables.

1. Schema drift — the declarative artifact (schema.prisma / structure.sql / snapshot) disagrees with

the migration history (a column exists in one but not the other) → directional warn.

Tier-0 static checks

Parse migration SQL / ORM migration files (scripts/parse-schema.mjs, parse-orm-python.py). Scan for: DROP TABLE|DROP COLUMN|TRUNCATE; ALTER COLUMN . TYPE; ADD COLUMN . NOT NULL without DEFAULT; CREATE INDEX lacking CONCURRENTLY; ADD CONSTRAINT (FK/CHECK) lacking NOT VALID; ALTER TYPE .* ADD VALUE / enum value removal or reorder; absence of a down/rollback block; drift between the authoritative artifact (references/detection-signals.md precedence) and the latest migration.

Tier-1 verification query

• Affected-table size (does the lock matter?): `SELECT pgsizepretty(pgtotalrelation_size('<t>')),

reltuples FROM pg_class WHERE relname='<t>';`

• Lock the statement would take: EXPLAIN/pglocks inspection, or a dryrun_migration against a

throwaway copy / shadow DB. Use verification.method: dryrunmigration when a shadow DB is available; otherwise migrationlint on the static SQL. When live size is needed to decide severity but no DB is reachable → needsapi, never a silent pass.

Findings

Emit per schema/finding.schema.json. Example ids:

• M22.20240115dropuserslegacy.destructiveirreversible — DROP COLUMN with no down and column

still referenced (fail, severity 5, axis performance, fixable proposed → expand-contract plan).

• M22.addindex.lockblocking — CREATE INDEX without CONCURRENTLY on a hot table (warn, severity 4,

axis performance, fixable auto → add CONCURRENTLY).

• M22.addemailnotnull.notnullnodefault — ADD COLUMN ... NOT NULL no default on a populated table

(fail, severity 4, axis performance, fixable proposed).

• M22.statusenum.enumvalue_removed — enum value removed/reordered (unsupported; breaks dependents) —

suggest a lookup table (warn, severity 3, axis performance).

• M22.drift.columninartifactnotmigration — schema drift (warn, severity 2, axis performance,

confidence directional). Each finding: evidence.observed quotes the offending migration statement verbatim (secrets redacted); verification.reproduce is a runnable command above using $DATABASEURL; verification.method is migrationlint or dryrunmigration; fixpreview carries the safer rewrite (e.g. CONCURRENTLY, NOT VALID + VALIDATE, an expand-contract sequence); expectedimpact carries {axis, confidence, magnitude, rationale} — banded, never a naked %.

Honesty

• Severity of a lock depends on table size and write rate — without live evidence, a blocking op on an

unknown-size table is directional (medium), not an established sev-5. Don't claim a downtime duration in seconds; magnitude banded only.

• Only DROP/TRUNCATE-class destructive and irreversible migrations cap performance at sev 5.

Reversible or expand-contract changes are warns. Drift is directional and never caps.

• Read-only: M22 lints and proposes; it never executes a migration. Execution is the writer agent's job

behind explicit user accept.