NanoClaw — Central DB Schema

The central store is accessed through the asynchronous DbDriver contract in src/db/driver.ts. src/db/compose.ts performs the one backend choice and registers it through src/db/driver-registry.ts; callers never import a backend directly. SQLite at data/v2.db remains the default composition.

Complete reference for data/v2.db, the host-owned admin-plane database. Start with db.md for the three-DB overview, the map, and the cross-mount rules.

Access layer: src/db/. src/db/schema.ts's SCHEMA constant is a reference copy of the core tables for orientation — it is not exhaustive: several tables (agent_destinations, pending_approvals, container_configs, agent_message_policies, pending_channel_approvals, and others) exist only in their migration files under src/db/migrations/, which remain the actual source of truth for what's created at runtime.

1. Tables

1.1 agent_groups

Agent workspaces. Each maps 1:1 to a groups/<folder>/ directory containing CLAUDE.md and skills. Container config lives in container_configs (see §1.x below); a container.json file is materialized at spawn time for the container runner to read.

CREATE TABLE agent_groups (
  id               TEXT PRIMARY KEY,
  name             TEXT NOT NULL,
  folder           TEXT NOT NULL UNIQUE,
  agent_provider   TEXT,
  created_at       TEXT NOT NULL
);

• Readers: src/session-manager.ts, src/delivery.ts, src/router.ts
• Writers: src/db/agent-groups.ts

1.2 messaging_groups

One row per platform chat (one WhatsApp group, one Slack channel, one 1:1 DM, etc.) per adapter instance.

CREATE TABLE messaging_groups (
  id                    TEXT PRIMARY KEY,
  channel_type          TEXT NOT NULL,
  platform_id           TEXT NOT NULL,
  instance              TEXT NOT NULL,
  name                  TEXT,
  is_group              INTEGER DEFAULT 0,
  unknown_sender_policy TEXT NOT NULL DEFAULT 'strict',
  created_at            TEXT NOT NULL,
  denied_at             TEXT,
  UNIQUE(channel_type, platform_id, instance)
);

• instance: adapter-instance name — N adapters of one platform (e.g. three Slack apps in one workspace) each own their rows. The default instance IS the channel type: migration 016 backfills instance = channel_type and createMessagingGroup stamps the same default, so single-instance installs never see the dimension. Inbound lookups are exact-on-instance (an unknown named instance auto-creates its own row); outbound lookups resolve default-instance-first.
• unknown_sender_policy: strict (drop), request_approval (ask admin), public (allow).
• Readers: src/router.ts, src/delivery.ts, src/session-manager.ts
• Writers: src/db/messaging-groups.ts, channel setup flows

1.3 messaging_group_agents

Wiring: which agent group handles which messaging group. Many-to-many — the same channel can route to multiple agents (see isolation-model.md).

CREATE TABLE messaging_group_agents (
  id                     TEXT PRIMARY KEY,
  messaging_group_id     TEXT NOT NULL REFERENCES messaging_groups(id),
  agent_group_id         TEXT NOT NULL REFERENCES agent_groups(id),
  engage_mode            TEXT NOT NULL DEFAULT 'mention',
                         -- 'pattern' | 'mention' | 'mention-sticky'
  engage_pattern         TEXT,   -- regex; required when engage_mode='pattern';
                                 -- '.' means "match every message"
  sender_scope           TEXT NOT NULL DEFAULT 'all',    -- 'all' | 'known'
  ignored_message_policy TEXT NOT NULL DEFAULT 'drop',   -- 'drop' | 'accumulate'
  session_mode           TEXT DEFAULT 'shared',
  priority               INTEGER DEFAULT 0,
  created_at             TEXT NOT NULL,
  UNIQUE(messaging_group_id, agent_group_id)
);

• session_mode: shared (one session per channel), per-thread (one per thread), agent-shared (one per agent group across all channels).
• engage_mode / engage_pattern / sender_scope / ignored_message_policy: four orthogonal axes (migration 010) that replaced v1's opaque trigger_rules JSON + response_scope enum. engage_mode='pattern' requires engage_pattern ('.' matches every message — the "always respond" flavor); sender_scope='known' restricts engagement to group members; ignored_message_policy='accumulate' keeps ignored messages as context instead of dropping them.
• Side effect: creating a wiring must also populate agent_destinations — don't mutate one without the other (see §1.10).

1.4 users

