fix/mrr-base

credits_included NULL → v becomes NULL, silently drops active credit orgs

infra/posthog/insights.tf

If any plan has p.credits_included = NULL, then greatest((consumed - NULL), 0) evaluates to NULL in ClickHouse, making v = base + NULL*rate = NULL. The org's rows are then silently excluded by WHERE mrr > 0. Fix: add coalesce(p.credits_included, 0) AS incl in the credit CTE.

Zero-consumed orgs lose base_price MRR — WHERE consumed > 0 over-excludes

infra/posthog/insights.tf

The WHERE consumed > 0 guard correctly prevents div/0, but an org on a paid base plan with no credit transactions in a month is absent from the credit CTE entirely — its base_price contribution is lost. The script header now documents this as a known limitation, but MRR is still understated for those months. A separate base-fee UNION ALL from org-plan to credit_mrr with consumed=0 would fix this.

Attribution rounding — three round() calls may not sum to org total

infra/posthog/insights.tf

round(v*mon_cr/consumed/100.0,0) + round(v*one_cr/consumed/100.0,0) + round(v*pay_cr/consumed/100.0,0) can differ from round(v/100.0,0) by ±2 MXN. Cosmetic for chart display, but produces unexplained discrepancies in reconciliation against org-level totals.

HogQL reads sensitive billing data — unchanged scope

infra/posthog/insights.tf

Query joins org names, plan pricing, credit counts. No new exposure vs. the previous version. Access should remain restricted to internal team members.

Hardcoded historical MRR figures in SQL — not secrets

infra/posthog/insights.tf

The multiIf values are internal revenue figures for analytics accuracy, not secrets. Appropriate for an internal-only PostHog project.

BAT-314 Smarter override lacks expiry guard — infra/posthog/insights.tf

infra/posthog/insights.tf

The multiIf hardcode for Feb–Jun 2026 has no TODO or removal trigger. Add: -- TODO(BAT-314): remove after 2026-06, verify live formula for Jul+. Without it, the override silently persists and future maintainers won't know it's time-bounded.

BAT-314 Smarter override lacks expiry guard — scripts/posthog-mrr-pivot.mjs

scripts/posthog-mrr-pivot.mjs

Same as the Terraform copy — the override is present without a removal comment or Linear-issue reference.

Column rename mon_dl→mon_cr, one_dl→one_cr — no grep audit documented

infra/posthog/insights.tf

Per canonical-form.md: audit before renaming any field. These are internal HogQL aliases, but a quick grep for mon_dl/one_dl in the Terraform file and dashboard configs would confirm no downstream breakage.

Dual-copy sync correct — follows established posthog-api-insights pattern

scripts/posthog-mrr-pivot.mjs

Both infra/posthog/insights.tf and scripts/posthog-mrr-pivot.mjs updated in the same commit — exactly the required convention for MRR · pivote maestro (HogQL variables prevent Terraform-only management).

No test for credit MRR formula — silent calculation bug was undetectable

The formula v = base + greatest((consumed - incl), 0) * rate with proportional attribution is pure arithmetic expressible as a pure JS function testable with Vitest. Without a test, the same class of silent miscalculation can recur.

No test for zero-consumption guard (WHERE consumed > 0)

The guard prevents div/0 but also excludes base-fee orgs with no downloads. A boundary test would document the trade-off explicitly.

No HogQL test pattern exists — project-wide gap

Analytics SQL has no unit-test pattern in this project. This PR is consistent with existing practice but the gap increases silent regression risk across all PostHog insights.

Duplicate HogQL in two files with no automated sync check

The same HogQL query lives in both infra/posthog/insights.tf and scripts/posthog-mrr-pivot.mjs. Both were correctly updated in this PR, but there is no CI check to detect future drift. The original bug (missing base_price) could silently recur if a future fix updates only one copy.

Smarter override fallthrough after Jun 2026 is ambiguous — 'aug=0' unexplained

infra/posthog/insights.tf

The commit says 'aug=0' without explaining whether this is intentional (no reports yet) or accidental (live formula broken). If the fallthrough formula is temporarily invalid, extend the override to cover Jul–Aug with explicit 0.0 so zero is intentional rather than invisible. Otherwise a future reader can't distinguish correct from broken.

Known zero-download gap unguarded in the SQL — documentation only in script header

scripts/posthog-mrr-pivot.mjs

The header documents the limitation but the SQL has no inline comment. A future developer diagnosing a MRR shortfall won't find the caveat in the query. Add a SQL comment on the credit CTE, and file a follow-up for a LEFT JOIN base-fee fix.

Magic 100.0 division unexplained — centavos → pesos

infra/posthog/insights.tf

The /100.0 throughout attribution formulas is a centavos-to-pesos conversion. A brief inline comment on the first occurrence would make this self-documenting.

Hardcoded Smarter MXN values have no documented source

infra/posthog/insights.tf

Values 71323.0, 70082.0, 73010.0, 69349.0 have no comment linking them to an invoice or spreadsheet. A brief audit trail comment helps if historical MRR is ever questioned.