feat/mrr-d3
needs attentionviewing older commitfb7f6ff · fullpre-PRreviewed 2026-08-07 16:46 UTC0H · 6M · 8L · 2I- Purpose
- Cross-reference MRR by product × org × plan to replace manual finance spreadsheet lookups with live Supabase warehouse analytics
- Goal
- MRR D3: cross-reference insights by product × org × plan (BAT-308/309/310/311)
- Sub-goals
- D3.1 (BAT-309) — full org × product MRR table: descargas / subs / payment_status
- D3.2a (BAT-310) — product rollup summary: total MRR and orgs per product
- D3.2b (BAT-310) — monthly usage time series (MoM) for on-platform billing actions
- D3.3 (BAT-311) — on-platform vs off-platform coverage gap with finance reference constants
- What
- Added 4 new posthog_insight Terraform resources (HogQLQuery type) querying Supabase warehouse tables (credit_ledger, credit_transactions, organizations, plans, utility_service_monitoring_subscriptions), plus 4 new layout tiles on the Revenue MRR dashboard at grid rows y=20–35
- Why
- Product needs per-product MRR visibility derived automatically from the Supabase SOT instead of manual finance spreadsheet cross-references; makes the on-platform measurement gap explicit for the team
- Areas
- infra/posthog/insights.tf+270−0infra/posthog/dashboards.tf+17−0
- Blast
- 2 IaC files, +287/-0 lines; analytics dashboards only, no app code or schema changes; one commit on branch
Findings · 16
correctness4
payment_status branch includes subscription-plan orgs — double-counts them with subs branch
infra/posthog/insights.tf:1313
No billingUnit filter on the payment_status UNION ALL leg. Subscription-plan orgs (Neto, Pilgrim's) that run cfe_payment_status transactions appear in both payment_status and subs rows. MRR calculation also uses overage_rate_mxn (per-RPU rate), not a per-query rate — semantically wrong for subscription accounts. Fix: add `AND coalesce(JSONExtractString(p.metadata, 'billingUnit'), '') != 'subscription'` matching the descargas branch guard.
mrr_by_product_summary subs branch groups by org name only — detail/summary MRR totals diverge
infra/posthog/insights.tf:1379
The subs leg in mrr_by_product_summary groups by o.name alone (not o.name, p.slug as in the detail query) and uses max(overage_rate_mxn). If any subscription org has contracts under multiple plan rates, the summary overstates their MRR and produces a different total than summing the detail rows.
mrr_by_product_summary counts orgs by name string, not by org ID
infra/posthog/insights.tf:1361
count(DISTINCT org) where org = o.name. Two distinct orgs sharing the same name string would be counted as one.
mrr_by_product_summary orgs count includes all non-subscription orgs including zero-MRR/zero-use ones
infra/posthog/insights.tf:1366
No equivalent of the detail's WHERE mrr_mxn > 0 OR uso_30d > 0 filter, so orgs count in summary > number of rows in detail.
security4
Hardcoded per-product revenue figures committed to git history
infra/posthog/insights.tf:1482
Six MXN constants (99650, 69349, 53256, 9561, 4070, 396429) representing Batu's per-product revenue breakdown are baked into SQL literals and committed to git permanently. Move to a Terraform local or git-ignored tfvars.
Customer org names + per-org financials visible to all PostHog project members
infra/posthog/insights.tf:847
PostHog project-level access is not dashboard-scoped. Anyone with read access to project 334265 sees org-level MRR. Document sensitivity and restrict membership explicitly.
plans.metadata billingUnit extracted without schema validation — silent misclassification
infra/posthog/insights.tf:1219
JSONExtractString returns empty string on missing/malformed keys, silently routing the org into the wrong billing bucket.
No SQL injection surface — all HogQL parameters are compile-time literals
infra/posthog/insights.tf:1266
conventions1
D3 block inserted mid-section — breaks file order vs dashboard tile order
infra/posthog/insights.tf:1232
The D3 block (lines 1232-1496) is inserted between mrr_subscription_based and pre-existing churned_mrr/upgrades/etc. that map to earlier dashboard tiles (y=5-15). Append D3 block after active_subs_by_plan (~line 1658) to match tile order.
improvement7
led CTE copy-pasted identically across 3 insights — extract to Terraform local
infra/posthog/insights.tf:1279
The argMax credit_ledger CTE is verbatim in mrr_by_product, mrr_by_product_summary, and mrr_coverage_gap. Use a Terraform local string interpolated into each query_json for single-point changes.
mrr_by_product_summary omits payment_status — detail/summary totals structurally diverge
infra/posthog/insights.tf:1343
D3.1 includes payment_status; D3.2a (the rollup) does not. sum(mrr) in summary != sum(mrr) in detail. Include payment_status in the summary or explicitly document in both descriptions that it is permanently excluded.
Finance constants in mrr_coverage_gap silently go stale; TOTAL is hardcoded not computed
infra/posthog/insights.tf:1482
TOTAL empresa (396429) is hardcoded but on-platform figures are live. As MRR changes, the TOTAL row immediately diverges. Compute TOTAL dynamically as (SELECT descargas FROM medido) + (SELECT subs FROM subs) + 235886. Add a last_updated column to surface the snapshot date in the PostHog UI.
descargas MRR formula duplicated 3x with inconsistent rounding point
infra/posthog/insights.tf:1302
Per-row vs aggregate rounding in D3.1/D3.2a/D3.3 accumulate different errors. Extract to a Terraform local.
argMax ordering rationale undocumented in D3 CTEs
infra/posthog/insights.tf:1282
org_360 documents why (billing_period_start, created_at) is the correct tiebreak; D3 CTEs copy the pattern without the rationale.
payment_status leg applies per-RPU subscription rate to credit transactions
infra/posthog/insights.tf:1311
For subscription-plan orgs, overage_rate_mxn is a per-RPU rate applied to credit transaction counts — semantically wrong. Add billingUnit guard.
mrr_coverage_gap TOTAL row is a constant — will visibly diverge from on-platform live sum
infra/posthog/insights.tf:1487
Readers can compute that on-platform + off-platform != TOTAL as soon as on-platform MRR changes. Compute dynamically.