Feature: Database

Database

Thatch stores all data in a single SQLite file. The schema is organized around feature concerns: memory entries, the prediction engine, the behavior engine, and cross-session chat.

Configuration

• Default path: $XDG_CONFIG_HOME/thatch/thatch.db (override via THATCH_DB_PATH)
• PRAGMAs: journal_mode=WAL, busy_timeout=5000, foreign_keys=ON
• Single file, no external services

Schema overview

The tables fall into these groups:

Group	Tables	Purpose
Memory	stores, entries, dedup_pairs	Core memory CRUD + dedup verdicts
Prediction engine	prediction_matchers, predictions, prediction_edges, prediction_provenance	User decision model
Behavior engine	behavior_matchers, behaviors, behavior_edges, behavior_provenance	LLM self-discipline rules
Cross-session chat	chat_sessions, chat_messages	Session directory + message inbox (shared across opencode processes)
Repo identity	repo_paths	Worktree-to-main-checkout cache for identity recovery after deletion

The prediction and behavior engines share the same four-table shape (matchers, items, edges, provenance) with different table names. They are separate because the semantics differ: predictions model what the user wants; behaviors model what the LLM should do.

Memory tables

stores

Registry of all store names. The global store is inserted automatically at schema init.

stores(name TEXT PRIMARY KEY)

entries

The core table. Memory entries with embeddings, metadata, and usage telemetry.

entries(
  slug TEXT NOT NULL,
  store TEXT NOT NULL REFERENCES stores(name),
  label TEXT NOT NULL,
  content TEXT NOT NULL,
  embedding BLOB,
  model TEXT,
  branch TEXT,
  confidence INTEGER,
  archived INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  recall_count INTEGER NOT NULL DEFAULT 0,
  last_recalled_at TEXT,
  PRIMARY KEY (slug, store)
)

• slug: derived from label via slugify(). Unicode letters/digits preserved. All-symbol labels fall back to hash.
• embedding: raw Float32Array bytes. Null if not yet embedded.
• model: embedding model tag (informational; dimension is the real discriminator).
• branch: git branch scope. Null = project-wide.
• confidence: 1-10 scale. Null = unset.
• archived: 0 = live, 1 = archived. Search, dedup, and staleness queries all exclude archived by default.
• recall_count / last_recalled_at: usage telemetry, stamped by db.recall() (not db.search()).

dedup_pairs

Records which duplicate-candidate pairs have been reviewed.

dedup_pairs(
  store TEXT NOT NULL,
  slug_a TEXT NOT NULL,
  slug_b TEXT NOT NULL,
  status TEXT NOT NULL,
  checked_at TEXT NOT NULL,
  PRIMARY KEY (store, slug_a, slug_b)
)

• Slugs stored sorted (a < b) for canonical matching.
• status: "duplicate", "supplement", "contradiction", or "unrelated".
• Overwriting or forgetting either memory clears the verdict (DELETE in remember() and forgetEntry()).

Prediction engine tables

prediction_matchers

Situation descriptions that trigger predictions. Embedded, cosine-matched at chat.message.

prediction_matchers(
  id TEXT PRIMARY KEY,
  store TEXT NOT NULL REFERENCES stores(name),
  description TEXT NOT NULL,
  embedding BLOB,
  model TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
)

predictions

User preference predictions with Bayesian confidence.

predictions(
  id TEXT PRIMARY KEY,
  store TEXT NOT NULL REFERENCES stores(name),
  statement TEXT NOT NULL,
  rationale TEXT,
  embedding BLOB,
  model TEXT,
  confidence REAL NOT NULL DEFAULT 0.5,
  confirm_count REAL NOT NULL DEFAULT 0,
  disconfirm_count REAL NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
)

• confidence: Bayesian posterior. p = (confirm + K*P0) / (confirm + disconfirm + K), K=5, P0=0.5.
• confirm_count / disconfirm_count: REAL (fractional for soft signals, weight 0.25).

prediction_edges

Many-to-many links between matchers and predictions.

prediction_edges(
  matcher_id TEXT NOT NULL REFERENCES prediction_matchers(id) ON DELETE CASCADE,
  prediction_id TEXT NOT NULL REFERENCES predictions(id) ON DELETE CASCADE,
  weight REAL NOT NULL DEFAULT 1.0,
  created_at TEXT NOT NULL,
  PRIMARY KEY (matcher_id, prediction_id)
)

prediction_provenance

Audit trail of every signal applied to a prediction.

prediction_provenance(
  id TEXT PRIMARY KEY,
  prediction_id TEXT NOT NULL REFERENCES predictions(id) ON DELETE CASCADE,
  signal TEXT NOT NULL,
  detail TEXT,
  created_at TEXT NOT NULL
)

• signal: "confirm", "disconfirm", "soft", or "create".

Behavior engine tables

Same shape as prediction engine, with behavior-specific semantics.

behavior_matchers

behavior_matchers(
  id TEXT PRIMARY KEY,
  store TEXT NOT NULL REFERENCES stores(name),
  description TEXT NOT NULL,
  embedding BLOB,
  model TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
)

