SQL Memory for agents. The third facet of the Memory primitive.

2026-06-30 · by Dennis Gubsky · ~11 min read

The Memory primitive shipped at v0.8.0 as a key-value store with TTL and atomic increments. v0.9.0 added the vector facet (sqlite-vec, pgvector, provider-agnostic embedders). For a couple of weeks that's been the shape: K/V for state, vectors for semantic search.

It wasn't enough. The use case that kept surfacing in JobEmber.ai's production agents and in every other real loomcycle deployment was the one neither facet covers: related tables with joins and aggregates. The agent that ingests 500 prime numbers and needs them stored in a way it can query later with SELECT MAX(n) FROM primes WHERE n < 100. The agent that builds a normalized denylist with (domain, reason, expires_at) rows and joins it against the inbound webhook table. The agent that wants ranked semantic search alongside structured filters (WHERE lang='en' ORDER BY embedding <=> ?). None of those fit cleanly in K/V; none fit cleanly in pure vector storage.

The workaround was Bash + sqlite3. Spawn a subprocess. Spin up a database file in some operator-supplied directory. Hope the agent doesn't escape the path-confinement. Forget that the Bash tool was restricted-not-isolated all along.

Today's v1.2.0 closes that gap.

Below: the shape of the new facet, the security posture, the exp9 demonstration, and what each phase added.

Two new ops on the Memory tool. No new tool.

SQL Memory is a facet of Memory, not a new tool. Agents call into it via two ops on the same Memory tool they were already using for K/V and vector:

// DDL or DML
{"tool":"memory",
 "input":{"op":"sql_exec",
         "scope":"user",
         "sql":"CREATE TABLE primes (n INTEGER PRIMARY KEY)"}}

// Read-only SELECT, structured response
{"tool":"memory",
 "input":{"op":"sql_query",
         "scope":"user",
         "sql":"SELECT MAX(n) FROM primes WHERE n < ?",
         "args":[100]}}

Same MCP wire surface as the existing Memory ops. Same scope vocabulary. Bind params via args. Structured response with a rows array and truncated flag (row caps enforced). No new tool to opt into, no new wire shape; the surface fits exactly where agents already are.

Scopes: durable + ephemeral

Three scopes, matching the rest of the Memory primitive:

• agent: durable, keyed by the authoritative tenant + agent ID. Persists across runs. One database per agent definition.
• user: durable, keyed by the authoritative tenant + user ID. Persists across runs. One database per end-user. Shared across agents acting for the same user.
• run: ephemeral. One database per spawn tree (keyed by RootRunID). Created on first use, dropped at run completion with fenced removal (mirrors the RFC AH ephemeral-volume pattern).

A coding agent and a validator agent on different runs but with the same user_id share the same database. They see each other's tables. That's how exp9 works.

Default-deny `sql_scopes` ACL

Having Memory in allowed_tools isn't enough. The agent needs an explicit sql_scopes list naming which scopes it can write SQL into:

agents:
  exp9-coder:
    allowed_tools: [Bash, Memory, Channel]
    memory_scopes: [user]      # K/V + vector facets, unchanged
    sql_scopes: [user]         # SQL facet, explicit opt-in

Empty sql_scopes means every SQL op refuses (with a boot warning if storage.sqlmem_enabled is on). This mirrors the memory_scopes + volume default-deny patterns from RFC W and RFC AH. Operator config is the floor; agents can't widen it.

The security posture (the design crux)

Letting agents write arbitrary SQL is a trust-boundary problem. The runtime has to defend three things: agents must not escape their scope's database, must not run dangerous SQL constructs, and must not exhaust resources.

Per-scope file/schema isolation

The primary defense is structural. One database per scope.

On the sqlite tier, each scope gets its own .db file under an operator-blessed directory. Path derivation sanitizes every identifier (no .., no separators, collision-safe via tenant + scope + scope-id hashing). An agent operating in scope (tenant=acme, scope=user, scope_id=alice) opens a different file than an agent operating in (tenant=acme, scope=user, scope_id=bob). A missed escape in one would still leave the other untouchable; the kernel's filesystem permissions are the floor.

On the postgres tier (Phase 2), each scope gets its own schema in a separate aux database, accessed through a per-scope least-privilege LOGIN role. The role's search_path points only at its own schema and any read-only shared schemas the operator declared. Cross-scope access is refused at the database engine layer, not at the application layer.

The Go-layer statement validator

The default sqlite driver (modernc.org/sqlite) has no SQL authorizer interface; the kernel hook other drivers expose isn't available without a cgo build. The primary, driver-agnostic defense is a Go-layer parsed-statement validator that refuses:

• ATTACH / DETACH (file escape)
• VACUUM / VACUUM INTO (file write outside the scope DB)
• PRAGMA (arbitrary engine reconfiguration)
• load_extension(...) including quoted-identifier forms (latent RCE vector on a future cgo build)
• Multi-statement smuggling (; DELETE ... trailing a SELECT)
• Any write inside an sql_query op (the read-only one)

