Database Schema Reference

Auto-Generated

This file is auto-generated by scripts/generate-db-docs.mjs. Do not edit manually. Run npm run docs:generate-db to regenerate.

SQLite database using Drizzle ORM over better-sqlite3. Schema source: packages/server/src/db/schema.ts

SQLite Pragmas

Pragma	Value	Purpose
journal_mode	WAL	Concurrent reads during writes
foreign_keys	ON	Enforce referential integrity
synchronous	NORMAL	~10x write speedup vs FULL
busy_timeout	5000	5s wait on write lock contention
cache_size	-64000	64MB page cache

Table of Contents

• Conversations & Messages (2 tables)
• Configuration (2 tables)
• Coordination (3 tables)
• Chat Groups (3 tables)
• Planning & Execution (3 tables)
• Projects (2 tables)
• Agent Registry & Tracking (3 tables)
• Knowledge & Memory (2 tables)
• Observability (2 tables)
• Infrastructure (2 tables)

---

Conversations & Messages

conversations

Primary key: id

Column	Type	Nullable	Default	Notes
id	TEXT	NOT NULL	—	PK
agent_id	TEXT	NOT NULL	—	—
task_id	TEXT	nullable	—	—

Indexes:

• idx_conversations_agent on agentId

---

messages

Primary key: id (auto-increment)

Column	Type	Nullable	Default	Notes
id	INTEGER	NOT NULL	—	PK, auto-increment
conversation_id	TEXT	NOT NULL	—	FK → conversations.id
sender	TEXT	NOT NULL	—	—
content	TEXT	NOT NULL	—	—
from_role	TEXT	nullable	—	—

Indexes:

• idx_messages_conversation on conversationId

---

Configuration

roles

Primary key: id

Column	Type	Nullable	Default	Notes
id	TEXT	NOT NULL	—	PK
name	TEXT	NOT NULL	—	—
description	TEXT	nullable		—
system_prompt	TEXT	nullable		—
color	TEXT	nullable	#888	—
icon	TEXT	nullable	🤖	—
built_in	INTEGER	nullable	0	—

---

settings

Primary key: key

Column	Type	Nullable	Default	Notes
key	TEXT	NOT NULL	—	PK

---

Coordination

file_locks

Primary key: file_path

Column	Type	Nullable	Default	Notes
file_path	TEXT	NOT NULL	—	PK
agent_id	TEXT	NOT NULL	—	—
agent_role	TEXT	NOT NULL	—	—
reason	TEXT	nullable		—
acquired_at	TEXT	nullable	UTC now	—
expires_at	TEXT	NOT NULL	—	—

Indexes:

• idx_file_locks_agent on agentId
• idx_file_locks_project on projectId

---

activity_log

Primary key: id (auto-increment)

Column	Type	Nullable	Default	Notes
id	INTEGER	NOT NULL	—	PK, auto-increment
agent_id	TEXT	NOT NULL	—	—
agent_role	TEXT	NOT NULL	—	—
action_type	TEXT	NOT NULL	—	—
summary	TEXT	NOT NULL	—	—
details	TEXT	nullable	{}	—
timestamp	TEXT	nullable	UTC now	—

Indexes:

• idx_activity_agent on agentId
• idx_activity_type on actionType
• idx_activity_project on projectId

---

decisions

Primary key: id

Column	Type	Nullable	Default	Notes
id	TEXT	NOT NULL	—	PK
agent_id	TEXT	NOT NULL	—	—
agent_role	TEXT	NOT NULL	—	—
lead_id	TEXT	nullable	—	—
project_id	TEXT	nullable	—	—
title	TEXT	NOT NULL	—	—
rationale	TEXT	nullable		—
needs_confirmation	INTEGER	nullable	0	—
status	TEXT	nullable	recorded	—
auto_approved	INTEGER	nullable	0	—
confirmed_at	TEXT	nullable	—	—

Indexes:

• idx_decisions_status on status
• idx_decisions_needs_confirmation on needsConfirmation
• idx_decisions_lead_id on leadId
• idx_decisions_project_id on projectId

---

Chat Groups

chat_groups

Primary key: (name, leadId) composite

Column	Type	Nullable	Default	Notes
name	TEXT	NOT NULL	—	—
lead_id	TEXT	NOT NULL	—	—
project_id	TEXT	nullable	—	—
roles	TEXT	nullable	—	—
archived	INTEGER	nullable	0	—

---

chat_group_members

Primary key: (groupName, leadId, agentId) composite

