You've built an event-sourced system. Every state change is an immutable, appended event. You can replay the log to reconstruct any past state. You feel good about this.
Then someone in payroll, finance, or billing asks:
"A correction was entered in May, backdated to April. What would we have calculated for September, back in February — before the correction existed?"
Your event log has the correction. It has the timestamp of when it happened. But it doesn't have an answer to that question, and no amount of WHERE created_at <= ? filtering gets you there cleanly — because the question isn't "what data existed then," it's "run the entire calculation as if it were still then." That's a different kind of query, and most event-sourced systems don't have a slot for it.
This post describes Tritemporal Event Sourcing — classical Event Sourcing plus bitemporal modeling, plus one more axis that turns "replay the log" into "replay the log from any point of view." There's a companion example repo with runnable TypeScript, C#, and Python implementations if you want to skip ahead to code.
The problem, concretely
Three events, one field:
-
January: an employee's salary is recorded as
5000, effective February. -
May: a correction changes it to
5500, effective April (retroactively). - September: "What would we have calculated for September, using only the information that existed in February?"
As a timeline, with T (when it was recorded) on the bottom axis and V (when it's effective) as the start of each bar:
In February, event 2 hadn't happened yet. A correct answer to question 3 must use 5000 — ignoring a correction that, from February's point of view, doesn't exist.
- A unitemporal system (plain Event Sourcing, or a mutable table with an
updated_atcolumn) only has "now." It sees5500. Wrong answer, and no way to even ask the question. - A bitemporal system (SQL:2011 temporal tables, most "as-of" implementations) can tell you which rows existed as of a past date. That's a data retrieval filter. But "fetch the row that existed in February" and "run the full calculation engine constrained to February's knowledge" are different problems — the former is a
SELECT, the latter requires the calculation logic itself to accept time as an input.
That gap — between filtering data and constraining a computation — is what the third axis closes.
Three axes, one cube
| Axis | Question | Who sets it | Stored? |
|---|---|---|---|
Transaction Time (T) |
When was this fact recorded? | System, automatically | Yes, immutable |
Valid Time (V) |
When is this fact true in the real world? | Business user | Yes |
Evaluation Time (E) |
From which point in time do we observe? | The caller, at query/calculation time | No — a runtime parameter |
T and V are properties of the data. E is a property of the question. That's the whole trick: instead of bolting "as-of" onto queries after the fact, you make the observation point a first-class input to the resolution logic itself — and every downstream calculation inherits it for free.
Don't over-read the cube geometrically, though: a single data element doesn't actually occupy a little box like the one drawn. T is one fixed point (unique per entity and field). V is a genuine interval — a start, and an optional end. E isn't a coordinate of the element at all; once written at T, an event stays visible for every later E — an open-ended ray, not a bounded edge. The cube is a schematic for "three independent axes," not a literal shape.
Every operation you'd normally write bespoke code for turns into a coordinate, or a comparison of coordinates, in this cube:
| Operation | What it is in the cube |
|---|---|
| Current calculation | A point at (T_now, V_now, E_now)
|
| Retroactive query ("what did we know then?") | A slice at a past E, scanning across V
|
| Forecast / what-if | A slice at a future E, scanning across V
|
| Audit reconstruction | A point at a past E and a specific V
|
| Retroactive correction (the delta) | Difference between two slices at different E, same V
|
One resolution rule. No if isRetro then ... else if isForecast then ... branches scattered through your codebase.
The resolution rule
For a field-level value with continuous validity (a salary, a working-hours entry, anything that stays in effect until changed):
value(Vq, E) = the event where
T <= E -- known at evaluation time
AND V_start <= Vq -- effective by the queried date
AND (V_end is unset OR Vq <= V_end) -- not yet expired
ORDER BY V_start DESC, T DESC -- most specific, most recent wins
LIMIT 1
In TypeScript, that's genuinely about 15 lines:
export function resolveTimeData<T>(
events: readonly TimeDataEvent<T>[],
queryDate: number,
evaluationTime: number,
): T | undefined {
const candidates = events.filter(
(e) =>
e.transactionTime <= evaluationTime &&
e.validFrom <= queryDate &&
(e.validTo === undefined || queryDate <= e.validTo),
);
if (candidates.length === 0) return undefined;
candidates.sort((a, b) => b.validFrom - a.validFrom || b.transactionTime - a.transactionTime);
return candidates[0].value;
}
Run the salary scenario through it:
const salary = [
{ transactionTime: date(2026, 1, 15), validFrom: date(2026, 2, 1), value: 5000 },
{ transactionTime: date(2026, 5, 10), validFrom: date(2026, 4, 1), value: 5500 },
];
resolveTimeData(salary, date(2026, 9, 1), date(2026, 9, 30)); // 5500 — current state
resolveTimeData(salary, date(2026, 9, 1), date(2026, 2, 28)); // 5000 — Feb's point of view
resolveTimeData(salary, date(2026, 4, 1), date(2026, 9, 30)); // 5500 — retro-corrected April
Same function, three different questions, zero special-casing. That last line is the retroactive correction from the original scenario — computed, not engineered.
Two kinds of Valid Time
One wrinkle that's easy to gloss over: Valid Time isn't one thing. There are two structurally different sub-types, and conflating them is where a lot of "bitemporal" implementations get subtly wrong:
-
Versioned data — complete rule sets with a
validFrom(tax tables, contribution rates, calculation formulas). Exactly one version applies per period, resolution is stepped:v1 | v2 | v3. - Time data — individual field values with a lifecycle (salary, working hours, date of birth). Values can overlap and get superseded; resolution is continuous.
Both are resolved by the same T <= E knowledge filter, but they answer a different second question — "which version governs this period" vs. "which value is active at this date." This matters most for versioned data, because it produces a non-obvious but critical guarantee:
A retroactive recalculation of January, run in September, must apply January's rates — even if a new rate was published for April, and even if that April rate is now fully "known."
const taxRate = [
{ transactionTime: date(2025, 12, 1), validFrom: date(2026, 1, 1), value: 0.20 },
{ transactionTime: date(2026, 3, 20), validFrom: date(2026, 4, 1), value: 0.22 },
];
resolveVersioned(taxRate, date(2026, 1, 15), date(2026, 9, 1)); // 0.20
E (September) constrains what's known. Vq (January) decides what applies. They're independent filters, and keeping them independent is exactly what makes the mid-year rate change not leak backwards into January's recalculation.
What this buys you
Because E is a genuine input to the calculation engine — not a query filter applied after the fact — three things fall out for free, using the same formula and the same code path:
-
Retro — "what did we know then" and "what do we know now about then" become two evaluations that differ only in
E. The delta between them is the retroactive correction. -
Forecast — set
EandVqto the future; the engine computes directly in the target period. No replay of intermediate periods needed. -
Audit — reproduce exactly what was shown to a user at a specific point in the past, by setting
Eto that moment.
And because business logic (salary * riskBonus, or whatever your formula is) never touches time directly, it's correct for all of these automatically. Time handling lives entirely in the resolution layer.
Don't let E evaporate downstream
E is cheap to get right inside the resolver and easy to lose everywhere else. Once a derived result, an API response, or a cache entry exists, E needs to travel with it — otherwise a downstream service can silently re-evaluate with now, or a cache can return the same business key for two different temporal viewpoints without anyone noticing. Treat (Vq, E) as part of a result's identity, not metadata you drop after the calculation runs.
This matters more, not less, once the caller isn't a human typing a query but an agent calling an API. If a system exposes temporal calculations to an LLM agent, an implicit now default is a silent correctness bug waiting to happen. A cheap fix: echo the resolved coordinates back in the response — "valid on 2026-09-01, evaluated with knowledge as of 2026-02-28" — turning an assumption baked into the request into something reviewable in the response.
A maturity ladder
If you want to place your own system on this spectrum:
| Level | Axes | You get | Typical of |
|---|---|---|---|
| 0 | — | Current state only | Simple CRUD |
| 1 | T |
Audit log, transaction history | Most Event Sourcing, most ERPs |
| 2 |
T + V
|
Bitemporal queries, effective dating | SQL:2011 temporal tables, Datomic |
| 3 |
T + V + E
|
Retro, forecast, and audit through one calculation path | — |
Most systems sit at Level 1. Level 2 is rarer than you'd think outside of specialized temporal databases — most teams simulate V in application code. Level 3 is where the "as-of" filter stops being a query concern and becomes a calculation concern.
Try it yourself
I put together a small, dependency-light reference implementation — same ~80-line core, three languages, same scenarios, runnable tests:
github.com/Giannoudis/tritemporal-event-sourcing-example
- TypeScript / Node —
npm install && npm run demo - C# — core library targets
netstandard2.0(.NET Framework 4.6.1+ through .NET 10+);dotnet run --project Tritemporal.Demo - Python 3.10+ —
python demo.py
Each folder has the same core module, a demo with annotated output, and a test suite. It's deliberately minimal — an in-memory list and O(n) filtering — because the point is to make the resolution rule legible, not to ship a temporal database.
Where this runs in production
The pattern described here isn't a thought experiment — it's the calculation model behind Payroll Engine, an open-source (MIT), .NET-based payroll framework, in production since April 2026 across multi-country regulation coverage. Payroll — with its constant stream of backdated corrections, mid-year rate changes, and "what would the payslip have said" audit questions — turns out to be an unusually good forcing function for getting temporal modeling right.
Further reading
- Fowler, M. (2005). Event Sourcing. martinfowler.com.
- Snodgrass, R.T. (2000). Developing Time-Oriented Database Applications in SQL.
- Johnston, T. & Weis, R. (2010). Managing Time in Relational Databases.
- ISO/IEC 9075-2:2011 (SQL:2011 temporal features).
- Payroll Engine — Time Data concept docs.
Have you run into this "temporal blind spot" in a system you've built — payroll, billing, insurance, anything with retroactive corrections? I'd like to hear how you solved it. Drop a comment below.
Top comments (2)
Making evaluation time a property of the question is a very clean distinction. A practical consequence is that
EandVqneed to travel with every derived result, cache key, and audit receipt. Otherwise a downstream service can silently re-evaluate withnow, or a cache can return the same business key for two different temporal viewpoints. For natural-language or agent-driven queries, I would also make the defaults explicit and echo them in the answer: 'valid on 2026-09-01, evaluated with knowledge as of 2026-02-28.' That turns an easy-to-miss semantic assumption into something reviewable.Exactly right, and it's the part that's easy to get right on paper and then quietly break in the first caching layer. In Payroll Engine, evaluationDate travels explicitly through every call — a first-class parameter on the calculation, not ambient context — precisely so nothing downstream can implicitly substitute now, and result/cache keys include it alongside the queried period.
The agent-driven case is a great addition, and probably more relevant every month. Once a system exposes temporal calculations through an API an LLM agent can call, an implicit "now" default is a silent correctness bug waiting to happen. Echoing "valid on X, evaluated with knowledge as of Y" back in the response is a cheap way to turn that assumption into something reviewable instead of assumed.
Good enough a point that I added a section on it — "Don't let E evaporate downstream," right after "What this buys you." Thanks for pushing on this.