Reconcile the Ledger Against the Channel Logs

Goal

You reconcile the core banking ledger and the channel log to find transactions that exist only in the channel, double debits after a timeout, transactions posted with a different amount, and balances edited without the ledger, and after correcting them with correcting transactions, you rebuild the balances from the ledger.

Why it matters

A bank's systems have three layers. The channel layer that customers tap (mobile, ATM, teller window, open banking), the core banking layer that holds accounts and the ledger, and the information layer that is a replicated copy for analysis and reporting. A transaction starts at the channel, is posted to core banking, and the result returns to the channel. The problem is that this round trip is not always complete. The channel may have answered success while the ledger posting failed, and the ledger may have posted it while the response was late and the channel recorded a timeout. In the latter case, the customer thinks it failed and taps again. Then the withdrawal goes out twice.

Such mismatches are not visible on screen. There is one way to catch them — reconciling the two records after the close. For each case the channel called success, you look for its pair in the ledger; for each ledger case, you look for its pair in the channel; and you pick out cases where there is a pair but the amounts differ. And since an account's balance column is a stored value and not the truth, you compare it with the value recomputed by adding the ledger to the opening balance.

Corrections have a method too. You do not edit or delete ledger rows. A missing transaction is entered as a correcting transaction, and a transaction that went out wrongly is offset with a transaction in the opposite direction. What happened has to remain in the ledger in order so you can explain it when a customer complaint arrives two months later.

Rules of this lab

Computed balance = open_balance + the sum of DEP in the ledger − the sum of WDR in the ledger. Gap = stored_balance − computed balance.
A channel SUCCESS case is paired only if the ledger has the same channel_ref. For FAIL, not being in the ledger is normal.
Double debit = a case where, within 10 minutes after a TIMEOUT case that was posted to the ledger (exactly 10 minutes included; count the time difference in seconds), there is another SUCCESS case with the same account, the same type, and the same amount. The same transaction more than 10 minutes later is treated as a separate transaction the customer intended.
Corrections: for a missing case, enter txn_id as FIX-<channel_ref> with the original type and amount as they are. For a wrongly posted case, offset it by entering txn_id as RVS-<channel_ref> with the opposite type (the reversal of a WDR is a DEP) and the same amount. For a case with a wrong amount, after offsetting, enter the correct amount again as FIX-<channel_ref>. A double debit is offset by entering RVS- with the channel_ref of the retry that was posted second (the SUCCESS one).

Steps

Save the generator in the example on the right as /root/bank/make_core.py and run it with python3 make_core.py. core.db (12 accounts, 45 ledger rows) and channel_20260825.csv (53 rows of channel log) are created.
Recompute the balances of all 12 accounts from the ledger and write them in /root/bank/balance_check.csv with the header acct_no,open_balance,ledger_net,computed,stored,gap. ledger_net is the deposit sum minus the withdrawal sum, and gap is stored minus computed.
In /root/bank/recon_summary.txt, write eight lines: channel_rows=, channel_success=, channel_fail=, channel_timeout=, ledger_rows=, matched=, only_channel=, and only_ledger=. matched is the number of SUCCESS cases with a pair in the ledger, only_channel is the number of SUCCESS cases without a pair, and only_ledger is the number of ledger cases with no SUCCESS pair in the channel.
Write the cases that are success in the channel but not in the ledger in /root/bank/only_channel.csv with the header channel_ref,acct_no,type,amount.
Write the cases that are TIMEOUT but posted to the ledger in /root/bank/timeout_posted.csv with the header channel_ref,acct_no,amount,txn_id, and the double debits that followed them in /root/bank/double_debit.csv with the header first_ref,retry_ref,acct_no,amount.
Write the cases that have a pair but a different amount in /root/bank/amount_diff.csv with the header channel_ref,channel_amount,ledger_amount,gap. gap is the channel amount minus the ledger amount.
Enter the correcting transactions into ledger according to the rules above, and refill the stored_balance of all 12 accounts with the computed balance. Write the result in /root/bank/balance_after.csv with the header acct_no,balance, and in /root/bank/recheck.txt in five lines: fix_rows=, rvs_rows=, ledger_rows_after=, gap_accounts_before=, and gap_accounts_after=.
In /root/bank/recon_report.md, write a report in five sections: ## Reconciliation result, ## Transactions only in the channel, ## Timeouts and double debits, ## Correction method, and ## Preventing recurrence.

Notes

