Payment and Transaction Processing Systems Questions

Direct answer

Retries are only safe for payments if every attempt of one logical charge carries the same idempotency key (a client-generated ID the gateway uses to recognise repeats and return the first result instead of charging again). On top of that: at most 4 attempts (1 original plus 3 retries) inside a hard 15-second overall deadline, exponential backoff with full jitter (a random wait between zero and a growing ceiling), retries only on errors that mean "try again" or "outcome unknown", and a client-wide retry budget plus circuit breaker (a guard that stops calling a failing dependency for a cool-down period once its failures cross a threshold, instead of continuing to hammer it) so that when the gateway is partly down the clients back off together instead of multiplying its load. An attempt whose outcome is still unknown when the deadline hits is not failed: it is marked pending and resolved later by a status lookup or webhook.

The policy, parameter by parameter

The wait before retry number $n$ (counting from 1) is:

Worst-case time if every attempt hits its full timeout: 4 x (1 s + 5 s) = 24 s of attempts plus at most 0.25 + 0.5 + 1.0 = 1.75 s of waiting, 25.75 s in total. That exceeds the 15 s deadline, which is the point: the deadline, not the attempt count, is what bounds the shopper's wait, and the fourth attempt only happens when earlier attempts failed fast (connection refused, 503).

Which responses to retry

Honour provider hints when they exist: Stripe sends Stripe-Should-Retry: true/false when it knows, and its client libraries already respect it.

Idempotency details the library must get right

• Key per logical operation, generated once when the charge intent is created (for example from the order ID plus the operation: order-5521:charge), and persisted before the first attempt. If the process crashes and restarts, it reloads the key instead of generating a new one.
• Identical parameters on every retry. Gateways compare parameters for a reused key and error if they differ; recomputing the amount between attempts (a currency conversion, a changed cart) turns a safe retry into an error or, without keys, a second charge.
• Key lifetime. Stripe documents that keys can be pruned once at least 24 hours old, after which a reused key is treated as a new request. So retries and crash-recovery must finish well inside a day; anything older is resolved by listing the payments for the order, never by resending.
• Concurrent callers: two threads charging the same order must share the key (and ideally a local lock), otherwise each has its own key and both succeed.

Partial gateway outages: coordinate, do not stampede

A partial outage means some requests fail (one region, one card network, one endpoint) while others succeed. Uncoordinated retries turn it into a full outage: 1,000 failing requests per second with 3 retries each becomes up to 4,000.

1. Retry budget (a token bucket: each first attempt adds 0.1 tokens, each retry spends one): during a total outage clients send about 1.1x normal traffic, not 4x.
2. Circuit breaker per gateway endpoint and region: if more than 50% of the last 20 calls within 10 s failed with retryable errors, the breaker opens and new charges fail fast (or are queued for asynchronous processing if the product allows "we will confirm by email"). After 30 s it goes half-open and lets a few probe requests through; success closes it.
3. Honour Retry-After and back off globally, not per request.
4. Failover to a second processor only for new payments. Never re-route a payment whose outcome at the first gateway is unknown: that is the textbook way to charge a customer twice. Unknown outcomes stay pending until the first gateway can answer.
5. Resolve unknowns asynchronously: a sweeper queries the status of pending charges by idempotency key or order reference once the gateway recovers, and the gateway's webhook is the other path to the truth.

Demonstrating why the key matters (runnable)

The program prints the jittered schedule for one charge, then simulates 10,000 orders against a fake gateway that creates the charge but loses the response 5% of the time. That lost response is exactly the "timeout after sending" case. Pinned seeds, standard library only.

import random

def backoff_schedule(max_attempts=4, base_ms=250, cap_ms=4000, seed=7):
    rng = random.Random(seed)
    waits = []
    for retry in range(1, max_attempts):          # waits happen between attempts
        ceiling = min(cap_ms, base_ms * 2 ** (retry - 1))
        waits.append((retry, ceiling, rng.randint(0, ceiling)))
    return waits

class FakeGateway:
    """Charges succeed on the gateway side, but the response is lost with prob p_lost."""
    def __init__(self, p_lost, seed):
        self.rng = random.Random(seed)
        self.p_lost = p_lost
        self.charges = []            # every charge actually created
        self.by_key = {}             # idempotency store
    def charge(self, order_id, key):
        if key is not None and key in self.by_key:
            result = self.by_key[key]           # replay stored result, no new charge
        else:
            self.charges.append(order_id)
            result = f"ch_{len(self.charges)}"
            if key is not None:
                self.by_key[key] = result
        if self.rng.random() < self.p_lost:
            raise TimeoutError                  # client never sees the result
        return result

def run(use_key, orders=10_000, p_lost=0.05, max_attempts=4, seed=42):
    gw = FakeGateway(p_lost, seed)
    unresolved = 0
    for i in range(orders):
        key = f"order-{i}" if use_key else None   # same key on every retry
        for attempt in range(max_attempts):
            try:
                gw.charge(i, key)
                break
            except TimeoutError:
                continue
        else:
            unresolved += 1
    dupes = len(gw.charges) - len(set(gw.charges))
    return len(gw.charges), dupes, unresolved

for r, ceiling, wait in backoff_schedule():
    print(f"retry {r}: ceiling {ceiling} ms, jittered wait {wait} ms")
for use_key in (False, True):
    total, dupes, unresolved = run(use_key)
    print(f"idempotency_key={use_key}: charges={total} duplicates={dupes} unresolved_after_4_attempts={unresolved}")

Output:

retry 1: ceiling 250 ms, jittered wait 82 ms
retry 2: ceiling 500 ms, jittered wait 485 ms
retry 3: ceiling 1000 ms, jittered wait 154 ms
idempotency_key=False: charges=10481 duplicates=481 unresolved_after_4_attempts=0
idempotency_key=True: charges=10000 duplicates=0 unresolved_after_4_attempts=0