Column	Type	Nullable	Default	Notes
group_name	TEXT	NOT NULL	—	—
lead_id	TEXT	NOT NULL	—	—
agent_id	TEXT	NOT NULL	—	—

---

chat_group_messages

Primary key: id

Column	Type	Nullable	Default	Notes
id	TEXT	NOT NULL	—	PK
group_name	TEXT	NOT NULL	—	—
lead_id	TEXT	NOT NULL	—	—
from_agent_id	TEXT	NOT NULL	—	—
from_role	TEXT	NOT NULL	—	—
content	TEXT	NOT NULL	—	—
reactions	TEXT	nullable	{}	—

Indexes:

• idx_group_messages_group on groupName, leadId

---

Planning & Execution

dag_tasks

Primary key: (id, leadId) composite

Column	Type	Nullable	Default	Notes
id	TEXT	NOT NULL	—	—
lead_id	TEXT	NOT NULL	—	—
project_id	TEXT	nullable	—	—
team_id	TEXT	NOT NULL	default	—
role	TEXT	NOT NULL	—	—
title	TEXT	nullable	—	—
description	TEXT	NOT NULL		—
files	TEXT	nullable	[]	—
depends_on	TEXT	nullable	[]	—
dag_status	TEXT	nullable	pending	—
priority	INTEGER	nullable	0	—
model	TEXT	nullable	—	—
assigned_agent_id	TEXT	nullable	—	—
failure_reason	TEXT	nullable	—	—
created_at	TEXT	nullable	UTC now	—
started_at	TEXT	nullable	—	—
completed_at	TEXT	nullable	—	—
archived_at	TEXT	nullable	—	—

Indexes:

• idx_dag_tasks_lead on leadId
• idx_dag_tasks_status on dagStatus
• idx_dag_tasks_project on projectId
• idx_dag_tasks_team on teamId
• idx_dag_tasks_id_team on id, teamId

---

agent_memory

Primary key: id (auto-increment)

Column	Type	Nullable	Default	Notes
id	INTEGER	NOT NULL	—	PK, auto-increment
lead_id	TEXT	NOT NULL	—	—
agent_id	TEXT	NOT NULL	—	—
key	TEXT	NOT NULL	—	—
value	TEXT	NOT NULL	—	—

Indexes:

• idx_agent_memory_lead on leadId
• idx_agent_memory_agent on agentId
• idx_agent_memory_unique (unique) on leadId, agentId, key

---

agent_plans

Primary key: agent_id

Column	Type	Nullable	Default	Notes
agent_id	TEXT	NOT NULL	—	PK
lead_id	TEXT	nullable	—	—
plan_json	TEXT	NOT NULL	[]	—

---

Projects

projects

Primary key: id

Column	Type	Nullable	Default	Notes
id	TEXT	NOT NULL	—	PK
name	TEXT	NOT NULL	—	—
description	TEXT	nullable		—
cwd	TEXT	nullable	—	—
created_at	TEXT	nullable	UTC now	—

Indexes:

• idx_projects_status on status

---

project_sessions

Primary key: id (auto-increment)

Column	Type	Nullable	Default	Notes
id	INTEGER	NOT NULL	—	PK, auto-increment
project_id	TEXT	NOT NULL	—	FK → projects.id
task	TEXT	nullable	—	—
started_at	TEXT	nullable	UTC now	—

Indexes:

• idx_project_sessions_project on projectId
• idx_project_sessions_lead on leadId

---

Agent Registry & Tracking

agent_roster

Primary key: agent_id

Column	Type	Nullable	Default	Notes
agent_id	TEXT	NOT NULL	—	PK
role	TEXT	NOT NULL	—	—
model	TEXT	NOT NULL	—	—
session_id	TEXT	nullable	—	—
project_id	TEXT	nullable	—	—
provider	TEXT	nullable	—	—
team_id	TEXT	NOT NULL	default	—
created_at	TEXT	NOT NULL	UTC now	—
updated_at	TEXT	NOT NULL	UTC now	—
last_task_summary	TEXT	nullable	—	—

Indexes:

• idx_agent_roster_status on status
• idx_agent_roster_project on projectId
• idx_agent_roster_project_team on projectId, teamId
• idx_agent_roster_team on teamId
• idx_agent_roster_session on sessionId

---

active_delegations

Primary key: delegation_id

