Payment System Design: Ledgers, Idempotency and Reconciliation

Money goes missing on paper when the app treats a slow bank reply as a failure, records one bank message twice, or edits balances in place. Each shortcut works on quiet days and breaks on the busiest one. Payment system design is the work of closing those gaps.

Picture a common setup. A payments company lets about 42,000 shops in Ahmedabad take UPI payments, India's bank-to-bank payments from a phone, through a QR code at the counter. The setup is made up, but the problems are real.

Customers scan the QR with any UPI app, and the company pays the shops the next morning. The first version had one payments table with a status column, and one balance number per merchant. When the bank said a payment worked, the code added the amount to that balance.

Then came the last night of Navratri, a nine-night festival when shops stay open late. Clothes shops and sweet shops took about 9 lakh (900,000) payments in one evening. The partner bank, which holds the company's account, slowed down, and many replies took over a minute.

Three things went wrong.

• The shop screen showed failed after 30 seconds of silence. Shopkeepers asked customers to pay again. Later the first payment went through too, so 214 people paid twice.
• The bank sent some success messages twice. The code added the amount twice, so some merchant balances grew by money that never arrived.
• The next morning, the company's records said it owed merchants Rs 61.4 lakh (6.14 million rupees). The money actually sitting in its bank account was Rs 61.02 lakh.

The reconciliation team, the people who match the company's records with the bank's, spent two days with spreadsheets hunting the Rs 38,000 gap. Payouts were paused, and shopkeepers called all day. This guide rebuilds the system so that night cannot repeat.

You need six words. Each one comes with a small example from the same QR payments company.

• UPI. The Unified Payments Interface is India's system for sending money between bank accounts from a phone. Every payment carries a unique reference number, called the UPI reference in this guide.
• NPCI. The National Payments Corporation of India runs UPI's central switch. The switch passes each payment between the customer's bank and the receiver's bank.
• Escrow account. A bank account that holds money on behalf of others. Customers pay into the company's escrow account, but the money inside belongs to the merchants.
• Ledger. The book of record for money. It says who owes what to whom, and exactly how each balance got there.
• Reconciliation. Matching your own records against an outside record, such as the bank statement, line by line, and explaining every difference.
• Settlement. Actually paying out what is owed. Here, that means sending each merchant their money from the escrow account.

Payment system design is planning the services, data and checks that move money between people and keep an exact, provable record of it. Speed matters, but being correct matters far more.

Most systems can be a little wrong for a moment. A social feed that shows an old post is fine. A payment system cannot lose one rupee, invent one, or count one twice.

Every rupee must be explainable months later to an auditor, a person who checks the books for owners and regulators. Before drawing any boxes, the team wrote down what the new system must do.

• Accept UPI payments to merchants and announce them within seconds on the small speaker box at the counter.
• Never mark a payment failed just because the bank is slow.
• Record each payment exactly once, even when the bank's message arrives twice.
• Keep the full history of every money movement, with nothing edited or deleted.
• Match the company's records against the bank's every day, and explain each difference.
• Pay merchants the next morning, but only money that has been matched.

Now check the load. Nine lakh payments in about four hours is 900,000 divided by 14,400 seconds, or 62.5 payments per second on average. Bursts run higher, but one well-run relational database, which stores data in tables, handles this. The hard part is correctness, not speed.

A UPI payment goes from the customer's UPI app to the NPCI switch. Strictly, the app reaches the switch through its own payment service provider (PSP) bank. The switch asks the customer's bank to debit (take out) the money, and the company's partner bank to credit (add) it. The partner bank then tells the company, and the company tells the shop.

Follow the boxes in order. Each note says who talks to whom. Notice that the company hears about the payment near the very end, and that any message on the way can be slow or lost.

The part that confuses beginners is the gap between the debit and the company hearing about it. The customer's money may already have left their account while the company still knows nothing. The worst Navratri problem, people paying twice, started inside that gap.

So the team never trusts a success message alone. The bank statement, checked later, proves the money really arrived.

A double-entry ledger records every money movement as an entry with at least two lines: one takes money from an account and one adds it to another. The lines always add up to zero, so money is moved, never made or lost.

The old system kept one balance number per merchant and changed it in place. When that number was wrong, nobody could tell how it got there. A ledger keeps the movements themselves, so any balance can be rebuilt by adding them up.

Think of hundi counting day at a temple, when the donation box is opened. The notes are counted, and each bundle goes into a bag with a slip and a seal. Nobody reopens a sealed bag to change its slip. If a bag was wrong, the temple writes a new correction slip, and both stay on file.

