Transaction Reconciliation Engine — Foundry Case Study

The Problem

Reconciling payment data across Stripe, PayPal, and bank statements manually takes accounting teams days of spreadsheet work per month. Dates disagree because of settlement delays. Amounts disagree because of fee deductions. Reference numbers change format between systems. The result is growing piles of "unmatched" transactions, delayed financial reporting, and eroding trust in the numbers.

What I Built

An automated reconciliation engine in Go that ingests transactions from three source types, normalizes them into a canonical format, and matches them through a 4-rule confidence-scored cascade — producing confirmed matches, categorized discrepancies, and settlement reports.

Architecture

Language: Go — single static binary, no runtime dependencies, scratch Docker image.

Data stores: PostgreSQL (transactions, matches, discrepancies, runs) + Redis (dedup cache, distributed locking).

Structure: Clean Architecture with interface-driven dependency injection:

cmd/recon/main.go          → CLI and server bootstrap
internal/adapter/           → Source adapters (Stripe, PayPal, CAMT.053)
internal/engine/            → Ingester, Reconciler, Scorer, DiscrepancyManager
internal/domain/            → Domain types (Transaction, Match, Discrepancy, Source)
internal/repository/        → PostgreSQL repositories
internal/api/               → REST API with middleware, routing, error handling
internal/lock/              → Redis SETNX+Lua distributed lock
internal/scheduler/         → Background job scheduler
internal/observability/     → Zerolog + Sentry integration
internal/report/            → Settlement and discrepancy report generators

Technical Decisions

1. Scoring Over Lookup

Every gateway transaction is evaluated against every unmatched ledger transaction through four independent rules. Each rule returns a confidence score. The highest confidence wins:

Exact (1.00): Amount + currency + date + counterparty all match
Amount + Date (0.90): Exact amount, date within configurable tolerance
Reference (0.80): One transaction's external_id found in the other's description
Fuzzy Amount (0.75): Amounts within percentage tolerance + relaxed date

Minimum threshold (default 0.70) gates persistence. This approach handles settlement delays, fee deductions, and format inconsistencies without custom rules per source.

2. Two-Tier Deduplication

Dedup key: SHA-256(source_id + external_id).

Hot path: Redis GET dedup:{key} → found = duplicate, skip. Fallback: PostgreSQL SELECT WHERE dedup_key = ? → found = duplicate, re-cache to Redis. Race guard: INSERT ... ON CONFLICT (dedup_key) DO NOTHING catches concurrent goroutines.

Redis is a performance optimization. PostgreSQL is the correctness guarantee. The system is correct without Redis. It is just slower.

3. Source Adapter Isolation

Each payment source implements the SourceAdapter interface — FetchTransactions for APIs, ParseFile for file-based sources. The adapter converts source-specific responses into a canonical IngestRequest:

Stripe: Cursor-paginated GET /v1/balance_transactions, exponential backoff retries, direction mapped from transaction type (refund/payout/stripe_fee = debit).
PayPal: OAuth 2.0 client credentials flow with cached token (60s pre-expiry buffer), date range split into 31-day windows (API constraint), decimal string to cents via math.Round(f * 100).
CAMT.053: ISO 20022 bank statement format used across European banks. The XML is deeply nested, but the adapter flattens it into the same canonical IngestRequest that Stripe and PayPal produce. From the engine's perspective, a CAMT.053 entry and a Stripe balance transaction are identical.

4. Distributed Locking

Redis SETNX for acquisition with TTL. Lua atomic script for release:

if redis.call("GET", KEYS[1]) == ARGV[1] then
  return redis.call("DEL", KEYS[1])
end
return 0

Owner verification prevents a slow process from releasing a lock that expired and was re-acquired by another instance.

5. Discrepancy Lifecycle

Unmatched transactions become discrepancies. The DiscrepancyManager handles:

