IdempotencyGENERALDATABASE-SPECIFICFRAMEWORK-SPECIFIC

The Idempotency Key Flow

Client sends a key, the server claims it atomically, and the outcome is either process-and-store or return-the-stored-result.

What actually happens How to build it

The requirement, the obvious build, and why it breaks

Every lesson starts where the work starts: someone asked for something, and the first implementation that comes to mind has a problem.

The question

Step by step, what does the server do when a request arrives carrying an idempotency key?

The requirement

POST /payments with Idempotency-Key: 7f3a... must charge once and return the same confirmation however many times the client retries, including when two retries arrive at the same moment.

The obvious build

Look the key up. If it exists, return the stored response. If not, process the request and store the response against the key. Two branches, easy to read.

Why it breaks

Two retries arrive milliseconds apart. Both look up the key, both find nothing, both process, both charge. The lookup and the store are separate operations with a window between them — the classic check-then-act (Duplicate Detection).

How it breaks in production

Two retries arrive milliseconds apart. Both look up the key, both find nothing, both process, both charge. The lookup and the store are separate operations with a window between them — the classic check-then-act (Duplicate Detection).
The response is stored after processing succeeds, so a crash between the charge and the store leaves the card charged and the key absent. The next retry charges again.
The key is stored before processing, so a crash during processing leaves a key marked done with no payment. Every retry now returns a success the customer never received.
The stored response includes a transient 503 from a dependency, so every subsequent retry replays a failure that would have succeeded.
The key row and the payment row are written in separate transactions, so a rollback of one leaves the other — and the two are now permanently inconsistent.

Requirement→API Contract→Application Logic→Data Access→External Deps→Concurrency→Failure→Security→Observability→Deployment→Scale

What is actually happening

The flow has exactly one hard part: turning "have I seen this key?" into an operation with no window. That means a claim, not a check — an atomic insert whose success or failure decides which request proceeds (Atomic Operations).
A unique constraint on (principal, key) is the primitive. INSERT ... ON CONFLICT DO NOTHING returns the affected row count, and that count is the answer: one means you claimed it, zero means someone else did.
Claiming creates a record in state in_progress. That state is what makes the concurrent case expressible: the second request finds a row that is neither absent nor complete, and can respond 409 Conflict with Retry-After rather than executing.
The key record and the business effect should commit in one transaction wherever both live in the same database. Then there is no window in which one exists without the other, and a duplicate cannot proceed because its claim failed (Where the Transaction Boundary Goes).
When the effect is external, the two cannot share a transaction, and the in_progress state becomes load-bearing: it records that an attempt was made, so recovery can ask the external system what happened rather than assuming (The Dual Write Problem).
Storing the response is what makes the retry return the *same answer* rather than a conflict — status code, body, and the headers a client needs to interpret them (Idempotency Storage).
A request fingerprint stored alongside the key is what catches a client reusing a key for a different intent. Without it, the client's bug becomes silently wrong data.

The whole flow, and the one step that must be atomic

Written out, the flow is six steps and only one of them is difficult. Authenticate, so the principal is known. Claim the key atomically. If the claim succeeded, execute and record the outcome in the same transaction. If it failed, read the existing record and dispatch on its state.

The claim is the step everything depends on. Replace it with a lookup followed by an insert and every other step is still correct while the whole flow is broken, because two concurrent retries both take the "first time" branch.

Note where the transaction boundary sits. The claim and the domain write are inside it together; that is what makes a rollback consistent and what makes a duplicate impossible rather than merely unlikely.

UserLLMAgentToolDataDecisionHumanGuardrail

POST /payments with Idempotency-Key

