An idempotent ledger

A phone on a train sends a charge. The server processes it and replies, but the reply never arrives. The app waits, times out, and does the reasonable thing: it tries again. Now the customer has paid twice.

Newer apps already sent a request id header. Older ones didn't, and we couldn't force anyone to update. The fix had to live on the server, inside the database we already trusted, without a new service in front of it.

The key is written in the same transaction as the charge. Either both exist or neither does. When the charge finishes, the response is saved next to the key, so a retry can get exactly the same answer.

On a retry, the API finds the key already done and returns the stored response. No new charge. The client can't tell it apart from the original reply, and that's what makes retrying safe.

Keys can't live forever, or the table grows without end. We kept them for 24 hours, because no retry could possibly take longer than that. That sentence turned out to be the bug.

Two copies of the same request can arrive at the same moment. INSERT … ON CONFLICT DO NOTHING lets exactly one of them claim the key. The other gets a 409 saying the charge is in progress and to ask again shortly.

A phone went offline for a weekend with a charge still queued. It came back 26 hours later and retried. The key had expired two hours earlier, so the server saw a brand-new request and charged again.

The hard part was never the unique constraint. It was deciding what "the same request" means and for how long. Keys are now scoped per account and kept for seven days. A key that comes back with a different body is rejected rather than trusted.