Design an Idempotent Create-Order API

Problem and Scope

Design the idempotency contract for POST /orders. A mobile client may time out on an unreliable network and retry automatically. A gateway may also replay a request after its connection breaks. Whether one logical operation arrives once or several times, the server may create only one order. After a request succeeds, an identical retry should replay the first result; the server must reject the same idempotency key when it represents different order parameters.

Assume the caller sends a random identifier for one logical operation in Idempotency-Key, and the server uses PostgreSQL. The order writes and a pending event can share one local database transaction. External payment and inventory systems cannot participate in that transaction. The API is multi-tenant, and its response can contain order data that requires authorization.

The scope covers request deduplication, concurrent races, transaction boundaries, result replay, retention, and downstream side effects. Cross-region database replication, the payment domain's own state machine, and message-broker selection are out of scope. This is a backend question because the main task is to turn API semantics into database constraints, failure recovery, and an observable request flow, rather than to design a broad multi-component system.

What Interviewers Evaluate

The first signal is whether the candidate separates retry identity from business identity. An idempotency key identifies one operation from the caller's perspective. It does not replace authentication, tenant isolation, or a domain-level order key. The server should scope it by at least (tenantid, endpoint, idempotencykey). A lookup by the bare key could let two tenants that happen to choose the same string read each other's result.

The second signal is whether concurrency correctness rests on a uniqueness constraint instead of application timing. A SELECT followed by an INSERT lets two requests observe no row and both act as the first request. A unique index with INSERT ... ON CONFLICT lets the database choose one owner. The comparison must also include a request fingerprint; otherwise, accidental reuse of an old key could return an old order as the result of a different order request.

The third signal is a precise commit boundary. The order, idempotency result, and outbox event must commit together. A failure before commit rolls all three back. If commit succeeds but the HTTP response is lost, a retry reads the completed record and replays it. Payment and inventory calls happen outside the transaction through an outbox consumer, which passes a derived idempotency identifier downstream.

A strong answer also defines retention, the response to a concurrent duplicate, which failures are stored, how a long-running operation is recovered, and how key conflicts and stuck work are monitored. “Use a Redis lock” leaves lock expiry, process crashes, result replay, and the race after database commit unanswered.

Questions to Clarify Before Answering

• What counts as a duplicate? Here, the same tenant, endpoint, and idempotency key identify one operation. If the domain already has a non-reusable checkout_id, the orders table should also have an independent uniqueness constraint on it.
• Can the caller choose the resource ID? If the caller owns a stable order ID, PUT /orders/{clientOrderId} is an alternative because HTTP defines PUT as idempotent. POST with an idempotency key fits a server-generated order ID.
• Is order creation entirely database work? If it fits in one short transaction, use the single-transaction design. A workflow that takes tens of seconds needs a visible IN_PROGRESS record, a lease, and a takeover token instead of a long-held database transaction.
• How exact must the replay be? This design stores the first HTTP status and a response body that is safe to replay. If a response contains a short-lived signature or live fields, store the resource ID and reconstruct the response under an explicit contract.
• How long are keys retained? Retention must cover the client's longest retry or offline replay window. If duplication must remain impossible after that window, add a permanent domain key; a longer cache TTL is not a substitute for a business constraint.
• Which failures should be remembered? Validation that fails before ownership is acquired is not stored. A deterministic business rejection inside the transaction can be stored and replayed. An infrastructure failure that rolls the transaction back leaves no completed result, so the client can retry.

30-Second Answer

“I would scope each idempotency key to the tenant and POST /orders, then fingerprint the normalized order parameters. A unique constraint on (tenantid, endpoint, idempotencykey) arbitrates concurrent requests. The first request writes the idempotency record, order, and outbox event in one transaction, then stores the status and response body before commit. A retry with the same fingerprint replays that result; a different fingerprint returns a conflict. A lost response after commit therefore cannot create another order. Payment and inventory run asynchronously from the outbox with derived idempotency keys. I would retain the request record for the maximum retry window and use a separate order-domain uniqueness constraint for permanent deduplication.”

Step-by-Step Deep Dive

Begin with the protocol. An authenticated caller generates a high-entropy key for one “create an order” intent and reuses it for every retry. A new order needs a new key. The key must not contain an email address, phone number, or other sensitive data. The server constrains its length and character set, but a random-looking key is never an authorization credential. POST does not have PUT's idempotent semantics by default; safe retries come from this application contract.

