SysTime

SysTime adds system-versioned temporal tables to Ecto and PostgreSQL. Normal queries still read the live table with no temporal predicate and no extra join. Inside Repo.travel/3, temporal schemas, preloads, expression subqueries, CTEs, and query combinations resolve at the same past instant.

# Now: unchanged, zero temporal-query cost.
Repo.all(Shareholder)

# Then: both temporal tables use the same instant.
{:ok, holders} =
  Repo.travel(~U[2025-03-14 16:00:00Z], fn ->
    Repo.all(
      from s in Shareholder,
        join: h in Holding,
        on: h.shareholder_id == s.id,
        group_by: [s.id, s.name],
        select: {s.name, sum(h.quantity)}
    )
  end)

Use Repo.travel!/3 when the callback does not deliberately call Repo.rollback/1. An explicit rollback raises %SysTime.RollbackError{reason: reason}:

holders =
  Repo.travel!(~U[2025-03-14 16:00:00Z], fn ->
    Repo.all(Shareholder)
  end)

The additional s.name grouping is required because PostgreSQL does not carry a base table's primary-key functional dependency through a UNION ALL view. Use explicit schema joins as shown above. Ecto resolves assoc/2 joins after repository query preparation, so SysTime raises when their target is temporal rather than silently mixing a historical parent with current children. Repo.preload/2 is supported.

Installation

PostgreSQL 14 is the minimum supported database version and reaches upstream end-of-life on 12 November 2026. CI exercises PostgreSQL 14, 16, 17, and 18. PostgreSQL 12 and earlier lack the xid8 transaction identity used by the write trigger; PostgreSQL 13 has already reached upstream end-of-life.

Add SysTime to mix.exs:

def deps do
  [
    {:sys_time, "~> 0.1.0"}
  ]
end

Add the repository hook after use Ecto.Repo:

defmodule MyApp.Repo do
  use Ecto.Repo,
    otp_app: :my_app,
    adapter: Ecto.Adapters.Postgres

  use SysTime.Repo
end

Install SysTime's shared database functions once:

defmodule MyApp.Repo.Migrations.InstallSysTime do
  use Ecto.Migration

  def change do
    SysTime.Migration.up()
  end
end

Then generate a migration for each existing table:

mix sys_time.gen.migration shareholders

The generated migration calls only install/2; the shared migration above must run first. Or write the table migration directly:

def change do
  SysTime.Migration.install(:shareholders)
end

up/0 installs the shared sys_time database schema and the btree_gist extension. install/2 adds sys_period and an internal transaction-identity column, creates shareholders_history, shareholders_v, and shareholders_all, and installs three ENABLE ALWAYS triggers. Call down/0 only after every temporal table has been uninstalled.

Schemas

defmodule MyApp.Shareholder do
  use SysTime.Schema

  @primary_key {:id, :binary_id, autogenerate: true}
  @foreign_key_type :binary_id

  temporal_schema "shareholders" do
    field :name, :string
    belongs_to :company, MyApp.Company
    has_many :holdings, MyApp.Holding
    timestamps(type: :utc_datetime_usec)
  end
end

SysTime generates MyApp.Shareholder.History. It has the same persisted fields but no primary key and no associations. A belongs_to becomes its plain foreign-key field; resolving associations from a historical row would otherwise silently return current data.

Explicit history queries

Explicit helpers work without a travel block:

versions = Repo.all(SysTime.Query.versions(Shareholder, shareholder_id))
history = Repo.all(SysTime.Query.history(Shareholder, shareholder_id))
old = Repo.one(SysTime.Query.at(Shareholder, shareholder_id, instant))
changes = SysTime.Query.diff(Repo, Shareholder, shareholder_id)
changed_rows =
  Repo.all(SysTime.Query.changed(Shareholder, window_start, window_end))

snapshot_changes =
  SysTime.Query.diff(
    Repo,
    Shareholder,
    shareholder_id,
    earlier_instant,
    later_instant
  )

versions/2 and at/3 return association-free Shareholder.History values so a past value cannot preload current related rows. Explicit helpers require a single-column primary key. Transparent travel/3 does not. at/3 deliberately has no SQL limit: Repo.one/2 raises if corrupted or manually backfilled data contains overlapping versions.

changed/3 returns every version with a lower or finite upper period boundary inside the half-open [window_start, window_end) interval. Inserts, both halves of updates, and deletions therefore appear; rows untouched during the window do not. Results are ordered by the first relevant boundary. The point-to-point diff/5 compares only the two selected snapshots and returns one field-change map; it raises if the entity is absent at either instant.

Telemetry

Every travel/3 and travel!/3 call emits a standard Telemetry span:

• [:sys_time, :travel, :start] includes :repo and the normalized :instant.
• [:sys_time, :travel, :stop] adds :outcome and measures :duration.
• [:sys_time, :travel, :exception] follows Telemetry's standard exception metadata when the transaction or callback raises.

Schema changes and sync/1

The archive trigger copies rows positionally. After adding a column to a live table, call sync/1 in the same migration before allowing writes:

def up do
  alter table(:shareholders) do
    add :country_code, :text
  end

  flush()
  SysTime.Migration.sync(:shareholders)
end

sync/1 only reconciles trailing additions. It raises when an existing name or PostgreSQL type differs at any ordinal position, or when history has trailing columns which would need to be discarded. It will not guess, reorder, cast, or delete historical data. Use explicit up/0 and down/0 migrations for temporal schema changes. sync/1 preserves view identities, grants, and dependent views with CREATE OR REPLACE VIEW.

SysTime.Test.drift_tests/2 generates checks for positional identity, history-schema fields, the primary-key overlap constraint, and all three ENABLE ALWAYS triggers.

Testing

Generate database-installation drift checks for every temporal schema:

defmodule MyApp.SysTimeDriftTest do
  use ExUnit.Case, async: false
  import SysTime.Test

  drift_tests MyApp.Shareholder, MyApp.Repo
end

Tests which create versions or call travel/3 cannot run through Ecto.Adapters.SQL.Sandbox. The sandbox makes every test write part of one outer transaction, while SysTime deliberately exposes at most one version per top-level transaction. Run behavioural tests synchronously with an ordinary pool and clean up explicitly. See Limits and operational semantics.

Costs

• Every update or delete writes the outgoing row to history and updates GiST exclusion indexes.
• History is full-row storage, not a delta log.
• Travel reads use UNION ALL views and can receive worse plans than live-table reads, especially for aggregates.
• btree_gist is required for the non-overlap constraint.
• TRUNCATE is not versioned.
• History tables are append-only through DML, but this is not a tamper-evidence system.
• One transaction creates at most one externally visible version per entity.
• Primary keys are immutable after insertion because they define version identity.
• System periods are trigger-owned and normally use one transaction-start boundary, not PostgreSQL commit time.

See Limits and operational semantics before adopting it.