How to Build a Scalable Reconciliation System for High‑Volume Transactions

1. Reconciliation Overview

Reconciliation (or "account verification") ensures consistency among records such as invoices, receipts, and system entries. Three basic verification modes exist: document‑to‑account, account‑to‑account, and account‑to‑cash. The goal is to guarantee that every transaction is recorded correctly across all systems, which is critical for both finance and business operations.

1.1 Definition

Reconciliation compares "account" (the ledger entry), "document" (the source proof), and "cash" (the actual funds or goods). Consistency among these three elements is required for accurate financial reporting.

1.2 Scenarios and Models

Typical scenarios include third‑party payment platforms, e‑commerce internal checks, and financial clearing institutions. Models are classified as transaction reconciliation, fund reconciliation, balance‑adjustment reconciliation, and other custom types.

2. Reconciliation Data Management

Data sources include channel files (e.g., settlement and clearing files from payment providers) and platform‑generated data. Files are usually Excel or TXT, but may also be XML, CSV, or PDF. Retrieval methods involve API downloads, manual downloads, or FTP transfers.

2.1 Channel Data Acquisition & Parsing

Obtain raw files via provider APIs or manual download.

Store files in a designated FTP directory with naming conventions.

Parse files either "as‑is" (preserving columns) or using generic templates, then load into a database.

2.2 Platform Data Acquisition

Platform data can be collected via scheduled file exports, inbound APIs, message queues, periodic SQL extracts, or manual uploads for legacy systems.

2.3 Data Classification

Data is grouped by high‑level categories (e.g., payment, coupon, order, clearing) and then sub‑categories, each mapped to its own database table to simplify querying and reconciliation.

3. Reconciliation Project Design

Projects define which data sets are compared, the unique identifiers used, and the frequency of execution. Projects can be transaction‑oriented (e.g., WeChat payment vs. clearing) or fund‑oriented (e.g., bank settlement vs. platform ledger).

3.1 Transaction Projects

Group by channel, payment type, and account.

Name projects clearly (e.g., "Member Purchase – WeChat – Collection").

3.2 Fund Projects

Each bank or payment account becomes a fund project (e.g., "WeChat‑Collection‑Account1"). Project configuration includes mapping clearing files to the appropriate account and defining fee‑type aggregation rules.

4. Reconciliation Engine

4.1 Control Logic

Continuity control – cannot skip days.

Time control – defines reconciliation date, start date, and end date.

Status control – tracks whether a project has been executed for a given date.

4.2 Core Processing

The engine performs daily, per‑project, per‑record verification, producing results such as balanced, one‑sided, or mismatched entries.

4.3 Result Management

Results are stored either in the source tables (simple cases) or in dedicated reconciliation tables (complex cases with multiple project overlaps).

5. Error Handling

5.1 Transaction Errors

Three outcomes exist: balanced, one‑sided, and mismatched. Errors are listed for manual or automated resolution, with predefined handling types (e.g., supplement missing orders).

5.2 Fund Errors

Fund discrepancies (long/short amounts) are identified, confirmed, and then either recorded as manual adjustments or automatically cleared via integration with upstream systems.

5.3 Accounting for Errors

All error corrections generate corresponding accounting entries (e.g., settlement, payment, or fund挂账) to keep the overall ledger balanced.