Fct Revenue Item

1-- Line-item grain revenue fact, the dbt analogue of PostHog's managed revenue_item view.
2-- Recognizes deferred revenue: each recurring line item is spread evenly across the calendar months
3-- of its service period [period_start, period_end], so an annual plan feeds 1/12 of its value into
4-- each month's MRR rather than the whole amount into a single month. One-time (non-subscription)
5-- items resolve to just the month they were charged. Grain is one row per (line item, service month).
6-- Converts to the reporting base currency via the currency_rates seed (dbt has no convertCurrency()).
7{{ config(materialized='table') }}
8
9with items as (
10    select * from {{ source('stripe', 'invoice_line_items') }}
11),
12rates as (
13    select currency, rate_date, rate_to_base from {{ ref('currency_rates') }}
14),
15-- month spine covering the full span of all line items; generate_series over dates is
16-- Postgres/DuckDB syntax — swap in your warehouse's month generator if it differs.
17month_spine as (
18    select generate_series(
19        date_trunc('month', min(period_start)),
20        date_trunc('month', max(coalesce(period_end, period_start))),
21        interval '1 month'
22    ) as month
23    from items
24),
25recognized as (
26    select
27        i.id,
28        i.subscription_id,
29        i.customer_id,
30        i.product_id,
31        i.period_start::date as period_start,
32        i.period_end::date   as period_end,
33        i.currency,
34        i.amount             as line_amount,
35        (i.subscription_id is not null) as is_recurring,
36        s.month::date        as month,
37        -- number of service months, to split the line amount evenly across the period
38        count(*) over (partition by i.id) as service_months
39    from items i
40    join month_spine s
41        on s.month >= date_trunc('month', i.period_start)
42       and s.month <= date_trunc('month', case
43               when i.subscription_id is not null then coalesce(i.period_end, i.period_start)
44               else i.period_start
45           end)
46)
47select
48    r.id                               as revenue_item_id,
49    r.month,
50    r.subscription_id,
51    r.customer_id,
52    r.product_id,
53    r.period_start,
54    r.period_end,
55    r.is_recurring,
56    r.currency                         as original_currency,
57    r.line_amount / r.service_months   as original_amount,
58    -- base-currency amount at the month's rate. A MISSING rate stays NULL on purpose so the
59    -- not_null test on `amount` fails loudly instead of silently assuming 1:1 parity. Seed the
60    -- base currency itself at rate_to_base = 1.0 so base-currency rows resolve.
61    (r.line_amount / r.service_months) * rt.rate_to_base as amount
62from recognized r
63left join rates rt
64    on rt.currency = r.currency
65   and rt.rate_date = r.month