# Versioned tables

> How VALID BY and REVISION BY choose the current row per key, what a VERSIONED table retains, and what AS OF COMMIT reads.

A `VERSIONED` table keeps one current row per primary key and retains every
accepted version beside it. Two optional clauses declare how versions of a key
are ordered, so the current row is the same whatever order the writes arrive
in.

## Choosing the current row

For each key the engine keeps the version that is greatest under this order,
compared left to right:

1. the effective order, from the `VALID BY (…)` columns: where a value takes
   effect;
2. the revision order, from the `REVISION BY (…)` columns: which recording wins
   at the same effective position;
3. the database commit order, which always breaks a remaining tie.

An empty binding falls through to commit order, so a table with neither clause
keeps the last committed write. Writes delivered in order, shuffled, or late
converge to the same current row and the same retained history.

The columns named by `VALID BY` and `REVISION BY` 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, for example a ledger position) and `balance_version` (which
recording wins at the same position):

```sql
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;
```

Four versions of one key arriving in this order:

| Arrival | `balance_index` | `balance_version` | Current row afterwards |
| --- | --- | --- | --- |
| 1 | 100 | 1 | write 1 |
| 2 | 200 | 1 | write 2 (later index) |
| 3 | 150 | 9 | still write 2: 150 is earlier than 200, so the high version does not matter |
| 4 | 200 | 2 | write 4 (same index as write 2, higher version) |

`VALID BY` and `REVISION BY` also apply on `UPSERT` tables, where they decide
which write is the latest without retaining history. An
[attribute group](/docs/concepts/attribute-groups) declares its own
`VALID BY` and `REVISION BY`, which order only that group's writes.

## What a VERSIONED table retains

`UPSERT` keeps the latest row per key only. `VERSIONED` keeps the current row
and every accepted version. An index declares which of the two it covers with
`SCOPE CURRENT`, `SCOPE HISTORY` or `SCOPE BOTH`; see
[Indexes](/docs/concepts/indexes).

## Reading at a commit: AS OF COMMIT

`AS OF COMMIT <n>` returns the rows as they stood at commit `n`. It is written
directly after the relation name, or after the `WHERE`, `GROUP BY` and
`ORDER BY` clauses, before `LIMIT`.

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

The read returns each key's winner at that commit, under the current schema;
columns added later read at the value they were introduced with. The operand is
a commit sequence, a positive integer.

## Related pages

- [Tables](/docs/concepts/tables) for the three modes.
- [CREATE TABLE](/docs/sql/create-table) for the full grammar.
- [SELECT](/docs/sql/select) for where `AS OF COMMIT` fits in a query.
- [Consistency](/docs/concepts/consistency) for what a commit means.

---

Source: https://nyxdb.io/docs/concepts/versioned-tables/