xmuu
cases /

Idempotent payments: the same request, charged exactly once

checking

October 16, 2026 · go · sqlite · open demo ↗

The demo runs as its own app. If it fails to load here, it is genuinely down — open fullscreen to check, or come back later. The write-up below stands on its own.

open demo ↗

Problem

A customer taps "Pay" twice because the spinner never stopped. A webhook fires again because the first delivery timed out. My retry loop re-sends a charge because the response was lost somewhere between the gateway and me. Three different causes, one identical outcome: the same logical payment arrives at my server more than once, and if I treat every arrival as a new order, somebody gets charged twice for one thing they bought.

This is not an edge case. Any network between the buyer and the ledger makes duplicate delivery a certainty rather than a possibility — TCP retries, load balancer timeouts, users double-clicking, queue consumers redelivering after a crash. I cannot prevent the duplicate from arriving. I can only decide what happens when it does. The demo makes that decision visible: send {"idempotency_key":"order-1234","amount_cents":2500} once and you get a fresh charge; send it ten more times and you get the same stored answer back, with the ledger still holding exactly one row for that key.

Context

The demo is a tiny payment endpoint backed by SQLite, one Go binary, no external services. Two tables do all the work. The payments table holds one row per idempotency key — the key is the primary key, so the database itself refuses a second charge under the same key. The attempts table logs every single POST /api/pay call with a deduped flag, so the UI can show the difference between "charged" and "absorbed": replays are visible, they just never become money movement.

I seeded the ledger with 3 sample charges (2500, 4999, and 12000 cents) so the page is never empty, and POST /reset restores exactly those 3 rows ({"status":"reset","rows":3}). The ledger endpoint returns the 50 most recent charges and attempts. Input is strict: keys run 1 to 128 characters, amounts 1 to 100000000 cents, bodies are capped at 1 MiB, and anything else is a 400, never a 500. Every payment round trip completed in 1.5 to 3 milliseconds — two single-row SQLite statements, no network calls.

The number I keep coming back to is from the concurrency check: I fired 10 simultaneous POSTs with the same key at the running demo and got back 1 charge, 10 attempts, 9 of them marked deduped. That is the whole case study in one line.

Alternatives Considered

Client-side dedup only (disable the button, debounce the click). This kills the double-click but nothing else. Retried webhooks, replayed queue messages, and a user resubmitting from a second tab all sail past it, because the duplicate arrives as a brand-new, well-formed request. I treat UI debouncing as courtesy, not correctness — the server must survive the duplicate arriving anyway.

"Check then insert" in application code (SELECT for the key, INSERT if absent). This works until two identical requests arrive in the same instant — both SELECTs find nothing, both INSERTs proceed, and the customer is charged twice by code that passed every sequential test. My 10-way concurrent POST check exists precisely to catch this shape of bug. The fix is to make the database the arbiter instead of the application: attempt the INSERT and let the UNIQUE constraint serialize the race. One writer wins, every loser gets told the key already exists, and the losers all read back the winner's row.

Natural dedup on business fields (same user + same amount + same minute). Fuzzy matching on "looks like the same payment" is a support-ticket factory: two genuinely different 2500-cent orders from one customer within a minute are common, and merging them means losing real money silently. An explicit client supplied key says exactly which requests are the same operation, with no guessing and no time windows.

Solution

The rule is one sentence: the idempotency key is the primary key of the charge. The client generates a unique key per logical operation (order ID, checkout session, webhook event ID) and sends it with every attempt. The server tries to INSERT the payment row; if the key is new, the row is created and the call is logged as a fresh charge. If the INSERT hits the UNIQUE conflict, the server reads back the stored row, logs the call as a deduped attempt, and returns the original amount and status with "deduped": true — HTTP 200 in both cases, so retry loops need no special error handling. A replay that disagrees on the amount still gets the original: I verified this live by replaying order-1234 with 9999 cents and receiving the stored 2500 back, deduped.

