Vibecode Metorik
track this build4 phases, 12 steps, beginner friendly0%KINDA · weekend project
The good news: every number Metorik shows you is already sitting in your own WooCommerce database, so nobody is gatekeeping your data. An agent can pull orders, customers and products through the Woo REST API into a local store and give you revenue charts, cohort and repeat-purchase views, product breakdowns and CSV exports in a weekend. The gap is everything that is not a chart: abandoned cart tracking needs a script on your storefront plus a capture endpoint, email automations need a sending domain and deliverability you do not have, and segment-driven campaigns need a rules engine that is a project of its own. Large stores also punish naive syncs, Metorik has spent years making that part boring. Build the dashboard, keep paying for the email side if that is why you signed up.
the project pack5 files · written for this build, step by step
You are building a lean indie version of Metorik. Create the following project files first, then implement the application by following them. Keep the files updated as decisions change. Do not collapse this into a single README or prompt. ===== README.md ===== # Metorik · indie build 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: **weekend**. Work `BUILD_PLAN.md` top to bottom · every phase ends in a check that has to pass before the next one starts. ## Stack | Part | Choice | Why | | --- | --- | --- | | Runtime | TypeScript on Node 22, Fastify bound to the loopback address, a small React dashboard | the store data is the whole point and none of it should leave the machine | | Warehouse | SQLite through better-sqlite3, with the raw API payload kept beside the normalised rows | when a figure looks wrong you need the original response, not your interpretation of it | | Sync | An incremental worker with a watermark, an overlap window and upserts by id | stores keep changing rows while you page through them | | Money | Integers in the currency's minor unit, with the currency stored per order | a report built on floating point sums is a report that disagrees with the shop | | Definitions | Every figure on screen carries the rule that produced it | the argument about revenue is always about which orders counted | ## Before you start Have every one of these ready. The plan assumes them from step one. - [ ] **Node.js 22 or newer** · free - Why: Runs the sync worker and serves the dashboard. - Get it: nodejs.org, or your version manager. - Verify: node --version prints v22 or higher - [ ] **A read-only REST API key pair for the store** · free - 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 - [ ] **Which order statuses count as revenue, written down before you build a chart** · free - 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 - [ ] **The timezone that defines a day in your reports** · free - 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 - [ ] **An anonymised order fixture including a refund, a guest order and a cancelled order** · free - 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 ## Quick start Follow Phase 1 in `BUILD_PLAN.md`; it creates the project. Then copy `.env.example` to `.env` and fill in the values it documents. ## Honest limits This build deliberately 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 If one of those is essential to you, that is the reason to keep paying for Metorik, and the README should say so rather than pretend. ===== BRIEF.md ===== # Build brief · Metorik The one-shot brief this plan expands. `BUILD_PLAN.md` (or `MILESTONES.md`) is the same sequence broken into steps and checks; where the two disagree, the plan wins. Build me a local warehouse and dashboard over my own store's order data that gets the money right to replace Metorik. Build it in phases, in the order below. Do not write the whole thing in one pass. Finish a phase, run its "Done when" check, fix what fails, and only then start the next phase. ### Stack (fixed, do not substitute) - Runtime: TypeScript on Node 22, Fastify bound to the loopback address, a small React dashboard · the store data is the whole point and none of it should leave the machine - Warehouse: SQLite through better-sqlite3, with the raw API payload kept beside the normalised rows · when a figure looks wrong you need the original response, not your interpretation of it - Sync: An incremental worker with a watermark, an overlap window and upserts by id · stores keep changing rows while you page through them - Money: Integers in the currency's minor unit, with the currency stored per order · a report built on floating point sums is a report that disagrees with the shop - Definitions: Every figure on screen carries the rule that produced it · the argument about revenue is always about which orders counted ### Data model (create this before Phase 1) - 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 ### Phase 1 · A sync you can interrupt Build: Page the orders, customers and products endpoints at the maximum page size the API allows, order by id rather than by date, and upsert by id. Keep a watermark per resource and re-request a small overlap on the next run. Back off on rate limits and server errors, and print a running count so an overnight first sync is checkable in the morning. Trap: 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. Done when: Killing the sync halfway and restarting produces the same row count as an uninterrupted run, tested against a seeded fixture. Running the sync twice in a row inserts no duplicate orders, asserted by a unique constraint and a test. Rate limit and server error responses back off rather than spinning, verified with a stubbed endpoint. The raw payload is stored for every order alongside the normalised row. Do not build yet: No reports, no money rules, no cohorts and no export yet. ### Phase 2 · Money you can actually add up Build: Convert every monetary string to an integer in the currency's minor unit on the way in, keeping the currency on the order. Decide and record which statuses count as revenue. Store refunds as their own rows and subtract them at report time. Show a separate gross and net figure rather than one number that quietly means one of them. Trap: 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. Done when: No monetary value is stored or computed as a floating point number, verified by review and by a test on the schema. A fully refunded order contributes its full value to gross and nothing to net, tested with a fixture. Orders in statuses outside the recorded revenue set appear in a count but never in a revenue figure, tested. An order in a currency other than the store's default is either converted with a recorded rate and date or excluded with a visible note, never silently added. Do not build yet: No cohorts, no export and no rebuild tooling yet. ### Phase 3 · Reports that say what they mean Build: An overview with a date range and a previous-period comparison, a products view by units and revenue, and a per-order detail. Every figure has a small note naming the statuses included, whether refunds are netted and which timezone defines a day. Compute from the warehouse only. Trap: 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. Done when: Every figure on the overview has a definition note naming its statuses, refund treatment and day boundary zone, checked by review. Changing the day boundary zone changes the daily series and not the monthly total, tested at a month boundary. The previous-period comparison uses the same rules as the current period, tested with a fixture spanning both. No report issues a request to the store; they all read the warehouse. Do not build yet: No cohorts, no repeat-purchase analysis and no export yet. ### Phase 4 · Customers who never made an account Build: Build an identities table keyed on the account id where there is one and on a normalised billing email otherwise. Compute first-purchase cohorts and repeat rates over that table. Show the split between account and guest identities so you can see how much of the analysis rests on email. Trap: 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. Done when: An order with a customer id of zero creates or joins an identity keyed on its normalised email, tested with a fixture containing both kinds. The cohort view reports the count of account-based and email-based identities separately. Normalisation is case-insensitive and trims whitespace, tested with a pair of orders differing only in case. A repeat-purchase rate is computed from identities rather than from order rows, asserted in the suite. Do not build yet: No export, no rebuild and no retention tooling yet. ### Phase 5 · Rebuild it, export it, and say what it is not Build: A rebuild command that drops the normalised tables, replays from the stored raw payloads and reports any row that no longer parses. CSV export for every table over a date range. Use a read-only API key and refuse to start with one that can write. State plainly in the interface which parts of the paid product this does not replace. Trap: 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. Done when: A rebuild from raw payloads reproduces identical figures to the live warehouse, tested by comparing totals before and after. The application refuses to start with an API key that can write, verified by attempting a write during startup and treating success as a fatal error. Export produces CSV for orders, items, refunds and identities and reimports into an empty database with matching totals. The dashboard states, on a page a user will actually see, that it does not do abandoned cart tracking or send any email. Do not build yet: Nothing after this. ### Out of scope (and why) - Abandoned cart tracking, which needs a script on the storefront and a capture endpoint, and is a different project with different privacy consequences. - Sending any email at all, because a sending domain with a reputation you have to maintain is the actual subscription and there is no weekend version of it. - Segment-driven automations, which are a rules engine plus a scheduler plus a sending stack you just decided not to build. - Multi-store aggregation, where currency, tax and status conventions stop agreeing and every figure needs a per-store rule. ### README must contain - Which order statuses count as revenue in this build, and that the API's own default returns everything including binned orders - That refunds are stored as separate rows and that gross and net are different figures shown side by side - Which timezone defines a day in the reports, and that changing it moves daily figures - That guest checkouts are keyed on a normalised email and what proportion of your identities that represents - That this replaces the reporting half only, with abandoned carts and email automation named as absent ===== AGENTS.md ===== # Agent instructions · Metorik indie build - Read `README.md` and `BUILD_PLAN.md` before writing code. The stack is fixed: TypeScript on Node 22, Fastify bound to the loopback address, a small React dashboard, SQLite through better-sqlite3, with the raw API payload kept beside the normalised rows, An incremental worker with a watermark, an overlap window and upserts by id, Integers in the currency's minor unit, with the currency stored per order, Every figure on screen carries the rule that produced it. Do not substitute. - Work one phase at a time, in order. Do not start a phase until every "Done when" item of the previous one passes. - Prefer the fewest moving parts that satisfy the step. No frameworks, services or dependencies the plan does not name. - Secrets live in `.env`, never in source or logs. Keep `.env.example` current when a variable is introduced. - Do not invent cryptography, security guarantees, APIs or compliance claims. - Add a focused test for every destructive, security-sensitive or data-loss path the plan names. - Run the project checks before declaring a phase complete, and record any deliberate shortcut in the README under "Tradeoffs". ## Known traps - 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. - 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. - 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. - 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. ===== BUILD_PLAN.md ===== # Build plan · Metorik 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. Phases are in dependency order. Each ends in a "Done when" list; treat an unticked item as a blocker, not a note. ## Phase 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. ### Steps 1. Page by a stable key, not by page number The set moves while you read it. Files: `src/sync/orders.ts`, `tests/pagination.test.ts` 2. Keep a watermark with an overlap window Seeing a boundary row twice is cheap; missing it is not. Files: `src/sync/state.ts` 3. Store the raw payload next to the normalised row Every argument about a number ends here. Files: `src/db/migrations/001_orders.sql` ### Done when - [ ] An interrupted sync resumes to the same row count - [ ] A repeated sync creates no duplicates - [ ] Rate limits and server errors back off - [ ] Raw payloads are stored per order ### 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. ## Phase 2 · Money you can actually add up Revenue excludes the orders that were never revenue and nets the refunds that were given back. ### Steps 1. Parse money into minor units at the boundary One place to get this wrong instead of thirty. Files: `src/money/parse.ts`, `tests/money.test.ts` 2. Choose your revenue statuses and write them down The default includes things that are not sales. Files: `src/reports/definitions.ts` 3. Keep refunds as rows and net them at report time The order total does not know it was refunded. Files: `src/db/migrations/002_refunds.sql`, `tests/refunds.test.ts` ### Done when - [ ] No floating point money anywhere - [ ] A refunded order is full gross and zero net - [ ] Non-revenue statuses never reach a revenue figure - [ ] Foreign currency orders are converted or excluded visibly ### 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. ## 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. ### Steps 1. Attach a definition to every figure The definition is the disagreement, so put it on screen. Files: `src/web/Definition.tsx`, `src/reports/definitions.ts` 2. Store the instant and the bucketing zone A day is not a fact without a place. Files: `src/reports/buckets.ts`, `tests/timezone.test.ts` 3. Read the warehouse, never the store A dashboard that calls the shop on render is a dashboard that takes it down. Files: `src/reports/query.ts` ### Done when - [ ] Every figure shows its definition - [ ] Changing the bucketing zone moves days, not months - [ ] Comparison periods use identical rules - [ ] No report makes a network call to the store ### 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. ## 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. ### Steps 1. Key identity on the account id, then on the email Otherwise every guest in the shop is one customer. Files: `src/identity/key.ts`, `tests/identity.test.ts` 2. Normalise the email before keying on it Case and whitespace will split one customer into three. Files: `src/identity/normalise.ts` 3. Show the account and guest split on the cohort view So you know how much of the chart is an inference. Files: `src/web/Cohorts.tsx` ### Done when - [ ] Guest orders create email-keyed identities - [ ] Account and guest identity counts are shown - [ ] Email normalisation is case and whitespace safe - [ ] Repeat rates come from identities, not orders ### 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. ## Not in this build - Abandoned cart tracking. - Any email sending at all. - Segment-driven automations. - Multi-store aggregation. ## 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 ===== .env.example ===== # Copy to .env and fill in. Never commit .env; this file documents it. # Required. Over HTTPS only. The key pair travels on every request. STORE_BASE_URL=https://shop.example.com # Required · secret. Read-only. The app checks this at startup. STORE_CONSUMER_KEY=ck_...redacted # Required · secret. Pairs with the key. Never in the repository. STORE_CONSUMER_SECRET=cs_...redacted # Required. The warehouse including raw payloads. This gets large; plan for it. DATABASE_PATH=./data/warehouse.db # Required. Defines a day in every report. Printed beside every date range. REPORT_TIMEZONE=Europe/London # Required. The statuses that count as revenue. There is no safe default here. REVENUE_STATUSES=completed,processing # Required. How often the worker runs. Smaller is not better on a busy shop. SYNC_INTERVAL_MINUTES=30 # Required. How far back each run re-requests, so boundary rows are seen twice rather than missed. SYNC_OVERLAP_MINUTES=15
You are building a lean indie version of Metorik. Create the following project files first, then implement the application by following them. Keep the files updated as decisions change. Do not collapse this into a single README or prompt. ===== README.md ===== # Metorik · indie build 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: **weekend**. Work `BUILD_PLAN.md` top to bottom · every phase ends in a check that has to pass before the next one starts. ## Stack | Part | Choice | Why | | --- | --- | --- | | Runtime | TypeScript on Node 22, Fastify bound to the loopback address, a small React dashboard | the store data is the whole point and none of it should leave the machine | | Warehouse | SQLite through better-sqlite3, with the raw API payload kept beside the normalised rows | when a figure looks wrong you need the original response, not your interpretation of it | | Sync | An incremental worker with a watermark, an overlap window and upserts by id | stores keep changing rows while you page through them | | Money | Integers in the currency's minor unit, with the currency stored per order | a report built on floating point sums is a report that disagrees with the shop | | Definitions | Every figure on screen carries the rule that produced it | the argument about revenue is always about which orders counted | ## Before you start Have every one of these ready. The plan assumes them from step one. - [ ] **Node.js 22 or newer** · free - Why: Runs the sync worker and serves the dashboard. - Get it: nodejs.org, or your version manager. - Verify: node --version prints v22 or higher - [ ] **A read-only REST API key pair for the store** · free - 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 - [ ] **Which order statuses count as revenue, written down before you build a chart** · free - 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 - [ ] **The timezone that defines a day in your reports** · free - 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 - [ ] **An anonymised order fixture including a refund, a guest order and a cancelled order** · free - 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 ## Quick start Follow Phase 1 in `BUILD_PLAN.md`; it creates the project. Then copy `.env.example` to `.env` and fill in the values it documents. ## Honest limits This build deliberately 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 If one of those is essential to you, that is the reason to keep paying for Metorik, and the README should say so rather than pretend. ===== BRIEF.md ===== # Build brief · Metorik The one-shot brief this plan expands. `BUILD_PLAN.md` (or `MILESTONES.md`) is the same sequence broken into steps and checks; where the two disagree, the plan wins. Build me a local warehouse and dashboard over my own store's order data that gets the money right to replace Metorik. Build it in phases, in the order below. Do not write the whole thing in one pass. Finish a phase, run its "Done when" check, fix what fails, and only then start the next phase. ### Stack (fixed, do not substitute) - Runtime: TypeScript on Node 22, Fastify bound to the loopback address, a small React dashboard · the store data is the whole point and none of it should leave the machine - Warehouse: SQLite through better-sqlite3, with the raw API payload kept beside the normalised rows · when a figure looks wrong you need the original response, not your interpretation of it - Sync: An incremental worker with a watermark, an overlap window and upserts by id · stores keep changing rows while you page through them - Money: Integers in the currency's minor unit, with the currency stored per order · a report built on floating point sums is a report that disagrees with the shop - Definitions: Every figure on screen carries the rule that produced it · the argument about revenue is always about which orders counted ### Data model (create this before Phase 1) - 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 ### Phase 1 · A sync you can interrupt Build: Page the orders, customers and products endpoints at the maximum page size the API allows, order by id rather than by date, and upsert by id. Keep a watermark per resource and re-request a small overlap on the next run. Back off on rate limits and server errors, and print a running count so an overnight first sync is checkable in the morning. Trap: 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. Done when: Killing the sync halfway and restarting produces the same row count as an uninterrupted run, tested against a seeded fixture. Running the sync twice in a row inserts no duplicate orders, asserted by a unique constraint and a test. Rate limit and server error responses back off rather than spinning, verified with a stubbed endpoint. The raw payload is stored for every order alongside the normalised row. Do not build yet: No reports, no money rules, no cohorts and no export yet. ### Phase 2 · Money you can actually add up Build: Convert every monetary string to an integer in the currency's minor unit on the way in, keeping the currency on the order. Decide and record which statuses count as revenue. Store refunds as their own rows and subtract them at report time. Show a separate gross and net figure rather than one number that quietly means one of them. Trap: 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. Done when: No monetary value is stored or computed as a floating point number, verified by review and by a test on the schema. A fully refunded order contributes its full value to gross and nothing to net, tested with a fixture. Orders in statuses outside the recorded revenue set appear in a count but never in a revenue figure, tested. An order in a currency other than the store's default is either converted with a recorded rate and date or excluded with a visible note, never silently added. Do not build yet: No cohorts, no export and no rebuild tooling yet. ### Phase 3 · Reports that say what they mean Build: An overview with a date range and a previous-period comparison, a products view by units and revenue, and a per-order detail. Every figure has a small note naming the statuses included, whether refunds are netted and which timezone defines a day. Compute from the warehouse only. Trap: 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. Done when: Every figure on the overview has a definition note naming its statuses, refund treatment and day boundary zone, checked by review. Changing the day boundary zone changes the daily series and not the monthly total, tested at a month boundary. The previous-period comparison uses the same rules as the current period, tested with a fixture spanning both. No report issues a request to the store; they all read the warehouse. Do not build yet: No cohorts, no repeat-purchase analysis and no export yet. ### Phase 4 · Customers who never made an account Build: Build an identities table keyed on the account id where there is one and on a normalised billing email otherwise. Compute first-purchase cohorts and repeat rates over that table. Show the split between account and guest identities so you can see how much of the analysis rests on email. Trap: 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. Done when: An order with a customer id of zero creates or joins an identity keyed on its normalised email, tested with a fixture containing both kinds. The cohort view reports the count of account-based and email-based identities separately. Normalisation is case-insensitive and trims whitespace, tested with a pair of orders differing only in case. A repeat-purchase rate is computed from identities rather than from order rows, asserted in the suite. Do not build yet: No export, no rebuild and no retention tooling yet. ### Phase 5 · Rebuild it, export it, and say what it is not Build: A rebuild command that drops the normalised tables, replays from the stored raw payloads and reports any row that no longer parses. CSV export for every table over a date range. Use a read-only API key and refuse to start with one that can write. State plainly in the interface which parts of the paid product this does not replace. Trap: 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. Done when: A rebuild from raw payloads reproduces identical figures to the live warehouse, tested by comparing totals before and after. The application refuses to start with an API key that can write, verified by attempting a write during startup and treating success as a fatal error. Export produces CSV for orders, items, refunds and identities and reimports into an empty database with matching totals. The dashboard states, on a page a user will actually see, that it does not do abandoned cart tracking or send any email. Do not build yet: Nothing after this. ### Out of scope (and why) - Abandoned cart tracking, which needs a script on the storefront and a capture endpoint, and is a different project with different privacy consequences. - Sending any email at all, because a sending domain with a reputation you have to maintain is the actual subscription and there is no weekend version of it. - Segment-driven automations, which are a rules engine plus a scheduler plus a sending stack you just decided not to build. - Multi-store aggregation, where currency, tax and status conventions stop agreeing and every figure needs a per-store rule. ### README must contain - Which order statuses count as revenue in this build, and that the API's own default returns everything including binned orders - That refunds are stored as separate rows and that gross and net are different figures shown side by side - Which timezone defines a day in the reports, and that changing it moves daily figures - That guest checkouts are keyed on a normalised email and what proportion of your identities that represents - That this replaces the reporting half only, with abandoned carts and email automation named as absent ===== AGENTS.md ===== # Agent instructions · Metorik indie build - Read `README.md` and `BUILD_PLAN.md` before writing code. The stack is fixed: TypeScript on Node 22, Fastify bound to the loopback address, a small React dashboard, SQLite through better-sqlite3, with the raw API payload kept beside the normalised rows, An incremental worker with a watermark, an overlap window and upserts by id, Integers in the currency's minor unit, with the currency stored per order, Every figure on screen carries the rule that produced it. Do not substitute. - Work one phase at a time, in order. Do not start a phase until every "Done when" item of the previous one passes. - Prefer the fewest moving parts that satisfy the step. No frameworks, services or dependencies the plan does not name. - Secrets live in `.env`, never in source or logs. Keep `.env.example` current when a variable is introduced. - Do not invent cryptography, security guarantees, APIs or compliance claims. - Add a focused test for every destructive, security-sensitive or data-loss path the plan names. - Run the project checks before declaring a phase complete, and record any deliberate shortcut in the README under "Tradeoffs". ## Known traps - 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. - 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. - 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. - 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. ===== BUILD_PLAN.md ===== # Build plan · Metorik 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. Phases are in dependency order. Each ends in a "Done when" list; treat an unticked item as a blocker, not a note. ## Phase 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. ### Steps 1. Page by a stable key, not by page number The set moves while you read it. Files: `src/sync/orders.ts`, `tests/pagination.test.ts` 2. Keep a watermark with an overlap window Seeing a boundary row twice is cheap; missing it is not. Files: `src/sync/state.ts` 3. Store the raw payload next to the normalised row Every argument about a number ends here. Files: `src/db/migrations/001_orders.sql` ### Done when - [ ] An interrupted sync resumes to the same row count - [ ] A repeated sync creates no duplicates - [ ] Rate limits and server errors back off - [ ] Raw payloads are stored per order ### 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. ## Phase 2 · Money you can actually add up Revenue excludes the orders that were never revenue and nets the refunds that were given back. ### Steps 1. Parse money into minor units at the boundary One place to get this wrong instead of thirty. Files: `src/money/parse.ts`, `tests/money.test.ts` 2. Choose your revenue statuses and write them down The default includes things that are not sales. Files: `src/reports/definitions.ts` 3. Keep refunds as rows and net them at report time The order total does not know it was refunded. Files: `src/db/migrations/002_refunds.sql`, `tests/refunds.test.ts` ### Done when - [ ] No floating point money anywhere - [ ] A refunded order is full gross and zero net - [ ] Non-revenue statuses never reach a revenue figure - [ ] Foreign currency orders are converted or excluded visibly ### 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. ## 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. ### Steps 1. Attach a definition to every figure The definition is the disagreement, so put it on screen. Files: `src/web/Definition.tsx`, `src/reports/definitions.ts` 2. Store the instant and the bucketing zone A day is not a fact without a place. Files: `src/reports/buckets.ts`, `tests/timezone.test.ts` 3. Read the warehouse, never the store A dashboard that calls the shop on render is a dashboard that takes it down. Files: `src/reports/query.ts` ### Done when - [ ] Every figure shows its definition - [ ] Changing the bucketing zone moves days, not months - [ ] Comparison periods use identical rules - [ ] No report makes a network call to the store ### 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. ## 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. ### Steps 1. Key identity on the account id, then on the email Otherwise every guest in the shop is one customer. Files: `src/identity/key.ts`, `tests/identity.test.ts` 2. Normalise the email before keying on it Case and whitespace will split one customer into three. Files: `src/identity/normalise.ts` 3. Show the account and guest split on the cohort view So you know how much of the chart is an inference. Files: `src/web/Cohorts.tsx` ### Done when - [ ] Guest orders create email-keyed identities - [ ] Account and guest identity counts are shown - [ ] Email normalisation is case and whitespace safe - [ ] Repeat rates come from identities, not orders ### 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. ## Not in this build - Abandoned cart tracking. - Any email sending at all. - Segment-driven automations. - Multi-store aggregation. ## 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 ===== .env.example ===== # Copy to .env and fill in. Never commit .env; this file documents it. # Required. Over HTTPS only. The key pair travels on every request. STORE_BASE_URL=https://shop.example.com # Required · secret. Read-only. The app checks this at startup. STORE_CONSUMER_KEY=ck_...redacted # Required · secret. Pairs with the key. Never in the repository. STORE_CONSUMER_SECRET=cs_...redacted # Required. The warehouse including raw payloads. This gets large; plan for it. DATABASE_PATH=./data/warehouse.db # Required. Defines a day in every report. Printed beside every date range. REPORT_TIMEZONE=Europe/London # Required. The statuses that count as revenue. There is no safe default here. REVENUE_STATUSES=completed,processing # Required. How often the worker runs. Smaller is not better on a busy shop. SYNC_INTERVAL_MINUTES=30 # Required. How far back each run re-requests, so boundary rows are seen twice rather than missed. SYNC_OVERLAP_MINUTES=15
You are building a production product version of Metorik. Create the following project files first, then implement the application by following them. Keep the files updated as decisions change. Do not collapse this into a single README or prompt. ===== PRODUCT.md ===== # Metorik · product brief ## Problem The good news: every number Metorik shows you is already sitting in your own WooCommerce database, so nobody is gatekeeping your data. An agent can pull orders, customers and products through the Woo REST API into a local store and give you revenue charts, cohort and repeat-purchase views, product breakdowns and CSV exports in a weekend. The gap is everything that is not a chart: abandoned cart tracking needs a script on your storefront plus a capture endpoint, email automations need a sending domain and deliverability you do not have, and segment-driven campaigns need a rules engine that is a project of its own. Large stores also punish naive syncs, Metorik has spent years making that part boring. Build the dashboard, keep paying for the email side if that is why you signed up. ## Product outcome A warehouse you can interrupt, rebuild and export, with revenue figures that exclude what was never a sale, net what was given back, and include the customers who never registered. ## Target user A builder who needs a maintainable product foundation, not a one-off demo. ## Required capabilities - A WooCommerce store with REST API consumer key and secret - Somewhere to run a small Node process and a database (a cheap VPS or your own machine) - Read access to the store over HTTPS - Optional: ability to add one script tag to the storefront if you want cart tracking ## Explicit non-goals for v1 - 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 ## Success criteria - An interrupted sync is confirmed to resume without loss or duplication - A fully refunded order is confirmed to contribute zero net revenue - Non-revenue statuses are confirmed absent from revenue figures - Guest orders are confirmed to form their own identities - A writable API key is confirmed to prevent startup ===== BRIEF.md ===== # Build brief · Metorik The one-shot brief this plan expands. `BUILD_PLAN.md` (or `MILESTONES.md`) is the same sequence broken into steps and checks; where the two disagree, the plan wins. Build me a local warehouse and dashboard over my own store's order data that gets the money right to replace Metorik. Build it in phases, in the order below. Do not write the whole thing in one pass. Finish a phase, run its "Done when" check, fix what fails, and only then start the next phase. ### Stack (fixed, do not substitute) - Runtime: TypeScript on Node 22, Fastify bound to the loopback address, a small React dashboard · the store data is the whole point and none of it should leave the machine - Warehouse: SQLite through better-sqlite3, with the raw API payload kept beside the normalised rows · when a figure looks wrong you need the original response, not your interpretation of it - Sync: An incremental worker with a watermark, an overlap window and upserts by id · stores keep changing rows while you page through them - Money: Integers in the currency's minor unit, with the currency stored per order · a report built on floating point sums is a report that disagrees with the shop - Definitions: Every figure on screen carries the rule that produced it · the argument about revenue is always about which orders counted ### Data model (create this before Phase 1) - 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 ### Phase 1 · A sync you can interrupt Build: Page the orders, customers and products endpoints at the maximum page size the API allows, order by id rather than by date, and upsert by id. Keep a watermark per resource and re-request a small overlap on the next run. Back off on rate limits and server errors, and print a running count so an overnight first sync is checkable in the morning. Trap: 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. Done when: Killing the sync halfway and restarting produces the same row count as an uninterrupted run, tested against a seeded fixture. Running the sync twice in a row inserts no duplicate orders, asserted by a unique constraint and a test. Rate limit and server error responses back off rather than spinning, verified with a stubbed endpoint. The raw payload is stored for every order alongside the normalised row. Do not build yet: No reports, no money rules, no cohorts and no export yet. ### Phase 2 · Money you can actually add up Build: Convert every monetary string to an integer in the currency's minor unit on the way in, keeping the currency on the order. Decide and record which statuses count as revenue. Store refunds as their own rows and subtract them at report time. Show a separate gross and net figure rather than one number that quietly means one of them. Trap: 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. Done when: No monetary value is stored or computed as a floating point number, verified by review and by a test on the schema. A fully refunded order contributes its full value to gross and nothing to net, tested with a fixture. Orders in statuses outside the recorded revenue set appear in a count but never in a revenue figure, tested. An order in a currency other than the store's default is either converted with a recorded rate and date or excluded with a visible note, never silently added. Do not build yet: No cohorts, no export and no rebuild tooling yet. ### Phase 3 · Reports that say what they mean Build: An overview with a date range and a previous-period comparison, a products view by units and revenue, and a per-order detail. Every figure has a small note naming the statuses included, whether refunds are netted and which timezone defines a day. Compute from the warehouse only. Trap: 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. Done when: Every figure on the overview has a definition note naming its statuses, refund treatment and day boundary zone, checked by review. Changing the day boundary zone changes the daily series and not the monthly total, tested at a month boundary. The previous-period comparison uses the same rules as the current period, tested with a fixture spanning both. No report issues a request to the store; they all read the warehouse. Do not build yet: No cohorts, no repeat-purchase analysis and no export yet. ### Phase 4 · Customers who never made an account Build: Build an identities table keyed on the account id where there is one and on a normalised billing email otherwise. Compute first-purchase cohorts and repeat rates over that table. Show the split between account and guest identities so you can see how much of the analysis rests on email. Trap: 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. Done when: An order with a customer id of zero creates or joins an identity keyed on its normalised email, tested with a fixture containing both kinds. The cohort view reports the count of account-based and email-based identities separately. Normalisation is case-insensitive and trims whitespace, tested with a pair of orders differing only in case. A repeat-purchase rate is computed from identities rather than from order rows, asserted in the suite. Do not build yet: No export, no rebuild and no retention tooling yet. ### Phase 5 · Rebuild it, export it, and say what it is not Build: A rebuild command that drops the normalised tables, replays from the stored raw payloads and reports any row that no longer parses. CSV export for every table over a date range. Use a read-only API key and refuse to start with one that can write. State plainly in the interface which parts of the paid product this does not replace. Trap: 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. Done when: A rebuild from raw payloads reproduces identical figures to the live warehouse, tested by comparing totals before and after. The application refuses to start with an API key that can write, verified by attempting a write during startup and treating success as a fatal error. Export produces CSV for orders, items, refunds and identities and reimports into an empty database with matching totals. The dashboard states, on a page a user will actually see, that it does not do abandoned cart tracking or send any email. Do not build yet: Nothing after this. ### Out of scope (and why) - Abandoned cart tracking, which needs a script on the storefront and a capture endpoint, and is a different project with different privacy consequences. - Sending any email at all, because a sending domain with a reputation you have to maintain is the actual subscription and there is no weekend version of it. - Segment-driven automations, which are a rules engine plus a scheduler plus a sending stack you just decided not to build. - Multi-store aggregation, where currency, tax and status conventions stop agreeing and every figure needs a per-store rule. ### README must contain - Which order statuses count as revenue in this build, and that the API's own default returns everything including binned orders - That refunds are stored as separate rows and that gross and net are different figures shown side by side - Which timezone defines a day in the reports, and that changing it moves daily figures - That guest checkouts are keyed on a normalised email and what proportion of your identities that represents - That this replaces the reporting half only, with abandoned carts and email automation named as absent ===== ARCHITECTURE.md ===== # Architecture · Metorik ## Stack | Part | Choice | Why | | --- | --- | --- | | Runtime | TypeScript on Node 22, Fastify bound to the loopback address, a small React dashboard | the store data is the whole point and none of it should leave the machine | | Warehouse | SQLite through better-sqlite3, with the raw API payload kept beside the normalised rows | when a figure looks wrong you need the original response, not your interpretation of it | | Sync | An incremental worker with a watermark, an overlap window and upserts by id | stores keep changing rows while you page through them | | Money | Integers in the currency's minor unit, with the currency stored per order | a report built on floating point sums is a report that disagrees with the shop | | Definitions | Every figure on screen carries the rule that produced it | the argument about revenue is always about which orders counted | ## Modules Each module has one owner concern and a documented way to replace it. | Module | Owns | How to replace it | | --- | --- | --- | | Sync | Paging, watermarks, overlap and upserts. | Any store API, provided paging is by a stable key. | | Money | Minor-unit parsing, currency and refund rows. | Not swappable for floating point, whatever the convenience. | | Definitions | Statuses, refund treatment and the day boundary. | Any presentation, provided the rule stays attached to the figure. | | Identity | Account and email keys, cohorts, repeat rates. | Any keying, provided guests are not collapsed into one customer. | | Rebuild | Replay from raw payloads and export. | Any format, provided the raw payloads survive the normalised tables. | ## Configuration Every runtime setting is an environment variable documented in `.env.example`, validated at startup, with a safe local default wherever one exists. - `STORE_BASE_URL` · required · Over HTTPS only. The key pair travels on every request. - `STORE_CONSUMER_KEY` · required, secret · Read-only. The app checks this at startup. - `STORE_CONSUMER_SECRET` · required, secret · Pairs with the key. Never in the repository. - `DATABASE_PATH` · required · The warehouse including raw payloads. This gets large; plan for it. - `REPORT_TIMEZONE` · required · Defines a day in every report. Printed beside every date range. - `REVENUE_STATUSES` · required · The statuses that count as revenue. There is no safe default here. - `SYNC_INTERVAL_MINUTES` · required · How often the worker runs. Smaller is not better on a busy shop. - `SYNC_OVERLAP_MINUTES` · required · How far back each run re-requests, so boundary rows are seen twice rather than missed. ## Production baseline - Security: least privilege, input validation at every boundary, secret redaction in logs, rate limits on abuse-prone paths, no invented security primitives. - Data: explicit schema and migrations, transactional writes where integrity matters, backup and restore procedures that have been exercised. - Integrations: adapters around third-party providers, idempotent webhook or job processing, bounded retries, timeouts. - Observability: structured logs with request or operation ids, an error-tracking hook, and health and readiness checks where a server exists. - Quality: unit tests for domain rules, integration tests at module boundaries, one end-to-end test of the critical path. ## Decision records For each dependency in the stack table, keep a short note: why it was chosen, its failure mode, and how it is replaced. Do not add infrastructure until a requirement in `PRODUCT.md` justifies it. ===== AGENTS.md ===== # Agent instructions · Metorik product build - Read `PRODUCT.md` and `ARCHITECTURE.md` before changing code. The stack is fixed: TypeScript on Node 22, Fastify bound to the loopback address, a small React dashboard, SQLite through better-sqlite3, with the raw API payload kept beside the normalised rows, An incremental worker with a watermark, an overlap window and upserts by id, Integers in the currency's minor unit, with the currency stored per order, Every figure on screen carries the rule that produced it. - Implement milestone by milestone from `MILESTONES.md`; keep each change reviewable and leave the application runnable at every commit. - Treat authentication, payments, encryption, imports, webhooks and destructive actions as high-risk boundaries when present. - Never invent cryptography or silently weaken a requirement to make a check pass. - Put every external service behind an interface with a deterministic fake for tests. - Add migrations and rollback or recovery notes for every persistent data change. - Log useful operational context without credentials, tokens, passwords or personal data. - Update documentation and run every check before completing a milestone. ## Known traps - 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. - 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. - 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. - 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. - 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. ===== MILESTONES.md ===== # Delivery milestones · Metorik Estimated effort: **weekend** for the indie phases; the production-only milestones add the trust and operability layer. ## M1 · A sync you can interrupt Pulling the store's orders can be killed at any point and resumes without losing or duplicating a row. ### Steps 1. Page by a stable key, not by page number The set moves while you read it. Files: `src/sync/orders.ts`, `tests/pagination.test.ts` 2. Keep a watermark with an overlap window Seeing a boundary row twice is cheap; missing it is not. Files: `src/sync/state.ts` 3. Store the raw payload next to the normalised row Every argument about a number ends here. Files: `src/db/migrations/001_orders.sql` ### Done when - [ ] An interrupted sync resumes to the same row count - [ ] A repeated sync creates no duplicates - [ ] Rate limits and server errors back off - [ ] Raw payloads are stored per order ### 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. ## M2 · Money you can actually add up Revenue excludes the orders that were never revenue and nets the refunds that were given back. ### Steps 1. Parse money into minor units at the boundary One place to get this wrong instead of thirty. Files: `src/money/parse.ts`, `tests/money.test.ts` 2. Choose your revenue statuses and write them down The default includes things that are not sales. Files: `src/reports/definitions.ts` 3. Keep refunds as rows and net them at report time The order total does not know it was refunded. Files: `src/db/migrations/002_refunds.sql`, `tests/refunds.test.ts` ### Done when - [ ] No floating point money anywhere - [ ] A refunded order is full gross and zero net - [ ] Non-revenue statuses never reach a revenue figure - [ ] Foreign currency orders are converted or excluded visibly ### 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. ## M3 · Reports that say what they mean Revenue, order count, average order value and product breakdowns, each displayed with the rule that produced it. ### Steps 1. Attach a definition to every figure The definition is the disagreement, so put it on screen. Files: `src/web/Definition.tsx`, `src/reports/definitions.ts` 2. Store the instant and the bucketing zone A day is not a fact without a place. Files: `src/reports/buckets.ts`, `tests/timezone.test.ts` 3. Read the warehouse, never the store A dashboard that calls the shop on render is a dashboard that takes it down. Files: `src/reports/query.ts` ### Done when - [ ] Every figure shows its definition - [ ] Changing the bucketing zone moves days, not months - [ ] Comparison periods use identical rules - [ ] No report makes a network call to the store ### 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. ## M4 · 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. ### Steps 1. Key identity on the account id, then on the email Otherwise every guest in the shop is one customer. Files: `src/identity/key.ts`, `tests/identity.test.ts` 2. Normalise the email before keying on it Case and whitespace will split one customer into three. Files: `src/identity/normalise.ts` 3. Show the account and guest split on the cohort view So you know how much of the chart is an inference. Files: `src/web/Cohorts.tsx` ### Done when - [ ] Guest orders create email-keyed identities - [ ] Account and guest identity counts are shown - [ ] Email normalisation is case and whitespace safe - [ ] Repeat rates come from identities, not orders ### 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. ## M5 · Rebuild it, export it, and say what it is not (production only) The warehouse can be thrown away and rebuilt, everything exports, and the credentials in use are read-only. ### Steps 1. Rebuild from stored payloads, not from the store A parsing fix should be a command, not an evening. Files: `src/cli/rebuild.ts`, `tests/rebuild.test.ts` 2. Refuse a key that can write A reporting tool has no business being able to change an order. Files: `src/startup/verify-key.ts` 3. Say what this is not, in the interface The reports are the easy half and the user deserves to know. Files: `src/web/About.tsx` ### Done when - [ ] A rebuild reproduces identical figures - [ ] A writable API key refuses to start the app - [ ] Export and reimport round-trips every table - [ ] The limitations are stated in the interface ### 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. ===== OPERATIONS.md ===== # Operations · Metorik ## Backup The SQLite file, and it is worth knowing that most of its size is the raw payloads. Those are what make a rebuild cheap, so do not be tempted to drop them to save space; back up the whole thing and let it be large. ## Restore Restore, run a rebuild from the raw payloads, and compare last month's gross and net against the shop's own reports before trusting anything. If they disagree, the definitions table tells you which rule moved. Do a restore drill before the first real user, and write the date here when it passes. ## Monitoring Minutes since the last successful sync, the count of rows whose raw payload no longer parses after a code change, and the gap between your gross figure and the shop's own for the same period. The third is the only end-to-end check that matters. ## Incident checklist The incident is a number somebody quoted in a meeting that turns out to be wrong. The raw payloads and the definitions table are what let you say precisely what was included on the day it was quoted, rather than arguing from memory about refunds. 1. Contain the issue without destroying evidence or user data. 2. Record the timeline and affected scope. 3. Rotate exposed secrets and revoke compromised sessions or credentials. 4. Restore from a verified backup when needed. 5. Document the root cause, the remediation and the regression test. ## Release gate - [ ] An interrupted sync is confirmed to resume without loss or duplication - [ ] A fully refunded order is confirmed to contribute zero net revenue - [ ] Non-revenue statuses are confirmed absent from revenue figures - [ ] Guest orders are confirmed to form their own identities - [ ] A writable API key is confirmed to prevent startup ## Launch constraint Do not market omitted Metorik capabilities as implemented. The non-goals in `PRODUCT.md` remain user-visible limitations until they are deliberately delivered. ===== .env.example ===== # Copy to .env and fill in. Never commit .env; this file documents it. # Required. Over HTTPS only. The key pair travels on every request. STORE_BASE_URL=https://shop.example.com # Required · secret. Read-only. The app checks this at startup. STORE_CONSUMER_KEY=ck_...redacted # Required · secret. Pairs with the key. Never in the repository. STORE_CONSUMER_SECRET=cs_...redacted # Required. The warehouse including raw payloads. This gets large; plan for it. DATABASE_PATH=./data/warehouse.db # Required. Defines a day in every report. Printed beside every date range. REPORT_TIMEZONE=Europe/London # Required. The statuses that count as revenue. There is no safe default here. REVENUE_STATUSES=completed,processing # Required. How often the worker runs. Smaller is not better on a busy shop. SYNC_INTERVAL_MINUTES=30 # Required. How far back each run re-requests, so boundary rows are seen twice rather than missed. SYNC_OVERLAP_MINUTES=15
README.md
# Metorik · indie build 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: **weekend**. Work `BUILD_PLAN.md` top to bottom · every phase ends in a check that has to pass before the next one starts. ## Stack | Part | Choice | Why | | --- | --- | --- | | Runtime | TypeScript on Node 22, Fastify bound to the loopback address, a small React dashboard | the store data is the whole point and none of it should leave the machine | | Warehouse | SQLite through better-sqlite3, with the raw API payload kept beside the normalised rows | when a figure looks wrong you need the original response, not your interpretation of it | | Sync | An incremental worker with a watermark, an overlap window and upserts by id | stores keep changing rows while you page through them | | Money | Integers in the currency's minor unit, with the currency stored per order | a report built on floating point sums is a report that disagrees with the shop | | Definitions | Every figure on screen carries the rule that produced it | the argument about revenue is always about which orders counted | ## Before you start Have every one of these ready. The plan assumes them from step one. - [ ] **Node.js 22 or newer** · free - Why: Runs the sync worker and serves the dashboard. - Get it: nodejs.org, or your version manager. - Verify: node --version prints v22 or higher - [ ] **A read-only REST API key pair for the store** · free - 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 - [ ] **Which order statuses count as revenue, written down before you build a chart** · free - 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 - [ ] **The timezone that defines a day in your reports** · free - 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 - [ ] **An anonymised order fixture including a refund, a guest order and a cancelled order** · free - 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 ## Quick start Follow Phase 1 in `BUILD_PLAN.md`; it creates the project. Then copy `.env.example` to `.env` and fill in the values it documents. ## Honest limits This build deliberately 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 If one of those is essential to you, that is the reason to keep paying for Metorik, and the README should say so rather than pretend.
BRIEF.md
# Build brief · Metorik The one-shot brief this plan expands. `BUILD_PLAN.md` (or `MILESTONES.md`) is the same sequence broken into steps and checks; where the two disagree, the plan wins. Build me a local warehouse and dashboard over my own store's order data that gets the money right to replace Metorik. Build it in phases, in the order below. Do not write the whole thing in one pass. Finish a phase, run its "Done when" check, fix what fails, and only then start the next phase. ### Stack (fixed, do not substitute) - Runtime: TypeScript on Node 22, Fastify bound to the loopback address, a small React dashboard · the store data is the whole point and none of it should leave the machine - Warehouse: SQLite through better-sqlite3, with the raw API payload kept beside the normalised rows · when a figure looks wrong you need the original response, not your interpretation of it - Sync: An incremental worker with a watermark, an overlap window and upserts by id · stores keep changing rows while you page through them - Money: Integers in the currency's minor unit, with the currency stored per order · a report built on floating point sums is a report that disagrees with the shop - Definitions: Every figure on screen carries the rule that produced it · the argument about revenue is always about which orders counted ### Data model (create this before Phase 1) - 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 ### Phase 1 · A sync you can interrupt Build: Page the orders, customers and products endpoints at the maximum page size the API allows, order by id rather than by date, and upsert by id. Keep a watermark per resource and re-request a small overlap on the next run. Back off on rate limits and server errors, and print a running count so an overnight first sync is checkable in the morning. Trap: 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. Done when: Killing the sync halfway and restarting produces the same row count as an uninterrupted run, tested against a seeded fixture. Running the sync twice in a row inserts no duplicate orders, asserted by a unique constraint and a test. Rate limit and server error responses back off rather than spinning, verified with a stubbed endpoint. The raw payload is stored for every order alongside the normalised row. Do not build yet: No reports, no money rules, no cohorts and no export yet. ### Phase 2 · Money you can actually add up Build: Convert every monetary string to an integer in the currency's minor unit on the way in, keeping the currency on the order. Decide and record which statuses count as revenue. Store refunds as their own rows and subtract them at report time. Show a separate gross and net figure rather than one number that quietly means one of them. Trap: 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. Done when: No monetary value is stored or computed as a floating point number, verified by review and by a test on the schema. A fully refunded order contributes its full value to gross and nothing to net, tested with a fixture. Orders in statuses outside the recorded revenue set appear in a count but never in a revenue figure, tested. An order in a currency other than the store's default is either converted with a recorded rate and date or excluded with a visible note, never silently added. Do not build yet: No cohorts, no export and no rebuild tooling yet. ### Phase 3 · Reports that say what they mean Build: An overview with a date range and a previous-period comparison, a products view by units and revenue, and a per-order detail. Every figure has a small note naming the statuses included, whether refunds are netted and which timezone defines a day. Compute from the warehouse only. Trap: 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. Done when: Every figure on the overview has a definition note naming its statuses, refund treatment and day boundary zone, checked by review. Changing the day boundary zone changes the daily series and not the monthly total, tested at a month boundary. The previous-period comparison uses the same rules as the current period, tested with a fixture spanning both. No report issues a request to the store; they all read the warehouse. Do not build yet: No cohorts, no repeat-purchase analysis and no export yet. ### Phase 4 · Customers who never made an account Build: Build an identities table keyed on the account id where there is one and on a normalised billing email otherwise. Compute first-purchase cohorts and repeat rates over that table. Show the split between account and guest identities so you can see how much of the analysis rests on email. Trap: 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. Done when: An order with a customer id of zero creates or joins an identity keyed on its normalised email, tested with a fixture containing both kinds. The cohort view reports the count of account-based and email-based identities separately. Normalisation is case-insensitive and trims whitespace, tested with a pair of orders differing only in case. A repeat-purchase rate is computed from identities rather than from order rows, asserted in the suite. Do not build yet: No export, no rebuild and no retention tooling yet. ### Phase 5 · Rebuild it, export it, and say what it is not Build: A rebuild command that drops the normalised tables, replays from the stored raw payloads and reports any row that no longer parses. CSV export for every table over a date range. Use a read-only API key and refuse to start with one that can write. State plainly in the interface which parts of the paid product this does not replace. Trap: 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. Done when: A rebuild from raw payloads reproduces identical figures to the live warehouse, tested by comparing totals before and after. The application refuses to start with an API key that can write, verified by attempting a write during startup and treating success as a fatal error. Export produces CSV for orders, items, refunds and identities and reimports into an empty database with matching totals. The dashboard states, on a page a user will actually see, that it does not do abandoned cart tracking or send any email. Do not build yet: Nothing after this. ### Out of scope (and why) - Abandoned cart tracking, which needs a script on the storefront and a capture endpoint, and is a different project with different privacy consequences. - Sending any email at all, because a sending domain with a reputation you have to maintain is the actual subscription and there is no weekend version of it. - Segment-driven automations, which are a rules engine plus a scheduler plus a sending stack you just decided not to build. - Multi-store aggregation, where currency, tax and status conventions stop agreeing and every figure needs a per-store rule. ### README must contain - Which order statuses count as revenue in this build, and that the API's own default returns everything including binned orders - That refunds are stored as separate rows and that gross and net are different figures shown side by side - Which timezone defines a day in the reports, and that changing it moves daily figures - That guest checkouts are keyed on a normalised email and what proportion of your identities that represents - That this replaces the reporting half only, with abandoned carts and email automation named as absent
AGENTS.md
# Agent instructions · Metorik indie build - Read `README.md` and `BUILD_PLAN.md` before writing code. The stack is fixed: TypeScript on Node 22, Fastify bound to the loopback address, a small React dashboard, SQLite through better-sqlite3, with the raw API payload kept beside the normalised rows, An incremental worker with a watermark, an overlap window and upserts by id, Integers in the currency's minor unit, with the currency stored per order, Every figure on screen carries the rule that produced it. Do not substitute. - Work one phase at a time, in order. Do not start a phase until every "Done when" item of the previous one passes. - Prefer the fewest moving parts that satisfy the step. No frameworks, services or dependencies the plan does not name. - Secrets live in `.env`, never in source or logs. Keep `.env.example` current when a variable is introduced. - Do not invent cryptography, security guarantees, APIs or compliance claims. - Add a focused test for every destructive, security-sensitive or data-loss path the plan names. - Run the project checks before declaring a phase complete, and record any deliberate shortcut in the README under "Tradeoffs". ## Known traps - 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. - 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. - 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. - 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.
BUILD_PLAN.md
# Build plan · Metorik 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. Phases are in dependency order. Each ends in a "Done when" list; treat an unticked item as a blocker, not a note. ## Phase 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. ### Steps 1. Page by a stable key, not by page number The set moves while you read it. Files: `src/sync/orders.ts`, `tests/pagination.test.ts` 2. Keep a watermark with an overlap window Seeing a boundary row twice is cheap; missing it is not. Files: `src/sync/state.ts` 3. Store the raw payload next to the normalised row Every argument about a number ends here. Files: `src/db/migrations/001_orders.sql` ### Done when - [ ] An interrupted sync resumes to the same row count - [ ] A repeated sync creates no duplicates - [ ] Rate limits and server errors back off - [ ] Raw payloads are stored per order ### 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. ## Phase 2 · Money you can actually add up Revenue excludes the orders that were never revenue and nets the refunds that were given back. ### Steps 1. Parse money into minor units at the boundary One place to get this wrong instead of thirty. Files: `src/money/parse.ts`, `tests/money.test.ts` 2. Choose your revenue statuses and write them down The default includes things that are not sales. Files: `src/reports/definitions.ts` 3. Keep refunds as rows and net them at report time The order total does not know it was refunded. Files: `src/db/migrations/002_refunds.sql`, `tests/refunds.test.ts` ### Done when - [ ] No floating point money anywhere - [ ] A refunded order is full gross and zero net - [ ] Non-revenue statuses never reach a revenue figure - [ ] Foreign currency orders are converted or excluded visibly ### 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. ## 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. ### Steps 1. Attach a definition to every figure The definition is the disagreement, so put it on screen. Files: `src/web/Definition.tsx`, `src/reports/definitions.ts` 2. Store the instant and the bucketing zone A day is not a fact without a place. Files: `src/reports/buckets.ts`, `tests/timezone.test.ts` 3. Read the warehouse, never the store A dashboard that calls the shop on render is a dashboard that takes it down. Files: `src/reports/query.ts` ### Done when - [ ] Every figure shows its definition - [ ] Changing the bucketing zone moves days, not months - [ ] Comparison periods use identical rules - [ ] No report makes a network call to the store ### 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. ## 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. ### Steps 1. Key identity on the account id, then on the email Otherwise every guest in the shop is one customer. Files: `src/identity/key.ts`, `tests/identity.test.ts` 2. Normalise the email before keying on it Case and whitespace will split one customer into three. Files: `src/identity/normalise.ts` 3. Show the account and guest split on the cohort view So you know how much of the chart is an inference. Files: `src/web/Cohorts.tsx` ### Done when - [ ] Guest orders create email-keyed identities - [ ] Account and guest identity counts are shown - [ ] Email normalisation is case and whitespace safe - [ ] Repeat rates come from identities, not orders ### 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. ## Not in this build - Abandoned cart tracking. - Any email sending at all. - Segment-driven automations. - Multi-store aggregation. ## 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
.env.example
# Copy to .env and fill in. Never commit .env; this file documents it. # Required. Over HTTPS only. The key pair travels on every request. STORE_BASE_URL=https://shop.example.com # Required · secret. Read-only. The app checks this at startup. STORE_CONSUMER_KEY=ck_...redacted # Required · secret. Pairs with the key. Never in the repository. STORE_CONSUMER_SECRET=cs_...redacted # Required. The warehouse including raw payloads. This gets large; plan for it. DATABASE_PATH=./data/warehouse.db # Required. Defines a day in every report. Printed beside every date range. REPORT_TIMEZONE=Europe/London # Required. The statuses that count as revenue. There is no safe default here. REVENUE_STATUSES=completed,processing # Required. How often the worker runs. Smaller is not better on a busy shop. SYNC_INTERVAL_MINUTES=30 # Required. How far back each run re-requests, so boundary rows are seen twice rather than missed. SYNC_OVERLAP_MINUTES=15
PRODUCT.md
# Metorik · product brief ## Problem The good news: every number Metorik shows you is already sitting in your own WooCommerce database, so nobody is gatekeeping your data. An agent can pull orders, customers and products through the Woo REST API into a local store and give you revenue charts, cohort and repeat-purchase views, product breakdowns and CSV exports in a weekend. The gap is everything that is not a chart: abandoned cart tracking needs a script on your storefront plus a capture endpoint, email automations need a sending domain and deliverability you do not have, and segment-driven campaigns need a rules engine that is a project of its own. Large stores also punish naive syncs, Metorik has spent years making that part boring. Build the dashboard, keep paying for the email side if that is why you signed up. ## Product outcome A warehouse you can interrupt, rebuild and export, with revenue figures that exclude what was never a sale, net what was given back, and include the customers who never registered. ## Target user A builder who needs a maintainable product foundation, not a one-off demo. ## Required capabilities - A WooCommerce store with REST API consumer key and secret - Somewhere to run a small Node process and a database (a cheap VPS or your own machine) - Read access to the store over HTTPS - Optional: ability to add one script tag to the storefront if you want cart tracking ## Explicit non-goals for v1 - 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 ## Success criteria - An interrupted sync is confirmed to resume without loss or duplication - A fully refunded order is confirmed to contribute zero net revenue - Non-revenue statuses are confirmed absent from revenue figures - Guest orders are confirmed to form their own identities - A writable API key is confirmed to prevent startup
BRIEF.md
# Build brief · Metorik The one-shot brief this plan expands. `BUILD_PLAN.md` (or `MILESTONES.md`) is the same sequence broken into steps and checks; where the two disagree, the plan wins. Build me a local warehouse and dashboard over my own store's order data that gets the money right to replace Metorik. Build it in phases, in the order below. Do not write the whole thing in one pass. Finish a phase, run its "Done when" check, fix what fails, and only then start the next phase. ### Stack (fixed, do not substitute) - Runtime: TypeScript on Node 22, Fastify bound to the loopback address, a small React dashboard · the store data is the whole point and none of it should leave the machine - Warehouse: SQLite through better-sqlite3, with the raw API payload kept beside the normalised rows · when a figure looks wrong you need the original response, not your interpretation of it - Sync: An incremental worker with a watermark, an overlap window and upserts by id · stores keep changing rows while you page through them - Money: Integers in the currency's minor unit, with the currency stored per order · a report built on floating point sums is a report that disagrees with the shop - Definitions: Every figure on screen carries the rule that produced it · the argument about revenue is always about which orders counted ### Data model (create this before Phase 1) - 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 ### Phase 1 · A sync you can interrupt Build: Page the orders, customers and products endpoints at the maximum page size the API allows, order by id rather than by date, and upsert by id. Keep a watermark per resource and re-request a small overlap on the next run. Back off on rate limits and server errors, and print a running count so an overnight first sync is checkable in the morning. Trap: 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. Done when: Killing the sync halfway and restarting produces the same row count as an uninterrupted run, tested against a seeded fixture. Running the sync twice in a row inserts no duplicate orders, asserted by a unique constraint and a test. Rate limit and server error responses back off rather than spinning, verified with a stubbed endpoint. The raw payload is stored for every order alongside the normalised row. Do not build yet: No reports, no money rules, no cohorts and no export yet. ### Phase 2 · Money you can actually add up Build: Convert every monetary string to an integer in the currency's minor unit on the way in, keeping the currency on the order. Decide and record which statuses count as revenue. Store refunds as their own rows and subtract them at report time. Show a separate gross and net figure rather than one number that quietly means one of them. Trap: 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. Done when: No monetary value is stored or computed as a floating point number, verified by review and by a test on the schema. A fully refunded order contributes its full value to gross and nothing to net, tested with a fixture. Orders in statuses outside the recorded revenue set appear in a count but never in a revenue figure, tested. An order in a currency other than the store's default is either converted with a recorded rate and date or excluded with a visible note, never silently added. Do not build yet: No cohorts, no export and no rebuild tooling yet. ### Phase 3 · Reports that say what they mean Build: An overview with a date range and a previous-period comparison, a products view by units and revenue, and a per-order detail. Every figure has a small note naming the statuses included, whether refunds are netted and which timezone defines a day. Compute from the warehouse only. Trap: 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. Done when: Every figure on the overview has a definition note naming its statuses, refund treatment and day boundary zone, checked by review. Changing the day boundary zone changes the daily series and not the monthly total, tested at a month boundary. The previous-period comparison uses the same rules as the current period, tested with a fixture spanning both. No report issues a request to the store; they all read the warehouse. Do not build yet: No cohorts, no repeat-purchase analysis and no export yet. ### Phase 4 · Customers who never made an account Build: Build an identities table keyed on the account id where there is one and on a normalised billing email otherwise. Compute first-purchase cohorts and repeat rates over that table. Show the split between account and guest identities so you can see how much of the analysis rests on email. Trap: 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. Done when: An order with a customer id of zero creates or joins an identity keyed on its normalised email, tested with a fixture containing both kinds. The cohort view reports the count of account-based and email-based identities separately. Normalisation is case-insensitive and trims whitespace, tested with a pair of orders differing only in case. A repeat-purchase rate is computed from identities rather than from order rows, asserted in the suite. Do not build yet: No export, no rebuild and no retention tooling yet. ### Phase 5 · Rebuild it, export it, and say what it is not Build: A rebuild command that drops the normalised tables, replays from the stored raw payloads and reports any row that no longer parses. CSV export for every table over a date range. Use a read-only API key and refuse to start with one that can write. State plainly in the interface which parts of the paid product this does not replace. Trap: 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. Done when: A rebuild from raw payloads reproduces identical figures to the live warehouse, tested by comparing totals before and after. The application refuses to start with an API key that can write, verified by attempting a write during startup and treating success as a fatal error. Export produces CSV for orders, items, refunds and identities and reimports into an empty database with matching totals. The dashboard states, on a page a user will actually see, that it does not do abandoned cart tracking or send any email. Do not build yet: Nothing after this. ### Out of scope (and why) - Abandoned cart tracking, which needs a script on the storefront and a capture endpoint, and is a different project with different privacy consequences. - Sending any email at all, because a sending domain with a reputation you have to maintain is the actual subscription and there is no weekend version of it. - Segment-driven automations, which are a rules engine plus a scheduler plus a sending stack you just decided not to build. - Multi-store aggregation, where currency, tax and status conventions stop agreeing and every figure needs a per-store rule. ### README must contain - Which order statuses count as revenue in this build, and that the API's own default returns everything including binned orders - That refunds are stored as separate rows and that gross and net are different figures shown side by side - Which timezone defines a day in the reports, and that changing it moves daily figures - That guest checkouts are keyed on a normalised email and what proportion of your identities that represents - That this replaces the reporting half only, with abandoned carts and email automation named as absent
ARCHITECTURE.md
# Architecture · Metorik ## Stack | Part | Choice | Why | | --- | --- | --- | | Runtime | TypeScript on Node 22, Fastify bound to the loopback address, a small React dashboard | the store data is the whole point and none of it should leave the machine | | Warehouse | SQLite through better-sqlite3, with the raw API payload kept beside the normalised rows | when a figure looks wrong you need the original response, not your interpretation of it | | Sync | An incremental worker with a watermark, an overlap window and upserts by id | stores keep changing rows while you page through them | | Money | Integers in the currency's minor unit, with the currency stored per order | a report built on floating point sums is a report that disagrees with the shop | | Definitions | Every figure on screen carries the rule that produced it | the argument about revenue is always about which orders counted | ## Modules Each module has one owner concern and a documented way to replace it. | Module | Owns | How to replace it | | --- | --- | --- | | Sync | Paging, watermarks, overlap and upserts. | Any store API, provided paging is by a stable key. | | Money | Minor-unit parsing, currency and refund rows. | Not swappable for floating point, whatever the convenience. | | Definitions | Statuses, refund treatment and the day boundary. | Any presentation, provided the rule stays attached to the figure. | | Identity | Account and email keys, cohorts, repeat rates. | Any keying, provided guests are not collapsed into one customer. | | Rebuild | Replay from raw payloads and export. | Any format, provided the raw payloads survive the normalised tables. | ## Configuration Every runtime setting is an environment variable documented in `.env.example`, validated at startup, with a safe local default wherever one exists. - `STORE_BASE_URL` · required · Over HTTPS only. The key pair travels on every request. - `STORE_CONSUMER_KEY` · required, secret · Read-only. The app checks this at startup. - `STORE_CONSUMER_SECRET` · required, secret · Pairs with the key. Never in the repository. - `DATABASE_PATH` · required · The warehouse including raw payloads. This gets large; plan for it. - `REPORT_TIMEZONE` · required · Defines a day in every report. Printed beside every date range. - `REVENUE_STATUSES` · required · The statuses that count as revenue. There is no safe default here. - `SYNC_INTERVAL_MINUTES` · required · How often the worker runs. Smaller is not better on a busy shop. - `SYNC_OVERLAP_MINUTES` · required · How far back each run re-requests, so boundary rows are seen twice rather than missed. ## Production baseline - Security: least privilege, input validation at every boundary, secret redaction in logs, rate limits on abuse-prone paths, no invented security primitives. - Data: explicit schema and migrations, transactional writes where integrity matters, backup and restore procedures that have been exercised. - Integrations: adapters around third-party providers, idempotent webhook or job processing, bounded retries, timeouts. - Observability: structured logs with request or operation ids, an error-tracking hook, and health and readiness checks where a server exists. - Quality: unit tests for domain rules, integration tests at module boundaries, one end-to-end test of the critical path. ## Decision records For each dependency in the stack table, keep a short note: why it was chosen, its failure mode, and how it is replaced. Do not add infrastructure until a requirement in `PRODUCT.md` justifies it.
AGENTS.md
# Agent instructions · Metorik product build - Read `PRODUCT.md` and `ARCHITECTURE.md` before changing code. The stack is fixed: TypeScript on Node 22, Fastify bound to the loopback address, a small React dashboard, SQLite through better-sqlite3, with the raw API payload kept beside the normalised rows, An incremental worker with a watermark, an overlap window and upserts by id, Integers in the currency's minor unit, with the currency stored per order, Every figure on screen carries the rule that produced it. - Implement milestone by milestone from `MILESTONES.md`; keep each change reviewable and leave the application runnable at every commit. - Treat authentication, payments, encryption, imports, webhooks and destructive actions as high-risk boundaries when present. - Never invent cryptography or silently weaken a requirement to make a check pass. - Put every external service behind an interface with a deterministic fake for tests. - Add migrations and rollback or recovery notes for every persistent data change. - Log useful operational context without credentials, tokens, passwords or personal data. - Update documentation and run every check before completing a milestone. ## Known traps - 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. - 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. - 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. - 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. - 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.
MILESTONES.md
# Delivery milestones · Metorik Estimated effort: **weekend** for the indie phases; the production-only milestones add the trust and operability layer. ## M1 · A sync you can interrupt Pulling the store's orders can be killed at any point and resumes without losing or duplicating a row. ### Steps 1. Page by a stable key, not by page number The set moves while you read it. Files: `src/sync/orders.ts`, `tests/pagination.test.ts` 2. Keep a watermark with an overlap window Seeing a boundary row twice is cheap; missing it is not. Files: `src/sync/state.ts` 3. Store the raw payload next to the normalised row Every argument about a number ends here. Files: `src/db/migrations/001_orders.sql` ### Done when - [ ] An interrupted sync resumes to the same row count - [ ] A repeated sync creates no duplicates - [ ] Rate limits and server errors back off - [ ] Raw payloads are stored per order ### 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. ## M2 · Money you can actually add up Revenue excludes the orders that were never revenue and nets the refunds that were given back. ### Steps 1. Parse money into minor units at the boundary One place to get this wrong instead of thirty. Files: `src/money/parse.ts`, `tests/money.test.ts` 2. Choose your revenue statuses and write them down The default includes things that are not sales. Files: `src/reports/definitions.ts` 3. Keep refunds as rows and net them at report time The order total does not know it was refunded. Files: `src/db/migrations/002_refunds.sql`, `tests/refunds.test.ts` ### Done when - [ ] No floating point money anywhere - [ ] A refunded order is full gross and zero net - [ ] Non-revenue statuses never reach a revenue figure - [ ] Foreign currency orders are converted or excluded visibly ### 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. ## M3 · Reports that say what they mean Revenue, order count, average order value and product breakdowns, each displayed with the rule that produced it. ### Steps 1. Attach a definition to every figure The definition is the disagreement, so put it on screen. Files: `src/web/Definition.tsx`, `src/reports/definitions.ts` 2. Store the instant and the bucketing zone A day is not a fact without a place. Files: `src/reports/buckets.ts`, `tests/timezone.test.ts` 3. Read the warehouse, never the store A dashboard that calls the shop on render is a dashboard that takes it down. Files: `src/reports/query.ts` ### Done when - [ ] Every figure shows its definition - [ ] Changing the bucketing zone moves days, not months - [ ] Comparison periods use identical rules - [ ] No report makes a network call to the store ### 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. ## M4 · 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. ### Steps 1. Key identity on the account id, then on the email Otherwise every guest in the shop is one customer. Files: `src/identity/key.ts`, `tests/identity.test.ts` 2. Normalise the email before keying on it Case and whitespace will split one customer into three. Files: `src/identity/normalise.ts` 3. Show the account and guest split on the cohort view So you know how much of the chart is an inference. Files: `src/web/Cohorts.tsx` ### Done when - [ ] Guest orders create email-keyed identities - [ ] Account and guest identity counts are shown - [ ] Email normalisation is case and whitespace safe - [ ] Repeat rates come from identities, not orders ### 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. ## M5 · Rebuild it, export it, and say what it is not (production only) The warehouse can be thrown away and rebuilt, everything exports, and the credentials in use are read-only. ### Steps 1. Rebuild from stored payloads, not from the store A parsing fix should be a command, not an evening. Files: `src/cli/rebuild.ts`, `tests/rebuild.test.ts` 2. Refuse a key that can write A reporting tool has no business being able to change an order. Files: `src/startup/verify-key.ts` 3. Say what this is not, in the interface The reports are the easy half and the user deserves to know. Files: `src/web/About.tsx` ### Done when - [ ] A rebuild reproduces identical figures - [ ] A writable API key refuses to start the app - [ ] Export and reimport round-trips every table - [ ] The limitations are stated in the interface ### 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.
OPERATIONS.md
# Operations · Metorik ## Backup The SQLite file, and it is worth knowing that most of its size is the raw payloads. Those are what make a rebuild cheap, so do not be tempted to drop them to save space; back up the whole thing and let it be large. ## Restore Restore, run a rebuild from the raw payloads, and compare last month's gross and net against the shop's own reports before trusting anything. If they disagree, the definitions table tells you which rule moved. Do a restore drill before the first real user, and write the date here when it passes. ## Monitoring Minutes since the last successful sync, the count of rows whose raw payload no longer parses after a code change, and the gap between your gross figure and the shop's own for the same period. The third is the only end-to-end check that matters. ## Incident checklist The incident is a number somebody quoted in a meeting that turns out to be wrong. The raw payloads and the definitions table are what let you say precisely what was included on the day it was quoted, rather than arguing from memory about refunds. 1. Contain the issue without destroying evidence or user data. 2. Record the timeline and affected scope. 3. Rotate exposed secrets and revoke compromised sessions or credentials. 4. Restore from a verified backup when needed. 5. Document the root cause, the remediation and the regression test. ## Release gate - [ ] An interrupted sync is confirmed to resume without loss or duplication - [ ] A fully refunded order is confirmed to contribute zero net revenue - [ ] Non-revenue statuses are confirmed absent from revenue figures - [ ] Guest orders are confirmed to form their own identities - [ ] A writable API key is confirmed to prevent startup ## Launch constraint Do not market omitted Metorik capabilities as implemented. The non-goals in `PRODUCT.md` remain user-visible limitations until they are deliberately delivered.
.env.example
# Copy to .env and fill in. Never commit .env; this file documents it. # Required. Over HTTPS only. The key pair travels on every request. STORE_BASE_URL=https://shop.example.com # Required · secret. Read-only. The app checks this at startup. STORE_CONSUMER_KEY=ck_...redacted # Required · secret. Pairs with the key. Never in the repository. STORE_CONSUMER_SECRET=cs_...redacted # Required. The warehouse including raw payloads. This gets large; plan for it. DATABASE_PATH=./data/warehouse.db # Required. Defines a day in every report. Printed beside every date range. REPORT_TIMEZONE=Europe/London # Required. The statuses that count as revenue. There is no safe default here. REVENUE_STATUSES=completed,processing # Required. How often the worker runs. Smaller is not better on a busy shop. SYNC_INTERVAL_MINUTES=30 # Required. How far back each run re-requests, so boundary rows are seen twice rather than missed. SYNC_OVERLAP_MINUTES=15
$ choose a build depth, inspect the files, then open the complete pack in your agent
Because a store owner wants the abandoned cart email that actually sends, not a chart they built themselves. Reports are the easy half and they are the half you can replicate; the automation half means a warmed sending domain, unsubscribe compliance, segment rules and someone to blame when the flow misfires during a sale. Metorik also absorbs the pain of WooCommerce version drift, plugin conflicts and gigantic order tables, which is unglamorous work that quietly eats your weekends if you own it.
xAbandoned cart tracking and cart recovery emails, which need storefront instrumentation plus real email sending
xAutomated email flows tied to customer segments, with templates, throttling and unsubscribe handling
xBattle-tested incremental sync for stores with hundreds of thousands of orders
xSubscriptions, refunds and tax edge case handling that Metorik has already hit and fixed
xMobile-friendly polish, live order feed and the everything-in-one-place feel
Nothing worth pointing at. That's why the prompt exists.
share on X ↗"I just replaced Metorik ($25/mo) with one prompt"
Vibecode Metorik
Kinda. The core of Metorik is buildable in a weekend with the prompt on this page, but there are real gaps: 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. Read the honest list above before committing.
How much does Metorik cost?
Metorik costs about $25/month (Starter Plan, checked 2026-08-18), which is $300 per year.
What do I lose by replacing Metorik?
Honestly: 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. If any of those are load-bearing for you, keep paying.
Is there an open-source alternative to Metorik?
No mature open-source alternative worth pointing at, which is exactly why the one-shot prompt on this page exists.