intent_engine/db/
mod.rs

1pub mod models;
2
3use crate::error::Result;
4use sqlx::sqlite::{SqliteConnectOptions, SqlitePool, SqlitePoolOptions};
5use std::path::Path;
6
7pub async fn create_pool(db_path: &Path) -> Result<SqlitePool> {
8    let options = SqliteConnectOptions::new()
9        .filename(db_path)
10        .create_if_missing(true)
11        .journal_mode(sqlx::sqlite::SqliteJournalMode::Wal)
12        .busy_timeout(std::time::Duration::from_millis(5000));
13
14    let pool = SqlitePoolOptions::new()
15        .max_connections(5)
16        .connect_with(options)
17        .await?;
18
19    Ok(pool)
20}
21
22pub async fn run_migrations(pool: &SqlitePool) -> Result<()> {
23    // Enable FTS5
24    sqlx::query("PRAGMA journal_mode=WAL;")
25        .execute(pool)
26        .await?;
27
28    // Create tasks table
29    sqlx::query(
30        r#"
31        CREATE TABLE IF NOT EXISTS tasks (
32            id INTEGER PRIMARY KEY AUTOINCREMENT,
33            parent_id INTEGER,
34            name TEXT NOT NULL,
35            spec TEXT,
36            status TEXT NOT NULL DEFAULT 'todo',
37            complexity INTEGER,
38            priority INTEGER DEFAULT 0,
39            first_todo_at DATETIME,
40            first_doing_at DATETIME,
41            first_done_at DATETIME,
42            FOREIGN KEY (parent_id) REFERENCES tasks(id) ON DELETE CASCADE,
43            CHECK (status IN ('todo', 'doing', 'done'))
44        )
45        "#,
46    )
47    .execute(pool)
48    .await?;
49
50    // Create FTS5 virtual table for tasks
51    sqlx::query(
52        r#"
53        CREATE VIRTUAL TABLE IF NOT EXISTS tasks_fts USING fts5(
54            name,
55            spec,
56            content=tasks,
57            content_rowid=id
58        )
59        "#,
60    )
61    .execute(pool)
62    .await?;
63
64    // Create triggers to keep FTS in sync
65    sqlx::query(
66        r#"
67        CREATE TRIGGER IF NOT EXISTS tasks_ai AFTER INSERT ON tasks BEGIN
68            INSERT INTO tasks_fts(rowid, name, spec) VALUES (new.id, new.name, new.spec);
69        END
70        "#,
71    )
72    .execute(pool)
73    .await?;
74
75    sqlx::query(
76        r#"
77        CREATE TRIGGER IF NOT EXISTS tasks_ad AFTER DELETE ON tasks BEGIN
78            DELETE FROM tasks_fts WHERE rowid = old.id;
79        END
80        "#,
81    )
82    .execute(pool)
83    .await?;
84
85    sqlx::query(
86        r#"
87        CREATE TRIGGER IF NOT EXISTS tasks_au AFTER UPDATE ON tasks BEGIN
88            UPDATE tasks_fts SET name = new.name, spec = new.spec WHERE rowid = old.id;
89        END
90        "#,
91    )
92    .execute(pool)
93    .await?;
94
95    // Create events table
96    sqlx::query(
97        r#"
98        CREATE TABLE IF NOT EXISTS events (
99            id INTEGER PRIMARY KEY AUTOINCREMENT,
100            task_id INTEGER NOT NULL,
101            timestamp DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
102            log_type TEXT NOT NULL,
103            discussion_data TEXT NOT NULL,
104            FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE
105        )
106        "#,
107    )
108    .execute(pool)
109    .await?;
110
111    // Create index on task_id for events
112    sqlx::query(
113        r#"
114        CREATE INDEX IF NOT EXISTS idx_events_task_id ON events(task_id)
115        "#,
116    )
117    .execute(pool)
118    .await?;
119
120    // Create FTS5 virtual table for events
121    sqlx::query(
122        r#"
123        CREATE VIRTUAL TABLE IF NOT EXISTS events_fts USING fts5(
124            discussion_data,
125            content=events,
126            content_rowid=id
127        )
128        "#,
129    )
130    .execute(pool)
131    .await?;
132
133    // Create triggers for events FTS
134    sqlx::query(
135        r#"
136        CREATE TRIGGER IF NOT EXISTS events_ai AFTER INSERT ON events BEGIN
137            INSERT INTO events_fts(rowid, discussion_data) VALUES (new.id, new.discussion_data);
138        END
139        "#,
140    )
141    .execute(pool)
142    .await?;
143
144    sqlx::query(
145        r#"
146        CREATE TRIGGER IF NOT EXISTS events_ad AFTER DELETE ON events BEGIN
147            DELETE FROM events_fts WHERE rowid = old.id;
148        END
149        "#,
150    )
151    .execute(pool)
152    .await?;
153
154    sqlx::query(
155        r#"
156        CREATE TRIGGER IF NOT EXISTS events_au AFTER UPDATE ON events BEGIN
157            UPDATE events_fts SET discussion_data = new.discussion_data WHERE rowid = old.id;
158        END
159        "#,
160    )
161    .execute(pool)
162    .await?;
163
164    // Create workspace_state table
165    sqlx::query(
166        r#"
167        CREATE TABLE IF NOT EXISTS workspace_state (
168            key TEXT PRIMARY KEY,
169            value TEXT NOT NULL
170        )
171        "#,
172    )
173    .execute(pool)
174    .await?;
175
176    Ok(())
177}
intent_engine/db/mod.rs

intent_engine/db/
mod.rs
