Every agent gets its own brain: a seven-database SQLite architecture

Agentz is a desktop app for creating AI agents. Every agent you create gets its own directory on disk, and inside that directory live seven SQLite databases:

agents/
  {agentId}/
    conversations.db   # messages, scheduled jobs, tasks, reminders, alerts
    graph.db           # knowledge graph nodes and edges
    semantic.db        # facts, beliefs, episodic memories, embeddings
    documents.db       # document storage + chunks for RAG
    code.db            # code library, execution history, code jobs
    data.db            # tables the agent creates for itself
    ledger.db          # financial accounts, transactions, categories
    assets/            # generated files: charts, CSVs, images

Not seven tables. Seven databases, per agent. This post is about why that's the right shape for agent state, and what it cost to get there.

A decision made on day four

Four days after the project's first commit, each agent had a single database that was already becoming a junk drawer. The first split — conversations, graph, and memory — happened on 23 December, before there was much data to migrate, which is exactly when you want to make this kind of call.

The reasoning was less about performance than about lifecycle. Different kinds of agent state change at different rates and matter in different ways:

Conversations are append-heavy, occasionally compacted, and the thing you'd most regret corrupting.
The knowledge graph gets rewritten constantly as the agent reorganises what it knows.
Memory is written by the agent itself, via tools, with schemas the app controls.
Agent-created data (data.db) has schemas the app doesn't control — the agent can call a data_create_table tool and invent whatever tables it likes. You really don't want an LLM issuing DDL in the same file as your message history.

That last point is the strongest argument for the whole design. One database per concern means one blast radius per concern. When an agent mangles a table it created for itself, the damage stops at the wall of data.db.

What actually lives in each database

The interesting one is semantic.db, which holds the memory model. Memory in Agentz isn't one table of "things the agent remembers" — it distinguishes three kinds of knowing:

Facts are subject–predicate–object triples with a confidence and a validity window:

CREATE TABLE IF NOT EXISTS facts (
  id TEXT PRIMARY KEY,
  subject TEXT NOT NULL,
  predicate TEXT NOT NULL,
  object TEXT NOT NULL,
  confidence REAL DEFAULT 1.0,
  source TEXT,
  valid_from INTEGER,
  valid_until INTEGER,
  ...
)

Beliefs are uncertain statements that accumulate evidence, track what they contradict, and get reinforced over time:

CREATE TABLE IF NOT EXISTS beliefs (
  id TEXT PRIMARY KEY,
  statement TEXT NOT NULL,
  confidence REAL DEFAULT 0.5,
  evidence TEXT DEFAULT '[]',
  contradicts TEXT DEFAULT '[]',
  last_reinforced INTEGER,
  reinforcement_count INTEGER DEFAULT 0,
  ...
)

Episodic memories are events, and yes, they have feelings:

CREATE TABLE IF NOT EXISTS episodic_memories (
  id TEXT PRIMARY KEY,
  event_type TEXT NOT NULL,
  summary TEXT NOT NULL,
  participants TEXT DEFAULT '[]',
  emotional_valence REAL DEFAULT 0.0,
  importance REAL DEFAULT 0.5,
  timestamp INTEGER NOT NULL,
  ...
)

emotional_valence started as a slightly whimsical field and turned out to be genuinely useful: it drives the agent's orb animation in the UI, and it gives memory consolidation a signal for what's worth keeping. All three types feed a unified search index with vector embeddings — generated locally via @xenova/transformers by default, or via OpenAI if configured — so "what do I know about X" is one semantic query across facts, beliefs, and episodes.

Alongside memory, documents.db holds ingested PDFs and DOCX files chunked for RAG, code.db keeps every snippet the agent has saved and every sandbox execution it has run, and ledger.db does double-entry-ish bookkeeping with accounts, categories, and VAT-aware transactions.

Why isolation wins

The per-agent, per-concern layout has paid for itself in ways I didn't fully anticipate in December:

Deletion is rm -rf. Deleting an agent removes one directory. No cascading foreign keys across a shared database, no orphaned rows, no "are we sure this WHERE clause only touches agent 7" moments.

Portability is cp. An agent is a folder. Back it up, move it to another machine, or export its assets when you delete it — the app has an option for exactly that.

The SQL browser is safe to expose. Agentz ships a built-in database browser, and agents themselves get SQL tools. Because connections are opened per database, an agent poking at its own data.db physically cannot touch another agent's conversations.

Privacy boundaries are real. Cross-agent visibility is an explicit opt-in setting, implemented as "open the other agent's files", not as "remember to filter by agent_id everywhere".

The cost side of the ledger: seven connections per agent (better-sqlite3 is synchronous, so this is cheap in practice), no cross-database joins (in four months this has hurt approximately never — the concerns really are separate), and seven schema initializers to keep in order.

Migrations without a framework

There is no migrations framework and, despite what an early draft of the project docs claimed, not even a user_version counter. Every schema initializer is idempotent: CREATE TABLE IF NOT EXISTS for structure, PRAGMA table_info(...) to detect missing columns before ALTER TABLE, and sqlite_master queries to decide whether a legacy table still needs copying. The database's actual state is the version. It's unfashionable and it has been dramatically less trouble than every migration framework I've used — partly because each database is small and single-concern, so each initializer stays readable.

Which is not to say this week was painless.

This week's war stories

The original memory database was doing three jobs — semantic memory, documents, and code — and this week it finally got split into semantic.db, documents.db, and code.db, at the same time as moving from flat {agentId}_conversations.db filenames to the directory-per-agent layout. Migrating live data across both changes at once produced three days of commits that read like a confession:

"Fix migration to handle legacy memory.db and orphan files" — flat-layout files that matched no known suffix now get swept into an _orphans/ directory instead of being silently abandoned.
"Copy documents from legacy memory.db during migration" — documents lived in the pre-split memory.db and simply weren't being copied. Three agents needed their documents manually restored.
"Copy all legacy memory.db data during migration" — the full list of what the first migration pass missed: facts, beliefs, episodes, key-value data, assets, the search index, and code executions.

The lesson from all three is the same: when you split a database, enumerate what you're migrating from the old schema's table list, not from memory of what you think is in there. The final version of the migration literally queries sqlite_master on the legacy file and walks the result — the approach the very first fix should have taken.

The other lesson is about INSERT OR IGNORE versus INSERT OR REPLACE. An earlier document migration used OR IGNORE, which meant rows that had already been copied in a half-finished earlier run — without their thumbnails — could never be repaired by re-running it. Idempotent migrations need to be idempotent toward the correct state, not merely safe to re-run.

The scorecard

Four months in, the day-four decision looks like this: the split has never once been the thing slowing a feature down, and it has repeatedly been the thing that made a scary change safe — this week's memory split touched three databases and zero conversations. If you're building anything agent-shaped: give each agent its own files, split them by concern earlier than feels necessary, and treat the migration code as the most important code you'll write that month. It is.