What Did It Cost on March 3rd, and When Did We Know? Bitemporal Modeling for a Service Price Catalog

Two Questions Wearing One Costume

Someone shows up at the counter in mid-March holding a printed quote from the previous week — the sort of figure anyone comparing prices around town collects before deciding. The number on the paper is not the number on the screen. Two explanations exist, and only two. Either the price genuinely changed between then and now, or the number on the paper was wrong when it was printed and has since been corrected. Those are different situations with different remedies, and the person standing there deserves to know which one they are in.

A schema with a single updated_at column cannot tell them. It cannot tell you either. updated_at records that a row was touched. It says nothing about the period during which the value it holds was the truth, and once it has been overwritten it says nothing about what the value used to be. You have thrown away both axes and kept a timestamp that answers neither question.

Here are the two questions, stated precisely, because the whole design falls out of taking them literally:

1. What was the price on March 3rd? This is a question about the world. It has an answer independent of what any database contained.
2. What did our system say the price on March 3rd was, as of March 10th? This is a question about our beliefs. It has a different answer, and on the day of a dispute it is usually the more useful one.

The first axis is called valid time: the period during which a fact was true of reality. The second is transaction time: the period during which our system asserted that fact. Model both and every version of the question above becomes a SELECT. Model one, and you are guessing. Model neither, and you are reconstructing history from Slack messages.

Framing: What Follows Is a Design Exercise

I work with a Calgary tire and oil-change business, a local independent operation with the ordinary pressures of that trade: a violent twice-yearly changeover rush, seasonal price movement, quotes issued days before the work happens, and fleet customers who reconcile invoices at month-end against numbers they were given weeks earlier. Those constraints are real and they are why this modeling problem is interesting to me.

The system described below is a design exercise. It is how I would model a service and price catalogue for a business shaped like that one. There is no deployment behind it, no incident report, no migration, no rollout, no measured adoption. Nothing here is running production infrastructure. The SQL is written against PostgreSQL 14-and-later semantics — multiranges, range_agg, INCLUDE columns on partial indexes — and it is a design sketch to adapt and test in your own instance, not a migration to paste. The scenario around it is a scenario, not a log.

Every dollar figure in this article is illustrative. I picked round numbers that make the interval arithmetic legible on a page. They are not anyone's prices, they are not derived from anyone's prices, and you should not read them as such. The engineering claims are checkable and I checked them. The money is pedagogy.

Valid Time and Transaction Time, Without Hand-Waving

Valid time is a property of the fact. "Mounting and balancing a 17-inch wheel is $45" was true from the first of October until the first of March. That statement is about the world. It remains true whether or not anybody ever wrote it down, and it stays true after the database is dropped.

Transaction time is a property of the record. "Our catalogue asserted that mounting a 17-inch wheel is $45" was true from 14:22 on September 28th until 09:05 on November 2nd, when someone replaced the row. That statement is about us. It is not falsifiable by the world — either we asserted it or we did not — and it is the only axis that gives you an audit trail.

The two come apart constantly, and the gap between them is where all the interesting behaviour lives:

• Future-dated change. On September 28th we record that from October 1st the seasonal changeover rate goes up. Valid time starts after transaction time. For three days the system holds a fact about a future it has not reached.
• Backdated correction. On March 12th we learn the figure we published on March 1st was wrong. Valid time starts before transaction time. For eleven days we asserted something false, and — this is the part that matters — we need to keep asserting that we asserted it.
• Retroactive restatement by a third party. A supplier says a line was mispriced from the first. We did not err; we were fed bad input. Valid time again precedes transaction time, but the story and the remedy both differ.

Any one of these is expressible with a single axis if you squint. All three together are not. The moment you need "what did we believe last Tuesday about last Monday," one axis is provably insufficient — you are asking for a value indexed by two independent coordinates.

Soft Deletes and a Touched-At Column Are Not Version History

The standard evolution of a price table goes like this, and I have watched it happen more than once.

Version one is price with an updated_at. Someone asks what a rate was last month. Nobody can answer.

Version two adds price_history, populated by a trigger. That answers "what did the row look like before the last edit," which is a different question. History tables record edits, not periods. Three edits in one afternoon, two of them typos, produce three rows and no way to tell which two were wrong.

Version three adds effective_date. This is real progress — it is a valid-time lower bound — but there is no upper bound, so "which row applies on March 3rd" becomes ORDER BY effective_date DESC LIMIT 1 with a <= predicate, and that query is a lie the moment somebody inserts a correction with an effective_date in the past. The correction silently changes the answer to historical questions, with no record that the answer used to be different.

