← all branches

feat/mrr-pivot

needs attention
28f6f22 · incrementalpre-PRreviewed 2026-08-08 00:15 UTC0H · 3M · 3L · 2I
The branch
Purpose
Add a PostHog MRR pivot table (out-of-band script + codification rule) for the Revenue & MRR dashboard
Goal
Interactive HogQL pivot with group-by dropdowns (Mes, Producto, Org, Plan) showing 6-month MRR trend comparison by default
Sub-goals
  • SG-1: Codify posthog-api-insights.md (escape hatch pattern for HogQL variable insights)
  • SG-2: Ship the MRR pivot script (idempotent, variable-driven)
  • SG-3: Add Mes dimension + 6-month period comparison (this commit)
The changes (whole branch)
What
2 new files: .claude/rules/posthog-api-insights.md (91 lines) + scripts/posthog-mrr-pivot.mjs (203 lines). This commit adds Mes to group-by, rewrites SQL from 30-day snapshot to 6-month monthly breakdown, adds variable drift detection + PATCH.
Why
The PostHog Terraform provider cannot represent HogQL SQL variables; this script is the codified, re-runnable alternative per posthog-api-insights.md pattern.
Areas
.claude/rules/posthog-api-insights.md+91−0scripts/posthog-mrr-pivot.mjs+203−0
Blast
Minimal — analytics script only. No app code, migrations, or API changes. Only affects PostHog dashboard when run manually.
analytics-only no-prod-code pre-PR
➖CI· no PR — pre-PR branch➖CodeRabbit· no .coderabbit.yaml

Findings · 8

correctness2

medium

Off-by-one month: credit CTE spans 7 months, subs CTE covers 6

scripts/posthog-mrr-pivot.mjs

WHERE ct.created_at >= toStartOfMonth(now()) - toIntervalMonth(6) includes data from 7 calendar months while months CTE has k=[0..5] = 6 rows. Credit rows for the 7th oldest month appear without a subs counterpart. Fix: use toIntervalMonth(5) in credit WHERE, or add k=6 to months array.

info

Historical rate uses current plan snapshot — by design

scripts/posthog-mrr-pivot.mjs

All 6 months valued at current overage_rate_mxn. Documented in header as consumption-valued trend series. No action needed.

security1

info

HogQL variable references are server-side — no injection risk

scripts/posthog-mrr-pivot.mjs

{variables.xxx} placeholders resolved by PostHog engine, not string-concatenated at script runtime.

conventions1

medium

Registry entry in posthog-api-insights.md has stale insight name

.claude/rules/posthog-api-insights.md

Registry lists 'MRR · pivote maestro' but INSIGHT_NAME is now 'MRR · pivote maestro (mes × producto × org × plan)'. Update the table row.

improvement2

low

Hardcoded 0.5 credit cost for payment status will silently break if pricing changes

scripts/posthog-mrr-pivot.mjs

Magic number 0.5 in credit CTE has no reference to the billing catalog. Extract to a named constant.

low

LIMIT 400 silently truncates with no indicator when exceeded

scripts/posthog-mrr-pivot.mjs

Hard cap with no warning. With Mes as outermost dimension, row count grows ~6x vs old design.

correctness/security/improvement1

medium

Fuzzy insight match via includes('pivote maestro') could patch wrong insight

scripts/posthog-mrr-pivot.mjs

Changed from exact-name match to substring. If a second insight with 'pivote maestro' in its name exists, .find() returns the first API result non-deterministically. Consider: exact first, fuzzy fallback with warning.

correctness/improvement1

low

sameArr is order-sensitive — false drift if PostHog reorders values array

scripts/posthog-mrr-pivot.mjs

JSON.stringify comparison is positional. If PostHog returns values in a different order, every run will PATCH unnecessarily.

History · 3 commits

  1. 28f6f22needs attentionincremental0H · 3M · 3L2026-08-08 00:15current
  2. 8df489eneeds attentionincremental2H · 1M · 3L2026-08-07 23:21
  3. 8f9031bneeds attentionfull3H · 8M · 9L2026-08-07 23:08