Bun 1.2 Native SQLite Concurrency, WAL Mode, and Checkpoint Isolation Standard

--- description: Bun 1.2 Native SQLite Concurrency, WAL Mode, and Checkpoint Isolation Standard - Enforces deterministic SQLite concurrency settings on Bun 1.2 runtimes by mandating Write-Ahead Logging (WAL), busy timeout thresholds, immediate transaction semantics, and background checkpoint management. This prevents write-lock starvation (SQLITE_BUSY) and unbounded WAL journal bloat during concurrent edge operations. 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-concurrency-isolation-standard" --- # Bun 1.2 Native SQLite Concurrency, WAL Mode, and Checkpoint Isolation Standard > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unconfigured Rollback Journals with Deferred Transaction Escalation and Unmonitored WAL Growth ## System Constraints You are a Principal Engineer enforcing the Bun 1.2 Native SQLite Concurrency & WAL Checkpoint Isolation Standard. You must reject any raw Database instantiation that fails to set required PRAGMA configurations (WAL mode, busy_timeout, synchronous=NORMAL), any deferred read-to-write transactions, and unmanaged WAL checkpointing. Enforce structured database factories and isolated transaction wrappers. ## Strict Guidelines - Engineers MUST ALWAYS execute `PRAGMA journal_mode = WAL;`, `PRAGMA busy_timeout = 5000;`, and `PRAGMA synchronous = NORMAL;` immediately upon opening a `bun:sqlite` Database instance. - Engineers MUST ALWAYS use `db.transaction(...).immediate()` or explicit `BEGIN IMMEDIATE` for write operations to prevent mid-transaction lock escalation deadlocks (`SQLITE_BUSY`). - Services MUST implement scheduled or threshold-based checkpointing (`PRAGMA wal_checkpoint(PASSIVE)` or `TRUNCATE`) to prevent WAL file bloat from exhausting disk resources in long-running edge processes. - Code SHOULD separate high-throughput read handles from transactional write handles or use single-writer queuing when operating over shared persistent volume mounts. - Components NEVER execute raw asynchronous tasks across an open deferred write transaction without holding an active lock boundary. ## Bad vs Good Implementations ### Configuring Bun SQLite for High Concurrency and WAL Isolation #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Default database connections in SQLite use rollback journals, lacking concurrent read/write capabilities. Without `busy_timeout`, any contention immediately throws `SQLITE_BUSY`. Deferred transactions permit multiple connections to start reading, but subsequent write escalations deadlock when competing for the single reserved write lock. ```typescript import { Database } from 'bun:sqlite'; // ANTI-PATTERN: Default journal_mode (DELETE), no busy_timeout, deferred transactions export const db = new Database('production.sqlite'); export function updateUserScore(userId: string, delta: number) { // Default transaction is DEFERRED, causing SQLITE_BUSY during concurrent read-to-write escalation const tx = db.transaction(() => { const row = db.query('SELECT score FROM users WHERE id = ?').get(userId) as { score: number } | null; if (!row) throw new Error('User not found'); db.query('UPDATE users SET score = ? WHERE id = ?').run(row.score + delta, userId); }); tx(); } ``` #### Production Standard (Correct) > **Standard**: Sets `journal_mode = WAL` to allow concurrent readers alongside a writer. `busy_timeout = 5000` allows waiting for transient write locks instead of failing immediately. `tx.immediate()` forces SQLite to claim the write lock immediately, preventing deadlock escalation. The maintenance method controls WAL growth with `PASSIVE` or `TRUNCATE` checkpoints without starving readers. ```typescript import { Database } from 'bun:sqlite'; export class SQLiteDatabaseService { private db: Database; constructor(filePath: string) { this.db = new Database(filePath, { create: true }); // CRITICAL: Configure concurrency invariants on connection boot this.db.exec('PRAGMA journal_mode = WAL;'); this.db.exec('PRAGMA busy_timeout = 5000;'); this.db.exec('PRAGMA synchronous = NORMAL;'); this.db.exec('PRAGMA wal_autocheckpoint = 1000;'); } public updateUserScore(userId: string, delta: number): void { // Invariant: Use .immediate() to acquire write lock at start, avoiding escalation deadlocks const tx = this.db.transaction(() => { const row = this.db.query('SELECT score FROM users WHERE id = ?1').get(userId) as { score: number } | null; if (!row) throw new Error('User not found'); this.db.query('UPDATE users SET score = ?1 WHERE id = ?2').run(row.score + delta, userId); }); tx.immediate(); } public runMaintenanceCheckpoint(): void { // Invariant: Passive checkpoint prevents blocking active readers while flushing dirty WAL pages const result = this.db.query('PRAGMA wal_checkpoint(PASSIVE);').get() as { busy: number; log: number; checkpointed: number; }; if (result && result.busy === 1) { console.warn('SQLite WAL checkpoint deferred: Reader currently holding lock.'); } } public close(): void { this.db.exec('PRAGMA wal_checkpoint(TRUNCATE);'); this.db.close(); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Bun 1.2 Native SQLite Concurrency, WAL Mode, and Checkpoint Isolation Standard

Bun 1.2 Native SQLite Concurrency, WAL Mode, and Checkpoint Isolation Standard defines an industrial-grade behavioral standard for backend engineering. Enforces deterministic SQLite concurrency settings on Bun 1.2 runtimes by mandating Write-Ahead Logging (WAL), busy timeout thresholds, immediate transaction semantics, and background checkpoint management. This prevents write-lock starvation (SQLITE_BUSY) and unbounded WAL journal bloat during concurrent edge operations. 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: Unconfigured Rollback Journals with Deferred Transaction Escalation and Unmonitored WAL 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	Unconfigured Rollback Journals with Deferred Transaction Escalation and Unmonitored WAL 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 execute `PRAGMA journal_mode = WAL

Engineers MUST ALWAYS execute `PRAGMA journal_mode = WAL;`, `PRAGMA busy_timeout = 5000;`, and `PRAGMA synchronous = NORMAL;` immediately upon opening a `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.