Bun 1.2 Native SQLite WAL Concurrent Snapshot Isolation and Worker Visibility Standard

--- description: Bun 1.2 Native SQLite WAL Concurrent Snapshot Isolation and Worker Visibility Standard - Enforces deterministic snapshot freshness and WAL checkpoint progression across concurrent Bun.Worker threads and asynchronous event loops using bun:sqlite. Eliminates stale read anomalies, WAL file bloat, and SQLITE_BUSY lock exhaustion by establishing explicit read transaction boundaries and isolated worker handles. globs: src/**/*.ts, src/**/*.worker.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/bun-sqlite-wal-snapshot-isolation-production-standard" --- # Bun 1.2 Native SQLite WAL Concurrent Snapshot Isolation and Worker Visibility Standard > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Dangling Read Snapshots and Cross-Worker Handle Contention in Bun SQLite WAL Mode ## System Constraints You are a Principal Software Architect enforcing Bun 1.2 Native SQLite WAL Concurrent Snapshot Isolation invariants. You must prevent stale read anomalies, SQLite lock starvation (SQLITE_BUSY), and WAL uncheck-pointed log bloat across concurrent worker pools and asynchronous event-loop microtasks. Enforce per-thread connection isolation, non-leaking prepared statement iterators, explicit transaction scopes, and strictly bounded PRAGMA WAL runtime configurations. ## Strict Guidelines - Engineers MUST ALWAYS initialize Bun 1.2 `Database` instances with `PRAGMA journal_mode = WAL;`, `PRAGMA busy_timeout = 5000;`, and `PRAGMA synchronous = NORMAL;` to allow non-blocking concurrent readers alongside a serial writer. - Engineers MUST NOT share a single `bun:sqlite` `Database` instance across concurrent `Bun.Worker` boundaries; each worker thread MUST open its own distinct database handle referencing the shared SQLite file. - Engineers MUST ALWAYS exhaust or finalize query iterators and prepared statements within the same event-loop microtask tick to prevent long-lived read snapshots from blocking SQLite WAL checkpointing. - Code SHOULD wrap sequential multi-query read operations requiring snapshot consistency in explicit read-only transactions (`BEGIN DEFERRED` / `COMMIT`) and immediately release them before initiating asynchronous I/O. - Services MUST NOT execute unbounded `await` promises while holding open SQLite transactions or active prepared statement iterators, avoiding read-mark pinning and cross-worker write stalls. ## Bad vs Good Implementations ### Resolving Cross-Worker WAL Snapshot Lag and Read Lock Starvation #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Without WAL mode and busy_timeout, SQLite defaults to database-level locks. Suspending execution via `await` while prepared statements or implicit read locks remain active holds a read mark open in the SQLite WAL index. This causes cross-worker stale reads and prevents WAL checkpointing, eventually leading to catastrophic file bloat and SQLITE_BUSY write rejections. ```typescript import { Database } from 'bun:sqlite'; // ANTI-PATTERN: Default journal mode (DELETE), no busy_timeout, holding statement open across await export const db = new Database('production.db'); export async function getRecentOrdersAndAudit(userId: string) { const stmt = db.prepare('SELECT * FROM orders WHERE user_id = ?'); // Unfinalized cursor holds open read snapshot across asynchronous network I/O const orders = stmt.all(userId); // External network hop pins the WAL read transaction indefinitely await fetch(`https://audit.internal.net/log?user=${userId}`); // Concurrent worker writes are invisible or trigger SQLITE_BUSY on checkpoint const count = db.query('SELECT COUNT(*) as total FROM orders WHERE user_id = ?').get(userId); return { orders, count }; } ``` #### Production Standard (Correct) > **Standard**: Configures WAL mode, synchronous=NORMAL, and a busy_timeout. Encloses read queries in an explicit synchronous transaction function that terminates and releases the SQLite WAL read mark before any asynchronous `await` boundary. Creates isolated per-worker connections rather than sharing mutable handles. ```typescript import { Database } from 'bun:sqlite'; export function createWorkerDatabaseConnection(): Database { const db = new Database('production.db', { strict: true }); // Configure WAL invariants for non-blocking concurrent reads and safe checkpoints db.exec(` PRAGMA journal_mode = WAL; PRAGMA busy_timeout = 5000; PRAGMA synchronous = NORMAL; `); return db; } export async function getRecentOrdersAndAudit(userId: string): Promise<{ orders: unknown[]; count: number }> { const db = createWorkerDatabaseConnection(); let orders: unknown[] = []; let count = 0; try { // Scope read snapshot strictly to synchronous transaction; release WAL mark immediately const readTx = db.transaction(() => { const ordersStmt = db.prepare('SELECT * FROM orders WHERE user_id = ?'); const countStmt = db.prepare('SELECT COUNT(*) as total FROM orders WHERE user_id = ?'); orders = ordersStmt.all(userId); const result = countStmt.get(userId) as { total: number } | null; count = result?.total ?? 0; }); readTx(); // Read transaction completes and releases snapshot before async boundary } finally { db.close(); } // Safe async I/O after SQLite lock/read-snapshot is completely relinquished await fetch(`https://audit.internal.net/log?user=${userId}`); return { orders, count }; } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Bun 1.2 Native SQLite WAL Concurrent Snapshot Isolation and Worker Visibility Standard

Bun 1.2 Native SQLite WAL Concurrent Snapshot Isolation and Worker Visibility Standard defines an industrial-grade behavioral standard for backend engineering. Enforces deterministic snapshot freshness and WAL checkpoint progression across concurrent Bun.Worker threads and asynchronous event loops using bun:sqlite. Eliminates stale read anomalies, WAL file bloat, and SQLITE_BUSY lock exhaustion by establishing explicit read transaction boundaries and isolated worker handles. 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: Dangling Read Snapshots and Cross-Worker Handle Contention in Bun SQLite WAL Mode 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	Dangling Read Snapshots and Cross-Worker Handle Contention in Bun SQLite WAL Mode	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 initialize Bun 1.2 `Database` instances with `PRAGMA journal_mode = WAL

Engineers MUST ALWAYS initialize Bun 1.2 `Database` instances with `PRAGMA journal_mode = WAL;`, `PRAGMA busy_timeout = 5000;`, and `PRAGMA synchronous = NORMAL;` to allow non-blocking concurrent readers alongside a serial writer.

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.