microclaw_engine/internal/storage/db/
mod.rs

1use rusqlite::OptionalExtension;
2use rusqlite::{params, Connection, Transaction};
3use std::path::Path;
4#[cfg(feature = "sqlite-vec")]
5use std::sync::Once;
6use std::sync::{Mutex, MutexGuard};
7
8use microclaw_core::error::MicroClawError;
9
10pub struct Database {
11    conn: Mutex<Connection>,
12}
13
14#[cfg(feature = "sqlite-vec")]
15static SQLITE_VEC_AUTOEXT_INIT: Once = Once::new();
16
17#[cfg(feature = "sqlite-vec")]
18type SqliteAutoExtensionFn = unsafe extern "C" fn(
19    *mut rusqlite::ffi::sqlite3,
20    *mut *mut i8,
21    *const rusqlite::ffi::sqlite3_api_routines,
22) -> i32;
23
24pub async fn call_blocking<T, F>(db: std::sync::Arc<Database>, f: F) -> Result<T, MicroClawError>
25where
26    T: Send + 'static,
27    F: FnOnce(&Database) -> Result<T, MicroClawError> + Send + 'static,
28{
29    tokio::task::spawn_blocking(move || f(db.as_ref()))
30        .await
31        .map_err(|e| MicroClawError::ToolExecution(format!("DB task join error: {e}")))?
32}
33
34impl Database {
35    pub(crate) fn lock_conn(&self) -> MutexGuard<'_, Connection> {
36        match self.conn.lock() {
37            Ok(guard) => guard,
38            Err(poisoned) => poisoned.into_inner(),
39        }
40    }
41
42    pub fn new(data_dir: &str) -> Result<Self, MicroClawError> {
43        let db_path = Path::new(data_dir).join("microclaw.db");
44        std::fs::create_dir_all(data_dir)?;
45
46        #[cfg(feature = "sqlite-vec")]
47        SQLITE_VEC_AUTOEXT_INIT.call_once(|| unsafe {
48            let init_fn_ptr = sqlite_vec::sqlite3_vec_init as *const ();
49            let init_fn: SqliteAutoExtensionFn = std::mem::transmute(init_fn_ptr);
50            rusqlite::ffi::sqlite3_auto_extension(Some(init_fn));
51        });
52
53        let conn = Connection::open(db_path)?;
54        conn.execute_batch("PRAGMA journal_mode=WAL; PRAGMA foreign_keys=ON;")?;
55
56        conn.execute_batch(
57            "CREATE TABLE IF NOT EXISTS chats (
58                chat_id INTEGER PRIMARY KEY,
59                chat_title TEXT,
60                chat_type TEXT NOT NULL DEFAULT 'private',
61                last_message_time TEXT NOT NULL,
62                channel TEXT,
63                external_chat_id TEXT
64            );
65
66            CREATE TABLE IF NOT EXISTS messages (
67                id TEXT NOT NULL,
68                chat_id INTEGER NOT NULL,
69                sender_name TEXT NOT NULL,
70                content TEXT NOT NULL,
71                is_from_bot INTEGER NOT NULL DEFAULT 0,
72                timestamp TEXT NOT NULL,
73                PRIMARY KEY (id, chat_id)
74            );
75
76            CREATE INDEX IF NOT EXISTS idx_messages_chat_timestamp
77                ON messages(chat_id, timestamp);
78
79            CREATE TABLE IF NOT EXISTS scheduled_tasks (
80                id INTEGER PRIMARY KEY AUTOINCREMENT,
81                chat_id INTEGER NOT NULL,
82                prompt TEXT NOT NULL,
83                schedule_type TEXT NOT NULL DEFAULT 'cron',
84                schedule_value TEXT NOT NULL,
85                timezone TEXT NOT NULL DEFAULT '',
86                next_run TEXT NOT NULL,
87                last_run TEXT,
88                status TEXT NOT NULL DEFAULT 'active',
89                created_at TEXT NOT NULL,
90                exit_criteria TEXT,
91                run_count INTEGER NOT NULL DEFAULT 0,
92                max_runs INTEGER,
93                not_after TEXT
94            );
95
96            CREATE INDEX IF NOT EXISTS idx_scheduled_tasks_status_next
97                ON scheduled_tasks(status, next_run);
98
99            CREATE TABLE IF NOT EXISTS task_run_logs (
100                id INTEGER PRIMARY KEY AUTOINCREMENT,
101                task_id INTEGER NOT NULL,
102                chat_id INTEGER NOT NULL,
103                started_at TEXT NOT NULL,
104                finished_at TEXT NOT NULL,
105                duration_ms INTEGER NOT NULL,
106                success INTEGER NOT NULL DEFAULT 1,
107                result_summary TEXT
108            );
109
110            CREATE INDEX IF NOT EXISTS idx_task_run_logs_task_id
111                ON task_run_logs(task_id);
112
113            CREATE TABLE IF NOT EXISTS scheduled_task_dlq (
114                id INTEGER PRIMARY KEY AUTOINCREMENT,
115                task_id INTEGER NOT NULL,
116                chat_id INTEGER NOT NULL,
117                failed_at TEXT NOT NULL,
118                started_at TEXT NOT NULL,
119                finished_at TEXT NOT NULL,
120                duration_ms INTEGER NOT NULL,
121                error_summary TEXT,
122                replayed_at TEXT,
123                replay_note TEXT
124            );
125            CREATE INDEX IF NOT EXISTS idx_scheduled_task_dlq_task_failed
126                ON scheduled_task_dlq(task_id, failed_at DESC);
127            CREATE INDEX IF NOT EXISTS idx_scheduled_task_dlq_chat_failed
128                ON scheduled_task_dlq(chat_id, failed_at DESC);
129
130            CREATE TABLE IF NOT EXISTS sessions (
131                chat_id INTEGER PRIMARY KEY,
132                messages_json TEXT NOT NULL,
133                updated_at TEXT NOT NULL,
134                label TEXT,
135                thinking_level TEXT,
136                verbose_level TEXT,
137                reasoning_level TEXT,
138                skill_envs_json TEXT,
139                parent_session_key TEXT,
140                fork_point INTEGER
141            );
142
143            CREATE TABLE IF NOT EXISTS llm_usage_logs (
144                id INTEGER PRIMARY KEY AUTOINCREMENT,
145                chat_id INTEGER NOT NULL,
146                caller_channel TEXT NOT NULL,
147                provider TEXT NOT NULL,
148                model TEXT NOT NULL,
149                input_tokens INTEGER NOT NULL,
150                output_tokens INTEGER NOT NULL,
151                total_tokens INTEGER NOT NULL,
152                request_kind TEXT NOT NULL DEFAULT 'agent_loop',
153                created_at TEXT NOT NULL
154            );
155
156            CREATE INDEX IF NOT EXISTS idx_llm_usage_chat_created
157                ON llm_usage_logs(chat_id, created_at);
158
159            CREATE INDEX IF NOT EXISTS idx_llm_usage_created
160                ON llm_usage_logs(created_at);
161
162            CREATE TABLE IF NOT EXISTS memories (
163                id INTEGER PRIMARY KEY AUTOINCREMENT,
164                chat_id INTEGER,
165                content TEXT NOT NULL,
166                category TEXT NOT NULL,
167                created_at TEXT NOT NULL,
168                updated_at TEXT NOT NULL,
169                embedding_model TEXT,
170                confidence REAL NOT NULL DEFAULT 0.70,
171                source TEXT NOT NULL DEFAULT 'legacy',
172                last_seen_at TEXT NOT NULL,
173                is_archived INTEGER NOT NULL DEFAULT 0,
174                archived_at TEXT,
175                chat_channel TEXT,
176                external_chat_id TEXT
177            );
178            CREATE INDEX IF NOT EXISTS idx_memories_chat ON memories(chat_id);
179
180            CREATE TABLE IF NOT EXISTS memory_reflector_state (
181                chat_id INTEGER PRIMARY KEY,
182                last_reflected_ts TEXT NOT NULL,
183                updated_at TEXT NOT NULL
184            );
185
186            CREATE TABLE IF NOT EXISTS memory_reflector_runs (
187                id INTEGER PRIMARY KEY AUTOINCREMENT,
188                chat_id INTEGER NOT NULL,
189                started_at TEXT NOT NULL,
190                finished_at TEXT NOT NULL,
191                extracted_count INTEGER NOT NULL DEFAULT 0,
192                inserted_count INTEGER NOT NULL DEFAULT 0,
193                updated_count INTEGER NOT NULL DEFAULT 0,
194                skipped_count INTEGER NOT NULL DEFAULT 0,
195                dedup_method TEXT NOT NULL,
196                parse_ok INTEGER NOT NULL DEFAULT 1,
197                error_text TEXT
198            );
199            CREATE INDEX IF NOT EXISTS idx_memory_reflector_runs_chat_started
200                ON memory_reflector_runs(chat_id, started_at);
201
202            CREATE TABLE IF NOT EXISTS memory_injection_logs (
203                id INTEGER PRIMARY KEY AUTOINCREMENT,
204                chat_id INTEGER NOT NULL,
205                created_at TEXT NOT NULL,
206                retrieval_method TEXT NOT NULL,
207                candidate_count INTEGER NOT NULL DEFAULT 0,
208                selected_count INTEGER NOT NULL DEFAULT 0,
209                omitted_count INTEGER NOT NULL DEFAULT 0,
210                tokens_est INTEGER NOT NULL DEFAULT 0
211            );
212            CREATE INDEX IF NOT EXISTS idx_memory_injection_logs_chat_created
213                ON memory_injection_logs(chat_id, created_at);
214
215            CREATE TABLE IF NOT EXISTS auth_passwords (
216                id INTEGER PRIMARY KEY CHECK(id = 1),
217                password_hash TEXT NOT NULL,
218                created_at TEXT NOT NULL,
219                updated_at TEXT NOT NULL
220            );
221
222            CREATE TABLE IF NOT EXISTS auth_sessions (
223                session_id TEXT PRIMARY KEY,
224                label TEXT,
225                created_at TEXT NOT NULL,
226                expires_at TEXT NOT NULL,
227                last_seen_at TEXT NOT NULL,
228                revoked_at TEXT
229            );
230            CREATE INDEX IF NOT EXISTS idx_auth_sessions_expires ON auth_sessions(expires_at);
231
232            CREATE TABLE IF NOT EXISTS api_keys (
233                id INTEGER PRIMARY KEY AUTOINCREMENT,
234                label TEXT NOT NULL,
235                key_hash TEXT NOT NULL UNIQUE,
236                prefix TEXT NOT NULL,
237                created_at TEXT NOT NULL,
238                revoked_at TEXT,
239                last_used_at TEXT,
240                expires_at TEXT,
241                rotated_from_key_id INTEGER
242            );
243            CREATE TABLE IF NOT EXISTS api_key_scopes (
244                api_key_id INTEGER NOT NULL,
245                scope TEXT NOT NULL,
246                PRIMARY KEY (api_key_id, scope)
247            );
248            CREATE INDEX IF NOT EXISTS idx_api_key_scopes_scope ON api_key_scopes(scope);
249
250            CREATE TABLE IF NOT EXISTS audit_logs (
251                id INTEGER PRIMARY KEY AUTOINCREMENT,
252                kind TEXT NOT NULL,
253                actor TEXT NOT NULL,
254                action TEXT NOT NULL,
255                target TEXT,
256                status TEXT NOT NULL,
257                detail TEXT,
258                created_at TEXT NOT NULL
259            );
260            CREATE INDEX IF NOT EXISTS idx_audit_logs_kind_created
261                ON audit_logs(kind, created_at DESC);
262
263            CREATE TABLE IF NOT EXISTS metrics_history (
264                timestamp_ms INTEGER PRIMARY KEY,
265                llm_completions INTEGER NOT NULL DEFAULT 0,
266                llm_input_tokens INTEGER NOT NULL DEFAULT 0,
267                llm_output_tokens INTEGER NOT NULL DEFAULT 0,
268                http_requests INTEGER NOT NULL DEFAULT 0,
269                tool_executions INTEGER NOT NULL DEFAULT 0,
270                mcp_calls INTEGER NOT NULL DEFAULT 0,
271                mcp_rate_limited_rejections INTEGER NOT NULL DEFAULT 0,
272                mcp_bulkhead_rejections INTEGER NOT NULL DEFAULT 0,
273                mcp_circuit_open_rejections INTEGER NOT NULL DEFAULT 0,
274                active_sessions INTEGER NOT NULL DEFAULT 0
275            );
276            CREATE INDEX IF NOT EXISTS idx_metrics_history_ts ON metrics_history(timestamp_ms);
277            ",
278        )?;
279
280        ensure_chat_identity_schema(&conn)?;
281        ensure_memory_schema(&conn)?;
282        ensure_sessions_schema(&conn)?;
283        conn.execute(
284            "CREATE INDEX IF NOT EXISTS idx_memories_active_updated ON memories(is_archived, updated_at)",
285            [],
286        )?;
287        conn.execute(
288            "CREATE INDEX IF NOT EXISTS idx_memories_confidence ON memories(confidence)",
289            [],
290        )?;
291        // Composite index for archive_excess_memories: covers capacity enforcement queries
292        conn.execute(
293            "CREATE INDEX IF NOT EXISTS idx_memories_chat_active_confidence ON memories(chat_id, is_archived, confidence, last_seen_at)",
294            [],
295        )?;
296        conn.execute(
297            "CREATE TABLE IF NOT EXISTS db_meta (key TEXT PRIMARY KEY, value TEXT NOT NULL)",
298            [],
299        )?;
300        apply_schema_migrations(&conn)?;
301
302        Ok(Database {
303            conn: Mutex::new(conn),
304        })
305    }
306}
307
308pub mod audit;
309pub mod auth;
310pub mod chats;
311pub mod learning;
312pub mod memory;
313pub mod meta;
314pub mod outbox;
315pub mod schema;
316pub mod sessions;
317pub mod subagents;
318pub mod tasks;
319#[cfg(test)]
320pub(crate) mod test_support;
321pub mod tool_cache;
322pub mod turns;
323pub mod usage;
324
325pub use self::audit::*;
326pub use self::auth::*;
327pub use self::chats::*;
328pub use self::learning::*;
329pub use self::memory::*;
330pub use self::meta::*;
331pub use self::outbox::*;
332pub(crate) use self::schema::*;
333pub use self::sessions::*;
334pub use self::subagents::*;
335pub use self::tasks::*;
336pub use self::tool_cache::*;
337pub use self::turns::*;
338pub use self::usage::*;
microclaw_engine/internal/storage/db/mod.rs

microclaw_engine/internal/storage/db/
mod.rs
