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

```text
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