agent-orchestrator/schema/canonical.sql

-- canonical.sql — Authoritative DDL for all AGOR SQLite databases.
-- Both Tauri (Rust) and Electrobun (Bun) stacks MUST match this schema.
-- DBs: settings.db, btmsg.db, search.db. See docs/SWITCHING.md.

PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;

-- ── VERSION TRACKING ──────────────────────────────────────────────────
-- Schema version fence. One row per database file.
CREATE TABLE IF NOT EXISTS schema_version (
  version            INTEGER NOT NULL,
  migration_source   TEXT,              -- e.g. 'tauri', 'electrobun', 'migrate-db'
  migration_timestamp TEXT              -- ISO-8601 when last migrated
);

-- ── settings.db TABLES ────────────────────────────────────────────────

-- Key-value application settings (theme, fonts, shell, cwd, etc.)
CREATE TABLE IF NOT EXISTS settings (key TEXT PRIMARY KEY, value TEXT NOT NULL);

-- Project configurations stored as JSON blobs.
CREATE TABLE IF NOT EXISTS projects (id TEXT PRIMARY KEY, config TEXT NOT NULL);

-- Workspace groups (sidebar tabs).
CREATE TABLE IF NOT EXISTS groups (
  id TEXT PRIMARY KEY, name TEXT NOT NULL,
  icon TEXT NOT NULL, position INTEGER NOT NULL
);

-- User-created custom themes.
CREATE TABLE IF NOT EXISTS custom_themes (
  id TEXT PRIMARY KEY, name TEXT NOT NULL,
  palette TEXT NOT NULL  -- JSON blob (color map)
);

-- User-customized keyboard shortcuts.
CREATE TABLE IF NOT EXISTS keybindings (id TEXT PRIMARY KEY, chord TEXT NOT NULL);

