Transaction Reconciliation System

Home > Portfolio > Transaction Reconciliation System

Turning millions of financial transactions into a system that could be trusted

A financial reconciliation platform built to automate the ingestion, processing, matching, and investigation of transactions across banking channels and external transaction sources — designed to answer, at scale, the question every digital bank eventually has to answer: *can we prove that every transaction recorded by one system is accurately accounted for everywhere else it touches?*

Key metrics

  • Integrated with mobile banking, ATM, and core banking systems
  • Automated identification of commissions, cash retracts, and cash refills
  • Configurable reconciliation modules with dynamic data mapping
  • Reduced manual investigation by ~85%
  • Deployed accorss 7 commercial banks

Context

As banks moved transactions from physical branches to digital and self-service channels, transaction volume grew fast — and so did the number of systems generating records of the same event. ATM networks, mobile banking, core banking, and payment processors each produced their own version of a transaction, often in incompatible formats.

That created operational questions that used to be cheap to answer and were quickly becoming expensive:

  • Did the transaction actually complete?
  • Did the customer receive the expected amount?
  • Was a transaction recorded twice?
  • Was a commission charged correctly?
  • Was cash retracted after being dispensed?
  • Which customer account does this transaction belong to?
  • Can it be traced back to its original source?
reconciliation system screenshot

The Challenge

The hard part wasn't comparing two files — it was building an engine that could ingest large volumes of financial records from different systems and formats while preserving data integrity end to end. An ATM log, for instance, could contain fields that had no direct equivalent in the corresponding core banking record.

That meant the system had to:

  • Ingest data from multiple sources
  • Parse different transaction formats
  • Transform records into a common structure
  • Match the same transaction across systems
  • Prevent duplicates
  • Preserve original source data for investigation
  • Protect sensitive financial information
  • Support investigation of unresolved transactions
  • Scale as volume increased

In practice, this meant the system needed to behave less like a CRUD application and more like a transaction-processing engine.

My Role

I was responsible for the architecture and development of the platform, not just individual features. My work included:

  • Designing the transaction-processing architecture and data model for high-volume records
  • Building ingestion and processing workflows, including automated ATM log extraction
  • Designing transaction matching, reconciliation, and duplicate-prevention logic
  • Designing the dynamic data-mapping system for onboarding new sources
  • Integrating directly with core banking systems (beyond file uploads)
  • Implementing transaction-related business rules (commissions, retracts, refills)
  • Handling encryption and secure storage of sensitive financial data
  • Designing the platform's modular reconciliation components

The central architectural responsibility: make sure a transaction could be traced from its original source through to its final reconciliation state without losing that chain.

Engineering Challenges

1
Volume as a first-class design constraint

At 5M+ transactions/day, this wasn't a script that loads two files into memory and diffs them — it was a system that had to continuously ingest, store, identify, and reconcile records without the database becoming the bottleneck. That reframed the problem from "how do we reconcile two files" to "how do we build an engine that can do this indefinitely, at increasing volume, without a redesign."

Simple archiving wasn't a viable answer to that question. Financial records carry regulatory and investigative requirements that mean data can't just be pushed off into cold storage and forgotten — it has to stay live, queryable, and traceable indefinitely. So instead of relying on ever-growing monolithic tables, I designed a middleware engine that broke incoming data down and created the underlying tables dynamically, without manual schema intervention as new sources or volume grew. The middleware sat between ingestion and storage, deciding how a given batch of transaction data should be structured and partitioned at the table level as it arrived, rather than requiring that structure to be predefined and maintained by hand.

That same middleware layer also drove reporting. Rather than relying on precomputed or scheduled reports, results were fetched dynamically based on the nature of the transaction and the requested time duration — so an operator querying a specific transaction type over a specific window wasn't scanning against static, generalized report tables, but pulling against structures shaped for that kind of query. This kept reporting responsive even as the underlying data volume kept growing.

2
Sources that spoke different data languages

Hard-coding every source's format would have made the system unmaintainable. Configurable mapping let the ingestion layer stay flexible while the reconciliation engine worked against one consistent model — new sources could be onboarded through configuration, not a rewrite.

3
Maintaining an audit trail

When a discrepancy occurred, an operator needed to trace it: where the transaction originated, what transformations were applied, whether it had already been processed or matched, and why reconciliation failed. Retaining raw ATM logs separately from structured, processed transaction data gave the system a clean separation between source evidence and processed state — critical for investigation, not just for reconciliation itself.

4
Protecting sensitive financial data

Card numbers and related financial information were encrypted before storage, with access gated by user permissions. Security had to be part of the data model from the start, not layered on afterward.

5
Reconciling more than amounts

A transaction isn't always just debit = credit. The engine needed to recognize related operational events — cash refills, cash retracts — and automatically associate commissions with their originating transaction. That meant modeling transaction *relationships* and business events, not just comparing values.

6
Getting from transaction to customer

The platform automatically identified the customer account tied to a given transaction, which turned "transaction X failed reconciliation" into "which customer and account were affected, and what should happen next" — a much more actionable output for the operations team.

Reflection

This project changed how I think about backend engineering. At smaller scale, a system is a collection of endpoints, tables, and business logic. At financial transaction volume, that model isn't sufficient — the questions that matter shift to how data moves through the system, how you guarantee a transaction is processed exactly once, how you trace it months later, and how you keep the system adaptable as external formats and volume both keep changing.

Good architecture here wasn't just about making the system work — it was about making sure it kept working as volume, integrations, and the cost of failure all increased. That's the lens I still bring to large-scale transaction-processing systems today.

Get In touch

Feel free to email me at hi@riteshr.com.np for counsuting work, and I'll get back to you.