schema

arizuko › reference › schema

Every SQLite table in arizuko's shared messages.db. Schema lives in store/migrations/ (embedded via store/store.go:13); gated applies them at boot, every other daemon connects read/write but never migrates. State described here is the cumulative result of migrations 0001 through 0057 — the 0049–0057 run added cost-log, proxyd-routes, unified ACL, topic lineage, pane sessions, and group visibility.

30 live tables, grouped by concern. Two carry-overs from the 0001 baseline (grants, channels) have no live Go writers and are noted as vestigial.

1. Identity & ACL

`auth_users`

OAuth-issued user identities. One row per subject across all linked providers; linked_to_sub points at the canonical row when this row is a secondary identity.

column	type	null	meaning
`id`	INTEGER		PK autoincrement
`sub`	TEXT	NOT NULL UNIQUE	OAuth subject (e.g. `github:1234`, `google:abc`)
`username`	TEXT	NOT NULL UNIQUE	display handle
`hash`	TEXT	NOT NULL	password hash (legacy local-auth)
`name`	TEXT	NOT NULL	full name
`created_at`	TEXT	NOT NULL	RFC3339
`linked_to_sub`	TEXT	NULL	canonical sub when this row is a linked identity (0040)

Indexes: PK on id; unique on sub, username; partial idx_auth_users_linked_to_sub on linked_to_sub WHERE linked_to_sub IS NOT NULL.

Writers: store/auth.go:28 (INSERT), :90 (UPDATE linked_to_sub), :95 (INSERT linked), onbod/main.go:468 (UPDATE username on claim).

Readers: proxyd (login), webd, dashd, onbod.

Migrations: created 0001; 0040 added linked_to_sub.

`auth_sessions`

Server-side session table (refresh-token store). JWTs are short-lived; this row carries the long-lived refresh state.

column	type	null	meaning
`token_hash`	TEXT PK	NOT NULL	SHA-256 of the cookie token
`user_sub`	TEXT	NOT NULL	owning `auth_users.sub`
`expires_at`	TEXT	NOT NULL	RFC3339
`created_at`	TEXT	NOT NULL	RFC3339

Writers: store/auth.go:121 (INSERT), :144 (DELETE).

Readers: proxyd, webd.

Migrations: created 0001.

`acl`

Unified permission rows. Answers "may principal P perform action A on scope S?" Replaced the legacy user_groups + grant_rules tables in v0.38.0. Principals are OAuth subs (google:…), folder agents (folder:…), room/user JIDs, or roles (role:operator). Match logic in auth.Authorize; effect='deny' wins over allow.

column	type	null	default	meaning
`principal`	TEXT	NOT NULL		sub, folder, JID, or `role:<name>`
`action`	TEXT	NOT NULL		`interact`, `admin`, `mcp:<tool>`, or `*`
`scope`	TEXT	NOT NULL		folder glob
`effect`	TEXT	NOT NULL	'allow'	'allow' or 'deny'
`params`	TEXT	NOT NULL	''	JSON predicate constraints (e.g. `{"jid":"telegram:*"}`)
`predicate`	TEXT	NOT NULL	''	reserved for richer expressions
`granted_by`	TEXT	NULL		actor source (CLI / dashd / migration)
`granted_at`	TEXT	NOT NULL		RFC3339

PK: (principal, action, scope, params, predicate, effect). Indexes: acl_by_principal_action, acl_by_scope.

Writers: store/acl.go (AddACLRow, RemoveACLRow); cmd/arizuko grant; dashd ACL editor; migration 0053 (back-fill).

Readers: many — proxyd, webd, dashd, onbod, gated (every folder-scoped HTTP route via auth.Authorize); MCP list_acl.

Migrations: created 0052; 0053 back-filled from user_groups + grant_rules then dropped those tables.

`acl_membership`

Identity and role indirection. One edge (child, parent) means "anything child proves it is, it also is parent." Used for role membership ((sub, role:operator)) and for OAuth-mediated JID claims ((discord:user/811…, google:114alice)). Walked transitively at authorization time.

column	type	null	meaning
`child`	TEXT	NOT NULL	the holder of the identity
`parent`	TEXT	NOT NULL	the role or canonical identity it expands to
`added_by`	TEXT	NULL	actor source
`added_at`	TEXT	NOT NULL	RFC3339

PK: (child, parent). Indexes: acl_membership_by_child.