Without a key, the same retry policy that makes the client resilient created 481 duplicate charges (the expected value is about 10,000 x (0.05 + 0.05^2 + 0.05^3), roughly 526, with a standard deviation across 10,000 independent orders of about 23.5 duplicates; this seeded run's 481 is about 1.9 standard deviations below that mean, unsurprising sampling noise for one pinned seed, not a bug). With the key, the gateway replays the stored result and the customer is charged once. The retry policy and the idempotency key are one design, not two.

Test cases

Run 1, 8 and 9 in CI (continuous integration) against a fault-injecting fake gateway; run 2 and 9 periodically in staging against the provider's test mode through a latency-injecting proxy, since sandbox latency is not production latency.

Trade-offs and pitfalls

• Longer timeouts vs more unknowns: a short read timeout makes the client feel responsive but manufactures "unknown" outcomes that later turn out to have succeeded; for authorizations, prefer a longer timeout and fewer attempts.
• Retrying at several layers (load balancer, SDK, application, job queue) multiplies attempts: 3 layers x 3 retries is 27 calls. Retry in one layer.
• Jitter without a budget spreads the storm in time but does not shrink it.
• Treating "unknown" as "failed" and asking the customer to try again with a new card is how customers get charged twice by the business itself.
• What would change the policy: for background jobs (subscription renewals) there is no human waiting, so use fewer immediate retries and a longer, scheduled retry over hours or days; for a gateway that has no idempotency support, drop automatic retries on unknown outcomes entirely and always query status first.

Direct answer

Two-phase commit (2PC; XA is the specific standard, dating to X/Open, that lets databases and message brokers act as coordinated participants in a 2PC transaction) cannot span the systems a payment touches: the card network and the PSP (payment service provider) will never join your transaction coordinator, and a capture that has reached a bank cannot be "rolled back", only reversed with a new money movement. So the real choice is how to build a saga: a sequence of local transactions, each committed on its own, where every step has a compensating action (a new transaction that semantically undoes it, such as a void or refund) instead of a rollback. Use an orchestrated saga with a durable state machine per payment, idempotent steps, a transactional outbox, and an append-only double-entry ledger; model refunds and chargebacks as new sagas that post new entries, never as edits to old ones.

2PC/XA versus sagas for this flow

Two-phase commit: a coordinator asks every participant "can you commit?" (phase 1, participants lock their rows and vote), then tells all of them "commit" or "abort" (phase 2).

2PC is still reasonable inside one database or between two databases you own (say, the payments table and the ledger in the same Postgres cluster (one PostgreSQL database installation): just use one local transaction). It fails for the cross-company, multi-day shape of a payment.

The saga

stateDiagram-v2
    [*] --> Authorized: authorize
    Authorized --> Captured: capture
    Authorized --> Voided: fulfilment fails / timeout
    Captured --> Booked: ledger posting
    Booked --> Fulfilled: fulfilment ok
    Booked --> Settled: PSP deposits funds (async, hours to days later)
    Booked --> Refunding: fulfilment fails
    Refunding --> Refunded: refund settled
    Fulfilled --> PartiallyRefunded: partial refund saga
    Fulfilled --> Disputed: chargeback arrives
    PartiallyRefunded --> Disputed: chargeback arrives

Steps and compensations:

Ordering to minimize inconsistent states, reconciled with the table and diagram above. The state machine and step table run Authorize, Capture, Ledger update, then Fulfilment, and that is the order this design recommends: put the cheapest-to-compensate steps first and the irreversible ones last. Concretely, split Fulfilment into its two halves and run the free half early: reserve inventory (the first half of step 4) before capture, so that if stock is gone you only void the authorization (free, step 1's compensation) instead of refunding a completed capture (fees lost, customer annoyed); only once the reservation confirms stock does the saga proceed to Capture, the Ledger update, and finally Fulfilment's second half, shipping. An alternative some businesses use instead, capture on ship (Authorize, reserve inventory, ship, then Capture, then Ledger update), delays charging the customer longer but risks the card expiring or being declined between order and ship, and it changes Capture's compensation from a refund to a void, since nothing was captured until shipping. This design's table and diagram assume the first pattern, capture at order time; capture-on-ship would reorder the Capture and Fulfilment rows and change Capture's compensation accordingly.

Making each step safe

1. Durable orchestrator. One state row per payment (state, version, next_action, deadline), updated with a compare-and-set (an atomic "update this row only if its version still equals the value I last read" operation) on version, so two orchestrator workers cannot both advance it. A workflow engine such as Temporal (durable execution: the workflow's history is persisted, so code resumes after a crash) is a good fit; a state table plus a job scheduler also works.
2. Idempotency keys on every external call (a value attached to a request so that repeating it with the same key returns the original result instead of repeating its effect), derived deterministically: payment_id + step + attempt_group (attempt_group identifies one logical attempt at a step: the orchestrator's own automatic retries of that same attempt, for example three network retries of one capture call, reuse the same attempt_group and so share one idempotency key and collapse into one PSP-side effect, while a fresh attempt started for a different reason, such as retrying capture after fixing a different bug, gets a new attempt_group and therefore a new key). A retried capture with the same key returns the original result from the PSP instead of capturing twice.
3. Transactional outbox. When a service updates its own table, it writes the event it needs to publish into an outbox table in the same local transaction; a relay publishes from the outbox. This removes the "committed but never announced" gap (the dual-write problem).
4. Unknown outcomes are a state. A PSP call that times out is capture_unknown, not failed. The orchestrator queries the PSP by idempotency key before deciding to retry or compensate. Compensating on a timeout is how you refund money that was never captured, or capture twice.
5. Compensations must themselves be idempotent and retried until they succeed; a refund that fails is escalated to a human queue, never silently dropped.

Partial refunds

A refund is a new saga against the payment, not an edit. Rules the refund saga enforces:

• Refundable amount = captured minus refunds already succeeded or in flight. Checked under the payment row lock so two concurrent partial refunds cannot exceed the total.
• Each refund has its own ID and idempotency key and its own states (requested → submitted → succeeded | failed).
• Ledger entries post when the PSP confirms, and reverse the right accounts: merchant revenue, and a platform fee if your policy returns it.

Late-arriving chargebacks

A chargeback is a reversal forced by the cardholder's bank; it can arrive weeks or months after the sale, after refunds, after the merchant was paid out. Handle it as a separate dispute saga keyed by the original payment:

1. Receive the dispute notification (webhook or dispute file); idempotent on the network's dispute ID.
2. Post ledger entries immediately: move the disputed amount (plus the dispute fee) from the merchant's balance to a disputes_held account. If the merchant's balance is already paid out, it goes negative and is recovered from future sales or a reserve.
3. Check for overlap with refunds: a chargeback for money you already refunded is contestable ("credit already processed"), so submit that evidence automatically.
4. Evidence deadline timer; on win, reverse the held entries; on loss, move them to a loss account.

Worked example

A 100.00 order. Each line is one balanced transaction: it debits one account and credits another by the same amount. "Merchant payable" is what the platform owes the merchant (a credit increases it, a debit reduces it); "PSP receivable" is what the PSP owes the platform, and it is cleared by an explicit Settlement entry once the PSP actually deposits the funds, which is what funds the Bank account below.

After the chargeback the merchant payable balance is 100 − 30 − 70 − 115 = −115.00: the merchant owes the platform 115.00 (the 100.00 disputed sale plus the 15.00 dispute fee they bear per policy). The system automatically submits evidence that 30.00 was already refunded; if the issuer accepts it, 30.00 comes back (debit PSP receivable, credit Merchant payable 30.00), leaving the merchant at −85.00, which exactly matches the 70.00 they actually received for a sale the cardholder successfully disputed, plus the 15.00 dispute fee. Every intermediate state is explainable from the entries, and nothing was edited.

A note on Settlement and the account balances, since two of them go negative and neither is an error: once Settlement moves the captured funds into Bank, PSP receivable returns to zero if nothing else happens. A refund or a chargeback that happens AFTER settlement, as both do here, pushes PSP receivable negative, because that money has already left PSP receivable and landed in Bank; the negative balance is what the PSP will net out of its next settlement to the platform (or, absent one in time, an amount the platform must wire back). Bank itself never goes negative in this example: Settlement funds it with 100.00 before any of that money is paid out (70.00) or clawed back, which is exactly why Settlement has to be an explicit step even though the question only names it in passing. Without it, the "Merchant paid out" line would debit Bank with no deposit ever having credited it.

Trade-offs and pitfalls

• Sagas expose intermediate states. A customer may see "charged" before "order confirmed". Design the UI and support tooling around explicit states rather than pretending to atomicity.
• Choreography versus orchestration. Choreography (each service reacts to events, no central brain) looks decoupled but scatters the payment's logic across services; for money, one orchestrator you can query per payment wins.
• Semantic, not exact, undo. A refund is not the inverse of a capture: fees may not come back, currency rates may have moved. Model those differences as explicit ledger lines.
• Reconciliation is the safety net. A daily job compares the PSP's settlement report with your ledger by payment ID; every mismatch is a saga that got stuck or a step your system never saw.

Direct answer

Separate the money side (subscription lifecycle, charged through a billing gateway) from the entitlement side (the fast yes/no "is this customer a member right now, and which perks apply?"). A Subscription service owns the lifecycle as a state machine and is the source of truth; every change emits an event that updates a read-optimised entitlement store and cache, so checkout reads entitlement in single-digit milliseconds. Every perk actually granted is recorded on the order as a benefit event with the entitlement version it relied on, so a nightly job can reconcile benefits consumed against what the billing ledger (an append-only record of every money movement) says each member paid. Money-affecting decisions are re-checked authoritatively at order placement, and driver and merchant apps never decide entitlement themselves; they only display a signed decision already made by the server.

Requirements and scale (assumed, so the arithmetic can be checked)

• A membership (monthly or annual) waives delivery or service fees on eligible orders above a minimum basket; free trials; cancellations; failed renewals.
• Load: 10 million orders/day, each checking entitlement about 3 times (browse badge, cart quote, checkout) = 30 million checks/day, which is 30,000,000 / 86,400 = about 350 checks/second on average and about 1,050/second at a 3x dinner peak.
• Target: p99 (the 99th-percentile latency: 99 of every 100 checks faster than this) under 10 ms for the check itself, because it sits inside the quote and checkout calls.
• 5 million members at about 200 bytes of entitlement state each is about 1 GB: small enough to hold entirely in memory in a replicated cache.

Architecture

flowchart LR
    BG[Billing gateway] -->|webhooks| SUB[Subscription service]
    SUB --> SDB[(Subscription DB + ledger)]
    SUB -->|outbox events| BUS[Event stream]
    BUS --> ENT[Entitlement projector]
    ENT --> CACHE[(Entitlement cache)]
    CO[Checkout and pricing] -->|check| CACHE
    CO -->|miss or stale| SUB
    CO --> ORD[(Orders + benefit events)]
    ORD --> REC[Usage vs ledger reconciliation]
    SDB --> REC
    CO -->|signed decision| APPS[Driver and merchant apps]

The "outbox events" arrow above is the outbox pattern: a state change is written to a table row in the same database transaction as the subscription update, and a separate publisher reads that table and emits the event, so a crash between updating the subscription and publishing the event can never happen.

Subscription lifecycle and billing-gateway integration

States: TRIAL -> ACTIVE -> PAST_DUE -> (ACTIVE | CANCELED), plus CANCEL_AT_PERIOD_END (still active until the paid period ends) and PAUSED.

• Renewals are driven by the gateway's billing engine or our scheduler, but either way the gateway's events (invoice paid, payment failed, subscription updated) arrive by webhook (an HTTP callback the gateway sends when something changes). The Subscription service verifies the signature, dedupes by event ID, and re-reads the subscription from the gateway before changing state, because webhooks can arrive duplicated and out of order.
• Idempotency: calls we make to the gateway (create subscription, cancel, change plan) carry an idempotency key (an identifier the gateway uses to recognize a retried request and return the original result instead of repeating the action) derived from our own command ID, so a retried request cannot create a second subscription or a second charge.
• Failed renewal (dunning): the process of retrying a failed recurring payment and reminding the customer. The subscription goes PAST_DUE with a grace period (say 3 days) during which perks continue, while the gateway retries the card on a schedule. After grace, perks stop; after the final retry, CANCELED.
• Ledger: each paid invoice is posted as double-entry lines (debits and credits that sum to zero): cash in, and deferred revenue, a liability released day by day over the period the member has paid for. That daily release is what reconciliation compares benefits against.
• Multiple gateways (for example card processor plus app-store billing, Apple's or Google's own in-app purchase system, which handles the charge and takes its own cut, for in-app purchases) sit behind an adapter interface; the Subscription service's state machine is the same whichever gateway produced the event.

Entitlement checks with low latency

• The entitlement projector turns subscription events into a flat record: member_id, plan, perks[], valid_until, grace_until, version, updated_at (the timestamp this record was last refreshed). It writes it to a replicated in-memory cache (and a durable table behind it). A separate projection exists because the Subscription DB is built for correctness and low-volume writes, not for a few hundred low-latency reads per second sitting inside every checkout call: querying it directly for every check would put checkout's tail latency at the mercy of the billing database's own load, joins and lock contention, and would miss the sub-10 ms p99 budget below.
• Checkout reads the cache: a single key lookup, well inside the 10 ms budget. The decision is computed from valid_until against the server clock, so an expired membership stops granting perks at the right moment even if an event is delayed.
• Two consistency levels, chosen by what is at stake:
  • Browse and cart quote: the cached record is enough; showing "free delivery" a few seconds after a cancellation is harmless.
  • Order placement, where the fee is actually waived: if the cached record's updated_at is older than a freshness bound (say 60 seconds), meaning the projector has not refreshed it recently and may be behind, or it is a cache miss, read the Subscription DB directly. Concretely: of the roughly 350 checks/second average (1,050 at peak), about a third are order-placement checks (the "checkout" leg of the three checks per order), so roughly 117/s average and 350/s at peak. Under healthy operation the projector's lag stays well under the 60-second bound, so only a small share of those, cache misses and the rare record whose refresh has genuinely fallen behind, actually read the Subscription DB; even a conservative 5% miss-or-stale rate is only about 6/s average and 17/s at peak, comfortably tens, not thousands.
• Read-your-writes after sign-up: when a customer joins and immediately orders, checkout passes the version returned by the sign-up call; if the cache is behind that version, it reads the source. This removes the "I just subscribed and was still charged a fee" bug.
• If the cache is unavailable, fall back to the source behind a circuit breaker (a guard that stops calling a failing dependency for a cool-down period so it is not overwhelmed); if both are down, fail open for display (show normal fees) and closed for money (charge normal fees and refund the difference later if the member was entitled). A small refund is cheaper than giving free perks to everyone during an outage.

Perk enforcement

• Perks are rules evaluated at pricing, not flags scattered across services: "delivery fee = 0 if member and basket at least 15.00 and merchant participates". The pricing service writes the result on the order as a benefit event: order_id, member_id, perk, amount_waived, entitlement_version, decided_at.
• Merchant participation and regional rules are part of the rule's inputs, so a non-participating merchant or region simply does not qualify.

Offline behaviour on driver and merchant apps

Drivers and merchants do not decide entitlement; they need to show and honour what was decided. So:

• The server attaches a signed decision to the order at placement (for example a compact token signed with the platform's private key, containing order ID, perks applied and amount waived). Apps verify the signature with the public key they already have, offline, and display it.
• If a driver's app is offline, it keeps working from the last-synced order payload; nothing it does changes what the customer is charged, because the fee was fixed on the server at placement.
• Actions taken offline (merchant marks a perk item as provided, driver completes delivery) are queued locally with timestamps and idempotency keys and synced when back online; the server applies them once.
• What the apps must never do: cache a "this customer is a member" flag and grant perks to new orders from it. That turns every offline device into an entitlement authority.

Reconciliation between usage and the billing ledger

A nightly job joins benefit events with the subscription ledger, per member-day:

1. Every waived fee has a covering entitlement: a benefit event is valid only if the member was ACTIVE, TRIAL or within grace at decided_at. Violations are counted by cause (stale cache, bug, fraud).
2. Every paid period has a subscription state: invoices paid without an active subscription, or active subscriptions without a paid invoice past grace, are breaks.
3. Economics per cohort: fees waived vs deferred revenue recognised, for product and finance.
4. Grace exposure is measured, not guessed: if 2% of 5 million members fail renewal in a month (100,000), and each orders 0.2 times per day during a 3-day grace with a 2.99 fee waived, grace costs 100,000 x 0.2 x 3 x 2.99 = 179,400 in waived fees per month. That number decides whether a 3-day grace is worth its recovered renewals.

Preventing abuse

Trade-offs and pitfalls

• Calling the billing gateway on every check puts an external dependency and its latency on the checkout path; the gateway is the system of record for payments, not a query service for entitlement.
• Cache-only entitlement with long TTLs (time-to-live, how long a cached value is used) lets cancelled members keep perks; use event-driven updates, validity timestamps and an authoritative check at placement.
• Ignoring out-of-order webhooks can resurrect a cancelled subscription when a late "invoice paid" arrives; re-read the subscription before transitioning.
• Not recording the entitlement version on the order makes reconciliation guesswork.
• What would change the design: at a few thousand members, drop the projector and cache and query the Subscription DB directly (a primary-key read is fast enough); if perks become usage-limited (for example 5 free deliveries a month), entitlement becomes a counter that must be decremented atomically at placement, which needs a stronger consistency path than a cached flag.

Direct answer

Keep the operational tables the question sketches (orders, payouts, adjustments) but make an append-only double-entry ledger the source of truth for money: every order, refund, dispute and correction becomes a balanced set of ledger entries (credits and debits that sum to zero), and a payout is simply "pay out the positive balance of this party's payable account as of the period end". The ETL (extract, transform, load) pipeline is incremental and idempotent: it reads order and adjustment changes, applies date-effective commission and tax rules, and posts entries with deterministic IDs so a rerun cannot double-count. Refunds, disputes and retroactive corrections never edit a past row or a paid payout; they post new entries in the current period, which is what makes the whole thing auditable.

Data model

Extending the sample schemas (amounts in integer minor units, the smallest whole subdivision of a currency, such as cents for USD, so 6000 means 60.00 dollars, suffixed _cents in the column names below, to avoid floating-point rounding):

orders(order_id, merchant_id, driver_id, jurisdiction, amount_cents,
       delivery_fee_cents, tax_cents, currency, status, created_at)
commission_rules(jurisdiction, rate_bps, effective_from, effective_to, rule_version)
tax_rules(jurisdiction, tax_type, rate_bps, collector{platform|merchant},
          effective_from, effective_to, rule_version)
adjustments(adjustment_id, order_id, party, amount_cents, reason,
            effective_date, created_at, created_by, approved_by)
disputes(dispute_id, order_id, amount_cents, status, opened_at, resolved_at)
ledger(entry_id, txn_id, account, amount_cents, source, posted_at,
       UNIQUE(txn_id, account))
payouts(payout_id, party, amount_cents, period_end, payout_date, status)
payout_lines(payout_id, txn_id)                 -- which entries a payout covered

• bps is basis points, hundredths of a percent: 1500 bps = 15%.
• Accounts are named per party: payable:m1 (owed to merchant m1), payable:d1 (owed to driver d1), revenue:commission, tax_payable:CA-ON (sales tax owed to the Ontario authority), customer_cash (a balancing account recording where the money for a transaction came from, not a real bank account) and bank_clearing (a balancing account recording that money has been handed off to the outside bank for an actual transfer, before that transfer settles).
• Sign convention: every ledger entry is a credit (posted positive) or a debit (posted negative), and one balanced transaction's entries always sum to zero. When an order is recognised, customer_cash is debited (negative) for the full amount the customer paid, because that same value is simultaneously credited (positive) into payable:*, revenue:commission and tax_payable:*; customer_cash is the balancing entry that makes one transaction close to zero, not a place where real money sits. A refund does the reverse: it credits customer_cash back up (positive) by the refunded amount, undoing part of the original debit, while debiting the responsible party's payable by the same amount to reduce what they are still owed.
• Date-effective rules with versions let you answer "which rate was used for this order, and which rate should have been".

Collector: platform-collected vs merchant-collected tax

The tax_rules.collector field (platform or merchant) decides who is legally responsible for remitting that jurisdiction's tax to the tax authority, and it changes what the ledger and the reports do:

• collector: platform (common where marketplace-facilitator law makes the platform responsible for tax on sales it facilitates): tax is posted to a platform-owned tax_payable:<jurisdiction> account, exactly as the CA-ON example above does, and the platform's own remittance report and filing cover it. The merchant never carries this tax as their liability.
• collector: merchant (the merchant remains the tax authority's registrant): the platform still collects the tax from the customer at checkout for cash-flow reasons, but it must pass that tax through to the merchant rather than keep it as a platform liability, because the merchant is the one who owes the tax authority and will file the return. The posting differs by one account: instead of crediting tax_payable:<jurisdiction>, credit payable:<merchant> for the tax amount too, so it flows into the merchant's own payout, and the report generator marks that line "collected on the merchant's behalf, remit yourself" rather than folding it into the platform's own remittance total.

Hand-computed illustration (not run; the CA-ON demo above stays single-jurisdiction, this shows the difference the collector field makes): a 50.00 order in a collector: merchant jurisdiction with a 10% commission and 8.00 tax, no driver fee for simplicity. Platform-collected posts customer_cash = -(50+8) = -58, payable:m = 50-5 = 45, revenue:commission = 5, tax_payable:jur = 8; sum = -58+45+5+8 = 0. Merchant-collected instead posts customer_cash = -58 (the customer still pays 58 at checkout either way), payable:m = 45+8 = 53 (the tax passes through into the merchant's own payout), revenue:commission = 5, and nothing at all to tax_payable:jur, because the platform never owes that 8.00 to the tax authority, the merchant does, out of the 53.00 they now hold; sum = -58+53+5 = 0. Both variants balance; only where the tax liability sits differs.

ETL pipeline

flowchart LR
  OLTP[(Order DB)] -->|CDC| RAW[(Raw change log)]
  RAW --> N[Normalize and validate]
  RULES[(Commission and tax rules)] --> C[Compute and post entries]
  N --> C
  ADJ[(Adjustments and disputes)] --> C
  C --> L[(Ledger)]
  L --> P[Payout run per period]
  L --> T[Tax remittance reports]
  L --> R[Reconciliation vs bank and payment provider]

The diagram's OLTP node (online transaction processing: the live application database that serves the app's everyday reads and writes, as opposed to an analytics warehouse) is the order database itself.

1. Extract: CDC (change-data-capture, streaming row changes from the order database's log) into a raw, immutable change log partitioned by date.
2. Normalize: validate currency, jurisdiction, required fields; quarantine bad rows rather than dropping them.
3. Compute and post: for each delivered order, look up the rule version effective on the order date and post one balanced transaction. The transaction ID is deterministic (order_id:v<rule_version>), and the ledger's unique key on (txn_id, account) makes posting idempotent.
4. Payout run: for each party with a positive payable balance as of the period end, create a payout and post payable → bank_clearing. Negative balances carry forward.
5. Reports: tax remittance (paying over the tax that was collected to the jurisdiction's tax authority, and filing the return that documents it) payable per jurisdiction per period, commission revenue, payout files for the bank.

Refunds, disputes and retroactive corrections

• Refund: an adjustments row plus ledger entries that debit the responsible party's payable. If the merchant was already paid for the order, their next payout is smaller, or their balance goes negative. Whether the platform returns its commission on a refund is a policy, expressed as an extra pair of entries.
• Dispute: on open, move the disputed amount to a held account; on resolution, post the final entries (release or loss). The dispute ID is the idempotency key. For example, suppose order o3 (merchant m2, 60.00 + 8.00 driver fee + 7.80 tax, recognised and corrected exactly as the demo below does it) is disputed three weeks after delivery, once m2 has already been paid the full 51.00 (6000 - 900 at the corrected 15% rate, in cents: payable:m2 is already 0 by then, per the demo's own printed balances). Opening the dispute posts post("dispute:o3-open", [("payable:m2", -5100), ("held:m2", 5100)]): payable:m2 goes to -5100 (m2 now owes the platform, to be recovered from future sales, exactly like the partial-refund case above), and a new held:m2 account holds the disputed funds separately so they cannot be paid out again mid-dispute. If the platform loses two weeks later: post("dispute:o3-lost", [("held:m2", -5100), ("customer_cash", 5100)]), releasing the hold back to the customer side of the ledger. If it had won instead, the reversing pair would be [("held:m2", -5100), ("payable:m2", 5100)], restoring m2's payable to exactly where it stood before the dispute opened.
• Retroactive correction: a rule was wrong (for example the Ontario commission was loaded as 20% when the contract says 15%). Add the corrected rule as a new version, compute the difference per affected order, and post correction entries dated today, referencing the original transaction. The paid payouts stay untouched; the difference flows into the next payout.

Worked example (runnable)

Three orders in Ontario, commission rule loaded wrongly at 20% then corrected to 15%, and a 10.00 partial refund charged to merchant m1 after week one was already paid:

import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE orders(order_id TEXT PRIMARY KEY, merchant_id TEXT, driver_id TEXT,
  jurisdiction TEXT, amount_cents INT, delivery_fee_cents INT, tax_cents INT,
  status TEXT, created_at TEXT);
CREATE TABLE commission_rules(jurisdiction TEXT, rate_bps INT,
  effective_from TEXT, effective_to TEXT, rule_version INT);
CREATE TABLE adjustments(adjustment_id TEXT PRIMARY KEY, order_id TEXT, party TEXT,
  amount_cents INT, reason TEXT, effective_date TEXT, created_at TEXT);
-- append-only double-entry ledger: credits positive, debits negative; every txn sums to zero
CREATE TABLE ledger(entry_id INTEGER PRIMARY KEY, txn_id TEXT, account TEXT,
  amount_cents INT, source TEXT, posted_at TEXT, UNIQUE(txn_id, account));
CREATE TABLE payouts(payout_id TEXT PRIMARY KEY, party TEXT, amount_cents INT,
  period_end TEXT, payout_date TEXT);
""")
db.executemany("INSERT INTO orders VALUES (?,?,?,?,?,?,?,?,?)", [
 ("o1","m1","d1","CA-ON",4000,600,520,"delivered","2026-09-01"),
 ("o2","m1","d1","CA-ON",2500,600,325,"delivered","2026-09-02"),
 ("o3","m2","d1","CA-ON",6000,800,780,"delivered","2026-09-03"),
])
# version 1 was loaded with the wrong rate (20%); version 2 corrects it to 15% retroactively
db.execute("INSERT INTO commission_rules VALUES ('CA-ON',2000,'2026-01-01','9999-12-31',1)")

def post(txn, legs, source, when):
    assert sum(a for _, a in legs) == 0, "unbalanced"
    for acct, amt in legs:  # INSERT OR IGNORE + UNIQUE key = idempotent rerun
        db.execute("INSERT OR IGNORE INTO ledger(txn_id,account,amount_cents,source,posted_at)"
                   " VALUES (?,?,?,?,?)", (txn, acct, amt, source, when))

def rate(jur, day, version):
    return db.execute("SELECT rate_bps FROM commission_rules WHERE jurisdiction=? AND ?"
      " BETWEEN effective_from AND effective_to AND rule_version=?", (jur, day, version)).fetchone()[0]

def recognise_orders(version, as_of):
    for oid, m, d, jur, amt, fee, tax, _, day in db.execute("SELECT * FROM orders").fetchall():
        comm = amt * rate(jur, day, version) // 10000
        post(f"{oid}:v{version}", [("customer_cash", -(amt+fee+tax)),
             (f"payable:{m}", amt-comm), ("revenue:commission", comm),
             (f"payable:{d}", fee), (f"tax_payable:{jur}", tax)], "orders", as_of)

def run_payouts(period_end, pay_date):
    rows = db.execute("SELECT account, SUM(amount_cents) FROM ledger WHERE account LIKE 'payable:%'"
                      " AND posted_at <= ? GROUP BY account", (period_end,)).fetchall()
    for acct, bal in rows:
        party = acct.split(":")[1]
        if bal > 0:
            pid = f"{party}:{period_end}"
            db.execute("INSERT OR IGNORE INTO payouts VALUES (?,?,?,?,?)", (pid, party, bal, period_end, pay_date))
            post(f"payout:{pid}", [(acct, -bal), ("bank_clearing", bal)], "payouts", pay_date)

recognise_orders(1, "2026-09-06")
recognise_orders(1, "2026-09-06")          # rerun of the same batch: no double count
run_payouts("2026-09-06", "2026-09-07")

# week 2: partial refund on o1 (merchant-fault, 1000c) and a retroactive rate correction
db.execute("INSERT INTO adjustments VALUES ('a1','o1','m1',-1000,'partial_refund','2026-09-09','2026-09-09')")
post("a1", [("payable:m1", -1000), ("customer_cash", 1000)], "adjustments", "2026-09-09")
db.execute("INSERT INTO commission_rules VALUES ('CA-ON',1500,'2026-01-01','9999-12-31',2)")
for oid, jur, amt, day, m in db.execute("SELECT order_id,jurisdiction,amount_cents,created_at,merchant_id FROM orders").fetchall():
    delta = amt * (rate(jur, day, 1) - rate(jur, day, 2)) // 10000   # commission over-charged
    post(f"{oid}:rate-v1-to-v2", [("revenue:commission", -delta), (f"payable:{m}", delta)], "correction", "2026-09-10")
run_payouts("2026-09-13", "2026-09-14")

for r in db.execute("SELECT payout_id, amount_cents FROM payouts ORDER BY payout_date, party"):
    print("payout", r[0], r[1])
for acct, bal in db.execute("SELECT account, SUM(amount_cents) FROM ledger WHERE account LIKE 'payable:%' GROUP BY account ORDER BY account"):
    print("balance", acct, bal)
print("ledger sums to", db.execute("SELECT SUM(amount_cents) FROM ledger").fetchone()[0])
print("commission revenue", db.execute("SELECT SUM(amount_cents) FROM ledger WHERE account='revenue:commission'").fetchone()[0])
print("tax owed CA-ON", db.execute("SELECT SUM(amount_cents) FROM ledger WHERE account='tax_payable:CA-ON'").fetchone()[0])

Output:

payout d1:2026-09-06 2000
payout m1:2026-09-06 5200
payout m2:2026-09-06 4800
payout m2:2026-09-13 300
balance payable:d1 0
balance payable:m1 -675
balance payable:m2 0
ledger sums to 0
commission revenue 1875
tax owed CA-ON 1625

Checking the numbers by hand:

• Week 1, at the wrong 20%: m1 gets (4000 − 800) + (2500 − 500) = 5200; m2 gets 6000 − 1200 = 4800; driver d1 gets the delivery fees 600 + 600 + 800 = 2000.
• Correction: the over-charge is 5% of each order, 200 + 125 = 325 back to m1 and 300 back to m2. Commission revenue ends at 2500 − 625 = 1875, which is exactly 15% of 12,500.
• m1 also has the 1000 refund, so m1's balance is 325 − 1000 = −675: nothing is paid and the debt carries forward to the next period. m2 is paid 300.
• Tax payable is 520 + 325 + 780 = 1625, unaffected by commission changes.
• The ledger sums to zero, and running the week-1 batch twice changed nothing.

Auditability

• Nothing is updated or deleted in the ledger; every entry carries source, the rule version, and (for adjustments) who created and who approved it.
• payout_lines ties each payout to the entries it paid, so any payout can be re-derived.
• Balances "as of" any date are a query (posted_at <= date), so you can reproduce a statement exactly as it was issued.
• Daily checks: ledger sum is zero; payouts sent match bank confirmations; tax payable per jurisdiction matches filed returns.

Trade-offs and pitfalls

• Recomputing history in place (rerunning last month's ETL with the new rate and overwriting payouts) is the common wrong turn: statements already sent no longer match the database and the audit trail is gone.
• Floats for money produce cent-level drift; use integer minor units and a stated rounding rule per jurisdiction.
• Multi-currency: post each entry in its own currency and record FX (foreign exchange) conversions as explicit transactions, never as a silently converted amount.
• Warehouse versus ledger: the analytics warehouse can be rebuilt from the ledger; the reverse is not true. Payouts must be driven from the ledger, not from a warehouse table.

Direct answer

Model money as a double-entry, append-only journal and keep a per-account balance row that is updated in the same database transaction as the journal entries, using a conditional update ("subtract only if the balance stays non-negative") so two concurrent debits can never both spend the same funds. Shard by merchant so each merchant's accounts live together and most transactions stay on one shard; split the platform's own hot accounts (shared balances, such as a fee-collection account, that nearly every transaction touches) per shard, and split very busy merchants into several sub-balances. Idempotency comes from a unique transaction ID stored in the same transaction, correctness is proved continuously by reconciliation, and reporting reads an eventually consistent copy that is never used to decide whether money can move.

Key terms

• Double-spend: the same funds being spent twice, for example two refunds that both see a balance of 1,000 and both succeed, taking the merchant to -200.
• Ledger / journal: the record of money movements. Double-entry means each movement is at least two lines (a debit on one account, an equal credit on another) summing to zero, so money is never created or destroyed by a bug without the books showing it.
• Idempotency: repeating the same request has the same effect as doing it once.
• Reconciliation: checking that internal records agree with each other and with processors (the external companies that route card authorizations to the networks and later move the settled money) and banks.
• Shard: one partition of the data held on its own database node.

Data model

• account(id, merchant_id, type, available, pending, version): one row per balance, for example merchant:42:available.
• txn(txn_id PRIMARY KEY, kind, created_at, ...): one row per business movement. The primary key is the idempotency guard.
• entry(txn_id, account_id, amount, ...): append-only; never updated or deleted. Corrections are new reversing transactions.
• Invariants: entries of every transaction sum to zero; each account's balance equals its opening balance plus the sum of its entries; available balance never goes below zero (or below an agreed negative limit).

Separate pending from available: a captured payment (one where the merchant has confirmed it wants the authorized funds, rather than a hold that might still expire unused) credits pending; it moves to available when settlement funds arrive (the days-later bank transfer that actually moves the money, distinct from the instant authorization hold). Refunds and payouts debit available only, which is what prevents refunding money the platform has not yet received.

Concurrency control

The balance check and the write must be one atomic step. Reading the balance, deciding in application code, then writing is the classic race: both requests read 1,000 and both write. Three correct options, differing in when they lock: pessimistic locking takes the lock before deciding, so every other writer waits; optimistic locking decides first, and only fails at write time if someone else got there first.

When a transaction touches several accounts, lock them in a fixed order (for example sorted by account ID) so two transactions cannot deadlock waiting on each other.

Runnable demonstration

This uses SQLite so it runs anywhere; the same statements work on PostgreSQL. One refund of 30 runs first, then 50 new refunds of 30 race each other alongside 10 client retries of the first refund.

import sqlite3, threading, os

DB = "/tmp/ledger-demo.db"
if os.path.exists(DB):
    os.remove(DB)

setup = sqlite3.connect(DB)
setup.executescript("""
CREATE TABLE account (id TEXT PRIMARY KEY, available INTEGER NOT NULL,
                      version INTEGER NOT NULL DEFAULT 0);
CREATE TABLE entry (id INTEGER PRIMARY KEY, txn_id TEXT NOT NULL,
                    account_id TEXT NOT NULL, amount INTEGER NOT NULL);
CREATE TABLE txn (txn_id TEXT PRIMARY KEY, kind TEXT NOT NULL);
INSERT INTO account VALUES ('merchant:42', 1000, 0), ('platform:refund_clearing', 0, 0);
""")
setup.commit()
setup.close()

def refund(txn_id, amount):
    """Debit the merchant, credit refund clearing, atomically and idempotently."""
    conn = sqlite3.connect(DB, timeout=30, isolation_level=None)
    try:
        # BEGIN IMMEDIATE grabs SQLite's write lock right away, instead of the default
        # behavior of only upgrading to a write lock at the first actual write; that
        # closes the gap where two transactions could both read the balance before
        # either one writes, which is exactly the race this function must prevent.
        conn.execute("BEGIN IMMEDIATE")
        try:
            conn.execute("INSERT INTO txn VALUES (?, 'refund')", (txn_id,))
        except sqlite3.IntegrityError:
            conn.execute("ROLLBACK")
            return "duplicate"
        cur = conn.execute(
            "UPDATE account SET available = available - ?, version = version + 1 "
            "WHERE id = 'merchant:42' AND available >= ?", (amount, amount))
        if cur.rowcount == 0:
            conn.execute("ROLLBACK")
            return "insufficient_funds"
        conn.execute("UPDATE account SET available = available + ? "
                     "WHERE id = 'platform:refund_clearing'", (amount,))
        conn.executemany("INSERT INTO entry (txn_id, account_id, amount) VALUES (?,?,?)",
                         [(txn_id, 'merchant:42', -amount),
                          (txn_id, 'platform:refund_clearing', amount)])
        conn.execute("COMMIT")
        return "ok"
    finally:
        conn.close()

print("first refund:", refund("refund-first", 30))   # balance 1000 -> 970

results = []
lock = threading.Lock()
def worker(txn_id):
    r = refund(txn_id, 30)
    with lock:
        results.append(r)

# 50 new refunds of 30 race each other and 10 client retries of the first refund.
threads = [threading.Thread(target=worker, args=(f"refund-{i}",)) for i in range(50)]
threads += [threading.Thread(target=worker, args=("refund-first",)) for _ in range(10)]
for t in threads: t.start()
for t in threads: t.join()

conn = sqlite3.connect(DB)
print("ok:", results.count("ok"),
      "insufficient_funds:", results.count("insufficient_funds"),
      "duplicate:", results.count("duplicate"))
print("merchant balance:", conn.execute(
    "SELECT available FROM account WHERE id='merchant:42'").fetchone()[0])
print("entries sum to zero:", conn.execute("SELECT SUM(amount) FROM entry").fetchone()[0] == 0)
print("balance equals 1000 + sum of merchant entries:", conn.execute(
    "SELECT 1000 + SUM(amount) FROM entry WHERE account_id='merchant:42'").fetchone()[0])

Output (identical on every run, because the outcome does not depend on thread scheduling):

first refund: ok
ok: 32 insufficient_funds: 18 duplicate: 10
merchant balance: 10
entries sum to zero: True
balance equals 1000 + sum of merchant entries: 10

After the first refund the balance is 970, which covers exactly 32 more refunds of 30 (970 / 30 = 32.3). All 10 retries are rejected by the txn primary key, so no refund applies twice, and no interleaving pushes the balance below zero.

Partitioning and sharding

• Shard key: merchant ID. All of a merchant's accounts sit on one shard, so the common transaction (debit merchant, credit something) commits on one node with a local transaction.
• Platform accounts are the trap. Every refund credits a platform "refund clearing" account; if that is one row, every shard's transactions contend on it and every transaction becomes cross-shard (touching accounts that live on two different shards, which cannot be updated together in one local, atomic transaction). Instead, give each shard its own platform sub-account (platform:refund_clearing:shard-17) and sum them for reporting.
• Hot merchants. A single row is updated one transaction at a time. If each update holds the row lock for about 2 ms (an assumption to measure), one row supports at most

A marketplace or large merchant above that gets its balance split into N sub-balances (say 16, for a ceiling near 8,000 updates/s under the same assumption). Credits go to a sub-balance chosen by hash; a debit takes from one sub-balance that has enough, and a background job rebalances. The cost: a large debit may need to gather from several sub-balances in one transaction.

Idempotency

• Every movement carries a txn_id derived from the business event (for example refund:re_881), not generated fresh on each attempt, so a retry produces the same ID.
• The txn insert, the balance updates and the entries commit together. A retry either finds the row (duplicate, return the original result) or finds nothing (the first attempt never committed, safe to apply).
• Events from upstream (payment captured, refund requested) are consumed at-least-once (a message may be delivered and processed more than once after a crash or retry, but is never silently dropped); the unique txn_id makes the consumer safe to re-run.

Eventual consistency for non-financial services

Journal entries are published through an outbox (an events table written in the same transaction as the entries) or through change-data-capture (reading the database's commit log) to a stream (a continuously flowing feed of events that other services subscribe to, instead of querying the database directly), which feeds the reporting warehouse, merchant dashboards and search. These can lag seconds or minutes. The rule: decisions that move money read the ledger primary; everything else may read the copy. A dashboard may show a balance a few seconds old; a payout job may not.

Reconciliation

• Internal, continuous: per transaction, entries sum to zero; per account, the stored balance equals the sum of its entries (the demo's last two lines are this check). Run it per shard in rolling windows and page on any difference.
• External, daily: processor settlement reports and bank statements are matched to ledger entries; unmatched items become tracked cases for finance.

Scaling to millions of merchants

Assume 2 million merchants and 50 million money movements per day, each written as 2 entries of about 200 bytes, with a peak 5 times the average:

If one shard comfortably sustains 2,000 entry writes per second (again, measure yours), peak needs 3 shards; choose 16 so each runs well below its limit and there is room for growth. Keep recent entries hot and move closed months to cheaper storage while keeping them queryable for audit.

Trade-offs and pitfalls

• Balance column vs computing from entries. Summing entries on every check is always correct but slows as history grows; the cached balance is fast but must be updated in the same transaction and verified by reconciliation.
• Cross-shard transfers (merchant to merchant on different shards) need a two-step flow through a clearing account with idempotent steps, not a distributed lock.
• Never update or delete entries. An "edit" destroys the audit trail; post a reversing transaction instead.
• Floating point for money causes rounding drift; use integer minor units (cents) plus a currency code.
• Reading balances from a replica to approve a refund reintroduces double-spend through replication lag.