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                    source_type     TEXT,
123                    source_url      TEXT,
124                    created_at      INTEGER NOT NULL,
125                    updated_at      INTEGER NOT NULL
126                );
127                CREATE INDEX IF NOT EXISTS idx_codebases_workspace ON codebases(workspace_id);
128
129                CREATE TABLE IF NOT EXISTS acp_sessions (
130                    id              TEXT PRIMARY KEY,
131                    name            TEXT,
132                    cwd             TEXT NOT NULL,
133                    branch          TEXT,
134                    workspace_id    TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
135                    routa_agent_id  TEXT,
136                    provider        TEXT,
137                    role            TEXT,
138                    mode_id         TEXT,
139                    custom_command  TEXT,
140                    custom_args     TEXT NOT NULL DEFAULT '[]',
141                    first_prompt_sent INTEGER DEFAULT 0,
142                    message_history TEXT NOT NULL DEFAULT '[]',
143                    created_at      INTEGER NOT NULL,
144                    updated_at      INTEGER NOT NULL
145                );
146                CREATE INDEX IF NOT EXISTS idx_acp_sessions_workspace ON acp_sessions(workspace_id);
147
148                CREATE TABLE IF NOT EXISTS skills (
149                    id              TEXT PRIMARY KEY,
150                    name            TEXT NOT NULL,
151                    description     TEXT NOT NULL DEFAULT '',
152                    source          TEXT NOT NULL,
153                    catalog_type    TEXT NOT NULL DEFAULT 'skillssh',
154                    files           TEXT NOT NULL DEFAULT '[]',
155                    license         TEXT,
156                    metadata        TEXT NOT NULL DEFAULT '{}',
157                    installs        INTEGER NOT NULL DEFAULT 0,
158                    created_at      INTEGER NOT NULL,
159                    updated_at      INTEGER NOT NULL
160                );
161
162                CREATE TABLE IF NOT EXISTS workspace_skills (
163                    workspace_id    TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
164                    skill_id        TEXT NOT NULL REFERENCES skills(id) ON DELETE CASCADE,
165                    installed_at    INTEGER NOT NULL,
166                    PRIMARY KEY (workspace_id, skill_id)
167                );
168
169                CREATE TABLE IF NOT EXISTS agents (
170                    id              TEXT PRIMARY KEY,
171                    name            TEXT NOT NULL,
172                    role            TEXT NOT NULL,
173                    model_tier      TEXT NOT NULL DEFAULT 'SMART',
174                    workspace_id    TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
175                    parent_id       TEXT,
176                    status          TEXT NOT NULL DEFAULT 'PENDING',
177                    metadata        TEXT NOT NULL DEFAULT '{}',
178                    created_at      INTEGER NOT NULL,
179                    updated_at      INTEGER NOT NULL
180                );
181
182                CREATE TABLE IF NOT EXISTS tasks (
183                    id                      TEXT PRIMARY KEY,
184                    title                   TEXT NOT NULL,
185                    objective               TEXT NOT NULL,
186                    comment                 TEXT,
187                    scope                   TEXT,
188                    acceptance_criteria     TEXT,
189                    verification_commands   TEXT,
190                    test_cases              TEXT,
191                    assigned_to             TEXT,
192                    status                  TEXT NOT NULL DEFAULT 'PENDING',
193                    board_id                TEXT,
194                    column_id               TEXT,
195                    position                INTEGER NOT NULL DEFAULT 0,
196                    priority                TEXT,
197                    labels                  TEXT NOT NULL DEFAULT '[]',
198                    assignee                TEXT,
199                    assigned_provider       TEXT,
200                    assigned_role           TEXT,
201                    assigned_specialist_id  TEXT,
202                    assigned_specialist_name TEXT,
203                    trigger_session_id      TEXT,
204                    github_id               TEXT,
205                    github_number           INTEGER,
206                    github_url              TEXT,
207                    github_repo             TEXT,
208                    github_state            TEXT,
209                    github_synced_at        INTEGER,
210                    last_sync_error         TEXT,
211                    dependencies            TEXT NOT NULL DEFAULT '[]',
212                    parallel_group          TEXT,
213                    workspace_id            TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
214                    session_id              TEXT,
215                    session_ids             TEXT NOT NULL DEFAULT '[]',
216                    lane_sessions           TEXT NOT NULL DEFAULT '[]',
217                    lane_handoffs           TEXT NOT NULL DEFAULT '[]',
218                    completion_summary      TEXT,
219                    verification_verdict    TEXT,
220                    verification_report     TEXT,
221                    codebase_ids            TEXT NOT NULL DEFAULT '[]',
222                    worktree_id             TEXT,
223                    version                 INTEGER NOT NULL DEFAULT 1,
224                    created_at              INTEGER NOT NULL,
225                    updated_at              INTEGER NOT NULL
226                );
227                CREATE TABLE IF NOT EXISTS artifacts (
228                    id                      TEXT PRIMARY KEY,
229                    type                    TEXT NOT NULL,
230                    task_id                 TEXT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
231                    workspace_id            TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
232                    provided_by_agent_id    TEXT,
233                    requested_by_agent_id   TEXT,
234                    request_id              TEXT,
235                    content                 TEXT,
236                    context                 TEXT,
237                    status                  TEXT NOT NULL DEFAULT 'provided',
238                    expires_at              INTEGER,
239                    metadata                TEXT,
240                    created_at              INTEGER NOT NULL,
241                    updated_at              INTEGER NOT NULL
242                );
243                CREATE TABLE IF NOT EXISTS kanban_boards (
244                    id              TEXT PRIMARY KEY,
245                    workspace_id    TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
246                    name            TEXT NOT NULL,
247                    is_default      INTEGER NOT NULL DEFAULT 0,
248                    columns         TEXT NOT NULL DEFAULT '[]',
249                    created_at      INTEGER NOT NULL,
250                    updated_at      INTEGER NOT NULL
251                );
252                CREATE TABLE IF NOT EXISTS notes (
253                    id                  TEXT NOT NULL,
254                    workspace_id        TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
255                    session_id          TEXT,
256                    title               TEXT NOT NULL,
257                    content             TEXT NOT NULL DEFAULT '',
258                    type                TEXT NOT NULL DEFAULT 'general',
259                    task_status         TEXT,
260                    assigned_agent_ids  TEXT,
261                    parent_note_id      TEXT,
262                    linked_task_id      TEXT,
263                    custom_metadata     TEXT,
264                    created_at          INTEGER NOT NULL,
265                    updated_at          INTEGER NOT NULL,
266                    PRIMARY KEY (workspace_id, id)
267                );
268
269                CREATE TABLE IF NOT EXISTS messages (
270                    id          TEXT PRIMARY KEY,
271                    agent_id    TEXT NOT NULL,
272                    role        TEXT NOT NULL,
273                    content     TEXT NOT NULL,
274                    timestamp   INTEGER NOT NULL,
275                    tool_name   TEXT,
276                    tool_args   TEXT,
277                    turn        INTEGER
278                );
279
280                CREATE TABLE IF NOT EXISTS event_subscriptions (
281                    id              TEXT PRIMARY KEY,
282                    agent_id        TEXT NOT NULL,
283                    agent_name      TEXT NOT NULL,
284                    event_types     TEXT NOT NULL,
285                    exclude_self    INTEGER NOT NULL DEFAULT 1,
286                    one_shot        INTEGER NOT NULL DEFAULT 0,
287                    wait_group_id   TEXT,
288                    priority        INTEGER NOT NULL DEFAULT 0,
289                    created_at      INTEGER NOT NULL
290                );
291
292                CREATE TABLE IF NOT EXISTS pending_events (
293                    id              TEXT PRIMARY KEY,
294                    agent_id        TEXT NOT NULL,
295                    event_type      TEXT NOT NULL,
296                    source_agent_id TEXT NOT NULL,
297                    workspace_id    TEXT NOT NULL,
298                    data            TEXT NOT NULL DEFAULT '{}',
299                    timestamp       INTEGER NOT NULL
300                );
301
302                CREATE INDEX IF NOT EXISTS idx_agents_workspace ON agents(workspace_id);
303                CREATE INDEX IF NOT EXISTS idx_tasks_workspace ON tasks(workspace_id);
304                CREATE INDEX IF NOT EXISTS idx_artifacts_task ON artifacts(task_id);
305                CREATE INDEX IF NOT EXISTS idx_artifacts_workspace ON artifacts(workspace_id);
306                CREATE INDEX IF NOT EXISTS idx_kanban_boards_workspace ON kanban_boards(workspace_id);
307                CREATE UNIQUE INDEX IF NOT EXISTS uq_kanban_boards_default_workspace ON kanban_boards(workspace_id) WHERE is_default = 1;
308                CREATE INDEX IF NOT EXISTS idx_notes_workspace ON notes(workspace_id);
309                CREATE INDEX IF NOT EXISTS idx_messages_agent ON messages(agent_id);
310
311                CREATE TABLE IF NOT EXISTS schedules (
312                    id              TEXT PRIMARY KEY,
313                    name            TEXT NOT NULL,
314                    cron_expr       TEXT NOT NULL,
315                    task_prompt     TEXT NOT NULL,
316                    agent_id        TEXT NOT NULL,
317                    workspace_id    TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
318                    enabled         INTEGER NOT NULL DEFAULT 1,
319                    last_run_at     INTEGER,
320                    next_run_at     INTEGER,
321                    last_task_id    TEXT,
322                    prompt_template TEXT,
323                    created_at      INTEGER NOT NULL,
324                    updated_at      INTEGER NOT NULL
325                );
326                CREATE INDEX IF NOT EXISTS idx_schedules_workspace ON schedules(workspace_id);
327                CREATE INDEX IF NOT EXISTS idx_schedules_next_run ON schedules(next_run_at) WHERE enabled = 1;
328
329                CREATE TABLE IF NOT EXISTS worktrees (
330                    id              TEXT PRIMARY KEY,
331                    codebase_id     TEXT NOT NULL REFERENCES codebases(id) ON DELETE CASCADE,
332                    workspace_id    TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
333                    worktree_path   TEXT NOT NULL,
334                    branch          TEXT NOT NULL,
335                    base_branch     TEXT NOT NULL,
336                    status          TEXT NOT NULL DEFAULT 'creating',
337                    session_id      TEXT,
338                    label           TEXT,
339                    error_message   TEXT,
340                    created_at      INTEGER NOT NULL,
341                    updated_at      INTEGER NOT NULL
342                );
343                CREATE INDEX IF NOT EXISTS idx_worktrees_workspace ON worktrees(workspace_id);
344                CREATE INDEX IF NOT EXISTS idx_worktrees_codebase ON worktrees(codebase_id);
345                CREATE UNIQUE INDEX IF NOT EXISTS uq_worktrees_codebase_branch
346                    ON worktrees(codebase_id, branch);
347                CREATE UNIQUE INDEX IF NOT EXISTS uq_worktrees_path
348                    ON worktrees(worktree_path);
349                "
350            )
351        })?;
352        self.run_migrations()
353    }
354
355    /// Apply incremental migrations for schema changes on existing databases.
356    fn run_migrations(&self) -> Result<(), ServerError> {
357        self.with_conn(|conn| {
358            // Add session_id to tasks if it doesn't exist yet (ignore error if already present)
359            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN comment TEXT", []))?;
360            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN session_id TEXT", []))?;
361            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN board_id TEXT", []))?;
362            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN column_id TEXT", []))?;
363            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN position INTEGER NOT NULL DEFAULT 0", []))?;
364            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN priority TEXT", []))?;
365            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN labels TEXT NOT NULL DEFAULT '[]'", []))?;
366            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN assignee TEXT", []))?;
367            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN assigned_provider TEXT", []))?;
368            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN assigned_role TEXT", []))?;
369            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN assigned_specialist_id TEXT", []))?;
370            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN assigned_specialist_name TEXT", []))?;
371            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN trigger_session_id TEXT", []))?;
372            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN github_id TEXT", []))?;
373            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN github_number INTEGER", []))?;
374            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN github_url TEXT", []))?;
375            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN github_repo TEXT", []))?;
376            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN github_state TEXT", []))?;
377            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN github_synced_at INTEGER", []))?;
378            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN last_sync_error TEXT", []))?;
379            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN test_cases TEXT", []))?;
380            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN codebase_ids TEXT NOT NULL DEFAULT '[]'", []))?;
381            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN worktree_id TEXT", []))?;
382            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN session_ids TEXT NOT NULL DEFAULT '[]'", []))?;
383            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN lane_sessions TEXT NOT NULL DEFAULT '[]'", []))?;
384            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN lane_handoffs TEXT NOT NULL DEFAULT '[]'", []))?;
385            // Add session_id to notes if it doesn't exist yet (ignore error if already present)
386            Self::ignore_duplicate_column(conn.execute("ALTER TABLE notes ADD COLUMN session_id TEXT", []))?;
387            Self::ignore_duplicate_column(conn.execute("ALTER TABLE acp_sessions ADD COLUMN branch TEXT", []))?;
388            // Add parent_session_id to acp_sessions for CRAFTER child session tracking
389            Self::ignore_duplicate_column(conn.execute("ALTER TABLE acp_sessions ADD COLUMN parent_session_id TEXT", []))?;
390            Self::ignore_duplicate_column(conn.execute("ALTER TABLE acp_sessions ADD COLUMN custom_command TEXT", []))?;
391            Self::ignore_duplicate_column(conn.execute("ALTER TABLE acp_sessions ADD COLUMN custom_args TEXT NOT NULL DEFAULT '[]'", []))?;
392            Self::ignore_duplicate_column(conn.execute("ALTER TABLE codebases ADD COLUMN source_type TEXT", []))?;
393            Self::ignore_duplicate_column(conn.execute("ALTER TABLE codebases ADD COLUMN source_url TEXT", []))?;
394            conn.execute_batch(
395                "CREATE TABLE IF NOT EXISTS kanban_boards (
396                    id TEXT PRIMARY KEY,
397                    workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
398                    name TEXT NOT NULL,
399                    is_default INTEGER NOT NULL DEFAULT 0,
400                    columns TEXT NOT NULL DEFAULT '[]',
401                    created_at INTEGER NOT NULL,
402                    updated_at INTEGER NOT NULL
403                );
404                CREATE INDEX IF NOT EXISTS idx_kanban_boards_workspace ON kanban_boards(workspace_id);
405                CREATE UNIQUE INDEX IF NOT EXISTS uq_kanban_boards_default_workspace ON kanban_boards(workspace_id) WHERE is_default = 1;"
406            )?;
407            conn.execute_batch(
408                "CREATE TABLE IF NOT EXISTS artifacts (
409                    id TEXT PRIMARY KEY,
410                    type TEXT NOT NULL,
411                    task_id TEXT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
412                    workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
413                    provided_by_agent_id TEXT,
414                    requested_by_agent_id TEXT,
415                    request_id TEXT,
416                    content TEXT,
417                    context TEXT,
418                    status TEXT NOT NULL DEFAULT 'provided',
419                    expires_at INTEGER,
420                    metadata TEXT,
421                    created_at INTEGER NOT NULL,
422                    updated_at INTEGER NOT NULL
423                );
424                CREATE INDEX IF NOT EXISTS idx_artifacts_task ON artifacts(task_id);
425                CREATE INDEX IF NOT EXISTS idx_artifacts_workspace ON artifacts(workspace_id);"
426            )?;
427            Self::ignore_duplicate_column(conn.execute("ALTER TABLE kanban_boards ADD COLUMN columns TEXT NOT NULL DEFAULT '[]'", []))?;
428            let _ = conn.execute("UPDATE kanban_boards SET columns = columns_json WHERE (columns IS NULL OR columns = '[]') AND columns_json IS NOT NULL", []);
429            // Create indexes for session_id columns
430            conn.execute_batch(
431                "CREATE INDEX IF NOT EXISTS idx_tasks_session ON tasks(session_id);
432                 CREATE INDEX IF NOT EXISTS idx_notes_session ON notes(session_id);
433                 CREATE INDEX IF NOT EXISTS idx_acp_sessions_parent ON acp_sessions(parent_session_id);"
434            )
435        })
436    }
437}
routa_core/db/mod.rs

routa_core/db/
mod.rs
