feat/mrr-d3
needs attentionviewing older commitcf2ce90 · incrementalpre-PRreviewed 2026-08-07 17:14 UTC0H · 1M · 2L · 2I- Purpose
- MRR D3 analytics infrastructure — cross-reference MRR by product × org × plan to make billing coverage and gaps visible in PostHog
- Goal
- Three correlated PostHog insights (D3.1 detail, D3.2a summary, D3.3 coverage gap) that share consistent logic and constants so they can't drift from each other
- Sub-goals
- D3.1 (BAT-309): per-org × product MRR base view
- D3.2a (BAT-310): product-level rollup that matches D3.1 row-for-row
- mrr_coverage_gap (BAT-311 Approach A): on-platform medido vs finance reference vs total estimado
- What
- Extracted shared d3_led_cte Terraform local (argMax CTE) used by all 3 D3 insights. Extracted off-platform finance constants into d3_offplatform_mrr map with auto-computed total and generated UNION ALL rows. Fixed payment_status billingUnit guard in D3.1, added payment_status leg and fixed org_id grouping in D3.2a, made TOTAL estimado dynamic in D3.3, added periodo column.
- Why
- Prior version had 3 copies of the argMax CTE that could silently drift. D3.2a sum(mrr) didn't match D3.1. Hardcoded TOTAL estimado would go stale as on-platform MRR grows.
- Areas
- infra/posthog/insights.tf+44−29
- Blast
- 1 file, +44/-29 lines. PostHog IaC only — no app code, no DB migrations, no API surface. 3 PostHog insights affected.
Findings · 5
correctness1
D3.3 TOTAL estimado silently omits on-platform payment_status MRR while off-platform constant already includes it
infra/posthog/insights.tf:1520
medido CTE only computes descargas MRR. TOTAL estimado = medido + subs + d3_offplatform_total, but d3_offplatform_total already includes off-platform 'payment_status + rates' (9,561 MXN). If on-platform payment_status billing activates, D3.3 understates MRR and double-counts in the constant. Comments say 'hoy ~$0 medido'. Fix: add pay_mrr scalar CTE to medido, or document that D3.3 treats all payment_status as off-platform.
security1
Product name map keys interpolated verbatim into SQL string literals — fragile if a key contains a single quote
infra/posthog/insights.tf:1299
d3_offplatform_rows uses `'${prod}'` in a for expression. Current keys safe but a future key with apostrophe would produce broken HogQL. Mitigate with replace(prod, "'", "''") or a comment prohibiting apostrophes.
improvement3
`d3_company_total_finance` (396,429) can silently drift from `d3_offplatform_mrr` map on monthly refresh
infra/posthog/insights.tf:1293
d3_offplatform_total is auto-computed from the map but d3_company_total_finance is a raw literal. Monthly refresh requires 3 atomic updates: d3_finance_period + map values + d3_company_total_finance. Add inline comment or precondition asserting d3_company_total_finance >= d3_offplatform_total.
Three-piece monthly refresh checklist is implicit — a single collated comment block would make it operational
infra/posthog/insights.tf:1280
Add a MONTHLY REFRESH comment block listing all 3 things to update atomically: d3_finance_period, d3_offplatform_mrr values, d3_company_total_finance.
Terraform for-over-map guarantees lexicographic order since 0.13 — sort() not needed, worth knowing
infra/posthog/insights.tf:1297
Terraform 0.13+ guarantees maps iterate in key-sorted order in for expressions, so d3_offplatform_rows output is deterministic. No action needed.