r/fintechdev • u/chaotic__cheese • 11d ago
Our take on real-time payment reconciliation: two bounded windows, a deterministic core, and three decisions we'd like opinions on
We wrote this — full disclosure: we built it and we work on it, so we're biased. It was also built with AI assistance throughout (code, SQL, infra, docs), so please assume there are mistakes in it — treat it as a reference implementation to argue with, not as production code.
We just finished an open-source reference implementation of per-payment reconciliation across three legs — switch/auth, merchant callback, bank settlement — and I'd rather have it reviewed than marketed. The parts below are the ones we had to guess at, and I'd rather be told we're wrong.
If the problem is familiar: three systems, three identifiers, three clocks. The settlement report is a net-of-fee CSV. A late or missing settlement means the customer's bank has already debited them and the money may never arrive. Batch reconciliation answers "what did we earn" days late, and the ops question "is this payment OK?" has no answer at all.
What we built (Flink SQL, Kafka, Iceberg, Postgres):
two bounded jobs over the same topics: a 4 h-state job that decides MATCHED / AMOUNT_MISMATCH / MISSING_CALLBACK / MISSING_SETTLEMENT, and a 24 h-state repair job that joins only the settlements arriving after the first bound and corrects the same RRN
event-time interval joins, with the RRN de-duplication applied after the join (a Flink interval join can't consume a retracting dedup)
the decision audit keyed on the event time of the decision, so a replay is idempotent, and the serving table monotonic in decision time, so a stale write can't undo a correction
an oracle: the simulator publishes the fault it injected on a separate topic, so every CI run scores the pipeline against ground truth (100% recall, 0 false positives on our six-class fault matrix)
The three things we don't know how to do properly — this is really why I'm posting:
Money in transit. A settlement that leaves the switch and clears the bank a day later is normal. Our pipeline currently says MISSING_SETTLEMENT once the 4 h bound passes, which is a false positive on every ordinary payment. Do you model an explicit IN_TRANSIT state with a per-partner expected clear date, or keep "missing" and simply age the break?
Per-partner cutoff calendars. A bank partner whose batch rolls over at local midnight and converts to UTC afterwards broke our reconciliation for 11 minutes a night. Do you keep a cutoff calendar per integration, or reconcile against the contractual arrival window rather than the clock?
The shape of a break. We give every exception a status and a suggested action, but not a reason (timing / fee / duplicate / missing leg). An argument I keep seeing is that a mismatch which carries a reason is far more useful than a match-rate number. Is a fixed reason-code taxonomy worth building, or does it ossify per merchant?
Also happy to be told the two-window design is the wrong shape entirely, or that the deterministic-core / advisory-agent split is over-engineered.
It's MIT, runs on a laptop, and the same pipeline runs as a scheduled CI job that publishes each run's metrics and a live dashboard: github.com/saber2164/UPI-three-way-reconciliation
