Bun 1.2 Embedded SQLite WAL Checkpoint Starvation Fence & Deterministic Read Scopes

--- description: Bun 1.2 Embedded SQLite WAL Checkpoint Starvation Fence & Deterministic Read Scopes - Enforces zero-async execution within SQLite transactions, eager cursor materialization, and proactive WAL checkpoint fencing in Bun 1.2. Prevents unbounded Write-Ahead Log disk ballooning and checkpoint deadlocks caused by microtask-delayed read locks holding the SQLite WAL read mark open. globs: src/**/*.{ts,tsx}, lib/**/*.{ts,tsx} alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/bun-sqlite-wal-checkpoint-starvation-fence" --- # Bun 1.2 Embedded SQLite WAL Checkpoint Starvation Fence & Deterministic Read Scopes > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Interleaving Async Microtasks Across Open Read Cursors Causing WAL Checkpoint Starvation ## System Constraints You are a Principal Engineer enforcing Bun 1.2 Embedded SQLite WAL Checkpoint Starvation Fence. Mandate zero asynchronous code inside database transactions, require eager result materialization before yielding to the JavaScript event loop, and enforce defensive WAL checkpoint fences with deterministic busy timeouts. ## Strict Guidelines - Engineers MUST ALWAYS fully materialize prepared statement results into memory synchronously (e.g., using `query.all()` or Array.from) before initiating any `await` or yielding execution across microtasks. - Engineers MUST NOT supply an `async` function or return Promises inside `db.transaction(...)` callbacks under any circumstances in Bun. - Code SHOULD configure `PRAGMA busy_timeout = 5000;` and execute periodic checkpoint health checks using `PRAGMA wal_checkpoint(PASSIVE)` to monitor uncheckpointed frame thresholds. - Services MUST run an asynchronous WAL checkpoint fence worker using bounded checkpoints (`PASSIVE` or `RESTART`) with backoff to prevent unbounded `.wal` file growth under high-throughput read/write workloads. - Components NEVER leave open statement iterators or unclosed explicit read transactions across network boundaries or I/O waits. ## Bad vs Good Implementations ### Prevent WAL Starvation by Eliminating Async Microtasks Across SQLite Read Locks #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Holding open a SQLite statement iterator or read transaction across asynchronous operations (`await fetch()`) traps the WAL read mark at the oldest active reader frame. Any concurrent writer appending frames prevents WAL truncation, causing the `-wal` file to balloon into gigabytes and creating checkpoint starvation. ```typescript import { Database } from 'bun:sqlite'; const db = new Database('production.sqlite'); db.run('PRAGMA journal_mode = WAL;'); // ANTI-PATTERN: Async callback in transaction or interleaving await while cursor is open // The read transaction remains open across microtask ticks, locking the WAL read mark // and causing subsequent wal_checkpoint(TRUNCATE) calls to block or fail indefinitely. export async function processPendingOrders() { const selectOrders = db.query('SELECT id, user_id, amount FROM orders WHERE status = ?'); // Iterating cursor lazily across async network I/O for (const order of selectOrders.iterate('pending') as IterableIterator<{ id: number; user_id: number; amount: number }>) { // Awaiting here yields control to the event loop while an internal read lock is held const userResponse = await fetch(`https://api.internal/users/${order.user_id}`); const userData = await userResponse.json(); db.run('UPDATE orders SET status = ?, processed_at = ? WHERE id = ?', [ userData.valid ? 'processed' : 'failed', Date.now(), order.id, ]); } } ``` #### Production Standard (Correct) > **Standard**: All reads are synchronously materialized into memory via `query.all()` before any async network operations are invoked. SQLite transactions are strictly synchronous without async closures, ensuring read locks release immediately and allowing periodic WAL checkpoints (`PRAGMA wal_checkpoint(PASSIVE)`) to execute without starvation. ```typescript import { Database } from 'bun:sqlite'; interface OrderRow { id: number; user_id: number; amount: number; } export class OrderService { private readonly db: Database; private readonly selectPendingQuery; private readonly updateOrderQuery; constructor(databasePath: string) { this.db = new Database(databasePath, { strict: true }); // Mandatory pragmas to avoid indefinite locks and tune WAL behavior this.db.run('PRAGMA journal_mode = WAL;'); this.db.run('PRAGMA busy_timeout = 5000;'); this.db.run('PRAGMA synchronous = NORMAL;'); this.selectPendingQuery = this.db.query<OrderRow, [string]>( 'SELECT id, user_id, amount FROM orders WHERE status = ?' ); this.updateOrderQuery = this.db.query<void, [string, number, number]>( 'UPDATE orders SET status = ?, processed_at = ? WHERE id = ?' ); } public async processPendingOrders(): Promise<void> { // INVARIANT: Eagerly materialize all rows synchronously before yielding to the event loop. // SQLite locks are fully released before executing asynchronous network requests. const pendingOrders: OrderRow[] = this.selectPendingQuery.all('pending'); if (pendingOrders.length === 0) return; for (const order of pendingOrders) { const userResponse = await fetch(`https://api.internal/users/${order.user_id}`); const userData = (await userResponse.json()) as { valid: boolean }; // Atomic synchronous transaction to commit updates with zero async interleaving this.updateStatusTransaction(order.id, userData.valid ? 'processed' : 'failed'); } } // Strictly synchronous transaction wrapper; Bun transactions MUST NOT be async private updateStatusTransaction = this.db.transaction((orderId: number, status: string) => { this.updateOrderQuery.run(status, Date.now(), orderId); }); // WAL checkpoint starvation fence to guarantee bounded log size public checkpointFence(): { busy: number; log: number; checkpointed: number } { const [busy, log, checkpointed] = this.db .query<[number, number, number], []>('PRAGMA wal_checkpoint(PASSIVE);') .values()[0]; return { busy, log, checkpointed }; } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Bun 1.2 Embedded SQLite WAL Checkpoint Starvation Fence & Deterministic Read Scopes

Bun 1.2 Embedded SQLite WAL Checkpoint Starvation Fence & Deterministic Read Scopes defines an industrial-grade behavioral standard for backend engineering. Enforces zero-async execution within SQLite transactions, eager cursor materialization, and proactive WAL checkpoint fencing in Bun 1.2. Prevents unbounded Write-Ahead Log disk ballooning and checkpoint deadlocks caused by microtask-delayed read locks holding the SQLite WAL read mark open. 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: Interleaving Async Microtasks Across Open Read Cursors Causing WAL Checkpoint Starvation 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	Interleaving Async Microtasks Across Open Read Cursors Causing WAL Checkpoint Starvation	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 fully materialize prepared statement results into memory synchronously (e.g., using `query.all()` or Array.from) before initiating any `await` or yielding execution across microtasks.

Engineers MUST ALWAYS fully materialize prepared statement results into memory synchronously (e.g., using `query.all()` or Array.from) before initiating any `await` or yielding execution across microtasks.

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.