Skip to content
LLD Learn/Distributed & Large-Scale Modeling
Browsing as a guest. Sign in to save your progress and earn XP as you complete chapters.

Bi-Temporal & Depletion-Aware Modeling

4 min read

You'll learn to

  • -Tell valid time (when it was true) from transaction time (when the system learned it)
  • -Answer both "what did we believe then?" and "what was really true then?" from the same table
  • -Model a resource that gets used up as a ledger of changes, and read its level at any past moment

Bi-temporal modeling tracks two independent timelines for the same fact. Valid time is when the fact was actually true in the real world. Transaction time is when the system recorded or learned it. The two sound alike but are different, and mixing them up is a common and costly schema mistake.

An Analogy: The Newspaper Correction

A newspaper prints on Tuesday that a shop opened on Monday. On Friday it prints a correction: the shop actually opened on Sunday. Two different questions now have different answers. "What did the paper say on Wednesday about the opening?" is Monday. "When did the shop really open?" is Sunday. Both are legitimate, and you can answer both only if you kept both the dates and the original and corrected printings.

Why Two Timelines, Not One

Take a cryo-sleep chamber whose record said "occupied" from January 1. On March 15 the crew finds a logging error: the chamber was actually empty from January 1. The valid time of "empty" is January 1, when it was true. Its transaction time is March 15, when the system learned it. A single timestamp column cannot hold both.

Four timestamp columns, capturing two independent timelines
CREATE TABLE cryo_sleep_status (
    id INTEGER PRIMARY KEY,
    chamber_id INTEGER NOT NULL,
    occupant_id INTEGER,
    status TEXT NOT NULL,               -- 'occupied' or 'empty'
    valid_from TIMESTAMP NOT NULL,      -- when this was actually true in reality
    valid_to TIMESTAMP,                 -- NULL = still true; else when it stopped being true
    recorded_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,  -- when the SYSTEM learned this
    superseded_at TIMESTAMP             -- when a later correction replaced this record
);
-- The corrected row: valid_from = Jan 1 (when reality changed),
-- but recorded_at = Mar 15 (when the correction was actually entered) -
-- both timestamps are real, and both answer different, legitimate questions.

Run the demo on those two rows. It asks the same chamber two kinds of question. "What did we believe?" looks only at rows the system knew about on that date and had not yet replaced. "What is actually true?" uses the latest knowledge.

Press Run: on Feb 1 we believed 'occupied', but we now know it was empty

A schema with one timestamp can answer only one of these questions, and would silently answer the wrong one for the other.

Depletion-Aware Modeling: Resources That Get Used Up

A resource system for fuel, oxygen or food that keeps a single "amount remaining" column has the problem the event-sourcing chapter described for exchange rates. It knows how much is left now, but not how much was allocated on a given date, or the balance right after. The fix is to record each use or resupply as its own append-only row, and work out the current level as a running sum.

Depletion as an append-only ledger, not a single decrementing column
CREATE TABLE fuel_transactions (
    id INTEGER PRIMARY KEY,
    resource_type TEXT NOT NULL,        -- 'fuel', 'oxygen'
    delta DECIMAL(10, 2) NOT NULL,      -- negative for consumption, positive for resupply
    reason TEXT NOT NULL,
    recorded_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
-- current remaining fuel is a running sum, not a stored, separately-mutated value:
SELECT SUM(delta) FROM fuel_transactions WHERE resource_type = 'fuel';
-- fuel level as of any specific past moment:
SELECT SUM(delta) FROM fuel_transactions WHERE resource_type = 'fuel' AND recorded_at <= :as_of_time;
Press Run: the fuel level now, and at two moments in the past

This is the same ledger technique as the double-entry chapter, applied to physical resources instead of money. Once you recognize "something that changes over time and needs historical accountability", the same pattern fits.

Interview Signal is part of Pro

See a real weak answer next to a real strong one for this exact topic.

Quiz is part of Pro

Test what you just read with a short quiz, and bank the XP.

Ready to Build This?

Design the Cryo-Sleep Registry and Resource Allocation schemas in the LLD Lab's Interstellar act.

Discussion0

Join the Discussion

Sign in to leave comments, reply to others, or like insights.

Sign In to ScaleDojo

No comments yet. Be the first to start the thread!