Reading a table as it was: versioned tables and AS OF COMMIT

An audit asks what a balance was when the decision was made, not what it is now. A backtest needs the features as they stood at the time, not after a later correction. A support ticket is about the state a customer saw yesterday. In most stacks, answering any of these means replaying a log or restoring a snapshot into a scratch database, and the answer arrives hours later.

A versioned table in NYXDB keeps every accepted version of every key beside the current row. Reading the table as it was at a past commit is one clause on an ordinary query, and the read returns each key’s winner at that commit.

One current row, every version kept

A table’s mode decides what it keeps. An append table keeps every row. An upsert table keeps the latest row per key and discards the previous value. A versioned table keeps one current row per primary key and retains every accepted version beside it, so the current state and the full history live in one place, with no second table for you to maintain.

Because the history is part of the table, the things built on it see it too. An index declares whether it covers the current rows, the history or both, and a transformation over a versioned table reads the same current rows a query does.

Which version wins, whatever order writes arrive in

Writes arrive out of order. A late correction can land after a newer value, and two producers can record the same position twice. A versioned table settles this with two optional clauses that declare how versions of a key are ordered, so the current row is the same whatever order the writes arrive in.

For each key, the engine keeps the version that is greatest under one order, compared left to right. First comes the effective order, from the VALID BY columns: where a value takes effect, for example a ledger position.

Then comes the revision order, from the REVISION BY columns: which recording wins at the same effective position. Last comes the database commit order, which always breaks a remaining tie. A table with neither clause keeps the last committed write. The columns named by the two clauses are non-null integers or timestamps, and every write supplies them.

The holding group of balances is ordered by balance_index, where the balance takes effect, and balance_version, which recording wins at the same position. A write supplies both:

UPSERT INTO balances (
  chain_id, address, token, amount, updated_at, balance_index, balance_version
)
VALUES (1, '0x9c2e4f1a', 'USDC', 100, '2026-10-01 12:00:00.000', 100, 1)
ATTRIBUTE GROUP holding;

Suppose four writes of one key arrive in this order: the first at index 100, the second at index 200, a third at index 150 with a much higher revision, and a fourth at index 200 with a higher revision than the second. After the third write, the current row is still the second: 150 is earlier than 200, so the high revision does not matter. After the fourth, the current row is the fourth: the same index as the second, and a higher revision. Delivered in order, shuffled or late, the writes converge to the same current row and the same retained history.

The two clauses also apply on an upsert table, where they decide which write is the latest without retaining history, and an attribute group declares its own, ordering only that group’s writes.

Reading at a commit

Every statement commits atomically, and each tablet orders its own commits with a commit sequence. A write is acknowledged once its record is durable in the write-ahead log, and when the write returns its receipt, the next read sees it, with no retry. A read that runs while a write is in flight returns either the state before that write or the state after it, never a partly applied row.

That commit sequence is the handle for history. AS OF COMMIT returns the rows as they stood at the named commit: each key’s winner at that commit, under the current schema, with columns added later read at the value they were introduced with. The operand is a commit sequence, a positive integer. The clause is written directly after the relation name, or after the filtering, grouping and ordering clauses, before the row limit.

SELECT address, token, amount
FROM balances AS OF COMMIT 42
WHERE chain_id = 1;

History without a second system

No log replay to answer an audit. No snapshot restore to reproduce a bug. No separate history table that a job appends to and a reader joins. The history is in the same table as the current state, maintained by the same commits, and the derived tables over it stay exact because a transformation applies each source commit as the engine publishes it.

The versioned tables concept page has the full ordering rules and the worked example, consistency and visibility states every guarantee a statement and a transaction make, and indexes explains which rows an index covers.