Read with sqlite3 -readonly core.db, and open for writing only in step 7, the correction. If you load the channel log into the channel table with .import --csv, you can reconcile with SQL.
The basis of reconciliation is two LEFT JOINs — one from the channel side and one from the ledger side.
Common mistake 1: putting FAIL cases into only_channel. For a failure, not being in the ledger is normal.
Common mistake 2: looking for the "first case" of a double debit among the SUCCESS ones. The first case is the one recorded as TIMEOUT.
Common mistake 3: editing ledger rows with UPDATE or DELETE in step 7. The grader also checks that the original rows are still there.

Create the core banking copy and the channel log

Save the generator in the example on the right as /root/bank/make_core.py and run it with python3 make_core.py. core.db (12 accounts, 45 ledger rows) and channel_20260825.csv (53 rows of channel log) are created.

Save the generator in the lab instructions as /root/bank/make_core.py and run it with python3. If you edit it by hand, all the numbers in the later steps go off.

Rebuild the balances from the ledger and compare

Recompute the balances of all 12 accounts from the ledger and write them in /root/bank/balance_check.csv with the header acct_no,open_balance,ledger_net,computed,stored,gap. ledger_net is the deposit sum minus the withdrawal sum, and gap is stored minus computed.

LEFT JOIN ledger on account and compute SUM(CASE type WHEN 'DEP' THEN amount ELSE -amount END) per account. For an account with no transactions, give 0 with COALESCE. There is one account whose gap is not 0.

Load the channel log and count from both sides

In /root/bank/recon_summary.txt, write eight lines: channel_rows=, channel_success=, channel_fail=, channel_timeout=, ledger_rows=, matched=, only_channel=, and only_ledger=. matched is the number of SUCCESS cases with a pair in the ledger, only_channel is the number of SUCCESS cases without a pair, and only_ledger is the number of ledger cases with no SUCCESS pair in the channel.

Create the channel table with sqlite3's .import --csv (with DROP TABLE IF EXISTS first so it can be rerun), and do a LEFT JOIN with ledger on channel_ref once in each direction. matched + only_channel = channel_success and matched + only_ledger = ledger_rows must hold.

Pull out the transactions only in the channel

Write the cases that are success in the channel but not in the ledger in /root/bank/only_channel.csv with the header channel_ref,acct_no,type,amount.

In the only_channel query from step 3, pull the reference, account, type, and amount instead of COUNT, and sort by channel_ref. Do not include FAIL.

Find double debits after a timeout

Write the cases that are TIMEOUT but posted to the ledger in /root/bank/timeout_posted.csv with the header channel_ref,acct_no,amount,txn_id, and the double debits that followed them in /root/bank/double_debit.csv with the header first_ref,retry_ref,acct_no,amount.

The first file is an INNER JOIN of channel(result='TIMEOUT') and ledger. For the second, join channel once more to that result and find a SUCCESS with the same acct_no, type, and amount whose ts follows within 10 minutes (10 minutes included). The julianday difference × 1440 is floating point, so exactly 10 minutes comes out as 10.0000000745 and misses the boundary — subtract whole seconds from strftime('%s', ts) and compare with 600.

Find transactions posted with a different amount

Write the cases that have a pair but a different amount in /root/bank/amount_diff.csv with the header channel_ref,channel_amount,ledger_amount,gap. gap is the channel amount minus the ledger amount.

INNER JOIN channel and ledger on channel_ref and keep only the rows whose amounts differ. The amount in a table made with .import is TEXT — SQLite converts it to a number when you compare it with an INTEGER column such as ledger.amount, but size comparisons with literals and sorting become string comparisons, so make it explicitly numeric with CAST(... AS INTEGER).

Enter the correcting transactions and rebuild the balances

Enter the correcting transactions into ledger according to the rules above, and refill the stored_balance of all 12 accounts with the computed balance. Write the result in /root/bank/balance_after.csv with the header acct_no,balance, and in /root/bank/recheck.txt in five lines: fix_rows=, rvs_rows=, ledger_rows_after=, gap_accounts_before=, and gap_accounts_after=.

Use INSERT ... SELECT to put the query results of steps 4, 5, and 6 into the ledger as they are, prefixing txn_id with FIX-/RVS-, and flip the type for RVS. Use INSERT OR IGNORE so it is safe even if run twice. At the end, UPDATE account SET stored_balance = open_balance + (ledger sum). Count gap_accounts_before from the step 2 file.

Write the reconciliation report

In /root/bank/recon_report.md, write a report in five sections: ## Reconciliation result, ## Transactions only in the channel, ## Timeouts and double debits, ## Correction method, and ## Preventing recurrence.

Use one line starting with ## for each of the five section titles in the task (the English titles in backticks, or titles with the same meaning in Korean, Japanese, or Chinese). In the reconciliation result, write the step 3 numbers; in transactions only in the channel, references like CH0301; in the timeout section, every first-case reference; in the correction method, FIX-/RVS-; and in preventing recurrence, the idempotency key.