Overview
API Analytics Hub pulls order data from a Shopify store through the Admin GraphQL API, stores it as dated snapshots in Postgres, and shows it in a dashboard. The dashboard never calls Shopify to render a page. It reads what the sync wrote.
It runs against a real Shopify development store that I seeded with test orders, so every figure came out of the real API. Nobody uses it, and it has one connector. I deployed a demo on free hosting, but the free Postgres instance expired on 2026-09-19, so there is no live link. The repo has the code, the migrations and the tests.
What it does
- Syncs from Shopify and paces from the response. Shopify limits GraphQL by query cost against a leaky bucket, and every response reports the bucket's size, what is available and the restore rate. The client reads those numbers each time and keeps a third of the bucket in reserve.
- Keeps the tenant boundary in the database. Row-level security is enabled and forced on every tenant table, and the policies read the tenant from a transaction-local setting. The API never relies on a
WHERE tenant_idclause for isolation, so a forgotten clause cannot leak a row. - Stores history as snapshots. One dated row per connection, metric and day, never overwritten, so a re-sync corrects a day instead of duplicating it. Money is an integer in minor units with its currency beside it, enforced by a database check.
- Encrypts stored credentials. AES-256-GCM, with the tenant, connection and platform bound into the ciphertext so a blob copied to another row cannot be decrypted.
- Leases its syncs. A scheduled run and a manual Sync Now cannot overlap, and a worker that dies silently shows up as a failed run instead of a row stuck on "running".
What I measured
The live store disagreed with my notes in several places. Its query-cost bucket held 20,000 points restoring at 1,000 a second, where I had written 1,000 and 50. A connector with the standard numbers hardcoded would have throttled itself far harder than needed and nothing would have looked broken. Reading the limits from each response made that a non-event.
I also recomputed the numbers independently. The sync read 21 orders over five days. A separate script with no shared code summed the same window straight from a different query, and orders, gross revenue and distinct buyers matched on every day. A connector agreeing with itself proves nothing, so that check goes through a second path.
What broke
A cross-tenant leak in my own tests. The first run of the isolation suite showed tenant A reading tenant B's rows, [111, 222] where it should have returned [111]. Row-level security was enabled and forced and every policy was correct. The tests had connected as the bootstrap database user, which Postgres creates as a superuser, and a superuser bypasses row-level security completely. Forcing the policy closes the table-owner gap, not the superuser one. I fixed it by creating a non-superuser request role inside the migration, and added a test that asserts that role is not a superuser, does not hold the bypass privilege and does not own the tables. The connection pool also refuses to start if its role could bypass security.
Tests that proved nothing. The mutation harness deletes each guard and checks that the suite goes red. It found that my rate-limit reserve test used a bucket too small to cover the request, so the code waited whether or not a reserve existed. It also found a test that measured a constant using that same constant, so changing the constant moved the test with it. Another mutation made the suite hang instead of fail, and the harness now records a hang as its own outcome. The harness in the repo breaks 28 named guards.
A plausible wrong number. The dashboard card for customers summed daily distinct buyers and showed 9. The true distinct count over the same window was 5, because the same people ordered on more than one day. Every daily figure was right and the total looked fine. A distinct count cannot be added up across days, so the card now shows buyers per day with that caveat printed under it.
Two different "access denied" errors. With the order and customer scopes granted at install, Shopify still refused the Order object, because protected customer data is declared by the developer per app and is separate from merchant-granted scopes. The remedies differ, so the connector raises a distinct error for it and degrades. It stops reporting the customers metric entirely instead of writing a zero, because a zero in a dated snapshot makes a permissions problem look like a business result.
What it does not do yet
There is one connector, and the abstraction is only proven once a second platform fits without Shopify's field names leaking in. The scheduler is a runner with no clock, so something still has to call it. Keys sit in an environment variable, there is no alerting, and backfill is manual.
Tech Stack
Backend: Python, FastAPI, PostgreSQL with row-level security Integration: Shopify Admin GraphQL, OAuth with offline tokens, signed webhooks Frontend: Next.js, TypeScript Testing: pytest against a real Postgres in Docker, plus a mutation harness Infrastructure: Docker Compose, AES-256-GCM credential encryption