Tier 4 — Bitemporal storage foundations

This tier anchors l2trace’s data-model vocabulary in established temporal-database theory rather than re-coining it. The terminology in CLAUDE.md (valid_during, recorded_during) isn’t novel — it predates l2trace by 25+ years and has a canonical practitioner’s treatment.

Snodgrass 2000 — Developing Time-Oriented Database Applications in SQL

Source. Richard T. Snodgrass. Developing Time-Oriented Database Applications in SQL. Morgan Kaufmann, 2000. 528 pages. Foreword by Jim Gray. Open-access PDF: https://www2.cs.arizona.edu/~rts/tdbbook.pdf.

The canonical SQL practitioner’s treatment of the 1998 temporal-database consensus.

Why this book matters for l2trace

Chapter 2 (“Fundamental Concepts”) contains the formal definitions of valid-time, transaction-time, and bitemporal tables; Chapter 10 (“Bitemporal Tables”) works through a complete schema, modification, query, and integrity treatment using a real-world case (Nykredit Bank, Denmark). l2trace’s mac_observation table is — in Snodgrass’s terminology — a bitemporal state table, and the terminology already used in CLAUDE.md is exactly the 1998 consensus.

Core definitions

[Snodgrass 2000, §2.1–§2.3]:

Valid-time state table [§2.1, p. 12]: “It records information valid at some time in the modeled reality, and it records states, that is, facts that are true over a period of time. The FROM_DATE and TO_DATE columns delimit the period of validity of the information in the row.”

Transaction-time state table [§2.2, p. 18]: “A table that can be reconstructed as of a previous date is termed a transaction-time state table, as it captures the transactions applied to the table. […] rows are logically deleted, because physically deleting old rows would prevent past states from being reconstructed.”

Bitemporal table [§2.3, p. 20]: “Valid time, capturing the history of a changing reality, and transaction time, capturing the sequence of states of a changing table, are orthogonal, and can thus be separately utilized or applied in concert. A table supporting both is termed a bitemporal table.”

Three query classes

[Snodgrass 2000, §2.1.1, p. 13–14]: every nontemporal query over a non-temporal table has three temporal analogs over the corresponding bitemporal table:

Current (“What is now?”) — filter to rows where now BETWEEN FROM_DATE AND TO_DATE. Easy.
Sequenced (“What was, and when?”) — preserves the time dimension in the result. l2trace’s L2-traceroute-at-time-T query is exactly a sequenced query. Snodgrass §6.3 (p. 145) notes these are “prevalent” but “surprisingly arduous” in SQL-92.
Nonsequenced (“What was, at any time?”) — strips the temporal structure, treats timestamps as ordinary columns.

Modifications

[Snodgrass 2000, §2.1.2]: three matching classes — current, sequenced, nonsequenced. l2trace’s reconciler performs primarily sequenced inserts (a new CAM observation arrives with a specific valid-time period) and sequenced retractions (a later source overrides an earlier belief, closing recorded_during).

Nykredit case study

[Snodgrass 2000, §10, p. 277–340]: Danish mortgage bank with 9M loans × 8M customers × 7M properties. Their Prop_Owner table is bitemporal with exactly the schema l2trace uses:

CREATE TABLE Prop_Owner (
    customer_number INT,
    property_number INT,
    VT_Begin  DATE,        -- start of valid time
    VT_End    DATE,        -- end of valid time
    TT_Start  TIMESTAMP,   -- start of transaction time
    TT_Stop   TIMESTAMP    -- end of transaction time
);

The operational workflow Snodgrass describes [§10, p. 278] is the exact pattern l2trace’s debugging workflow inherits:

“IT personnel can first determine when the erroneous data was stored (a transaction time), roll back the table to that point, and look at the valid-time history. They can then determine what the correct valid-time history should be.”

For l2trace, substitute “an operator debugging a misbehaving MAC move”:

When did the system first learn MAC X was at port P?
Was that learned from gNMI or SNMP?
Was that contradicted later by a different source?

Bitemporal storage answers all three questions in a single query. See Why bitemporal? for the full narrative.

Vacuuming and partitioning

[Snodgrass 2000, §9.5, §10.5–§10.6, p. 268–339]: bitemporal tables grow monotonically — every observation becomes permanent history. Snodgrass treats this as a first-class concern:

Vacuuming means physically pruning rows whose recorded_during end-date is older than a retention threshold without losing the valid-time structure of currently-true facts.
Temporal partitioning means physically splitting the table along time boundaries for query performance.

l2trace inherits both concerns without yet implementing either [mac_observation grows monotonically today]. The scale envelope reference discusses where this becomes pressing.

What l2trace borrows

Essentially the entire vocabulary + the table layout. The valid_during TSTZRANGE + recorded_during TSTZRANGE columns are the 1998-consensus formulation; the bitemporal-write-with-late-arrival algorithm is the canonical sequenced retraction. l2trace doesn’t claim novelty in the storage model — its novelty is applying it to L2 forwarding paths.

SQL:2011 temporal features

Status. ISO/IEC 9075:2011 is paywalled. Summarized from authoritative secondary sources without quoting the standard.

What SQL:2011 standardized

Per Kulkarni & Michels, “Temporal Features in SQL:2011,” SIGMOD Record 41(3), 2012. DOI: 10.1145/2380776.2380786.

Application-time period tables — what Snodgrass calls valid-time tables. SQL:2011 syntax: PERIOD FOR business_time (start_col, end_col).
System-versioned tables — what Snodgrass calls transaction-time tables. SQL:2011 syntax: PERIOD FOR SYSTEM_TIME (start_col, end_col) + WITH SYSTEM VERSIONING. The DBMS automatically maintains a history table.
Bitemporal tables — combination of the two; orthogonal periods.
FOR SYSTEM_TIME AS OF clause — point-in-time queries on system-versioned tables.

Why l2trace doesn’t use the SQL:2011 syntax

PostgreSQL (l2trace’s chosen DBMS) does not implement SQL:2011 temporal features natively. Its tstzrange + btree_gist + exclusion constraints + range-overlap operators provide a more flexible alternative. l2trace’s exclusion constraint:

EXCLUDE USING gist (
    mac WITH =, device_id WITH =, vlan WITH =, source WITH =,
    valid_during WITH &&
) WHERE (upper_inf(recorded_during))

…is doing the work that PERIOD FOR business_time + a UNIQUE constraint would do in standards-compliant SQL:2011. PostgreSQL’s approach is more expressive: the WHERE upper_inf(recorded_during) clause lets us scope the constraint to “currently believed” rows, which SQL:2011 doesn’t support cleanly.

The trade-off: l2trace’s schema is not portable between DBMSes the way a SQL:2011 schema would be (in principle — vendor support for the temporal features is uneven). For a single-DBMS project, the expressive gain wins.

Jensen/Snodgrass 1998 Consensus Glossary

The 1998 consensus terminology was originally published as a chapter in Temporal Databases — Research and Practice (LNCS 1399, Springer 1998). The glossary content is woven into Snodgrass 2000 Chapter 2 and the book’s own glossary (p. 471 onward). For citation purposes, Snodgrass 2000 subsumes the 1998 glossary as a stand-in canonical source.