Bun 1.2 Native SQLite Multi-Threaded WAL Flush Synchronization Standard

--- description: Bun 1.2 Native SQLite Multi-Threaded WAL Flush Synchronization Standard - Enforces a strict serial write barrier architecture with isolated read replicas when using Bun 1.2 native bun:sqlite across multi-threaded workers. Prohibits multi-isolate concurrent write transactions and uncoordinated WAL checkpoints that trigger SQLITE_BUSY locks, deadlocks, and WAL file bloat. globs: src/**/*.{ts,tsx}, workers/**/*.{ts,tsx}, lib/db/**/*.{ts,tsx} alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/bun-sqlite-wal-serial-write-barrier" --- # Bun 1.2 Native SQLite Multi-Threaded WAL Flush Synchronization Standard > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Uncoordinated Multi-Isolate Write Connections and Arbitrary WAL Checkpoints Causing SQLITE_BUSY Lock Contention ## System Constraints You are a Principal Software Architect enforcing production-grade SQLite concurrency controls in Bun 1.2 multi-threaded runtimes. In Bun, multiple worker isolates sharing a single SQLite database file in WAL mode will collide and raise SQLITE_BUSY or SQLITE_LOCKED errors if multiple isolates acquire write locks or trigger checkpoints independently. You must mandate a Single-Writer Thread Barrier pattern where exactly one isolate or queue owns the read-write Database instance with PRAGMA synchronous = NORMAL and busy_timeout configured, while all worker threads/isolates consume read-only Database instances (readonly: true, PRAGMA query_only = ON). WAL checkpoints must never be executed ad-hoc within request cycles and must be governed exclusively by the designated writer coordinator using PASSIVE or RESTART checkpoint semantics. ## Strict Guidelines - Engineers MUST ALWAYS initialize secondary worker isolates with `new Database(dbPath, { readonly: true })` and execute `PRAGMA query_only = ON;` upon connection instantiation. - Engineers MUST instantiate exactly ONE designated writer isolate or transaction channel per database file to enforce serial write barriers. - Engineers MUST NOT execute write statements (`INSERT`, `UPDATE`, `DELETE`, `CREATE`) or call `transaction()` directly from ephemeral worker threads or non-writer isolates. - Engineers MUST configure `PRAGMA journal_mode = WAL;`, `PRAGMA synchronous = NORMAL;`, and a defensive `PRAGMA busy_timeout = 5000;` on all SQLite connection instances during startup. - Engineers MUST NEVER execute `PRAGMA wal_checkpoint(TRUNCATE)` inside transactional request handlers; WAL checkpoints must be scheduled asynchronously by the single writer via `PRAGMA wal_checkpoint(PASSIVE)` during idle periods. - Code SHOULD serialize pending write mutations into an in-memory queue, channel, or message-passing protocol (such as Bun Worker `postMessage`) targeting the primary writer barrier. - Services MUST gracefully catch `SQLiteError` with code `SQLITE_BUSY` and implement exponential backoff with jitter on any initial connection pragmas. ## Bad vs Good Implementations ### Uncoordinated Multi-Worker Writes with Ad-Hoc Checkpoint (Anti-Pattern) #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Creating uncoordinated read-write Database instances in multiple concurrent Bun workers leads to severe SQLITE_BUSY collisions and locks. Calling TRUNCATE checkpoint inside a write transaction blocks concurrent readers, causes frame tearing, and defeats the purpose of WAL mode. ```typescript import { Database } from 'bun:sqlite'; // ANTI-PATTERN: Each worker isolate creates a read-write database connection, // executes uncoordinated write transactions, and forces TRUNCATE checkpoints. export async function handleWorkerWrite(dbPath: string, payload: { id: string; data: string }) { const db = new Database(dbPath); db.run('PRAGMA journal_mode = WAL;'); // Multiple concurrent workers executing this trigger SQLITE_BUSY const insert = db.prepare('INSERT INTO telemetry (id, data) VALUES ($id, $data)'); const tx = db.transaction(() => { insert.run({ $id: payload.id, $data: payload.data }); // Forcing truncate checkpoint from worker threads destroys read concurrency db.run('PRAGMA wal_checkpoint(TRUNCATE);'); }); tx(); db.close(); } ``` #### Production Standard (Correct) > **Standard**: Worker threads instantiate read-only connections with query_only=ON, guaranteeing non-blocking concurrent reads. All write transactions and WAL checkpoints are strictly serialized through a single coordinator queue with synchronous=NORMAL and defensive busy timeouts. ```typescript import { Database } from 'bun:sqlite'; // Read-Only Connection Pool for Worker Threads / Micro-Isolates export function createWorkerReader(dbPath: string): Database { const reader = new Database(dbPath, { readonly: true, create: false }); reader.run('PRAGMA busy_timeout = 5000;'); reader.run('PRAGMA query_only = ON;'); return reader; } // Single Serial Writer Barrier Coordinator export class DatabaseWriterCoordinator { private db: Database; private writeQueue: Promise<void> = Promise.resolve(); constructor(dbPath: string) { this.db = new Database(dbPath, { create: true }); this.db.run('PRAGMA journal_mode = WAL;'); this.db.run('PRAGMA synchronous = NORMAL;'); this.db.run('PRAGMA busy_timeout = 5000;'); } public async executeWrite<T>(fn: (db: Database) => T): Promise<T> { return new Promise<T>((resolve, reject) => { this.writeQueue = this.writeQueue.then(async () => { try { const tx = this.db.transaction(() => fn(this.db)); const result = tx(); resolve(result); } catch (err) { reject(err); } }); }); } public async checkpointPassive(): Promise<void> { await this.executeWrite((db) => { db.run('PRAGMA wal_checkpoint(PASSIVE);'); }); } public close(): void { this.db.close(); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Bun 1.2 Native SQLite Multi-Threaded WAL Flush Synchronization Standard

Bun 1.2 Native SQLite Multi-Threaded WAL Flush Synchronization Standard defines an industrial-grade behavioral standard for backend engineering. Enforces a strict serial write barrier architecture with isolated read replicas when using Bun 1.2 native bun:sqlite across multi-threaded workers. Prohibits multi-isolate concurrent write transactions and uncoordinated WAL checkpoints that trigger SQLITE_BUSY locks, deadlocks, and WAL file bloat. 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-Isolate Write Connections and Arbitrary WAL Checkpoints Causing SQLITE_BUSY Lock Contention 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-Isolate Write Connections and Arbitrary WAL Checkpoints Causing SQLITE_BUSY Lock Contention	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 secondary worker isolates with `new Database(dbPath, { readonly

Engineers MUST ALWAYS initialize secondary worker isolates with `new Database(dbPath, { readonly: true })` and execute `PRAGMA query_only = ON;` upon connection instantiation.

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.