Baremetrics

Promptfree, for everyone, and the only version there is

Copy the prompt for Claude CodeCopy the prompt for CodexCopy the prompt for Cursor

Copy

Build me subscription analytics that replace Baremetrics: recurring revenue, churn and expansion, computed from my payment processor's own data.

STACK
- Node 20+ with Fastify, server-rendered HTML
- SQLite through better-sqlite3, WAL mode
- The processor's API and webhooks as the source
- Charts as inline SVG drawn by hand. No charting library
- Caddy in front

THE DATA MODEL
- customers: id, provider_ref, email_hash, created_at, country, currency
- subscriptions: id, customer_id, provider_ref, plan_ref, status, interval, amount_cents, currency, started_at, current_period_end, cancelled_at, ended_at
- subscription_events: id, subscription_id, kind, from_amount_cents, to_amount_cents, at — created, upgraded, downgraded, paused, cancelled, reactivated. **Append-only, and every number below is computed from it**
- charges: id, customer_id, subscription_id, provider_ref, amount_cents, currency, status, paid_at, refunded_at, refunded_cents
- provider_events: id, event_id, kind, payload_json, processed_at — idempotency lives here
- daily_snapshots are forbidden. Everything is computed from the event stream at query time, or a definition change orphans a year of history

GETTING THE DATA IN
- Backfill everything from the processor's API on first run, paginating, with a cursor so it can resume
- Then webhooks, with signature verification, idempotency by event id, and out-of-order delivery handled — the subscription-updated event routinely arrives before checkout-completed
- Reconcile nightly against the processor's own totals and alert on any disagreement. The local copy is a mirror and must be treated as one
- Currencies: store the charge in its own currency and the converted amount with the rate and its date, both. Reporting a mixed-currency business in one number without recording the rate makes last quarter unreproducible

THE DEFINITIONS, WHICH ARE THE PRODUCT
- **Write each one down beside the number, in plain words.** Everybody computes these differently, and a metric without a definition is decoration
- Monthly recurring revenue: the normalised monthly value of active subscriptions on a date. Annual plans divided by twelve, not counted in the month they were paid
- The movement broken into new, expansion, contraction, churned and reactivated. **The total alone tells you nothing** — a flat month can be heavy churn covered by heavy acquisition, and only the breakdown shows it
- Churn as revenue and as customers, separately, because they answer different questions
- Involuntary churn — a failed payment — reported apart from voluntary. Different problem, different fix
- Lifetime value stated with its formula and its assumption about survival, and treated as an estimate with a wide margin rather than a fact
- A trial that never converts is not churn. Decide where trials enter the numbers and say so

WHAT TO SHOW
- Recurring revenue over time with the movement stacked beneath it
- Churn by cohort — of the customers who joined in a month, what share are still paying — which is the honest view and the one that takes a year to become useful
- Revenue by plan, by country, by acquisition month
- Failed payments now, and how much of last month's involuntary churn was recovered
- Everything computed at query time from events

OPERATIONS
- .env: DATABASE_PATH, BASE_URL, PROVIDER_KEY, PROVIDER_WEBHOOK_SECRET, SESSION_SECRET, HASH_SALT
- Migrations on boot, each once; nightly backup off the machine
- Health endpoint reporting webhook lag and the last successful reconciliation

WHAT MATTERS MOST
The event table and the written definitions. Backfill, then recompute last quarter and check it against what the processor says — if the two disagree and you cannot explain why, the definitions are wrong rather than the code.