Data & consistencyAdvanced
Taking a payment exactly once, over a network that will not let you
The client retried because it saw a timeout. The first request had already succeeded. Everything in this design exists because of that sentence.
The brief
A checkout service charges a customer through a third-party payment provider and records the result.
The network between any two of these can time out at any point, including after the work was done.
Requirements
Functional
- Charge a customer for an order
- A retried charge must not take a second payment
- Every charge must be reconstructable afterwards from stored records
Non-functional
- Charging twice is far worse than failing to charge — the failure modes are not symmetric and the design should not treat them as if they were
- The provider is a system you do not control and cannot roll back
- 'Exactly once' does not exist across a network. Design for at-least-once delivery with at-most-once effect.
Back-of-envelope
Assume
- 500 orders per minute at peak
- A provider call takes 300 ms–2 s, and times out on roughly 1 in 1,000 calls
- Clients retry a timeout once, automatically
Therefore
- Timeouts: 500/min × 1/1,000 ≈ 0.5 per minute, about 720 a day.
- Every one of those is a request whose outcome you do not know. Some succeeded at the provider and told you nothing.
- With automatic retry and no idempotency key, each of those is a potential double charge — on the order of hundreds a day, on 500 orders a minute.
A timeout rate of 0.1% sounds like a rounding error until it is multiplied by traffic and by a retry. That multiplication is the argument for idempotency keys, and it is much more convincing than the word 'best practice'.
The interface
POST /charges · Idempotency-Key: <client-generated> → 201 { chargeId, status }A replay returns the ORIGINAL response, with the original status code — not a 409. The client that retried cannot tell it retried, which is the entire point; answering 409 makes every retry an error the client now has to interpret.
Same key, different body → 422The one case that must not be a replay. A client reusing a key for a different charge is a bug in the client, and returning the first charge's success for a second, different intent hides it behind a correct-looking response.
POST /charges/{id}/refunds · Idempotency-Key: <new key>A refund is its own intent with its own key. Reusing the charge's key would make the refund look like a replay of the charge and return success without refunding anything.
What is stored
charge_attempts
The unique constraint IS the concurrency control — two replicas racing on the same key resolve at the database, not in application logic that reads and then hopes. requestHash is what makes the 422 above possible. responseBody is stored because a replay has to return what the first call returned, and reconstructing it is not the same as replaying it.
idempotencyKey (UNIQUE) · orderId · requestHash · state (IN_FLIGHT | SUCCEEDED | FAILED | UNKNOWN) · providerRef · responseBody · createdAtThe unique constraint IS the concurrency control — two replicas racing on the same key resolve at the database, not in application logic that reads and then hopes. requestHash is what makes the 422 above possible. responseBody is stored because a replay has to return what the first call returned, and reconstructing it is not the same as replaying it.
provider_events
'Every charge must be reconstructable afterwards' is a functional requirement, and an append-only log is the only shape that survives the case where your own state machine is the thing that was wrong.
append-only: providerRef · kind · payload · receivedAt'Every charge must be reconstructable afterwards' is a functional requirement, and an append-only log is the only shape that survives the case where your own state machine is the thing that was wrong.
The design
The idempotency keyGenerated by the CLIENT, before the first attempt, and reused on every retry of that same intent. Generated server-side it would be new on each retry, which defeats the entire mechanism.
The attempt recordWritten BEFORE calling the provider, keyed by the idempotency key, with a unique constraint. A concurrent duplicate loses the insert rather than making a second call.
The provider callPass the same key downstream if the provider supports one. If it does not, its outcome is only knowable by querying it afterwards, which is the reconciliation job below.
ReconciliationA job that finds attempts stuck in 'in flight' past a timeout and asks the provider what actually happened. This is not optional cleanup; it is the only thing that resolves the unknown-outcome case.
The decisions
Each of these could go the other way. The choice, the reason, and what it costs — a design that lists only what it chose teaches the choice; one that lists what it gave up teaches the judgement.
Where does the idempotency key come from?Chose — The client, once per intentBecause — The key must be stable across retries of the same user action. Only the client knows that two calls are the same intent; the server just sees two requests.It is now part of your API contract, and a client that regenerates the key on retry gets a double charge with your idempotency layer working exactly as specified. That has to be documented loudly.
Two-phase commit, or a saga with compensation?Chose — SagaBecause — 2PC requires every participant to hold a lock until the coordinator decides, and the payment provider is not a participant you can enrol in your transaction. It is not available, so the choice is somewhat made for you.Compensation is not a rollback. A refund is a second real event with its own record, its own failure modes, and its own visibility to the customer. The system is eventually consistent and the UI has to be honest about it.
How long do you keep idempotency keys?Chose — Long enough to outlive every retry, and no longerBecause — The keys exist to catch retries. Once no client could still be retrying, the record is dead weight, and there are a lot of them.Pick too short and a delayed retry — a queued mobile request after a flight — arrives after expiry and charges again. This is a real number you have to choose, and 24 hours is a decision, not a default.
What breaks first
In order. Each names what you would actually observe, and each fix carries its cost.
Holding a database connection across the provider callSymptom — The provider takes 300 ms to 2 s. If the attempt row is written and the transaction stays open until the provider answers, the connection pool is sized by the provider's latency rather than by your own — and it empties at peak.Fix — Commit the attempt as IN_FLIGHT and release the connection BEFORE calling the provider; take a second transaction to record the outcome.A window where the row says IN_FLIGHT and no provider call has been made yet. Reconciliation must treat that as 'possibly never sent' rather than 'possibly charged', which means it has to ask the provider rather than assume either way.
The reconciliation scanSymptom — It looks for IN_FLIGHT rows older than a timeout, in a table taking 720,000 inserts a day. Written naively it scans the whole table to find the handful — roughly 720 a day, a thousandth of the rows.Fix — A partial index on state = IN_FLIGHT, which stays tiny because rows leave that state within seconds.One more index on the hottest write path in the system, maintained on every insert to speed up a job that runs every minute.
Key retention, at 720,000 attempts a daySymptom — The table grows without bound, and the unique index on it is the thing every write touches. Index size, not row count, is what you feel.Fix — Partition by day and drop whole partitions once the retention window has passed.A retry that arrives after its partition is gone charges again. The decision above chose 24 hours; partitioning makes that choice permanent and physical rather than a WHERE clause you could change later.
When something fails
The provider call times outThe attempt goes to UNKNOWN — never to FAILED. FAILED means you know nothing happened, and you do not. Reconciliation asks the provider what it actually did, and only the provider's answer moves the row.
The provider succeeded and then your database was unreachableMoney moved and you have no record of it. Nothing in your own system can recover this, which is the point: the provider's ledger is the source of truth for money, yours is a cache of it, and reconciliation reads from the provider rather than from you.
A refund failsThe order sits in a state that is neither charged-and-fine nor refunded, and the UI has to say so in words. Compensation is a second real event, and a screen that shows 'refunded' when the refund is still being retried is a lie the customer will act on.
Scaling it
Each step is triggered by a number, not a feeling — and carries what it costs.
10× orders — 5,000 a minuteMove — Accept the charge, enqueue the provider call, and return 202 with a charge you can poll. The unique-constraint insert is still trivial at this rate; the pool waiting on the provider is not.The API becomes asynchronous, so the client needs polling or a webhook and the checkout screen needs a pending state. That is a product change, not just a technical one, and it should be sold as such.
A second regionMove — Make the region part of the key's uniqueness, or move the attempt table to a store with a global unique index.A per-region key means the same key in two regions is two charges — so routing has to be sticky per intent, and now routing is part of your correctness argument.
A second payment providerMove — providerRef becomes (provider, ref), and reconciliation gets an implementation per provider.'Exactly once' is now per provider, and a failover that retries a timed-out charge on provider B while provider A quietly succeeded is a double charge your idempotency key cannot see, because it is one key across two ledgers.
What gets probed
The design is the easy half. These are where the conversation goes, and each has a defensible answer above.
- The provider call times out. Was the customer charged? How does your system find out?
- Two identical requests with the same key arrive at two replicas simultaneously. Which one calls the provider?
- A refund fails. What state is the order in, and what does the customer see?
- Your database commit succeeds and the provider call fails. Now the reverse. Which is worse, and does your design agree?