元帳とチャネルログを突合する

目標

勘定系の元帳とチャネル系のログを突合して、チャネルにだけある取引、タイムアウトのあとの二重出金、金額が違って載った取引、元帳なしで直された残高を見つけ出し、訂正取引で正したあと、残高を元帳から作り直します。

なぜ重要なのか

銀行システムは3つの層です。顧客が押すチャネル系(モバイル・ATM・窓口・オープンバンキング)、口座と元帳を持つ勘定系、分析と報告のために複製しておく情報系。取引1件は、チャネルで始まって勘定系に載り、その結果がチャネルに戻ります。問題は、この往復が、常に完全とは限らないことです。チャネルは成功と答えたのに、元帳への反映が失敗することがあり、元帳には載ったのに、応答が遅れて、チャネルがタイムアウトとして記録することがあります。後者で、顧客は失敗したと思ってもう一度押します。すると、出金が2回出ます。

こうしたずれは、画面では見えません。捕まえる方法は1つで、締めのあとに2つの記録を突合することです。チャネルが成功とした案件ごとに元帳のペアを探し、元帳の案件ごとにチャネルのペアを探し、ペアはあるのに金額が違う案件を選び出します。そして、口座の残高カラムは保存された値であって、真実ではないので、開始残高に元帳を足して計算し直した値と突き合わせます。

訂正にも方式があります。元帳の行を直したり消したりしません。抜けた取引は訂正取引として入れ、誤って出た取引は、逆方向の取引で相殺します。元帳には、何があったかが順番に残っていてはじめて、2か月後に苦情が入ったときに説明できます。

このラボのルール

計算残高 = open_balance + 元帳のDEPの合計 − 元帳のWDRの合計。差 = stored_balance − 計算残高。
チャネルのSUCCESSの案件は、元帳に同じchannel_refがあってはじめてペアが合ったことになります。FAILは、元帳にないのが正常です。
二重出金 = TIMEOUTで、かつ元帳に載った案件のあと10分以内に(ちょうど10分まで含み、時刻の差は秒単位で数えます)、同じ口座・同じ種別・同じ金額でSUCCESSした案件がもう1件ある場合。10分を過ぎたあとの同じ取引は、顧客が意図した別の取引とみなします。
訂正: 抜けた案件は、txn_idをFIX-<channel_ref>にして、元の種別・金額のまま入れます。誤って載った案件は、txn_idをRVS-<channel_ref>にして、反対の種別(WDRの取消はDEP)・同じ金額で入れて相殺します。金額が間違った案件は、相殺したあと、FIX-<channel_ref>で正しい金額を入れ直します。二重出金は、2回目に載った再試行の案件(SUCCESSのほう)のchannel_refでRVS-を入れて相殺します。

ステップ

右の例にあるジェネレーターを/root/bank/make_core.pyとして保存し、python3 make_core.pyで実行してください。core.db(口座12、元帳45行)とchannel_20260825.csv(チャネルログ53行)が作られます。
口座12個すべての残高を元帳から計算し直して、/root/bank/balance_check.csvにacct_no,open_balance,ledger_net,computed,stored,gapのヘッダーで書いてください。ledger_netは入金の合計から出金の合計を引いた値、gapはstoredからcomputedを引いた値です。
/root/bank/recon_summary.txtにchannel_rows=、channel_success=、channel_fail=、channel_timeout=、ledger_rows=、matched=、only_channel=、only_ledger=の8行を書いてください。matchedは、元帳にペアがあるSUCCESSの案件、only_channelはペアがないSUCCESSの案件、only_ledgerは、チャネルにSUCCESSのペアがない元帳の案件の数です。
チャネルは成功なのに元帳にない案件を、/root/bank/only_channel.csvにchannel_ref,acct_no,type,amountのヘッダーで書いてください。
TIMEOUTなのに元帳に載った案件を/root/bank/timeout_posted.csvにchannel_ref,acct_no,amount,txn_idのヘッダーで、そのあとに続いた二重出金を/root/bank/double_debit.csvにfirst_ref,retry_ref,acct_no,amountのヘッダーで書いてください。
ペアはあるのに金額が違う案件を、/root/bank/amount_diff.csvにchannel_ref,channel_amount,ledger_amount,gapのヘッダーで書いてください。gapは、チャネルの金額から元帳の金額を引いた値です。
上のルールどおりに訂正取引をledgerに入れ、口座12個のstored_balanceを計算残高で埋め直してください。結果を/root/bank/balance_after.csvにacct_no,balanceのヘッダーで、/root/bank/recheck.txtにfix_rows=、rvs_rows=、ledger_rows_after=、gap_accounts_before=、gap_accounts_after=の5行で書いてください。
/root/bank/recon_report.mdに、## Reconciliation result(突合結果)、## Transactions only in the channel(チャネルにだけある取引)、## Timeouts and double debits(タイムアウトと二重出金)、## Correction method(訂正方法)、## Preventing recurrence(再発防止)の5つの節で報告書を書いてください。

参考

sqlite3 -readonly core.dbで読み、訂正するステップ7でだけ、書き込みで開きます。チャネルログは、.import --csvでchannel表に取り込めば、SQLで突合できます。
突合の基本は、LEFT JOIN 2回です。チャネル基準で1回、元帳基準で1回。
よくある間違い1: FAILの案件をonly_channelに入れてしまうことです。失敗は元帳にないのが正常です。
よくある間違い2: 二重出金の「最初の案件」をSUCCESSの中から探してしまうことです。最初の案件は、TIMEOUTとして記録されたその案件です。
よくある間違い3: ステップ7で、元帳の行をUPDATEやDELETEで直してしまうことです。採点ツールは、元の行がそのまま残っているかも見ます。

