Bi-temporal query reference#

Relata is bi-temporal by default. Every row and every edge carries four timestamps — two for the real-world validity period, two for the database's knowledge period. This page covers the SQL surface, the index path, and the common query patterns.

The four timestamps#

The pair (valid_from, valid_to) is the valid time axis (business reality). The pair (system_from, system_to) is the system time axis (database knowledge). The four together give a 2-D point in time-travel space.

The pair is half-open: [valid_from, valid_to) — the row is visible at valid_from inclusive, invisible at valid_to exclusive.

SQL surface#

AS OF — time travel#

Point-in-time query on either axis. The bare form AS OF '<ts>' selects the valid-time axis; AS OF SYSTEM TIME '<ts>' selects the system-time axis; AS OF CURRENT is sugar for both axes at NOW.

-- What did we know about Alice as of 2024-06-01 (system time)?
SELECT * FROM Person AS OF SYSTEM TIME '2024-06-01T00:00:00Z' WHERE id = 'p1'
 
-- What was true in the real world as of 2024-06-01 (valid time)?
SELECT * FROM Person AS OF '2024-06-01T00:00:00Z' WHERE id = 'p1'

A single AS OF clause addresses one axis at a time — Relata does not accept two consecutive AS OF clauses in one statement. To combine valid- and system-time filters, write the predicates out explicitly (e.g. WHERE valid_from <= ts AND system_from <= ts).

WITH PROVENANCE — show the source#

SELECT id, name
  FROM Person
  WHERE id = 'p1'
  WITH PROVENANCE

Returns the standard columns plus a parallel provenance array (one entry per row) carrying source (the ingest batch / API call that produced the row), method, confidence, recorded_at, and derived_from (hex ProvenanceRef of the source row this one was derived from, or null for genesis).

EXPLAIN_REPLAY — re-derive an exhibit seal#

EXPLAIN_REPLAY('<exhibit-id>', SEQ => <n>)

Re-derives a logged exhibit link's seal byte-identically. To diff two points in time, query the audit log or compare two AS OF snapshots.

How AS OF works#

The bi-temporal model is enforced by BiTemporalRange on every row. The executor's scan_as_of filters in-memory by visible_as_of(&r.temporal, valid_t, system_t).

For spilled (on-disk) segments, zone maps and per-column bloom filters prune segments pre-decode — segments whose valid_from range doesn't overlap the queried timestamp are skipped entirely.

Common patterns#

"What did we know at time T?"#

SELECT * FROM Person AS OF SYSTEM TIME '2024-06-01T00:00:00Z'

Equivalent to: system_from <= T AND system_to > T.

"What was true at time T?"#

SELECT * FROM Person AS OF '2024-06-01T00:00:00Z'

Equivalent to: valid_from <= T AND valid_to > T.

"When did Alice's email change?"#

SELECT valid_from, email
  FROM Person AS OF CURRENT
  WHERE id = 'p1'
  ORDER BY valid_from

AS OF CURRENT returns every version the database currently knows about (all rows whose system_to = i64::MAX); it is sugar for AS OF <now_ns> on both axes.

"Show me the history of Alice"#

SELECT valid_from, valid_to, system_from, email
  FROM Person
  WHERE id = 'p1'
  ORDER BY system_from

Without AS OF, every version is returned (including superseded ones). This is the full audit trail for the row.

"Restore Alice's email as it was on June 1"#

-- Read the system-time value at T:
SELECT email FROM Person
  AS OF SYSTEM TIME '2024-06-01T00:00:00Z'
  WHERE id = 'p1'
 
-- Write it back as a new version (the old version is not modified — bi-temporal
-- is append-only):
INSERT INTO Person (_pk, id, email, valid_from)
  VALUES ('p1-v2', 'p1', '<old-email>', '2024-07-01T00:00:00Z')

"What changed in the last hour?"#

SELECT id, system_from
  FROM Person
  WHERE system_from > NOW() - INTERVAL '1 hour'
  ORDER BY system_from DESC

Or use the audit log directly:

curl -H "Authorization: Bearer $RELATA_TOKEN" \
  "http://localhost:9090/audit/entries?since=3600&object_type=Person" | jq

Temporal graph queries (edges are bi-temporal too)#

-- Who did Alice know? (PATHS_BETWEEN accepts from_id, to_id, MAX_HOPS => n only —
-- to filter by time, scope the underlying edge rows via their valid_from/system_from.)
PURPOSE 'investigation' SELECT * FROM PATHS_BETWEEN('p1', '?', MAX_HOPS => 3)

Edges carry the same four timestamps as rows, so an AS OF snapshot of the link types feeds the path expansion; PATHS_BETWEEN itself does not accept an AS_OF parameter.

Indexes#

The bi-temporal index is the range index (BTreeMap<RangeKey, BTreeSet<RowId>>) on (valid_from, system_from). Range queries on either axis use this index.

Equality on id uses the live-index (HashMap<RowId, RowLoc>) for O(1) point lookup at the latest version.

Performance notes#

• AS OF CURRENT is the fastest path — same as a regular scan with system_to = i64::MAX.
• AS OF '<past-timestamp>' walks the range index; on spilled segments, the zone map prunes non-overlapping segments.
• For temporal joins (Person AS OF T1 JOIN CdrRecord AS OF T2), use the same timestamp for both axes to avoid pinning two different snapshots.

Bi-temporal model gotchas#

valid_to = i64::MAX means "still true"#

Don't write WHERE valid_to IS NULL — there is no null. Use WHERE valid_to = 9223372036854775807 or use AS OF CURRENT which is the intended API.

Updates are append-only#

Updating a row closes the old version (sets system_to = NOW) and inserts a new version (system_from = NOW). The old version is preserved for audit. This is why a single id can have many rows in the store.

Deletes are soft#

DELETE sets valid_to = NOW on the matching rows; the rows themselves stay in the store. Hard deletes (FORGET) are a separate GDPR-style operation (POST /memory/forget/:id or DELETE /types/:name/:id?hard=true).

Compaction preserves history#

Compaction merges adjacent versions where safe but never drops a version that still has system_to = i64::MAX (live data) or that falls inside any configured retention window.

See also#

• Query cookbook
• SQL reference
• Agent memory