1
Authenticate
Establishes the principal that scopes the key.
fails by Idempotency middleware placed before auth, so keys are claimed anonymously (Middleware Ordering Is a Correctness Decision).
2
Validate the key header
Presence, length, character set.
fails by Accepting an unbounded attacker-supplied string as a database key.
3
Claim
INSERT (principal, key, fingerprint, state=in_progress) ON CONFLICT DO NOTHING.
fails by Being a SELECT followed by an INSERT — two operations, one window, duplicate charges.
4
Branch on rows affected
1 -> execute; 0 -> read the existing record.
fails by Branching on a prior read instead, reintroducing the race.
5
Execute + record, one transaction
Charge, write the payment row, set state=completed with the response.
fails by Two transactions, leaving effect-without-record or record-without-effect.
6
Dispatch an existing record
completed -> replay; in_progress -> 409 + Retry-After; fingerprint mismatch -> 422.
fails by Returning 409 for completed keys; blocking on in_progress; ignoring the fingerprint.
7
Sweep stale claims
Resolves in_progress records older than the maximum request duration.
fails by Absent (keys blocked forever) or too aggressive (releases a live claim).

Steps 3 and 5 carry the guarantee. Everything else is bookkeeping around them.

Claim, do not check

The difference between a working implementation and a broken one is two lines of SQL, and the broken one reads better. That is why it keeps getting written.

The insight to hold onto is that the database's unique index is already an atomic decision procedure shared by every connection. You do not need a lock, a mutex or a distributed coordinator to decide which of two concurrent requests wins — you need to ask the index, once, and read the answer off the affected-row count.

This is the same primitive as compare-and-swap, expressed in SQL: a single operation that both tests a condition and takes an action, with no observable moment in between (Compare-and-Swap: The Primitive Everything Is Built On in the concurrency domain covers the underlying idea).

Deciding whether this request is the first

Check, then act

const existing = await db.oneOrNone(
  'SELECT * FROM idempotency_keys WHERE principal = $1 AND key = $2',
  [principal, key])

if (existing) return replay(existing)

// Window: a concurrent retry also found nothing and is here too.
const result = await charge(body)
await db.none(
  'INSERT INTO idempotency_keys (principal, key, response) VALUES ($1,$2,$3)',
  [principal, key, result])
return result

Claim, then act

const claim = await db.oneOrNone(
  `INSERT INTO idempotency_keys (principal, key, fingerprint, state)
        VALUES ($1, $2, $3, 'in_progress')
   ON CONFLICT (principal, key) DO NOTHING
     RETURNING id`,
  [principal, key, fingerprintOf(body)])

if (!claim) {
  const existing = await db.one(
    'SELECT * FROM idempotency_keys WHERE principal = $1 AND key = $2',
    [principal, key])
  if (existing.fingerprint !== fingerprintOf(body)) throw new KeyReuse422()
  if (existing.state === 'in_progress') throw new Conflict409({ retryAfter: 1 })
  return replay(existing)                       // same status, same body
}

await db.tx(async (t) => {                      // one transaction
  const result = await charge(body, downstreamKey(key, 'charge'))
  await t.none('INSERT INTO payments ...', [result])
  await t.none(
    'UPDATE idempotency_keys SET state = $2, response = $3 WHERE id = $1',
    [claim.id, 'completed', result])
})

The left version has a window between the SELECT and the charge in which a concurrent retry passes the same check. The right version has none: the unique index on (principal, key) admits exactly one claimant, the loser learns it lost from a null return, and the payment row and the completed state commit together so no crash can leave one without the other.

The states a key can be in, and why in_progress must expire

DATABASE-SPECIFICWith Redis as the key store, SET key val NX PX <ttl> gives the claim and the in_progress expiry in one operation, so the sweeper is free. What you lose is the shared transaction with the business write — the claim can commit while the payment rolls back, which is the trade you are making when you move the key store out of the database.

Introducing an in_progress state solves the concurrency problem and creates an operational one: a process that crashes between claiming and completing leaves a record that will never resolve on its own. Every subsequent retry of that intent gets 409, forever, and the customer's payment is permanently stuck.

