You are implementing support for the Idempotency-Key request header on a POST endpoint. What does the server store, and what is the request-handling flow for a first request versus a replay?

## The data model A workable dedupe record looks like this: - **scope** — who the key belongs to: API key, tenant, or account id. Never a global namespace (covered below). - **idempotency_key** — the client-supplied value. Together with scope this is the primary key or unique index. - **request_fingerprint** — a hash over the method, request target and body, used to detect a key reused with a different payload. - **state** — `in_progress` or `completed`. This is what makes concurrent retries safe. - **response_status, response_headers, response_body** — the frozen outcome to replay. - **resource_id** (optional) — a direct pointer to the entity created, useful for reconciliation. - **created_at, expires_at** — retention control. - **locked_at / lease_expires_at** (optional) — so a crashed in-progress row does not poison the key forever. ## The flow **Step 1 — claim the key by writing, not by reading.** The instinct is `SELECT ... ; if absent then execute`. That is a check-then-act race: two concurrent retries both see nothing and both execute. Instead, attempt `INSERT` of an `in_progress` row and let the unique index arbitrate. Exactly one insert succeeds; the other gets a unique-violation and knows a sibling request owns the key. **Step 2 — validate the fingerprint.** If a row exists and the fingerprint differs, the client has reused a key for a different payload. Reject (commonly 422; some APIs use 400 or 409) and execute nothing. **Step 3 — execute the operation.** **Step 4 — persist the response and mark completed.** If the dedupe store lives in the same database as the business data, do steps 3 and 4 in one transaction; then either both happened or neither did, and a retry after a crash is genuinely a first attempt. This is the single most valuable implementation decision in the whole pattern, and it is the reason people put the dedupe table in the primary database rather than in a separate cache. **Step 5 — on replay, return the stored response verbatim.** Same status, same body, same `Location`. Clients parse these; changing 201 to 200 on replay breaks callers that branch on the status. Signal the replay in a separate response header — `Idempotent-Replayed: true` is the common convention — so callers and your own logs can distinguish it. ## Which responses to freeze - **2xx** — freeze. That is the point. - **4xx caused by the request** (validation errors) — usually freeze, since the same request will fail identically anyway and freezing prevents a bad retry loop from doing work. Some APIs choose not to, so that a client fixing its payload… but note that a fixed payload changes the fingerprint and would be rejected as a mismatch anyway; the client should use a new key. - **5xx and timeouts** — normally do **not** freeze. If you store "500" against the key, the client's retry gets 500 forever and the operation can never succeed. Instead leave the key in a retryable state (or delete the row) so a retry re-executes. This is why the state column matters: a crashed request must leave behind either nothing or an expired lease, not a permanent failure. ## Response-size and storage realities Storing full response bodies is fine for small JSON but not for large payloads. Common mitigations: cap the stored body size, store only what is needed to reconstruct (status + resource id, re-rendering the representation on replay), or compress. Whatever you choose, replays must remain faithful — a replay that returns a *re-read* of the resource can differ from the original response if the resource changed afterwards, which is a subtle and real behavioural difference to decide deliberately. ## Where to put the store **Same relational database as the business data** — strongly preferred when the effect is local, because you get atomicity between the effect and the dedupe record for free, plus durability and a unique index for the race. Cost: extra write load on the primary and a table that needs pruning. **Redis or another cache** — fast and easy TTLs, but it is a second system with no transactional relationship to your effect. A crash between "charge succeeded" and "key marked completed" is unrecoverable; an eviction or failover losing keys silently permits duplicate execution. Acceptable when the effect is cheap to repeat, dangerous for money. ## Idempotency for effects you don't own If the operation calls a downstream provider (a PSP), the cleanest design is to **pass your key through** as the downstream idempotency key. Then even if your own record is lost, the provider deduplicates, and the two systems agree on what "the same operation" means. ## Pitfalls worth naming in an interview - SELECT-then-INSERT instead of INSERT-and-catch-violation (the race). - Marking `completed` before the effect commits (a crash then replays a success that never happened). - Returning a different status on replay than on the original. - Freezing 5xx responses and permanently bricking the key. - No TTL, so the table grows without bound. - Storing the key without a scope, so two tenants can collide.