Writers: store/acl.go (AddMembership, RemoveMembership); arizuko grant <sub> **; OAuth callback for JID claims; migration 0053 (back-fill of pre-0053 user_groups('**') rows and user_jids bindings).

Readers: gated, proxyd, webd, dashd, onbod (via auth.Authorize walking Ancestors(sub)).

Migrations: created 0052.

`identities` + `identity_claims` + `identity_codes`

Cross-channel identity merge (spec 5/9). One identities row is a canonical person; identity_claims binds one or more platform sender subs to it; identity_codes are short-lived link codes minted by /auth/link-code. Advisory — agents read via inspect_identity, never enforce.

table.column	type	meaning
`identities.id`	TEXT PK	canonical identity ID
`identities.name`	TEXT NOT NULL	display name
`identities.created_at`	TEXT NOT NULL	RFC3339
`identity_claims.sub`	TEXT PK	platform sender sub
`identity_claims.identity_id`	TEXT NOT NULL	`identities.id`
`identity_claims.claimed_at`	TEXT NOT NULL	RFC3339
`identity_codes.code`	TEXT PK	short-lived link code
`identity_codes.identity_id`	TEXT NOT NULL	`identities.id`
`identity_codes.expires_at`	TEXT NOT NULL	RFC3339

Indexes: idx_identity_claims_id on identity_claims(identity_id).

Writers: store/identities.go:34 (INSERT identity), :111 (INSERT OR REPLACE claim), :119 (DELETE claim), :134 (INSERT code), :169/:175/:186 (claim on code redeem + GC).

Readers: gated (inspect_identity MCP tool), api/api.go (link-code inbound detection).

Migrations: all three created 0035.

2. Messages, sessions, turns

`messages`

The main bus. Every inbound and outbound message lands here. Rebuilt by migration 0023 to drop dead columns and re-source identity around source.

column	type	null	default	meaning
`id`	TEXT PK	NOT NULL		platform-specific message ID
`chat_jid`	TEXT	NOT NULL		typed JID of the chat
`sender`	TEXT	NOT NULL		typed sender JID
`sender_name`	TEXT	NULL		display name
`content`	TEXT	NOT NULL		message body
`timestamp`	TEXT	NOT NULL		RFC3339Nano
`is_from_me`	INTEGER	NOT NULL	0	set on outbound
`is_bot_message`	INTEGER	NOT NULL	0	set on agent output
`forwarded_from`	TEXT	NULL		upstream forward source
`reply_to_id`	TEXT	NULL		0003
`reply_to_text`	TEXT	NULL		parent excerpt
`reply_to_sender`	TEXT	NULL		parent sender (typed JID after 0042)
`topic`	TEXT	NOT NULL	''	topic-sessions key (0008)
`routed_to`	TEXT	NOT NULL	''	folder this message was routed to (0015)
`verb`	TEXT	NOT NULL	'message'	event type (0017)
`attachments`	TEXT	NOT NULL	''	JSON []chanlib.InboundAttachment (0019)
`source`	TEXT	NOT NULL	''	handling adapter; receiver inbound, deliverer outbound
`errored`	INTEGER	NOT NULL	0	marked when a prior run failed; re-fed tagged (0030)
`turn_id`	TEXT	NULL		inbound msg ID that triggered this outbound (0038)
`status`	TEXT	NOT NULL	'sent'	'sent' \| 'pending' \| 'failed' (0039)
`chat_name`	TEXT	NOT NULL	''	human channel/group name (0046)

Indexes: idx_messages_chat_ts on (chat_jid, timestamp); idx_messages_turn_id on turn_id WHERE turn_id IS NOT NULL; idx_messages_status on status WHERE status != 'sent'.

Writers: store/messages.go:19 (PutMessage), timed/main.go:132 (scheduled-task inject); UPDATEs in store/messages.go (status, content, errored, delivered).

Readers: many — gated, dashd, webd, davd, vited, router, ipc.

Migrations: 0001, 0003 (reply_to_id), 0005 (source, group_folder), 0008 (topic), 0015 (routed_to), 0017 (verb), 0019 (attachments), 0023 (rebuild; drop group_folder), 0030 (errored), 0038 (turn_id), 0039 (status), 0042/0043 (typed JIDs), 0046 (chat_name).

`messages_fts` FTS5 shadow

