QuickCart Stage 6: Payments and the Ledger, Money Without Mistakes

UPDATE payments
SET refunded_minor = refunded_minor + $1
WHERE id = $2
  AND refunded_minor + $1 <= captured_minor;
-- 1 row updated: refund allowed. 0 rows: it would exceed what was captured.

A CHECK (refunded_minor <= captured_minor) constraint is a second net, and captured_minor <= authorised_minor follows the same rule.

Money as integer minor units

Never store money as floating point. PostgreSQL's documentation calls real and double precision inexact, so "storing and retrieving a value might show slight discrepancies", and recommends numeric where exactness is required. JavaScript numbers behave the same way:

console.log(0.1 + 0.2); // 0.30000000000000004

QuickCart stores each amount as a bigint count of the minor unit, with the currency code beside it. ISO 4217 lists the minor-unit digits per currency: two for the US dollar (45.00 is stored as 4500), none for the Japanese yen (450 yen is 450). Never add amounts of different currencies.

Percentages bring fractions back: 15 percent of 4,205 is 630.75, which does not exist. Fix the rounding rule once and derive one side by subtraction, so the sides sum to the total:

// itemsMinor is an integer number of minor units.
export function splitItems(itemsMinor: number, feePercent: number): { feeMinor: number; storeMinor: number } {
  const feeMinor = Math.floor((itemsMinor * feePercent) / 100);
  return { feeMinor, storeMinor: itemsMinor - feeMinor };
}
// splitItems(4200, 15) returns { feeMinor: 630, storeMinor: 3570 }
// splitItems(4205, 15) returns { feeMinor: 630, storeMinor: 3575 }

JavaScript represents integers exactly up to 9,007,199,254,740,991, ample for one order.

The ledger

Double-entry ledgers

A ledger is the permanent record of money movements. A double-entry ledger records each movement twice, as a debit in one account and a credit in another, so value only moves; it never appears or disappears.

• An account is a named bucket with a balance: provider_clearing (money the provider holds for QuickCart, not yet paid out), store_payable:S42 (owed to store S42), platform_fee_revenue, courier_payable.
• An entry is one line: account, direction (debit or credit) and a positive amount.
• A ledger transaction is a set of entries that belong together, such as "capture of order ord_1001".
• The invariant: in every transaction, debits equal credits, per currency.

An asset (something QuickCart has or is owed) grows with debits; a liability (something it owes) and revenue grow with credits. Software needs only this convention stored on the account.

The ledger is immutable: entries are never updated or deleted. A mistake is fixed by a reversing transaction, leaving both error and fix visible.

Figure 4. The ledger tables

How to read this: facts live in ledger_entries; account_balances is a copy that can be rebuilt from them.

CREATE TABLE ledger_accounts (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  code      text    NOT NULL UNIQUE,
  kind      text    NOT NULL CHECK (kind IN ('asset', 'liability', 'revenue', 'expense')),
  currency  char(3) NOT NULL,
  UNIQUE (id, currency)
);

CREATE TABLE ledger_transactions (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  source_type  text        NOT NULL,   -- 'capture', 'refund', 'payout'
  source_id    text        NOT NULL,   -- e.g. the payment id: makes posting idempotent
  provider_ref text,
  description  text,
  posted_at    timestamptz NOT NULL DEFAULT now(),
  UNIQUE (source_type, source_id)
);

CREATE TABLE ledger_entries (
  id             bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  transaction_id bigint  NOT NULL REFERENCES ledger_transactions (id),
  account_id     bigint  NOT NULL,
  direction      text    NOT NULL CHECK (direction IN ('debit', 'credit')),
  amount_minor   bigint  NOT NULL CHECK (amount_minor > 0),
  currency       char(3) NOT NULL,
  FOREIGN KEY (account_id, currency) REFERENCES ledger_accounts (id, currency)
);

The composite foreign key forces an entry's currency to match its account's. The positive-amount check means direction, not sign, carries meaning. UNIQUE (source_type, source_id) is the idempotency guard for hop 4: "capture of pay_7731" can exist only once.

The database should enforce the invariant. PostgreSQL constraint triggers are AFTER ... FOR EACH ROW triggers that can be DEFERRABLE INITIALLY DEFERRED, firing at the end of the transaction, which matters because a transaction is briefly unbalanced while entries go in one at a time.

