Bi-Temporal & Depletion-Aware Modeling
You'll learn to
- -Track both when a fact was true (valid time) and when the system learned it (transaction time) in a bi-temporal schema
- -Model a depleting resource (fuel, inventory) so historical allocation decisions remain queryable after the resource changes
Bi-temporal modeling tracks two independent timelines for the same fact: valid time (when the fact was actually true in the real world) and transaction time (when the system recorded or learned about it) - two questions that sound similar but are genuinely different, and conflating them is a common, costly schema-design mistake.
Why Two Timelines, Not One
Consider a cryo-sleep chamber whose occupant status is corrected after the fact: the record said "occupied" starting January 1st, but on March 15th, the crew discovers a logging error - the chamber was actually empty starting January 1st, not occupied. The valid time for "empty" is January 1st (when it was actually true). The transaction time for that fact is March 15th (when the system learned it). A single timestamp column cannot represent both of these different, independently meaningful moments at once.
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."What did we believe on February 1st" (query by recorded_at/superseded_at) and "what was actually true on February 1st" (query by valid_from/valid_to) are two different, both-legitimate questions this structure can answer - a single-timestamp schema can only ever answer one of them, and would silently answer the wrong one for any query about the other.
Depletion-Aware Modeling: Resources That Get Consumed
A resource allocation system (fuel, oxygen, food) tracking a single "current amount remaining" column has the same problem the event-sourcing chapter identified for exchange rates: it can answer "how much is left now" but not "how much was allocated to Project X on a specific date, and what was the running balance immediately after." Modeling each allocation or consumption as its own append-only event, with the current amount derived as a running sum, restores that historical queryability.
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;This is the same ledger technique from the double-entry chapter, applied to physical resources instead of money - a strong sign of how transferable that pattern is once you recognize "something that changes over time and needs historical accountability" as the shared underlying shape.
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.
Design the Cryo-Sleep Registry and Resource Allocation schemas in the LLD Lab's Interstellar act.