Switches version; unavailable chapters open its introduction.

Keyboard shortcuts

Press ← or → to navigate between chapters

Press S or / to search in the book

Press ? to show this help

Press Esc to hide this help

Getting Started

Run contextdb in 2 minutes.


Install

Download the latest release for your platform from GitHub Releases:

# Linux x86_64
curl -fsSL https://github.com/context-graph-ai/contextdb/releases/latest/download/x86_64-unknown-linux-gnu.tar.gz | tar xz
# Linux ARM64
curl -fsSL https://github.com/context-graph-ai/contextdb/releases/latest/download/aarch64-unknown-linux-gnu.tar.gz | tar xz
# macOS Intel
curl -fsSL https://github.com/context-graph-ai/contextdb/releases/latest/download/x86_64-apple-darwin.tar.gz | tar xz
# macOS Apple Silicon
curl -fsSL https://github.com/context-graph-ai/contextdb/releases/latest/download/aarch64-apple-darwin.tar.gz | tar xz

Install via cargo

cargo install contextdb-cli

Build from source

Requires Rust stable (1.75+) and Git.

git clone https://github.com/context-graph-ai/contextdb.git
cd contextdb
cargo build --release -p contextdb-cli

First REPL Session

contextdb ./my.db --write

--write creates the store if it is missing and authorizes mutation — see docs/cli.md. Example tables such as decisions are yours to define; see the README.

Try the state machine — the feature that makes contextdb different from plain SQL:

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

INSERT INTO decisions (id, status, reasoning)
VALUES ('550e8400-e29b-41d4-a716-446655440000', 'draft', 'initial assessment');

-- Valid transition: draft -> active
UPDATE decisions SET status = 'active'
WHERE id = '550e8400-e29b-41d4-a716-446655440000';

-- Invalid transition: active -> draft (rejected by the database)
UPDATE decisions SET status = 'draft'
WHERE id = '550e8400-e29b-41d4-a716-446655440000';
-- Error: invalid state transition: active -> draft

The database enforces the state machine. No application code needed.

Audit-frozen columns — declare IMMUTABLE on any column whose value must never be silently rewritten after the initial INSERT:

CREATE TABLE audit_decisions (
  id UUID PRIMARY KEY,
  decision_type TEXT NOT NULL IMMUTABLE,
  description TEXT NOT NULL IMMUTABLE,
  status TEXT NOT NULL DEFAULT 'active'
);

INSERT INTO audit_decisions (id, decision_type, description)
VALUES ('550e8400-e29b-41d4-a716-446655440001', 'sql-migration', 'adopt contextdb');

-- Mutable column: succeeds
UPDATE audit_decisions SET status = 'superseded'
WHERE id = '550e8400-e29b-41d4-a716-446655440001';

-- Flagged column: rejected with Error::ImmutableColumn
UPDATE audit_decisions SET decision_type = 'other'
WHERE id = '550e8400-e29b-41d4-a716-446655440001';
-- Error: column `decision_type` on table `audit_decisions` is immutable

The row stays at its original decision_type; the session continues. To record a correction, INSERT a new row and mark the original superseded.

Persist to Disk

The first session already wrote ./my.db. One database file plus its .lock companion (my.db and my.db.lock), and a third file — my.db.fabric-identity.key — once the store has synced. Crash-safe via redb. Reopen and your data is there. Without --write the same path opens a read-only session instead, which is the subject of the next chapter. See docs/cli.md.

Inspecting a store safely

You have a contextdb store on disk — maybe your own, maybe one a running service owns — and you want to look inside without any risk of changing it. That is the default command:

contextdb ./my.db

This opens a read-only session. Mutation needs --write; the refusals and routing are in docs/cli.md.

1. Look around

contextdb(ro)> .tables
decisions
has_more: false

contextdb(ro)> .schema decisions

.schema reprints the declaration from the first session, with from-states sorted rather than in the order you declared them.

contextdb(ro)> SELECT id, status FROM decisions;
+--------------------------------------+--------+
| id                                   | status |
+--------------------------------------+--------+
| 550e8400-e29b-41d4-a716-446655440000 | active |
+--------------------------------------+--------+
(1 rows)

Three shapes to expect. .tables is a bounded page, so it always closes with a has_more: line. .schema prints the from-states sorted. And an ordinary SELECT renders as a bordered table with exactly one (N rows) footer.

The first reading command prints one route notice on stderr telling you how you are reading. In human mode that is one sentence — reading the committed snapshot taken at 2026-08-29T06:41:47Z when nobody owns the store (you are reading a point-in-time snapshot), or reading through the live owner's local channel when a live process owns it and is serving you its committed state over an authenticated local channel (same commands, same shapes). Under --json the same notice carries the machine form in detail.route ("file" or "owner") and detail.snapshot_at (the committed moment on the file route, null on the owner route) — the shapes are in docs/cli.md.

2. Everything else a reading session can do

The read surface has exactly one reference — docs/cli.md. Rather than restate it here and let the two copies drift, three sentences and where each one is spelled out in full:

  • A result that crosses a ceiling is refused whole — never truncated, no partial rows — and the refusal names both escapes and carries a copy-ready .cursor open <statement> you can page it with: Ordinary results: complete or refused and Large results: the session cursor.
  • Which escape is honest depends on your route, so check the route notice or .owner status first: raising --read-result-rows / --read-result-bytes for a one-shot export works on the file route only — on the owner route the ceilings are the owner’s, a reader cannot raise them, and the cursor is the in-session escape. If a live process owns the store, contextdb <path> reads through that owner automatically and --write is refused with held_by_writer: Reading routes and Declared limits.
  • Scripts pass --json: stdout is one JSON document per statement, every notice and error is a JSON document on stderr, and the process exit code is one of the four in Exit Codes.

Indexes and Scale

contextdb is designed for agents holding tens of thousands of entities with sub-100ms filtered retrieval. Indexes accelerate filtered scans so a 10,000-row table answers WHERE tag = 'x' in microseconds instead of milliseconds.

-- `observations` is an example table you define - see the README.
CREATE TABLE observations (
  id UUID PRIMARY KEY,
  tag TEXT,
  value INTEGER
);

CREATE INDEX idx_tag ON observations (tag);

INSERT INTO observations (id, tag, value)
VALUES ('650e8400-e29b-41d4-a716-446655440010', 'pay', 1);

SELECT value FROM observations WHERE tag = 'pay';

Composite indexes push a matched leading prefix, not just the first column:

CREATE INDEX idx_observation_route ON observations (tag, value, id);

.explain SELECT id FROM observations WHERE tag = 'pay' AND value = 1;

The explanation reports IndexScan { index: idx_observation_route } with predicates_pushed: [tag, value]. A query whose WHERE clause does not match the first column of any declared or auto-index reports Scan.

Rank Vector Search by Outcomes

This release ships exact vector search: AUTO searches exactly and INDEXED is unavailable.

Vector search does not have to rank by cosine similarity alone. Declare a RANK_POLICY on the vector column and every caller asking for the same SORT_KEY gets the same outcome-weighted ordering — resolved at DDL time, stored with the schema, and replicated through sync — so the row that is closest but failed sorts below the row that was less similar and worked. No application copies formula text into its queries.

  • Grammar — RANK_POLICY, JOIN, the FORMULA placeholders, SORT_KEY, USE RANK: docs/query-language.md.
  • Runnable walkthrough with the expected ordering, the same query without USE RANK for contrast, the join-index requirement, the approximate-search caveat, and how to change a policy: skills/vector-search/SKILL.md.

Use as a Library

contextdb is an embedded database — the CLI is for exploration. The primary interface is the Rust API:

#![allow(unused)]
fn main() {
use contextdb_engine::Database;
use contextdb_core::Value;
use std::collections::HashMap;

let db = Database::open(std::path::Path::new("./my.db"))?;

let params = HashMap::new();
db.execute(
    "CREATE TABLE observations (
       id UUID PRIMARY KEY,
       data JSON,
       embedding VECTOR(384)
     ) IMMUTABLE",
    &params,
)?;

// Insert with parameters
let mut params = HashMap::new();
params.insert("id".into(), Value::Uuid(uuid::Uuid::new_v4()));
params.insert("data".into(), Value::Json(serde_json::json!({"type": "sensor"})));
params.insert("embedding".into(), Value::Vector(vec![0.1; 384]));

db.execute(
    "INSERT INTO observations (id, data, embedding) VALUES ($id, $data, $embedding)",
    &params,
)?;
}

Add to your Cargo.toml:

[dependencies]
contextdb-engine = "1.0.0"
contextdb-core = "1.0.0"

Reading a store from a second process

Database::open takes ownership of a store file, so one process holds it at a time. Reading is a separate door. ReadSession::open (or ReadSession::open_with_options, which carries the caller’s own limits and deadlines) reads a store whether or not somebody owns it: it routes through the live owner’s local channel when a process owns the store, and reads the committed file directly when none does.

ReadSession::open_owner_only is the third door — the one for questions only a running owner can answer (what the process is doing, whether it is serving yet). It takes the owner route when a process owns the store, and when none does it says owner_not_running rather than falling through to the file. Four exported names, and that is the whole surface:

#![allow(unused)]
fn main() {
use contextdb_engine::{ReadSession, ReadSessionOptions};

// The live owner if one is running, the committed file if not.
let session = ReadSession::open("./my.db")?;

// The same, with the caller's own ceilings and deadlines.
let bounded = ReadSession::open_with_options("./my.db", ReadSessionOptions::default())?;

// Administrative asking and readiness probing: an owner, or nothing.
let owner = ReadSession::open_owner_only("./my.db", ReadSessionOptions::default())?;
}

Why open_owner_only never falls through to the file, what a readiness probe would otherwise do to the writer it is waiting for, and how a refused read splits into a stable kind() and a machine-readable detail(): docs/architecture.md.

What’s Next

Why contextdb?

Agents need memory that does more than store embeddings. They need to know what they decided, what those decisions were based on, and whether the basis has changed. They need to trace provenance through a graph of relationships, search by semantic similarity within a specific neighborhood, and have invalid state transitions rejected — not by application code, but by the database.


The Problem

Most agents today stitch together three systems:

  1. SQLite for relational state (what was decided, when, what status it’s in)
  2. A vector database (Chroma, Qdrant, Pinecone) for semantic search over embeddings
  3. Application code for graph traversal (recursive CTEs, Python-side BFS, or a separate graph DB)

This works until it doesn’t:

  • No unified transactions. Crash between updating the row and the vector index? Inconsistent state. Your agent confidently searches over stale embeddings.
  • Unbounded graph walks. Recursive CTEs or Python-side BFS have no built-in depth limits or cycle detection. One circular reference and your agent spins forever.
  • Three sync problems. When the local instance goes offline (a laptop, a browser plugin, a mobile app), you need three sync strategies — one per system — or you accept data loss.
  • Constraints live in application code. Every consumer duplicates the same validation: “don’t transition from draft to superseded”, “don’t insert into this immutable table”, “cascade this state change to dependents.” Miss one consumer and the invariant breaks silently.

How contextdb Is Different

contextdb replaces all three with one embedded database. One transaction atomically updates relational rows, graph adjacency structures, and vector indexes. One read snapshot sees consistent state across all three. Multiple vector columns on the same table let a typed-evidence system keep text, vision, audio, or policy embeddings separate without leaving the database.

CapabilitySQLite + extensionscontextdb
Vector searchsqlite-vec (separate extension, no unified transactions with relational data)Built-in exact search with pre-filtering, same MVCC transaction as rows
Graph traversalRecursive CTEs (unbounded, no cycle detection)SQL/PGQ-style MATCH with bounded BFS, DAG enforcement, typed edges
State machinesCHECK constraints + validation triggers (bypassable)STATE MACHINE in DDL, enforced by the database engine
Atomic cross-model updatesApplication-level coordinationSingle MVCC transaction across relational + graph + vector
SyncBuild your ownBidirectional dial-by-key sync — each database exchanges changesets with per-table conflict resolution, not WAL pages, no broker to install
Immutable tablesNot enforceable with bypassable validation triggersIMMUTABLE keyword, enforced by the database engine
Cascading invalidationApplication codePROPAGATE in DDL — state changes cascade along edges and FKs

Enforceable Policy Constraints

The most distinctive feature: constraints that the database guarantees, not application code.

STATE MACHINE — Define valid state transitions in DDL. The database rejects invalid transitions at the engine level, regardless of how the write arrives:

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

UPDATE decisions SET status = 'superseded' WHERE id = $id;
-- Error if current status is 'draft': invalid state transition: draft -> superseded

$id is a parameter bound through the Rust API (db.execute(sql, &params)); in the CLI REPL write the literal value.

PROPAGATE — When one thing changes state, related things react automatically. Along graph edges, along foreign keys, with bounded depth:

PROPAGATE ON EDGE CITES INCOMING STATE invalidated SET invalidated MAX DEPTH 3

Archive an intention, and in one transaction: FK-linked decisions transition to invalidated, edge-linked decisions also cascade, and invalidated decisions are excluded from vector search.

DAG — Prevent cycles in directed relationships. Enforced on insert via BFS from target back to source:

CREATE TABLE edges (...) DAG ('DEPENDS_ON', 'BLOCKS')

IMMUTABLE — Once inserted, rows cannot be updated or deleted:

CREATE TABLE observations (...) IMMUTABLE

RETAIN — Automatic TTL expiry with sync-safe option (rows aren’t purged until synced). HISTORY — the second axis: RETAIN bounds how long a row lives, HISTORY CURRENT ONLY bounds how many past versions of a still-live row are kept, so a status table rewritten on every poll cycle collapses back to one physical version per key instead of accumulating one forever:

CREATE TABLE scratch (...) RETAIN 24 HOURS
CREATE TABLE logs (...) RETAIN 90 DAYS SYNC SAFE
CREATE TABLE device_status (...) HISTORY CURRENT ONLY SYNC CONFLICT KEEP LATEST

Triggers — Host callbacks for transactional observation and cascade writes. These are not validation triggers; engine invariants stay in DDL. A declared CREATE TRIGGER plus a registered Rust 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 deadlock-guard timeouts emit a structured tracing::warn!.


Familiar Conventions

contextdb follows existing standards so there’s nothing new to learn:

  • PostgreSQL-compatible SQL — SELECT, INSERT, UPDATE, DELETE, JOINs, CTEs, upsert, DISTINCT, LIMIT, LIKE, BETWEEN, parameter binding ($name)
  • pgvector syntax — Cosine similarity via <=> operator
  • SQL/PGQ-style graph queries — GRAPH_TABLE(... MATCH ...) with bounded BFS, typed edges, variable-length paths ({1,3}), direction control

Not the full SQL/PGQ standard — just the subset that matters for bounded traversal in agentic workloads. contextdb is a focused tool for agent memory, not a general-purpose database.


One Query, Three Subsystems

Find semantically similar observations within a graph neighborhood, filtered by relational predicates — a query that would take ~40 lines of Python across SQLite, ChromaDB, and a hand-rolled BFS:

WITH neighborhood AS (
  SELECT b_id FROM GRAPH_TABLE(
    edges MATCH (start)-[:RELATES_TO]->{1,3}(related)
    WHERE start.id = $entity_id
    COLUMNS (related.id AS b_id)
  )
),
candidates AS (
  SELECT o.id, o.data, o.embedding
  FROM observations o
  INNER JOIN neighborhood n ON o.entity_id = n.b_id
  WHERE o.observation_type = 'config_change'
)
SELECT id, data FROM candidates
ORDER BY embedding <=> $query_embedding
LIMIT 5

One query. One transaction. One process.


Design Envelope

contextdb is designed for agentic memory, not data warehousing:

  • 10K-1M rows per database
  • Sparse graphs with bounded traversal (depth <= 10)
  • Append-heavy writes, small transactions
  • Configurable memory budget via SET MEMORY_LIMIT (no hard-coded ceiling)
  • Configurable disk budget for file-backed databases via SET DISK_LIMIT / SHOW DISK_LIMIT or --disk-limit
  • See Architecture for memory-limit behavior on edge devices.
  • Laptops, ARM64 devices (browser and mobile via Rust’s WASM target are future directions)

Next Steps

Usage Scenarios

16 problem-first walkthroughs showing how contextdb solves real agent memory problems with SQL. Each scenario starts with a problem, then shows the solution.

For background on why these problems exist and how contextdb compares to alternatives, see Why contextdb?. To build and try these examples yourself, see Getting Started.

Note: Examples use $param parameter binding syntax, which works in the Rust API via db.execute(sql, &params). The CLI REPL does not support parameter binding — use literal values directly when trying these interactively.

Scenarios build on each other’s schemas rather than redeclaring them. A block that needs a table an earlier scenario created says so immediately above it; run that block first, in the same store.


Example Use Cases

Before diving into SQL, here are concrete scenarios where these problems surface. Every table you’ll see below — decisions, observations, intentions, entities, edges, digests, and the rest — is an example schema these walkthroughs define on top of contextdb’s own primitives: STATE MACHINE, DAG, IMMUTABLE, PROPAGATE, RETAIN, vector columns, graph traversal, and sync (see the README). You declare your own tables and attach whichever of these policies you need; contextdb enforces them. The agentic-memory ontology used throughout is one illustrative consumer schema, not contextdb’s model.

An AI coding assistant stores decisions about file locations, architecture patterns, and tool preferences. When a file is renamed (observation), any decision that referenced the old path should be flagged as stale (invalidation). When the assistant searches its memory, it needs to find semantically similar past decisions (vector search) that are still valid (relational filter on status) and trace what they were based on (graph traversal).

A security monitoring system runs object detection on camera feeds. Each detection is an immutable observation with an embedding. The system learns that a particular person is a regular visitor (decision). When an unknown person appears, graph traversal finds all active decisions about expected visitors at that location. Dozens of sites sync corrections to a central server; new sites pull learned patterns immediately.

An infrastructure monitoring agent tracks services, their configurations, and the decisions made about alert thresholds. When a config change observation arrives (region moved, instance type changed), the agent needs to find which threshold decisions relied on the old configuration — automatically, not by manual lookup.

A team knowledge system stores architectural decisions with structured reasoning, links them to the entities they affect, and tracks outcomes. When a new team member’s agent asks “why is this service configured this way?”, the answer includes the decision, its reasoning, the entities it was based on, and whether any of those entities have changed since.


Scenario 1: Store Observations With Provenance

Problem: You have observations arriving from multiple sources. Each observation is about an entity. You need to store them immutably and trace which entities and decisions they connect to.

-- Entities that observations are about
-- (example schema - contextdb ships no tables of its own; you define these)
CREATE TABLE entities (
  id UUID PRIMARY KEY,
  name TEXT NOT NULL,
  entity_type TEXT NOT NULL,
  properties JSON
);

-- Immutable observation log
CREATE TABLE observations (
  id UUID PRIMARY KEY,
  observation_type TEXT NOT NULL,
  data JSON NOT NULL,
  entity_id UUID NOT NULL,
  source TEXT NOT NULL,
  embedding VECTOR(384),
  recorded_at TIMESTAMP DEFAULT NOW()
) IMMUTABLE;

-- Graph edges connecting entities, decisions, observations
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');

Insert an observation and link it to its entity:

INSERT INTO observations (id, observation_type, data, entity_id, source, embedding)
VALUES ($id, 'config_change', $data, $entity_id, 'terraform-hook', $embedding);

INSERT INTO edges (id, source_id, target_id, edge_type)
VALUES ($edge_id, $observation_id, $entity_id, 'OBSERVED_ON');

The observation is immutable — it can never be modified or deleted. The OBSERVED_ON edge is deduplicated if inserted twice — inserting the same (source_id, target_id, edge_type) is a silent no-op, making agent operations idempotent (“ensure this link exists” without checking first).

The DAG('DEPENDS_ON', 'BASED_ON') declaration means these specific edge types are acyclic — the engine rejects any insert that would create a cycle, checking both committed edges and pending writes in the current transaction. Other edge types in the same table (like OBSERVED_ON) can have cycles if needed.

Multi-Modal Evidence

Evidence can carry independent vector indexes for different modalities:

CREATE TABLE evidence (
  id UUID PRIMARY KEY,
  source TEXT NOT NULL,
  vector_text VECTOR(768) WITH (quantization = 'SQ8'),
  vector_vision VECTOR(512) WITH (quantization = 'SQ8')
);

ORDER BY vector_text <=> $query and ORDER BY vector_vision <=> $query route to different (table, column) indexes, each with its own dimension, quantization, and index state.


Scenario 2: Decisions With State Machines

Problem: Decisions have a lifecycle — draft, active, superseded, invalidated. Invalid transitions (draft directly to superseded) should be impossible.

Correction-via-supersede. Provenance columns — what was decided, what it was based on — are marked IMMUTABLE. A recorded decision is never silently rewritten. When a correction is needed, insert a new row with the corrected values and transition the original status to superseded. Nothing disappears from the audit trail.

-- Example table (yours - contextdb defines no `decisions` schema);
-- STATE MACHINE is the contextdb primitive doing the enforcement below
CREATE TABLE decisions (
  id UUID PRIMARY KEY,
  description TEXT NOT NULL IMMUTABLE,
  status TEXT NOT NULL,
  confidence REAL,
  context_id UUID,
  entity_type TEXT,
  created_at TIMESTAMP,
  embedding VECTOR(384)
) STATE MACHINE (status: draft -> [active, rejected], active -> [superseded, invalidated]);

Against that decisions table, one composite index serves the whole access pattern:

CREATE INDEX idx_decisions_by_ctx ON decisions (context_id, entity_type, created_at DESC, id DESC);
-- Valid: draft -> active
UPDATE decisions SET status = 'active' WHERE id = $id;

-- Rejected by the database: draft -> superseded
-- ($other_draft_id is a second row still in `draft`; a `draft` row may only
-- move to `active` or `rejected`. Reusing $id here would instead demonstrate
-- the legal `active -> superseded` hop the line above just enabled.)
UPDATE decisions SET status = 'superseded' WHERE id = $other_draft_id;
-- Error: invalid state transition: draft -> superseded

The composite idx_decisions_by_ctx index keeps filter-by-context + order-by-created queries sub-100ms at p95 over tens of thousands of decisions — the planner walks the index range directly and elides the ORDER BY sort.


Scenario 3: Cascading Invalidation

Problem: A decision was based on an entity’s state. The entity changed. Every decision that relied on the old state — and every decision that cited those decisions — should be flagged.

-- Example tables - contextdb ships neither; PROPAGATE/STATE MACHINE are its primitives
CREATE TABLE intentions (
  id UUID PRIMARY KEY,
  goal TEXT NOT NULL,
  status TEXT NOT NULL
) STATE MACHINE (status: active -> [archived, completed]);

