---
id: saas/historico-de-estado
industry: SaaS and technology
usage: common
effort: medium
grain: Entity x version (_available_from, _available_until) and entity x day
---

# State history

How every subscription, account, owner and usage indicator looked on any given day, stored before billing, CRM and product overwrite or delete the past.

## Executive summary

**For:** SaaS companies that need to answer how the base looked on a past day: finance, RevOps and CS.

**What it solves**

- MRR, books and status for past months stop changing with today's state.
- Usage and events that the source deletes are kept.
- A sync that fails does not take the history with it.

**Why it matters:** Almost every SaaS number that matters is a comparison with the past: NRR, churn, commission by owner of the month, usage trend. If the source keeps only the present, each of those numbers is rebuilt by hand, and comes out different every time. The past that was not stored does not come back.

**Business questions it answers**

- What was customer X's subscription status on June 30?
- Who owned this account in March?
- Which customers asked to cancel at the end of the cycle and later changed their minds?
- How did this account's usage evolve over the last 12 weeks?
- Since when do we have history for each table?

**What you need**

- Billing, CRM and product connected to Nekt
- SCD Type 2 turned on for tables that change in place
- The list of business fields worth tracking

**What you get**

- Versioned tables with validity start and end
- Daily snapshots of usage and calculated metrics
- A coverage map with the start date of each history
- An AI agent that answers how things looked on a date

## Detailed

### The problem

Billing, CRM and product keep only the current state. The subscription that was past_due yesterday shows as active today, the owner of the deal that closed is now someone else, analytics' 30-day usage is overwritten on every sync, and Stripe events and TTL collections disappear after a few weeks. Without stored history there is no MRR for past months, no commission by owner of the month and no usage trend. And a full sync that fails halfway can wipe out months.

### Business rules

