Zhongdayuan

Background

The client analyzes bank statements to trace where money goes. A case can involve dozens of accounts and tens of thousands to over a million transactions. Previously, staff marked transactions by hand in Excel. Tracing funds could take days, and changing the criteria meant starting over.

I wrote a 105-line Node.js script to check whether first-in, first-out tracing could match the manual results. Once it worked, the scope grew to include more bank formats, case collaboration, working paper exports, and an offline version. The script became a case management system.

1. 01ImportImport bank statements and detect column headers
2. 02CleanMerge worksheets, remove duplicates, and standardize fields
3. 03TraceTrace funds through accounts in FIFO order
4. 04ExportFund trees, flow graphs, working papers, and evidence request lists

What I did

I handle requirements, design, development, deployment, and maintenance. The system has four main parts:

• Fund analysis: Transaction filtering, fund tracing, fund trees, and flow graphs. Results can be exported as working papers and evidence request lists.
• Data cleaning: Parses statement formats from multiple banks in China, merges and deduplicates worksheets, and handles Excel files of around 500 MB per import.
• Case management: Case creation, progress tracking, reply workflows, and notifications. Access depends on user role, department hierarchy, and case participation. Every action is logged for audit.
• Licensing and distribution: Offline licensing for the desktop version (issue, activate, unbind, deactivate) and release distribution.

Technical details

Tracing algorithm: FIFO (first in, first out) matches each incoming transaction to later payments. BFS (breadth-first search) follows the funds through downstream accounts. Additional rules handle balances, full-amount transfers, and small transactions.

In

300

Mar 1

500

Mar 2

200

Mar 4

Out

300

100

Mar 3, out 400

400

200

Mar 5, out 600

Colors match outgoing payments to incoming funds in FIFO order.

Header detection: Banks use different statement headers. The system maps 99 Chinese header aliases to standard fields, such as transaction date, account number, and debit or credit, during import.

Large imports: PostgreSQL stores transactions in monthly partitions. Streaming COPY imports 100000 rows per batch. Partition creation and COPY run in separate transactions to avoid lock conflicts.

Offline desktop version: The Electron app bundles PostgreSQL 17 and needs no separate dependency installation. The installer was reduced from 269 MB to 129 MB. Ed25519 signatures allow license verification without an internet connection.

Unit tests: Forty test files cover fund tracing and data cleaning, with over 93% coverage of core business logic.