Zero Rows Returned

A payouts API reserves the idempotency key before doing any work, using a unique constraint so that concurrent duplicates cannot both proceed.

-- idempotency_keys(key TEXT PRIMARY KEY, request_hash TEXT,
--                  status TEXT, response_body JSONB)
INSERT INTO idempotency_keys (key, request_hash, status)
VALUES ($1, $2, 'in_progress')
ON CONFLICT (key) DO NOTHING
RETURNING key;

const rows = await db.query(RESERVE_KEY, [key, hash]);
if (rows.length === 0) {
  throw new ServerError('could not reserve idempotency key');   // 500
}
await createPayout(key, amount);

No payout is ever duplicated, but a handful of clients get a 500 and retry forever, never learning what happened to their payout. What is the defect and its fix?

request_hash is written but never compared, so the real bug is a client reusing one key with two different bodies.

Zero returned rows is the conflict path, not a failure: RETURNING only yields rows that were actually inserted or updated. Read the existing row and either replay its stored response or answer 409 while it is still in_progress.

DO NOTHING is the wrong action. ON CONFLICT (key) DO UPDATE SET status = 'in_progress' always returns a row, so the null case disappears.

The insert and createPayout are not in one transaction. Wrapping the handler in BEGIN/COMMIT makes the duplicate block on the row lock until the first request finishes, and it then reads the committed result.