Reporting from an ERP is a cache invalidation problem. Query Epicor live and every dashboard waits on OData; snapshot nightly and finance works with yesterday's truth. We took a third path: an explicit, on-demand staging cache.
The pipeline
Every dataset starts as a BAQ (Business Activity Query) exposed through
Epicor's OData BaqSvc — OFPA-Company, OFPA-Book, OFPA-TB, and so on.
A sync route derives its endpoint from a single configured base URL, pages
through the result set with retry and timeout handling, transforms rows into
typed application models, and writes them to the staging cache.
The cache is the app's own D1 database — single-tenant, in the deploy region, and refreshed only through explicit sync actions. Reports never wait on Epicor; they read the cache and render.
Connections are data, not config
Version 16.3 introduced the integration runtime: ERP connections are rows in an encrypted table rather than environment variables. A connection stores the base URL and optional per-endpoint overrides; credentials are encrypted with AES-256-GCM under a key that never leaves the environment. Environment variables remain a fallback, but the admin UI is the source of truth.
That's also what makes the onboarding wizard possible — "test connection"
builds the same headers the runtime will use later, probes OFPA-Company,
and only then writes the row.
Consistency you can audit
Because syncs are explicit, every statement can tell you how fresh its inputs are, and the fiscal calendar service validates that any period you request actually exists before a single balance is fetched. When something does fail, each step returns a typed error — 503 for "not configured", 502 for "Epicor failed", 422 for "Epicor returned nothing" — instead of a JSON blob of stack traces.
The result: finance gets an explicitly refreshed, inspectable pipeline, and nobody has to trust a spreadsheet chain ever again.