CREATE FUNCTION check_transaction_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 entries_balanced
  AFTER INSERT ON ledger_entries
  DEFERRABLE INITIALLY DEFERRED
  FOR EACH ROW EXECUTE FUNCTION check_transaction_balanced();

CREATE FUNCTION forbid_ledger_change() RETURNS trigger LANGUAGE plpgsql AS $
BEGIN
  RAISE EXCEPTION 'ledger entries are immutable';
END $;

CREATE TRIGGER entries_immutable
  BEFORE UPDATE OR DELETE ON ledger_entries
  FOR EACH ROW EXECUTE FUNCTION forbid_ledger_change();

A transaction that commits with unequal debits and credits now fails. Also revoke UPDATE, DELETE and TRUNCATE from the application's role, since a row-level trigger does not fire for TRUNCATE.

Size: at the assumed peak of 58 checkouts per second and four entries per capture, about 232 rows per second are inserted; 500,000 orders per day is about 2,000,000 rows per day, or 730,000,000 per year, before refunds and payouts (illustrative). That is plausible for one PostgreSQL primary with time-based partitioning; measure it. The ledger is its own database, not sharded by shopper like the orders database from Stage 4, because accounts such as platform fee revenue are shared by every shopper.

A worked example: one order, one store, one fee

Shopper 8812 buys 42.00 of groceries from store S42 plus a 3.00 delivery fee: 45.00 in all. QuickCart's fee is 15 percent of the item subtotal (illustrative policy); the delivery fee is owed to the courier. Amounts are minor units of an example currency, written USD.

• Charged 4,500. Fee: 15 percent of 4,200 = 630. Store: 4,200 - 630 = 3,570. Courier: 300. Check: 3,570 + 630 + 300 = 4,500.

T1, the capture (webhook confirmed):

Debits 4,500, credits 4,500: the provider holds 4,500 for QuickCart, which owes the store 3,570 and the courier 300 and earned 630.

Figure 5. Where the 4,500 goes

How to read this: the arrows show allocation, not four payments. One debit is matched by three credits that add up to it.

Later one item is reported missing. Illustrative policy: a 1,000 refund returns the platform's 15 percent (150) and the store's share (850).

T2, the partial refund of 1,000 (its own provider reference and idempotency key): debit store_payable:S42 850, debit platform_fee_revenue 150, credit provider_clearing 1,000.

When the store is paid, QuickCart pays what it owes: 3,570 - 850 = 2,720.

T3, the payout of 2,720: debit store_payable:S42 2,720, credit provider_clearing 2,720.

Check the balances. store_payable:S42 (credit minus debit): 3,570 - 850 - 2,720 = 0, so the store is paid. platform_fee_revenue: 630 - 150 = 480. courier_payable: 300 still owed. provider_clearing (debit minus credit): 4,500 - 1,000 - 2,720 = 780, exactly the 480 QuickCart keeps plus the 300 it owes the courier. The accounts explain each other; that is what double entry buys.

To post T1 idempotently, one statement inserts the transaction and its entries. On a duplicate source_id, DO NOTHING returns no row, the tx set is empty and no entries are inserted:

WITH tx AS (
  INSERT INTO ledger_transactions (source_type, source_id, provider_ref, description)
  VALUES ('capture', 'pay_7731', 'cap_8841', 'Order ord_1001 captured')
  ON CONFLICT (source_type, source_id) DO NOTHING
  RETURNING id
)
INSERT INTO ledger_entries (transaction_id, account_id, direction, amount_minor, currency)
SELECT tx.id, a.id, v.direction, v.amount_minor, 'USD'
FROM tx
CROSS JOIN (VALUES
  ('provider_clearing',    'debit',  4500),
  ('store_payable:S42',    'credit', 3570),
  ('platform_fee_revenue', 'credit',  630),
  ('courier_payable',      'credit',  300)
) AS v (code, direction, amount_minor)
JOIN ledger_accounts a ON a.code = v.code;

The ids are made up.

Balances are derived data

A balance is a sum of facts, not a fact. For a liability account:

SELECT COALESCE(SUM(CASE direction WHEN 'credit' THEN amount_minor ELSE -amount_minor END), 0) AS balance_minor
FROM ledger_entries
WHERE account_id = $1;

Summing millions of entries per read is slow, so keep a balance read model, account_balances, maintained one of two ways:

