Current section
Files
Jump to
Current section
Files
README.md
# SysTime
[](https://hex.pm/packages/sys_time)
[](https://hexdocs.pm/sys_time)
[](https://github.com/UntangleDev/sys_time/actions/workflows/ci.yml)
[](https://github.com/UntangleDev/sys_time/blob/main/LICENSE)
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.
```elixir
# 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}`:
```elixir
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
| Component | Supported versions |
| --- | --- |
| Elixir | `>= 1.15 and < 2.0` |
| Ecto SQL | `>= 3.11 and < 3.15` |
| Postgrex | `>= 0.19 and < 1.0` |
| PostgreSQL | 14 through 18 |
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`:
```elixir
def deps do
[
{:sys_time, "~> 0.1.0"}
]
end
```
Add the repository hook after `use Ecto.Repo`:
```elixir
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:
```elixir
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:
```console
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:
```elixir
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
```elixir
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:
```elixir
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:
```elixir
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:
```elixir
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](guides/limits.md#the-ecto-sandbox-is-incompatible-with-behavioural-tests).
## 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](guides/limits.md) before adopting it.