Version four adds deleted_at and calls it versioning. It is not. A soft delete is a tombstone with a timestamp; it tells you a row stopped being used, not when the fact stopped being true, and not what replaced it. Two rows both soft-deleted five minutes apart give you no ordering of beliefs, only an ordering of clicks.

The tell that you are in this hole is a support conversation containing the phrase "let me check the audit log." If the audit log is a separate artifact from the data, the data has no history and you are reconstructing it from a side channel nobody tested. History is not a side channel. It is the table.

The Shape of the Table

Here is the core object. It is append-only in the sense that facts are never erased; the only in-place mutation permitted is closing a transaction-time upper bound, and I will argue later that even that should go through one function and nothing else.

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TYPE offering_state AS ENUM ('priced', 'not_offered', 'on_request');

CREATE TABLE service_price (
    row_id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    catalog_key   text          NOT NULL,
    state         offering_state NOT NULL DEFAULT 'priced',
    amount        numeric(10,2),
    currency      char(3)       NOT NULL DEFAULT 'CAD',
    valid_span    daterange     NOT NULL,
    txn_span      tstzrange     NOT NULL,
    authored_by   text          NOT NULL,
    intent        text          NOT NULL,
    note          text,
    CONSTRAINT amount_matches_state CHECK (
        (state = 'priced' AND amount IS NOT NULL)
        OR (state <> 'priced' AND amount IS NULL)
    ),
    CONSTRAINT valid_span_has_start CHECK (NOT lower_inf(valid_span)),
    CONSTRAINT txn_span_has_start   CHECK (NOT lower_inf(txn_span)),
    CONSTRAINT spans_nonempty       CHECK (NOT isempty(valid_span) AND NOT isempty(txn_span))
);

Five decisions in there are worth defending.

valid_span is a daterange, not a tstzrange. Prices in this domain change on business dates, not at 14:07:33. Using a date range removes an entire category of questions — what instant does "starting October 1st" mean, in which zone, and what about the row that starts at midnight — by making them unrepresentable. If you genuinely need intra-day valid time, use tstzrange and read the section on local dates below twice.

txn_span is a tstzrange because transaction time is an instant, always, and it is assigned by the system. Users may specify any valid time they like. Users may never specify transaction time. The moment you let an operator backdate the transaction axis, the audit property is gone and you have an expensive table with none of the benefits.

Catalogue keys deserve a word too. MOUNT_BALANCE:17 bundles two operations — mounting and wheel balancing — under one priced unit, keyed on a rim diameter that comes straight off the markings on the sidewall. Keys should be stable strings that never encode a price, a season, or a supplier, because anything encoded in the key cannot be corrected by the mechanism this whole article is about.

state exists so that absence never has to be interpreted. A missing row and a row saying "we do not price this" are different facts, and I will come back to why.

intent is free text with a house vocabulary — initial, scheduled, correction, supplier_restatement, withdrawal. Some of it is derivable from the two lower bounds, but derivation cannot separate "we typed it wrong" from "we were told wrong," and those land very differently on anyone quoted under the old figure.

Half-Open Intervals and the Tax Closed Ones Charge

Both spans are half-open: [lower, upper). Lower bound included, upper bound excluded. This is not a stylistic preference, it is the only convention that composes.

With half-open intervals, adjacency is equality: the span that ends on April 1st and the span that begins on April 1st are adjacent with no gap and no overlap, and you can test that with upper(a) = lower(b). With closed intervals you write the previous span as ending March 31st, and now adjacency requires you to know the granularity of the type in order to add one to it. That knowledge leaks everywhere. It leaks into application code, into reporting queries, into the CSV export, and into whatever service consumes the CSV export.

The failure mode is specific and I have seen its fingerprints in more than one schema: someone widens a column from date to timestamp, and every "minus one day" in the codebase is now wrong by 86,399 seconds. A hole opens in the history on every boundary day, and the daily revenue query does not error — it returns nothing for that key on that date, and the number is quietly a little low forever.

PostgreSQL helps on one axis and not the other, and the asymmetry catches people. The behaviour below is documented rather than surprising, which somehow makes it easier to be bitten by:

SELECT daterange('2026-03-01', '2026-03-31', '[]') AS canonicalized;
--  canonicalized
-- -------------------------
--  [2026-03-01,2026-04-01)

SELECT tstzrange('2026-04-01 00:00-06', '2026-05-01 00:00-06', '[]') AS untouched;
--  untouched
-- ----------------------------------------------------
--  ["2026-04-01 00:00:00-06","2026-05-01 00:00:00-06"]

