Getting Started

Run contextdb in under a minute.

Install

Download a pre-built binary (recommended)

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

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.

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.

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

.explain shows which plan ran — IndexScan for the filtered select above, Scan for a query whose WHERE clause does not match a declared index.

Rank Vector Search by Outcomes

Vector search can use a schema-declared rank policy when cosine similarity is not the only signal. The joined column must be indexed:

contextdb-cli :memory: <<'SQL'
CREATE TABLE outcomes (
  id UUID PRIMARY KEY,
  decision_id UUID NOT NULL,
  success BOOLEAN NOT NULL
);
CREATE INDEX outcomes_decision_id_idx ON outcomes(decision_id);

CREATE TABLE decisions (
  id UUID PRIMARY KEY,
  description TEXT NOT NULL,
  confidence REAL,
  embedding VECTOR(2) RANK_POLICY (
    JOIN outcomes ON decision_id,
    FORMULA 'coalesce({confidence}, 1.0) * coalesce({success}, 1.0)',
    SORT_KEY effective_confidence
  )
);

INSERT INTO decisions (id, description, confidence, embedding) VALUES
  ('11111111-1111-1111-1111-111111111111', 'closest but failed', 1.0, [1.0, 0.0]),
  ('22222222-2222-2222-2222-222222222222', 'less similar but worked', 1.0, [0.5, 0.0]),
  ('33333333-3333-3333-3333-333333333333', 'fallback with no outcome', 0.25, [0.75, 0.0]);

INSERT INTO outcomes (id, decision_id, success) VALUES
  ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', '11111111-1111-1111-1111-111111111111', FALSE),
  ('bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb', '22222222-2222-2222-2222-222222222222', TRUE);

SELECT id, description, confidence
FROM decisions
ORDER BY embedding <=> [1.0, 0.0] USE RANK effective_confidence
LIMIT 5;
SQL

Expected ordering:

22222222-2222-2222-2222-222222222222 | less similar but worked | 1.0
33333333-3333-3333-3333-333333333333 | fallback with no outcome | 0.25
11111111-1111-1111-1111-111111111111 | closest but failed | 1.0

The formula and join path are resolved at DDL time, stored with the schema, and replicated through sync. JOIN outcomes ON decision_id looks up outcomes.decision_id through outcomes_decision_id_idx and compares it with decisions.id. Search results are ranked by the formula before the top-k cutoff. On large HNSW-backed indexes, this ranking applies to the vector candidates returned by ANN retrieval; use a single current summary row on the joined side when outcome ranking must be deterministic.

Rank policies are schema. To change a policy today, recreate the affected table with the new RANK_POLICY clause and reload the rows:

DROP TABLE decisions;

CREATE TABLE decisions (
  id UUID PRIMARY KEY,
  description TEXT NOT NULL,
  confidence REAL,
  embedding VECTOR(2) RANK_POLICY (
    JOIN outcomes ON decision_id,
    FORMULA 'coalesce({vector_score}, 0.0) * coalesce({confidence}, 1.0) * coalesce({success}, 1.0)',
    SORT_KEY effective_confidence
  )
);

Persist to Disk

Replace :memory: with a file path. Everything else works the same:

contextdb-cli ./my.db

Single file. Crash-safe via redb. Reopen and your data is there.

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"

What’s Next

• Why contextdb? — the problems it solves and how it compares to alternatives
• Usage Scenarios — 16 problem-first walkthroughs with SQL
• Query Language — full SQL, graph, and vector reference
• CLI Reference — REPL commands, sync, scripting

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.

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

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

CREATE TABLE scratch (...) RETAIN 24 HOURS
CREATE TABLE logs (...) RETAIN 90 DAYS SYNC SAFE

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
• Laptops, ARM64 devices (browser and mobile via Rust’s WASM target are future directions)

Next Steps

• Getting Started — build and run in 2 minutes
• Usage Scenarios — 16 problem-first walkthroughs
• Architecture — how it works under the hood

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.

Example Use Cases