QuickCart updates per-store payables synchronously and high-traffic aggregates asynchronously (seconds of dashboard lag are harmless); a hot row can be split into sub-accounts. The entries stay the truth, so this nightly query must return nothing; a row means drift, and the table is rebuilt from entries:

SELECT b.account_id, b.balance_minor AS stored, d.derived
FROM account_balances b
JOIN (
  SELECT e.account_id,
         SUM(CASE WHEN (e.direction = 'credit') = (a.kind IN ('liability', 'revenue'))
                  THEN e.amount_minor ELSE -e.amount_minor END) AS derived
  FROM ledger_entries e
  JOIN ledger_accounts a ON a.id = e.account_id
  GROUP BY e.account_id
) d ON d.account_id = b.account_id
WHERE b.balance_minor <> d.derived;

Reconciliation

Reconciliation

A perfect ledger records only what QuickCart believes happened. The provider has its own records. Reconciliation is the routine comparison of the two, so disagreements surface within a day instead of a quarter.

The inputs: internal, ledger entries on provider_clearing grouped by provider reference; external, the provider's settlement report (one line per capture, refund, fee and payout; its format and cut-off time zone are the provider's). A full outer join on the reference exposes unmatched rows on either side. Assume each report line has a signed amount from QuickCart's side:

WITH ours AS (
  SELECT t.provider_ref,
         SUM(CASE e.direction WHEN 'debit' THEN e.amount_minor ELSE -e.amount_minor END) AS amount_minor
  FROM ledger_entries e
  JOIN ledger_transactions t ON t.id = e.transaction_id
  JOIN ledger_accounts a ON a.id = e.account_id
  WHERE a.code = 'provider_clearing'
    AND t.provider_ref IS NOT NULL
    AND t.posted_at >= TIMESTAMPTZ '2026-11-06 00:00+00'
    AND t.posted_at <  TIMESTAMPTZ '2026-11-07 00:00+00'
  GROUP BY t.provider_ref
)
SELECT COALESCE(o.provider_ref, r.provider_ref) AS provider_ref,
       o.amount_minor AS ours,
       r.amount_minor AS theirs
FROM ours o
FULL OUTER JOIN provider_report_lines r ON r.provider_ref = o.provider_ref
WHERE o.provider_ref IS NULL
   OR r.provider_ref IS NULL
   OR o.amount_minor IS DISTINCT FROM r.amount_minor;

Figure 6. Daily reconciliation

How to read this: every line ends in one of two places, matched or on a human's list. Nothing is dropped silently.

Never fix a mismatch by editing history. Post a correction, or an entry to a suspense account for unexplained amounts, with an owner and a deadline; a growing suspense balance is itself a signal. Alert on the exception count and the age of the oldest, and treat an empty report as a failure, not a clean day. Reconciliation does not prevent a double charge; it bounds how long one can stay hidden.

Consistency across services

2PC versus saga for money

A payment spans the orders database, inventory, the ledger database and an external provider. The classic tool for atomicity across resources is two-phase commit (2PC): a coordinator asks every participant to prepare, and when all agree, tells them to commit. A prepared participant holds its locks until the coordinator decides, so a coordinator failure at the wrong moment leaves participants waiting (Distributed Transactions covers the protocol).

For QuickCart's money flow it is a poor fit. The provider is external and has no prepare-and-wait interface a QuickCart coordinator could drive. Locks held across services cost availability: at 58 checkouts per second, one slow participant stalls checkout. And every participant must speak the protocol, which ends independent deployment.

So QuickCart uses the saga from Stage 5: local transactions, each with a compensating action if a later step fails.

A saga gives up isolation: readers can see intermediate states (stock reserved, payment authorised, not yet captured). The design copes by making each state explicit (Figure 3), each step idempotent and each compensation a recorded forward action, never a delete. Where everything lives in one database a local transaction still wins: posting a ledger transaction is one, and so is "set captured plus write the outbox row" (the transactional outbox from Stage 5).

Figure 7. Which mechanism for which money change

How to read this: ask top to bottom. The ledger work answers the first question (one database); the payment call answers the second (a provider, so a saga).

The authorise-then-capture flow is the Try-Confirm/Cancel pattern from the same lesson: authorise is "try", capture is "confirm", void is "cancel".

Event sourcing and audit

Event sourcing and CQRS for the ledger

Event sourcing stores changes as a sequence of immutable events and computes current state from them. CQRS (Command Query Responsibility Segregation) separates the model that accepts changes from the models that answer questions, so each can have its own shape. CQRS and Event Sourcing covers both.

