History and change data capture

An entity’s history strategy decides what data.svc keeps when a row changes: nothing, one step back, every version, a pre-image copy, or a per-field change log. The declaration keys and their three aliases are in Entities; this page is what each strategy does at runtime, what it puts in the database, and how you read it back.

The eight strategies

Strategy	What a write records	Where it lands	?version= reads
none	Nothing. In-place UPDATE.	main table	no
type1	Nothing. Runtime-identical to none.	main table	no
type2	Every version as its own row.	main table	yes
type3	The single previous value of each tracked: field.	<field>_prev columns	no
type4	A copy of the pre-image on every update and delete.	<entity>_history	no
type6	type2 and type3 together.	main table	yes
typeaudit (alias audit)	One row per changed field per mutation.	<entity>_audit	no
typenoversion (alias noversion)	Nothing.	main table	no

type1 exists so an explicit “overwrite in place” declaration is not silently downgraded to the default. typenoversion exists so an entity whose versions live somewhere else — file versions in blob storage, for example — can say so; the engine does no history bookkeeping for it. Both behave exactly like none.

type1 and typenoversion fail the backend capability check

No backend lists either token in its supported-strategy set — only none is allowed implicitly. A host that opens its datastore through the library’s capability-gated path therefore rejects both at boot, on PostgreSQL as much as anywhere else. data.svc does not run that gate (see Backend support), so both work there, but a schema you share with a capability-gated host will not load. Declare none unless you have a host that skips the gate.

Only type2 and type6 produce more than one physical row per logical id. Only they, and type4, materialise historical state at all: type3 keeps one step and loses the rest, and typeaudit records a change log that is never readable as a past version of the row.

Versioned rows — type2 and type6

Columns the engine adds

Declared as valid_from / valid_to unless you override them with history.valid_from_field and history.valid_to_field. Each is emitted only when the entity has not already declared a field of that name.

Column	PostgreSQL type	Meaning
valid_from	TIMESTAMPTZ NOT NULL DEFAULT now()	When this version became current.
valid_to	TIMESTAMPTZ NULL	When it stopped being current. NULL marks the current row.
_version_num	BIGINT NOT NULL DEFAULT 1	Per-logical-id counter, starting at 1.

The primary key is rewritten to (<declared pk columns…>, valid_from) so one logical id can hold many rows, and three indexes are synthesised — a partial unique index that enforces at most one current row per id, plus two lookup indexes. The index names and the resulting unique: caveat are in Entities.

type6 additionally emits a nullable <field>_prev column for every name in history.tracked, typed like the field it shadows. A name in tracked: that is not a declared field is skipped without a diagnostic.

What each operation does

Operation	Effect
Create	Straight INSERT with valid_from = now, valid_to = NULL, _version_num = 1.
Update	In one transaction: read the current row, UPDATE it to valid_to = now, then INSERT the pre-image overlaid with the payload at valid_from = now, valid_to = NULL, _version_num = previous + 1.
Delete	UPDATE the current row to valid_to = now. No new row is written.
Restore	INSERT a copy of the most recently closed row with valid_to = NULL and the next version number, so the timeline reads v1 open → v2 closed → v3 restored.
Purge (?purge=true)	Bypasses versioning entirely: a real DELETE that removes every version row for that id.

On type6, the update path also copies each tracked field’s pre-image value into <field>_prev on the new version row. Restore is the exception — it carries the closed row’s _prev values through unchanged, because a restore has no new “previous” value.

A delete closes the current version; it does not stamp the soft-delete columns. deletedon and deletedby stay NULL on a versioned entity, and the row disappears from reads because valid_to IS NULL no longer matches, not because of the soft-delete filter.

Mutations always target the current version. There is no write path to the past.

A concurrent update returns 500, not 409

If another writer closes the row between this request’s pre-image read and its own close, the engine aborts with exec: version conflict — row was modified by another writer rather than leave two open versions. The REST layer classifies by error type first — access denied to 403, validation to 422, not-found to 404 — and falls back to matching the message text for everything else. The conflict is an untyped error whose message matches no fallback branch, so what reaches the client is 500 with code v2.update_failed — not the 409 the engine’s contract describes. Treat that message as retryable: re-read the row and rebase the write.

Reading a version

?version= is the only history read that works over HTTP.

Form	Resolves to
N	_version_num = N
first	version 1
latest	the current row — same as omitting the flag
latest-N, -N	MAX(_version_num) - N for that row

curl -G "https://api.example.com/data/rest/subscription/id/sub_1" \
  -H "Authorization: Bearer $TOKEN" \
  --data-urlencode "version=3"