Here is how the picture maps. A sealed bag with its slip is a ledger entry. The rule against reopening bags is the append-only rule. The correction slip is a reversing entry, a new entry that cancels a wrong one.

Where the picture breaks: the temple counts once a day, by hand. A ledger posts each entry the moment it happens, and the computer refuses any entry whose lines do not add up to zero.

Append-only means you can add new entries, but never edit or delete old ones. Stripe's engineers describe their own ledger the same way, as an immutable log of events, in a February 2024 post. A mistake there is fixed by reverting and reprocessing earlier operations, not by editing them.

The ledger writes what the company owes as a minus number. So a merchant account at minus Rs 1,250 means the company owes that shop Rs 1,250. Here is one payment's whole life in the ledger.

Watch upi_receivable. It goes up when the bank says success and comes back to zero when the statement agrees. An account like this is called a clearing account: it holds money that is in the middle of a move. If it does not return to zero, something is stuck.

Stripe uses the same idea at a far larger scale. In that February 2024 post it reported about five billion ledger events a day. A clearing balance left over at steady state points its engineers to a missing, late or wrong record.

Check that the lines add up to zero, save all lines in one database transaction, and give the entry an id that a repeat cannot reuse. A transaction is a group of writes that the database saves completely or not at all.

Here is a runnable version using SQLite, the small database built into Python. Amounts are whole paise, the hundredth part of a rupee, so Rs 1,250 is stored as 125000.

import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE entries (id TEXT PRIMARY KEY);
CREATE TABLE lines (entry_id TEXT NOT NULL REFERENCES entries(id),
                    account TEXT NOT NULL, paise INTEGER NOT NULL);