勘定系の複製とチャネルログを作る

右の例にあるジェネレーターを/root/bank/make_core.pyとして保存し、python3 make_core.pyで実行してください。core.db(口座12、元帳45行)とchannel_20260825.csv(チャネルログ53行)が作られます。

ラボの案内にあるジェネレーターを/root/bank/make_core.pyとして保存し、python3で実行してください。手で直すと、後のステップの数字がすべてずれます。

残高を元帳から作り直して突き合わせる

口座12個すべての残高を元帳から計算し直して、/root/bank/balance_check.csvにacct_no,open_balance,ledger_net,computed,stored,gapのヘッダーで書いてください。ledger_netは入金の合計から出金の合計を引いた値、gapはstoredからcomputedを引いた値です。

accountを基準にledgerをLEFT JOINして、口座ごとにSUM(CASE type WHEN 'DEP' THEN amount ELSE -amount END)を出してください。取引がない口座は、COALESCEで0にします。gapが0でない口座が1つあります。

チャネルログを取り込み、両側から数える

/root/bank/recon_summary.txtにchannel_rows=、channel_success=、channel_fail=、channel_timeout=、ledger_rows=、matched=、only_channel=、only_ledger=の8行を書いてください。matchedは、元帳にペアがあるSUCCESSの案件、only_channelはペアがないSUCCESSの案件、only_ledgerは、チャネルにSUCCESSのペアがない元帳の案件の数です。

sqlite3の.import --csvでchannel表を作り(再実行のために、先にDROP TABLE IF EXISTS)、channel_refでledgerとLEFT JOINを、両方向で1回ずつ行ってください。matched + only_channel = channel_success、matched + only_ledger = ledger_rowsが成り立たなければなりません。

チャネルにだけある取引を抽出する

チャネルは成功なのに元帳にない案件を、/root/bank/only_channel.csvにchannel_ref,acct_no,type,amountのヘッダーで書いてください。

ステップ3のonly_channelのクエリで、COUNTの代わりに、参照・口座・種別・金額を抽出し、channel_ref順にソートしてください。FAILは入れません。

タイムアウトのあとの二重出金を探す

TIMEOUTなのに元帳に載った案件を/root/bank/timeout_posted.csvにchannel_ref,acct_no,amount,txn_idのヘッダーで、そのあとに続いた二重出金を/root/bank/double_debit.csvにfirst_ref,retry_ref,acct_no,amountのヘッダーで書いてください。

最初のファイルは、channel(result='TIMEOUT')とledgerのINNER JOINです。2つ目は、その結果にchannelをもう一度付けて、同じacct_no・type・amountで、tsが10分以内に(10分を含む)続くSUCCESSを探します。julianday差 × 1440は浮動小数点なので、ちょうど10分が10.0000000745と出て境界を取りこぼします。strftime('%s', ts)で秒単位の整数を引いて、600と比べてください。

金額が違って載った取引を探す

ペアはあるのに金額が違う案件を、/root/bank/amount_diff.csvにchannel_ref,channel_amount,ledger_amount,gapのヘッダーで書いてください。gapは、チャネルの金額から元帳の金額を引いた値です。

channelとledgerをchannel_refでINNER JOINして、金額が違う行だけを残してください。.importで作った表のamountはTEXTです。ledger.amountのようなINTEGERの列との比較はSQLiteが数値に合わせてくれますが、リテラルとの大小比較・ソートは文字列になるので、CAST(... AS INTEGER)で数値であることを明確にしてください。

訂正取引を入れて残高を作り直す

上のルールどおりに訂正取引をledgerに入れ、口座12個のstored_balanceを計算残高で埋め直してください。結果を/root/bank/balance_after.csvにacct_no,balanceのヘッダーで、/root/bank/recheck.txtにfix_rows=、rvs_rows=、ledger_rows_after=、gap_accounts_before=、gap_accounts_after=の5行で書いてください。

INSERT ... SELECTで、ステップ4・5・6のクエリ結果をそのまま元帳に入れ、txn_idの前にFIX-/RVS-を付け、RVSは種別を反転してください。INSERT OR IGNOREで、2回回しても安全に。最後にUPDATE account SET stored_balance = open_balance + (元帳の合計)です。gap_accounts_beforeは、ステップ2のファイルで数えます。

突合報告書を書く

/root/bank/recon_report.mdに、## Reconciliation result(突合結果)、## Transactions only in the channel(チャネルにだけある取引)、## Timeouts and double debits(タイムアウトと二重出金)、## Correction method(訂正方法)、## Preventing recurrence(再発防止)の5つの節で報告書を書いてください。

taskの節の見出し5つ(バッククォートの中の英語の見出し、または括弧の中の日本語の見出しや、同じ意味の韓国語・中国語の見出し)を、それぞれ## で始まる1行で書いてください。突合結果にはステップ3の数字を、チャネルにだけある取引にはCH0301のような参照を、タイムアウトの節には最初の案件の参照をすべて、訂正方法にはFIX-/RVS-を、再発防止には冪等キー(idempotency key)を書いてください。