Multi-Worker SQLite Architecture

Diminuendo stores every session’s events, messages, and usage records in per-tenant SQLite databases. This is a deliberate architectural choice: per-session SQLite files yield zero-ops deployment, trivial backup (copy a file), per-session isolation, and horizontal scaling without shared state. The trade-off is that SQLite serializes all writes within a single database connection. Left unaddressed, a single-threaded writer becomes a bottleneck the moment dozens of concurrent sessions generate events, messages, and token-usage records simultaneously. The solution is a two-worker architecture that separates read and write paths into dedicated Bun Web Workers, communicating with the main thread via structured message passing.

Architecture Overview

                       Main Thread
                     (Bun.serve + WS)
                           |
                     WorkerManager
                    (Effect Layer)
                    /              \
          postMessage()         postMessage()
              |                      |
     Writer Worker            Reader Worker
  sqlite-writer.worker.ts   sqlite-reader.worker.ts
       |        |                |        |
   DbLruCache(128)          DbLruCache(64)
    /    |    \               /    |    \
  WAL   WAL   WAL         RO    RO    RO
  .db   .db   .db         .db   .db   .db

Both workers open their own database handles to the same underlying SQLite files. WAL (Write-Ahead Logging) mode allows the reader worker to execute SELECT queries concurrently while the writer worker holds a write lock — there is never contention between a read and a write.

The Writer Worker

The writer worker (sqlite-writer.worker.ts) receives fire-and-forget write commands from the main thread. It never sends responses for ordinary writes — only for explicit flush and shutdown commands that require acknowledgement.

Batching Strategy

Rather than executing each write immediately, the worker buffers incoming commands and flushes on whichever condition is met first:

Timer: 50ms since the first buffered command
Batch size: 100 commands accumulated

On flush, commands are grouped by sessionId and each group runs inside a single BEGIN / COMMIT transaction. This dramatically improves throughput because SQLite’s per-transaction overhead (fsync, WAL checkpoint) is amortized across many writes rather than paid per-statement.

// Batching constants
const BATCH_INTERVAL_MS = 50
const BATCH_MAX_SIZE = 100

Supported Write Commands

The writer handles five data-bearing command types, each mapped to a prepared INSERT statement:

Command	Table	Description
`insert_event`	`events`	Persistent gateway events with sequence numbers
`insert_event_with_id`	`events`	Same, with an explicit `event_id`
`insert_message`	`messages`	User or assistant messages tied to a turn
`insert_message_meta`	`messages`	Messages with JSON metadata (e.g., question responses)
`insert_usage`	`turn_usage`	Token counts, model info, cost per turn

In addition, three lifecycle commands manage database handles:

Command	Behavior
`ensure_db`	Opens the DB connection lazily (no-op if already cached)
`close_db`	Deferred until after the current batch’s transaction commits, then evicts the handle
`flush`	Forces an immediate flush of all buffered commands and sends a `flush_ack` response

The flush command is critical for correctness before destructive operations. When a client deletes a session, the main thread calls flush(sessionId) to guarantee all pending writes are committed before the session directory is removed from disk.

Shutdown Protocol

Shutdown bypasses the buffer entirely: it flushes all pending commands, closes every cached database handle, and posts a shutdown_ack response. The WorkerManager enforces a 5-second timeout — if the worker doesn’t acknowledge in time, it is forcibly terminated.

// Shutdown bypasses buffer
if (cmd.type === "shutdown") {
  flushBuffer()
  dbCache.closeAll()
  const ack: ShutdownAck = { type: "shutdown_ack", requestId: cmd.requestId }
  postMessage(ack)
  return
}

The Reader Worker

The reader worker (sqlite-reader.worker.ts) handles SELECT queries using a request/response pattern. Every request carries a requestId (a UUID generated by the main thread), and the response echoes it back for correlation.

Read Operations

Request Type	SQL Pattern	Use Case
`get_history`	`SELECT FROM messages WHERE session_id = ? AND rowid > ? LIMIT ?`	Paginated message history
`get_events`	`SELECT FROM events WHERE session_id = ? AND seq > ? LIMIT ?`	Event replay after a given sequence
`get_snapshot_messages`	`SELECT FROM messages WHERE session_id = ? ORDER BY created_at DESC LIMIT ?`	Recent messages for join snapshots

