Bitemporal Persistence

Bitemporal persistence is a data management approach that tracks both valid time (when a fact is true in the real world) and transaction time (when a fact is stored in the database). This allows for historical queries, auditing, and correction of past data without losing information about previous states.

Core Concepts

Time Coordinates

TimeCoordinates represents a pair of timestamps:

@Serializable
data class TimeCoordinates(val effective: Long, val recorded: Long)

• Effective Time (Valid Time): When a fact is true in the real world. The time period during which the information is considered valid.
• Recorded Time (Transaction Time): When a fact was recorded in the system. The history of how the system’s knowledge of the real world evolved.

All bitemporal records are associated with a TimeCoordinates instance, enabling queries as of any point in both timelines.

BitemporalTable Schema

BitemporalTable abstracts a table with columns for:

• eId — entity identity (UUID)
• rId — record identity (version identifier, UUID)
• effectiveAsOf — when this fact was effective in the real world
• recordedAsOf — when this fact was recorded in the system
• author — who made this change
• previous — reference to the previous record in the lineage (UUID, nullable)
• retired — logical delete flag

TimeCoordinatesColumn is a composite column for storing and retrieving TimeCoordinates pairs.

BitemporalEntity and BitemporalRecord

BitemporalEntity is a serializable data class representing a versioned entity:

abstract class BitemporalRecord<EP, M, TBL, SELF> {
    val rId: EntityID<UUID>
    var eId by table.eId
    var effectiveAsOf by table.effectiveAsOf
    var recordedAsOf by table.recordedAsOf
    var retired by table.retired
    var previous by table.previous
    var author by table.author
    var payload: EP
    var metadata: M
}

Version selection versus row qualification

Two different questions are asked of a bitemporal read, and since 16.0.0 they are two different predicates:

Question	Predicate	Applies to
Which of an entity’s rows may be its current version?	UniversalCondition.versionSelection	the choice among an entity’s versions
Which entities may this caller see?	UniversalCondition.filter	the row that choice produced

They were one predicate, and conflating them produced a defect that is worth stating plainly, because the symptom looks like a filter bug rather than a versioning one.

A subject whose table records attempts as well as states — an occurrence log — has rows that are not versions of the entity at all. Excluding those by adding “committed only” to the rule about which entities a caller may see works for reading one entity by id, which takes the newest row satisfying the rule. It does not work for listing, counting or finding by filter, which take the newest row of any kind and then test it: a non-version row passes that test on every count except the one that matters, and the entity is simply gone from every list until a later committed row lands.

Reads that resolve a single latest row never noticed the difference, because there both predicates restrict the same row. That is exactly why the defect survived: the read path that is easiest to test is the one that cannot see it.

list, count, findOne and aggregate take the version-selection condition alongside the existing one. It is optional and defaults to “every row is a version”, so a subject that has not thought about this needs no change.

The capability built on this separation is described in The Occurrence Pattern.

Reading an entity’s history deliberately does not apply it. A subject declares one precisely because some of its rows are not versions, and those rows are what a caller reading the log has asked to see.

History is ordered totally, not just temporally

History reads newest first — by effective time, then recorded time, then the row id.

The last key is not a preference. Neither temporal column is unique: PostgreSQL’s now() is transaction-stable, so every row a transaction writes carries the same recorded time, and a caller may reuse an effective time as often as it likes. Rows sharing both are tied, and SQL leaves tied rows in whatever order it finds them.

That is invisible in a single read and costly across several. Paging a history issues one LIMIT/OFFSET query per page, and two of them need not agree on which tied row sits at which offset — so an entry can come back twice, or not at all, while the read promises each one exactly once. The row id is unique, which is the only property being asked of it; its direction carries no meaning.

The predicate must not correlate the subquery

Version selection renders against the version subquery’s own alias, which is what keeps the subquery uncorrelated — one pass for the whole query rather than one evaluation per candidate row.

This is the second predicate that has to obey that rule. The first cost roughly 13.5 days of database time a week in production before it was fixed, and the index that serves it is described in Bitemporal Indexes. A test asserts the plan shape rather than only the answer, because a correlated rewrite returns identical rows and merely costs more.

The same rule governs the child-collection subquery used by quantified locators: it is narrowed to the candidate children the filter could match, rather than sorting every version of every child before the parent join narrows anything.

Guards and Idempotency

• CreateGuard, UpdateGuard, DeleteGuard: Type aliases for suspend functions that enforce business rules before operations.
• Idempotency: Enum controlling how updates handle duplicate or conflicting changes:
  • REJECT: Reject the duplicate
  • CONFIRM: Treat as confirmed (update succeeds with existing state)
  • SKIP: Silently skip duplicates

Persistence API

Contract

The Universe interface defines the contract for bitemporal persistence:

• create(payload, metadata, asOf, author): Creates a new bitemporal entity. The asOf parameter specifies both effective and recorded time.
• read(eId, asOf): Retrieves the entity record valid at the specified effective time and recorded at or before the specified recorded time.
• readRecord(rId): Retrieves a specific historical record by record ID.
• update(update): Creates a new record representing the updated state, with new effectiveAsOf and recordedAsOf timestamps, linking to the previous record.
• delete(originEId, metadata, asOf, author): Creates a new record marked as retired = true.

All operations return DBIO<T> — see Functional Programming: DBIO.

BitemporalEntityClass

Provides Exposed-based implementations for:

• Reading the latest entity as-of a given time
• Listing all latest entities for each unique ID (using subquery or window function strategies)
• Counting entities as-of a given time
• Ensuring uniqueness and enforcing idempotency/versioning rules

Benefits

• Full auditability and historical reconstruction
• Correction of past data without data loss
• Support for complex business rules and versioning strategies
• “As-of” querying on either or both time dimensions

References

• Source code: /common-module/lib/src/main/kotlin/cards/arda/common/lib/persistence/bitemporal/
• Bitemporal Database (Wikipedia)
• Exposed ORM

See Also

• Information Model Design — design-time playbook for defining new bitemporal entities (DDL conventions, indices, composite-value flattening).
• Universe Design — class hierarchy that consumes the bitemporal record types.
• Data Authority Module Pattern — the four-layer module structure that wraps a bitemporal universe.