scud/db/
schema.rs

1//! Database schema creation and migrations.
2
3use anyhow::Result;
4use rusqlite::Connection;
5
6pub fn create_tables(conn: &Connection) -> Result<()> {
7    conn.execute_batch(
8        r#"
9        -- Swarm/spawn sessions
10        CREATE TABLE IF NOT EXISTS sessions (
11            session_id TEXT PRIMARY KEY,
12            session_name TEXT NOT NULL,
13            tag TEXT NOT NULL,
14            terminal_mode TEXT NOT NULL,
15            working_dir TEXT NOT NULL,
16            round_size INTEGER,
17            started_at TEXT NOT NULL,
18            completed_at TEXT
19        );
20        CREATE INDEX IF NOT EXISTS idx_sessions_tag ON sessions(tag);
21        CREATE INDEX IF NOT EXISTS idx_sessions_started ON sessions(started_at);
22
23        -- Agent runs (one per task execution attempt)
24        CREATE TABLE IF NOT EXISTS agent_runs (
25            id INTEGER PRIMARY KEY,
26            session_id TEXT NOT NULL,
27            task_id TEXT NOT NULL,
28            tag TEXT NOT NULL,
29            wave_number INTEGER,
30            round_number INTEGER,
31            harness TEXT,
32            model TEXT,
33            prompt TEXT,
34            window_name TEXT,
35            spawned_at TEXT NOT NULL,
36            completed_at TEXT,
37            success INTEGER,
38            duration_ms INTEGER,
39            exit_code INTEGER
40        );
41        CREATE INDEX IF NOT EXISTS idx_agent_runs_task ON agent_runs(task_id);
42        CREATE INDEX IF NOT EXISTS idx_agent_runs_session ON agent_runs(session_id);
43
44        -- Events (lifecycle, tools, files, dependencies)
45        CREATE TABLE IF NOT EXISTS events (
46            id INTEGER PRIMARY KEY,
47            timestamp TEXT NOT NULL,
48            session_id TEXT NOT NULL,
49            task_id TEXT NOT NULL,
50            agent_run_id INTEGER,
51            kind TEXT NOT NULL,
52            success INTEGER,
53            duration_ms INTEGER,
54            tool_name TEXT,
55            file_path TEXT,
56            dependency_id TEXT,
57            reason TEXT,
58            data TEXT
59        );
60        CREATE INDEX IF NOT EXISTS idx_events_session_task ON events(session_id, task_id);
61        CREATE INDEX IF NOT EXISTS idx_events_timestamp ON events(timestamp);
62        CREATE INDEX IF NOT EXISTS idx_events_kind ON events(kind);
63
64        -- Transcript messages (from Claude Code JSONL)
65        CREATE TABLE IF NOT EXISTS transcript_messages (
66            id INTEGER PRIMARY KEY,
67            claude_session_id TEXT NOT NULL,
68            scud_session_id TEXT,
69            task_id TEXT,
70            timestamp TEXT NOT NULL,
71            uuid TEXT NOT NULL,
72            parent_uuid TEXT,
73            role TEXT NOT NULL,
74            content TEXT NOT NULL,
75            model TEXT,
76            input_tokens INTEGER,
77            output_tokens INTEGER,
78            FOREIGN KEY (scud_session_id) REFERENCES sessions(session_id)
79        );
80        CREATE INDEX IF NOT EXISTS idx_transcript_claude_session ON transcript_messages(claude_session_id);
81        CREATE INDEX IF NOT EXISTS idx_transcript_scud_session ON transcript_messages(scud_session_id);
82        CREATE INDEX IF NOT EXISTS idx_transcript_timestamp ON transcript_messages(timestamp);
83        CREATE INDEX IF NOT EXISTS idx_transcript_task ON transcript_messages(task_id);
84
85        -- Tool calls (extracted from transcripts)
86        CREATE TABLE IF NOT EXISTS tool_calls (
87            id INTEGER PRIMARY KEY,
88            message_id INTEGER NOT NULL,
89            claude_session_id TEXT NOT NULL,
90            timestamp TEXT NOT NULL,
91            tool_id TEXT NOT NULL,
92            tool_name TEXT NOT NULL,
93            input_json TEXT,
94            FOREIGN KEY (message_id) REFERENCES transcript_messages(id)
95        );
96        CREATE INDEX IF NOT EXISTS idx_tool_calls_session ON tool_calls(claude_session_id);
97        CREATE INDEX IF NOT EXISTS idx_tool_calls_name ON tool_calls(tool_name);
98
99        -- Tool results (extracted from transcripts)
100        CREATE TABLE IF NOT EXISTS tool_results (
101            id INTEGER PRIMARY KEY,
102            message_id INTEGER NOT NULL,
103            claude_session_id TEXT NOT NULL,
104            timestamp TEXT NOT NULL,
105            tool_use_id TEXT NOT NULL,
106            content TEXT,
107            is_error INTEGER NOT NULL DEFAULT 0,
108            FOREIGN KEY (message_id) REFERENCES transcript_messages(id)
109        );
110        CREATE INDEX IF NOT EXISTS idx_tool_results_session ON tool_results(claude_session_id);
111        CREATE INDEX IF NOT EXISTS idx_tool_results_tool_use ON tool_results(tool_use_id);
112
113        -- Validation runs
114        CREATE TABLE IF NOT EXISTS validation_runs (
115            id INTEGER PRIMARY KEY,
116            session_id TEXT NOT NULL,
117            wave_number INTEGER NOT NULL,
118            all_passed INTEGER NOT NULL,
119            started_at TEXT NOT NULL,
120            completed_at TEXT,
121            FOREIGN KEY (session_id) REFERENCES sessions(session_id)
122        );
123
124        -- Validation command results
125        CREATE TABLE IF NOT EXISTS validation_commands (
126            id INTEGER PRIMARY KEY,
127            validation_run_id INTEGER NOT NULL,
128            command TEXT NOT NULL,
129            passed INTEGER NOT NULL,
130            exit_code INTEGER,
131            stdout TEXT,
132            stderr TEXT,
133            duration_secs REAL,
134            FOREIGN KEY (validation_run_id) REFERENCES validation_runs(id)
135        );
136
137        -- Salvo worktrees
138        CREATE TABLE IF NOT EXISTS salvo_worktrees (
139            id INTEGER PRIMARY KEY,
140            tag TEXT NOT NULL UNIQUE,
141            worktree_path TEXT NOT NULL,
142            branch_name TEXT NOT NULL,
143            created_at TEXT NOT NULL,
144            last_sync_at TEXT
145        );
146        CREATE INDEX IF NOT EXISTS idx_salvo_worktrees_tag ON salvo_worktrees(tag);
147
148        -- Schema version for migrations
149        CREATE TABLE IF NOT EXISTS schema_version (
150            version INTEGER PRIMARY KEY
151        );
152        INSERT OR IGNORE INTO schema_version (version) VALUES (1);
153    "#,
154    )?;
155    Ok(())
156}
scud/db/schema.rs

scud/db/
schema.rs
