Event-time state and reference data

GraphJSON stores timestamped JSON facts. It does not maintain a hidden mutable user profile or automatically attach today’s account plan to last year’s events.

That makes historical meaning explicit. It also means you must choose how to represent values that change over time.

Distinguish four data patterns#

Do not use one current database row to answer every historical question. A current row can tell you what is true now; it cannot always tell you what was true when an older action occurred.

Put event-time attributes on important facts#

When a property is needed to segment the event as it happened, include it:

{
  "event": "report_published",
  "event_id": "evt_01J...",
  "account_id": "acct_7",
  "report_id": "rpt_42",
  "plan_at_event": "pro",
  "region_at_event": "us",
  "schema_version": 1
}

This makes “reports published by plan at event time” straightforward and stable.

Use event-time copies when:

• the property is small and bounded
• historical segmentation is common
• the producer can determine the value authoritatively
• duplicating it does not create a privacy or consistency problem

Name the semantics. plan_at_event is clearer than plan when a current plan also exists elsewhere.

Emit state transitions#

For lifecycle analysis:

{
  "event": "subscription_state_changed",
  "event_id": "sub_evt_91",
  "account_id": "acct_7",
  "subscription_id": "sub_42",
  "previous_state": "trialing",
  "state": "active",
  "effective_at": 1785081600,
  "reason": "first_invoice_paid",
  "source_version": 314,
  "schema_version": 1
}

Use the GraphJSON request timestamp for the business-effective time when that is known. Keep a separate observed_at or delivery-latency field if operational analysis needs it.

source_version can resolve two updates with the same second. It should come from the authoritative system, not from the retry attempt.

Query the latest known state#

WITH state_events AS (
  SELECT
    JSONExtractString(json, 'account_id') AS account_id,
    JSONExtractString(json, 'state') AS state,
    JSONExtractInt(json, 'source_version') AS source_version,
    timestamp
  FROM billing_events
  WHERE
    JSONExtractString(json, 'event') = 'subscription_state_changed'
    AND JSONExtractString(json, 'account_id') != ''
)
SELECT
  account_id,
  argMax(
    state,
    tuple(timestamp, source_version)
  ) AS current_state,
  max(timestamp) AS last_effective_at
FROM state_events
GROUP BY account_id

Use a deterministic tie-breaker. argMax(state, timestamp) is ambiguous when two updates share one second.

This result is “latest state observed in the retained GraphJSON history,” not necessarily the authoritative current state. Reconcile it with the source system when the distinction matters.

Query state as of an action#

Suppose you need the plan each account had when it published a report:

WITH
  actions AS (
    SELECT
      JSONExtractString(json, 'event_id') AS event_id,
      JSONExtractString(json, 'account_id') AS account_id,
      timestamp AS action_at
    FROM product_events
    WHERE
      JSONExtractString(json, 'event') = 'report_published'
      AND JSONExtractString(json, 'account_id') != ''
  ),
  plan_changes AS (
    SELECT
      JSONExtractString(json, 'account_id') AS account_id,
      JSONExtractString(json, 'plan') AS plan,
      JSONExtractInt(json, 'source_version') AS source_version,
      timestamp AS effective_at
    FROM account_state_events
    WHERE JSONExtractString(json, 'event') = 'account_plan_changed'
  ),
  plan_histories AS (
    SELECT
      account_id,
      arraySort(
        item -> (item.1, item.2),
        groupArray((effective_at, source_version, plan))
      ) AS history
    FROM plan_changes
    GROUP BY account_id
  ),
  actions_with_history AS (
    SELECT
      actions.event_id,
      actions.account_id,
      actions.action_at,
      arrayFilter(
        item -> item.1 <= actions.action_at,
        plan_histories.history
      ) AS eligible_history
    FROM actions
    LEFT JOIN plan_histories
      ON actions.account_id = plan_histories.account_id
  )
SELECT
  event_id,
  account_id,
  action_at,
  if(
    length(eligible_history) = 0,
    'unknown',
    arrayElement(eligible_history, -1).3
  ) AS plan_at_event
FROM actions_with_history

