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

> Discover how ai-memory ensures thread-safe SQLite writes using a single writer actor pattern and async channels for efficient, serialized database operations. Learn about its unique approach.

- Repository: [Fabio Akita/ai-memory](https://github.com/akitaonrails/ai-memory)
- Tags: internals
- Published: 2026-09-09

---

**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`](https://github.com/akitaonrails/ai-memory/blob/main/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:

```rust
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`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/writer.rs) (lines 63-71), the pattern looks like:

```rust
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`](https://github.com/akitaonrails/ai-memory/blob/main/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:

```rust
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`](https://github.com/akitaonrails/ai-memory/blob/main/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`](https://github.com/akitaonrails/ai-memory/blob/main/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:

```rust
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`](https://github.com/akitaonrails/ai-memory/blob/main/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`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/writer.rs) manages the concurrency architecture, the concrete SQL functions reside in [`crates/ai-memory-store/src/ops.rs`](https://github.com/akitaonrails/ai-memory/blob/main/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.