The validator runs before the statement reaches the driver. Two adversarial reviews surfaced the quoted-load_extension bypass during Phase 1 implementation; the fix went in before the merge.

Resource limits

Per-statement timeout (default tunable, enforced via context cancellation that modernc honors with an interrupt). Per-scope size quota (page count × page size on sqlite; pg_total_relation_size on postgres) checked before every write. Row cap on query results with a truncated flag for the agent to see.

Audit

Every SQL op records actor + scope + op + rows + duration + the redacted statement text (through the existing RFC Z redactor, so secret values inlined into queries don't land in the audit log). Two modes: full captures the redacted SQL; metadata captures only the shape (op + table-touched + row count). Best-effort, never blocks the op.

exp9: prime stream → SQL → channel → validator agent

The demonstration. examples/exp9-prime-sql-memory/ ships as a self-contained directory. Two agents, one Python sieve script, two POSTs from the operator.

The topology, in one block:

operator POST /v1/runs agent=exp9-coder user_id=exp9
  └─ Bash: python3 ../primes.py 500 → parse stdout
  └─ Memory sql_exec scope=user: CREATE TABLE primes (n INTEGER PRIMARY KEY)
  └─ Memory sql_exec scope=user: INSERT INTO primes ...   (batch, one statement)
  └─ Channel op=publish exp9-primes-done {"count":N,"max":P}

operator POST /v1/runs agent=exp9-validator user_id=exp9
  └─ Channel op=subscribe exp9-primes-done       (waits up to 5 min)
  └─ Memory sql_query scope=user: SELECT n FROM primes ORDER BY n
  └─ Bash: python3 inline trial-division over the list
  └─ Memory sql_exec scope=user: CREATE TABLE prime_check (n INT, valid INT)
  └─ Memory sql_exec scope=user: INSERT INTO prime_check ...
  └─ Memory sql_query scope=user: SELECT COUNT(*) FROM prime_check WHERE valid=0
  └─ Report: VALIDATED N primes. Invalid: K. All correct: yes/no

Two separate runs. Two agents. The shared key is user_id=exp9 on both. That's what routes them to the same user-scoped SQL database (scope="user", scope_id="exp9"). The coder writes; the validator reads from the same table; the validator writes a second table the coder never sees.

Three things this proves:

1. Cross-run state sharing works. Agent A writes a table; Agent B reads it later via the same scope key. No global state, no shared mutable channel, no Bash + sqlite3 wrapper. The substrate primitive does it.
2. The channel + SQL composition is the right shape. The validator subscribes to the channel and only queries the SQL table once the publisher confirms it's ready. No polling, no race. Channel for synchronization, SQL for the data.
3. The validator finds zero invalid primes. The coder's sieve output round-trips through SQL and back through Bash without corruption, and the trial-division verdict matches across both. The substrate is honest.

What Phases 3a through 3g added on top

Phase 1 was the floor: storage + the validator + scopes + audit. Phase 2 added the postgres tier. The 3a-through-3g phases that followed were polish, but each one closed a real use case Phase 1 couldn't cover.

Phase 3a — explicit transactions

Three new ops: sql_begin, sql_commit, sql_rollback. Atomic multi-step writes. The runtime owns the transaction (the validator still refuses agent-issued BEGIN); the agent just calls the dedicated ops.

Cleanup is the load-bearing detail. Explicit commit/rollback works as expected. Run-end auto-rollback hooks into the run-completion purge before the run-scope drop. A TTL reaper (sqlmem_txn_timeout_ms, default 30s) rolls back transactions an agent forgot to close. A held connection plus locks never leak past a stuck agent.

Phase 3b — nested transactions via SAVEPOINT

A second sql_begin while a transaction is open nests via SAVEPOINT instead of erroring. The agent doesn't manage savepoint names; the runtime does. A nested sql_rollback undoes only the inner level; the outer transaction continues. Each op's result reports the current depth (1 = root, 0 = closed). LIFO, capped at sqlmem_max_txn_depth (default 16). Works on both tiers.

Phase 3c — vector columns (postgres tier)

Agents keep embeddings inside their own SQL tables. Semantic KNN and structured filters in one query. The thing K/V plus the main vector Memory can't do.

SELECT id, lang, embedding <=> ? AS distance
FROM articles
WHERE lang = 'en'
ORDER BY embedding <=> ?
LIMIT 10

The agent declares its own vector(N) column and HNSW index. The bind argument {"$embed": "<text>"} is replaced server-side by the embedding of that text, reusing the existing memory.embedder. A multi-KB vector never round-trips through the LLM. The agent writes ordinary SQL and references the embedding by its text content.

Postgres only (pgvector); the sqlite tier returns a typed refusal until cgo-build sqlite-vec lands. The agent can't install the extension (CREATE EXTENSION stays denied); the operator installs pgvector once into a shared read-only sqlmem_ext schema, and the runtime bakes it onto each scope role's search_path.

Phase 3d + 3f.3 — durable-scope GC

agent and user scopes persist across runs. A deployment accumulates one per distinct agent + end-user forever unless something reclaims idle ones. Two complementary sweepers:

• TTL-based (3d): sqlmem_scope_ttl_ms + a sweeper running every sqlmem_gc_interval_ms (default 1h). Drops any durable scope not used for longer than the TTL. Off by default + lossy by contract (set the TTL generously).
• Size-budget-based (3f.3): sqlmem_total_max_bytes caps the aggregate size of all durable scopes. When breached, the sweeper evicts the largest idle scopes until back under budget. Complements TTL (idle-targeting) with bulk-targeting.

In-use scopes (in-flight op, open transaction) are never evicted. run scopes never counted. The sweep reuses the existing fenced-and-owned drop logic from Phase 1, so no new trust surface.

Phase 3e + 3f.2 — snapshot integration

The runtime JSON snapshot now captures SQL Memory. Every durable scope is dumped logically (schema DDL + table data) into an optional, tier-tagged sqlmem envelope section. Restore replays it through the normal provisioned path; a restored scope is identical to one the runtime created. Opt-in (present only when SQL Memory is enabled, so a runtime without it produces a byte-identical pre-3e envelope). Idempotent (a re-restore skips an already-populated table).

Phase 3f.2 added a per-scope cap: sqlmem_snapshot_max_scope_bytes. A scope whose logical dump exceeds it is excluded from the snapshot and recorded in skipped_scopes (restore emits a warning), so one runaway scope can't fail the whole capture or blow the envelope cap.

Phase 3g — read-only shared schemas

Agents are otherwise fully isolated. Phase 3g lets an operator expose curated reference data (lookup tables, taxonomies, configuration) to every agent. The operator loads it into a dedicated schema, GRANT SELECTs it to PUBLIC, and lists the schema in sqlmem_shared_schemas. The runtime bakes it onto every scope role's search_path. Agents can SELECT and JOIN it; they cannot write it (read-only enforced at the postgres engine, the role holds only SELECT).

A scope's own table shadows a same-named shared table (the scope wins). A shared schema is global, cross-tenant; put only non-sensitive reference data there. Generalizes the sqlmem_ext pattern Phase 3c established for pgvector. Postgres tier only (ignored on sqlite, where shared schemas don't have an equivalent mechanism).

