CASE 08
14 weeks
Replaced a wall of spreadsheets with one unified operations view.
A regional logistics operator ran the business from twelve interlinked Google Sheets. We modelled the real operation into Postgres, ingested live telematics, and shipped one dashboard where dispatchers, drivers and the owner each see exactly their slice — with the historical spreadsheets migrated rather than abandoned.
THE PROBLEM
The fleet manager ran the business out of twelve Google Sheets linked by pray-and-hope formulas. Every question about a shipment required three tabs and a phone call.
The sheets had grown organically over six years. Cross-sheet formulas broke silently when someone inserted a row, and nobody could say with confidence which sheet was authoritative for vehicle status.
Driver locations lived in the telematics vendor's portal, which nobody in dispatch used because it did not know about shipments. Position and job were two separate mental models joined by phone calls.
Month-end reconciliation — fuel, mileage, incidents, subcontractor invoices — took the fleet manager the better part of a week, and produced numbers the owner did not fully trust.
CONSTRAINTS WE WORKED UNDER
WHAT WE DID
Normalized the data model into Postgres — shipments, drivers, vehicles, fuel, incidents — with migration scripts that preserved the historical sheets.
Live driver GPS ingestion (existing telematics provider) so the dashboard shows real positions, not yesterday's snapshots.
Role-based access so dispatchers see their region, the owner sees everything, drivers see their own day.
Mobile-optimized views for the yard and on-the-road use.
ARCHITECTURE
01
Shipments, legs, vehicles, drivers, fuel events, incidents and subcontractors as first-class tables with real foreign keys and constraints. Most of the spreadsheet chaos was a modelling problem: once a leg existed as an entity, half the reconciliation questions answered themselves.
02
Repeatable importers for each sheet, with validation reports listing every row that failed and why. Run nightly against the live sheets throughout the parallel-running period, so the database was never stale during transition.
03
Poller against the vendor API writing positions into a time-series table, with Redis holding the current position per vehicle for fast dashboard reads. Historical positions retained at reduced resolution beyond thirty days.
04
One application, three experiences. Dispatchers get regional queues and assignment; the owner gets margin and utilization; drivers get today's jobs, a proof-of-delivery capture and nothing else.
05
Driver screens cache the day's assignments and queue status updates locally, syncing when signal returns. A yard with no bars was the normal operating condition, not an edge case.
KEY DECISIONS
WHY
A hard cutover on a business that cannot stop is how you get a rollback. Nightly re-imports meant the database could be wrong on a Tuesday without anyone losing a shipment.
TRADE-OFF
Six weeks of double entry for some fields, and importer maintenance during that window. The client called it the reason the project landed at all.
WHY
The hardware was installed, paid for and working. Replacing it would have added cost and a fleet-wide installation schedule to a software project.
TRADE-OFF
We inherited their API's rate limits and occasional gaps, which the ingestion layer smooths over rather than fixes.
WHY
Sixty drivers, mixed devices, no MDM. Distribution and update cost for native apps would have exceeded the build cost of the feature.
TRADE-OFF
No background location and a weaker offline story than native would give. Both were solvable well enough with caching and queued writes.
THE HARD PARTS
Vehicle registrations appeared in four formats, driver names in three, and dates in whichever locale the entering person's browser used. The importers normalize aggressively and log every transformation, so the fleet manager could audit the migration rather than trust it.
One sheet computed subcontractor margin through a chain of five cross-references. Rebuilding it required sitting with the fleet manager and reconstructing the intent, which surfaced two long-standing calculation errors that had been quietly under-billing a client.
The first dashboard was organised the way the data was organised. Dispatchers work by region and time-of-day, so we reorganised around that, which cut the clicks for the most common action from four to one.
HOW IT RAN
01
Sat with dispatch and the fleet manager, mapped the real workflow, and reverse-engineered every sheet including the formulas nobody could explain.
02
Postgres schema, importers with validation reporting, and the first full historical load with a written reconciliation against the sheets.
03
Dispatcher and owner views, live position ingestion, role-based access, and driver mobile views with offline tolerance.
04
Six weeks of nightly re-import overlapping the build, then a staged cutover region by region, then handover.
OUTCOME
~10 hrs/wk
Reconciliation time saved
12 → 1
Spreadsheets replaced by one system of record
6 yrs
Historical data migrated and reconciled
0
Days of operational downtime during cutover
One source of truth. The fleet manager estimates it saves around ten hours a week of reconciliation, and the owner can finally answer 'how are we doing?' without a phone call.
Month-end went from a week of assembly to a report that was already correct. The owner's first reaction was to ask for margin by client, a question the spreadsheets could not have answered at all.
The two billing errors uncovered during migration paid back a visible share of the project cost in the first quarter.
STACK
APPLICATION
DATA
INTEGRATION
OPS
HANDOVER
