fix/mrr-base
needs attention2db5192 · incrementalpre-PRreviewed 2026-08-11 22:54 UTC0H · 4M · 5L · 2I- Purpose
- Fix MRR analytics reporting accuracy: strip IVA from Stripe-billed credit plans and account for off-platform fixed-amount contracts (Pilgrim, Niko) that have no ledger entries in the platform
- Goal
- PostHog MRR pivot and coverage-gap insight report MRR sin IVA (net), aligned with Stripe net amounts and matching finance expectations
- Sub-goals
- Strip IVA (÷1.16) from credit plan MRR — Stripe bills with IVA included, MRR reported net
- Fix Pilgrim's Pride: override count×rate ($25/RPU wrong) to contractual flat $9,000/mo
- Add Niko Energy: off-platform Asset Management at flat $50,000/mo until mrr_billing_profile Phase 3
- What
- infra/posthog/insights.tf: MRR HogQL SQL updated — credit_mrr CTE now divides by 1.16, subs CTE adds Pilgrim flat-rate branch and Niko Energy UNION ALL row; same changes mirrored in scripts/posthog-mrr-pivot.mjs; updated header docs in the script
- Why
- The prior MRR model reported gross Stripe amounts (IVA included) for credit plans, overstating MRR by ~16% for those orgs. Pilgrim's rate was contractually wrong (per-RPU vs flat). Niko Energy had no in-platform presence.
- Areas
- infra/posthog/insights.tf+38−11scripts/posthog-mrr-pivot.mjs+39−13.claude/rules/posthog-annotations.md+18−6.github/workflows/release-promote.yml+50−5scripts/posthog-annotate.mjs+11−0
- Blast
- 5 files, +156/−35 across the full branch; analytics SQL and CI workflow only — no app code, no API, no DB migrations
Findings · 12
correctness2
mrr_coverage_gap subs CTE does not apply Pilgrim flat-rate override
infra/posthog/insights.tf
The pivot correctly overrides Pilgrim's to flat $9,000/mo; the mrr_coverage_gap subs CTE still uses count×rate (count×$25), making the cross-check a misleading comparator for Pilgrim's org.
Niko Energy appears at full $50,000 in the current partial month
infra/posthog/insights.tf
FROM months mm includes the current calendar month so Niko shows $50,000 regardless of progress through the month. Consistent with how base fees are treated; acknowledged limitation.
security3
Customer names and exact contracted revenue committed to tracked files
infra/posthog/insights.tf
'Niko Energy' + $50,000/mo and 'custom-pilgrims' + $9,000/mo are commercially sensitive data hardcoded in git. Acceptable for a private repo; risk escalates if access widens.
No HogQL injection risk — SQL is entirely static
scripts/posthog-mrr-pivot.mjs
No user-controlled input reaches the SQL string. No injection risk.
API key handling is correct — read from env, never logged
scripts/posthog-mrr-pivot.mjs
POSTHOG_PERSONAL_API_KEY is read exclusively from process.env; never printed or committed.
conventions1
Pilgrim + Niko temp overrides lack Linear ticket references
infra/posthog/insights.tf:1186
Smarter override cites BAT-314 as its exit criterion; Pilgrim and Niko only mention 'Phase 3' with no BAT-NNN — not grep-able for cleanup.
tests2
mrr.test.ts comment describes Pilgrim as count×rate — now stale
packages/billing/src/__tests__/mrr.test.ts:47
Line 47 says '// Pilgrim's Pride: 115 subs @ $25'. After this diff, Pilgrim's PostHog MRR is flat $9,000. The test logic is unchanged but the comment misleads future readers.
No documented manual verification baseline for hardcoded overrides
scripts/posthog-mrr-pivot.mjs
Pilgrim $9,000 and Niko $50,000 are business-critical revenue lines with no repo-level test path. A validation note would serve as a regression baseline.
improvement4
IVA 1.16 divisor is a bare magic number appearing in two files
scripts/posthog-mrr-pivot.mjs
1.16 appears in credit_mrr (script) and mrr_coverage_gap (Terraform). A named constant IVA_FACTOR = 1.16 in the script and matching Terraform comment would make a future rate change findable and prevent drift.
Niko Energy hardcode lacks BAT-NNN Linear ticket — inconsistent with Smarter pattern
scripts/posthog-mrr-pivot.mjs
Smarter carries (BAT-314) as cleanup trigger. Niko only says 'until mrr_billing_profile table lands (Phase 3)' — no searchable ticket. The Phase 3 PR author has no automatic way to find this row.
Growing UNION ALL chain mixes on-platform and off-platform arms without visual grouping
scripts/posthog-mrr-pivot.mjs
The base CTE now has 6 arms. Grouping comments (on-platform / off-platform overrides) would signal which arms are data-driven vs manual temp rows.
Pilgrim $9,000 literal also lacks ticket reference in both files
infra/posthog/insights.tf
Same as Niko: a BAT-NNN reference in the if(plan='custom-pilgrims', 9000, ...) comment would make it grep-able when the plan contract changes.