Bun 1.2 Native SQLite WAL Read-Replica Concurrency & Checkpoint Invariant

--- description: Bun 1.2 Native SQLite WAL Read-Replica Concurrency & Checkpoint Invariant - Enforces strict single-writer actor isolation and immutable read-only replicas across Bun 1.2 worker threads utilizing bun:sqlite in WAL mode. Prevents reader-induced checkpoint deadlocks, unbounded WAL file growth, and SQLITE_BUSY starvation under high-throughput concurrent edge cache workloads. globs: src/**/*.ts, src/workers/**/*.ts, src/cache/**/*.ts, src/db/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/bun-sqlite-wal-concurrency-invariant-bun-1-2-clustere-765" --- # Bun 1.2 Native SQLite WAL Read-Replica Concurrency & Checkpoint Invariant > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Multi-Threaded Read-Write bun:sqlite Handles with Long-Lived Statements Starving WAL Checkpoints ## System Constraints You are a Principal Backend Systems Architect enforcing strict concurrency and durability invariants for Bun 1.2 native SQLite (bun:sqlite) edge applications. You must prevent reader lock-starvation, SQLITE_BUSY checkpoint lockups, and unconstrained WAL accumulation across Bun clustered worker threads. Mandate a decoupled Single-Writer Multi-Reader (SWMR) architecture where workers strictly instantiate read-only handles with bounded busy timeouts, and writes/checkpoints are isolated to an orchestrated primary process. ## Strict Guidelines - Worker threads MUST ALWAYS open SQLite instances with explicit `{ readonly: true }` flags and configure `PRAGMA busy_timeout = 5000` upon initialization. - Engineers MUST NOT allow multiple Bun worker threads to open read-write SQLite connections (`{ readonly: false }` or default) to the same WAL database file. - Services MUST route all mutating operations (INSERT, UPDATE, DELETE) through an IPC message channel or dedicated singleton writer process that manages checkpointing. - Read queries MUST NEVER span asynchronous yield points (`await`, `Bun.sleep`, or external fetch calls) while holding open active SQLite statements or read transactions. - The dedicated writer MUST execute `PRAGMA wal_autocheckpoint = 1000` and schedule passive checkpoints (`PRAGMA wal_checkpoint(PASSIVE)`) to prevent blocking concurrent edge readers. - Components NEVER execute `PRAGMA wal_checkpoint(TRUNCATE)` or `PRAGMA wal_checkpoint(RESTART)` inside latency-critical HTTP request paths or inside worker threads. ## Bad vs Good Implementations ### Worker Thread Edge Cache Initialization and Access #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Opening SQLite read-write in workers causes lock contention and file corruption risk under clustered Bun threads. Initiating a transaction across an async operation ('await fetcher()') prevents SQLite from completing WAL checkpoints, causing unbounded log growth and SQLITE_BUSY exceptions. ```typescript import { Database } from "bun:sqlite"; // ANTI-PATTERN: Worker thread opens read-write database on shared path. // Default mode allows concurrent writers across threads, triggering SQLITE_BUSY // and locks that block WAL checkpointing during high-throughput queries. export class EdgeCacheWorker { private db: Database; constructor(dbPath: string) { this.db = new Database(dbPath); // Implicit read-write, no busy_timeout this.db.exec("PRAGMA journal_mode = WAL;"); } async getOrSet(key: string, fetcher: () => Promise<string>): Promise<string> { // Anti-pattern: Holding transaction across async boundary this.db.exec("BEGIN IMMEDIATE;"); const row = this.db.query("SELECT val FROM cache WHERE key = ?").get(key) as { val: string } | null; if (row) { this.db.exec("COMMIT;"); return row.val; } const fresh = await fetcher(); // Async boundary holds lock; starves checkpoint! this.db.query("INSERT OR REPLACE INTO cache (key, val) VALUES (?, ?)").run(key, fresh); this.db.exec("COMMIT;"); return fresh; } } ``` #### Production Standard (Correct) > **Standard**: Worker threads open read-only handles with 'query_only = ON' and a defensive 'busy_timeout'. Queries evaluate synchronously without holding locks across async yields. Mutating operations are dispatched to an isolated writer process, eliminating lock-starvation and checkpoint deadlocks. ```typescript import { Database } from "bun:sqlite"; // PRODUCTION FIX: Worker thread operates as a strict read-only replica. // Writes are offloaded to the main thread via IPC to ensure single-writer discipline. export class EdgeCacheWorker { private db: Database; constructor(dbPath: string) { this.db = new Database(dbPath, { readonly: true, create: false }); // Bound wait time for WAL read-locks and lock table updates this.db.exec("PRAGMA busy_timeout = 5000;"); this.db.exec("PRAGMA query_only = ON;"); } async getOrSet(key: string, fetcher: () => Promise<string>): Promise<string> { // Synchronous point-in-time read completes before any async boundary const stmt = this.db.query<{ val: string }, [string]>( "SELECT val FROM cache WHERE key = ? AND (expires_at IS NULL OR expires_at > unixepoch())" ); const hit = stmt.get(key); if (hit !== null) { return hit.val; } // Async fetch occurs outside SQLite lock scope const fresh = await fetcher(); // Delegate write to dedicated primary writer thread/actor via postMessage postMessage({ type: "CACHE_SET", payload: { key, val: fresh, ttlSeconds: 300 } }); return fresh; } close(): void { this.db.close(); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Bun 1.2 Native SQLite WAL Read-Replica Concurrency & Checkpoint Invariant

Bun 1.2 Native SQLite WAL Read-Replica Concurrency & Checkpoint Invariant defines an industrial-grade behavioral standard for backend engineering. Enforces strict single-writer actor isolation and immutable read-only replicas across Bun 1.2 worker threads utilizing bun:sqlite in WAL mode. Prevents reader-induced checkpoint deadlocks, unbounded WAL file growth, and SQLITE_BUSY starvation under high-throughput concurrent edge cache workloads. 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: Multi-Threaded Read-Write bun:sqlite Handles with Long-Lived Statements Starving WAL Checkpoints 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	Multi-Threaded Read-Write bun:sqlite Handles with Long-Lived Statements Starving WAL Checkpoints	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:

Worker threads MUST ALWAYS open SQLite instances with explicit `{ readonly

Worker threads MUST ALWAYS open SQLite instances with explicit `{ readonly: true }` flags and configure `PRAGMA busy_timeout = 5000` upon initialization.

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.