Designing a payment system: the timeout that charges twice, and the ledger that proves it Advanced

The problem

A generic online shop. Order 101 costs 450,000 VND. The payment service asks an external payment gateway to charge the card. Today the gateway is slow: after 10 seconds the call times out, and checkout shows “Payment failed, try again”.

The customer clicks Pay again, and it succeeds. A week later, finance finds the card charged twice: the first charge had succeeded, only the reply was lost.

How should a payment service record, retry and account for a charge whose outcome it does not know?

What to choose? (summary first)

Situation	Choose	Condition
Call to the gateway times out or returns 5xx	Record unknown, never failed	failed only on a definite decline from the gateway
Retrying a charge	Same idempotency key, or query status first	Key stored before the first call; within the gateway’s key retention window
Customer clicks Pay twice	One active payment per order (partial UNIQUE)	Return the existing payment while it is pending/unknown
Recording money	Double-entry ledger, append-only	Integer minor units + currency; corrections are new journals
Webhook from the gateway	Verify signature, dedupe by event id, monotonic transitions	Treat it as a hint; fetch status if the payload is thin
Collecting card numbers	Hosted fields / iframe or redirect to the gateway	Your servers only see a token

Load estimation (stated assumptions)

These numbers are illustrative assumptions, not data from a real shop or gateway.

Assumption	Value
Paid orders per day	2 million
Payment attempts per order	1.2 (declines, retries)
Peak-to-average factor (sale days)	10x
Gateway calls that time out	0.1%
Ledger entries per successful payment	5 (capture 2, settlement 3)
One entry row with indexes	~150 bytes

Quantity	Calculation	Result
Payment attempts	2.4M / 86,400	~28/s, peak ~280/s
Unknown outcomes	2.4M × 0.1%	~2,400 per day
Ledger entries	2M × 5	10M rows/day
Ledger storage	10M × 150 B	~1.5 GB/day, ~550 GB/year

Throughput is easy: ~280 writes/s fits one PostgreSQL primary. The decisive number is 2,400 unknown outcomes a day. Guessing on each one means double charges or lost revenue every day.

Core concepts

• Gateway: the external service that talks to card networks. It owns the truth about whether money moved.
• Authorize / capture: authorize holds the amount; capture takes it. Many shops do both in one call.
• Idempotency key: a unique value that makes repeats of a request produce one effect (see idempotency, Idempotency-Key).
• Ledger: append-only money movements, separate from the payment status row.
• Settlement file: the gateway’s daily report of captures, refunds, payouts and fees.

Payment state machine

Loading diagram…

1. failed means the gateway said no. A timeout says nothing about its decision.
2. Transitions only move forward: captured never returns to pending, even if a late event says so.

How it works

Two idempotency keys

Boundary	Key	Protects against
Client → payment service	Idempotency-Key header from the app, plus one active payment per order	Double click, mobile retry after a dropped response
Payment service → gateway	payment.id (pay_101), stored before the first call	Our own retries after a timeout

The second key prevents the opening incident. It is committed with status pending, then sent, and every retry reuses it.

Timeout → status query → reconcile

A lost reply leaves pay_101 unknown; the same key, the webhook and the settlement file all resolve it to one charge

◀ Prev▶ PlayNext ▶↺ RestartStep 1/7

Before calling the gateway, the service stores pay_101 as pending; the key exists before any money moves.

State after each step:

1. pay_101 is pending with us; the gateway has not seen it.
2. The gateway has captured 450,000 VND. We do not know.
3. We record unknown; checkout says “being confirmed”, not “failed”.
4. The second click finds the active pay_101 and returns it. No second charge.
5. The status query returns captured. Status, ledger journal and outbox event commit together.
6. The late webhook’s event id is stored; captured → captured is a no-op.
7. Reconciliation finds pay_101 once in the settlement file and once in the ledger.

