1use super::*;
2
3pub fn default_store_path() -> PathBuf {
4 PathBuf::from(".sac").join("store.db")
5}
6
7pub fn initialize(path: &Path) -> Result<()> {
8 tracing::debug!(db_path = %path.display(), "initializing store schema");
9 let _ = open_connection(path)?;
10 tracing::info!(db_path = %path.display(), "store schema initialized");
11 Ok(())
12}
13
14pub(crate) fn open_connection(path: &Path) -> Result<Connection> {
15 tracing::trace!(db_path = %path.display(), "opening SQLite store connection");
16 if let Some(parent) = path.parent() {
17 std::fs::create_dir_all(parent)
18 .with_context(|| format!("failed to create store dir {}", parent.display()))?;
19 }
20
21 let conn = Connection::open(path)
22 .with_context(|| format!("failed to open SQLite store {}", path.display()))?;
23 conn.execute_batch(
24 "PRAGMA foreign_keys = ON;
25 PRAGMA journal_mode = WAL;
26 CREATE TABLE IF NOT EXISTS threads (
27 name TEXT NOT NULL,
28 session_id TEXT NOT NULL,
29 created_at TEXT NOT NULL,
30 updated_at TEXT NOT NULL,
31 PRIMARY KEY (name, session_id)
32 );
33 CREATE TABLE IF NOT EXISTS episodes (
34 id INTEGER PRIMARY KEY AUTOINCREMENT,
35 thread_name TEXT NOT NULL,
36 session_id TEXT NOT NULL,
37 action TEXT NOT NULL,
38 content TEXT NOT NULL,
39 created_at TEXT NOT NULL,
40 FOREIGN KEY (thread_name, session_id) REFERENCES threads(name, session_id)
41 );
42 CREATE TABLE IF NOT EXISTS worksets (
43 id TEXT NOT NULL,
44 session_id TEXT NOT NULL,
45 kind TEXT NOT NULL,
46 instruction TEXT NOT NULL,
47 status TEXT NOT NULL,
48 summary TEXT NOT NULL,
49 verification_recipe TEXT,
50 created_at TEXT NOT NULL,
51 updated_at TEXT NOT NULL,
52 PRIMARY KEY (id, session_id)
53 );
54 CREATE TABLE IF NOT EXISTS workset_items (
55 workset_id TEXT NOT NULL,
56 session_id TEXT NOT NULL,
57 position INTEGER NOT NULL,
58 title TEXT NOT NULL,
59 thread_name TEXT NOT NULL,
60 scope TEXT NOT NULL,
61 description TEXT NOT NULL,
62 item_kind TEXT NOT NULL,
63 status TEXT NOT NULL,
64 source_threads_json TEXT NOT NULL,
65 last_summary TEXT,
66 acceptance TEXT NOT NULL DEFAULT '',
67 updated_at TEXT NOT NULL,
68 PRIMARY KEY (workset_id, session_id, position),
69 FOREIGN KEY (workset_id, session_id) REFERENCES worksets(id, session_id)
70 );
71 CREATE TABLE IF NOT EXISTS sessions (
72 session_id TEXT PRIMARY KEY,
73 cwd TEXT NOT NULL,
74 store_path TEXT NOT NULL,
75 model TEXT NOT NULL,
76 base_url TEXT NOT NULL,
77 backend TEXT,
78 reasoning_effort TEXT,
79 sandbox_json TEXT,
80 messages_json TEXT NOT NULL,
81 last_response_duration_ms INTEGER,
82 previous_response_duration_ms INTEGER,
83 response_durations_ms_json TEXT,
84 created_at TEXT NOT NULL,
85 updated_at TEXT NOT NULL
86 );
87 CREATE TABLE IF NOT EXISTS goals (
88 session_id TEXT PRIMARY KEY,
89 objective TEXT NOT NULL,
90 status TEXT NOT NULL DEFAULT 'active',
91 tokens_used INTEGER NOT NULL DEFAULT 0,
92 time_used_seconds INTEGER NOT NULL DEFAULT 0,
93 token_budget INTEGER,
94 created_at TEXT NOT NULL,
95 updated_at TEXT NOT NULL
96 );
97 CREATE INDEX IF NOT EXISTS idx_episodes_thread_session_created
98 ON episodes(thread_name, session_id, id);
99 CREATE INDEX IF NOT EXISTS idx_worksets_session_updated
100 ON worksets(session_id, updated_at DESC);
101 CREATE INDEX IF NOT EXISTS idx_workset_items_workset_position
102 ON workset_items(workset_id, session_id, position);
103 CREATE INDEX IF NOT EXISTS idx_sessions_updated_at
104 ON sessions(updated_at DESC);",
105 )?;
106 ensure_workset_items_acceptance_column(&conn)?;
107 ensure_column(&conn, "sessions", "backend", "TEXT")?;
108 ensure_column(&conn, "sessions", "reasoning_effort", "TEXT")?;
109 ensure_column(&conn, "sessions", "last_response_duration_ms", "INTEGER")?;
110 ensure_column(
111 &conn,
112 "sessions",
113 "previous_response_duration_ms",
114 "INTEGER",
115 )?;
116 ensure_column(&conn, "sessions", "response_durations_ms_json", "TEXT")?;
117 ensure_column(&conn, "sessions", "timeline_json", "TEXT")?;
118 ensure_column(&conn, "sessions", "archived_messages_json", "TEXT")?;
119 ensure_column(&conn, "goals", "goal_id", "TEXT NOT NULL DEFAULT ''")?;
120 ensure_column(&conn, "goals", "tokens_used", "INTEGER NOT NULL DEFAULT 0")?;
121 ensure_column(&conn, "goals", "time_used_seconds", "INTEGER NOT NULL DEFAULT 0")?;
122 ensure_column(&conn, "goals", "token_budget", "INTEGER")?;
123 tracing::trace!(db_path = %path.display(), "SQLite store connection ready");
124 Ok(conn)
125}
126
127fn ensure_workset_items_acceptance_column(conn: &Connection) -> Result<()> {
128 let mut stmt = conn.prepare("PRAGMA table_info(workset_items)")?;
129 let mut rows = stmt.query([])?;
130 while let Some(row) = rows.next()? {
131 let name: String = row.get(1)?;
132 if name == "acceptance" {
133 return Ok(());
134 }
135 }
136
137 conn.execute(
138 "ALTER TABLE workset_items ADD COLUMN acceptance TEXT NOT NULL DEFAULT ''",
139 [],
140 )?;
141 Ok(())
142}
143
144fn ensure_column(conn: &Connection, table: &str, column: &str, definition: &str) -> Result<()> {
145 let pragma = format!("PRAGMA table_info({})", table);
146 let mut stmt = conn.prepare(&pragma)?;
147 let columns = stmt.query_map([], |row| row.get::<_, String>(1))?;
148 for existing in columns {
149 if existing? == column {
150 return Ok(());
151 }
152 }
153
154 let alter = format!("ALTER TABLE {} ADD COLUMN {} {}", table, column, definition);
155 conn.execute(&alter, [])?;
156 Ok(())
157}