Before diving into SQL, here are concrete scenarios where these problems surface:

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

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

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
UPDATE decisions SET status = 'superseded' WHERE id = $id;
-- Error: InvalidStateTransition { from: "draft", to: "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.

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 all decisions that were BASED_ON this entity
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'

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.

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.

Scenario 7: Graph Neighborhood + Vector Search

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
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
contextdb-server --tenant-id production --db-path ./server.db

# Instance 1 (laptop app) — works offline, pushes when connected
contextdb-cli ./local1.db --tenant-id production
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: 2 applied, 0 skipped, 0 conflicts

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

Sync is bidirectional, per-table configurable:

-- This table only pushes (observations flow up, never down)
ALTER TABLE observations SET SYNC_CONFLICT_POLICY 'insert_if_not_exists'

contextdb> .sync direction observations Push
contextdb> .sync direction decisions Both
contextdb> .sync direction scratch None

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 use WebSocket transport (ws://) to connect to the NATS server. This is deliberate — WebSocket connections traverse NAT and firewalls without hole-punching, so a laptop behind a home router, a browser plugin, or a mobile app can all sync without network configuration.

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) are automatically chunked below NATS’s message limit and reassembled on the receiver.

In Rust:

#![allow(unused)]
fn main() {
use contextdb_server::SyncClient;

let client = SyncClient::new(db.clone(), "ws://nats:9222", "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.

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

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.

Annotated-Corpus Semantic Search

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:

ALTER TABLE observations ADD COLUMN confidence REAL;
ALTER TABLE entities RENAME COLUMN entity_type TO kind;
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 combines all of the above:

-- Isolation boundary
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 schema gives an agent:

• Structured state — entities with typed properties, decisions with enforced lifecycles
• Semantic recall — vector search across observations, decisions, and digests
• Provenance — graph traversal traces what was based on what, who cited whom
• Automatic invalidation — state changes cascade through the graph, with first-class invalidation records
• Feedback loop — outcomes record whether decisions worked, weighting future precedent search
• Intent normalization — blueprints collapse diverse expressions into canonical patterns
• Sync — local instances work offline, push/pull when connected
• Retention — old observations expire, but not before syncing

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 constraints for agentic memory workloads: IMMUTABLE, STATE MACHINE, DAG, RETAIN, and PROPAGATE. These are contextdb-specific extensions — everything else should feel familiar if you’ve used PostgreSQL.

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

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

See Table Options for IMMUTABLE, STATE MACHINE, DAG, RETAIN, and PROPAGATE.

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 SYNC_CONFLICT_POLICY 'latest_wins'
ALTER TABLE t DROP SYNC_CONFLICT_POLICY

DROP TABLE

DROP TABLE t

CREATE INDEX

CREATE INDEX idx_name ON t (col)

INSERT

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

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

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

UPDATE / DELETE

UPDATE decisions 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]

CTEs

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

Multiple CTEs via comma separation. Non-recursive only.

Transactions

BEGIN
-- statements
COMMIT

-- or
ROLLBACK

Configuration

SET SYNC_CONFLICT_POLICY 'latest_wins'
SHOW SYNC_CONFLICT_POLICY
SET MEMORY_LIMIT '512M'
SHOW MEMORY_LIMIT
SET DISK_LIMIT '1G'
SET DISK_LIMIT 'none'
SHOW DISK_LIMIT

SHOW MEMORY_LIMIT returns limit, used, available, and startup_ceiling.

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

Column Types

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

Vector columns can choose per-index scalar quantization:

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

Valid quantization values are F32, SQ8, and SQ4; the default is F32. Each vector column is a separate (table, column) index. Search routes to the column named in ORDER BY:

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

Inspect registered vector indexes with:

SHOW VECTOR_INDEXES;

It returns table, column, dimension, quantization, vector_count, and bytes.

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 outcomes (
  id UUID PRIMARY KEY,
  decision_id UUID NOT NULL,
  success BOOLEAN NOT NULL
);

CREATE INDEX outcomes_decision_id_idx ON outcomes(decision_id);

CREATE TABLE decisions (
  id UUID PRIMARY KEY,
  description 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
  )
);

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

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

Without USE RANK, the same query returns cosine ordering:

SELECT id, description
FROM decisions
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 decisions
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:

• On large HNSW-backed vector indexes, rank policies rank the ANN candidate set returned by vector retrieval before applying the final top-k. They do not force an exhaustive scan of every row in the corpus. If a formula does not reference {vector_score}, use a larger search limit or an exact workflow when cosine is a weak candidate generator for the metric being optimized.
• 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

CREATE TABLE decisions (
  id UUID PRIMARY KEY,
  description TEXT NOT NULL,
  status TEXT NOT NULL,
  confidence REAL DEFAULT 0.0,
  email TEXT UNIQUE,
  intention_id UUID REFERENCES intentions(id)
)

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 decisions (
  id UUID PRIMARY KEY,
  decision_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])

