Query Language

contextdb's query language is built on three standards:

PostgreSQL-compatible SQL — DDL, DML, expressions, operators, JOINs, CTEs, ON CONFLICT DO UPDATE, $param binding
pgvector conventions — <=> operator for cosine similarity in ORDER BY
SQL/PGQ-style graph queries — GRAPH_TABLE(... MATCH ...) following SQL/PGQ conventions for bounded graph traversal (not a full standard implementation)

On top of these, contextdb adds declarative policy primitives: IMMUTABLE, STATE MACHINE, DAG, RETAIN, HISTORY, SYNC, and PROPAGATE. These are contextdb-specific extensions — everything else should feel familiar if you've used PostgreSQL.

contextdb ships with no built-in tables or schema. You define your own tables and attach these primitives to whichever columns need them. The example tables in this reference (documents, tasks, items, media, and so on) are illustrative only — stand-ins for whatever schema you design, not something contextdb provides.

All examples work in the Rust API via db.execute(sql, &params) where parameters are passed as HashMap<String, Value>. The CLI REPL does not support parameter binding ($param) — use literal values directly. Vector search works in the CLI using vector literals: ORDER BY embedding <=> [0.1, 0.2, 0.3] LIMIT 5.

Statements

CREATE TABLE

documents here is an example table you might define — name your own tables and columns (see the README):

CREATE TABLE documents (
  id UUID PRIMARY KEY,
  data JSON,
  embedding VECTOR(384),
  bucket TEXT,
  recorded_at TIMESTAMP DEFAULT NOW()
) IMMUTABLE

See Table Options for IMMUTABLE, STATE MACHINE, DAG, RETAIN, HISTORY, MAINTAIN COUNT, SYNC, and PROPAGATE.

CREATE TABLE never redefines a table that already exists. A second CREATE TABLE documents (...) is refused, naming the table, and the existing declaration and every row in it are left exactly as they were — nothing is replaced and no column's values are dropped. To re-run a schema script harmlessly, write the idempotent spelling:

CREATE TABLE IF NOT EXISTS documents (
  id UUID PRIMARY KEY,
  data JSON
)

IF NOT EXISTS creates the table when the name is free and does nothing at all when it is taken — the existing table keeps its own columns and rows even when the skipped declaration describes a different shape. Use ALTER TABLE to change a live table's shape, or DROP TABLE first to start over.

Later examples in this reference also use a second illustrative table, items:

CREATE TABLE items (
  id UUID PRIMARY KEY,
  name TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'active',
  item_type TEXT,
  context_id UUID,
  is_deprecated BOOLEAN DEFAULT FALSE,
  embedding VECTOR(384),
  created_at TIMESTAMP DEFAULT NOW()
)

ALTER TABLE

ALTER TABLE t ADD [COLUMN] col TYPE
ALTER TABLE t DROP [COLUMN] col
ALTER TABLE t RENAME COLUMN old TO new
ALTER TABLE t SET RETAIN 7 DAYS [SYNC SAFE]
ALTER TABLE t DROP RETAIN
ALTER TABLE t SET HISTORY ALL | CURRENT ONLY
ALTER TABLE t SET SYNC OFF | SYNC PUSH ONLY | SYNC PULL ONLY | SYNC TWO WAY
ALTER TABLE t SET SYNC CONFLICT KEEP FIRST | KEEP LATEST
ALTER TABLE t SET MAINTAIN COUNT (col [ASC|DESC], ...), (...) | ALL PREFIXES | NONE | DEFAULT
ALTER TABLE t ALTER COLUMN vector_col SET MAX_PARTITIONS positive_integer
ALTER TABLE t ALTER COLUMN vector_col SET SEARCH_MODE AUTO | EXACT | INDEXED
ALTER TABLE t ALTER COLUMN vector_col SET AUTO_INDEX_AT positive_integer | DEFAULT
ALTER TABLE t ALTER COLUMN vector_col SET HNSW DEFAULT | (M = positive_integer | DEFAULT, EF_CONSTRUCTION = positive_integer | DEFAULT, EF_SEARCH = positive_integer | DEFAULT)

COLUMN is optional on ADD and on DROP — ALTER TABLE t DROP a and ALTER TABLE t DROP COLUMN a both parse.

A table's conflict policy is declared on the table itself with the SYNC CONFLICT KEEP FIRST | KEEP LATEST clause (see CREATE TABLE), and can also be changed later with ALTER TABLE t SET SYNC CONFLICT ... above. There is no DROP HISTORY: ALTER TABLE t SET HISTORY ALL is the removal, and reads as what it does — dropping a window restores "keeps forever" (an absence), whereas HISTORY ALL is a nameable state.

DROP TABLE

DROP TABLE t

CREATE TRIGGER

CREATE TRIGGER document_seen ON documents WHEN INSERT;
CREATE TRIGGER item_changed ON items WHEN UPDATE;
CREATE TRIGGER synced_item_seen ON items WHEN INSERT INCLUDING SYNC;
DROP TRIGGER document_seen;

CREATE TRIGGER declares a host-callback Trigger. The callback is registered through the Rust API with Database::register_transactional_trigger_callback (the older register_trigger_callback name registers the same kind of handler) and activated by Database::complete_initialization. Triggers are for transactional observation and cascade writes; they are not validation triggers. Use STATE MACHINE, IMMUTABLE, DAG, and PROPAGATE for engine-enforced invariants.

A trigger handler only prepares database work inside the firing transaction. It runs before the engine's final validation of the complete write set, so it may insert a row that the triggering write depends on, such as a referenced parent. If final validation, a plugin, or the durable commit then refuses the transaction, nothing the handler wrote becomes visible and no delivery is queued. A handler must not send notifications or perform other irreversible external work. For that, route an event to a sink registered with Database::register_sink: a routed row written in the transaction queues a pending delivery in the same commit, and the sink runs only after that commit is durable, retrying with the same SinkEvent::action_id after a failure or restart.

DROP TRIGGER requires an administrative handle and is refused inside a trigger callback, including its transaction-bound handle, with Error::TriggerRequiresAdmin. The refusal preserves the active trigger.

By default, a trigger fires only for local writes. INCLUDING SYNC also fires an INSERT or UPDATE trigger for authenticated sync rows that this database accepts. DELETE triggers, with or without INCLUDING SYNC, are refused at declaration time with TriggerEventUnsupported. The incoming row, callback writes, and callback outcome share one transaction: either all commit, or callback failure rolls all of them back. The private sync image initially stages only the received unit's affected tables and their dependencies. When a callback accesses another existing table, the engine discards the incomplete private attempt, stages that table and its dependencies under the same memory budget, and retries. This discovery also runs when the callback catches TableNotFound; the final commit still includes its related write. A genuinely absent table retains its ordinary lookup error. Callbacks can run more than once during this private preparation, so they must keep their work inside the transaction. On this received-sync path the callback may only append relational INSERT rows (plus engine-owned lineage sidecars). Relational updates/deletes, graph or vector mutations, DDL, and non-engine configuration are refused with a typed trigger-effect error and the whole received transaction is rolled back. The same callback invoked by a local write retains the full callback API. A received-sync callback may use an active-transaction TXID placeholder in a non-identity column; the engine canonicalizes it after final transaction assignment. The same placeholder in any sync identity column (an explicit natural key, every primary-key column, or the id fallback) is refused as TXID identity placeholder; a fixed non-placeholder TXID identity remains valid.

Concurrency: the callback runs inside the firing transaction. Same-DB cross-thread writers wait-and-proceed, unrelated databases proceed independently, callback-thread reentry receives CallbackReentry, callback tx-bound handles stay isolated to the runner thread, cron same-DB contention remains immediate, and the bounded deadlock guard emits a structured tracing::warn! with trigger_name, waited_ms, and surface. See Architecture for the operator runbook.

CREATE INDEX

CREATE INDEX idx_name ON t (col)

INSERT

In the Rust API, a missing binding for a required INSERT value returns the existing Error::NotFound naming the parameter before the row is staged. A supplied NULL remains a constraint violation; a missing binding is not silently converted to NULL.

INSERT INTO documents (id, data, embedding)
VALUES ($id, $data, $embedding)

-- Multiple rows
INSERT INTO items (id, name) VALUES ($id1, $name1), ($id2, $name2)

-- Upsert
INSERT INTO items (id, name) VALUES ($id, $name)
ON CONFLICT (id) DO UPDATE SET name = $name

UPDATE / DELETE

UPDATE tasks SET status = 'superseded' WHERE id = $id
DELETE FROM scratch WHERE created_at < $cutoff

SELECT

SELECT [DISTINCT] columns FROM table
  [INNER JOIN | LEFT JOIN other ON condition]
  [WHERE condition]
  [ORDER BY col [ASC|DESC]]
  [USE RANK sort_key]
  [LIMIT n]

A SELECT-list column that does not exist on the table is ColumnNotFound { table, column } — the same class WHERE/JOIN/ORDER BY already use for an identical unknown-name failure — never Ok with a wrong or missing column:

SELECT nope FROM items;
-- Err(ColumnNotFound { table: "items", column: "nope" })

Predicate Evaluation Errors

A WHERE (or JOIN ... ON) predicate that fails to EVALUATE — as opposed to one that reads a NULL value — is a statement error, never a silently empty or partial result:

-- `tag` is TEXT; negating it cannot evaluate:
SELECT id FROM items WHERE -tag > 0;
-- Err(PlanError("cannot negate non-numeric value"))

A real NULL value compared with =, <, etc. is unaffected — that is standard SQL's NULL-as-false comparison semantics, and still correctly excludes the row rather than erroring:

-- `score` is NULL on this row; the row is excluded, not an error:
SELECT id FROM items WHERE score = 5;

Column Qualifiers

A qualifier naming a table that is not in scope for the query is ColumnNotFound, even when the bare column name it qualifies is real on some other table:

-- `x` is not `items` and is not declared as an alias anywhere in this query:
SELECT id FROM items WHERE x.name = 'a';
-- Err(ColumnNotFound { table: "items", column: "name" })

A qualifier naming the query's own table, alias, or a joined relation keeps working exactly as before:

SELECT t.id FROM items AS t WHERE t.name = 'a';
SELECT items.id FROM items INNER JOIN tags ON items.id = tags.item_id WHERE tags.label = 'x';