An FTS5 virtual table over messages.content, kept in sync by triggers. Backs the find_messages MCP tool. messages.id is TEXT, so the shadow keys on the implicit INTEGER rowid SQLite gives the table. Tokenizer is unicode61 remove_diacritics 2 — Czech, Spanish, Japanese match without surprises (Czech "úroveň" matches "uroven").

column	type	null	default	meaning
`content`	FTS5			indexed copy of `messages.content`
`rowid`	INTEGER			`content_rowid='rowid'` — joins back to `messages.rowid`

Sync: triggers messages_fts_ai (insert), messages_fts_au (update), messages_fts_ad (delete) mirror every change. Queried via messages_fts MATCH ? with bm25() rank and snippet() highlight.

Readers: store/messages.go:700 (FindMessages).

Migrations: 0070.

`sessions`

The active Claude Code session ID per (folder, topic). Used to resume an existing conversation in the in-container agent.

column	type	null	default	meaning
`group_folder`	TEXT	NOT NULL		folder path
`topic`	TEXT	NOT NULL	''	topic key
`session_id`	TEXT	NOT NULL		Claude Code session UUID
`parent_topic`	TEXT	NULL		topic this one forked from (`''` = main); NULL when no lineage (main itself)
`forked_at`	TEXT	NULL		RFC3339Nano UTC fork point — audit metadata (the parent’s session file is already copied into the child’s session)
`observed_cursor`	TEXT	NULL		per-topic high-water mark over `is_observed=1` messages; gateway advances it after each turn

PK: (group_folder, topic).

Writers: store/sessions.go — SetSession (UPSERT session_id), EnsureTopicLineage (seed lineage on first turn), ForkTopic (explicit branch), UpdateObservedCursor, DeleteSession.

Readers: gated.

Migrations: created 0001; rebuilt 0008 with topic column + composite PK; 0055 add parent_topic, forked_at, observed_cursor (spec 6/F).

`session_log`

Audit log of every agent run.

column	type	meaning
`id`	INTEGER PK AUTOINCREMENT
`group_folder`	TEXT NOT NULL	folder
`session_id`	TEXT NOT NULL	Claude Code session UUID
`started_at`	TEXT NOT NULL	RFC3339
`ended_at`	TEXT NULL	RFC3339
`result`	TEXT NULL	outcome summary
`error`	TEXT NULL	error message
`message_count`	INTEGER NULL	messages processed

Writers: store/sessions.go:91 (gateway, at spawn end).

Readers: dashd.

Migrations: created 0001.

`turn_results`

Idempotent record per (folder, turn_id) so a webd round-handle protocol can fetch all frames of a single run by FK lookup instead of timestamp correlation.

column	type	meaning
`folder`	TEXT NOT NULL
`turn_id`	TEXT NOT NULL	inbound message ID
`session_id`	TEXT NULL	resumed Claude session
`status`	TEXT NOT NULL	'ok' / 'error'
`recorded_at`	TEXT NOT NULL	RFC3339Nano

PK: (folder, turn_id).

Writers: store/turns.go:15 (gateway).

Readers: webd (round-handle for /slink/<token>/{turn_id}/...).

Migrations: created 0036.

`system_messages`

System-origin events (e.g. migration broadcasts) merged into the agent's message stream.

column	type	meaning
`id`	INTEGER PK AUTOINCREMENT
`group_id`	TEXT NOT NULL	target folder
`origin`	TEXT NOT NULL	'self', 'platform', etc.
`event`	TEXT NOT NULL	event name
`attrs`	TEXT NULL	JSON attrs
`body`	TEXT NOT NULL DEFAULT ''	event payload
`created_at`	TEXT NOT NULL	RFC3339

Writers: store/sessions.go:54.

Readers: gated.

Migrations: created 0001.

`chat_reply_state`

Last bot reply ID per (chat_jid, topic) — used to chain assistant replies into a single visible thread on platforms that support thread/reply pointers.

column	type	meaning
`jid`	TEXT NOT NULL	chat JID (typed after 0043)
`topic`	TEXT NOT NULL DEFAULT ''	topic key
`last_reply_id`	TEXT NOT NULL	last bot message ID

PK: (jid, topic).

Writers: store/messages.go:401.

Readers: gateway/adapter outbound layer.

Migrations: created 0014; 0043 rewrote rows to typed JID.

`pane_sessions`

Slack assistant-pane state per (team, user, thread_ts). Persists across slakd restarts; the in-memory map is a read-through cache. See slack pane for the concept.