-- Agent sessions per project (provider-agnostic).
CREATE TABLE IF NOT EXISTS agent_sessions (
  project_id    TEXT NOT NULL,
  session_id    TEXT PRIMARY KEY,
  provider      TEXT NOT NULL,
  status        TEXT NOT NULL DEFAULT 'idle'
                CHECK (status IN ('idle','running','error','stopped','completed')),
  cost_usd      REAL NOT NULL DEFAULT 0,
  input_tokens  INTEGER NOT NULL DEFAULT 0,
  output_tokens INTEGER NOT NULL DEFAULT 0,
  model         TEXT NOT NULL DEFAULT '',
  error         TEXT,
  created_at    INTEGER NOT NULL,
  updated_at    INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_agent_sessions_project ON agent_sessions(project_id);

-- Individual agent messages within a session.
CREATE TABLE IF NOT EXISTS agent_messages (
  session_id    TEXT NOT NULL,
  msg_id        TEXT NOT NULL,
  role          TEXT NOT NULL,
  content       TEXT NOT NULL DEFAULT '',
  tool_name     TEXT,
  tool_input    TEXT,
  timestamp     INTEGER NOT NULL,
  cost_usd      REAL NOT NULL DEFAULT 0,
  input_tokens  INTEGER NOT NULL DEFAULT 0,
  output_tokens INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (session_id, msg_id),
  FOREIGN KEY (session_id) REFERENCES agent_sessions(session_id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_agent_messages_session ON agent_messages(session_id, timestamp);

-- Historical session metrics (cost, tokens, turns) per project.
CREATE TABLE IF NOT EXISTS session_metrics (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  project_id      TEXT NOT NULL,
  session_id      TEXT NOT NULL,
  start_time      INTEGER NOT NULL,
  end_time        INTEGER NOT NULL,
  peak_tokens     INTEGER DEFAULT 0,
  turn_count      INTEGER DEFAULT 0,
  tool_call_count INTEGER DEFAULT 0,
  cost_usd        REAL DEFAULT 0,
  model           TEXT,
  status          TEXT NOT NULL,
  error_message   TEXT
);
CREATE INDEX IF NOT EXISTS idx_session_metrics_project ON session_metrics(project_id);

-- Session anchors — preserved turns surviving compaction.
CREATE TABLE IF NOT EXISTS session_anchors (
  id               TEXT PRIMARY KEY,
  project_id       TEXT NOT NULL,
  message_id       TEXT NOT NULL,
  anchor_type      TEXT NOT NULL
                   CHECK (anchor_type IN ('auto','pinned','promoted')),
  content          TEXT NOT NULL,
  estimated_tokens INTEGER NOT NULL,
  turn_index       INTEGER NOT NULL DEFAULT 0,
  created_at       INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_session_anchors_project ON session_anchors(project_id);

-- Remote relay machine connections.
CREATE TABLE IF NOT EXISTS remote_machines (
  id           TEXT PRIMARY KEY,
  label        TEXT NOT NULL,
  url          TEXT NOT NULL,
  token        TEXT NOT NULL,
  auto_connect INTEGER NOT NULL DEFAULT 0,
  spki_pins    TEXT NOT NULL DEFAULT '[]',  -- JSON array of SPKI hashes
  created_at   INTEGER NOT NULL,
  updated_at   INTEGER NOT NULL
);

-- Legacy v2 layout state (single-row).
CREATE TABLE IF NOT EXISTS layout_state (
  id INTEGER PRIMARY KEY CHECK (id = 1),
  preset TEXT NOT NULL DEFAULT '1-col', pane_ids TEXT NOT NULL DEFAULT '[]'
);

-- Legacy v2 terminal sessions.
CREATE TABLE IF NOT EXISTS sessions (
  id           TEXT PRIMARY KEY,
  type         TEXT NOT NULL,
  title        TEXT NOT NULL,
  shell        TEXT,
  cwd          TEXT,
  args         TEXT,
  created_at   INTEGER NOT NULL,
  last_used_at INTEGER NOT NULL,
  group_name   TEXT DEFAULT '',
  project_id   TEXT DEFAULT ''
);

-- SSH connection profiles.
CREATE TABLE IF NOT EXISTS ssh_sessions (
  id           TEXT PRIMARY KEY,
  name         TEXT NOT NULL,
  host         TEXT NOT NULL,
  port         INTEGER NOT NULL DEFAULT 22,
  username     TEXT NOT NULL,
  key_file     TEXT DEFAULT '',
  folder       TEXT DEFAULT '',
  color        TEXT DEFAULT '#89b4fa',
  created_at   INTEGER NOT NULL,
  last_used_at INTEGER NOT NULL
);

-- ── btmsg.db TABLES — inter-agent messaging & task board ──────────────

-- Registered agents (Tier 1 management + Tier 2 project).
CREATE TABLE IF NOT EXISTS agents (
  id              TEXT PRIMARY KEY,
  name            TEXT NOT NULL,
  role            TEXT NOT NULL,
  group_id        TEXT NOT NULL,
  tier            INTEGER NOT NULL DEFAULT 2,
  model           TEXT,
  cwd             TEXT,
  system_prompt   TEXT,
  status          TEXT DEFAULT 'stopped',
  last_active_at  TEXT,
  created_at      TEXT DEFAULT (datetime('now'))
);

-- Agent-to-agent visibility ACL.
CREATE TABLE IF NOT EXISTS contacts (
  agent_id TEXT NOT NULL, contact_id TEXT NOT NULL,
  PRIMARY KEY (agent_id, contact_id)
);

-- Direct messages between agents.
CREATE TABLE IF NOT EXISTS messages (
  id              TEXT PRIMARY KEY,
  from_agent      TEXT NOT NULL,
  to_agent        TEXT NOT NULL,
  content         TEXT NOT NULL,
  read            INTEGER DEFAULT 0,
  reply_to        TEXT,
  group_id        TEXT NOT NULL,
  sender_group_id TEXT,
  created_at      TEXT DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_messages_to   ON messages(to_agent, read);
CREATE INDEX IF NOT EXISTS idx_messages_from ON messages(from_agent);

-- Broadcast channels within a group.
CREATE TABLE IF NOT EXISTS channels (
  id TEXT PRIMARY KEY, name TEXT NOT NULL, group_id TEXT NOT NULL,
  created_by TEXT NOT NULL, created_at TEXT DEFAULT (datetime('now'))
);

-- Channel membership (many-to-many).
CREATE TABLE IF NOT EXISTS channel_members (
  channel_id TEXT NOT NULL, agent_id TEXT NOT NULL,
  joined_at TEXT DEFAULT (datetime('now')),
  PRIMARY KEY (channel_id, agent_id)
);

-- Messages posted to channels.
CREATE TABLE IF NOT EXISTS channel_messages (
  id         TEXT PRIMARY KEY,
  channel_id TEXT NOT NULL,
  from_agent TEXT NOT NULL,
  content    TEXT NOT NULL,
  created_at TEXT DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_channel_messages ON channel_messages(channel_id, created_at);

-- Agent liveness heartbeats (unix epoch seconds).
CREATE TABLE IF NOT EXISTS heartbeats (agent_id TEXT PRIMARY KEY, timestamp INTEGER NOT NULL);

-- Undeliverable messages (recipient not found, etc.)
CREATE TABLE IF NOT EXISTS dead_letter_queue (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  from_agent TEXT NOT NULL,
  to_agent   TEXT NOT NULL,
  content    TEXT NOT NULL,
  error      TEXT NOT NULL,
  created_at TEXT DEFAULT (datetime('now'))
);

-- Audit trail for agent actions.
CREATE TABLE IF NOT EXISTS audit_log (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  agent_id   TEXT NOT NULL,
  event_type TEXT NOT NULL,
  detail     TEXT NOT NULL,
  created_at TEXT DEFAULT (datetime('now'))
);

-- Per-session message acknowledgment (prevents re-processing).
CREATE TABLE IF NOT EXISTS seen_messages (
  session_id TEXT NOT NULL, message_id TEXT NOT NULL,
  seen_at INTEGER NOT NULL DEFAULT (unixepoch()),
  PRIMARY KEY (session_id, message_id)
);
CREATE INDEX IF NOT EXISTS idx_seen_messages_session ON seen_messages(session_id);

-- Kanban task board.
CREATE TABLE IF NOT EXISTS tasks (
  id             TEXT PRIMARY KEY,
  title          TEXT NOT NULL,
  description    TEXT DEFAULT '',
  status         TEXT DEFAULT 'todo'
                 CHECK (status IN ('todo','progress','review','done','blocked')),
  priority       TEXT DEFAULT 'medium'
                 CHECK (priority IN ('low','medium','high')),
  assigned_to    TEXT,
  created_by     TEXT NOT NULL,
  group_id       TEXT NOT NULL,
  parent_task_id TEXT,
  sort_order     INTEGER DEFAULT 0,
  created_at     TEXT DEFAULT (datetime('now')),
  updated_at     TEXT DEFAULT (datetime('now')),
  version        INTEGER DEFAULT 1  -- optimistic locking
);
CREATE INDEX IF NOT EXISTS idx_tasks_group  ON tasks(group_id);
CREATE INDEX IF NOT EXISTS idx_tasks_status ON tasks(status);

-- Task discussion comments.
CREATE TABLE IF NOT EXISTS task_comments (
  id         TEXT PRIMARY KEY,
  task_id    TEXT NOT NULL,
  agent_id   TEXT NOT NULL,
  content    TEXT NOT NULL,
  created_at TEXT DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_task_comments_task ON task_comments(task_id);

-- ── search.db TABLES — FTS5 full-text search ─────────────────────────

-- Agent message search index.
CREATE VIRTUAL TABLE IF NOT EXISTS search_messages USING fts5(
  session_id,
  role,
  content,
  timestamp
);

-- Task search index.
CREATE VIRTUAL TABLE IF NOT EXISTS search_tasks USING fts5(
  task_id,
  title,
  description,
  status,
  assigned_to
);

-- Inter-agent message search index.
CREATE VIRTUAL TABLE IF NOT EXISTS search_btmsg USING fts5(
  message_id,
  from_agent,
  to_agent,
  content,
  channel_name
);