So the state machine needs a way out. A sweeper that reclaims records older than the maximum plausible request duration is the standard answer, and its threshold is a genuine safety parameter: too long and legitimate intents stay blocked; too short and it releases a claim whose original request is still running, permitting the double execution the whole design exists to prevent.

The threshold should therefore be derived from the request timeout, not chosen aesthetically. If no request can survive past sixty seconds because the proxy kills it, then a claim older than several minutes is unambiguously dead. And where the effect was external, "reclaim" should mean querying the provider for what actually happened rather than assuming it did not.

State	Set when	Response to a new attempt	How it is left behind
(absent)	Never seen this key for this principal	Claim it and execute	Normal first attempt
`in_progress`	Claim succeeded, work started	409 with `Retry-After`	Process crashed mid-execution — needs the sweeper
`completed`	Work finished and committed with the response	Replay the stored status and body	Terminal; removed only by retention
`failed_permanent`	Deterministic business rejection	Replay the stored error	Terminal; retrying cannot change it
(released)	Transient failure — dependency down, timeout	Treated as absent; retry may proceed	Deliberate: a 503 must not poison the key
`stale`	Sweeper found `in_progress` past the threshold	Reconcile with the external system, then resolve	Only reachable if the sweeper exists

How to build it

Most important first.

Claim the key atomically before doing any work: a single INSERT ... ON CONFLICT DO NOTHING that also records the request fingerprint and a state of in_progress.
Branch on rows affected, not on a prior read. One row: proceed. Zero rows: read the existing record and dispatch on its state.
On an existing completed record, compare fingerprints first. Mismatch is 422; match returns the stored status and body verbatim.
On an existing in_progress record, return 409 with Retry-After. Do not block waiting for the other request — that converts a duplicate into a held connection and a possible deadlock (Pessimistic Locking).
Commit the key record and the domain write together where both are local. That single decision removes most of the crash windows for free (One Transaction or Two).
Store only responses that represent a settled outcome. A transient dependency failure should release the claim so a retry can genuinely retry (An Error Taxonomy That Maps Cause to Response).
Add a stale-claim sweeper: an in_progress record older than the maximum plausible request duration is a crashed attempt, and must be resolvable rather than permanently blocking.

What can go wrong

Failure modes

Implementing the claim as SELECT then INSERT — the exact race the mechanism exists to close, and the most common way this is built.
A crashed request leaving a permanent in_progress record, so every subsequent retry receives 409 forever and the intent can never complete.
Storing a transient error as the final response, converting one bad moment into a permanently poisoned key.
The stale-claim sweeper being too aggressive and releasing a claim while the original request is still running, allowing a genuine double execution.
The 409 path having no Retry-After, so clients retry immediately in a tight loop and turn a brief overlap into a hot spin (Retry Storms).
The fingerprint computed over a body that includes a per-attempt field, so every retry looks like a key reused with a different body and is rejected with 422.
Idempotency middleware placed before authentication, so an unauthenticated request can claim a key belonging to a real caller (Middleware Ordering Is a Correctness Decision).

What can race

Two retries claiming the same key concurrently — resolved by the unique index, which is the entire point of the claim-first design (Optimistic Concurrency).
A retry arriving while the original is between its claim and its commit, seeing in_progress and correctly declining to act.
The stale-claim sweeper racing the original request it believes has crashed. Setting the staleness threshold above the maximum request timeout is what keeps this closed.
The key record committing while the external charge is still in flight, so the record's state describes an intent whose outcome is genuinely unknown (The Dual Write Problem).

Security

Claim under (principal, key), never key alone. A key presented by a different caller must be treated as unseen, not as a hit (Multi-Tenancy).
Run authentication before idempotency in the middleware chain, so the principal is known when the claim is made (Authenticate First, or Rate-Limit First?).
Re-check authorization on replay. A stored response for an operation the caller may no longer perform must not be replayed to them (Object-Level Authorization).
A 409 leaks that a key is in use. Since keys are caller-scoped and caller-generated, that is not a disclosure — but only because of the scoping, which is the reason it is not optional.
Bound the fingerprint: hash the body rather than storing it, so the key table does not become a second copy of every payment request.

