Recipe / Reliability

Database idempotency keys

Network retries are inevitable. If a client retries a charge, an order, or a webhook delivery, the server must produce the same outcome the second time as the first. The idempotency key pattern stores the result of a request under a client-supplied identifier, so duplicate calls converge to a single side effect and a single response.

1.Design the storage row

Keys live in their own table with a primary-key constraint on the key itself. Store a hash of the request body so you can reject a replayed key with a different payload, and cache the response so the second request returns the original bytes without re-running the transaction.

2.Claim the key atomically

Use a single INSERT ... ON CONFLICT DO NOTHING to claim the key. If the insert returns a row, you are the first caller and must execute the work inside the same transaction. If it returns nothing, another caller has the key — wait briefly and read the cached response.

3.Expire and reap

Idempotency is a short-window guarantee, usually 24 hours. Set anexpires_atcolumn on insert and run a nightly job that deletes expired rows. This keeps the table small enough that the primary-key lookup stays in the buffer cache.

schema.sql

-- Postgres: idempotency keys table
CREATE TABLE idempotency_keys (
  key           TEXT PRIMARY KEY,
  request_hash  TEXT NOT NULL,
  response_body JSONB,
  status_code   INT,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  expires_at    TIMESTAMPTZ NOT NULL
);

CREATE INDEX idx_idem_expires ON idempotency_keys (expires_at);

-- Atomic claim: INSERT or return existing row
INSERT INTO idempotency_keys (key, request_hash, expires_at)
VALUES ($1, $2, now() + interval '24 hours')
ON CONFLICT (key) DO NOTHING
RETURNING key;