latest-N needs a primary-key equality predicate in the request so the engine can look up that row’s maximum version. Without one, or when the arithmetic walks before version 1, the read falls back to the current view and records a warning in hook metadata that no response surfaces — a silently current answer to a historical question. first and an explicit integer have no such dependency and work on list reads too.

Point-in-time reads are not reachable over REST

asof is missing from the reserved query-key set, so on every route that builds a query from the URL it becomes an equality predicate on a column named asof and the request fails with 404. The temporal filter itself is implemented — valid_from <= T AND (valid_to > T OR valid_to IS NULL), applied before row-level security so a point-in-time read cannot escape it — but nothing over HTTP can reach it. See Request flags. GraphQL exposes no history arguments at all.

Previous-value columns — type3

Each name in history.tracked gets a nullable <field>_prev column on the main table. On update the engine reads the current row and folds each tracked field’s pre-image into the same UPDATE, so the row carries both the new value and the one it replaced. Create sets nothing; delete drops the columns with the row. There is one step of history and no way to reach the step before it.

entities:
  - name: subscription
    history:
      strategy: type3
      tracked: [status, plan]

The population is best-effort. If the request carries no primary key, or the pre-image read fails, the update still runs and the _prev columns keep whatever they held before. Nothing in the response says the history step was skipped.

Parallel history table — type4

The main table holds current state and is updated in place. Every update and delete copies the pre-image into a side table.

Object	Shape
<entity>_history	Every column of the main table, without its primary-key or unique constraints, plus _op and _archived_at.
_op	update or delete — which operation archived this row.
_archived_at	When the copy was written. Defaults to now.
<table>_pk_archived_idx	Index on (<pk>, _archived_at).

Override the table name with history.history_table.

The copy is written after the main mutation commits, and its error is discarded: a broken or drifted history table produces committed mutations, no history rows, and no signal anywhere in the response. If the pre-image read fails first, the insert is skipped entirely and the mutation still succeeds. Monitor the row count of the side table rather than trusting the write path.

Query it directly — the side table is engine-owned and has no HTTP surface:

SELECT _op, _archived_at, status, total
FROM   "order_history"
WHERE  id = 'ord_42'
ORDER  BY _archived_at DESC;

A single-row point-in-time read (WHERE pk = X plus a timestamp) is rewritten by the engine onto this table as _archived_at > T ORDER BY _archived_at ASC LIMIT 1; an empty result means the row has not changed since T and the current row is the answer. List reads are never rewritten — they return current rows and stash an unsurfaced warning. Both paths are academic over HTTP today, since asof cannot reach the engine.

Per-field change log — typeaudit

The main table is untouched. Every mutation writes one row per field into <entity>_audit, whose shape is fixed across every backend.

Column	Contents
id	Audit-row primary key. A 32-character hex string: 48-bit millisecond timestamp then 80 random bits, so it sorts by write time. Not a Crockford-encoded ULID — do not parse it as one.
entity_id	Primary-key value of the changed row, stringified.
field_name	Which field changed. Empty is reserved for a delete against a row that had already vanished.
old_value	Value before the change.
new_value	Value after the change.
operation	create, update or delete.
changed_at	When the change happened, UTC.
changed_by	Who performed it. Empty for system writes.

An index on (entity_id, changed_at) backs both “history of row X” and “everything between T1 and T2”. On ClickHouse the same access pattern is the table’s ORDER BY instead of a separate index.

Operation	Rows written
Create	One per non-null field, old_value null.
Update	One per changed field only.
Delete	One per non-null pre-image field, new_value null.

Values are stored as text: strings verbatim, numbers and booleans as their printed form, anything structured as JSON. The engine’s temporal columns are excluded, but the audit columns are not — the hook stamps updatedon and updatedby on every update, so every update writes at least those two audit rows on top of whatever the caller changed.

SELECT changed_at, changed_by, operation, field_name, old_value, new_value
FROM   "order_audit"
WHERE  entity_id = 'ord_42'
ORDER  BY changed_at DESC, field_name;

Two behaviours to plan around:

• “Changed” is a string comparison. Two values are equal when their default printed form matches. Structured payloads that print identically are recorded as unchanged; values that are semantically equal but print differently — numeric precision, map ordering — are recorded as changed.
• A failed audit write does not fail the mutation. The error is discarded and the mutation returns success. A failed pre-image read is likewise non-fatal: the update’s audit rows simply lose their old_value.

Audit rows are a log, not a version store. Reads of the entity always return current state.

The four audit columns

