# 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.

Original: https://fdetimes.net/en/guides/idempotency-key-post-duplicate-orders/

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

```sql
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:

```sql
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

```python
# 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:

```sql
-- 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:

```bash
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.

**Key point:** A retry is only safe when the server remembers what it answered before.

## 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?

**Try this week:**

- Pick a POST endpoint you have handed over, create the idempotency_keys table from the template and run the three curl checks in this guide
- Reread the customer's client-side retry code: check that they reuse the same key on every attempt and use exponential backoff with jitter
- Add a short section to the integration docs explaining the Idempotency-Key header and the 409 and 422 codes the customer may see

## Sources

- [Idempotent requests | Stripe API Reference](https://docs.stripe.com/api/idempotent_requests)

- [The Idempotency-Key HTTP Header Field (draft-ietf-httpapi-idempotency-key-header-08)](https://datatracker.ietf.org/doc/draft-ietf-httpapi-idempotency-key-header/)

- [Implementing Stripe-like Idempotency Keys in Postgres](https://brandur.org/idempotency-keys)

- [Designing robust and predictable APIs with idempotency](https://stripe.com/en-US/blog/idempotency)