""")


def post_entry(entry_id, lines):
    """lines: list of (account, paise). Plus raises, minus lowers."""
    if len(lines) < 2 or sum(paise for _, paise in lines) != 0:
        raise ValueError(f"{entry_id} does not balance")
    with db:  # one transaction: all lines or none
        cur = db.execute("INSERT INTO entries VALUES (?) "
                         "ON CONFLICT (id) DO NOTHING", (entry_id,))
        if cur.rowcount == 0:
            return "duplicate, ignored"
        db.executemany("INSERT INTO lines VALUES (?, ?, ?)",
                       [(entry_id, a, p) for a, p in lines])
    return "posted"


success = [("upi_receivable", 125000), ("merchant:saree_shop", -125000)]
print(post_entry("upi-success:000000000101", success))
print(post_entry("upi-success:000000000101", success))  # the bank resends
print(db.execute("SELECT account, SUM(paise) FROM lines "
                 "GROUP BY account ORDER BY account").fetchall())
print(db.execute("SELECT SUM(paise) FROM lines").fetchone()[0])

Save it as ledger.py and run python3 ledger.py. On Python 3.13 with SQLite 3.45 you should see this output.

posted
duplicate, ignored
[('merchant:saree_shop', -125000), ('upi_receivable', 125000)]
0

What just happened? The sum check refuses any entry that would create or lose money. The with db block is one transaction, so a crash leaves all lines or none.

The entry id comes from the UPI reference, so the resent message changed nothing. The last line of output is the sum of every line in the ledger, and it is zero.

Two details that matter in production

First, ON CONFLICT DO NOTHING ignores a repeat even when it disagrees with the first copy, say with a different amount. The entries table above stores only the id, so the code cannot spot this. Store the amount on the entry row too, and raise a break when a repeat's amount differs.

Second, duplicates can arrive at the same moment. We tested this on PostgreSQL 16.15 with two sessions inserting the same entry id. The second insert waited until the first committed, then did nothing. In our test the first session held its transaction open for 3 seconds.

When the first session rolled back instead, the second insert was saved. So the unique key, not a check in your code, is what makes one bank message one entry.

You stop duplicates by giving every payment and every event a unique id, and making any repeat of that id do nothing. This property is called idempotency: doing something twice has the same effect as doing it once.

The company needs idempotency in three places.

• The order. Each bill at the counter gets a fresh QR with its own order id and amount. That order can move to paid only once.
• The bank's message. The ledger entry id comes from the UPI reference, so a repeated success message hits the unique id and changes nothing.
• The app's own retries. When the shop app calls the company's servers again after a timeout, it sends the same request id. The server returns the first answer instead of acting again.

That last point is the idempotency key pattern. The id is created once, before the first try, and every retry carries the same id.

Now replay one Navratri double payment with the new design.

1. 1

   The first scan

   A customer scans the QR for order 7781 and approves Rs 1,250. The bank is slow, so the company hears nothing for 40 seconds.

2. 2

   Honest waiting

   The shop's screen and speaker say the payment is pending, not failed. Most customers now simply wait a minute.

3. 3

   A second payment anyway

   One impatient customer pays again from a second UPI app. Both payments succeed. The second success arrives for an order that is already paid.

4. 4

   Automatic refund

   The rule says an order is paid once. The extra Rs 1,250 is recorded as owed to the customer, and a refund starts at once.

5. 5

   Clean books

   The ledger shows both payments and the refund as separate entries. Nothing was edited, so the reconciliation team can read the whole story in one place.

It should keep the payment pending, ask the bank for its status a few times with growing gaps, and hand anything still unknown to reconciliation. It should never guess.

A state machine makes this exact. A state machine is a fixed list of states, plus the only moves allowed between them. A payment can go from pending to success, but never from success back to pending.

Start at the top of the diagram. Notice that silence from the bank leads to a status check, never straight to failed.

Asking again sounds harmless, but timing matters. If thousands of payments ask at the same second, a slow bank gets buried. The Amazon Builders' Library article on timeouts and retries calls retries selfish, because each one spends more of a struggling server's time.

Its advice has three parts. Back off, meaning wait longer after each try, usually doubling up to a cap. Limit the number of tries, because a cap alone leaves every client retrying at the capped rate. Add jitter, a random part of each wait, so clients do not all retry in step.

And retry an action with side effects only when it is idempotent. A status check only reads, so it is safe to repeat.

import random
import time


def resolve_pending(upi_ref, ask_bank_status, tries=3, base_s=15, cap_s=120):
    for attempt in range(tries):
        wait = min(cap_s, base_s * 2 ** attempt)        # 15, 30, 60 seconds
        time.sleep(wait / 2 + random.uniform(0, wait / 2))  # jitter
        status = ask_bank_status(upi_ref)
        if status in ("SUCCESS", "FAILED"):
            return status
    return "PENDING"  # still unknown: reconciliation decides

The wait doubles on each try but never passes the cap. The jitter line sleeps somewhere between half and all of that wait, so thousands of pending payments spread out. When the bank still has no answer, the function returns pending instead of inventing one.

To check it without waiting, replace time.sleep with a function that records each wait. Over 10,000 runs on Python 3.13, the three waits stayed within 7.5 to 15, 15 to 30 and 30 to 60 seconds.

With three tries the 120 second cap never bites. It starts to matter from a fifth try: the waits would be 15, 30, 60, 120 and then 120 instead of 240.

In production this runs in a background worker, not while the customer waits. These timings are examples. Your partner bank and NPCI's rules set the real ones.

The RBI deadline for stuck payments

Some payments stay unknown because money left the customer's bank and never reached the merchant. The Reserve Bank of India (RBI), India's central bank, set deadlines for this in a September 2019 circular on failed transactions.

For a UPI payment to a merchant where the customer is debited but the merchant gets no confirmation, the money must be auto-reversed by T+5. T is the calendar date of the transaction.

Beyond T+5, the customer is owed Rs 100 for each day of delay. For a person-to-person UPI transfer where the receiver is not credited, the limit is T+1, with the same Rs 100 a day.

As of 19 October 2026, the RBI site shows this circular with no amendments listed. Check it again before you build on these numbers.

So pending can never mean forgotten. The team tracks every unresolved payment by age, and the oldest ones get human attention first.

Payment reconciliation compares your ledger with outside records, usually the bank's reports and statement, matching each payment by its reference number. Every match confirms a payment, and every mismatch, called a break, gets investigated.

Go back to hundi counting day. Two teams count the same notes separately, and their totals must agree with each other and with the slips sealed in each bag. When totals differ, they recount that bag and find the reason before anyone signs.

The company's reconciliation is the same idea with three counts. Its own ledger is the first. The partner bank's daily transaction report, listing every UPI payment it processed, is the second. The escrow account statement, showing money that actually landed, is the third.

The diagram shows the daily job. Follow the three inputs into the matching job, then see where the breaks go.

Look at the fix column. The team never edits a ledger line. Each fix is a new entry, like the sealed bag and its new slip.

Matching three records in code

Matching itself is mostly plain code. Join the three records on the UPI reference, then compare amounts. Here is the core of it, with amounts in paise.

# Amounts in paise, keyed by UPI reference
ledger    = {"000000000101": 125000, "000000000102": 50000, "000000000104": 9900}
report    = {"000000000101": 125000, "000000000102": 50000, "000000000103": 20000}
statement = {"000000000101": 125000, "000000000103": 20000}

for ref in sorted(ledger.keys() | report.keys() | statement.keys()):
    seen = (ref in ledger, ref in report, ref in statement)
    amounts = {d[ref] for d in (ledger, report, statement) if ref in d}
    if seen == (True, True, True) and len(amounts) == 1:
        result = "matched"
    elif len(amounts) > 1:
        result = "break: amounts differ"
    elif not seen[0]:
        result = "break: missing from ledger"
    elif not seen[1]:
        result = "break: missing from bank report"
    else:
        result = "wait: not in statement yet, retry tomorrow"
    print(ref, result)

Run it with python3 recon.py and you should see one line per reference.

000000000101 matched
000000000102 wait: not in statement yet, retry tomorrow
000000000103 break: missing from ledger
000000000104 break: missing from bank report

The second payment is not a break yet. Payments near midnight can land on the bank's next day, so the job carries a missing statement line forward one day before raising it.

Merchant settlement means paying each merchant what the ledger says they are owed, from the money held for them. The company pays only amounts that reconciliation has matched, so it never pays out money it cannot prove it holds.

In India, a payment aggregator is a company that collects payments for merchants and then settles the money to them, like the company here. The RBI's Master Direction on payment aggregators, issued on 15 September 2025, sets the rules.

A non-bank aggregator must keep merchant money in a separate escrow account with a scheduled commercial bank in India. That is a commercial bank on the RBI's official list of scheduled banks. That account may be used only for authorised aggregator business.

The direction sets no fixed payout day. Credit to the merchant follows the aggregator's agreement with the merchant, which must be fair and must state the settlement timelines clearly.

It also says the escrow balance at the end of each day must not fall below the money collected for merchants but not yet paid to them. These are the rules as of 19 October 2026.

The company promises next-morning payouts, so the daily match must finish before 9 am. Each morning the settlement job does four things, in order.

• Read each merchant's balance from the ledger, counting only matched entries.
• Hold back refunds still owed to that merchant's customers.
• Post one payout entry per merchant, with an id built from the merchant and the date, so a rerun pays nobody twice.
• Send the bank transfers, then match the payouts against the next statement, like any other payment.

Replaying the old Navratri night through the new design shows where most of the Rs 38,000 went. Duplicate success messages had raised merchant balances by money that never reached escrow. Now those duplicates hit the unique entry id and change nothing.

Split ledger data by merchant, serve reports from read replicas (read-only copies of the database), and spread hot accounts across sub-accounts. The correctness rules stay the same at every size.

A hot account is one that almost every payment touches, like escrow_cash. Inserting new lines does not clash. But many ledgers also keep a running balance row per account, so reads stay fast.

Then every payment updates that one row, and each update waits for the row lock held by the one before it. On PostgreSQL 16.15, a second update of the same balance row waited until the first transaction committed.

A common fix is to split escrow_cash into, say, 16 sub-accounts, pick one per entry, and add them up when you need the total. In the same test, updates to two different sub-account rows did not wait for each other.

Do the sums before you split. If each transaction holds the row lock for 5 ms, one balance row takes at most about 200 updates a second, one after another. Keep slow calls, such as a bank request, outside the transaction.

The trade-off is that a balance check now needs a sum across sub-accounts. That is cheap for reports, but harder when a balance must never go below zero at any instant.

Lead with correctness, then walk the money from the customer to the merchant, naming the guard at each step. Keep scale talk short unless you are asked for it.

1. 1

   Agree the scope

   UPI payments to merchants, next-day settlement and rough volume. Here, 9 lakh payments on a peak evening, about 62 per second on average, with bursts above that.

2. 2

   Draw the payment flow

   Customer app, NPCI switch, both banks, your service and the ledger. Point out that your service hears about the payment last.

3. 3

   Show the state machine

   Created, pending, success and failed. A timeout leads to status checks with backoff and jitter, never straight to failed.

4. 4

   Design the ledger

   Double entry, append-only, integer paise, one database transaction per entry, and unique entry ids built from the UPI reference.

5. 5

   Add reconciliation

   A daily match of ledger, bank report and statement. Breaks go to a queue and are fixed with new entries, never edits.

6. 6

   Settle from matched money

   Payouts come from the escrow account, only for matched amounts, with ids that make a rerun safe.

7. 7

   Then talk about scale

   Split ledger data by merchant, add read replicas for reports, and spread hot accounts such as escrow_cash across sub-accounts.