Short answers to what readers ask most about this topic.
How to design a payment system that never loses or double-charges money: a payment state machine, an append-only double-entry ledger in integer cents, idempotency keys, webhooks and daily reconciliation.
Matthews Wong
Software Engineer
Key Takeaway
A payment system that never loses or double-charges money combines four things: a payment state machine with an explicit unknown state, an append-only double-entry ledger in integer minor units, idempotency keys at the API boundary, and a reconciliation job comparing your ledger with the processor's settlement reports. Refunds are new entries, never edits.
The scariest payment bug is not a crash. It is a request that times out after the processor already took the money, because your code cannot tell whether to retry or give up, and either guess can cost a customer or the business real cash.
This post is the whole-system design: the pieces and the order they have to be built in. I work on ERP and POS systems on Postgres and NestJS, so the examples use Postgres and TypeScript. Every number below is derived in front of you or cited from documentation, and nothing here is a benchmark.
Two failures define the problem, and they pull in opposite directions. A lost payment happens when the customer was charged and your system has no record. A double charge happens when one intent is executed twice. Retrying harder fixes the first and causes the second, so the design has to give you three guarantees at once.
Notice what is absent: a promise that nothing ever fails. Networks time out, processors send events twice and sometimes never. The goal is a system where every failure ends in a known state or a ticket, never a silent mismatch.
Model each payment as a state machine with a fixed table of allowed transitions, and write that table as data. The one unusual state is unknown: the request was sent and the outcome is not. Only reconciliation or a confirmed webhook may leave it. The transition below is a compare-and-set update plus an audit row in the same transaction.
type PaymentState =
| "created" | "submitted" | "authorised" | "captured"
| "failed" | "unknown" | "partially_refunded" | "refunded";
// The whole lifecycle in one table. Anything not listed here cannot happen.
const ALLOWED: Record<PaymentState, PaymentState[]> = {
created: ["submitted", "failed"],
submitted: ["authorised", "captured", "failed", "unknown"],
unknown: ["authorised", "captured", "failed"], // only reconciliation resolves it
authorised: ["captured", "failed"],
captured: ["partially_refunded", "refunded"],
partially_refunded: ["partially_refunded", "refunded"],
failed: [], // terminal: a retry is a NEW payment
refunded: [],
};
async function transition(
db: PoolClient,
paymentId: string,
from: PaymentState,
to: PaymentState,
evidence: { source: "api" | "webhook" | "reconciliation"; ref: string },
): Promise<void> {
if (!ALLOWED[from].includes(to)) {
throw new Error("illegal transition " + from + " -> " + to);
}
// Compare-and-set: two racing writers cannot both move the same payment.
const res = await db.query(
"UPDATE payments SET state = $3, version = version + 1 WHERE id = $1 AND state = $2",
[paymentId, from, to],
);
if (res.rowCount === 0) return; // someone else got there first: a no-op, not an error
// The audit row commits with the state change or not at all.
await db.query(
"INSERT INTO payment_events (payment_id, from_state, to_state, source, ref) VALUES ($1, $2, $3, $4, $5)",
[paymentId, from, to, evidence.source, evidence.ref],
);
// Money movement (ledger entries) is posted by the caller in this same transaction.
}The compare-and-set is what stops two workers, or a webhook and a retry, from both moving the same payment. The loser matches zero rows and treats it as a no-op. Failed is terminal on purpose: a customer retrying is a new payment with a new idempotency key, never a resurrected old one.
Keep the state machine and the ledger in the same database transaction. If the state says captured, the capture entries exist, and if the transaction rolls back, neither does. Two systems that must agree will eventually disagree.
Store money as integers in the currency's minor unit, never as floats. Binary floating point cannot represent most decimal fractions, and the error shows up exactly when you multiply or sum. Minor units differ by currency, and zero-decimal currencies exist, so store the currency next to every amount and let rounding rules be explicit code.
// Wrong: binary floating point cannot represent most decimal fractions.
0.1 + 0.2; // 0.30000000000000004
19.99 * 100; // 1998.9999999999998, and Math.floor() gives 1998
// Right: integers in the currency's minor unit, rounding decided once and written down.
const priceMinor = 1999; // USD 19.99 stored as 1999 cents
const taxMinor = Math.round((priceMinor * 11) / 100); // 219.89 -> 220, an explicit ruleOn top of integers, use double-entry bookkeeping: every movement is a set of debit and credit entries that sum to zero, so money is never created or destroyed, only moved between accounts. Entries are only ever inserted. The schema below enforces both rules in the database instead of trusting application code.
CREATE TABLE accounts (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
code text NOT NULL UNIQUE, -- 'psp_clearing', 'revenue', 'psp_fees', 'bank'
currency char(3) NOT NULL
);
CREATE TABLE ledger_transactions (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
payment_id uuid NOT NULL,
kind text NOT NULL CHECK (kind IN ('capture', 'fee', 'payout', 'refund', 'reversal')),
reverses_id uuid REFERENCES ledger_transactions(id), -- corrections point at what they undo
external_ref text, -- the PSP's id for this movement
created_at timestamptz NOT NULL DEFAULT now(),
UNIQUE (kind, external_ref) -- the same PSP event can never post twice
);
CREATE TABLE ledger_entries (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
transaction_id uuid NOT NULL REFERENCES ledger_transactions(id),
account_id bigint NOT NULL REFERENCES accounts(id),
direction text NOT NULL CHECK (direction IN ('debit', 'credit')),
amount_minor bigint NOT NULL CHECK (amount_minor > 0), -- sign lives in direction, never here
currency char(3) NOT NULL
);
-- A CHECK constraint sees one row, so it cannot say "the rows of a transaction sum to zero".
-- A deferred constraint trigger runs at COMMIT, after every entry of the transaction exists.
CREATE FUNCTION assert_balanced() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
IF EXISTS (
SELECT 1 FROM ledger_entries
WHERE transaction_id = NEW.transaction_id
GROUP BY currency
HAVING sum(CASE direction WHEN 'debit' THEN amount_minor ELSE -amount_minor END) <> 0
) THEN
RAISE EXCEPTION 'ledger transaction % is unbalanced', NEW.transaction_id;
END IF;
RETURN NULL;
END $$;
CREATE CONSTRAINT TRIGGER ledger_entries_balanced
AFTER INSERT ON ledger_entries
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW EXECUTE FUNCTION assert_balanced();
-- Append-only, enforced by the database and not by good intentions.
CREATE FUNCTION forbid_mutation() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
RAISE EXCEPTION 'ledger rows are immutable: post a reversal instead';
END $$;
CREATE TRIGGER ledger_entries_immutable
BEFORE UPDATE OR DELETE ON ledger_entries
FOR EACH ROW EXECUTE FUNCTION forbid_mutation();
-- Also: REVOKE UPDATE, DELETE, TRUNCATE ON ledger_entries FROM the application role.A CHECK constraint sees a single row, so it cannot express that a transaction balances. A deferred constraint trigger runs at commit, after all the entries of the transaction exist, and raises if any currency does not sum to zero. The unique pair of kind and external reference means the same processor event cannot be posted twice, even if your code tries.
Revoke UPDATE, DELETE and TRUNCATE from the application role as well as adding the trigger. A trigger protects you from bugs, and permissions protect you from the migration someone runs at 2 am. Balances are always derived by summing entries, never stored and edited.
The client generates a unique key per intent and sends it with the request. The server inserts the key into a table whose primary key is the guarantee, before doing any work. Stripe documents the same idea: keys up to 255 characters, and for its v1 API they may be removed automatically once they are at least 24 hours old, so choose your own retention deliberately. I cover the mechanics in a separate post, so here is only the shape that matters for payments.
CREATE TABLE idempotency_keys (
merchant_id text NOT NULL,
key text NOT NULL,
request_hash text NOT NULL, -- sha256 of the canonical request body
payment_id uuid,
response_status int,
response_body jsonb,
created_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (merchant_id, key) -- the primary key IS the guarantee
);
-- First line of every payment request. One row comes back for the winner, none for a replay.
INSERT INTO idempotency_keys (merchant_id, key, request_hash)
VALUES ($1, $2, $3)
ON CONFLICT (merchant_id, key) DO NOTHING
RETURNING key;
-- Zero rows: this is a replay. Read the stored row, then:
-- same request_hash, response stored -> return the stored response verbatim
-- same request_hash, no response yet -> 409: the first attempt is still running
-- different request_hash -> 422: the key is being reused for a different requestStore a hash of the request with the key. Replaying the same key with a different body is a client bug, and silently returning the first result would hide it. Return a conflict instead. Also keep the key row and the payment row in one transaction, so a crash cannot leave a key that points at nothing.
A timeout is not a failure, it is an unknown. What you saw does not tell you what the processor did, so the table below maps each observation to the safe action. The rule underneath is that you may resend the same idempotency key as often as you like, but you must never create a new key to retry an unknown outcome.
| What you observed | What may be true at the processor | Safe action | Ledger effect |
|---|---|---|---|
| Connection refused or DNS failure before sending | The request never arrived | Retry with the same idempotency key | None |
| Read timeout after the request was sent | Captured, declined or still processing | Mark the payment unknown, retry the same key or look it up by your reference | None until the outcome is confirmed |
| HTTP 5xx from the processor | Ambiguous, same as a timeout | Treat it exactly like a read timeout | None until the outcome is confirmed |
| A clear decline response | Definitively refused | Move to failed, ask the customer to try again as a new payment | Nothing was posted |
Unknown payments are resolved by one of two things arriving later: a webhook, or the reconciliation job asking the processor directly. Until then the customer sees pending, never failed and never paid. Showing failed for an unknown payment is how people get charged twice by paying again.
Webhooks are how the processor tells you what happened after your call returned. They are delivered at least once and can arrive out of order or not at all, so the handler stores the event id under a unique index and ignores repeats. Stripe's own guidance is to log processed event ids and skip already-logged events. Acknowledge quickly and do the work from the stored row.
app.post("/webhooks/psp", async (req, res) => {
// 1. Verify the signature over the RAW body first (see your PSP's docs for the exact scheme).
// 2. Record the event id. A duplicate delivery hits the unique index and does nothing.
const inserted = await db.query(
"INSERT INTO psp_events (event_id, payload) VALUES ($1, $2) ON CONFLICT (event_id) DO NOTHING RETURNING event_id",
[event.id, event],
);
res.sendStatus(200); // acknowledge fast; do the work from the stored row
if (inserted.rowCount === 0) return; // already processed: webhooks are delivered at least once
await applyEvent(event); // runs transition() + ledger posting in one transaction
});Webhooks can be lost, so a webhook-only design has a hole. The backstop is a daily reconciliation job that imports the processor's settlement report and compares it with your ledger. A full outer join shows all three failure shapes in one result: in our ledger but not at the processor, at the processor but not in our ledger, and amounts that differ.
-- psp_report_lines is the settlement file you import each day: (ref, kind, amount_minor).
-- A FULL OUTER JOIN shows all three failure shapes in one result.
SELECT coalesce(l.ref, p.ref) AS ref,
l.amount_minor AS ours,
p.amount_minor AS theirs,
CASE
WHEN p.ref IS NULL THEN 'in our ledger, not at PSP'
WHEN l.ref IS NULL THEN 'at PSP, not in our ledger'
ELSE 'amount differs'
END AS problem
FROM (
SELECT t.external_ref AS ref, sum(e.amount_minor) AS amount_minor
FROM ledger_transactions t
JOIN ledger_entries e ON e.transaction_id = t.id AND e.direction = 'debit'
WHERE t.kind = 'capture' AND t.created_at >= $1 AND t.created_at < $2
GROUP BY t.external_ref
) l
FULL OUTER JOIN (
SELECT ref, amount_minor FROM psp_report_lines WHERE kind = 'capture' AND day = $3
) p USING (ref)
WHERE l.amount_minor IS DISTINCT FROM p.amount_minor;
-- Zero rows means the day agrees. Every row is a ticket, and none is fixed by editing the ledger.Reconciliation never edits the ledger to make numbers match. Each row becomes a ticket with a fix that is itself a new entry or a state transition, for example resolving an unknown payment to captured and posting its entries. The job is also your monitoring: the count of unmatched rows per day is the one number that tells you the system is healthy.
A refund is a new transaction that moves money back, not an edit of the original capture. The worked example below traces USD 19.99 through capture, payout and a partial refund in cents. Notice that the payout nets an 88 cent fee from the settlement file, so 1999 minus 88 is 1911 reaching the bank, and that every transaction balances by construction.
-- Worked example: USD 19.99 captured, then a USD 5.00 refund. Every amount is cents.
-- capture (PSP event evt_cap_1) DR psp_clearing 1999 CR revenue 1999
-- payout (settlement line st_1) DR bank 1911 DR psp_fees 88 CR psp_clearing 1999
-- (the settlement file shows an 88 cent fee, so 1999 - 88 = 1911 reaches the bank)
-- refund (PSP event evt_ref_1) DR revenue 500 CR psp_clearing 500
--
-- psp_clearing: debit 1999, credit 1999, credit 500 -> net 500 credit (owed back on the next payout)
-- revenue : credit 1999, debit 500 -> net 1499 credit
-- Trial balance: total debits must equal total credits, to the cent, forever.
SELECT sum(CASE direction WHEN 'debit' THEN amount_minor ELSE -amount_minor END) AS should_be_zero
FROM ledger_entries;Guard the refund by the arithmetic: refunds already posted plus the new amount must not exceed the captured amount. After the 500 refund, 1499 is still refundable, so a second request for 1600 fails because 500 plus 1600 is 2100, more than 1999. A wrong entry is corrected by a reversal that points at the original through its reverses column, followed by the right entry, which keeps both visible forever.
The audit trail is what you already built: the payment events table records every transition with its source and reference, and the ledger records every movement. Together they let you answer who moved this money, when and why, without reading application logs. Before launch, check the design against this list.
For a small system on a single Postgres instance, all of this fits in a handful of tables and two background jobs. The cost is discipline, not infrastructure, and it is far cheaper than explaining to a customer why they were charged twice.
Treat unknown as a first-class state, make every write repeatable, record money as balanced append-only entries, and let a reconciliation job prove the books against the processor every day. Correctness here is a set of constraints in the database, not a property of careful code.
Related reading