Assertions and Transitions: rules about groups and rules about change

Some rules are about one value: a price is not negative, a status is one of five words. CHECK constraints cover those. Two other kinds of rule appear in nearly every transactional application, and databases rarely enforce either:

• Rules about a group of rows. At most 4 tickets on a flight. At most 3 purchases of one item per account.
• Rules about change. An order goes NEW → CHECKED → FINALIZED and never skips a step.

These usually live in application code: read, check, then write. That works until two requests run at once, or a second service, a migration script or an agent writes the same table without the check. KDBS enforces both kinds of rule in the engine, with Assertions and Transitions.

Assertions

What SQL-92 promised

The SQL-92 standard defined CREATE ASSERTION: a condition, written as an arbitrary query, that the database must keep true at all times. Mainstream databases such as PostgreSQL, MySQL, Oracle and SQL Server never implemented it. An arbitrary condition can be expensive to re-check after every write, and keeping it exact while many sessions write at once is harder still.

The class KDBS enforces

KDBS supports a narrow class: an upper bound on COUNT(*) or SUM(column) per group. That class can be checked in constant time and kept exact under concurrency.

CREATE ASSERTION seat_limit ON tickets
  GROUP BY (flight_id) CHECK COUNT(*) <= 4;

CREATE ASSERTION baggage_cap ON tickets
  GROUP BY (passenger_id) CHECK SUM(baggage_kg) <= 46;

A write that would take a group over its bound is refused. The error names the assertion, the group and the bound:

kdbs> INSERT INTO tickets VALUES (1, 5, '16C', 0, 0, NULL)
ERR ASSERTION_VIOLATION retryable=0 assertion "seat_limit" group (flight_id=1): COUNT(*) would exceed bound 4

kdbs> UPDATE tickets SET baggage_kg = 30 WHERE id = 1
ERR ASSERTION_VIOLATION retryable=0 assertion "baggage_cap" group (passenger_id=1): SUM(baggage_kg) would exceed bound 46

How it stays exact

• A running total per group. Each assertion keeps a Bound Cabin with one header per group, holding its running count and sum. A check reads only that header, so its cost does not depend on how many rows the group has.
• Reservations. A write that passes reserves its amount in the header immediately, before it commits. Later checks count committed rows plus reservations. If a write fails only because of another transaction’s reservation, it waits for that transaction to finish and checks again.
• One limit per instance. Every CPU core reserves into the same registry. Two sessions on two cores that both try to take the last seat get one success and one violation.
• Durable. The Bound Cabin is written to the write-ahead log and replayed at startup, so an assertion enforces as soon as the server is up, with no rebuild scan.
• Only the writes that can break it are checked. An INSERT, an UPDATE that raises the summed value, or an UPDATE that moves a row to another group. A DELETE can never break an upper bound, so it is not checked, but it frees room in its group immediately.

Use cases

Rule	Assertion
Seats per flight, tickets per event	GROUP BY (flight_id) CHECK COUNT(*) <= 180
Purchase limits per customer and product	GROUP BY (account_id, item_id) CHECK COUNT(*) <= 3
Coupon redemptions	GROUP BY (coupon_id) CHECK COUNT(*) <= 1000
Credit or spending caps	GROUP BY (account_id) CHECK SUM(amount) <= 500000
Concurrent sessions per user (each row is one live session)	GROUP BY (user_id) CHECK COUNT(*) <= 5
Daily quotas, with a day column in the group	GROUP BY (user_id, day) CHECK COUNT(*) <= 100

What is out of scope

Lower bounds and equality are refused, because enforcing them would mean checking deletes and decreases too. So are MIN, MAX and AVG, SUM over anything but an int64 column, conditions across tables, groups filtered by WHERE, and deferred checking. Each refusal names the byte it refused, so you know exactly which part to rewrite. The full list is in Assertions.

Transitions

An assertion constrains how many rows a group may have. A Transition constrains how one column’s value may change over a row’s life. It names the states a row may start in, the moves it may make, and the states it may be deleted from:

CREATE TRANSITION order_status ON orders(status)
  ON INSERT ('NEW')
  ON UPDATE ('NEW' => 'CHECKED', 'CHECKED' => 'FINALIZED',
             'NEW' => 'CANCELLED', 'CHECKED' => 'CANCELLED')
  ON DELETE FROM ('NEW', 'CANCELLED');

 NEW ──▶ CHECKED ──▶ FINALIZED
  │         │
  └────┬────┘
       ▼
   CANCELLED

