Idempotency keys are harder than a unique index

Every payments API tells you to send an idempotency key so retries do not double-charge. The client side is simple. The server side is where it gets interesting, and most implementations I have reviewed handle roughly a third of the cases.

The naive version#

CREATE TABLE idempotency (
  key         TEXT PRIMARY KEY,
  response    JSONB NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

Check for the key, return the stored response if present, otherwise do the work and store the result. This handles the case it was designed for: a client whose connection dropped after the server committed, retrying the same request a few seconds later.

It does not handle the three cases that actually cause incidents.

Failure one: concurrent duplicates#

A client with an aggressive retry timeout can send the second request while the first is still in flight. Both look up the key, both miss, both proceed to charge the card.

The fix is to claim the key before doing the work, inside the same transaction, and let the database arbitrate:

INSERT INTO idempotency (key, state)
VALUES ($1, 'in_progress')
ON CONFLICT (key) DO NOTHING
RETURNING key;

If that returns no row, someone else owns the key. The question is what to do next. Returning a 409 is honest but pushes the problem to the client. Better is to wait briefly for the in-flight request to finish and then return its result, falling back to 409 only on timeout.

Failure two: the same key with a different body#

Nothing stops a buggy client from reusing a key across genuinely different requests. If you only store the key, you will happily return the response for a ₹100 charge to a request that asked for ₹10,000.

Store a fingerprint of the request and compare it:

const fingerprint = createHash("sha256")
  .update(canonicalJson(req.body))
  .digest("hex");
 
const existing = await claimKey(key, fingerprint);
if (existing && existing.fingerprint !== fingerprint) {
  // Same key, different request. This is a client bug, not a retry.
  throw new HttpError(422, "idempotency key reused with a different payload");
}

Canonicalising the JSON matters — sort object keys, normalise number formats — or a client that serialises fields in a different order on retry will trip the check on a request that was genuinely identical.

Failure three: the crash window#

The hard one. The server charges the card, then crashes before writing the response. The key is sitting in in_progress with no result, and the money has moved.

A retry now finds a claimed key with no stored response. It cannot return success, because it does not know what happened. It cannot re-run the charge, because the charge may well have gone through.

There are two honest ways out:

1. Record intent before acting. Write the key, the fingerprint, and a generated request ID; pass that request ID to the gateway as its idempotency key. On retry, ask the gateway what happened to that ID. This makes the gateway the source of truth and works as long as it supports idempotent creates. Most do.
2. Reconcile out of band. Sweep in_progress rows older than some threshold, query the gateway for their state, and resolve them. Slower, but it is the safety net you want regardless.

Do both. The first handles the common case in-line; the second catches whatever the first misses.

The state machine that actually works#

                claim key
   (none) ──────────────────▶ in_progress
                                  │
              gateway result      │      crash / timeout
          ┌───────────────────────┴───────────────────┐
          ▼                                           ▼
      succeeded                                  needs_recovery
     (response stored)                    (sweeper queries gateway,
                                           transitions to succeeded
                                              or failed)

Three things make this work that the naive version lacks: the key is claimed atomically before any side effect, the request fingerprint is checked on every hit, and there is an explicit state for "we do not know yet" rather than overloading absence-of-response to mean failure.

Expiry#

Keys cannot live forever. Stripe holds them for 24 hours; that is a reasonable default. What matters is that expiry is longer than your maximum client retry window, or a slow retry will sail past an expired key and charge again. If your clients retry with exponential backoff for up to an hour, a 24 hour TTL has plenty of headroom. If some batch job retries failed rows the next morning, it does not.