Idempotency Keys

The buyer's browser sent POST /orders twice, and the service had no way to know it was the same order. An idempotency key is a client-generated identifier for the intent, sent as a header, stored by the service together with the response it produced, so that the second request with the same key returns the first response without doing the work again. Topic 08 of Chapter 2 made the header part of the contract and settled who makes the key. This topic is the table behind it, the three outcomes of one insert, and the details that decide whether the mechanism holds on a bad night: the scope, the hash, the in-progress case, and what exactly is stored.

It is the fix for the double charge, and it is nothing new. Topic 34 of Chapter 6 closed the oversell with a unique constraint as the last line, so that a second hold on a seat loses the insert whatever the code did. The idempotency table is the same constraint on a different column: the second request with the same key loses the insert, and the difference from the seat is only that the loser is handed the winner's answer instead of a 409. A unique constraint with a stored response. That sentence is the design, and the rest is what happens in the 400 milliseconds between the insert and the stored response.

The Key Is the Intent

The key must be born where the retry is born. The buyer clicks "pay" once; that click is the intent, and the browser generates a UUID at that moment and attaches it to the request. When the request times out and the browser retries, it sends the same request with the same key, because the retry is the same intent. When the buyer sees an error, goes back and clicks "pay" again, that is a new click and a new key, and if it charges her twice she asked for it twice. A key generated when the request object is built, one layer below the click, is wrong in a way that is invisible in every test: the retry builds a new request, gets a new key, and the double charge returns with the mechanism apparently in place.

POST /orders HTTP/1.1
Idempotency-Key: 7f3a9c2e-5b1d-4e8a-9f60-2c4d8e1b7a05
Content-Type: application/json

{"hold_ids": [88211, 88212], "payment_method": "pm_4Yc…"}

--- 4 seconds pass; the connection drops; the browser retries ---

POST /orders HTTP/1.1
Idempotency-Key: 7f3a9c2e-5b1d-4e8a-9f60-2c4d8e1b7a05       # identical
Content-Type: application/json

{"hold_ids": [88211, 88212], "payment_method": "pm_4Yc…"}

HTTP/1.1 201 Created                                       # the first request's response, replayed
Location: /orders/9b1e4d3a-…

Two requests, byte for byte the same, 4 seconds apart, and the second one is answered with the first one's 201 and the first order's location. Payrail was called once. The orders table has one row. The buyer, who saw a spinner and then a confirmation, does not know there were two requests and does not need to. Compare the night in Chapter 1: the same two requests with no header, and the service treated the second as a new order because nothing said otherwise.

The Table

The idempotency_keys table has six columns: key, user_id, request_hash, response_status, response_body and created_at, with a unique constraint on (user_id, key). The user in the constraint is the scope. Two buyers who happen to generate the same UUID, or one attacker who guesses another buyer's key, must not collide, and scoping the key to the authenticated caller from Chapter 5 makes a collision between users impossible and a guessed key useless: another user's key is, to this user, a key nobody has seen.

The request hash is the second guard. It is a SHA-256 over the method, the path and the body, computed at the boundary in Chapter 3 from the bytes the client sent. A second request with the same key and the same hash is a replay, and gets the stored response. A second request with the same key and a different hash is a client bug: a retry loop that reused the key on a different order, or a key reused on a different endpoint. That is answered with 422 and the Problem Details type https://stagedoor.example/problems/idempotency-key-reused, never with the stored response, because the stored response is the answer to a different question. Including the path in the hash is what scopes the key to the endpoint without a column for it.

The Flow

The first thing the handler does, before it reads the holds or calls Payrail, is try to insert the key. The insert is the race, and the database decides it, exactly as the seat row's constraint decided the race for 14C. If the insert wins, this request is the first with this key, and it goes on to do the work and, as its last act, to store the response on the row. If the insert loses, the row exists, and the handler reads it to find out which of three situations it is in.

