Case study 02 · Data architecture
Reporting the ERP couldn't do
The ERP ran the business fine. Reporting on it was another matter — numbers moved with report settings, and definitions lived nowhere. The fix wasn't a better report. It was owning the data: an archive-first layer built from the ERP's API, with definitions written once in code and every figure reconciling back to the source, to the cent.
Reconciliation variance
Across all nineteen invoice months in the archive — raw capture, transformed layer, and derived reporting re-sum to the cent, every month.
The layer's trust model is one rule: derive and tie back, never fork. Nothing in it is hand-maintained; every figure re-derives from raw payloads on demand and must reconcile to the live system. The layer never asks to be trusted — it invites the check, and the check is the exhibit.
The business question
The ERP did what ERPs do: ran projects, time, and invoicing without drama. Reporting was the weak half. The same question could return different numbers depending on report settings; definitions lived in whoever built the last export; and anything leadership actually wanted became a spreadsheet expedition. The reporting module was the dependency nobody chose but everyone inherited.
The stakes were concrete: a monthly reporting pack — labor validation, projections versus actuals, regional revenue — that finance reviews and leadership decides from. Numbers that move when a filter breathes are worse than no numbers, because they spend trust that measurement work needs later.
The instrument
The fix was to stop asking the ERP to report and start owning the data. A daily, count-verified pull captures every API payload verbatim into a raw archive — the permanent record. A typed layer regenerates from raw with every definition standardized once, in code. The reporting pack derives from that layer, and an in-ERP check holds newly entered data to the same definitions before it reaches an invoice — consistent data in, consistent reporting out.
The design principle that makes it defensible is the trust model: derive and tie back, never fork. The layer holds no hand-keyed numbers. Every figure re-derives from raw payloads on demand, and the derivation must reconcile to the source system — per record in sampled audits, per month against the invoice ledger, to the cent. When the reconciliation is boring, the layer is working.
Method
Raw capture came first, and it earned its paranoia. Bulk pulls are count-verified because the API once returned short-but-clean responses that would have silently dropped rows. Thousands of records turned out to be invisible to the ERP’s own list queries — orphaned when parent records were deleted — and were salvaged individually by id; a handful the ERP had purged entirely were recovered from the prior pipeline’s files, their only surviving copy. The archive also keeps what the ERP later deletes: it still holds an invoice the live system no longer remembers.
Definitions came second, and only once. The typed layer encodes what every field actually means — where the ERP’s semantics shifted over time, the layer documents the boundary and computes around it rather than pretending history is clean. That discipline is what made the measurement work in case study 01 defensible: the failed first framing there was caught precisely because the layer knew where its own data stopped being comparable.
Verification runs standing, not once. Aggregates re-derive from fresh raw pulls with zero variance; sampled records match the live API field for field; and the reporting layer reconciles to the invoice ledger monthly. The strongest single moment: with the leadership dashboard a refresh behind, the layer computed what that dashboard would show once it caught up — and when it refreshed, it matched to the dollar. The layer doesn’t chase the dashboard. It predicts it.
Measured outcome
Stratified record samples matched the live system exactly, field for field, in the layer's standing audit.
ERP modules reconciled with zero layer-side discrepancies at cutover, arbitrated record-by-record against the live API.
Successful automated pulls since cutover — every bulk fetch count-verified before the layer accepts it.
What the layer changed: the monthly reporting pack — labor validation, projections-vs-actuals, regional revenue — now regenerates from one set of definitions instead of being rebuilt inside the ERP's reporting module, and the revenue figures leadership sees reconcile to the invoice ledger to the cent. What it deliberately doesn't claim: no dollar savings estimate, and no pretense the ERP itself changed — the point was to stop depending on the part of it that couldn't be trusted. When the ERP reaches its scheduled shutdown, the archive simply keeps going.
Appendix — method notes
Design. Two layers in one embedded database. The raw layer stores every API payload verbatim — the permanent record, including rows the ERP has since deleted. The typed layer is regenerated from raw, with every definition standardized once in code; changing a definition means one rebuild, not a hunt through saved reports.
Hardening. Bulk pulls are count-verified before acceptance; incremental fetches key on modification timestamps with a lookback window; 6,820 records invisible to the ERP's own list queries were salvaged individually by id, and 27 purged rows were recovered from the prior pipeline's archive — its only surviving copy.
Verification. A standing audit re-derives aggregates from fresh raw pulls (zero variance), samples records against the live API field-for-field, and reconciles the reporting layer to the ERP's invoice ledger monthly. The layer once predicted what the leadership dashboard would show after its next refresh; the dashboard refreshed and matched to the dollar.
Boundaries. The ERP vendor is deliberately unnamed. Figures are ratios, counts, and variances by design — no client names, no absolute revenue, no row-level data. The measurement work built on this layer is case study 01.