daterange is a range over a discrete type, so Postgres canonicalizes every literal to [) regardless of what you wrote. tstzrange is continuous and cannot be canonicalized, so an inclusive upper bound survives — and two "adjacent" tstzranges written with [] will overlap at exactly one instant, which is precisely enough to make an exclusion constraint reject an insert that looks obviously fine to the person writing it. Construct every tstzrange with an explicit '[)' third argument. Every one. Make it a lint rule if you have to.

Unbounded upper bounds are the other half of the convention. A currently-believed row has txn_span with an infinite upper bound, written tstzrange(now(), NULL, '[)'), and a price with no scheduled end has valid_span unbounded above. upper_inf(txn_span) then becomes the definition of "this is a row we currently assert," which is a cheap and very indexable predicate.

Two Truths at One Instant Are a Bug, So Make Them Impossible

The invariant that makes the whole model trustworthy is this: for any catalogue key, any valid date, and any transaction instant, at most one row applies. Enforce it in the database, because enforcing it in the application means enforcing it in every code path anyone will ever add.

ALTER TABLE service_price
  ADD CONSTRAINT service_price_single_truth
  EXCLUDE USING gist (
      catalog_key WITH =,
      valid_span  WITH &&,
      txn_span    WITH &&
  );

Read what that actually forbids, because it is subtler than it looks. It rejects a pair of rows for the same key whose valid spans overlap and whose transaction spans overlap. Overlap on one axis alone is not merely permitted, it is the entire point:

• A correction produces two rows with overlapping — often identical — valid spans. Their transaction spans are disjoint, because the old row's belief window was closed at the instant the new one opened. Allowed.
• A scheduled price change produces two rows with overlapping transaction spans, both currently believed. Their valid spans are disjoint, one ending where the next begins. Allowed.
• Two rows both claiming to be the truth for the same key on the same day at the same instant. Rejected, at the storage layer, with a constraint violation and a row identifier.

The catalog_key WITH = element requires btree_gist, which teaches GiST equality on scalar types. Without the extension the constraint will not build.

One caveat that bites during backfills: the constraint is checked immediately, row by row, as each insert lands. A multi-row rewrite that passes through an intermediate overlapping state fails even when the final state would have been clean. You can declare the constraint DEFERRABLE INITIALLY DEFERRED and push the check to commit, and occasionally that is the pragmatic answer, but I would rather not — a deferred check turns a precise error at the offending statement into a vague one at commit time, and it invites write paths that are only correct in aggregate. Order the writes instead: close before you open, inside one transaction. The intermediate state is then a gap rather than an overlap, which the constraint permits and which no other session observes, because nothing has committed.

The Query Nobody Argues About: Current Truth

Start with the boring one, because it establishes the shape.

SELECT sp.catalog_key,
       sp.state,
       sp.amount
  FROM service_price AS sp
 WHERE sp.catalog_key = 'MOUNT_BALANCE:17'
   AND sp.valid_span @> (now() AT TIME ZONE 'America/Edmonton')::date
   AND upper_inf(sp.txn_span);

Two predicates instead of one: the permanent cost of the model. Every read grows a valid-time containment test and a transaction-time test, and no version of bitemporality makes that go away. The only real question is whether it buys you something.

Note upper_inf(sp.txn_span) rather than sp.txn_span @> now(). Same rows, but the first is a clock-independent boolean over a single row, so it can back a partial index and cannot change answer mid-statement. Save containment for when you genuinely need to move along the transaction axis.

Rewinding the World: As Of a Valid Date

Now ask what was true on the third of March, according to everything we believe today.

WITH probe AS (SELECT DATE '2026-03-03' AS on_day)
SELECT s.amount,
       lower(s.valid_span) AS took_effect,
       upper(s.valid_span) AS superseded
  FROM service_price AS s, probe
 WHERE s.catalog_key = 'MOUNT_BALANCE:17'
   AND s.valid_span @> probe.on_day
   AND upper_inf(s.txn_span);

This is the query most teams think they want, and for reporting it usually is. It reflects corrections: if we discovered on the twelfth that the third was mispriced, this returns the corrected figure. That is right for a revenue restatement and wrong for a dispute, which is the distinction the next two queries exist to draw.

Rewinding the Record: As Of a Belief Instant

Same key, same day, but now pinned to what the system asserted at a particular moment.

SELECT p.amount,
       p.intent,
       lower(p.txn_span) AS asserted_at
  FROM service_price AS p
 WHERE p.catalog_key = 'MOUNT_BALANCE:17'
   AND p.valid_span @> DATE '2026-03-03'
   AND p.txn_span   @> TIMESTAMPTZ '2026-03-10 11:00-06';