Platform user identities. ID is namespaced: tg:123456, discord:abc, phone:+1555..., email:[email protected]. One human may own several rows — no cross-channel linking yet.

CREATE TABLE users (
  id           TEXT PRIMARY KEY,
  kind         TEXT NOT NULL,
  display_name TEXT,
  created_at   TEXT NOT NULL
);

• Writers/readers: src/db/users.ts; channel auth flows

1.5 user_roles

Permissions. Privilege is user-level, never agent-group-level.

CREATE TABLE user_roles (
  user_id        TEXT NOT NULL REFERENCES users(id),
  role           TEXT NOT NULL,
  agent_group_id TEXT REFERENCES agent_groups(id),
  granted_by     TEXT REFERENCES users(id),
  granted_at     TEXT NOT NULL,
  PRIMARY KEY (user_id, role, agent_group_id)
);
CREATE INDEX idx_user_roles_scope ON user_roles(agent_group_id, role);

Invariants:

• role = 'owner' → must be global (agent_group_id IS NULL). Enforced in grantRole().
• role = 'admin' → global (NULL) or scoped to one agent group.
• Admin @ A implies membership in A — no agent_group_members row required.

Access layer: src/db/user-roles.ts, src/access.ts.

1.6 agent_group_members

Explicit membership for non-privileged users. Owner and admins don't need rows here — they're implicit members.

CREATE TABLE agent_group_members (
  user_id        TEXT NOT NULL REFERENCES users(id),
  agent_group_id TEXT NOT NULL REFERENCES agent_groups(id),
  added_by       TEXT REFERENCES users(id),
  added_at       TEXT NOT NULL,
  PRIMARY KEY (user_id, agent_group_id)
);

1.7 user_dms

Cache of DM channel discovery. Lets the host send a cold DM (approval card, pairing code) without hitting the platform's openConversation API every time.

CREATE TABLE user_dms (
  user_id            TEXT NOT NULL REFERENCES users(id),
  channel_type       TEXT NOT NULL,
  messaging_group_id TEXT NOT NULL REFERENCES messaging_groups(id),
  resolved_at        TEXT NOT NULL,
  PRIMARY KEY (user_id, channel_type)
);

Populated lazily by ensureUserDm() in src/user-dm.ts. Cold DMs resolve via the channel's default adapter instance — PRIMARY KEY (user_id, channel_type) is per-platform, not per-instance.

1.8 sessions

Session registry. One row per (agent group, messaging group, thread) tuple subject to session_mode. Stores lifecycle metadata only — no messages.

CREATE TABLE sessions (
  id                 TEXT PRIMARY KEY,
  agent_group_id     TEXT NOT NULL REFERENCES agent_groups(id),
  messaging_group_id TEXT REFERENCES messaging_groups(id),
  thread_id          TEXT,
  agent_provider     TEXT,
  status             TEXT DEFAULT 'active',
  container_status   TEXT DEFAULT 'stopped',
  last_active        TEXT,
  created_at         TEXT NOT NULL
);
CREATE INDEX idx_sessions_agent_group ON sessions(agent_group_id);
CREATE INDEX idx_sessions_lookup     ON sessions(messaging_group_id, thread_id);

• Resolved by: resolveSession() in src/session-manager.ts.
• Creating a session also provisions the session folder and both session DBs via initSessionFolder() — see db-session.md.

1.9 pending_questions

The ask_user_question MCP tool parks an interactive question here, and the container matches incoming system messages back to it by questionId.

CREATE TABLE pending_questions (
  question_id    TEXT PRIMARY KEY,
  session_id     TEXT NOT NULL REFERENCES sessions(id),
  message_out_id TEXT NOT NULL,
  platform_id    TEXT,
  channel_type   TEXT,
  thread_id      TEXT,
  title          TEXT NOT NULL,
  options_json   TEXT NOT NULL,
  created_at     TEXT NOT NULL
);

1.10 agent_destinations

Permission ACL and name-resolution map for outbound sending. An agent asking to send_message(to="dev-channel") must have a row here with local_name = 'dev-channel', or the send is rejected as unknown destination.

CREATE TABLE agent_destinations (
  agent_group_id TEXT NOT NULL REFERENCES agent_groups(id),
  local_name     TEXT NOT NULL,
  target_type    TEXT NOT NULL,   -- 'channel' | 'agent'
  target_id      TEXT NOT NULL,   -- messaging_group_id | agent_group_id
  created_at     TEXT NOT NULL,
  PRIMARY KEY (agent_group_id, local_name)
);
CREATE INDEX idx_agent_dest_target ON agent_destinations(target_type, target_id);

