上篇說明了對帳系統的概念模型：三本帳的關係、以結算日為基準的決策、日終對帳的五個步驟、軋差計算與差異單的分類處理，這些概念是設計實作的地基，這篇則著重於如何把地基變成可以執行的系統；本篇的重心有以下四個：對帳系統需要的核心資料表、確保批次作業可以安全重跑的冪等性設計、完整的比對與軋差驗算邏輯，以及多閘道報表匯入的介面抽象。
核心資料表設計
orders 和 refunds 表負責提供系統帳的資料來源，對帳系統在此之上還需要建立四張專屬資料表。
gateway_transactions（閘道交易記錄）
這張表儲存從閘道報表匯入的原始交易資料，是串聯系統帳和銀行帳的橋樑：
1CREATE TABLE gateway_transactions (
2 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
3 gateway_name VARCHAR(50) NOT NULL, -- 'stripe', 'tappay', 'jkopay'
4 gateway_txn_id VARCHAR(100) NOT NULL,
5 order_id UUID REFERENCES orders(id),
6 amount DECIMAL(12,2) NOT NULL,
7 fee DECIMAL(12,2) NOT NULL DEFAULT 0,
8 net_amount DECIMAL(12,2) NOT NULL, -- amount - fee
9 txn_type VARCHAR(20) NOT NULL, -- 'payment', 'refund', 'chargeback'
10 settlement_date DATE NOT NULL,
11 imported_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
12 UNIQUE (gateway_name, gateway_txn_id) -- 防止重複匯入
13);
settlement_date 是這張表最重要的欄位，比對邏輯和軋差計算都以它為基準，對應上篇說明的「以結算日為基準」決策；UNIQUE (gateway_name, gateway_txn_id) 則確保同一筆閘道交易不會被重複匯入，給匯入流程天然的冪等性保障。
bank_statements（銀行入帳記錄）
1CREATE TABLE bank_statements (
2 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
3 bank_name VARCHAR(50) NOT NULL,
4 reference_no VARCHAR(100) NOT NULL,
5 amount DECIMAL(12,2) NOT NULL,
6 txn_type VARCHAR(20) NOT NULL, -- 'credit', 'debit'
7 value_date DATE NOT NULL,
8 description TEXT,
9 imported_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
10 UNIQUE (bank_name, reference_no)
11);
reconciliation_runs（對帳執行記錄）
每次執行對帳都建立一筆記錄，這是對帳系統冪等性的核心：
1CREATE TABLE reconciliation_runs (
2 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
3 settlement_date DATE NOT NULL UNIQUE, -- 同一天只允許一筆 completed 記錄
4 status VARCHAR(20) NOT NULL, -- 'running', 'completed', 'failed'
5 started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
6 completed_at TIMESTAMPTZ,
7 total_matched INT DEFAULT 0,
8 total_exceptions INT DEFAULT 0
9);
settlement_date UNIQUE 用以約束確保同一個結算日不會產生兩次完整的對帳結果，若需要重跑，則要先將記錄狀態改為 failed、刪除對應的差異單，再重新執行，而不是直接新增。
reconciliation_exceptions（差異單）
1CREATE TABLE reconciliation_exceptions (
2 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
3 run_id UUID NOT NULL REFERENCES reconciliation_runs(id),
4 exception_type VARCHAR(50) NOT NULL,
5 severity VARCHAR(10) NOT NULL
6 CHECK (severity IN ('critical', 'warning', 'info')),
7 order_id UUID,
8 gateway_txn_id VARCHAR(100),
9 system_amount DECIMAL(12,2),
10 gateway_amount DECIMAL(12,2),
11 description TEXT NOT NULL,
12 status VARCHAR(20) NOT NULL DEFAULT 'open'
13 CHECK (status IN ('open', 'in_review', 'resolved')),
14 resolved_by VARCHAR(100),
15 resolved_at TIMESTAMPTZ,
16 resolution_note TEXT,
17 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
18);
resolution_note 欄位記錄人工處理的說明，是稽核軌跡的重要組成，財務人員解決差異單時，必須在這個欄位填寫原因，以備後續審計。
對帳作業的冪等性設計
對帳作業本身必須保持冪等性，批次作業可能因為各種原因中途失敗，需要能夠安全地重新執行，不能重跑一次就多出一批重複的差異單；冪等性的保障方式在 reconciliation_runs 表以 settlement_date 做唯一約束，對帳開始前先查詢當天是否已有 completed 的記錄，若有則直接跳過：
1public async Task RunAsync(DateOnly settlementDate)
2{
3 // 冪等性檢查
4 var existing = await _db.ReconciliationRuns
5 .FirstOrDefaultAsync(r => r.SettlementDate == settlementDate);
6
7 if (existing?.Status == "completed")
8 {
9 _logger.LogInformation(
10 "Reconciliation for {Date} already completed, skipping",
11 settlementDate);
12 return;
13 }
14
15 // 建立或重用執行記錄
16 var run = existing ?? new ReconciliationRun
17 {
18 SettlementDate = settlementDate,
19 Status = "running"
20 };
21 run.Status = "running";
22 run.StartedAt = DateTime.UtcNow;
23
24 if (existing == null) _db.ReconciliationRuns.Add(run);
25 await _db.SaveChangesAsync();
26
27 try
28 {
29 await ExecuteReconciliationAsync(run, settlementDate);
30 run.Status = "completed";
31 run.CompletedAt = DateTime.UtcNow;
32 }
33 catch (Exception ex)
34 {
35 run.Status = "failed";
36 _logger.LogError(ex, "Reconciliation failed for {Date}", settlementDate);
37 throw;
38 }
39 finally
40 {
41 await _db.SaveChangesAsync();
42 }
43}
若需要重跑（例如閘道報表資料有誤，需要重新匯入），則要先將 reconciliation_runs 的記錄狀態改為 failed，刪除對應的 reconciliation_exceptions，再重新執行；每次重跑都從乾淨的狀態開始，不會累加錯誤數據。
核心對帳邏輯的完整實作
1private async Task ExecuteReconciliationAsync(
2 ReconciliationRun run, DateOnly settlementDate)
3{
4 var exceptions = new List<ReconciliationException>();
5
6 // Step 1：讀取系統帳（當日已結算訂單）
7 var systemOrders = await _db.Orders
8 .Where(o => o.SettledDate == settlementDate
9 && o.Status == OrderStatus.Settled)
10 .ToListAsync();
11
12 // Step 2：讀取閘道報表（已匯入）
13 var gatewayTxns = await _db.GatewayTransactions
14 .Where(g => g.SettlementDate == settlementDate
15 && g.TxnType == "payment")
16 .ToListAsync();
17
18 // Step 3：交易比對（系統帳 → 閘道報表）
19 foreach (var order in systemOrders)
20 {
21 var gw = gatewayTxns.FirstOrDefault(g => g.OrderId == order.Id);
22
23 if (gw == null)
24 {
25 exceptions.Add(new ReconciliationException
26 {
27 RunId = run.Id,
28 ExceptionType = "one_sided",
29 Severity = "warning",
30 OrderId = order.Id,
31 SystemAmount = order.CapturedAmount,
32 Description = $"系統帳有訂單 {order.Id}，閘道報表無對應記錄"
33 });
34 }
35 else if (Math.Abs(order.CapturedAmount - gw.NetAmount) > 0.01m)
36 {
37 exceptions.Add(new ReconciliationException
38 {
39 RunId = run.Id,
40 ExceptionType = "amount_mismatch",
41 Severity = "critical",
42 OrderId = order.Id,
43 GatewayTxnId = gw.GatewayTxnId,
44 SystemAmount = order.CapturedAmount,
45 GatewayAmount = gw.NetAmount,
46 Description = $"金額差異 {order.CapturedAmount - gw.NetAmount:N2} TWD"
47 });
48 }
49 }
50
51 // Step 3（反向）：閘道報表有、系統帳沒有的交易
52 var matchedOrderIds = systemOrders.Select(o => o.Id).ToHashSet();
53 foreach (var gw in gatewayTxns.Where(g => !matchedOrderIds.Contains(g.OrderId!.Value)))
54 {
55 exceptions.Add(new ReconciliationException
56 {
57 RunId = run.Id,
58 ExceptionType = "one_sided",
59 Severity = "warning",
60 GatewayTxnId = gw.GatewayTxnId,
61 GatewayAmount = gw.NetAmount,
62 Description = $"閘道報表有交易 {gw.GatewayTxnId}，系統帳無對應訂單"
63 });
64 }
65
66 // Step 4：軋差驗算
67 var totalSystemAmount = systemOrders.Sum(o => o.CapturedAmount);
68 var totalRefunds = await _db.Refunds
69 .Where(r => r.SettledDate == settlementDate && r.Status == "succeeded")
70 .SumAsync(r => r.Amount);
71 var totalFees = gatewayTxns.Sum(g => g.Fee);
72 var expectedNet = totalSystemAmount - totalRefunds - totalFees;
73
74 var bankDeposit = await _db.BankStatements
75 .Where(b => b.ValueDate == settlementDate && b.TxnType == "credit")
76 .SumAsync(b => b.Amount);
77
78 if (Math.Abs(expectedNet - bankDeposit) > 1m)
79 {
80 exceptions.Add(new ReconciliationException
81 {
82 RunId = run.Id,
83 ExceptionType = "netting_mismatch",
84 Severity = "critical",
85 SystemAmount = expectedNet,
86 GatewayAmount = bankDeposit,
87 Description = $"軋差不符：預期淨額 {expectedNet:N2}，" +
88 $"銀行實際入帳 {bankDeposit:N2}，" +
89 $"差距 {expectedNet - bankDeposit:N2} TWD"
90 });
91 }
92
93 // Step 5：儲存結果並通知
94 _db.ReconciliationExceptions.AddRange(exceptions);
95 run.TotalMatched = systemOrders.Count - exceptions.Count(e => e.OrderId != null);
96 run.TotalExceptions = exceptions.Count;
97 await _db.SaveChangesAsync();
98
99 var criticalItems = exceptions.Where(e => e.Severity == "critical").ToList();
100 if (criticalItems.Any())
101 await _notifier.SendAlertAsync(settlementDate, criticalItems);
102}
比對邏輯有兩個方向：系統帳掃閘道報表（找出系統有但閘道沒有的）、閘道報表掃系統帳（找出閘道有但系統沒有的），兩個方向都要跑，才能抓到所有的單邊差異；軋差允許 NT$1 以內的差距，超過才產生差異單，這個閾值應和財務部門共同定義。
閘道報表的匯入設計
閘道報表的匯入是對帳系統的資料入口，需要能處理不同閘道的格式差異，同時保證冪等性，因此建議抽象出 IGatewayReportImporter 介面，每個閘道各自實作：
1public interface IGatewayReportImporter
2{
3 string GatewayName { get; }
4 Task<IEnumerable<GatewayTransaction>> FetchAsync(DateOnly settlementDate);
5}
6
7public class StripeReportImporter : IGatewayReportImporter
8{
9 public string GatewayName => "stripe";
10
11 public async Task<IEnumerable<GatewayTransaction>> FetchAsync(DateOnly settlementDate)
12 {
13 // 呼叫 Stripe Balance Transactions API
14 // 篩選 created 在 settlementDate 範圍內的記錄
15 // 轉換格式並回傳
16 }
17}
18
19public class TapPayReportImporter : IGatewayReportImporter
20{
21 public string GatewayName => "tappay";
22
23 public async Task<IEnumerable<GatewayTransaction>> FetchAsync(DateOnly settlementDate)
24 {
25 // 從 SFTP 下載 CSV
26 // 解析 CSV 格式
27 // 轉換格式並回傳
28 }
29}
這個設計和支付生命週期篇的 IPaymentGateway 介面是同一套思路：把各閘道的格式差異封裝在實作類別裡，核心對帳邏輯不需要知道底層是 Stripe 的 API 還是 TapPay 的 CSV，新增一個閘道只需要新增一個實作類別；匯入時利用 UNIQUE (gateway_name, gateway_txn_id) 約束做去重，使用 INSERT ... ON CONFLICT DO NOTHING 讓重複匯入安全地跳過：
1INSERT INTO gateway_transactions
2 (id, gateway_name, gateway_txn_id, order_id, amount, fee, net_amount,
3 txn_type, settlement_date, imported_at)
4VALUES
5 (@id, @gateway_name, @gateway_txn_id, @order_id, @amount, @fee, @net_amount,
6 @txn_type, @settlement_date, NOW())
7ON CONFLICT (gateway_name, gateway_txn_id) DO NOTHING;
從資料表到程式碼，對帳系統的工程設計有幾個一致貫穿的原則：
- 四張表各有職責：
gateway_transactions是系統帳和銀行帳之間的橋樑，bank_statements是最終的資金驗證來源，reconciliation_runs是對帳作業的冪等性錨點，reconciliation_exceptions是財務人員的工作佇列，也是稽核軌跡的記錄。 - 對帳作業本身必須冪等：
reconciliation_runs表的唯一約束和狀態設計，確保重跑不會產生重複的差異單或累加的錯誤數據。 - 比對要跑兩個方向：系統帳掃閘道報表、閘道報表掃系統帳，兩個方向都跑才能抓到所有的單邊差異，少一個方向就會有漏網之魚。
- 閘道報表匯入要冪等：
UNIQUE constraint加ON CONFLICT DO NOTHING是最簡單可靠的去重機制，讓重複執行匯入腳本不會造成問題。 - 介面抽象讓多閘道可維護：
IGatewayReportImporter把各閘道的格式差異封裝起來，核心對帳邏輯保持整潔，新增閘道不動核心程式碼。 - 稽核軌跡不可缺：每一筆差異單的處理過程都要記錄在
resolution_note，讓未來的審計有跡可循。
對帳系統設計好之後，下一個值得深入的主題是 Outbox Pattern，確保訂單狀態更新和通知送出在同一個原子操作內完成，解決支付系統中「資料庫寫入成功，但 Webhook 沒送出去」這個棘手的可靠性問題。
本文為個人學習筆記，持續更新中。