원장과 채널 로그 대사하기

목표

계정계 원장과 채널계 로그를 대사해 채널에만 있는 거래, 타임아웃 뒤의 이중 출금, 금액이 다르게 실린 거래, 원장 없이 고쳐진 잔액을 찾아내고, 정정 거래로 바로잡은 뒤 잔액을 원장에서 다시 만듭니다.

왜 중요한가

은행 시스템은 세 층입니다. 고객이 누르는 채널계(모바일·ATM·창구·오픈뱅킹), 계좌와 원장을 가진 계정계, 분석과 보고를 위해 복제해 둔 정보계. 거래 하나는 채널에서 시작해 계정계에 실리고, 그 결과가 채널로 돌아갑니다. 문제는 이 왕복이 언제나 완전하지 않다는 것입니다. 채널은 성공이라 답했는데 원장 반영이 실패할 수 있고, 원장에는 실렸는데 응답이 늦어 채널은 타임아웃으로 기록할 수 있습니다. 후자에서 고객은 실패한 줄 알고 다시 누릅니다. 그러면 출금이 두 번 나갑니다.

이런 어긋남은 화면으로는 보이지 않습니다. 잡는 방법은 하나 — 마감 뒤 두 기록을 대사하는 것입니다. 채널이 성공이라 한 건마다 원장의 짝을 찾고, 원장의 건마다 채널의 짝을 찾고, 짝은 있는데 금액이 다른 건을 가려냅니다. 그리고 계좌의 잔액 컬럼은 저장된 값이지 진실이 아니므로, 개시 잔액에 원장을 더해 다시 계산한 값과 대조합니다.

정정도 방식이 있습니다. 원장의 행을 고치거나 지우지 않습니다. 빠진 거래는 정정 거래로 넣고, 잘못 나간 거래는 반대 방향 거래로 상쇄합니다. 원장에는 무슨 일이 있었는지가 순서대로 남아야 두 달 뒤 민원이 들어왔을 때 설명할 수 있습니다.

이 실습의 규칙

계산 잔액 = open_balance + 원장의 DEP 합 − 원장의 WDR 합. 차이 = stored_balance − 계산 잔액.
채널의 SUCCESS 건은 원장에 같은 channel_ref 가 있어야 짝이 맞은 것입니다. FAIL 은 원장에 없는 것이 정상입니다.
이중 출금 = TIMEOUT 이면서 원장에 실린 건 뒤 10분 안에(정확히 10분까지 포함, 시각 차는 초 단위로 셉니다), 같은 계좌·같은 유형·같은 금액으로 SUCCESS 한 건이 또 있는 경우. 10분이 지난 뒤의 같은 거래는 고객이 의도한 별개 거래로 봅니다.
정정: 빠진 건은 txn_id 를 FIX-<channel_ref> 로 원래 유형·금액 그대로 넣습니다. 잘못 실린 건은 txn_id 를 RVS-<channel_ref> 로 반대 유형(WDR 의 취소는 DEP)·같은 금액으로 넣어 상쇄합니다. 금액이 틀린 건은 상쇄한 뒤 FIX-<channel_ref> 로 올바른 금액을 다시 넣습니다. 이중 출금은 두 번째로 실린 재시도 건(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= 여덟 줄을 적으세요. 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= 다섯 줄로 적으세요.
/root/bank/recon_report.md 에 ## Reconciliation result(대사 결과), ## Transactions only in the channel(채널에만 있는 거래), ## Timeouts and double debits(타임아웃과 이중 출금), ## Correction method(정정 방법), ## Preventing recurrence(재발 방지) 다섯 절로 보고서를 쓰세요.

참고

sqlite3 -readonly core.db 로 읽고, 정정하는 7단계에서만 쓰기로 엽니다. 채널 로그는 .import --csv 로 channel 표에 적재하면 SQL 로 대사할 수 있습니다.
대사의 기본은 LEFT JOIN 두 번입니다 — 채널 기준 한 번, 원장 기준 한 번.
흔한 실수 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 이 아닌 계좌가 하나 있습니다.

채널 로그를 적재하고 양쪽에서 세기

/root/bank/recon_summary.txt 에 channel_rows=, channel_success=, channel_fail=, channel_timeout=, ledger_rows=, matched=, only_channel=, only_ledger= 여덟 줄을 적으세요. matched 는 원장에 짝이 있는 SUCCESS 건, only_channel 은 짝이 없는 SUCCESS 건, only_ledger 는 채널에 SUCCESS 짝이 없는 원장 건의 수입니다.

sqlite3 의 .import --csv 로 channel 표를 만들고(재실행을 위해 DROP TABLE IF EXISTS 먼저), channel_ref 로 ledger 와 LEFT JOIN 을 양방향으로 한 번씩 하세요. 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 입니다. 둘째는 그 결과에 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= 다섯 줄로 적으세요.

INSERT ... SELECT 로 4·5·6단계의 질의 결과를 그대로 원장에 넣되 txn_id 앞에 FIX-/RVS- 를 붙이고, RVS 는 유형을 뒤집으세요. INSERT OR IGNORE 로 두 번 돌려도 안전하게. 마지막에 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(재발 방지) 다섯 절로 보고서를 쓰세요.

task 의 절 제목 다섯 개(백틱 안의 영어 제목, 또는 괄호 안의 한국어 제목이나 같은 뜻의 일본어·중국어 제목)를 각각 ## 로 시작하는 한 줄로 쓰세요. 대사 결과에는 3단계 숫자를, 채널에만 있는 거래에는 CH0301 같은 참조를, 타임아웃 절에는 첫 건 참조 전부를, 정정 방법에는 FIX-/RVS- 를, 재발 방지에는 멱등키(idempotency key)를 적으세요.