column	type	null	meaning
`team_id`	TEXT	NOT NULL	Slack workspace ID
`user_id`	TEXT	NOT NULL	Slack user who opened the pane
`thread_ts`	TEXT	NOT NULL	pane thread timestamp
`channel_id`	TEXT	NOT NULL	DM channel that hosts the pane
`context_jid`	TEXT	NULL	workspace channel the user is currently viewing
`opened_at`	TEXT	NOT NULL	RFC3339Nano UTC
`last_status_at`	TEXT	NULL	last `setStatus` call — used to debounce

PK: (team_id, user_id, thread_ts). Index: idx_pane_sessions_channel on channel_id.

Writers: store/pane_sessions.go — UpsertPane, SetPaneContext, SetPaneStatusAt.

Readers: slakd (pane lookup keyed by channel), gateway (pane-context hint in buildAgentPrompt).

Migrations: created 0056.

3. Groups (folders), chats, routes

`groups`

The folder hierarchy. Path is identity (tier from depth via auth.Resolve). Created in 0020 by renaming the older registered_groups table and rekeying by folder.

column	type	null	default	meaning
`folder`	TEXT PK	NOT NULL		folder path (e.g. `atlas/support`)
`name`	TEXT	NOT NULL		display name
`added_at`	TEXT	NOT NULL		RFC3339
`container_config`	TEXT	NULL		JSON GroupConfig (mounts, timeout, max_children)
`parent`	TEXT	NULL		parent folder path
`updated_at`	TEXT	NULL		RFC3339
`product`	TEXT	NOT NULL	'assistant'	product slug (0044)
`open`	INTEGER	NOT NULL	1	1 = visible to sibling folders as ambient source (cross-folder `<observed>`); 0 = sealed off. Spec 6/F. Toggled via `set_group_open`.
`observe_window_messages`	INTEGER	NULL		per-group cap on ambient messages surfaced per turn (NULL = fall back to `OBSERVE_WINDOW_MESSAGES`). Per-route override wins.
`observe_window_chars`	INTEGER	NULL		per-group cap on ambient chars per turn (NULL = fall back to `OBSERVE_WINDOW_CHARS`).

Writers: store/groups.go:30 (INSERT), :50 (DELETE), :250 (SetGroupOpen), SetGroupObserveWindow.

Readers: many — gated, dashd, webd, davd, onbod, timed, every MCP tool that scopes to a folder.

Migrations: created 0020; 0041 dropped state, spawn_ttl_days, archive_closed_days; 0044 added product; 0057 added open, observe_window_messages, observe_window_chars (spec 6/F).

`chats`

Per-chat state: agent cursor, sticky routing pins, single-user/group classification. Rebuilt 0023; is_group re-added 0033.

column	type	null	default	meaning
`jid`	TEXT PK	NOT NULL		typed chat JID
`agent_cursor`	TEXT	NULL		last-processed message ID (0020 moved from registered_groups)
`sticky_group`	TEXT	NULL		folder pin via `@folder` command (0016)
`sticky_topic`	TEXT	NULL		topic pin via `#topic` command (0016)
`is_group`	INTEGER	NOT NULL	0	1 = group chat; gates user-secret overlay (0033)

Writers: store/groups.go:84 (upsert cursor), :209 (is_group), :237 (sticky_group), :252 (sticky_topic).

Readers: gated (routing, cursor, single-user check).

Migrations: 0001, 0002 (channel-prefix normalize), 0016 (sticky_group/sticky_topic), 0020 (added agent_cursor), 0023 (rebuild: dropped name/channel/is_group/last_message_time/errored), 0030 (DROP errored), 0033 (re-add is_group), 0042 (typed JIDs in PK).

`routes`

Inbound match-target table. match is space-separated key=glob pairs evaluated by router.RouteMatches; target is the destination folder with an optional #fragment. #observe is the only reserved mode (store without firing a turn); any other fragment (folder#deploy, folder#triage) pins the routed message to that topic. Routes evaluate in (seq, id) order; first match wins.

column	type	null	default	meaning
`id`	INTEGER PK AUTOINCREMENT	NOT NULL
`seq`	INTEGER	NOT NULL	0	evaluation order (lower first)
`match`	TEXT	NOT NULL	''	space-separated key=glob predicates
`target`	TEXT	NOT NULL		destination folder, optionally suffixed with `#mode`
`observe_window_messages`	INTEGER	NULL		per-route override for the trailing `#observe` message count folded into a trigger turn (0/NULL = fall back to `OBSERVE_WINDOW_MESSAGES`)
`observe_window_chars`	INTEGER	NULL		per-route char cap on the `#observe` context window (0/NULL = fall back to `OBSERVE_WINDOW_CHARS`)

