From May to June last year, we spent four afternoons in the finance office of Lianyu Trading at Xinfa Di. Three months of original documents were spread across the table — A4 printouts, handwritten slips, carbon copies faded to gray, a few printed WeChat screenshots. What we were building sounded simple: a statement of account for every customer at month-end.
The difficulty was exactly that statement.
First, How the Business Actually Runs
Lianyu runs fresh-produce wholesale, supplying dozens of secondary wholesalers and a batch of cafeteria clients. A few hundred orders ship daily, each often spanning seven or eight categories, with different weights, unit prices, and case counts. Drivers load up in the morning with duplicate-copy delivery notes; customers sign one copy, the driver brings it back, and data entry clerks key it into the system in the afternoon.
Sounds standard. Only when you actually flip through the documents do you find:
- A delivery note isn't always settled in one payment. Some customers settle weekly, so one delivery note maps to two or three payment vouchers — we came to call this one order, many invoices.
- Returns never delete the original note. Vegetables that arrived spoiled come back the next day with a red-letter note to offset them; the original stays.
- Rounding down is the norm. The computed total is 3,278.60, the customer pays 3,270 — that 8.60 exists in no ledger, but finance keeps a mental tally.
- Some customers buy by weight, others by the case — the same category in two units, requiring conversion at reconciliation.
All of this used to be hand-reconciled in Excel by an accountant with over a decade on the job. She had her own color code: yellow for unsettled, green for settled, red for returns. When we took over, her sheet had 2,000-plus rows and 17 columns.
Why Version One Was Wrong
In version one we modeled around the order: one record per order, payments attached to it in a single paid_amount field. The first statement we generated, the client glanced at and said it was wrong — three entries missing.
Missing were the one-order-many-invoice cases. One note with two payments; the second overwrote the first.
Version two moved payments into their own table, one-to-many with orders. The counts were right this time, but the amounts didn't match — off on returns and rounding. We were treating red-letter notes as negative-amount orders, but they offset a note from three months earlier. Cross a month boundary, and this month's statement spontaneously grew a negative entry. The client said: "I didn't return anything this month."
Version three finally made it click: a statement of account isn't an "order summary." It's the running ledger of three things — receivable, received, adjusted — for one customer over a period. It's not the order table and has to be computed separately.
The 3 Tables We Ended Up With
- bill_items: statement detail rows. One row per customer per month per category, holding opening balance, receivable this period, returns this period, rounding, and closing receivable — 5 amount fields.
- settlements: payment records. One order with many invoices means many rows, each with voucher number, date and amount, loosely associated with the order, not enforced.
- adjustments: adjustment entries. Return offsets, rounding, bad debt — all here, each carrying the original note number and a reason, so cross-month items can be attached back.
A scheduled job generates statements at 2 a.m. on the 1st of each month, running with table locks and writing a bill_header table with the totals. We'd been burned before by a cron job running twice — the flock and run_id safeguards were borrowed straight from that experience.
The generation logic isn't complicated, but three rules came out of those four afternoons with the paper documents:
Returns only offset the receivable of the month containing the original note, never entering the current month's flow. Rounding gets its own column, never quietly spread into unit prices. One-order-many-invoices are assigned to their months by voucher date, not order date.
The third rule took the longest to settle. The accountant wanted order dates; the business side wanted cash-received dates. We landed on voucher date, because that's what bank statements follow — it reconciles.
Those 17 Columns
We printed her 17 Excel columns and went through them one by one: "Do you use this column at month-end reconciliation?" As with the visa form, 6 moved off to the right: driver name, plate number, loading time, customer contact, remarks, and data-entry clerk. Not useless — just not things that belong on a statement. We kept 11 columns, 3 computed by the system, with people filling only 8.
She stared at the printout for a moment, then said: "Don't delete the plate number column — sometimes I need it to find a note." So the plate number stayed in the detail rows, simply excluded from amount calculations. We never wrote that into the requirements doc; we wrote it on a sticky note and stuck it to the monitor.
Launch Day
On June 5, the first automatic generation. We laid the system's statement beside her handmade one and went through them line by line for about two hours. Two discrepancies: one rounding entry she'd logged in May while the system recorded it in June by voucher date; and an April red-letter note the system offset in April, where she habitually attached it to May. Both were convention mismatches, not calculation errors. We aligned the conventions, re-ran, and everything matched.
She said nothing, printed the system's statement, signed the top-right corner with a pen, and clipped it into her folder — the folder that still held months of yellow, green and red.
Afterward
Month-end reconciliation went from two days to half a day — that part is visible. But what stayed with us wasn't the time saved; it was "don't delete the plate number column." Every amount in the system can be computed. That one column exists for a human being.
The ERP later gained stores, stocktaking, and Excel export; the schema grew from 3 tables to a dozen-plus. Every time we touch the reconciliation part, we still spread those three paper documents out and look at them first.
Asked where the hardest part of this system lies, we don't say the database. We say those four afternoons.
