Writing: INSERT/UPDATE/DELETE with ACID (WAL + indexes)

WAL-first ordering#

The invariant for every mutation is: write the op frame(s) to the WAL, then apply to memory. The WAL is the source of truth; on restart the committed log replays to rebuild state. The frame encodes the operation (OP_PUT or OP_DELETE), the table name, and the full record bytes — the same bytes the change-stream/replication path ships.

  validate → assign _id (insert) → check unique → WAL append (fsync if durable) → apply to rows+indexes → notify change observers

INSERT: auto _id and unique double-check#

1. Validate the record against the table schema (schemaless tables always pass).
2. Drop any caller-supplied _id and prepend a DB-generated, immutable _id (a monotonic integer).
3. Check unique indexes under a read lock; a violation returns conflict before anything is logged.
4. Append the OP_PUT frame to the WAL (durable commit before memory).
5. Take the write lock, RE-CHECK unique (insert is the only grow path), index the new row, and push it.

INSERT INTO users { name: "alice", age: 30 }

{ "error": null, "data": [ { "_id": 1, "name": "alice", "age": 30 } ], "root": { "count": 1 } }

-- users has a unique index on `email`
INSERT INTO users { email: "a@b" }
INSERT INTO users { email: "a@b" }

// second insert -> { "error": { "code": "conflict", "message": "unique index 'u_email' violated" }, "data": null, "root": { "count": 0 } }

UPDATE: stage, validate, log, apply#

UPDATE uses two-phase locking. Phase 1 takes a READ lock and stages the new rows: it finds targets (by primary point lookup when the filter is _id = n, else by predicate scan), copies each, applies the SET assignments (skipping the immutable _id), and keys each staged row by its _id. Schema tables then VALIDATE every staged row (an UPDATE must not persist a wrongly-typed field). Phase 2 takes ONE write lock around log-then-apply: it re-resolves each staged row by _id (so a concurrently-deleted row is skipped), logs exactly the rows that are still present, and applies them — refreshing any secondary index whose fields changed. The response count is the number of rows updated.

-- acct schema: bal:int
UPDATE acct SET bal = 200 WHERE _id = 1   -- ok
UPDATE acct SET bal = "oops" WHERE _id = 1 -- rejected, row unchanged
UPDATE acct SET ghost = 1 WHERE _id = 1    -- rejected, unknown column

// ok -> { "error": null, "data": null, "root": { "count": 1 } }
// bad type / unknown column -> { "error": { "code": "bad_request", ... }, "data": null, "root": { "count": 0 } }

DELETE: retention gate and reindex#

DELETE also uses two-phase locking. Phase 1 (read lock) collects the rows to remove and notes whether any are retention-blocked. If retention is configured (retention_secs > 0), a row whose created_at is younger than the window is kept and the delete is reported as retention_locked (when nothing else was removable). Phase 2 (write lock) logs the OP_DELETE frames first, then retains the survivors and rebuilds the index (positions shift after removal). The response count is the number of rows deleted.

INSERT INTO k { v: 1 }
UPDATE k SET v = 2 WHERE _id = 1
SELECT v FROM k WHERE _id = 1
DELETE FROM k WHERE _id = 1
SELECT * FROM k

// SELECT after update -> { "v": 2 }
// DELETE -> count 1 ; final SELECT -> count 0

WORM and compliance gates#

A table created with a write-once (WORM) policy rejects every UPDATE and DELETE with worm_violation — only INSERT is allowed. The retention policy blocks deletes inside the retention window with retention_locked. These are enforced by the planner before any WAL/apply.

-- ledger created with worm = true
INSERT INTO ledger { x: 1 }          -- ok
UPDATE ledger SET x = 2 WHERE x = 1  -- worm_violation
DELETE FROM ledger WHERE x = 1       -- worm_violation

// update/delete -> { "error": { "code": "worm_violation", "message": "table is write-once (WORM)" }, "data": null, "root": { "count": 0 } }

Concurrency model#