Bun 1.2 Embedded SQLite WAL Checkpointing and Reader Barrier Isolation

--- description: Bun 1.2 Embedded SQLite WAL Checkpointing and Reader Barrier Isolation - Enforces strict read/write connection segregation and deterministic out-of-band WAL checkpointing for Bun 1.2 embedded SQLite workloads. Prevents SQLITE_BUSY errors, disk lock contention, and unbounded WAL file inflation under high concurrent request volumes by isolating read-only handles and managing WAL size through non-blocking checkpoints. globs: src/**/*.{ts,tsx}, lib/**/*.{ts,tsx}, services/**/*.{ts,tsx} alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/bun-sqlite-wal-checkpoint-reader-barrier-standard" --- # Bun 1.2 Embedded SQLite WAL Checkpointing and Reader Barrier Isolation > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Monolithic Read-Write DB Handles Executing Inline WAL Checkpoints on Concurrent Hot Paths ## System Constraints You are a Principal Engineer enforcing strict zero-lock SQLite concurrency architecture under Bun 1.2. Mandate the complete architectural separation of dedicated write connections and read-only reader handles, explicit WAL pragmatic configurations, and out-of-band non-blocking WAL checkpointer loops. ## Strict Guidelines - Engineers MUST ALWAYS open distinct SQLite connection handles for reading and writing: readers MUST use `readonly: true` in Bun's `Database` options. - Engineers MUST initialize the writer connection with `PRAGMA journal_mode = WAL;`, `PRAGMA synchronous = NORMAL;`, and `PRAGMA busy_timeout = 5000;` before serving traffic. - Engineers MUST NOT execute `PRAGMA wal_checkpoint` synchronously inside write or read request request-response cycles. - Services MUST implement a decoupled background checkpointer loop utilizing `PRAGMA wal_checkpoint(PASSIVE)` or `wal_checkpoint(RESTART)` to prevent reader starvation. - Components NEVER execute raw concurrent write transactions across multiple uncoordinated handles; all write transactions MUST be serialized through a single writer queue or connection actor. ## Bad vs Good Implementations ### Monolithic Shared Database with Inline Checkpoints vs. Segregated Reader Barrier with Background Checkpointer #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Using a single read/write database handle across high-throughput endpoints and triggering aggressive WAL truncation on the hot path blocks readers, triggers SQLITE_BUSY disk contention, and degrades tail latency. ```typescript import { Database } from "bun:sqlite"; // ANTI-PATTERN: Single shared read-write instance with inline checkpointing export const db = new Database("cache.sqlite"); export async function handleRequest(key: string, data?: string) { if (data) { db.run("INSERT OR REPLACE INTO store (key, value) VALUES (?, ?)", [key, data]); // Blocking checkpoint invoked directly on the hot path causes SQLITE_BUSY db.run("PRAGMA wal_checkpoint(TRUNCATE);"); return { success: true }; } // Concurrent reads share the write lock and suffer from checkpoint latency spikes const row = db.query("SELECT value FROM store WHERE key = ?").get(key); return row; } ``` #### Production Standard (Correct) > **Standard**: Separates read and write handles cleanly, using `readonly: true` to bypass SQLite table write locks. Standardizes WAL pragmas and isolates checkpointing to a decoupled passive interval that never starves reader queries. ```typescript import { Database } from "bun:sqlite"; const DB_PATH = "cache.sqlite"; // 1. Dedicated Single Writer Connection const writerDb = new Database(DB_PATH); writerDb.exec("PRAGMA journal_mode = WAL;"); writerDb.exec("PRAGMA synchronous = NORMAL;"); writerDb.exec("PRAGMA busy_timeout = 5000;"); // 2. Lock-Free Dedicated Reader Connection Barrier export const readerDb = new Database(DB_PATH, { readonly: true }); readerDb.exec("PRAGMA busy_timeout = 5000;"); // 3. Serialized Writer Interface export async function writeItem(key: string, value: string): Promise<void> { writerDb.run("INSERT OR REPLACE INTO store (key, value) VALUES (?, ?)", [key, value]); } // 4. Lock-Free Read Interface export function readItem(key: string): { value: string } | null { return readerDb.query("SELECT value FROM store WHERE key = ?").get(key) as { value: string } | null; } // 5. Decoupled, Non-blocking WAL Checkpointer setInterval(() => { try { // PASSIVE checkpoint checkpoints without waiting for concurrent readers or blocking writers writerDb.run("PRAGMA wal_checkpoint(PASSIVE);"); } catch (error) { console.error("Background WAL checkpoint failed:", error); } }, 10_000); ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Bun 1.2 Embedded SQLite WAL Checkpointing and Reader Barrier Isolation

Bun 1.2 Embedded SQLite WAL Checkpointing and Reader Barrier Isolation defines an industrial-grade behavioral standard for backend engineering. Enforces strict read/write connection segregation and deterministic out-of-band WAL checkpointing for Bun 1.2 embedded SQLite workloads. Prevents SQLITE_BUSY errors, disk lock contention, and unbounded WAL file inflation under high concurrent request volumes by isolating read-only handles and managing WAL size through non-blocking checkpoints. 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: Monolithic Read-Write DB Handles Executing Inline WAL Checkpoints on Concurrent Hot Paths 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	Monolithic Read-Write DB Handles Executing Inline WAL Checkpoints on Concurrent Hot Paths	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 open distinct SQLite connection handles for reading and writing

Engineers MUST ALWAYS open distinct SQLite connection handles for reading and writing: readers MUST use `readonly: true` in Bun's `Database` options.

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.