Stop client retries creating duplicate orders with Idempotency-Key
A Postgres table, a unique constraint and clear rules for 409 and 422 make every retry return the original result.
In brief
- The client generates a UUID v4 for each operation and sends that same key on every retry; the server stores the status code and body of the first run and replays them.
- Same key with a different payload returns 422; a key still being processed returns 409, following the IETF draft.
- The unique constraint sits on the pair (user_id, idempotency_key); handlers that call external systems should be split into atomic phases with recovery points.
Picture a customer’s ERP system calling POST /orders on the API you have just handed over. The request times out, the ERP calls again, and the warehouse receives two orders for a single click. There is nothing wrong with your code. It simply does not yet know how to recognise a retry.
Bugs like this usually surface only once real data is flowing, which is exactly when the FDE is sitting at the customer’s site.
Stripe’s API documentation is clear about what idempotency is for: when a connection fails, the client can repeat the request without fear of creating a second object or applying an update twice.
This guide builds that mechanism for any POST endpoint in about an afternoon.
You will end up with a Postgres table that stores keys, a wrapped handler, a job that clears old keys and three curl commands that prove the endpoint behaves correctly. You need a working POST endpoint, permission to create tables in Postgres, and curl. The handler code below is a simplified sketch in Python-like pseudocode. Adapt it to whatever framework you use.
The key is the client’s job; remembering is the server’s
Brandur Leach, author of a guide to building Stripe-style idempotency keys on Postgres, defines an idempotent endpoint as one that can be called any number of times while its side effects happen only once. To achieve that, the server needs a signal that two requests are in fact the same operation.
The client creates that signal. Stripe’s engineering blog describes the approach: the client generates a unique ID for exactly one operation, and sends that same ID again when it retries.
Stripe’s documentation recommends a UUID v4 or another random string with enough entropy, up to 255 characters long, and warns against putting sensitive data in the key. The IETF draft draft-ietf-httpapi-idempotency-key-header standardises the header name as Idempotency-Key, for methods that are not idempotent by nature, such as POST and PATCH.
According to Stripe, GET and DELETE are already idempotent by definition, so sending a key with them has no effect. You therefore only need to wrap POST endpoints, and PATCH if you have any.
Step 1: one table, one unique constraint per user
CREATE TABLE idempotency_keys (
user_id bigint NOT NULL,
idempotency_key varchar(255) NOT NULL,
request_hash text NOT NULL,
status text NOT NULL DEFAULT 'processing',
response_code int,
response_body jsonb,
created_at timestamptz NOT NULL DEFAULT now(),
UNIQUE (user_id, idempotency_key)
);
The most important detail is the last line. In Brandur’s design the key is not unique across the whole table but unique per pair (user_id, idempotency_key). Two customers who happen to generate the same string will not overwrite each other’s data, and one customer cannot guess a key to read another’s response.
The request_hash column holds a hash of the request body, used for the payload check below. Check: run \d idempotency_keys in psql and confirm there is a unique index on the two columns.
Step 2: reserve the key before doing anything else
When a request arrives, the first thing to do is try to insert the key into the table. That insert is also your lock:
INSERT INTO idempotency_keys (user_id, idempotency_key, request_hash)
VALUES ($1, $2, $3)
ON CONFLICT (user_id, idempotency_key) DO NOTHING
RETURNING status;
If the statement returns a row, this is the first time you have seen the key and you can carry on. If it returns nothing, the key already exists. You then read the existing row and decide according to the three cases in the table below.
| Situation when the key already exists | Response |
|---|---|
| Payload hash differs from the stored hash | 422: the same key must not be used for a different payload |
status = 'processing' |
409: the original request is still running |
status = 'done' |
Replay the stored response_code and response_body exactly |
The first two status codes come from the IETF draft. The draft states that a key must not be reused for a request with a different payload, and that the server should return 422 in that case.
If the retry arrives before the original request has finished, the server should return 409. Stripe also compares parameters when a key is reused and returns an error if they differ, to catch accidental misuse.
Step 3: store the first response, even when it is a 500
# Simplified sketch of the flow
def create_order(req):
key = req.headers.get("Idempotency-Key")
if key is None:
return handle(req) # policy choice: let it through or require a key
errors = validate(req.body)
if errors:
return 400, errors # validation error: do not store the key
h = sha256(canonical_json(req.body))
row = try_reserve(req.user_id, key, h)
if row is None:
old = load(req.user_id, key)
if old.request_hash != h: return 422, {...}
if old.status == "processing": return 409, {...}
return old.response_code, old.response_body
code, body = handle(req)
save_response(req.user_id, key, code, body) # status = 'done'
return code, body
According to Stripe’s documentation, the v1 API stores the status code and body of the first request made with a key, once the endpoint has started executing, and replays them for every retry, including when the result was a 500 error.
Requests that fail validation or collide with another request running concurrently are not stored, so the client is free to try again. That is why the code above validates before reserving the key.
Check: after a successful request, SELECT status, response_code FROM idempotency_keys should return done along with the code you just sent back.
Step 4: split the work into phases when you call out
The code above assumes handle runs neatly inside one transaction. In practice, an order endpoint often also calls a payment gateway or sends a webhook to another system. If the process dies midway, the key row stays stuck in processing for ever.
Brandur handles this with atomic phases: a group of local state changes run inside a transaction, sitting between changes of state in external systems. After each phase you write a recovery point to the key row, such as order_created or payment_charged. The next retry reads that recovery point and continues from where it stopped, rather than starting over.
For your first endpoint you do not need the full machinery. List every external call in the handler: each one is a phase boundary, and each boundary should be followed immediately by a recovery point.
Step 5: clear old keys and prove it with curl
Stripe may remove keys after 24 hours. The example below uses that window; if the customer retries over a longer period, raise the interval so it exceeds their longest retry window, then schedule it:
-- replace '24 hours' with a window longer than the customer's retry window
DELETE FROM idempotency_keys
WHERE created_at < now() - interval '24 hours';
Then run three scenarios. The first command sends a normal request:
curl -i -X POST https://api.example.com/orders \
-H "Idempotency-Key: 6f1d2c3a-8b4e-4f5a-9c7d-1e2f3a4b5c6d" \
-H "Content-Type: application/json" \
-d '{"sku":"A1","qty":2}'
Run exactly the same command again: you should get the same order id back, and the orders table should have no new row. Keep the key but change qty to 3: the result should be 422. Finally, use a new key and fire two commands at once by adding & after each. If the handler is slow enough, one of them will receive 409.
The most common mistakes
The most common mistake is putting the unique constraint on the key column alone and forgetting user_id. The second is storing the key without the payload hash, so a client that accidentally reuses a key for a different order silently receives the old order’s response. The third is storing validation errors too, so a customer who fixes the request still gets the same 400 back.
The client side is just as easy to get wrong. Generating a new key for each retry means there is no idempotency at all. Stripe’s blog recommends pairing keys with exponential backoff on retries, plus a little random jitter in each client’s wait time, so that clients do not all hit the server at the same moment.
What this skill looks like at the customer’s site
On site, the SQL is only half the job. The other half is sitting with the customer’s integration team, reading their retry code and agreeing who generates the key, where, and how it is stored so that a retry reuses the original key.
Once you put the three curl commands and the status-code table above on the table, the conversation moves from arguing over whose bug it is to agreeing a clear API contract.
If you want to move into FDE work, do this exercise on a personal project and then write on your CV exactly what you verified, for example: “added Idempotency-Key to an order-creation API, tested repeated calls, changed payloads and concurrent calls”.
When reading job ads, look for requirements around integrating APIs with customer systems. That is your cue to walk through this exercise in the interview.
The next time someone reports duplicate data, your first question should be: did the retry carry the original key?
Was this article useful?
Thanks for the feedback!