Database schema reference

​

At a glance

​

ER diagram (main clusters)

​

Registry cluster

​

chat_messages

• 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

• 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

• 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

• 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

• Columns: id (PK, autoinc), agent_id (FK → agents.id), model, input_tokens, output_tokens, cost_usd (REAL), run_id, created_at.
• Indexes: idx_cost_records_agent_id, idx_cost_records_run_id, idx_cost_records_created_at.

​

graph_layouts

• 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

• Columns: key (PK, text), value (text), updated_at.
• Helpers: getSetting(db, key) / setSetting(db, key, value) (upsert on conflict).

​

skills

• 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

• 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

• Columns: team_id (PK, FK → teams.id ON DELETE CASCADE), content (markdown), updated_at.

​

approval_history

• 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

​

tasks

• 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

• 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

• 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

• 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

• 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

• 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

​

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

​

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

• 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

• 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, created_at, expires_at (not null), resolved_at.
• Indexes: idx_tool_approvals_status, idx_tool_approvals_created.

​

Governance cluster

​

budgets

• 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.

​

governance_audit

• 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

• 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

• 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.

​

Team-chat cluster

​

team_chat

• 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.

​

agent_inbox

• 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_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.

​

Schema source of truth: no migration ladder

• 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.

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

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.

​

Pragmas and contention

PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = ON;
PRAGMA synchronous = NORMAL;
PRAGMA busy_timeout = 250;
PRAGMA wal_autocheckpoint = 50;

​

Where the file lives

​

See also

• Configuration: settings.json, file/dir locations
• Boo Zero and Using teams: the documented settings-table keys
• Environment variables: CLAWBOO_DB_PATH, CLAWBOO_HOME
• Data and state: SQLite file, backup, reset
• The board: tasks / task_deps / execution_processes state machine and atomic claim
• Memory: the memory_facts / memory_procedures tiers
• Governance: budgets / governance_audit
• Observability: the orchestration_events log
• @clawboo/db package reference: the data-access exports over these tables