What Was True vs. What We Knew: Bitemporal Data for Developers

On October 3, Toche's assistant told a household they owed 4,200,000 pesos on their Visa. By October 9 the correct answer was 3,895,000. Someone asked whether the assistant had been wrong.

The honest answer is "it depends on what you're asking". And if your database can't tell the possible questions apart, it can't answer that one either.

This is the same household and the same debt as in the Keel launch post, with one difference: there the October statement arrived on time, and here it arrives late and with a mistake, the way real statements do. In order:

• September 1. The September debt snapshot arrives: the Visa owes 4,200,000.
• October 1. The bank closes the October statement. The balance that day is different, but nobody in the system knows yet.
• October 3. The household asks the assistant how much they owe. The assistant answers 4,200,000, the only figure it has.
• October 6. The October statement arrives five days late: 3,870,000 as of October 1.
• October 9. The bank reissues the statement. It had left out a 25,000 insurance charge. The correct balance as of October 1 was 3,895,000.

Now ask "how much did we owe on October 3?". There are three answers, and each one is right for its own question:

• 3,895,000 is what was true on October 3, given everything we know today.
• 4,200,000 is what the system believed on October 3, and what the assistant saw when it answered. The figure was wrong, but the assistant wasn't at fault: it answered correctly for what it had.
• 3,870,000 is what you'd have believed on October 7, between the late statement and the correction.

A table with a balance column and an updated_at answers one question only: what we believe now about now. The last UPDATE overwrote everything else.

Two clocks

Every fact has two times, and almost every system keeps only one.

Valid time (world time): when the fact was true in the world. The 3,895,000 balance is valid from October 1, even though nobody knew it until the 9th.

Transaction time (system time): when your system found out. The 3,895,000 was recorded on October 9.

A bitemporal model keeps both. The idea isn't new: SQL:2011 standardized application-time period tables (valid time) and system-versioned tables (transaction time), MariaDB supports both, and databases like XTDB are built around the idea. What is new is how much software now needs the second dimension. An AI agent makes decisions, and when someone audits a decision the question isn't "what was true" but "what did the agent know when it decided".

In practice, every fact carries four times:

System-versioned tables keep one version of the row per change, each with its own transaction interval. If you build it by hand with one row per fact, closed_at is the column people most often forget. Without it you know when each version appeared, but not when you learned the previous one no longer held, and that's exactly what you need to rebuild "what we believed on October 3". Further down you'll see that a single column isn't enough either, if you trust it to read the past.

The three facts, stored

I ran the scenario through Keel, the context layer I released last week, because its store is bitemporal, so I can show you real output instead of made-up tables. There are three events, each with its occurred_at (valid time) and its recorded_at (transaction time). This is the balance history after ingesting them:

value      valid_from   valid_to     recorded_at        closed_at
4,200,000  09-01 08:00  10-01 00:00  09-01 08:00        10-06 10:00
3,870,000  10-01 00:00  10-01 00:00  10-06 10:00        10-09 16:00
3,895,000  10-01 00:00  (open)       10-09 16:00        (open)

There are three things to look at.

Nothing was overwritten. The September balance is still there. It closed at valid_to = October 1 because it stopped being true that day, and its closed_at is October 6 because that's when we found out.

The correction is an empty interval. The 3,870,000 runs from October 1 to October 1. In world time it was never true. But it existed in system time, from October 6 to October 9, and someone could have made a decision with it. This is a design choice in Keel: a change and a correction are stored the same way, as a new row that closes the previous one, and the times are what tell them apart. A correction starts at the same instant as the value it corrects. The classic SQL:2011 model does the opposite: it keeps the wrong row's valid interval and ends its transaction interval instead. Both answer the same questions.

Arrival order doesn't matter. I tried all six orders of the three events and every one produces the same values, the same valid_to and the same closed_at. Further down I explain why that's harder than it looks.

The four queries

With the four times, each question is a different filter:

tools.context("debt:visa", as_of="2026-10-03 12:00 UTC")
# 3,895,000: what was true on October 3, knowing what we know today
 
tools.context("debt:visa", as_of="2026-10-03 12:00 UTC", as_known_at="2026-10-03 12:00 UTC")
# 4,200,000: what we believed on October 3. What the assistant saw
 
