数据库设计 / Database Design

OpenCowork 使用 SQLite + better-sqlite3 作为本地持久化核心。数据库文件位于 ~/.open-cowork/data.db，初始化入口在 src/main/db/database.ts。

基础配置 / Database config

项	值
引擎	`better-sqlite3`
位置	`~/.open-cowork/data.db`
模式	WAL
外键	`foreign_keys = ON`
迁移方式	集中建表 + 幂等列补齐

主要数据域 / Main domains

1) 会话与消息 / Sessions & messages

CREATE TABLE sessions (
  id TEXT PRIMARY KEY,
  title TEXT NOT NULL,
  mode TEXT NOT NULL DEFAULT 'chat',
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  message_count INTEGER NOT NULL DEFAULT 0,
  working_folder TEXT,
  pinned INTEGER DEFAULT 0,
  icon TEXT,
  plugin_id TEXT,
  ssh_connection_id TEXT,
  project_id TEXT,
  provider_id TEXT,
  model_id TEXT,
  plan_id TEXT,
  external_chat_id TEXT
);

CREATE TABLE messages (
  id TEXT PRIMARY KEY,
  session_id TEXT NOT NULL,
  role TEXT NOT NULL,
  content TEXT NOT NULL,
  meta TEXT,
  created_at INTEGER NOT NULL,
  usage TEXT,
  sort_order INTEGER NOT NULL,
  FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE
);

2) 项目、计划、任务 / Projects, plans, tasks

CREATE TABLE projects (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  working_folder TEXT,
  ssh_connection_id TEXT,
  plugin_id TEXT,
  pinned INTEGER DEFAULT 0,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL
);

CREATE TABLE plans (
  id TEXT PRIMARY KEY,
  session_id TEXT NOT NULL,
  title TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'drafting',
  file_path TEXT,
  content TEXT,
  spec_json TEXT,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE
);

CREATE TABLE tasks (
  id TEXT PRIMARY KEY,
  session_id TEXT NOT NULL,
  plan_id TEXT,
  subject TEXT NOT NULL,
  description TEXT NOT NULL DEFAULT '',
  active_form TEXT,
  status TEXT NOT NULL DEFAULT 'pending',
  owner TEXT,
  blocks TEXT DEFAULT '[]',
  blocked_by TEXT DEFAULT '[]',
  metadata TEXT,
  sort_order INTEGER NOT NULL DEFAULT 0,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE,
  FOREIGN KEY (plan_id) REFERENCES plans(id) ON DELETE SET NULL
);

3) 计划与目标 / Goals

CREATE TABLE session_goals (
  session_id TEXT PRIMARY KEY NOT NULL,
  goal_id TEXT NOT NULL,
  objective TEXT NOT NULL,
  status TEXT NOT NULL CHECK(status IN ('active', 'paused', 'budget_limited', 'complete')),
  token_budget INTEGER,
  tokens_used INTEGER NOT NULL DEFAULT 0,
  time_used_seconds INTEGER NOT NULL DEFAULT 0,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL
);

CREATE TABLE session_goal_events (
  id TEXT PRIMARY KEY,
  session_id TEXT NOT NULL,
  goal_id TEXT,
  event_type TEXT NOT NULL,
  message TEXT,
  metadata_json TEXT,
  created_at INTEGER NOT NULL
);

4) Cron / Scheduling

cron_jobs
cron_runs
cron_run_messages
cron_run_logs

cron_jobs 保存调度定义，cron_runs 保存每次运行快照，cron_run_messages / cron_run_logs 保存过程记录。

5) SSH / Remote access

ssh_groups
ssh_connections

SSH 不只是连接配置，还承载远程目录、身份认证、默认目录、代理跳转、保活间隔等信息。

6) Wiki / Knowledge base

wiki_documents
wiki_sections
wiki_section_sources
wiki_project_state
wiki_generation_runs

7) 可观测性 / Observability

usage_events
draw_runs
agent_change_sets
agent_file_changes
qq_wakeup_windows

迁移策略 / Migration strategy

OpenCowork 采用的是“加列不删列”的演进策略：

先集中 CREATE TABLE IF NOT EXISTS
再通过 ensureColumn() 或 ALTER TABLE ... ADD COLUMN 补齐旧列
再做少量回填，例如把旧 sessions 迁移到 projects

这种策略简单、稳定，也适合桌面应用的本地数据库。

设计结论 / Design takeaways

数据库已经从“聊天记录仓库”演进成“本地 Agent 工作台的核心存储层”
主线关系是：project -> session -> messages / plans / tasks
Cron、SSH、Wiki、Goal、Usage 都是一级对象，不是附属功能
文档里如果看到旧 schema，应该以 src/main/db/database.ts 为准

数据库设计

On this page