egide_storage_sqlite/
lib.rs

1//! # Egide Storage - `SQLite` Backend
2//!
3//! `SQLite` implementation of the storage backend with tenant isolation.
4//! Each tenant gets its own database file for maximum security.
5
6#![forbid(unsafe_code)]
7
8use std::path::{Path, PathBuf};
9
10use async_trait::async_trait;
11use sqlx::sqlite::{SqlitePool, SqlitePoolOptions};
12use tracing::{debug, info};
13
14use egide_storage::{StorageBackend, StorageError};
15
16/// `SQLite` storage backend with tenant isolation.
17///
18/// Each tenant gets its own database file at `{base_path}/{tenant}.db`.
19/// This ensures complete data isolation between tenants.
20#[derive(Clone)]
21pub struct SqliteBackend {
22    pool: SqlitePool,
23    actor: Option<String>,
24    #[allow(dead_code)]
25    db_path: PathBuf,
26}
27
28impl SqliteBackend {
29    /// Opens or creates a `SQLite` database for a tenant.
30    ///
31    /// # Arguments
32    ///
33    /// * `base_path` - Directory where tenant databases are stored
34    /// * `tenant` - Tenant identifier (must match `[a-z0-9_-]+`)
35    ///
36    /// # Errors
37    ///
38    /// Returns an error if:
39    /// - Tenant name is invalid
40    /// - Directory cannot be created
41    /// - Database connection fails
42    pub async fn open(base_path: impl AsRef<Path>, tenant: &str) -> Result<Self, StorageError> {
43        Self::validate_tenant(tenant)?;
44
45        let base = base_path.as_ref();
46        std::fs::create_dir_all(base).map_err(|e| {
47            StorageError::ConnectionFailed(format!("failed to create directory: {e}"))
48        })?;
49
50        let db_path = base.join(format!("{tenant}.db"));
51        let db_url = format!("sqlite:{}?mode=rwc", db_path.display());
52
53        debug!(tenant = %tenant, path = %db_path.display(), "Opening SQLite database");
54
55        let pool = SqlitePoolOptions::new()
56            .max_connections(5)
57            .connect(&db_url)
58            .await
59            .map_err(|e| StorageError::ConnectionFailed(e.to_string()))?;
60
61        let backend = Self {
62            pool,
63            actor: None,
64            db_path,
65        };
66
67        backend.migrate().await?;
68
69        info!(tenant = %tenant, "SQLite backend ready");
70
71        Ok(backend)
72    }
73
74    /// Sets the actor for audit logging.
75    ///
76    /// Returns a new instance with the actor set. All operations
77    /// performed with this instance will be logged with this actor.
78    #[must_use]
79    pub fn with_actor(mut self, actor: impl Into<String>) -> Self {
80        self.actor = Some(actor.into());
81        self
82    }
83
84    /// Validates that a tenant name is safe.
85    ///
86    /// Only allows: lowercase letters, digits, underscore, hyphen.
87    fn validate_tenant(tenant: &str) -> Result<(), StorageError> {
88        if tenant.is_empty() {
89            return Err(StorageError::InvalidInput("tenant cannot be empty".into()));
90        }
91
92        if tenant.len() > 64 {
93            return Err(StorageError::InvalidInput("tenant name too long".into()));
94        }
95
96        let valid = tenant
97            .chars()
98            .all(|c| c.is_ascii_lowercase() || c.is_ascii_digit() || c == '_' || c == '-');
99
100        if !valid {
101            return Err(StorageError::InvalidInput(
102                "tenant must match [a-z0-9_-]+".into(),
103            ));
104        }
105
106        Ok(())
107    }
108
109    /// Runs database migrations.
110    async fn migrate(&self) -> Result<(), StorageError> {
111        debug!("Running database migrations");
112
113        sqlx::query(
114            r"
115            CREATE TABLE IF NOT EXISTS kv_store (
116                key        TEXT PRIMARY KEY,
117                value      BLOB NOT NULL,
118                version    INTEGER NOT NULL DEFAULT 1,
119                created_at INTEGER NOT NULL,
120                updated_at INTEGER NOT NULL
121            )
122            ",
123        )
124        .execute(&self.pool)
125        .await
126        .map_err(|e| StorageError::ConnectionFailed(format!("migration failed: {e}")))?;
127
128        sqlx::query(
129            r"
130            CREATE TABLE IF NOT EXISTS kv_history (
131                id         INTEGER PRIMARY KEY AUTOINCREMENT,
132                key        TEXT NOT NULL,
133                value      BLOB,
134                version    INTEGER NOT NULL,
135                operation  TEXT NOT NULL,
136                actor      TEXT,
137                timestamp  INTEGER NOT NULL
138            )
139            ",
140        )
141        .execute(&self.pool)
142        .await
143        .map_err(|e| StorageError::ConnectionFailed(format!("migration failed: {e}")))?;
144
145        sqlx::query("CREATE INDEX IF NOT EXISTS idx_history_key ON kv_history (key)")
146            .execute(&self.pool)
147            .await
148            .map_err(|e| StorageError::ConnectionFailed(format!("migration failed: {e}")))?;
149
150        sqlx::query("CREATE INDEX IF NOT EXISTS idx_history_timestamp ON kv_history (timestamp)")
151            .execute(&self.pool)
152            .await
153            .map_err(|e| StorageError::ConnectionFailed(format!("migration failed: {e}")))?;
154
155        debug!("Migrations complete");
156
157        Ok(())
158    }
159
160    /// Returns the current Unix timestamp.
161    fn now() -> i64 {
162        std::time::SystemTime::now()
163            .duration_since(std::time::UNIX_EPOCH)
164            .expect("system time before UNIX epoch")
165            .as_secs()
166            .cast_signed()
167    }
168
169    /// Returns the current actor, if set.
170    #[must_use]
171    pub fn current_actor(&self) -> Option<String> {
172        self.actor.clone()
173    }
174
175    /// Executes raw SQL statements (for migrations/schema creation).
176    pub async fn execute_raw(&self, sql: &str) -> Result<(), StorageError> {
177        for statement in sql.split(';').filter(|s| !s.trim().is_empty()) {
178            sqlx::query(sqlx::AssertSqlSafe(statement.trim()))
179                .execute(&self.pool)
180                .await
181                .map_err(|e| StorageError::QueryFailed(e.to_string()))?;
182        }
183        Ok(())
184    }
185
186    /// Executes a SQL statement with parameters.
187    pub async fn execute(&self, sql: &str, params: &[&str]) -> Result<(), StorageError> {
188        let mut query = sqlx::query(sqlx::AssertSqlSafe(sql));
189        for param in params {
190            query = query.bind(*param);
191        }
192        query
193            .execute(&self.pool)
194            .await
195            .map_err(|e| StorageError::QueryFailed(e.to_string()))?;
196        Ok(())
197    }
198
199    /// Queries a single row with typed results.
200    pub async fn query_one<T>(&self, sql: &str, params: &[&str]) -> Result<Option<T>, StorageError>
201    where
202        T: for<'r> sqlx::FromRow<'r, sqlx::sqlite::SqliteRow> + Send + Unpin,
203    {
204        let mut query = sqlx::query_as::<_, T>(sqlx::AssertSqlSafe(sql));
205        for param in params {
206            query = query.bind(*param);
207        }
208        query
209            .fetch_optional(&self.pool)
210            .await
211            .map_err(|e| StorageError::QueryFailed(e.to_string()))
212    }
213
214    /// Queries multiple rows with typed results.
215    pub async fn query_all<T>(&self, sql: &str, params: &[&str]) -> Result<Vec<T>, StorageError>
216    where
217        T: for<'r> sqlx::FromRow<'r, sqlx::sqlite::SqliteRow> + Send + Unpin,
218    {
219        let mut query = sqlx::query_as::<_, T>(sqlx::AssertSqlSafe(sql));
220        for param in params {
221            query = query.bind(*param);
222        }
223        query
224            .fetch_all(&self.pool)
225            .await
226            .map_err(|e| StorageError::QueryFailed(e.to_string()))
227    }
228}
229
230#[async_trait]
231impl StorageBackend for SqliteBackend {
232    async fn get(&self, key: &str) -> Result<Option<Vec<u8>>, StorageError> {
233        let row: Option<(Vec<u8>,)> = sqlx::query_as("SELECT value FROM kv_store WHERE key = ?")
234            .bind(key)
235            .fetch_optional(&self.pool)
236            .await
237            .map_err(|e| StorageError::QueryFailed(e.to_string()))?;
238
239        Ok(row.map(|(v,)| v))
240    }
241
242    async fn put(&self, key: &str, value: &[u8]) -> Result<(), StorageError> {
243        let now = Self::now();
244
245        let existing: Option<(i64,)> = sqlx::query_as("SELECT version FROM kv_store WHERE key = ?")
246            .bind(key)
247            .fetch_optional(&self.pool)
248            .await
249            .map_err(|e| StorageError::QueryFailed(e.to_string()))?;
250
251        let (version, operation) = match existing {
252            Some((v,)) => (v + 1, "update"),
253            None => (1, "create"),
254        };
255
256        sqlx::query(
257            r"
258            INSERT INTO kv_store (key, value, version, created_at, updated_at)
259            VALUES (?, ?, ?, ?, ?)
260            ON CONFLICT(key) DO UPDATE SET
261                value = excluded.value,
262                version = excluded.version,
263                updated_at = excluded.updated_at
264            ",
265        )
266        .bind(key)
267        .bind(value)
268        .bind(version)
269        .bind(now)
270        .bind(now)
271        .execute(&self.pool)
272        .await
273        .map_err(|e| StorageError::QueryFailed(e.to_string()))?;
274
275        sqlx::query(
276            "INSERT INTO kv_history (key, value, version, operation, actor, timestamp) VALUES (?, ?, ?, ?, ?, ?)",
277        )
278        .bind(key)
279        .bind(value)
280        .bind(version)
281        .bind(operation)
282        .bind(self.actor.as_deref())
283        .bind(now)
284        .execute(&self.pool)
285        .await
286        .map_err(|e| StorageError::QueryFailed(e.to_string()))?;
287
288        Ok(())
289    }
290
291    async fn delete(&self, key: &str) -> Result<(), StorageError> {
292        let existing: Option<(i64,)> = sqlx::query_as("SELECT version FROM kv_store WHERE key = ?")
293            .bind(key)
294            .fetch_optional(&self.pool)
295            .await
296            .map_err(|e| StorageError::QueryFailed(e.to_string()))?;
297
298        if let Some((version,)) = existing {
299            let now = Self::now();
300
301            sqlx::query("DELETE FROM kv_store WHERE key = ?")
302                .bind(key)
303                .execute(&self.pool)
304                .await
305                .map_err(|e| StorageError::QueryFailed(e.to_string()))?;
306
307            sqlx::query(
308                "INSERT INTO kv_history (key, value, version, operation, actor, timestamp) VALUES (?, NULL, ?, 'delete', ?, ?)",
309            )
310            .bind(key)
311            .bind(version + 1)
312            .bind(self.actor.as_deref())
313            .bind(now)
314            .execute(&self.pool)
315            .await
316            .map_err(|e| StorageError::QueryFailed(e.to_string()))?;
317        }
318
319        Ok(())
320    }
321
322    async fn list(&self, prefix: &str) -> Result<Vec<String>, StorageError> {
323        let pattern = format!("{prefix}%");
324
325        let rows: Vec<(String,)> = sqlx::query_as("SELECT key FROM kv_store WHERE key LIKE ?")
326            .bind(&pattern)
327            .fetch_all(&self.pool)
328            .await
329            .map_err(|e| StorageError::QueryFailed(e.to_string()))?;
330
331        Ok(rows.into_iter().map(|(k,)| k).collect())
332    }
333}
334
335#[cfg(test)]
336#[allow(clippy::disallowed_methods)]
337mod tests {
338    use super::*;
339    use tempfile::TempDir;
340
341    async fn setup() -> (TempDir, SqliteBackend) {
342        let tmp = TempDir::new().unwrap();
343        let backend = SqliteBackend::open(tmp.path(), "test-tenant")
344            .await
345            .unwrap();
346        (tmp, backend)
347    }
348
349    #[tokio::test]
350    async fn test_open_creates_db() {
351        let tmp = TempDir::new().unwrap();
352        let _backend = SqliteBackend::open(tmp.path(), "my-tenant").await.unwrap();
353
354        let db_path = tmp.path().join("my-tenant.db");
355        assert!(db_path.exists(), "database file should be created");
356    }
357
358    #[tokio::test]
359    async fn test_tenant_validation_empty() {
360        let tmp = TempDir::new().unwrap();
361        let result = SqliteBackend::open(tmp.path(), "").await;
362        assert!(matches!(result, Err(StorageError::InvalidInput(_))));
363    }
364
365    #[tokio::test]
366    async fn test_tenant_validation_invalid_chars() {
367        let tmp = TempDir::new().unwrap();
368
369        let invalid_names = [
370            "Tenant",
371            "my tenant",
372            "tenant/sub",
373            "../escape",
374            "tenant.db",
375        ];
376
377        for name in invalid_names {
378            let result = SqliteBackend::open(tmp.path(), name).await;
379            assert!(
380                matches!(result, Err(StorageError::InvalidInput(_))),
381                "should reject tenant name: {name}"
382            );
383        }
384    }
385
386    #[tokio::test]
387    async fn test_tenant_validation_valid() {
388        let tmp = TempDir::new().unwrap();
389
390        let valid_names = ["tenant", "my-tenant", "tenant_1", "123", "a-b_c"];
391
392        for name in valid_names {
393            let result = SqliteBackend::open(tmp.path(), name).await;
394            assert!(result.is_ok(), "should accept tenant name: {name}");
395        }
396    }
397
398    #[tokio::test]
399    async fn test_crud_roundtrip() {
400        let (_tmp, backend) = setup().await;
401
402        // Get non-existent key
403        let result = backend.get("secret/key").await.unwrap();
404        assert!(result.is_none());
405
406        // Put
407        backend.put("secret/key", b"secret-value").await.unwrap();
408
409        // Get
410        let result = backend.get("secret/key").await.unwrap();
411        assert_eq!(result, Some(b"secret-value".to_vec()));
412
413        // Update
414        backend.put("secret/key", b"new-value").await.unwrap();
415        let result = backend.get("secret/key").await.unwrap();
416        assert_eq!(result, Some(b"new-value".to_vec()));
417
418        // Delete
419        backend.delete("secret/key").await.unwrap();
420        let result = backend.get("secret/key").await.unwrap();
421        assert!(result.is_none());
422    }
423
424    #[tokio::test]
425    async fn test_delete_nonexistent_is_ok() {
426        let (_tmp, backend) = setup().await;
427
428        // Should not error
429        backend.delete("nonexistent").await.unwrap();
430    }
431
432    #[tokio::test]
433    async fn test_list_prefix() {
434        let (_tmp, backend) = setup().await;
435
436        backend.put("prod/app/db", b"1").await.unwrap();
437        backend.put("prod/app/api", b"2").await.unwrap();
438        backend.put("prod/other/key", b"3").await.unwrap();
439        backend.put("dev/app/db", b"4").await.unwrap();
440
441        let mut keys = backend.list("prod/").await.unwrap();
442        keys.sort();
443        assert_eq!(keys, vec!["prod/app/api", "prod/app/db", "prod/other/key"]);
444
445        let mut keys = backend.list("prod/app/").await.unwrap();
446        keys.sort();
447        assert_eq!(keys, vec!["prod/app/api", "prod/app/db"]);
448
449        let keys = backend.list("staging/").await.unwrap();
450        assert!(keys.is_empty());
451    }
452
453    #[tokio::test]
454    async fn test_list_all() {
455        let (_tmp, backend) = setup().await;
456
457        backend.put("a", b"1").await.unwrap();
458        backend.put("b", b"2").await.unwrap();
459
460        let mut keys = backend.list("").await.unwrap();
461        keys.sort();
462        assert_eq!(keys, vec!["a", "b"]);
463    }
464
465    #[tokio::test]
466    async fn test_with_actor() {
467        let (_tmp, backend) = setup().await;
468        let backend = backend.with_actor("user:alice");
469
470        backend.put("key", b"value").await.unwrap();
471
472        // Verify actor in history
473        let row: (String,) = sqlx::query_as("SELECT actor FROM kv_history WHERE key = ?")
474            .bind("key")
475            .fetch_one(&backend.pool)
476            .await
477            .unwrap();
478
479        assert_eq!(row.0, "user:alice");
480    }
481
482    #[tokio::test]
483    async fn test_history_records_operations() {
484        let (_tmp, backend) = setup().await;
485        let backend = backend.with_actor("system");
486
487        // Create
488        backend.put("key", b"v1").await.unwrap();
489        // Update
490        backend.put("key", b"v2").await.unwrap();
491        // Delete
492        backend.delete("key").await.unwrap();
493
494        let rows: Vec<(String, i64)> =
495            sqlx::query_as("SELECT operation, version FROM kv_history WHERE key = ? ORDER BY id")
496                .bind("key")
497                .fetch_all(&backend.pool)
498                .await
499                .unwrap();
500
501        assert_eq!(rows.len(), 3);
502        assert_eq!(rows[0], ("create".to_string(), 1));
503        assert_eq!(rows[1], ("update".to_string(), 2));
504        assert_eq!(rows[2], ("delete".to_string(), 3));
505    }
506
507    #[tokio::test]
508    async fn test_version_increments() {
509        let (_tmp, backend) = setup().await;
510
511        backend.put("key", b"v1").await.unwrap();
512        backend.put("key", b"v2").await.unwrap();
513        backend.put("key", b"v3").await.unwrap();
514
515        let row: (i64,) = sqlx::query_as("SELECT version FROM kv_store WHERE key = ?")
516            .bind("key")
517            .fetch_one(&backend.pool)
518            .await
519            .unwrap();
520
521        assert_eq!(row.0, 3);
522    }
523
524    #[tokio::test]
525    async fn test_binary_data() {
526        let (_tmp, backend) = setup().await;
527
528        let binary_data: Vec<u8> = (0..=255).collect();
529        backend.put("binary", &binary_data).await.unwrap();
530
531        let result = backend.get("binary").await.unwrap();
532        assert_eq!(result, Some(binary_data));
533    }
534
535    #[tokio::test]
536    async fn test_tenant_isolation() {
537        let tmp = TempDir::new().unwrap();
538
539        let backend_a = SqliteBackend::open(tmp.path(), "tenant-a").await.unwrap();
540        let backend_b = SqliteBackend::open(tmp.path(), "tenant-b").await.unwrap();
541
542        backend_a.put("shared-key", b"value-a").await.unwrap();
543        backend_b.put("shared-key", b"value-b").await.unwrap();
544
545        // Each tenant sees only their own data
546        assert_eq!(
547            backend_a.get("shared-key").await.unwrap(),
548            Some(b"value-a".to_vec())
549        );
550        assert_eq!(
551            backend_b.get("shared-key").await.unwrap(),
552            Some(b"value-b".to_vec())
553        );
554    }
555}
egide_storage_sqlite/lib.rs

egide_storage_sqlite/
lib.rs