If a correction landed on the twelfth, this returns the old figure — the one that was on the screen when someone printed the quote on the tenth. That is the answer to "why does my paper say something different," and it is not reachable from any single-axis schema.

The Full As-Of-As-Of, Which Is What Settles Arguments

Parameterize both coordinates and you have one query that subsumes the previous three.

CREATE FUNCTION price_at(
    p_key   text,
    p_day   date,
    p_asof  timestamptz
) RETURNS TABLE (state offering_state, amount numeric, note text, intent text)
LANGUAGE sql STABLE AS $$
    SELECT r.state, r.amount, r.note, r.intent
      FROM service_price AS r
     WHERE r.catalog_key = p_key
       AND r.valid_span @> p_day
       AND r.txn_span   @> p_asof;
$$;

price_at('MOUNT_BALANCE:17', '2026-03-03', now()) is current belief about the past. price_at('MOUNT_BALANCE:17', '2026-03-03', '2026-03-10 11:00-06') is past belief about the past. price_at('MOUNT_BALANCE:17', '2026-10-01', now()) is current belief about the future, which is exactly how you answer "what will the winter tire changeover rate be in October" without a separate scheduling table.

The function returns at most one row, guaranteed by the exclusion constraint rather than by hope. That guarantee is why I am comfortable putting a TABLE return type on it instead of defensively aggregating.

Recording a Correction Without Erasing What You Believed

Here is where naive implementations break. Somebody discovers that the figure published for March was wrong. The temptation is UPDATE service_price SET amount = ... WHERE .... Do that and you have destroyed the only copy of what the quote on the tenth was based on.

The correct operation has three parts, in one transaction, at one instant:

1. Close the transaction span of every currently-believed row whose valid span intersects the interval being corrected.
2. Insert the corrected row or rows covering the interval.
3. Re-insert the remainders — the parts of the closed rows' valid spans that fall outside the corrected interval — as fresh rows with the new transaction lower bound.

Step three is the one people forget, and forgetting it silently deletes history at the edges. If the old row was valid from January 1st onward and the correction covers only March, you must reassert January-through-February and April-onward, or those periods now have no applicable row at all.

PostgreSQL 14 and later make the remainder computation pleasant, because multiranges support difference:

CREATE FUNCTION restate_price(
    p_key    text,
    p_span   daterange,
    p_state  offering_state,
    p_amount numeric,
    p_intent text,
    p_who    text,
    p_note   text DEFAULT NULL
) RETURNS void
LANGUAGE plpgsql AS $$
DECLARE
    t_now   timestamptz := transaction_timestamp();
    victim  record;
    leftover daterange;
BEGIN
    FOR victim IN
        SELECT * FROM service_price
         WHERE catalog_key = p_key
           AND upper_inf(txn_span)
           AND valid_span && p_span
         FOR UPDATE
    LOOP
        UPDATE service_price
           SET txn_span = tstzrange(lower(txn_span), t_now, '[)')
         WHERE row_id = victim.row_id;

        FOR leftover IN
            SELECT unnest(datemultirange(victim.valid_span) - datemultirange(p_span))
        LOOP
            INSERT INTO service_price
                (catalog_key, state, amount, valid_span, txn_span, authored_by, intent, note)
            VALUES
                (victim.catalog_key, victim.state, victim.amount, leftover,
                 tstzrange(t_now, NULL, '[)'), p_who, 'carried_forward', victim.note);
        END LOOP;
    END LOOP;

    INSERT INTO service_price
        (catalog_key, state, amount, valid_span, txn_span, authored_by, intent, note)
    VALUES
        (p_key, p_state, p_amount, p_span, tstzrange(t_now, NULL, '[)'), p_who, p_intent, p_note);
END;
$$;

transaction_timestamp() is doing critical work here. Close the old span at one instant and open the new one at a slightly later instant — which is exactly what clock_timestamp() hands you — and there exists a real, microseconds-wide instant at which this key has no applicable row. A transaction-time probe landing in that window returns nothing. It will land there roughly never, until the once it does and you lose an afternoon. A single instant for the whole transaction closes the seam by construction.

FOR UPDATE matters too. Two concurrent restatements over overlapping intervals would otherwise read the same open rows, close them, and insert; the exclusion constraint catches the overlap and aborts one, which is safe but produces a baffling error. Locking the victims first turns that into an ordinary serialization wait.

Fixing the Past Versus Scheduling the Future

