Point-in-Time Reports

Here is a bug that does not look like a bug. You build a weekly report. It reads from the database and shows what’s going on. It’s correct. Everyone’s happy.

Then someone opens last week’s report. Or last quarter’s. And it has changed. The initiative that was in Block 1 back then now shows Block 3, because it moved. The request that was in progress that week now shows done. The release that shipped last month is marked done for every week, including the weeks before it existed.

The report isn’t wrong about today. It’s wrong about the past, and it rewrites the past every time you reopen it. A report you can’t trust to say the same thing twice isn’t a report. It’s a live query wearing a date picker.

This is the exact problem we hit with Glide’s weekly report. This doc is how we fixed it properly, and the pattern generalizes to any report, dashboard, or “as of” view in any project.

The tempting shortcut, and why it’s a trap#

The obvious fix is a snapshot table: at the end of each week, a job runs, computes the report, and stores it. Reopen an old week? Read the stored snapshot. Immutable. Done.

It’s tempting because it’s easy. It’s a trap because:

• It only works from the day you turn it on. Every week before the first snapshot is gone forever. You cannot reconstruct last year’s W29 from a job you started this month.
• It stores derived data. The snapshot is a computed view. The moment the report’s shape changes (a new field, a new grouping), your old snapshots are stale, in the old shape, and can’t be regenerated.
• It answers exactly one question. You snapshotted the report. Ask a slightly different question about the past (“who owned this initiative in March?”) and the snapshot has nothing.

A snapshot freezes an answer. What you actually want is to freeze the facts, and compute any answer from them, for any past instant, forever.

The principle: store events, derive state#

The database row tells you what a thing is right now. That’s a projection of everything that ever happened to it: you’ve just thrown away the history and kept the final frame.

Keep the history. Record every change as an immutable, timestamped event:

At 14:32:07 on Tuesday, initiative GLIDE-48’s block_id changed from Block 1 to Block 3.

Once you have the full stream of events, the value of any field as of any instant T is a pure function of the log:

the value carried by the most recent change with changed_at < T.

Reopen W29 today, or in ten years: same events, same instant, same answer. The report stops drifting because it stops reading now and starts reading then. This is the same move event sourcing and bitemporal databases make; you don’t need the full machinery to get the benefit.

Glide already worked this way in two places without us noticing it was a pattern: a request carries submitted_at / started_at / resolved_at, so its status as of any week is derivable from timestamps; a release got a completed_at so “was it shipped yet, that week?” has an answer. The insight was to name the pattern and extend it to the two fields still read live: an initiative’s block and status.

The design we shipped#

1. One general change-log, not a table per feature#

We already had a status_changes table (built for request status). Rather than add a third audit table, we generalized it into a system-wide field-change log:

status_changes
  entity_type   -- 'initiative' | 'request' | ...
  entity_id
  field         -- 'block_id' | 'status' | 'quarter' | 'dri' | ...
  from_value    -- text, nullable
  to_value      -- text
  changed_at    -- timestamptz  ← the instant, precise
  changed_by    -- nullable (genesis/system rows have no human actor)
index (entity_type, entity_id, field, changed_at)

One row per field change. The index is the whole game: (entity, field, changed_at) makes “latest value before T” a single indexed lookup. Any entity, any field, can adopt it, so the next feature that needs history doesn’t build its own thing.

2. Record at a single write choke-point#

Every write is a chance to forget to log. So log in one place. In Glide, an initiative’s block and status only change through a couple of tRPC procedures, and the pre-change value is still in scope there, so we can record a clean from → to. Recording is fire-and-forget: the log is an observer, never in the critical path of the write, and never able to fail the user’s action.

The rule: if you find yourself writing “and also record a change” in two places for the same field, collapse them first. Scattered logging is how a log develops holes, and a log with holes silently lies.

3. Backfill honestly, and say so#

Turning this on doesn’t give you a past you didn’t record. For every existing initiative we inserted a genesis row (its current value, stamped at its created_at) so an as-of lookup always resolves to something. But that something, for weeks before we started tracking, is the current value, not the true historical one. We couldn’t invent history that was never written down.

That’s a real limitation, so we wrote it into the migration comment and the docs rather than letting it read as fidelity it doesn’t have. An approximation you’ve labeled is a footnote; an approximation you’ve hidden is a bug waiting to be discovered by the person who trusts it.

4. One boundary, applied to everything#

The subtle killer of a point-in-time view is an inconsistent boundary. If requests freeze at end-of-Sunday but block assignments freeze at “now,” the report is internally incoherent: a snapshot of no single moment.

So the whole report resolves to one instant (the exact end-of-second of the selected week), and every dimension uses it with the same comparison: request status, release progress, block, status, and existence. A change exactly at the boundary is not yet applied; an entity created at or after the boundary does not appear. Pick the operator once (< windowEnd), use it everywhere.

5. Gate existence, not just values#

Reconstructing a value isn’t enough; you also have to reconstruct what existed. A release created after the report week must not appear in that week’s report at all, or it’ll show up as “next up” in a week before anyone had thought of it. Gate every entity by created_at < windowEnd. The past should contain only what the past contained.

The distinction worth stealing: audit log ≠ event log#

We had a second audit table (the People module’s audit_logs), and it was tempting to force everything through one. We didn’t, because they do different jobs:

Reconstructing a value from a diff log means replaying diffs; reconstructing it from a typed change-log is one indexed row. Conflating “compliance trail” with “temporal state” gives you a table that’s mediocre at both. Two small, sharp tools beat one blunt one.

The lessons, portable to any project#

1. Store events; derive state. The row is the latest frame; keep the film.
2. Reconstruct, don’t snapshot. A snapshot table freezes one answer from the day you switch it on. An event log answers any question about any past instant, retroactively.
3. One instant, one operator, everywhere. A point-in-time view is only consistent if every field freezes at the same moment the same way.
4. Gate existence, not just values. The past contained only what existed then.
5. Backfill honestly and label the approximation. You can’t record a past you didn’t write down; say so where the reader will see it.
6. One general log beats a table per feature. Make the next feature’s history free.
7. Know which log you’re building. Accountability (“who did it”) and temporal state (“what was it”) are different problems.

The cost, named#

Reconstruction isn’t free: instead of reading a column, you read a field’s change history and fold it. At Glide’s scale (hundreds of initiatives) that’s nothing. If a log grows to where the fold hurts, the fixes are ordinary: index it, cap the window, or cache a periodic materialized state derived from the log (a snapshot as an optimization, never as the source of truth). The events stay canonical; everything else is a projection you can rebuild. That’s the property the snapshot shortcut never had, and the reason it’s worth the extra table.