Indexes: idx_routes_seq on seq (post-0022 rebuild).

Writers: store/routes.go:38 (AddRoute), :55 (DELETE on folder tear-down), :62 (replace set), :72 (DELETE by id), onbod/main.go:974/:1039.

Readers: gated (ResolveRoute), dashd, ipc (route MCP tools).

Migrations: created 0001; 0008 ensure-exists + unique index; 0012 fix prefix seq; 0022 rebuild (drop jid/type, add match); 0042/0043 rewrite predicates to typed JIDs; 0054 drop impulse_config, encode mode in target fragment, add observe_window_*.

`route_tokens`

URL-bound credentials. One row per issued URL; the JID prefix drives sender attribution and default output style. Each URL is bound to its JID prefix kind: /chat/<token>/ accepts only web: tokens, /hook/<token> only hook: tokens. Mechanics shared at the handler; binding enforced at token lookup.

column	type	null	meaning
`token_hash`	BLOB PK	NOT NULL	sha256 of the raw token; raw token returned once at issuance
`jid`	TEXT	NOT NULL	`web:<folder>[/<suffix>]` or `hook:<folder>/<source>[/<suffix>]` — `<folder>` is the destination, distinct from `owner_folder`
`owner_folder`	TEXT	NOT NULL	issuing folder; bounds revocation
`created_at`	TEXT	NOT NULL	RFC3339

Indexes: route_tokens_jid on jid.