Both a backdated correction and a future-dated change have lower(valid_span) <> lower(txn_span). The sign of the difference separates them:

SELECT catalog_key,
       intent,
       lower(valid_span) AS effective_from,
       lower(txn_span)::date AS entered_on,
       CASE
         WHEN lower(valid_span) > lower(txn_span)::date THEN 'ahead of record'
         WHEN lower(valid_span) < lower(txn_span)::date THEN 'behind record'
         ELSE 'same day'
       END AS posture
  FROM service_price
 WHERE upper_inf(txn_span)
 ORDER BY lower(txn_span) DESC;

That derivation is fine for a dashboard and insufficient for anything else, which is why intent is stored rather than computed. Take two rows both sitting "behind record" by eleven days. In one, a colleague fat-fingered a figure and we fixed it. In the other, a supplier restated a line and we passed it through. The database sees identical geometry; the business sees two very different conversations with whoever was quoted in between. Geometry does not encode responsibility. Store the reason at write time — you will never recover it later.

Future-dated rows have their own hazard, which is that they are visible. A row with valid_span starting October 1st is in the table in late September, currently believed, and any query that forgets the valid-time predicate will find it. It gets worse with more than one reader. A counter terminal and an online reservation form consume the same rows and will not be updated on the same day. This is the likeliest leak in a bitemporal catalogue: not the exotic queries, but a reporting script that joins on catalog_key and filters only on upper_inf(txn_span) because the author was thinking about "current rows." Current in transaction time is not current in valid time. They are orthogonal, and the vocabulary does not help.

Restatement, and the Quotes Already Out the Door

The supplier says a line was mispriced since the first. You restate. Now: what about the quotes you handed out in between?

This is not a database question, but the database determines whether it is answerable. The rule is that any document you emit which contains a price must persist the coordinates it was priced at, not just the number. Beyond its own identity, a quote line needs three fields:

CREATE TABLE quote_line (
    quote_line_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    quote_id      bigint      NOT NULL,
    catalog_key   text        NOT NULL,
    service_day   date        NOT NULL,
    priced_asof   timestamptz NOT NULL,
    amount_shown  numeric(10,2) NOT NULL
);

service_day is the valid-time coordinate, priced_asof is the transaction-time coordinate, and amount_shown is a denormalized copy so that the document renders identically forever even if someone drops the whole catalogue. With those, "why did we quote this figure" is reconstructible exactly:

SELECT q.quote_id,
       q.amount_shown,
       reconstructed.amount AS believed_then,
       current_view.amount  AS believed_now
  FROM quote_line AS q
  CROSS JOIN LATERAL price_at(q.catalog_key, q.service_day, q.priced_asof) AS reconstructed
  CROSS JOIN LATERAL price_at(q.catalog_key, q.service_day, now())         AS current_view
 WHERE q.quote_id = 4711;

If amount_shown and believed_then disagree, you have a bug in the quoting path and you can prove it. If believed_then and believed_now disagree, you have a restatement and you can list every affected document with one query rather than by memory. For a fleet account reconciling thirty vehicles at month-end, that list is the difference between a five-minute conversation and a spreadsheet argument.

What you then do about those quotes is policy, and the schema should stay out of it. Honour the old figure, reissue, absorb the difference — all defensible. But none of the three is even available if the quote stored a bare number with no coordinates, because you cannot identify which documents were affected.

We Stopped Offering It Versus We Never Offered It

Absence is the most under-modeled concept in catalogues, and bitemporality makes the distinctions crisp instead of ambiguous.

We never offered it. No rows for that key at any coordinate. price_at returns zero rows for every probe. This is the only case where genuine absence is correct.

We stopped offering it as of a date. The world changed. Close the valid span of the priced row at that date and insert a not_offered row valid from that date onward. Historical valid-time queries still return the old price, which is correct — it was offered then. A seasonal mobile service line is exactly this shape — available, then not, then available again — and if it is also priced differently across the areas it covers, each of those spans has its own history. All of them should remain readable rather than being overwritten by whatever is true today.

We never should have listed it. Our belief was wrong, not the world. Close the transaction span with no replacement row. Now current queries find nothing, and — crucially — as-of-valid-time queries also find nothing, because there is no currently-believed row covering that date. But as-of-transaction-time queries still show what we wrongly asserted, which is what you need when someone was quoted from a listing that should never have existed.

Those three collapse into one indistinguishable state under soft deletes. Under two time axes they are three distinct geometries, and you can tell them apart with a query rather than an interview.

