Bun 1.2 Embedded SQLite WAL Concurrent Snapshot Barrier & Checkpoint Isolation

--- description: Bun 1.2 Embedded SQLite WAL Concurrent Snapshot Barrier & Checkpoint Isolation - Enforces strict snapshot barrier boundaries and deterministic WAL (Write-Ahead Logging) checkpoint synchronization across multi-threaded or isolated Bun worker processes sharing embedded SQLite storage. Prevents unbounded WAL frame expansion, SQLITE_BUSY lock contention, and read-lock checkpoint starvation by establishing explicit transaction scopes, optimal PRAGMAs, and barrier-isolated checkpoint scheduling. globs: src/**/*.ts, src/db/**/*.ts, workers/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/bun-sqlite-wal-concurrent-snapshot-barrier" --- # Bun 1.2 Embedded SQLite WAL Concurrent Snapshot Barrier & Checkpoint Isolation > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Uncoordinated Multi-Worker SQLite Access with Passive Checkpoint Starvation and Long-Lived Ambient Transactions ## System Constraints You are a Principal Software Architect enforcing Bun 1.2 Embedded SQLite concurrency standards. You must mandate strict transactional snapshot isolation, explicit WAL checkpoint coordinator boundaries, non-blocking busy timeouts, and synchronous NORMAL settings. Reject any patterns that introduce long-lived ambient read transactions or uncoordinated checkpoints across Bun workers. ## Strict Guidelines - Engineers MUST ALWAYS initialize every Bun SQLite connection with `PRAGMA journal_mode = WAL;`, `PRAGMA busy_timeout = 5000;`, and `PRAGMA synchronous = NORMAL;` immediately upon opening. - Engineers MUST NEVER execute uncoordinated `wal_checkpoint(TRUNCATE)` or `wal_checkpoint(RESTART)` from arbitrary worker instances; checkpoints MUST be driven by a single coordinator process or guarded by a shared snapshot barrier. - Code MUST wrap mutating database operations in explicit `db.transaction('immediate')` or `db.transaction('exclusive')` calls to prevent mid-flight snapshot lock upgrades from failing with SQLITE_BUSY. - Workers MUST NEVER hold read queries or active prepared statements open across async I/O awaits, network requests, or microtask delays, as active read marks prevent the WAL frame pointer from wrapping around. - Services MUST monitor WAL file size thresholds (e.g., `-wal` exceeding 4000 pages or 16MB) and yield reader transactions to allow the designated coordinator to drain checkpoints cleanly. ## Bad vs Good Implementations ### Concurrent Worker SQLite Initialization & Transaction Boundary #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Default journal mode causes lock contention. The absence of a busy timeout fails queries immediately with SQLITE_BUSY. Holding read states open across an async operation (`await Bun.sleep(10)`) anchors the SQLite WAL read mark, preventing the checkpoint from truncating and causing unbounded WAL growth. Executing `wal_checkpoint(TRUNCATE)` from a general worker starves peer threads. ```typescript import { Database } from 'bun:sqlite'; // ANTI-PATTERN: Default journal mode, missing busy timeouts, ambient reads over async boundaries export class WorkerDataStore { private db = new Database('shared_data.db'); async recordWorkerBatch(events: Array<{ id: string; payload: string }>) { // Implicit deferred transaction allows race conditions during lock elevation for (const event of events) { // Read held open across async operations causes reader starvation in WAL const exists = this.db.query('SELECT 1 FROM events WHERE id = ?').get(event.id); await Bun.sleep(10); // Dangerous: reader lock held while yielding to event loop if (!exists) { this.db.query('INSERT INTO events (id, payload) VALUES (?, ?)').run(event.id, event.payload); } } // Uncoordinated checkpoint in worker thread starves other active readers this.db.exec('PRAGMA wal_checkpoint(TRUNCATE);'); } } ``` #### Production Standard (Correct) > **Standard**: Configures WAL mode, a 5000ms busy timeout, and `synchronous = NORMAL` to prevent I/O blocking. Wraps mutations inside `transaction.immediate` to serialize writers predictably without lock upgrade errors. Keeps read and write executions entirely synchronous to avoid holding snapshots over async yield points. Restricts checkpoints to a designated coordinator using `wal_checkpoint(PASSIVE)` to eliminate reader starvation. ```typescript import { Database } from 'bun:sqlite'; export interface DatabaseConfig { filePath: string; busyTimeoutMs?: number; isCoordinator?: boolean; } export class WorkerDataStore { private db: Database; private isCoordinator: boolean; constructor(config: DatabaseConfig) { this.db = new Database(config.filePath, { create: true }); this.isCoordinator = config.isCoordinator ?? false; // CRITICAL PRAGMAS: Mandate WAL, resilient busy timeout, and non-blocking disk sync this.db.exec(` PRAGMA journal_mode = WAL; PRAGMA busy_timeout = ${config.busyTimeoutMs ?? 5000}; PRAGMA synchronous = NORMAL; PRAGMA foreign_keys = ON; `); } public recordWorkerBatch(events: Array<{ id: string; payload: string }>): void { // Immediate transaction guarantees immediate acquisition of write lock, preventing deadlocks const batchInsert = this.db.transaction((items: Array<{ id: string; payload: string }>) => { const insertStmt = this.db.prepare('INSERT OR IGNORE INTO events (id, payload) VALUES (?, ?)'); for (const item of items) { insertStmt.run(item.id, item.payload); } }); // Synchronous execution: NEVER yield to async event loop inside active read/write frames batchInsert.immediate(events); } public executeCheckpointBarrier(): { busy: boolean; log: number; checkpointed: number } { if (!this.isCoordinator) { throw new Error('Only the designated barrier coordinator process may trigger checkpoints.'); } // PASSIVE checkpoint barrier will not block active readers and reports backfill progress const result = this.db.query('PRAGMA wal_checkpoint(PASSIVE);').get() as { busy: number; log: number; checkpointed: number; }; return { busy: result.busy === 1, log: result.log, checkpointed: result.checkpointed, }; } public close(): void { this.db.close(); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Bun 1.2 Embedded SQLite WAL Concurrent Snapshot Barrier & Checkpoint Isolation

Bun 1.2 Embedded SQLite WAL Concurrent Snapshot Barrier & Checkpoint Isolation defines an industrial-grade behavioral standard for backend engineering. Enforces strict snapshot barrier boundaries and deterministic WAL (Write-Ahead Logging) checkpoint synchronization across multi-threaded or isolated Bun worker processes sharing embedded SQLite storage. Prevents unbounded WAL frame expansion, SQLITE_BUSY lock contention, and read-lock checkpoint starvation by establishing explicit transaction scopes, optimal PRAGMAs, and barrier-isolated checkpoint scheduling. 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-Worker SQLite Access with Passive Checkpoint Starvation and Long-Lived Ambient Transactions 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-Worker SQLite Access with Passive Checkpoint Starvation and Long-Lived Ambient Transactions	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 every Bun SQLite connection with `PRAGMA journal_mode = WAL

Engineers MUST ALWAYS initialize every Bun SQLite connection with `PRAGMA journal_mode = WAL;`, `PRAGMA busy_timeout = 5000;`, and `PRAGMA synchronous = NORMAL;` immediately upon opening.

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.