Skip to main content
Clawboo persists all of its durable state in one local SQLite file. This page documents every table defined in packages/db/src/schema.ts: purpose, key columns, indexes, and foreign keys, plus how the schema is created and where the file lives. There are 28 tables. They cluster by subsystem: the agent/team registry of record, the durable board, the memory store, the tools broker, governance, the observability event log, the capability inventory, and the team-chat room substrate. The memory_facts_fts FTS5 virtual table (and its shadow tables) are not in the table count; they are raw DDL in createDb, not modellable in schema.ts.
There is no migration ladder. The CREATE TABLE IF NOT EXISTS block in ensureSchema() (packages/db/src/schemaBootstrap.ts) is the sole schema-creation source. schema.ts is the Drizzle type layer over the same tables, used for typed queries, never to apply migrations. Upgrading in place adds columns an older database is missing, derived from that same DDL; changing or removing an existing column is still a reset. See Schema source of truth below.

​
At a glance

Integer columns are epoch-milliseconds when named *_at / *_ms (e.g. created_at, timestamp_ms); boolean-ish flags are stored as INTEGER 0/1 (e.g. is_archived, dropped, enabled, is_error, recovery_tombstone). JSON payloads are stored as TEXT and noted per column.

​
ER diagram (main clusters)

The diagram shows the FK-enforced edges and the load-bearing soft references (board ids are upstream-owned, so most board edges are soft refs by design; only tasks.parent_task_id self-reference and the task_* → tasks edges are FK-enforced).
task_deps, task_comments, workspaces, and execution_processes reference tasks(id) with a real FK. Cross-subsystem references (board team_id/assignee_agent_id, tool_call_approvals.task_id, scheduled_runs.agent_id/team_id, all memory/obs/governance scope columns) are soft refs with no FK constraint; those ids are owned by the Gateway, the chat store, or another source, so the board only references them, never duplicates or constrains them.

​
Registry cluster

​
chat_messages

Persisted transcript entries so chat history survives a page refresh. Keyed internally by an autoincrement id, but deduplicated on the application-supplied entry_id (UUID) for idempotent batch inserts.
  • Columns: id (PK, autoinc), session_key, gateway_url, entry_id, timestamp_ms, data (JSON-serialised TranscriptEntry).
  • Indexes: uniq_chat_messages_entry_id (unique on entry_id), idx_chat_messages_session_ts on (session_key, timestamp_ms), idx_chat_messages_session_id on (session_key, id) (the tail index the live SSE stream range-seeks on, so each poll is O(new rows) per key instead of an O(history) scan + sort).

​
teams

Groups of agents deployed together. Holds team identity (name, emoji icon, color, optional color_collection_id), the optional in-team leader_agent_id, and an is_archived flag.
  • Columns: id (PK, text), name, icon, color, color_collection_id, template_id, leader_agent_id, is_archived (0/1), tenant_id (dormant), created_at, updated_at.
  • Indexes: idx_teams_name on (name).
  • Referenced by: agents.team_id, boo_zero_team_briefs.team_id (cascade).

​
agents

The agent registry of record. An AgentSource (e.g. OpenClawAgentSource) syncs upstream agents INTO this table; SQLite then serves reads so the fleet renders even when the Gateway is down. Columns split into Gateway-synced (overwritten every sync: name, status, identity_json, source_agent_id) and clawboo-native (preserved across re-sync: team_id, personality_config, exec_config, avatar_seed, participant_kind, runtime, capabilities, tenant_id).
  • Columns: id (PK, text), name, gateway_id, avatar_seed, personality_config (JSON slider values), exec_config (JSON { execAsk, execSecurity }), team_id (FK → teams.id), status (default idle), source_id (default openclaw), source_agent_id, identity_json (JSON Gateway identity), participant_kind (default agent; agent|human, dormant), runtime (default openclaw, open set, dormant), capabilities (JSON, dormant), tenant_id (dormant), archived_at (soft-delete tombstone, epoch ms; null = live), created_at, updated_at.
  • Indexes: idx_agents_gateway_id, idx_agents_status, idx_agents_team_id, idx_agents_source on (source_id, source_agent_id).

​
sessions

