Finding a Debit/Credit Imbalance in the Ledger

Goal

Starting from a double-entry ledger, you narrow one entry whose debits and credits do not match down to a single journal line, correct it in a way that does not erase the original record, and then bring even the balance table into line with the ledger.

Why it matters

In a bank system, the balance is not a stored value but a value computed from the ledger. A balance column is kept per account for performance, but it is a copy of the ledger, not the original.

This distinction is decisive in an investigation for the following reason. A balance is just a number, so there is no way to tell from that number alone whether it is right or wrong. A double-entry ledger, on the other hand, verifies itself — because every transaction is recorded on both the debit and credit sides, the debit sum and the credit sum of the whole ledger must be equal, and if this identity is broken, that itself is an incident signal. And that signal narrows down once more to the level of an entry.

This case goes like this. The customer's operations contact got in touch saying "the closing balance does not match." There are two divergences in three days of the ledger, and their natures are the exact opposite of each other. On one side the ledger is wrong and the balance table is right, and on the other the ledger is right and the balance table is wrong. If you touch them without telling these two apart, you fix the wrong side.

The way to correct is also different from an ordinary service. You must not fix a wrong entry with an UPDATE. You put in a reversing entry that mirrors the original entry upside down to offset it, and post the correct entry anew. Only if three entries remain side by side in the ledger can you explain what happened in an audit two months later.

Steps

Create and run /root/bank/gen_ledger.py to make /root/bank/ledger.db. It holds 10 accounts, 201 entries, and 430 journal lines.
Pull out a per-account trial balance into /root/bank/trial_balance.csv. The header is code,name,debit,credit,balance, and balance is the debit sum minus the credit sum. Keep accounts that never moved too.
Write four lines in /root/bank/tb_totals.txt. debit_total=, credit_total=, and gap= come from the ledger, and cached_sum= is the sum of accounts.cached_balance.
Compare the debit sum and the credit sum per entry to find the entry that does not match, and write entry_id=, debit=, credit=, and gap= in /root/bank/broken_entry.txt. The journal lines of that entry are dumped as they are into /root/bank/broken_lines.csv with the header line_id,entry_id,account_code,dc,amount.
In /root/bank/balance_diff.csv, with the header code,cached,ledger,gap, keep only the accounts where the balance table and the ledger sum differ. gap is cached minus ledger.
Do not touch the original entry, and put in two correcting entries. Register the reversing entry as JE-20260826-0134-R and the re-posting as JE-20260826-0134-C, and put them in both journal and journal_line.
Recompute accounts.cached_balance from the ledger sum and overwrite it, and write seven lines in /root/bank/recheck.txt: debit_total=, credit_total=, gap=, unbalanced_entries=, original_credit=, cached_sum=, and balance_diff_rows=.
Write a report in /root/bank/ledger_report.md with five sections: ## 무엇이 틀렸나, ## 어떻게 찾았나, ## 어떻게 고쳤나, ## 잔액 표는 왜 못 잡았나, and ## 재발 방지 (the Korean headings mean: What was wrong, How it was found, How it was fixed, Why the balance table did not catch it, and Preventing recurrence).

Notes

If you read with sqlite3 -readonly <파일> "<쿼리>" (the placeholders are the file and the query), you can prevent accidental writes during the investigation.
When pulling out CSV, first pass .headers on and .mode csv on standard input: sqlite3 -readonly ledger.db > out.csv <<'SQL' ... SQL.
You get a signed balance in one go with SUM(CASE dc WHEN 'D' THEN amount ELSE -amount END).
Common mistake 1: dropping accounts with no movement from the table in step 2. Then "no movement" and "no such account" cannot be told apart.
Common mistake 2: writing the reversing entry amount as the correct amount in step 6. You must flip it with the recorded amount for the error to be offset exactly.
Common mistake 3: expecting the per-entry imbalance to become 0 in step 7. It is normal for the original entry and the reversing entry to remain as a pair.

Build the ledger snapshot

Create and run /root/bank/gen_ledger.py to make /root/bank/ledger.db. It holds 10 accounts, 201 entries, and 430 journal lines.

First create /root/bank, and build the sqlite DB with python3 in it. There are three tables, accounts, journal, and journal_line, and the journal lines are 430.

Build the trial balance

Pull out a per-account trial balance into /root/bank/trial_balance.csv. The header is code,name,debit,credit,balance, and balance is the debit sum minus the credit sum. Keep accounts that never moved too.

For each account, compute the sum of the debit amounts and the sum of the credit amounts separately, and balance is the debit sum minus the credit sum. Accounts that never moved in these three days must also remain in the table, so use a LEFT JOIN.

Count the ledger and the balance table separately

Write four lines in /root/bank/tb_totals.txt. debit_total=, credit_total=, and gap= come from the ledger, and cached_sum= is the sum of accounts.cached_balance.

There are four numbers. The first three come from journal_line, and the last comes from accounts.cached_balance. The finding of this step is that the two tables diverge by different amounts.

Narrow down to one entry and one journal line

Compare the debit sum and the credit sum per entry to find the entry that does not match, and write entry_id=, debit=, credit=, and gap= in /root/bank/broken_entry.txt. The journal lines of that entry are dumped as they are into /root/bank/broken_lines.csv with the header line_id,entry_id,account_code,dc,amount.

Group by entry and compare the debit sum and the credit sum. If you put a condition in the HAVING clause that the difference is not 0, only one remains. Do not change the values of the journal lines; dump them exactly as they are.

Reconcile the balance table against the ledger sum

In /root/bank/balance_diff.csv, with the header code,cached,ledger,gap, keep only the accounts where the balance table and the ledger sum differ. gap is cached minus ledger.

Put accounts.cached_balance and the ledger sum side by side per account and keep only the ones that differ. gap is cached minus ledger. Two rows come out, and the reasons they diverge are the exact opposite of each other.

Correct with a reversing entry and a re-posting

Do not touch the original entry, and put in two correcting entries. Register the reversing entry as JE-20260826-0134-R and the re-posting as JE-20260826-0134-C, and put them in both journal and journal_line.

Do not touch a single line of the original entry. The reversing entry only swaps the debits and credits of the original entry and uses the amounts exactly as recorded. Then put in one more entry with the correct amount.

Rebuild the balance table from the ledger

Recompute accounts.cached_balance from the ledger sum and overwrite it, and write seven lines in /root/bank/recheck.txt: debit_total=, credit_total=, gap=, unbalanced_entries=, original_credit=, cached_sum=, and balance_diff_rows=.

When they diverge, you always fix the copy. Overwrite accounts.cached_balance with the ledger sum. And do not expect the per-entry imbalance to become 0 — the reversing entry remains paired with the original entry.

Write the ledger integrity report

Write a report in /root/bank/ledger_report.md with five sections: ## 무엇이 틀렸나, ## 어떻게 찾았나, ## 어떻게 고쳤나, ## 잔액 표는 왜 못 잡았나, and ## 재발 방지 (the Korean headings mean: What was wrong, How it was found, How it was fixed, Why the balance table did not catch it, and Preventing recurrence).

You need five sections. In the "what was wrong" section, write the entry number and the difference amount; in the "how it was fixed" section, the number of the reversing entry; and in the "why the balance table did not catch it" section, the account number and amount where only the balance diverged.