Standard-SQL WHERE Forms

A bare boolean column, its negation, a boolean $param, and a literal NULL are all standard SQL and all work as predicates on their own — no = TRUE / = FALSE needed:

SELECT id FROM items WHERE active;               -- active = TRUE
SELECT id FROM items WHERE NOT active;            -- active = FALSE
SELECT id FROM items WHERE $include_drafts;       -- boolean parameter
SELECT id FROM items WHERE NULL;                  -- matches nothing, never an error

ORDER BY Expressions

ORDER BY accepts a plain column or any expression form a SELECT list already evaluates — currently COALESCE(...) and the arithmetic operators (+, -, *, /):

SELECT tag FROM readings ORDER BY COALESCE(pref, fallback);
SELECT tag FROM readings ORDER BY score + 1 DESC;

An expression form the engine cannot evaluate is a typed, plan-time refusal rather than a silently unsorted result:

SELECT tag FROM readings ORDER BY UPPER(tag);
-- Err(OrderByExpressionNotSupported)

CTEs

WITH active AS (
  SELECT id, name FROM items WHERE status = 'active'
)
SELECT * FROM active WHERE name LIKE 'sensor%'

Multiple CTEs via comma separation. Non-recursive only.

Anchor-Shape vs List-Shape Reads

Scoped handles filter ordinary reads to the rows visible to that handle. List shapes keep this silent-filter behavior: full scans, non-unique predicates, ranges, IN (...) predicates even on primary keys, ordered LIMIT 1, and sort-elided index walks return Ok with only visible rows.

Explicit-anchor SELECT shapes are different. If a constrained handle names a specific row identity through equality on a primary key, a single-column UNIQUE, or every column of a composite UNIQUE, and the row exists but is hidden by the handle, the query returns the typed visibility error from the gate that hid it: ContextScopeViolation, ScopeLabelViolation, or AclDenied. Missing rows still return an empty result.

Predicate writes keep their existing contract. UPDATE ... WHERE pk = $id and DELETE ... WHERE pk = $id against a hidden row return Ok(rows_affected = 0) rather than a typed read-refusal error.

Transactions

BEGIN;
-- statements
COMMIT;

-- or
ROLLBACK;

One transaction may declare a complete schema in authored order, including tables, indexes, vector/rank/DAG table shapes, event types, triggers, sinks, and routes. Commit publishes the declarations together; rollback publishes none. A transaction containing schema declarations may not also write rows.

Configuration

SHOW SYNC_CONFLICT_POLICY;
SET MEMORY_LIMIT 512M;
SHOW MEMORY_LIMIT;
SET MEMORY_LIMIT NONE;
SET DISK_LIMIT 1G;
SET DISK_LIMIT NONE;
SHOW DISK_LIMIT;
SET MAINTENANCE_POLL_INTERVAL 250 MILLISECONDS;
SET MAINTENANCE_POLL_INTERVAL '5 SECONDS';
SHOW MAINTENANCE_POLL_INTERVAL;

SHOW SYNC_CONFLICT_POLICY returns one policy column: a first row giving the bare deployment default (keep_first), then one {table}={word} row per table that declares a conflict policy, plus one such row for each built-in work-ledger table (and peer_directory) currently present in the store, suffixed (engine-owned) to distinguish it from an operator-declared row.

SHOW MEMORY_LIMIT returns limit, used, available, and startup_ceiling. Size suffixes are binary. Both settings accept an unquoted or quoted size and NONE: SET MEMORY_LIMIT 2G, SET MEMORY_LIMIT '2G', SET DISK_LIMIT 2G, and SET DISK_LIMIT NONE use the same forms. 2G resolves to 2147483648 bytes. CLI --memory-limit 2G uses the same value. The limit governs charged bytes, which are separate from whole-process RSS.

SHOW DISK_LIMIT returns the same columns for file-backed storage. On :memory: databases, disk limit commands are accepted but ignored.

SHOW MAINTENANCE_POLL_INTERVAL returns one milliseconds column. The default is 60,000 ms. SET MAINTENANCE_POLL_INTERVAL accepts a positive integer followed by MILLISECONDS or SECONDS, either unquoted or as one quoted value. The CLI flag --maintenance-poll-ms remains an integer millisecond value, not SQL text. If that flag is omitted, a file-backed store keeps and uses its persisted interval. For a file-backed database the value persists and governs later opens. Time wakes the engine-owned worker; the declared per-column thresholds decide whether vector consolidation runs.

Schema inspection preserves authored CONTEXT_ID, SCOPE_LABEL (...), and SCOPE_LABEL_READ (...) WRITE (...) in rendered .schema DDL. The typed schema answer also keeps Simple and Split scope-label forms and their label sets. A synced full-schema restore retains the same access declarations; local vector partition keys grant no additional access.

Column Types

Type	Description	Example
`INTEGER` / `INT`	64-bit signed integer	`42`
`REAL` / `FLOAT`	64-bit floating point	`3.14`
`TEXT`	UTF-8 string	`'hello'`
`BOOLEAN` / `BOOL`	Boolean	`TRUE`, `FALSE`
`UUID`	128-bit UUID	`'550e8400-e29b-41d4-a716-446655440000'`
`TIMESTAMP`	Stored as a Unix timestamp (`Value::Timestamp(i64)`); ISO 8601 text literals are also accepted on input	`NOW()`
`JSON`	JSON value	`'{"key": "value"}'`
`VECTOR(n)`	Fixed-dimension float vector	`[0.1, 0.2, 0.3]`
TXID	Engine-issued transaction id (`Value::TxId`). Populate only via the library API with a bound parameter; SQL literals are rejected. Sync-apply advances the local TxId allocator past incoming peer values.	`Value::TxId(tx.id())`

NULL values display as NULL. Vectors display as [0.1, 0.2, ...].

Vector columns can choose per-index scalar quantization:

CREATE TABLE media (
  id UUID PRIMARY KEY,
  source TEXT,
  vector_text VECTOR(8) WITH (quantization = 'SQ8'),
  vector_vision VECTOR(4) WITH (quantization = 'SQ4')
);

Valid quantization values are F32, SQ8, and SQ4; the default is F32.

A VECTOR(n) column holds embeddings and nothing else. Every write door — INSERT, UPDATE, a conditional UPDATE, ON CONFLICT ... DO UPDATE, and a column default — accepts an embedding of the declared dimension, the text spelling of one ('[0.1, 0.2, 0.3]'), or NULL where the column is nullable. Anything else is refused before the statement stages a row, with ColumnTypeMismatch { table, column, expected, actual } naming the column that rejected it and the type of the value that was bound. A scalar, boolean, or JSON value bound into a vector column by mistake is therefore reported at the statement that bound it, not discovered later when the store is read back.

NOT NULL on a vector column keeps that meaning when the store is read directly — the route a reader takes when no live process owns the file (see CLI Reference). Such a read serves a row only when the file still holds the embedding the declaration promises; where it does not, the read is refused with direct_read_requires_writer rather than handing back a row whose required column has quietly vanished, and a writable open (--write) is what mends the store. The rule follows the declaration, not the storage layout: a quantized SQ8/SQ4 column keeps its embedding in the vector index instead of in the row, so a required quantized column is served from that stored embedding and refused when the embedding is gone, exactly as a required F32 column is refused when its cell holds no embedding. A nullable vector column is untouched by all of this — an absent embedding is a value there, and the row is served with NULL. Each vector column is a separate (table, column) index. Search routes to the column named in ORDER BY:

SELECT id FROM media
WHERE source = 'camera-1'
ORDER BY vector_vision <=> '[0,0,1,0]' LIMIT 5;

Inspect registered vector indexes with:

SHOW VECTOR_INDEXES;
SHOW VECTOR_PARTITIONS FOR media.vector_vision;

SHOW VECTOR_INDEXES returns one summary row per (table, column) with its declaration, aggregate lifecycle and accounting state. SHOW VECTOR_PARTITIONS returns the typed-key detail for individual layouts; use its optional FOR, LIMIT, and OFFSET clauses when a column has many layouts. Inspection is read-only and does not build, replay, or repair an index.

Rank Policies

A VECTOR(n) column can declare a rank policy that combines raw cosine similarity with typed columns from the anchor row and one indexed joined row. The policy is attached to the specific (table, vector_column) index. Because the policy lives in schema, every caller that asks for the same SORT_KEY gets the same ranking behavior; applications do not need to copy formula text into each query.

Grammar shape:

<column> VECTOR(n)
  [WITH (quantization = 'F32' | 'SQ8' | 'SQ4')]
  RANK_POLICY (
    JOIN <joined_table> ON <joined_indexed_column>,
    FORMULA '<rank expression>',
    SORT_KEY <identifier>
  )

CREATE TABLE task_results (
  id UUID PRIMARY KEY,
  task_id UUID NOT NULL,
  success BOOLEAN NOT NULL
);

CREATE INDEX task_results_task_id_idx ON task_results(task_id);

CREATE TABLE tasks (
  id UUID PRIMARY KEY,
  description TEXT NOT NULL,
  confidence REAL,
  embedding VECTOR(384) RANK_POLICY (
    JOIN task_results ON task_id,
    FORMULA 'coalesce({confidence}, 1.0) * coalesce({success}, 1.0)',
    SORT_KEY effective_confidence
  )
);

Use the policy during vector search with USE RANK <sort_key>:

SELECT id, description
FROM tasks
ORDER BY embedding <=> $query USE RANK effective_confidence
LIMIT 10;

Without USE RANK, the same query returns cosine ordering:

SELECT id, description
FROM tasks
ORDER BY embedding <=> $query
LIMIT 3;

-- id   description        vector_score
-- d1   closest match      1.0
-- d2   near match         0.75
-- d3   weaker match       0.5

With the policy, ordering uses the formula before the final LIMIT:

SELECT id, description
FROM tasks
ORDER BY embedding <=> $query USE RANK effective_confidence
LIMIT 3;

-- id   description        vector_score   rank
-- d2   near match         0.75           1.0
-- d3   weaker match       0.5            0.5
-- d1   closest match      1.0            0.0

USE RANK requires the vector ORDER BY ... <=> ... and LIMIT in the same query. Unknown sort keys return RankPolicyNotFound; there is no silent fallback to cosine ordering.

