Bun 1.2 Native SQLite WAL Read-Write Concurrency Fence & Checkpoint Isolation

--- description: Bun 1.2 Native SQLite WAL Read-Write Concurrency Fence & Checkpoint Isolation - Enforces strict architectural isolation between a single serialized writer connection and pooled read-only instances in Bun 1.2's native sqlite engine. Mandates WAL journal mode, explicit busy timeouts, synchronized normal durability, and non-blocking passive checkpointing to prevent MMAP thrashing, thread starvation, and SQLITE_BUSY deadlocks during high-throughput edge cache persistence. globs: src/**/*.{ts,tsx}, lib/**/*.{ts,tsx}, server/**/*.{ts,tsx} alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/bun-sqlite-wal-concurrency-fence" --- # Bun 1.2 Native SQLite WAL Read-Write Concurrency Fence & Checkpoint Isolation > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Shared Read-Write Database Handles with Default Rollback Journaling and Unfenced Checkpoints Stalling Event Loops ## System Constraints You are a Principal Engineer enforcing Bun 1.2 Native SQLite WAL Concurrency Fences. Mandate strict segregation between single-writer channels and pooled read-only connections using `bun:sqlite`. You must enforce explicit WAL pragmas, busy timeouts, memory-mapped I/O controls, and passive background checkpointing to eliminate reader starvation and lock escalation under high edge loads. ## Strict Guidelines - Engineers MUST ALWAYS isolate SQLite database operations into dedicated single-writer handles and distinct read-only (`readonly: true`) reader pools. - Engineers MUST ALWAYS initialize SQLite connections with `PRAGMA journal_mode = WAL;`, `PRAGMA synchronous = NORMAL;`, and `PRAGMA busy_timeout = 5000;` on startup. - Engineers MUST NOT execute write statements (`INSERT`, `UPDATE`, `DELETE`, `DDL`) on read-only database connections or allow uncoordinated multi-threaded concurrent write queries. - Code SHOULD configure `PRAGMA mmap_size = 268435456;` (256MB) to ensure memory-mapped reads bypass user-space buffer allocations without exhausting edge memory fences. - Services MUST schedule periodic non-blocking background checkpointing using `PRAGMA wal_checkpoint(PASSIVE);` rather than allowing SQLite to execute automatic blocking truncate checkpoints during write bursts. - Components NEVER use `PRAGMA journal_mode = DELETE` or `MEMORY` for persistent local edge state accessed concurrently across async tasks. ## Bad vs Good Implementations ### Unfenced Shared Database Handle with Rollback Journaling #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Using a single read-write connection with default SQLite settings locks the entire database file during write transactions. Under high read/write loads, readers block writers and writers starve readers, culminating in unhandled SQLITE_BUSY exceptions and significant event loop latency. ```typescript import { Database } from "bun:sqlite"; // ANTI-PATTERN: Shared read-write connection without WAL configuration // High-concurrency reads and writes cause immediate SQLITE_BUSY exceptions and disk stalls. export const db = new Database("edge_cache.sqlite", { create: true }); export async function writeMetric(key: string, payload: string) { db.run("INSERT OR REPLACE INTO metrics (key, value) VALUES (?, ?)", [key, payload]); } export async function readMetric(key: string) { return db.query("SELECT value FROM metrics WHERE key = ?").get(key); } ``` #### Production Standard (Correct) > **Standard**: Segregates the database access path: a dedicated write handle handles serialized mutations wrapped in native transactions, while a distinct read-only connection handles concurrent lookups. WAL mode and synchronous=NORMAL allow concurrent readers during active writes, while passive checkpointing prevents latency spikes. ```typescript import { Database } from "bun:sqlite"; // PRODUCTION FIX: Concurrency Fence separating single serialized writer from pooled read-only handles const DB_PATH = "edge_cache.sqlite"; function configurePragmas(instance: Database, isWriter: boolean): void { instance.run("PRAGMA busy_timeout = 5000;"); if (isWriter) { instance.run("PRAGMA journal_mode = WAL;"); instance.run("PRAGMA synchronous = NORMAL;"); instance.run("PRAGMA wal_autocheckpoint = 1000;"); } instance.run("PRAGMA mmap_size = 268435456;"); // 256MB memory map } // Dedicated single-writer connection const writerDb = new Database(DB_PATH, { create: true }); configurePragmas(writerDb, true); // Read-only connection pool instance const readerDb = new Database(DB_PATH, { readonly: true }); configurePragmas(readerDb, false); // Writer queue fence to guarantee strictly serialized disk writes let writeQueue = Promise.resolve(); export async function writeMetric(key: string, payload: string): Promise<void> { return new Promise((resolve, reject) => { writeQueue = writeQueue.then(async () => { try { writerDb.transaction(() => { writerDb.run("INSERT OR REPLACE INTO metrics (key, value) VALUES (?, ?)", [key, payload]); })(); resolve(); } catch (err) { reject(err); } }); }); } export async function readMetric(key: string): Promise<{ value: string } | null> { return readerDb.query<{ value: string }, [string]>( "SELECT value FROM metrics WHERE key = ?" ).get(key); } // Non-blocking passive checkpoint fence executed out-of-band setInterval(() => { try { writerDb.run("PRAGMA wal_checkpoint(PASSIVE);"); } catch (err) { console.error("Background checkpoint error:", err); } }, 30000).unref(); ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Bun 1.2 Native SQLite WAL Read-Write Concurrency Fence & Checkpoint Isolation

Bun 1.2 Native SQLite WAL Read-Write Concurrency Fence & Checkpoint Isolation defines an industrial-grade behavioral standard for backend engineering. Enforces strict architectural isolation between a single serialized writer connection and pooled read-only instances in Bun 1.2's native sqlite engine. Mandates WAL journal mode, explicit busy timeouts, synchronized normal durability, and non-blocking passive checkpointing to prevent MMAP thrashing, thread starvation, and SQLITE_BUSY deadlocks during high-throughput edge cache persistence. 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: Shared Read-Write Database Handles with Default Rollback Journaling and Unfenced Checkpoints Stalling Event Loops 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	Shared Read-Write Database Handles with Default Rollback Journaling and Unfenced Checkpoints Stalling Event Loops	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 isolate SQLite database operations into dedicated single-writer handles and distinct read-only (`readonly

Engineers MUST ALWAYS isolate SQLite database operations into dedicated single-writer handles and distinct read-only (`readonly: true`) reader pools.

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.