The reader opens databases in read-only mode. If the writer hasn’t yet created a database file for a session, the reader temporarily opens it in writable mode to run migrations, closes that handle, and then re-opens read-only. This avoids caching a writable handle in the reader’s LRU.

Error Handling

Every read operation is wrapped in a try/catch. On failure, the worker posts a typed ReaderErrorRes with the requestId and a safe error message. The main thread’s sendReaderRequest helper rejects the corresponding Effect.async callback, surfacing the error through the Effect pipeline.

WorkerManager: The Effect Layer

The WorkerManager is an Effect Context.Tag that provides a typed API for the main thread. It abstracts away the worker boundary entirely — consumers interact with methods like write(), readHistory(), and flush() without knowing that structured messages are being passed across threads.

export class WorkerManager extends Context.Tag("WorkerManager")<WorkerManager, {
  readonly write: (cmd: WriterCommand) => void
  readonly readHistory: (params: { sessionId: string; afterSeq: number; limit: number }) => Effect.Effect<MessageRow[]>
  readonly readEvents: (params: { sessionId: string; afterSeq: number; limit: number }) => Effect.Effect<EventRow[]>
  readonly readSnapshotMessages: (params: { sessionId: string; limit: number }) => Effect.Effect<MessageRow[]>
  readonly flush: (sessionId: string) => Effect.Effect<void>
  readonly closeDb: (sessionId: string) => Effect.Effect<void>
  readonly shutdown: () => Effect.Effect<void>
}>() {}

Key design details:

write() is synchronous and void-returning. The main thread posts the command and moves on. No backpressure, no acknowledgement. This is safe because the writer’s batching strategy ensures writes are committed promptly.
readHistory() / readEvents() / readSnapshotMessages() return Effect.Effect. Under the hood, each generates a UUID requestId, posts the request via postMessage, and suspends the current fiber with Effect.async until the reader responds.
flush() awaits a flush_ack. It is the only writer command that blocks the caller.
closeDb() closes handles in both workers. The writer receives a fire-and-forget close_db; the reader’s close_db is awaited for confirmation.

Worker Lifecycle

Both workers are spawned when the WorkerManagerLive layer is built. The first message sent to each is a string — the sessionsBaseDir path — which configures where they find SQLite files on disk.

const writerWorker = new Worker(
  new URL("./sqlite-writer.worker.ts", import.meta.url).href,
)
const readerWorker = new Worker(
  new URL("./sqlite-reader.worker.ts", import.meta.url).href,
)

writerWorker.postMessage(sessionsBaseDir)
readerWorker.postMessage(sessionsBaseDir)

Prepared Statement Cache

The PreparedStatements module provides a WeakMap-based cache that maps each Database handle to a Map<string, Statement>. Statements are prepared once per (db, key) pair and reused on every subsequent call, avoiding repeated SQL compilation on hot paths.

const cache = new WeakMap<Database, Map<string, Statement>>()

export function stmt<TRow, TParams>(db: Database, key: string, sql: string): Statement<TRow, TParams> {
  let stmtMap = cache.get(db)
  if (!stmtMap) {
    stmtMap = new Map()
    cache.set(db, stmtMap)
  }
  let s = stmtMap.get(key)
  if (!s) {
    s = db.query(sql)
    stmtMap.set(key, s)
  }
  return s as Statement<TRow, TParams>
}

The WeakMap keying is deliberate: when a Database handle is closed and evicted from the LRU cache, the entire statement map for that database becomes eligible for garbage collection. No manual cleanup is required beyond the evictStatements(db) call that the DbLruCache issues before closing each handle.

Database Handle LRU Cache

Both workers use DbLruCache to manage open database connections. The writer caches up to 128 handles; the reader caches up to 64. The cache uses Map insertion-order semantics for O(1) get, set, and eviction:

Get touches the entry by deleting and re-inserting it (moving it to the end of iteration order).
Set evicts the oldest entry (first in iteration order) if the cache is at capacity.
Evict calls evictStatements(db) to clear the prepared statement cache, then db.close().

export class DbLruCache {
  private readonly cache = new Map<string, Database>()
  constructor(private readonly maxSize: number) {}
  // ...
}

Worker Protocol: Type Safety Across Threads

