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                    creation_source         TEXT,
217                    session_ids             TEXT NOT NULL DEFAULT '[]',
218                    lane_sessions           TEXT NOT NULL DEFAULT '[]',
219                    lane_handoffs           TEXT NOT NULL DEFAULT '[]',
220                    completion_summary      TEXT,
221                    verification_verdict    TEXT,
222                    verification_report     TEXT,
223                    codebase_ids            TEXT NOT NULL DEFAULT '[]',
224                    worktree_id             TEXT,
225                    version                 INTEGER NOT NULL DEFAULT 1,
226                    created_at              INTEGER NOT NULL,
227                    updated_at              INTEGER NOT NULL
228                );
229                CREATE TABLE IF NOT EXISTS artifacts (
230                    id                      TEXT PRIMARY KEY,
231                    type                    TEXT NOT NULL,
232                    task_id                 TEXT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
233                    workspace_id            TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
234                    provided_by_agent_id    TEXT,
235                    requested_by_agent_id   TEXT,
236                    request_id              TEXT,
237                    content                 TEXT,
238                    context                 TEXT,
239                    status                  TEXT NOT NULL DEFAULT 'provided',
240                    expires_at              INTEGER,
241                    metadata                TEXT,
242                    created_at              INTEGER NOT NULL,
243                    updated_at              INTEGER NOT NULL
244                );
245                CREATE TABLE IF NOT EXISTS kanban_boards (
246                    id              TEXT PRIMARY KEY,
247                    workspace_id    TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
248                    name            TEXT NOT NULL,
249                    is_default      INTEGER NOT NULL DEFAULT 0,
250                    columns         TEXT NOT NULL DEFAULT '[]',
251                    created_at      INTEGER NOT NULL,
252                    updated_at      INTEGER NOT NULL
253                );
254                CREATE TABLE IF NOT EXISTS notes (
255                    id                  TEXT NOT NULL,
256                    workspace_id        TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
257                    session_id          TEXT,
258                    title               TEXT NOT NULL,
259                    content             TEXT NOT NULL DEFAULT '',
260                    type                TEXT NOT NULL DEFAULT 'general',
261                    task_status         TEXT,
262                    assigned_agent_ids  TEXT,
263                    parent_note_id      TEXT,
264                    linked_task_id      TEXT,
265                    custom_metadata     TEXT,
266                    created_at          INTEGER NOT NULL,
267                    updated_at          INTEGER NOT NULL,
268                    PRIMARY KEY (workspace_id, id)
269                );
270
271                CREATE TABLE IF NOT EXISTS messages (
272                    id          TEXT PRIMARY KEY,
273                    agent_id    TEXT NOT NULL,
274                    role        TEXT NOT NULL,
275                    content     TEXT NOT NULL,
276                    timestamp   INTEGER NOT NULL,
277                    tool_name   TEXT,
278                    tool_args   TEXT,
279                    turn        INTEGER
280                );
281
282                CREATE TABLE IF NOT EXISTS event_subscriptions (
283                    id              TEXT PRIMARY KEY,
284                    agent_id        TEXT NOT NULL,
285                    agent_name      TEXT NOT NULL,
286                    event_types     TEXT NOT NULL,
287                    exclude_self    INTEGER NOT NULL DEFAULT 1,
288                    one_shot        INTEGER NOT NULL DEFAULT 0,
289                    wait_group_id   TEXT,
290                    priority        INTEGER NOT NULL DEFAULT 0,
291                    created_at      INTEGER NOT NULL
292                );
293
294                CREATE TABLE IF NOT EXISTS pending_events (
295                    id              TEXT PRIMARY KEY,
296                    agent_id        TEXT NOT NULL,
297                    event_type      TEXT NOT NULL,
298                    source_agent_id TEXT NOT NULL,
299                    workspace_id    TEXT NOT NULL,
300                    data            TEXT NOT NULL DEFAULT '{}',
301                    timestamp       INTEGER NOT NULL
302                );
303
304                CREATE INDEX IF NOT EXISTS idx_agents_workspace ON agents(workspace_id);
305                CREATE INDEX IF NOT EXISTS idx_tasks_workspace ON tasks(workspace_id);
306                CREATE INDEX IF NOT EXISTS idx_artifacts_task ON artifacts(task_id);
307                CREATE INDEX IF NOT EXISTS idx_artifacts_workspace ON artifacts(workspace_id);
308                CREATE INDEX IF NOT EXISTS idx_kanban_boards_workspace ON kanban_boards(workspace_id);
309                CREATE UNIQUE INDEX IF NOT EXISTS uq_kanban_boards_default_workspace ON kanban_boards(workspace_id) WHERE is_default = 1;
310                CREATE INDEX IF NOT EXISTS idx_notes_workspace ON notes(workspace_id);
311                CREATE INDEX IF NOT EXISTS idx_messages_agent ON messages(agent_id);
312
313                CREATE TABLE IF NOT EXISTS schedules (
314                    id              TEXT PRIMARY KEY,
315                    name            TEXT NOT NULL,
316                    cron_expr       TEXT NOT NULL,
317                    task_prompt     TEXT NOT NULL,
318                    agent_id        TEXT NOT NULL,
319                    workspace_id    TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
320                    enabled         INTEGER NOT NULL DEFAULT 1,
321                    last_run_at     INTEGER,
322                    next_run_at     INTEGER,
323                    last_task_id    TEXT,
324                    prompt_template TEXT,
325                    created_at      INTEGER NOT NULL,
326                    updated_at      INTEGER NOT NULL
327                );
328                CREATE INDEX IF NOT EXISTS idx_schedules_workspace ON schedules(workspace_id);
329                CREATE INDEX IF NOT EXISTS idx_schedules_next_run ON schedules(next_run_at) WHERE enabled = 1;
330
331                CREATE TABLE IF NOT EXISTS worktrees (
332                    id              TEXT PRIMARY KEY,
333                    codebase_id     TEXT NOT NULL REFERENCES codebases(id) ON DELETE CASCADE,
334                    workspace_id    TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
335                    worktree_path   TEXT NOT NULL,
336                    branch          TEXT NOT NULL,
337                    base_branch     TEXT NOT NULL,
338                    status          TEXT NOT NULL DEFAULT 'creating',
339                    session_id      TEXT,
340                    label           TEXT,
341                    error_message   TEXT,
342                    created_at      INTEGER NOT NULL,
343                    updated_at      INTEGER NOT NULL
344                );
345                CREATE INDEX IF NOT EXISTS idx_worktrees_workspace ON worktrees(workspace_id);
346                CREATE INDEX IF NOT EXISTS idx_worktrees_codebase ON worktrees(codebase_id);
347                CREATE UNIQUE INDEX IF NOT EXISTS uq_worktrees_codebase_branch
348                    ON worktrees(codebase_id, branch);
349                CREATE UNIQUE INDEX IF NOT EXISTS uq_worktrees_path
350                    ON worktrees(worktree_path);
351                "
352            )
353        })?;
354        self.run_migrations()
355    }
356
357    /// Apply incremental migrations for schema changes on existing databases.
358    fn run_migrations(&self) -> Result<(), ServerError> {
359        self.with_conn(|conn| {
360            // Add session_id to tasks if it doesn't exist yet (ignore error if already present)
361            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN comment TEXT", []))?;
362            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN session_id TEXT", []))?;
363            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN board_id TEXT", []))?;
364            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN column_id TEXT", []))?;
365            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN position INTEGER NOT NULL DEFAULT 0", []))?;
366            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN priority TEXT", []))?;
367            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN labels TEXT NOT NULL DEFAULT '[]'", []))?;
368            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN assignee TEXT", []))?;
369            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN assigned_provider TEXT", []))?;
370            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN assigned_role TEXT", []))?;
371            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN assigned_specialist_id TEXT", []))?;
372            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN assigned_specialist_name TEXT", []))?;
373            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN trigger_session_id TEXT", []))?;
374            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN github_id TEXT", []))?;
375            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN github_number INTEGER", []))?;
376            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN github_url TEXT", []))?;
377            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN github_repo TEXT", []))?;
378            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN github_state TEXT", []))?;
379            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN github_synced_at INTEGER", []))?;
380            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN last_sync_error TEXT", []))?;
381            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN test_cases TEXT", []))?;
382            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN codebase_ids TEXT NOT NULL DEFAULT '[]'", []))?;
383            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN worktree_id TEXT", []))?;
384            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN creation_source TEXT", []))?;
385            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN session_ids TEXT NOT NULL DEFAULT '[]'", []))?;
386            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN lane_sessions TEXT NOT NULL DEFAULT '[]'", []))?;
387            Self::ignore_duplicate_column(conn.execute("ALTER TABLE tasks ADD COLUMN lane_handoffs TEXT NOT NULL DEFAULT '[]'", []))?;
388            // Add session_id to notes if it doesn't exist yet (ignore error if already present)
389            Self::ignore_duplicate_column(conn.execute("ALTER TABLE notes ADD COLUMN session_id TEXT", []))?;
390            Self::ignore_duplicate_column(conn.execute("ALTER TABLE acp_sessions ADD COLUMN branch TEXT", []))?;
391            // Add parent_session_id to acp_sessions for CRAFTER child session tracking
392            Self::ignore_duplicate_column(conn.execute("ALTER TABLE acp_sessions ADD COLUMN parent_session_id TEXT", []))?;
393            Self::ignore_duplicate_column(conn.execute("ALTER TABLE acp_sessions ADD COLUMN provider_session_id TEXT", []))?;
394            Self::ignore_duplicate_column(conn.execute("ALTER TABLE acp_sessions ADD COLUMN custom_command TEXT", []))?;
395            Self::ignore_duplicate_column(conn.execute("ALTER TABLE acp_sessions ADD COLUMN custom_args TEXT NOT NULL DEFAULT '[]'", []))?;
396            Self::ignore_duplicate_column(conn.execute("ALTER TABLE codebases ADD COLUMN source_type TEXT", []))?;
397            Self::ignore_duplicate_column(conn.execute("ALTER TABLE codebases ADD COLUMN source_url TEXT", []))?;
398            conn.execute_batch(
399                "CREATE TABLE IF NOT EXISTS kanban_boards (
400                    id TEXT PRIMARY KEY,
401                    workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
402                    name TEXT NOT NULL,
403                    is_default INTEGER NOT NULL DEFAULT 0,
404                    columns TEXT NOT NULL DEFAULT '[]',
405                    created_at INTEGER NOT NULL,
406                    updated_at INTEGER NOT NULL
407                );
408                CREATE INDEX IF NOT EXISTS idx_kanban_boards_workspace ON kanban_boards(workspace_id);
409                CREATE UNIQUE INDEX IF NOT EXISTS uq_kanban_boards_default_workspace ON kanban_boards(workspace_id) WHERE is_default = 1;"
410            )?;
411            conn.execute_batch(
412                "CREATE TABLE IF NOT EXISTS artifacts (
413                    id TEXT PRIMARY KEY,
414                    type TEXT NOT NULL,
415                    task_id TEXT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
416                    workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
417                    provided_by_agent_id TEXT,
418                    requested_by_agent_id TEXT,
419                    request_id TEXT,
420                    content TEXT,
421                    context TEXT,
422                    status TEXT NOT NULL DEFAULT 'provided',
423                    expires_at INTEGER,
424                    metadata TEXT,
425                    created_at INTEGER NOT NULL,
426                    updated_at INTEGER NOT NULL
427                );
428                CREATE INDEX IF NOT EXISTS idx_artifacts_task ON artifacts(task_id);
429                CREATE INDEX IF NOT EXISTS idx_artifacts_workspace ON artifacts(workspace_id);"
430            )?;
431            Self::ignore_duplicate_column(conn.execute("ALTER TABLE kanban_boards ADD COLUMN columns TEXT NOT NULL DEFAULT '[]'", []))?;
432            let _ = conn.execute("UPDATE kanban_boards SET columns = columns_json WHERE (columns IS NULL OR columns = '[]') AND columns_json IS NOT NULL", []);
433            // Create indexes for session_id columns
434            conn.execute_batch(
435                "CREATE INDEX IF NOT EXISTS idx_tasks_session ON tasks(session_id);
436                 CREATE INDEX IF NOT EXISTS idx_notes_session ON notes(session_id);
437                 CREATE INDEX IF NOT EXISTS idx_acp_sessions_parent ON acp_sessions(parent_session_id);"
438            )
439        })
440    }
441}
routa_core/db/mod.rs

routa_core/db/
mod.rs