Misreads

"Check if the key exists, then insert." That is the bug, written as a design. The check and the insert must be one statement.
"Return 409 on any repeat." A completed key should return the original response, not a conflict. 409 is only for the in-flight case.
"Store the response after the transaction commits." Then a crash between commit and store leaves the effect applied and the key unresolved.
"The middleware can handle it generically." Generic middleware can claim keys and replay responses; it cannot make the effect and the record atomic, because it does not own the transaction.
"A 409 means something went wrong." It means two copies of one request are in flight, which is normal on a bad connection.

Operating it

How you see it in production

A single counter with an outcome label: claimed, replayed, in_progress_conflict, fingerprint_mismatch, stale_reclaimed. Each is a different story about your clients.
Alert on in_progress records older than the request timeout. Every one is a crashed attempt with an unknown external effect.
Track the replay-to-claim ratio per endpoint. A rise means client timeouts are firing more often, usually because the endpoint got slower (Tail Latency: Why p50 Being Fine Does Not Help).
Trace-link the replay to the original: storing the original's correlation id in the key record lets one support question resolve in one query (Correlation Ids That Survive Every Hop).

What changes at 10x and 100x

The claim is an indexed insert on the hot path of every protected write. It is small, and it is not free, and at high write rates the index on (principal, key) is a contention point (Hot Keys: When Aggregate Metrics Hide a Saturated Node).
The in_progress sweeper must scan by state and age, so those columns need an index or the sweeper becomes the slowest query in the system.
409-on-concurrent-use scales cleanly because it does not block; a design that waits for the in-flight request instead consumes a connection per waiter and collapses under exactly the load that produces the overlaps (Connection Pool Saturation: Waiting in Front of an Idle Database).

What this costs

Returning 409 for concurrent use is simple and correct and pushes a retry back onto the client. Waiting for the original would be friendlier and would hold a connection for the duration of another request, which is how a burst becomes an outage.
One transaction for the claim and the effect gives atomicity and requires both to live in the same database. Once the effect is external, you trade that for a state machine and a sweeper.
Fingerprint checking catches real client bugs and creates a new failure mode when the client legitimately changes a field between attempts.
Storing responses makes replay exact and turns the key store into a store of response bodies, with the size and retention consequences that follow (Idempotency Storage).

Where this applies

Backend advice is context-sensitive. These labels say what each claim is specific to, and where a different stack or scale would differ.

GENERALClaim, dispatch on state, store the outcome — the shape holds regardless of stack.
DATABASE-SPECIFICPostgres gives you INSERT ... ON CONFLICT DO NOTHING RETURNING, which claims and reports in one round trip. MySQL's INSERT IGNORE suppresses other errors too and its affected-row semantics under ON DUPLICATE KEY UPDATE differ (0 for an unchanged row, 2 for an updated one), so a branch on rows-affected needs different code. Redis SET key value NX PX ttl is an equivalent atomic claim with built-in expiry but no transactional relationship to your business write, which is exactly the tradeoff.
FRAMEWORK-SPECIFICImplementing this as middleware requires the middleware to participate in the handler's transaction, which most frameworks do not make natural — the usual result is a claim committed separately from the effect. Doing it inside the service layer, where the transaction lives, is less elegant and more correct.

Where the depth lives

This domain teaches the application-side mechanism and hands the rest off.

API Designidempotency-keys idempotency status-codes

Databasetransactions-and-acid concurrency-anomalies isolation-levels

Performancehot-keys connection-pool-saturation

Concurrencycompare-and-swap atomicity-illusion reasoning-about-races finding-the-critical-section

Domains that do not exist yet

— Distributed Systems — a claim on a shared key as a one-shot consensus, and why a single unique index is enough when everything shares one database.