Formula references use {column}. Supported operands are REAL, INTEGER, BOOLEAN, numeric literals, {vector_score}, +, *, parentheses, and coalesce(expr, literal). BOOLEAN values coerce only inside the rank formula (TRUE = 1.0, FALSE = 0.0). TEXT, JSON, VECTOR, dotted refs, subqueries, CASE, subtraction, division, and arbitrary function calls are rejected at DDL time. * binds tighter than +; parentheses override precedence.

JOIN table ON column is a single left-outer lookup through an existing index on the joined table. The joined column is resolved to an anchor-side join column at DDL time and the protected joined-table index is used at search time. Candidates with no joined row remain eligible; joined columns evaluate as NULL, so coalesce can provide a fallback. Dropping the joined table, joined column, resolved anchor join column, formula-referenced columns, or the protected join index is refused while the rank policy depends on it.

Anchor-side join-column resolution is deterministic. If the joined column is the joined table's primary key, ContextDB first looks for <singular_joined_table>_id or <joined_table>_id on the anchor table, then falls back to a same-named anchor column, then the anchor primary key. For other joined columns, ContextDB uses a same-named anchor column when present, otherwise the anchor primary key. Ambiguous inferred anchor columns are rejected at DDL time.

min_similarity applies to raw cosine before the rank formula runs. A candidate below the similarity floor is excluded even if its joined-row metric would have made the formula large.

Current limits to account for in production designs:

INDEXED — and AUTO on the indexed route — ranks the rank formula over the bounded candidate set vector retrieval returns, then applies the final top-k; it does not force an exhaustive scan of every row in the corpus. USE VECTOR EXACT evaluates the rank formula over every allowed stored vector before the final LIMIT. If a formula does not reference {vector_score} and cosine is a weak candidate generator for the metric being optimized, use USE VECTOR EXACT rather than widening the search limit.
If more than one joined row matches a candidate, the current policy uses one matched row, chosen by highest internal RowId. Model joined data as a single current summary row when ranking semantics need to be stable.
Formula {column} references resolve anchor-row columns before joined-row columns. The reserved {vector_score} name cannot be shadowed, and an id present on both sides is rejected as ambiguous. Avoid duplicate formula column names until table-qualified references exist.
JOIN table ON column is rank-policy lookup syntax, not arbitrary SQL join syntax. It does not support predicates, composite joins, aggregation, or ordering.
The formula language is intentionally closed for safety. Functions such as min, max, clamp, time decay, division, and subtraction are not part of the current surface.
Sync currently round-trips rank policies through rendered DDL text. Structured policy replication is a future hardening item.

Column Constraints

projects is another illustrative table, referenced below by a foreign key:

CREATE TABLE projects (id UUID PRIMARY KEY, name TEXT NOT NULL)

CREATE TABLE tasks (
  id UUID PRIMARY KEY,
  description TEXT NOT NULL,
  status TEXT NOT NULL,
  confidence REAL DEFAULT 0.0,
  email TEXT UNIQUE,
  project_id UUID REFERENCES projects(id)
)

Table-level composite foreign keys are supported when the referenced parent tuple is covered by an ordered PRIMARY KEY or UNIQUE constraint:

CREATE TABLE parent (tenant INTEGER, number INTEGER, UNIQUE(tenant, number));
CREATE TABLE child (
  id INTEGER PRIMARY KEY,
  tenant INTEGER,
  number INTEGER,
  FOREIGN KEY (tenant, number) REFERENCES parent(tenant, number)
);

Constraint	Description
`PRIMARY KEY`	Unique row identifier
`NOT NULL`	Value required
`UNIQUE`	No duplicate values. On a single column, a duplicate plain `INSERT` is an idempotent no-op (returns `Ok(rows_affected=0)`); a table-level multi-column `UNIQUE (col, ...)` instead refuses loudly — see Composite Uniqueness.
`DEFAULT expr`	Default value for inserts
`REFERENCES table(col)` / `FOREIGN KEY (...) REFERENCES ...`	Foreign key — writes are rejected if the referenced row or tuple does not exist; in explicit transactions the error may surface at `COMMIT`
`IMMUTABLE`	Column is audit-frozen — INSERT sets the value once; `UPDATE`, `ON CONFLICT DO UPDATE`, sync-apply mutations, and schema-altering DDL against the column are rejected with `Error::ImmutableColumn`
`ACL REFERENCES table(col)`	Column carries the row's access-control id — a non-administrative handle reads the row only when its principal holds a matching grant; a handle with no grant-holding principal is refused the table with `Error::PrincipalRequired`

Audit-Frozen Columns

An audit-frozen column carries data that must not be silently rewritten by anyone, through any path. Declare it with IMMUTABLE:

CREATE TABLE tasks (
  id UUID PRIMARY KEY,
  task_type TEXT NOT NULL IMMUTABLE,
  description TEXT NOT NULL IMMUTABLE,
  reasoning JSON,
  confidence REAL,
  status TEXT NOT NULL DEFAULT 'active'
) STATE MACHINE (status: active -> [superseded, archived])

task_type and description are provenance — set once at INSERT and never rewritten. status and confidence remain mutable. An UPDATE tasks SET task_type = '…' returns Error::ImmutableColumn; the row is unchanged. Sync-apply across a synced edge enforces the same rule on the peer: incoming row-changes that mutate a flagged column are rejected and surface in ApplyResult.conflicts. ALTER TABLE ... DROP COLUMN, RENAME COLUMN, and column-type-altering ALTER against a flagged column are refused.

Correction without rewrite — the supersede pattern. When a recorded row turns out to be wrong, insert a new row with the corrected values and mark the original superseded:

-- Original (frozen)
INSERT INTO tasks (id, task_type, description, status)
VALUES ('11111111-1111-4111-8111-111111111111', 'sql-migration', 'migrate to contextdb', 'active');

-- Correction: a new row, not an update. Both rows remain queryable.
INSERT INTO tasks (id, task_type, description, status)
VALUES ('22222222-2222-4222-8222-222222222222', 'sql-migration', 'migrate to contextdb (rev 2)', 'active');
UPDATE tasks SET status = 'superseded' WHERE id = '11111111-1111-4111-8111-111111111111';

Nothing disappears. The audit trail shows both the original commitment and its correction.

Access-Controlled Rows (ACL)

Row-level authorization is opt-in and declared by the application, not built into the engine's tables. A data table names the column that carries each row's access-control id, and a grant table says which principal holds which id:

CREATE TABLE acl_grants (
  id UUID PRIMARY KEY,
  principal_kind TEXT,
  principal_id TEXT,
  acl_id UUID
);

CREATE TABLE notes (
  id INTEGER PRIMARY KEY,
  context_id UUID CONTEXT_ID,
  acl_id UUID ACL REFERENCES acl_grants(acl_id),
  payload TEXT
);

Grant a principal one access-control id by inserting a grant row. principal_kind is 'Agent' or 'Human'; principal_id is that principal's name:

INSERT INTO acl_grants (id, principal_kind, principal_id, acl_id)
VALUES ('33333333-3333-4333-8333-333333333333', 'Agent', 'report-reader',
        '44444444-4444-4444-8444-444444444444');

Read through a handle that names the principal. The same handle form serves an ordinary query and a bounded read session, and both answer the same rows:

let reader = database.scoped_with_constraints(
    None,
    None,
    Some(Principal::Agent("report-reader".to_string())),
);
let rows = reader.execute("SELECT payload FROM notes ORDER BY id", &HashMap::new())?;
let bounded = reader.read_session(ReadLimits::default())?
    .execute("SELECT payload FROM notes ORDER BY id", &HashMap::new())?;

Rows whose acl_id the principal was not granted are absent from both answers.

A handle that narrowed itself by context or scope but named no principal that can hold grants is refused the table with Error::PrincipalRequired. Authorization is a property the table declared, not an axis the reader opts into, so narrowing by some other axis never turns the grant filter off.

Principal::System cannot hold grants and is refused the same way.

Constraints compose. A handle that names both a context and a principal hides the rows outside its context and the rows it holds no grant for:

let reader = database.scoped_with_constraints(
    Some(BTreeSet::from([ContextId::new(context)])),
    None,
    Some(Principal::Agent("report-reader".to_string())),
);

An administrative handle — one that declared no context, no scope, and no principal — reads every row, and a table that declares no ACL REFERENCES column is unaffected: it keeps narrowing by exactly the axes it does declare.

A direct read of a CLOSED store is not a handle that declared anything. ReadSession::open on a path with no live owner, and the CLI pointed at a path, read the committed store as its owner does: no principal is declared, so every row is returned, ACL column or not. Row-level authorization therefore governs handles that declare a principal — an embedded scoped_with_constraints reader and a read served by a live owner — while who may read a closed store at all is governed by the file's permissions.

ACL is authorization, not relevance ranking. A withheld row is withheld because the reader is not entitled to it; it is never scored, ordered, or surfaced as a lower-ranked result.

Composite Uniqueness

Enforce uniqueness across a combination of columns using a table-level constraint:

CREATE TABLE edges (
  id UUID PRIMARY KEY,
  source_id UUID NOT NULL,
  target_id UUID NOT NULL,
  edge_type TEXT NOT NULL,
  UNIQUE(source_id, target_id, edge_type)
)

A duplicate (source_id, target_id, edge_type) tuple is refused with a loud, standard-SQL constraint error — Error::UniqueViolation { table, column }, with column naming every column of the constraint — never a silent Ok. Rows that share individual column values but differ in at least one constrained column are allowed. Rows with NULL in any constrained column do not participate in the composite uniqueness check.

INSERT INTO edges (id, source_id, target_id, edge_type)
VALUES ($id, $source_id, $target_id, $edge_type);
-- A second row with the identical (source_id, target_id, edge_type) tuple:
INSERT INTO edges (id, source_id, target_id, edge_type)
VALUES ($id2, $source_id, $target_id, $edge_type);
-- Err(UniqueViolation { table: "edges", column: "source_id, target_id, edge_type" })

A single-column UNIQUE (declared on one column, not as a table-level tuple) keeps its existing idempotent-no-op convention on a plain INSERT: a duplicate value returns Ok(rows_affected=0) and leaves the row count unchanged. ON CONFLICT ... DO UPDATE is unaffected either way — it always updates the conflicting row rather than erroring.

Composite Primary Key

When a row's identity is a combination of columns — not a single surrogate key — declare it with a table-level PRIMARY KEY (col, col, ...):

CREATE TABLE metric_windows (
  machine_id TEXT NOT NULL,
  sensor_id TEXT NOT NULL,
  metric TEXT NOT NULL,
  window_start INTEGER NOT NULL,
  value INTEGER NOT NULL,
  PRIMARY KEY (machine_id, sensor_id, metric, window_start)
)

The whole declared tuple is the row's identity everywhere it matters, including across synchronization: two machines writing the same (machine_id, sensor_id, metric, window_start) are the same row, and two rows differing in only one key column — even a later one such as window_start — are distinct rows and both survive a sync. Re-declaring the identical tuple is a duplicate and is refused. The columns are matched in the order declared, and the declaration round-trips through .schema and travels with synced DDL to a receiving machine.

Use a table-level PRIMARY KEY (...) for a multi-column key only; a single-column key stays the column-level id UUID PRIMARY KEY form. A table-level PRIMARY KEY (...) cannot be combined with a column-level PRIMARY KEY, and its columns must exist and be exact-matchable (not REAL, JSON, or VECTOR). A table that promises cross-machine delivery (RETAIN ... SYNC SAFE) satisfies its key requirement with a multi-column primary key just as it would with a single-column one. A composite UNIQUE constraint or a multi-column index is a uniqueness or lookup rule, never the sync identity — only PRIMARY KEY is.

Foreign Key State Propagation

Trigger a state change on this row when the referenced row transitions:

CREATE TABLE tasks (
  id UUID PRIMARY KEY,
  status TEXT NOT NULL,
  project_id UUID REFERENCES projects(id)
    ON STATE archived PROPAGATE SET invalidated
) STATE MACHINE (status: active -> [invalidated, superseded])

When a projects row transitions to archived, any tasks row referencing it transitions to invalidated.

Table Options

Table options appear after the closing ) of the column list. Multiple options can be combined. They attach to whatever table and columns you define — contextdb has no built-in tables of its own.

IMMUTABLE

Rows cannot be updated or deleted after insertion. Useful for append-only data like event logs and audit trails:

CREATE TABLE documents (
  id UUID PRIMARY KEY,
  data JSON,
  embedding VECTOR(384)
) IMMUTABLE

Table-level IMMUTABLE and RETAIN are refused together at parse time.

STATE MACHINE

Restrict a column's value transitions to declared edges:

CREATE TABLE tasks (
  id UUID PRIMARY KEY,
  status TEXT NOT NULL
) STATE MACHINE (status: draft -> [active, rejected], active -> [superseded])

Inserting a row sets the initial state. Updates that violate the transition graph are rejected.

DAG

Enforce directed acyclic graph constraint on specified edge types, preventing cycles:

CREATE TABLE edges (
  id UUID PRIMARY KEY,
  source_id UUID NOT NULL,
  target_id UUID NOT NULL,
  edge_type TEXT NOT NULL
) DAG('DEPENDS_ON', 'BASED_ON')

Inserting an edge that would create a cycle returns CycleDetected. Duplicate (source_id, target_id, edge_type) inserts are silently deduplicated.

RETAIN

Automatic row expiry. Units: SECONDS, MINUTES, HOURS, DAYS. Optional SYNC SAFE means a row is not expired here until the destination confirms it received it:

CREATE TABLE scratch (
  id UUID PRIMARY KEY,
  data TEXT
) RETAIN 24 HOURS SYNC SAFE

Can also be set via ALTER TABLE:

ALTER TABLE scratch SET RETAIN 7 DAYS;
ALTER TABLE scratch DROP RETAIN;

RETAIN says only WHEN rows expire and SYNC SAFE says only that expiry waits on delivery. Neither decides where rows travel — that is the separate SYNC clause below. Writing a direction inside the retention clause (RETAIN 24 HOURS SYNC SAFE PUSH ONLY) is a parse error naming the clause to use instead.

HISTORY

How much of each live row's version history a table keeps — a different axis from RETAIN, which says how long a row lives. HISTORY ALL (the default) keeps every superseded version. HISTORY CURRENT ONLY declares that only a row's current version has consumer value, so superseded versions may be reclaimed by the maintenance loop:

CREATE TABLE device_status (
  device_id TEXT PRIMARY KEY,
  state TEXT NOT NULL
) HISTORY CURRENT ONLY SYNC CONFLICT KEEP LATEST

HISTORY ALL keeps each superseded version's change-log, commit-index and vector-directory metadata in writer memory. On a mutable table that republishes rows often, such as a search projection that re-embeds passages or a status table, each version grows writer residency until the finite MEMORY_LIMIT refuses writes. Declare HISTORY CURRENT ONLY for these tables so the maintenance cycle can reclaim superseded versions and their metadata. Unchanged raw vector bodies share memory across scalar updates, but that sharing does not remove the metadata ceiling.

Also settable with ALTER TABLE:

ALTER TABLE device_status SET HISTORY CURRENT ONLY;
ALTER TABLE device_status SET HISTORY ALL;

RETAIN bounds rows, HISTORY bounds versions, and neither implies the other — RETAIN plus HISTORY CURRENT ONLY together are legal and independent: rows expire on the window, and each surviving row keeps only its current version.

The refusal. HISTORY CURRENT ONLY is refused on a table that both delivers rows to another machine (SYNC PUSH ONLY or SYNC TWO WAY, the default direction) and arbitrates conflicts non-overwriting (SYNC CONFLICT KEEP FIRST, the default when no conflict clause is written):

CREATE TABLE device_status (
  device_id TEXT PRIMARY KEY,
  state TEXT NOT NULL
) HISTORY CURRENT ONLY
-- refused: HISTORY CURRENT ONLY needs SYNC CONFLICT KEEP LATEST. Reclaiming
-- superseded versions leaves only the newest value, and a machine that
-- pulls this table under SYNC CONFLICT KEEP FIRST would file that newest
-- value as the FIRST value for its key.

Two ways out: declare SYNC CONFLICT KEEP LATEST (the table carries current truth, so last-writer-wins is the correct arbitration), or SYNC OFF (the table never leaves this machine, so no peer can ever be handed a stale first value). The same refusal fires on ALTER TABLE ... SET HISTORY CURRENT ONLY, on ALTER TABLE ... SET SYNC CONFLICT KEEP FIRST narrowing an already-current-only table back to keep-first, on ALTER TABLE ... SET SYNC ... widening a keep-first current-only table into a delivering direction, and on an arriving definition from another machine — every door that can produce the combination refuses it, not only a local CREATE TABLE.

IMMUTABLE and HISTORY CURRENT ONLY are refused together at parse time: an immutable table never supersedes a row, so it has no version history to reclaim.

What changes and when. ALTER TABLE t SET HISTORY CURRENT ONLY on a table that has already accumulated versions is a declaration change, not an immediate data operation — the accumulated versions are reclaimed on the next maintenance cycle, not synchronously within the statement. Setting HISTORY ALL stops reclaiming versions from that point on; it does not restore versions already reclaimed under HISTORY CURRENT ONLY — the clause is not a time machine. A STATE MACHINE table that also declares HISTORY CURRENT ONLY keeps its current state and its transition constraint (enforcement is evaluated at write time against the current version), but loses the ability to read back the sequence of past states.

MAINTAIN COUNT

Which index-prefix counts the engine keeps current on every write to this table. A count is keyed by an ordered column list and the values under it — not by an index — so two indexes that share leading columns name one count, and it is maintained once.

A table that declares nothing maintains no prefix count. COUNT(*) still answers exactly: the query opens the exact prefix range on the index and counts the live postings, charging one index-entry touch per posting, inside the statement's own work, memory and time budget. Declaring the column list a hot COUNT(*) uses turns that read into a single stored number, and costs one authenticated write per matching index write.

CREATE TABLE events (
  id UUID PRIMARY KEY,
  context_id UUID NOT NULL,
  connector_id TEXT NOT NULL,
  source_instance_ref TEXT NOT NULL,
  UNIQUE (context_id, id),
  UNIQUE (context_id, connector_id, source_instance_ref, id)
) MAINTAIN COUNT (context_id), (context_id, connector_id, source_instance_ref)

Each list is a parenthesised column list with the same optional per-column ASC/DESC that CREATE INDEX accepts, defaulting to ASC. One statement replaces the whole declared set:

ALTER TABLE events SET MAINTAIN COUNT (context_id);
ALTER TABLE events SET MAINTAIN COUNT ALL PREFIXES;
ALTER TABLE events SET MAINTAIN COUNT NONE;
ALTER TABLE events SET MAINTAIN COUNT DEFAULT;

ALL PREFIXES maintains every prefix length of every index the table carries, including the ones the engine derives for foreign keys and unique constraints. NONE is the default spelled out. DEFAULT clears the declaration and returns the table to silence; it is accepted only on ALTER TABLE, because a fresh CREATE TABLE has nothing to clear.

Changing the declaration on a table that already holds rows is online and atomic: the counts the new declaration names are recomputed, and the ones it drops are removed, in the same transaction that installs the declaration. The allocation is admitted by the memory accountant first, so a recompute that cannot fit is the ordinary typed budget refusal with nothing installed.

Which route a COUNT(*) took. .explain says so in one of three states, and names the statement that would change it when a statement would.

A stored count is reachable only from a store read as a committed image — the direct-file read route. A writable handle and the owner read route count the live postings over the same exact prefix range instead. Every one of the three answers is exact; only the first is O(1):

IndexScan { index: events_by_context }
  predicates_pushed: [context_id]
  maintained count: used (context_id)

IndexScan { index: events_by_context }
  predicates_pushed: [context_id]
  maintained count: declared (context_id), counted from the index range on this read route

IndexScan { index: events_by_context }
  predicates_pushed: [context_id, connector_id]
  maintained count: not declared — ALTER TABLE events SET MAINTAIN COUNT (context_id), (context_id, connector_id) makes this O(1)

The middle line is not a declaration problem and no DDL changes it: the table maintains that count and every write keeps it current, but this read route counted postings. Only the third line names a statement, and that statement is runnable as printed. Because SET MAINTAIN COUNT replaces the whole declared set, the advice carries the lists the table already declares alongside the missing one — following it never drops a count you already asked for.

The refusals. A list no index leads with is refused, naming the list and the indexes considered — a count nothing can read is not a thing to maintain. A list a primary key or unique constraint already bounds at 0 or 1 is refused too: an exact-key range over the index reads at most one posting and gives the same answer, so the count would store a number nobody can disagree with. That rule is not policy — ALL PREFIXES does not maintain such a count either. A duplicate list, a repeated column and an unknown column are refused by name.

SYNC

Where a table's rows travel under synchronization. It applies to retained and non-retained tables alike, persists with the table definition, renders in .schema, and travels with the definition to other machines:

CREATE TABLE windows (
  id UUID PRIMARY KEY,
  body TEXT
) RETAIN 48 HOURS SYNC SAFE SYNC TWO WAY

Clause	Meaning
`SYNC OFF`	rows stay on the machine that wrote them
`SYNC PUSH ONLY`	rows go out and never come back
`SYNC PULL ONLY`	rows arrive from other machines; nothing local goes out
`SYNC TWO WAY`	rows travel both ways

A table that declares no direction is SYNC TWO WAY — the default, and the recovery contract: delete a machine's database, recreate it against the same tenant, and every still-live row comes back.

Changeable on an existing table — but windows is SYNC SAFE, and SYNC SAFE combined with SYNC PULL ONLY can never deliver the table outward for a destination to confirm, so this is refused (see below):

ALTER TABLE windows SET SYNC PULL ONLY;

On a table without SYNC SAFE, the same direction change is accepted:

ALTER TABLE items SET SYNC PULL ONLY;

SYNC SAFE with a direction that never delivers the table (SYNC OFF or SYNC PULL ONLY) is refused when it is written — at CREATE, at ALTER, and when the definition arrives from another machine. The promise could never be kept, so the rows would simply never expire. Plain RETAIN with no delivery promise may declare any direction, including SYNC OFF for a colocated installation that keeps one copy.

A table that syncs needs a sync identity — the covering-index requirement. Any direction but SYNC OFF tells apart rows arriving from another machine by that table's declared identity: a PRIMARY KEY (single-column or a table-level PRIMARY KEY (a, b, ...)), or, failing that, an indexed id column as a fallback. A table with neither — a keyless table — has no way to do that. If a keyless table declares SYNC OFF this never matters, since its rows were never eligible to leave the machine either way. But a keyless table that would otherwise sync makes a push refuse loudly (Error::SyncError, naming all three fixes) instead of silently reporting success while that table's rows never actually cross the wire.

The keyless fallback — a column named id with no declared key — requires a covering index on id. Without that index, sync apply refuses with exact sync key probe on keyless(id) has no covering index (the table name fills the first slot).

-- Keyless and would sync (the default SYNC TWO WAY) — push refuses:
CREATE TABLE events (payload TEXT NOT NULL, occurred_at INTEGER NOT NULL)

-- Fix one of:
CREATE TABLE events (id UUID PRIMARY KEY, payload TEXT NOT NULL, occurred_at INTEGER NOT NULL)
-- or
CREATE TABLE events (payload TEXT NOT NULL, occurred_at INTEGER NOT NULL, id UUID)
CREATE INDEX events_id_idx ON events (id)
-- or
CREATE TABLE events (payload TEXT NOT NULL, occurred_at INTEGER NOT NULL) SYNC OFF

SYNC CONFLICT

Which value survives when the same row is written on more than one machine, or the same key is re-sent. Like the direction clause, it is declared on the table itself, persists with the definition, renders in .schema, and travels with the synced definition to other machines, so the durable hub honors the table's declared policy:

CREATE TABLE observations (
  id UUID PRIMARY KEY,
  body TEXT
) SYNC CONFLICT KEEP FIRST

Clause	Meaning
`SYNC CONFLICT KEEP FIRST`	write-once — the first value written for a key stays; a re-send of that key does not overwrite it. Later writes to that key lose — including deletes, which are arbitrated exactly like any other contending write. A pulling edge reports the delete as skipped and the row survives.
`SYNC CONFLICT KEEP LATEST`	last-writer-wins — a re-send of a key replaces the value already there. The last hub-accepted write wins; deletes propagate.

A table that declares no policy is SYNC CONFLICT KEEP FIRST — the non-overwriting default, so a re-send of an existing key never silently rewrites it.

On a hub the policy is resolved in one order: a system table's baked policy wins first, then the table's own declaration, then the default. So an application table always gets exactly the policy it declared, while the engine's own distributed tables keep the policy their contract requires.

The policy composes with the retention, history, and direction clauses on one table:

CREATE TABLE windows (
  id UUID PRIMARY KEY,
  body TEXT
) RETAIN 48 HOURS SYNC SAFE HISTORY ALL SYNC TWO WAY SYNC CONFLICT KEEP FIRST

PROPAGATE ON EDGE

Cascade state changes along graph edges when a row transitions:

CREATE TABLE tasks (
  id UUID PRIMARY KEY,
  status TEXT NOT NULL
) STATE MACHINE (status: active -> [invalidated, superseded])
  PROPAGATE ON EDGE CITES INCOMING STATE invalidated SET invalidated

When a tasks row transitions to invalidated, rows connected via incoming CITES edges also transition to invalidated. Options: INCOMING, OUTGOING, BOTH for edge direction. MAX DEPTH n limits traversal. ABORT ON FAILURE rolls back if any propagation fails.

PROPAGATE ON STATE ... EXCLUDE VECTOR

Remove a row's vector from similarity search results when it enters a given state, without deleting the row:

CREATE TABLE tasks (...)
  PROPAGATE ON STATE invalidated EXCLUDE VECTOR
  PROPAGATE ON STATE superseded EXCLUDE VECTOR

Combining Options

Options compose — a real-world table might use several:

CREATE TABLE tasks (
  id UUID PRIMARY KEY,
  description TEXT NOT NULL,
  status TEXT NOT NULL,
  confidence REAL,
  created_at TIMESTAMP DEFAULT NOW(),
  project_id UUID REFERENCES projects(id)
    ON STATE archived PROPAGATE SET invalidated,
  embedding VECTOR(384)
) STATE MACHINE (status: active -> [invalidated, superseded])
  PROPAGATE ON EDGE CITES INCOMING STATE invalidated SET invalidated
  PROPAGATE ON STATE invalidated EXCLUDE VECTOR
  PROPAGATE ON STATE superseded EXCLUDE VECTOR

Expressions and Operators

Comparison

=, !=, <>, <, <=, >, >=

Logical

AND, OR, NOT

Arithmetic

+, -, *, /

Pattern Matching

WHERE name LIKE 'sensor%'       -- % matches any substring
WHERE name LIKE 'item_3'        -- _ matches single character
WHERE name NOT LIKE '%draft%'

Range

WHERE confidence BETWEEN 0.5 AND 1.0
WHERE confidence NOT BETWEEN 0 AND 0.1

Set Membership

WHERE status IN ('active', 'draft')
WHERE id IN (SELECT id FROM other_table WHERE ...)
WHERE status NOT IN ('deleted', 'archived')

Subqueries in IN must select exactly one column.

NULL Checks

WHERE superseded_at IS NULL
WHERE embedding IS NOT NULL

Functions

Function	Returns	Description
`COUNT(*)`	INTEGER	Count all rows
`COUNT(col)`	INTEGER	Count non-NULL values in column
`SUM(col)`	INTEGER or FLOAT	Add the non-NULL values in a numeric column
`COALESCE(a, b, ...)`	varies	First non-NULL argument
`NOW()`	TIMESTAMP	Current Unix timestamp

COUNT and SUM operate over the entire result set. No GROUP BY or HAVING — use CTEs or application-level grouping for aggregation.

NOW() in an INSERT ... VALUES list is refused for a NOT NULL column. Writing it into a nullable column is accepted, and UPDATE ... SET ts = NOW() is accepted for either; but

CREATE TABLE audits (id UUID PRIMARY KEY, ts TIMESTAMP NOT NULL);
INSERT INTO audits (id, ts) VALUES ($id, NOW());
-- Error: plan error: unsupported expression in schema enforcer

The NOT NULL check runs in the schema enforcer, which evaluates literals rather than call expressions. Declare the column DEFAULT NOW() and omit it from the INSERT instead — the default is applied for you and the row lands with a real timestamp:

CREATE TABLE audits (id UUID PRIMARY KEY, ts TIMESTAMP NOT NULL DEFAULT NOW());
INSERT INTO audits (id) VALUES ($id);

SUM skips NULLs, returns NULL (not zero) when no row reaches it, adds an INTEGER column as an integer and refuses an out-of-range total rather than wrapping it, and adds a FLOAT column as a float. A non-numeric column is an error. The output column is named after the function unless you alias it:

SELECT SUM(amount) FROM payments WHERE status = 'settled';
SELECT SUM(amount) AS settled_total FROM payments;

An aggregate read through a cursor finishes across pages: each fetch adds up as much input as that fetch's work budget allows, and a fetch that runs out returns an empty page with more still to come. The total arrives on the page that reaches the end of the input.

Parameter Binding

In the Rust API, parameters are passed as HashMap<String, Value>:

let mut params = HashMap::new();
params.insert("item_id".into(), Value::Uuid(id));
params.insert("type".into(), Value::Text("sensor".into()));

let result = db.execute(
    "SELECT * FROM items WHERE id = $item_id AND type = $type",
    &params,
)?;

The CLI does not support parameter binding — use literal values directly.

Graph Traversal

Graph queries use GRAPH_TABLE in the FROM clause with openCypher-subset MATCH patterns. The graph executor uses dedicated adjacency indexes and bounded BFS — graph traversal is a native operator, not recursive SQL.

Syntax

SELECT columns FROM GRAPH_TABLE(
  edge_table
  MATCH pattern
  [WHERE condition]
  COLUMNS (expr AS alias, ...)
)

The edge_table is a table with source_id, target_id, and edge_type columns.

Patterns

-- Outgoing edges
MATCH (a)-[:DEPENDS_ON]->(b)

-- Incoming edges
MATCH (a)<-[:BASED_ON]-(b)

-- Bidirectional
MATCH (a)-[:RELATES_TO]-(b)

-- Any edge type
MATCH (a)-[]->(b)

Variable-Length Paths

