zeph_memory/sqlite/
mod.rs

1// SPDX-FileCopyrightText: 2026 Andrei G <bug-ops>
2// SPDX-License-Identifier: MIT OR Apache-2.0
3
4mod acp_sessions;
5pub mod corrections;
6mod history;
7mod messages;
8mod skills;
9mod summaries;
10mod trust;
11
12use sqlx::SqlitePool;
13use sqlx::sqlite::{SqliteConnectOptions, SqlitePoolOptions};
14use std::str::FromStr;
15
16use crate::error::MemoryError;
17
18pub use acp_sessions::{AcpSessionEvent, AcpSessionInfo};
19pub use messages::role_str;
20pub use skills::{SkillMetricsRow, SkillUsageRow, SkillVersionRow};
21pub use trust::{SkillTrustRow, SourceKind};
22
23#[derive(Debug, Clone)]
24pub struct SqliteStore {
25    pool: SqlitePool,
26}
27
28impl SqliteStore {
29    /// Open (or create) the `SQLite` database and run migrations.
30    ///
31    /// Enables foreign key constraints at connection level so that
32    /// `ON DELETE CASCADE` and other FK rules are enforced.
33    ///
34    /// # Errors
35    ///
36    /// Returns an error if the database cannot be opened or migrations fail.
37    pub async fn new(path: &str) -> Result<Self, MemoryError> {
38        Self::with_pool_size(path, 5).await
39    }
40
41    /// Open (or create) the `SQLite` database with a configurable connection pool size.
42    ///
43    /// # Errors
44    ///
45    /// Returns an error if the database cannot be opened or migrations fail.
46    pub async fn with_pool_size(path: &str, pool_size: u32) -> Result<Self, MemoryError> {
47        let url = if path == ":memory:" {
48            "sqlite::memory:".to_string()
49        } else {
50            if let Some(parent) = std::path::Path::new(path).parent()
51                && !parent.as_os_str().is_empty()
52            {
53                std::fs::create_dir_all(parent)?;
54            }
55            format!("sqlite:{path}?mode=rwc")
56        };
57
58        let opts = SqliteConnectOptions::from_str(&url)?
59            .create_if_missing(true)
60            .foreign_keys(true)
61            .journal_mode(sqlx::sqlite::SqliteJournalMode::Wal)
62            .synchronous(sqlx::sqlite::SqliteSynchronous::Normal);
63
64        let pool = SqlitePoolOptions::new()
65            .max_connections(pool_size)
66            .connect_with(opts)
67            .await?;
68
69        sqlx::migrate!("./migrations").run(&pool).await?;
70
71        Ok(Self { pool })
72    }
73
74    /// Expose the underlying pool for shared access by other stores.
75    #[must_use]
76    pub fn pool(&self) -> &SqlitePool {
77        &self.pool
78    }
79
80    /// Run all migrations on the given pool.
81    ///
82    /// # Errors
83    ///
84    /// Returns an error if any migration fails.
85    pub async fn run_migrations(pool: &SqlitePool) -> Result<(), MemoryError> {
86        sqlx::migrate!("./migrations").run(pool).await?;
87        Ok(())
88    }
89}
90
91#[cfg(test)]
92mod tests {
93    use super::*;
94    use tempfile::NamedTempFile;
95
96    #[tokio::test]
97    async fn wal_journal_mode_enabled_on_file_db() {
98        let file = NamedTempFile::new().expect("tempfile");
99        let path = file.path().to_str().expect("valid path");
100
101        let store = SqliteStore::new(path).await.expect("SqliteStore::new");
102
103        let mode: String = sqlx::query_scalar("PRAGMA journal_mode")
104            .fetch_one(store.pool())
105            .await
106            .expect("PRAGMA query");
107
108        assert_eq!(mode, "wal", "expected WAL journal mode, got: {mode}");
109    }
110
111    #[tokio::test]
112    async fn creates_parent_dirs() {
113        let dir = tempfile::tempdir().expect("tempdir");
114        let deep = dir.path().join("a/b/c/zeph.db");
115        let path = deep.to_str().expect("valid path");
116        let _store = SqliteStore::new(path).await.expect("SqliteStore::new");
117        assert!(deep.exists(), "database file should exist");
118    }
119
120    #[tokio::test]
121    async fn with_pool_size_accepts_custom_size() {
122        let store = SqliteStore::with_pool_size(":memory:", 2)
123            .await
124            .expect("with_pool_size");
125        // Verify the store is operational with the custom pool size.
126        let _cid = store
127            .create_conversation()
128            .await
129            .expect("create_conversation");
130    }
131}
zeph_memory/sqlite/mod.rs

zeph_memory/sqlite/
mod.rs