Every entity carries createdby, createdon, updatedby and updatedon from the built-in common trait, and deletedby / deletedon from softdelete. See Traits for the declarations and the override rules.

Column	Create	Update	Delete
createdon	set to now	—	—
createdby	set to the caller	—	—
updatedon	set to now	set to now	—
updatedby	set to the caller	set to the caller	—
version	set to 1 when the caller left it empty	compiler emits version = version + 1	—
deletedon / deletedby	—	—	stamped by the compiler’s soft-delete path

Population is name-matched on those exact spellings and applies whether or not the entity declares a history strategy. An entity that spells them created_at / created_by gets no population and no warning. Whatever the caller sent in the four audit columns is overwritten; version is the exception — a caller-supplied value survives on create.

Who gets recorded

The identity is resolved per request: the verified principal attached to the request context wins, and the engine’s own configured identity fills any gap so genuine system writes still attribute. Under impersonation the two columns mean different things and must not be reconciled:

Column	Records
Entity row’s updatedby / createdby	The identity the change was made under — the target user.
Audit row’s changed_by	The identity that performed it — the operator.

Read together they say “row updated under user X, by operator Y”. An operator id can only appear in a tenant’s audit trail through impersonation, because an operator cannot write tenant data directly.

changed_by records the engine’s configured user when the principal is missing

The fallback that lets system writes attribute correctly is the same fallback that silently mis-attributes a caller-driven write. When no verified principal is on the request context, every audit row and every createdby / updatedby on that write records the engine’s static configured identity instead — and on a per-tenant engine that value is empty, so the trail records what changed and not who.

This is live on one shipping path: bulk create builds its hook context from the engine’s configured identity rather than resolving the request principal, so audit stamps on bulk-inserted rows are blank. Bulk create also runs only the validation phases, so no history strategy’s bookkeeping fires for those rows at all. A host that wires its own engine without attaching the verified principal to the context has the same problem on every write.

Verify attribution with a real request before you rely on the trail for compliance: create one row through the API and read changed_by back out of the audit table.

Side tables and migrations

<entity>_audit and <entity>_history are created by the migration when the entity declares the strategy, together with their index, and are then never altered. Their shape is engine-owned: schema discovery filters them out of the table list, and the diff re-implies them from the parent entity’s strategy rather than comparing columns.

Adding a field to a type4 entity later therefore leaves the history table one column short. The next update builds its insert from the pre-image’s columns, the statement fails against the older table, and — because the type4 history error is discarded — history silently stops being recorded while mutations keep succeeding. Recreate the side table yourself when the parent entity’s columns change.

Backend support

Each backend declares the strategies it supports. The capability gate compares that set against the schema on the library’s datastore-open path, before any connection is made, and refuses an unsupported strategy with unsupported history strategy: entity "…" declares History.Strategy=… but backend "…" supports only […].

Backend	Strategies	Point-in-time filter	Notes
PostgreSQL	type2, type3, type4, type6, typeaudit	yes	The reference implementation.
SQLite	type2, type3, type4, type6, typeaudit	yes	Timestamps stored as text.
DuckDB	type2, type3, type4, type6, typeaudit	yes	—
ClickHouse	none	no	Any entity declaring history is refused.

ClickHouse declares no strategies because its updates and deletes are asynchronous background mutations: there is no row-level locking for an atomic close-and-insert, and no transactional rollback to keep a side table consistent with the main write. Its dialect can emit the DDL — a ReplacingMergeTree ordered by (pk, valid_from) — but the gate rejects the schema first. Soft delete is refused on the same backend for the same reason.

data.svc does not run the capability gate

The gate is part of the library’s datastore-open call. data.svc builds its per-tenant engine directly against a PostgreSQL pool and never makes that call, so nothing in the deployed service checks a declared strategy against the backend. The table above is the contract a capability-gated host enforces, not a guard data.svc gives you. Since data.svc serves PostgreSQL tenant datastores only, the gap costs you nothing on the strategies PostgreSQL implements — it is why type1 and typenoversion load there despite being outside every backend’s declared set.

Note

data.svc as deployed serves PostgreSQL tenant datastores only; the other three backends are library-level. See Datastores.

Declared but inert

history.archive.after, history.archive.table and history.archive.drop parse and are stored on the entity, and nothing reads them. Aged version rows are not moved or dropped by any code path. Ageing rows out of the primary table is a separate declaration and a separate runner — see Retention and archives, which operates on current rows and knows nothing about version chains.

Version rows accumulate for the life of the entity. On a type2 entity with frequent updates, plan capacity for the version count, not the row count.