temporal-data

Temporal Data Patterns

This skill covers system time (transaction time) only. For valid time and bitemporal patterns, see the Liftwizard temporal docs and the Klass DSL.

Core Principles

• Immutability: writes are append-only, except for system_to.
• Contiguous timeline: a new row's system_from equals the old row's system_to, forming an unbroken chain of versions per entity.
• Far-future sentinel over NULL: use 9999-12-31 23:59:59 for open-ended records. This allows NOT NULL constraints, composite primary keys, and uniform queries.
• Deduplication: before a phase-out / phase-in, compare incoming data with the current row and leave it untouched if unchanged. Adjacent identical rows produce false changes in history queries and diffs.

Schema Design

Every temporal table has system_from (when this version became current, defaulting to now) and system_to (when it was superseded, defaulting to the far-future sentinel), both NOT NULL.

Primary key on (id, system_to)

This is the Reladomo convention. All current rows share system_to = FAR_FUTURE_DATE, so a bug that creates two current rows for one ID fails immediately with a PK violation. With (id, system_from), that bug only surfaces if both rows have the exact same start timestamp, so it goes undetected.

If both constraints are needed, put the PK on one and a unique index on the other. The (id, system_to) index serves the most common query, finding the current version.

Example DDL

CREATE TABLE nodes (
  id TEXT NOT NULL,
  name TEXT,
  system_from TEXT NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%S', 'now')),
  system_to TEXT NOT NULL DEFAULT '9999-12-31 23:59:59',
  PRIMARY KEY (id, system_to)
);

CREATE INDEX nodes_system_from_idx ON nodes(id, system_from);

Adding Temporal Columns to Existing Table

SQLite requires recreating the table to change the primary key: