#Databases

Four SQLite files, node:sqlite — stdlib on Node 26, so no dependency, no process, no port. Why they are four and not one is in What is stored.

node:sqlite is fully synchronous, so every write blocks the event loop. Batch inserts in a transaction and keep writes off the streaming path — persistence happens on finish, not per delta. Pragmas are applied on every open: WAL persists, the rest do not, and foreign_keys silently defaults to off.

#audit.db — the feedback loop on tool design

The field that makes this worth querying is failure_kind. It separates Nextcloud refused — the system working exactly as designed — from the model could not use the tool, which means the ACI is wrong and should change.

CREATE TABLE calls (
  id INTEGER PRIMARY KEY, ts INTEGER NOT NULL, uid TEXT NOT NULL,
  conversation_id TEXT NOT NULL, turn_id INTEGER, step INTEGER,
  tool TEXT NOT NULL, server TEXT NOT NULL,
  category TEXT NOT NULL CHECK (category IN ('data','action')),
  args_json TEXT NOT NULL, ok INTEGER NOT NULL,
  error TEXT,
  failure_kind TEXT NOT NULL CHECK (failure_kind IN
    ('none','permission','not_found','invalid_input',
     'upstream','timeout','denied_by_user','internal')),
  approved_by_user INTEGER NOT NULL DEFAULT 0,
  duration_ms INTEGER NOT NULL
);

Read it this way:

Useful queries:

-- Which tools does the model struggle to call correctly?
SELECT tool, failure_kind, COUNT(*) n FROM calls
WHERE ok = 0 AND failure_kind IN ('not_found','invalid_input')
GROUP BY tool, failure_kind ORDER BY n DESC;

-- Token cost per turn — the number every context threshold depends on.
SELECT model, AVG(total_tokens), MAX(total_tokens), AVG(steps) FROM turns;

-- Which actions ran without a confirmation? (i.e. were not gated by G3)
SELECT tool, COUNT(*) n FROM calls
WHERE category = 'action' AND approved_by_user = 0 GROUP BY tool;

approved_by_user is derived, not parsed: it is true exactly when G3 gated the call. The reasoning is that the SDK refuses to execute a needsApproval tool without an approval response it has HMAC-verified against that exact call — so for such a tool, reaching the audit hook is the user having confirmed it. Reading the approval part back out of the message history would be two hops through ModelMessages for a fact the structure already guarantees.

Read the query above accordingly: a 0 on an action means the tool was policy-exempt (annotated idempotentHint, so G3 let it through), not that a confirmation was skipped. An unknown tool defaults to 1, so a gap in the meta map never shows up as a missing confirmation.

turns records tokens, steps, duration, finish reason, and whether the turn was compacted or pruned. tool_fingerprints stores a digest per tool definition, so a definition that changes under us forces needsApproval: true and logs loudly — a tool contract moving silently is a supply-chain problem.

#chat.db — stored faithfully, sent selectively

Two rules keep this honest.

parts_json holds UIMessage.parts verbatim. The UI form is the stored form; ModelMessage is derived on the way out, never the other way round. Converting on write loses information you cannot get back.

pruned is a display hint only. What reaches the model is decided by pruneMessages inside prepareStep, recomputed every turn so it stays deterministic. Letting a stored flag drive the model context would create two sources of truth that disagree.

CREATE TABLE conversations (id TEXT PRIMARY KEY, uid TEXT NOT NULL, title TEXT, …) WITHOUT ROWID;
CREATE TABLE messages (
  id TEXT PRIMARY KEY,
  conversation_id TEXT NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
  seq INTEGER NOT NULL, role TEXT NOT NULL,
  parts_json TEXT NOT NULL, metadata_json TEXT,
  created_at INTEGER NOT NULL, pruned INTEGER NOT NULL DEFAULT 0
) WITHOUT ROWID;
CREATE TABLE summaries (…, conversation_id …, up_to_seq INTEGER NOT NULL, text TEXT NOT NULL, …);
CREATE UNIQUE INDEX summaries_conv_seq ON summaries(conversation_id, up_to_seq);

The unique index on (conversation_id, up_to_seq) is what makes a summary reused rather than regenerated — the whole point of rung 2 paying once per compaction event. The title is the first user message, truncated: cheap, and no extra model call.

⚠️ A conversation id comes from the client. Every read and write filters on uid. ensureConversation throws when the id exists under another user — a loud refusal rather than a silent 404, because that is the difference between a bug and an IDOR.

#memory.db — the dangerous one

Read by the model on every turn, which makes it a persistent instruction channel. See the agent for why memory_write is annotated destructive.

CREATE TABLE facts (
  id INTEGER PRIMARY KEY, uid TEXT NOT NULL, ts INTEGER NOT NULL,
  text TEXT NOT NULL, source TEXT NOT NULL CHECK (source IN ('user','agent')),
  conversation_id TEXT, superseded_by INTEGER REFERENCES facts(id)
);
CREATE VIRTUAL TABLE facts_fts USING fts5(
  text, content='facts', content_rowid='id',
  tokenize="unicode61 remove_diacritics 2"   -- so "reunion" finds "réunion"
);

Two traps, both tested, both capable of doing real damage:

FTS5 cannot filter by uid. Every search must join back to facts and filter there:

SELECT f.id, f.text, f.source, f.ts
FROM facts_fts JOIN facts f ON f.id = facts_fts.rowid
WHERE facts_fts MATCH ? AND f.uid = ? AND f.superseded_by IS NULL
ORDER BY bm25(facts_fts) LIMIT ?;

Forget the join and one user reads another's memory. The test asserts both directions, so a passing result cannot be an accident.

User text is FTS5 query syntax. A stray ", -, * or NEAR throws or silently changes the query. toMatchQuery() tokenises and re-quotes each term, OR-joined, so no operator survives. Terms of one character are dropped — they match almost everything.

source exists so everything the agent attributed to itself can be purged separately. superseded_by supersedes rather than deletes, so "my office moved to the 3rd floor" hides the old fact without losing the history.

#Operating on them

docker exec nextcloud-ai-orchestrator-1 ls -la /app/data
docker exec nextcloud-ai-orchestrator-1 node -e "
  const {DatabaseSync}=require('node:sqlite');
  const db=new DatabaseSync('/app/data/audit.db');
  console.log(db.prepare('SELECT tool,ok,failure_kind FROM calls ORDER BY id DESC LIMIT 10').all());
"

-wal and -shm siblings next to each .db are normal. A clean shutdown checkpoints them — index.ts closes the databases on SIGTERM, which is why killing the process mid-write leaves a -wal to recover from.

node:sqlite also exposes Session and backup, so hot backup needs no external tool. Not wired up yet.