If the gateway never saw pay_101, retrying with the same key is safe. If the query also times out, the payment stays unknown and a worker retries with backoff.

Never resolve unknown by sending a new charge with a new key. That is exactly the double charge.

Webhooks

Signed events such as charge.captured follow webhook signatures and dedupe: verify the signature over the raw body, insert the event id under a UNIQUE constraint in the same transaction as the state change, apply forward transitions only, and return 2xx.

A missing webhook is normal; queries and reconciliation cover it.

Double-entry ledger

Each money event is a journal of two or more entries; each entry debits or credits one account. Per journal, debits equal credits. Balances are sums of entries, never a stored column.

Money is a bigint in minor units plus a currency. ISO 4217 lists minor units: VND has 0 (450,000 VND = 450000), USD has 2 ($49.99 = 4999). Never float8; see data type pitfalls.

entry has CHECK (amount_minor > 0), CHECK (direction IN ('D','C')) and a composite foreign key (account_id, currency). A CHECK sees one row only, so “debits = credits” is a deferred constraint trigger that runs at COMMIT:

CREATE CONSTRAINT TRIGGER entry_balanced
  AFTER INSERT ON entry
  DEFERRABLE INITIALLY DEFERRED
  FOR EACH ROW EXECUTE FUNCTION assert_journal_balanced();

A second trigger rejects UPDATE and DELETE. Mistakes are fixed by a correcting journal that reverses them, so history stays intact.

Outbox for downstream events

Orders, email and fulfilment learn about the capture from a PaymentCaptured row written to an outbox in the same transaction as the status and journal (see transactional outbox). Consumers dedupe by event id.

Operational details

• Deadline: the gateway call’s deadline is shorter than checkout’s, so the service can answer “being confirmed” (see timeout budgets).
• Key retention: gateways keep idempotency keys for a provider-specific window. Resolve every unknown well inside it.
• Unknown sweeper: query every unknown older than 1 minute, with backoff. Alert on the count and the oldest age.
• Reconciliation: daily, match settlement rows ↔ payment ↔ ledger by gateway reference and amount. Each break (missing on either side, amount or fee mismatch) becomes a ticket, then a correcting journal.
• PCI DSS scope: with a redirect or the gateway’s iframe (hosted fields), card numbers go from browser to gateway; you keep a token. The PCI SSC says such merchants may be eligible for SAQ A. Since v4.0.1, iframe pages also need script-attack protection. The acquirer confirms the SAQ.

Real-world example

One bad afternoon, reconstructed by reconciliation

During a sale, the gateway’s p99 passes 10 s for 40 minutes. The old checkout marks 1,900 timeouts as failed. The gateway had captured 1,300 of them, and 1,100 of those customers paid again. Next morning, 1,300 settlement rows match no captured payment (illustrative numbers).

For the 1,100 duplicates, reconciliation posts a capture journal crediting customer_refundable, refunds through the gateway with key refund:<payment id>, and posts a refund journal once confirmed. The other 200 orders are captured late and shipped, or refunded.

The ledger demo replays one such duplicate, plus rejected writes, on PostgreSQL 17.11 (examples/docker-compose.yml, port 5433):

docker compose -f examples/docker-compose.yml exec -T postgres \
  psql -U hub -d hub -v ON_ERROR_STOP=0 < examples/src/payment-system/ledger.sql

Real output, abridged:

--- 2. unbalanced journal: rejected at COMMIT
ERROR:  journal 2 unbalanced in VND: debit 450000 <> credit 405000
--- 5. editing an entry: rejected
ERROR:  ledger entries are immutable; post a correcting journal instead
--- 8. balances are sums of entries, never a stored column
       account       |   kind    | balance_minor
---------------------+-----------+---------------
 bank_cash           | asset     |        441000
 customer_refundable | liability |             0
 gateway_fees        | expense   |          9000
 gateway_receivable  | asset     |             0
 sales               | revenue   |        450000
--- 9. trial balance: total debits = total credits
 debits  | credits