Why this is a Memory facet, not a new tool

The framing matters. SQL Memory could have been a new Sql tool with its own MCP surface. It wasn't, for two reasons.

First, the scope vocabulary is the same. An agent that writes to memory_scope=user for K/V should write to the same logical container for SQL. The shared scope means the K/V key and the SQL table belong to the same "user's data," queryable side by side, durable together, snapshot together. Splitting them into two tools would force the agent to think about two scope hierarchies and risk inconsistency between them.

Second, the trust posture is the same. Same per-scope isolation, same default-deny ACL, same audit log, same redactor. The only difference is the wire shape of what gets written. Putting that under one tool keeps the trust-boundary discipline obvious and the operator's mental model small.

The cost: two more ops on a Memory tool that now has eight ops (get, set, delete, list-scopes, search, sql_exec, sql_query, sql_begin, sql_commit, sql_rollback). The MCP tool description got longer. That's acceptable.

How to actually use it

# Upgrade
brew upgrade loomcycle           # macOS / Linux
docker pull denngubsky/loomcycle:1.2.0

# Enable the SQL facet (additive + off by default)
# Set in loomcycle.yaml:
#   storage:
#     sqlmem_enabled: true
#     sqlmem_root: /work/sqlmem             # sqlite tier
#     # OR for the postgres tier:
#     # sqlmem_pg_dsn: postgres://...
#
# Per-agent opt-in:
# agents:
#   my-agent:
#     allowed_tools: [Memory, ...]
#     sql_scopes: [user, run]

# Try exp9 directly
git clone https://github.com/denn-gubsky/loomcycle
cd loomcycle/examples/exp9-prime-sql-memory
./run.sh serve
# in another terminal:
python3 work/exp9_run.py

Full release notes in REVISIONS.md covering all eight phases (1, 2, 3a, 3b, 3c, 3d, 3e, 3f.2, 3f.3, 3g). The TS and Python adapters are unchanged at 1.1.1. SQL Memory rides on the existing Memory MCP surface, so no new wire-shape RPCs to generate. @loomcycle/[email protected] already works against v1.2.0.

No breaking changes. The whole subsystem is additive and off-by-default; a deployment that doesn't set storage.sqlmem_enabled sees zero behavior change. v1.0 and v1.1.x users upgrade safely.

Companion reading: v1.0 — the substrate-complete release (where Memory had two facets and SQL was a roadmap item), v1.1.0 — Filesystem Volumes (where the ephemeral-scope discipline that Phase 1 here reused was first proven), v1.1.1 — interactive sessions on every adapter (the previous release).