-- Between 1 and 3 hops
MATCH (a)-[:DEPENDS_ON]->{1,3}(b)

-- 1 to 10 hops (explicit upper bound required)
MATCH (a)-[:EDGE]->{1,10}(b)

An explicit upper bound is always required. Maximum traversal depth enforced by the engine is 10.

Filtering and Projection

Use WHERE to filter after traversal, COLUMNS to project results:

SELECT target_id FROM GRAPH_TABLE(
  edges
  MATCH (a)-[:DEPENDS_ON]->{1,3}(b)
  WHERE a.id = '550e8400-e29b-41d4-a716-446655440000'
  COLUMNS (b.id AS target_id)
)

Composing with SQL via CTEs

Graph results become a relational CTE for joins, filters, or vector search:

WITH deps AS (
  SELECT b_id FROM GRAPH_TABLE(
    edges
    MATCH (a)-[:DEPENDS_ON]->{1,3}(b)
    WHERE a.id = $start
    COLUMNS (b.id AS b_id)
  )
)
SELECT t.id, t.status FROM tasks t
INNER JOIN deps ON t.id = deps.b_id
WHERE t.status = 'active'

Graph + Vector: Neighborhood Similarity Search

Find semantically similar items within a graph neighborhood:

WITH neighborhood AS (
  SELECT b_id FROM GRAPH_TABLE(
    edges
    MATCH (a)-[:RELATES_TO]->{1,2}(b)
    COLUMNS (b.id AS b_id)
  )
),
candidates AS (
  SELECT id, name, embedding
  FROM items i
  INNER JOIN neighborhood n ON i.id = n.b_id
  WHERE i.is_deprecated = FALSE
)
SELECT id, name FROM candidates
ORDER BY embedding <=> $query
LIMIT 5

Vector Similarity Search

The `<=>` Operator

Cosine distance between two vectors. Used in ORDER BY for nearest-neighbor search:

-- Rust API with parameter binding
SELECT id, data FROM documents
ORDER BY embedding <=> $query_vector
LIMIT 10

-- CLI with vector literal (against a table with a short vector dimension,
-- so the literal can be written out in full)
CREATE TABLE evidence (id UUID PRIMARY KEY, vector_text VECTOR(4));

SELECT id FROM evidence
ORDER BY vector_text <=> [0.1, 0.2, 0.3, 0.4]
LIMIT 10

Lower distance = more similar. A LIMIT clause is required — unbounded vector searches are rejected.

The query vector can also come from an existing row:

SELECT id, data FROM documents
WHERE id != $query_id
ORDER BY embedding <=> ROW_VECTOR('documents', 'embedding', $query_id)
LIMIT 10

ROW_VECTOR(table, column, key) is only valid as the right side of <=> in ORDER BY. The table and column arguments are string literals naming a VECTOR(n) column, and key is a literal or parameter matched against the source table's natural key, usually its primary key. The source vector is read from the same MVCC snapshot as candidate filtering and vector scoring. Scoped handles honor source-row visibility: a hidden source row returns the same typed read-scope error as an explicit anchor read. Missing source tables return TableNotFound, non-vector source columns return UnknownVectorIndex, dimension mismatches return VectorIndexDimensionMismatch, missing source rows return PersistedRowVectorRowMissing, and rows with NULL vector cells return PersistedRowVectorCellNull.

Pre-Filtered Search

Combine WHERE filters with vector ranking. The engine filters first, then scores only matching rows:

SELECT id, description FROM tasks
WHERE status = 'active'
ORDER BY embedding <=> $query
LIMIT 5

Indexing

Every vector column has a maintained local search layout. An unpartitioned column has one logical layout; a partitioned column has one for each declared key tuple. A partition is a local search layout, not an authorization boundary, tenant, or sync direction. Authorization still applies to the rows a caller may read, and a table may use any declared sync direction.

Declare a partitioned column in this canonical order (the same order .schema prints):

CREATE TABLE vector_items (
  item_id UUID PRIMARY KEY,
  scope_id UUID NOT NULL,
  kind TEXT NOT NULL,
  embedding VECTOR(768)
    PARTITION_KEY (scope_id, kind)
    MAX_PARTITIONS 256
    SEARCH_MODE AUTO
    AUTO_INDEX_AT 10000
    HNSW (M = 24, EF_CONSTRUCTION = 400, EF_SEARCH = 128)
    CONSOLIDATION (CHANGE_PERCENT = 25, TOMBSTONE_PERCENT = 5)
);

WITH (quantization = 'SQ8'), when used, follows VECTOR(768) and precedes these clauses. PARTITION_KEY is optional. Its columns must be non-null columns on the same table with an exact identity type (UUID, TEXT, INTEGER, BOOLEAN, TIMESTAMP, or TXID); a key cannot repeat a column or include the vector column. MAX_PARTITIONS is legal only with PARTITION_KEY; omitting it means the effective limit is 256, and .schema prints that value. A key value written to a row, locally or through sync, must be of its column's declared type or the write is refused naming that column; the declaration itself is not at fault. The limit counts layouts containing a live indexed vector or a version still needed by a held snapshot. A NULL vector creates no layout. A write that would exceed the limit is refused as one atomic write; it does not leave a row accepted without its vector.

AUTO_INDEX_AT, HNSW, and CONSOLIDATION are optional per-column policy clauses and follow SEARCH_MODE in the canonical rendered order. AUTO_INDEX_AT is the first aggregate allowed-vector count for the whole requested answer at which AUTO requires the maintained indexed route; below it, AUTO compares exactly only when the existing resource limits permit. It is not a per-partition threshold. HNSW names the maintained graph's M (neighbour links per point), EF_CONSTRUCTION (candidate work while building), and EF_SEARCH (candidate work while searching). EF_CONSTRUCTION must be at least M.

CONSOLIDATION controls when maintenance rebuilds an otherwise healthy graph. CHANGE_PERCENT counts pending inserts plus tombstones against the live-plus-retained partition population; TOMBSTONE_PERCENT counts tombstones alone. Because tombstones are part of total change, the tombstone trigger can decide first only when its percentage is lower than the change percentage. Each value is from 1 through 100. If the clause or one member is silent, the effective defaults are 20% total change and 10% tombstones, and .schema keeps that default silent. At each maintenance wake, the change percentage is the policy bound that decides when a healthy partition's retained post-base journal is published into a replacement generation. Writes can take the journal past that percentage between wakes, and the percentage is not a byte ceiling. ALTER ... SET CONSOLIDATION (...) changes the named members while retaining omitted members; ALTER ... SET CONSOLIDATION DEFAULT clears both declarations. The policy persists in schema, travels with it, and is honored by in-memory and file-backed maintenance. CONSOLIDATION NONE (including ALTER ... SET CONSOLIDATION NONE) disables automatic threshold consolidation without disabling the maintained index, initial construction, topology replacement, or repair. Because healthy-tail threshold rebuilds no longer advance the covered frontier, the post-base change journal remains retained until a required replacement or repair publishes, or threshold consolidation is re-enabled and becomes due; choose NONE with that disk-retention consequence in mind. Both vector inspection surfaces show the declared mode and members beside their effective mode and members. declared_consolidation_mode is NULL when the declaration is silent, thresholds when either percentage is explicit, and none for explicit CONSOLIDATION NONE; effective mode is always thresholds or none.

On CREATE TABLE, omitting either clause preserves the compatibility profile and .schema renders that silence. Its current defaults are: F32 uses (M, EF_CONSTRUCTION, profile EF_SEARCH) of (16, 200, 10) through 2,500 live vectors, (16, 200, 20) from 2,501 through 5,000, (24, 400, 400) from 5,001 through 50,000, and (16, 200, 200) above 50,000; SQ8/SQ4 use (8, 32, min(96,max(32,n))) through 5,000 and (12, 64, 128) above 5,000. The undeclared AUTO_INDEX_AT defaults are 1,000 for F32 and 5,001 for SQ8/SQ4. An undeclared EF_SEARCH uses at least its profile and the existing 10 * k query default; an explicit value is raised only to reach k.

The F32 search breadth default is now narrower for small layouts. An F32 vector column that does not declare EF_SEARCH now searches a layout of up to 2,500 live vectors with a profile breadth of 10, and a layout of 2,501 through 5,000 with 20. The query default of 10 * k still applies, so a top-10 query searches at least 100 candidates. M and EF_CONSTRUCTION for these bands are unchanged, and the bands above 5,000 are unchanged. A column that declares EF_SEARCH keeps exactly its declared breadth; declare one, for example ALTER TABLE t ALTER COLUMN embedding SET HNSW (EF_SEARCH = 200), to search more widely.

Change the policy online with the ALTER forms above. DEFAULT on AUTO_INDEX_AT clears that declaration; SET HNSW DEFAULT clears the whole group, while DEFAULT for a named HNSW member clears only that member and omitted members retain their declarations. Each statement changes its named HNSW members atomically. AUTO_INDEX_AT and EF_SEARCH affect newly opened queries without rebuilding a graph. A change to the effective M or EF_CONSTRUCTION topology schedules a bounded replacement generation; the previous complete graph continues serving, and an older policy revision cannot publish over a newer declaration. An explicit value that resolves to the serving topology retains its authored identity without forcing a redundant build. Explicit AUTO_INDEX_AT, HNSW, and CONSOLIDATION values persist, participate in schema identity, and render in this canonical order.

An operator can raise the limit online, but cannot lower it below live-plus-retained use:

ALTER TABLE vector_items ALTER COLUMN embedding SET MAX_PARTITIONS 512;
ALTER TABLE vector_items ALTER COLUMN embedding SET SEARCH_MODE INDEXED;
ALTER TABLE vector_items ALTER COLUMN embedding SET AUTO_INDEX_AT 10000;
ALTER TABLE vector_items ALTER COLUMN embedding SET HNSW (EF_SEARCH = 128);
ALTER TABLE vector_items ALTER COLUMN embedding SET CONSOLIDATION (CHANGE_PERCENT = 11);
ALTER TABLE vector_items ALTER COLUMN embedding SET CONSOLIDATION NONE;

Changing a key value moves that row's non-NULL vector between local layouts in the same commit. Changing the key declaration itself, or dropping, renaming, or changing the type/nullability of a key component while the declaration remains, is refused through dependent-DDL rules. Rename the vector column through otherwise legal DDL to move its local state with it, or make a replacement table with the desired vector partition key and copy the wanted rows; the replacement vector column creates the corresponding local vector index.

Search modes and query override

SEARCH_MODE sets the column default:

AUTO decides by count: it compares the aggregate allowed count for the whole requested answer against the column's effective AUTO_INDEX_AT. Below that count, the answer is exact when the active limits can pay for it and a typed refusal when they cannot; memory never silently picks the indexed route on AUTO's behalf. At or above that count, AUTO admits indexed planning for the whole answer, then chooses work per Context: a Context whose live vector count is at or below its resolved EF_SEARCH is compared exactly, while a larger Context traverses its maintained graph. ContextDB merges both kinds of candidates into the same global answer. For F32 the automatic switch is at 1,000 allowed vectors; for SQ8/SQ4 it is at 5,001. These counts apply to the whole requested answer, not separately to each partition.
EXACT compares every allowed stored vector, or returns a typed budget refusal. It never quietly substitutes an indexed answer. Exact means exact over the stored column values; SQ8 and SQ4 stay quantized by design.
INDEXED requires a maintained bounded route, including for a small non-empty layout. If that route is not ready, it returns a typed unavailable-route refusal instead of a full scan. It traverses the graph even when AUTO would exact-compare that same small Context.

Override the declaration for one vector-nearest-neighbour query after vector ordering and before USE RANK or LIMIT:

SELECT passage_id
FROM event_search_passages
WHERE context_id = $context
ORDER BY vector_text <=> $query
USE VECTOR EXACT
LIMIT 10;

USE VECTOR is legal only with vector ordering. The selected mode covers the whole requested scope: if any required layout cannot satisfy INDEXED, the query refuses rather than omit that layout or return a partial list. A missing or empty authorized tuple is a complete empty result.

Initial graph construction and repair run only through maintenance. Declarations, writes, database open, and queries do not wait for a full build. Engine-owned maintenance advances in the background; caller-driven hosts advance it with Database::run_maintenance_cycle or CLI .maintenance run. While a required indexed route is unavailable, INDEXED refuses and AUTO may compare exactly only inside its existing budget. Ordinary relational/graph reads and admitted writes remain usable.

For an exhaustive AUTO query over dormant raw partitions, a refused whole-partition load can fall back to reading one stored vector at a time, preserving the exact answer without installing a resident partition. For the same unrestricted search, a writable handle explicitly requesting EXACT retains whole-partition admission and can be refused at that load under the same ceiling.

Partition scope, filters, and one answer

Equality on every key component selects one layout. A finite IN or equivalent OR selects a finite set; a prefix of a composite key, or no key predicate, searches every authorized matching layout. A named tuple that does not exist returns no rows and never broadens to another tuple. ContextDB searches selected layouts independently, then merges candidates by score before USE RANK and LIMIT. The result is one global top-k, with the existing ascending row-id tie-break for equal scores — never one limit per layout and never iteration-order results.

WHERE filters still decide which rows are allowed inside that scope. A small allowed set may be compared exactly under AUTO; a large one needs a bounded filtered-index route. If ContextDB has no bounded way to find or evaluate the allowed rows, INDEXED refuses before a table-wide predicate scan. AUTO uses exact work only when the active budget can pay for it; EXACT remains exhaustive within its declared budget. .explain names a supporting ordinary CREATE INDEX when one is a safe recovery, otherwise it says to use EXACT or rewrite the filter. It never hides a full table scan behind indexed search.

Explain

.explain reports the requested and resolved mode, declared partition key, whether the query selects one, several, or all authorized layouts, the route used by each Context, the global candidate merge, and any safe fallback or refusal reason. It redacts unauthorized keys and counts. A constrained handle's explanation omits per-partition HNSW numbers, whose adaptive values could reveal the hidden population. For an unrestricted caller, disclosed M, EF_CONSTRUCTION, and policy revision describe the snapshot's serving graph; EF_SEARCH describes the current query policy at its requested k. A query-only policy change therefore applies immediately even while the serving build revision is older. Its authorized count and memory refusals use only authorized identities and the operation's available headroom. Explaining is metadata-only: it does not build an index, replay a journal, start repair, or fault every graph or vector page into memory.

SQL Comments

Both styles are stripped before parsing:

-- Line comment
SELECT * FROM items; /* Block comment */

Unsupported Features

These are explicitly rejected with descriptive error messages:

Feature	Error
`WITH RECURSIVE`	`RecursiveCteNotSupported`
Window functions (`OVER`)	`WindowFunctionNotSupported`
`CREATE PROCEDURE` / `CREATE FUNCTION`	`StoredProcNotSupported`
Full-text search (`WHERE col MATCH pattern`)	`FullTextSearchNotSupported`
`GROUP BY` / `HAVING`	Not supported
`UNION` / `INTERSECT` / `EXCEPT`	Not supported
`INSERT ... SELECT`	Not supported
Subqueries outside `IN`	`SubqueryNotSupported`
AVG, MIN, MAX	Not supported (COUNT and SUM only)

Indexes

Indexes accelerate filtered scans. Declared indexes maintain a sorted B-tree from the index key to the underlying row ids, with MVCC postings so every read sees the set of rows live at its snapshot.

CREATE INDEX

CREATE INDEX idx_bucket ON documents (bucket);

-- Per-column direction
CREATE INDEX idx_recent ON tasks (created_at DESC, id DESC);

-- Composite index (leading-prefix equality pushdown)
CREATE INDEX idx_items ON items (context_id, item_type, created_at DESC, id DESC);

Indexable column types: INTEGER, TEXT, UUID, TIMESTAMP, TXID, BOOLEAN, REAL. JSON and VECTOR columns are rejected at DDL time with ColumnNotIndexable; extract JSON fields into typed columns or use HNSW for vectors.

Index names are scoped to the table. A duplicate name on the same table returns DuplicateIndex; the same name on two different tables is allowed.

DROP INDEX

DROP INDEX idx_bucket ON documents;
DROP INDEX IF EXISTS idx_bucket ON documents;

DROP INDEX without IF EXISTS on a nonexistent index returns IndexNotFound. DROP INDEX IF EXISTS is idempotent (returns rows_affected == 0).

ALTER TABLE DROP COLUMN

ALTER TABLE t DROP a;                     -- COLUMN is optional
ALTER TABLE t DROP COLUMN a;              -- defaults to RESTRICT
ALTER TABLE t DROP COLUMN a RESTRICT;     -- explicit
ALTER TABLE t DROP COLUMN a CASCADE;      -- drops dependent indexes

Under RESTRICT (the default), dropping a column referenced by any index returns ColumnInIndex { table, column, index } naming the first dependent index in declaration order. A vector partition-key component instead returns VectorPartitionKeyInUse { table, column, index } with the required next step: create a replacement table with the desired partition key and copy the wanted rows. Under CASCADE, every index whose column list mentions the target column is removed, and the returned QueryResult.cascade carries a dropped_indexes list.

Ordering

Indexes sort by declared direction per column. NULL sorts LAST under ASC and FIRST under DESC, matching the engine's ORDER BY convention. Float64 values use f64::total_cmp — NaN sorts greater than any finite value, matching the ordering test suite.

Auto-Indexes

PRIMARY KEY and UNIQUE columns automatically acquire a backing index named __pk_<col> for PRIMARY KEY, __unique_<col> for a single-column UNIQUE, and __unique_<col1>_<col2>... for a composite UNIQUE (col1, col2, ...) constraint. These indexes exist so PK / UNIQUE constraint probes run in O(log n) and so SELECT ... WHERE pk_col = $v queries pick an IndexScan without requiring a user CREATE INDEX. Composite foreign keys also create child-side __fk_... auto-indexes so parent deletes and tuple validation do not rely on table scans. A table whose columns include source_id, target_id, and edge_type — the edge-row shape graph traversal uses — additionally gets an internal __graph_edge_source_target_type route index over that column triple, so adjacency lookups do not scan. Because it covers the same three columns, it ties a user-declared (source_id, target_id, edge_type) index and wins routing only by creation order; both are fully index-driven.

Auto-indexes are elided from .schema output to keep schema printouts focused on user-authored DDL. They remain visible in EXPLAIN <query> output as index candidates so agents can programmatically confirm that a query routed through the auto-index rather than a table scan.

When several user-declared or auto-indexes can route a filtered scan, contextdb chooses the index with the longest matched leading prefix. Equality, IN, and IS NULL on the first indexed column can push adjacent suffix equalities into the same B-tree probe, so an index on (source_id, target_id, edge_type) is preferred over (source_id) for WHERE source_id IN (...) AND target_id = ... AND edge_type = .... If two indexes match the same number of leading columns, the more selective leading predicate wins (= / IN before range predicates and !=, before IS NULL / IS NOT NULL). A remaining tie is resolved by index creation order. This choice is intentionally based on filter selectivity, not ORDER BY; a later sort can still be elided when the chosen index order proves compatible with the requested ordering.

User-declared index names must not begin with __pk_, __unique_, or __fk_. CREATE INDEX __pk_id ON t (id) returns ReservedIndexName { table, name, prefix }.

Index-Selection Trace Reasons

.explain <sql> and QueryResult.trace expose the chosen route and the rejected index candidates. A routed composite prefix renders every pushed column in index order:

IndexScan { index: idx_edges_route }
  predicates_pushed: [source_id, target_id, edge_type]
  indexes_considered: [idx_edges_source: fewer predicate columns matched than chosen index]

Rejected candidates use stable reason strings so commercial products and agents can distinguish a missed index from a deliberate planner decision:

Reason	Meaning
`first column not in WHERE`	The index's leading column is absent from the filter.
`function call in predicate`	The leading column is wrapped in a function such as `UPPER(col)`.
`arithmetic in predicate`	The leading column appears inside arithmetic such as `col + 1`.
`non-literal rhs`	The leading predicate compares to another column or subquery rather than a literal or bound parameter.
`LIKE is residual-only`	`LIKE` remains a residual filter and does not drive an index scan.
`fewer predicate columns matched than chosen index`	Another index matched a longer leading prefix.
`lower selectivity than chosen index`	Another index matched the same prefix length with a more selective leading predicate.
`tied with chosen index; lost by creation order`	The candidates were otherwise equivalent and the earlier index won.
`no pinned vertex`	A graph adjacency candidate was rejected because the single-hop graph query had no pinned start or target vertex.

