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.
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
- Timeout before commit: transaction rolls back; a retry can safely acquire the key.
- Timeout after commit: retry returns the stored order ID and status, not a second order.
- Two replicas race: unique index chooses one owner; the other waits or returns a retryable conflict.
- Same key, changed payload: reject without changing the first result.
- Outbox publish succeeds but acknowledgement fails: duplicate delivery is possible; consumer deduplication is required.
- Key expires: define retention longer than the expected retry window and use durable business uniqueness where duplicates would be catastrophic.
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.
Official references
- PostgreSQL INSERT and ON CONFLICT
- PostgreSQL transaction isolation
- Stripe idempotent request semantics
- Node.js randomUUID