Modeling Validity Over Time and Retroactive Correction

A customer moves in March. In June, the billing system is asked which tax jurisdiction applied to an invoice issued in February. The customer’s current address is the wrong answer, and so is the address on file in June if the move was recorded late. Two different questions hide behind the word “then”: where the customer actually lived in February, and what the system believed in February. A model that stores one address cannot answer either question after the fact.

Two axes of time

The distinction is between valid time and transaction time. Valid time is the period during which a fact is true in the world: the customer lived at the old address from the day the lease began until the day it ended. Transaction time is the period during which the database held that fact: the row was inserted on a given date and superseded on another.

Both are intervals, and they answer different questions. “Where did the customer live in February?” is a valid-time query. “What did the system think in February?” is a transaction-time query. “What did the system believe in June about February?” is both, and it is the query that matters during an audit or a dispute.

Systems that store only the current value can answer neither retroactively. Systems that store valid time but overwrite on correction can answer the first question only if the correction was timely. Systems that store both can answer any of them, at the cost of a row per version and a query that has to state which axis it means.

Intervals and why they end where they begin

A validity record carries valid_from and valid_to, and the convention that removes most off-by-one errors is to make the interval half-open: it includes valid_from and excludes valid_to. A record with [2020-01-01, 2021-01-01) covers exactly one year, and two adjacent records meet without overlapping or leaving a gap. With closed intervals, an address valid through December 31 and the next valid from January 1 overlap on no day but require care at every boundary; with open timestamps, the successor’s valid_from equals the predecessor’s valid_to, which is a single equality that can be enforced.

The open-ended current record needs a sentinel. Using NULL for valid_to is common and convenient in application code, but it complicates range predicates and exclusion constraints, because NULL never compares equal to anything. The alternative is a far-future timestamp such as 9999-12-31, which sorts correctly and participates in range comparisons. Both work; the choice must be consistent across every query, because a mix produces records that are invisible to one predicate and visible to another.

Whether to use date or timestamptz is a modelling decision, not a storage preference. Facts that change at the granularity of a day — tax residency, address, employment — should use dates, because a timestamp invites the question of which time zone defined the instant. Facts that change at an instant — a price change effective at the start of a trading session, a rate change — need a timestamp with an explicit zone, and the zone is part of the fact.

Asking the question as of a date

An as-of query selects the record whose interval contains the date, for each subject. The predicate is two comparisons and no ordering assumptions:

SELECT c.id, c.address
  FROM customer_address AS c
 WHERE c.customer_id = $1
   AND c.valid_from <= $2
   AND c.valid_to   >  $2;

With well-formed, non-overlapping intervals, this returns at most one row. Without that guarantee, it can return several, and the application must then decide which wins — a decision that will be made differently in different code paths and will eventually disagree with itself.

The same shape answers a range query: records that overlap a reporting period. valid_from < period_end AND valid_to > period_start finds every record that was true at any point in the period, which is what a “list all addresses during the year” report needs. That predicate cannot use a simple B-tree index efficiently on both columns; a GiST or SP-GiST index on the interval is the usual fix, and the cost of the index is paid on every write.

When both axes are stored, the query grows a second pair of comparisons, and the semantics have to be stated precisely: to reconstruct what the system believed at transaction time T about valid date D, select rows whose transaction interval contains T and whose valid interval contains D. Confusing the axes produces answers that look plausible and are wrong in exactly the cases where the distinction matters.

Corrections that arrive late

A retroactive correction is a new transaction-time record that asserts a different valid-time fact. If the customer’s move was recorded on 10 June but took effect on 1 March, the correction closes the old row’s valid interval at 1 March and opens a new row from 1 March, both stamped with a transaction time of 10 June.

The old row is not deleted. It remains the record of what the system believed before the correction, which is what an audit needs. The consequence is that the table accumulates versions of the same valid period, distinguished only by transaction time, and every query must specify which axis it is filtering on. A query that omits both filters returns duplicates that look like data corruption.

Corrections also break derived state. Invoices calculated on the old belief about tax jurisdiction are now wrong, and there are three possible responses: leave them, because they were correct when issued; recalculate and issue adjustments; or recalculate and rewrite, which destroys the audit trail. The choice is a business decision that the model must represent, usually as an explicit superseded_by relationship or an adjustment document, rather than as an UPDATE.

The same problem appears one level up, in aggregates. If an aggregate’s current state was derived from facts that have since been corrected, the aggregate is stale in a way that no version counter detects, because nothing about the aggregate changed. Rebuilding it from corrected facts is a separate operation, and in an event-sourced system it means replaying events whose payloads were themselves corrected — which is why event payloads that embed derived values age badly. The sequencing required to correct stored facts while the system keeps serving is described in Evolving Persistent Domain Models.

Enforcing non-overlap in the store

Application-level checks are insufficient here, because two concurrent transactions can each read a table with no overlap and each insert a row that overlaps the other. The guarantee has to come from the database.

PostgreSQL supports exclusion constraints that express exactly this rule over a range type:

CREATE EXTENSION IF NOT EXISTS btree_gist;

ALTER TABLE customer_address
  ADD CONSTRAINT no_overlapping_addresses
  EXCLUDE USING gist (
    customer_id WITH =,
    daterange(valid_from, valid_to, '[)') WITH &&
  );

The constraint says that for the same customer, no two date ranges may overlap, with the range constructed as half-open. Violations surface as a constraint error at commit, which is the correct place: the conflicting writer learns immediately, and the reader never sees an overlapping pair. Databases without exclusion constraints can approximate the rule with a unique index on (customer_id, valid_from) plus a trigger, or with application locking on the customer row, both of which are more fragile.

Gaps are a separate question. The constraint forbids overlap and permits a gap, and whether a gap is legal depends on the domain: an address history with a three-day hole is probably an error, while an employment record with a gap between two jobs is normal. If gaps are illegal, the model must state it, usually by requiring that each insert either extends the latest interval or closes it, and by validating that the first interval starts at a defined origin.

Temporal modelling interacts badly with soft deletes. A deleted_at column is a transaction-time fact pretending to be a valid-time one, and it cannot express “this record was removed from the system but was true in the world”. Representing removal properly means closing the transaction interval without closing the valid interval, which is a two-axis design and not a nullable column.

The machinery described here is what makes a historical answer trustworthy: half-open intervals so adjacency is an equality, a constraint so overlap is impossible, two axes so belief and fact stay separate, and an explicit version relationship so a correction is recorded rather than erased. The event log in Domain Events and State Transitions provides transaction time for free, but valid time is never free — it has to be captured from the domain, and it is the part that people forget.