PostgreSQL Idempotency for REST APIs Under Retries

Retries are normal in backend systems. A mobile client loses connectivity, a reverse proxy reaches its timeout, or a worker restarts after sending a request but before reading the response. The client tries again, and the API has to decide whether that second request is new work or the same work arriving late.

The retry math get ugly when a POST both writes business data and triggers an external action. Without an explicit idempotency design, one customer can receive two subscriptions, two webhook deliveries, or two email verification records. This post describes a small PostgreSQL pattern I use for REST API endpoints that must survive duplicate requests.

Retries change the API contract

An idempotency key is a client-provided identifier for one intended operation. It is not the same thing as a request ID. A request ID helps trace an attempt; an idempotency key connects multiple attempts to the same logical command.

For example, a client can send Idempotency-Key: order-7f3... when creating an order. If the first request commits but the response is lost, the second request should return the original result. It should not create a second order just because the first response never made it back.

The key needs a scope. Usually that means the authenticated account, endpoint or operation type, and the key itself. A key from one customer must never match another customer. It is also worth recording a request fingerprint, such as a hash of the normalized body. Reusing one key with different input should be a clear client error, not an unpredictable merge.

This boundary is useful in email-heavy flows too. When testing isolating signup email flows, the same principle keeps a retried verification command from creating multiple test fixtures. A temp mailid can be a fixture value, but it should not become the identity of the operation.

Let PostgreSQL enforce the invariant

Application code can check for an existing key before inserting, but a check-then-insert sequence races under concurrency. Two requests can both observe an empty table and then both perform the work. The database should own the uniqueness rule.

One simple table looks like this:

CREATE TABLE api_idempotency_keys (
    account_id bigint NOT NULL,
    operation text NOT NULL,
    key text NOT NULL,
    request_hash text NOT NULL,
    status_code integer,
    response_body jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (account_id, operation, key)
);

The primary key is the important part. It means concurrent requests cannot both claim the same operation. The request hash protects against a subtle bug: a caller retries with the same key but accidentally changes the payload. That should return 409 Conflict or another documented client error.

The table is small, but it carry a lot of safety. Give it a retention policy, though. Keeping every key forever makes storage and operational reasoning harder; deleting a key too soon can allow a very late retry to run again.

A Node.js implementation pattern

For a business operation that can stay inside one database transaction, insert the idempotency row and the business row together. The second request can then read the stored response after it finds the existing key.

await client.query('BEGIN');

const claim = await client.query(
  `INSERT INTO api_idempotency_keys
     (account_id, operation, key, request_hash)
   VALUES ($1, $2, $3, $4)
   ON CONFLICT (account_id, operation, key) DO NOTHING
   RETURNING key`,
  [accountId, 'create-order', idempotencyKey, requestHash]
);

if (claim.rowCount === 0) {
  const previous = await client.query(
    `SELECT request_hash, status_code, response_body
       FROM api_idempotency_keys
      WHERE account_id = $1 AND operation = $2 AND key = $3
      FOR UPDATE`,
    [accountId, 'create-order', idempotencyKey]
  );

  if (previous.rows[0].request_hash !== requestHash) {
    throw new Error('idempotency key reused with different input');
  }

  await client.query('COMMIT');
  return previous.rows[0];
}

// Insert the order, construct the response, and update the claim here.
await client.query('COMMIT');

Production code should return a structured domain error, release the connection in finally, and handle an in-progress claim according to the API contract. For long external calls, do not hold a database transaction open while waiting on another service. Store a durable command, commit it, and let a worker perform the side effect with its own deduplication rule.

This also make incident review less guessy: the idempotency row shows whether the request was claimed, completed, or needs recovery. Add metrics for claim conflicts, hash mismatches, replayed responses, and expired keys. A high replay count can reveal a proxy timeout long before customers file a duplicate-charge report.

What to store and what to return

Store the status code and a deterministic response body when the response is safe to replay. If the response includes a short-lived token or a time-sensitive link, store the business resource ID instead and reconstruct a safe response. Never store secrets just because replaying the entire HTTP body is convenient.

For systems that send mail, keep privacy boundaries explicit. A temporary disposable mail address may be fine for a controlled test, while a production account recovery address needs stricter handling. The privacy boundaries around test mail are part of the system design, not just a QA preference. Also, do not quietly accept values such as temp gamil com as real addresses without deciding how normalization and validation should work.

Questions engineers usually ask

Should every endpoint accept an idempotency key?

No. It is most valuable for commands that create or trigger side effects, especially POST requests. A naturally idempotent PUT may already have a stable resource identifier. Still, document retry behavior for every endpoint so clients are not forced to guess.

What happens when the first request is still running?

Choose deliberately: wait briefly, return a conflict such as 409, or return an accepted command state such as 202. The second request should not start a parallel side effect just because the first request is slow. The second request arrive before the first one finishes is a normal case, not an edge case.

Operational checklist

• Scope keys by account and operation.
• Enforce uniqueness with a PostgreSQL constraint.
• Hash normalized input and reject mismatched reuses.
• Define behavior for an in-progress operation.
• Store only what is safe to replay.
• Retain keys long enough for realistic late retries.
• Measure conflicts, replays, mismatches, and expiry cleanup.
• Test concurrency, connection loss, and worker restarts.

An idempotency key is a small API feature with a large reliability payoff. The endpoint should be boring on purpose: repeated delivery either returns the known result or exposes a clear state that a client can handle. That makes REST API behavior easier to reason about, PostgreSQL constraints do the hard concurrency work, and operators get evidence when retry behavior starts changing.