I shipped a nightly enrichment job with a unique index on (tenant_id, source_hash) and called it idempotent.

Forty-eight hours later I had three rows for one tenant and one source document, all identical, and the index had approved every one of them.

If a unique index is unique, how does an insert get past it twice? The answer is not a race, a bug in Postgres, or a failed commit — it is one nullable column and a rule most of us read once and then forget.

I want to write this down because the fix took ten minutes and the diagnosis took a weekend, and because the harness I used is short enough to paste into any throwaway database.

What the job was supposed to guarantee

The contract was small and, I thought, airtight:

One row per (tenant_id, source_hash) pair, forever.

Re-runs are safe: if the row exists, skip the expensive step.

A skipped row must never mean a half-written row.

The expensive step was a model call that labelled each document, so skipping mattered.…