Payment Reconciliation at Scale: Engineering the Pipeline That Catches Every Cent

Reconciliation Is Just Fancy Diffing

Strip away the finance jargon and reconciliation is a diff operation. You have what you think happened (your internal database), what the payment provider says happened (their settlement report), and what the bank confirms happened (the bank statement). Your job is to make all three agree.

Sounds simple until you're dealing with 14 different PSPs, each with their own file format, settlement cadence, and creative interpretation of what a "transaction ID" should look like. At our crypto exchange, the finance team was spending 6+ hours a day manually cross-referencing spreadsheets. That's not sustainable when you're processing thousands of transactions daily across fiat and crypto rails.

The Three-Way Match Problem

Two-way matching (your DB vs. PSP report) is straightforward. Three-way matching is where things get painful, because bank statements operate on a completely different timeline and granularity. Banks batch transactions. They net settlements. They sometimes combine Tuesday's and Wednesday's deposits into a single line item on Thursday.

The approach I landed on: treat the three-way match as two sequential two-way matches. First, reconcile internal records against PSP settlement files (transaction-level). Then, reconcile PSP settlement totals against bank statement entries (aggregate-level). Trying to do all three simultaneously is a recipe for combinatorial explosion.

The Pipeline Architecture

After a few iterations, the pipeline settled into five distinct stages. Each stage is independently deployable and communicates through a message queue, so a failure in matching doesn't block ingestion of new files.

Stage 1: Ingestion — Herding the File Formats

Every PSP delivers settlement data differently. Some drop CSVs on an SFTP server at 3 AM. Others expose a REST API you poll. A few still email password-protected Excel files (yes, in 2026). And if you're lucky enough to work with banks that support ISO 20022, you get well-structured XML — but "well-structured" is relative.

PSP Type	Format	Delivery	Settlement
Card processors (Stripe, Adyen)	CSV / API (JSON)	API pull or webhook	T+2 rolling
Bank transfers (SWIFT)	MT940 / ISO 20022 XML	SFTP drop	T+1
Local payment methods	CSV (varied encodings)	SFTP / Email	T+1 to T+3
Crypto on-ramps	JSON API	API pull	Near real-time
E-wallets (GrabPay, GCash)	CSV / XLSX	Portal download / SFTP	T+1 to T+2

The ingestion layer's only job is to fetch files and store them raw. No parsing, no transformation. I learned this after a bug in our CSV parser silently dropped rows with unescaped commas in the merchant name field. Raw files give you an audit trail and the ability to reprocess.

Stage 2: Normalization — One Schema to Rule Them All

Every PSP record gets transformed into a canonical struct. This is where you absorb all the weirdness so downstream stages don't have to care where the data came from.

// Go: Canonical settlement record
type SettlementRecord struct {
    PSP            string          `json:"psp"`
    ExternalRef    string          `json:"external_ref"`
    InternalRef    string          `json:"internal_ref"`
    Amount         decimal.Decimal `json:"amount"`
    Currency       string          `json:"currency"`
    SettledAt      time.Time       `json:"settled_at"`
    TransactedAt   time.Time       `json:"transacted_at"`
    Type           string          `json:"type"` // payment, refund, chargeback
    RawLine        int             `json:"raw_line"`
    SourceFile     string          `json:"source_file"`
}

// Each PSP gets its own parser that returns []SettlementRecord
func (p *StripeParser) Parse(raw []byte) ([]SettlementRecord, error) {
    reader := csv.NewReader(bytes.NewReader(raw))
    records, err := reader.ReadAll()
    if err != nil {
        return nil, fmt.Errorf("stripe csv parse: %w", err)
    }

    var out []SettlementRecord
    for i, row := range records[1:] { // skip header
        amt, _ := decimal.NewFromString(row[4])
        out = append(out, SettlementRecord{
            PSP:         "stripe",
            ExternalRef: row[0],
            InternalRef: row[2], // metadata.order_id
            Amount:      amt,
            Currency:    strings.ToUpper(row[5]),
            RawLine:     i + 2,
        })
    }
    return out, nil
}

Two things I'd stress here: use decimal types for money (never floats), and always keep a pointer back to the raw source line. When an exception pops up six months later, you need to trace it back to the exact row in the original file.

Stage 3: Matching — Where the Magic Happens

