nexus_entity_states is an SCD2 table where each entity version has dimension columns (categorical states) and measurement columns (numeric values), plus precomputed _delta columns for measurements. This enables efficient time-series queries without expensive date-spine joins.

Delta computation: For each measurement, the _delta column is the difference from the previous state version. The first delta for each entity equals the initial value itself (the change from "nothing" to "something"), so new entities entering the system are correctly captured in time-series aggregations.

Point-in-Time Queries

Current state of all subscriptions

SELECT entity_id, subscription_lifecycle, mrr_amount
FROM nexus_entity_states
WHERE entity_type = 'subscription'
  AND is_current = TRUE

Total current MRR

SELECT SUM(mrr_amount) as total_mrr
FROM nexus_entity_states
WHERE entity_type = 'subscription'
  AND is_current = TRUE
  AND subscription_lifecycle = 'active'

State of subscriptions at a specific date

SELECT entity_id, subscription_lifecycle, mrr_amount
FROM nexus_entity_states
WHERE entity_type = 'subscription'
  AND valid_from <= '2025-06-01'
  AND (valid_to > '2025-06-01' OR valid_to IS NULL)

MRR on a specific date

SELECT SUM(mrr_amount) as total_mrr
FROM nexus_entity_states
WHERE entity_type = 'subscription'
  AND valid_from <= '2025-06-01'
  AND (valid_to > '2025-06-01' OR valid_to IS NULL)
  AND subscription_lifecycle IN ('active', 'past_due')

Time-Series Queries: Opening State + Deltas

For queries like "show me MRR over time", avoid joining every entity to every day. Instead, use the opening state + deltas pattern.

MRR over time (Jan 2025 – Jan 2026)

-- Step 1: Opening state — total MRR at the start date
WITH opening_state AS (
    SELECT COALESCE(SUM(COALESCE(mrr_amount, 0)), 0) as opening_mrr
    FROM nexus_entity_states
    WHERE entity_type = 'subscription'
      AND valid_from <= '2025-01-01'
      AND (valid_to > '2025-01-01' OR valid_to IS NULL)
),

-- Step 2: All MRR deltas within the period
deltas AS (
    SELECT
        DATE(valid_from) as change_date,
        SUM(COALESCE(mrr_amount_delta, 0)) as daily_delta
    FROM nexus_entity_states
    WHERE entity_type = 'subscription'
      AND valid_from > '2025-01-01'
      AND valid_from <= '2026-01-01'
    GROUP BY DATE(valid_from)
),

-- Step 3: Cumulative sum from opening state
running AS (
    SELECT
        change_date,
        daily_delta,
        (SELECT opening_mrr FROM opening_state)
          + SUM(daily_delta) OVER (ORDER BY change_date) as running_mrr
    FROM deltas
)

-- Include the opening row and all deltas
SELECT
    '2025-01-01' as change_date,
    0 as daily_delta,
    (SELECT opening_mrr FROM opening_state) as running_mrr
UNION ALL
SELECT * FROM running
ORDER BY change_date

Why this is better than a date spine

A date-spine approach would:

1. Generate every day in the range (365 rows)
2. Cross-join with every subscription entity (potentially thousands)
3. Filter to find which subscriptions were active on each day

That's O(days × entities) — a cartesian join that gets expensive fast.

The opening-state + deltas approach is O(state_changes) — it only processes the actual changes, which is typically orders of magnitude smaller.

Churn and Active Count Queries

Active subscription count over time

WITH opening_count AS (
    SELECT COUNT(*) as opening_active
    FROM nexus_entity_states
    WHERE entity_type = 'subscription'
      AND valid_from <= '2025-01-01'
      AND (valid_to > '2025-01-01' OR valid_to IS NULL)
      AND subscription_lifecycle = 'active'
),

activations AS (
    SELECT valid_from as change_date, 1 as delta
    FROM nexus_entity_states
    WHERE entity_type = 'subscription'
      AND subscription_lifecycle = 'active'
      AND valid_from > '2025-01-01'
      AND valid_from <= '2026-01-01'
),

deactivations AS (
    SELECT valid_to as change_date, -1 as delta
    FROM nexus_entity_states
    WHERE entity_type = 'subscription'
      AND subscription_lifecycle = 'active'
      AND valid_to IS NOT NULL
      AND valid_to > '2025-01-01'
      AND valid_to <= '2026-01-01'
),

all_changes AS (
    SELECT change_date, delta FROM activations
    UNION ALL
    SELECT change_date, delta FROM deactivations
),

daily_changes AS (
    SELECT
        DATE(change_date) as change_date,
        SUM(delta) as daily_delta
    FROM all_changes
    GROUP BY DATE(change_date)
)

SELECT
    d.change_date,
    o.opening_active + SUM(d.daily_delta) OVER (ORDER BY d.change_date) as active_subscriptions
FROM daily_changes d
CROSS JOIN opening_count o
ORDER BY d.change_date

MRR by Company

Use relationships to aggregate MRR at the company level:

SELECT
    e.name as company_name,
    SUM(es.mrr_amount) as total_mrr,
    COUNT(*) as subscription_count
FROM nexus_relationships r
JOIN nexus_entities e
    ON r.entity_a_id = e.entity_id
JOIN nexus_entity_states es
    ON r.entity_b_id = es.entity_id
WHERE r.relationship_type = 'owns'
  AND es.is_current = TRUE
  AND es.subscription_lifecycle = 'active'
GROUP BY e.name
ORDER BY total_mrr DESC

Subscription Detail with Company

Join entities, relationships, and states for a full subscription view:

SELECT
    e.name as company_name,
    sub.entity_id as subscription_id,
    es.subscription_lifecycle,
    es.mrr_amount,
    es.valid_from as state_since
FROM nexus_entity_states es
JOIN nexus_entities sub
    ON es.entity_id = sub.entity_id
JOIN nexus_relationships r
    ON sub.entity_id = r.entity_b_id
    AND r.relationship_type = 'owns'
JOIN nexus_entities e
    ON r.entity_a_id = e.entity_id
WHERE es.is_current = TRUE
ORDER BY e.name, es.subscription_lifecycle