Writers: gated (insertRouteToken, called from both MCP issue_chat_link/issue_webhook and REST POST /v1/route_tokens/*).

Readers: webd (hash + lookup on every /chat/* and /hook/* request); gated (list, revoke).

`web_routes`

webd's reverse-proxy route table: path_prefix → folder with an access mode.

column	type	null	meaning
`path_prefix`	TEXT PK	NOT NULL	e.g. `/dash/`
`access`	TEXT	NOT NULL CHECK IN ('public','auth','deny','redirect')
`redirect_to`	TEXT	NULL	destination when `access='redirect'`
`folder`	TEXT	NOT NULL	owning folder (ACL key)
`created_at`	TEXT	NOT NULL	RFC3339

Writers: store/web_routes.go:25 (UpsertWebRoute), :40 (DELETE).

Readers: webd.

Migrations: created 0045.

4. Onboarding & invites

`onboarding`

Admission state machine. A new sender lands here; gates and tokens flow through this row.

column	type	null	meaning
`jid`	TEXT PK	NOT NULL	typed JID of the unknown sender
`status`	TEXT	NOT NULL	'awaiting_message' \| 'queued' \| 'approved' \| etc.
`prompted_at`	TEXT	NULL	RFC3339
`created`	TEXT	NOT NULL	RFC3339
`token`	TEXT	NULL	opaque admission token (0024)
`token_expires`	TEXT	NULL	RFC3339
`user_sub`	TEXT	NULL	linked auth_users.sub on claim
`gate`	TEXT	NULL	gate name that admitted (0027)
`queued_at`	TEXT	NULL	RFC3339 (0027)

Indexes: idx_onboarding_token on token.

Writers: store/onboarding.go (queue mgmt), onbod/main.go:235/:251/:321/:571/:580/:659.

Readers: onbod, dashd (operator queue), gated (admission check).

Migrations: created 0009; 0021 collapsed flow; 0023 dropped sender/world_name/channel; 0024 added token/user_sub; 0027 added gate/queued_at; 0042 rewrote rows to typed JID.

`onboarding_gates`

Per-gate admission limits.

column	type	meaning
`gate`	TEXT PK	gate name
`limit_per_day`	INTEGER NOT NULL	cap
`enabled`	INTEGER NOT NULL DEFAULT 1	on/off

Writers: store/onboarding.go:43 (upsert), :52 (DELETE), :62 (enable/disable).

Readers: onbod, dashd.

Migrations: created 0029.

`invites`

Invite tokens for the onboarding flow. Hard cutover of older invitations table in 0032.

column	type	null	default	meaning
`token`	TEXT PK	NOT NULL		opaque token
`target_glob`	TEXT	NOT NULL		folder glob the invite admits to
`issued_by_sub`	TEXT	NOT NULL		issuer auth_users.sub
`issued_at`	TEXT	NOT NULL		RFC3339
`expires_at`	TEXT	NULL		RFC3339
`max_uses`	INTEGER	NOT NULL	1	cap
`used_count`	INTEGER	NOT NULL	0	counter

Writers: store/invites.go:55 (INSERT), :119 (DELETE), :138 (UPDATE used_count).

Readers: onbod, cmd/arizuko (arizuko invite).

Migrations: created 0032 (replaces dropped invitations from 0028).

5. Secrets

Per-folder MCP rule overlays (formerly the grant_rules table, dropped in 0053) now live in acl as (principal=folder:<f>, action=mcp:<tool>, scope=<f>, params, effect) rows. Tier defaults are still derived in code by grants.DeriveRules (used as a fall-back for mcp:* actions with no acl match).

`secrets`

Folder- and user-scoped secret values. Plaintext at rest (operator trusts disk + FS perms per spec 9/11).

column	type	meaning
`scope_kind`	TEXT NOT NULL	'folder' or 'user'
`scope_id`	TEXT NOT NULL	folder path or auth_users.sub
`key`	TEXT NOT NULL	env-var name
`value`	BLOB NOT NULL	plaintext (renamed from `enc_value` in 0047)
`created_at`	TEXT NOT NULL	RFC3339

PK: (scope_kind, scope_id, key).

Indexes: partial idx_secrets_folder on scope_id WHERE scope_kind='folder'; partial idx_secrets_user on scope_id WHERE scope_kind='user'.

Writers: store/secrets.go:55 (upsert), :119 (DELETE), dashd/me_secrets.go:133 (user-scope INSERT), :174 (UPDATE), :209 (DELETE).

Readers: gated (FolderSecretsResolved, UserSecrets at spawn), dashd.

Migrations: created 0034 (originally enc_value BLOB); 0047 renamed to value.

`secret_use_log`

Audit log for the secret broker (spec 9/11). One row per tool-call × resolved key. No secret values stored.

column	type	default	meaning
`ts`	TEXT NOT NULL		RFC3339Nano
`spawn_id`	TEXT NOT NULL	''
`caller_sub`	TEXT NOT NULL	''	resolved user sub
`folder`	TEXT NOT NULL	''	spawn folder
`tool`	TEXT NOT NULL		MCP tool name
`key`	TEXT NOT NULL		secret key name
`scope`	TEXT NOT NULL		'folder' or 'user'
`status`	TEXT NOT NULL		'ok' / 'denied' / 'missing'
`latency_ms`	INTEGER NOT NULL	0

Indexes: idx_secret_use_log_ts on ts.

Writers: store/secret_use_log.go:25.

Readers: dashd (audit views).

Migrations: created 0048.

6. Egress

`network_rules`

Per-folder egress allowlist; resolved root-to-folder by store.ResolveAllowlist at container spawn and consumed by crackbox.

column	type	default	meaning
`folder`	TEXT NOT NULL		folder path; `''` = root seed
`target`	TEXT NOT NULL		host (e.g. `api.github.com`)
`created_at`	TEXT NOT NULL		RFC3339
`created_by`	TEXT NOT NULL	''	'system' / sub

PK: (folder, target). Indexes: idx_network_rules_folder on folder.

Seed rows: ('', 'anthropic.com'), ('', 'api.anthropic.com') at migration time.

Writers: store/network.go:18 (INSERT OR IGNORE), :27 (DELETE).

Readers: gated, dashd, cmd/arizuko (arizuko network).

Migrations: created 0037.

7. Scheduling

`scheduled_tasks`

Cron- and interval-scheduled prompts (driven by timed).

column	type	null	default	meaning
`id`	TEXT PK	NOT NULL
`owner`	TEXT	NOT NULL		owning folder
`chat_jid`	TEXT	NOT NULL		typed chat JID (rewritten in 0043)
`prompt`	TEXT	NOT NULL		injected text
`cron`	TEXT	NULL		cron expr / interval ms / empty = one-shot
`next_run`	TEXT	NULL		RFC3339
`status`	TEXT	NOT NULL	'active'	'active' \| 'paused' \| 'firing' \| 'completed'
`created_at`	TEXT	NOT NULL		RFC3339
`context_mode`	TEXT	NOT NULL	'group'	'group' \| 'isolated' (0011)

Indexes: idx_tasks_next on (status, next_run).

Writers: store/tasks.go:27 (INSERT), :81 (UPDATE), :86 (DELETE), timed/main.go:96 (status transitions during fire).

Readers: timed, gated, ipc (task MCP tools), dashd.

Migrations: created 0001; 0011 added context_mode; 0043 rewrote chat_jid to typed shape.

`task_run_logs`

Audit log for every scheduled-task fire.

column	type	meaning
`id`	INTEGER PK AUTOINCREMENT
`task_id`	TEXT NOT NULL REFERENCES scheduled_tasks(id) ON DELETE CASCADE
`run_at`	TEXT NOT NULL	RFC3339
`duration_ms`	INTEGER NULL
`status`	TEXT NOT NULL	'ok' / 'error'
`result`	TEXT NULL
`error`	TEXT NULL

Indexes: idx_task_run_logs_task on task_id.

Writers: timed/main.go:86.

Readers: dashd, ipc (list_task_runs).

Migrations: created 0011.

8. Misc

`router_state`

Generic key-value scratch for the gateway (per-key strings).

column	type	meaning
`key`	TEXT PK
`value`	TEXT NOT NULL

Writers: store/sessions.go:45.

Readers: gated.

Migrations: created 0001.

9. Vestigial (declared but unused)

Pre-v0.38.0 the schema also carried grants, user_groups, user_jids, and grant_rules. Migration 0053 dropped all four after back-filling acl + acl_membership.

`channels` read-only (dashd), not written by live code

Intended persistent registry for channel adapter URL lookup; current implementation keeps the registry in memory (chanreg/). dashd reads counts/rows from this table for the operator dashboard, but no production code writes it.

column	type	meaning
`name`	TEXT PK	adapter name
`url`	TEXT NOT NULL	backend URL
`jid_prefixes`	TEXT NULL
`capabilities`	TEXT NULL

Migrations: created 0009.

`email_threads` declared in messages.db but unused (emaid uses its own emaid.db)

0001 defined this table in the shared DB, but the live emaid adapter maintains its own email_threads in emaid.db with a different shape (emaid/store.go:26). The shared-DB row is dead.

column	type	meaning
`thread_id`	TEXT PK
`chat_jid`	TEXT NOT NULL
`subject`	TEXT NULL
`last_message_id`	TEXT NULL

Migrations: created 0001.

10. Tables removed by migrations

registered_groups — created in 0001, rebuilt in 0007 (dropped trigger_word, requires_trigger), expanded in 0010, dropped in 0020 after rekeying its contents into groups (by folder) and chats.agent_cursor.
invitations — created in 0028, dropped in 0032 after migrating rows to invites.

11. Audit of writers

Schema owner: gated (the //go:embed migrations/*.sql in store/store.go:13 runs at boot). Every other daemon opens the same DB read/write but never applies migrations. Per-table writer mapping above lists the canonical INSERT/UPDATE/DELETE call sites; multiple readers per table is the norm and noted as "many" where widespread.

schema

1. Identity & ACL

auth_users

auth_sessions

acl

acl_membership

identities + identity_claims + identity_codes

2. Messages, sessions, turns

messages

messages_fts FTS5 shadow

sessions

session_log

turn_results

system_messages

chat_reply_state

pane_sessions

3. Groups (folders), chats, routes

groups

chats

routes

route_tokens

web_routes

4. Onboarding & invites

onboarding

onboarding_gates

invites

5. Secrets

secrets

secret_use_log

6. Egress

network_rules

7. Scheduling

scheduled_tasks

task_run_logs

8. Misc

router_state

9. Vestigial (declared but unused)

channels read-only (dashd), not written by live code

email_threads declared in messages.db but unused (emaid uses its own emaid.db)

10. Tables removed by migrations

11. Audit of writers

`auth_users`

`auth_sessions`

`acl`

`acl_membership`

`identities` + `identity_claims` + `identity_codes`

`messages`

`messages_fts` FTS5 shadow

`sessions`

`session_log`

`turn_results`

`system_messages`

`chat_reply_state`

`pane_sessions`

`groups`

`chats`

`routes`

`route_tokens`

`web_routes`

`onboarding`

`onboarding_gates`

`invites`

`secrets`

`secret_use_log`

`network_rules`

`scheduled_tasks`

`task_run_logs`

`router_state`

`channels` read-only (dashd), not written by live code

`email_threads` declared in messages.db but unused (emaid uses its own emaid.db)