Skip to content

Analytics and reporting

Generated from a canonical source

This page is a read-only projection of docs/handoff-corpus/analytics-and-reporting.md. Edit the canonical file, then run npm --prefix tools/project-knowledge-derive run derive.

What analytics-and-reporting is for

The invariant you must not break: every analytics/reporting query must filter by the authenticated store_hash, at the SQL layer, for every row. A cross-tenant MRR, churn, cohort, or exception-queue read would leak one merchant's revenue and customer data to another — a data-isolation breach with no post-hoc fix once observed by the wrong party. Every read path traced in this domain (computeMrrCents, computeChurnRate, computeCohortRetention, computeLtvProjections, handleAdminSubscriptionsExport) takes store_hash as a bound SQL parameter, not a post-query filter. (Source: ADR-0009; apps/api/src/routes/admin/analytics.ts lines 80-84, 193, 214.)

This domain covers the merchant-facing KPI dashboard, the exception queue, the subscriptions list/detail/export surfaces, and the cohort/LTV analytics page (BRD Epic 21), plus the epicMetrics/epicLogger structured-log substrate those surfaces are built alongside. It does not cover payments-adapter-level or dunning-stage-level metrics (owned by the canonical-charge-rail and dunning handoff pages) or the tools/observability/epic-NN.ts alert-config registry's own capability (referenced here only for the substrate gap it documents).

  • KPI summary tiles — MRR, gross churn rate, and failed-charge count at a glance on the merchant's dashboard landing screen, each with a 7-day trend sparkline (US-21.1)
  • Upcoming charges forecast — 7-day forward view of scheduled charge count and dollar value so a merchant can anticipate near-term revenue (US-21.2)
  • Exception queue — a prioritized, filterable list of subscriptions needing attention (failed charge, OOS renewal, reconciliation drift), with per-row resolve/refund/open actions (US-21.3)
  • Subscription list with search & filter — a paginated, shareable-URL table of every subscription, filterable by status/plan/customer (US-21.4)
  • Bulk actions on subscriptions — skip/pause/cancel a filtered or hand-selected set of subscriptions at once (US-21.5, P2)
  • Subscription detail view — one screen with a subscription's state, charge history (linked to BC orders), event timeline, and manual CS actions (US-21.6)
  • CSV export of the subscription list — download the current filtered view for offline analysis (US-21.7)
  • Cohort retention & LTV analytics — an acquisition-cohort × renewal-cycle retention heatmap and per-plan 12/24-month LTV projection, so a merchant understands subscriber economics beyond the headline KPIs (US-21.8)

The load-bearing decisions:

  • Workers Analytics Engine is the ratified Phase-1 metrics substrate — but the shipped dashboard and metrics emitters don't use it. ADR-0015 locks WAE + structured JSON logs as the taxonomy Epic-21's dashboard was designed to read from, and explicitly rejects a D1-metrics-table approach ("Roll our own (D1 metrics tables + bespoke read Worker)" is a named rejected path). The domain that actually shipped reads D1 directly for every KPI and logs via console.log only. No ADR ratifies this substitution — see the canonical-framing attestation below.
  • Single D1 + store_hash tenant isolation, enforced at the application layer — every analytics/reporting query in this domain (dashboard.ts, analytics.ts, subscriptions-export.ts) filters by store_hash at the SQL layer, never post-query in application code; this is the same primitive Epic-21's own G4 tenant-isolation scenario (US-21.8) exercises directly. (ADR-0009)
  • CSV export ships as synchronous streaming, not the BRD's async-job design — a deliberate Phase-1 deviation the code documents inline, not silent: the BRD AC calls for a background job writing to Vercel Blob with a ready-notification; the shipped version streams CSV rows directly in the HTTP response and blocks the request for the export's duration. (Source: apps/api/src/routes/admin/subscriptions-export.ts header comment, "Implementation note (deviation from BRD's 'background job + notify')".)

