Make a Write Endpoint Safely Retryable

In one line

The client sends one unique Idempotency-Key per logical operation. The server stores key → response the first time, and on any retry of that key returns the stored response without re-running the write.

You're here when

Signs this is your situation

A POST or PATCH can be retried, by a client with a timeout, a job queue, a webhook sender
A duplicate would mean two orders, two charges, two emails
You've seen "the payment went through but the client got a 504 and tried again"
A partner SDK already sends an Idempotency-Key header and you're ignoring it

The play

1Require an Idempotency-Key header on the endpoint (a client-generated UUID). Reject a mutating request without one: 400.
2Scope the key by account and endpoint, so one client's key can't collide with another's or with a different operation.
3Claim the key by inserting its row with a UNIQUE (account_id, endpoint, key) constraint and ON CONFLICT DO NOTHING.
4Insert succeeded → first time: do the work, save the status + body against the key, return it.
5Insert conflicted → the key exists: if it has a stored response, replay it verbatim (mark the replay); if it doesn't yet, a concurrent first request is still running, so return 409.
6Sweep keys older than a fixed window (24–72h) so the table doesn't grow forever and stale keys can't be replayed.

@itsnas handler.ts

codehandler.ts

async function handle(req: Request) {
  const key = req.header('Idempotency-Key')
  if (!key) return json(400, { error: 'Idempotency-Key required' })
 
  return tx(async db => {
    // claim: the UNIQUE constraint makes this insert the lock
    const [claimed] = await db`
      insert into idempotency_keys (account_id, endpoint, key)
      values (${req.accountId}, ${req.route}, ${key})
      on conflict (account_id, endpoint, key) do nothing
      returning id
    `
 
    if (!claimed) {
      const [prior] = await db`
        select status, response from idempotency_keys
        where account_id = ${req.accountId}
          and endpoint = ${req.route}
          and key = ${key}
      `
 
      return prior.status === 'completed'
        ? replay(prior.response)
        : json(409, { error: 'request already in progress' })
    }
 
    const result = await doTheWrite(req.body) // create order, charge card…
    const response = json(201, result)
 
    await db`
      update idempotency_keys
      set status = 'completed', response = ${response.serialised}
      where id = ${claimed.id}
    `
 
    return response
  })
}

main

Nas (@itsnas)

create table idempotency_keys (
  id          uuid primary key default gen_random_uuid(),
  account_id  uuid not null,
  endpoint    text not null,
  key         text not null,
  status      text not null default 'in_flight', -- in_flight | completed
  response    jsonb,                             -- status + body, set on completion
  created_at  timestamptz not null default now(),
 
  unique (account_id, endpoint, key)
);
 
-- run on a schedule
delete from idempotency_keys
where created_at < now() - interval '72 hours';

main

Nas (@itsnas)

Which path

The operation is a single DB write

One transaction: claim the key, do the write, mark it completed, commit.
A crash rolls back both halves, and the client's retry re-claims cleanly.

The operation calls an external service

You can't hold a DB transaction across a network call. Claim and commit the key first, then make the call, then record its outcome against the key.
Pass your own idempotency key through to the provider, so a retry after a crash can ask them what happened instead of guessing.

Gotchas

1
Keying on a hash of the request body
Two genuinely different requests that serialise the same get merged; and one operation retried with a re-serialised body (map order, an added default) looks brand new. The client owns the key.
2
Storing the key but not the response
You dedupe the write, but the retry still gets back a fresh 500 or 404. The point is to replay the original outcome (success or failure), not just to skip the work.
3
Letting keys live forever
The table becomes your largest, and a replay of a six-month-old key does something surprising. Fixed TTL, swept on a schedule.
4
Ignoring the in-flight window
Two retries land milliseconds apart; without the in_flight state both do the work. The unique constraint is the lock: the second claim must fail and return 409, not proceed.
5
Idempotency-keying GET requests
Reads are already idempotent. Keys are for POST / PATCH / DELETE that change state.

Confirm you're clear

1The same request fired twice with the same key produces one resource / one charge and two identical responses.
2The second response carries the replay marker you chose (a header or a body field).
3Two parallel requests with one key return one 201 and one 409, never two 201.
4Killing the endpoint mid-write and retrying leaves the system consistent.

Pairs with

Retry with Backoff & Jitter