The worker-protocol.ts module defines discriminated unions for every message that crosses the worker boundary:

Writer Commands
Writer Responses
Reader Requests
Reader Responses

export type WriterCommand =
  | InsertEventCmd
  | InsertEventWithIdCmd
  | InsertMessageCmd
  | InsertMessageMetaCmd
  | InsertUsageCmd
  | EnsureDbCmd
  | CloseDbCmd
  | FlushCmd
  | ShutdownCmd

export type WriterResponse = FlushAck | ShutdownAck

export type ReaderRequest =
  | GetHistoryReq
  | GetEventsReq
  | GetSnapshotMessagesReq
  | ReaderCloseDbReq
  | ReaderShutdownReq

export type ReaderResponse =
  | GetHistoryRes
  | GetEventsRes
  | GetSnapshotMessagesRes
  | ReaderCloseDbRes
  | ReaderShutdownRes
  | ReaderErrorRes

Every interface uses readonly fields, and command types are string literal discriminants. This makes exhaustive switch statements in both workers fully type-checked at compile time.

Why Not PostgreSQL?

Zero-Ops Deployment

No external process to manage. The gateway binary is the entire backend. Start it, and SQLite files are created on demand.

Per-Session Isolation

Each session’s data lives in its own file. A corrupted database affects exactly one session. There is no blast radius.

Trivial Backup

Back up a session by copying its directory. Restore by placing files on disk. No pg_dump, no logical replication.

Horizontal Scaling

No shared state means no coordination. Each gateway instance manages its own SQLite files. Scale by adding instances behind a load balancer with session affinity.

SQLite is not a silver bullet. This architecture assumes session affinity: a given session is managed by exactly one gateway instance at a time. If you need multi-writer access to the same session from multiple processes, you will need to either introduce a coordination layer or switch to a shared database.

Performance Characteristics

The two-worker design achieves the following:

Write throughput: Batching amortizes transaction overhead. At 100 commands per batch, a single writer can sustain thousands of writes per second per database.
Read latency: Reader queries run on a separate thread and never block writes. WAL mode means readers see a consistent snapshot even while the writer is mid-transaction.
Memory pressure: LRU caches (128 writer, 64 reader) bound the number of open file descriptors. WeakMap-based statement caches ensure garbage collection reclaims memory when databases are evicted.
Graceful degradation: If a worker crashes, the onerror handler on the main thread rejects all pending promises. The gateway can detect this and restart the worker or shut down cleanly.

Getting Started

Architecture

Wire Protocol

Client SDKs

Frontend Clients

Platform Integration

Operations

Multi-Worker SQLite Architecture

Multi-Worker SQLite Architecture

Architecture Overview

The Writer Worker

Batching Strategy

Supported Write Commands

Shutdown Protocol

The Reader Worker

Read Operations

Error Handling

WorkerManager: The Effect Layer

Worker Lifecycle

Prepared Statement Cache

Database Handle LRU Cache

Worker Protocol: Type Safety Across Threads

Why Not PostgreSQL?

Zero-Ops Deployment

Per-Session Isolation

Trivial Backup

Horizontal Scaling

Performance Characteristics

Getting Started

Architecture

Wire Protocol

Client SDKs

Frontend Clients

Platform Integration

Operations

​Multi-Worker SQLite Architecture

​Architecture Overview

​The Writer Worker

​Batching Strategy

​Supported Write Commands

​Shutdown Protocol

​The Reader Worker

​Read Operations

​Error Handling

​WorkerManager: The Effect Layer

​Worker Lifecycle

​Prepared Statement Cache

​Database Handle LRU Cache

​Worker Protocol: Type Safety Across Threads

​Why Not PostgreSQL?

Zero-Ops Deployment

Per-Session Isolation

Trivial Backup

Horizontal Scaling

​Performance Characteristics

Multi-Worker SQLite Architecture

Architecture Overview

The Writer Worker

Batching Strategy

Supported Write Commands

Shutdown Protocol

The Reader Worker

Read Operations

Error Handling

WorkerManager: The Effect Layer

Worker Lifecycle

Prepared Statement Cache

Database Handle LRU Cache

Worker Protocol: Type Safety Across Threads

Why Not PostgreSQL?

Performance Characteristics