Build the request fingerprint from the server-validated business fields, not the raw JSON bytes. Field order, insignificant whitespace, and default values should not give the same request different fingerprints. Exclude trace IDs, timestamps, and other non-business fields. A practical input is a stable encoding of the normalized DTO, endpoint version, and every field that affects the order result. Hash that encoding. The digest detects key misuse; it is not a signature or an authentication mechanism.

This minimal schema expresses the required constraints; the SQL is illustrative:

CREATE TABLE idempotency_requests (
  tenant_id text NOT NULL,
  endpoint text NOT NULL,
  idempotency_key text NOT NULL,
  request_fingerprint text NOT NULL,
  response_status integer,
  response_body jsonb,
  resource_id uuid,
  created_at timestamptz NOT NULL,
  expires_at timestamptz NOT NULL,
  PRIMARY KEY (tenant_id, endpoint, idempotency_key)
);

The normal path for a short transaction is:

1. Outside the transaction, authenticate, validate the key format and request, and compute the fingerprint.
2. Start a transaction and attempt to claim the key with INSERT ... ON CONFLICT DO NOTHING RETURNING ....
3. The request that inserts the row is the owner. It creates the order, appends the outbox event, updates the idempotency row with the status, response body, and order ID, and commits once.
4. A request that did not insert reads the existing row. A different fingerprint returns 409 idempotencykeyreused. The same fingerprint returns the first stored status and body, optionally with an API-defined replay marker.
5. Send the HTTP response only after commit. If the connection breaks after commit, the next request follows step 4 instead of creating an order.

PostgreSQL's unique index makes concurrent inserts of the same key wait and then resolves them to one insert or one conflict. Bound that wait. If the first transaction commits quickly, the second request can read and replay the result. If the wait reaches its limit, return an explicit idempotencyinprogress outcome with retry guidance. Do not use an application-level SELECT before deciding to insert, and never overwrite the stored fingerprint or result on conflict.

The central invariant is that whenever the order row is visible, its idempotency result and outbox event are visible too; when the transaction rolls back, none of them is visible. The failure cases follow directly. A process crash before order creation rolls back, so a retry can compete again. A lost response after the order commits is replayed. The same key with different parameters never executes. Of two identical requests arriving together, only one can pass the uniqueness constraint and finish the writes. A deterministic domain rejection such as an expired coupon can be stored as a stable error response inside the transaction. An uncommitted failure such as a lost database connection is not stored.

If order creation cannot stay within a short transaction, use a two-phase state machine. A first short transaction commits INPROGRESS, leaseexpiresat, and a monotonically increasing attempttoken. The API returns 202, or another request with the key reads a status endpoint. After lease expiry, a new worker uses compare-and-swap to claim a larger token. Every completion update verifies that token, which prevents an old worker from overwriting a newer result after it resumes. The order still needs a uniqueness constraint on the business operation ID, and side effects still use the outbox or downstream idempotency keys. A status row with a TTL but no takeover token allows old and new workers to run together; it merely delays the duplicate race.

External payment, inventory, and notification calls do not belong inside the local database transaction. The owner writes an OrderCreated outbox row with the order, and a consumer delivers it after commit. The consumer deduplicates by event ID. Calls to a payment or inventory API use a stable key derived from the order ID and operation type. At-least-once message delivery then does not become at-least-once charging. If a downstream service has no idempotent interface, use a local state machine, reconciliation query, or manual compensation; an outbox alone does not remove external duplicates.

Retention follows the retry contract. Stripe's public implementation permits key removal after a retention period. That demonstrates that keys can have a lifecycle, but its duration is not universal. Set expiresat to cover the longest mobile offline queue, gateway retry, and manual replay window. Reusing an old key after cleanup is a new operation. If one checkoutid must produce at most one order forever, place a permanent unique constraint on orders so that it still blocks a duplicate after the idempotency record is gone.

Redis may cache completed results, but it should not be the sole correctness boundary. After SET NX succeeds, a process can crash either before or after the order commit, and the lock TTL can expire before a slow request finishes. The cache also cannot atomically commit the order and outbox. Let the database uniqueness constraint decide “created at most once.” Use a cache only to reduce reads for hot replays, with the database record as the source of truth.

Testing requires more than two sequential requests. Cover at least these cases: 50 concurrent requests with one key produce one order and one outbox event; the same key with different parameters returns a conflict; a forced disconnect after commit but before the response still replays the original order; the same key can retry after a transaction rollback; behavior after key expiry matches the contract; two tenants using the same bare key remain isolated; and duplicate delivery to the consumer does not duplicate a charge. Monitor new claims, replays, parameter conflicts, concurrent wait timeouts, the oldest unfinished operation, transaction rollbacks, outbox lag, and expired-key cleanup. Logs must not include sensitive request bodies associated with a key.

