A rollup warehouse on top of the Shopify Admin API
The Admin API is not an analytics database. So I built one.
Stack
Per-company revenue needs one API round trip each. Serverless gives you 300 seconds.
Sales reps are paid commission on the wholesale accounts assigned to them, which means the monthly question is "what did each company buy, in this period, since the rep was assigned to it?" The Shopify Admin API can answer that for one company at a time. Across a full roster it is hundreds of sequential round trips, and the report times out before it finishes — on a serverless platform that terminates the function at 300 seconds regardless. Two further traps sit underneath. Querying orders without an explicit date range silently returns only the last ~60 days, so a historical backfill can report success while missing entire years. And a money-only refund does not change the order total and frequently does not fire an update webhook at all, so it is invisible to anything watching orders alone — meaning a rep gets paid commission on money the business refunded.
Live ingest
Order create, update and cancel webhooks, plus a dedicated refund webhook, each apply a snapshot to the local ledger.
Historical backfill
A five-minute cron resumes from a versioned cursor, pages orders per status phase within an explicit date range, and checkpoints after every page.
Delta application
Each ingest is computed as a delta against what is already stored, so replaying any webhook is a no-op rather than a double count.
Daily rollup
Order facts aggregate into per-company and per-product daily rows, turning a per-company revenue question into a single indexed query.
Reporting
Commission and analytics endpoints read local SQL when the rollup is complete and transparently fall back to paging Shopify when it is not.
Map sync
A separate job geocodes company locations into Shopify metaobjects for the storefront store locator, using the metaobjects themselves as the geocode cache.
The core is a facts-plus-daily-rollup schema in Postgres: one row per order, one row per line, and pre-aggregated daily totals per company and per product, with money held as fixed-point numeric rather than floating point. Shopify populates it two ways — webhooks for live orders, and a resumable cron for history — and every write is expressed as a delta against current stored state, which is what makes replay safe. Reporting reads the rollup when it is complete and degrades to paging the Admin API when it is not, with every response labelling which source produced it so the interface can be honest about it. The frontend is deliberately unbuilt: four hand-written HTML pages with no framework and no build step, served with an ETag derived from file modification time, backed by a small client-side cache that canonicalises URLs so differently-ordered query parameters hit the same entry. Authorisation is three-layered, because Shopify's own model is insufficient here — OAuth proves which shop, a decoded App Bridge session token proves which user, and a per-rep PIN gates commission figures, since identity from the embedded app alone is self-asserted.
The non-obvious calls — where the straightforward approach fails, and what it cost to find that out.
Why it breaks
Floating point is not money. A float can represent 0.1 + 0.2 as 0.30000000000000004, and a commission report that is off by a cent is not a rounding error — it is a bug that a rep will notice and a bookkeeper will reject.
What I did
Money is stored as numeric(12,2) and arithmetic is done in SQL, not JavaScript. The type system and the database agree that a dollar is not a float.
Why it breaks
A backfill that starts at the beginning of time and runs until it finishes will be killed by the platform. A backfill that does not checkpoint will start over every time. And a backfill that does not pass an explicit date range will silently return only the last ~60 days, report success, and leave years of history missing.
What I did
The cron resumes from a versioned cursor, pages within an explicit date range, and checkpoints after every page. A failed run continues from where it left off rather than starting over, and a successful run is one that has actually seen every order in the window.
Why it breaks
A money-only refund does not change the order total and frequently does not fire an orders/updated webhook. Anything watching orders alone will pay commission on money the business already refunded.
What I did
A dedicated refunds/create webhook applies the refund as a delta against the stored order, so the ledger stays correct even when Shopify does not announce the change on the order itself.
Why it breaks
Shopify's embedded app session proves which shop and which user, but it does not prove that the user is allowed to see commission figures. Identity from the embedded app is self-asserted.
What I did
OAuth proves the shop, a decoded App Bridge session token proves the user, and a per-rep PIN gates the commission numbers. The PIN is hashed, attempt-counted, and locked out on failure.
What keeps it right once nobody is watching it run.
Every write is expressed as a delta against current stored state. Replaying any webhook is a no-op rather than a double count, so the system can retry aggressively without risk of inflating numbers.
Reporting reads the rollup when it is complete and degrades to paging the Admin API when it is not, with every response labelling which source produced it so the interface can be honest about it.
Per-rep PINs use scrypt with a random salt and are verified with a constant-time comparison behind a length check, with attempt counting and lockout. The one function permitted to read the raw hash is marked as internal-only, and the record serialiser exposes only whether a PIN exists.
Whether you want this exact architecture deployed to your store, or a completely bespoke solution built from scratch.
Skip the R&D. I can deploy the Wholesale CRM & Order Analytics architecture directly to your operations in days, not months.
Have a unique workflow? Let's build a bespoke app tailored perfectly to your complex business needs.