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

routa_core/db/
mod.rs
