Revenue Analytics counts canceled Stripe subscriptions as active when subscription rows are stale

[MattBro]

Bug

Revenue Analytics' subscription, customer, and LTV charts can be wrong when the data warehouse holds stale Stripe subscription rows. Subs canceled after their initial sync stay counted as active forever; churn never increments; LTV stays empty. MRR / Gross revenue look correct.

Repro

1. Set up a Stripe data warehouse source with the default Subscription schema (append-only incremental, keyed on created).
2. Have at least one subscription that gets canceled in Stripe after it was first synced.
3. Open Revenue Analytics, look at any period after the cancellation.

Expected: the canceled sub falls out of "active" and appears in churn after its ended_at.
Actual: it stays counted as active forever; churn never increments; LTV chart is empty.

Root cause

Stripe subscription sync is append-only by created timestamp (posthog/temporal/data_imports/sources/stripe/settings.py:78, source.py:262 - supports_incremental=False, supports_append=True). The DWH only fetches subs with new created values; existing rows are never re-fetched. When Stripe populates ended_at on cancellation, our copy of the row stays with ended_at = NULL.

The Revenue Analytics subscription view passes ended_at straight through (products/revenue_analytics/backend/views/sources/stripe/subscription.py:55), and the metrics query treats NULL ended_at as still active (_period_gteq_expr in revenue_analytics_query_runner.py:469-482 short-circuits to true when isNull(left)).

Result:

• Subscription / customer counts inflate as canceled subs accumulate
• Churn check (_period_eq_expr on ended_at) never fires, so churned_customer_count = 0
• LTV multiIf returns NULL when churned_customer_count = 0, so the series is empty (revenue_analytics_metrics_query_runner.py:204-209)

MRR is unaffected because it's driven by invoices; when billing stops, no new invoices arrive and MRR drops naturally.

Fix options

1. Add a merge / upsert sync mode for Stripe subscriptions in the data warehouse so existing rows update.
2. Auto-schedule periodic full refresh of the Subscription schema as a workaround.
3. Land the durable fix in Customer Analytics if Revenue Analytics stays in maintenance mode.

Customer workaround today: trigger a full refresh on the Subscription schema in DWH settings, and repeat periodically.