This is a point-in-time lookup: it joins the account on an equality key, keeps only state effective on or before the action, and selects the latest eligible version. The explicit unknown value prevents a missing history from being silently labeled as a real plan.

Precompute or attach the property at ingestion when this join is common and the producer has authoritative state. Large many-to-many joins are expensive and easier to misinterpret.

Use snapshots for stocks#

A stock is measured at an instant:

• active subscriptions
• account balance
• recurring revenue
• inventory on hand
• enabled seats

Recommended monthly recurring-revenue snapshot:

{
  "event": "account_mrr_snapshot",
  "snapshot_at": 1782864000,
  "account_id": "acct_7",
  "mrr_minor": 4900,
  "currency": "usd",
  "billing_model_version": "mrr-v3"
}

Generate snapshots from the authoritative system on a deterministic schedule. Do not reconstruct a month-end stock by summing unrelated page events.

Snapshots answer:

What was the state at each observation?

Transitions answer:

How and why did state change?

Many systems need both.

Do not sum snapshots across time#

This is wrong:

January MRR + February MRR + March MRR = quarterly revenue

MRR is a stock, not collected revenue. Use snapshots for retention and current state; use payment or invoice facts for revenue flows.

For account counts, count distinct entities in each snapshot period. Do not sum the same account across periods unless “account-periods” is the intended unit.

Build reference collections deliberately#

Reference data can describe:

• plan code and display name
• region classification
• feature entitlement
• sales segment
• account size band

One versioned fact:

{
  "event": "plan_definition_published",
  "plan": "pro",
  "definition_version": 4,
  "display_name": "Pro",
  "included_seats": 20,
  "currency": "usd",
  "monthly_price_minor": 4900
}

Put reference facts in a dedicated collection when their retention, owner, or change cadence differs from product events.

Do not join historical events to the latest reference definition when the meaning changed. Carry definition_version or perform an effective-time join.

Model entitlement changes#

An entitlement is often account-scoped and time-varying:

{
  "event": "feature_entitlement_changed",
  "account_id": "acct_7",
  "feature": "scheduled_reports",
  "enabled": true,
  "effective_at": 1785081600,
  "reason": "plan_upgrade",
  "source_version": 92
}

Use entitlement-at-event for adoption denominators. An account that could not use a feature should not count as a non-adopter.

For authorization, always consult the transactional entitlement source. GraphJSON is an analytical copy, not an access-control decision service.

Handle corrections#

Never silently reuse one version number with different meaning.

Choose one:

• emit a later correction transition with a higher source version
• emit an explicit reversal and replacement fact
• rebuild a dedicated derived collection from the authoritative source
• version the query while old and new interpretations coexist

Keep the original event for audit unless your privacy or correction process requires removal. Use Bad-data containment, correction, and replay for a production incident.

Handle late and out-of-order facts#

Always order state by business-effective time plus a stable source sequence. Delivery time can be later because of:

• provider webhook retries
• offline devices
• delayed jobs
• database backfills
• integration reconnects

Monitor:

observed_at - effective_at

When a late state change affects a previously published metric, define whether the metric restates history or freezes a reported period.

Choose current versus event-time semantics#

Put the choice in the metric definition. “Revenue by plan” is incomplete until it says current plan, plan at invoice time, or plan at payment time.

Test known histories#

Seed:

1. account created on Starter
2. report published
3. account upgraded to Pro
4. another report published
5. late-arriving correction to the upgrade time
6. account cancelled

Assert:

• the current state is cancelled
• the first report remains Starter
• the second report follows the corrected effective time
• the monthly snapshot matches the source
• two updates in one second use source version deterministically
• empty and unknown state remain distinct

Production checklist#

• Every changing field has current-state or event-time semantics.
• Important historical segments are attached or joined point in time.
• Transition events include effective time and a stable tie-breaker.
• Stocks use snapshots; flows use occurrence events.
• Snapshot values are not summed across periods accidentally.
• Reference definitions are versioned when meaning changes.
• GraphJSON state is not used for transactional authorization.
• Late-arrival and restatement policies are documented.
• Known lifecycle histories are covered by metric tests.

Continue with Customer health and account scoring, Revenue and subscription analytics, and Test metrics and saved SQL in CI.