---------+---------
 1800000 | 1800000

What the demo does not cover: the trigger cannot catch a journal with no entries, concurrency control on balances (for example a spending limit), or permissions; production also REVOKEs UPDATE/DELETE from the application role.

🎮 Try it

• Scenario: payment timeout: the gateway times out, the customer pays twice, and finance finds the duplicates.

Trade-offs

Pros

• An explicit unknown turns guesses into a queue to resolve.

• One key per payment makes retries safe without trusting the network.

• An append-only ledger explains every balance; reconciliation catches the rest.

< slot="cons">

• Customers sometimes see “being confirmed” instead of an instant answer.

• More moving parts: sweeper, webhook handler, reconciliation, outbox relay.

• Balances are queries over growing tables; large accounts need snapshots.

Cons

Production checklist

1. Commit payment as pending with its gateway key before the first gateway call.
2. Map every timeout, connection reset and 5xx to unknown; only explicit declines become failed.
3. Enforce one active payment per order with a partial UNIQUE index.
4. Run an unknown sweeper; alert when the oldest unknown passes 15 minutes.
5. Store money as bigint minor units with a currency; enforce balanced journals at commit.
6. Make ledger entries append-only at the database level, not just in code.
7. Verify and dedupe webhooks; apply forward-only transitions.
8. Reconcile daily against the settlement file and track breaks to zero.

Common mistakes

The first mistake is the one from the problem section, written as code.

Retrying a timed-out charge

✕ Common

try { await gw.charge({ amount, key: randomUUID() }); }
catch (e) { if (isTimeout(e)) await gw.charge({ amount, key: randomUUID() }); }

✓ Prefer

await markUnknown(pay.id);              // never "failed"
const s = await gw.getCharge(pay.id);   // same key as the first call
if (s === 'not_found') await gw.charge({ amount, key: pay.id });

Why

A new key tells the gateway this is a new charge. The same key lets the gateway return the first result.

The second mistake hides in the ledger, where a fix looks harmless.

Editing a ledger row to fix an amount

UPDATE entry SET amount_minor = 405000 makes today’s report right and destroys why yesterday’s report was different. Post a correcting journal that reverses the wrong one, then the right journal.

Misconception	Consequence	Do instead
Timeout means the charge failed	Customer pays again; double charge	unknown + status query
The webhook is the source of truth	Missed or late event leaves the payment stuck	Webhook, status query and reconciliation together
Balance column updated in place	Drift nobody can explain	Balance = sum of entries
Amounts as float8 or decimal strings	Rounding errors, mixed currencies	bigint minor units + currency

Self-check

1. The status query for an unknown payment returns “not found”. Why is retrying with the same key safe, and a new key not?
2. Why is “debits = credits” a deferred trigger and not a CHECK constraint?
3. A refund call times out. Which state and which ledger entries do you record?

Summary

Key takeaways

• Under the stated assumptions, ~2,400 outcomes a day are unknown; unknown must be a state.
• One gateway key per payment, stored before the call and reused on every retry; never a new key for an unknown charge.
• Resolve unknown by status query, webhook and daily reconciliation.
• Double-entry, append-only ledger in integer minor units; corrections are new journals.
• Hosted fields or redirect keep card numbers off your servers and shrink PCI DSS scope.

References

• The Idempotency-Key HTTP Header Field — IETF draft: key semantics for retrying non-idempotent requests.
• CREATE TRIGGER — PostgreSQL 17: constraint triggers must be AFTER ROW and can be deferred to the end of the transaction.
• Constraints — PostgreSQL 17: why a CHECK must not reference other rows.
• ISO 4217 data standards — SIX: the currency list with minor units per currency.
• FAQ clarifies new SAQ A eligibility criteria — PCI SSC: iframe vs redirect under PCI DSS v4.0.1.
• Document Library — PCI SSC: PCI DSS and the self-assessment questionnaires.