db-migration-verification

Migration plans include catalog verification

Every database migration produced under a plan-first gate must ship its SQL alongside a post-apply verification query that reads catalog state directly. The user applies the migration, runs the verification query, then pastes the result back. Spot-check before any code is written against the new schema.

When this applies

This skill fires on any work involving:

• New migrations (CREATE TABLE, ALTER TABLE, CREATE TYPE)
• RLS policy changes (CREATE POLICY, DROP POLICY, policy sweeps)
• Trigger changes (CREATE TRIGGER, function changes)
• Constraint changes (CHECK, UNIQUE, FOREIGN KEY adds or drops)
• Column adds, drops, or type changes
• Index changes when correctness depends on them

What a verification query looks like

The query reads catalog tables directly, not application tables. The shape varies by what's being verified.

For RLS policy sweeps, query pg_policy:

SELECT polname, polcmd,
       polqual IS NOT NULL AS has_using,
       polwithcheck IS NOT NULL AS has_with_check
FROM pg_policy p
JOIN pg_class c ON c.oid = p.polrelid
WHERE c.relname IN ('<affected_tables>');

For CHECK or UNIQUE constraint adds, query pg_constraint:

SELECT conname, contype, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'public.<table>'::regclass;

For trigger changes, query pg_trigger:

SELECT tgname, pg_get_triggerdef(oid)
FROM pg_trigger
WHERE tgrelid = 'public.<table>'::regclass
  AND NOT tgisinternal;

For column changes, \d+ public.<table> in psql or its information_schema.columns equivalent works.

For function or trigger function changes, pg_get_functiondef('<schema>.<function>'::regproc).

The principle: read what the catalog actually contains, not what the application assumes is there.

What "good" looks like

The plan must spell out the expected result of the verification query in concrete terms before the migration is applied.

Examples:

• "Expecting has_with_check = true on all 29 recreated FOR ALL policies. If any are NULL, flag and pause."
• "Expecting the new payment_auth_action_type constraint to enumerate exactly ('payment', 'close_bank_account', 'toggle_rics_designation'). Any other set means a prior migration moved the baseline."
• "Expecting one new trigger named enforce_admin_charge_summary_of_rights. Any other trigger count on this table means the migration partially failed or another migration ran in parallel."