behaviors

behaviors(
  id TEXT PRIMARY KEY,
  store TEXT NOT NULL REFERENCES stores(name),
  statement TEXT NOT NULL,
  rationale TEXT,
  embedding BLOB,
  model TEXT,
  confidence REAL NOT NULL DEFAULT 0.5,
  confirm_count REAL NOT NULL DEFAULT 0,
  disconfirm_count REAL NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
)

behavior_edges

behavior_edges(
  matcher_id TEXT NOT NULL REFERENCES behavior_matchers(id) ON DELETE CASCADE,
  behavior_id TEXT NOT NULL REFERENCES behaviors(id) ON DELETE CASCADE,
  weight REAL NOT NULL DEFAULT 1.0,
  created_at TEXT NOT NULL,
  PRIMARY KEY (matcher_id, behavior_id)
)

behavior_provenance

behavior_provenance(
  id TEXT PRIMARY KEY,
  behavior_id TEXT NOT NULL REFERENCES behaviors(id) ON DELETE CASCADE,
  signal TEXT NOT NULL,
  detail TEXT,
  created_at TEXT NOT NULL
)

• signal: "confirm" (ham), "disconfirm" (spam), or "codify".

Cross-session chat tables

chat_sessions

chat_sessions(
  session_id TEXT PRIMARY KEY,
  name TEXT NOT NULL UNIQUE COLLATE NOCASE,
  topic TEXT,
  project TEXT,
  host_kind TEXT NOT NULL DEFAULT 'opencode',
  registered_at TEXT NOT NULL,
  last_seen TEXT NOT NULL,
  auto INTEGER NOT NULL DEFAULT 0,
  worktree TEXT NOT NULL DEFAULT ''
)

• The machine-wide session directory. last_seen is the heartbeat: each host process refreshes it for its own sessions every poll cycle, and a stale value marks a crashed or closed process. Name uniqueness is case-insensitive; databases whose chat_sessions table predates the NOCASE constraint are rebuilt at schema init (first row wins per case group).

chat_messages

chat_messages(
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  from_session TEXT NOT NULL,
  to_session TEXT NOT NULL,
  body TEXT NOT NULL,
  created_at TEXT NOT NULL,
  delivered_at TEXT,
  read_at TEXT,
  via_broadcast INTEGER NOT NULL DEFAULT 0
)

• delivered_at is the last wake-prompt stamp (restamped on re-nudges); read_at is set when the recipient drains its inbox. via_broadcast marks rows a chat_broadcast fan-out created (the CLI tail's sent events carry it as broadcast: true beside the real recipient). The endpoints are plain columns, not foreign keys: unregistering a session must not be blocked by message history, and a departed sender degrades to an unknown name in the reader's view.

See cross-session-chat.md for the delivery model.

repo_paths

repo_paths(
  worktree_path TEXT PRIMARY KEY,
  main_path     TEXT NOT NULL,
  repo_slug     TEXT NOT NULL,
  resolved_at   INTEGER NOT NULL
)

• Records, per worktree directory (realpath-canonicalized key), the main checkout path and the repo identity that detectRepo resolved while the directory was alive. Read after the directory is deleted -- the recovery and validation rules live in repo-identity.md.
• Written by detectRepo on successful remote or common-dir resolution, never from the basename fallback. Rows are evicted only on definitive invalidation (the main checkout exists but is no longer the recorded repo); a missing main checkout keeps the row, since a re-clone may be in progress.

See repo-identity.md for the resolution and eviction rules.

Schema migration

recall_count, last_recalled_at, and archived are added to pre-existing databases by an idempotent column migration at init. The migration uses PRAGMA table_info to check for the column's existence, then ALTER TABLE ADD COLUMN if missing. This handles databases created before these columns existed.

Embedding serialization

Embeddings are raw Float32Array bytes stored as BLOBs. Serialization honors byteOffset/byteLength — transformers.js can return views into larger tensor buffers, and serializing the whole backing buffer corrupts vectors. Always serialize the view's own bytes, not the underlying buffer.

Key invariants

• Single SQLite file, WAL mode, 5s busy timeout, foreign keys ON.
• Embedding spaces are discriminated by vector dimension, not model tag.
• Embedding serialization honors byteOffset/byteLength (views into larger buffers).
• Slugs are slugified labels; slug + store = composite PK.
• Dedup verdicts auto-clear when either memory is overwritten or forgotten.
• Archived memories excluded from search, dedup, and staleness by default.
• Column migration is idempotent (PRAGMA table_info + ALTER TABLE).

Interactions with other features

• Memory store (memory-store.md): entries table is the core; stores table is the registry
• Deduplication (deduplication.md): dedup_pairs table tracks reviewed pairs
• Prediction engine (prediction-engine.md): the prediction tables
• Behavior engine (behavior-engine.md): the behavior tables
• Cross-session chat (cross-session-chat.md): the chat tables, shared across opencode processes
• Hygiene (hygiene.md): staleEntryCount uses recall_count and last_recalled_at; branchesInStore uses branch column

Source files

• src/db.ts — all schema, CRUD, search, scoring, migrations