This resume is also available in English.

7. Pitfalls in practice4 / 5 · All columns

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.

  • 支付
  • 對帳
  • Go
  • PostgreSQL
In this article
  1. What this article solves
  2. Step 1: what reconciliation is and isn't
  3. Step 2: fetching statements reliably
  4. Step 3: matching line by line
  5. Step 4: classifying differences
  6. Step 5: handling differences
  7. Step 6: tying the ledger to the bank payout
  8. Step 7: running it in production
  9. Running the tests
  10. Common pitfalls
  11. Key takeaways
  12. 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:

  1. Make clear which parties reconciliation compares, how often, and who owns it.
  2. Fetch statements reliably: scheduling, retries, raw files that are never edited, imports that can be rerun.
  3. Parse statements: BOMs, CSV quoting, encodings, amounts as integers, and the easiest thing to get wrong, time zones and the day cut-off.
  4. Match line by line: keys, one-to-many cases, fees, refunds and chargebacks, status mapping.
  5. Classify differences: which ones are just timing and which ones mean the user has already been charged.
  6. Handle differences: the discrepancy table, its lifecycle, auto-resolution rules, adjusting entries, idempotent corrective actions.
  7. 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 payment service'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.

Our payment records payment service, PostgreSQL Provider statement daily file, one line each Ledger (double-entry) receivable, fees, revenue Bank payout net amount received line by line: trade no amount, currency, status, fee book successes fees, refunds batch: payout net gross - refunds - fees clears receivable Only when all three agree and the ledger balances is the day reconciled

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 unknown payments 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”.

Fetch SFTP or API retry on failure Raw file object storage SHA-256, never edited Parse encoding, time zone amounts to integers Staging replace whole day key: hash + line Totals check count and sum by kind, currency Match by trade no classify Discrepancies state, owner every change logged Alert, handle by age and amount auto or manual Every run is written to ingestion_runs: which day, which file, how many rows, success or failure, the error

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 becomes merchant_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 with strings.Split(line, ",") yourself; use encoding/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.Valid first and reject the whole file if it isn't UTF-8, leaving it to a conversion step (for example golang.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.50 or 1,000. Convert them straight to integer minor units without ever using float64; 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, so 1,000 must become 100000 (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.

Taipei 00:00 = 16:00 UTC 00:00 UTC = Taipei 08:00 Taipei UTC 11/14 11/15 11/14 11/15 A 23:59:59 15:59:59 B 00:00:30 16:00:30 A: both sides say 11/14. B: Taipei says 11/15, but the UTC file says 11/14. Pick one day definition and document it; pull in the neighbouring day when matching.

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:

  1. 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.
  2. 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.
  3. 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 TWD and 1000 JPY are 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, pending or unknown: a status mismatch, and the user has already been charged. This is the most dangerous kind.
  • We say pending or unknown and the provider has nothing: still in flight, left to the previous article's sweeper, not a reconciliation difference.
  • We say failed and 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:

Match by (kind, merchant trade no) Only on our side We say succeeded missing_at_provider usually timing, recheck pending / unknown not a difference left to the sweeper We say failed normal may not be listed Only on provider's side Provider succeeded missing_in_ours user was charged: urgent Same trade no twice duplicate new txn id = double charge Chargeback line open a dispute case reply has a deadline Provider failed just record it no money moved On both (checked in order) Status differs status_mismatch provider succeeded: urgent Amount or currency amount_mismatch investigate today Fee differs fee_mismatch check the contract rate All equal matched mark reconciled Dark boxes: the user may have been charged while we don't show success; handle the same day

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.

KindSituationCommon causesUrgency
missing_at_providerWe say succeeded, the statement doesn't list itTiming across midnight; we wrongly marked it successful; omittedWait a day, investigate if still missing
missing_in_oursThe statement says succeeded, we have nothingEvery webhook failed; created by hand in the dashboard; a bugUrgent: the user has been charged
status_mismatchBoth have it, with different statusesA timeout treated as failure that actually succeeded; webhook bugUrgent when the provider says succeeded
amount_mismatchAmount or currency differsA partial refund not synced; currency conversion; edited dataInvestigate the same day
fee_mismatchThe fee differs from the contractRate change without notice; different rounding; card typeNot urgent, but confirm with the provider
duplicateOne merchant trade no appears twiceSame transaction ID = repeated line; different = a double chargeUrgent 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

open waiting investigating being worked resolved fixed written_off approved write-off assign matched next day: auto-resolved book, refund, adjust approved, post write-off Every transition writes an event: who, when, why, and the state before and after

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; resolution records 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:

EventDebitCreditExample (cents)
Payment succeededReceivable from providerRevenue100000
Provider charges its feeFee expenseReceivable from provider2800
Partial refundRevenue (or sales returns)Receivable from provider20000
Payout arrives at the bankBankReceivable from provider77200

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_runs after the agreed time; alert engineering.
  • Reconciliation failed: a parse error, or totals far apart; alert engineering.
  • Urgent differences > 0: missing_in_ours, a status_mismatch where 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_provider older 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:

  1. Confirm that every day of the month has a successful reconciliation run, with no missing dates.
  2. 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.
  3. 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.
  4. 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.084s

Common pitfalls

  • Splitting CSV with strings.Split. A comma inside quotes shifts every field. Use encoding/csv and look columns up by name.
  • Parsing amounts with float64. strconv.ParseFloat("0.29", 64) * 100 converted 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 uses float64 for 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.