How ai-memory Manages SQLite Database Writes: Single-Writer Actor Pattern

ai-memory guarantees thread-safe SQLite mutations by dedicating a single OS thread to all write operations, using an async mpsc channel to serialize commands through a rusqlite::Connection while returning results via oneshot channels.

The ai-memory crate implements a rigorous single-writer concurrency model to eliminate database lock contention and ensure ACID compliance across its embedded SQLite storage layer. By isolating all mutating operations to a dedicated actor thread, the system prevents the "database is locked" errors common in multi-threaded access patterns while centralizing write operations through a WriterHandle API.

Single-Writer Thread Architecture

At the core of ai-memory SQLite database writes is an actor-pattern design where one dedicated thread owns the sole write-capable rusqlite::Connection. This exclusivity eliminates write-lock race conditions entirely.

Writer Creation and Handle Management

The WriterHandle::spawn method initializes this architecture by taking ownership of a SQLite Connection and spawning the writer thread that runs the worker_loop event loop. The handle stores an Arc<WriterInner> containing the async channel sender (tx) and the thread's JoinHandle, allowing the main application to communicate with the dedicated writer thread without blocking the async runtime.

In crates/ai-memory-store/src/writer.rs (lines 24-34), the initialization sequence establishes the mpsc channel, wraps the sender in the handle, and moves the receiver and connection into the new thread:

use rusqlite::Connection;
use ai_memory_store::WriterHandle;

// Assume `conn` is a freshly opened SQLite connection.
let writer = WriterHandle::spawn(conn);

Command Submission and Channel Flow

All database mutations occur through command objects sent across an async channel. The WriteCmd enum encapsulates every possible operation—from upserting pages to purging projects—allowing the public API to remain ergonomic while enforcing serialization.

Async API to Sync Worker Bridge

Public methods on WriterHandle (such as get_or_create_workspace or insert_observation) construct a WriteCmd variant and transmit it via self.inner.tx.send. Each method simultaneously creates a oneshot channel to receive the operation result, bridging the async caller context with the synchronous worker thread.

As implemented in crates/ai-memory-store/src/writer.rs (lines 63-71), the pattern looks like:

use ai_memory_core::NewObservation;
use ai_memory_store::WriterHandle;

let obs = NewObservation { /* fields */ };
let sanitized = ai_memory_core::Sanitized::new(obs); // privacy strip enforced

let obs_id = writer.insert_observation(sanitized).await?;
println!("Stored observation {}", obs_id);

Worker Loop and SQL Dispatch

The dedicated writer thread runs a continuous worker_loop that receives commands using rx.blocking_recv(). For each WriteCmd variant received, the loop dispatches to the corresponding function in crate::ops—which performs the actual SQL execution—and then forwards the result back through the oneshot sender.

This dispatch mechanism in crates/ai-memory-store/src/writer.rs (lines 96-107) ensures that complex cascades (such as PURGE operations deleting rows across multiple tables) execute atomically within a single transaction:

use ai_memory_core::{WorkspaceId, ProjectId};

let summary = writer
    .purge_project(workspace_id, project_id, "my-label".into(), None, false, ops::Compaction::Reclaim)
    .await?;
println!("Purged {} rows", summary.deleted_rows);

Error Handling and Backpressure

The system implements robust error handling through the send_or_warn utility function. If the caller's oneshot receiver has been dropped (indicating cancellation or timeout), the writer logs a warning rather than silently discarding the result. This surfaces back-pressure or cancellation to developers explicitly.

According to crates/ai-memory-store/src/writer.rs (lines 81-94), this mechanism prevents resource leaks while providing observability into write failures. Additionally, if the writer's channel fills, further writes naturally await capacity, throttling high-throughput producers without dropping data.

Graceful Shutdown Mechanism

When the WriterHandle is dropped, the implementation automatically sends a Shutdown command through the command channel and joins the writer thread. This ensures all pending writes complete before the process exits, preventing data corruption during application termination.

The Drop implementation in crates/ai-memory-store/src/writer.rs (lines 72-78) guarantees that in-flight transactions commit or rollback properly before resources are released.

Practical Session Management Examples

The single-writer pattern handles complex session lifecycle operations that require strict ordering:

use ai_memory_core::NewSession;

let session = NewSession { /* fields */ };
writer.begin_session(session.clone()).await?;

// …perform work…

writer.end_session(session.id, None).await?;

Because these operations pass through the same serialized channel, session boundaries remain consistent even under concurrent load.

Summary

• Single-threaded write ownership: One dedicated OS thread holds the exclusive rusqlite::Connection, eliminating SQLite lock errors.
• Command serialization: All mutations pass through an async mpsc channel as WriteCmd variants, ensuring FIFO execution.
• Result bridging: Each async operation uses a oneshot channel to receive results from the synchronous worker thread.
• Robust error handling: The send_or_warn utility prevents silent failures when callers drop receivers prematurely.
• Graceful termination: The Drop implementation on WriterHandle signals shutdown and joins the thread, completing pending transactions.

Frequently Asked Questions

Why does ai-memory use a single writer thread instead of a connection pool?

SQLite supports multiple readers but only one writer at the file level. By dedicating a single thread to writes, ai-memory eliminates the "database is locked" race condition entirely while ensuring that complex multi-table operations (like purge_project) execute atomically in isolated transactions. The connection pool pattern found in crates/ai-memory-store/src/reader.rs is reserved for read-only operations only.

How does ai-memory handle backpressure when the write queue fills up?

The mpsc channel acts as a natural throttle. If the writer thread cannot process commands as fast as producers generate them, the channel buffer fills and subsequent send operations await capacity. This back-pressure propagates to the async callers through .await points on the send operation, preventing unbounded memory growth and allowing the system to degrade gracefully under load.

What happens to pending writes when the application shuts down?

When the WriterHandle drops, it sends a Shutdown command through the command channel and blocks on join() for the writer thread. This ensures that any commands already received by the worker_loop complete their SQL execution and commit to disk before the process terminates, guaranteeing durability for accepted writes.

Where are the actual SQL operations implemented?

While crates/ai-memory-store/src/writer.rs manages the concurrency architecture, the concrete SQL functions reside in crates/ai-memory-store/src/ops.rs. This separation separates the "how" of thread safety from the "what" of database logic, with the worker_loop dispatching to functions like upsert_page and begin_session defined in the ops module.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}