A dormant seam plus the live session-rotation lineage. For OpenClaw, sessions stay Gateway-live and this table is inert; the native runtime owns its sessions here, and the session-rotation engine records successor sessions linked by parent_session_id.
  • Columns: id (PK, text), source_id (default openclaw), source_session_id, agent_id (soft ref), team_id, status (default idle), parent_session_id (soft self-ref → rotation predecessor), runtime, tenant_id (dormant), created_at, updated_at.
  • Indexes: uniq_sessions_source (unique on (source_id, source_session_id)), idx_sessions_agent, idx_sessions_parent.

​
cost_records

Per-agent token and USD cost ledger.
  • Columns: id (PK, autoinc), agent_id (FK → agents.id), model, input_tokens, output_tokens, cost_usd (REAL), run_id, session_key (nullable), created_at.
  • Indexes: idx_cost_records_agent_id, idx_cost_records_session on (session_key, created_at), idx_cost_records_run_id, idx_cost_records_created_at.
session_key names the conversation a row’s spend belongs to. Billing is seeded from the last recorded spend so a restart does not re-bill a live turn, and without the key that lookup could pick up a different session of the same agent. It is nullable: rows written before the column existed have no answer to give, and POST /api/cost-records, the route the browser estimator posts to, does not send one either. The column and its index are additive, so an existing database gains them from the same DDL with no migration.

​
graph_layouts

Saved Ghost Graph / Atlas node and edge positions, keyed per layout name + gateway URL.
  • Columns: id (PK, autoinc), name (default default), gateway_url, layout_data (JSON node + edge positions), created_at, updated_at.
  • Indexes: uniq_graph_layouts_name_url (unique on (name, gateway_url)).

​
settings

A typed key/value store. Backs team rules, onboarding flags, the Boo Zero global brief / display name, and any other simple config-by-key. (Keys are documented where they are set: see Boo Zero for boo-zero:global-brief and boo-zero:display-name:<agentId>, and Using teams for team-rules:<teamId> and team-onboarding:<teamId>.)
  • Columns: key (PK, text), value (text), updated_at.
  • Helpers: getSetting(db, key) / setSetting(db, key, value) (upsert on conflict).

​
skills

Installed-skill tracking for the legacy markdown-bullet skill model (the brokered tool layer in tool_registry supersedes it; both coexist).
  • Columns: id (PK, text), name, source (free-text provenance, e.g. curated; not an external registry), category, trust_score (REAL, nullable — retained but no longer populated), installed_at, metadata (JSON; carries the agentIds list).
  • Indexes: idx_skills_source, idx_skills_category.

​
team_profiles

Stored team profile templates (JSON agent + skill configs and an optional saved layout).
  • Columns: id (PK, text), name, description, agents_config (JSON), skills_config (JSON), graph_layout (JSON), is_builtin (0/1), created_at.

​
boo_zero_team_briefs

Per-team context briefs that Boo Zero (the universal team leader) reads when operating on a team. One row per team, markdown content, injected into Boo Zero’s context preamble at runtime.
  • Columns: team_id (PK, FK → teams.id ON DELETE CASCADE), content (markdown), updated_at.

​
approval_history

A resolved-exec-approval audit trail (distinct from the live tool-call approval handshake in tool_call_approvals).
  • Columns: id (PK, autoinc), agent_id (FK → agents.id), action (allow_once|always_allow|deny), tool_name, details (JSON), created_at.
  • Indexes: idx_approval_history_agent_id, idx_approval_history_created_at.

​
Board cluster

The transactional source of truth for team/task coordination state. The board references agents/runtimes/sessions/delegations by id but never duplicates them; only the internal parent_task_id self-reference and the task_* → tasks edges are FK-enforced.

​
tasks

The kanban cards.
  • Columns: id (PK, text), title, description, status (default backlog; one of backlog|todo|in_progress|in_review|blocked|done|cancelled), priority (default 0), team_id (soft ref), assignee_agent_id (soft ref), assignee_runtime, parent_task_id (FK → tasks.id self-ref), source_delegation_id, worktree_ref, branch_ref, cost_usd (REAL, default 0), parent_session_id, dropped (0/1 soft-delete), tenant_id (dormant), verification (JSON VerificationResult; null until a gate runs; the in_review → done gate reads .status === 'pass'), scheduled_by (default manual; the one-firing-owner label, open set: manual|clawboo|openclaw|…), created_at, updated_at, completed_at.
  • Indexes: idx_tasks_team_status on (team_id, status), idx_tasks_assignee, idx_tasks_parent, idx_tasks_parent_dropped_created on (parent_task_id, dropped, created_at). The composite serves both guarded-create counts, which run while the write lock is held: the per-parent child count (parent_task_id = ? AND dropped = 0, an index prefix) and the root-rate count (parent_task_id IS NULL AND dropped = 0 AND created_at > ?, equality-equality-range, so the range column comes last). idx_tasks_parent is deliberately retained: dropping it from the DDL would not drop it from databases that already bootstrapped it.

