P-04 · Enterprise system · Web · Under NDA

Wholesale ERP & Accounting

Two generations of an order, logistics and double-entry accounting system for a wholesale food distributor.

Role: Solo — system design, development, deployment, data migration
Timeline: Two generations · client under NDA
Platforms: Staff back office (web) · Sales-rep portal · B2B customer portal
Links: Private system — client under NDA

React 18ViteSupabase (Postgres, Auth, RLS, Edge Functions)PHPWordPress plugin architectureMySQLGoogle Maps DirectionsSheetJSVitest

TL;DR

For a wholesale food distributor, Jin Oh designed and built — alone — the software that runs the business, twice. Generation 1 is a WordPress-based ERP covering B2B ordering, processing stations, truck routes, picklists, invoices, returns and payments in English and Chinese. Generation 2 is a React + Supabase platform for sales reps and admins with a double-entry general ledger that replaced QuickBooks Desktop, including a full migration of historical QuickBooks data.

Key facts

01About 100,000 lines of code across both generations, with roughly 65 custom database tables.
02Order lifecycle pending → accepted → shipped → posted with explicit allowed transitions and full rollback of downstream data.
03Truck routes drawn on Google Maps in the dispatcher’s exact stop order, chunked to stay inside Directions API waypoint limits.
04Returns become credits that carry forward automatically to the customer’s next invoice.
05Every operational document (invoice, bill, payment) posts balanced journal entries to a built-in general ledger with period close and lock.
06Historical QuickBooks Desktop data migrated through IIF and XLSX importers with batch tracking and ID mapping.

~100klines of code, two generations
~65database tables
47row-level security policies (Gen 2)
27automated test files (Gen 2)

The problem

The distributor ran orders by phone, spreadsheets for picking, and QuickBooks Desktop for the books. Nothing was connected, so every order was typed three times.

Generation 1 connected ordering to the warehouse and the trucks. Generation 2 connected it all to the money.

Who it serves — and what they needed

Owner / managementReplace spreadsheets and QuickBooks with one system and real-time numbers.
Sales repsTake orders on the road with the right price floor for each customer.
Warehouse, drivers & dispatchKnow what to cut, pick and load — and in which order to deliver.
BookkeeperTrust the ledger: P&L, balance sheet, aging and GST/HST without re-keying.

How it fits together

FIG.From order to ledger

01Gen 1 — order to delivery routeB2B customer → WordPress ERP → Processing stations → Routes & picklists → Google Directions
02Gen 2 — order to ledgerSales rep → React portal → Supabase Postgres · Admin approval → Supabase Postgres → General ledger → Financial reports
03QuickBooks history brought acrossQuickBooks data → Supabase Postgres

What’s inside

Ordering

B2B shop with per-customer catalogue, sorted by order frequency
Sales-rep portal with minimum pricing, one-time and pickup customers
Pending-order merge that combines identical lines

Warehouse

Station work orders per order line
Picklists with box counts: ceil(qty ÷ box weight) to one decimal
Inventory reserved on order, deducted on fulfilment

Delivery

Trucks with regions and operating days
Route sequence per truck per date
Estimated load weight per truck

Billing

Invoice created on ship, printable
Return credits carried to the next invoice
Payments allocated across invoices, partial and cheque payments

Accounting (Gen 2)

Chart of accounts, journals, bank reconciliation
Fiscal period close and lock
P&L, balance sheet, trial balance, AR/AP aging, GST/HST

Migration & quality

QuickBooks IIF parser and XLSX transaction importers
Cutover runbook
27 Vitest test files; spec-first design docs

Hard parts, solved

C-01
Replacing QuickBooks without losing history
Problem
Years of customers, vendors, items and transactions — including credit memos — lived in QuickBooks Desktop, with no API.
Approach
Parsed IIF list exports and XLSX transaction reports, classified each QuickBooks transaction type, and materialised it as a native document that posts to the ledger; batch tracking and qb_list_id mapping make re-runs idempotent.
Result
A clean cutover with history intact and no duplicate records.
C-02
Operations that post their own journal entries
Problem
The bookkeeper needed a real double-entry ledger, but staff should never have to think in debits and credits.
Approach
A hybrid GL: every invoice, bill, payment and credit posts balanced entries automatically, while closed periods reject back-dated changes.
Result
Financial statements are always current, and closed months stay closed.
C-03
Long delivery routes in the dispatcher’s order
Problem
Google Directions caps waypoints per request, and its optimiser reshuffles stops that dispatchers ordered deliberately.
Approach
Split each route into 25-stop chunks (origin + 23 waypoints + destination), stitched end to end, with optimisation turned off.
Result
Any route length renders exactly as planned.
C-04
Undo that really undoes
Problem
Moving an order back from shipped to accepted has to unwind invoices, routes, picklists and station jobs — or the books drift.
Approach
An explicit state machine with allowed transitions; each backward move deletes and rebuilds the downstream data in a transaction.
Result
Staff can correct mistakes without calling the developer.