Columns › 7. Pitfalls in practice › Part 29
Reconciliation: Matching Payment Provider Statements Reliably
對帳:金流對帳單怎麼對才可靠
Two systems recording the same money will always drift apart, and reconciliation is how you find every difference, every day. This article covers fetching and keeping statements, parsing and day cut-offs, line-by-line matching, classifying and handling differences, tying the ledger to payouts, and alerts and month-end close, with a tested Go reconciler.
In this article
- What this article solves
- Step 1: what reconciliation is and isn't
- Step 2: fetching statements reliably
- Step 3: matching line by line
- Step 4: classifying differences
- Step 5: handling differences
- Step 6: tying the ledger to the bank payout
- Step 7: running it in production
- Running the tests
- Common pitfalls
- Key takeaways
- Next
The previous article, “Preventing Duplicate Payments and Handling Payment Incidents: From the Button to the Provider, and How to Stop the Bleeding”, blocked duplicates all the way from the button to the payment provider and listed the steps for stopping the bleeding when something goes wrong. But however good the defences are, a few records always slip through: every webhook retry fails, one release writes the wrong status, someone refunds by hand in the provider's dashboard, the fee doesn't match the contract. None of these make the program fail; they just make the books slowly stop adding up, and the only way to find them is reconciliation.
Problems 8 and 9 of “Payment Pitfalls: Double Charges, Callbacks and Reconciliation” already gave the short versions of daily reconciliation and double-entry bookkeeping. This article expands them into a complete approach: how to fetch and keep statements, and how to parse them without being fooled by encodings and time zones; what the matching key is and what to do with one-to-many cases; which kinds of difference exist and which ones are worth waking someone up for; how to track a difference until it is closed and why amounts must never be edited directly; and finally how to make the ledger, the statement and the bank payout agree.
What this article solves
We continue the series example: the payment service of the online shop example.com/shop keeps payments and refunds in PostgreSQL, with every amount as an int64 in minor units. Each time it asks the payment provider to charge, it sends the payment attempt's ID (for example pay_1001) as the merchant trade no (providers call it different things: merchant trade no, merchant reference, order ID); the provider replies with its own transaction ID. Every day the provider publishes a statement, and on the agreed settlement date it transfers the net amount, after fees, into our bank account.
Step by step, this article will:
- Make clear which parties reconciliation compares, how often, and who owns it.
- Fetch statements reliably: scheduling, retries, raw files that are never edited, imports that can be rerun.
- Parse statements: BOMs, CSV quoting, encodings, amounts as integers, and the easiest thing to get wrong, time zones and the day cut-off.
- Match line by line: keys, one-to-many cases, fees, refunds and chargebacks, status mapping.
- Classify differences: which ones are just timing and which ones mean the user has already been charged.
- Handle differences: the discrepancy table, its lifecycle, auto-resolution rules, adjusting entries, idempotent corrective actions.
- Tie the ledger to the bank payout, and run it in production: alerts, reruns, data retention and month-end close.
The code lives in an internal/recon package; all of it compiles and has been tested with go test -race.
Tip: statement formats vary a lot between payment providers. Some drop a CSV on SFTP every day, some make you download it through an API, some use fixed-width text, and column names and day definitions differ too. This article uses a common CSV layout as the example; when you integrate for real, always follow your provider's own documentation.
Step 1: what reconciliation is and isn't
Three-way reconciliation
The money of one payment leaves records in three places:
- Our records: the
paymentservice's payments and refunds, and the order status. - The provider's statement: which transactions it considers successful, for how much, what fee it took, and when it pays out.
- The bank: how much the provider actually transferred.
Reconciliation compares these three in pairs. Our records and the statement are compared line by line: every payment should have exactly one matching statement line with the same amount, currency and status. The statement and the bank are compared by batch: the net amount of a settlement date (gross payments minus refunds, chargebacks and fees) should equal what the bank actually received that day. In the middle sits the ledger: every successful payment, fee, refund and payout is written as double-entry lines, so the balance of the "receivable from provider" account must equal the net amount the provider has confirmed but not yet paid out.
Figure: our records and the statement are compared line by line, the statement and the bank payout by batch, and all three must tie to the ledger.
What reconciliation is not
- Not a replacement for monitoring. Reconciliation usually runs the next day, so by the time it finds something, the money has been taken for a day already. Failing webhooks or too many
unknownpayments must be caught first by real-time metrics and alerts (“Observability in Go: Logs, Metrics and Tracing”); reconciliation is the last safety net. - Not a shortcut for fixing data. Once reconciliation finds a difference, the fix must go through proper actions: query the provider, record the status, refund, post an adjusting entry. Never
UPDATE payments SET amount = .... - Not just comparing totals. Equal totals don't mean every line is right: one payment overcharged by 100 and another undercharged by 100 add up to the same total. Totals are only a quick first check.
Once a day, or in real time?
Most providers produce one statement per day covering the previous day, so daily reconciliation is the most common rhythm. Some providers also offer a transaction query API, which allows near-real-time comparison, for example querying recent payments every hour. The two don't conflict: real-time queries find problems earlier (that is exactly the previous article's sweeper for stuck pending payments), while daily reconciliation is based on the statement the provider officially produces, and that is what month-end close and accounting rely on.
Who owns it
Reconciliation involves engineering, finance and customer support, and its most common failure is not technical but nobody owning it: discrepancies sitting in a table for three months without anyone looking. Before launch, settle who gets the alert when the reconciliation job fails (engineering), who makes the first call on a difference (usually finance or operations), who takes it when code needs checking or data needs fixing (engineering), who contacts users when needed (support), and who can approve a write-off (a manager).
Step 2: fetching statements reliably
Scheduled fetching and retries
Statements usually only appear after a certain time, for example "the previous day's file can be downloaded after a certain hour in the morning", and in practice they are often late. The fetch job should:
- Start fetching after the expected time, back off and retry when the file isn't there yet (“Timeouts, Retries and Circuit Breakers”), and alert only after a deadline (for example noon), not on the first failure.
- Write a run record for every attempt: which recon day, where it fetched from, which file it got, how many rows, success or failure, and the error. Without this table you cannot answer "was the 14 November file ever imported?".
- Record the unpacking step if the file is compressed or encrypted, and make it rerunnable too.
Raw files are never edited
A statement is evidence. The fetched file must be kept exactly as received, usually in object storage, together with its SHA-256 hash, size and fetch time. Everything afterwards is recomputed from that raw file, and nobody edits it: if the parser is wrong, fix the parser and rerun; if the provider says the file is wrong, ask it to re-issue one and keep both. Object storage can enable versioning or a write-once retention setting, so this is enforced rather than just agreed.
Staging: an import you can rerun
Each parsed line is first written to a staging table, and matched from there. The import must be idempotent (“Idempotency and Distributed Locks”): a scheduled retry, a manual rerun or two processes running at once must all give the same result as running once. The approach:
- Each line's unique key is "file hash + line number", so importing the same file twice adds no lines.
- When importing a recon day, delete that day's staged lines and write the file's lines in the same transaction. A corrected file re-issued by the provider (a different hash) then replaces the whole day instead of mixing with the old one.
- Serialize imports of the same recon day with an advisory lock, so two interleaved transactions can't leave a mixture of two versions.
CREATE TABLE statement_files (
sha256 char(64) PRIMARY KEY,
provider text NOT NULL,
recon_day date NOT NULL,
name text NOT NULL,
object_key text NOT NULL, -- location in object storage, e.g. recon/raw/2026/11/14/<sha256>.csv
byte_size bigint NOT NULL,
fetched_at timestamptz NOT NULL DEFAULT now(),
superseded_by char(64) REFERENCES statement_files (sha256) -- the corrected file that replaced it
);
CREATE TABLE ingestion_runs (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
provider text NOT NULL,
recon_day date NOT NULL,
file_sha256 char(64) REFERENCES statement_files (sha256),
status text NOT NULL CHECK (status IN ('running', 'staged', 'matched', 'failed')),
row_count int,
error text,
started_at timestamptz NOT NULL DEFAULT now(),
finished_at timestamptz
);
CREATE TABLE statement_lines (
file_sha256 char(64) NOT NULL REFERENCES statement_files (sha256),
line_no int NOT NULL,
provider text NOT NULL,
recon_day date NOT NULL,
merchant_trade_no text NOT NULL,
provider_txn_id text NOT NULL,
kind text NOT NULL CHECK (kind IN ('payment', 'refund', 'chargeback')),
status text NOT NULL CHECK (status IN ('succeeded', 'failed')),
amount bigint NOT NULL CHECK (amount > 0), -- minor units
fee bigint NOT NULL,
currency char(3) NOT NULL,
transacted_at timestamptz NOT NULL,
settlement_date date NOT NULL,
PRIMARY KEY (file_sha256, line_no)
);
CREATE INDEX statement_lines_day ON statement_lines (provider, recon_day);
CREATE INDEX statement_lines_trade_no ON statement_lines (merchant_trade_no);
-- Import one recon day: $1 provider, $2 recon_day, $3 the file's sha256
BEGIN;
SELECT pg_advisory_xact_lock(hashtext('recon:' || $1 || ':' || $2::text));
DELETE FROM statement_lines WHERE provider = $1 AND recon_day = $2;
-- one INSERT per line, or a single COPY; rerunning the same file adds no lines
INSERT INTO statement_lines (file_sha256, line_no, provider, recon_day, merchant_trade_no,
provider_txn_id, kind, status, amount, fee, currency, transacted_at, settlement_date)
VALUES ($3, $4, $1, $2, $5, $6, $7, $8, $9, $10, $11, $12, $13)
ON CONFLICT (file_sha256, line_no) DO NOTHING;
UPDATE statement_files SET superseded_by = $3
WHERE provider = $1 AND recon_day = $2 AND sha256 <> $3 AND superseded_by IS NULL;
COMMIT;To keep the illustration short, the SQL above shows all the parameters together; in real code the INSERT runs once per line (or is replaced by COPY). Transactions and connections are covered in “database/sql, Transactions and Connection Pools in Go”.
Figure: fetch, keep the raw file, parse and stage first; only then check totals, match line by line, record discrepancies and alert. Every run leaves a record.
Parsing pitfalls: BOMs, quotes and encodings
CSV looks simple, yet you will hit almost every pitfall:
- BOM. UTF-8 files saved by spreadsheet software often start with a three-byte BOM (
EF BB BF). Left alone, the first column name becomesmerchant_trade_no, and looking up columns by name fails. - Quotes. A field containing a comma or newline is wrapped in double quotes, for example
"1,000". Never split withstrings.Split(line, ",")yourself; useencoding/csv. It also handles newlines inside quotes, at which point "line number" and "record number" are no longer the same. - Encoding. Some systems don't produce UTF-8 but Big5 or another encoding. Check
utf8.Validfirst and reject the whole file if it isn't UTF-8, leaving it to a conversion step (for examplegolang.org/x/text/encoding), rather than letting garbled trade numbers into matching, where they become a pile of "only on the provider's side". - Column order. Find columns by the names in the header row instead of hard-coding positions, so a new column from the provider doesn't make the program read the wrong field. If a required column is missing, reject the whole file.
- Amounts. Statement amounts are usually decimal strings such as
12.50or1,000. Convert them straight to integer minor units without ever usingfloat64; more decimal places than the currency allows is an error, not something to round quietly. The New Taiwan dollar has 2 decimal places in ISO 4217, but many providers express it in whole dollars, so1,000must become100000(in cents); that conversion belongs in the parsing layer, and the next article covers it in full. - Signs. Some statements write refunds as negative numbers, others as positive numbers with a type column. Normalize while parsing: amounts are always positive, and the kind gives the direction.
- Status. Map the provider's status strings (
SUCCESS,PAID,FAILED, ...) to our vocabulary while parsing; if a value is unknown, stop the whole file instead of guessing.
One last principle: if one line fails to parse, the whole file is not imported. Skipping broken lines looks "flexible", but it really makes that transaction disappear from reconciliation, where nobody will ever notice it again.
Time zones and the day cut-off
This is the most common and least visible reconciliation problem. Our shop is in Taipei and naturally defines "14 November" as 00:00 to 24:00 Taipei time; the provider's statement may cut days in UTC, or in its own local time zone. Midnight in Taipei is 16:00 UTC, so a payment made at 00:00:30 on 15 November Taipei time appears in the provider's 14 November (UTC) file.
Figure: payment A belongs to 11/14 on both sides; payment B is 11/15 in Taipei but 11/14 in the UTC file.
There are three things to do:
- Choose one definition of the recon day and document it. For example, "recon day = the calendar date of the transaction time in Asia/Taipei". All reports, discrepancies and month-end close use the same definition.
- Give times the right time zone while parsing. If the statement's time fields carry no zone, use the zone the provider documents (
time.ParseInLocation), then convert to the agreed zone to take the date. - Pull in the neighbouring day when matching. Even with the same day definition, the time we record (when the payment was created) and the provider's transaction time can differ by a few seconds and straddle midnight. So records from the previous day that are unmatched but still waiting are carried over and compared again the next day (the auto-resolution rule in step 5).
Taiwan has no daylight saving time, but if your business is somewhere that does, a day can be 23 or 25 hours long; even more reason never to compute day boundaries by "adding 24 hours", and always to convert through the time zone database.
The parser
ParseCSV handles every pitfall above: it strips the BOM, checks UTF-8, finds columns by name, converts amounts to integers, maps statuses, attaches the time zone, and rejects the whole file if any line is wrong, with the line number in the error.
// Package recon matches payment provider statements against our own payment records, line by line.
package recon
import (
"bytes"
"encoding/csv"
"errors"
"fmt"
"io"
"strconv"
"strings"
"time"
"unicode/utf8"
)
// Kind is the kind of transaction on a statement line. Payments, refunds and chargebacks each get their own line and never net off.
type Kind string
const (
Payment Kind = "payment"
Refund Kind = "refund"
Chargeback Kind = "chargeback"
)
// Row is one statement line; amounts are always converted to int64 minor units.
type Row struct {
Line int // line where this record starts in the file; unique together with the file hash
TradeNo string // the merchant trade no we sent (payment attempt ID or refund ID)
ProviderTxnID string // the provider's transaction ID
Kind Kind
Status string // mapped to our vocabulary: succeeded or failed
Amount int64 // gross amount, always positive; Kind gives the direction
Fee int64 // fee for this line; negative when a refund returns the fee
Currency string // ISO 4217
At time.Time // transaction time, with its time zone
SettleDate string // settlement (payout) date YYYY-MM-DD
}
// ErrBadRow means the statement is not what we expect; the whole file is rejected rather than skipping bad lines.
var ErrBadRow = errors.New("recon: bad statement")
// exponents are the currency decimal places (ISO 4217); the next article covers them fully.
var exponents = map[string]int{"TWD": 2, "USD": 2, "JPY": 0}
// statusMap maps the provider's status strings to our vocabulary; anything unknown is an error, never a guess.
var statusMap = map[string]string{"SUCCESS": "succeeded", "PAID": "succeeded", "FAILED": "failed"}
var columns = []string{"merchant_trade_no", "provider_txn_id", "type", "status",
"amount", "fee", "currency", "transacted_at", "settlement_date"}
// ParseCSV parses one statement. providerTZ is the time zone of the statement's time fields (from the provider's documentation).
func ParseCSV(data []byte, providerTZ *time.Location) ([]Row, error) {
data = bytes.TrimPrefix(data, []byte("\xef\xbb\xbf")) // UTF-8 saved by spreadsheet software often starts with a BOM
if !utf8.Valid(data) {
return nil, fmt.Errorf("%w: not UTF-8, convert the encoding first", ErrBadRow)
}
cr := csv.NewReader(bytes.NewReader(data))
header, err := cr.Read()
if err != nil {
return nil, fmt.Errorf("%w: header: %v", ErrBadRow, err)
}
idx := make(map[string]int, len(header))
for i, h := range header {
idx[strings.ToLower(strings.TrimSpace(h))] = i
}
for _, c := range columns {
if _, ok := idx[c]; !ok {
return nil, fmt.Errorf("%w: missing column %q", ErrBadRow, c)
}
}
var rows []Row
for {
rec, err := cr.Read()
if err == io.EOF {
return rows, nil
}
if err != nil {
return nil, fmt.Errorf("%w: %v", ErrBadRow, err)
}
line, _ := cr.FieldPos(0) // still the line where the record starts, even with newlines inside quotes
get := func(c string) string { return strings.TrimSpace(rec[idx[c]]) }
r, err := parseRow(get, providerTZ)
if err != nil {
return nil, fmt.Errorf("%w: line %d: %v", ErrBadRow, line, err)
}
r.Line = line
rows = append(rows, r)
}
}
func parseRow(get func(string) string, tz *time.Location) (Row, error) {
r := Row{
TradeNo: get("merchant_trade_no"),
ProviderTxnID: get("provider_txn_id"),
Kind: Kind(strings.ToLower(get("type"))),
Currency: strings.ToUpper(get("currency")),
SettleDate: get("settlement_date"),
}
switch r.Kind {
case Payment, Refund, Chargeback:
default:
return Row{}, fmt.Errorf("unknown type %q", r.Kind)
}
if r.TradeNo == "" || r.ProviderTxnID == "" {
return Row{}, errors.New("missing merchant_trade_no or provider_txn_id")
}
var ok bool
if r.Status, ok = statusMap[strings.ToUpper(get("status"))]; !ok {
return Row{}, fmt.Errorf("unknown status %q", get("status"))
}
exp, ok := exponents[r.Currency]
if !ok {
return Row{}, fmt.Errorf("unknown currency %q", r.Currency)
}
var err error
if r.Amount, err = parseMinor(get("amount"), exp); err != nil {
return Row{}, err
}
if r.Amount < 0 && r.Kind != Payment {
r.Amount = -r.Amount // some statements write refunds as negatives; Kind alone carries the direction
}
if r.Amount <= 0 {
return Row{}, fmt.Errorf("amount must be positive, got %q", get("amount"))
}
if r.Fee, err = parseMinor(get("fee"), exp); err != nil {
return Row{}, err
}
if r.At, err = time.ParseInLocation(time.DateTime, get("transacted_at"), tz); err != nil {
return Row{}, fmt.Errorf("transacted_at: %v", err)
}
if _, err = time.Parse(time.DateOnly, r.SettleDate); err != nil {
return Row{}, fmt.Errorf("settlement_date: %v", err)
}
return r, nil
}
// parseMinor turns a decimal string such as "1,234.5" into integer minor units without ever using float64.
// More decimal places than the currency allows is an error, not a silent rounding.
func parseMinor(s string, exp int) (int64, error) {
t := strings.ReplaceAll(s, ",", "") // thousands separators: only possible when the field is quoted
neg := strings.HasPrefix(t, "-")
whole, frac, _ := strings.Cut(strings.TrimPrefix(t, "-"), ".")
if whole == "" || len(frac) > exp || strings.ContainsAny(whole+frac, "+-") {
return 0, fmt.Errorf("invalid amount %q", s)
}
n, err := strconv.ParseInt(whole+frac+strings.Repeat("0", exp-len(frac)), 10, 64)
if err != nil {
return 0, fmt.Errorf("invalid amount %q", s)
}
if neg {
n = -n
}
return n, nil
}
// Day returns the recon day a transaction belongs to: convert to the agreed time zone, then take the calendar date.
func Day(t time.Time, loc *time.Location) string {
return t.In(loc).Format(time.DateOnly)
}A few details: cr.FieldPos(0) returns the line where the record starts in the file, which stays correct with newlines inside quotes; together with the file hash it is the staging table's unique key, so you can always go back to the raw file. parseMinor drops thousands separators, then joins the integer part with the zero-padded fractional part and converts once with strconv.ParseInt, which also returns an error on overflow. exponents only lists the three currencies the example needs; full currency handling is left to the next article.
The tests cover the BOM, thousands separators inside quotes, an upper-case type, a negative refund, the day cut-off and a range of bad files:
package recon
import (
"errors"
"strings"
"testing"
"time"
)
// Taiwan has no daylight saving time, so the test uses a fixed zone and doesn't depend on the machine's time zone database.
var taipei = time.FixedZone("Asia/Taipei", 8*60*60)
const header = "merchant_trade_no,provider_txn_id,type,status,amount,fee,currency,transacted_at,settlement_date\n"
func TestParseCSVHandlesBOMQuotingAndNegativeRefunds(t *testing.T) {
data := "\xef\xbb\xbf" + header +
`pay_1001,T9001,payment,SUCCESS,"1,000",28,TWD,2026-11-14 02:00:00,2026-11-16` + "\n" +
`"pay_1002","T9002",PAYMENT,success,"12.50",0.36,USD,2026-11-14 03:00:00,2026-11-16` + "\n" +
`ref_1001,T9003,refund,SUCCESS,-200,0,TWD,2026-11-14 04:00:00,2026-11-16` + "\n"
rows, err := ParseCSV([]byte(data), time.UTC)
if err != nil {
t.Fatal(err)
}
want := []struct {
trade string
kind Kind
amount int64
fee int64
line int
}{
{"pay_1001", Payment, 100000, 2800, 2}, // TWD 1,000 = 100000 (in cents)
{"pay_1002", Payment, 1250, 36, 3},
{"ref_1001", Refund, 20000, 0, 4}, // the negative refund becomes positive; Kind gives the direction
}
if len(rows) != len(want) {
t.Fatalf("got %d rows, want %d", len(rows), len(want))
}
for i, w := range want {
r := rows[i]
if r.TradeNo != w.trade || r.Kind != w.kind || r.Amount != w.amount || r.Fee != w.fee || r.Line != w.line {
t.Errorf("row %d = %+v, want %+v", i, r, w)
}
}
}
func TestDayCutOff(t *testing.T) {
// The statement uses UTC; our recon day is the calendar date in Taipei
data := header +
"pay_1,T1,payment,SUCCESS,100,0,TWD,2026-11-14 15:59:59,2026-11-16\n" +
"pay_2,T2,payment,SUCCESS,100,0,TWD,2026-11-14 16:00:00,2026-11-17\n"
rows, err := ParseCSV([]byte(data), time.UTC)
if err != nil {
t.Fatal(err)
}
if got := Day(rows[0].At, taipei); got != "2026-11-14" {
t.Errorf("23:59:59 Taipei: day %s", got)
}
if got := Day(rows[1].At, taipei); got != "2026-11-15" {
t.Errorf("00:00:00 Taipei: day %s", got)
}
if got := Day(rows[1].At, time.UTC); got != "2026-11-14" {
t.Errorf("same row, provider's UTC day: %s", got)
}
}
func TestParseCSVRejectsWholeFile(t *testing.T) {
ok := "pay_1,T1,payment,SUCCESS,100,0,TWD,2026-11-14 01:00:00,2026-11-16\n"
cases := map[string]struct{ data, msg string }{
"missing column": {"merchant_trade_no,amount\npay_1,100\n", `missing column "provider_txn_id"`},
"unknown status": {header + ok + "pay_2,T2,payment,PROCESSING,100,0,TWD,2026-11-14 01:00:00,2026-11-16\n", "line 3: unknown status"},
"too many digits": {header + "pay_2,T2,payment,SUCCESS,10.005,0,USD,2026-11-14 01:00:00,2026-11-16\n", `line 2: invalid amount "10.005"`},
"negative charge": {header + "pay_2,T2,payment,SUCCESS,-100,0,TWD,2026-11-14 01:00:00,2026-11-16\n", "line 2: amount must be positive"},
"bad time": {header + "pay_2,T2,payment,SUCCESS,100,0,TWD,2026/11/14 01:00,2026-11-16\n", "line 2: transacted_at"},
"not utf-8": {header + "pay_2,T2,payment,SUCCESS,100,0,TWD,2026-11-14 01:00:00,2026-11-16,\xa4\xa4\n", "not UTF-8"},
"field count": {header + ok + "pay_2,T2,payment\n", "wrong number of fields"},
}
for name, c := range cases {
t.Run(name, func(t *testing.T) {
rows, err := ParseCSV([]byte(c.data), time.UTC)
if !errors.Is(err, ErrBadRow) || !strings.Contains(err.Error(), c.msg) {
t.Fatalf("err = %v, want %q", err, c.msg)
}
if rows != nil {
t.Fatalf("got %d rows from a bad file", len(rows))
}
})
}
}TestDayCutOff uses time.FixedZone because Taiwan has no daylight saving time, so a fixed offset is enough, and the test doesn't depend on whether the environment has a time zone database. In production code, prefer time.LoadLocation("Asia/Taipei") and import time/tzdata in the main program, so it still works in a container image without time zone data.
An idempotent import
Ingest chains "keep the raw file, parse, replace the staged day". It depends on a small Store interface: in production that is object storage plus the SQL above, in tests an in-memory version.
package recon
import (
"context"
"crypto/sha256"
"encoding/hex"
"fmt"
"time"
)
// Store is the storage an import needs: object storage plus PostgreSQL in production, an in-memory version in tests.
type Store interface {
// PutRaw keeps the raw file; if the hash already exists it does nothing, raw files are never rewritten.
PutRaw(ctx context.Context, sum, name string, data []byte) error
// ReplaceDay deletes the recon day's staged lines and writes this file's lines in one transaction,
// each keyed by file hash + line number.
ReplaceDay(ctx context.Context, day, sum string, rows []Row) error
}
// Ingest imports a recon day's statement and returns the file's SHA-256.
// Rerunning the same file gives the same result; a corrected file (different hash) replaces the whole staged day.
func Ingest(ctx context.Context, s Store, day, name string, data []byte, providerTZ *time.Location) (string, error) {
h := sha256.Sum256(data)
sum := hex.EncodeToString(h[:])
// Keep the raw file before parsing: if parsing fails, the evidence is already saved
if err := s.PutRaw(ctx, sum, name, data); err != nil {
return "", fmt.Errorf("recon: save raw %s: %w", name, err)
}
rows, err := ParseCSV(data, providerTZ)
if err != nil {
return sum, fmt.Errorf("recon: parse %s: %w", name, err)
}
if err := s.ReplaceDay(ctx, day, sum, rows); err != nil {
return sum, fmt.Errorf("recon: stage %s: %w", name, err)
}
return sum, nil
}Note the order: keep the raw file first, then parse. The other way round, a file that fails to parse would not be kept, and later nobody could look at what that broken file actually contained.
package recon
import (
"bytes"
"context"
"fmt"
"sync"
"testing"
"time"
)
// memStore is an in-memory Store that behaves like the PostgreSQL one: raw files are written once, days are replaced whole.
type memStore struct {
mu sync.Mutex
raw map[string][]byte
days map[string]map[string]Row // recon day → "hash:line" → row
}
func newMemStore() *memStore {
return &memStore{raw: map[string][]byte{}, days: map[string]map[string]Row{}}
}
func (m *memStore) PutRaw(_ context.Context, sum, _ string, data []byte) error {
m.mu.Lock()
defer m.mu.Unlock()
if _, ok := m.raw[sum]; !ok {
m.raw[sum] = bytes.Clone(data)
}
return nil
}
func (m *memStore) ReplaceDay(_ context.Context, day, sum string, rows []Row) error {
staged := make(map[string]Row, len(rows))
for _, r := range rows {
staged[fmt.Sprintf("%s:%d", sum, r.Line)] = r
}
m.mu.Lock()
defer m.mu.Unlock()
m.days[day] = staged
return nil
}
func TestIngestIsIdempotent(t *testing.T) {
ctx := context.Background()
s := newMemStore()
v1 := []byte(header +
"pay_1,T1,payment,SUCCESS,100,0,TWD,2026-11-14 01:00:00,2026-11-16\n" +
"pay_2,T2,payment,SUCCESS,250,0,TWD,2026-11-14 02:00:00,2026-11-16\n")
// A scheduled retry and a manual rerun can overlap: importing one file eight times must equal importing it once
var wg sync.WaitGroup
for range 8 {
wg.Go(func() {
if _, err := Ingest(ctx, s, "2026-11-14", "2026-11-14.csv", v1, time.UTC); err != nil {
t.Error(err)
}
})
}
wg.Wait()
if len(s.raw) != 1 || len(s.days["2026-11-14"]) != 2 {
t.Fatalf("raw files %d, staged rows %d; want 1 and 2", len(s.raw), len(s.days["2026-11-14"]))
}
// The provider re-issues a corrected file: both raw files are kept, the staged day is replaced by the new one
v2 := []byte(header + "pay_1,T1,payment,SUCCESS,100,0,TWD,2026-11-14 01:00:00,2026-11-16\n")
sum2, err := Ingest(ctx, s, "2026-11-14", "2026-11-14.v2.csv", v2, time.UTC)
if err != nil {
t.Fatal(err)
}
day := s.days["2026-11-14"]
if _, ok := day[sum2+":2"]; len(s.raw) != 2 || len(day) != 1 || !ok {
t.Fatalf("after correction: raw %d, staged %v", len(s.raw), day)
}
}
func TestIngestKeepsRawEvenWhenParsingFails(t *testing.T) {
s := newMemStore()
_, err := Ingest(context.Background(), s, "2026-11-14", "bad.csv", []byte("not,a,statement\n"), time.UTC)
if err == nil || len(s.raw) != 1 || len(s.days) != 0 {
t.Fatalf("err %v, raw %d, days %d", err, len(s.raw), len(s.days))
}
}The first test has eight goroutines import the same file at once; with -race it checks that memStore has no data race, and that the result is the same as importing once. It then imports a corrected file and checks that both raw files are kept and only the new version is staged. The second test checks that when parsing fails, the raw file is still kept and staging is untouched.
Step 3: matching line by line
The matching key
Matching needs a key that both sides have and that never changes. The best choice is the merchant trade no that we generate and send to the provider: it is already in our database before the request goes out, so even if every webhook is lost and we never learned the provider's transaction ID, we can still match. The provider's transaction ID is the second key, used to tell apart "two different transactions under the same merchant trade no".
This is also why the earlier articles kept insisting that the merchant trade no should be the payment attempt's ID, not the order ID. If an order's first payment fails and the second succeeds, there are two attempts with two different numbers, and reconciliation can tell them apart.
One-to-many
Not everything matches one to one:
- Partial refunds. One payment can have several refunds. Each refund needs its own ID, sent as the refund's merchant reference; refunds are matched separately from payments, keyed by "kind + reference". Some providers' refund lines only carry the original payment's reference plus their own refund transaction ID; then key on "original reference + refund transaction ID", and check that the refunds of one payment never add up to more than the original amount.
- Split settlements. One day's transactions may be paid out in several batches, or one payout may cover several days. Payouts are compared as batches by settlement date or payout batch ID (step 6), not mixed into line matching.
- Chargebacks. The cardholder disputes the charge with the issuing bank and the money is pulled back. This happens on the provider's side and we have no matching record at first, so it always shows up as "only on the provider's side", usually with a deadline to respond.
Amounts, currencies and fees
- Always compare amounts as integer minor units, and the currency must match too;
1000 TWDand1000 JPYare not the same amount. - Statements commonly use one of two forms: gross plus a fee column, or just the net amount. Line matching must use gross, because we record what the user paid; if only net is given, reconstruct gross as "net + fee".
- Fees may be charged per transaction (2.8% each), per batch (a fixed fee per payout), or both. Per-transaction fees are compared during matching against the value we compute from the contract; per-batch fees are compared during payout reconciliation. Fee calculation has the same rounding questions, which are left to the next article.
Status mapping
The provider has fewer and coarser statuses than we do: statements usually only say succeeded or failed, and some only list successful transactions. We, on the other hand, have "not known yet" states such as pending and unknown. The mapping rules are:
- The provider says succeeded and we say
succeeded: they match. - The provider says succeeded and we say
failed,pendingorunknown: a status mismatch, and the user has already been charged. This is the most dangerous kind. - We say
pendingorunknownand the provider has nothing: still in flight, left to the previous article's sweeper, not a reconciliation difference. - We say
failedand the provider has nothing: normal, failed transactions are not necessarily listed.
Match turns all these rules into code. It is a pure function that never touches the database, so it is easy to test; the caller prepares both sides:
package recon
import (
"cmp"
"fmt"
"slices"
)
// Ours is one record on our side: a payment attempt or a refund.
type Ours struct {
ID string // payment attempt or refund ID, sent to the provider as the merchant trade no
Kind Kind
Status string // succeeded / failed / pending / unknown
Amount int64 // minor units
Currency string
ExpectedFee int64 // fee expected from the contract rate
}
// DiffKind is the kind of a difference.
type DiffKind string
const (
MissingAtProvider DiffKind = "missing_at_provider" // we succeeded, the provider has nothing
MissingInOurs DiffKind = "missing_in_ours" // the provider has it, we have nothing at all
StatusMismatch DiffKind = "status_mismatch"
AmountMismatch DiffKind = "amount_mismatch" // amount or currency differs
FeeMismatch DiffKind = "fee_mismatch"
Duplicate DiffKind = "duplicate" // the same merchant trade no appears twice at the provider
)
// Discrepancy is one difference; Urgent means the user may have been charged while we don't show success.
type Discrepancy struct {
Kind DiffKind
Key string
Ours *Ours
Theirs *Row
Urgent bool
Note string
}
// Result is the outcome of one matching run.
type Result struct {
Matched int
Diffs []Discrepancy
}
func key(k Kind, tradeNo string) string { return string(k) + "/" + tradeNo }
// Match pairs records line by line. ours must include the recon day's records, the trade numbers on the statement
// and earlier open ones, so transactions near midnight are not reported missing.
func Match(ours []Ours, theirs []Row) Result {
mine := make(map[string]*Ours, len(ours))
for i := range ours {
mine[key(ours[i].Kind, ours[i].ID)] = &ours[i]
}
seen := make(map[string]*Row, len(theirs))
var res Result
add := func(d Discrepancy) { res.Diffs = append(res.Diffs, d) }
for i := range theirs {
t := &theirs[i]
k := key(t.Kind, t.TradeNo)
if first, dup := seen[k]; dup {
// same txn ID = one line sent twice, should be dropped at import; different = two real transactions
add(Discrepancy{Kind: Duplicate, Key: k, Theirs: t,
Urgent: t.ProviderTxnID != first.ProviderTxnID && t.Status == "succeeded",
Note: fmt.Sprintf("first %s, again %s", first.ProviderTxnID, t.ProviderTxnID)})
continue
}
seen[k] = t
o, ok := mine[k]
switch {
case !ok:
add(Discrepancy{Kind: MissingInOurs, Key: k, Theirs: t, Urgent: t.Status == "succeeded"})
case o.Status != t.Status:
add(Discrepancy{Kind: StatusMismatch, Key: k, Ours: o, Theirs: t, Urgent: t.Status == "succeeded",
Note: fmt.Sprintf("ours %s, provider %s", o.Status, t.Status)})
case o.Amount != t.Amount || o.Currency != t.Currency:
add(Discrepancy{Kind: AmountMismatch, Key: k, Ours: o, Theirs: t,
Note: fmt.Sprintf("ours %d %s, provider %d %s", o.Amount, o.Currency, t.Amount, t.Currency)})
case o.ExpectedFee != t.Fee:
add(Discrepancy{Kind: FeeMismatch, Key: k, Ours: o, Theirs: t,
Note: fmt.Sprintf("expected %d, charged %d", o.ExpectedFee, t.Fee)})
default:
res.Matched++
}
}
for k, o := range mine {
// pending and unknown are in flight and left to the sweeper; failed ones may never be listed
if _, ok := seen[k]; !ok && o.Status == "succeeded" {
add(Discrepancy{Kind: MissingAtProvider, Key: k, Ours: o})
}
}
slices.SortFunc(res.Diffs, func(a, b Discrepancy) int {
return cmp.Or(cmp.Compare(a.Key, b.Key), cmp.Compare(a.Kind, b.Kind))
})
return res
}How should ours be prepared? Taking only the recon day's records is not enough, because transactions around midnight would be misreported. In practice it is the union of three conditions: records created on the recon day, every merchant trade no that appears on this statement (whatever day it was created), and earlier "missing at the provider" records that are still open:
-- recon_our_records is a view combining payments and refunds; id is the merchant trade no sent to the provider
SELECT id, kind, status, amount, currency, expected_fee
FROM recon_our_records
WHERE (created_at AT TIME ZONE 'Asia/Taipei')::date = $2 -- the recon day
OR id = ANY ($3::text[]) -- trade numbers on this statement
OR id IN (SELECT split_part(match_key, '/', 2) -- earlier open "missing at provider"
FROM recon_discrepancies
WHERE provider = $1 AND kind = 'missing_at_provider'
AND state IN ('open', 'investigating'));Step 4: classifying differences
The differences Match produces fall into six kinds, each with its own meaning and urgency:
Figure: first check whether both sides have the record, then check status, amount and fee in order; dark boxes mean the user may have been charged and must be handled the same day.
| Kind | Situation | Common causes | Urgency |
|---|---|---|---|
missing_at_provider | We say succeeded, the statement doesn't list it | Timing across midnight; we wrongly marked it successful; omitted | Wait a day, investigate if still missing |
missing_in_ours | The statement says succeeded, we have nothing | Every webhook failed; created by hand in the dashboard; a bug | Urgent: the user has been charged |
status_mismatch | Both have it, with different statuses | A timeout treated as failure that actually succeeded; webhook bug | Urgent when the provider says succeeded |
amount_mismatch | Amount or currency differs | A partial refund not synced; currency conversion; edited data | Investigate the same day |
fee_mismatch | The fee differs from the contract | Rate change without notice; different rounding; card type | Not urgent, but confirm with the provider |
duplicate | One merchant trade no appears twice | Same transaction ID = repeated line; different = a double charge | Urgent when the IDs differ and it succeeded |
"Only on the provider's side" and "status mismatch where the provider succeeded" are the most dangerous kinds: the user's money has been taken, and we haven't shipped, or the order has even been cancelled. Handle them like the previous article's sweeper: query the provider for that single transaction by merchant trade no, and once success is confirmed, depending on the order's current state, either record the success and fulfil or refund; both must be idempotent, so a rerun of reconciliation never refunds twice.
"Only on our side" is mostly timing and doesn't need an immediate alert, but it can't wait forever. If it is still missing from the next day's and the day after's statements, query the provider: maybe we wrongly treated a timeout as success (a serious bug), or maybe the provider left it out.
The test puts every kind into one data set and checks the classification, the order and the urgent flag:
package recon
import (
"slices"
"testing"
)
func TestMatchClassifiesDifferences(t *testing.T) {
ours := []Ours{
{ID: "pay_1", Kind: Payment, Status: "succeeded", Amount: 100000, Currency: "TWD", ExpectedFee: 2800},
{ID: "pay_2", Kind: Payment, Status: "succeeded", Amount: 50000, Currency: "TWD"}, // missing at the provider
{ID: "pay_3", Kind: Payment, Status: "failed", Amount: 30000, Currency: "TWD"}, // but the provider charged
{ID: "pay_4", Kind: Payment, Status: "succeeded", Amount: 20000, Currency: "TWD"},
{ID: "pay_5", Kind: Payment, Status: "succeeded", Amount: 10000, Currency: "TWD", ExpectedFee: 280},
{ID: "pay_6", Kind: Payment, Status: "pending", Amount: 10000, Currency: "TWD"}, // still in flight, not a difference
{ID: "ref_1", Kind: Refund, Status: "succeeded", Amount: 20000, Currency: "TWD"},
}
theirs := []Row{
{TradeNo: "pay_1", ProviderTxnID: "T1", Kind: Payment, Status: "succeeded", Amount: 100000, Currency: "TWD", Fee: 2800},
{TradeNo: "pay_3", ProviderTxnID: "T3", Kind: Payment, Status: "succeeded", Amount: 30000, Currency: "TWD"},
{TradeNo: "pay_4", ProviderTxnID: "T4", Kind: Payment, Status: "succeeded", Amount: 2000, Currency: "TWD"},
{TradeNo: "pay_5", ProviderTxnID: "T5", Kind: Payment, Status: "succeeded", Amount: 10000, Currency: "TWD", Fee: 300},
{TradeNo: "pay_7", ProviderTxnID: "T7", Kind: Payment, Status: "succeeded", Amount: 9900, Currency: "TWD"}, // missing on our side
{TradeNo: "pay_1", ProviderTxnID: "T8", Kind: Payment, Status: "succeeded", Amount: 100000, Currency: "TWD"}, // a second one
{TradeNo: "ref_1", ProviderTxnID: "R1", Kind: Refund, Status: "succeeded", Amount: 20000, Currency: "TWD"},
}
res := Match(ours, theirs)
type got struct {
key string
kind DiffKind
urgent bool
}
var diffs []got
for _, d := range res.Diffs {
diffs = append(diffs, got{d.Key, d.Kind, d.Urgent})
}
want := []got{
{"payment/pay_1", Duplicate, true},
{"payment/pay_2", MissingAtProvider, false},
{"payment/pay_3", StatusMismatch, true},
{"payment/pay_4", AmountMismatch, false},
{"payment/pay_5", FeeMismatch, false},
{"payment/pay_7", MissingInOurs, true},
}
if !slices.Equal(diffs, want) {
t.Fatalf("diffs\n got %v\nwant %v", diffs, want)
}
if res.Matched != 2 {
t.Errorf("matched = %d, want 2", res.Matched)
}
}
func TestTotalsAndNet(t *testing.T) {
rows := []Row{
{Kind: Payment, Status: "succeeded", Amount: 100000, Fee: 2800, Currency: "TWD"},
{Kind: Payment, Status: "succeeded", Amount: 50000, Fee: 1400, Currency: "TWD"},
{Kind: Payment, Status: "failed", Amount: 70000, Currency: "TWD"}, // failures don't count
{Kind: Refund, Status: "succeeded", Amount: 20000, Fee: -560, Currency: "TWD"},
{Kind: Payment, Status: "succeeded", Amount: 1250, Fee: 36, Currency: "USD"},
}
ours := []Ours{
{Kind: Payment, Status: "succeeded", Amount: 100000, ExpectedFee: 2800, Currency: "TWD"},
{Kind: Payment, Status: "succeeded", Amount: 50000, ExpectedFee: 1400, Currency: "TWD"},
{Kind: Refund, Status: "succeeded", Amount: 20000, ExpectedFee: -560, Currency: "TWD"},
}
theirs := SumRows(rows)
diff := CompareTotals(SumOurs(ours), theirs)
if want := []string{"payment USD: ours 0/0/0, provider 1/1250/36"}; !slices.Equal(diff, want) {
t.Errorf("CompareTotals = %q, want %q", diff, want)
}
// 150000 − 20000 − (2800 + 1400 − 560) = 126360
if got := Net(theirs, "TWD"); got != 126360 {
t.Errorf("Net TWD = %d, want 126360", got)
}
}pay_6 is pending and missing at the provider, so it is not a difference; pay_1 appears twice on the statement with different transaction IDs, and the second one is flagged as a duplicate. The totals functions used by TestTotalsAndNet are explained in step 6.
Step 5: handling differences
The discrepancy table
Every difference goes into a table with a state, an owner and a full history. When reconciliation reruns every day, a partial unique index guarantees that the same difference has only one open record:
CREATE TABLE recon_discrepancies (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
provider text NOT NULL,
recon_day date NOT NULL, -- recon day it was first found
match_key text NOT NULL, -- e.g. payment/pay_1001
kind text NOT NULL,
urgent boolean NOT NULL DEFAULT false,
our_amount bigint,
their_amount bigint,
currency char(3),
state text NOT NULL DEFAULT 'open'
CHECK (state IN ('open', 'investigating', 'resolved', 'written_off')),
resolution text, -- matched_later / booked / refunded / adjusted / written_off
owner text,
note text,
first_seen_at timestamptz NOT NULL DEFAULT now(),
last_seen_at timestamptz NOT NULL DEFAULT now(),
resolved_at timestamptz,
CHECK ((state IN ('resolved', 'written_off')) = (resolved_at IS NOT NULL))
);
-- Only one open record per difference; a daily rerun updates last_seen_at instead of inserting
CREATE UNIQUE INDEX recon_discrepancies_one_open
ON recon_discrepancies (provider, match_key, kind)
WHERE state IN ('open', 'investigating');
-- Every change writes an event, insert-only
CREATE TABLE recon_discrepancy_events (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
discrepancy_id bigint NOT NULL REFERENCES recon_discrepancies (id),
actor text NOT NULL, -- a staff account, or system:recon
action text NOT NULL, -- opened / seen_again / assigned / note / resolved / written_off
from_state text,
to_state text,
detail jsonb NOT NULL DEFAULT '{}',
at timestamptz NOT NULL DEFAULT now()
);
-- Record a difference: if an open one exists, update when it was last seen
INSERT INTO recon_discrepancies
(provider, recon_day, match_key, kind, urgent, our_amount, their_amount, currency, note)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
ON CONFLICT (provider, match_key, kind) WHERE state IN ('open', 'investigating')
DO UPDATE SET last_seen_at = now(),
urgent = recon_discrepancies.urgent OR EXCLUDED.urgent
RETURNING id;The lifecycle
Figure: a difference starts as open, moves to investigating once assigned, and becomes resolved once fixed; one that can't be recovered becomes written_off after a manager approves it. Timing differences are resolved automatically when they match the next day.
- open: just found, nobody is on it yet.
- investigating: an owner has been assigned and is looking into it.
- resolved: fixed;
resolutionrecords how (recorded, refunded, adjusting entry posted, matched automatically the next day). - written_off: confirmed unrecoverable, or so small that chasing it costs more than it is worth, and written off after approval. A write-off is also an entry in the ledger, never a deleted record.
Every state change, assignment and note writes an event. When an auditor asks three months later "why was this 990 written off?", you must be able to answer who decided, when, and on what grounds.
Auto-resolution: timing differences
missing_at_provider caused by midnight is by far the most common difference, and the least worth a human's time. The rule is simple: if a "missing at provider" record was not listed again after this run started, it matched today, so resolve it automatically and write an event:
-- $1 provider, $2 the time this reconciliation run started
WITH done AS (
UPDATE recon_discrepancies
SET state = 'resolved', resolution = 'matched_later', resolved_at = now()
WHERE provider = $1 AND kind = 'missing_at_provider'
AND state = 'open' AND last_seen_at < $2
RETURNING id
)
INSERT INTO recon_discrepancy_events (discrepancy_id, actor, action, from_state, to_state)
SELECT id, 'system:recon', 'resolved', 'open', 'resolved' FROM done;The rule only holds if ours always includes earlier open records (the query in step 3) and this run finished successfully; if reconciliation failed halfway, don't run it, or every difference that wasn't relisted would wrongly count as resolved. For the same reason, alerts look at age: missing_at_provider doesn't alert on its first day, only when it is still open after two recon days.
A review screen
In the end the discrepancy table is for people. A useful review screen has at least:
- Sorting by urgency, age and amount, with urgent ones always on top.
- For an opened difference, our records (payment, order, webhook log, status history) side by side with the original statement line (linked to the raw file and line number).
- A "query the provider" button whose result is written as an event.
- Corrective actions as buttons (record success, start a refund, post an adjusting entry, request a write-off), each requiring a reason; a write-off needs a second person to approve.
- Personal data masked by default (see step 7).
The rule for fixes: don't edit amounts, post entries
When reconciliation finds a wrong amount, the instinct is to change payments.amount to the statement's number. Don't. Once changed, the original value is gone, the entries already in the ledger no longer agree with it, and the next person who audits the books can't explain anything.
The right approach continues the double-entry bookkeeping of problem 9 in “Payment Pitfalls: Double Charges, Callbacks and Reconciliation”: leave the original entries alone, post an adjusting entry that adds or reverses the difference, use the discrepancy's ID as the entry's idempotency key, and note the entry number in the discrepancy's events. For example, the provider actually charged a fee 20 cents higher than expected:
-- Idempotency key = discrepancy ID, so one discrepancy is adjusted only once
INSERT INTO journal_entries (idempotency_key, kind)
VALUES ('recon-adjust-' || $1, 'recon_adjustment')
ON CONFLICT (idempotency_key) DO NOTHING
RETURNING id;
-- Only if an id came back, write the lines: debit fee expense 20, credit receivable from provider 20
-- $1 the new entry's id, $2 the fee expense account, $3 the receivable-from-provider account
INSERT INTO ledger_lines (entry_id, account_id, debit, credit)
VALUES ($1, $2, 20, 0), ($1, $3, 0, 20);journal_entries and ledger_lines are defined in that article; kind is free text there, and here it gains one more value, recon_adjustment.
Corrective actions must be idempotent
Reconciliation reruns every day, and people click buttons twice. Every corrective action that moves money needs an idempotency key:
- Refunds: the key is "discrepancy ID + action", for example
recon-refund-1234, and it is also sent to the provider as the refund's merchant reference, so the provider blocks duplicates on a rerun as well. - Recording success and fulfilling: use the normal state machine's conditional update (
WHERE status IN ('pending', 'unknown', 'failed')), and send the fulfilment event through an outbox (“Distributed Transactions: Sagas and the Outbox Pattern”); if the condition fails, someone has already handled it. - Adjusting entries: as above, keyed by the discrepancy ID.
Step 6: tying the ledger to the bank payout
Totals first, then line by line
Before line matching, run a totals check: on each side, count and sum amounts and fees by "kind + currency". If totals differ, there must be differences, and line matching will tell you which. If totals agree, that doesn't prove there is no problem, but if they are far apart (say the statement has half the usual volume), the file is usually incomplete or for the wrong date, and you should stop and check the file rather than generate thousands of discrepancies.
package recon
import (
"cmp"
"fmt"
"maps"
"slices"
)
// TotalsKey splits totals by kind and currency; amounts in different currencies must never be added together.
type TotalsKey struct {
Kind Kind
Currency string
}
// Totals are the count, gross amount and fees of successful transactions.
type Totals struct {
Count int
Amount int64
Fee int64
}
// SumRows totals the successful statement lines.
func SumRows(rows []Row) map[TotalsKey]Totals {
m := map[TotalsKey]Totals{}
for _, r := range rows {
if r.Status != "succeeded" {
continue
}
k := TotalsKey{r.Kind, r.Currency}
t := m[k]
t.Count, t.Amount, t.Fee = t.Count+1, t.Amount+r.Amount, t.Fee+r.Fee
m[k] = t
}
return m
}
// SumOurs totals our successful records, using the expected fees.
func SumOurs(ours []Ours) map[TotalsKey]Totals {
m := map[TotalsKey]Totals{}
for _, o := range ours {
if o.Status != "succeeded" {
continue
}
k := TotalsKey{o.Kind, o.Currency}
t := m[k]
t.Count, t.Amount, t.Fee = t.Count+1, t.Amount+o.Amount, t.Fee+o.ExpectedFee
m[k] = t
}
return m
}
// CompareTotals lists the totals that differ between the two sides, in a stable order.
func CompareTotals(ours, theirs map[TotalsKey]Totals) []string {
keys := slices.Collect(maps.Keys(ours))
for k := range theirs {
if _, ok := ours[k]; !ok {
keys = append(keys, k)
}
}
slices.SortFunc(keys, func(a, b TotalsKey) int {
return cmp.Or(cmp.Compare(a.Kind, b.Kind), cmp.Compare(a.Currency, b.Currency))
})
var out []string
for _, k := range keys {
if a, b := ours[k], theirs[k]; a != b {
out = append(out, fmt.Sprintf("%s %s: ours %d/%d/%d, provider %d/%d/%d",
k.Kind, k.Currency, a.Count, a.Amount, a.Fee, b.Count, b.Amount, b.Fee))
}
}
return out
}
// Net is what the provider should pay out for this batch: payments - refunds - chargebacks - all fees.
func Net(t map[TotalsKey]Totals, currency string) int64 {
n := t[TotalsKey{Payment, currency}].Amount -
t[TotalsKey{Refund, currency}].Amount -
t[TotalsKey{Chargeback, currency}].Amount
for k, v := range t {
if k.Currency == currency {
n -= v.Fee
}
}
return n
}Net is what the provider should pay out for this batch: gross payments minus refunds and chargebacks, minus all fees. When a refund returns the fee, the fee is negative, and subtracting a negative adds it back. Amounts in different currencies are never added together, so the totals key always includes the currency.
TestTotalsAndNet (in match_test.go above) checks that failed transactions don't count, that a currency on only one side is still listed, and the net calculation: 150000 - 20000 - (2800 + 1400 - 560) = 126360.
Four kinds of entries in the ledger
For the ledger to tie to the statement and the bank, it needs at least these accounts: receivable from provider (money the provider collected but hasn't paid out yet), bank, fee expense, revenue (or the amount payable to a merchant), plus accounts for refunds. A payment writes four kinds of entries from start to finish:
| Event | Debit | Credit | Example (cents) |
|---|---|---|---|
| Payment succeeded | Receivable from provider | Revenue | 100000 |
| Provider charges its fee | Fee expense | Receivable from provider | 2800 |
| Partial refund | Revenue (or sales returns) | Receivable from provider | 20000 |
| Payout arrives at the bank | Bank | Receivable from provider | 77200 |
After the payout, this batch's debits and credits on "receivable from provider" net to exactly 0. So three-way reconciliation comes down to one sentence: for every settlement date, the decrease of the receivable from provider in the ledger equals the net amount of that batch on the statement, and equals what the bank actually received.
Statement versus bank
Bank-side data can come from the bank's statement or deposit notifications, stored in bank_deposits. Compare the net amounts of both sides by settlement date:
-- $1 provider; bank deposits are in bank_deposits (provider, value_date, amount, currency, reference)
WITH stmt AS (
SELECT settlement_date, currency,
sum(CASE kind WHEN 'payment' THEN amount ELSE -amount END) - sum(fee) AS net
FROM statement_lines
WHERE provider = $1 AND status = 'succeeded'
GROUP BY settlement_date, currency
), bank AS (
SELECT value_date AS settlement_date, currency, sum(amount) AS net
FROM bank_deposits
WHERE provider = $1
GROUP BY value_date, currency
)
SELECT settlement_date, currency, s.net AS statement_net, b.net AS bank_net
FROM stmt s FULL JOIN bank b USING (settlement_date, currency)
WHERE s.net IS DISTINCT FROM b.net
ORDER BY settlement_date, currency;This query is only a starting point; in practice there are some common sources of difference. Payouts are delayed by holidays, so the bank's value date can differ from the statement's settlement date (if the provider gives a payout batch ID, matching on it is more reliable than matching on dates); per-batch fees, transfer fees or reserves are deducted from the payout and must be found as lines in the statement or payout details; chargebacks may be deducted from a later payout. Each of these deserves an explicit rule rather than someone working it out by hand every month.
Daily totals of the ledger and the statement
Finally, the ledger side gets the same totals, compared with the statement's:
-- Ledger: for one recon day, entries and net change on "receivable from provider" by entry kind ($1 account, $2 recon day)
SELECT e.kind, count(*) AS entries, sum(l.debit - l.credit) AS net_change
FROM journal_entries e
JOIN ledger_lines l ON l.entry_id = e.id
WHERE l.account_id = $1
AND (e.created_at AT TIME ZONE 'Asia/Taipei')::date = $2
GROUP BY e.kind
ORDER BY e.kind;
-- Statement: the same recon day, count and totals by kind ($1 provider, $2 recon day)
SELECT kind, count(*) AS lines, sum(amount) AS amount, sum(fee) AS fee
FROM statement_lines
WHERE provider = $1 AND recon_day = $2 AND status = 'succeeded'
GROUP BY kind
ORDER BY kind;An entry's time is when we booked it (for example when the webhook arrived), which isn't necessarily the same day as the transaction, so this is another timing difference. A common approach is to store a separate "business date" on each entry, filled with the recon day derived from the transaction time, and to use it for totals instead of created_at.
Step 7: running it in production
Daily report
After every run, produce a short report: the recon day, the file and its hash, counts and totals on both sides, the number matched, count and amount for each kind of difference, how many were auto-resolved, and the age distribution of open differences. Keep the report too; month-end close refers to it directly.
Metrics and alerts
The reconciliation job must be monitored like any other service, exporting metrics (“Observability in Go: Logs, Metrics and Tracing”):
- The statement didn't arrive on time: no successful
ingestion_runsafter the agreed time; alert engineering. - Reconciliation failed: a parse error, or totals far apart; alert engineering.
- Urgent differences > 0:
missing_in_ours, astatus_mismatchwhere the provider succeeded, a double charge; alert on a single one, because each one is a user who was charged and got nothing. - Open differences by age: for example
missing_at_providerolder than two recon days, or any difference older than seven days; alert the owner. - Amount of open differences: computed per currency, alert above a threshold.
- The trend in the number of differences: if there are usually two or three a day and today there are fifty, something usually broke (a new release, a provider format change), and that deserves more attention than any single difference.
Metric labels should only hold low-cardinality values such as kind, currency and age bucket, never merchant trade numbers.
Reruns and backfills
- Rerunning a day: because raw files are never edited and both the import and the discrepancy writes are idempotent, a rerun is just "run again from the same raw file", and the result should be the same. This is also how you backfill after fixing the parser.
- Backfilling a period: for example onboarding a new provider, or discovering a month whose files were never fetched. Run day by day in date order, writing a run record for each day; don't load a whole month and match it at once, or the carry-over rule for timing differences won't work.
- Reruns must have no side effects: matching and classification are pure computation, and every corrective action that moves money has an idempotency key, so a rerun never refunds again.
Retention, access control and personal data
Statements usually contain personal data: cardholder names, partly masked card numbers, emails, phone numbers, IP addresses. The rules:
- Raw files live in access-controlled object storage, readable only by the reconciliation job and a few people, with access logged.
- The staging table only holds the columns reconciliation needs; personal data stays out of staging and is looked up in the raw file when needed.
- The review screen masks by default; seeing the full content requires a reason and is logged.
- Logs only record the merchant trade no, amount and kind of difference, never the whole line.
- Retention follows accounting and legal requirements, and expired files are deleted or archived according to policy. Full card numbers and security codes should never appear in a statement in the first place; if they do, report it to the provider.
Month-end close
At the end of the month, reconciliation's job is to let finance close the books:
- Confirm that every day of the month has a successful reconciliation run, with no missing dates.
- All urgent differences are closed; the remaining open ones are listed with their reason and expected resolution date, and small ones are written off according to policy.
- Every payout of the month ties to a bank deposit; payouts that cross the month boundary (end-of-month transactions paid out next month) are listed as a receivable balance that equals the ledger's receivable from provider.
- Once closed, the month's data no longer changes; problems found later are handled with adjusting entries in the next month, noted in the discrepancy's events.
Running the tests
$ go vet ./...
$ go test -race ./...
ok example.com/shop/internal/recon 1.084sCommon pitfalls
- Splitting CSV with
strings.Split. A comma inside quotes shifts every field. Useencoding/csvand look columns up by name. - Parsing amounts with
float64.strconv.ParseFloat("0.29", 64) * 100converted back to an integer can become 28. Parse straight to integers. - Skipping broken lines. That transaction disappears for good. Reject the whole file on any error.
- Different day cut-offs on each side. A small batch of "missing at provider" every day that never goes away. Define the recon day first, then add the carry-over rule.
- Alerting on "missing at provider" on day one. Once alert fatigue sets in, nobody looks at the truly urgent one either. Grade alerts by kind and age.
- Editing amounts or deleting differences. The ledger and the event log are the only things that can explain the past. Post adjusting entries and close the record.
- Corrective actions that aren't idempotent. Every rerun refunds once more.
- Comparing only totals. An overcharge and an undercharge cancel out. Totals are only a first check.
- Running auto-resolution after a failed run. Not being listed doesn't mean resolved; it may just not have been checked.
Key takeaways
- Reconciliation is three-way: our records and the statement are compared line by line, the statement and the bank payout by batch, and all three must tie to the ledger.
- A statement is evidence: keep the raw file untouched with its hash, so every result can be recomputed from it.
- Imports must be idempotent: staging is keyed by "file hash + line number", a recon day is replaced whole in one transaction, and a corrected file simply replaces the old one.
- Parse strictly: handle BOMs, quotes and encodings, convert amounts straight to integer minor units, stop on unknown statuses, and reject the whole file if one line is wrong.
- Define the recon day before dealing with time zones: give times the right zone, convert to the agreed zone to take the date, and carry records near midnight over to the next day.
- Match on the merchant trade no you generate, matching payments, refunds and chargebacks separately; check totals before matching line by line.
- Classify and grade differences: "the provider succeeded, we didn't" means the user has been charged and is handled the same day; timing differences auto-resolve the next day.
- Differences have a state, an owner and an event log; fixes post adjusting entries instead of editing amounts, and every fix that moves money is idempotent.
Before launch, check each item:
- The documentation states the recon day definition (time zone and cut-off), each party's data source, and who owns each role.
- Fetching is scheduled, backs off and retries, alerts when the file is overdue, and writes every run to
ingestion_runs. - Raw files are kept in object storage with their SHA-256 and never edited; a corrected file is kept separately and marked as superseding the old one.
- Parsing uses
encoding/csv, handles the BOM, checks UTF-8, finds columns by name, never usesfloat64for amounts, rejects the whole file on one bad line, and has tests for all of it. - Staging is keyed by "file hash + line number", a recon day is replaced whole in one transaction, and imports of the same day are locked.
- The matching key is the merchant trade no (payment attempt ID, refund ID), and matching includes neighbouring days and open records.
- A totals check (count, amount, fee, by kind and currency) runs before line matching and stops the run when totals are far apart.
- The discrepancy table has a state, an owner, an event log and a partial unique index; timing differences have an auto-resolution rule that doesn't run after a failed run.
- A single urgent difference raises an alert; other differences alert by age and amount.
- Every fix posts an adjusting entry; refunds, recorded successes and write-offs all have idempotency keys; write-offs need a second person's approval.
- Payouts are matched to bank deposits by settlement date or batch ID, and the receivable from provider agrees with the ledger.
- Personal data in raw files and on the review screen is access-controlled, masked by default, access-logged and has a retention period.
- Before month-end close, every day has a successful run, all urgent differences are closed, and cross-month payouts are listed as receivables.
Next
The most stubborn reconciliation differences are often not whole missing transactions, but a cent or two: the fee multiplied out from a rate leaves a fraction, and we round half up while the provider rounds down; a discount spread across three items, and how much to refund when one of them comes back; New Taiwan dollars in whole units on the statement while we count in cents internally. These are all questions of "how to handle money". The next article, “Handling Money in Go: Minor Units, Decimals, Currencies, Rounding and Allocation”, starts from why float64 doesn't work, builds a money type that carries its currency, and gets rounding and allocation right to the last cent.