​
task_deps

The blocks / blocked-by dependency graph. The composite primary key prevents duplicate edges.
  • Columns: task_id (FK → tasks.id), depends_on_task_id (FK → tasks.id), tenant_id.
  • Indexes: composite PK (task_id, depends_on_task_id), idx_task_deps_task, idx_task_deps_depends.

​
task_comments

Per-task discussion and system notes (report-up summaries, verification verdicts, board narration).
  • Columns: id (PK, text), task_id (FK → tasks.id), author_agent_id, author_type (agent|user|system), body, tenant_id, created_at.
  • Indexes: idx_task_comments_task.

​
workspaces

Per-task git worktree isolation.
  • Columns: id (PK, text), task_id (FK → tasks.id), repo_path, branch, worktree_path, status (default active; active|archived|stale), tenant_id, created_at, last_used_at.
  • Indexes: idx_workspaces_task.

​
execution_processes

One spawned run per task, for any executor. Records git checkpoints (before/after commit), a token/cost ledger, and the recovery_tombstone that makes startup orphan reconciliation idempotent (no infinite auto-resume).
  • Columns: id (PK, text), task_id (FK → tasks.id), workspace_id (FK → workspaces.id), executor_type (openclaw|claude-code|codex|…), status (default queued; queued|running|succeeded|failed|timed_out|cancelled), claimed_at, started_at, completed_at, before_commit, after_commit, input_tokens, output_tokens, cache_read, cache_write, cost_usd (REAL), summary, run_reason, error, recovery_tombstone (0/1), tenant_id, created_at.
  • Indexes: idx_exec_task, idx_exec_status.

​
scheduled_runs

The Routines ledger: durable team-task schedules, the external wake for every runtime class. The row is the source of truth; the in-process ticker is a rebuildable actuator that re-arms from next_run_at on boot. next_run_at NULL = disarmed (a spent once@, paused, or errored routine).
  • Columns: id (PK, text), agent_id (soft ref), team_id (soft ref), cron_spec (a croner expression or once@<iso>), task_template (JSON TaskTemplate), status (default idle; idle|queued|claimed|running|paused|error), last_run_at, next_run_at, scheduled_by (default clawboo; the firing owner), last_error, tenant_id (dormant), created_at, updated_at.
  • Indexes: idx_scheduled_runs_next on (next_run_at), idx_scheduled_runs_status_next on (status, next_run_at), idx_scheduled_runs_agent.

​
Memory cluster

A two-tier memory store: declarative facts + versioned procedures. Full-text search rides a companion FTS5 virtual table (see FTS5 search); the optional embedding BLOB powers vector / hybrid search.

​
memory_facts

  • Columns: id (PK, text), title, content, tags (JSON string[], default []), embedding (Float32 little-endian BLOB; null when no embedding provider, so search falls back to FTS), embedding_model (the provider id that produced it), scope_agent_id, scope_team_id, tenant_id (dormant), created_at, updated_at.
  • Indexes: idx_memory_facts_team on (scope_team_id), idx_memory_facts_agent on (scope_agent_id), idx_memory_facts_created.

​
memory_procedures

  • Columns: id (PK, text), name, version (default 1), content, scope_agent_id, scope_team_id, tenant_id, created_at.
  • Indexes: idx_memory_procedures_name, idx_memory_procedures_team on (scope_team_id).

​
Tools cluster

The brokered tool layer that supersedes the markdown-bullet skill model. The registry persists descriptor metadata plus the provenance seam (Ed25519 signature verify is real but enforcement is off by default). Every call is audited (args/result scrubbed of secrets); risky calls open a DB-mediated approval the UI resolves.

​
tool_registry

  • Columns: name (PK, text), description, input_schema (serialised JSON Schema), availability (JSON availability requirement), owner (default core; core|plugin|channel|mcp), provenance_signer_id, provenance_signature, provenance_signed_at, enabled (0/1, default 1), created_at, updated_at.
  • Indexes: idx_tool_registry_owner.

