When a client times out after sending POST /orders, it cannot tell whether the server committed the order or lost the request. Retrying with a fresh request can create two orders. In a Kubernetes deployment, the retry may even land on a different replica. A process-local lock is therefore not a correctness mechanism. The database needs to recognize one intended operation across every attempt.

1. Define the contract before the middleware

Require an Idempotency-Key for operations that create costly or irreversible effects. The client generates one unpredictable key per logical operation and reuses it for retries with identical inputs. Scope the key to a tenant and operation; a global key namespace risks accidental cross-customer collisions. Validate size and format before persistence. A key is not authentication and must never grant access to another tenant's result.

Three outcomes, not oneFirst request: execute and persist. Completed retry: replay the recorded response. Concurrent retry while work is pending: return a documented conflict or retry-later response, rather than performing the action again.

2. Let PostgreSQL arbitrate concurrent requests

Use a unique constraint on the logical identity. A preliminary SELECT followed by an INSERT races under concurrency; two replicas can both observe no row. PostgreSQL's unique index and ON CONFLICT make ownership of the key an atomic database decision.

CREATE TABLE api_idempotency (
  tenant_id uuid NOT NULL,
  operation text NOT NULL,
  idem_key text NOT NULL,
  request_hash text NOT NULL,
  status text NOT NULL CHECK (status IN ('processing','completed')),
  http_status integer,
  response_json jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (tenant_id, operation, idem_key)
);

CREATE TABLE orders (
  id uuid PRIMARY KEY,
  tenant_id uuid NOT NULL,
  sku text NOT NULL,
  quantity integer NOT NULL CHECK (quantity > 0)
);

Store a canonical fingerprint of the relevant request fields, such as tenant, SKU, quantity, and currency. Do not hash raw JSON strings without a canonicalization rule: field ordering and numeric representations can differ. A reused key with a different fingerprint is a client error, not a replay.

3. Commit the business effect and replay record together

For a database-only order creation, keep the idempotency row, order row, and final response in one PostgreSQL transaction. The following is a compact pattern using pg; the surrounding handler must validate authentication, input and the key before calling it.

import { randomUUID } from 'node:crypto';

async function createOrder(pool, input) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const claim = await client.query(`
      INSERT INTO api_idempotency
        (tenant_id, operation, idem_key, request_hash, status)
      VALUES ($1, 'create-order', $2, $3, 'processing')
      ON CONFLICT DO NOTHING
      RETURNING idem_key
    `, [input.tenantId, input.key, input.hash]);

    if (!claim.rowCount) {
      const existing = await client.query(`
        SELECT request_hash, status, http_status, response_json
        FROM api_idempotency
        WHERE tenant_id=$1 AND operation='create-order' AND idem_key=$2
      `, [input.tenantId, input.key]);
      await client.query('COMMIT');
      const row = existing.rows[0];
      if (!row) return { status: 409, body: { error: 'retry_later' } };
      if (row.request_hash !== input.hash)
        return { status: 422, body: { error: 'key_reused' } };
      if (row.status !== 'completed')
        return { status: 409, body: { error: 'retry_later' } };
      return { status: row.http_status, body: row.response_json };
    }

    const orderId = randomUUID();
    await client.query(`
      INSERT INTO orders(id, tenant_id, sku, quantity)
      VALUES ($1,$2,$3,$4)
    `, [orderId, input.tenantId, input.sku, input.quantity]);
    const body = { id: orderId, status: 'created' };
    await client.query(`
      UPDATE api_idempotency
      SET status='completed', http_status=201, response_json=$3::jsonb
      WHERE tenant_id=$1 AND operation='create-order' AND idem_key=$2
    `, [input.tenantId, input.key, JSON.stringify(body)]);
    await client.query('COMMIT');
    return { status: 201, body };
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
}

Important: this example deliberately keeps the claim uncommitted until the order and response commit. A competing INSERT ... ON CONFLICT DO NOTHING may wait for the first transaction. After the first commits, the competing transaction can read the completed record. If the first rolls back, another request may become owner. Apply a bounded statement timeout and return a retryable response when waiting exceeds your API budget.

4. External effects need an outbox, not a longer transaction

Never hold a PostgreSQL transaction open while calling a payment gateway, Kafka producer, or email API. A process can crash after the external call succeeds but before your local transaction records it. Instead, write an outbox_events row in the same transaction as the order. A worker publishes the event after commit and retries on failure. Consumers still need their own deduplication keyed by event or business ID, because outbox delivery is generally at least once.

CREATE TABLE outbox_events (
  event_id uuid PRIMARY KEY,
  aggregate_id uuid NOT NULL,
  event_type text NOT NULL,
  payload jsonb NOT NULL,
  published_at timestamptz
);
-- Insert the outbox row in the SAME transaction as the order.
-- Publish asynchronously; make the consumer idempotent too.

When integrating a provider that supports idempotency keys, pass a stable provider-side key derived from the business operation. Confirm the provider's actual retention and replay rules. A provider's idempotency guarantee does not automatically cover your own database or downstream consumers.

5. Handle failure windows explicitly

6. Test the behavior you claim

Run 50 concurrent identical requests across multiple replicas and assert one order row, one logical outbox event and the same response identity. Repeat with mismatched payloads. Kill the process immediately before commit and immediately after commit. Inject database timeouts, delayed transactions and duplicate event delivery. Measure idempotency conflicts, replay rate, wait duration, key cardinality and outbox backlog. Never log raw keys or sensitive request bodies by default.

Production ruleIdempotency is a durable agreement about an operation, not a cache of HTTP responses. Define the business identity, persist it atomically, and test every crash boundary.

Official references

Related engineering notes