csaf_models/

db.rs

// SPDX-License-Identifier: Apache-2.0
// Copyright (c) 2026 Pierre Gronau, ndaal in Cologne

//! Embedded SQLite database management using rusqlite.
//!
//! Provides a thread-safe connection pool wrapper and schema migration.
//! Adapted from the vulnerability-lookup-rs `DbPool` pattern.

use std::path::Path;
use std::sync::{Arc, Mutex};

use rusqlite::Connection;

/// Thread-safe SQLite connection pool (single writer via Mutex).
///
/// For a web server workload the single-writer approach is acceptable because
/// SQLite WAL mode allows concurrent readers. All writes are serialised
/// through the `Mutex`.
#[derive(Clone)]
pub struct DbPool {
    conn: Arc<Mutex<Connection>>,
}

impl DbPool {
    /// Open or create the SQLite database at the given path and run migrations.
    ///
    /// # Errors
    ///
    /// Returns an error if the database cannot be opened or migrations fail.
    pub fn open(db_path: &Path) -> Result<Self, rusqlite::Error> {
        if let Some(parent) = db_path.parent() {
            std::fs::create_dir_all(parent).ok();
        }

        let conn = Connection::open(db_path)?;

        conn.execute_batch("PRAGMA journal_mode=WAL;")?;
        conn.execute_batch("PRAGMA foreign_keys=ON;")?;
        conn.execute_batch("PRAGMA busy_timeout=5000;")?;

        let pool = Self {
            conn: Arc::new(Mutex::new(conn)),
        };

        pool.run_migrations()?;

        Ok(pool)
    }

    /// Open an in-memory database (useful for tests).
    ///
    /// # Errors
    ///
    /// Returns an error if the in-memory database cannot be created.
    pub fn open_in_memory() -> Result<Self, rusqlite::Error> {
        let conn = Connection::open_in_memory()?;
        conn.execute_batch("PRAGMA foreign_keys=ON;")?;

        let pool = Self {
            conn: Arc::new(Mutex::new(conn)),
        };
        pool.run_migrations()?;
        Ok(pool)
    }

    /// Execute a closure with an exclusive lock on the connection.
    ///
    /// # Errors
    ///
    /// Returns any error produced by the closure. If the internal mutex
    /// is poisoned, recovers the guard and continues to serve requests.
    pub fn with_conn<F, T>(&self, f: F) -> Result<T, rusqlite::Error>
    where
        F: FnOnce(&Connection) -> Result<T, rusqlite::Error>,
    {
        let conn = self
            .conn
            .lock()
            .unwrap_or_else(std::sync::PoisonError::into_inner);
        let result = f(&conn);
        drop(conn);
        result
    }

    /// Run all schema migrations.
    fn run_migrations(&self) -> Result<(), rusqlite::Error> {
        let conn = self
            .conn
            .lock()
            .unwrap_or_else(std::sync::PoisonError::into_inner);
        conn.execute_batch(INITIAL_MIGRATION)?;
        drop(conn);
        Ok(())
    }
}

/// Initial SQLite schema for the CSAF CRUD application.
const INITIAL_MIGRATION: &str = r"
-- Users
CREATE TABLE IF NOT EXISTS users (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    uuid            TEXT NOT NULL UNIQUE,
    login           TEXT NOT NULL UNIQUE,
    name            TEXT NOT NULL,
    email           TEXT NOT NULL UNIQUE,
    pwdhash         TEXT NOT NULL,
    apikey          TEXT NOT NULL,
    is_active       INTEGER NOT NULL DEFAULT 1,
    is_admin        INTEGER NOT NULL DEFAULT 0,
    created_at      TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
    last_seen       TEXT
);
CREATE INDEX IF NOT EXISTS idx_users_uuid ON users (uuid);
CREATE INDEX IF NOT EXISTS idx_users_apikey ON users (apikey);
CREATE INDEX IF NOT EXISTS idx_users_email ON users (email);

-- Sessions
CREATE TABLE IF NOT EXISTS sessions (
    id              TEXT PRIMARY KEY,
    user_id         INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    created_at      TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
    expires_at      TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_sessions_user_id ON sessions (user_id);

-- Audit log (ISO 8601 timestamps)
CREATE TABLE IF NOT EXISTS audit_log (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    timestamp       TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%S.000Z', 'now')),
    action          TEXT NOT NULL,
    tracking_id     TEXT NOT NULL,
    user_id         INTEGER REFERENCES users(id),
    details         TEXT,
    CONSTRAINT valid_action CHECK (action IN ('create', 'update', 'delete', 'import', 'export', 'settings_reset'))
);
CREATE INDEX IF NOT EXISTS idx_audit_log_tracking ON audit_log(tracking_id);
CREATE INDEX IF NOT EXISTS idx_audit_log_timestamp ON audit_log(timestamp);
";

#[cfg(test)]
mod tests {
    use super::*;

    #[test]
    fn test_open_in_memory() {
        let pool = DbPool::open_in_memory().expect("Failed to open in-memory DB");
        pool.with_conn(|conn| {
            conn.execute(
                "INSERT INTO users (uuid, login, name, email, pwdhash, apikey) \
                 VALUES (?1, ?2, ?3, ?4, ?5, ?6)",
                rusqlite::params![
                    "test-uuid",
                    "admin",
                    "Admin",
                    "admin@test.com",
                    "hash",
                    "key"
                ],
            )?;
            let count: i64 = conn.query_row("SELECT COUNT(*) FROM users", [], |row| row.get(0))?;
            assert_eq!(count, 1);
            Ok(())
        })
        .expect("DB operation failed");
    }

    #[test]
    fn test_audit_log_constraint() {
        let pool = DbPool::open_in_memory().expect("Failed to open in-memory DB");
        let result = pool.with_conn(|conn| {
            conn.execute(
                "INSERT INTO audit_log (action, tracking_id) VALUES (?1, ?2)",
                rusqlite::params!["invalid_action", "test-id"],
            )?;
            Ok(())
        });
        assert!(result.is_err(), "Invalid action should fail constraint");
    }

    #[test]
    fn test_audit_log_valid_actions() {
        let pool = DbPool::open_in_memory().expect("Failed to open in-memory DB");
        for action in &["create", "update", "delete", "import", "export"] {
            pool.with_conn(|conn| {
                conn.execute(
                    "INSERT INTO audit_log (action, tracking_id) VALUES (?1, ?2)",
                    rusqlite::params![action, format!("test-{action}")],
                )?;
                Ok(())
            })
            .unwrap_or_else(|e| panic!("Action '{action}' should be valid: {e}"));
        }
    }

    #[test]
    fn test_foreign_key_enforcement() {
        let pool = DbPool::open_in_memory().expect("Failed to open in-memory DB");
        let result = pool.with_conn(|conn| {
            conn.execute(
                "INSERT INTO sessions (id, user_id, expires_at) VALUES (?1, ?2, ?3)",
                rusqlite::params!["session-1", 999, "2026-12-31T00:00:00Z"],
            )?;
            Ok(())
        });
        assert!(result.is_err(), "FK to nonexistent user should fail");
    }
}