A double-entry ledger already has this shape: ledger_entries is an append-only log of facts and balances are computed from it. The write side is "post a balanced transaction". The read side is a set of read models: account_balances, a statement for the store's dashboard, and daily revenue for analytics fed by LedgerTransactionPosted events through the outbox.

Benefits: a read model can be dropped and rebuilt, a new question can be answered by building a new model over old history, and every number traces to entries. Costs: read models lag, replay takes time (snapshots help), event meaning must stay stable as schemas evolve, and the team must think in events. QuickCart therefore applies event sourcing to the ledger only. Orders stay an ordinary table with a status column and a history table. Use the pattern where the log is the product.

A strict event-sourced system derives all state from events. QuickCart keeps one mutable convenience, account_balances, verified nightly: entries are authoritative and the balance table is a provable cache.

Audit logging

The ledger records what happened to the money. An audit log records who did what, including actions that move no money: a support agent issuing a refund, an admin changing a fee rate, someone viewing a shopper's payment history. They are separate stores with different access and retention rules. A record answers who, what, when and with what result:

{
  "event_id": "aud_01JD3K8Z",
  "occurred_at": "2026-11-07T09:41:12Z",
  "actor": { "type": "staff", "id": "agent_204" },
  "action": "refund.issue",
  "resource": { "type": "payment", "id": "pay_7731" },
  "details": { "amount_minor": 1000, "currency": "USD", "reason": "item_missing" },
  "outcome": "success",
  "request_id": "req_5c1e",
  "prev_hash": "9ab4...",
  "hash": "e1d7..."
}

Identifiers are examples and the hashes are shortened. What makes such a log trustworthy:

1. Append-only for the writer: insert permission, no update or delete.
2. Server-generated time and identity: the actor comes from the authenticated session, never the request body.
3. No secrets or card data, minimal personal data. Store ids, not names.
4. Tamper-evident. In a hash chain, each record includes the previous record's hash, so changing or removing one breaks every later hash.

import { createHash } from "node:crypto";

// Serialise the record the same way every time (fixed key order), or the hash will differ.
export function chainHash(prevHash: string, record: Record<string, unknown>): string {
  return createHash("sha256").update(prevHash).update(JSON.stringify(record)).digest("hex");
}

A chain detects tampering only against a trusted copy of the latest hash, because someone with write access to the whole store could recompute it all. So periodically copy the latest hash somewhere the log's operators cannot change (write-once storage or a separate account) and re-verify the chain in a job.

5. Retention is a policy decision. How long audit and financial records must be kept differs by country and record type, and privacy law may require deleting personal data. Ask compliance, keep the periods as configuration, and store ids rather than personal details. See Security.
6. Index by what you will ask: resource id, actor id and the request id shared with logs and traces (Observability and SLOs).

Decisions and trade-offs

In practice: QuickCart at this stage

The situation

QuickCart is at Stage 5, at an assumed 5,000,000 monthly active users and 500,000 orders per day. The saga's payment step calls the provider from a worker, sets the order PAYMENT_AUTHORISED and publishes PaymentAuthorised. A finance analyst compares the monthly provider statement with internal totals in a spreadsheet, and support has a refund button that runs an UPDATE and calls the provider.

Starting architecture

Figure 8. Payments before this stage

How to read this: the worker has no memory of its charge. If the response is lost, the retry (red dashed) looks like a new payment, and a crash before the status write leaves a charged shopper with an order that never advances.

Options considered

The decision

Rollout and verification

1. Shadow ledger for two weeks (illustrative): the old path stays the record, ledger transactions are posted from the same events, and daily totals are compared.
2. Key table behind a flag for a small share of shoppers, with a test that sends one key twice, concurrently, to a provider sandbox and asserts one authorisation.
3. Reconciliation in report-only mode, reviewing early exceptions by hand to learn the report's quirks.
4. Cut over payouts to the ledger, running the old query in parallel for one cycle.

Automate: the balance invariant and drift queries return no rows; captured_minor <= authorised_minor and refunded_minor <= captured_minor hold; reconciliation exception count and age alert; webhook signature failures are watched.

The 2 a.m. failure

A deploy moves key generation inside the capture retry loop, so each retry gets a fresh attempt key. At 02:00 the provider slows, many capture calls time out, and the job retries them. Some originals had succeeded, so those shoppers are charged twice.