CREATE TABLE decisions (
  id UUID PRIMARY KEY,
  description TEXT NOT NULL,
  status TEXT NOT NULL,
  confidence REAL,
  intention_id UUID REFERENCES intentions(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;

When you archive an intention:

UPDATE intentions SET status = 'archived' WHERE id = $intention_id;

In one atomic transaction:

  1. The intention transitions to archived
  2. All decisions referencing it via FK transition to invalidated
  3. Decisions citing those decisions (via CITES edges) also transition to invalidated
  4. Invalidated decisions are excluded from vector similarity search

No application code. The database handles the entire cascade.


Scenario 4: “Has Anyone Solved This Before?” — Hybrid Query

Problem: An agent has a new task. It needs to find relevant past decisions — semantically similar, still active, and traceable back to their basis entities.

-- Find decisions similar to the current task
WITH similar_decisions AS (
  SELECT id, description, confidence
  FROM decisions
  WHERE status = 'active'
  ORDER BY embedding <=> $task_embedding
  LIMIT 10
),
-- For each decision, find what entities it was based on
basis_entities AS (
  SELECT b_id FROM GRAPH_TABLE(
    edges
    MATCH (d)-[:BASED_ON]->(b)
    WHERE d.id IN (SELECT id FROM similar_decisions)
    COLUMNS (b.id AS b_id)
  )
)
SELECT sd.id, sd.description, sd.confidence, e.name, e.properties
FROM similar_decisions sd
LEFT JOIN basis_entities be ON TRUE
LEFT JOIN entities e ON e.id = be.b_id;

This combines all three paradigms:

  • Vector: find semantically similar decisions
  • Relational: filter to active status, join with entity metadata
  • Graph: traverse BASED_ON edges to find the basis

Scenario 5: “What Just Became Stale?” — Decision Staleness

Problem: An observation arrived that changed an entity’s state. Which active decisions relied on the entity’s old state?

-- The entity that changed
-- (application records the observation and updates the entity)

-- Find the ids of all decisions that were BASED_ON this entity
SELECT b_id FROM GRAPH_TABLE(
  edges
  MATCH (entity)<-[:BASED_ON]-(decision)
  WHERE entity.id = $changed_entity_id
  COLUMNS (decision.id AS b_id)
);

The PROPAGATE constraints (Scenario 3) handle automatic invalidation. This query is for when the application wants to inspect what would be affected before triggering a state change. In the CLI, enable .trace on before the query above (shown bare, not wrapped in a WITH / JOIN); an id-pinned single-hop traversal prints AdjacencyProbe index=reverse_adj pushed=[entity.id] rows_examined=N, proving the work is proportional to the changed entity’s connections rather than the total edge-table size.

To pull the matched decisions’ own columns (description, status, confidence), wrap this in a WITH CTE and JOIN against decisions, filtering on status:

WITH affected AS (
  SELECT b_id FROM GRAPH_TABLE(
    edges
    MATCH (entity)<-[:BASED_ON]-(decision)
    WHERE entity.id = $changed_entity_id
    COLUMNS (decision.id AS b_id)
  )
)
SELECT d.id, d.description, d.status, d.confidence
FROM decisions d
INNER JOIN affected a ON d.id = a.b_id
WHERE d.status = 'active';

This composed form does not get the same trace as the bare query above. Today it plans as a Scan over decisions (.trace on prints Scan rows_examined=N for the decisions scan, not AdjacencyProbe) — the CTE’s own GRAPH_TABLE traversal still probes internally, but the join against decisions is not planned index-driven the way the bare probe is. Functionally correct either way (both return the right rows), but if you’re specifically validating “is this proportional to the connection count, not the table size,” trace the bare GRAPH_TABLE query, not the full CTE+JOIN — the join-routing gap is a known limitation, not something this doc papers over.

For application-side edge-table lookups, use a composite index that matches the full route predicate:

CREATE TABLE edge_refs (
  id UUID PRIMARY KEY,
  source_id UUID,
  target_id UUID,
  edge_type TEXT,
  UNIQUE (source_id, target_id)
);

CREATE INDEX idx_edge_refs_route ON edge_refs (source_id, target_id, edge_type);

.explain SELECT id FROM edge_refs WHERE source_id IN ($changed_entity_id, $related_entity_id) AND target_id = $decision_id AND edge_type = 'BASED_ON';

A dot-command like .explain consumes exactly one line — unlike an ordinary SQL statement, it does not accumulate across newlines until a terminating ;, so splitting it across multiple lines parses only the first line as the command and fails on the rest. Always paste a dot-command’s whole invocation on one line, however long.

The routed explanation shows a three-column covering index serving all three predicates. Note the chosen index: any table with source_id, target_id, and edge_type columns automatically gets an internal route index (__graph_edge_source_target_type), and here it covers the query, so the planner routes through it — your equivalent idx_edge_refs_route ties it and loses only on creation order. Either way the scan is fully index-driven:

IndexScan { index: __graph_edge_source_target_type }
  predicates_pushed: [source_id, target_id, edge_type]
  indexes_considered: [__pk_id: first column not in WHERE, __unique_source_id_target_id: fewer predicate columns matched than chosen index, idx_edge_refs_route: tied with chosen index; lost by creation order]

Scenario 6: Emergent Unknown Detection — “Things You Didn’t Know You Didn’t Know”

Problem: An agent makes a decision based on an entity — say, a service’s configuration. The decision’s reasoning mentions the region and instance type, but doesn’t enumerate every property it depends on. Later, an observation adds a completely new property to the entity — one that didn’t exist at decision time. Maybe operations added a rate_limit field, or a compliance_tier appeared after a policy change. The decision was made without considering this property, and no one knew to watch for it.

This is the hardest problem in agentic memory: you can’t search for something you don’t know exists.

contextdb solves it structurally. A decision is BASED_ON an entity as a whole, not on specific fields. When any property changes — including properties that are entirely new — the database detects it:

-- Entity at decision time had: {region: "us-east-1", instance_type: "m5.large"}
-- Observation adds a new property: {region: "us-east-1", instance_type: "m5.large", rate_limit: 500}

-- The basis_diff shows: rate_limit: null -> 500
-- (null = didn't exist when the decision was made)

-- Find decisions that were BASED_ON this entity - they're all potentially stale
WITH affected AS (
  SELECT b_id FROM GRAPH_TABLE(
    edges
    MATCH (entity)<-[:BASED_ON]-(decision)
    WHERE entity.id = $changed_entity_id
    COLUMNS (decision.id AS b_id)
  )
)
SELECT d.id, d.description, d.confidence
FROM decisions d
INNER JOIN affected a ON d.id = a.b_id
WHERE d.status = 'active';

The agent didn’t know to watch for rate_limit. No one configured a trigger for it. But because the decision was linked to the entity via a BASED_ON edge, the graph traversal finds it, and the application can compute the basis diff: the entity’s state at decision time vs. now.

Combined with cascading invalidation (Scenario 3), this propagates further: if decision D1 is invalidated because an unknown property appeared, and decision D2 cites D1 via a CITES edge, D2 is also flagged — automatically, via bounded BFS, within the same transaction.

This is not a filter you configure. It’s a structural property of the graph: link decisions to what they’re based on, and the database will tell you when the basis changes — even in ways nobody anticipated.


Problem: An agent is working on a task. It needs context — but not from the entire database. It needs relevant observations within the neighborhood of entities related to the current task.

-- Start from the task entity, traverse RELATES_TO edges to build a neighborhood
WITH neighborhood AS (
  SELECT b_id FROM GRAPH_TABLE(
    edges
    MATCH (task)-[:RELATES_TO]->{1,2}(related)
    WHERE task.id = $task_id
    COLUMNS (related.id AS b_id)
  )
),
-- Find observations linked to entities in the neighborhood
candidates AS (
  SELECT o.id, o.data, o.embedding
  FROM observations o
  INNER JOIN neighborhood n ON o.entity_id = n.b_id
)
-- Rank by semantic similarity to the current query
SELECT id, data FROM candidates
ORDER BY embedding <=> $query_embedding
LIMIT 5;

Graph-first (narrow the scope), then vector (rank within scope). The inverse — vector-first, then graph — works equally well for different access patterns.


Scenario 8: Digest Provenance — Tracing Conversations

Problem: Decisions and observations were extracted from conversations. When reviewing a decision, you need to trace it back to the conversation that produced it.

-- Conversation digests are immutable with embeddings for search
-- (example table - IMMUTABLE is the contextdb primitive; `digests` itself is not)
CREATE TABLE digests (
  id UUID PRIMARY KEY,
  source TEXT NOT NULL,
  summary TEXT NOT NULL,
  embedding VECTOR(384)
) IMMUTABLE;

-- Find relevant conversations, then trace what was extracted from them
WITH relevant AS (
  SELECT id FROM digests
  ORDER BY embedding <=> $query
  LIMIT 10
),
extracted AS (
  SELECT b_id FROM GRAPH_TABLE(
    edges
    MATCH (digest)<-[:EXTRACTED_FROM]-(derived)
    WHERE digest.id IN (SELECT id FROM relevant)
    COLUMNS (derived.id AS b_id)
  )
)
SELECT d.id, d.description, d.status
FROM decisions d
INNER JOIN extracted e ON d.id = e.b_id;

Scenario 9: Local-to-Server Sync

Problem: Multiple local instances accumulate data independently — a developer’s laptop, a browser plugin, a mobile app — sometimes offline for hours or days. When they reconnect, data should sync without duplication, with clear conflict resolution.

# Central server — prints `enrollment ticket: <...>` to stdout on startup; copy it for the edges
contextdb-server --tenant-id production --db-path ./server.db

# Instance 1 (laptop app) — works offline, pushes when connected.
# A pasted ticket is auto-pinned to this edge's identity key
# (./local1.db.fabric-identity.key), so `.sync status` shows the rewritten spec.
contextdb ./local1.db --write --tenant-id production --sync-endpoint <ticket>
contextdb> CREATE TABLE sensors (id UUID PRIMARY KEY, name TEXT, reading REAL);
contextdb> INSERT INTO sensors VALUES ('...', 'temp-north', 23.5);
contextdb> .sync push
Pushed: 1 applied, 0 skipped, 0 conflicts

# Instance 2 (another machine) — pulls and gets everything, including schema
contextdb ./local2.db --write --tenant-id production --sync-endpoint <ticket>
contextdb> .sync pull
Pulled: 1 applied, 0 skipped, 0 conflicts
contextdb> SELECT * FROM sensors;

The applied count reports data rows; the CREATE TABLE replicates too (instance 2’s schema is created on pull) but does not add to the row tally.

Sync behavior is declared per table:

-- Conflict resolution is declared on the table itself (keep-first is the default)
CREATE TABLE observations (id UUID PRIMARY KEY, body TEXT) SYNC CONFLICT KEEP FIRST;
contextdb> ALTER TABLE observations SET SYNC PUSH ONLY;
contextdb> ALTER TABLE decisions SET SYNC TWO WAY;
contextdb> ALTER TABLE scratch SET SYNC OFF;

Each instance stores its data in a single file. No WAL directories, no journal files, no auxiliary indexes. Back up the file, copy it to another machine, or embed it in a container image.

Local instances reach the server by dial-by-key — pasting its enrollment ticket into --sync-endpoint, connecting through the server’s cryptographic identity rather than a broker address. This is deliberate — a node behind NAT dials outbound, so a laptop behind a home router, a browser plugin, or a mobile app can all sync without network configuration. Machines on one LAN sync over direct connections with zero external infrastructure; peers across networks are introduced by a self-hosted or opt-in relay.

Sync survives restarts. Change logs are ephemeral — they exist in memory for incremental sync while the process runs. After a restart, the database reconstructs a full-state snapshot from persisted data when a peer requests changes. Large payloads (tables with high-dimensional vectors or large JSON blobs) ride a framed stream that carries them whole — no message-size ceiling to chunk below.

In Rust:

#![allow(unused)]
fn main() {
use contextdb_core::TenantId;
use contextdb_engine::Database;
use contextdb_server::SyncClient;
use std::sync::Arc;

// `db` is an Arc<Database>; the endpoint parameter is the server's enrollment
// ticket; the tenant is the TenantId newtype, not a bare &str.
let db: Arc<Database> = Arc::new(Database::open(std::path::Path::new("./edge.db"))?);
let client = SyncClient::new(db.clone(), &server_ticket, TenantId::from("production"));
client.push().await?;
client.pull_default().await?;
}

Scenario 10: Retention and Cleanup

Problem: Scratch data, temporary observations, and working state should expire automatically. But synced data shouldn’t be purged until it’s been replicated.

-- Scratch notes expire after 24 hours
CREATE TABLE scratch (
  id UUID PRIMARY KEY,
  content TEXT,
  created_at TIMESTAMP DEFAULT NOW()
) RETAIN 24 HOURS;

-- Observations are kept for 30 days, but not purged until synced
CREATE TABLE observations (
  id UUID PRIMARY KEY,
  data JSON,
  embedding VECTOR(384)
) RETAIN 30 DAYS SYNC SAFE;

The background pruning loop handles cleanup. SYNC SAFE ensures no data is lost before it reaches the server.

A different shape: a status table written on a timer, never deleted. A heartbeat or liveness row is upserted over and over (INSERT ... ON CONFLICT (id) DO UPDATE) — the row never expires (RETAIN doesn’t apply), but every update mints a new physical version and nothing ever reclaimed the superseded ones, so a long-running node accumulates one physical version per update for a handful of live rows. HISTORY CURRENT ONLY is the declaration for exactly this case — it bounds version count instead of row lifetime:

CREATE TABLE node_heartbeat (
  node_id TEXT PRIMARY KEY,
  state TEXT NOT NULL,
  reported_at TIMESTAMP NOT NULL
) HISTORY CURRENT ONLY SYNC CONFLICT KEEP LATEST;

The maintenance loop collapses each node_id back to its single current version on the same cadence that prunes RETAIN windows. SYNC CONFLICT KEEP LATEST is required here because reclaiming history means only the newest value survives to send — a peer pulling this table under the non-overwriting default (KEEP FIRST) would otherwise file that newest value as the first value it has ever seen for the key. A table combining both — RETAIN 48 HOURS HISTORY CURRENT ONLY SYNC CONFLICT KEEP LATEST — both collapses to one version per key and eventually ages the row out entirely.


Scenario 11: Reactive and Time-Based Processing

Problem: When data changes, downstream systems need to react — re-embed vectors, push to external APIs, trigger analysis pipelines. And some actions need to happen on a schedule — periodic staleness sweeps, retention cleanup, health checks. Building this with external cron jobs and polling is fragile: they run outside the transaction boundary, miss events during downtime, and duplicate logic.

contextdb provides two primitives that compose into cron-like behavior without external infrastructure:

Event-driven: db.subscribe() delivers commit events in real-time. The application reacts to specific table changes:

#![allow(unused)]
fn main() {
let rx = db.subscribe();  // std::sync::mpsc::Receiver<CommitEvent>

std::thread::spawn(move || {
    while let Ok(event) = rx.recv() {
        // event.tables_changed: which tables were modified
        // event.lsn: position in the change log
        // event.row_count: total operations in this commit
        // event.source: User | AutoCommit | SyncPull

        match event.source {
            CommitSource::SyncPull => {
                // Data arrived from another instance — check for conflicts
            }
            _ => {
                if event.tables_changed.contains(&"observations".to_string()) {
                    // New local observation — run impact analysis
                }
                if event.tables_changed.contains(&"invalidations".to_string()) {
                    // Invalidation detected — alert the agent
                }
            }
        }
    }
});
}

The receiver is std::sync::mpsc::Receiver (blocking). Use std::thread::spawn or tokio::task::spawn_blocking if integrating with async code.

Subscriptions are best-effort. If a subscriber’s channel is full, the event is dropped — the commit never blocks. Applications that need guaranteed delivery should treat subscriptions as triggers to re-query, not as a durable event log.

Time-driven: RETAIN handles TTL-based expiry at the database level. The application can schedule periodic queries using standard Rust — since contextdb is an embedded library, a periodic task is just a function call:

#![allow(unused)]
fn main() {
// Periodic staleness sweep — every hour, find decisions
// whose basis entities changed since they were last reviewed
let db_clone = db.clone();
tokio::spawn(async move {
    let mut interval = tokio::time::interval(Duration::from_secs(3600));
    loop {
        interval.tick().await;
        let result = db_clone.execute(
            "SELECT d.id, d.description FROM decisions d
             WHERE d.status = 'active'
             AND d.context_id = $ctx",
            &params,
        );
        // Application runs impact analysis on each active decision
    }
});
}

No external scheduler. No cron daemon. No polling service. The database is in-process — time-driven actions are just loops with db.execute() calls, and event-driven actions are subscription handlers. Both run inside the same process with full transactional guarantees.


Scenario 12: Intentions, Blueprints, and Decision Lineage

Problem: Decisions don’t appear from nowhere. An agent has an intention — a goal it’s pursuing. When multiple agents across different instances express the same intent in different words (“monitor auth-service health”, “watch the login system for failures”, “track auth uptime”), those should normalize to the same pattern. And when a decision is superseded, you need to trace the full chain: which intention it served, what it was based on, and what replaced it.

Blueprints are parameterized intent templates. They normalize diverse expressions into a canonical form with typed slots:

-- Reusable intent templates (example table - contextdb has no built-in
-- notion of "blueprints"; IMMUTABLE is the primitive declared on it)
CREATE TABLE blueprints (
  id UUID PRIMARY KEY,
  name TEXT NOT NULL,
  template TEXT NOT NULL,
  slots JSON NOT NULL,
  embedding VECTOR(384)
) IMMUTABLE;

-- What we're trying to achieve - an instance of a blueprint
CREATE TABLE intentions (
  id UUID PRIMARY KEY,
  goal TEXT NOT NULL,
  status TEXT NOT NULL,
  blueprint_id UUID,
  bindings JSON,
  context_id UUID NOT NULL,
  embedding VECTOR(384)
) STATE MACHINE (status: active -> [archived, completed, paused], paused -> [active]);

-- What we chose to do - always in service of an intention
CREATE TABLE decisions (
  id UUID PRIMARY KEY,
  description TEXT NOT NULL,
  status TEXT NOT NULL,
  confidence REAL,
  intention_id UUID REFERENCES intentions(id)
    ON STATE archived PROPAGATE SET invalidated,
  context_id UUID NOT NULL,
  embedding VECTOR(384)
) STATE MACHINE (status: active -> [invalidated, superseded])
  PROPAGATE ON EDGE CITES INCOMING STATE invalidated SET invalidated
  PROPAGATE ON STATE invalidated EXCLUDE VECTOR;

The flow:

-- 1. Blueprint exists: "Monitor {service_name} for {failure_type}"
INSERT INTO blueprints (id, name, template, slots, embedding)
VALUES ($bp_id, 'service-health-monitor',
        'Monitor {service_name} for {failure_type}',
        '{"service_name": "TEXT", "failure_type": "TEXT"}',
        $bp_embedding);

-- 2. Agent creates an intention, bound to the blueprint
INSERT INTO intentions (id, goal, status, blueprint_id, bindings, context_id, embedding)
VALUES ($int_id, 'Monitor auth-service for login failures', 'active',
        $bp_id, '{"service_name": "auth-service", "failure_type": "login failures"}',
        $ctx_id, $int_embedding);

-- 3. Link intention to blueprint
INSERT INTO edges (id, source_id, target_id, edge_type)
VALUES ($edge_id, $int_id, $bp_id, 'INSTANTIATES');

-- 4. Decision serves the intention, based on entity state
INSERT INTO decisions (id, description, status, confidence, intention_id, context_id, embedding)
VALUES ($dec_id, 'Alert at 200ms p99 latency', 'active', 0.85,
        $int_id, $ctx_id, $dec_embedding);

INSERT INTO edges (id, source_id, target_id, edge_type)
VALUES ($e1, $dec_id, $int_id, 'SERVES'),
       ($e2, $dec_id, $entity_id, 'BASED_ON');

When the decision is invalidated and replaced:

-- New decision supersedes the old one, serves the same intention
INSERT INTO decisions (id, description, status, confidence, intention_id, context_id, embedding)
VALUES ($new_dec_id, 'Alert at 350ms p99 - adjusted for eu-west-1', 'active', 0.9,
        $int_id, $ctx_id, $new_embedding);

INSERT INTO edges (id, source_id, target_id, edge_type)
VALUES ($e3, $new_dec_id, $int_id, 'SERVES'),
       ($e4, $new_dec_id, $dec_id, 'SUPERSEDES'),
       ($e5, $new_dec_id, $entity_id, 'BASED_ON');

UPDATE decisions SET status = 'superseded' WHERE id = $dec_id;

Now the lineage is queryable: intention → decisions that served it (current and historical) → what each was based on → what superseded what. Blueprints let the server aggregate across instances: “how many active intentions match the service-health-monitor blueprint across all contexts?”


Scenario 13: Outcomes and Learning From Results

Problem: An agent keeps recommending approaches that failed last time. It finds semantically similar past decisions, but has no signal for whether they worked. Without a feedback loop, the agent’s memory is a filing cabinet — organized but not intelligent.

-- Track what actually happened after a decision
CREATE TABLE outcomes (
  id UUID PRIMARY KEY,
  decision_id UUID NOT NULL,
  success BOOLEAN NOT NULL,
  impact TEXT,
  measured_at TIMESTAMP DEFAULT NOW()
) IMMUTABLE;

CREATE INDEX outcomes_decision_id_idx ON outcomes(decision_id);

-- Link decision to its outcome
-- edges table already exists from earlier scenarios

Record an outcome:

INSERT INTO outcomes (id, decision_id, success, impact)
VALUES ($outcome_id, $decision_id, TRUE, 'p99 latency dropped to 180ms');

INSERT INTO edges (id, source_id, target_id, edge_type)
VALUES ($edge_id, $decision_id, $outcome_id, 'HAS_OUTCOME');

Declare the outcome-weighted rank policy on the decision vector. The JOIN outcomes ON decision_id clause is resolved at DDL time, so the outcomes table above must already exist in this store — declaring it against a missing table refuses with rank policy on index 'decisions.embedding' joins unknown table 'outcomes':

CREATE TABLE decisions (
  id UUID PRIMARY KEY,
  description TEXT NOT NULL,
  status TEXT NOT NULL,
  confidence REAL,
  embedding VECTOR(384) RANK_POLICY (
    JOIN outcomes ON decision_id,
    FORMULA 'coalesce({confidence}, 1.0) * coalesce({success}, 1.0)',
    SORT_KEY effective_confidence
  )
) STATE MACHINE (status: active -> [invalidated, superseded]);

Now precedent search factors in outcomes inside the vector top-k selection:

SELECT id, description, confidence
FROM decisions
WHERE status IN ('active', 'superseded')
ORDER BY embedding <=> $task_embedding USE RANK effective_confidence
LIMIT 20;

The engine looks up outcomes.decision_id through the protected outcomes_decision_id_idx index and compares it with each decision’s id. It computes effective confidence before applying LIMIT. Decisions with poor outcomes sink in rankings. The agent learns from experience, not just similarity. For production schemas, keep the joined outcome table as one current summary row per decision. Multi-row history tables should be summarized before ranking until rank policies grow explicit aggregation or “latest outcome” semantics.

The same mechanism works outside decisions and outcomes. A corpus table can own embeddings while a separate annotation table stores a per-document metric such as editor approval, freshness, or compliance score.

CREATE TABLE annotations (
  id UUID PRIMARY KEY,
  document_id UUID NOT NULL,
  approved BOOLEAN NOT NULL,
  quality REAL NOT NULL
);
CREATE INDEX annotations_document_id_idx ON annotations(document_id);

CREATE TABLE documents (
  id UUID PRIMARY KEY,
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  embedding VECTOR(384) RANK_POLICY (
    JOIN annotations ON document_id,
    FORMULA 'coalesce({quality}, 1.0) * coalesce({approved}, 1.0)',
    SORT_KEY editorial_rank
  )
);

Here annotations.document_id is looked up through annotations_document_id_idx and compared with documents.id.

Raw similarity returns the nearest documents:

SELECT id, title
FROM documents
ORDER BY embedding <=> $query_embedding
LIMIT 3;

Ranked search uses the annotation metric before applying the final limit:

SELECT id, title
FROM documents
ORDER BY embedding <=> $query_embedding USE RANK editorial_rank
LIMIT 3;

Example ordering:

raw cosine:   draft-a, final-b, note-c
rank policy:  final-b, note-c, draft-a

This keeps the engine schema-agnostic: it does not know what “approved” or “quality” means. It only evaluates the declared formula against the candidate set and applies the top-k cutoff after ranking.


Scenario 14: Invalidation as a First-Class Workflow

Problem: Scenario 3 showed automatic cascading invalidation — the database transitioning decisions to invalidated. But an agent developer needs more than a status flag. When a decision is invalidated, the application needs to know: what exactly changed (basis diff), how critical is this (severity), has anyone acknowledged it (lifecycle), and what replaced it (resolution).

Invalidations are first-class objects with their own state machine:

CREATE TABLE invalidations (
  id UUID PRIMARY KEY,
  status TEXT NOT NULL,
  affected_decision_id UUID NOT NULL,
  trigger_observation_id UUID NOT NULL,
  basis_diff JSON NOT NULL,
  severity TEXT NOT NULL,
  detected_at TIMESTAMP DEFAULT NOW(),
  resolved_at TIMESTAMP,
  resolution_decision_id UUID
) STATE MACHINE (status: pending -> [acknowledged], acknowledged -> [resolved, dismissed]);

When impact analysis detects staleness, the application creates an invalidation record:

-- Record the invalidation with its basis diff
INSERT INTO invalidations (id, status, affected_decision_id, trigger_observation_id,
                           basis_diff, severity)
VALUES ($inv_id, 'pending', $decision_id, $observation_id,
        '{"field": "region", "old": "us-east-1", "new": "eu-west-1"}',
        'warning');

-- Link it via graph edges
INSERT INTO edges (id, source_id, target_id, edge_type)
VALUES ($e1, $inv_id, $decision_id, 'INVALIDATES'),
       ($e2, $inv_id, $observation_id, 'TRIGGERED_BY');

The invalidation lifecycle is the agent’s inbox for stale knowledge:

-- What needs attention? (pending invalidations, critical first)
SELECT i.id, i.severity, i.basis_diff,
       d.description, d.confidence
FROM invalidations i
INNER JOIN decisions d ON d.id = i.affected_decision_id
WHERE i.status = 'pending';

-- Acknowledge it
UPDATE invalidations SET status = 'acknowledged' WHERE id = $inv_id;

-- Resolve it by creating a replacement decision
UPDATE invalidations SET status = 'resolved',
       resolved_at = NOW(),
       resolution_decision_id = $new_decision_id
WHERE id = $inv_id;

Severity is confidence-weighted: a high-confidence decision (0.9) that is the sole active decision serving its intention → critical. A low-confidence decision (0.3) with alternatives → info. The application controls the thresholds.


Scenario 15: Enforceable Policy — Your Schema, Your Rules

Problem: The preceding scenarios use an agentic memory ontology (intentions, decisions, observations). But contextdb doesn’t mandate any particular schema. It provides enforceable policy primitives — STATE MACHINE, DAG, PROPAGATE, IMMUTABLE, RETAIN — that you declare on your tables. The database guarantees them. No application code can bypass them, no consumer can forget to check them, no race condition can violate them.

Here’s the same constraint system applied to a content workflow:

CREATE TABLE articles (
  id UUID PRIMARY KEY,
  title TEXT NOT NULL,
  body TEXT,
  status TEXT NOT NULL,
  embedding VECTOR(384)
) STATE MACHINE (status: draft -> [review], review -> [published, rejected],
                 rejected -> [draft], published -> [archived])
  PROPAGATE ON STATE archived EXCLUDE VECTOR;

CREATE TABLE categories (
  id UUID PRIMARY KEY,
  parent_id UUID,
  name TEXT NOT NULL
);

CREATE TABLE category_edges (
  id UUID PRIMARY KEY,
  source_id UUID NOT NULL,
  target_id UUID NOT NULL,
  edge_type TEXT NOT NULL
) DAG('CHILD_OF');

CREATE TABLE edit_log (
  id UUID PRIMARY KEY,
  article_id UUID NOT NULL,
  editor TEXT NOT NULL,
  diff JSON,
  edited_at TIMESTAMP DEFAULT NOW()
) RETAIN 365 DAYS;

Same enforceable policies, completely different domain:

  • STATE MACHINE enforces the editorial workflow — no application code can jump from draft to published
  • DAG prevents circular category hierarchies — no insert can create a cycle
  • RETAIN expires old edit logs after a year — no external cron job needed
  • PROPAGATE ON STATE archived EXCLUDE VECTOR removes archived articles from similarity search — no manual cleanup

These are database-level guarantees. The application declares the policy once in the schema. Every consumer — every agent, every API, every sync peer — is bound by it.

Schemas evolve at runtime too. As an agent learns about a new domain, it adds columns, renames fields, or drops what’s no longer useful. This block alters observations, entities and scratch — the tables Scenario 1 and Scenario 10 declare — so run those blocks in the same store first:

ALTER TABLE observations ADD COLUMN confidence REAL;
ALTER TABLE entities RENAME COLUMN entity_type TO kind;
ALTER TABLE scratch ADD COLUMN debug_notes TEXT;
ALTER TABLE scratch DROP COLUMN debug_notes;

Existing rows get NULL for newly added columns. Primary key columns cannot be dropped or renamed. DDL changes propagate through sync — when one instance adds a column, other instances pick it up on the next pull.

The agentic memory ontology is one powerful schema built with these tools. It’s not the only one.


Scenario 16: The Full Picture

A realistic agentic memory schema — one example schema built on contextdb’s primitives, not a schema contextdb ships — combines all of the above:

-- Isolation boundary (example table - you define contexts, contextdb doesn't ship one)
CREATE TABLE contexts (id UUID PRIMARY KEY, name TEXT NOT NULL);

-- Things in the world
CREATE TABLE entities (
  id UUID PRIMARY KEY,
  entity_type TEXT NOT NULL,
  name TEXT NOT NULL,
  context_id UUID NOT NULL,
  properties JSON
);

-- What happened (append-only facts, auto-expire after 90 days)
CREATE TABLE observations (
  id UUID PRIMARY KEY,
  observation_type TEXT NOT NULL,
  data JSON NOT NULL,
  entity_id UUID NOT NULL,
  source TEXT NOT NULL,
  embedding VECTOR(384),
  recorded_at TIMESTAMP DEFAULT NOW()
) RETAIN 90 DAYS SYNC SAFE;

-- Reusable intent templates
CREATE TABLE blueprints (
  id UUID PRIMARY KEY,
  name TEXT NOT NULL,
  template TEXT NOT NULL,
  slots JSON NOT NULL,
  embedding VECTOR(384)
) IMMUTABLE;

-- What we're trying to achieve - may instantiate a blueprint
CREATE TABLE intentions (
  id UUID PRIMARY KEY,
  goal TEXT NOT NULL,
  status TEXT NOT NULL,
  blueprint_id UUID,
  bindings JSON,
  context_id UUID NOT NULL,
  embedding VECTOR(384)
) STATE MACHINE (status: active -> [archived, completed, paused], paused -> [active]);

-- What we chose to do - always in service of an intention
CREATE TABLE decisions (
  id UUID PRIMARY KEY,
  description TEXT NOT NULL,
  status TEXT NOT NULL,
  confidence REAL,
  intention_id UUID REFERENCES intentions(id)
    ON STATE archived PROPAGATE SET invalidated,
  context_id UUID NOT NULL,
  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;

-- What actually happened after a decision
CREATE TABLE outcomes (
  id UUID PRIMARY KEY,
  decision_id UUID NOT NULL,
  success BOOLEAN NOT NULL,
  impact TEXT,
  measured_at TIMESTAMP DEFAULT NOW()
) IMMUTABLE;

-- Staleness notices with their own lifecycle
CREATE TABLE invalidations (
  id UUID PRIMARY KEY,
  status TEXT NOT NULL,
  affected_decision_id UUID NOT NULL,
  trigger_observation_id UUID NOT NULL,
  basis_diff JSON NOT NULL,
  severity TEXT NOT NULL,
  detected_at TIMESTAMP DEFAULT NOW(),
  resolved_at TIMESTAMP,
  resolution_decision_id UUID
) STATE MACHINE (status: pending -> [acknowledged], acknowledged -> [resolved, dismissed]);

-- Conversation summaries (retrieval anchor)
CREATE TABLE digests (
  id UUID PRIMARY KEY,
  source TEXT NOT NULL,
  summary TEXT NOT NULL,
  embedding VECTOR(384),
  context_id UUID NOT NULL
) IMMUTABLE;

-- Relationships between everything
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', 'CITES');

This example schema gives an agent all of this — but every capability below is a contextdb primitive doing the work; decisions, observations, intentions, and the rest are just the tables this walkthrough chose to declare it on:

  • Structured state — STATE MACHINE enforces lifecycles on whichever tables you declare it on (here, decisions, intentions, invalidations); entities carries typed properties as ordinary JSON columns, nothing contextdb-specific
  • Semantic recall — vector columns + <=> search work over any table you index — here, observations, decisions, and digests
  • Provenance — the DAG-declared edges table traces what was based on what, who cited whom; edges is an example graph table, not a built-in one
  • Automatic invalidation — PROPAGATE cascades status changes through the graph; invalidations is this schema’s example of a first-class record of that, not something contextdb ships
  • Feedback loop — a RANK_POLICY join (Scenario 13) lets outcomes weight future precedent search; the joined table can be anything you define
  • Intent normalization — blueprints is an example pattern-normalization table, made durable only by the IMMUTABLE policy declared on it
  • Sync — contextdb’s sync layer moves whatever tables you declare, SYNC TWO WAY/PUSH ONLY/PULL ONLY/OFF per table; local instances work offline, push/pull when connected
  • Retention — RETAIN expires rows on any table you attach it to; here, observations

None of this is contextdb’s schema — it’s one example schema, built entirely from primitives contextdb actually ships: STATE MACHINE, DAG, PROPAGATE, IMMUTABLE, RETAIN, vector columns, graph traversal, and sync. Your schema will look different, and contextdb enforces it just the same.

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 SEARCH DEFAULT | (TARGET_ACCURACY = decimal_percent | DEFAULT, EXPECTED_VECTORS = positive_integer | DEFAULT)
ALTER TABLE t ALTER COLUMN vector_col SET IVF DEFAULT | (LISTS = positive_integer | DEFAULT, PROBES = positive_integer | DEFAULT, REFINE_FACTOR = decimal_at_least_1 | 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.

Delivery is at least once: after a crash or an uncertain outcome the sink may see the same SinkEvent::action_id again, so a receiver deduplicates by it. There is no cross-event order promise. An event the sink’s registration cannot see (another context or scope label) stays queued and is retried on a later pass over the queue, behind events queued after it. A file-backed store keeps at most 1,024 queued events, or 1 MiB of decoded events, of each sink in memory and reads the rest from the store as delivery reaches it. A window the writer memory limit cannot hold is counted in SinkMetrics::window_refusals and flagged by SinkMetrics::window_refused; those events stay queued and are delivered once the limit admits them.

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]

GROUP BY is accepted only as COUNT(*) grouped by a declared MAINTAIN COUNT list; see Grouped counts.

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';

A bare JOIN with no join type is an inner join, as in standard SQL: FROM items JOIN tags ON ... parses exactly as FROM items INNER JOIN tags ON .... The alias rules are unchanged, so FROM items x JOIN tags y ON x.id = y.id keeps x and y as the aliases.

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.

Ranked rows per group

ROW_NUMBER() OVER (PARTITION BY cols ORDER BY col [ASC|DESC], ...) numbers each partition’s rows 1, 2, 3, … in the window’s order; OVER (ORDER BY ...) without PARTITION BY numbers the whole input as one partition. Ties get distinct numbers in the window’s order. Filter on the number in an outer query to take the first rows of each partition:

WITH ranked AS (
  SELECT context_id, connector_id, session_ref, latest_occurred_at,
         ROW_NUMBER() OVER (PARTITION BY context_id
                            ORDER BY latest_occurred_at DESC, connector_id ASC, session_ref ASC)
           AS session_rank
  FROM event_context_sessions
  WHERE context_id IN ($a, $b) AND latest_occurred_at IS NOT NULL
)
SELECT context_id, connector_id, session_ref FROM ranked
WHERE (context_id = $a AND session_rank <= $n) OR (context_id = $b AND session_rank <= $m)
ORDER BY context_id ASC, session_rank ASC

When each selected partition is named by equality with its own rank <= limit, and an index leads with the partition columns followed by the window’s order columns in the same directions (a column the inner WHERE pins with = or IS NULL may sit between them), each partition is read as one index seek that stops at its own limit: it examines at most its limit plus one entry, however many rows the partition holds. Otherwise the input is sorted by partition and order and numbered — the same answer, within the statement’s ordinary work and memory budgets. A reversed index does not serve a mixed-direction order, so newest-first and oldest-first each need their own index. The trace’s groups lists each partition the read touched, its key redacted, with the entries it examined, the rows it returned, and whether Index or Sort served it.

Every other window form is refused with WindowFunctionNotSupported naming it: RANK, DENSE_RANK, aggregates with OVER, ROW_NUMBER without ORDER BY, frames, named windows, several different windows in one SELECT, and computed partition or order expressions.

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.

Graph queries follow the same split. A GRAPH_TABLE ... MATCH query whose anchor pins a node the handle cannot see returns the typed visibility error that hid it — AclDenied for an ACL-hidden node — rather than an empty result. A traversal that reaches a hidden node omits that node silently; it does not return an error.

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, startup_ceiling, and ceiling_refresh_failures: how many times this process could not read its inherited memory ceiling (its cgroup limit). Each such failure keeps the last ceiling it did read; a process that has never read one refuses writer admissions, typed, until it can. Size suffixes are binary. Both settings accept optional =, 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.

SET READ_CACHE_SHARE = <percent> changes only the current ReadSession’s file page cache. The = is optional, as it is for SET MEMORY_LIMIT and SET DISK_LIMIT. An integer from 0 through 100 declares that percentage of the session memory budget after fixed source reservations. SET READ_CACHE_SHARE = DEFAULT restores the exact derived default, (budget - fixed reservations) / 7, displayed as default (14%). Zero disables the file cache. The change takes effect immediately without reopening the snapshot; an open cursor keeps its rows, snapshot, and funded pinned pages. An existing cursor also applies a later declaration on its next fetch after its writer exits. Cache inspection selects the current readable route after writer turnover; routing failures remain typed errors. Another session and a reopened session keep their own policy. These statements require no writable store and do not persist a store setting.

SET READ_CACHE_SHARE = 40;
SHOW READ_CACHE_SHARE;
SET READ_CACHE_SHARE = 0;
SET READ_CACHE_SHARE = DEFAULT;

Range refusals use Error::InvalidSetting { name, value, allowed }; for this setting name is READ_CACHE_SHARE and allowed is 0..=100, an integer percent. Non-integers such as 40% or 4.5 use this typed refusal and keep the previous declaration. SET MEMORY_LIMIT above its startup ceiling (or NONE under a finite startup ceiling) uses the same typed variant. A valid cache percentage still has to pass total memory admission: on an opened file reader, 100 consumes the entire remainder and leaves no room for the prepaid index and decoded state, so it returns the typed MemoryBudgetExceeded refusal. Either refusal keeps the previous declaration and capacity.

SHOW READ_CACHE_SHARE returns one row: declared (an integer, or text default), effective_bytes (Int64), effective_percent (Float64, relative to the remainder after fixed reservations), last_refusal (the last refused value as text, possibly shortened; initially Null), last_query_resident_percent (Float64 percent; initially Null), budget_bytes, fixed_reservation_bytes, prepaid_index_bytes, held_page_bytes, and default_percent. Settings and their inspection do not replace the last data statement’s residency observation. A running cursor can keep a smaller cache when following a growth would leave too little room to build its pages. Its rows and snapshot continue; the session’s declared share stays what the user set. last_refusal identifies the cursor and the capacity it kept. A later shrink still applies on its next fetch. On the owner route the policy describes a future file reader; physical cache fields and query residency are Null because this session does not own the writer’s shared cache.

Query residency is an advisory check of up to one cache-full of distinct pages from the last successful data statement. It can change with concurrent eviction and does not predict query cost. Null means no pages were measured: this includes no prior data statement, a statement that read no file pages, a disabled file cache, or a window superseded while another statement ran. Inspecting other settings and statements that fail before execution keep the last data statement’s observation.

SHOW DISK_LIMIT returns the same columns for file-backed storage.

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; no vector index is built in this release, so a wake builds and consolidates nothing.

SET STORAGE_COMPACTION START_RATIO 0.4 MIN_INTERVAL 30 MINUTES;
SET STORAGE_COMPACTION STOP_RATIO 0.02 MAX_SWEEPS 4;
SET STORAGE_COMPACTION DEFAULT;
SHOW STORAGE_COMPACTION;

Storage compaction is a per-store setting, like the polling interval: local configuration, not schema, and never synced. Undeclared values use these defaults:

SettingDefaultMeaning
START_RATIO0.5Automatic and post-prune compaction start once the share of the file a tidy-up could give back by moving pages reaches this. That share is interior free pages over the pages a trim would leave. Greater than 0 and at most 1.
MIN_INTERVAL1 hourMinimum time between automatic attempts. A check that finds the file below the start ratio also starts the interval. Takes MILLISECONDS, SECONDS, MINUTES or HOURS. Must be positive and at most 9,223,372,036,854,775,807 ms, so SHOW reports it exactly.
STOP_RATIO0.05Explicit compaction (.maintenance compact) keeps sweeping while the share is at or above this and a sweep still shrinks the file; when the share cannot be measured, it keeps sweeping while sweeps shrink the file. The same ratio also bounds every growth of the file: one growth adds at most this share of the file’s usable size (rounded down to whole bytes), or twice the allocation that needs it when that is larger, so a compacted file does not double back on its next write. Page and region-header rounding can add a few pages on top; a declared DISK_LIMIT still refuses growth past it. Greater than 0 and at most 1.
MAX_SWEEPS8The most sweeps one explicit compaction runs. At least 1.

Each SET STORAGE_COMPACTION names only the clauses it changes; the others keep their values. SET STORAGE_COMPACTION DEFAULT returns every setting to its default and removes the declaration. SHOW STORAGE_COMPACTION returns one row per setting with setting, value and source; source is declared or default. For a file-backed database the declaration persists across opens. .maintenance status shows the same values and sources, and every maintenance and compaction receipt names the policy that governed it.

A value outside its range, or a clause named twice in one statement, is refused with InvalidStorageCompactionDeclaration and changes nothing. The Rust setter Database::set_storage_compaction refuses the same values with the same issue, and a persisted declaration is checked again when the store opens. Concurrent declarations apply one at a time, so each keeps the clauses the other did not name, and the live setting is always the persisted one.


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

TypeDescriptionExample
INTEGER / INT64-bit signed integer42
REAL / FLOAT64-bit floating point3.14
TEXTUTF-8 string'hello'
BOOLEAN / BOOLBooleanTRUE, FALSE
UUID128-bit UUID'550e8400-e29b-41d4-a716-446655440000'
TIMESTAMPStored as a Unix timestamp (Value::Timestamp(i64)); ISO 8601 text literals are also accepted on inputNOW()
JSONJSON value'{"key": "value"}'
VECTOR(n)Fixed-dimension float vector[0.1, 0.2, 0.3]
TXIDEngine-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) declaration requires n to be at least 1. VECTOR(0) is refused before publishing the schema with InvalidVectorPolicyDeclaration and the DimensionsNotPositive issue: “vector dimensions must be at least 1”.

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:

  • AUTO and EXACT evaluate the rank formula over allowed vectors before the final limit. INDEXED is unavailable in this release.
  • 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)
);
ConstraintDescription
PRIMARY KEYUnique row identifier
NOT NULLValue required
UNIQUENo 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 exprDefault 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
IMMUTABLEColumn 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

Declared column defaults apply to both SQL INSERT and Database::insert_row before constraint validation. Supplied values remain unchanged; an omitted DEFAULT NULL is stored as a NULL cell. DEFAULT NOW() is evaluated at insertion, and later reads and reopens keep that stored timestamp.

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:

#![allow(unused)]
fn main() {
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:

#![allow(unused)]
fn main() {
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.

The direct-file route serves a stored count from its committed image. Writable handles and the owner read route serve it from the statement’s captured writer root and report used. If a route cannot serve the stored count, it counts the live postings over the same exact prefix range and reports declared. Every answer is exact; serving the stored count 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.

Grouped counts. COUNT(*) grouped by the rest of a declared list, with the list’s leading columns pinned by = or IS NULL, is answered from the stored counts — one stored count per group, so its work is the number of groups and never the number of rows they count:

SELECT connector_id, source_instance_ref, COUNT(*) AS event_count
FROM events WHERE context_id = $context_id
GROUP BY connector_id, source_instance_ref

A writable handle and the owner read route serve it from the counts at the statement’s own snapshot (maintained count: used (...)). A transaction that has staged rows of the table, a table whose rows are filtered per reader, and a store read as a committed image count the same groups from the rows instead, and the trace says declared (...), counted from the index range on this read route. Groups come back in the declared list’s order. A grouped COUNT(*) over columns the table maintains no count for is refused, naming the ALTER TABLE ... SET MAINTAIN COUNT that would serve it.

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
ClauseMeaning
SYNC OFFrows stay on the machine that wrote them
SYNC PUSH ONLYrows go out and never come back
SYNC PULL ONLYrows arrive from other machines; nothing local goes out
SYNC TWO WAYrows 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
ClauseMeaning
SYNC CONFLICT KEEP FIRSTwrite-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 LATESTlast-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

FunctionReturnsDescription
COUNT(*)INTEGERCount all rows
COUNT(col)INTEGERCount non-NULL values in column
SUM(col)INTEGER or FLOATAdd the non-NULL values in a numeric column
COALESCE(a, b, ...)variesFirst non-NULL argument
NOW()TIMESTAMPCurrent Unix timestamp

COUNT and SUM operate over the entire result set. The one grouped form is COUNT(*) grouped by a column list the table declares under MAINTAIN COUNT — the WHERE clause pins that list’s leading columns with = or IS NULL and GROUP BY names the rest — and it is answered from the stored counts. Every other grouped form is refused: HAVING, SUM or COUNT(col) per group, a select item that is neither a grouped column nor COUNT(*), and DISTINCT, ORDER BY, LIMIT, joins or CTEs around a grouped read.

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>:

#![allow(unused)]
fn main() {
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

An edge’s identity is the unique key its table declares. Where no edge key is declared, (source_id, target_id, edge_type) is automatically deduplicated; a surrogate id primary key still identifies the stored row. A declared key containing additional columns makes those columns part of the edge’s identity, so two rows differing in one of them are distinct edges. A row that duplicates a declared unique key, including a key that is exactly the triple, is refused with UniqueViolation. Both insertion APIs observe the same logical edge identity.

Graph cursors keep the row and authorization view captured when they open. Valid writes in the same transaction continue; a newly opened cursor sees those later writes. Captured staged rows retain their original writer admission until their last holder drops.

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.

What a Match Returns

The same matching rule applies to every reader. Access rules only remove the nodes and edges a reader may not see; they never change how the rest are matched.

  • A single hop returns one row for each matching edge. MATCH (a)-[:LINKS]->(b) with edges x→z and y→z returns (x, z) and (y, z). Two edges between the same nodes that both match, such as edges of two types under MATCH (a)-[]->(b), return two rows. An undirected hop reports a self-loop once.
  • A longer match (a variable-length step or several steps) walks every matching edge out from each start, up to the upper bound. An edge back to a node already reached still counts, so cycles and self-loops count as reachable, and a cycle that returns to a start node reports that start node when the cycle’s length is within the range.
  • Each combination of target nodes is reported once in a longer match, at the shallowest depth any start node reaches it. When two start nodes reach it at the same depth, the start node that comes first wins: first in the order the start nodes are given (an IN list, or the rows of a start subquery), or the smallest node id when the match does not name its start nodes.

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)
)

COLUMNS can project the id of each node the match binds (a.id, b.id), plus id and depth for the node reached and its depth. It cannot yet project any other property of a bound node: COLUMNS (b.context_id AS context) fails with project column not found: b.context_id. To read such a property, project the node’s id and join the node table on it.

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'

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

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.

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
    SEARCH (TARGET_ACCURACY = 95, EXPECTED_VECTORS = 1000000)
    IVF (LISTS = 64, PROBES = 8)
    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 stored 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, SEARCH, IVF, and CONSOLIDATION remain optional schema declarations. This release ships exact vector search only. AUTO and EXACT compare allowed stored vectors within active budgets. INDEXED returns VectorIndexedRouteUnavailable; the clustered index is switched off. Index policy declarations do not enable construction, maintenance or indexed serving.

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 EXACT;
ALTER TABLE vector_items ALTER COLUMN embedding SET AUTO_INDEX_AT 10000;
ALTER TABLE vector_items ALTER COLUMN embedding SET IVF (PROBES = 16);
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 vector identity and stored vectors with it, or make a replacement table with the desired vector partition key and copy the wanted rows; the replacement vector column declares its own vector identity (no index is built in this release).

Search modes and query override

This release ships exact vector search only. AUTO and EXACT compare allowed stored vectors within the active budgets; INDEXED returns VectorIndexedRouteUnavailable. The clustered index is switched off: commits and maintenance do not build or write it, and queries and inspection do not read it. Index policy declarations are retained but do not enable an index.

Use USE VECTOR EXACT after vector ordering and before USE RANK or LIMIT:

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

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 decide which rows are allowed before exact scoring. The active read budgets still apply.

Explain

.explain reports the requested and resolved mode (AUTO resolves to EXACT in this release), the declared partition key, whether the query selects one, several, or all authorized layouts, the route used by each Context (exact), the global candidate merge, and a refusal field (none when the find is served). With no index built, the explanation names the base and change layers as absent and the tail layer as present. It redacts unauthorized keys and counts. A constrained handle’s explanation omits per-partition population numbers, which could reveal the hidden population. 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; a constrained handle’s explanation leaves charged memory, passive vector activity, and relational scan counts unchanged.


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:

FeatureError
WITH RECURSIVERecursiveCteNotSupported
Window functions other than ROW_NUMBER() OVER ([PARTITION BY ...] ORDER BY ...)WindowFunctionNotSupported { form }
CREATE PROCEDURE / CREATE FUNCTIONStoredProcNotSupported
Full-text search (WHERE col MATCH pattern)FullTextSearchNotSupported
GROUP BY other than COUNT(*) grouped by a declared MAINTAIN COUNT list, and HAVINGParseError("GROUP BY is not supported")
COUNT(*) grouped by columns the table maintains no count overPlanError naming the ALTER TABLE ... SET MAINTAIN COUNT that would serve it
UNION / INTERSECT / EXCEPTNot supported
INSERT ... SELECTNot supported
Subqueries outside INSubqueryNotSupported
AVG, MIN, MAXNot 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 vector search 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:

ReasonMeaning
first column not in WHEREThe index’s leading column is absent from the filter.
function call in predicateThe leading column is wrapped in a function such as UPPER(col).
arithmetic in predicateThe leading column appears inside arithmetic such as col + 1.
non-literal rhsThe leading predicate compares to another column or subquery rather than a literal or bound parameter.
LIKE is residual-onlyLIKE remains a residual filter and does not drive an index scan.
fewer predicate columns matched than chosen indexAnother index matched a longer leading prefix.
lower selectivity than chosen indexAnother index matched the same prefix length with a more selective leading predicate.
tied with chosen index; lost by creation orderThe candidates were otherwise equivalent and the earlier index won.
no pinned vertexA 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

ErrorWhen 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.

Expression nesting limit

SQL expressions permit nesting of at most 128 levels in the source text, and expression trees at most 128 deep. These limits are independent. Parentheses, unary NOT and unary minus count toward source nesting; SQL strings, quoted identifiers and comments do not. Boolean AND and OR chains form balanced trees, preserving operand order and short-circuit evaluation; their balanced tree depth counts toward the expression-tree limit. Arithmetic retains its left-to-right grouping to preserve floating-point and overflow semantics. An arithmetic chain whose left-fold depth exceeds 128 is refused, never reassociated. Excessive nesting returns the typed ExpressionDepthExceeded error with the observed depth and limit before recursive parsing or AST use can overflow.

Sync Across Two Machines

contextdb syncs by dialing a key, not by connecting to a broker. One machine runs a hub; any number of edges dial it directly using a ticket the hub prints — no message broker, no port-forwarding, and (on a LAN) no external infrastructure at all.

You need the contextdb CLI and the contextdb-server binary on the machines involved; see Install. The .sync meta-commands and auto-sync are specified in the CLI Reference; the recipe-shaped depth — enrollment, delete propagation, conflict classes — is in skills/sync/SKILL.md.

Start the hub

On machine A, run a sync hub — a contextdb-server process the edges dial into:

contextdb-server --db-path hub.db --tenant-id demo

A hub requires a durable store: --db-path names the file it keeps its state in.

On startup it prints an enrollment ticket and the exact command an edge runs to connect (substitute your own database path for <client-db-path>):

enrollment ticket: <ticket>
To connect a client, run:
  contextdb <client-db-path> --write --sync-endpoint <ticket> --tenant-id demo

The ticket is the hub’s cryptographic identity — dial-by-key. Whoever holds it can dial the hub directly, wherever it is, including from behind NAT (the edge dials out; nothing needs to be reachable on machine A). The hub only serves — you don’t type SQL at it; your data lives on the edges that dial in. For scripting, three flags skip the banner: --show-ticket prints the bare ticket and exits, --ticket-file <path> writes it to a file, and --json emits a JSON object with enrollment_ticket, dial_command, endpoint, and tenant_id.

Treat the ticket as sensitive bearer material, not a public identifier. There is no allowlist — anyone who obtains the ticket can enroll and sync with the hub until the hub’s identity changes. Keep any file it’s written to (--ticket-file, --json output, shell history) out of version control and restrict its file permissions. If a ticket leaks, rotate by re-keying the hub — delete hub.db.fabric-identity.key and restart, which changes the hub’s identity and invalidates every previously issued ticket.

Connect two edges

On each edge machine, paste the ticket, giving each its own database file:

# machine B
contextdb edge-1.db --write --sync-endpoint <ticket> --tenant-id demo
# machine C
contextdb edge-2.db --write --sync-endpoint <ticket> --tenant-id demo

The ticket is pinned to each edge’s identity key on first connect, so later reconnects are authenticated the same way. Two machines on one LAN sync with nothing running in the middle — no cloud relay or other third party.

Converge in both directions

Sync is push/pull, driven from the REPL with .sync meta-commands. On edge 1, create a small example table (decisions is a table you define — see the README), insert a row, and push:

-- on machine B (edge 1)
CREATE TABLE decisions (id UUID PRIMARY KEY, status TEXT NOT NULL, reasoning TEXT);
INSERT INTO decisions (id, status, reasoning) VALUES ('750e8400-e29b-41d4-a716-446655440020', 'active', 'captured on edge 1');
.sync push

On edge 2, pull and read it back — the schema and the row both arrive:

-- on machine C (edge 2)
.sync pull
SELECT * FROM decisions WHERE id = '750e8400-e29b-41d4-a716-446655440020';
-- returns the row captured on edge 1

Now the other direction. Insert a row on edge 2, push, and pull it onto edge 1:

-- on machine C (edge 2)
INSERT INTO decisions (id, status, reasoning) VALUES ('850e8400-e29b-41d4-a716-446655440030', 'active', 'captured on edge 2');
.sync push
-- on machine B (edge 1)
.sync pull
SELECT * FROM decisions WHERE id = '850e8400-e29b-41d4-a716-446655440030';
-- returns the row captured on edge 2

Both edges now hold the same two rows, converged through the hub. .sync status reports what’s pending in each direction before you push or pull. Table direction and conflict behavior are durable CREATE TABLE/ALTER TABLE declarations (SYNC ... and SYNC CONFLICT ...), not session commands. The sync meta-commands and auto-sync are in the CLI Reference; the wire protocol is covered in the Architecture doc’s Sync section.

Restart durability

The hub keeps its identity in <db-path>.fabric-identity.key next to the database file (here, hub.db.fabric-identity.key). Restarting the hub reuses that key, so the same ticket — and every edge already pinned to it — keeps working. Back this file up like a credential; never commit it to source control.

Memory accounting

Fetching delivery outcomes is charged to the reading store’s declared reader memory on both sides. Each page stops at the reader’s cursor_page_bytes (and never exceeds the transport’s 64 MiB frame), and a fetch the reader cannot fund is refused with a typed memory refusal before its bytes are allocated.

Two things sit outside this accounting: the Iroh networking stack’s own buffers and connection state, and tracing output. Each accepted connection also keeps one small fixed task frame, bounded by the hub’s pre-admission connection limit (128 by default).

Manifested application units

The enrollment ticket authenticates the hub; application-table policy is bound separately. First declare it with contextdb --write hub.db; then call the edge’s SyncClient::bind_application_table_policy before creating those tables. The complete runnable recipe includes both steps.

For tenant-governed event tables, declare policy on the hub, bind it through the edge’s authenticated SyncClient, and install matching DDL before recording units. Register each manifest in its root’s write transaction. Manifests use the ordinary push and its existing oversized-unit staging transport. Every committed column travels with its row, including a large body the edge store keeps on disk rather than in memory.

A durable outcome, rather than a push watermark, ends that unit’s pending obligation; fetch recovers lost acknowledgements without applying rows again. Refused rows stay on the edge and their diagnostic keys identify them.

An ordinary status exchange verifies one hub-signed prefix checkpoint against the hub image. A failed verification is an error. A restored image that lost acknowledged custody receives a new incarnation; unrelated writes or later purges cannot mask that loss. This also works with a declared snapshot taken before the first binding: the edge automatically rebinds, old outcomes become pending, and unchanged local rows are offered again. Missing declarations remain a typed policy refusal. Reopening the current image preserves its incarnation and valid credit.

See tenant policy and event custody for the SQL, local-discard modes, metadata inspection, and atomic purge delivery to independently keyed node-local tables. Operator-held backups from before a purge remain outside the engine’s erasure reach: restore one only with the documented re-purge recovery.

CLI Reference

contextdb is primarily used as an embedded Rust library. The CLI is for exploration, debugging, and scripting against a database file.

Two binaries: contextdb (interactive client) and contextdb-server (sync coordinator).


CLI Client (contextdb)

contextdb <PATH> [OPTIONS]

<PATH> is the database file.

Reading is the default. contextdb <PATH> opens an existing store for bounded, read-only inspection: it never creates the store, never mutates it, and leaves every byte in the store folder unchanged. A no-op meta-command like .help does not rewrite anything either. There is nothing to copy first — reading never requires a peek copy.

Anything that would write — creating a

store, DML, DDL, transactions, maintenance, sync operations — takes the explicit --write flag; a read session refuses it before execution with write_requires_flag.

The same command works whether the store is idle or already owned by a live process:

SituationCommandOutcome
Idle existing storecontextdb <path>Direct read-only file session; several direct readers may coexist.
Missing storecontextdb <path>Refused without creating anything (store_not_found); --write creates it.
Idle or missing storecontextdb <path> --writeFull read-write session; the store is created if missing.
Live file-backed ownercontextdb <path>Reading session over that owner’s authenticated local channel.
Live file-backed ownercontextdb <path> --writeRefused with held_by_writer; the refusal says a read session (drop --write) reaches the live owner’s channel.
Reader selecting its snapshotcontextdb <path> --writeWaits once for the OS store lock; the reader releases its admission hold after fixed-cost snapshot selection and registration, before heavy loading. Only caller cancellation/deadline ends the wait early; an already-expired deadline yields typed held_by_readers with the actual count and identifiable names.

Options

FlagDefaultDescription
--writeoffAuthorizes file creation, mutation, transactions, maintenance, and sync. Also starts the local owner-reading service (see Owner Inspection).
--jsonoffMachine output: stdout is JSON Lines, one document per statement or meta-command; errors and notices are JSON documents on stderr. Changes rendering only — never a ceiling.
--sync-endpoint <TICKET>(none)Writer-only: server’s enrollment ticket to sync with (dial-by-key).
--tenant-id <ID>(none)Writer-only: sync namespace. Omit for local-only mode.
--sync-debounce-ms <MS>500Writer-only: auto-sync batching window.
--memory-limit <SIZE>(unlimited)Whole-session memory ceiling, read or write mode. Suffixes: K, M, G. A read-only open first reserves about 200 KB (197,048 bytes) of fixed source memory plus its cache share, so a limit below that is refused; the floor is necessary, not sufficient, because the schema, controls and cache need more.
--disk-limit <SIZE>(unlimited)Session disk ceiling. Never authorizes writes.
--maintenance <MODE>engine-ownedMaintenance schedule for this process: engine-owned starts the database worker when work is declared; caller-driven starts no worker and leaves scheduling to .maintenance run or the embedding host. Writer-only for file-backed stores.
--maintenance-poll-ms <MS>persisted value, else 60000Positive engine-owned poll interval in milliseconds. A supplied value is persisted for a file-backed database; omitting the flag uses that persisted value on later opens rather than overwriting it. Writer-only for file-backed stores.

Per-invocation read ceilings and deadlines (see Declared Limits):

FlagDefaultGoverns
--read-result-rows500Complete ordinary-result rows; also the maximum one cursor fetch may request
--read-result-bytes4 MiBCanonical bytes of one complete result, metadata page, or metadata object
--read-work50,000Items examined by one read or one fetch
--read-active-ms5,000Active execution per read or fetch
--read-memory16 MiBTemporary memory held by one read
--read-cursor-page-rows100Fetch size when the row count is omitted
--read-cursor-page-bytes1 MiBCanonical bytes in one cursor page
--read-cursor-idle-ms300,000Time allowed between cursor fetches
--read-cursor-lifetime-ms1,800,000Total cursor lifetime
--read-hydration-notice-ms1,000Threshold for the loading and statement-progress notices
--read-owner-connect-ms1,000Owner-route connect + handshake deadline
--read-owner-routing-retry-ms1,000Owner startup/shutdown race retry window
--read-owner-response-ms11,000Complete owner reply after admission

All limit values must be positive; configuration is refused before open (exit 2) unless cursor_page_rows <= result_rows, cursor_page_bytes <= result_bytes, and cursor_idle_ms <= cursor_lifetime_ms.

The environment is not a behavior surface. The CLI reads exactly two declared-behavior environment variables — CONTEXTDB_DB_PATH and CONTEXTDB_OWNER_READ_RUNTIME_DIR, the two things a process must know before flags exist — and a flag always wins over either. Platform location inputs also locate the channel and private data directories: XDG_RUNTIME_DIR, XDG_DATA_HOME and HOME (with an effective-user home lookup when needed). These select locations, not read limits or query behavior; see Owner Inspection. For observability, RUST_LOG changes log verbosity on the diagnostic stream and nothing else — never routing, limits, results, or exits.

Reading routes

The first store-reading command resolves how this session reads (.help, .trace, .quit, .exit, .sync status, and .owner status never resolve a route). At route selection the CLI emits a structured notice on stderr after that command. It emits another notice whenever the selected route or committed snapshot LSN changes; an unchanged selection is announced only once:

{"notice":{"class":"io","message":"…","detail":{"kind":"read_route","route":"file","snapshot_lsn":42,"snapshot_at":"2026-08-16T09:30:12Z"}}}

route is file (a direct read of the committed snapshot) or owner (a live process owns the store and serves this session over its authenticated local channel — same commands, same shapes). On the file route snapshot_lsn identifies the committed snapshot, and snapshot_at states when it was selected. The LSN names its committed read frontier. Both fields are null on the owner route, which serves live committed state. Later commands follow returning writers within the same store incarnation and announce their new selection. An already open cursor keeps the snapshot it captured. If a statement loses its owner while running and cannot continue, the statement fails visibly without publishing a partial result; later commands select the current route within their routing allowance.

Two liveness notices share the --read-hydration-notice-ms threshold (default 1 s), both on stderr so --json stdout stays pure: while the first read is still loading the store, a hydration notice with bytes loaded so far; once a statement is executing, a statement_progress notice with elapsed time and the rows and bytes produced so far, refreshed until the statement completes or refuses — a long deliberate export is never mistaken for a hang.

--json

Under --json, stdout is JSON Lines: one complete JSON document per statement or meta-command, and nothing else. Every successful ordinary SELECT is one namespaced, column-carrying document:

$ echo "SELECT * FROM entities;" | contextdb ./my.db --json
{"result":{"columns":["id","name"],"rows":[{"id":"550e8400-…","name":"sensor-1"}]}}

Every meta-command emits a document whose top-level key names its payload:

CommandDocument
.tables{"tables":{"items":[…],"has_more":bool,"continuation":"…"|null}}
.schema <table>{"schema":{"table":…,"columns":[…],"primary_key":[…],"indexes":[…],…,"ddl":…}} — the full declared contract, namespaced
.events status{"events_status":{"items":[…],"has_more":bool,"continuation":"…"|null}}
.maintenance status{"maintenance":{…}}
.verify history <table>{"verify_history":{"table":…,"indexes":[{"index":…,"postings":n,"versions":n}]}}
.explain <sql>`{“explain”:{“physical_plan”:…,“runtime_trace”:true
.cursor open / .cursor fetch{"cursor":{"columns":[…],"rows":[…],"has_more":bool}}
.cursor close{"cursor":{"closed":true}}
.owner status{"owner":{"state":…,…}} (see Owner Inspection)
.trace on|off{"trace":"on"} / {"trace":"off"}
.sync policy [FOR table]{"result":{"columns":[…],"rows":[{…}]}} — persisted tenant policy
.sync bindings{"result":{"columns":[…],"rows":[{…}]}} — persisted authenticated bindings
.sync outcomes FOR table [WHERE ...] [LIMIT n OFFSET m]{"result":{"columns":[…],"rows":[{…}]}} — durable unit outcomes
.sync status (read session){"sync_status":{"message":"no sync in this session — …"}}
.sync push / .sync pull / .sync reconnect / .sync destination / .sync auto (write session)The shipped sync documents ({"sync":…}, {"sync_push":…}, {"sync_pull":…}, …)
.help{"help":["line", …]}, on stderr

.schema returns the table’s declared contract as data — columns (type, nullability, key/unique/immutable flags, references and propagation clauses, acl_references naming the grant table and grant column an access-controlled column is authorized against, vector quantization, rank policy), primary_key, indexes, state_machine, retain, history, sync_direction, conflict_policy, dag_edge_types, propagate, and ddl, the exact text the human .schema prints. Five fields are always present whether or not the table declared them — immutable (false), dag_edge_types ([]), propagate ([]), indexes ([]), and each column’s default / immutable / unique / expires — so a consumer reads those as values, not as evidence of a declaration. Every other policy — state_machine, retain, history, sync_direction, conflict_policy — is absent when the table never declared it, rather than filled with a default nobody wrote.

Everything that is not a result goes to stderr as a JSON document — errors, notices, traces, and .help:

  • Errors, one document per error: {"error":{"class":"sql|sync|io|usage","message":"…","detail":{"kind":"…", …}}}. class and detail.kind are the stable contract to branch on; message is prose for a person and its wording changes freely. line is present when the CLI knows which input line the statement started on. See Error Classification for every stable kind.
  • Notices — the same envelope with "notice": something worth seeing that is not a failure. The route, hydration, and statement-progress notices above are the notice family’s read-surface members.
  • Execution traces from .trace on, as {"trace":{…,"rows_examined":N}}.
  • .help, as {"help":["line", …]} — guidance for a person, not a result.

The promise starts once the arguments have parsed: a malformed command line is rejected by the argument parser with its own human rendering and exit 2; everything after that point is JSON.

Ordinary results: complete or refused

Every successful ordinary SELECT publishes its complete result — no renderer truncates, and there is no row cap to disable (--all does not exist). Under shipped defaults a result succeeds only when it has at most 500 rows (result_rows) and its canonical encoding is at most 4 MiB (result_bytes). Crossing either ceiling publishes no rows for that statement and refuses with owner_limit_exceeded, exit 1. The refusal carries the refused statement verbatim (detail.statement) and teaches both escapes, route-aware:

  • File route: raise the matching --read-* ceiling for a deliberate one-shot export (a work refusal names --read-work and does not suggest result paging), or page with the cursor when the result row or byte ceiling is the limit. The detail carries the copy-ready .cursor open <statement> in remedy_command, and human mode prints the whole refusal as one line:

    Error: the answer went past the rows this read is allowed: 500 rows; .cursor open SELECT * FROM big; raise --read-result-rows / --read-result-bytes for a deliberate one-shot export
    
  • Owner route: the ceiling is owner-imposed — a caller can lower but never raise owner policy. The refusal names the matching writer-side --owner-read-* setting; a work refusal names --owner-read-work and does not offer cursor paging as a remedy. A writer’s own reads use --read-*, including the default --read-work of 50,000; --owner-read-* governs only the read service it exposes to other sessions.

Human mode renders a successful ordinary SELECT as a bordered +---+ table — a rule line, the column names, a rule line, one row per line, a closing rule line — and ends it with exactly one footer: (N rows), on its own line. That is the whole rendering (the REPL sample under REPL is the same table), and there is no built-in pager — pipe to less. Cursor pages are the one read result that is not boxed: .cursor open / .cursor fetch print one row per line, values separated by |, with no border, and close the page with its own footer — (N rows, has_more: <true|false>) — rather than the (N rows) an ordinary SELECT ends with. The same bounds apply to a --write session’s SELECTs — a --write session executes write-classified statements normally, and its reads are bounded like anyone’s; the embedded Database::execute API keeps its uncapped library contract. The SQL dialect itself (types, functions, graph and vector clauses) is documented in docs/query-language.md.

Large results: the session cursor

One cursor per CLI session (piped sessions included):

  • .cursor open <SELECT or SHOW VECTOR_PARTITIONS statement> — opens one committed snapshot, returns the first page.
  • .cursor fetch [rows] — next page; omitting rows uses cursor_page_rows (default 100); an explicit count above the effective result_rows refuses with owner_limit_exceeded without ending the read — the refusal carries the copy-ready .cursor fetch <effective-limit> that works, and running it pages on from exactly where the cursor stopped.
  • .cursor close — releases the cursor.

.cursor open accepts exactly one read-only SELECT or SHOW VECTOR_PARTITIONS statement. A write refuses with write_requires_flag; any other statement is cursor_invalid_statement and never executes. A second .cursor open refuses with cursor_already_open until the first closes; independent CLI sessions have independent cursors.

A page contains only complete rows and stops at the requested row count or cursor_page_bytes; a single row that cannot fit a page even alone refuses with owner_limit_exceeded naming cursor_page_bytes and the advice to select fewer columns — no value is cut in half.

`has_more: false` appears only on genuine exhaustion and

closes the cursor automatically. Draining or closing past the end is never an error: a later .cursor fetch returns one empty success page (rows: [], has_more: false) and a later .cursor close succeeds as a no-op — so a fetch-until-empty loop always exits clean. cursor_not_found fires on either route when a cursor never existed, was explicitly closed, or belongs to another session. On the live-owner route only, if owner shutdown drains a cursor or its prepared page before delivery, the request instead receives owner_not_serving. A mis-sized .cursor fetch <n> is the one refusal that does NOT end the read: it is checked before any row is read, so nothing is consumed and the cursor keeps its place on both the direct-file and live-owner routes. Everything else that stops a cursor ends it and releases its snapshot and memory — exhaustion, .cursor close, cursor_expired, losing the owner, and a single row too wide for cursor_page_bytes.

Work and active-time budgets are charged per fetch, so a

table far larger than one work budget pages to exhaustion under shipped defaults.

A cursor that crosses `cursor_idle_ms` or

cursor_lifetime_ms refuses with cursor_expired, naming idle or lifetime and pointing at a fresh .cursor open.

A --write session may use the cursor while no transaction is open; .cursor open during an active transaction refuses with cursor_transaction_active without disturbing the transaction.

A cursor you opened while the writer was running keeps its results after the writer stops; it uses the memory you gave the reader, not the writer’s.

Metadata: bounded and resumable

.tables and .events status return as many complete items as fit in result_bytes, plus continuation state; they never use the SQL cursor:

.tables [--continue <continuation>]
.events status [--continue <continuation>]

Each sink item reports uncertain separately from delivered and permanent_failures. It counts external attempts whose outcome could not be confirmed, including an attempt interrupted by a crash. A pending action may still be retried with the same action identity; this count does not assert that the recipient saw or did not see it. After an unclean exit, the file route may require a writable reopen to repair the store before any read-only file session can open. The owner route then reports the pending uncertainty before a sink callback is registered or delivery resumes. After a clean close, the file route reports the same history without a writer.

continuation is a string exactly when has_more is true and null when it is false; a continuation is accepted only by the command that issued it (anything else refuses with invalid_continuation). Every human page ends with a has_more: <true|false> line — printed on an exhausted page too — and when it is true the exact follow-up command comes on the next line.

.schema <table> and .maintenance status are single complete responses. If the complete response does not fit result_bytes, no partial metadata is published; the refusal is one valid document whose detail names result_bytes, required_bytes, and required_setting rendered as effective result_bytes >= <required_bytes>. A single .tables/.events status item that cannot fit even an empty page refuses the same way — never a partial or oversized page. Both routes have identical shapes, continuation mechanics, and refusal fields. Consistency across pages differs by route: reading an idle file, metadata pages use the selected committed snapshot and are mutually consistent while that committed file is unchanged; reading through a live owner, each page reads current committed state in stable item order — an item never appears on two pages, an item that existed unchanged throughout is never skipped, and an item created or dropped between pages may or may not appear (as psql’s catalog listings behave under concurrent DDL).

A reader keeps working while the writer restarts. If the writer is down, the reader answers from what was last saved. When the writer is back, the reader switches to it on its own. You never have to reopen a reader because the writer came and went. In the read shell you see a short line each time this happens, with the point in the data it is reading from.

Declared limits

Every read threshold is declared configuration. A direct reader spends its own process resources and uses its requested values; on the owner route the effective value is, field by field, the stricter of your request and the owner’s advertised maximum — you can lower owner policy, never raise it. The writer/server side declares its policy with the mirrored --owner-read-* flags plus --owner-read-concurrency (default 4), --owner-read-request-ms (10,000), --owner-read-shutdown-drain-ms (10,000), --no-owner-reads, and --owner-read-runtime-dir <absolute-path>.

Local-Only Mode

The simplest way to start — no server, no network:

contextdb ./my.db          # read-only inspection of an existing store
contextdb ./my.db --write  # create/mutate, persisted to file

Sync Mode

Sync is writer capability: --sync-endpoint and --tenant-id configure a --write session’s edge enrollment, exactly as before. A read session’s .sync status prints exactly: no sync in this session — this reports the CLI session only, not the store; a live owner's sync state belongs to that owner process.

For an edge, a bare pasted ticket uses the durable adjacent identity at <canonical-db-path>.fabric-identity.key; an explicit identity=<key-file> always wins. If the endpoint is down, sync prints one clear warning line rather than failing hard.

Logging

Logs never share a stream with machine output: CLI logs go to stderr so they don’t interfere with query output, and the server keeps its logs on stderr whenever its stdout carries a machine document (--json). The default level is ERROR; raise it with RUST_LOG=debug — verbosity is the only thing that variable can change.


Store Maintenance (reset / diagnose / snapshot / inspect / purge)

This section describes the maintenance subcommands and their operator contracts.

Operational subcommands are part of the one typed command tree and appear in ordinary --help.

contextdb reset <PATH> --force
contextdb diagnose <PATH>
contextdb snapshot export <PATH> <NEW_ARTIFACT> [--json]
contextdb inspect key <SNAPSHOT_OR_DATABASE_PATH> --table <TABLE> --key-json <NATURAL_KEY_JSON> [--column <COLUMN>]... [--json]
contextdb inspect blob <SNAPSHOT_OR_DATABASE_PATH> --hash <64_HEX_CHARS> [--json]
contextdb inspect sync-apply-state <SNAPSHOT_OR_DATABASE_PATH> [--json]
contextdb purge <PATH> --table <TABLE> --force
CommandWhat it does
reset <PATH> --forceRecreate a wedged or corrupt store from scratch. Destructive.
diagnose <PATH>Report a store’s format and schema layout read-only, never modifying it.
snapshot export <PATH> <NEW_ARTIFACT>Publish a transactionally consistent, purge-fenced backup artifact.
inspect <SUBCOMMAND> <SNAPSHOT_OR_DATABASE_PATH>Read durable key and media state from a snapshot artifact or database file.
purge <PATH> --table <TABLE> --forceForce-gated whole-table authoritative erasure.

reset --force — recreate a wedged or corrupt root from scratch

Destructive: deletes the existing file and creates a fresh, empty current-format store. Requires the explicit --force flag (exit 2 without it, nothing attempted) — restore anything you need from a backup or a healthy sync peer FIRST.

diagnose — read-only diagnosis, never modifies

Reads the store’s format marker and top-level schema layout through a read-only handle and reports whether the store is readable, was written by an earlier build, or is corrupt or truncated, without writing to the path. A store written by an earlier build is not read. Move it aside and create the store again.

snapshot export — publish a purge-fenced backup

Uses the engine’s transactionally consistent export path. The artifact path must not already exist. If an authoritative purge wins the race after capture, publication is refused instead of producing a backup that silently resurrects the purged lineage.

inspect — read durable key and media state from a snapshot artifact or database file

inspect never opens the supplied path directly: it copies into a private temporary directory, opens only the disposable copy, and emits a bounded report with no raw media bytes, tag names, identity material, or storage paths. inspect key reports a row’s retained versions (newest 128, with explicit truncation), lineage, and up to 16 requested columns within a 1 MiB budget; inspect blob reports an engine-held blob’s fences and transfer progress, never the payload; inspect sync-apply-state emits one opaque deterministic digest plus category counts for before/after atomic-sync audits. A snapshot export artifact is the guaranteed-consistent input; a live file gives whatever state exists at copy time.

inspect key’s --key-json takes the row’s natural key as a JSON object with exactly three fields — column (the leading key column), value (that column’s value), and rest (the remaining [column, value] pairs of a composite key, [] for a single-column key). value is a typed value, written as a one-key object naming the type: {"Uuid": "<uuid>"}, {"Text": "<string>"}, {"Integer": 42}. A bare "<uuid>", a {"id": "<uuid>"} map, or a [["id", …]] pair list are all rejected by the deserializer. One worked call:

contextdb inspect key ./my.db --table decisions \
  --key-json '{"column":"id","value":{"Uuid":"550e8400-e29b-41d4-a716-446655440000"},"rest":[]}' \
  --column description --json
# {"lineage":null,"retained_versions":[{"created_tx":1,"deleted_tx":null,"lsn":2,"row_id":1,
#   "values":{"description":{"kind":"text","omission_reason":null,"omitted":false,
#   "source_units":15,"value":{"Text":"adopt contextdb"}}}}],
#  "total_retained_versions":1,"versions_truncated":false}

A composite key (tenant, id) is {"column":"tenant","value":{"Text":"acme"},"rest":[["id",{"Uuid":"…"}]]}.

purge — force-gated whole-table authoritative erasure

contextdb purge <path> --table <table> --force; narrower selection remains SQL PURGE ... WHERE in a --write session.


REPL

An interactive session prints one start banner naming its mode — read sessions read-only session — pass --write to mutate, write sessions write session — mutations are enabled — and prompts contextdb(ro)> or contextdb> . Neither the banner nor the prompt appears when the session is not interactive.

contextdb(ro)> SELECT * FROM entities;
+--------------------------------------+----------+
| id                                   | name     |
+--------------------------------------+----------+
| 550e8400-e29b-41d4-a716-446655440000 | sensor-1 |
+--------------------------------------+----------+
(1 rows)

Every statement ends with a ;; pressing Enter before one continues the statement under the ...> prompt. Ctrl-C during a running statement or cursor fetch cancels only that statement and returns to the prompt with the session and any open cursor intact; Ctrl-C at an idle prompt clears the typed line; Ctrl-D or .quit ends the session.

A reader can use SET READ_CACHE_SHARE 40 (or SET READ_CACHE_SHARE = 40), SET READ_CACHE_SHARE DEFAULT, and SHOW READ_CACHE_SHARE without --write. The declaration changes this session’s file cache immediately; it is not persisted. SHOW includes the declared/effective share, the last refusal and advisory last-query residency. Null means no pages were measured. Percentages must be integers from 0 through 100; admission can refuse a valid percentage while keeping the previous declaration.

Runtime budget control is SQL-driven and classified like everything else: SHOW MEMORY_LIMIT / SHOW DISK_LIMIT are reads; SET MEMORY_LIMIT '512M' / SET DISK_LIMIT '1G' are writes and need a --write session. Input that does not parse as any statement is refused before execution with an ordinary sql-class parse error naming the offending token, identically in read and write sessions.

Meta-Commands

Every spelling in this table comes from one place — canonical_help_signatures() in the command registry, which is also what the REPL’s own .help is generated from. There is no second command table anywhere, so a command cannot appear in one surface and be missing from the other.

A meta-command consumes exactly one line — it never accumulates across newlines until a ;. Paste a meta-command’s whole invocation, including any SQL argument, on one line.

CommandAliasRead/WriteDescription
.help / .help vector / .help propagate\?sessionShow available commands / specialized grammar.
.quit / .exit\qsessionExit the REPL.
.tables [--continue <c>]\dtreadList table names; large listings resume via continuation.
.schema <table>\d <table>readShow the table’s full declared contract; complete-or-refuse. Per-column IMMUTABLE, vector quantization, and RANK_POLICY clauses render alongside NOT NULL / PRIMARY KEY.
.explain <sql>readShow the route: ordinary read-only queries execute through the bounded reader, vector similarity is inspected passively, and writes are only planned.
.trace on / .trace offsessionToggle one-line execution traces.
.events status [--continue <c>]readBounded, resumable event/sink/route/schedule health.
.maintenance statusreadOne complete bounded maintenance-state response.
.verify history <table>readCheck every historical index of a table against its row versions; refuses with StoreCorrupted on the first disagreement.
.maintenance run / .maintenance compactwriteRun one synchronous maintenance cycle / explicitly drain file compaction.
.cursor open/fetch/closereadBounded traversal beyond an ordinary-result ceiling.
.owner statusstatusOwner policy and state; works at capacity.
.sync statussessionCurrent CLI session’s sync state only.
.sync push/pull/reconnect/destination/autowriteSync operation and recovery.

Each conventional alias is one spelling of the command beside it and carries that command’s classification exactly:

AliasSpells
\dt.tables
\d.schema
\q.quit
\?.help

\trace and \sync do not exist.

.maintenance run drives one synchronous maintenance cycle on this process: it expires rows whose table declared RETAIN, reclaims superseded versions on HISTORY CURRENT ONLY tables, advances vector index work, and may attempt automatic file compaction. Use it when the next statement must see that cycle finish — in particular a --write session opened with --maintenance caller-driven, which starts no background worker and leaves scheduling to this command. An idle file-backed store prints one line of counters:

pruned_rows=0 rows_deferred_for_readers=0 currency_pruned_versions=0 pruned_trigger_audit_rows=0 auto_compact_ran=true vector_built_indexes=0 vector_remaining_indexes=0 vector_nonempty_partitions=0 vector_ready_partitions=0 vector_built_partitions=0 vector_remaining_partitions=0 vector_first_failure=none vector_failure_operation=none vector_failure_requested_bytes=none vector_failure_available_bytes=none vector_failure_current_bytes=none vector_failure_budget_limit_bytes=none vector_failure_message=none vector_failure_recovery_action=none vector_failure_recovery_instruction=none auto_compact_fragmentation_before=unknown compaction_policy=start_ratio:0.5(default),min_interval_ms:3600000(default),stop_ratio:0.05(default),max_sweeps:8(default)

--json emits one maintenance_cycle document with those same facts as named fields (pruned_rows, rows_deferred_for_readers, reclaimed_bytes, file_shrank, currency_pruned_versions, currency_versions_deferred_for_readers, currency_redb_compacted, pruned_trigger_audit_rows, vector, compaction). Compaction byte counts and duration depend on the file.

A compaction receipt’s fragmentation_before is the share of the file a tidy-up could give back by moving pages: interior free pages over the pages a trim would leave. Both the automatic path and .maintenance compact report this one quantity, and automatic compaction starts when it reaches the declared start ratio (0.5 by default; see SET STORAGE_COMPACTION in the query language reference). .maintenance compact also reports page_slack_ratio: the share of bytes inside live pages that hold no data, from an exact walk of the file. Compaction moves whole pages and does not repack them, so that slack is not given back; it is unknown (JSON null) when the walk failed, and always JSON null in .maintenance run’s compaction document. When nothing was measured, the field is unknown: JSON null, text fragmentation_before=unknown (auto_compact_fragmentation_before=unknown in the .maintenance run line). That covers a cycle with no check due, a store whose allocator totals are not trusted, and a failed measurement. A check that found the file below the start ratio reports what it measured. Every later batch of a sweep reports the measurement that sweep started on, so a sweep that started unmeasured stays unknown until it completes, across reopen. It is never reported as 0.

Each compaction receipt also names the storage compaction policy that governed it. JSON carries a policy object under compaction, mapping each setting to its value and source (declared or default). Text carries one compaction_policy= token, for example compaction_policy=start_ratio:0.5(default),min_interval_ms:3600000(default),stop_ratio:0.05(default),max_sweeps:8(default).

.maintenance status adds a storage_compaction list. Each entry is a setting, value and source; source is declared or default. The text form adds one line, for example storage_compaction start_ratio=0.5(default) min_interval_ms=3600000(default) stop_ratio=0.05(default) max_sweeps=8(default). Both read routes show the same declaration and sources: the committed file when no writer is open, and the live owner when one is.

Trace vs Explain

.explain <sql> has three deliberately different paths. An ordinary relational SELECT or WITH executes through the session’s normal bounded read route, under the same row, byte, work, memory, deadline, and cancellation ceilings. Its explanation is the route that actually ran: human output shows the physical strategy, chosen index, pushed predicates, rejected candidates, and sort elision; JSON carries those route facts plus real rows_examined and reports runtime_trace: true.

A vector nearest-neighbour query whose parsed ORDER BY uses <=> is passive. It reads only safe catalog, authorization, lifecycle, and policy metadata; it does not run the search, load graph or raw-vector pages, replay or build an index, start repair or compaction, or change residency and charges. Its complete redacted vector-route disclosure reports runtime_trace: false. A write is also planned without execution, so .explain DELETE FROM t and .explain INSERT INTO t ... never change rows and likewise report runtime_trace: false. Use .trace on to print the runtime trace after a statement you execute directly.

The trace .trace on prints is the value a Rust caller reads from QueryResult.trace: the REPL renders that QueryTrace field of the executed statement’s result and adds nothing to it, so a scripted session and an API caller describe the same run in the same fields.

.schema and Enforced Policy

.schema <table> reflects the table’s full enforced policy — RETAIN, HISTORY, STATE MACHINE, PROPAGATE, sync direction and conflict clauses, per-column IMMUTABLE, ACL REFERENCES, vector quantization, RANK_POLICY — and its printed DDL re-parses to a table with the same policy, so .schema output remains a valid way to snapshot or replay a definition.

Four things the printed DDL does not reproduce literally, and what to read instead:

  • Column DEFAULT clauses are omitted from the rendered DDL. A table declared status TEXT NOT NULL DEFAULT 'active' prints back as status TEXT NOT NULL. The default is still declared and still applied on INSERT; it is the printed text that drops it, so replaying that text alone rebuilds the table without its defaults. Read the default from --json instead — each entry of schema.columns carries it verbatim in default ("'active'", "NOW()", null when there is none).
  • STATE MACHINE from-states print in sorted order, not the declared one. STATE MACHINE (status: draft -> [active, rejected], active -> [superseded]) prints back as STATE MACHINE (status: active -> [superseded], draft -> [active, rejected]) — the transitions are held as a map keyed by from-state, so the keys come back sorted while each state’s target list keeps the order you declared. Both surfaces share that map, so --json schema.state_machine.transitions orders its keys the same way. The graph is identical; compare it as a set of edges rather than diffing the text against what you typed.
  • The CONTEXT_ID column marker is omitted from the rendered DDL. A table declared context_id UUID CONTEXT_ID prints back as context_id UUID. This marker is not decoration: it is how the row gate finds the column holding each row’s context, so a read session that declared a narrowed set of contexts filters by it. Replay the printed text alone and the rebuilt table has no CONTEXT_ID column at all — every row is served to every reader, narrowed or not. There is currently no other surface (printed DDL or --json) that names which column carries this marker; treat a .schema snapshot of such a table as incomplete for replay and keep the original CREATE TABLE text instead.
  • The SCOPE_LABEL column marker is omitted from the rendered DDL. Same mechanism, same consequence: a table declared with a SCOPE_LABEL column prints back without the clause, the row gate has no column to read a scope from on the replayed table, and a read session that declared a narrowed set of scopes gets every row instead of only its own. As with CONTEXT_ID, neither the printed DDL nor --json names this column today, so replaying .schema output alone silently drops the narrowing.

.verify history — check a table’s historical indexes

A historical index answers a read at an older snapshot from its postings alone, so its postings must cover exactly what the row versions cover. Every write commits to both, and a read at an older snapshot already refuses a commitment that does not authenticate. .verify history <table> runs the full check: it recomputes both sides from every stored posting and row version, checks that no row’s postings overlap or touch and no row’s versions overlap, and requires the result to match the published commitment. It works in a read session, visits every posting and row version once, and refuses with StoreCorrupted (exit code 1) naming the first disagreement.

db="$(mktemp -d)/history.db"
printf "%s\n" \
  "CREATE TABLE tagged (id INTEGER PRIMARY KEY, tag TEXT) HISTORY ALL;" \
  "CREATE INDEX tagged_by_tag ON tagged (tag);" \
  "INSERT INTO tagged VALUES (1, 'a');" \
  "UPDATE tagged SET tag = 'b' WHERE id = 1;" | contextdb "$db" --write --json > /dev/null
echo ".verify history tagged" | contextdb "$db" --json
# {"verify_history":{"table":"tagged","indexes":[…,{"index":"tagged_by_tag","postings":2,"versions":2}]}}

Vector inspection and recovery

Use SQL SHOW VECTOR_INDEXES to inspect a vector column as a whole. It keeps the familiar table, column, dimension, quantization, vector_count, and bytes fields, and adds the declared partition key and effective limit, live and snapshot-retained layout counts, search mode, base range, pending inserts, tombstones, durable and charged vector/index bytes, query and maintenance state, work progress, unavailable/stalled counts, and broad-route state. bytes remains the compatibility total of charged vector bytes plus charged index bytes; do not add those three figures together.

Use the detail view when one layout needs attention:

SHOW VECTOR_PARTITIONS;
SHOW VECTOR_PARTITIONS LIMIT 50;
SHOW VECTOR_PARTITIONS FOR vector_items.embedding;
SHOW VECTOR_PARTITIONS FOR vector_items.embedding LIMIT 50 OFFSET 100;

The grammar is SHOW VECTOR_PARTITIONS [FOR <table>.<column>] [LIMIT <count> [OFFSET <start>]]. LIMIT works with or without FOR, and omitting OFFSET means zero. The unpaged form is an ordinary result: if it crosses the active result-row or result-byte ceiling, ContextDB refuses the complete result instead of truncating it. Page that same stable result with the ordinary cursor:

db="$(mktemp -d)/vector-inspection.db"
contextdb "$db" --write --json --read-cursor-page-rows 1 <<'SQL'
CREATE TABLE vector_items (
  id INTEGER PRIMARY KEY,
  scope INTEGER NOT NULL,
  embedding VECTOR(3) PARTITION_KEY (scope)
);
INSERT INTO vector_items VALUES (1, 10, '[1,0,0]'), (2, 20, '[0,1,0]');
.maintenance run
.cursor open SHOW VECTOR_PARTITIONS FOR vector_items.embedding
.cursor fetch
SQL

This release ships exact vector search only. AUTO and EXACT compare allowed stored vectors within active budgets. INDEXED returns VectorIndexedRouteUnavailable; the clustered index is switched off. Index policy declarations do not enable construction, maintenance or indexed serving.

Use SHOW VECTOR_INDEXES and SHOW VECTOR_PARTITIONS for the declared columns and local partition inventory. They do not activate an index.

Vector column policy

Use SQL to retain a vector column’s AUTO_INDEX_AT, SEARCH, IVF, and CONSOLIDATION declarations; these do not activate an index in this release. See Vector Similarity Search for syntax.

Sync Commands (write sessions)

All sync operation requires a --write session with --tenant-id. Without --tenant-id, .sync status and .sync auto answer Sync not configured and exit 0; the action subcommands fail (exit 1) so a scripted push && shutdown never reads “not configured” as “pushed”. Sync direction (SYNC PUSH ONLY / PULL ONLY / TWO WAY / OFF) and conflict policy (KEEP FIRST / KEEP LATEST) are declared in DDL. Auto-sync (.sync auto on) debounces background pushes (--sync-debounce-ms); on exit the CLI performs a final push regardless.


Owner Inspection (.owner status)

Every file-backed writer — the --write CLI, contextdb-server, an embedding application — serves bounded same-machine reads by default over an authenticated local channel that never accepts a write. That channel is what a plain contextdb <path> session uses when a live owner holds the store. It is an administrative plane for inspection, not an application database pool: an application serves its own users in-process through the read kernel, and heavy remote read serving belongs to a sync-following replica.

.owner status describes the file-backed process owner — never sync health. It resolves no row-reading route and stays usable when every reader slot is occupied:

SituationStateExit
No ownernot_running0
Owner servingserving0
Deliberately disabled (--no-owner-reads)serving_disabled + reason0
Expected to serve but cannotnot_serving + reason1
Windowsnot_applicable + reason platform_unsupported0

Windows has no local owner channel: a store held by a live writer cannot be inspected there — close the writer and rerun. Direct file reading of an idle store works everywhere.

The serving JSON reports every limit and timeout as {"value":N,"source":"default|override"}, plus concurrency, active_readers, and database_memory (available_bytes is null when database-wide memory is unbounded).

An owner serves at most concurrency (default 4) simultaneous readers; the next reader is refused immediately with owner_at_capacity — retry, close another inspecting session, or raise the writer’s --owner-read-concurrency. No queue forms. An open owner cursor holds one slot for its lifetime; direct readers have no owner slot ceiling.

The channel lives in the per-user runtime directory ($XDG_RUNTIME_DIR/contextdb, with /run/user/<uid> as the systemd fallback). Headless Linux and containers without either use an automatically prepared private directory beneath the effective user’s data home; no runtime-folder setup is required. --owner-read-runtime-dir is an optional override. That one directory serves BOTH sides: give the same --owner-read-runtime-dir (or CONTEXTDB_OWNER_READ_RUNTIME_DIR) to a reading session and it looks for the channel exactly where the writer put it.

Owner-channel startup failure never fails the database open: the writer keeps running and reports not_serving with one startup warning. Two distinct names for the two sides of that situation: not_serving is the serving STATE on the owner’s own status surface; owner_not_serving is the refusal KIND an inspecting session receives when it tries to reach that owner — scripts branch on each in its own place.

Reader breadcrumbs, retained-snapshot registrations, and erasure fences use the platform per-user directory when it is available; headless Linux also discovers the private data-home fallback. An explicitly supplied directory participates in discovery too. A participant also inventories the supplied directory when configured, so a reader and writer using different channel directories still coordinate over the same physical database through the platform directory when it is available. Purge publication reaches every known candidate directory. An empty candidate directory alone never proves that there are no readers. If a candidate directory cannot be authenticated, reclamation or purge refuses with a typed reader-coordination error naming that directory; channel startup failure alone leaves the database open and reports not_serving. If neither a platform runtime nor a secure data-home fallback is available, the reader and writer may supply different channel directories. A direct reader keeps a byte-free advisory presence lock on the database file after its initial load lock releases. If the writer sees that lock but cannot find the reader’s registration in any runtime directory it knows, ordinary opens and commits remain available with a conservative retention floor. Explicit storage reclamation and authoritative purge refuse with ReaderCoordinationUnavailable, naming the missing registration; they resume after the unknown reader exits. This preserves the channel-directory option without requiring a shared directory. The container must expose the same physical database file and support Linux open-file-description advisory locks for this journey. On other Unix systems without a common platform runtime root, ordinary writes retain a conservative floor and reclamation or purge refuses because reader inventory completeness cannot be proved.


Server (contextdb-server)

Coordinates sync between edge clients, and (as a file-backed writer) serves the same owner inspection channel as the writable CLI.

contextdb-server --tenant-id <TENANT_ID> [OPTIONS]
FlagDefaultDescription
--db-path <PATH>(required)Database file path (env bootstrap alias: CONTEXTDB_DB_PATH, flag wins). The server refuses to start without one.
--sync-endpoint <SPEC>(auto)iroh:?identity=<key-file>[&port=<u16>][&relay=…][&relay-ca=…][&publish=…][&lookup=…] — transport identity and routing only.
--response-staging-bytes <N>unlimitedDurable unfinished-response storage bound.
--pre-admission-connections <N>128Incoming connection/handshake task bound.
--pre-admission-bytes <N>64 MiBAggregate request payload reserved before route admission.
--request-read-idle-ms <MS>30,000Closes a request making no byte progress.
--owner-read-* / --no-owner-reads / --owner-read-runtime-dirshipped defaultsOwner-service policy, disablement, runtime directory (CONTEXTDB_OWNER_READ_RUNTIME_DIR bootstrap alias, flag wins).
--ticket-file <PATH>(none)Write the enrollment ticket to a file once bound. Sensitive bearer material.
--show-ticketoffPrint the bare ticket and exit.
--jsonoffOne JSON object with enrollment_ticket and dial_command.
--tenant-id <ID>(required)Tenant identifier.

The four resource-policy controls are top-level flags; the endpoint string carries only transport identity and routing (identity, port, relay, publish, lookup).

In the default build a hub is reached by the direct addresses carried in its enrollment ticket. A DHCP reassignment of the hub’s IP breaks dialing until the ticket is re-issued and re-pasted. The sticky port recorded beside the identity key only survives restarts on the same address; LAN self-healing across IP changes needs a build with the mdns cargo feature (lookup=mdns).

Everything else about the server — the enrollment ticket contract and its sensitivity, relay and address-lookup configuration, restart semantics and port stickiness, files on disk (*.fabric-identity.key, .port, <db-stem>.db.lock — the companion lock appends .lock to the full file name), exit codes — is unchanged from the shipped behavior.


Non-Interactive Mode

When stdin is not a terminal, the CLI runs in pipe mode — and a piped invocation is a full session: the same dot-commands, the same single session cursor, the same one route notice, so every instruction the CLI prints (including the cursor advice inside a refusal) is executable by a non-interactive agent.

  • No prompt, no banner
  • SQL statements may span lines and end at the first ;; meta-commands stay single-line
  • Results go to stdout; every error, notice, and diagnostic goes to stderr
  • An INSERT is echoed to stdout — its own statement text, on its own line, immediately before its ok (rows_affected=N) — so a scripted load reads as a transcript of what it wrote. Only INSERT echoes, and only here: --json suppresses it to keep stdout a clean machine channel, and an interactive session never echoes because the terminal already shows what you typed
  • The session continues past a refused statement so one run reports all of its errors, and the process exits 1 if any statement suffered a runtime refusal
echo "SELECT 1 + 1;" | contextdb ./my.db --write
contextdb ./my.db --write < schema.sql
echo "SELECT * FROM t;" | contextdb ./my.db && echo "OK" || echo "FAILED"

Exit Codes

Every binary reports one of four codes:

CodeMeaningRaised by
0Success. Every statement the run attempted succeeded.A clean run, and any interactive session — it showed you each error as it happened, so the code reports the session rather than the statements inside it, exactly as psql and sqlite3 do.
1Error. The invocation was valid; something in the run failed.Every runtime refusal on this surface, any SQL or engine error, a failed meta-command, a definitive sync failure, a store that could not be opened or closed.
2Usage error. The invocation itself was wrong and nothing was attempted.An unknown flag or missing argument, an unparseable flag value such as --memory-limit 12Q, an invalid limit relationship, or an incomplete combination such as --tenant-id with no --sync-endpoint.
3A .sync push was interrupted after sending and its outcome is unconfirmed — the hub never said whether it landed, so re-pushing is the safe move.An interrupted push, including one that carries schema changes and the automatic final push on exit.

Precedence: a definitive error (1) dominates an unconfirmed push (3), which dominates success (0). A usage error (2) is terminal before anything runs, so it never competes. A non-interactive session exits 1 when any statement was refused and 0 only when everything succeeded; an unconfirmed push still reports 3 even from an interactive session, because nobody can act on it once the process is gone.

A script continues after a refused statement, including a memory or disk refusal. Independent statements still run. SHOW MEMORY_LIMIT / SET MEMORY_LIMIT and SHOW DISK_LIMIT / SET DISK_LIMIT accept optional = and remain available to inspect and raise an exhausted limit. SET READ_CACHE_SHARE [=] <percent> | DEFAULT and SHOW READ_CACHE_SHARE also remain available to adjust or inspect this session’s file cache without a writable store. Successful recovery does not erase the earlier error: that scripted run still exits 1.

Error Classification

Finer classification lives on the output: each error is one stderr document whose stable class and detail.kind are what scripts branch on. The read surface’s stable kinds, each teaching its recovery:

detail.kindClassMeaning and next action
write_requires_flagsqlA read session got a mutating statement/command; add --write.
store_not_foundioThe store does not exist; --write creates it.
held_by_writerioA writer owns the store; a read session (drop --write) reaches its channel. The refusal names the store in store_path and, when the holder has published a record about itself, the holding process in process_id.
held_by_readersioThe caller’s expired writer-open deadline ended admission while starting readers held the snapshot-selection window; includes the real observed reader count and identifiable names.
owner_not_runningioNo writer is running for this store right now.
owner_not_servingioA writer holds the store but has no usable inspection channel — including a writer that has claimed the store and not published its serving decision inside the caller’s declared deadlines, or a draining owner that released the requested cursor/page before delivery.
owner_user_mismatchioThe owner belongs to a different operating-system user.
owner_mismatchioThe responder does not own this database/run.
owner_at_capacityioEvery reader slot is occupied; retry, close a session, or raise --owner-read-concurrency.
owner_limit_exceededioA read ceiling was crossed; carries limit, the ceiling in value, the refused statement, and the route-aware remedy.
owner_timeoutioA connected owner missed a configured deadline.
owner_disconnectedioThe in-flight statement or cursor aborts. The next command on the same session reads the committed file image or follows a returning writer.
invalid_channel_dataioA malformed or oversized local frame.
local_protocol_mismatchioCaller and owner use different local protocol versions.
invalid_continuationusageA continuation was malformed or given to a command that did not issue it.
cursor_already_opensqlThe session’s cursor is still open; close it first.
cursor_transaction_activesql.cursor open during an active write transaction.
cursor_invalid_statementusage.cursor open received something other than one SELECT or SHOW VECTOR_PARTITIONS statement.
cursor_expiredioIdle or lifetime crossed (detail names which); reopen with .cursor open.
cursor_not_foundioOn either route, the cursor never existed, was explicitly closed, or is another session’s (an exhausted cursor answers an empty success page instead). On the live-owner route only, a cursor/page drained during owner shutdown is owner_not_serving.
direct_read_requires_writerioSafe decode found state needing a corrective writable open; close holders, rerun with --write.
operation_already_completedioThe local-channel operation had already produced its outcome before this poll reached it — an internal race guard, not a condition a caller triggers by any documented action.
owner_route_unsupportedioThe requested inspection kind is not implemented over the live owner’s channel (today: image-state metadata); the refusal names the kind and says a direct file session answers it once no writer holds the store.

Custody metadata as JSON Lines

contextdb --json database.db accepts .sync policy, .sync bindings, and .sync outcomes FOR events in an ordinary read session. Each answer uses the existing {"result":{"columns":[...],"rows":[{...}]}} JSON Lines envelope. Policies expose effective clauses, version and digest; bindings add authenticated tenant and peer identities; outcomes expose root reference, unit digest, verdict, cause, identities, acceptance position, and conflicts. These reads need no --tenant-id, do not connect to a hub, and change no journal or watermark. A Context-, scope-label-, or principal-constrained handle receives the typed inspection refusal.

Use --write for DECLARE TENANT TABLE POLICY, DISCARD, or PURGE. The policy and erasure SQL contracts, including node-local predicate delivery and transaction-local discard, are in the query language reference.

Architecture

contextdb has ten Rust workspace members plus the maintained contextdb-redb dependency fork outside the workspace. This document covers the crate structure, subsystem design, key traits, and extension points.


Crate Map

contextdb-core          Types, executor traits, errors, Value enum, TableMeta
    │
contextdb-tx            MVCC transaction manager, WriteSet, WriteSetApplicator trait
    │
    ├── contextdb-relational    Row storage, scan, insert, upsert, delete
    ├── contextdb-graph         Adjacency index, bounded BFS, DAG enforcement
    └── contextdb-vector        Partitioned exact vector search
            │
contextdb-parser        pest grammar → AST (SQL + GRAPH_TABLE + vector extensions)
    │
contextdb-planner       AST → PhysicalPlan (rule-based, no cost optimizer)
    │
contextdb-engine        Database struct — wires all subsystems, plugin API, subscriptions
    │
    ├── contextdb-server    SyncServer + SyncClient (dial-by-key transport, conflict resolution)
    └── contextdb-cli       Interactive REPL binary

contextdb-redb          Maintained storage dependency of the engine; outside the workspace

Modules that carry the bounded writer working set (details in each crate’s AGENTS.md):

CrateModuleWhat it does
contextdb-redbtree_store/page_store/allocator_pager.rsPages allocator bitmap words through 32 fixed windows with same-directory scratch spill
contextdb-vectorivf/, store/membership.rsClustered index pages read and written a page at a time; durable raw-membership sources with no retained directory
contextdb-relationalsource.rsRoot-pinned row and index reads with staged-row merge
contextdb-graphsource.rsRoot-pinned adjacency reads with an edge overlay
contextdb-txcommit_directory.rs, ordered_staged.rsDurable commit-history seam; ordered staged rows
contextdb-enginepersistence/writer_*.rs, database/writer_read.rsPinned-root writer reads, durable history cursors, open bounds, statement read scope

A writable file store keeps a bounded working set however long its history is: it opens without reading row versions, change log or commit index, serves reads from roots pinned at publication, and releases pages, indexes and history after use; readers keep the view they opened with.

Dependencies flow downward. contextdb-engine owns the Database struct and is the crate applications depend on.

The engine depends on the maintained contextdb-redb package (library name redb) at crates/contextdb-redb, based on upstream redb 4.1.0 (crates.io checksum 8e925444704b5f17d32bf42f5b6e2df050bceebc3dcd6e71cc73dafe8092e839, upstream revision 6ed1f981ba4deab0b2adbdd7bccb46ec409b2191). It retains the upstream MIT OR Apache-2.0 licence. The crate remains outside the workspace so its complete upstream integration suite runs from its own locked manifest in CI. Its provenance, fork delta, upgrade procedure, and upstream contribution path are maintained in crates/contextdb-redb/MAINTENANCE.md. Distribution requires publishing this package before the engine, formatting/linting/testing its standalone manifest, and building the unpacked engine against the unpacked fork with scripts/verify-packaged-engine.sh. An upstream proposal remains part of maintenance; no upstream acceptance is claimed.

The fork’s contextdb-internal byte-table seam provides bounded lookup, prefix inspection and a root-pinned opaque cursor. Reject-only windows can exclude an unrelated oversized singleton without materializing its full extent. Possible matches use ordinary complete-page admission and decoding; retained roots keep complete checksum verification. Complete nodes reached by lookup, prefix iteration or cursor navigation validate their layouts before offset access, so malformed leaves and branches return typed corruption without panic. Branch cycles on all three paths return typed corruption without hanging. The engine owns inventory policy above this seam.


Subsystem Design

Relational (contextdb-relational)

The canonical source of truth. All rows live here. Graph and vector indexes are secondary structures derived from relational data.

  • In-memory row store with column-typed Value enum
  • Point lookups by primary key, range scans with filter predicates
  • Upsert via INSERT ... ON CONFLICT DO UPDATE
  • DDL metadata stored alongside rows (columns, types, constraints)

Graph (contextdb-graph)

Dedicated adjacency index maintained incrementally as edges are inserted/deleted. Not recursive SQL over edge tables.

  • Bounded BFS with configurable max depth (engine limit: 10)
  • Edge-type filtering per hop
  • Direction control (outgoing, incoming, bidirectional)
  • DAG cycle detection on insert (BFS from target back to source)
  • Edge identity follows its table’s declared key. Without a wider declared key, duplicate (source_id, target_id, edge_type) writes name one logical edge.

One logical edge can be witnessed through both the graph insertion API and its relational row. Readers see one binding when the table uses the triple or has no edge key; rows distinguished by a wider declared key remain distinct bindings. The graph-door record keeps ownership of its properties, including later updates. The row-owned witness keeps its exact row and version coordinates for history and purge authentication. Visibility and authorization apply to each witness before the shared traversal merge coalesces their alias. On a wider-key table, the standalone triple aliases only the row whose extra identity columns equal their literal or NULL defaults. Other key values remain distinct; without a matching row, the standalone remains its own binding. A volatile default such as NOW() cannot name one row, so its standalone binding stays distinct.

Vector (contextdb-vector)

Secondary index over relational rows with VECTOR(n) columns. Index identity is the full (table, column) pair, so one table can carry separate text, image, audio, or policy embeddings with different dimensions and quantization choices.

  • Cosine similarity via <=> operator
  • VECTOR(N) WITH (quantization = 'F32'|'SQ8'|'SQ4') per column
  • SQ8/SQ4 columns keep quantized live payloads and quantized index payloads; f32 is reconstructed only at API/materialization boundaries
  • A two-level local registry: (table, column) identifies a vector index and a typed declared partition key identifies one local search layout. The unpartitioned column has one empty key. This is a search layout only, never an authorization boundary, tenant, or sync direction.
  • This release ships exact vector search only. AUTO and EXACT compare allowed stored vectors within the active budgets; INDEXED returns VectorIndexedRouteUnavailable. The clustered index is switched off: commits and maintenance do not build or write it, and queries and inspection do not read it. Index policy declarations are retained but do not enable an index.

Unified Transactions (MVCC)

contextdb-tx provides MVCC with consistent read snapshots:

  • Each read sees a consistent snapshot across relational, graph, and vector state
  • Writers don’t block readers; readers don’t block writers
  • Writes are serialized through a commit mutex (one writer at a time)
  • WriteSet accumulates all mutations within a transaction
  • On commit, the WriteSet is applied atomically to all subsystems
  • Propagation (state machine transitions cascading along edges/FKs) happens within the same WriteSet

Store Ownership & Concurrency

Writing a database file is owned by exactly one process at a time. A second writable open of the same path — whether from another thread in the same process or from a separate process — returns Error::DatabaseLocked. This is enforced at two layers: an in-process open registry and an on-disk PID lock backing an OS file lock. Single-writer ownership is a deliberate guarantee of the substrate, not a missing feature.

Reading is a separate door that does not take the write lock. A read session (ReadSession in Rust, contextdb <path> without --write on the CLI) resolves one of two routes when it opens. Ordinary reads keep that route for the session:

  • Owner route — a live process owns the store, so the reader is served that owner’s committed state over an authenticated local channel. The owner’s declared ceilings and deadlines govern what it may ask for.
  • File route — nobody owns the store, so the reader reads the committed snapshot from the file directly and leaves every byte of the store folder unchanged. Several direct readers coexist on one store.

The two directions see each other. A writable open of a store a writer already owns is refused with held_by_writer. A starting direct reader holds writers out only while it selects and registers its committed snapshot, at fixed cost independent of store size. It releases that admission hold before loading the image; its session and cursors retain their registered snapshot afterward.

A writable open waits exactly once for the operating-system store lock. There is no engine timeout and no application reopen/retry loop. Only actual caller cancellation or the caller’s deadline can end that wait early. An already-expired caller deadline returns the immediate typed held_by_readers refusal while readers hold admission, including the real observed count and identifiable reader names. A direct read that finds committed state a writable open would have to mend refuses with direct_read_requires_writer instead of serving something the file cannot back.

The supplied runtime directory names the owner channel; a channel startup failure leaves the database open with a not_serving status. Reader coordination uses the platform per-user directory when available, with an automatically prepared private data-home fallback on headless Linux; an explicit supplied directory also participates in discovery. Inventories inspect every known candidate directory, and an empty inventory does not prove that no reader exists. A writer publishes purge fences to each candidate. If one cannot be authenticated, the affected purge or reclamation refuses with a typed error naming that directory while database open remains available. On Linux each reader also holds byte-free database-file advisory presence locks. An unmatched lock retains potentially old pages during commits and refuses explicit purge or reclamation with ReaderCoordinationUnavailable, covering readers with different supplied roots when no platform root exists. The filesystem must support Linux OFD locks. Other Unix systems without a common platform root retain a conservative floor and refuse these operations when the inventory cannot be proved complete.

Holder presence. A process that owns background work beside a store, and keeps working while its Database handle is closed, proves it is alive with persistence::holder_presence::hold: an exclusive Linux OFD lock on one byte of the store’s empty <store>.holders companion, chosen from the work’s 16-byte token. The lock lasts as long as the returned guard, independent of any Database handle, and the kernel releases it when the holder exits for any reason, a kill or a crash included; a stopped or suspended process keeps it. probe asks whether a token’s holder is alive without changing a byte or creating the companion, and wait_released blocks in the kernel until the holder releases or exits, with no polling and no timeout. A filesystem that cannot take the lock refuses with HolderPresenceUnavailable before anything is written, and so does any platform without OFD locks. Holder locks never touch the data file or the .lock companion, so they neither hide nor impersonate a reader or the writer. Presence reports liveness only: a consumer that publishes work under a claim still fences that publication on its own token inside the publishing transaction, because a holder can lose its lock without exiting.

Once a file reader has captured its committed image, it publishes an authenticated registration for that physical store incarnation and exact storage root for the lifetime of the shared session target. A later writer validates the pre-writer registrations before it can allocate or reclaim pages. Redb authenticates each saved root page and pins its transaction as a floor for durable deferred frees. Cursor admission keeps only fixed-size root metadata and does not enumerate the database under the commit lock. While a root is held, this conservative floor can also defer unrelated freed pages. They remain in the store’s existing disk allocation and disk limit; the on-demand root-page diagnostic counts only the pages reachable from registered roots. The last close or authenticated process death releases the snapshot floor; subsequent durable writer commits reclaim eligible extents incrementally. Each such commit examines at most 64 deferred-free list entries and reclaims at most 64 allocated extents in total across redb’s data and system free trees. An extent is one contiguous allocation and can span multiple physical pages, so this is not a 64-physical-page or byte bound. The separate append-time and memory acceptance gates still apply. The remaining list position is durable, so an ordinary abort before commit-only reclamation leaves pending work intact and later commits continue after restart. Reclamation runs only inside commit, after the ordinary abort path is disabled; aborting after that private reclaim stage is not a supported transaction operation. A released cursor does not make the next small append drain an arbitrarily large backlog. Explicit close drains eligible deferred frees through bounded commits before publishing the read-only handoff. A live reader or savepoint leaves protected work for a later writer. After handoff publication, no storage write is permitted. Cleanup or handoff failure is reported from explicit close after owner resources are released; an owner-read timeout can defer that failure until a later close finishes the drain. Destructor cleanup does not promise a drain. This is allocator cleanup behind the reader fence, distinct from retention’s selection and removal of logical row versions below. Handles and cursors sharing a direct-file session target share one root hold. The registration does not hold writer admission for the session.

Before asking an owner to open a cursor, the client registers a provisional hold on the exact physical file. After receiving the first page it publishes the exact saved root before releasing that provisional hold. This overlap protects the original snapshot even when the owner closes and a replacement writer commits between response receipt and exact registration; registration does not require the old writer to remain present. The exact hold remains with the cursor when the owner exits. If the owner channel is withdrawn, the cursor continues through the file reader at that registered snapshot: it reexecutes its statement, skips the rows already delivered, and returns the remaining pages under the original cursor limits. A new writer may commit in between without changing the cursor’s answer or forcing the caller to retry. Authoritative erasure returns its typed refusal and immediately releases the client’s cursor registration and retained resources, even while the failed cursor object remains alive. A complete first page or final fetch releases the cursor’s root even when the caller keeps its cursor object. A deadline watcher uses the cursor’s declared clock to release an idle or lifetime-expired root without another fetch; a later fetch keeps the typed expiry. Direct-file cursors release their own execution resources at the same terminal pages, while a shared direct-file session root remains held for that session’s lifetime. This continuation is internal to an already-open cursor; the session’s selected route and ordinary request behavior remain fixed. Replay spends the caller’s per-fetch work and active-time budget. If catching up uses that budget, the cursor keeps its position and may return an empty page with has_more before continuing on the next fetch. Admission reads one root page regardless of unrelated store size; explicit diagnostics may inspect all root pages. Replay work grows with rows already delivered.

A file reader opens from compact indexes the writer keeps beside the data, not from a copy of the whole store. At open it reads one small membership record per stored vector, checked against a commitment the writer publishes in the same transaction, and it compares that complete membership and visibility copy with a set commitment carried in the committed vector family roots — the same identity that authenticates vector bodies — before the copy controls search selection. It also compares the vector table’s physical root fingerprint with the committed one without reading any vector body. It copies only the handful of settings a reader needs to start and compares them with an ordered digest carried in the committed Configuration root. The bytes an open reads for vectors and settings therefore do not grow with vector dimension or with unrelated configuration values; the vector part grows only with the number of vectors, one small record each. Rows, vector bodies and every other setting are read and authenticated from the reader’s pinned root only when a query selects them. A tampered or incomplete index or startup copy is refused at open as corruption. A membership copy whose deletion markers hide a still- committed vector is refused at open even when its local checksums agree: the committed vector roots carry an order-independent lattice-hash commitment to the complete membership set, which the writer keeps current by folding in only the memberships each commit changes, so appending to a large store does not rehash its memberships. The copied custody index used for read-only policy and delivery inspection authenticates each copied record and compares the complete copy with the same kind of commitment in the committed Configuration root, also maintained per changed record, so a shortened copy is refused as damage even when its local checksums agree. That check runs when inspection reads the index, not at open, and it does not read unrelated configuration values. A damaged vector key is refused at open; damage confined to a vector’s stored value is refused when a query selects that vector.

When the owner closes gracefully, readers are handed over rather than cut off. The owner first stops accepting writes, makes the file’s committed state clean for direct readers, and publishes that the store is quiescent while it still holds the store; only then does it withdraw its read channel. Owner reads it had already accepted run to completion, or to their own declared limits, before it releases the store. A reader that opens during the handoff takes the file route and sees the committed rows. When several callers close the same database at once, one of them performs the shutdown and the others return only after it has finished, so a writable reopen after close() returns never races a half-finished shutdown.

ReadSession::charged_bytes reports the memory the session is currently charged for. On the file route that is the session’s own charge; on the owner route it is the owner’s shared usage, which can include other work. Closing a cursor releases its result memory, but a storage or proof page still held by the session or another reader stays charged until its last holder releases it. Evicting the cache entry alone does not release that charge.

The file reader’s page-cache policy is declared per session through ReadSessionOptions::read_cache_share, an optional integer percent from 0 to 100. The declaration persists for the session and travels to each direct-file open. The policy default is default (14%): exactly (B - F) / 7, saturating at zero, where B is the declared session memory ceiling, or limits.memory when no session ceiling is declared, and F is the 64 KiB source overhead plus the verifier, LSN and history-open control reservations. An explicit percent applies to B - F. There is no fixed byte cap. ReadSession::read_cache_share_status() returns Result<ReadCacheShareStatus> and resolves writer turnover before inspecting the declared percent, default percent, budget, fixed reservation, cache capacity and effective percent of the remaining budget after fixed source reservations. Route-selection failures remain typed errors; inspection never silently substitutes declaration-only status for a failed route. The options-only ReadSessionOptions::read_cache_share_status() remains infallible and does not open or select a route. The file reader’s admitted read root now follows the session’s declared memory_limit, falling back to limits.memory when no session limit is declared. For example, memory_limit = 1 GiB with the shipped 16 MiB limits.memory gives the file reader a 1 GiB root for loaded state and cache; previously that root was bounded by 16 MiB. Each statement remains bounded by limits.memory.

ReadSession::set_read_cache_share resolves writer turnover before accessing the selected cache, propagating typed route failures. It changes capacity immediately under the cache insertion lock, without reopening or changing its snapshot. Growth is admitted before publication; shrink retires entries while cursor-held backing keeps its invoice until final physical release. Zero disables file page caching; 100 selects the whole remainder and is still subject to total admission. A 100% declaration is valid, but a file open or resize then refuses it when the reservation consumes all B and leaves no room for the prepaid index and decoded state. None restores the derived default. A refused change keeps the previous declaration and capacity.

On the owner route, inspection reports the session’s formula values for its file cache policy, with prepaid_index_bytes and held_page_bytes both None; it does not report or resize the writer’s shared cache. The owner route can retain a 100% declaration without allocating a file cache; a later file open must still pass total admission. Newly selected file readers resolve the current declaration when they open. An owner cursor retains the policy it opened under for its first continuation page; a current shrink applies immediately, while growth waits for that reader’s first real page measurement. Later fetches also resolve the current declaration, using that reader’s admitted memory budget. It resizes the existing retained source, preserving the exact registered root and delivered-row position. Cache growth leaves room for the running cursor’s measured page work: production kernel memory telemetry, actual row credits, encoded buffer/control credits and the encoder diagnostic Layout supply the measurement. The maximum observed page need is retained in the cursor’s existing memory-control object. The encoder’s original payer admits a temporary stack credit for that room while real cache admission finds the largest byte capacity that still fits. No new backing or parallel budget is created. If the continuation cannot follow a declaration fully, it keeps that bounded capacity and continues fetching; the session’s declared share remains unchanged. last_refusal names the cursor and the capacity it kept. Each fetch rechecks its room, and shrink applies immediately.

The SQL session surface is SET READ_CACHE_SHARE = <percent> | DEFAULT and SHOW READ_CACHE_SHARE. It uses the same live mutation path as the Rust API and never writes a durable setting. The show row includes the declared and effective values, last refused value, and last_query_resident_percent. Out-of-range cache shares and memory limits above their startup ceiling use the common Error::InvalidSetting { name, value, allowed } vocabulary. Admission refusals keep their memory-budget fields and leave the prior declaration intact.

Successful file data statements publish residency from a bounded distinct-page window, including ordinary, byte-window and native cache hits. Non-data inspections and failed statements preserve the previous observation. Empty, zero-share and superseded windows report Null; Null means no pages were measured. Owner routes also have no session-owned file cache to measure.

Identifiers and their intrusive list links live in the existing prepaid native index arena; its original exact Layout invoice covers the metadata through physical free. A statement captures its arena from the live read index and retains that existing control only until its final probe; the file keeps no stale alias after writer startup replaces the index. Recording uses atomic controls, without the free-slot mutex or cache stripe locks. The hash table stays at most half full; the sample is also bounded by selected cache capacity divided by page size. The final residency walk follows recorded links only, never the capacity array, and probes production cache residency without I/O or CLOCK changes. A superseding epoch or an in-flight writer invalidates the measurement instead of inventing zero percent. Window initialization alone serializes its reset.

The native index slots are prepaid for the maximum selectable capacity B - F and reused across changes. Session inspection reports prepaid_index_bytes, even at share zero, and the physical page bytes still held by cursors. The index has its own admitted Layouts; it is separate from the selected page reserve. Cache capacity remains inside the original memory ceiling; opening and query work still refuse before allocation if their total does not fit.

A stored value too large for a read’s memory budget is refused before its source buffer is allocated. Opening a session need not read that value: the refusal belongs to the statement that selects it, on both the file and owner routes, and leaves no new charge behind.

Reader memory refusals name the allocation refused in their typed operation field. Each phase charges the same executing reader.

OperationAllocation refused
decode_ownerThe native control that establishes an operation’s decoder owner, before stored-value decoding begins.
query_decodePayload storage allocated while decoding selected stored values.
materialize_answerStorage allocated to materialize the answer.

Fallible engine reads that return history-sized results use a per-operation reader child and admit retained output as it is built. trigger_audit_history and changes_since_with_arrivals therefore refuse with MemoryBudgetExceeded when their selected history exceeds that child; get_edge_properties uses the same pattern for its returned properties. Configuration-scale metadata reads (table_names, table_meta, retention_sync_peer, list_triggers, and registered_trigger_callbacks) remain infallible and are exempt from per-operation admission. plugin_describe returns host-supplied plugin output, and RowChange/ChangeSet DTO cloning and transformations operate on values already owned by the caller; those allocations belong to the host. trigger_audit_log is a bounded recent ring rather than persisted unbounded history.

This is still the standard embedded-database model — the same shape as SQLite, LMDB, or redb: the application that mutates the data owns the handle for its lifetime, and every write goes through that owner. Two consequences for anyone embedding contextdb:

  • A long-running service answers its own writes and can be read beside. A process that holds the database open serves its own reads in-process; a second command that wants that data opens a read session, which reaches the running owner over its channel. What a second process must never do is start a competing writable opener.
  • Never keep a shadow copy. Working around the lock by mirroring the data into a second file or an in-memory side-store outside the owner is an anti-pattern: it creates a second source of truth, drifts from the real one, and defeats the integrity that single-writer ownership exists to provide. The fix for “I can’t open it from over here” is “read it through a read session,” never “keep my own copy.”

The three read doors, and why the owner-only one never falls back

ReadSession::open (and ReadSession::open_with_options, which carries the caller’s own limits and deadlines) resolves either route: the live owner’s channel when a process owns the store, the committed file when none does.

Some questions only a running owner can answer — what the process is doing, whether it is serving yet, anything asked of the process rather than of the data — and the committed file has no answer to give. ReadSession::open_owner_only is the door for those: it takes the owner route when a process owns the store, and when none does it says plainly that the owner is not running (owner_not_running) instead of falling back to the file. A writer that has claimed the store but has not yet published whether it is serving is never reported absent: the caller waits for that writer’s own answer inside the deadlines it declared, and if the answer does not come in time it is told the store is owned and not serving (owner_not_serving) — never that nobody owns it.

It never opens the store’s file at all, and that is the point of it. Reading the committed image publishes a reader that a writer starting beside it must wait for, so a readiness probe that fell through to the file would stand in the way of the very process it is waiting for; and a file that cannot be read directly would answer with the file’s condition instead of the plain fact that nobody owns the store. A caller that wants the committed rows when there is no owner opens an ordinary session instead. The exported signatures and a runnable four-line example are in docs/getting-started.md.

A refused read arrives as Error::ReadFailure. Its kind() is the stable classification a program branches on and its detail() carries the machine-readable specifics of that classification; the sentence a person reads stays in the error’s Display and its wording is free to change. A store somebody holds for writing refuses with the HeldByWriter kind and a ReadFailureDetail::HeldByWriter detail that names the store in store_path and the holding process in process_id — filled in whenever that writer has published a record about itself, and None when it has not, so a caller can tell “held by process 4213” from “held, by someone who did not say who”. Reading the process id out of the prose is never necessary.


Trigger Concurrency

Triggers are host callbacks declared with CREATE TRIGGER and registered through Database::register_transactional_trigger_callback (or the older register_trigger_callback name). They are not PG-style validation triggers; schema invariants remain engine-enforced through DDL. A trigger callback runs synchronously inside the firing transaction’s commit window, and callback writes use the supplied tx-bound Database handle so relational rows, graph edges, and vectors commit atomically. The callback runs before final validation, so its writes are validated with the rest of the transaction, and a refused commit publishes none of them. External effects belong in a sink registered with Database::register_sink, which the durable queue invokes only after commit; see README.md for the delivery and retry contract.

The callback-active contract is deliberately split by concurrency domain:

  • same-DB trigger contention waits-and-proceeds inside the engine, including public tx-control, SQL write paths, direct write helpers, and internal handles that share the same trigger state
  • unrelated cross-DB writers proceed independently; a parked callback on DB-X does not poison ordinary worker-thread writes on DB-Y
  • callback-thread reentry returns CallbackReentry; retrying inside the callback body is misuse
  • callback tx-bound handles remain isolated to their runner thread
  • cron same-DB contention keeps the immediate typed cron callback-active error
  • a same-DB trigger wait that exceeds the deadlock guard’s timeout returns the typed trigger callback-active error and emits one warning

Waiters do not hold the public-operation read guard while parked. That lets close() acquire the write barrier after the active callback exits; a parked writer then wakes to the ordinary closed-handle error instead of proceeding.

Operator Runbook

Healthy same-DB trigger contention does not emit tracing events. A warning means the bounded deadlock guard fired:

#![allow(unused)]
fn main() {
use tracing_subscriber::{EnvFilter, fmt, prelude::*};

tracing_subscriber::registry()
    .with(EnvFilter::from_default_env())
    .with(fmt::layer().json())
    .init();
}

The warning carries structured fields:

trigger_name=<name> waited_ms=<milliseconds> surface=<begin|commit|rollback|apply_changes|close|execute|execute_in_tx|direct helper>

Interpretation: no warning means normal wait-and-proceed contention; a warning means the callback did not finish within the guard budget. The guard is a fixed 60-second default and is not configurable via environment variable — the environment is not a behavior surface on this stack (see CLI Reference).


Storage: WriteSetApplicator

The boundary between compute and storage:

#![allow(unused)]
fn main() {
pub trait WriteSetApplicator: Send + Sync {
    fn apply(&self, ws: &WriteSet) -> Result<()>;
    fn new_row_id(&self) -> RowId;
}
}

Two implementations:

ImplementationUsed byBehavior
PersistentCompositeStoreDatabase::open(path)Applies to the engine’s working stores + flushes to redb

This trait is the extension point for additional backends if required. The applicator borrows the commit WriteSet; stores clone only the row, edge, or vector data they retain. The engine owns compute state (in-memory stores and index caches). The applicator owns durability.

Persistence (redb)

Single-file storage via redb:

  • Flush-on-commit: every committed WriteSet is written to redb
  • On open: relational rows load with table-local index maintenance. Vector lifecycle metadata opens first; valid base and change graphs and raw-vector pages are loaded or mapped only for the active working set, rather than rebuilding every vector index.
  • Crash-safe: redb provides atomic transactions
  • Committed row bodies: a file-backed store keeps key, index, constraint and policy columns and values up to 1 KiB resident; larger text/JSON values and vectors stay only in redb. Anything that hands a row on — a pushed or pulled sync changeset, a full-state snapshot, a custody manifest digest — reads the paged-out columns back from that row’s exact durable version when the change is built, one row at a time, so the wire unit carries every committed column. A unit that then exceeds one sync frame travels on the oversized staging transport. Under a bounded history answer the version’s stored size is charged to the declared memory limit before it is read.
  • Vector membership commitments are updated from the records changed by the commit. Appending or deleting one vector does not rehash the complete membership directory; the same incremental rule applies to a custody-policy write’s set commitment.
  • Tables: rows, DDL metadata, graph edges, vector entries, counters
  • Vector entries use one composite-key table keyed by (table, column, row_id).
  • A metadata table stores format_version = "1.0.0"; missing markers are treated as incompatible stores, while unreadable markers are reported as corrupt.

Vector generations, restart, and repair

This release ships exact vector search only. AUTO and EXACT compare allowed stored vectors within active budgets. INDEXED returns VectorIndexedRouteUnavailable; the clustered index is switched off. Index policy declarations do not enable construction, maintenance or indexed serving.


Plugin System

#![allow(unused)]
fn main() {
pub trait DatabasePlugin: Send + Sync {
    fn pre_commit(&self, ws: &WriteSet, source: CommitSource) -> Result<()>;
    fn post_commit(&self, ws: &WriteSet, source: CommitSource);
    fn on_open(&self) -> Result<()>;
    fn on_close(&self) -> Result<()>;
    fn on_ddl(&self, change: &DdlChange) -> Result<()>;
    fn on_query(&self, sql: &str) -> Result<()>;
    fn post_query(&self, sql: &str, duration: Duration, outcome: &QueryOutcome);
    fn health(&self) -> PluginHealth;
    fn describe(&self) -> serde_json::Value;
    fn on_sync_push(&self, changeset: &mut ChangeSet) -> Result<()>;
    fn on_sync_pull(&self, changeset: &mut ChangeSet) -> Result<()>;
}
}

All methods have default no-op implementations. CorePlugin ships as the default and handles engine-internal concerns (subscriptions). Retention pruning and version cleanup are NOT plugin-driven — they are the engine-owned maintenance loop described below.

Inject a custom plugin:

#![allow(unused)]
fn main() {
let plugin = Arc::new(MyPlugin::new());
let db = Database::open_with_plugin(path, plugin)?;
}

Maintenance (retention + version cleanup)

contextdb starts one background maintenance thread per database, under the default engine-owned policy, when the database declares something to maintain: a RETAIN window, HISTORY CURRENT ONLY, a durable trigger with audit history, or other declared maintenance work. It advances a finite batch and does near-zero work when nothing is pending. An embedding host may explicitly choose caller-driven maintenance instead: then ContextDB starts no hidden thread and each existing Database::run_maintenance_cycle call (or CLI .maintenance run) advances one finite batch. Vector index construction is disabled in this release.

Each cycle advances retention, trigger-audit retention, and version cleanup. No vector index is built in this release, so a cycle’s vector report counts no built indexes or partitions, whether the host calls the cycle or the engine-owned loop wakes. The default engine-owned loop polls every 60 seconds; callers can persist a different database interval through SET MAINTENANCE_POLL_INTERVAL, Database::set_maintenance_poll_interval, or the CLI’s millisecond-only --maintenance-poll-ms; omitting the CLI flag uses the database’s persisted interval. A per-column CONSOLIDATION policy is retained as declared (rules and defaults: Vector Similarity Search) and no cycle acts on it, because there is no graph to rebuild. Retention runs before current-only version cleanup, so an expiring row is not first collapsed as history. Caller-driven policy is an open option and CLI mode, starts no background worker, and runs the same cycle when the host calls it.

Because no vector index is built, a maintenance cycle has no partition-local build to refuse or cancel: its vector report counts zero built indexes and partitions and carries no first failure, and neither a commit, an explicit cycle, an engine-owned wake, erasure, retention nor compaction writes index state.

The storage layer’s bounded byte cursor keeps reading siblings after a caller-refused singleton body, whatever its position among them. A navigation or container refusal ends that cursor with no backend read, and a fresh cursor retries once the admission condition has changed.

Version cleanup and a held read snapshot. Every in-flight statement registers its own read snapshot for the call’s duration, and a caller that needs to reuse a SnapshotId ACROSS separate calls (on a table declaring HISTORY CURRENT ONLY) registers it explicitly via Database::pin_snapshot, holding the returned guard for as long as the snapshot is still wanted. Each open store prepays ReadLimits::SHIPPED_SNAPSHOT_PIN_SLOTS (64) pin slots. Further pins charge their exact registration bytes against the store’s declared reader memory and release that charge at unpin; pin_snapshot returns the typed snapshot_pin memory refusal only when that budget cannot fund another pin. Dropping a prepaid pin returns its slot. Pin admission has no fixed count ceiling. A version-cleanup pass samples every currently-registered snapshot (not merely the oldest) plus the committed watermark, atomically, once at the start of the pass, and defers any superseded version still visible to ANY of those registered snapshots to a later cycle — a version created between two registered snapshots and superseded after both is protected by the higher one even though the lower one alone would not see it. Protection begins when pin_snapshot RETURNS: a pin requested while a removal pass is already mid-flight, for a snapshot at or before that pass’s sampled watermark, waits for the pass to finish first (bounded by one pass duration) before registering, so the pin can never return with a false promise of protection the SAME in-flight pass is still free to violate — the next cleanup cycle honors it instead. A pin for a snapshot strictly after the pass’s watermark registers immediately: nothing that pass can prune was ever visible to it. Versions a pass already reclaimed before the pin is requested cannot come back; that boundary is unchanged.

The cost model. Version cleanup examines eligible table histories and the affected vector columns’ partition-directory metadata to select exact version identities; selection does not load vector bodies. One redb transaction then removes the selected row versions and change-log entries, vector bodies, directory memberships, eligible deletion records, and eligible commit-index entries. Memory changes only after that transaction commits. A crash before commit leaves the selected scope intact; a crash after commit leaves it durably removed, so retry does not depend on a deleted discovery row. Registered readers defer versions they still need. Immutable graphs may still name old versions; query merge scores each distinct candidate from its snapshot-visible native vector before ordering and applying the limit, even after cleanup leaves only one directory version.

Retention selects due versions from RETAIN tables at one commit frontier, borrowing source payloads and FK references instead of cloning table histories. The shared memory accountant admits candidate identities and expiry mutations before allocation; reclamation uses the same borrowed selection and preserves complete-lifetime checks without copying unrelated payloads. An empty or not-due retained table reserves no expiry workspace as unrelated history grows. Reclamation admits selected vector directory identities and edge deletion keys, without loading vector bodies or copying edge properties merely to remove them. Reader-deferred edge identities retain their own memory charge between cycles; carry-only retries admit copied identities and durable encoding before allocation, including after restart. Position-map and surviving index-key rebuilds for selected tables, generation catalogs/reader chains and affected-LSN sibling buffers are admitted before durable reclamation commits. Resident vector removal visits selected indexes and updates whole-row reverse maps in place. Selected-row identity repair admits the borrowed durable encoding before decoding bodies omitted from the resident directory, and holds that charge through identity publication.

Retention first commits expiry as a visibility boundary: newly opened reads exclude expired rows, while already-registered snapshots retain their complete row/vector/graph view. Physical reclamation defers versions those readers still need, including vector generations. After the pins are released, a later cycle can reclaim them; expiry alone does not promise an immediate fall in resident bytes. Retention commits its selected row versions, change-log and source-LSN records, vector copies, reclaimable adjacency, reader-deferred adjacency identities, eligible commit-index entries, and reclaimable vector generations in one redb transaction. In-memory removal follows that commit. Cleanup cost includes selection through the eligible histories/directories, membership-prefix checks for selected vectors, and reading and rewriting affected change-log LSN groups. It is not proportional only to the number of removed keys. Surviving rows and unrelated tables remain untouched. Version cleanup never opens graph tables: edge identity is self-owned and is not versioned by a relational row.

File compaction is separate from scoped cleanup. Retention and version cleanup reclaim only the versions they select; redb can reuse those pages, so reclaiming rows does not itself promise a smaller file.

Automatic file compaction has two triggers. A retention pass that actually pruned rows starts a sweep when its pre-prune dead-space sample is at least 50%. At the end of a maintenance cycle, a new sweep starts when dead space is at least 50% and at least one hour has elapsed since the last completed sweep. Both use ContextDB’s vendored redb compact_step path to relocate at most 64 pages in one batch. Relocation uses copy-on-write pages and retains old pages behind redb’s reader fence, so existing and newly opened reads can overlap an active batch. Foreground writes share the single writer lock with each finite relocation or free-page transaction; the database handle lock is released during relocation. Later maintenance cycles resume the cursor until the sweep completes. Completion recycles the handle only when no live storage read transaction owns it; otherwise the existing handle stays open. A restart discards the in-process cursor and a later eligible cycle starts a new sweep. MaintenanceReport.compaction reports the end-of-cycle batch; handle_recycled and file_shrank report observed results, not unconditional recycling or shrink guarantees.

Database::compact_now() (the CLI’s .maintenance compact) is the explicit alternative. It drains the file compaction immediately, without the automatic threshold or interval gate. It is distinct from .maintenance run and from the scoped cleanup reports.

Eligibility is declared, not named. A table is version-cleanup-eligible because it declares HISTORY CURRENT ONLY, never because of its name. The three built-in fabric tables (work_capabilities, peer_directory, work_node_contacts) declare it in their own CREATE TABLE text like any other table would.

Those three tables’ declared policy, plus work_inputs’ RETAIN 7 DAYS, is engine-owned, not operator policy. All four are built-in work-fabric tables (see the work-fabric skill) whose own bookkeeping depends on staying at the shape declared in their own CREATE TABLE text: work_inputs’ retention window is what keeps ledger-carried input copies bounded, and the three currency tables’ HISTORY CURRENT ONLY plus their SYNC CONFLICT KEEP LATEST (or SYNC OFF) is what makes version-cleanup safe to reclaim their superseded rows at all. A locally-typed ALTER TABLE refuses any RETAIN / HISTORY / SYNC CONFLICT / SYNC ... / SYNC SAFE value that mismatches one of these four tables’ own canonical declaration, with a message naming the table as engine-owned infrastructure — including SET SYNC ... (work_node_contacts’ own SYNC OFF declaration is guarded on this axis exactly like the others); silence, or an explicit restate of the table’s already-declared value on that axis, still passes, the same mismatch-only contract the rest of this paragraph describes for the other doors. A locally-typed CREATE TABLE of one of these four names is guarded too: it refuses unless the declared shape structurally matches the owning installer’s own CREATE TABLE text — every column (name, type, nullability, primary key, uniqueness, default, references, EXPIRES, IMMUTABLE) AND every table-level option (a table-level IMMUTABLE, STATE_MACHINE, DAG, or PROPAGATE clause, none of which any of the nine reserved names’ own DDL declares) — and refuses an explicit non-canonical policy clause the same way the ALTER door does — a table outside the nine reserved names remains entirely unrestricted (silence on policy still passes). A locally-typed ALTER TABLE ADD COLUMN / DROP COLUMN / RENAME COLUMN naming one of the nine reserved names is refused outright too, before any other ALTER validation runs — column shape on these nine is exactly as fixed against a column-level ALTER as it is against SET RETAIN / SET HISTORY / SET SYNC CONFLICT / SET SYNC .... Existence is checked first: on a store where the table was never installed, this ALTER reports “table not found,” not the engine-owned refusal — the same existence-before-ownership ordering every other clause in this door already follows, so an absent table and a governed one are never confused. The arriving-sync-DDL mirror of both guards reconstructs the wire columns and table-level constraint text into real CREATE TABLE SQL and runs it through the same parser and the same shape judgment, so a peer cannot smuggle a table-level option (or a per-column attribute) past the wire path that the local path already refuses. The shape guard actually spans nine reserved names, not four: the same four plus the five hub-refereed work-ledger tables (work_jobs, work_claims, work_results, work_failures, work_cancellations), whose real arbitration is a hardcoded keep_first override applied at every sync chokepoint rather than a fully declared policy shape. Those five get a narrower, SYNC-CONFLICT-only counterpart of the policy guard: a CREATE TABLE or ALTER TABLE, locally or over an arriving sync DDL, that declares a SYNC CONFLICT value other than keep_first refuses; silence, or an explicit restate of keep_first, still passes. SHOW SYNC_CONFLICT_POLICY renders every one of these seven work-ledger tables, plus peer_directory (also engine-owned, via its own declared SYNC CONFLICT KEEP LATEST), actually present in the store as {table}={word} (engine-owned), distinct from an operator-declared row. work_node_contacts declares no conflict policy at all, so it never renders in SHOW SYNC_CONFLICT_POLICY, in any form. An arriving sync DDL naming one of the original four is held to the identical shape by THREE guards because a peer’s own DDL always carries the table’s FULL current shape, whether or not a given axis actually changed: an EXPLICIT differing value — spelled as an AlterTable, as a CreateTable adopting an already-existing table, or as a fresh CreateTable of a reserved name (guarding against DROP + CREATE circumvention) — is refused atomically for the whole batch before any of it is written; an axis the arriving DDL is simply SILENT on PRESERVES the table’s current declared value instead of being read as an implicit clear, which is what lets a half-healed peer’s own in-progress multi-step reconcile interoperate. This is not a workaround for a missing knob: an installer (or a peer) that restates the canonical declaration needs no exception, because a healing call always restates that same declared shape verbatim, and a verbatim restatement always applies — locally, and over sync.

pre_commit can reject a transaction by returning Err. post_commit fires after the write is durable. Downstream applications use contextdb as a library and accept Database via dependency injection — they are database users, not plugin authors.


Subscriptions

Reactive commit notifications via bounded broadcast channels:

#![allow(unused)]
fn main() {
let rx: Receiver<CommitEvent> = db.subscribe();
// or with custom capacity:
let rx = db.subscribe_with_capacity(256);
}
#![allow(unused)]
fn main() {
pub struct CommitEvent {
    pub source: CommitSource,  // User or Autocommit
    pub lsn: u64,
    pub tables_changed: Vec<String>,
    pub row_count: usize,
}
}

Fan-out to multiple subscribers. Dead channels are cleaned up automatically. Graceful shutdown disconnects all subscribers.

Memory Limit On Edge Devices

SET MEMORY_LIMIT, SHOW MEMORY_LIMIT, and the --memory-limit startup option all feed the same global memory accountant. A read-only reader reserves a fixed source floor before it holds any data: a 64 KiB source window, a 128 KiB verifier ledger and two small controls, 197,048 bytes (about 200 KB) in total, plus its cache share. A declared limit below the floor is refused, and the refusal names the floor. The floor is necessary, not sufficient: the schema the reader opens, its controls and its cache need more, and a reader charges the exact-probe filters it physically holds, not one per declared index. Vector operations attribute allocations with tags such as [email protected]_text so operators can identify the offending index from errors.

The 2 GB edge working-set target guides constrained deployments. For a constrained device, SQ8 can reduce the stored vector footprint:

SET MEMORY_LIMIT '1536M';
CREATE TABLE evidence (
  id UUID PRIMARY KEY,
  vector_text VECTOR(768) WITH (quantization = 'SQ8'),
  vector_vision VECTOR(512) WITH (quantization = 'SQ8')
);

SHOW VECTOR_INDEXES gives one summary row per vector column, including aggregate counts and live vector payload byte totals. SHOW VECTOR_PARTITIONS FOR table.column supplies the typed-key, per-partition lifecycle detail, including each durable generation’s state; use those surfaces instead of parsing memory operation tags.


Sync

This release emits and accepts sync protocol 7. Vector declarations use that protocol’s schema vocabulary without changing the vector payload shape. The ALPN identifier remains contextdb.sync.v6; it names the transport framing, including the reply-receipt byte that lets graceful shutdown prove the dialing peer received a terminal reply and the per-publication nonce that prevents identical large replies from sharing durable bytes or completion state. Payload version skew is checked by the envelope, not the ALPN.

Once shutdown closes admission, the hub finishes sync work it already accepted, including the chunk and authenticated completion exchange for an oversized reply. New requests are refused. A peer that stops acknowledging replies cannot hold the hub forever: each reply receipt and the complete graceful-drain phase have a 30-second failure ceiling, after which the endpoint closes the stalled work. These deadlines are liveness bounds, not scheduling delays; normal progress advances on protocol state and returns as soon as the accepted work is complete. Before route admission, the default server policy permits at most 128 incoming connection/handshake tasks and 64 MiB of aggregate declared sync-frame payload. Request-frame reads must make application-byte progress every 30 seconds; QUIC keepalives do not reset that application deadline. Operators declare different server-local limits with pre-admission-connections=, pre-admission-bytes=, and request-read-idle-ms= in the bind endpoint spec. Exhaustion refuses new work immediately instead of allocating memory or creating a waiting task. The edge’s push request uses the general 60-second sync-operation ceiling, so it does not reset a valid larger atomic push while the hub is still committing; on shutdown the hub’s 30-second drain ceiling therefore decides first and returns a confirmed reply or a loud failure. The hub reserves at most 1,024 unfinished oversized replies in its shutdown registry and refuses the next reply before staging it. Each authenticated, validated chunk refreshes a 30-second inactivity lease. A transfer that resumes while the hub is still serving is re-registered after its durable manifest and chunk validate; a transfer that was silent for the full lease before shutdown is stalled work and is not allowed to reopen admission during drain. When a peer cannot represent a table’s declared schema vocabulary, ContextDB holds that table with a typed diagnostic naming the table, missing capability, and node to upgrade. Unaffected tables continue syncing and the held table resumes after the capability is available.

This release emits and accepts only protocol 7; envelopes using any other protocol version are refused. The current wire retains row arrival ordering, the serving-store incarnation in pull responses, the purge instruction lane, schema provenance, and the structured authoritative-hub purge refusal.

The wire version bumps when a change alters sync bytes or sync semantics. SQL, storage, CLI, or maintenance work that leaves sync unchanged does not bump the protocol. A downstream consumer that must adapt to a new wire does that in its own release; the bump is not a contextdb defect.

Partitioned vector search leaves the sync message shape unchanged. Partition-key declarations travel with ordinary schema DDL; on receipt, ContextDB derives the local layout membership from the row it accepted in the same transaction. A key-only update moves each non-NULL vector locally even when no new vector payload arrives. This local layout neither authorizes a row nor changes a table’s declared sync direction.

Deployment Topology

contextdb uses a client-server sync model where every instance — client or server — runs the same database engine. There is no “replica” or “read-only copy.” Each database is a full read-write contextdb that works independently offline.

┌──────────────┐  ┌──────────────┐  ┌──────────────┐
│  contextdb   │  │  contextdb   │  │  contextdb   │
│  (laptop)    │  │  (service)   │  │  (device)    │
│  SyncClient  │  │  SyncClient  │  │  SyncClient  │
└──────┬───────┘  └──────┬───────┘  └──────┬───────┘
       │ dial            │ dial            │ dial
       │                 │                 │
       └────────┬────────┴────────┬────────┘
                │ sync endpoint (dial-by-key) │
                └────────┬────────────────┘
                         │
                ┌────────┴───────┐
                │  contextdb     │
                │  (server)      │
                │  SyncServer    │
                └────────────────┘

Each client database accumulates knowledge independently — decisions, observations, corrections, embeddings. On sync, changesets flow bidirectionally: local changes push up, server changes pull down. This is collaborative sync, not WAL replication — logical changesets with per-table conflict resolution, so knowledge learned by any participant propagates to all others.

Dial-by-key means clients reach the server through its cryptographic identity, not a broker address. A node behind NAT dials outbound, so machines on one LAN sync over direct connections with no port forwarding, no VPN, and no network configuration. The default configuration contacts no third-party service. To introduce peers across networks, the operator either self-hosts a small stateless iroh-relay (which only forwards end-to-end-encrypted bytes) via relay=<url>, or opts into the free public relays with relay=n0 — connectivity is never a paid feature. A self-hosted relay presenting a private or self-signed certificate is trusted by pointing relay-ca=<cert-file> at its PEM bundle or single DER certificate. Dynamic address resolution is equally opt-in: publish= announces a node’s addresses to a chosen service (n0’s free one or self-hosted) and lookup= (mdns / n0 / a self-hosted zone or relay) resolves peers by identity alone — with these, tickets survive IP changes.

The server is just a contextdb instance running SyncServer. Self-host it, or point your client databases at a hosted server — the client binary and database files don’t change, only the enrollment ticket they dial. Managed hosting is coming soon — join the waitlist.

Manifests and outcomes

The engine’s private custody modules own canonical policy, manifest, signed outcome, and incarnation records in the existing config-value journal. SyncClient and SyncServer in contextdb-engine perform authenticated bind, ordinary manifested push, status, and outcome fetch; contextdb-server re-exports them. Row/outcome acceptance shares the production Redb transaction. Local DISCARD stages a physical erasure projection in the ordinary write transaction and publishes it with the ordinary writes only after durable success. It reuses the authoritative purge’s physical erasure writer and failure boundary but creates no fleet frontier.

Custody does not maintain a per-commit history chain or inspect its journal on unrelated writes. The owned immutable metadata cache is invalidated at custody commits and erasure. Admin SHOW uses that metadata; scoped delivery reads use the existing root visibility and read-route budgets.

Every application column contributes to the canonical BLAKE3 row and unit digests; local bookkeeping belongs outside those rows when identical imports must compare equivalent. One durable terminal outcome shares the unit’s commit. Outcome fetch returns every outcome after the supplied acceptance position, including every outcome sharing one position. Watermarks never substitute for receipts. The journal grows with retained units and is erased with them, not reclaimed by ordinary delivery. On mutable tables, updating or deleting a manifested row retires its current manifest ownership in the row commit while retaining signed history and terminal outcomes for inspection. A replacement root without a newly registered manifest receives manifest_required; unrelated units keep syncing. Deletes follow the table’s ordinary conflict policy and are never reoffered as live manifested rows. Delivery counts include current manifest owners only; re-registration creates a new pending unit.

An ordinary status request carries one hub-signed prefix checkpoint. The hub checks that prefix against its committed image; unrelated writes and purges cannot conceal a lost accepted prefix. A current image retains its incarnation. An older image rotates it, including a declared snapshot from before the first binding, and the edge automatically rebinds and returns old credit to pending. Missing policy declarations still require the operator to declare them. Verification failure is reported to the caller. A listed node-local purge predicate and bound values travel as permitted journal erasure instructions; each edge selects and erases its own keys within the same applied boundary.

Components

  • SyncClient — runs on each participant. Pushes local changes to server, pulls remote changes.
  • SyncServer — runs on the central server. Receives pushes, serves pulls.

Both communicate over per-tenant sync channels: sync.{tenant_id}.push / sync.{tenant_id}.pull. Custody adds BindApplicationTablePolicyRequest and FetchDeliveryOutcomesRequest. Manifests and outcomes travel on ordinary push/reply; ordinary status carries the hub incarnation and verifies one signed prefix checkpoint. There is no separate purge or authority-exchange request kind.

Change Tracking

  • Every committed row is assigned an LSN (Log Sequence Number)
  • SyncClient tracks push and pull watermarks (the LSN of the last synced change)
  • On push: sends all changes since the push watermark
  • On pull: requests all changes since the pull watermark
  • After restart: full_state_snapshot fallback rebuilds from current state (the ephemeral change log is lost)

A table needs a sync identity to sync. A row is told apart from another row of the same table by its identity: a declared PRIMARY KEY (single-column or table-level PRIMARY KEY (a, b, ...)), or failing that, an indexed id column as a fallback. A table declaring neither has no way to tell one row from another across the wire — this is a keyless table.

A keyless table that would never leave the machine anyway (SYNC OFF) is unaffected; it was never eligible to sync either way. But a keyless table that WOULD sync (any other direction — the default is SYNC TWO WAY) makes push() refuse loudly with Error::SyncError, naming the three ways to fix it, rather than silently reporting success while that table’s rows never actually cross the wire:

table 'events' has no usable sync identity — no declared PRIMARY KEY and no
indexed `id`-column fallback — so its rows cannot be told apart across the
wire. Push refuses rather than silently omitting them while reporting
success. Fix one of: declare a PRIMARY KEY on 'events'; add an indexed `id`
column as the fallback identity; or declare 'events' SYNC OFF.

This is a covering-index requirement in spirit: a table an application means to sync must declare an identity, the same way a table meant to be looked up efficiently declares an index. There is no silent partial-sync mode — either the table can be synced (it has an identity) or it explicitly opts out (SYNC OFF); nothing in between quietly drops rows.

The keyless fallback — a column named id with no declared key — is usable only when id has a covering index. Sync apply otherwise refuses with exact sync key probe on keyless(id) has no covering index (the table name fills the first slot: a table events would read events(id)).

Conflict Resolution

Each table declares its conflict behavior in DDL:

  • SYNC CONFLICT KEEP FIRST — the hub’s first accepted value remains.
  • SYNC CONFLICT KEEP LATEST — the later accepted value replaces it.

Whichever policy applies, a conflict means two machines genuinely diverged. A row that arrives carrying exactly what the receiving node already holds is a re-delivery — the everyday case being an edge pulling back rows it just pushed — and is a pure no-op that appears in none of the three counts an apply reports: applied_rows counts rows that changed local state, conflicts records genuine divergence at a natural key, and skipped_rows counts rows a policy or the context scope turned away. Re-delivered data changed nothing and refused nothing, so it is counted nowhere. The decision is made per row against that row’s own content, so one changeset mixing a re-delivery with a genuine divergence still reports the divergence in full.

Both of those judgements are made strictly within what the receiver can see. A row outside the receiving handle’s context scope is refused before its content is ever compared — it counts as skipped_rows and never as a conflict. A conflict says two peers both saw a row and disagreed about it, which a receiver that cannot see the row has no basis to claim, and the record would name the hidden row’s natural key, disclosing the very existence the access boundary exists to hide. So “identical content is a no-op” is a statement about rows within visible scope; a hidden row is refused outright, whatever it contains.

Arrival Ordering

KEEP LATEST arbitrates on one clock: the accepting node’s own ordering of arrivals — never two machines’ independent LSN counters. Each row’s wire form carries arrival: the ordering position some node already gave it, or absent when the sender itself authored the row fresh and never yet synced it anywhere. The rule:

  • byte-identical values → no-op (the ordinary re-delivery case above);
  • the incoming row carries no arrival → this accepting node is the one ordering it, so it always wins over whatever is already held, regardless of the sender’s own local clock;
  • the incoming row carries an arrival at or below the position already stored for that row → a stale echo — a no-op, never a conflict and never counted as skipped;
  • the incoming row carries a strictly higher arrival → it wins.

The winner is therefore always the mutation the accepting node took last — never the row from whichever machine happened to have run more local writes first. arrival is minted from the accepting node’s own commit LSN (the same value already restored monotonically at open — no new counter, no new durable table) and is re-stamped forward on every winning apply, so a later relay of the same row carries the position the FLEET actually agrees on, not the sender’s own unrelated counter. A row accepted without an established position takes EXACTLY its own commit position on the accepting node — never a value sampled before that commit was ordered, so two rows accepted around the same instant can never be minted the same arrival only to land at two different committed positions.

Pull Cursors Are Bound To Their Serving Store

A pull cursor is only ever compared against the history of the store that issued it. The puller persists (source incarnation, lsn) as one record — never a bare Lsn — so a page served by a store other than the one the cursor addresses is discarded unapplied, the cursor resets to Lsn(0), and the client fully re-pulls the new store’s history. This covers two operator-facing scenarios a bare watermark cannot: pointing an edge at a different endpoint for the same tenant, and a hub wiped and rebuilt under the same transport identity. A mid-pull source change (the serving store changes between two pages of one paged pull) discards only the mismatched page — whatever already applied from earlier pages, and their cursor advance, stands. The existing stale-restore guard (fires when the new store’s clock is numerically BEHIND the old cursor) is unaffected and still applies; source binding closes the complementary case where the new store is numerically AHEAD but holds real history below the old cursor.

Transport

Dial-by-key: each machine is reached by its own cryptographic identity (an ed25519 public key), carried by the Iroh library (iroh 1.0, wire-stable). A framed stream carries each payload whole; payload size is bounded by batching above the transport, and vector byte sizes are accounted for in batch estimation. LAN peers connect directly; cross-network peers are introduced by a self-hosted or opt-in relay.

Iroh is the single sync transport. Every exchange authenticates its peer by cryptographic fabric identity.

SyncClient::new gives any file-backed Database a durable identity without exposing the database’s persistence path: with a bare enrollment ticket it loads or creates <canonical-db-path>.fabric-identity.key. Reopening that database and recreating the database file while retaining this adjacent key both keep the same node identity; a recreated database still has its new store incarnation. An endpoint that explicitly supplies identity=<key-file> always takes precedence over the adjacent key. The lower-level raw transport may still use an ephemeral identity only for protocol-level anonymous-refusal coverage where no database participates.

DDL Sync

Schema changes (CREATE TABLE, ALTER TABLE, DROP TABLE) are synced alongside data. Constraints (PRIMARY KEY, NOT NULL, UNIQUE, single-column and composite FOREIGN KEY, STATE MACHINE, DAG) are preserved across sync.

A synced ALTER TABLE carries the operation its author ran, not just the resulting column list. DROP COLUMN and RENAME COLUMN travel as explicit, signed markers inside the authenticated DDL, and a receiving machine removes or renames a column only when such a marker names it: a dropped column leaves with its stored values, vectors and index, and a renamed one keeps its values, vectors and index under the new name, on the author, on every receiver, and after a restart. A column that an arriving ALTER TABLE simply does not mention is kept, with its data, whether or not the statement restates the primary key. A receiver never infers a drop from an omitted column or a rename from two columns that happen to share a type. A node that relays a received ALTER logs the same markers, so a machine further down the line applies the same operation.

An authored SET MAINTAIN COUNT declaration travels through authenticated push to the hub. The hub installs the declaration and answers a matching exact COUNT(*). A peer without that schema capability holds back the affected table while other tables keep syncing; once the peer supports it, the held declaration can be pushed.

Received schema history replays one source transaction at a time, in author order. A table or trigger created and later dropped still participates in the intervening transactions; the receiver does not reduce the history to its final schema before applying the rows.

An arriving CREATE TABLE declaration whose columns differ from the existing table is reported as a schema mismatch instead of silently replacing that table.

A repeated received schema unit with the same recorded migration identity returns its original apply result without changing the installed schema or rows, including after the receiver reopens. The engine’s received_schema_atomic_retry_tests::identical_received_marker_vector_retry_returns_its_original_observation_without_mutation asserts the returned result and stored state are equal before and after the retry. An authenticated push retry after a lost reply also leaves the receiver’s DDL history unchanged.

Hub declarations persist before an application table exists. The first authenticated binding freezes each declared policy. Bound local and arriving DDL must match the binding; arriving DDL cannot overwrite an explicitly declared local policy. A per-table refusal leaves unrelated tables available for sync.

Tenants and Contexts

A tenant’s application-table policy is declared at its authoritative hub and bound independently of enrollment. The binding names the tenant, hub identity and database incarnation.

Two identifiers look similar but sit on different axes; don’t conflate them. A TenantId (contextdb_core::TenantId) is a sync-surface identity — the isolation boundary a SyncClient/SyncServer pair operates under, one tenant per sync relationship (the sync channels above are literally namespaced sync.{tenant_id}.push / .pull). A context id is a different axis entirely: an in-database scoping handle that rows carry, controlling which rows are visible within a single store, independent of who that store syncs with. Downstream consumers map their own organizing handle — an intention, a site, a tenant of their own — onto one or both of contextdb’s axes, but the axes themselves stay separate: TenantId answers who syncs with whom, a context id answers which rows are visible.

The relationship between the axes is one-to-many: one tenant owns many contexts. A person or organization is one tenant — their sync/routing identity — and partitions their worlds into contexts under that single tenancy (one user’s product-development context and hobby-electronics context; one customer’s N per-site contexts). The stack’s consumers default a fresh capture context to the tenant’s own name — “your tenant is your first context” — and users narrow into per-world contexts deliberately. The two identifiers are never two names for one thing and are not to be unified.


Work Ledger and Media Plane

Two library surfaces, no CLI. They’re documented together because a job on the ledger can reference a blob_ref input, and resolving that reference is the media plane’s job, not the ledger’s.

Work Ledger

There are two modules named work_ledger, owned by different crates, at different layers. Importing the wrong one is an easy mistake — disambiguate by what you need:

  • contextdb_engine::work_ledger is the class-blind bookkeeping layer: seven append-only tables recording job lifecycle events. A job’s state — Pending, Leased, Done, Failed, or Cancelled — is computed from those rows, never stored as a column. A job is submitted (submit_job), claimed with a lease (insert_claim), completed exactly once (record_result), failed (record_failure — the failure row is what legalizes the next attempt), or cancelled. Lease expiry is advisory wall-clock time supplied by the caller; the engine never trusts a clock on its own, and lease expiry alone does not mean a job has been abandoned — that judgment belongs to the caller deciding whether to re-claim.
  • contextdb_server::work_ledger is the distributed execution layer built on top, over the transport-neutral SyncClient. claim_job claims by push: it inserts a local claim row, pushes it, and the hub’s conflict reply on the claim key is the arbitration verdict. If the hub is unreachable, the claim is held locally as Won { synced: false } rather than blocking. poll_and_execute_once and run_worker_loop drive execution; the product supplies a WorkExecutor trait implementation as the seam where real work happens.

If you’re looking at the server module and want to know where job state actually lives, it’s the tables and the pure claim/lease functions in contextdb_engine::work_ledger. If you’re looking at the engine module and want the cross-machine claim path — the part that arbitrates between two workers racing for the same job — that’s contextdb_server::work_ledger::claim_job, one layer up.

Media / Blob Plane

contextdb_server::BlobStore (re-exported from the server crate root) moves opaque, content-addressed bytes between nodes:

The bytes, chunk manifests, partial-transfer checkpoints, and protection tags live inside ContextDB’s own redb file. They are not a second filesystem store. That ownership is what makes authoritative PURGE honest: one transaction removes the selected ledger lineage and every now-unreferenced engine-held blob copy, or the whole statement fails with no partial erasure. After success no ContextDB query, history read, serve, export, resume path, or engine-created backup can reach those bytes. This is logical engine erasure, not a claim that redb can overwrite filesystem journals, SSD wear-leveling ghosts, or external OS snapshots; encryption-at-rest with key erasure is the route to that stronger forensic guarantee.

This boundary covers media deliberately ingested for distributed work, such as a detection clip sent from a submitting node to a GPU worker. An application’s independent recording archive is outside ContextDB: for example, deleting continuous camera footage written by Vigil’s own media pipeline remains a Vigil retention/erasure responsibility and must be coordinated separately for a product-level “erase this person” operation.

  • Ingest on the holder node: ingest_bytes(&[u8]) -> BlobHash or ingest_file(&Path) -> BlobHash. The returned BlobHash is the hash of the bytes, so ingesting the same content twice is idempotent.
  • Serve on the holder: serve_on(&IrohServer) -> Result<()> registers the serving handler. It checks the requesting peer’s authorization against the ledger before any payload bytes move — the check is the gate, not an after-the-fact audit. Each request obtains its own child of the store’s declared reader memory. Hosts supplying their existing provider use serve_on_with_read_budget_provider. Directory registration failures propagate.
  • Fetch on the consumer: resolve_blob_ref(&BlobHash, holder_ticket, sink) is async, fetches node-to-node, and is hash-verified — only bytes that hash to the requested BlobHash ever reach the sink. Errors are a matchable ResolveError: Unentitled, PolicyForbidden, HashMismatch, HolderUnreachable, LocalStoreUnavailable, BlobNotFound, TransferAborted, SinkWrite, FetchTimedOut, LedgerRead(Error). The default creates an operation child internally; hosts supplying their actual READ owner use resolve_blob_ref_with_read_memory. Ledger source refusals retain their typed core error, including when returned by the holder on the same blob stream before Bao payload data. A malformed or oversized control frame returns a typed protocol refusal.
  • Fetch deadline: the whole resolve_blob_ref attempt — dial, the holder’s tag bookkeeping, and the verified transfer loop — is bounded by a declared BlobFetchPolicy { fetch_deadline_ms } (documented default: 120_000, i.e. 120s). Set it per instance with blob_store.set_fetch_policy(BlobFetchPolicy { fetch_deadline_ms: 30_000 }) before calling resolve_blob_ref. The bound is enforced by spawning the fetch and timing out the JOIN, not the future being awaited directly, and the abandoned fetch is aborted at the next yield point — a fetch that never yields continues occupying its worker thread until it completes, it is not preempted. This bound is runtime-shape-dependent, not unconditional: on a MULTI-THREAD tokio runtime the caller genuinely returns within the declared timeout regardless of the fetch’s internal behavior, because a non-yielding spawned task can only occupy the one worker thread it landed on, leaving the timer free to fire on another thread. On a CURRENT-THREAD runtime there is only one OS thread total, so a spawned fetch that never cooperatively yields starves that thread entirely — including the timer backing this very deadline — and the caller does NOT return within the bound until the fetch itself yields or completes. Dropping the resolve_blob_ref future early (e.g. because ITS OWN caller applied a shorter outer timeout) has the same multi-thread-only guarantee, so a current-thread runtime is never left waiting un-cancellably by this mechanism alone — choose a multi-thread runtime when this deadline must hold regardless of a peer’s behavior.
  • Reclaim: reclaim_unreferenced(now_ms, grace_ms) frees a blob once every job referencing it is terminal past the grace window (or once no job references it at all). A later resolve attempt against a reclaimed blob returns BlobNotFound, even from an otherwise-entitled caller. The captured ledger root stays funded by the operation child throughout the sweep. spawn_reclaim_driver(grace_ms) obtains a child of the declared reader parent for each cycle. Explicit host variants are reclaim_unreferenced_with_read_memory and spawn_reclaim_driver_with_read_budget_provider.
  • Direct vs. relay: whether a transfer goes direct or via a relay is a serve-time choice on the holder’s endpoint spec. By default it’s direct only — contextdb contacts no relay unless asked. The operator opts into the public relays or a self-hosted relay URL, the same relay configuration described under Sync. The choice rides in the serve ticket, so a consumer behind NAT that can’t reach the holder directly is bridged automatically.

Resolving a blob requires an entitling claim. A consumer node may fetch a blob only while it holds a live claim — lease still ahead, that attempt not failed — on a job whose inputs reference that blob’s hash (node_holds_claim_for_blob). This is the actual security boundary, and it’s checked holder-side at serve time, not trusted from the requester. There is also a local, identity-blind pre-check the consumer can run before dialing — that check exists purely to avoid a wasted network round trip; it is not the entitlement boundary and should never be treated as one.

The ledger reserves the blob_ref input kind but doesn’t resolve it: materialize_inputs refuses a blob_ref input with Error::InputRequiresBlobResolver, pointing the caller at the blob resolver above. The ledger tracks that a job depends on a blob; the media plane is what actually moves the bytes.

The seven ledger tables are not created for you. Every call below — submit_job, job_state, claim_job — needs them present, so install them at open on each node: install_work_ledger_schema(&db)? is idempotent, safe to call every time, and safe when the schema already arrived via sync.

#![allow(unused)]
fn main() {
// Node A (holder): ingest a blob, serve it, and submit a job that references it.
use contextdb_engine::work_ledger::{InputRef, JobSpec, install_work_ledger_schema, submit_job};
use contextdb_server::{BlobStore, work_ledger::claim_job};

install_work_ledger_schema(&db)?;

let bytes = std::fs::read("frame.jpg")?;
let blob_hash = blob_store.ingest_bytes(&bytes)?;
blob_store.serve_on_with_read_budget_provider(&iroh_server, read_budgets.clone())?;

let spec = JobSpec::builder("job-1", "describe-image", "once", "node-a")
    .input_refs(vec![InputRef::blob_ref(blob_hash.clone())])
    .build();
let no_direct_inputs: [&[u8]; 0] = [];
submit_job(&db, &spec, &no_direct_inputs)?;

// Node B (worker): claim the job under a lease, then resolve the referenced blob.
// Holding a live claim on this job is what entitles Node B to fetch the bytes.
let claim = claim_job(&sync_client, "job-1", 1, "node-b", lease_deadline_ms, now_ms).await?;
let mut sink = Vec::new();
let bytes_written = blob_store
    .resolve_blob_ref_with_read_memory(read_memory.clone(), &blob_hash, &holder_ticket, &mut sink)
    .await?;
}

Store Format and Recovery

The on-disk store carries a format-version marker (current: 1.0.0). Opening a store with an incompatible marker, schema layout, or versioned-key shape fails with LegacyVectorStoreDetected. A store written by an earlier build is not read. Move it aside and create the store again.

Reading a store whose writer died

A writer killed at any point leaves a store that a direct reader on the same machine opens at once, without waiting for a writer to repair it. Every commit writes all of its pages to the file before the header that names it and syncs once, so after a process crash the header always names a commit whose pages are all in the file. The reader picks the commit slot exactly as a writer’s repair would, verifies only its two root pages, and registers that root – the same fixed admission work as a healthy read (ADMISSION_PAGE_BUDGET), with no allocator state, no walk of the store, and nothing written to it. What it serves is what the next writer’s repair keeps.

It does this only when the dead writer’s published run names this boot and this exact data file (device, inode, birth time), and the file sits on a local filesystem (ext2/3/4, XFS, Btrfs, tmpfs, F2FS) on Linux. Otherwise it refuses with DirectReadRequiresWriter and a writable open repairs the store:

  • after a machine crash or power loss, a commit can be torn below its roots, and only repair’s walk of the whole tree can tell;
  • on other platforms and filesystems there is no such guarantee to lean on.

Two limits are recorded:

  • A reader on the same boot can serve a commit whose sync the dead writer never completed; if the machine then loses power before any writer repairs the store, that commit is gone.
  • Media damage below verified roots surfaces as StoreCorrupted when a read reaches it.

Recovering a wedged or corrupt data root

A corrupt or truncated store is detected on open and surfaced as StoreCorrupted, with the error message naming the next commands rather than leaving the caller to guess:

contextdb diagnose ./my.db   # read-only: reports what is salvageable/diagnosable, never modifies the store
contextdb reset ./my.db --force   # destructive: recreates a fresh, empty current-format store at the same path

diagnose reads the store’s format marker and top-level schema layout through a read-only handle and reports its diagnosis (current-format and readable, written by an earlier build, or corrupt/truncated with the underlying reason) — it never opens the store read-write and never writes to the path, so running it is always safe. reset refuses without --force (see CLI Reference for the exit code it uses); with --force it deletes the existing file and recreates an empty store, so restore anything you need from a backup or a healthy sync peer FIRST if the data still matters.

A second open of a data-root file already held open by another process — same process or a different one — returns a database-locked error; that is the ownership guarantee described under Store Ownership & Concurrency, not a corruption signal, and doesn’t call for either command above.


Query Pipeline

SQL string
  → contextdb-parser (pest grammar → AST)
  → contextdb-planner (AST → PhysicalPlan)
  → contextdb-engine (dispatches to executors)
    → contextdb-relational (row operations)
    → contextdb-graph (BFS traversal)
    → contextdb-vector (ANN search)
  → QueryResult { columns, rows, rows_affected }

The planner is rule-based (no cost optimizer). Key planning decisions:

  • GRAPH_TABLE in FROM → PhysicalPlan::GraphBfs
  • ORDER BY ... <=> ... → PhysicalPlan::VectorSearch (with candidate restriction from WHERE)
  • CTE containing GRAPH_TABLE → recursive plan composition
  • IN (SELECT ...) → subquery evaluation

Memory And Disk Budgets

The database tracks all row, graph, and vector allocations against one memory budget. Embedding callers use the durable Database::set_memory_limit(Some(bytes)); its setting survives reopen and remains runtime-configurable. A process that needs a non-raisable startup ceiling uses contextdb_engine::database::open_with_startup_limits. The CLI’s --memory-limit uses that same startup path. Budget exceeded → operations return MemoryBudgetExceeded. The raw mutable accountant is an engine implementation detail and is not attachable or reachable through the normal public API.

File-backed databases also support a persisted disk budget:

  • startup ceiling/default via --disk-limit
  • runtime control via SET DISK_LIMIT / SHOW DISK_LIMIT
  • persisted live config in the redb file so reopen preserves the limit

Disk enforcement happens in the engine write paths before INSERT, UPDATE, and sync-apply work begins. The storage allocator also reserves each projected physical file growth before allocating it, so pages retained for a live file reader and newly allocated writer pages cannot carry the file past the configured limit. Once the on-disk file is at or above the configured limit, or an allocator growth would cross it, further file-backed writes fail with DiskBudgetExceeded without poisoning the writer.

Capability Index — what contextdb is, is not, and where it stops

One page to orient a new reader or an agent before it reads anything else: what contextdb actually does, what it deliberately does not do, and the numbers that bound it. Every claim below is backed by a doc section or a test in this repo — this page is a map, not a new source of truth.

What contextdb is

  • An embedded, schema-free, multi-model database. Relational tables, graph traversal, and vector similarity search, all under one MVCC transaction, in a single file, in a single process. See Why contextdb? and Architecture.
  • A policy engine, not just storage. STATE MACHINE, DAG, IMMUTABLE, PROPAGATE, RETAIN, and HISTORY CURRENT ONLY are declared in table DDL and enforced by the database itself, not by application code or bypassable triggers. See Query Language.
  • PostgreSQL-compatible SQL for relational work, pgvector syntax (<=>) for vector search, and a bounded subset of SQL/PGQ GRAPH_TABLE ... MATCH for graph queries — familiar surface, not a new query language to learn.
  • Bidirectional dial-by-key sync. Every database syncs changesets with a declared, per-table conflict policy (SYNC CONFLICT KEEP FIRST | KEEP LATEST) over the Iroh transport — nodes reach each other by cryptographic identity, no broker installed by default. See Architecture — Sync. This release emits and accepts only sync protocol 7. Envelopes using any other protocol version are refused.
  • A durable work ledger and a media/blob plane, both pure library surfaces today (no CLI flag): jobs/claims/leases for distributing work across machines, and content-addressed blob ingest/fetch/reclaim for large media. See Architecture.
  • A CLI, a REPL, and a Rust library — contextdb/contextdb-server binaries plus the contextdb-engine crate as an embeddable dependency. See CLI Reference and Getting Started.
  • Exact vector search. This release ships exact vector search only. AUTO and EXACT compare allowed stored vectors within the active budgets; INDEXED returns VectorIndexedRouteUnavailable. The clustered index is switched off: commits and maintenance do not build or write it, and queries and inspection do not read it. Index policy declarations are retained but do not enable an index.

What contextdb is not

  • Not a schema. The agentic-memory tables used as running examples throughout these docs (decisions, observations, intentions, digests) are one example schema you can delete or replace. See the README and Why contextdb?.
  • Not a general-purpose database. The SQL/PGQ graph subset covers bounded traversal for agentic workloads, not the full standard; contextdb is a focused tool for agent memory, not a data warehouse. See the Design Envelope.
  • Not a message broker or a third-party relay by default. The default configuration contacts no external service; a self-hosted or opt-in iroh-relay is only for introducing peers across networks that can’t reach each other directly. See Architecture — Sync.
  • Not multi-tenant orchestration. --tenant-id is a sync namespace — every client and server sharing one tenant ID replicate with each other. Running many isolated tenants behind one deployment, cross-tenant intelligence, and managed hosting are commercial-layer concerns outside this repo, not something contextdb itself provides.
  • Not a majority-vote distributed system. Conflict arbitration is declared per table (KEEP FIRST / KEEP LATEST) and, for the five hub-refereed work-ledger tables, hardcoded to keep_first — there is no quorum or leader election; a hub is a specific, addressable machine an edge dials.
  • Not yet bound to Python or TypeScript. Rust library and CLI today; other language bindings are on the roadmap (README).
  • Schema vocabulary a peer cannot read holds back only that table. The holdback names the affected table, missing capability, and node to upgrade while unrelated tables continue; the held table resumes when the capability is available.

Where it stops — the numbers

  • Design envelope (from Why contextdb? — Design Envelope): 10K–1M rows per database, sparse graphs with bounded traversal (depth ≤ 10), append-heavy writes with small transactions, laptops and ARM64 devices as the target hardware (browser/mobile via WASM is a future direction, not shipped).
  • Nine reserved table names (work_jobs, work_claims, work_results, work_failures, work_cancellations, work_inputs, work_capabilities, peer_directory, work_node_contacts) carry a fixed, engine-owned shape and policy — an operator table using any other name is entirely unrestricted. See Architecture.
  • A table that syncs needs a declared identity (a PRIMARY KEY or an indexed id column) — the CREATE TABLE is accepted, and a keyless table declared with any sync direction other than SYNC OFF then refuses loudly at its first push rather than silently failing to replicate. See Query Language.
  • Memory and disk are configurable, not unbounded: SET MEMORY_LIMIT and SET DISK_LIMIT (or the process-start bootstrap functions) are how a caller sets a ceiling; there is no hard-coded default cap.
  • A CLI read is bounded, and a bounded read is complete or refused: one SELECT succeeds only within the declared ceilings — 500 rows and 4 MiB by default — and crossing either publishes no rows and refuses with owner_limit_exceeded. Nothing is ever silently truncated, there is no flag that disables the ceiling, and .cursor open / .cursor fetch is how a larger result is paged. The embedded Database::execute library call keeps its uncapped contract. See CLI Reference.
  • All-scope vector search is bounded, not a promise to load every layout at once. The engine fans out under the reader’s work, memory, and cancellation limits and either returns one complete global answer or refuses honestly. The edge-device memory target is SET MEMORY_LIMIT 2G (CLI --memory-limit 2G), exactly 2147483648 bytes. See Architecture and CLI Reference.

Linked from AGENTS.md. This page is generated by hand today; if it drifts from the sections it cites, the cited doc is the source of truth.

Tenant-governed event custody (Unreleased)

Hub policy declaration/binding, transactional manifests, durable outcomes, lost-ack readback, and policy-governed local discard are implemented in the engine. See SQL and API guidance. A unit’s complete application rows are compared with BLAKE3, and only a durable authenticated outcome ends pending delivery; a watermark is not a receipt. An old hub image that lost custody revokes old credit automatically when the edge next checks the hub, without erasing edge rows. Multi-table fleet purge and local discard each use one erasure boundary. Listed SYNC OFF predicates select each edge’s independently keyed rows; repeat delivery preserves fresh later creations. The outcome journal consumes storage until its owning unit is purged or discarded locally. Logical erasure does not promise forensic secure deletion or removal from operator-held backups; those remain the separate erasure and backup-recovery boundaries.

Agent readiness

This page describes how this repository measures whether an AI coding assistant can use contextdb as an operator and contribute a change to it as a developer. Both measurements are machine-graded — an AI model never grades another AI model’s work — and both are re-run against a specific commit, not claimed in the abstract.

USE: can an assistant operate a database it just opened?

Three models (a frontier-tier model, a fast-tier model, and a third-party model) were each given the same seven operator tasks, three fresh attempts per task per model, starting from nothing but this repository and its docs:

TaskWhat it covers
CreateCold start: open a store, create a schema, insert and read back rows.
Policy (graph)Build a DAG with GRAPH_TABLE, and get a cycle insert refused loudly rather than silently accepted.
Query (state machine)Drive a state-machine table through PROPAGATE transitions.
VectorHybrid graph + vector queries against embedding columns.
SyncStand up a hub, enroll two edges, and reach a correct KEEP-FIRST collision outcome.
DiagnoseRecovery: diagnose a store and recreate it with reset.
RetentionOperator lifecycle: retention windows and durable delete that survive sync and restart.

Every attempt is graded by executable post-conditions — process exit codes and the real resulting database state read back with --json — never by asking a model whether the output looks right. At the release-candidate commit measured here (9d362f6), the frontier-tier model passed all three attempts on all seven tasks (7/7). The fast-tier model passed six of the seven tasks fully and passed only one of three attempts on the seventh, the vector task. The third-party model likewise passed six of the seven tasks fully and passed only one of three attempts on the seventh, the diagnose task. A separate control run removed the repository’s agent-guidance files (AGENTS.md and the per-task skills/) and covered one operator task and one orientation task: with the guidance gone, the operator task still passed 3/3 but took roughly 1.3-1.4x the turns and wall-clock time, and the orientation task took roughly twice as long (9 turns to 17, 43 seconds to 106 seconds). Guidance cut turns and wall-clock time on both tasks — dramatically so for orientation — but the operator task’s token count came out essentially even with or without it; only the orientation task used dramatically fewer tokens with guidance. The docs alone were sufficient to pass, just slower.

CONTRIBUTE: can an assistant land a change through the real verification gate?

This measurement is narrower and says so plainly: it is a smoke test of this repository’s contributor surface — clear instructions, a working build, a gate that says what it means — not a general claim about AI coding ability, and the patches produced were never merged into the product.

Two small, real coding exercises were invented for the purpose, both graded at commit 889a372: adding a LENGTH(text) SQL scalar function, and adding a statement_kind field to .explain output. A fresh model was given only the public repository (no hints beyond what a real contributor would have) and graded by the repository’s own five-command verification gate (cargo fmt --all --check, clippy, the full test suite, a release build, and an isolated release install plus a ticketed-Iroh durability smoke) plus a small functional spot-check per task, so a change that passes the gate but is subtly wrong (for example counting bytes instead of characters, or a build that quietly excludes the changed code) still fails.

Before any graded attempt counted, the grading machinery was checked against both exercises. For LENGTH, a hand-written reference solution was run through the exact grader — the full five-command gate plus the behavior spot-check — on the trial machine, at that same commit, and passed. The .explain exercise had no reference solution to check that direction with, so its grader was validated only in the rejection direction. Both negative controls — a byte-counting LENGTH implementation, and a release build with the .explain feature excluded — were confirmed to fail the graders’ behavior checks. So the grader is proven two ways on LENGTH (accepts a correct answer, rejects a wrong one) and one way on .explain (rejects a wrong one only).

The honest result: the strongest model tested did not clear the bar within the fixed 25-turn budget. On the LENGTH exercise it repeatedly produced a semantically correct implementation with its own tests, but ran out of turns before running cargo fmt, and the unformatted diff failed the verification’s first step. On the .explain exercise, which touches more files across the parser, planner, and CLI, it spent its full turn budget exploring the codebase and never reached an edit.

Formatting is the first verification command: an otherwise-correct change fails immediately on an unformatted line. Time-dependent tests use the injected clock seam rather than wall-clock sleeps. The packaged-engine check builds from a plain source export. The sync skill’s cross-network guidance matches a real two-machine run (NAT traversal and hub-log expectations).

Benchmarking

A catalogue of contextdb’s benchmark targets: what each measures, which measurement profile runs it, and the rules for writing new ones. Benchmarks are descriptive. The product’s acceptance bars are listed in the performance guide; this page does not define, relax or replace them.

How to run benchmarks

All performance run commands — profiles, preparing clean checkouts, baseline and candidate runs, comparison and publication — are in docs/performance/README.md. They are not repeated here.

Targets

Every target is declared in its crate’s Cargo.toml and uses Criterion. scripts/ci-performance.py assigns targets to profiles and is authoritative if this table and the script disagree. Targets marked all only run only in the all profile.

CrateTargetProfileWhat it measures
contextdb-parserparser_throughputall onlyParser-only microbenchmark.
contextdb-engineengine_throughputvector (filter nv_)Local engine behaviour; its nv_ cases cover named-vector indexes.
contextdb-engineengine_full_throughputstorageLarger write/reopen and mixed workflow paths.
contextdb-enginestorage_scale_hardeningstorageTable-local index maintenance, reopen locality, wide-row commit batch size and file-backed single-row autocommit. Its fixtures assert locality counters before timing, so a failure is a correctness/locality failure first.
contextdb-enginerank_policy_recallvectorRank-policy recall.
contextdb-enginemixed_workflows_prworkflowsBounded mixed graph, relational and vector workflows.
contextdb-enginesubscriptions_prworkflowsBounded subscription fan-out and backpressure workloads.
contextdb-serversync_prworkflowsBounded sync workloads through direct sync APIs, without CLI/process overhead.
contextdb-serverserver_throughputsystemCLI throughput.
contextdb-serverserver_sync_systemsystemSystem sync with authenticated Iroh and file-backed CLI/server processes; needs the release binaries the system profile builds first.
contextdb-serverblob_transferall onlyBlob transfer.
contextdb-engineindexed_scan_filter_pk_inserts, indexed_scan_filter_file_backed_open, indexed_scan_filter_in_memory_pk_inserts, indexed_scan_filter_entity_list, indexed_scan_filter_non_id_pk_insertsall onlyIndexed scan and filter paths.
contextdb-engineacceptance_three_paradigm_query, sql_surface_distinct, graph_adjacency_probe, composite_index_routingall onlyQuery-surface and routing paths; see each source.
contextdb-enginetier1_atomic_visibility_under_contention, tier1_event_bus_throughput, tier1_context_guard_overhead, tier1_scope_label_overhead, tier1_acl_overheadall onlyVisibility, event bus, context, scope-label and ACL overheads.
contextdb-enginetier2_commit_validate_overhead, tier2_fk_revalidation_overhead, tier2_state_machine_overheadall onlyCommit validation, foreign-key revalidation and state-machine overheads.
contextdb-enginecommit_write_volume_scaling, sigterm_drain_in_flight_persistence, trigger_throughput, version_cleanup_scaling (feature test-seams)all onlyCommit volume scaling, shutdown drain persistence, trigger throughput and version cleanup scaling.

Some sources assert internal budgets or counters (for example a millisecond budget inside a bench). Those assertions are real checks, but they are not product acceptance bars.

Design rules

  • Keep setup outside timed loops unless startup cost is the thing being measured.
  • Keep benchmarks bounded enough to avoid Criterion stretching runs into multi-minute samples.
  • Derive system benchmarks from acceptance and integration scenarios rather than synthetic isolated loops.
  • If a benchmark spends its whole run on setup or build noise and never reaches useful Criterion output, treat that as a benchmark-shape problem to fix, not a valid result.