decision_type and description are provenance — set once at INSERT and never rewritten. status and confidence remain mutable. An UPDATE decisions SET decision_type = '…' returns Error::ImmutableColumn; the row is unchanged. Sync-apply across a NATS 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 decision turns out to be wrong, insert a new row with the corrected values and mark the original superseded:

-- Original (frozen)
INSERT INTO decisions (id, decision_type, description, status)
VALUES ('…A', 'sql-migration', 'adopt contextdb', 'active');

-- Correction: a new row, not an update. Both rows remain queryable.
INSERT INTO decisions (id, decision_type, description, status)
VALUES ('…B', 'sql-migration', 'adopt contextdb (rev 2)', 'active');
UPDATE decisions SET status = 'superseded' WHERE id = '…A';

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

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 a silent no-op — the second INSERT returns Ok(rows_affected=0) and the row count is unchanged, making agent operations idempotent. 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.

Foreign Key State Propagation

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

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

When an intentions row transitions to archived, any decisions row referencing it transitions to invalidated.

Table Options

Table options appear after the closing ) of the column list. Multiple options can be combined.

IMMUTABLE

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

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

STATE MACHINE

Restrict a column’s value transitions to declared edges:

CREATE TABLE decisions (
  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 delays purging until synced:

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

PROPAGATE ON EDGE

Cascade state changes along graph edges when a row transitions:

CREATE TABLE decisions (
  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 decisions 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 decisions (...)
  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 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
  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

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

Parameter Binding

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

#![allow(unused)]
fn main() {
let mut params = HashMap::new();
params.insert("entity_id".into(), Value::Uuid(id));
params.insert("type".into(), Value::Text("sensor".into()));

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

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

Graph Traversal

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

Syntax

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

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

Patterns

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

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

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

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

Variable-Length Paths

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

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

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

Filtering and Projection

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

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

Composing with SQL via CTEs

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

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

Graph + Vector: Neighborhood Similarity Search

Find semantically similar entities 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 entities e
  INNER JOIN neighborhood n ON e.id = n.b_id
  WHERE e.is_deprecated = FALSE
)
SELECT id, name FROM candidates
ORDER BY embedding <=> $query
LIMIT 5

Vector Similarity Search

The <=> Operator

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

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

-- CLI with vector literal
SELECT id, data FROM observations
ORDER BY embedding <=> [0.1, 0.2, 0.3]
LIMIT 10

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

Pre-Filtered Search

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

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

Indexing

The engine automatically selects the search strategy based on vector count:

• Below ~1000 vectors: brute-force linear scan (exact)
• At/above ~1000 vectors: HNSW approximate nearest neighbors (recall target >= 95%)

No manual index creation needed. Use .explain in the CLI to see which strategy is active:

contextdb> .explain SELECT id FROM observations ORDER BY embedding <=> $q LIMIT 5
HNSWSearch { table: "observations", limit: 5 }

SQL Comments

Both styles are stripped before parsing:

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

Unsupported Features

These are explicitly rejected with descriptive error messages:

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 observations (bucket);

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

-- Composite index (leading-column equality + residual filter)
CREATE INDEX idx_entities ON entities (context_id, entity_type, created_at DESC, id DESC);

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

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

DROP INDEX

DROP INDEX idx_bucket ON observations;
DROP INDEX IF EXISTS idx_bucket ON observations;

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

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.

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

Error variants

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-cli (interactive client) and contextdb-server (sync coordinator).

CLI Client (contextdb-cli)

contextdb-cli <PATH> [OPTIONS]

<PATH> is the database file. Use :memory: for an in-memory (ephemeral) database.

Options

Local-Only Mode

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

contextdb-cli :memory:         # ephemeral, lost on exit
contextdb-cli ./my.db          # persisted to file

All sync commands return Sync not configured in this mode.

Sync Mode

To replicate with a server, provide --tenant-id matching the server’s tenant. A tenant is a sync namespace — all clients and the server sharing the same tenant ID replicate with each other.

The --nats-url must point to the NATS WebSocket endpoint (port 9222 by default):

contextdb-cli ./edge.db --tenant-id dev
contextdb-cli ./edge.db --tenant-id production --nats-url ws://nats.example.com:9222

This is the same configuration you’d set in Rust code when constructing a SyncClient — the CLI just exposes it as flags.

Logging

Logs go to stderr so they don’t interfere with query output:

RUST_LOG=debug contextdb-cli :memory:

REPL

On startup the REPL prints a version banner:

contextdb> CREATE TABLE entities (id UUID PRIMARY KEY, name TEXT NOT NULL);
ok (rows_affected=0)

contextdb> INSERT INTO entities VALUES ('550e8400-e29b-41d4-a716-446655440000', 'sensor-1');
ok (rows_affected=1)

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

The REPL accepts SQL statements (see Query Language) and meta-commands.

Runtime budget control is SQL-driven:

• SET MEMORY_LIMIT '512M' / SHOW MEMORY_LIMIT
• SET DISK_LIMIT '1G' / SHOW DISK_LIMIT

For file-backed databases, SET DISK_LIMIT persists in the database file and survives reopen. :memory: accepts the command but ignores it.

Meta-Commands

Trace vs Explain

.explain <sql> is the interactive CLI command — it runs the SQL, formats the physical plan and trace fields, and prints them for a human. It names the strategy (Scan, IndexScan, Sort, etc.), the chosen index name when applicable, any predicates pushed to the index, and whether a Sort node was elided by index-driven ordering.

For machine-readable access, every QueryResult returned by Database::execute carries a trace: QueryTrace field with the same information plus the set of indexes the planner considered and rejected. Agent code and tests assert on QueryResult.trace directly; .explain formats the same fields for terminal inspection.

Sync Commands

All sync commands require --tenant-id at startup. Without it:

Sync not configured. Start with --tenant-id to enable.

Sync directions (case-insensitive): Push, Pull, Both, None

• Push — local writes replicate to server, remote changes ignored
• Pull — remote changes applied locally, local writes not pushed
• Both — bidirectional (default)
• None — table excluded from sync

Conflict policies (case-sensitive): InsertIfNotExists, ServerWins, EdgeWins, LatestWins

• LatestWins — most recent write by logical timestamp wins (default)
• ServerWins — server version always takes precedence
• EdgeWins — client version always takes precedence
• InsertIfNotExists — insert only if the row doesn’t exist; skip otherwise

LSN (Log Sequence Number) is the position in the change log. The push and pull watermarks shown by .sync status tell you how far each direction has progressed — useful for diagnosing sync lag.

Auto-Sync

When enabled (.sync auto on), INSERT/UPDATE/DELETE statements trigger a background push. By default the worker debounces for 500ms so rapid writes are batched, but you can tune that with --sync-debounce-ms or CONTEXTDB_SYNC_DEBOUNCE_MS.

If a background push fails, the CLI now reports the failure to stderr and keeps retrying in the background instead of silently dropping the pending sync.

On exit, the CLI always performs a final push to flush pending changes, regardless of auto-sync setting.

Example: Two-Client Sync

This mirrors what happens when two edge devices sync through a server — the same SyncClient/SyncServer code the CLI uses is what your Rust application would use.

Terminal 1 — start NATS and the server:

docker run -d -p 4222:4222 -p 9222:9222 nats:latest
contextdb-server --tenant-id demo

Terminal 2 — client A creates data and pushes:

contextdb-cli ./a.db --tenant-id demo
contextdb> CREATE TABLE items (id UUID PRIMARY KEY, name TEXT);
contextdb> INSERT INTO items VALUES ('aaa...', 'from client A');
contextdb> .sync push
Pushed: 2 applied, 0 skipped, 0 conflicts

Terminal 3 — client B pulls and sees the data:

contextdb-cli ./b.db --tenant-id demo
contextdb> .sync pull
Pulled: 2 applied, 0 skipped, 0 conflicts
contextdb> SELECT * FROM items;

Server (contextdb-server)

Coordinates sync between edge clients via NATS. In production, your application would run its own server binary or embed SyncServer directly.

contextdb-server --tenant-id <TENANT_ID> [OPTIONS]

Options

The server requires a running NATS instance. Port 4222 is the native NATS protocol (used by the server). Port 9222 is WebSocket (used by CLI clients and edge devices).

docker run -d -p 4222:4222 -p 9222:9222 nats:latest
contextdb-server --tenant-id dev

Default conflict policy is LatestWins. Control log level with RUST_LOG:

RUST_LOG=info contextdb-server --tenant-id dev

Non-Interactive Mode

When stdin is not a terminal, the CLI runs in pipe mode — useful for scripting, CI, and seeding databases:

• No prompt, no version banner
• INSERT statements are echoed to stdout before execution
• Fatal errors (parse errors, missing tables) go to stderr and cause non-zero exit
• Non-fatal runtime errors print to stdout, exit code stays zero

echo "SELECT 1 + 1;" | contextdb-cli :memory:

contextdb-cli ./my.db < schema.sql

echo "SELECT * FROM t;" | contextdb-cli ./my.db && echo "OK" || echo "FAILED"

Error Routing

Architecture

contextdb is a 10-crate Rust 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        Cosine similarity, brute-force + HNSW auto-switch
            │
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 (NATS transport, conflict resolution)
    └── contextdb-cli       Interactive REPL binary

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

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)
• Deduplication: (source_id, target_id, edge_type) is a natural key

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 HNSW payloads; f32 is reconstructed only at API/materialization boundaries
• Below ~1000 vectors: brute-force exact scan
• At/above ~1000 vectors: HNSW (via hnsw_rs) with 10x overfetch + exact reranking
• Pre-filtered search: WHERE clause narrows candidates before scoring
• HNSW is built lazily per index; a search against one vector column does not build sibling indexes
• OOM during HNSW build falls back to brute-force via catch_unwind

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

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:

This trait is the extension point for additional backends if required. The engine owns compute state (in-memory stores, HNSW cache). The applicator owns durability.

Persistence (redb)

Single-file storage via redb:

• Flush-on-commit: every committed WriteSet is written to redb
• On open: all data loaded from redb into memory, HNSW rebuilt
• Crash-safe: redb provides atomic transactions
• 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 legacy stores, while unreadable markers are reported as corrupt.

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

Inject a custom plugin:

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

pre_commit can reject a transaction by returning Err. post_commit fires after the write is durable. Applications like cg and Vigil 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 CONTEXTDB_MEMORY_LIMIT / --memory-limit startup option all feed the same global memory accountant. Vector operations attribute allocations with tags such as [email protected]_text and [email protected]_vision so operators can identify the offending index from errors.

On a 2GB Jetson-class device, prefer SQ8 for high-dimensional evidence:

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 structured per-index counts and live vector payload byte totals, including any materialized HNSW payload estimate; use it instead of parsing memory operation tags.

Sync

The wire protocol is currently PROTOCOL_VERSION = 2. The server prints the supported protocol version in contextdb-server --version and logs it at startup; mismatched envelopes are rejected instead of being partially applied.

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  │
└──────┬───────┘  └──────┬───────┘  └──────┬───────┘
       │ ws://           │ ws://           │ ws://
       │                 │                 │
       └────────┬────────┴────────┬────────┘
                │  NATS (WebSocket :9222)  │
                └────────┬────────────────┘
                         │
                ┌────────┴───────┐
                │  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.

WebSocket transport means clients behind NAT (laptops, mobile, browser) connect outbound to the NATS server — no port forwarding, no VPN, no network configuration.

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

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 via NATS subjects: sync.{tenant_id}.push / sync.{tenant_id}.pull.

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)

Conflict Resolution

Per-table configurable policies:

• LatestWins — most recent write by logical timestamp (default)
• ServerWins — server version takes precedence
• EdgeWins — edge version takes precedence
• InsertIfNotExists — insert if absent, skip otherwise

Transport

NATS with automatic chunking for payloads exceeding the 1MB NATS message limit. Vector byte sizes are accounted for in batch estimation. WebSocket transport for edge clients (port 9222), native protocol for server-to-server (port 4222).

DDL Sync

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

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

MemoryAccountant tracks memory usage against a configurable budget. Set via --memory-limit in the CLI or MemoryAccountant::with_budget(bytes) in the API. All vector and row allocations are accounted. Budget exceeded → operations return MemoryBudgetExceeded.

File-backed databases also support a persisted disk budget:

• startup ceiling/default via --disk-limit or CONTEXTDB_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. Once the on-disk file is at or above the configured limit, further file-backed writes fail with DiskBudgetExceeded. In-memory databases accept the SQL but ignore disk budgeting because there is no backing file to measure.

Benchmarking

ContextDB now has explicit benchmark tiers instead of one undifferentiated suite.

Targets

• cargo bench -p contextdb-parser --bench parser_throughput
  • parser-only microbench
• cargo bench -p contextdb-engine --bench engine_throughput
  • developer smoke bench for local engine behavior, including named-vector index smoke gates under the nv_ filter
