Struct SqliteBackend 

pub struct SqliteBackend {
    conn: Mutex<Connection>,
    db_path: Option<PathBuf>,
}

SQLite-based index backend with FTS5.

Concurrency Model

Uses a Mutex<Connection> for thread-safe access. While this serializes database operations, SQLite’s WAL mode and busy_timeout pragma mitigate contention:

• WAL mode: Allows concurrent readers with a single writer
• busy_timeout: Waits up to 5 seconds for locks instead of failing immediately
• NORMAL synchronous: Balances durability with performance

For high-throughput scenarios requiring true connection pooling, consider using r2d2-rusqlite or deadpool-sqlite. This would require refactoring to use Pool<SqliteConnectionManager> instead of Mutex<Connection>.

Fields

conn: Mutex<Connection>

Connection to the SQLite database.

Protected by Mutex because rusqlite::Connection is not Sync. WAL mode and busy_timeout handle concurrent access gracefully.

db_path: Option<PathBuf>

Path to the SQLite database (None for in-memory).

Implementations

impl SqliteBackend

pub fn new(db_path: impl Into<PathBuf>) -> Result<Self>

Creates a new SQLite backend.

Errors

Returns an error if the database cannot be opened or initialized.

pub fn in_memory() -> Result<Self>

Creates an in-memory SQLite backend (useful for testing).

Errors

Returns an error if the database cannot be initialized.

pub fn db_path(&self) -> Option<&Path>

Returns the database path.

fn initialize(&self) -> Result<()>

Initializes the database schema.

fn create_indexes(conn: &Connection)

Creates indexes for optimized queries.

fn build_filter_clause_numbered( &self, filter: &SearchFilter, start_param: usize, ) -> (String, Vec<String>, usize)

Builds a WHERE clause from a search filter with numbered parameters. Returns the clause string, the parameters, and the next parameter index.

fn record_operation_metrics( &self, operation: &'static str, start: Instant, status: &'static str, )

pub fn checkpoint(&self) -> Result<(u32, u32)>

Performs a WAL checkpoint to merge WAL file into main database (RES-M3).

This is useful for:

• Graceful shutdown (ensure WAL is flushed)
• Periodic maintenance (prevent WAL file growth)
• Before backup operations

Uses TRUNCATE mode which blocks briefly but ensures WAL is fully merged and then truncated to zero bytes.

Returns

Returns a tuple of (pages_written, pages_remaining) on success. pages_remaining should be 0 if checkpoint completed fully.

Errors

Returns an error if the checkpoint operation fails.

pub fn wal_size(&self) -> Option<u32>

Returns the current WAL file size in pages.

Useful for monitoring and deciding when to trigger a checkpoint.

Errors

Returns an error if the query fails.

pub fn checkpoint_if_needed( &self, threshold_pages: u32, ) -> Result<Option<(u32, u32)>>

Checkpoints the WAL if it exceeds the given threshold in pages.

Default SQLite auto-checkpoint threshold is 1000 pages (~4MB with 4KB pages). This method allows explicit control over checkpointing.

Arguments

• threshold_pages - Checkpoint if WAL size exceeds this number of pages.

Returns

Returns Some((pages_written, pages_remaining)) if checkpoint was performed, None if WAL was below threshold.

Errors

Returns an error if the checkpoint operation fails.

pub fn store_edge( &self, from_id: &MemoryId, to_id: &MemoryId, edge_type: EdgeType, ) -> Result<()>

Stores a memory edge relationship in the database.

Creates a directed edge from one memory to another with the specified edge type. Used by the consolidation service to link original memories to their summaries.

Arguments

• from_id - The source memory ID
• to_id - The target memory ID
• edge_type - The type of relationship

Errors

Returns an error if the database operation fails or if the memory IDs don’t exist.

Examples

use subcog::storage::index::SqliteBackend;
use subcog::models::{MemoryId, EdgeType};

let backend = SqliteBackend::new("./index.db")?;
let from_id = MemoryId::new("original_memory");
let to_id = MemoryId::new("summary_memory");

backend.store_edge(&from_id, &to_id, EdgeType::SummarizedBy)?;

pub fn query_edges( &self, from_id: &MemoryId, edge_type: EdgeType, ) -> Result<Vec<MemoryId>>

Queries edges for a given memory ID and edge type.

Returns a list of target memory IDs that have the specified edge type relationship with the source memory ID.

Arguments

• from_id - The source memory ID
• edge_type - The type of relationship to query

Errors

Returns an error if the database query fails.

Examples

use subcog::storage::index::SqliteBackend;
use subcog::models::{MemoryId, EdgeType};

let backend = SqliteBackend::new("./index.db")?;
let from_id = MemoryId::new("original_memory");

let targets = backend.query_edges(&from_id, EdgeType::SummarizedBy)?;

Trait Implementations

impl IndexBackend for SqliteBackend

fn get_memories_batch(&self, ids: &[MemoryId]) -> Result<Vec<Option<Memory>>>

Retrieves multiple memories in a single batch query (PERF-C1 fix).

Uses a single SQL query with IN clause instead of N individual queries.

fn reindex(&self, memories: &[Memory]) -> Result<()>

Re-indexes all memories in a single transaction (DB-H2).

This is more efficient than the default implementation which creates a transaction per memory.

fn index(&self, memory: &Memory) -> Result<()>

Indexes a memory for full-text search.

fn remove(&self, id: &MemoryId) -> Result<bool>

Removes a memory from the index.

fn search( &self, query: &str, filter: &SearchFilter, limit: usize, ) -> Result<Vec<(MemoryId, f32)>>

Searches for memories matching a text query.

fn clear(&self) -> Result<()>

Clears the entire index.

fn list_all( &self, filter: &SearchFilter, limit: usize, ) -> Result<Vec<(MemoryId, f32)>>

Lists all indexed memories, optionally filtered.

fn get_memory(&self, id: &MemoryId) -> Result<Option<Memory>>

Retrieves a memory by ID.

impl PersistenceBackend for SqliteBackend

fn store(&self, memory: &Memory) -> Result<()>

Stores a memory.

fn get(&self, id: &MemoryId) -> Result<Option<Memory>>

Retrieves a memory by ID.

fn delete(&self, id: &MemoryId) -> Result<bool>

Deletes a memory by ID.

fn list_ids(&self) -> Result<Vec<MemoryId>>

Lists all memory IDs.

fn get_batch(&self, ids: &[MemoryId]) -> Result<Vec<Memory>>

Retrieves multiple memories by their IDs in a single batch operation.

fn exists(&self, id: &MemoryId) -> Result<bool>

Checks if a memory exists.

fn count(&self) -> Result<usize>

Returns the total count of memories.