Projection invariant (load-bearing). The central table is the source of truth, but each running container reads from a projection in its own inbound.db (see db-session.md §2.3). Any code that mutates agent_destinations while a container is running must also call writeDestinations() (src/session-manager.ts) or the container will reject sends with stale data. Known call sites: createMessagingGroupAgent() in src/db/messaging-groups.ts, the create_agent system action in src/delivery.ts.

Access layer: src/db/agent-destinations.ts.

1.11 pending_approvals

Two workflows share this table:

• Session-bound MCP approvals — install_packages, add_mcp_server. session_id is set.
• OneCLI credential approvals — session_id may be NULL; agent_group_id + channel_type + platform_id route the admin card.

CREATE TABLE pending_approvals (
  approval_id         TEXT PRIMARY KEY,
  session_id          TEXT REFERENCES sessions(id),
  request_id          TEXT NOT NULL,
  action              TEXT NOT NULL,
  payload             TEXT NOT NULL,
  created_at          TEXT NOT NULL,
  agent_group_id      TEXT REFERENCES agent_groups(id),
  channel_type        TEXT,
  platform_id         TEXT,
  platform_message_id TEXT,
  expires_at          TEXT,
  status              TEXT NOT NULL DEFAULT 'pending',
  title               TEXT NOT NULL DEFAULT '',
  question            TEXT NOT NULL DEFAULT '',       -- added by migration 021
  options_json        TEXT NOT NULL DEFAULT '[]'
);
CREATE INDEX idx_pending_approvals_action_status ON pending_approvals(action, status);

• status: pending | approved | rejected | expired.
• platform_message_id lets the host edit the admin card in place after a decision.
• Access layer: src/db/sessions.ts; sweep + delivery: src/onecli-approvals.ts.

1.12 unregistered_senders

Audit trail: every time a message gets dropped (unknown sender, strict policy), we increment a counter here so admins can see who's been trying to knock.

CREATE TABLE unregistered_senders (
  channel_type       TEXT NOT NULL,
  platform_id        TEXT NOT NULL,
  user_id            TEXT,
  sender_name        TEXT,
  reason             TEXT NOT NULL,
  messaging_group_id TEXT,
  agent_group_id     TEXT,
  message_count      INTEGER NOT NULL DEFAULT 1,
  first_seen         TEXT NOT NULL,
  last_seen          TEXT NOT NULL,
  PRIMARY KEY (channel_type, platform_id)
);
CREATE INDEX idx_unregistered_senders_last_seen ON unregistered_senders(last_seen);

Writer: recordDroppedMessage() in src/db/dropped-messages.ts. On conflict, bumps message_count + last_seen.

1.13 Chat SDK bridge tables

State backing the SqliteStateAdapter used by the Chat SDK bridge (see api-details.md). NanoClaw code rarely touches these directly — they're owned by src/state-sqlite.ts.

CREATE TABLE chat_sdk_kv (
  key        TEXT PRIMARY KEY,
  value      TEXT NOT NULL,
  expires_at INTEGER                    -- unix ts, nullable
);

