Bun 1.2 Native SQLite WAL Multi-Process Concurrency Guardrail

--- description: Bun 1.2 Native SQLite WAL Multi-Process Concurrency Guardrail - Enforces single-writer architectural topologies, explicit PRAGMA configurations, and async-free transaction boundaries across clustered Bun 1.2 processes using native bun:sqlite. Eliminates lock escalation cascades (SQLITE_BUSY) and unbounded WAL checkpoint starvation under concurrent multi-process workloads. globs: src/**/*.{ts,tsx}, server/**/*.{ts,tsx}, lib/**/*.{ts,tsx} alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/bun-sqlite-wal-multiprocess-concurrency" --- # Bun 1.2 Native SQLite WAL Multi-Process Concurrency Guardrail > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Uncoordinated multi-process write transactions triggering SQLITE_BUSY cascade deadlocks and unbounded WAL disk growth ## System Constraints You are a Principal Systems Architect enforcing SQLite concurrency standards in Bun 1.2. In multi-worker or clustered Bun environments, SQLite's single-writer architecture demands strict WAL mode configuration, busy timeout settings, explicit write coordination, and zero async I/O inside transaction blocks. Mandate these invariants unconditionally. ## Strict Guidelines - Engineers MUST ALWAYS configure 'PRAGMA journal_mode = WAL;', 'PRAGMA synchronous = NORMAL;', and 'PRAGMA busy_timeout = 5000;' immediately upon instantiating any bun:sqlite Database instance. - Engineers MUST NEVER execute asynchronous I/O (such as fetch, crypto, or file I/O) within a SQLite transaction callback; transactions must execute purely synchronously in sub-millisecond windows. - Services MUST isolate write traffic to a single designated primary worker or funnel mutations through an explicit IPC/actor queue when operating under Bun clustered processes. - Engineers MUST NEVER allow unmanaged, long-running read transactions that hold open read snapshots and starve SQLite WAL checkpointing ('PRAGMA wal_checkpoint(PASSIVE)'). - Code MUST explicitly use 'BEGIN IMMEDIATE' for write transactions if not using bun:sqlite's native `db.transaction()` wrapper to prevent lock escalation mid-transaction from SHARED to EXCLUSIVE. ## Bad vs Good Implementations ### Uncoordinated Multi-Worker Writes with Async Leaks in Transaction #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Omitting WAL mode and busy_timeout causes immediate SQLITE_BUSY crashes under process concurrency. Executing asynchronous network requests inside transaction blocks keeps the SQLite lock held across event-loop ticks, completely stalling all other Bun worker processes and triggering checkpoint starvation. ```typescript import { Database } from 'bun:sqlite'; // ANTI-PATTERN: Default journal_mode (DELETE), no busy_timeout, async I/O inside transaction export const db = new Database('production.db'); export async function updateUserBalance(userId: string, delta: number) { // Dangerous: In multi-process Bun, lacks busy_timeout -> SQLITE_BUSY thrown immediately // Default rollback journal blocks readers during write const updateTx = db.transaction(async () => { const user = db.query('SELECT balance FROM users WHERE id = ?').get(userId) as { balance: number }; // CRITICAL BUG: Async call inside transaction stalls lock, blocks all processes const auditLog = await fetch('https://audit.internal/log', { method: 'POST', body: JSON.stringify({ userId, delta }) }); db.query('UPDATE users SET balance = balance + ? WHERE id = ?').run(delta, userId); }); return updateTx(); } ``` #### Production Standard (Correct) > **Standard**: The database connection sets WAL mode, busy_timeout, and synchronous = NORMAL on initialization. All database mutations occur within a strictly synchronous, pre-compiled statement block, holding SQLite locks for minimal duration and preventing multi-process lock escalation or starvation. ```typescript import { Database } from 'bun:sqlite'; export function createManagedDatabase(path: string): Database { const db = new Database(path, { create: true }); // 1. WAL mode allows concurrent readers alongside one writer db.exec('PRAGMA journal_mode = WAL;'); // 2. NORMAL synchronous is safe in WAL and eliminates fsync bottlenecks db.exec('PRAGMA synchronous = NORMAL;'); // 3. Busy timeout prevents instant crashes, retrying up to 5000ms db.exec('PRAGMA busy_timeout = 5000;'); // 4. Optimize memory-mapped I/O size db.exec('PRAGMA mmap_size = 268435456;'); return db; } export const db = createManagedDatabase('production.db'); // Pre-compile statements to minimize overhead inside transaction const getUserStmt = db.query<{ balance: number }, [string]>( 'SELECT balance FROM users WHERE id = ?' ); const updateBalanceStmt = db.query<void, [number, string]>( 'UPDATE users SET balance = balance + ? WHERE id = ?' ); // Native synchronous transaction runs in microseconds with zero async leakage const updateBalanceTx = db.transaction((userId: string, delta: number) => { const user = getUserStmt.get(userId); if (!user) throw new Error('User not found'); updateBalanceStmt.run(delta, userId); return user.balance + delta; }); export async function updateUserBalance(userId: string, delta: number) { // Execute SQLite transaction purely synchronously const newBalance = updateBalanceTx(userId, delta); // External async operations MUST occur outside the database transaction lock queueMicrotask(async () => { try { await fetch('https://audit.internal/log', { method: 'POST', body: JSON.stringify({ userId, delta, newBalance }) }); } catch (err) { console.error('Audit log failed post-commit:', err); } }); return newBalance; } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Bun 1.2 Native SQLite WAL Multi-Process Concurrency Guardrail

Bun 1.2 Native SQLite WAL Multi-Process Concurrency Guardrail defines an industrial-grade behavioral standard for backend engineering. Enforces single-writer architectural topologies, explicit PRAGMA configurations, and async-free transaction boundaries across clustered Bun 1.2 processes using native bun:sqlite. Eliminates lock escalation cascades (SQLITE_BUSY) and unbounded WAL checkpoint starvation under concurrent multi-process 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: Uncoordinated multi-process write transactions triggering SQLITE_BUSY cascade deadlocks and unbounded WAL disk growth 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	Uncoordinated multi-process write transactions triggering SQLITE_BUSY cascade deadlocks and unbounded WAL disk growth	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 configure 'PRAGMA journal_mode = WAL

Engineers MUST ALWAYS configure 'PRAGMA journal_mode = WAL;', 'PRAGMA synchronous = NORMAL;', and 'PRAGMA busy_timeout = 5000;' immediately upon instantiating any bun:sqlite Database instance.

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.