Idempotency Keys for Safe API Writes at the Database Level

1. Why network retries can create duplicate writes

A client sending a write request to an API cannot, on a timeout or connection drop, distinguish whether the request never reached the server, whether it reached and was successfully processed but the response was lost, or whether processing failed midway. From the client's perspective, an automatic retry is the only sensible response to a timeout, since a permanently lost request would be unacceptable for many business processes.

Without extra safeguards, that exact retry, in the second of the three cases mentioned, triggers a second, fully independent processing of the same business operation. For a payment that means a duplicate charge, for an order a duplicate shipment, for a notification a duplicate message to the customer. These cases are not rare in practice, they are a direct, statistically predictable consequence of any system with network communication and retry logic.

2. The basic principle: a unique key per logical operation

An idempotency key is a client-generated, unique value, typically a UUID, marking a single logical operation regardless of how many times the associated HTTP request is actually transmitted. The client generates this key once per business action, for example once per click on the order button, and sends it identically with every retry attempt.

The server checks on every incoming request first whether this idempotency key has already been processed. If so, the original, already-generated response is returned again without executing the business operation a second time. If the key is new, the operation runs normally and the result is stored under that key.

3. Practical table design: key, status, and response snapshot

The central safeguard is a UNIQUE constraint on the idempotency key column, combined with the customer or tenant context, so the same client-generated key does not accidentally collide across different tenants. Alongside the key itself, the table stores the processing status, so retries arriving in parallel can recognize whether processing is already running, already completed, or has failed.

In addition, a snapshot of the original response is stored, typically as a JSON column with status code and response body. Only that way can a repeated request with the same key be returned exactly the response the client should have received on the first, lost attempt.

CREATE TABLE idempotency_key (
    idempotency_key UUID NOT NULL,
    tenant_id       BIGINT NOT NULL,
    status          TEXT NOT NULL CHECK (status IN ('processing', 'completed', 'failed')),
    request_hash    TEXT NOT NULL,
    response_status INT,
    response_body   JSONB,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    expires_at      TIMESTAMPTZ NOT NULL,
    PRIMARY KEY (idempotency_key, tenant_id)
);

4. Race conditions on identical requests arriving in parallel

Network retries do not always arrive cleanly one after another. On a very long timeout, a client may start a second attempt while the first request is still being processed, so both requests reach the server almost simultaneously. Without extra safeguards, both requests could then check in parallel that the key does not yet exist and both start business-level processing.

The UNIQUE constraint solves exactly this problem structurally: the first request attempting to insert the row with the idempotency key wins the insert, the second, concurrently arriving request receives a constraint violation error from the database and thereby reliably knows that processing is already running or completed, without any explicit lock or application code that would have to resolve race conditions itself.

-- Attempt the insert; a constraint violation means
-- an entry for this idempotency key already exists
INSERT INTO idempotency_key
    (idempotency_key, tenant_id, status, request_hash, expires_at)
VALUES ($1, $2, 'processing', $3, now() + interval '24 hours')
ON CONFLICT (idempotency_key, tenant_id) DO NOTHING
RETURNING idempotency_key;

5. Why a request hash guards against key reuse

An idempotency key only guarantees that the same logical operation is not executed twice, not that a client uses the key correctly. If the same key were accidentally or maliciously reused for a request with different content, the API would incorrectly return the old response matching the original request, even though the new request should actually be processed.

A stored hash over the relevant request parameters, such as amount and recipient for a payment, allows checking on every request with a known key whether the content is actually identical. If the hash differs, the API can return an explicit conflict error instead of silently delivering a wrong, stale response.

6. Handling failed processing and stuck open states

If business processing fails after the idempotency key insert succeeded, for example because a downstream payment provider returns an error, the status must be consistently updated to a failed state. A subsequent retry with the same key can then deliberately decide whether a new attempt is allowed or whether the failed state should be returned permanently.

If an entry gets permanently stuck in a processing state, for example because the processing worker crashed without updating the status, the system needs timeout logic that marks an entry recognized as stuck as failed after a reasonable period, allowing a new attempt again instead of permanently blocking the client.

7. Cleanup strategy: why idempotency entries must not live forever

Idempotency keys only need to remain stored as long as realistic client retry attempts are expected, usually a period of hours to a few days, not indefinitely. An unboundedly growing idempotency table not only increases storage usage but also the size of the associated UNIQUE index, gradually slowing down every lookup on incoming requests.

The expiry column in the example table enables regular cleanup through a batch job that deletes expired entries. An additional index on that column keeps this delete operation efficient even on large tables, and some database systems even allow automating this expiry behavior through a native TTL feature instead of maintaining a custom batch job.

-- Regular cleanup job for expired entries
DELETE FROM idempotency_key
WHERE expires_at < now()
LIMIT 10000;

CREATE INDEX idx_idempotency_expiry ON idempotency_key (expires_at);

8. Limits of the pattern: what an idempotency key does not solve

An idempotency key only protects against duplicate processing of the same request, not against business-distinct but content-equivalent operations, such as two separate orders with identical content that the customer deliberately triggers twice. Side effects outside the own transaction, such as calling an external payment provider, must be idempotent themselves or additionally safeguarded, because the own idempotency key does not automatically protect that external call.

For distributed systems with multiple involved services, a single idempotency key on a single database is therefore often not enough. The key then must be consistently passed through the entire call chain so every involved service can enforce the guarantee independently.

9. Practical recommendation: where the effort genuinely pays off

Idempotency keys pay off especially for write operations with financial or otherwise serious consequences: payments, order completions, shipment triggers, and similar operations where accidental duplicate processing is expensive or visible to customers. For purely read operations or for writes that are naturally already idempotent, such as setting a fixed status value, the extra effort is usually not justified.

For new APIs it is worth introducing the idempotency key as an explicit, documented part of the interface, for example as an HTTP header, rather than deriving it implicitly from other fields. That makes the guarantee visible to client developers and prevents retry safety from depending on incidental implementation details.

10. Summary

11. FAQ: Idempotency Keys in Practice