Canonical-framing attestation (operator-ratified 2026-07-02). ADR-0015 (canonical: true) ratifies Workers Analytics Engine as the Phase-1 time-series metrics substrate and explicitly names Epic-21's merchant dashboard as the canonical reader, reading against WAE's SQL HTTP endpoint. It explicitly rejects "roll our own (D1 metrics tables + bespoke read Worker)" as a considered-and-rejected path. As actually shipped, Epic-21's dashboard (routes/admin/dashboard.ts, routes/admin/analytics.ts) computes every KPI — MRR, churn rate, cohort retention, LTV, upcoming-charge trends — via direct D1 SQL aggregation against subscriptions/charges/plans, which is precisely the rejected path. Separately, the epicMetrics/epicLogger substrate that does exist (apps/api/src/observability/epic-NN.ts, imported by oauth.ts/plans.ts/processor-connections.ts/etc.) is 100% console.log(JSON.stringify(...)) structured logging — zero calls to env.ANALYTICS.writeDataPoint and zero analytics_engine_datasets bindings exist anywhere in the repo's wrangler.toml files. No ADR supersedes ADR-0015's WAE decision or ratifies the D1-direct-query / structured-log-only shape that actually shipped. tools/observability/OWNER-SPEC.md (a non-deciding, non-ADR artifact) still describes Phase-1 Outputs as "Workers Analytics for log lines" and "Workers Analytics counters / histograms / gauges" — also traced-false against the code. This is a missing-decision-record gap, not merely residue: the metrics substrate that shipped works and is tested, but no decision record ratifies the pivot away from the one that's canonical.

How it actually works

KPI dashboard, at-risk subscriptions, and trend lines

GET /api/v1/admin/dashboard → handleAdminDashboard (routes/admin/dashboard.ts:368-411) fans out nine independent D1 aggregations in a single Promise.all and assembles one AdminDashboardResponse:

  • computeMrrCents — SUM over active subscriptions joined to plans, normalising every interval to a monthly figure (year/12, week×4, day×30) so mixed-cadence plans roll up to one comparable number (dashboard.ts:117-133).
  • computeChurnRate — canceled-this-calendar-month ÷ active-at-month-start, both counted with dedicated D1 COUNT(*) queries scoped by store_hash (dashboard.ts:146-172).
  • computeFailedCount — count of charges that ended failed within a trailing 7-day window, anchored on COALESCE(attempted_at, scheduled_at) (dashboard.ts:179-191).
  • listAtRiskSubscriptions — subscriptions with next_charge_at inside a 24h lookahead and status IN ('past_due','paused','trialing'), capped at 10 rows (dashboard.ts:199-239). The handler's own header comment names this as a schema-aligned proxy for the PRD's dunning_state != 'healthy' filter, which the schema does not carry, and logs the gap as a Spec-Reconciliation follow-up (dashboard.ts:24-29).
  • Three trend functions — computeMrrTrend, computeChurnRateTrend, computeFailedCountTrend — each build a zero-padded 7-day series, oldest first (dashboard.ts:245-329). computeChurnRateTrend fans its 14 underlying per-day queries out concurrently via Promise.all — the function's own comment documents a prior sequential-loop version that took ~700ms wall-clock, fixed to run in roughly one round-trip (dashboard.ts:276-278).
  • computeUpcomingCharges7d — pending charges scheduled over the next 7 days, grouped by day with count + SUM(amount_cents) (dashboard.ts:336-366).

Every one of these nine functions takes store_hash as a bound query parameter (?1), never a post-fetch filter — the pattern the invariant above requires.

Cohort retention and LTV projections

