Build Metorik

KINDAreplaces $25/mosaves $300/yrback to the verdict

0%0 of 17 items done

Saved on this device only. Tick prerequisites first, then work the phases in order · do not start one until the checks above it pass.

A self-hosted WooCommerce reporting stack whose hard problems are a sync that can be interrupted without losing orders, revenue figures that survive refunds and cancelled orders, and a customer identity that exists for people who never made an account.

estimated effort weekendthe files for this build are in the project pack

RuntimeTypeScript on Node 22, Fastify bound to the loopback address, a small React dashboardWarehouseSQLite through better-sqlite3, with the raw API payload kept beside the normalised rowsSyncAn incremental worker with a watermark, an overlap window and upserts by idMoneyIntegers in the currency's minor unit, with the currency stored per orderDefinitionsEvery figure on screen carries the rule that produced it

Before step 1

Everything below is assumed from the first step. Tick each one when you actually have it, not when you plan to.

installfree
Why Runs the sync worker and serves the dashboard.
Get it nodejs.org, or your version manager. open ↗
Verify node --version prints v22 or higher
API keyfree
Why This tool reads and reports; a key that can write orders is a risk with no upside here.
Get it Create a key in the store's advanced settings with read permission only, over HTTPS.
Verify A read request succeeds and a write request is refused
decidefree
Why The API returns everything by default and every figure in this build depends on where you draw that line.
Get it List your store's statuses and mark each as revenue, not revenue, or excluded entirely.
Verify The list is in the definitions table and appears under every figure
decidefree
Why Daily figures move when this changes and everyone assumes the answer is obvious until two people compare screens.
Get it Pick the store's zone or yours, write it down, and print it beside every date range.
Verify The zone appears in the definitions table and in the interface
have readyfree
Why Those three rows are where every reporting bug in this category lives, and you need them in the test suite rather than in production.
Get it Export a handful of real orders, strip the personal fields, commit them as fixtures.
Verify The suite fails if refund netting, guest identity or status filtering regresses

Data model

Create these before the first phase that stores anything. Changing a table later is the expensive kind of change.

- sync_state(resource, watermark_utc, last_cursor, last_run_at, last_error, pages_done) · one row per resource, so a crash mid-sync resumes rather than restarts
- orders(id, number, status, currency, date_created_utc, date_modified_utc, customer_id, email_key, total_minor, tax_minor, shipping_minor, discount_minor, raw_json)
- order_items(id, order_id, product_id, variation_id, name, quantity, subtotal_minor, total_minor, tax_minor)
- refunds(id, order_id, amount_minor, reason, date_created_utc) · stored as a positive amount with the sign applied at report time, never folded into the order total
- identities(key, kind, first_order_at, last_order_at, order_count) · kind is account or email, because an order placed without an account carries a customer id of zero
- products(id, parent_id, name, sku, type) and variations under the same table with parent_id set
- definitions(metric, statuses_included, nets_refunds, day_boundary_zone, updated_at) · the rules behind each figure, stored rather than remembered

Environment variables

These go in a .env file the app reads at startup. The pack's .env.example is this table as a file · copy it, never commit the filled-in version.

Variable	Needed	Example	Where the value comes from
`STORE_BASE_URL`	required	`https://shop.example.com`	Over HTTPS only. The key pair travels on every request.
`STORE_CONSUMER_KEY`secret	required	`ck_...redacted`	Read-only. The app checks this at startup.
`STORE_CONSUMER_SECRET`secret	required	`cs_...redacted`	Pairs with the key. Never in the repository.
`DATABASE_PATH`	required	`./data/warehouse.db`	The warehouse including raw payloads. This gets large; plan for it.
`REPORT_TIMEZONE`	required	`Europe/London`	Defines a day in every report. Printed beside every date range.
`REVENUE_STATUSES`	required	`completed,processing`	The statuses that count as revenue. There is no safe default here.
`SYNC_INTERVAL_MINUTES`	required	`30`	How often the worker runs. Smaller is not better on a busy shop.
`SYNC_OVERLAP_MINUTES`	required	`15`	How far back each run re-requests, so boundary rows are seen twice rather than missed.

The build, in order

1
A sync you can interrupt
Pulling the store's orders can be killed at any point and resumes without losing or duplicating a row.
1. The set moves while you read it.
  Files src/sync/orders.tstests/pagination.test.ts
2. Seeing a boundary row twice is cheap; missing it is not.
  Files src/sync/state.ts
