1use tracing::info;
2
3use super::{SqliteError, SqlitePool};
4
5impl SqlitePool {
6 pub(crate) fn migrate(&self) -> Result<(), SqliteError> {
8 self.with_conn(|conn| {
9 conn.execute_batch(
10 "CREATE TABLE IF NOT EXISTS _migrations (
11 version INTEGER PRIMARY KEY,
12 name TEXT NOT NULL,
13 applied_at TEXT NOT NULL DEFAULT (datetime('now'))
14 );",
15 )?;
16
17 let current_version: i64 = conn
18 .query_row(
19 "SELECT COALESCE(MAX(version), 0) FROM _migrations",
20 [],
21 |row| row.get(0),
22 )
23 .unwrap_or(0);
24
25 let migrations = Self::migrations();
26
27 for (version, name, sql) in &migrations {
28 if *version > current_version {
29 info!("Running migration {version}: {name}");
30 conn.execute_batch(sql).map_err(|e| {
31 SqliteError::Migration(format!("Migration {version} ({name}) failed: {e}"))
32 })?;
33
34 conn.execute(
35 "INSERT INTO _migrations (version, name) VALUES (?1, ?2)",
36 rusqlite::params![version, name],
37 )?;
38 }
39 }
40
41 if current_version < migrations.last().map_or(0, |m| m.0) {
42 info!(
43 "Migrations complete (v{current_version} → v{})",
44 migrations.last().expect("BUG: migrations list is empty").0
45 );
46 }
47
48 Ok(())
49 })
50 }
51
52 fn migrations() -> Vec<(i64, &'static str, &'static str)> {
54 vec![
55 (
56 1,
57 "create_sessions",
58 "
59 CREATE TABLE IF NOT EXISTS sessions (
60 id TEXT PRIMARY KEY,
61 started_at TEXT NOT NULL DEFAULT (datetime('now')),
62 ended_at TEXT,
63 channel TEXT NOT NULL DEFAULT 'cli',
64 metadata TEXT
65 );
66 ",
67 ),
68 (
69 2,
70 "create_episodes",
71 "
72 CREATE TABLE IF NOT EXISTS episodes (
73 id TEXT PRIMARY KEY,
74 session_id TEXT NOT NULL REFERENCES sessions(id),
75 role TEXT NOT NULL CHECK(role IN ('user', 'assistant', 'system')),
76 content TEXT NOT NULL,
77 timestamp TEXT NOT NULL DEFAULT (datetime('now')),
78 importance REAL NOT NULL DEFAULT 0.5,
79 decay_rate REAL NOT NULL DEFAULT 0.1,
80 reinforcement_count INTEGER NOT NULL DEFAULT 0,
81 last_accessed TEXT,
82 metadata TEXT
83 );
84
85 CREATE INDEX IF NOT EXISTS idx_episodes_session ON episodes(session_id);
86 CREATE INDEX IF NOT EXISTS idx_episodes_timestamp ON episodes(timestamp DESC);
87 CREATE INDEX IF NOT EXISTS idx_episodes_importance ON episodes(importance DESC);
88 ",
89 ),
90 (
91 3,
92 "create_episodes_fts",
93 "
94 CREATE VIRTUAL TABLE IF NOT EXISTS episodes_fts USING fts5(
95 content,
96 content_rowid='rowid',
97 tokenize='porter unicode61'
98 );
99 ",
100 ),
101 (
102 4,
103 "create_semantic_facts",
104 "
105 CREATE TABLE IF NOT EXISTS semantic_facts (
106 id TEXT PRIMARY KEY,
107 category TEXT NOT NULL,
108 subject TEXT NOT NULL,
109 predicate TEXT NOT NULL,
110 object TEXT NOT NULL,
111 confidence REAL NOT NULL DEFAULT 1.0,
112 source_episode_id TEXT REFERENCES episodes(id) ON DELETE SET NULL,
113 created_at TEXT NOT NULL DEFAULT (datetime('now')),
114 updated_at TEXT NOT NULL DEFAULT (datetime('now')),
115 superseded_by TEXT REFERENCES semantic_facts(id) ON DELETE SET NULL
116 );
117
118 CREATE INDEX IF NOT EXISTS idx_facts_category ON semantic_facts(category);
119 CREATE INDEX IF NOT EXISTS idx_facts_subject ON semantic_facts(subject);
120 ",
121 ),
122 (
123 5,
124 "create_user_profile",
125 "
126 CREATE TABLE IF NOT EXISTS user_profile (
127 key TEXT PRIMARY KEY,
128 value TEXT NOT NULL,
129 source TEXT,
130 updated_at TEXT NOT NULL DEFAULT (datetime('now'))
131 );
132 ",
133 ),
134 (
135 6,
136 "create_procedures",
137 "
138 CREATE TABLE IF NOT EXISTS procedures (
139 id TEXT PRIMARY KEY,
140 trigger_pattern TEXT NOT NULL,
141 steps_json TEXT NOT NULL DEFAULT '[]',
142 created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
143 updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
144 use_count INTEGER NOT NULL DEFAULT 0
145 );
146 CREATE INDEX IF NOT EXISTS idx_procedures_trigger
147 ON procedures(trigger_pattern);
148 ",
149 ),
150 (
151 7,
152 "create_audit_log",
153 "
154 CREATE TABLE IF NOT EXISTS audit_log (
155 id INTEGER PRIMARY KEY AUTOINCREMENT,
156 timestamp TEXT NOT NULL DEFAULT (datetime('now')),
157 action TEXT NOT NULL,
158 details TEXT,
159 prev_hash TEXT,
160 hash TEXT NOT NULL
161 );
162
163 CREATE INDEX IF NOT EXISTS idx_audit_timestamp ON audit_log(timestamp DESC);
164 ",
165 ),
166 (
167 10,
168 "add_namespace_to_semantic_facts",
169 "
170 ALTER TABLE semantic_facts ADD COLUMN namespace TEXT NOT NULL DEFAULT 'personal';
171 CREATE INDEX IF NOT EXISTS idx_facts_namespace ON semantic_facts(namespace);
172 ",
173 ),
174 (
175 11,
176 "add_namespace_to_episodes",
177 "
178 ALTER TABLE episodes ADD COLUMN namespace TEXT NOT NULL DEFAULT 'personal';
179 CREATE INDEX IF NOT EXISTS idx_episodes_namespace ON episodes(namespace);
180 ",
181 ),
182 (
183 12,
184 "rebuild_procedures_table",
185 "
186 DROP TABLE IF EXISTS procedures;
187 CREATE TABLE IF NOT EXISTS procedures (
188 id TEXT PRIMARY KEY,
189 trigger_pattern TEXT NOT NULL,
190 steps_json TEXT NOT NULL DEFAULT '[]',
191 created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
192 updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
193 use_count INTEGER NOT NULL DEFAULT 0
194 );
195 CREATE INDEX IF NOT EXISTS idx_procedures_trigger
196 ON procedures(trigger_pattern);
197 ",
198 ),
199 (
200 13,
201 "create_episode_promotions",
202 "
203 CREATE TABLE IF NOT EXISTS episode_promotions (
204 episode_id TEXT PRIMARY KEY REFERENCES episodes(id) ON DELETE CASCADE,
205 fact_id TEXT NOT NULL REFERENCES semantic_facts(id) ON DELETE CASCADE,
206 promoted_at TEXT NOT NULL DEFAULT (datetime('now'))
207 );
208 ",
209 ),
210 (
211 14,
212 "create_scheduled_intents",
213 "
214 CREATE TABLE IF NOT EXISTS scheduled_intents (
215 id TEXT PRIMARY KEY,
216 description TEXT NOT NULL,
217 cron TEXT,
218 namespace TEXT NOT NULL DEFAULT 'personal',
219 created_at TEXT NOT NULL DEFAULT (datetime('now')),
220 status TEXT NOT NULL DEFAULT 'scheduled',
221 metadata TEXT
222 );
223 CREATE INDEX IF NOT EXISTS idx_scheduled_intents_namespace
224 ON scheduled_intents(namespace);
225 CREATE INDEX IF NOT EXISTS idx_scheduled_intents_status
226 ON scheduled_intents(status);
227 ",
228 ),
229 (
230 15,
231 "create_notification_outbox",
232 "
233 CREATE TABLE IF NOT EXISTS notification_outbox (
234 id TEXT PRIMARY KEY,
235 content TEXT NOT NULL,
236 priority INTEGER NOT NULL DEFAULT 1,
237 triggered_by TEXT NOT NULL DEFAULT '',
238 created_at TEXT NOT NULL DEFAULT (datetime('now')),
239 delivered_at TEXT,
240 channel TEXT
241 );
242 CREATE INDEX IF NOT EXISTS idx_outbox_pending
243 ON notification_outbox(delivered_at, priority, created_at)
244 WHERE delivered_at IS NULL;
245 ",
246 ),
247 (
248 16,
249 "add_agent_column",
250 "
251 ALTER TABLE episodes ADD COLUMN agent TEXT;
252 ALTER TABLE semantic_facts ADD COLUMN agent TEXT;
253 ",
254 ),
255 (
256 17,
257 "fix_orphaned_facts",
258 "
259 -- Clear orphaned source_episode_id references
260 UPDATE semantic_facts SET source_episode_id = NULL
261 WHERE source_episode_id NOT IN (SELECT id FROM episodes);
262 -- Clear orphaned superseded_by references
263 UPDATE semantic_facts SET superseded_by = NULL
264 WHERE superseded_by NOT IN (SELECT id FROM semantic_facts);
265 ",
266 ),
267 (
268 18,
269 "add_performance_indexes",
270 "
271 -- Composite index for open-loop and habit detection
272 -- (filters by role = 'user' AND timestamp >= ?)
273 CREATE INDEX IF NOT EXISTS idx_episodes_role_timestamp
274 ON episodes(role, timestamp);
275
276 -- Partial index for active (non-superseded) facts
277 -- (count, list_all, list_by_namespace all filter superseded_by IS NULL)
278 CREATE INDEX IF NOT EXISTS idx_facts_active
279 ON semantic_facts(superseded_by)
280 WHERE superseded_by IS NULL;
281 ",
282 ),
283 ]
284 }
285
286 pub fn schema_version(&self) -> Result<i64, SqliteError> {
288 self.with_conn(|conn| {
289 let version: i64 = conn
290 .query_row(
291 "SELECT COALESCE(MAX(version), 0) FROM _migrations",
292 [],
293 |row| row.get(0),
294 )
295 .unwrap_or(0);
296 Ok(version)
297 })
298 }
299}