Bun 1.2 Worker Thread SQLite WAL Affinity and Concurrency Guardrail

--- description: Bun 1.2 Worker Thread SQLite WAL Affinity and Concurrency Guardrail - Enforces strict database connection isolation, WAL (Write-Ahead Logging) configuration, and single-writer thread affinity across concurrent native Bun worker threads. Prevents SQLITE_BUSY lock starvation, database corruption, and shared-memory descriptor desynchronization caused by uncoordinated concurrent write transactions. globs: src/**/*.{ts,tsx} alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/bun-sqlite-worker-wal-affinity-standard" --- # Bun 1.2 Worker Thread SQLite WAL Affinity and Concurrency Guardrail > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Uncoordinated Multi-Threaded Read-Write SQLite Connections Without WAL Affinity or Busy Timeouts ## System Constraints You are a Principal Engineer enforcing the Bun 1.2 Worker Thread SQLite WAL Affinity Standard. Native Bun worker threads execute in separate native OS threads. Sharing open SQLite file handles, opening unconfigured read-write connections across threads, or omitting WAL mode and busy timeouts causes SQLite locking starvation (SQLITE_BUSY) and memory-mapped file descriptor corruption. Mandate dedicated single-writer affinity or isolated read-only worker connections configured with PRAGMA journal_mode = WAL. ## Strict Guidelines - Engineers MUST ALWAYS enable Write-Ahead Logging (`PRAGMA journal_mode = WAL;`) and configure a non-zero busy timeout (`PRAGMA busy_timeout = 5000;`) on every SQLite connection. - Engineers MUST NOT initialize read-write `Database` instances in multiple concurrent worker threads; write transactions MUST be isolated to a single dedicated writer thread or orchestrated through message passing. - Worker threads executing read-only queries MUST initialize the SQLite connection using the `{ readonly: true }` option. - Services MUST ensure that `PRAGMA synchronous = NORMAL;` is configured in WAL mode to balance durability with non-blocking disk I/O. - Components NEVER share raw `Database` instances or internal statement pointers across worker boundaries via global state or serialization attempts. ## Bad vs Good Implementations ### Multi-Worker SQLite Read-Write Contention vs WAL Single-Writer Affinity #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Default Bun SQLite instances use journal_mode = DELETE without busy_timeout. When spawned in multiple Bun workers, simultaneous write operations attempt to obtain exclusive locks, triggering immediate SQLITE_BUSY errors, lock starvation, and potential shared memory index (.shm) corruption. ```typescript import { Database } from 'bun:sqlite'; // ANTI-PATTERN: Multiple native workers instantiate default read-write databases // Default journal_mode (DELETE) locks the entire database file during writes. // Omitting busy_timeout causes immediate SQLITE_BUSY exceptions under concurrency. const db = new Database('production.db'); export function recordEvent(userId: string, payload: string): void { // Multiple worker threads running this concurrent write will crash or block starvation db.run('INSERT INTO events (user_id, payload) VALUES (?, ?)', [userId, payload]); } ``` #### Production Standard (Correct) > **Standard**: By explicitly configuring WAL mode, busy timeout, and enforcing read-only constraints on reader workers, SQLite supports concurrent non-blocking readers alongside an isolated single-writer thread, eliminating SQLITE_BUSY lock collisions and file corruption. ```typescript import { Database } from 'bun:sqlite'; export interface WorkerDbConfig { isWriterThread?: boolean; } // PRODUCTION FIX: Explicit connection affinity, WAL mode, and busy timeout configuration export function initializeWorkerDatabase(config: WorkerDbConfig = {}): Database { const isWriter = config.isWriterThread ?? false; const db = new Database('production.db', { readonly: !isWriter, create: isWriter }); // Mandate WAL mode for multi-threaded read/write coexistence db.run('PRAGMA journal_mode = WAL;'); // Non-blocking timeout allows SQLite to retry before throwing SQLITE_BUSY db.run('PRAGMA busy_timeout = 5000;'); // Safe durability optimization for WAL mode db.run('PRAGMA synchronous = NORMAL;'); return db; } // Usage in Worker thread: // - Reader workers call: const db = initializeWorkerDatabase({ isWriterThread: false }); // - Dedicated writer worker calls: const db = initializeWorkerDatabase({ isWriterThread: true }); ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Bun 1.2 Worker Thread SQLite WAL Affinity and Concurrency Guardrail

Bun 1.2 Worker Thread SQLite WAL Affinity and Concurrency Guardrail defines an industrial-grade behavioral standard for backend engineering. Enforces strict database connection isolation, WAL (Write-Ahead Logging) configuration, and single-writer thread affinity across concurrent native Bun worker threads. Prevents SQLITE_BUSY lock starvation, database corruption, and shared-memory descriptor desynchronization caused by uncoordinated concurrent write transactions. Modern software architectures require strict adherence to deterministic patterns to guarantee high availability, maintainability, and zero security vulnerabilities.

Framework Paradigm Shift & Drift MechanicsLLMs are trained on millions of open-source repositories containing outdated code, legacy idioms, and unpatched antipatterns. When coding assistants generate implementations for this stack, they default to common statistical tokens: Uncoordinated Multi-Threaded Read-Write SQLite Connections Without WAL Affinity or Busy Timeouts Without an explicit system instruction file, developers spend hours debugging silent failures.

Failure Modes & Production Blast Radius

Hazard Analysis: What Breaks Without This Rule

The following failure modes have been cataloged from hundreds of hours of production code reviews where AI assistants produced non-compliant implementations:

Failure Mode	AI Symptom (The Anti-Pattern)	Severity	Production Impact & Remedy
AI Hallucination & Deprecation Drift	Uncoordinated Multi-Threaded Read-Write SQLite Connections Without WAL Affinity or Busy Timeouts	Critical	Violating this industrial standard introduces regression bugs, security risks, and unexpected runtime failures in production. Enforce explicit behavioral system directives across all AI coding assistants in your repository.
Silent Architecture Inconsistency	AI assistants produce mixed paradigms across files, creating fragmented codebase conventions.	Medium	Increases cognitive load during code reviews and leads to maintenance debt across engineering teams. Pin project conventions in .cursor/rules, CLAUDE.md, and AGENTS.md so all team agents follow identical guardrails.

Architectural Directives in Detail

Step-by-Step Production Directives

Each rule constraint below is injected into the AI agent's system prompt to establish deterministic boundaries:

Engineers MUST ALWAYS enable Write-Ahead Logging (`PRAGMA journal_mode = WAL

Engineers MUST ALWAYS enable Write-Ahead Logging (`PRAGMA journal_mode = WAL;`) and configure a non-zero busy timeout (`PRAGMA busy_timeout = 5000;`) on every SQLite connection.

Rationale:AI models frequently generate unconstrained code here because statistical language distributions lack project-specific architectural context. Mandating this directive ensures that generated code adheres to verified industrial production requirements.