Design and Implementation of a Channel Reconciliation System for ZuanZuan Payments

With the rapid growth of ZuanZuan's business, transaction volume and payment channel interactions have increased dramatically, raising the risk of fund safety issues; therefore, a systematic reconciliation solution becomes essential.

1. Background – Reconciliation ensures consistency between ZuanZuan's internal transaction records and third‑party payment channel data, aiming for "no missing cents and no overpaid cents".

2. Overview – The system distinguishes online and offline reconciliation, focusing on channel (offline) reconciliation, which consists of an offline reconciliation platform and a balance center, with the platform split into data ingestion and data verification layers.

3. Design Idea – Reconciliation proceeds in three stages: data preparation, data verification, and error handling, linking the stages to address problem discovery, resolution, and prevention.

4. Data Preparation – Internal data is ingested via four methods: data pulling, file push, MQ push, and binlog listening, with binlog chosen as the primary zero‑intrusion approach; external channel data is downloaded via scheduled tasks, normalized to CSV, and stored locally to avoid repeated downloads.

4.3 Massive Data Solution – To handle up to 1 million records per day, the system uses a combination of sharding (partitioned MySQL tables) and Hive archiving, keeping recent data in MySQL (monthly partitions) and moving older data to Hive for long‑term storage.

5. Data Verification – Offline reconciliation runs on a T+1 cycle. Two granularity levels are employed: detailed line‑by‑line checks for pinpointing issues and aggregate checks for quick validation, both executed in parallel.

5.3 Reconciliation Methods – Two approaches were evaluated: Hive‑SQL comparison and MQ‑driven comparison; the MQ method was selected for operational convenience despite slightly lower efficiency.

6. Error Handling – An error‑driven model records abnormal reconciliation data, including scenario, error type, and processing node status, and triggers remediation either via scheduled tasks or automatic compensation.

7. Conclusion and Outlook – The integrated system efficiently performs channel reconciliation, reduces complaints, and improves fund flow transparency; future work includes expanding reconciliation coverage to settlement systems, margin accounts, and further reducing integration costs.

Advantages

Disadvantages

Sharding

Increases max connections; improves read/write performance

Complex transactions; wasted resources for non‑sharded queries

Cold‑Hot DB

Hot DB offers high performance for recent data

Cold DB requires compatibility handling for updates

ES/Hive

High query performance

Slightly weaker consistency; extra data extraction work