• cargo bench -p contextdb-engine --bench engine_full_throughput
  • heavier engine benchmarks for larger write/reopen and mixed workflow paths
• cargo bench -p contextdb-engine --bench mixed_workflows_pr
  • bounded PR-tier mixed graph + relational + vector workflows
• cargo bench -p contextdb-engine --bench subscriptions_pr
  • bounded PR-tier subscription fan-out and backpressure workloads
• cargo bench -p contextdb-server --bench server_throughput
  • CLI smoke bench
• cargo bench -p contextdb-server --bench server_sync_system
  • system sync bench with real NATS via testcontainers
• cargo bench -p contextdb-server --bench sync_pr
  • bounded PR-tier sync workloads using direct sync APIs

Guidance

• Use the parser and engine smoke benches for quick local regression checks.
• Use engine_full_throughput when you want larger local workloads.
• Use mixed_workflows_pr and subscriptions_pr for realistic engine-level PR regression coverage.
• Use sync_pr when you want realistic sync regression coverage without CLI/process overhead in the timed path.
• Use server_sync_system only when you are intentionally testing sync/system behavior.
• The server sync bench is heavier because it uses release binaries, a real NATS container, and file-backed CLI/server processes.

Suggested Commands

• smoke:
  • timeout 180s cargo bench -p contextdb-engine --bench engine_throughput -- --sample-size 10 --measurement-time 0.05 --warm-up-time 0.05
  • timeout 900s cargo bench -p contextdb-engine --bench engine_throughput -- nv_ --save-baseline named-vector-indexes
  • timeout 180s cargo bench -p contextdb-server --bench server_throughput -- --sample-size 10 --measurement-time 0.05 --warm-up-time 0.05