GET /api/v1/admin/analytics/cohort and GET /api/v1/admin/analytics/ltv → handleAdminAnalyticsCohort / handleAdminAnalyticsLtv (routes/admin/analytics.ts).

  • Cohort retention: one query groups subscriptions by strftime('%Y-%m', created_at) (the acquisition cohort) crossed with cycles_completed, scoped by store_hash and is_deleted = 0 (analytics.ts:75-85). The handler then computes, per cohort and per cycle 1–12, the fraction of that cohort whose cycles_completed reached or exceeded that cycle. The function's own docblock names the tradeoff explicitly: subscriptions that haven't yet had time to reach a given cycle are still counted in the cohort's denominator for every cycle, which the comment calls "a conservative lower bound" rather than excluding not-yet-eligible members (analytics.ts:56-66).
  • LTV projections: computeLtvProjections (analytics.ts:171-244) reads an average succeeded-charge amount per plan over the last 90 days, a churn-derived monthly survival rate per plan (90-day cancellations ÷ 90-day total, normalised to a monthly figure by dividing by 3), then projects 12- and 24-month LTV via a closed-form geometric series (geometricLtv, analytics.ts:163-169): LTV_N = avg_charge × Σ(k=0..N-1) survival^k.

Both handlers are routed live and unconditionally in worker.ts — not gated behind a feature flag or a stub: worker.ts:2686-2687 wires handleAdminAnalyticsCohort/handleAdminAnalyticsLtv, and worker.ts:1620 wires handleAdminDashboard.

CSV export