tools.context("debt:visa", as_of="2026-10-03 12:00 UTC", as_known_at="2026-10-07 12:00 UTC")
# 3,870,000: what we believed on October 7 about the 3rd
 
tools.context("debt:visa", as_of="2026-09-15 12:00 UTC")
# 4,200,000: what was true on September 15

The valid-time query is the familiar one: valid_from <= t < valid_to. The transaction-time query is the interesting one, and it works in two steps. First you rebuild the world as you saw it at known_at: only the facts recorded by then, each closed only by what you already knew at that moment. Then you apply the valid-time filter. On October 3 the 4,200,000 fact was already recorded and no successor or close was known yet (they arrived on the 6th), so at that moment it was still open, and the as_of filter returns it.

The key phrase is "closed only by what you already knew". Keel's first version implemented it the obvious way, and got it wrong. That story is rule 5.

Five rules I learned by breaking them

1. Close, don't overwrite

An UPDATE destroys exactly the information an audit needs. Every value change is a new row that closes the previous one. If space worries you, store short values and pointers to the system of record, not documents.

2. Transaction time comes from the source, not your ingest clock

Keel set recorded_at to the time it processed the event. That works until you rebuild the graph by replaying the log, which is the promise of any event-based system. On replay, every fact is "recorded" today, and "what did we know on October 3" no longer has an answer. In the Keel paper's benchmark, before this fix, every one of the 900 late-arrival windows injected into one of the projects came back with a wrong answer.

The fix is for the event to declare its recorded_at when the source knows it: the statement arrived on the 6th, even if we process it on the 22nd. If the event doesn't declare it, the ingest time is used, and that's documented as lost information, not as a good default.

One catch: you need a third clock, even though you never query it. Keel keeps changed_at, the real time of each write, because an agent asking "what changed since my last run" needs a cursor that always moves forward, even when an event arrives with an old recorded_at. That clock is pure bookkeeping. If you let it into business queries, you're back to the previous problem.

3. Order by what the fact carries, never by arrival

When a fact arrives, it has to go into its place in the history: close the one current at its valid_from and, if a later one already exists (because it arrived first), close itself where that one starts. The naive version orders by valid_from and breaks ties by arrival order. And ties happen: two changes in the same second, or a correction with the same valid_from as the value it corrects.

In one of the benchmark's real Jira projects, an issue moved to the same status twice in one second. With the log in order, Keel left two open statuses at once, and with the log shuffled it built a different history. The fix was a total order on what the fact carries:

def succession(f):
    # by valid_from; ties by when they were recorded, then by event and value.
    # Never by arrival, so any arrival order builds the same history
    return (f.valid_from, f.recorded_at or valid_never, f.evidence or "", json.dumps(f.value, sort_keys=True))

4. A close is known once you know both facts

What's the closed_at of the September balance? The tempting answer is "the recorded_at of the fact that closes it", and it's the one Keel shipped first. But if the successor was recorded before the predecessor (a late fact about the past came in), you couldn't know the predecessor ended before you knew about it. The close is known at the later of the two recorded_at. The replay test caught this during development: a successor that arrived first gave a different closed_at.

The variant I like best only shows up with three events: a correction arriving before the value it corrects. The system closed the first one correctly, but when a successor recorded earlier at the same instant showed up later, it never revisited that close. With the log in order it never happened. With the benchmark's logs shuffled, which have late arrivals and corrections injected, it hit 0.3% of the facts. Now a test runs those three events in all six possible orders.

5. The stored close belongs to the present; the past one is rebuilt

The review of this very post found this bug, not a test, because none of the tests covered it.

Three balances for the same debt, arriving in this order:

C arrives last, but in world time it falls between A and B. When B arrives, A closes on October 15, and we know it from the 16th. When C arrives, A's close moves to October 1, and we know it from the 20th. A's row has only one valid_to and one closed_at, so the first close gets overwritten. The current history ends up right. Reading the past is where it breaks.

Ask "what did we believe on October 18 about the 17th?". On the 18th we knew A and B, and we knew A ended on the 15th, so the answer is 3,500,000. The first version of as_known_at did the obvious thing:

# the version with the bug
if f.closed_at is not None and f.closed_at > known_at:
    f = replace(f, valid_to=None)  # the close is from the future: at that moment it was still open

