roboticus-db 0.11.1

//! # roboticus-db
//!
//! SQLite persistence layer for the Roboticus agent runtime. All state --
//! sessions, memories, tool calls, policy decisions, cron jobs, embeddings,
//! skills, and semantic cache -- lives in a single SQLite database with WAL
//! mode enabled.
//!
//! ## Key Types
//!
//! - [`Database`] -- Thread-safe handle wrapping `Arc<Mutex<Connection>>`
//!
//! ## Modules
//!
//! - `schema` -- Table definitions, `initialize_db()`, migration runner
//! - `sessions` -- Session CRUD, message append/list, turn persistence
//! - `memory` -- 5-tier memory CRUD (working, episodic, semantic, procedural, relationship) + FTS5
//! - `embeddings` -- BLOB embedding storage / lookup with JSON fallback
//! - `ann` -- HNSW approximate nearest-neighbor index (instant-distance)
//! - `hippocampus` -- Long-term memory consolidation and decay
//! - `learned_skills` -- Learned skill CRUD, reinforcement (success/failure), priority
//! - `checkpoint` -- Session checkpoint / restore via `context_snapshots` table
//! - `efficiency` -- Efficiency metrics tracking and queries
//! - `agents` -- Sub-agent registry and enabled-agent CRUD
//! - `backend` -- Storage backend abstraction trait
//! - `cache` -- Semantic cache persistence (loaded on boot, flushed every 5 min)
//! - `cron` -- Cron job state, lease acquisition, run history
//! - `skills` -- Skill definition CRUD and trigger lookup
//! - `tools` -- Tool call records
//! - `policy` -- Policy decision records
//! - `metrics` -- Inference cost tracking, proxy snapshots, transactions, turn feedback
//! - `routing_dataset` -- Historical routing decision + cost outcome JOIN for ML training
//! - `shadow_routing` -- Counterfactual ML predictions stored alongside production decisions
//! - `revenue_introspection` -- Unified introspection surface: strategy health, profitability, audit trail
//! - `traces` -- Pipeline trace persistence (`pipeline_traces` table) + flight-recorder ReAct trace JSON

pub mod abuse;
pub mod agents;
pub mod ann;
pub mod approvals;
pub mod backend;
pub mod cache;
pub mod checkpoint;
pub mod cron;
pub mod delegation;
pub mod delivery_queue;
pub mod efficiency;
pub mod embeddings;
mod ext;
pub use ext::*;
pub mod hippocampus;
pub mod hygiene_log;
pub mod learned_skills;
pub mod memory;
pub mod metrics;
pub mod model_selection;
pub mod policy;
pub mod revenue_accounting;
pub mod revenue_feedback;
pub mod revenue_introspection;
pub mod revenue_opportunity_queries;
pub mod revenue_scoring;
pub mod revenue_strategy_summary;
pub mod revenue_swap_tasks;
pub mod revenue_tax_tasks;
pub mod routing_dataset;
pub mod schema;
pub mod service_revenue;
pub mod sessions;
pub mod shadow_routing;
pub mod skills;
pub mod task_events;
pub mod tasks;
pub mod tool_embeddings;
pub mod tools;
pub mod traces;

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

use rusqlite::Connection;
pub use rusqlite::params_from_iter;

use roboticus_core::Result;

#[derive(Clone)]
pub struct Database {
    conn: Arc<Mutex<Connection>>,
}

impl Database {
    /// Opens a new database at the given path (or in-memory if `":memory:"`).
    ///
    /// # Examples
    ///
    /// ```
    /// use roboticus_db::Database;
    ///
    /// let db = Database::new(":memory:").unwrap();
    /// // database is now ready for use
    /// ```
    pub fn new(path: &str) -> Result<Self> {
        let conn = if path == ":memory:" {
            Connection::open_in_memory()
        } else {
            Connection::open(path)
        }
        .db_err()?;

        // WAL mode + foreign keys + synchronous=NORMAL (safe under WAL, ~2x
        // write throughput vs FULL which adds an unnecessary extra fsync on
        // every checkpoint).  auto_vacuum=INCREMENTAL lets us reclaim space
        // on demand via `PRAGMA incremental_vacuum` without full-db rewrites.
        conn.execute_batch(
            "PRAGMA journal_mode=WAL; \
             PRAGMA foreign_keys=ON; \
             PRAGMA synchronous=NORMAL; \
             PRAGMA auto_vacuum=INCREMENTAL;",
        )
        .db_err()?;

        // For existing databases that were created with auto_vacuum=NONE,
        // switching to INCREMENTAL requires a one-time full VACUUM.  Check
        // current mode and upgrade if needed.  This is a no-op on new DBs.
        let current_auto_vacuum: i64 = conn
            .query_row("PRAGMA auto_vacuum", [], |row| row.get(0))
            .unwrap_or(0);
        if current_auto_vacuum == 0 {
            // 0 = NONE, 2 = INCREMENTAL.  VACUUM rewrites the DB file once.
            let _ = conn.execute_batch("PRAGMA auto_vacuum=INCREMENTAL; VACUUM;");
        }

        let db = Self {
            conn: Arc::new(Mutex::new(conn)),
        };
        schema::initialize_db(&db)?;
        Ok(db)
    }

    /// Acquires the database connection mutex, recovering from poison if a
    /// prior holder panicked.
    ///
    /// Rationale: the `Connection` is a handle to a WAL-mode SQLite database.
    /// If a thread panics mid-transaction, SQLite's journal automatically rolls
    /// back uncommitted changes on the next access, so the database file is
    /// never left in a corrupt state. Propagating the poison would make the
    /// entire database permanently unavailable for all threads, which is worse
    /// than allowing the next caller to proceed with a connection that SQLite
    /// has already self-healed.
    pub fn conn(&self) -> std::sync::MutexGuard<'_, Connection> {
        self.conn.lock().unwrap_or_else(|e| e.into_inner())
    }
}

impl std::fmt::Debug for Database {
    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
        f.debug_struct("Database").finish()
    }
}

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

    #[test]
    fn database_debug_impl() {
        let db = Database::new(":memory:").expect("in-memory db");
        let s = format!("{:?}", db);
        assert_eq!(s, "Database");
    }

    #[test]
    fn database_new_in_memory() {
        let db = Database::new(":memory:").expect("in-memory db");
        let _guard = db.conn();
    }

    #[test]
    fn database_new_invalid_path_returns_error() {
        let result = Database::new("/");
        assert!(result.is_err(), "opening \"/\" as database should fail");
    }
}