​
tool_call_audit

Append-only before/after audit of every brokered call. args_summary / result_summary are scrubbed of secrets (and the result is compacted) before storage; the model still receives the real, unscrubbed output.
  • Columns: id (PK, text), tool_name, agent_id, phase (before|after), decision (allow|deny|require_approval|rewrite, set on the before row), args_summary (scrubbed JSON), result_summary (scrubbed + compacted), is_error (0/1), tenant_id, created_at.
  • Indexes: idx_tool_audit_tool, idx_tool_audit_created.

​
tool_call_approvals

The DB-mediated approval handshake, uniform across both MCP transports and cross-process. task_id lets the TTL reaper unblock a gated board task on expiry; it is nullable because a bare tool-call approval carries no task.
  • Columns: id (PK, text), tool_name, agent_id, args_summary (scrubbed JSON), reason, status (default pending; pending|allow_once|allow_always|deny|expired), task_id (soft ref → tasks), tenant_id, grant_id, connector_id, never_remember (0/1, default 0), rule_reason, tool_class (read|write|destructive), tool_summary, kind (not null, default tool; tool|exec), created_at, expires_at (not null), resolved_at.
  • Indexes: idx_tool_approvals_status, idx_tool_approvals_created, idx_tool_approvals_grant on (grant_id, status).
kind says who is holding the call while the human decides. tool is a call Clawboo is holding in its own process, the broker blocked inside waitForApproval or the native shell waiting on the row it just wrote; exec is an OpenClaw shell command held by the Gateway, which Clawboo mirrors into a row here and answers over exec.approval.resolve. The two are released by different means, so the resolver is told which it is rather than left to infer it from a null connector_id or a tool named exec. A mirrored exec row is keyed by the Gateway’s own request id, which is what makes a redelivered request idempotent, and it carries tool_name exec, tool_class destructive, tool_summary set to the first 200 characters of the command, and args_summary set to {command, cwd}. See Approvals. never_remember is 1 when “Always” must not be offered for this prompt. It is persisted at prompt time rather than recomputed, so the resolve path cannot mint a durable rule the prompt never offered. grant_id and connector_id name the connector grant the call was decided against, and rule_reason is why that decision asked rather than allowed (policy-always, policy-writes, risk-destructive, risk-external, lethal-trifecta, tainted-run, never-remembered). tool_class and tool_summary carry the server’s own reading of the tool, so the card never has to guess a risk level from the tool’s name.

​
Governance cluster

The hard USD budget kill-switch plus an append-only forensic audit.

​
budgets

Scoped budgets with cent-exact integer spend, so the atomic read-modify-write never drifts. spent_micro_cents is a lossless accumulator in ten-thousandths of a cent (sub-cent cost events carry here so repeated tiny amounts are not floored to zero); spent_usd_cents is the whole-cent display mirror = floor(micro / 10000).
  • Columns: id (PK, text), scope (agent|mission|team|tenant), scope_id, limit_usd_cents (INTEGER), spent_usd_cents (default 0), spent_micro_cents (default 0), status (default active; active|soft_capped|paused), mode (default warn; cap = hard cap auto-pause at 100%, warn = track-and-warn, never auto-pause), tenant_id, created_at, updated_at.
  • Indexes: uniq_budgets_scope (unique on (scope, scope_id)), idx_budgets_status.
The default mode is warn (track-and-warn): spend is recorded and a warning is emitted at the 80% / 100% crossings, but the run is never auto-paused. A hard cap (mode: 'cap') is opt-in. See Production defaults.

​
governance_audit

Insert-only forensic governance log (no update/delete writer), secrets scrubbed before storage, indexed by (agent_id, created_at) for lineage queries.
  • Columns: id (PK, text), event_type (install|approval|tool_call|budget|cap_hit|verification), agent_id, task_id, team_id, tenant_id, summary (scrubbed JSON), created_at.
  • Indexes: idx_gov_audit_agent on (agent_id, created_at), idx_gov_audit_created.

​
Observability cluster

​
orchestration_events