A's stored close was learned on the 20th, after the 18th, so the function reopened A. The answer: [4200000, 3500000], two current balances at once, a state the system never actually held. The right close for the 18th (the October 15 one) no longer existed anywhere.

The first fix that comes to mind is to keep a history of closes per row. That doesn't work either: which fact closes which depends on arrival order, so that history would depend on arrival order too. The fix was to stop reading the past close off the row and derive it instead: from the facts recorded by known_at, each one ends where the next one you already knew about starts, or at an explicit close you already knew about, whichever comes first.

def as_known_at(facts, known_at):
    slots = defaultdict(list)  # the facts that close each other: subject, predicate, scope...
    for f in facts:
        if f.recorded_at is None or f.recorded_at <= known_at:
            slots[slot(f)].append(f)
    out = []
    for group in slots.values():
        group.sort(key=succession)
        for f, nxt in zip(group, [*group[1:], None]):
            # it ends where the next one we already knew about starts...
            ends = [(nxt.valid_from, later(f.recorded_at, nxt.recorded_at))] if nxt else []
            # ...or at an explicit close we already knew about, whichever comes first
            if f.valid_to is not None and (f.closed_at is None or f.closed_at <= known_at):
                ends.append((f.valid_to, f.closed_at))
            valid_to, closed_at = min(ends, key=lambda e: e[0], default=(None, None))
            out.append(replace(f, valid_to=valid_to, closed_at=closed_at))
    return out

(Trimmed; Keel's version also groups multi-valued predicates per author.) It's a function of the facts known by then and nothing else, so any arrival order gives the same answer, and no schema change was needed. The lesson is that the stored closed_at is a cache of the present. For questions about the past, the close is rebuilt from what was known then.

The test you have to write

If you implement anything bitemporal, this is the test worth more than all the others combined:

1. Take an event log with late arrivals and corrections. If you don't have any, inject them with a fixed seed, and include one that lands between two existing facts.
2. Build it in order, reversed and shuffled.
3. Compare the full histories, field by field, ignoring nothing except the bookkeeping clock. Sort them by (valid_from, recorded_at) before comparing, because two rows with the same valid_from can come out in a different order even when they're identical.
4. Compare the reads too, not just what's stored: query as_known_at at every instant something was recorded and check that no predicate ever has two open values at once.

Step 4 is the one I was missing. The bugs in rules 3 and 4 lived in what was stored, and step 3 found them. The one in rule 5 lived in a read, on top of a perfectly stored history. Now a conformance test runs that case in all six orders, on every store, reading on three different dates.

When you need it and when you don't

You need it if:

• Data arrives late or gets corrected afterwards: bank statements, reconciliations, integrations with slow systems, anything coming from a third party.
• Someone is going to audit decisions. "Why was this loan approved?" is answered with what was known when it was approved, not with what's known today.
• You have agents that decide. An agent is a decision-maker at scale, and when one gets it wrong, the first question is whether the fault was its own or the data it saw. Without transaction time you can't answer it.
• You need to replay the past for an eval: run an agent "as if it were October 3" without it seeing what happened on the 9th.

You don't need it if:

• You only care about current state and nobody will ask about the past. Most CRUD fits here, and that's fine.
• Valid time is enough for you. If data never arrives late and never gets corrected, a history with valid_from and valid_to is plenty. The second clock only earns its keep when what you knew and what was true can differ.
• You already have an immutable event log and can afford to replay it for every historical query. A bitemporal model is, at bottom, that log already indexed for querying.

What I take away

Toche's assistant wasn't at fault on October 3. It answered with the only thing it knew, and the system should be able to prove it. "What was true" and "what we knew" are different questions. A model that only answers the first turns every late fact into an accusation with no defense.

The idea fits in four columns. Getting it right takes more: a total order, closes that are derived instead of read, and a test that shuffles the log and compares what you read as well. I needed five bugs to learn that, and the review of this post found the last one. If your product has data that arrives late and someone who makes decisions with it, human or agent, you probably need them sooner than you think.

If you want to see the model working, Keel is open source and the bitemporal store lives in keel/store/. And if you want the formal spec, with the queries a store must be able to answer, it's in the SPEC.