Strong Sample Answer

“I would define one idempotency key as one create intent for one tenant on POST /orders. The client generates it on the first call and keeps it for timeout retries. The server fingerprints the validated order fields together with the API version. The same key and fingerprint can replay; the same key with a different fingerprint is a conflict.

In PostgreSQL, I would make (tenantid, endpoint, idempotencykey) the primary key. After request validation, each call competes for ownership with INSERT ... ON CONFLICT DO NOTHING. The winner creates the order, writes the outbox row, and stores the first status and response body in one short transaction before committing. The loser cannot create another order. It waits for the first transaction, reads the record, and returns the original result for a matching fingerprint. If the internal wait limit is reached, it tells the caller that the operation is still in progress. A crash after commit but before the HTTP response is therefore recovered as the same order on retry.

I would not call payment or inventory synchronously inside that transaction. An outbox consumer delivers those actions and passes a key derived from the order ID to each downstream operation. The idempotency record exists only for the maximum retry window, so if a checkout session must never create a second order, checkout_id also gets a permanent unique constraint on the order.

I would finish with concurrency, disconnect, and duplicate-message fault injection. I would assert the counts of orders, idempotency results, and outbox events rather than merely checking for HTTP 200. In production, I would watch replay rate, same-key parameter conflicts, concurrent wait timeouts, transaction rollbacks, and outbox lag.”

Common Mistakes

• Check for a row, then insert the order → two requests can both observe no row and both write → let a database uniqueness constraint and atomic conflict handling choose the owner.
• Look up only the idempotency key → tenants can collide or even receive another tenant's response → scope by the authenticated tenant and endpoint, and authorize again on replay.
• Return the old result without comparing parameters → accidental key reuse makes an old order look like a new one → store a normalized request fingerprint and reject a mismatch.
• Commit the order, then write the idempotency result → a crash between the writes leaves an order with no deduplication record → commit the order, result, and outbox in one database transaction.
• Call the payment service inside the database transaction → network timeouts extend lock duration, and a successful payment cannot be atomically rolled back locally → write an outbox entry in the transaction and use downstream idempotency outside it.
• Use only a Redis lock with a TTL → after a crash or expiry, it cannot reveal whether the order committed or replay the result → use the database constraint for correctness and the cache only for acceleration.
• Cache every error → a corrected request can still receive an old validation error, while a transient failure becomes impossible to retry → do not store pre-ownership validation failures; distinguish stable domain results from uncommitted failures.
• Promise permanent deduplication after deleting request records → an expired key is treated as a new operation → publish the retention window and use a domain unique key for the permanent invariant.

Follow-up Questions and Answers

Follow-up 1: What should the second request receive while the first has not committed?

With the short-transaction design, the unique-index conflict makes the second insert wait. Bound both the database wait and the endpoint execution below the client timeout. If the first transaction commits in time, the second request reads and replays it. At the limit, return a recognizable idempotencyinprogress outcome and retry timing instead of creating an order. A long task uses a committed IN_PROGRESS state and a status endpoint so the HTTP connection does not remain occupied.

Follow-up 2: What happens if the service crashes after commit but before writing the HTTP response?

That is why the result is stored. The order, outbox event, and response snapshot have committed together. The retry conflicts on the unique key, verifies the fingerprint, and returns the stored status and body. A mere “processed” Boolean would not tell the server which order to return, and the caller could not distinguish success from an unknown outcome.

Follow-up 3: What if the same idempotency key carries a different amount?

Return 409 idempotencykeyreused, record a conflict metric, and do not execute order creation. Never overwrite the old record with the new parameters or silently return the old order. The fingerprint must include amount, currency, items, shipping-reference fields, and every other field that changes the business result, plus the API or canonicalization version.

Follow-up 4: A long-running worker pauses, another worker takes over its expired lease, and the old worker resumes. What prevents stale writes?

Increment attempt_token on every takeover. Order-state changes, completion writes, and controllable downstream tasks carry that token and update only when it matches the current value. The old worker's write fails after it resumes, so it reads the new state and exits. If a downstream system cannot verify the token, it still needs an idempotent API keyed by a stable business operation ID or a reconciliation and compensation process. The lease alone cannot retract an external side effect already sent.

Follow-up 5: Why not use only a unique constraint on orders.checkout_id?

That constraint prevents a second order and is an excellent permanent backstop. It does not define whether different parameters under one idempotency key conflict, what status the first request returned, how to respond while work is concurrent, or what to do for callers without a checkout_id. The idempotency record provides a request-level protocol and result replay; the business unique key protects the domain invariant. They operate at different layers.