Mastering Reconciliation Systems: Design, Architecture, Data Management & Error Handling

1. Reconciliation Overview

Everyday activities involve reconciliation, from confirming a restaurant payment to matching thousands of e‑commerce orders with payment records. Reconciliation is the verification of "account‑document‑reality" consistency, typically in three modes: account‑document, account‑account, and document‑reality checks.

1.1 Definition of Reconciliation

Reconciliation verifies that the recorded account (账), the supporting document (证), and the actual funds or goods (实) match. For example, a restaurant bill, the cash paid, and the cash register entry must all align.

From a financial perspective, documents include invoices, receipts, shipping orders, and payment logs. From a broader view, any data requiring consistency—such as attendance counts—can be considered reconciliation.

1.2 Reconciliation Scenarios and Models

Typical scenarios include third‑party payment platforms, e‑commerce platforms, and financial clearing institutions. Models are divided into transaction reconciliation, fund reconciliation, balance‑adjustment reconciliation, and other types.

(1) Transaction Reconciliation Model

Data are matched one‑to‑one, one‑to‑many, or many‑to‑many using unique identifiers.

(2) Fund Reconciliation Model

Transaction data are aggregated by payment type (e.g., collection, fees) before comparison.

(3) Balance‑Adjustment Reconciliation

System balances are compared with actual account balances after in‑transit adjustments.

2. Reconciliation Data Management

Data sources include channel files, platform‑generated files, APIs, MQ messages, SQL extracts, and manual uploads. Configuration covers source acquisition, parsing rules, and storage.

2.1 Basic Data Configuration

Obtain raw data (platform, channel) easily.

Parse or reprocess file data.

Flexibly configure matching rules.

View reconciliation results.

Track and handle differences.

Provide results externally.

Export data.

2.2 Channel Data Acquisition and Parsing

Payment channels (WeChat, Alipay, UnionPay, etc.) provide clearing and settlement files, usually in Excel or txt formats. Files can be downloaded via APIs or manually and then uploaded to the reconciliation system for parsing.

2.3 Self‑Owned Data Acquisition

Internal data can be obtained via scheduled file generation (FTP), API reception, MQ subscription, periodic SQL extraction, or manual upload for external systems like Kingdee.

3. Reconciliation Project Design

Projects group related reconciliation tasks, allowing configuration‑driven management to reduce development effort.

3.1 Transaction Reconciliation Projects

Projects compare platform payment data with channel clearing data, producing results and error handling. Naming conventions (e.g., "Member Purchase WeChat‑Collect") help identify the purpose.

Project 1: Member purchase WeChat‑collect

Project 2: Member purchase WeChat‑refund

3.2 Fund Reconciliation Projects

Each fund account (e.g., WeChat‑Account1, Alipay‑Account3) becomes a project. Files are matched to accounts using naming rules like "channel‑account‑type‑date".

3.3 Balance‑Adjustment Reconciliation

Ensures platform balances align with channel balances after in‑transit adjustments.

4. Reconciliation Engine

4.1 Reconciliation Control

Controls include continuity (no cross‑day reconciliation), time windows, and status tracking to avoid duplicate runs.

4.2 Core Processing Engine

The engine executes daily, per‑project, per‑record matching, marking each as balanced, one‑sided, or erroneous.

4.3 Reconciliation Result Management

Results include total counts, differences, and detailed views for both transaction and fund reconciliation.

5. Error Handling

Errors are discrepancies between recorded data and objective facts. The process includes detection, root‑cause analysis, solution design, and execution, often via standardized SOPs.

5.1 Error Handling Principles

Even identical errors may be resolved differently depending on company policy (e.g., absorb loss vs. recover from user).

5.2 Accounting‑Related Errors

Strikethrough correction for textual errors.

Red‑letter reversal for debit/credit mistakes.

Supplementary entry for missing amounts.

5.3 Transaction‑Related Errors

Errors arise from mismatched transaction records; handling varies by payment type (online, offline, refund, payout, etc.).

5.4 Fund‑Related Errors

Long‑short fund differences are cleared via offset entries; each offset generates a journal record.

6. Should Reconciliation Precede Settlement?

The article discusses the dilemma of “reconcile first, then settle” versus “settle first, then reconcile.” It proposes a flexible approach: start with a threshold‑based settlement that allows timely payouts while gradually improving reconciliation capability until full pre‑settlement reconciliation is achievable.

In summary, the guide emphasizes a strategy that balances data accuracy, merchant experience, and bank reputation, advocating an adaptable relationship between reconciliation and settlement based on operational maturity and business priorities.