routa_core/db/
mod.rs

1//! SQLite database layer for the Routa desktop backend.
2//!
3//! Uses rusqlite with WAL mode for concurrent read performance.
4//! All database operations are executed via `tokio::task::spawn_blocking`
5//! to avoid blocking the async runtime.
6
7use rusqlite::Connection;
8use std::path::Path;
9use std::sync::{Arc, Mutex};
10
11use crate::error::ServerError;
12
13/// Thread-safe handle to the SQLite database.
14#[derive(Clone)]
15pub struct Database {
16    conn: Arc<Mutex<Connection>>,
17}
18
19impl Database {
20    fn ignore_duplicate_column(
21        result: Result<usize, rusqlite::Error>,
22    ) -> Result<(), rusqlite::Error> {
23        match result {
24            Ok(_) => Ok(()),
25            Err(error)
26                if error
27                    .to_string()
28                    .to_ascii_lowercase()
29                    .contains("duplicate column name") =>
30            {
31                Ok(())
32            }
33            Err(error) => Err(error),
34        }
35    }
36
37    /// Open (or create) a SQLite database at the given path.
38    pub fn open(db_path: &str) -> Result<Self, ServerError> {
39        let path = Path::new(db_path);
40        if let Some(parent) = path.parent() {
41            std::fs::create_dir_all(parent).ok();
42        }
43
44        let conn = Connection::open(db_path)
45            .map_err(|e| ServerError::Database(format!("Failed to open database: {}", e)))?;
46
47        conn.execute_batch("PRAGMA journal_mode=WAL; PRAGMA foreign_keys=ON;")
48            .map_err(|e| ServerError::Database(format!("Failed to set pragmas: {}", e)))?;
49
50        let db = Self {
51            conn: Arc::new(Mutex::new(conn)),
52        };
53
54        db.initialize_tables()?;
55
56        tracing::info!("SQLite database opened at: {}", db_path);
57        Ok(db)
58    }
59
60    /// Open an in-memory database (for testing).
61    pub fn open_in_memory() -> Result<Self, ServerError> {
62        let conn = Connection::open_in_memory()
63            .map_err(|e| ServerError::Database(format!("Failed to open in-memory db: {}", e)))?;
64
65        conn.execute_batch("PRAGMA foreign_keys=ON;")
66            .map_err(|e| ServerError::Database(format!("Failed to set pragmas: {}", e)))?;
67
68        let db = Self {
69            conn: Arc::new(Mutex::new(conn)),
70        };
71
72        db.initialize_tables()?;
73        Ok(db)
74    }
75
76    /// Execute a closure with access to the database connection.
77    /// Automatically handles locking and error conversion.
78    pub fn with_conn<F, T>(&self, f: F) -> Result<T, ServerError>
79    where
80        F: FnOnce(&Connection) -> Result<T, rusqlite::Error>,
81    {
82        let conn = self
83            .conn
84            .lock()
85            .map_err(|e| ServerError::Database(format!("Lock poisoned: {}", e)))?;
86        f(&conn).map_err(|e| ServerError::Database(e.to_string()))
87    }
88
89    /// Execute a closure with access to the database connection (async-friendly).
90    pub async fn with_conn_async<F, T>(&self, f: F) -> Result<T, ServerError>
91    where
92        F: FnOnce(&Connection) -> Result<T, rusqlite::Error> + Send + 'static,
93        T: Send + 'static,
94    {
95        let db = self.clone();
96        tokio::task::spawn_blocking(move || db.with_conn(f))
97            .await
98            .map_err(|e| ServerError::Database(format!("Task join error: {}", e)))?
99    }
100
101    /// Create all tables if they don't exist.
102    fn initialize_tables(&self) -> Result<(), ServerError> {
103        self.with_conn(|conn| {
104            conn.execute_batch(
105                "
106                CREATE TABLE IF NOT EXISTS workspaces (
107                    id              TEXT PRIMARY KEY,
108                    title           TEXT NOT NULL,
109                    status          TEXT NOT NULL DEFAULT 'active',
110                    metadata        TEXT NOT NULL DEFAULT '{}',
111                    created_at      INTEGER NOT NULL,
112                    updated_at      INTEGER NOT NULL
113                );
114
115                CREATE TABLE IF NOT EXISTS codebases (
116                    id              TEXT PRIMARY KEY,
117                    workspace_id    TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
118                    repo_path       TEXT NOT NULL,
119                    branch          TEXT,
120                    label           TEXT,
121                    is_default      INTEGER NOT NULL DEFAULT 0,
122                    created_at      INTEGER NOT NULL,
123                    updated_at      INTEGER NOT NULL
124                );
125                CREATE INDEX IF NOT EXISTS idx_codebases_workspace ON codebases(workspace_id);
126
127                CREATE TABLE IF NOT EXISTS acp_sessions (
128                    id              TEXT PRIMARY KEY,
129                    name            TEXT,
130                    cwd             TEXT NOT NULL,
131                    branch          TEXT,
132                    workspace_id    TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
133                    routa_agent_id  TEXT,
134                    provider        TEXT,
135                    role            TEXT,
136                    mode_id         TEXT,
137                    first_prompt_sent INTEGER DEFAULT 0,
138                    message_history TEXT NOT NULL DEFAULT '[]',
139                    created_at      INTEGER NOT NULL,
140                    updated_at      INTEGER NOT NULL
141                );
142                CREATE INDEX IF NOT EXISTS idx_acp_sessions_workspace ON acp_sessions(workspace_id);
143
144                CREATE TABLE IF NOT EXISTS skills (
145                    id              TEXT PRIMARY KEY,
146                    name            TEXT NOT NULL,
147                    description     TEXT NOT NULL DEFAULT '',
148                    source          TEXT NOT NULL,
149                    catalog_type    TEXT NOT NULL DEFAULT 'skillssh',
150                    files           TEXT NOT NULL DEFAULT '[]',
151                    license         TEXT,
152                    metadata        TEXT NOT NULL DEFAULT '{}',
153                    installs        INTEGER NOT NULL DEFAULT 0,
154                    created_at      INTEGER NOT NULL,
155                    updated_at      INTEGER NOT NULL
156                );
157
158                CREATE TABLE IF NOT EXISTS workspace_skills (
159                    workspace_id    TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
160                    skill_id        TEXT NOT NULL REFERENCES skills(id) ON DELETE CASCADE,
161                    installed_at    INTEGER NOT NULL,
162                    PRIMARY KEY (workspace_id, skill_id)
163                );
164
165                CREATE TABLE IF NOT EXISTS agents (
166                    id              TEXT PRIMARY KEY,
167                    name            TEXT NOT NULL,
168                    role            TEXT NOT NULL,
169                    model_tier      TEXT NOT NULL DEFAULT 'SMART',
170                    workspace_id    TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
171                    parent_id       TEXT,
172                    status          TEXT NOT NULL DEFAULT 'PENDING',
173                    metadata        TEXT NOT NULL DEFAULT '{}',
174                    created_at      INTEGER NOT NULL,
175                    updated_at      INTEGER NOT NULL
176                );
177
178                CREATE TABLE IF NOT EXISTS tasks (
179                    id                      TEXT PRIMARY KEY,
180                    title                   TEXT NOT NULL,
181                    objective               TEXT NOT NULL,
182                    comment                 TEXT,
183                    scope                   TEXT,
184                    acceptance_criteria     TEXT,
185                    verification_commands   TEXT,
186                    test_cases              TEXT,
187                    assigned_to             TEXT,
188                    status                  TEXT NOT NULL DEFAULT 'PENDING',
189                    board_id                TEXT,
190                    column_id               TEXT,
191                    position                INTEGER NOT NULL DEFAULT 0,
192                    priority                TEXT,
193                    labels                  TEXT NOT NULL DEFAULT '[]',
194                    assignee                TEXT,
195                    assigned_provider       TEXT,
196                    assigned_role           TEXT,
197                    assigned_specialist_id  TEXT,
198                    assigned_specialist_name TEXT,
199                    trigger_session_id      TEXT,
200                    github_id               TEXT,
201                    github_number           INTEGER,
202                    github_url              TEXT,
203                    github_repo             TEXT,
204                    github_state            TEXT,
205                    github_synced_at        INTEGER,
206                    last_sync_error         TEXT,
207                    dependencies            TEXT NOT NULL DEFAULT '[]',
208                    parallel_group          TEXT,
209                    workspace_id            TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
210                    session_id              TEXT,
211                    session_ids             TEXT NOT NULL DEFAULT '[]',
212                    lane_sessions           TEXT NOT NULL DEFAULT '[]',
213                    lane_handoffs           TEXT NOT NULL DEFAULT '[]',
214                    completion_summary      TEXT,
215                    verification_verdict    TEXT,
216                    verification_report     TEXT,
217                    codebase_ids            TEXT NOT NULL DEFAULT '[]',
218                    worktree_id             TEXT,
219                    version                 INTEGER NOT NULL DEFAULT 1,
220                    created_at              INTEGER NOT NULL,
221                    updated_at              INTEGER NOT NULL
222                );
223                CREATE TABLE IF NOT EXISTS artifacts (
224                    id                      TEXT PRIMARY KEY,
225                    type                    TEXT NOT NULL,
226                    task_id                 TEXT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
227                    workspace_id            TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
228                    provided_by_agent_id    TEXT,
229                    requested_by_agent_id   TEXT,
230                    request_id              TEXT,
231                    content                 TEXT,
232                    context                 TEXT,
233                    status                  TEXT NOT NULL DEFAULT 'provided',
234                    expires_at              INTEGER,
235                    metadata                TEXT,
236                    created_at              INTEGER NOT NULL,
237                    updated_at              INTEGER NOT NULL
238                );
239                CREATE TABLE IF NOT EXISTS kanban_boards (
240                    id              TEXT PRIMARY KEY,
241                    workspace_id    TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
242                    name            TEXT NOT NULL,
243                    is_default      INTEGER NOT NULL DEFAULT 0,
244                    columns         TEXT NOT NULL DEFAULT '[]',
245                    created_at      INTEGER NOT NULL,
246                    updated_at      INTEGER NOT NULL
247                );
248                CREATE TABLE IF NOT EXISTS notes (
249                    id                  TEXT NOT NULL,
250                    workspace_id        TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
251                    session_id          TEXT,
252                    title               TEXT NOT NULL,
253                    content             TEXT NOT NULL DEFAULT '',
254                    type                TEXT NOT NULL DEFAULT 'general',
255                    task_status         TEXT,
256                    assigned_agent_ids  TEXT,
257                    parent_note_id      TEXT,
258                    linked_task_id      TEXT,
259                    custom_metadata     TEXT,
260                    created_at          INTEGER NOT NULL,
261                    updated_at          INTEGER NOT NULL,
262                    PRIMARY KEY (workspace_id, id)
263                );
264
265                CREATE TABLE IF NOT EXISTS messages (
266                    id          TEXT PRIMARY KEY,
267                    agent_id    TEXT NOT NULL,
268                    role        TEXT NOT NULL,
269                    content     TEXT NOT NULL,
270                    timestamp   INTEGER NOT NULL,
271                    tool_name   TEXT,
272                    tool_args   TEXT,
273                    turn        INTEGER
274                );
275
276                CREATE TABLE IF NOT EXISTS event_subscriptions (
277                    id              TEXT PRIMARY KEY,
278                    agent_id        TEXT NOT NULL,
279                    agent_name      TEXT NOT NULL,
280                    event_types     TEXT NOT NULL,
281                    exclude_self    INTEGER NOT NULL DEFAULT 1,
282                    one_shot        INTEGER NOT NULL DEFAULT 0,
283                    wait_group_id   TEXT,
284                    priority        INTEGER NOT NULL DEFAULT 0,
285                    created_at      INTEGER NOT NULL
286                );
287
288                CREATE TABLE IF NOT EXISTS pending_events (
289                    id              TEXT PRIMARY KEY,
290                    agent_id        TEXT NOT NULL,
291                    event_type      TEXT NOT NULL,
292                    source_agent_id TEXT NOT NULL,
293                    workspace_id    TEXT NOT NULL,
294                    data            TEXT NOT NULL DEFAULT '{}',
295                    timestamp       INTEGER NOT NULL
296                );
297
298                CREATE INDEX IF NOT EXISTS idx_agents_workspace ON agents(workspace_id);
299                CREATE INDEX IF NOT EXISTS idx_tasks_workspace ON tasks(workspace_id);
300                CREATE INDEX IF NOT EXISTS idx_artifacts_task ON artifacts(task_id);
301                CREATE INDEX IF NOT EXISTS idx_artifacts_workspace ON artifacts(workspace_id);
302                CREATE INDEX IF NOT EXISTS idx_kanban_boards_workspace ON kanban_boards(workspace_id);
303                CREATE UNIQUE INDEX IF NOT EXISTS uq_kanban_boards_default_workspace ON kanban_boards(workspace_id) WHERE is_default = 1;
304                CREATE INDEX IF NOT EXISTS idx_notes_workspace ON notes(workspace_id);
305                CREATE INDEX IF NOT EXISTS idx_messages_agent ON messages(agent_id);
306
307                CREATE TABLE IF NOT EXISTS schedules (
308                    id              TEXT PRIMARY KEY,
309                    name            TEXT NOT NULL,
310                    cron_expr       TEXT NOT NULL,
311                    task_prompt     TEXT NOT NULL,
312                    agent_id        TEXT NOT NULL,
313                    workspace_id    TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
314                    enabled         INTEGER NOT NULL DEFAULT 1,
315                    last_run_at     INTEGER,
316                    next_run_at     INTEGER,
317                    last_task_id    TEXT,
318                    prompt_template TEXT,
319                    created_at      INTEGER NOT NULL,
320                    updated_at      INTEGER NOT NULL
321                );
322                CREATE INDEX IF NOT EXISTS idx_schedules_workspace ON schedules(workspace_id);
323                CREATE INDEX IF NOT EXISTS idx_schedules_next_run ON schedules(next_run_at) WHERE enabled = 1;
324
325                CREATE TABLE IF NOT EXISTS worktrees (
326                    id              TEXT PRIMARY KEY,
327                    codebase_id     TEXT NOT NULL REFERENCES codebases(id) ON DELETE CASCADE,
328                    workspace_id    TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
329                    worktree_path   TEXT NOT NULL,
330                    branch          TEXT NOT NULL,
331                    base_branch     TEXT NOT NULL,
332                    status          TEXT NOT NULL DEFAULT 'creating',
333                    session_id      TEXT,
334                    label           TEXT,
335                    error_message   TEXT,
336                    created_at      INTEGER NOT NULL,
337                    updated_at      INTEGER NOT NULL
338                );
339                CREATE INDEX IF NOT EXISTS idx_worktrees_workspace ON worktrees(workspace_id);
340                CREATE INDEX IF NOT EXISTS idx_worktrees_codebase ON worktrees(codebase_id);
341                CREATE UNIQUE INDEX IF NOT EXISTS uq_worktrees_codebase_branch
342                    ON worktrees(codebase_id, branch);
343                CREATE UNIQUE INDEX IF NOT EXISTS uq_worktrees_path
344                    ON worktrees(worktree_path);
345                "
346            )
347        })?;
348        self.run_migrations()
349    }
350
351    /// Apply incremental migrations for schema changes on existing databases.
352    fn run_migrations(&self) -> Result<(), ServerError> {
353        self.with_conn(|conn| {
354            // Add session_id to tasks if it doesn't exist yet (ignore error if already present)
355            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN comment TEXT", []))?;
356            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN session_id TEXT", []))?;
357            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN board_id TEXT", []))?;
358            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN column_id TEXT", []))?;
359            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN position INTEGER NOT NULL DEFAULT 0", []))?;
360            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN priority TEXT", []))?;
361            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN labels TEXT NOT NULL DEFAULT '[]'", []))?;
362            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN assignee TEXT", []))?;
363            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN assigned_provider TEXT", []))?;
364            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN assigned_role TEXT", []))?;
365            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN assigned_specialist_id TEXT", []))?;
366            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN assigned_specialist_name TEXT", []))?;
367            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN trigger_session_id TEXT", []))?;
368            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN github_id TEXT", []))?;
369            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN github_number INTEGER", []))?;
370            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN github_url TEXT", []))?;
371            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN github_repo TEXT", []))?;
372            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN github_state TEXT", []))?;
373            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN github_synced_at INTEGER", []))?;
374            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN last_sync_error TEXT", []))?;
375            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN test_cases TEXT", []))?;
376            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN codebase_ids TEXT NOT NULL DEFAULT '[]'", []))?;
377            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN worktree_id TEXT", []))?;
378            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN session_ids TEXT NOT NULL DEFAULT '[]'", []))?;
379            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN lane_sessions TEXT NOT NULL DEFAULT '[]'", []))?;
380            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN lane_handoffs TEXT NOT NULL DEFAULT '[]'", []))?;
381            // Add session_id to notes if it doesn't exist yet (ignore error if already present)
382            Self::ignore_duplicate_column(conn.execute("ALTER TABLE notes ADD COLUMN session_id TEXT", []))?;
383            Self::ignore_duplicate_column(conn.execute("ALTER TABLE acp_sessions ADD COLUMN branch TEXT", []))?;
384            // Add parent_session_id to acp_sessions for CRAFTER child session tracking
385            Self::ignore_duplicate_column(conn.execute("ALTER TABLE acp_sessions ADD COLUMN parent_session_id TEXT", []))?;
386            conn.execute_batch(
387                "CREATE TABLE IF NOT EXISTS kanban_boards (
388                    id TEXT PRIMARY KEY,
389                    workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
390                    name TEXT NOT NULL,
391                    is_default INTEGER NOT NULL DEFAULT 0,
392                    columns TEXT NOT NULL DEFAULT '[]',
393                    created_at INTEGER NOT NULL,
394                    updated_at INTEGER NOT NULL
395                );
396                CREATE INDEX IF NOT EXISTS idx_kanban_boards_workspace ON kanban_boards(workspace_id);
397                CREATE UNIQUE INDEX IF NOT EXISTS uq_kanban_boards_default_workspace ON kanban_boards(workspace_id) WHERE is_default = 1;"
398            )?;
399            conn.execute_batch(
400                "CREATE TABLE IF NOT EXISTS artifacts (
401                    id TEXT PRIMARY KEY,
402                    type TEXT NOT NULL,
403                    task_id TEXT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
404                    workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
405                    provided_by_agent_id TEXT,
406                    requested_by_agent_id TEXT,
407                    request_id TEXT,
408                    content TEXT,
409                    context TEXT,
410                    status TEXT NOT NULL DEFAULT 'provided',
411                    expires_at INTEGER,
412                    metadata TEXT,
413                    created_at INTEGER NOT NULL,
414                    updated_at INTEGER NOT NULL
415                );
416                CREATE INDEX IF NOT EXISTS idx_artifacts_task ON artifacts(task_id);
417                CREATE INDEX IF NOT EXISTS idx_artifacts_workspace ON artifacts(workspace_id);"
418            )?;
419            Self::ignore_duplicate_column(conn.execute("ALTER TABLE kanban_boards ADD COLUMN columns TEXT NOT NULL DEFAULT '[]'", []))?;
420            let _ = conn.execute("UPDATE kanban_boards SET columns = columns_json WHERE (columns IS NULL OR columns = '[]') AND columns_json IS NOT NULL", []);
421            // Create indexes for session_id columns
422            conn.execute_batch(
423                "CREATE INDEX IF NOT EXISTS idx_tasks_session ON tasks(session_id);
424                 CREATE INDEX IF NOT EXISTS idx_notes_session ON notes(session_id);
425                 CREATE INDEX IF NOT EXISTS idx_acp_sessions_parent ON acp_sessions(parent_session_id);"
426            )
427        })
428    }
429}
routa_core/db/mod.rs

routa_core/db/
mod.rs