The append-only orchestration event stream: the always-on local trace store (a trace = events sharing a trace_id, ordered by seq), the Ghost-Graph projection source, and the metric + error-taxonomy source. seq is INTEGER PRIMARY KEY AUTOINCREMENT so ordering is monotonic and never reused across multiple writers (the Express server and the MCP stdio bins open the same file). Insert-only by discipline; data is scrubbed before storage.
  • Columns: seq (PK, autoinc: the cross-process monotonic key), id (text, not null), ts, kind, team_id, task_id, agent_id, runtime, trace_id, span_id, parent_span_id, correlation_id, data (scrubbed JSON), tenant_id, created_at.
  • Indexes: uniq_orch_events_id (unique on id), idx_orch_events_team_seq on (team_id, seq), idx_orch_events_task_seq on (task_id, seq), idx_orch_events_trace_seq on (trace_id, seq), idx_orch_events_kind_ts on (kind, ts), idx_orch_events_created.

​
Capabilities cluster

​
capabilities

The durable projection of every runtime’s capabilities (skills / tools / connectors), read by the six CapabilitySource adapters and fanned by the CapabilityMultiplexer. One stream drives both the Ghost Graph and the Capabilities dashboard. The primary key id (${source_id}:${rawKey}) deterministically encodes the composite identity, so the PK is the upsert key; source_id scopes the read-reconcile so one source’s re-read never deletes another source’s rows.
  • Columns: id (PK, text: sourceId:rawKey), source_id (the owning adapter: native|hermes|claude-code|codex|openclaw), source_key, kind (skill|tool|connector), runtime (open set), scope (team|agent|global), agent_id (null for team/global scope), origin (brokered-mcp|curated-skill|filesystem-skill-md|mcp-connector|runtime-builtin|openclaw-extension|external-vendor-cli), manageability (managed|external-write|runtime-of-record|observe-only), name, description (default ''), availability (JSON | null), available (0/1, default 1), diagnostics (JSON string[], default []), provenance (JSON | null), status (default ready), tenant_id (dormant), synced_at, created_at, updated_at.
  • Indexes: idx_capabilities_source, idx_capabilities_runtime, idx_capabilities_agent, idx_capabilities_kind.

​
Connector and grant cluster

​
connectors

One row per connector clawboo has actually connected, written by the supervisor once discovery has succeeded and never before: a row for a server that never answered would claim a connection that does not exist. Three nouns are kept apart on purpose. The catalog type is committed TypeScript in @clawboo/connector-catalog, this row is a configured instance, and a capabilities row is one callable tool. spec_hash and tools_hash are sha256 hex over the canonical strings @clawboo/governance produces, which is what makes a server that rewrites its own tool list detectable as drift. No secret material lands here: spec carries only secret:NAME references, which is also why it is safe to hash and to log.
  • Columns: id (PK, text), slug, catalog_id (null for a connector the operator added themselves), display_name, transport (stdio|streamable-http), spec (JSON), spec_hash, tools_hash, egress_allow (JSON string[]), trifecta (JSON), health (unknown|ok|needs-auth|degraded|error|drift, default unknown), health_detail, failures (default 0), tenant_id (dormant), created_at, updated_at.
  • Indexes: uniq_connectors_slug (unique on slug).

​
capability_grants

The grant spine. One row is both the permission executeBrokeredCall enforces and the edge an operator reads on the Ghost Graph. The renderer calls the same decideGrant the gate calls, over the same candidate rows, so a badge cannot be computed by a second code path. grant_key is the canonicalised (subject, capability) composite, and it is the only column uniqueness can live on: a UNIQUE index over the five nullable identity columns does not enforce one grant per pair, because SQLite treats NULLs as distinct. Identity normalisation is enforced by grantKey in @clawboo/db: when connector_id is set, capability_id is stored NULL, because a capabilities.id folds the owning agent into its raw key and a grant keyed on one would be unfindable by the grantee. There is deliberately no last_used_at and no use_count. A team- or global-scoped grant is a hot single row on a single-writer database, and a contended write can block the event loop for the full retry budget. Both values are derived from tool_call_audit via idx_tool_audit_grant.
  • Columns: id (PK, text), grant_key, subject_kind (agent|team|global), subject_id (null for global), capability_kind, connector_id, capability_id, tool_allow (JSON glob list, default ["*"]), tool_deny (JSON, default []), mode (read|write|admin, default read), approval_policy (never|risk|writes|always, default risk), state (proposed|active|suspended|revoked|expired, default active), origin (owner = the runtime already attaches this, never drawn as an edge; operator = a human deliberately shared it, which is what draws one), expires_at, spec_hash_pin, tools_hash_pin, call_ceiling_per_hour, granted_by, granted_at, revoked_at, revoked_reason, tenant_id (dormant), created_at, updated_at.
  • Indexes: uniq_capability_grants_key (unique on grant_key), idx_grants_subject, idx_grants_connector, idx_grants_capability, idx_grants_state, idx_grants_expires.