async def place_order(req, ctx):
    won = await conn.execute(
        "INSERT INTO idempotency_keys (key, user_id, request_hash, created_at) "
        "VALUES (%s, %s, %s, now()) ON CONFLICT (user_id, key) DO NOTHING RETURNING key",
        (req.idempotency_key, ctx.principal.id, req.hash),
    )
    if await won.fetchone() is None:                      # the insert lost: a row exists
        row = await fetch_key(conn, ctx.principal.id, req.idempotency_key)
        if row.request_hash != req.hash:
            raise KeyReused()                             # 422: same key, different request
        if row.response_status is None:
            raise InProgress(retry_after=1)                # 409: the first request is still running
        return Replay(row.response_status, row.response_body)

    response = await orders.place(req, ctx)               # holds, Payrail, the order row: 400 ms
    await conn.execute(
        "UPDATE idempotency_keys SET response_status = %s, response_body = %s "
        "WHERE user_id = %s AND key = %s",
        (response.status, response.body, ctx.principal.id, req.idempotency_key),
    )
    return response

The insert uses ON CONFLICT DO NOTHING with a RETURNING clause, so it returns a row when it inserted and nothing when it did not, and that one result decides everything that follows. When the insert won, the handler does the work and stores the status and body as the final step. When it lost, the handler reads the existing row and checks two things in order: whether the hash matches, which decides replay against 422, and whether a response is stored yet, which decides replay against 409. The three outcomes on a lost insert are a mismatched request, a request still in progress, and a completed request whose answer is returned unchanged. None of them touches the holds, Payrail or the orders table.

In Progress

The third row is the case that a naive design misses. The first request inserted its key and is 200 milliseconds into its Payrail call when the second request arrives on the other instance. The row exists. The response columns are null. The handler has three options: do the work anyway, which is the double charge; wait on the loop for the first request to finish, which parks a task for up to 3 seconds on an instance that may not be the one doing the work; or tell the client to come back in a second. Stagedoor does the third. The 409 carries Retry-After: 1 and the type https://stagedoor.example/problems/request-in-progress, and a well-behaved client retries once and gets the replay.

When the table write itself is the contention point, which happens at 3,000 requests a second only if the checkout path is hot enough to make the insert wait on the index, the alternative is a short lock in Redis: SET idem:{key} 1 NX EX 30, which succeeds for exactly one caller and expires in 30 seconds if that caller dies. The lock decides in-progress at Redis speed and the table still stores the response, so a lock that expires early cannot cause a second execution of a request that already finished; it can only let a second execution start after 30 seconds if the first is still running, which at a 3-second Payrail timeout it cannot be. Stagedoor uses the table alone, because the insert on (user_id, key) has never been the slow part of a checkout.

Store the Whole Response

The row stores the status, the body, and the headers a client acts on, of which Location is the one that matters for a 201. The replay must be indistinguishable from the original, and that includes the failures. A buyer whose card was declined got a 402 with a Problem Details body saying so; if her client retries that request with the same key, it gets the same 402, not a second attempt at the charge. The intent was one attempt, the answer was no, and the answer is now part of the record. Storing only "seen" and returning a bare 200 to any replay would turn a declined card into a confirmation with an empty body, which is a worse bug than the one the table exists to fix.

The one thing the table must not store is a 5xx. A 504 from a Payrail timeout in Topic 37 or a 503 from an open breaker in Topic 40 is not an answer to the intent; it is the service saying it does not know yet. The handler leaves response_status null on those paths, so the row reads as in progress, and the next request with the key gets a 409 and then, once reconciliation has settled the order, the real answer. What the key row records is the outcome of the intent, and a timeout has no outcome.

Expiry and Scope

Keys live 24 hours. That is the retry window a client plausibly needs: a mobile app that lost the network in a tunnel and retries on the next morning's commute is inside it, and a key from last week is not a retry of anything. The scheduled job of Chapter 8 deletes rows older than 24 hours once an hour, and without it the table grows by every order ever placed plus every retry, which at Stagedoor's volume makes it the largest table in the database inside a year. After expiry a reused key is a new intent, which is the correct reading of a key nobody has sent for a day.

The service is also a client, and its call to Payrail needs the same protection from the same fact. Stagedoor's retry of a charge after a timeout must not charge twice at Payrail, so the key it sends Payrail is not the buyer's key and not a fresh UUID per attempt; it is derived from the order, order-{public_id}, the same value on every attempt of every retry of every restart. Payrail keeps its own table, and a second charge request with that key returns Payrail's first answer. Chapter 10 builds the Payrail client; the key it sends is decided here, and it is the buyer's intent translated into Payrail's vocabulary.