Graph Adjacency Probe

Single-hop GRAPH_TABLE ... MATCH queries with a pinned start or target vertex route through the per-vertex graph adjacency index automatically. No CREATE INDEX statement or schema change is required. In the CLI, run .trace on before the query to see the physical routing:

trace: AdjacencyProbe index=forward_adj pushed=[a.id] rows_examined=5

forward_adj and reverse_adj are public trace identifiers for the chosen direction. For a single-hop query pinned directly by a.id or b.id, rows_examined is the pinned vertex's visible degree. When the pin is resolved from node metadata, the count also includes the start/target resolution work before the degree walk. An unpinned single-hop query reports EdgesScan and records the rejected adjacency candidate with reason no pinned vertex in the programmatic trace. Multi-hop or variable-length traversals report GraphBfs.

Error variants

Error	When it fires
`IndexNotFound { table, index }`	`DROP INDEX` without `IF EXISTS` on a missing index
`DuplicateIndex { table, index }`	`CREATE INDEX` with a name already in use on the same table
`ColumnNotIndexable { table, column, column_type }`	`CREATE INDEX` on a `JSON` or `VECTOR` column
`ColumnInIndex { table, column, index }`	`ALTER TABLE ... DROP COLUMN c RESTRICT` on a column referenced by an index
`VectorPartitionKeyInUse { table, column, index }`	Dropping, renaming, or changing a vector partition-key component; create a replacement table/index and copy the wanted rows
`ColumnNotFound { table, column }`	`CREATE INDEX` naming a column that does not exist on the table — also the class every `WHERE` / `JOIN ... ON` / `ORDER BY` / `SELECT`-list unknown-column or unrecognized-qualifier reference uses (see Column Qualifiers and the `SELECT` section above)
`ReservedIndexName { table, name, prefix }`	`CREATE INDEX` using a name that begins with `__pk_`, `__unique_`, or `__fk_` (reserved for auto-indexes)
`UniqueViolation { table, column }`	A duplicate value on a `UNIQUE` column, or a duplicate tuple on a table-level `UNIQUE (col, ...)` (`column` names every column of the constraint); also a `PRIMARY KEY` duplicate
`ColumnTypeMismatch { table, column, expected, actual }`	A value that is not an embedding, the text spelling of one, or a permitted `NULL` was written into a `VECTOR(n)` column (see Column Types)

Tenant policy and event custody

The authoritative hub declares application policy before an edge installs the tables:

DECLARE TENANT TABLE POLICY events
  SYNC PUSH ONLY SYNC CONFLICT KEEP FIRST IMMUTABLE
  DELIVERY MANIFEST OVER event_anchors
  EDGE DISCARD AFTER OUTCOME;
DECLARE TENANT TABLE POLICY event_anchors
  SYNC PUSH ONLY SYNC CONFLICT KEEP FIRST IMMUTABLE;
SHOW TENANT TABLE POLICY FOR events;

Declarations contain existing sync, conflict, retention, and history options, plus the manifest member-table list and edge-discard mode. They create no application table. Before the first binding, replacement advances the declaration version and canonical digest. After binding, TenantPolicyBound refuses replacement. A sync edge receives DeclareRequiresAuthoritativeHub naming its registered hub. A standalone declaration is attached to the tenant when that store starts serving its hub.

The edge calls SyncClient::bind_application_table_policy with an ApplicationTablePolicyExpectation. Matching policy returns an authenticated, persisted binding; a missing table or differing clause is refused without installing authority. Bound names must match at local CREATE TABLE / ALTER TABLE and at arriving DDL. An explicitly declared local policy is preserved against a differing arriving declaration; unrelated tables continue syncing.

Create the root and member tables with the matching clauses. Each member must declare a foreign key to the root. In the same transaction that inserts those rows, call Database::register_delivery_manifest(tx, DeliveryManifest { root_table, root_key, members }), then commit. The member list may be empty. A member outside the transaction, a wrong root reference, or incomplete membership refuses the registration and aborts the transaction. The engine hashes every committed application column, including bookkeeping columns. Keep machine-specific bookkeeping in another table if equivalent imports must have identical content.

On a mutable manifested table, an UPDATE or DELETE retires the current manifest ownership in the same commit. A surviving rewritten root without a new registration receives a per-unit manifest_required outcome while other units continue; a deleted root is no longer eligible. Registering the rewritten root again in its writing transaction creates a new pending unit.

Before registering a manifest, construct SyncClient or SyncServer on the same Database handle in this process to load its authenticated identity. It need not be connected. Repeat this preparation after reopening in another process. Without it, registration returns SchemaInvalid: delivery registration requires an authenticated sync identity. Construct the sync component first, then retry registration.

A unit's rows and terminal outcome share the hub commit. accepted means the entire unit was stored; equivalent means its whole-unit digest matches the incumbent; refused is terminal and carries its cause and the complete keep-first diagnostic. Fetch the registered edge's outcomes with SyncClient::fetch_delivery_outcomes(since) to recover a lost acknowledgement. The continuation is opaque. Database::delivery_outcome reads one root's answer; delivery_status derives eligible, accepted, equivalent, refused, and pending counts. A watermark alone never grants delivery credit. SYNC OFF status is disabled with zero eligible units. An observed change of hub incarnation or registered hub returns old credit to pending and preserves local rows.

SHOW SYNC BINDINGS;
SHOW DELIVERY OUTCOMES FOR events WHERE outcome = 'refused' LIMIT 20 OFFSET 0;
DISCARD FROM events WHERE session_ref = $session,
             event_anchors WHERE session_ref = $session;

The three custody SHOW statements require an unconstrained admin handle. They read metadata only, including keys, identities, digests, positions, and conflict causes; they do not include row bodies or advance sync state.

DISCARD erases only this node's selected copies. It is legal inside BEGIN and rolls back with the transaction, including selected rows inserted or updated earlier in that transaction. Push-only and SYNC OFF tables are eligible; a hub must use PURGE. Only bound tables have a hub-declared discard mode; an eligible unbound table proceeds. The persisted binding governs the answer offline: NEVER refuses before selection, AFTER OUTCOME refuses if a selected owning unit is pending, and ALWAYS proceeds and reports pending units under the root table. Silence means AFTER OUTCOME. Each listed table reports table, rows_affected, survivors, and pending_units. A refusal or failed commit leaves every table unchanged. Discard removes local custody records and leaves no tombstone and creates no fleet frontier. Re-import starts a new local unit; a hub retaining identical content can answer equivalent.

PURGE FROM table WHERE ..., other_table WHERE ... prepares every selection before one durable boundary and returns each table's affected count and survivor report, including zero matches. The single-table form preserves its existing result: the affected count and, when nonempty, blob_hash / remaining_job_id survivor rows. It remains standalone and authoritative: it is refused in a transaction and on an enrolled edge. For each listed SYNC OFF table, the ordinary authoritative purge unit carries that table's self-contained WHERE predicate and bound parameter values. Each edge selects its own matching rows, including independently generated keys, within the same atomic erasure as the other listed tables. This instruction is delivered even when the hub matches no local row. A subquery in any listed node-local predicate is refused with SubqueryNotSupported before any table is selected; subqueries on the other listed tables retain the existing single-table behavior. No foreign key or column correspondence is inferred.

The purge journal may retain these predicates and selection values as erasure instructions, just as it retains purged keys. This explicit privacy exception does not permit retaining erased row bodies. Repeating an applied instruction is idempotent and preserves fresh rows created after its first application. An all-empty purge containing only key-based selections writes nothing. Application-table survivor lists are empty: ordinary columns have no engine blob-reference value type; the work-ledger blob reference surface owns nonempty blob-survivor reports.

A purge tombstone bans the purged lineage, never the key. A row written at a purged key after the purge is new data: a later PURGE that selects it erases it like any other row, on the hub, on a node-local table, and on every edge that applies the hub's purges, including an edge that learns several purges of one key in a single pull. Every purged life of the key stays permanently refused, with its own frontier, after reopen and after a later life of the key is deleted, discarded, or purged.

Declarations and bindings report TenantPolicyNotDeclared, TenantPolicyMismatch, TenantPolicyBound, TableBindingMismatch, or DeclaredPolicyPreserved as appropriate. Manifest registration reports ManifestRequired, ManifestIncomplete, or ManifestMemberOutsideTransaction. Discard eligibility and mode failures are DiscardNotEligible, DiscardNotOnHub, and EdgeDiscardDenied; they name tables, modes, and counts, never row content.

FilesExpand file tree

query-language.md

Latest commit

History

query-language.md

File metadata and controls

Query Language

Statements

CREATE TABLE

ALTER TABLE

DROP TABLE

CREATE TRIGGER

CREATE INDEX

INSERT

UPDATE / DELETE

SELECT

Predicate Evaluation Errors

Column Qualifiers

Standard-SQL WHERE Forms

ORDER BY Expressions

CTEs

Anchor-Shape vs List-Shape Reads

Transactions

Configuration

Column Types

Rank Policies

Column Constraints

Audit-Frozen Columns

Access-Controlled Rows (ACL)

Composite Uniqueness

Composite Primary Key

Foreign Key State Propagation

Table Options

IMMUTABLE

STATE MACHINE

DAG

RETAIN

HISTORY

MAINTAIN COUNT

SYNC

SYNC CONFLICT

PROPAGATE ON EDGE

PROPAGATE ON STATE ... EXCLUDE VECTOR

Combining Options

Expressions and Operators

Comparison

Logical

Arithmetic

Pattern Matching

Range

Set Membership

NULL Checks

Functions

Parameter Binding

Graph Traversal

Syntax

Patterns

Variable-Length Paths

Filtering and Projection

Composing with SQL via CTEs

Graph + Vector: Neighborhood Similarity Search

Vector Similarity Search

The <=> Operator

Pre-Filtered Search

Indexing

Search modes and query override

Partition scope, filters, and one answer

Explain

SQL Comments

Unsupported Features

Indexes

CREATE INDEX

DROP INDEX

ALTER TABLE DROP COLUMN

Ordering

Auto-Indexes

Index-Selection Trace Reasons

Graph Adjacency Probe

Error variants

Tenant policy and event custody

The `<=>` Operator