The state enum earns its keep here. A not_offered row is a positive assertion of unavailability with an author and a timestamp on it, not a hole for some future reader to interpret. on_request covers the case that always turns up eventually — commercial and oversize work where the figure depends on the vehicle and no catalogue entry would be honest. "We have no price" and "priced individually" are different facts.

A Worked Example, With Deliberately Fake Numbers

Every figure below is invented for the example. Say the key is MOUNT_BALANCE:17 and the timeline runs as follows.

September 28th, 2025, 14:22. We record that from October 1st the rate is $45, unbounded above. One row: valid [2025-10-01, ∞), transaction [2025-09-28 14:22, ∞), intent scheduled.

October 1st, 2025. Nothing happens in the database. The row that was future-dated is now current, purely because the calendar moved. No write, no trigger, no job. This is one of the quiet pleasures of valid-time modeling.

March 1st, 2026, 08:00. We record a spring rate of $39, valid from March 1st. restate_price closes the October row's transaction span, inserts the remainder [2025-10-01, 2026-03-01) at $45, and inserts [2026-03-01, ∞) at $39. Three rows now, two of them currently believed.

March 10th, 11:00. A fleet account asks for a written figure covering a service visit that already happened on March 3rd. The document is generated that morning showing $39, and the line it writes stores service_day = 2026-03-03, priced_asof = 2026-03-10 11:00-06.

March 12th, 16:40. We discover the spring figure should have been $41 all along. restate_price runs again with valid span [2026-03-01, ∞), intent correction.

The table now looks like this. Transaction upper bounds are shown as ∞ where the row is still believed:

row	valid span	transaction span	amount	intent
1	[2025-10-01, ∞)	[2025-09-28 14:22, 2026-03-01 08:00)	45.00	scheduled
2	[2025-10-01, 2026-03-01)	[2026-03-01 08:00, ∞)	45.00	carried_forward
3	[2026-03-01, ∞)	[2026-03-01 08:00, 2026-03-12 16:40)	39.00	scheduled
4	[2026-03-01, ∞)	[2026-03-12 16:40, ∞)	41.00	correction

Four rows where a single-axis schema would have one row and a support conversation. Now run the probes:

• price_at(key, '2026-03-03', now()) hits row 4. Answer: 41.00. What we now believe was true that day.
• price_at(key, '2026-03-03', '2026-03-10 11:00-06') hits row 3. Answer: 39.00. What the printed quote was based on, recoverable two years later.
• price_at(key, '2025-11-15', now()) hits row 2. Answer: 45.00, carried forward untouched through two restatements.
• price_at(key, '2025-11-15', '2026-02-01 09:00-07') hits row 1. Same amount, different row, because in February we believed it via a different assertion.

That last pair is the one that convinces people. The answer is the same but the provenance is not, and only one of the two rows can be cited in a conversation about what the system said in February.

When a Business Date Meets an Absolute Instant

A brief hazard, because it is a genuine trap rather than the subject of this piece.

valid_span is a daterange in the local business calendar. txn_span is a tstzrange, an absolute instant. Mixing them carelessly is how you get a price that takes effect at 5 p.m. the previous afternoon.

The specific failure: CURRENT_DATE in PostgreSQL is evaluated in the session's TimeZone setting. A connection pool that leaves TimeZone at UTC will, every evening after 6 p.m. Mountain Daylight Time, compute tomorrow's date. Your October 1st price goes live on the evening of September 30th for every query issued from that pool. Write the conversion explicitly — (now() AT TIME ZONE 'America/Edmonton')::date — and never rely on session configuration for a business-meaningful boundary.

The write-side rule follows: "effective October 1st" is a local business date and belongs in the table as the date 2026-10-01, not as an instant computed at write time. Conversion to an instant is a rendering concern for the edge, where the asking zone is known. Store the business date; derive the instant.

Indexes, Range Types, and What the Planner Will Actually Do

The exclusion constraint builds a GiST index over (catalog_key, valid_span, txn_span). It will service && and @> queries, and for genuine time-travel it is the right structure. It is not, however, the right structure for the hot path, and assuming it is will make your catalogue reads slower than the single-axis table they replaced.

The hot path is "current price for one key," which is extremely selective on catalog_key and then wants exactly the rows with an infinite transaction upper bound. In a table that only ever grows, currently-believed rows become an ever-shrinking fraction of the whole. That is a textbook partial index:

CREATE INDEX service_price_live
    ON service_price (catalog_key)
    INCLUDE (valid_span, state, amount)
 WHERE upper_inf(txn_span);