The core of it is this insert-or-read-back, with the constraint as the judge:

if _, err := s.db.Exec(
    `INSERT INTO payments (idempotency_key, amount_cents) VALUES (?, ?)`,
    key, amount); err == nil {
    // First use of this key: record the charge and the attempt.
    s.db.Exec(`INSERT INTO attempts (idempotency_key, amount_cents, deduped)
        VALUES (?, ?, 0)`, key, amount)
    return PayResult{Key: key, AmountCents: amount}, nil
} else if !isUniqueViolation(err) {
    return PayResult{}, fmt.Errorf("insert payment: %w", err)
}
var stored int64
s.db.QueryRow(`SELECT amount_cents FROM payments WHERE idempotency_key = ?`,
    key).Scan(&stored)
s.db.Exec(`INSERT INTO attempts (idempotency_key, amount_cents, deduped)
    VALUES (?, ?, 1)`, key, amount)
return PayResult{Key: key, AmountCents: stored, Deduped: true}, nil

Try it in the playground above the write-up: type any key, execute, then hit "Replay same key" — the response panel shows the second call returning 200 with "deduped": true while the attempt table gains a highlighted row and the charge count stays put. Press "Random key" and the next execution is a fresh charge again, proving the dedup scopes to the key and nothing else. The automated suite asserts the same contract over HTTP: replay-same-key yields exactly 1 charge against 3 attempts (2 deduped), two distinct keys yield 2 charges, and /reset wipes a test charge so it never survives. The full package passes go vet and go test -race in about a second and a half.

Trade-offs

The UNIQUE-key design buys its safety with storage and semantics I should be honest about. First, keys live forever in this demo and would grow without bound in production — every distinct operation leaves a row that future replays must find, so a real deployment needs a retention policy (keep keys for the dispute window, then archive) and that policy is itself a correctness decision: expire a key too early and a late replay becomes a double charge.

Second, "same key returns the original" silently ignores amount mismatches. I chose return-original because payment gateways (Stripe's Idempotency-Key is the reference) treat the first request as authoritative, but it means a buggy client reusing one key for different carts gets charged the first cart's total with a cheerful 200. An alternative is failing mismatched replays with 422 so the bug surfaces loudly; I stayed with the gateway convention and note the 422 option for anyone whose clients cannot be trusted to mint fresh keys.

Third, the demo leans on SQLite's single-writer model (one open connection), which makes the INSERT conflict path trivially safe. Under a multi-writer store the same pattern still holds — the unique index is still the arbiter — but the "read back the winner's row" step must tolerate the winner's transaction not yet being visible, usually with a short retry. And the attempts log is observability, not audit: it shares the demo database and the reset wipes it, which is fine for a playground and wrong for a ledger anyone's accountant reads.

Production Notes

What ships around this pattern matters more than the pattern itself. Key generation belongs to the operation creator: use the checkout session ID or order UUID, generated before the first attempt and persisted client-side so retries reuse it — a key minted fresh on every retry dedups nothing. Scope keys per endpoint and per merchant account; a global keyspace invites cross-operation collisions.

Monitor three things. The dedup ratio (deduped attempts over total) tells you your retry health — a sudden spike means a misbehaving client or a flapping dependency, not fraud. The mismatch rate (replays whose amount differs from the stored one) is your client-bug detector; alert on it. And watch key-space growth against your retention job, because an ever-growing unique index slows every insert and the failure mode is latency, not errors.

On failure modes: this design converts duplicate charges into stale reads at worst — a replay served during a replica lag window could theoretically return "not found" and create a second charge, so the read-back and the insert must hit the primary, never a replica. Rate-limit key creation per client to bound storage abuse, expire idempotency records on a documented schedule aligned with your chargeback window, and keep the attempts log in a separate, append-only store from the money table so debugging never contends with charging. The demo compresses all of that into one SQLite file on purpose; production spreads it across a primary database, a retention job, and a dashboard — same invariant, more machinery.