Ledgers and reconciliation

Finance tells you: "The wallet total is Rs 5,000 less than the money in the bank. Why?" The interviewer asks: "How do you design the records so that you can find the reason?"

A weak answer keeps one balance column and changes it inside a transaction. That records the result, but not the story, so nobody can explain a difference. A strong answer keeps a list of money entries. Nobody edits or deletes a line, and every money move is written in two places. It keeps a payment pending until the company that takes the payment confirms it. Each day it compares its own records with that company's report.

At SDE-1 level you should say why a balance column is not enough. At SDE-2 level you should design the entries and handle pending payments and repeated messages. At SDE-3 level you should design the daily check and the way you fix a difference.

Think about the account book of a small shop. Every sale is a new line. Nobody erases a line. A wrong line is fixed with a new line that cancels it. And every money move is written twice: where the money came from, and where it went. At the end of the day the two sides must give the same total. If they do not, there is a mistake.

In this lesson, a gateway is the payment company that takes the customer's payment for you and pays you after a delay. Lesson 61 called it the payment provider.

Why one balance column is not enough

The code balance = balance - 500 keeps only the result. It does not say why the balance is Rs 4,500. A bug, a repeated request (lesson 61) or two payments at once (lesson 21) can change it, and nothing shows what happened. You cannot prove the number, and you cannot find the error.

The ledger

A ledger is a table of entries. An entry is one money change on one account. The table is append-only: you add rows, and you never update or delete them. The balance of an account is the sum of its entries. An entry holds the account, the side (debit or credit, explained below) and the amount. It also holds a reference, such as the gateway's payment ID, and the time.

Store money as a whole number of the smallest unit. In India that is the paisa: 1 rupee = 100 paise, so Rs 500 = 50,000 paise. Decimal numbers in programming languages cannot hold some values exactly. For example, 0.1 + 0.2 gives 0.30000000000000004 in JavaScript. Payment APIs such as Razorpay also take the amount in paise. The Postgres manual says to use an exact number type for money.

Double entry

Double-entry means that every money move is a posting: a group of two or more entries that are saved together. In every posting, the total of the debits equals the total of the credits. Debit and credit are only the names of the two sides of an entry. Which side adds money depends on the account:

• For an account that holds money we owe (a user's wallet), a credit adds, and a debit removes.
• For an account that holds our money or money owed to us (the gateway), a debit adds, and a credit removes.
• For a cost account (fees), a debit adds, and a credit removes.

Our example wallet app has three accounts:

• WALLET:U1: the money we owe user U1.
• GATEWAY: the money that the payment gateway holds for us and will pay to our bank.
• FEES: the cost that the gateway charges us.

The rules:

1. The sides must be equal. Check the totals before you save the posting. A job can also sum the whole table, and total debits minus total credits must be 0. A bug that writes only one side breaks this and is found.
2. One database transaction. All entries of one posting are saved together or not at all (lesson 21).
3. A unique reference. Each posting carries a reference, such as the gateway payment ID, with a unique constraint (lesson 61). A repeated request or a repeated message tries the same reference and posts nothing the second time.

Money cannot appear or disappear. It only moves from one account to another.

Fixing a mistake: reversal

Do not edit or delete a wrong entry. That destroys the proof. Post a reversal: the same entries with the debit and credit sides swapped, pointing at the original posting. It has its own reference, such as gw_pay_9002_reversal. Then post the correct entries. The history shows all three.

Pending payments and late callbacks

A callback is a message that the gateway sends to your server after a while, to say how a payment ended. It is the webhook of lesson 61. A callback can be late or can come twice, and a payment can stay pending for minutes.

1. Post only facts. Do not credit the wallet when the user starts the payment. Keep a payment record with the status PENDING, and no ledger entries. Post to the ledger when the gateway confirms success.
2. A timeout is not a failure (lesson 54). If no answer comes, the status stays PENDING. Ask the gateway for the status of the payment after a wait, for example after 1 minute, 5 minutes and 30 minutes.
3. One posting per payment ID. Find the payment by the gateway's ID. The unique reference makes a second callback do nothing.
4. A late callback is normal. Handle it as you handle an early one.
5. If two callbacks disagree, do not guess. Do not overwrite a final result with the opposite one. Raise an alarm. The daily check below settles it.
6. For money that leaves, hold it first. To pay Rs 300 out of the wallet, post a debit to WALLET:U1 and a credit to PAYOUTS_PENDING at once. Then the user cannot spend it twice. PAYOUTS_PENDING is money we owe to the bank transfer, so a credit adds. If the transfer works, debit PAYOUTS_PENDING and credit GATEWAY. If it fails, debit PAYOUTS_PENDING and credit WALLET:U1.

Fast balances

Summing millions of entries on every read is slow. Keep a balance column for each account as a copy. Update it in the same transaction as the entries, and lock the row while you do (lesson 22). Each night, compare each copy with the sum of the entries. The entries are the truth. The copy is only for speed.

Reconciliation

Reconciliation means comparing your records with the records of an outside party and explaining every difference. A payment gateway gives a settlement report: a daily file with one line for each payment (ID, amount, fee, status). The bank gives a statement in the same way. Each day:

1. Load the report for the day.
2. Match each line with the ledger by the gateway's payment ID.
3. Sort the results into four groups:
   • matched;
   • in the report but not in the ledger;
   • in the ledger but not in the report;
   • the same ID, but a different amount or status.
4. Fix each difference with new postings: post what is missing, reverse what is extra, adjust what is wrong. Record who decided and why.
5. Compare the totals as a second check. After the fixes, they must match.

Reconciliation does not prevent errors. It finds them the next day. It exists because messages get lost, gateways make mistakes and code has bugs.

A wallet app. Users add money by UPI (a bank-to-bank payment system in India) through a payment gateway.

Assumptions (made up to teach with, not real data):

• 100,000 top-ups per day, each Rs 500.
• The gateway fee is 1%, which is Rs 5 for each top-up.
• Each entry takes 100 bytes.

Step 1: the posting. Debits are 495 + 5 = 500. Credits are 500. The sides are equal. The missing entry in the predict box is the fee:

So the posting in the predict box is not valid: debits 495, credits 500. In paise the amounts are 49,500, 500 and 50,000. The wallet balance of U1 is now Rs 500.

Step 2: pending. At 10:00 the user pays, and the gateway says "pending". The payment record is PENDING, the ledger has no entry, and the wallet still shows Rs 0. At 10:05 the status check says "pending" again. At 10:08 the callback "success" arrives. We post the three entries with the reference gw_pay_8841.

Step 3: a repeat. At 10:08 and 20 seconds the same callback arrives again. The insert with reference gw_pay_8841 hits the unique constraint, so nothing is posted, and the server answers 200.

Step 4: a reversal. Support finds that gw_pay_9002 was credited, but the gateway says "failed". Do not delete. Post the same entries with the sides swapped: credit GATEWAY 495, credit FEES 5 and debit WALLET:U1 500. The wallet drops by Rs 500, and the history shows the credit and the reversal.

Step 5: the daily check. The report has 100,000 lines. Total, before fees: 100,000 x Rs 500 = Rs 50,000,000 (5 crore). Assumption: 40 payments are in the report but not in the ledger, because their callbacks were lost. Assumption: 15 payments are in the ledger but have no line in the report. We credited them from a callback, but the gateway has no record of them.

• Payments in the ledger: 100,000 - 40 + 15 = 99,975.
• Ledger total: 99,975 x Rs 500 = Rs 49,987,500.
• Difference: 50,000,000 - 49,987,500 = Rs 12,500. Check: (40 - 15) x Rs 500 = Rs 12,500.

The total shows that a difference exists. The line-by-line match shows where: 40 + 15 = 55 lines out of 100,000, which is 0.055%. The matching needs 100,000 IDs of about 20 bytes, which is 2 MB, so it is cheap.

The fixes: post the 40 missing payments, which credits 40 x Rs 500 = Rs 20,000 to users who paid. Reverse the 15 extra credits, which is 15 x Rs 500 = Rs 7,500. If a user already spent that money, the wallet goes below zero, and a business rule must decide what to do.

Step 6: size. Each top-up has 3 entries. 100,000 x 3 x 100 bytes = 30,000,000 bytes = 30 MB per day. For a year: 365 x 30 MB = 10,950 MB, which is about 11 GB.

• A ledger solves missing history, unexplained balances and silent bugs. It costs more rows (3 entries for one top-up), more design (accounts and sides), no edits, and a copy of the balance for speed. I would use it for any money, and not for a counter such as likes.
• Pending until confirmed solves crediting money that never arrived. It costs a wait for the user and a status check job. I would show "Rs 500 pending" to the user.
• Reconciliation solves the errors that nothing else caught. It costs a daily job, a person for the differences, and a delay of one day. I would run it every day, and more often when the volume is high.
• Reversal instead of edit solves the proof. It costs longer history. I would never edit.

"One balance column inside a transaction is enough for money." A transaction keeps the column consistent, but it keeps no story. Nothing explains a gap.

"To fix a wrong entry, update or delete the row." That destroys the proof. Post a reversal and then the correct entries.

"If the callback does not come, the payment failed." No answer is not a failure. The money may have moved. Keep the payment pending, ask the gateway, and let reconciliation catch what remains.

1. Why do we use double entry? What must be true for every posting?
2. A top-up of Rs 2,000 has a gateway fee of 1.5%. (a) Write the posting. (b) For one day the report has 80,000 lines of Rs 2,000, and the ledger has 79,950. If the 50 missing payments are the only difference, what is the difference in rupees?
3. A support agent credited the wrong user Rs 500. An engineer says: "Delete those two rows." What do you do instead?