Matching runs in two passes. The first pass is deterministic: join on internal_ref and compare amounts. This catches ~92% of transactions in our case. The second pass is fuzzy — it handles cases where reference IDs got mangled, amounts differ due to FX conversion, or timestamps are off by a day.

// Matching with tolerance for FX variance
func matchWithTolerance(internal, external SettlementRecord) MatchResult {
    if internal.InternalRef == external.InternalRef {
        diff := internal.Amount.Sub(external.Amount).Abs()
        tolerance := internal.Amount.Mul(decimal.NewFromFloat(0.005)) // 0.5%

        if diff.IsZero() {
            return MatchResult{Status: "exact", Confidence: 1.0}
        }
        if diff.LessThanOrEqual(tolerance) {
            return MatchResult{Status: "fx_variance", Confidence: 0.95, Diff: diff}
        }
        return MatchResult{Status: "amount_mismatch", Confidence: 0.5, Diff: diff}
    }
    return MatchResult{Status: "no_match", Confidence: 0}
}

Stage 4 & 5: Exceptions and Resolution

Not everything matches, and that's expected. The key insight is categorizing exceptions so you can automate resolution for the predictable ones and only surface genuinely weird cases to humans.

Our exception categories, ranked by frequency:

1. Timing mismatches — Transaction in our DB but not yet in PSP settlement (it'll show up tomorrow). Auto-resolve: park it and re-match next cycle.
2. FX rounding — Amount differs by <0.5% due to currency conversion at slightly different rates. Auto-resolve: accept with variance flag.
3. Duplicate references — PSP retry created a duplicate entry. Auto-resolve: deduplicate by idempotency key.
4. Missing internal record — PSP settled something we have no record of. Manual review: this could be a webhook we missed or a test transaction.
5. Amount mismatch >0.5% — Something is genuinely wrong. Manual review: could be a partial refund, a fee we didn't account for, or fraud.

The Timing Problem Nobody Warns You About

Settlement windows are the silent killer of reconciliation pipelines. Stripe settles on T+2 rolling. Bank transfers might be T+1. Some local payment methods in Southeast Asia settle on T+3 — or "whenever they feel like it" if there's a public holiday.

The naive approach is to reconcile daily. The correct approach is to reconcile in overlapping windows. Every run looks back 5 business days and re-matches everything in that window. Yes, you're doing redundant work. No, it doesn't matter — the compute cost is trivial compared to the cost of a missed transaction sitting in limbo for a week.

Currency Conversion: Trust Nothing

If you're operating across currencies, you'll discover that your internal FX rate, the PSP's FX rate, and the bank's FX rate are three different numbers. They were captured at different times, sometimes hours apart, and each party rounds differently.

Our rule: store the original currency and amount from every source. Convert to a base currency only for reporting, never for matching. Match on the original currency pair, and flag anything where the converted amounts diverge by more than your tolerance threshold. We started at 1% tolerance and tightened to 0.5% as we understood each PSP's conversion behavior.

# Ruby: Quick sanity check for FX drift in settlement batches
settlement_batch.group_by(&:currency_pair).each do |pair, records|
  rates = records.map { |r| r.settled_amount / r.original_amount }
  spread = rates.max - rates.min

  if spread > 0.02 # 2% spread within a single batch is suspicious
    AlertService.notify(
      channel: :recon,
      message: "FX spread alert: #{pair} spread=#{spread.round(4)} in batch #{batch_id}"
    )
  end
end

What I'd Do Differently

If I were building this from scratch today, I'd invest more upfront in the normalization layer. We bolted on PSP parsers reactively — every new integration meant a rush to write a parser before the first settlement file arrived. A config-driven parser framework (column mappings in YAML, not code) would have saved weeks of cumulative effort.

I'd also build the exception dashboard first, not last. The finance team doesn't care about your pipeline architecture. They care about the 12 transactions that didn't match and whether they need to do something about it. Start from that screen and work backwards.

References

• ISO 20022 — Universal financial industry message scheme
• Stripe Balance Transaction Types — Settlement reporting docs
• Adyen Settlement Detail Report — File format reference
• shopspring/decimal — Arbitrary-precision fixed-point decimals in Go
• SWIFT ISO 20022 Migration Programme

Disclaimer: This article reflects the author's personal experience and opinions. Product names, logos, and brands are property of their respective owners. Technical specifications are subject to change — always verify with official documentation.