1. **Turn on history for state tables, starting now.** Enable Nekt's SCD Type 2 on tables that change in place: billing subscriptions and items, CRM customers, deals and companies, product accounts and plans. Every change becomes a version with _available_from, _available_until and _deleted. What came before does not come back: for MRR, rebuild from invoices; for owner and stage, from the CRM property history. (Avoid: Calculating the past with today's table.)
2. **A source that deletes needs an accumulator.** Stripe events are available for about 30 to 40 days, MongoDB collections with a TTL delete documents (about 45 days in a real case) and Amplitude charts and metric spreadsheets return only an overwritten 30-day window. Accumulate into your own table by day and id, with merge, reprocessing the last few days on each run so a failure heals itself. (Avoid: Trusting the source to keep event and usage history.)
3. **A snapshot of a calculated number is a notebook, not SQL.** A SQL transformation on Nekt overwrites the whole table on every run. To snapshot daily MRR, books or aggregated usage, use a notebook with mode merge, or a query that reads its own output and keeps all previous days, replacing only the current day. (Avoid: A SQL query that recalculates the whole series from today's state.)
4. **Never overwrite with empty.** Before writing, the routine refuses an empty result or one much smaller than the previous one, unless explicitly allowed. Before a full sync of a large source, take a frozen copy: a full sync that failed halfway once wiped almost five months of events from a table. (Avoid: Writing whatever comes back, even if it is empty.)
5. **A new version is not a business event.** In billing, most new versions only advance the billing cycle or swap the latest invoice. To count a change in status, plan, quantity, price or end-of-cycle cancellation notice (cancel_at_period_end), compare the business field with the previous version and count only when it changed. (Avoid: Counting every new version as a subscription change.)
6. **Day cutoff in the business time zone.** The state on day D is the version valid at D+1 00:00 Brasília time, that is, D+1 03:00 UTC. With several versions on the same day, the last one wins. Run the session in UTC and convert only at the cutoff, once. (Avoid: Cutting the day in UTC, or converting the time zone twice.)
7. **The owner of the month has a cutoff day.** For books, NDR and commissions, the owner in a past month is the owner valid on an agreed day (for example, the 5th, giving the team days 1 to 4 to transfer accounts). Changes after the cutoff are flagged as mid-month transfers, without changing the base owner. The current month uses the latest owner. (Avoid: Assigning the whole history to today's owner.)
8. **CRM history comes from properties.** When SCD was not turned on in time, recover stage, amount and owner from HubSpot's property history (propertiesWithHistory, one entry per change with value, date and source). If the connector does not bring the property you need, a temporary script fetches it from the API, flagged as temporary and with a removal date. (Avoid: Reading only the property's current value.)
9. **Rebuilt is not recorded.** A past rebuilt from invoices, logs or a backfill is flagged with its origin. Models and indicators use only what was available at the end of the previous period (point-in-time). In a real case, contract attributes rebuilt by a backfill contaminated a churn model and had to be removed. (Avoid: Mixing recorded and rebuilt history with no flag.)
10. **A correction is an adjustment, never a rewrite.** A closed month is read from the frozen snapshot. A historical reclassification is done once, documented with date and reason, and is not repeated on every run. A later correction goes in as an adjustment in the month it was found. (Avoid: Recalculating closed months on every run.)

### What to keep and how

Each kind of SaaS data has its own way of not losing the past.

- **Billing subscriptions and items** → SCD Type 2 at the source (`_available_from / _available_until`)
- **Stripe change events (30 to 40 days)** → Accumulator by event id
- **CRM deals, owner and stage** → SCD or property history (`propertiesWithHistory`)
- **30-day usage from analytics or a spreadsheet** → Daily snapshot per account
- **Product collection with a TTL** → Daily merge by day and id
- **Daily MRR, books, score** → Notebook with merge or self-referencing query
- **Large table before a full sync** → Manual frozen copy

### Definitions

| Metric | Definition |
|---|---|
| Current version | row with _available_until null and _deleted false |
| State on date D | version with _available_from up to D+1 03:00 UTC (midnight Brasília time) and _available_until null or later than that instant |
| Daily snapshot | one row per entity and day with the end-of-day state, rewritable only within the declared reprocessing window |
| Business change | version in which a declared field (status, plan, quantity, price, cancel_at_period_end, owner, stage) differs from the previous version |
| Owner of the month | owner valid on the month's cutoff day for closed months; the latest owner for the current month |
| History coverage | date of the first stored version or snapshot of each table; before it, only flagged reconstruction |
| Gap | a day with no stored snapshot in an accumulated source, listed and never filled with zero |

### Pitfalls

- **Months wiped by a full sync.** A full sync of product events failed halfway and wiped almost five months from the table. How to avoid: Frozen copy before any full sync of a large source.
- **Accumulated snapshot overwritten.** One run overwrote the daily snapshot table and three weeks had to be rebuilt by hand. How to avoid: Self-referencing query that only replaces the current day, plus a guard against empty results.
- **No trend in the health score.** The product's conversations collection deleted documents older than 45 days, and the trend component could never be calculated. How to avoid: Daily accumulator per customer from day one.
- **Usage that vanishes the next day.** The metrics spreadsheet only held the last 30 days, overwritten daily; there was no way to tell whether 45% adoption was rising from 20% or falling from 80%. How to avoid: Accumulated weekly or daily snapshot per account.
- **Owner history that never arrived.** The connector did not bring deal owner history, and monthly books ended up with today's owner. How to avoid: Temporary API script, flagged for removal once the connector brings the field.
- **Model trained on the future.** Contract attributes filled by a backfill leaked later information into the training of a churn model. How to avoid: Features only from what existed at the end of the previous period.

Validated on real data: In the history of Nekt's own Stripe subscriptions, about 7 in 10 new versions changed neither status, items nor the cancellation notice: almost all of them only advanced the billing cycle. Only about 2 in 10 changed the status. Counting versions as changes would inflate subscription events more than 3 times.

### Acceptance test

- [ ] For 20 subscriptions, the status on date D read from history matches the invoice or the billing report for that day.
- [ ] Every entity has exactly one current version at any instant, with no overlapping validity.
- [ ] The daily snapshot has one row per entity and day since the first collection, and gaps are listed.
- [ ] Running the routine twice on the same day does not change earlier days' snapshots.
- [ ] A run with an empty result fails without writing.

### Context document

```text
State history
- For any question about the past, use the versioned table or the daily snapshot, never the current-state table.
- State on date D = version with _available_from up to D+1 03:00 UTC and _available_until null or later. Current today = _available_until null and _deleted false.
- A new version is not a business change. Change = status, plan, quantity, price, cancel_at_period_end, owner or stage different from the previous version.
- Owner of the month = owner valid on day <cutoff day> of the month; in the current month, the latest owner.
- Coverage: <table> has history since <date>. Before that, data is rebuilt and flagged; say so in the answer.
- A day with no snapshot is a gap, not zero.
- A closed month comes from the frozen snapshot; do not recalculate.
Client exceptions:
- <client>: deal owner history comes from a temporary script until <date>.
- <client>: one-off reclassification done on <date> for reason <reason>; do not reapply.
```

### Variations

- **Freeze periods:** Customer freezes and deactivations rebuilt as periods (start and end) from automation events, with no overlap.
- **Versioned score:** Lead score and health score stored daily, to measure whether the rule was right without rewriting what the team saw.
- **Basis for forecasting:** Attributes per account and month calculated at the end of the previous month, to predict churn and NRR without leaking the future.