Test Idempotency Keys End to End

1. Create the key table with a primary key that makes double claims impossible.

CREATE TABLE idempotency_keys (
  tenant_id uuid NOT NULL,
  key text NOT NULL,
  request_hash text NOT NULL,
  state text NOT NULL CHECK (state IN ('processing', 'completed')),
  response_status int,
  response_body jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (tenant_id, key)
);

Expected output: Two concurrent inserts with the same tenant and key cannot both succeed.

2. Claim the key inside the same transaction as the side effect before doing the work.

await client.query('BEGIN');
const { rowCount } = await client.query(
  `INSERT INTO idempotency_keys (tenant_id, key, request_hash, state)
   VALUES ($1, $2, $3, 'processing')
   ON CONFLICT (tenant_id, key) DO NOTHING`,
  [tenantId, key, requestHash],
);
if (rowCount === 0) return replayStoredResponse(tenantId, key, requestHash);

Expected output: Only one request claims the key; the other reads the stored state instead of creating a charge.

3. Store the response in the same transaction and return it unchanged on replay.

const charge = await createCharge({ tenantId, amount });
await client.query(
  `UPDATE idempotency_keys
   SET state = 'completed', response_status = 201, response_body = $3
   WHERE tenant_id = $1 AND key = $2`,
  [tenantId, key, JSON.stringify(charge)],
);
await client.query('COMMIT');

Expected output: A replay with the same key and body returns 201 with the original charge id and creates no second charge.

4. Reject the same key with a different payload instead of returning the old response.

const second = await post('/api/payments', { key: 'k-1', body: { amount: 999 } });
assert.equal(second.status, 422);
assert.equal(second.body.error, 'idempotency_key_reuse');

Expected output: 422 with a stable error code, because returning the original charge for a different amount would be misleading.

5. Fire concurrent duplicates and assert exactly one side effect.

const responses = await Promise.all(
  Array.from({ length: 5 }, () => post('/api/payments', { key: 'k-2', body: charge })),
);
assert.equal(await countCharges('k-2'), 1);
const ids = new Set(responses.filter((r) => r.status === 201).map((r) => r.body.id));
assert.equal(ids.size, 1);

Expected output: One charge row exists, every successful response references it, and overlapping losers receive 409 while processing.

Problem

The same key creates two resources

Solution

The key is written after the side effect, leaving a crash window. Insert the key first in the same transaction, or use a unique constraint on the effect itself.

Problem

Concurrent duplicates both proceed

Solution

Two requests read no row and both insert. Use INSERT ... ON CONFLICT DO NOTHING and check rowCount, or lock the key row with SELECT FOR UPDATE.

Problem

The replayed response differs from the original

Solution

Persist the status code, body, and important headers. Recomputing the response from current state produces a different answer after other changes.

Problem

Keys collide between endpoints

Solution

Scope the unique key by tenant and endpoint, or namespace the key value with the route.