Bun 1.2 Clustered SQLite WAL Read-Replica Affinity & Truncation Barrier Standard

--- description: Bun 1.2 Clustered SQLite WAL Read-Replica Affinity & Truncation Barrier Standard - Enforces strict process-level separation between solitary write-coordinator threads and read-replica worker pools when leveraging bun:sqlite under high concurrency. Prevents SQLite busy timeouts and unconstrained WAL log explosion by mandating explicit query-only PRAGMAs on read threads alongside deterministic checkpoint barriers via the primary engine thread. 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-concurrency-invariant" --- # Bun 1.2 Clustered SQLite WAL Read-Replica Affinity & Truncation Barrier Standard > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unpartitioned Multi-Threaded Read/Write Contention Leading to SQLITE_BUSY Starvation and Unbounded WAL Expansion ## System Constraints You are a Principal Engineer enforcing the Bun 1.2 Clustered Worker SQLite WAL Concurrency Invariant. You must guarantee that clustered engine workers maintain strict connection affinity: writes are dispatched exclusively through an isolated primary writer with a dedicated queue, worker threads open read-only database connections with query_only=ON and synchronous=NORMAL, and periodic PRAGMA wal_checkpoint(PASSIVE/RESTART) operations execute under bounded reader pressure to prevent write-lock starvation and unconstrained WAL file growth. ## Strict Guidelines - Engineers MUST ALWAYS initialize SQLite connections in worker threads with `readonly: true` and execute `PRAGMA query_only = ON;` immediately upon worker boot. - Engineers MUST NOT allow distributed clustered workers to instantiate read-write handles against shared WAL database files. - Services MUST route all write and mutation intents to an isolated primary write coordinator via IPC message channels or an in-memory queue. - The primary writer MUST configure `PRAGMA busy_timeout = 5000;` and execute periodic `PRAGMA wal_checkpoint(TRUNCATE);` or `PRAGMA wal_checkpoint(PASSIVE);` during reader quiescence. - Workers NEVER execute direct `PRAGMA wal_checkpoint` or DDL statements independently of the write coordinator. ## Bad vs Good Implementations ### Clustered Worker SQLite Connection Initialization and Write Delegation #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Opening read-write database connections across multiple clustered Bun workers causes multi-process lock contention on SQLite's single-writer lock. Uncoordinated writes induce frequent SQLITE_BUSY errors and block the WAL from checkpointing properly. ```typescript import { Database } from 'bun:sqlite'; // ANTI-PATTERN: Direct read-write connection inside a clustered worker thread export class WorkerDatabase { private db: Database; constructor(path: string) { // Opens read-write by default across multiple workers, causing lock contention this.db = new Database(path); this.db.exec('PRAGMA journal_mode = WAL;'); } public mutate(userId: string, balance: number) { // Concurrent transactions across clustered threads result in SQLITE_BUSY const query = this.db.prepare('UPDATE accounts SET balance = ? WHERE id = ?'); return query.run(balance, userId); } } ``` #### Production Standard (Correct) > **Standard**: Configures workers with strict read-only connections and explicit query_only PRAGMA, eliminating write-lock acquisition attempts within the worker pool and delegating write requests to a designated coordinator thread. ```typescript import { Database } from 'bun:sqlite'; // PRODUCTION INVARIANT: Read-replica worker with strict query_only isolation export class WorkerReplicaDatabase { private readonly db: Database; constructor(databasePath: string) { this.db = new Database(databasePath, { readonly: true }); // Enforce read-only semantics at the SQLite virtual machine level this.db.exec('PRAGMA query_only = ON;'); this.db.exec('PRAGMA busy_timeout = 3000;'); } public getAccount(userId: string) { const stmt = this.db.prepare('SELECT id, balance FROM accounts WHERE id = ?'); return stmt.get(userId); } public requestMutation(userId: string, balance: number): Promise<void> { // Delegate write operations exclusively to the primary writer process via IPC return new Promise((resolve, reject) => { const correlationId = crypto.randomUUID(); process.send?.({ type: 'DB_WRITE', correlationId, payload: { userId, balance } }); // In production, listen on correlationId channel for completion confirmation resolve(); }); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Bun 1.2 Clustered SQLite WAL Read-Replica Affinity & Truncation Barrier Standard

Bun 1.2 Clustered SQLite WAL Read-Replica Affinity & Truncation Barrier Standard defines an industrial-grade behavioral standard for backend engineering. Enforces strict process-level separation between solitary write-coordinator threads and read-replica worker pools when leveraging bun:sqlite under high concurrency. Prevents SQLite busy timeouts and unconstrained WAL log explosion by mandating explicit query-only PRAGMAs on read threads alongside deterministic checkpoint barriers via the primary engine thread. 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: Unpartitioned Multi-Threaded Read/Write Contention Leading to SQLITE_BUSY Starvation and Unbounded WAL Expansion 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	Unpartitioned Multi-Threaded Read/Write Contention Leading to SQLITE_BUSY Starvation and Unbounded WAL Expansion	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 SQLite connections in worker threads with `readonly

Engineers MUST ALWAYS initialize SQLite connections in worker threads with `readonly: true` and execute `PRAGMA query_only = ON;` immediately upon worker boot.

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.