agent ran the migration against staging and it passed, then the same file failed on prod - production data broke an...

TL;DR

Treat it as a data problem, not a migration problem. Write a diagnostic query that finds the rows breaking the migration, fix the data or make the migration data-tolerant, and add a pre-migration data check against a prod snapshot to the runbook. Staging passes because its data is clean; prod has years of edge cases the migration assumed away.

agent ran the migration against staging and it passed, then the same file failed on prod - production data broke an assumption

Steps

1. Capture the exact failure. Get the failing statement and the full error from the migration logs, not just the tool's summary line. Expected: you have the exact statement and error, for example a NOT NULL violation naming the column, or a unique violation naming the constraint.

2. Write a diagnostic query that reproduces the condition on prod data. Nulls: select count(*) from [table] where [column] is null;. Duplicates: select [column], count(*) as n from [table] group by [column]; and look for rows where n is 2 or more. Expected: the query returns the exact rows that break the migration.

3. Decide the fix and write it down in the migration's comments. Either clean the data first with a backfill, or make the migration data-tolerant: add the column nullable, backfill in batches, then add the constraint in a follow-up migration. Expected: a recorded decision, not a guess applied under pressure.

4. If prod is partially migrated, check the migration history table and reconcile state before retrying, so nothing gets applied twice. Expected: the history table matches the actual schema.

5. Prevent the recurrence. Add a pre-migration data-validation step that runs your diagnostic queries against a recent prod snapshot before every deploy. Expected: the next violation gets caught before it ships, not during the deploy.

Use this when

• The migration passes on staging but fails on prod.
• The error names data URIs nulls, duplicates, check or unique violations.
• The failing step is DDL that validates existing rows.
• The same file ran clean in every environment except prod.

Not for this skill when

• The migration fails identically on staging (that is a pure syntax or logic bug).
• The failure is a connection, permission, or timeout issue on prod.
• The tool version differs between staging and prod (that is version skew).

Variant phrasings

• migration works on staging fails on production
• production data broke the migration
• NOT NULL constraint fails only in prod
• same migration file different result staging vs prod

Why it happens

Staging data is usually a small clean subset or synthetic fixtures. Prod accumulates nulls, duplicates, and long-tail values over years. Constraints the migration adds get validated against every existing row, so a migration that is logically correct still fails on data the author never saw.

Edge cases

• Validating a new constraint on a huge prod table takes a strong lock. Add the constraint as NOT VALID, then run VALIDATE separately to keep the lock short.
• The data fix may itself need to be a migration (or several): add nullable, backfill in batches, then add the constraint. One big-bang migration is how you got here.
• If the diagnostic query is slow on prod, run it against a restored snapshot instead of the live primary.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_495jsVzvBWvnmef5JeVR5Q