GET /api/v1/admin/subscriptions/export → handleAdminSubscriptionsExport (routes/admin/subscriptions-export.ts:56-151) runs one store_hash-scoped query (with correlated subqueries for last_charge_at and total_charges_count, matching the list handler's join shape), applies status/q filtering in JavaScript to mirror the list view's pre-pagination semantics, then streams CSV rows through a TransformStream — never materialising the full result set in memory. The row cap is MAX_EXPORT_ROWS = 50_000 (subscriptions-export.ts:50).

The observability substrate this domain shares

epicLogger/epicMetrics (apps/api/src/observability/epic-01.ts:14-33, representative of epic-{01,02,04,08}.ts) is entirely console.log/ console.warn/console.error calls emitting { epic, level, event, ...fields } JSON lines — there is no env.ANALYTICS.writeDataPoint call and no analytics_engine_datasets binding anywhere in the repo. tools/observability/OWNER-SPEC.md §Outputs (Phase 1) still lists "Workers Analytics for log lines" and "Workers Analytics counters / histograms / gauges" as the Phase-1 output — traced false against the code that actually ships (see the canonical-framing attestation above).

erDiagram
    subscriptions {
        TEXT store_hash FK
        TEXT status
        TEXT next_charge_at
        INTEGER cycles_completed
        TEXT cancelled_at
        TEXT created_at
    }
    charges {
        TEXT subscription_id FK
        TEXT status
        INTEGER amount_cents
        TEXT scheduled_at
        TEXT attempted_at
        TEXT succeeded_at
    }
    plans {
        TEXT store_hash FK
        INTEGER amount_cents
        TEXT interval
        INTEGER interval_count
        TEXT currency
    }
    plans ||--o{ subscriptions : "governs"
    subscriptions ||--o{ charges : "billed by"

Diagram provenance. Trimmed excerpt (this domain's aggregated fields only) of the canonical, code-sourced docs/architecture/data-model-erd.md (mechanically derived from schema.sql by tools/erd-derive/, as_of_commit: 80fc35f4, staleness_threshold_days: infinite — this source has no sign_off field; its own staleness marker is the CI-enforced drift gate instead). The full ERD carries 85 tables with every attribute shown; subscriptions, charges, and plans each carry far more columns than shown here — only the columns this domain's SQL actually aggregates over are included. No dedicated sequence diagram exists for the dashboard/exception-queue/analytics read paths in the source document — these are stateless read endpoints, not multi-step flows, so an ERD excerpt (not a sequence diagram) is the applicable visual aid, matching what Input-B's visual-aid pointer names as the closest artifact.

Where intent and reality diverge

The derived coverage matrix (_coverage-matrix.json) reports all 8 US-21.x rows at g4_status: "pass" (terminal_gate: "G4", verification_tier: "g4-scenario"). That is true, and it is not the whole truth. Eight typed deltas:

  • Superseded-framing residue — ADR-0015 (canonical:true) ratifies WAE as the Phase-1 metrics substrate and names Epic-21's dashboard as its reader, while explicitly rejecting a D1-direct-query approach; the shipped dashboard/analytics/cohort/LTV code is entirely D1-direct-query, and the epicMetrics/epicLogger emitters are entirely console.log structured logs — zero WAE bindings exist anywhere in the repo. No ADR ratifies the substitution; tools/observability/OWNER-SPEC.md compounds the drift by still describing Phase-1 Outputs as WAE-based. This is a missing-decision-record gap per the Input-B contract's own procedure (see canonical-framing attestation above).
  • Verified-but-incomplete — US-21.1's KPI tile row ships 3 of the BRD's 6 required tiles (MRR, gross churn rate, failed-charge count); active_subscription_count, new_subs_30d, cancels_30d, and a separate net-revenue-churn figure are absent from both AdminDashboardResponse (routes/admin/dashboard.ts lines 67-81) and AdminDashboardPayload (apps/admin/src/lib/api-client.ts lines 428-444) — churn_rate is gross churn only, with no net-revenue-churn companion metric anywhere in the codebase. (Source: docs/audits/derived/brd-epics/epic-21-merchant-dashboard-kpis-exception-queue.md US-21.1 gaps; confirmed independently by direct code read of both files.)
  • Verified-but-incomplete — US-21.2's upcoming-charges panel ships only the 7-day forecast; the BRD AC requires both 7-day and 30-day views, and no upcoming_charges_30d field or toggle exists in AdminDashboardResponse/AdminDashboardPayload. (Source: same epic-view file, US-21.2 gaps.)
  • Named-deferred — US-21.3's "real-time" requirement (BRD non-functional: "Queue is real-time (websocket or polling) — new exceptions appear without page refresh") is not built: ExceptionQueueList.tsx fetches once per mount/filter-change only, with no polling interval or websocket connection. The BRD's "Escalate" action and date-range filter are likewise absent — only "Mark resolved" and type/severity/sort filters ship. (Source: epic-view US-21.3 gaps, items 1, 2, 4.)
  • Verified-but-incomplete — US-21.5 bulk actions (P2) ship scoped only to hand-checked rows or "select all on this page" — the BRD AC's "confirm scope: selected vs. all matching filter" step, and the "queued and progress-tracked" async execution model, are both unbuilt; the shipped version is a single synchronous POST with no destructive-action confirm guard on bulk Cancel. (Source: epic-view US-21.5 gaps.)
  • Verified-but-incomplete — US-21.6's subscription detail view is missing two of the BRD AC's enumerated sections entirely: payment-method health (card brand/last4/expiry/health indicator) and shipping/billing addresses. Charge history, event timeline, and manual-action buttons are present and BC-order-linked. (Source: epic-view US-21.6 gaps.)
  • Named-deferred — US-21.7's CSV export ships as a synchronous streaming download rather than the BRD's background-job-plus-Vercel-Blob-notification design; this is a documented, deliberate Phase-1 scope cut, not a silent gap — the code's own header comment names it and gates the re-architecture on future volume. No async job, no Blob storage, no ready-notification exist; a large export blocks the HTTP request and can time out with only a raw error. (Source: apps/api/src/routes/admin/subscriptions-export.ts header comment; epic-view US-21.7 gaps.)
  • Verified-but-incomplete — US-21.8's cohort heatmap renders 0% for any cell beyond a cohort's actual age (cells the cohort hasn't had time to reach yet), which is visually indistinguishable from genuine 0% retention at that cycle — a young cohort's still-pending cycles look like total churn. Per-cell retention detail (cohort heatmap) and per-day forecast detail (US-21.2 bar chart) are both exposed only via mouse-hover title attributes, not keyboard- or screen-reader-reachable. (Source: epic-view US-21.8 gaps; US-21.2 disabled_focus.gaps.)
  • Contract-verified, not live-verified — every US-21.x row is G4-pass (apps/api/test/scenarios/epic-21-merchant-dashboard.scenario.ts, against a D1-miniflare instance) plus RENDER-mode Playwright coverage (apps/admin/tests/e2e/dashboard-kpi.spec.ts and the dashboard-health.feature / analytics.feature BDD journeys) that stub the /api/v1/admin/* network calls with deterministic fixtures. A REAL-mode variant of the two BDD journeys exists and can hit the live deployed Worker (E2E_REAL_BACKEND=1), but it runs only via an out-of-band workflow_dispatch job, not on routine dev-push/PR CI — no routine-tier evidence observes any KPI, cohort, LTV, or exception-queue figure against a live production/sandbox D1 with real subscriber data (see live-state attestation below).
  • Built-but-untrodden — the Epic-21 G4 scenario file's own header comment claims US-21.8's cohort/LTV backend is "not yet wired to a route" (authored 2026-06-10) — this is stale within the same file: a later-added Scenario 9 in that same file imports and directly exercises the real route handlers (handleAdminAnalyticsCohort, handleAdminAnalyticsLtv), and worker.ts routes both endpoints live. The comment is dead documentation inside a still-active test file, not a real gap — flagged here so a future reader doesn't inherit the stale claim (see live-state attestation for the full trace).

Live-state attestation (operator-ratified 2026-07-02). All Epic-21 analytics/reporting endpoints are wired live in worker.ts and G4-verified against a real D1 (miniflare) instance: GET /api/v1/admin/dashboard (US-21.1/21.2/21.3-summary/21.6-partial), GET /api/v1/admin/analytics/cohort and /ltv (US-21.8), GET /api/v1/admin/subscriptions/export (US-21.7). The Epic-21 G4 scenario file's own header comment claims "US-21.8 — Cohort/LTV... backend not yet wired to a route" (line 33, authored 2026-06-10) — this is stale and contradicted by the same file's own body: Scenario 9 (added in a later commit to the same file) imports handleAdminAnalyticsCohort/handleAdminAnalyticsLtv directly and asserts against live D1-backed responses, and worker.ts routes both paths to those exact handlers. Beyond G4, two dual-mode (RENDER/REAL) Playwright-BDD journeys exist — e2e/behavior/dashboard-health.feature and e2e/behavior/analytics.feature — that can drive the real deployed admin against the real deployed Worker (E2E_REAL_BACKEND=1), but REAL mode only runs via an out-of-band workflow_dispatch job (.github/workflows/ui-demo-convergence.yml:58), not on routine PR/dev-push CI; the routine-tier evidence (unit + G4 scenario + the Playwright e2e spec apps/admin/tests/e2e/dashboard-kpi.spec.ts) all stub the /api/v1/admin/dashboard network call or call the D1-miniflare handler directly — no routine-tier run observes the endpoint against a live production/sandbox D1 with real subscriber data. Captured demo stills exist at apps/portal/public/try/demo/convergence/{dashboard-health,analytics}/ but these are produced by the same fixture-injecting RENDER-mode walk, not a REAL-mode capture. Net: G4-verified + RENDER-mode-e2e-verified; no routine-tier G5 (live-store) evidence found for any US-21.x row in this domain.

How to operate & extend

  • Change the at-risk lookahead / failed-charge lookback windows: both are exported constants — AT_RISK_LOOKAHEAD_MS (24h) and FAILED_LOOKBACK_MS (7d) in dashboard.ts:85-86 — not per-store or per-plan settings.
  • Add a missing KPI tile (e.g. active-subs count, net-revenue-churn): extend AdminDashboardResponse/_internal in dashboard.ts with a new compute* function (mirror the existing nine — one bound store_hash query, exported for unit testing), then extend AdminDashboardPayload in apps/admin/src/lib/api-client.ts and the Dashboard.tsx tile row to render it.
  • Add polling or websocket delivery to the exception queue: the code as read in this session already has a POLL_INTERVAL_MS = 30_000 setInterval in ExceptionQueueList.tsx — see the contradiction flagged in Confidence notes before assuming this is unbuilt.
  • Wire real metrics emission to WAE (closing the ADR-0015 gap): every D1-direct-query compute* function in this domain, plus every epicMetrics.* call site, would need a parallel env.ANALYTICS.writeDataPoint emission per the ADR-0015 taxonomy, an ANALYTICS binding added to every Worker's wrangler.toml, and either a migration of the dashboard's read path to WAE's SQL HTTP endpoint or a documented decision to keep D1-reads as the query layer while WAE carries only the metrics-taxonomy counters. Either way, this needs a new ADR — silently continuing to diverge from ADR-0015 compounds the missing-decision-record gap.
  • Ship paid CSV-export re-architecture (async job + Vercel Blob): the synchronous-streaming implementation notes its own upgrade trigger ("Re-architecting to a background queue is a separate epic when row counts demand it") — the MAX_EXPORT_ROWS = 50_000 cap is the current backstop, not a redesign.
  • The invariant you must not break: every analytics/reporting query binds store_hash as a SQL parameter, never a post-fetch filter (see above). A new compute* function or export handler that reads subscriptions/charges/plans without a store_hash = ? clause reintroduces the exact cross-tenant leak class ADR-0009 exists to close — and this domain's own G4 tenant-isolation scenario (US-21.8) is the test that would catch it if extended to a new query.

Confidence notes

  • Input-B's US-21.3 "Named-deferred" delta claims ExceptionQueueList.tsx has "no polling interval or websocket connection" and that the BRD's "Escalate" action and date-range filter are "likewise absent." Tracing the current file directly contradicts all three sub-claims: a POLL_INTERVAL_MS = 30_000 setInterval-driven background poll exists and is explicitly commented as satisfying "US-21.3 non-functional: queue is real-time" (ExceptionQueueList.tsx:27-28,276-288); a handleEscalate handler exists and calls an escalateException API client function (ExceptionQueueList.tsx:24,325-338); and a fromDate/toDate date-range filter with two <Input type="date"> controls exists (ExceptionQueueList.tsx:227-228,251-254,457-476). Per this generation prompt's rule, I rendered the delta as Input-B states it (bold type + citation preserved) rather than silently softening or dropping it — this is reported, not resolved. A human should re-verify against the epic-view gaps: source Input-B cites and re-attest or correct the delta.
  • US-21.7's coverage-matrix label reads "Export subscription data (CSV)" (docs/audits/derived/_coverage-matrix.json, ac: "US-21.7") rather than Input-B's descriptor "CSV export of the subscription list" — same capability, different wording; not treated as a contradiction, just noted since a reader cross-referencing the raw matrix by label text (not ac id) could momentarily miss the match.
  • No dedicated sequence diagram exists for this domain's read paths, and the ERD excerpt above is authored by this generation (trimmed to the fields computeMrrCents/computeChurnRate/computeCohortRetention/ computeLtvProjections/handleAdminSubscriptionsExport actually touch), following Input-B's own visual-aid note that no dedicated visual aid exists for this domain. Every field name in the excerpt was cross-checked against the actual SQL in dashboard.ts/analytics.ts, not copied from the full ERD file (which was not fully re-read column-by-column here) — flagging in case a later full ERD diff surfaces a mismatch.
  • docs/architecture/data-model-erd.md's full subscriptions/plans entities were not read in full (85 tables total) — only grepped for the file-level frontmatter and confirmed the excerpt's field names against the code's own SQL column references, not against the ERD document's literal table blocks. If a column name in the ERD differs cosmetically from the schema (e.g. a derived/computed column), this excerpt would not catch that; the code citations above are the load-bearing source of truth for the excerpt's accuracy, not the ERD file itself.