Every write that leaves the graph is refused, with the edge it tried to take:

kdbs> INSERT INTO orders VALUES ('kim', 'FINALIZED')
ERR TRANSITION_VIOLATION retryable=0 transition 'order_status' on column 'status': a row may not be inserted in state 'FINALIZED' (byte 34)
kdbs> INSERT INTO orders VALUES ('kim', 'NEW')
INSERTED oid=4047 id=1 page=157 slot=0
kdbs> UPDATE orders SET status = 'FINALIZED' WHERE id = 1
ERR TRANSITION_VIOLATION retryable=0 transition 'order_status' on column 'status': 'NEW' => 'FINALIZED' is not a declared edge, row id 1 (byte 27)
kdbs> UPDATE orders SET status = 'CHECKED' WHERE id = 1
UPDATED 1
kdbs> UPDATE orders SET status = 'FINALIZED' WHERE id = 1
UPDATED 1
kdbs> DELETE FROM orders WHERE id = 1
ERR TRANSITION_VIOLATION retryable=0 transition 'order_status' on column 'status': a row may not be deleted from state 'FINALIZED', row id 1

A rule about history, with no history stored

“FINALIZED only after CHECKED” is a property of the path a row has taken. A Transition never stores that path, and it never checks the property on its own. It follows by induction. Every insert writes an initial state, and every update follows a declared edge, so every committed value a row has held lies on a walk of the graph from an initial state. The only edge into FINALIZED leaves CHECKED, so no row can reach FINALIZED without passing through CHECKED.

The induction has one way out: restarting a row’s history under its old identity, by deleting it and inserting the same key again in state NEW. KDBS closes that path with the immutable-key rule from the first post in this series. A deleted key stays bound until a PURGE frees it, and PURGE is refused on a table that carries a Transition:

kdbs> PURGE FROM orders WHERE id = 1
ERR 'orders' carries transition 'order_status', whose state history a freed key would restart; PURGE frees no key of a relation that carries a Transition (byte 11)

A key on a governed table is bound for life, so the path property holds for every row.

Properties

• Row-local and stateless. A check reads only the row being written and the declaration. There is no derived structure, nothing to reserve and nothing to rebuild after a crash.
• Checked per statement. NEW → CHECKED → FINALIZED inside one transaction passes, because each step is an edge.
• Rollback is not a transition. Undoing an update returns to a committed value, so it is not checked.
• The state set is closed. A value the declaration does not name is refused on any write.
• History is not verified at creation. CREATE TRANSITION accepts existing rows whose state is in the set, wherever they came from. The graph speaks for the values committed while it stands.
• One Transition per column, on a NOT NULL integer, char(N) or varchar(N) column.

Use cases

• Order lifecycles: NEW → PAID → SHIPPED → DELIVERED, with CANCELLED reachable only before shipping.
• Payments: AUTHORIZED → CAPTURED → REFUNDED. A refund cannot happen before a capture, and a captured payment cannot be deleted.
• Approvals: DRAFT → SUBMITTED → APPROVED | REJECTED, with nothing approved that was never submitted.
• Support tickets, KYC reviews, shipment tracking: any column where skipping a step is a bug.

Availability

Transitions shipped in engine v3.2.0, and the sandbox runs it. The full rules are in Transitions. Create an orders table with a status varchar(16) column, declare the Transition above, and walk a row through the graph.

Where each one fits

	Assertion	Transition
Constrains	A group of rows	One row’s column, over time
Example	At most 4 tickets per flight	No FINALIZED before CHECKED
State it keeps	A running total per group	None
Checked on	Inserts, raising updates, moves between groups	Inserts, updates of the column, deletes
Error	ASSERTION_VIOLATION	TRANSITION_VIOLATION

Both are declared once, in the schema, and hold for every writer: a service, a script, a migration or an agent. The application stops re-implementing the rule, and the database stops depending on every writer to remember it.

Try both in the sandbox: run SHOW ASSERTIONS, try to add a fifth ticket to flight 1, then declare a Transition and try to skip a step.