Idempotency at the Effect Boundary, Not the Handler Entry

A job executes, sends its mail, and the worker is killed before the acknowledgement reaches the queue. The lease expires, another worker claims the job, and the mail is sent again. Nothing in that sequence is a defect in the queue: the queue promised at-least-once execution, and it delivered. Preventing the second mail is the handler’s job, and the place where it can be prevented is narrower than it first appears.

The temptation is to put a guard at the top of the handler: read a processed flag, return early if set, otherwise do the work and set the flag. That pattern fails under exactly the conditions it was written for, because the check and the work are separate operations with a window between them, and the window is where a second worker arrives.

A check at the entry is not a lock

Two workers can both read the flag as unset — one because the job was genuinely never processed, the other because the first worker’s transaction has not committed yet — and both proceed. Adding the flag write first and the work second inverts the bug rather than removing it: a crash between the flag and the effect leaves a record saying the work was done when it was not, and every subsequent attempt returns early forever.

Both orderings are unsound for the same reason: they treat a piece of application state as a mutual-exclusion primitive, when mutual exclusion is provided by the storage engine’s constraints and locks, or by nothing at all.

What works is making the deduplication record and the effect part of one atomic operation. When both live in the same database, a unique constraint does it:

-- The key is derived from the logical request, not the attempt.
INSERT INTO effects (dedup_key, job_id, kind)
VALUES ($1, $2, 'receipt-mail')
ON CONFLICT (dedup_key) DO NOTHING
RETURNING id;

A returned row means this execution owns the effect; an empty result means another execution already inserted the key and this one must not proceed. The ON CONFLICT clause makes the insert conditional in a single statement, and the unique index is the arbiter. There is no window: the index either accepts the new key or it does not, and the decision is made inside the storage engine while the row lock is held.

The key has to identify the request, not the attempt

The deduplication key is the hard part, and choosing it wrongly produces either duplicates or silently dropped work.

A key built from the job identifier and the attempt number is useless: every retry has a different attempt number and therefore a different key. A key built from the job identifier alone is correct for a job that must produce exactly one effect, and wrong for a job that legitimately produces several — a nightly digest job that sends one mail per subscriber needs a key per subscriber, not per job.

The workable rule is that the key names the fact the effect asserts, in the vocabulary of the domain: receipt-mail:{order_id}, ledger-entry:{transfer_id}:legacy-migration, thumbnail:{asset_id}:{width}. Two executions of the same job compute the same key; two logically different effects compute different ones. Where the effect depends on user-supplied content that can change between attempts, the key usually should not include a hash of that content, because a changed input on retry would produce a second effect rather than being recognised as the same job. If the content can genuinely change, the job is not one effect but a stream of them, and the key needs a version or an input revision number.

The same reasoning applies one level up, at enqueue. A client that times out on its enqueue call and retries produces two jobs unless the queue accepts a producer-supplied key and rejects a second insert with the same key inside a retention window. A unique index on (queue_name, dedup_key) with a partial predicate that ignores finished jobs of the distant past gives exactly that, at the cost of the producer having to generate a stable key before its first attempt.

Turning deltas into facts

The strongest form of idempotency needs no deduplication table at all: the effect is expressed so that repeating it cannot change the outcome.

An increment is not of that form. UPDATE accounts SET balance = balance + 100 WHERE id = $1 applied twice moves the balance by 200. A state transition with a guard is: UPDATE shipments SET status = 'dispatched' WHERE id = $1 AND status = 'packed' changes nothing on the second run, provided the state machine does not cycle back.

The general conversion is from a delta to a fact. Rather than adjusting a counter, insert an immutable row that records the event, keyed by the event’s identity, and derive the counter from the rows:

INSERT INTO ledger (transfer_id, account_id, amount_cents)
VALUES ($1, $2, $3)
ON CONFLICT (transfer_id) DO NOTHING;

The balance becomes a sum over the ledger, materialised into a running total that is updated in the same transaction as the insert. A duplicate execution attempts a second insert with the same transfer identifier, the unique index rejects it, and the sum is unchanged. Nothing needs to be remembered about attempts, because the identity of the transfer is carried in the data.

