The payment went through twice

A customer was charged twice for one order. Your logs show one checkout. The payment provider shows two authorisations a second apart, identical amounts, identical card. The load balancer shows one request, one gateway timeout, then a second request from the same session.

Nobody wrote a retry. The browser did, or the mobile client, or the HTTP library's default. Duplicates are not an edge case in a network; they are the normal consequence of a timeout.

Predict

You want one charge per checkout, whatever the network does. Where does the uniqueness have to be decided?

Choose one answer

In the client. Disable the button on submit and the second request never happens.In the payment provider. They can see both authorisations and they are the ones taking the money.In a uniqueness constraint in your own database, keyed by something the client generated before its first attempt.In the service, by checking for a recent identical charge before creating a new one.

Lock in answer

The three parts, and the one everybody gets wrong

The key is generated before the first attempt, and reused across retries. This is the part that fails in practice. A key generated inside the retry loop, or on each render, or from a request hash, is a new key per attempt and buys nothing. Generate it when the user's intent is formed — when the checkout page loads, or when the job is enqueued — and carry it through every attempt including the ones a library makes without telling you.

The key is stored where the effect is stored, in the same transaction. If the charge lands in Postgres and the key lands in Redis, there is a window where one exists and the other does not, and that window is exactly when the duplicate arrives. Same database, same transaction, one commit.

The uniqueness is a constraint, not a lookup. SELECT then INSERT is a race. INSERT ... ON CONFLICT DO NOTHING and then reading back the row, or catching the unique-violation error, is not.

CREATE TABLE idempotency_keys (
  key            text PRIMARY KEY,
  request_hash   text NOT NULL,
  state          text NOT NULL,          -- in_progress | succeeded | failed
  response_code  int,
  response_body  jsonb,
  created_at     timestamptz NOT NULL DEFAULT now()
);

The server's first act is to insert the key with state in_progress. If the insert succeeds, this is the first attempt and it does the work. If the insert conflicts, this is a repeat: return the stored response if there is one, and if the state is still in_progress, return a 409 rather than waiting, because the honest answer is that the first attempt has not finished and the client should ask again.

request_hash is the part that catches the real bugs. If the same key arrives with a different body — a different amount, a different account — the client has reused a key for a different intent, and the right response is a 422 rather than silently returning the first result. Several payment providers do exactly this, and it has caught more client bugs than it has caused.

What it costs

One extra write per mutating request, in the same transaction as the work. On a payment endpoint this is free in relative terms. On a high-volume write path it is a second index to maintain and a hot one, because the keys are random and the index pages are therefore scattered.

Retention. The table grows by one row per request forever unless something removes them. The clean way is range-partitioning by created_at and dropping old partitions, which makes the delete a metadata operation instead of a long-running DELETE that creates its own problems. Pick the window from how long a client might reasonably retry — hours, not weeks — and write down that a key outside the window is treated as new, because that is a real correctness boundary rather than a cleanup detail.

A decision about scope. Keys must be unique per account, not globally, or one tenant can block another by guessing. Make the primary key the pair.

The part you cannot buy. Idempotency makes a repeat safe; it does not make a client's unknown outcome known. A client that times out still has to choose between retrying with the same key, which is now safe, and showing the user an error for a charge that may have succeeded. The second is the bug that outlives the first.

Check

Your worker consumes from an at-least-once queue and writes a row per message. You add an idempotency key taken from the message id. One duplicate still produces two rows. Why?

Choose one answer

The queue redelivered with a new message id, so the two messages were never the same key.The key is written in a different transaction from the row, so a crash between the two leaves the work done and the key absent.Two consumers processed the duplicate concurrently, and both checked for the key before either wrote it.The key window expired between the original delivery and the redelivery.

Check answer

What I would use

For anything with an external side effect, an idempotency_keys table in the same database as the effect, keyed on (account_id, key), inserted first, range-partitioned by day, with partitions dropped past the retry horizon. The client generates a UUID when the intent is formed and sends it in a header. This is the shape Stripe exposes as Idempotency-Key, PayPal as PayPal-Request-Id, Adyen as an idempotency key header, and many AWS APIs as ClientToken — not a coincidence, and worth copying rather than inventing.

For anything whose effect is a row you own, skip the table and put the unique constraint on the business data: UNIQUE (order_id) on the payments table means a duplicate charge attempt cannot be represented, with no second table, no retention policy and no scope decision. This is the better answer whenever it is available, and it is available more often than people assume.

For message consumers, the producer stamps a message id that identifies the intent, and the consumer makes its write conditional on that id. Infrastructure dedup helps and does not replace this: a broker's deduplication window is minutes, and the queue's own guarantee is at-least-once outside it.

Where idempotency keys are not the answer

The whole mechanism exists to stop a side effect happening twice, so it has nothing to offer an operation with no side effect to repeat. A pure read, or a write that sets a value rather than changing one by a delta, is already safe to repeat: UPDATE settings SET theme = 'dark' WHERE user_id = $1 applied twice has the same result as once. Adding a key to those paths buys a table and a retention job in exchange for nothing.

The boundary is worth stating precisely, because it is not "reads versus writes". It is whether the operation is expressible as a statement about the desired end state rather than a change to the current one. Set the value, assign the state, replace the collection — all naturally repeatable. Add, append, increment, charge, send — none of them, and every one of those needs a key. Where a delta is genuinely required, as with money, the key is the price of the delta.

›Exactly-once, and why nobody has itOptional

A broker cannot deliver exactly once across a network. It can deliver at-most-once, by forgetting the message before it knows you processed it, or at-least-once, by remembering until you say so — and the acknowledgement can be lost, which is where the duplicate comes from. No protocol removes that, because the general problem of agreeing on a single fact over a lossy channel is provably unsolvable in bounded time.

What products sell as exactly-once is at-least-once delivery plus deduplication somewhere, with the dedup window stated in the small print. Kafka's idempotent producer removes duplicates introduced by its own internal retries, per partition, within a producer session, and its transactions extend that to an atomic read-process-write within Kafka itself. Amazon SQS FIFO queues deduplicate on a deduplication id within a five-minute window.

Both are useful and neither reaches your database. The moment the effect leaves the system doing the deduplication — a row, a charge, an email — the guarantee you have is at-least-once and the uniqueness is yours to enforce. Which is the argument for enforcing it on the effect rather than on the delivery.

Worth remembering

• A timeout plus a retry is the normal way a duplicate is created, not an edge case.
• The key must be generated before the first attempt and reused across every retry.
• The key must commit in the same transaction as the effect it protects.
• Uniqueness must be a constraint; SELECT then INSERT is a race under concurrency.
• Store a hash of the request so a reused key with a different body is rejected.
• An in-progress repeat gets a 409, because the first attempt has not finished.
• Scope keys per account, or one tenant can block another by guessing a key.
• Range-partition the key table and drop partitions past the retry horizon.
• A unique constraint on the business row beats a key table whenever it is available.
• Operations that set an end state are already repeatable; deltas are what need keys.
• Exactly-once is at-least-once plus deduplication with a stated window.