• PR:
  • timeout 180s cargo bench -p contextdb-engine --bench engine_full_throughput -- --sample-size 10 --measurement-time 0.05 --warm-up-time 0.05
  • timeout 180s cargo bench -p contextdb-engine --bench mixed_workflows_pr -- --sample-size 10 --measurement-time 0.05 --warm-up-time 0.05
  • timeout 180s cargo bench -p contextdb-engine --bench subscriptions_pr -- --sample-size 10 --measurement-time 0.05 --warm-up-time 0.05
  • timeout 180s cargo bench -p contextdb-server --bench sync_pr -- chunked_large_pull_600_rows --sample-size 10 --measurement-time 0.05 --warm-up-time 0.05
• system:
  • timeout 180s cargo bench -p contextdb-server --bench server_sync_system -- --sample-size 10 --measurement-time 0.05 --warm-up-time 0.05

Always use timeout on heavier runs. If a benchmark spends the whole run on setup/build noise and never reaches useful Criterion output, treat that as a benchmark-shape problem to fix rather than a valid perf result.

The named-vector nv_ cases use bounded local defaults so they are practical as a smoke gate. Set CONTEXTDB_NV_BENCH_FULL=1 or the per-case CONTEXTDB_NV_* sizing variables in benches/engine_throughput.rs when running the full ship-scale footprint, replay, and latency checks.

Design Rules

• Keep setup outside timed loops unless startup cost is the thing being measured.
• Keep smoke 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.