336 lines
12 KiB
SQL
336 lines
12 KiB
SQL
CREATE TABLE IF NOT EXISTS projects (
|
|
id TEXT PRIMARY KEY,
|
|
slug TEXT NOT NULL UNIQUE,
|
|
name TEXT NOT NULL,
|
|
root_path TEXT NOT NULL UNIQUE,
|
|
default_branch TEXT NOT NULL,
|
|
status TEXT NOT NULL,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS workspaces (
|
|
id TEXT PRIMARY KEY,
|
|
project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
slug TEXT NOT NULL UNIQUE,
|
|
name TEXT NOT NULL,
|
|
root_path TEXT NOT NULL UNIQUE,
|
|
base_branch TEXT NOT NULL,
|
|
worktree_branch TEXT NOT NULL UNIQUE,
|
|
runtime_backend TEXT NOT NULL,
|
|
runtime_ref TEXT NOT NULL DEFAULT '',
|
|
status TEXT NOT NULL,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_workspaces_project ON workspaces(project_id, status, slug);
|
|
|
|
CREATE TABLE IF NOT EXISTS roles (
|
|
name TEXT PRIMARY KEY,
|
|
title TEXT NOT NULL,
|
|
category TEXT NOT NULL,
|
|
description TEXT NOT NULL DEFAULT '',
|
|
is_enabled INTEGER NOT NULL DEFAULT 1,
|
|
is_builtin INTEGER NOT NULL DEFAULT 1,
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS role_prompts (
|
|
id TEXT PRIMARY KEY,
|
|
role_name TEXT NOT NULL REFERENCES roles(name) ON DELETE CASCADE,
|
|
workspace_id TEXT REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
prompt_kind TEXT NOT NULL,
|
|
content_markdown TEXT NOT NULL,
|
|
version INTEGER NOT NULL,
|
|
updated_by TEXT NOT NULL DEFAULT '',
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_role_prompts_global
|
|
ON role_prompts(role_name, prompt_kind)
|
|
WHERE workspace_id IS NULL;
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_role_prompts_workspace
|
|
ON role_prompts(role_name, workspace_id, prompt_kind)
|
|
WHERE workspace_id IS NOT NULL;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_role_prompts_role ON role_prompts(role_name, prompt_kind, workspace_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS role_configs (
|
|
id TEXT PRIMARY KEY,
|
|
role_name TEXT NOT NULL REFERENCES roles(name) ON DELETE CASCADE,
|
|
workspace_id TEXT REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
model TEXT NOT NULL DEFAULT '',
|
|
model_provider TEXT NOT NULL DEFAULT '',
|
|
provider_name TEXT NOT NULL DEFAULT '',
|
|
provider_base_url TEXT NOT NULL DEFAULT '',
|
|
provider_wire_api TEXT NOT NULL DEFAULT '',
|
|
reasoning_effort TEXT NOT NULL DEFAULT '',
|
|
plan_model TEXT NOT NULL DEFAULT '',
|
|
plan_reasoning_effort TEXT NOT NULL DEFAULT '',
|
|
disable_response_storage INTEGER NOT NULL DEFAULT 0,
|
|
shell_env_inherit TEXT NOT NULL DEFAULT 'core',
|
|
shell_env_overrides_json TEXT NOT NULL DEFAULT '{}',
|
|
extra_config_json TEXT NOT NULL DEFAULT '{}',
|
|
version INTEGER NOT NULL,
|
|
updated_by TEXT NOT NULL DEFAULT '',
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_role_configs_global
|
|
ON role_configs(role_name)
|
|
WHERE workspace_id IS NULL;
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_role_configs_workspace
|
|
ON role_configs(role_name, workspace_id)
|
|
WHERE workspace_id IS NOT NULL;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_role_configs_role ON role_configs(role_name, workspace_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS skills (
|
|
id TEXT PRIMARY KEY,
|
|
skill_key TEXT NOT NULL UNIQUE,
|
|
name TEXT NOT NULL,
|
|
description TEXT NOT NULL DEFAULT '',
|
|
source_type TEXT NOT NULL,
|
|
content_markdown TEXT NOT NULL DEFAULT '',
|
|
status TEXT NOT NULL,
|
|
version INTEGER NOT NULL,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_skills_status ON skills(status, name);
|
|
|
|
CREATE TABLE IF NOT EXISTS role_skill_bindings (
|
|
id TEXT PRIMARY KEY,
|
|
role_name TEXT NOT NULL REFERENCES roles(name) ON DELETE CASCADE,
|
|
workspace_id TEXT REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
skill_id TEXT NOT NULL REFERENCES skills(id) ON DELETE CASCADE,
|
|
is_enabled INTEGER NOT NULL DEFAULT 1,
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
config_json TEXT NOT NULL DEFAULT '{}',
|
|
version INTEGER NOT NULL,
|
|
updated_by TEXT NOT NULL DEFAULT '',
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_role_skill_bindings_global
|
|
ON role_skill_bindings(role_name, skill_id)
|
|
WHERE workspace_id IS NULL;
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_role_skill_bindings_workspace
|
|
ON role_skill_bindings(role_name, workspace_id, skill_id)
|
|
WHERE workspace_id IS NOT NULL;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_role_skill_bindings_role ON role_skill_bindings(role_name, workspace_id, sort_order);
|
|
|
|
CREATE TABLE IF NOT EXISTS config_change_logs (
|
|
id TEXT PRIMARY KEY,
|
|
config_domain TEXT NOT NULL,
|
|
entity_id TEXT NOT NULL,
|
|
workspace_id TEXT REFERENCES workspaces(id) ON DELETE SET NULL,
|
|
change_action TEXT NOT NULL,
|
|
before_json TEXT NOT NULL DEFAULT '{}',
|
|
after_json TEXT NOT NULL DEFAULT '{}',
|
|
changed_by TEXT NOT NULL DEFAULT '',
|
|
created_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_config_change_logs_domain ON config_change_logs(config_domain, created_at);
|
|
CREATE INDEX IF NOT EXISTS idx_config_change_logs_workspace ON config_change_logs(workspace_id, created_at);
|
|
|
|
CREATE TABLE IF NOT EXISTS topics (
|
|
id TEXT PRIMARY KEY,
|
|
workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
slug TEXT NOT NULL,
|
|
title TEXT NOT NULL,
|
|
space TEXT NOT NULL,
|
|
status TEXT NOT NULL,
|
|
source_topic_id TEXT REFERENCES topics(id) ON DELETE SET NULL,
|
|
owner_role_name TEXT REFERENCES roles(name) ON DELETE SET NULL,
|
|
summary TEXT NOT NULL DEFAULT '',
|
|
meta_json TEXT NOT NULL DEFAULT '{}',
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
closed_at TEXT,
|
|
UNIQUE(workspace_id, slug)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_topics_workspace_space ON topics(workspace_id, space, updated_at);
|
|
|
|
CREATE TABLE IF NOT EXISTS topic_documents (
|
|
id TEXT PRIMARY KEY,
|
|
topic_id TEXT NOT NULL REFERENCES topics(id) ON DELETE CASCADE,
|
|
kind TEXT NOT NULL,
|
|
content_markdown TEXT NOT NULL,
|
|
version INTEGER NOT NULL,
|
|
updated_by_role_name TEXT REFERENCES roles(name) ON DELETE SET NULL,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
UNIQUE(topic_id, kind)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS messages (
|
|
id TEXT PRIMARY KEY,
|
|
workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
topic_id TEXT NOT NULL REFERENCES topics(id) ON DELETE CASCADE,
|
|
from_role_name TEXT NOT NULL REFERENCES roles(name),
|
|
to_expr TEXT NOT NULL,
|
|
type TEXT NOT NULL,
|
|
stage TEXT NOT NULL,
|
|
round INTEGER,
|
|
reply_to_message_id TEXT REFERENCES messages(id) ON DELETE SET NULL,
|
|
body_markdown TEXT NOT NULL,
|
|
created_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_messages_workspace_topic ON messages(workspace_id, topic_id, created_at);
|
|
CREATE INDEX IF NOT EXISTS idx_messages_reply_to ON messages(reply_to_message_id, created_at);
|
|
|
|
CREATE TABLE IF NOT EXISTS message_deliveries (
|
|
message_id TEXT NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
|
|
recipient_role_name TEXT NOT NULL REFERENCES roles(name),
|
|
state TEXT NOT NULL,
|
|
delivered_at TEXT NOT NULL,
|
|
read_at TEXT,
|
|
archived_at TEXT,
|
|
updated_at TEXT NOT NULL,
|
|
PRIMARY KEY (message_id, recipient_role_name)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_message_deliveries_role ON message_deliveries(recipient_role_name, state, updated_at);
|
|
|
|
CREATE TABLE IF NOT EXISTS role_threads (
|
|
workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
topic_id TEXT NOT NULL REFERENCES topics(id) ON DELETE CASCADE,
|
|
role_name TEXT NOT NULL REFERENCES roles(name),
|
|
thread_id TEXT NOT NULL,
|
|
last_message_id TEXT REFERENCES messages(id) ON DELETE SET NULL,
|
|
created_at TEXT NOT NULL,
|
|
last_used_at TEXT NOT NULL,
|
|
PRIMARY KEY (workspace_id, topic_id, role_name)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_role_threads_role ON role_threads(role_name, last_used_at);
|
|
|
|
CREATE TABLE IF NOT EXISTS requirements (
|
|
id TEXT PRIMARY KEY,
|
|
workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
topic_id TEXT NOT NULL REFERENCES topics(id) ON DELETE CASCADE,
|
|
title TEXT NOT NULL,
|
|
body_markdown TEXT NOT NULL,
|
|
status TEXT NOT NULL,
|
|
priority INTEGER NOT NULL,
|
|
created_by_role_name TEXT NOT NULL REFERENCES roles(name),
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
completed_at TEXT
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_requirements_workspace_status ON requirements(workspace_id, status, priority, created_at);
|
|
CREATE INDEX IF NOT EXISTS idx_requirements_topic ON requirements(topic_id, created_at);
|
|
|
|
CREATE TABLE IF NOT EXISTS discovery_rounds (
|
|
id TEXT PRIMARY KEY,
|
|
workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
topic_id TEXT NOT NULL REFERENCES topics(id) ON DELETE CASCADE,
|
|
phase TEXT NOT NULL,
|
|
request_document_id TEXT REFERENCES topic_documents(id) ON DELETE SET NULL,
|
|
result_document_id TEXT REFERENCES topic_documents(id) ON DELETE SET NULL,
|
|
started_at TEXT NOT NULL,
|
|
completed_at TEXT,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_discovery_rounds_topic ON discovery_rounds(topic_id, started_at);
|
|
|
|
CREATE TABLE IF NOT EXISTS discovery_candidates (
|
|
id TEXT PRIMARY KEY,
|
|
round_id TEXT NOT NULL REFERENCES discovery_rounds(id) ON DELETE CASCADE,
|
|
proposer_role_name TEXT NOT NULL REFERENCES roles(name),
|
|
status TEXT NOT NULL,
|
|
title TEXT NOT NULL,
|
|
problem TEXT NOT NULL DEFAULT '',
|
|
evidence TEXT NOT NULL DEFAULT '',
|
|
proposal TEXT NOT NULL DEFAULT '',
|
|
expected_impact TEXT NOT NULL DEFAULT '',
|
|
risk TEXT NOT NULL DEFAULT '',
|
|
how_to_verify TEXT NOT NULL DEFAULT '',
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_discovery_candidates_round ON discovery_candidates(round_id, status, updated_at);
|
|
|
|
CREATE TABLE IF NOT EXISTS discovery_votes (
|
|
id TEXT PRIMARY KEY,
|
|
round_id TEXT NOT NULL REFERENCES discovery_rounds(id) ON DELETE CASCADE,
|
|
candidate_id TEXT NOT NULL REFERENCES discovery_candidates(id) ON DELETE CASCADE,
|
|
voter_role_name TEXT NOT NULL REFERENCES roles(name),
|
|
vote TEXT NOT NULL,
|
|
reason TEXT NOT NULL DEFAULT '',
|
|
created_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_discovery_votes_unique ON discovery_votes(candidate_id, voter_role_name);
|
|
CREATE INDEX IF NOT EXISTS idx_discovery_votes_round ON discovery_votes(round_id, created_at);
|
|
|
|
CREATE TABLE IF NOT EXISTS workflow_runs (
|
|
id TEXT PRIMARY KEY,
|
|
workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
topic_id TEXT NOT NULL REFERENCES topics(id) ON DELETE CASCADE,
|
|
role_name TEXT NOT NULL REFERENCES roles(name),
|
|
stage TEXT NOT NULL,
|
|
mode TEXT NOT NULL DEFAULT '',
|
|
status TEXT NOT NULL,
|
|
request_message_id TEXT REFERENCES messages(id) ON DELETE SET NULL,
|
|
thread_id TEXT NOT NULL DEFAULT '',
|
|
prior_thread_id TEXT NOT NULL DEFAULT '',
|
|
config_snapshot_json TEXT NOT NULL DEFAULT '{}',
|
|
command_json TEXT NOT NULL DEFAULT '[]',
|
|
reply_message_id TEXT REFERENCES messages(id) ON DELETE SET NULL,
|
|
exit_code INTEGER NOT NULL DEFAULT 0,
|
|
started_at TEXT NOT NULL,
|
|
completed_at TEXT,
|
|
error_message TEXT NOT NULL DEFAULT ''
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_workflow_runs_topic ON workflow_runs(topic_id, started_at);
|
|
CREATE INDEX IF NOT EXISTS idx_workflow_runs_role ON workflow_runs(role_name, status, started_at);
|
|
|
|
CREATE TABLE IF NOT EXISTS workflow_run_logs (
|
|
run_id TEXT NOT NULL REFERENCES workflow_runs(id) ON DELETE CASCADE,
|
|
seq INTEGER NOT NULL,
|
|
stream TEXT NOT NULL,
|
|
content TEXT NOT NULL,
|
|
created_at TEXT NOT NULL,
|
|
PRIMARY KEY (run_id, seq)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS merge_requests (
|
|
id TEXT PRIMARY KEY,
|
|
workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
topic_id TEXT NOT NULL REFERENCES topics(id) ON DELETE CASCADE,
|
|
workflow_run_id TEXT REFERENCES workflow_runs(id) ON DELETE SET NULL,
|
|
requested_by_role_name TEXT REFERENCES roles(name) ON DELETE SET NULL,
|
|
target_branch TEXT NOT NULL,
|
|
status TEXT NOT NULL,
|
|
summary TEXT NOT NULL DEFAULT '',
|
|
files_changed INTEGER NOT NULL DEFAULT 0,
|
|
insertions INTEGER NOT NULL DEFAULT 0,
|
|
deletions INTEGER NOT NULL DEFAULT 0,
|
|
changed_files_json TEXT NOT NULL DEFAULT '[]',
|
|
created_at TEXT NOT NULL,
|
|
merged_at TEXT,
|
|
error_message TEXT NOT NULL DEFAULT ''
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_merge_requests_topic ON merge_requests(topic_id, created_at);
|