feat/posthog-org-360
needs attentionviewing older commit1581114 · fullpre-PRreviewed 2026-08-04 00:07 UTC4H · 5M · 5L · 3I- Purpose
- Create a single PostHog insight that shows one row per customer org combining business data (MRR, credits, plan) with engagement events and CFE delivery health — the 'connective tissue' for ops health monitoring.
- Goal
- Org-360: one-pane view per org for growth/MRR/engagement/delivery health (BAT-295)
- Sub-goals
- Add posthog_insight.org_360 HogQL DataVisualizationNode to platform_health dashboard
- Join warehouse tables (organizations, credit_ledger, plans, sites, memberships, cfe_jobs) with PostHog events
- Surface MRR (credit model), engagement ratios, CFE delivery health per org
- What
- Added 96 lines to infra/posthog/insights.tf: one new posthog_insight resource (org_360) with a multi-table HogQL join across 7 warehouse sources.
- Why
- BAT-295 — ops team needs a single place to triage org health across growth, billing, and delivery dimensions.
- Areas
- infra/posthog+96−0
- Blast
- 1 file, +96 lines. PostHog dashboard-only change. No application code, schema migrations, or API contracts involved.
Findings · 18
correctness7
credit_ledger WHERE status='active' pre-filters before argMax — churned orgs show NULL MRR indistinguishably
infra/posthog/insights.tf
Subquery filters status='active' before argMax. Churned (only 'closed' rows) and mid-rollover orgs get no subquery row → mrr_creditos_mxn = NULL, indistinguishable from no-billing-history orgs. Remove the WHERE pre-filter, use argMax across all statuses, inspect status in outer SELECT.
argMax ordered by created_at should be billing_period_start
infra/posthog/insights.tf
A correction insert with newer created_at but older billing_period_start silently surfaces wrong-period credits. Application code orders by billing_period_start DESC — mirror that in HogQL.
rpus_failing_30d and rpus_delivered_30d are not mutually exclusive
infra/posthog/insights.tf
An RPU with some success and some failure in 30d appears in both counts. rpus_entregados + rpus_fallando can exceed rpus_suscritos, breaking any ratio-based health check.
if(e.last_active > 0, ...) null guard is a no-op
infra/posthog/insights.tf
DateTime > 0 is always true for any real timestamp. NULL is already propagated by the LEFT JOIN. Replace with isNotNull(e.last_active) or simplify to toDate(e.last_active).
ratio_activos can exceed 1.0 — users_30d and members count different populations
infra/posthog/insights.tf
users_30d counts PostHog person_ids (includes removed members, bots); members counts current membership rows. Clamp with least(..., 1.0) or rename.
members subquery counts all memberships including inactive/deleted ones
infra/posthog/insights.tf
No status or deleted_at filter. Application RLS filters status='active'. Add WHERE status='active' AND deleted_at IS NULL.
collect_jobs_30d includes pending/running jobs — inconsistent with RPU delivery counts
infra/posthog/insights.tf
Add AND j.status NOT IN ('pending', 'running') for consistency with rpus_delivered_30d and rpus_failing_30d.
security2
Verify PostHog project 334265 access controls on warehouse-synced tables
Insight surfaces org names, plan slugs, MRR, and credit balances. Confirm project access is restricted to team members.
Hardcoded error codes in HogQL will silently under-count if codes change
RPU_INVALID and RPU_SERVICE_NAME_MISMATCH are string literals. Monitor if rpus_failing_30d reads 0 after any error taxonomy change.
conventions2
Stale comment claims DataVisualizationNode is unsupported — deepened by this PR
infra/posthog/insights.tf
Comment at ~line 621 says 'the provider has no first-class DataVisualizationNode support' but the file has multiple such resources and this PR adds another. Remove or update the comment.
tableSettings/pinnedColumns has no precedent — verify Terraform provider serializes it
infra/posthog/insights.tf
No other DataVisualizationNode insight uses tableSettings. If the provider drops unknown keys silently, pinning will be lost on apply.
tests1
No automated tests for HogQL queries — expected for Terraform IaC insights
Verify manually post-deploy by spot-checking a known org's MRR and RPU counts against source tables.
improvement6
rpus_failing_30d misses pipeline-side failures — metric name overpromises
infra/posthog/insights.tf
Only RPU_INVALID and RPU_SERVICE_NAME_MISMATCH counted. Network/auth/portal errors excluded. An org with pipeline failures shows rpus_fallando_30d = 0 and appears healthy.
collect_jobs_30d volume without delivery rate makes health assessment hard
infra/posthog/insights.tf
High job count is ambiguous (healthy volume vs high failure rate). Consider adding pct_entregados = round(rpus_entregados_30d / nullIf(rpus_suscritos, 0), 2).
Engagement events subquery includes system/passive PostHog events
infra/posthog/insights.tf
users_30d counts any person whose any event fired for the org, including $feature_flag_called, session replay snapshots. Consider filtering to domain-namespaced Batu events.
No org created_at column — cannot separate new orgs from churned dormant ones
A new org with 0 active users looks identical to a churned veteran. Add toDate(o.created_at) AS fecha_alta to enable onboarding vs churn triage.
rpus_suscritos may count cancelled/inactive subscriptions
infra/posthog/insights.tf
No status/active filter on public__utility_service_monitoring_subscriptions. Check schema and add WHERE clause if applicable.
Persona-clustering feature vector lacks numeric stability columns
For future clustering: continuous ratio columns (ratio_activos, mrr_creditos_mxn) are unstable for small-denominator orgs. Log-scale versions (log_mrr, log_members) should go in the warehouse saved-query twin when clustering begins.