This design also gives an audit trail and a repair path for free: the ledger rows are the record of what was asserted, so an inconsistency between the running total and the sum can be detected and rebuilt. The cost is storage proportional to the number of events rather than to the number of accounts, and a read path that must either sum or maintain a derived total.

Not every operation converts. Allocating the next value from a sequence is the standard exception: a retry consumes a number even if the effect is rolled back, because the allocation itself is the effect. Where allocation must be retried, the gap has to be acceptable to the consumer — sequences already permit gaps for other reasons — or the number must be assigned by the database that stores the result.

Two phases for effects that cannot be transactional

The unique-constraint pattern assumes the effect and the key live in one transactional store. When the effect is an HTTP call to another service, there is no such store, and the pair “key recorded” and “call made” cannot be committed together.

The standard structure has three steps in two phases. First, insert the key with an in_progress state, so that a concurrent execution is refused. Second, perform the effect. Third, mark the key completed with whatever result the caller needs. A crash between the first and third steps leaves an in_progress row that looks exactly like a concurrent execution in progress, and a naive reader would refuse all further attempts. That is a livelock: the job can never succeed because its own abandoned intent record blocks it.

A job whose effect is the enqueue of its successor has the same shape, and the intent record is the successor’s unique key, as Chained Jobs and Fan-In Completion describes for the last child of a batch.

The fix is to give the intent record an expiry, the same lease idea that governs job claims. An in_progress row older than a threshold is treated as abandoned and may be taken over; a fresh one is respected. The threshold is unavoidably a guess about how long the external call takes, and it must exceed the call’s timeout, including the retries the client performs internally.

Even with the expiry, the crash window remains: the effect may have been applied remotely and the process died before marking it completed. The retry then repeats a call that already succeeded. This is why remote effects need their own deduplication, keyed by the same identifier — the receiving service stores the key alongside the response and returns the stored response instead of applying the effect twice. Stripe’s API does exactly this: a request carrying an Idempotency-Key header is executed once, and repeats with the same key replay the original response rather than charging again.

Services without that facility leave the caller with a choice between possible duplicates and possible lost work. Duplicates are usually the smaller harm for a payment retry only when the receiving side reconciles on its own identifier; where it does not, the correct move is not to retry without a key at all and to surface the ambiguous case for reconciliation. An acknowledgement that says “the request may or may not have been applied” is honest; silent duplication is not.

Where the records live and when they go

Deduplication records grow with traffic and are useful only for as long as a duplicate can still arrive. The retention window is set by the maximum time a job can be retried, not by anything about the effect.

For a queue with a 14-day retention and a retry policy spanning two days, a deduplication window of three days covers every possible replay. Records older than that can be deleted, which makes the table a high-churn, append-heavy structure with a periodic purge on the creation timestamp. Deleting by range on an indexed timestamp is cheap; deleting scattered rows is not.

Two details cause trouble. A record deleted while a very old duplicate is still in flight produces a second effect, so the window must be computed from the worst-case retry horizon rather than from the typical one. And the deduplication table should not be the same table as the effect’s own data when the effect’s data must be retained forever: the purge then either deletes audit data or is not run.

The honest guarantee

At-least-once delivery plus a deduplication key equals at-most-one effect only if the effect and the key are committed together, or if every receiver consults the same key. Neither holds universally, and the gap is where the term exactly-once is misused.

What can be stated without qualification is narrower and still useful: a job that writes only to a single transactional store can be made effect-once by unique constraints; a job that calls another service can be made effect-once if that service accepts a key; a job that calls a service without such support can only be made duplicate-visible, with a reconciliation path that compares the two sides.

The mechanisms downstream depend on which of those cases applies. A stale worker whose lease expired can be prevented from committing by the fencing described in Fencing Tokens and the Stale Claim Problem, but fencing protects the store, not the outside world. Retry policy, covered in Retry Ladders and Dead-Letter Promotion, decides how many times the same key will be presented — which is why the key has to be stable across attempts in the first place.