Note that valid_span sits in the INCLUDE payload, not in the key. A B-tree cannot answer containment, so putting a range in a key column buys nothing; carrying it as payload lets the containment filter run without a heap fetch. The index stays roughly the size of the live catalogue rather than the size of its entire history, and it does not grow when you restate. Pair it with the upper_inf() form of the predicate in the query — this is why I wrote the current-truth query that way rather than with txn_span @> now().

Three further planner notes worth knowing before you profile:

• now() is STABLE, not VOLATILE, so Postgres evaluates it once per statement and can use it as an index-scan bound. clock_timestamp() is volatile and cannot be used that way. If you sprinkle clock_timestamp() through predicates for "accuracy," you will silently lose index usage on every one of them.
• GiST gives the planner worse selectivity estimates for @> than B-tree does for equality. A query combining a very selective key equality with a range containment is usually better planned as a B-tree lookup plus a filter, which is exactly what the partial index encourages.
• SP-GiST supports range types and often beats GiST on pure containment, but cannot back an exclusion constraint. If time-travel reporting becomes a real workload, a second SP-GiST index on valid_span is worth measuring. Measure it; do not assume it.

One sentence of realism about growth: a few thousand keys restated a handful of times a year stays trivially small for a decade. If your price catalogue needs partitioning, it is probably not a price catalogue.

Snapshot Views That Cannot Quietly Drift

Application developers should not be writing two temporal predicates on every read. Give them a view.

CREATE VIEW catalog_today AS
SELECT v.catalog_key,
       v.state,
       v.amount,
       v.currency,
       lower(v.valid_span) AS in_effect_since,
       upper(v.valid_span) AS in_effect_until
  FROM service_price AS v
 WHERE upper_inf(v.txn_span)
   AND v.valid_span @> (now() AT TIME ZONE 'America/Edmonton')::date;

A plain view is honest by construction: it is the query, so it cannot disagree with the query. Reach for a materialized view only when profiling says so, and when you do, make it self-describing. Carry the instant it was computed from as a column, so any consumer can verify the snapshot rather than trust it:

CREATE MATERIALIZED VIEW catalog_snapshot AS
SELECT now() AS derived_at, c.* FROM catalog_today AS c;

Then a scheduled job can assert equality between the materialized rows and a live recomputation, and fail loudly on mismatch. The failure mode you are protecting against is not staleness in the abstract — it is a materialized view that has silently stopped refreshing while every dashboard built on it continues to render confidently. A snapshot that carries its own derivation coordinate can be checked. One that does not, cannot.

One more trap: a "current price" cache keyed only on catalog_key throws away both axes at the cache layer and reintroduces the original problem a tier up. Cache the result of a coordinate pair, or do not cache.

Property Tests Over Generated Interval Sets

Example-based tests will not find the bugs in this model. The bugs live at boundaries — spans that touch, spans that nest, corrections that exactly cover a previous correction — and hand-written cases cluster around the shapes you already thought of.

Generate operation sequences instead: a random series of asserts, restatements, scheduled changes, and withdrawals against a small key space, with invariants checked after every step.

One craft note matters more than the framework you pick — generate dates from a small pool, not uniformly at random. Draw dates uniformly from a decade and adjacent or identical boundaries essentially never occur, so the boundary bugs this model exists to prevent go unexercised. Twenty or thirty candidate dates makes collisions common. Same for keys: three, not three thousand.

from datetime import date
from hypothesis import given, strategies as st

DAYS = [date(2026, m, d) for m in (1, 3, 4, 10) for d in (1, 15, 28)]
KEYS = ["MOUNT_BALANCE:17", "ROTATE:STD", "REPAIR:PATCH"]

spans = st.lists(st.sampled_from(DAYS), min_size=2, max_size=2, unique=True).map(
    lambda pair: (min(pair), max(pair))
)

ops = st.tuples(
    st.sampled_from(KEYS),
    spans,
    st.sampled_from(["priced", "not_offered"]),
    st.integers(min_value=20, max_value=90),
)

@given(st.lists(ops, min_size=1, max_size=25))
def test_sequence_preserves_invariants(sequence):
    with fresh_schema() as db:
        for key, (lo, hi), state, amount in sequence:
            db.restate(key, lo, hi, state, amount)
            assert_invariants(db)

The three keys differ structurally rather than cosmetically: a bundled operation, a flat-rate service, and a puncture repair — the entry most likely to sit in on_request, because whether a given injury is repairable at all gets decided at the vehicle, not in a price list. Homogeneous keys make for a lazy generator.

Shrinking is the reason to use a property framework rather than a loop with a random seed. When an invariant breaks after nineteen operations, the framework hands you back the minimal three-operation sequence that reproduces it, and that sequence is almost always immediately legible as a design flaw rather than a coding slip.