​
approval_rules

A remembered Always, bound to the grant it was approved under. Minted when an operator resolves a tool approval with allow_always, and cascade-deleted when that grant is revoked: a remembered approval outliving its grant would let a re-grant silently inherit permissions given under different circumstances. expires_at is not null, and that is load-bearing. The matcher treats a null expiry as immortal, and an immortal allow bought with one click is a permission nobody revisits. A rule whose prompt was marked never-rememberable (a lethal trifecta, a tainted run) is never minted at all.
  • Columns: id (PK, text), rule_key (canonical (grant_id, tool_name, args_shape) composite, for the same NULL reason as grant_key), grant_id (not null), tool_name (not null), args_shape (null = covers any arguments; a value scopes the rule to one argument shape and is strictly more specific), decision (allow|deny), created_from_approval_id, expires_at (not null), tenant_id (dormant), created_at.
  • Indexes: uniq_approval_rules_key (unique on rule_key).

​
Team-chat cluster

​
team_chat

The durable group-chat room substrate for mixed-runtime peer chat: every team member posts as a named peer into one room. This is the team’s narration transcript, distinct from the leader-orchestrated task_comments thread. seq is per-room monotonic (assigned in an immediateWrite transaction as MAX(seq)+1 WHERE room_id=?), so a cursor read (subscribe) is stable. The board stays canonical; a post never mutates the board.
  • Columns: id (PK, text), room_id (team:<teamId> by default; kept distinct from team_id for a future multi-room seam), team_id (not null), author_agent_id (not null; resolved from the MCP connection binding, never spoofable via tool args), body, kind (default peer; peer = a teammate’s post, system = board-mutation narration, user), created_at, seq (per-room monotonic).
  • Indexes: uniq_team_chat_room_seq (unique on (room_id, seq)), idx_team_chat_team.
team_chat carries no tenant_id column. The room query is kept tenant-scopable via team_id, but the dormant multi-tenant column is not yet present on this table.

​
agent_inbox

The durable per-agent mailbox: the delivery plane that makes a coordination notice survive whatever happens to the agent it is aimed at. A row is something the agent must eventually receive, written by a board-lifecycle subscriber or the orchestrator. Delivery is exactly-once across channels rather than per channel: whichever channel reaches the agent first claims the row and stamps delivered_at with its own name in delivered_via, so a digest racing a mid-run piggyback cannot render the same notice twice. Undelivered rows outlive orchestrator eviction and server restarts, which is what makes a restart a pause instead of amnesia.
  • Columns: id (PK, text), team_id, agent_id (not null, the recipient), kind (not null; task_update = a delegated or executor task reached a terminal, alert = a coordination failure such as a parked or delivery-exhausted task, signal = an ambient FYI), body (not null), task_id, created_at (not null), delivered_at (null while pending), delivered_via (digest | mcp | signal), tenant_id.
  • Indexes: idx_agent_inbox_pending on (agent_id, delivered_at), which is the pending-rows read every delivery channel makes.

​
FTS5 search: memory_facts_fts

memory_facts full-text search rides a companion FTS5 virtual table, memory_facts_fts, declared as raw DDL in createDb (Drizzle cannot model a virtual table, so it is not in schema.ts and not counted among the 28 tables). It mirrors title, content, and an unindexed fact_id, kept in sync by three triggers:
  • memory_facts_ai: AFTER INSERT: copies the new row into the FTS index.
  • memory_facts_ad: AFTER DELETE: removes the FTS row.
  • memory_facts_au: AFTER UPDATE: deletes then re-inserts the FTS row.
The schema-drift guard (schemaSource.test.ts) deliberately excludes memory_facts_fts (and its auto-created shadow tables) from the column comparison.

​
Schema source of truth: no migration ladder