3. Every argument about a number ends here.
  Files src/db/migrations/001_orders.sql
done when · tick each as it passes
watch out
- Paging by page number over a set ordered by last-modified date means the store keeps modifying orders while you read, rows shift between pages under you, and you silently skip some entirely, which shows up months later as a revenue figure that is a few percent low for one week. Order by a stable key, upsert rather than insert, and re-request an overlap window each run so a boundary row is seen twice rather than never.
next phase
2
Money you can actually add up
Revenue excludes the orders that were never revenue and nets the refunds that were given back.
1. One place to get this wrong instead of thirty.
  Files src/money/parse.tstests/money.test.ts
2. The default includes things that are not sales.
  Files src/reports/definitions.ts
3. The order total does not know it was refunded.
  Files src/db/migrations/002_refunds.sqltests/refunds.test.ts
done when · tick each as it passes
watch out
- The orders endpoint's status filter defaults to any and its accepted values include trash, so a naive sync pulls pending, failed, cancelled and binned orders and a naive revenue query adds them all up; worse, refunds arrive as a separate array on the order whose totals are strings with a leading minus while the order's own total is left untouched, so the same query counts fully refunded orders at full value. Pick your statuses explicitly, store refunds as rows, and make the dashboard show gross and net side by side.
next phase
3
Reports that say what they mean
Revenue, order count, average order value and product breakdowns, each displayed with the rule that produced it.
1. The definition is the disagreement, so put it on screen.
  Files src/web/Definition.tsxsrc/reports/definitions.ts
2. A day is not a fact without a place.
  Files src/reports/buckets.tstests/timezone.test.ts
3. A dashboard that calls the shop on render is a dashboard that takes it down.
  Files src/reports/query.ts
done when · tick each as it passes
watch out
- Bucketing orders into days using the store's local created date while your dashboard runs in a different timezone moves the late-evening orders into the wrong day, and the totals still add up for the month so nobody notices until somebody compares a single day against the shop's own screen. Pick one zone, store both the UTC instant and the zone you bucket in, and print the zone beside every date range.
next phase
4
Customers who never made an account
Cohorts and repeat-purchase rates include the people who checked out as guests, which in most shops is most of them.
1. Otherwise every guest in the shop is one customer.
  Files src/identity/key.tstests/identity.test.ts
2. Case and whitespace will split one customer into three.
  Files src/identity/normalise.ts
3. So you know how much of the chart is an inference.
  Files src/web/Cohorts.tsx
done when · tick each as it passes
watch out
- Grouping customers by the customer id the API returns drops every guest checkout into one enormous customer whose id is zero, and since guest orders are the majority in many shops your cohort chart is then a picture of your minority of registered users with one absurd outlier. Key identity on the account id when it exists and on a normalised email otherwise, and show how many identities came from each.
5
Rebuild it, export it, and say what it is notproduct builder
The warehouse can be thrown away and rebuilt, everything exports, and the credentials in use are read-only.
1. A parsing fix should be a command, not an evening.
  Files src/cli/rebuild.tstests/rebuild.test.ts
2. A reporting tool has no business being able to change an order.
  Files src/startup/verify-key.ts
3. The reports are the easy half and the user deserves to know.
  Files src/web/About.tsx
done when · tick each as it passes
watch out
- Keeping only the normalised rows means the first time you find a parsing bug you have to re-pull the entire store history to fix it, and on a shop with years of orders that is hours of paging plus whatever the rate limiter thinks of you. Keep the raw payloads, rebuild from them, and treat a full re-pull as a last resort rather than the recovery plan.

what this build does not replace

Abandoned cart tracking.
Any email sending at all.
Segment-driven automations.
Multi-store aggregation.
Abandoned cart tracking and cart recovery emails, which need storefront instrumentation plus real email sending
Automated email flows tied to customer segments, with templates, throttling and unsubscribe handling
Battle-tested incremental sync for stores with hundreds of thousands of orders
Subscriptions, refunds and tax edge case handling that Metorik has already hit and fixed
Mobile-friendly polish, live order feed and the everything-in-one-place feel

after v1, if you want it

A product-level margin view, once you have a cost price source that is not a spreadsheet somebody edits
A daily snapshot table so a figure quoted last Tuesday can still be reproduced after a definition changes
A tax breakdown, which is the next place where the raw payloads earn their storage

Need the files? The project pack on the verdict page hands your agent the whole brief · more analytics.