CREATE TABLE chat_sdk_subscriptions (
  thread_id     TEXT PRIMARY KEY,
  subscribed_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE chat_sdk_locks (
  thread_id  TEXT PRIMARY KEY,
  token      TEXT NOT NULL,
  expires_at INTEGER NOT NULL
);

CREATE TABLE chat_sdk_lists (
  key        TEXT NOT NULL,
  idx        INTEGER NOT NULL,
  value      TEXT NOT NULL,
  expires_at INTEGER,
  PRIMARY KEY (key, idx)
);

1.14 schema_version

Migration ledger, written by the migration runner (§2).

CREATE TABLE schema_version (
  version INTEGER PRIMARY KEY,
  name    TEXT NOT NULL,
  applied TEXT NOT NULL
);

1.15 container_configs

Per-agent-group container runtime config. Source of truth for provider, model, packages, MCP servers, mounts, CLI scope, timezone, etc. Materialized to groups/<folder>/container.json at spawn time.

CREATE TABLE container_configs (
  agent_group_id         TEXT PRIMARY KEY REFERENCES agent_groups(id) ON DELETE CASCADE,
  provider               TEXT,
  model                  TEXT,
  effort                 TEXT,
  image_tag              TEXT,
  assistant_name         TEXT,
  max_messages_per_prompt INTEGER,
  skills                 TEXT NOT NULL DEFAULT '"all"',
  mcp_servers            TEXT NOT NULL DEFAULT '{}',
  packages_apt           TEXT NOT NULL DEFAULT '[]',
  packages_npm           TEXT NOT NULL DEFAULT '[]',
  additional_mounts      TEXT NOT NULL DEFAULT '[]',
  cli_scope              TEXT NOT NULL DEFAULT 'group',   -- disabled | group | global
  timezone               TEXT,                            -- IANA id; NULL = install-global TZ (added by migration 20)
  updated_at             TEXT NOT NULL
);

timezone overrides the install-global timezone for one agent group: host-side scheduling (cron interpretation, --process-after, run-log stamps) resolves it live via resolveGroupTimezone (src/container-config.ts); the container gets it as its TZ env on next respawn. Set via ncl groups config update --timezone <IANA> ("" clears back to NULL) or ncl groups create --timezone.

• Readers: src/container-config.ts, src/container-runner.ts, src/cli/dispatch.ts (scope enforcement), src/claude-md-compose.ts
• Writers: src/db/container-configs.ts, src/modules/self-mod/apply.ts, src/backfill-container-configs.ts

1.16 pending_sender_approvals

In-flight state for the unknown_sender_policy = 'request_approval' flow. A row exists while an admin-approval card is outstanding for a first-time sender in a wired messaging group; UNIQUE(messaging_group_id, sender_identity) dedups concurrent attempts from the same sender instead of spamming the admin with repeat cards.

CREATE TABLE pending_sender_approvals (
  id                 TEXT PRIMARY KEY,
  messaging_group_id TEXT NOT NULL REFERENCES messaging_groups(id),
  agent_group_id     TEXT NOT NULL REFERENCES agent_groups(id),
  sender_identity    TEXT NOT NULL,    -- namespaced user id (channel_type:handle)
  sender_name        TEXT,
  original_message   TEXT NOT NULL,    -- JSON of the original InboundEvent
  approver_user_id   TEXT NOT NULL,
  created_at         TEXT NOT NULL,
  title              TEXT NOT NULL DEFAULT '',       -- added by migration 013
  question           TEXT NOT NULL DEFAULT '',       -- added by migration 021
  options_json       TEXT NOT NULL DEFAULT '[]',     -- added by migration 013
  UNIQUE(messaging_group_id, sender_identity)
);

Deleted on admin approve (after adding the sender as a member) or deny.

• Access layer: src/modules/permissions/db/pending-sender-approvals.ts
• Readers/writers: src/modules/permissions/sender-approval.ts, src/modules/permissions/index.ts, src/db/sessions.ts (getAskQuestionRender), src/cli/resources/groups.ts

1.17 pending_channel_approvals

In-flight state for the unknown-channel registration flow. When a channel with no messaging_group_agents wiring receives a mention or DM, the router escalates to the owner; PRIMARY KEY(messaging_group_id) gives free in-flight dedup via INSERT OR IGNORE — a second mention while a card is pending drops silently.

CREATE TABLE pending_channel_approvals (
  messaging_group_id TEXT PRIMARY KEY REFERENCES messaging_groups(id),
  agent_group_id     TEXT NOT NULL REFERENCES agent_groups(id),
                     -- agent the approved wiring will target (earliest
                     -- agent_group by created_at, picked at request time)
  original_message   TEXT NOT NULL,      -- JSON of the original InboundEvent
  approver_user_id   TEXT NOT NULL,
  created_at         TEXT NOT NULL,
  title              TEXT NOT NULL DEFAULT '',       -- added by migration 013
  question           TEXT NOT NULL DEFAULT '',       -- added by migration 021
  options_json       TEXT NOT NULL DEFAULT '[]'      -- added by migration 013
);

Approve creates the messaging_group_agents wiring and replays the triggering event; deny sets messaging_groups.denied_at so future messages on that channel drop without re-prompting. Either way, this row is deleted.

• Access layer: src/modules/permissions/db/pending-channel-approvals.ts
• Readers/writers: src/modules/permissions/channel-approval.ts, src/modules/permissions/index.ts, src/router.ts, src/db/sessions.ts (getAskQuestionRender), src/cli/resources/groups.ts

1.18 agent_message_policies

Per-message approval gate on an agent-to-agent connection between two agent groups. No row for a (from, to) pair means free flow (no approval required); a row names the approver who must sign off on each message.

CREATE TABLE agent_message_policies (
  from_agent_group_id TEXT NOT NULL REFERENCES agent_groups(id),
  to_agent_group_id   TEXT NOT NULL REFERENCES agent_groups(id),
  approver            TEXT NOT NULL,
  created_at          TEXT NOT NULL,
  PRIMARY KEY (from_agent_group_id, to_agent_group_id)
);

• Access layer: src/modules/agent-to-agent/db/agent-message-policies.ts
• Readers/writers: src/cli/resources/policies.ts; approved messages create a row in pending_approvals (see §1.11) via the a2a send path.

2. Migration system

Migrations live in src/db/migrations/, one file per migration. Runner: runMigrations() in src/db/migrations/index.ts. It:

1. Creates schema_version if absent.
2. Reads every already-applied name from schema_version into a Set and filters the migrations barrel array down to the ones whose name isn't in that set — dedup is by name, not by the numeric version field.
3. Runs each pending migration's up(db) inside a transaction, in the barrel array's literal order (which is not sorted by version), then inserts a schema_version row.
4. The version column stored in schema_version is not the migration's own version field — it's COALESCE(MAX(version), 0) + 1, i.e. an auto-assigned applied-order number computed at insert time. The version field on the Migration object is just an ordering hint for humans reading the barrel file; it lets module migrations (installed later by skills) pick arbitrary numbers without coordinating with trunk.

A few migrations also set disableForeignKeys: true (needed for table recreates — SQLite can't relax a table-level UNIQUE without DROP+RENAME, which fails FK integrity checks with live child rows). The runner toggles PRAGMA foreign_keys around the transaction and runs PRAGMA foreign_key_check inside it, snapshotting pre-existing violations so it only fails on violations the migration itself introduced.

Several early migrations were later renamed/retired and replaced by "module" files (their original name is retained on the new file so already-migrated DBs don't re-run them):

Numbers 5 and 6 are intentionally absent — migrations were renumbered during early development.

Session DB schemas (INBOUND_SCHEMA, OUTBOUND_SCHEMA) are not versioned here. They're CREATE TABLE IF NOT EXISTS so new columns land via the session-DB lazy migration helpers (migrateDeliveredTable() etc.) when a session file from an older build is reopened. See db-session.md.

3. Portable SQL rules

Central-DB runtime SQL must work on SQLite and installed remote backends. Session mailbox SQL is outside this rule because inbound.db and outbound.db remain direct SQLite.

• Use INSERT ... ON CONFLICT (...) DO NOTHING instead of INSERT OR IGNORE.
• Use INSERT ... ON CONFLICT (...) DO UPDATE SET ... = excluded.... instead of INSERT OR REPLACE; replacement deletes and recreates a row and is not portable.
• Never use rowid for runtime ordering. Declare a stable domain-column order, with a deterministic key as the final tie-breaker.
• Use snake_case aliases because remote SQL engines may fold unquoted identifiers to lowercase.
• Use IS NOT DISTINCT FROM ? when nullable equality is required. IS ? is SQLite-only, while = does not match two NULL values.
• Keep named parameters in the existing @name form. The backend driver owns placeholder rewriting.
• Store timestamps as ISO-8601 UTC text produced by new Date().toISOString(). Portable central-DB SQL compares the consistently shaped text directly; SQLite-only operational snippets may use datetime() around both sides.
• Central migrations added after the async DB boundary must use the backend-neutral driver API and portable SQL. Backend-specific schema work belongs in a named migration override.

4. Migration execution

runMigrations() has three modes:

• auto migrates SQLite and validates non-SQLite backends.
• validate performs no DDL and refuses startup when the ledger is missing or pending.
• migrate applies migrations and is used by pnpm run migrate under the migration-owner role.

Backends may provide three narrow hooks: baseline bootstrap, name-keyed migration overrides, and a lock around the complete migration run. Legacy SQLite-only migrations are frozen by name; a non-SQLite backend must cover them in its baseline or provide an override. Foreign-key PRAGMA handling is never attempted outside SQLite.

Host startup uses auto; production schema changes are a separate operator step. scripts/q.ts sends only the canonical data/v2.db path through the installed composition. Explicit inbound.db and outbound.db paths always remain local SQLite files and retain their journal mode.