There is no live migration ladder. The model is “bootstrap-via-createDb”:
  • The single source of truth is the CREATE TABLE IF NOT EXISTS … block in ensureSchema() (packages/db/src/schemaBootstrap.ts). It declares every table, index, the FTS5 virtual table, and its triggers on a fresh DB outright. Running it twice is a no-op (IF NOT EXISTS).
  • schema.ts is the Drizzle type layer over the same tables, used for typed queries (db.select()…, the Db* / Db*Insert inferred types), never to apply migrations.
  • Upgrading in place is additive, and derived from that same DDL rather than hand-listed. See below.
This posture is enforced by schemaSource.test.ts, which:
  1. builds a DB through the real createDb() and asserts every schema.ts table and its column set matches the live DDL (and vice versa), the drift guard;
  2. asserts the unapplied drizzle ladder does not ship or run: package.json files excludes drizzle, there are no db:migrate / db:generate scripts, and no migration-ladder directory exists on disk.

​
Upgrading an existing database

CREATE TABLE IF NOT EXISTS skips the whole statement when the table is already there, so a column added to an existing table would be a silent no-op on every database created before the change: the upgrade appears to succeed, then fails at runtime on the first query that touches the column. ensureSchema closes that with one derived, additive step, in this order:
  1. reconcileSchema (packages/db/src/schemaReconcile.ts) reads the declared column set back out of the same DDL, diffs it against PRAGMA table_info for each table that already exists, and issues ALTER TABLE … ADD COLUMN for anything missing.
  2. the DDL batch then creates everything absent: new tables, indexes, triggers.
The order is load-bearing. A new column normally ships with an index over it, and CREATE INDEX IF NOT EXISTS … ON t (new_col) fails with “no such column” if the batch runs first, which takes the whole batch, and the boot, with it. Parsing the DDL rather than maintaining a list of ALTERs keeps one source of truth; schemaReconcile.test.ts makes that safe by asserting the parsed column set is identical to what SQLite actually creates from the same DDL, so a construct the parser cannot read fails the build instead of shipping.
ADD COLUMN is the only schema change SQLite makes without rewriting the table, so this covers additive evolution only. A new column on an existing table must be addable: it may not use PRIMARY KEY, UNIQUE, or a STORED generated column; any DEFAULT must be a literal rather than an expression; a NOT NULL column must carry one; and a REFERENCES column must not have a non-NULL one. A test catches one that is not before it ships, and if one ever escapes, the bootstrap throws with a message naming the column and the remedy, which the boot probe reports as a fatal databaseSchema check. Changing an existing column’s type or constraints, dropping one, adding a table constraint, redefining an index or trigger (their IF NOT EXISTS matches on name), and any change to the FTS5 virtual table are all still not in-place upgrades. Columns the DDL no longer declares are left alone, so opening a newer database with an older Clawboo does not destroy them.
Five drizzle migration stubs (0000–0004) existed before the migration ladder was removed; they now survive only in git history, never on disk. There is no packages/db/drizzle/ directory in the checked-out tree, and schemaSource.test.ts asserts it stays absent. Nothing applies them, so never resurrect one from history against a bootstrapped DB. The only drizzle-kit script wired up is db:studio (a read-only browser), and drizzle.config.ts exists for that tool only.

​
Pragmas and contention

openDb opens the file with the multi-writer-safe pragma recipe (many agents may write one DB); createDb is openDb followed by ensureSchema:
This pairs with the application-level jittered retry + BEGIN IMMEDIATE in the board repository (packages/db/src/board/contention.ts), the write-contention recipe behind the board’s atomic claim. The retry is bounded by a 1.5-second wall-clock budget (CLAWBOO_DB_WRITE_BUDGET_MS), which is why busy_timeout is deliberately short.

​
Where the file lives

The package-level default DB path is ~/.openclaw/clawboo/clawboo.db, returned by defaultDbPath() and used by the out-of-process MCP stdio bins so they open the same file the server serves. A CLAWBOO_DB_PATH override takes precedence.
The Express server does not use the package default. It resolves the path through getDbPath() → resolveClawbooDir(), which is ~/.clawboo/clawboo.db by default (CLAWBOO_HOME-overridable). CLAWBOO_DB_PATH overrides the package-level defaultDbPath() only. See Configuration and Environment variables.
Boot-time health helpers also live in db.ts: integrityCheck(db) runs PRAGMA integrity_check ('ok' when healthy), and listTableNames(db) lists the user tables; both feed the system boot probe.

​
See also

Last modified on September 15, 2026