Column	Type	Nullable	Default	Notes
delegation_id	TEXT	NOT NULL	—	PK
agent_id	TEXT	NOT NULL	—	FK → agentRoster.agentId
task	TEXT	NOT NULL	—	—
context	TEXT	nullable	—	—
dag_task_id	TEXT	nullable	—	—
team_id	TEXT	NOT NULL	default	—
created_at	TEXT	NOT NULL	UTC now	—
completed_at	TEXT	nullable	—	—

Indexes:

• idx_ad_agent on agentId, status
• idx_ad_status on status
• idx_ad_dag_task on dagTaskId
• idx_ad_team on teamId

---

agent_file_history

Primary key: (agentId, leadId, filePath) composite

Column	Type	Nullable	Default	Notes
agent_id	TEXT	NOT NULL	—	—
agent_role	TEXT	NOT NULL	—	—
lead_id	TEXT	NOT NULL	—	—
file_path	TEXT	NOT NULL	—	—
first_touched_at	TEXT	nullable	UTC now	—
last_touched_at	TEXT	nullable	UTC now	—

Indexes:

• idx_file_history_file on filePath, leadId
• idx_file_history_agent on agentId, leadId

---

Knowledge & Memory

collective_memory

Primary key: id (auto-increment)

Column	Type	Nullable	Default	Notes
id	INTEGER	NOT NULL	—	PK, auto-increment
key	TEXT	NOT NULL	—	—
value	TEXT	NOT NULL	—	—
project_id	TEXT	nullable		—
created_at	TEXT	nullable	UTC now	—
last_used_at	TEXT	nullable	UTC now	—

Indexes:

• idx_collective_memory_category on category
• idx_collective_memory_key on key
• idx_collective_memory_project on projectId
• idx_collective_memory_cat_key (unique) on category, key, projectId

---

knowledge

Primary key: id (auto-increment)

Column	Type	Nullable	Default	Notes
id	INTEGER	NOT NULL	—	PK, auto-increment
project_id	TEXT	NOT NULL	—	—
key	TEXT	NOT NULL	—	—
content	TEXT	NOT NULL	—	—
created_at	TEXT	NOT NULL	UTC now	—

Indexes:

• idx_knowledge_project_cat_key (unique) on projectId, category, key
• idx_knowledge_project_category on projectId, category

---

Observability

task_cost_records

Primary key: (agentId, dagTaskId, leadId) composite

Column	Type	Nullable	Default	Notes
agent_id	TEXT	NOT NULL	—	—
dag_task_id	TEXT	NOT NULL	—	—
lead_id	TEXT	NOT NULL	—	—
project_id	TEXT	nullable	—	—
input_tokens	INTEGER	nullable	0	—
output_tokens	INTEGER	nullable	0	—
cache_read_tokens	INTEGER	nullable	0	—
cache_write_tokens	INTEGER	nullable	0	—
cost_usd	REAL	nullable	0	—
created_at	TEXT	nullable	UTC now	—

Indexes:

• idx_task_cost_agent on agentId
• idx_task_cost_task on dagTaskId, leadId
• idx_task_cost_project on projectId

---

session_retros

Primary key: id (auto-increment)

Column	Type	Nullable	Default	Notes
id	INTEGER	NOT NULL	—	PK, auto-increment
lead_id	TEXT	NOT NULL	—	—

Indexes:

• idx_session_retros_lead on leadId

---

Infrastructure

timers

Primary key: id

Column	Type	Nullable	Default	Notes
id	TEXT	NOT NULL	—	PK
agent_id	TEXT	NOT NULL	—	—
agent_role	TEXT	NOT NULL	—	—
lead_id	TEXT	nullable	—	—
label	TEXT	NOT NULL	—	—
message	TEXT	NOT NULL	—	—
delay_seconds	INTEGER	NOT NULL	—	—
fire_at	TEXT	NOT NULL	—	—
created_at	TEXT	nullable	UTC now	—

Indexes:

• idx_timers_agent on agentId
• idx_timers_status on status

---

message_queue

Primary key: id (auto-increment)

Column	Type	Nullable	Default	Notes
id	INTEGER	NOT NULL	—	PK, auto-increment
target_agent_id	TEXT	NOT NULL	—	—
source_agent_id	TEXT	nullable	—	—
attempts	INTEGER	NOT NULL	0	—
created_at	TEXT	nullable	UTC now	—
delivered_at	TEXT	nullable	—	—

Indexes:

• idx_mq_target_status on targetAgentId, status
• idx_mq_project on projectId

---

24 tables. Generated from packages/server/src/db/schema.ts.