Categorization: Amount mismatch vs. date mismatch, with expected/actual values captured
Deduplication: Existing open discrepancy for the same transaction prevents duplicates
Resolution: Mandatory resolution_note — cannot silently close a discrepancy
Auto-resolution: When a subsequent reconciliation run matches a previously unmatched transaction, its discrepancy is automatically resolved
Age escalation: Open discrepancies escalate severity at thresholds: >7 days = high, >30 days = critical

6. Money Representation

All amounts are int64 cents. No float64 anywhere in the domain. PayPal's decimal string amounts are converted at the adapter boundary via int64(math.Round(f * 100)). CAMT.053 amounts use int64(f*100 + 0.5). Both approaches round correctly for standard currency denominations.

Decision Log

Decision	Alternative Rejected	Why
Confidence scoring cascade	Hash-based exact lookup	Settlement delays shifted dates 1-2 days for 27% of a single merchant's Stripe data. Exact matching produced false discrepancies for every one of them.
Two-tier dedup (Redis + PostgreSQL)	Redis-only dedup	Redis is a performance optimization, not a correctness guarantee. The system must admit zero duplicates during a Redis cold restart or connection timeout. PostgreSQL `ON CONFLICT DO NOTHING` is the fallback.
Go with value-type structs	Python / Node.js	The inner matching loop evaluates O(n x m) pairs. `MatchCandidate` is stack-allocated with zero pointer indirection. The GC never sees scoring artifacts. A single static binary compiles to a scratch Docker image with one file.
`int64` cents, no `float64`	Decimal library / float amounts	PayPal returns decimal strings. `float64` truncation on `49.99` produces `4998` instead of `4999`. `math.Round(f * 100)` at the adapter boundary eliminates this. No float field exists anywhere in the domain model.
Owner-verified Lua lock release	Simple `DEL` on unlock	A slow reconciliation run can exceed the lock TTL. Without owner verification, the next instance acquires the expired lock, and the slow process then DELs the new owner's lock. The Lua script checks `GET key == owner` atomically before `DEL`.

CLI and Operations

The binary supports six subcommands:

recon serve          — HTTP server with full dependency injection + scheduler
recon sync stripe    — Manual Stripe sync trigger
recon upload <file>  — Ingest CAMT.053 bank statement
recon reconcile      — Run matching cascade for date range
recon report         — Generate settlement or discrepancy report (JSON)
recon discrepancies  — List discrepancies with status filter

The scheduler runs five background jobs: Stripe sync, PayPal sync, auto-reconciliation (48-hour rolling window), discrepancy aging, and stale lock cleanup.

Test Coverage

70 Go files across 10 internal packages (42 source, 28 test). Every implementation file has a parallel *_test.go. Adapter tests spin up httptest.Server instances to simulate Stripe/PayPal API responses including error codes (401, 429, 500) and retry behavior. Engine tests verify scoring determinism, dedup correctness, and discrepancy lifecycle transitions. Integration tests cover full reconciliation runs with realistic multi-source datasets.

Results

The engine reconciles multi-source transaction data without manual intervention. The scoring cascade catches settlement delays and format inconsistencies that exact matching rejects entirely. Every reconciliation run records its own DurationMs, MatchRate, and DiscrepancyCount for auditability.

The binary compiles to a single static executable. Docker image is a scratch container with one file. No runtime dependencies, no configuration drift between environments. Build, test, and deploy is a single go build from any machine with Go installed.

What I Would Change

The MatchCandidate is already stack-allocated and the per-pair cost is low, but the outer loop still iterates all unmatched ledger transactions for each gateway transaction. At volumes above 50,000 per side, the wall-clock time becomes the bottleneck. Partitioning by currency first would eliminate cross-currency comparisons entirely. Date bucketing would shrink the candidate pool further. The TransactionFetcher interface already supports filtered queries, so this is a repository change, not an engine rewrite. I would add currency partitioning first because it is the cheapest filter with the highest elimination rate.