Designing idempotent job handlers when the database cannot enforce uniqueness
Teams often assume idempotency starts with a unique index on external_id. In legacy schemas—especially event-sourced ledgers—that constraint is politically or technically off the table. Jobs still retry after network blips, so handlers must tolerate duplicate delivery without double-spend.
Start from the business invariant
Write down what must happen at most once: “post settlement for trade T” or “emit invoice I.” Everything else is implementation. If finance accepts multiple rows with a reversal flag, your handler should write a provisional row and let reconciliation merge—not silently skip.
Application-level idempotency keys
Store keys in a narrow table with TTL aligned to your retry window. On handler entry, insert the key in the same transaction as the side effect. Duplicate inserts become no-ops with a clear log line for auditors.
Outbox versus inbox
When the side effect is “call partner API,” persist the intent in an outbox table first, then let a separate dispatcher own HTTP retries. Mixing HTTP and DB writes inside one job without an outbox is where we see the worst duplicate charges.
Testing duplicates on purpose
Chaos tests that enqueue the same payload twice should be part of CI for payment-adjacent jobs. If the second run changes row counts, you have work before production load does it for you.
Reliability audit engagements include a pass over your idempotency story for one critical pipeline.