The Invariants I Would Assert

These are the assertions worth having, roughly in order of how often I would expect each to catch something.

One truth per coordinate. For every key, every date in a probe set, and every distinct transaction lower bound in the table, the count of applicable rows is zero or one. This duplicates the exclusion constraint deliberately — constraints get dropped during migrations and not always restored.

Transaction time is append-only. No row's lower(txn_span) ever changes, and upper(txn_span) goes from infinite to finite exactly once, never back. Diff a table snapshot across each generated operation; any other mutation is a write-path bug.

History is stable. For any instant t and date d, price_at(key, d, t) computed now equals the same probe computed later. This is the property that makes the model worth its cost and it is trivially easy to lose — one stray UPDATE ... SET amount does it. Record every answer as you go, re-check them all at the end.

Valid-time coverage is contiguous. Aggregate the valid spans of all currently-believed rows for a key: SELECT range_agg(valid_span) FROM service_price WHERE catalog_key = $1 AND upper_inf(txn_span). The result must be a multirange with exactly one component. More than one component means a restatement dropped a remainder — that is the step-three bug, and this assertion catches it immediately.

Reversal is not erasure. Apply a correction, then apply a second correction restoring the original figure. Current-truth queries must return the original answer; the row count must have strictly increased; and the intermediate belief must still be reachable by a transaction-time probe. Systems that fail this one usually have an "undo" feature that deletes rows.

Nothing before the beginning. For every key, a probe at a date before the earliest lower(valid_span) returns zero rows in every transaction coordinate. Coverage should be total forward from the first assertion and empty before it — never accidentally unbounded below, which is what happens when someone constructs a range with a NULL lower bound by mistake.

When This Is Too Much Machinery

I would not put this in every schema, and the honest version of this article has to say where the line is.

It is overkill when the data is naturally immutable — an events table already carries transaction time and has no valid time worth modeling. It is overkill when nobody outside the team ever sees a derived figure, since the payoff is defending a number to someone who did not compute it. And it is overkill when corrections are rare enough that a spreadsheet annotation is genuinely fine; I would rather a team admit that than get a temporal model subtly wrong.

The costs compound:

• Every read grows two predicates, and every developer who joins has to learn why.
• ORMs fight you. Most assume a row is a thing rather than an assertion about a thing, and the workarounds are ugly in every framework I have used.
• "Current" becomes ambiguous in conversation. Current in valid time and current in transaction time are different, and people will conflate them verbally even when the code does not.
• Backfilling a single-axis table is hard, because historical transaction time is exactly what the old schema discarded. Seed everything at one instant and be honest that pre-cutover history is unknown; you cannot manufacture it.

Consider uni-temporal first, because it is often enough. Valid time alone handles scheduled changes and gives you the seasonal rate movement story cleanly. Transaction time alone gives you audit. Reach for both only when corrections to the past are a normal part of operations rather than an emergency — and in any business where a supplier can restate a figure after the fact, they are.

Three questions I would use as the test. Can an external party dispute a number you produced? Is a figure ever restated retroactively by someone who is not you? Must you be able to reproduce a decision months later? Two yeses and I would build this. Three and there is no real alternative — a service catalogue where customers receive written quotes, suppliers revise prices after the fact, and commercial invoices get reconciled at month-end hits all three without trying.

What I Would Actually Build First

If I were starting this tomorrow for a catalogue of tire and oil-change services, I would not begin with the full model. I would begin with the daterange valid-time axis and the exclusion constraint, because scheduled seasonal changes are the immediate operational need and half-open intervals are the thing you cannot retrofit cheaply. Getting the interval convention right on day one costs nothing; changing it later means rewriting every boundary in the table.

Then I would add the transaction axis the first time somebody asks a question beginning "what did it say when." That question always arrives, usually within a season. Where a customer is quoted for a set of winter rubber on a Tuesday and arrives the following Monday, or an all-weather line is restated by a supplier mid-month, the gap between what was true and what we said is not an edge case. It is the ordinary weather.

The failure I would most want to avoid is the one that looks like success: a table with valid_from, valid_to, and an updated_at, which appears to be temporal and is not. It answers the world question and silently overwrites the record question, and it does so without ever producing an error. You find out it was wrong when someone is standing there with a printout, which is the worst possible moment to discover that your history is a single mutable column.

Two axes. Half-open intervals on both. One exclusion constraint. One function that writes, and nothing else that does. That is the whole design, and everything above is just the consequences of taking it seriously.