Detection: the webhook consumer sees two payment.captured events for one payment, the captured_minor <= authorised_minor check fails, and an early reconciliation run shows references twice in the provider report but once in the ledger. The page reads "duplicate captures detected". First three checks: payments with captured above authorised; the attempt-key column for those payments (several keys on one payment shows the bug); capture calls per payment since the deploy.

Mitigation: switch off the retry job with its flag, refund the duplicates through the normal path, and tell support who is affected. Follow-ups: derive the attempt key from payment and operation (pay_7731:capture:1) and store it before the call; add a unique constraint on (payment id, operation, sequence) so the database rejects a second key; test that a retried timed-out capture creates one provider-side charge.

This is a realistic scenario, not a case history.

Architecture after this stage

Figure 9. QuickCart after Stage 6

How to read this: dashed grey boxes are unchanged or external; solid rust boxes are new. Confirmations return as webhooks, the stream feeds the ledger, and reconciliation closes the loop.

What QuickCart looks like now

Architect's notes

• Fix the unit of money on day one. Integer minor units plus a currency, everywhere. Changing later touches everything.
• Write the attempt before the call. Commit "about to call the provider with key X" first, or a crash leaves a charge nobody recorded.
• Keep the ledger boring. Few account types, balanced transactions, no updates.
• Measure exceptions, not only success: exception count and age, suspense balance, signature failures, drift results.

Common mistakes

• Floats for money. Rounding errors accumulate.
• A new key on every retry. Each request looks new, so nothing is deduplicated.
• Keys only in a cache. An eviction reopens the duplicate window.
• Unsigned or duplicated webhooks. Anyone can mark orders paid; duplicates post money twice.
• Fixing the ledger with UPDATE or DELETE. History is destroyed; post a reversing transaction.
• Skipping reconciliation because the ledger balances. It can balance and still disagree with the provider.
• 2PC across services and a provider. The provider cannot join; use a saga.

Key takeaways

• Every money-moving operation carries an idempotency key that survives client, API, provider call and consumer, stored with a request hash.
• A concurrent duplicate gets a clear answer: 409 while in progress, the stored response when done, 422 for a different body (per an IETF draft, not yet a standard).
• Back keys with a business constraint such as one live payment per order.
• Authorise at checkout, capture at the real total, and treat webhooks as confirmation: verify, deduplicate, tolerate disorder.
• Store money as integer minor units with a currency.
• A double-entry ledger is append-only and balanced; corrections are new transactions and balances are derived.
• Reconcile daily against the provider's report; a mismatch is an item with an owner.
• Use a saga, not 2PC, for money across services.
• Event sourcing earns its cost for the ledger, not every table.

Practice exercises

Exercise 1: Starter (30 min). Write the ledger transactions for an order of 60.00 of items and 4.00 delivery with a 12 percent platform fee on items (rounded down), then a full refund before any payout. Check each transaction balances and state each account's final balance.

Exercise 2: Intermediate (1.5 hours). Design the recovery job for keys stuck in_progress more than two minutes. Write the decision table (provider says authorised, nothing found, unreachable) and explain why "retry the call" is not always right.

Exercise 3: Advanced (half day). Build the key table and a payment endpoint against local PostgreSQL. Fire 20 concurrent requests with one key at a fake provider that counts charges and assert one charge. Repeat with a fresh key per request and show the partial unique index limiting the damage.

Exercise 4: Advanced (1 hour). If one in 10,000 of 500,000 daily orders mismatches (illustrative), how many reconciliation exceptions is that per day? Design who sees them, how long one may stay open and when a suspense entry is posted.

Related reading

• QuickCart Stage 5: The Order Pipeline, the saga, outbox and stream.
• QuickCart Stage 9: Resilience, timeouts and breakers around the provider call.
• System Design: Distributed Transactions, 2PC, sagas and idempotency.
• System Design: CQRS and Event Sourcing, read models.

Official documentation

• RFC 9110, HTTP Semantics: idempotent methods
• IETF Internet-Draft: The Idempotency-Key HTTP Header Field (a draft, not a standard)
• PostgreSQL: INSERT and ON CONFLICT
• PostgreSQL: CREATE TRIGGER (constraint triggers)
• PostgreSQL: Numeric types
• Apache Kafka documentation: Design (message delivery semantics)