Bun 1.2 Native SQLite WAL Concurrent Writer Sharding Standard

--- description: Bun 1.2 Native SQLite WAL Concurrent Writer Sharding Standard - Enforces strict write-worker thread affinity, PRAGMA tuning, and deterministic checkpoint coordination for high-throughput Bun 1.2 native SQLite microservices. Prevents SQLITE_BUSY locking stalls and unconstrained write-ahead log explosion by partitioning writes across dedicated shard workers while allowing unblocked read-only replicas in concurrent WAL mode. globs: src/**/*.ts, src/db/**/*.ts, src/workers/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/bun-sqlite-wal-concurrent-writer-sharding-standard" --- # Bun 1.2 Native SQLite WAL Concurrent Writer Sharding Standard > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unpartitioned Concurrent Mutex Contention and Uncontrolled WAL Growth Under Distributed Node/Worker Writers ## System Constraints You are a Principal Backend Engineer enforcing Bun 1.2 Native SQLite WAL Concurrent Writer Sharding. SQLite supports unlimited concurrent readers in WAL mode, but allows exactly ONE active writer process/connection per physical database file at any given millisecond. You must enforce dedicated single-threaded write-worker affinity per database shard, explicit PRAGMA configurations (journal_mode=WAL, synchronous=NORMAL, busy_timeout=5000), and dedicated out-of-band checkpoint loops. Never permit direct write statements from transient HTTP handler threads or multi-threaded reader pools. ## Strict Guidelines - Services MUST configure every SQLite instance with 'PRAGMA journal_mode = WAL;' and 'PRAGMA synchronous = NORMAL;' immediately upon instantiation in bun:sqlite. - Engineers MUST set 'PRAGMA busy_timeout = 5000;' (or higher) to prevent immediate SQLITE_BUSY errors during microsecond locks. - Engineers MUST NOT execute mutating SQL statements (INSERT, UPDATE, DELETE, CREATE, DROP) from shared connection pools or concurrent HTTP request context handlers. - Services MUST isolate write operations to an actor or dedicated Worker thread bound to a specific database shard, routing write payloads through an in-memory queue or Bun Worker MessagePort. - Services MUST execute read-only queries against separate 'readonly: true' Database instances to eliminate lock escalation stalls. - Components MUST NEVER execute inline 'PRAGMA wal_checkpoint(TRUNCATE)' or 'REINDEX' inside customer-facing request/response cycles. - Background orchestrators MUST coordinate WAL maintenance via periodic 'PRAGMA wal_checkpoint(PASSIVE)' and bounded 'PRAGMA wal_checkpoint(RESTART)' during idle windows to constrain WAL file size. ## Bad vs Good Implementations ### Naive Direct Concurrent SQLite Writes vs Sharded Dedicated Writer Worker #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Direct writes across concurrent asynchronous calls without WAL mode and busy timeouts cause immediate SQLITE_BUSY exceptions. Invoking a blocking TRUNCATE checkpoint directly on the request path stalls all concurrent readers and writers. ```typescript import { Database } from 'bun:sqlite'; // Anti-pattern: Shared single connection directly handling HTTP writes and reads concurrently const db = new Database('data.db'); // Missing WAL pragma, busy_timeout, and readonly isolation export async function handleUserDeposit(userId: string, amount: number) { // High-throughput concurrent calls hit SQLITE_BUSY: database is locked const stmt = db.prepare('UPDATE accounts SET balance = balance + ? WHERE id = ?'); stmt.run(amount, userId); // Costly inline synchronous checkpoint on every write path destroys throughput db.run('PRAGMA wal_checkpoint(TRUNCATE);'); } ``` #### Production Standard (Correct) > **Standard**: Separates read-only execution from a single-threaded batching write engine. Configures critical WAL pragmas with non-zero busy timeouts and microtask batching to prevent lock contention while containing WAL size out of band. ```typescript import { Database } from 'bun:sqlite'; // Production Invariant: Distinct read-only handle for high-throughput reads const readerDb = new Database('data.db', { readonly: true }); readerDb.exec('PRAGMA query_only = ON;'); // Dedicated single-writer queue for shard-level serialize execution type WriteJob<T> = { sql: string; params: (string | number | boolean | null)[]; resolve: (value: T) => void; reject: (reason: unknown) => void; }; class ShardWriteEngine { private writerDb: Database; private queue: WriteJob<unknown>[] = []; private processing = false; constructor(filename: string) { this.writerDb = new Database(filename, { create: true, readwrite: true }); // Invariant: Mandatory PRAGMA setup for Bun 1.2 WAL performance this.writerDb.exec(` PRAGMA journal_mode = WAL; PRAGMA synchronous = NORMAL; PRAGMA busy_timeout = 5000; PRAGMA temp_store = MEMORY; `); } public executeWrite<T>(sql: string, params: (string | number | boolean | null)[]): Promise<T> { return new Promise<T>((resolve, reject) => { this.queue.push({ sql, params, resolve: resolve as (val: unknown) => void, reject }); this.drain(); }); } private drain() { if (this.processing || this.queue.length === 0) return; this.processing = true; queueMicrotask(() => { try { // Batch commit transactions in one transaction tick to maximize disk IOPS const transaction = this.writerDb.transaction((batch: WriteJob<unknown>[]) => { for (const job of batch) { try { const stmt = this.writerDb.prepare(job.sql); const result = stmt.run(...job.params); job.resolve(result); } catch (err) { job.reject(err); } } }); const jobsToProcess = this.queue.splice(0, 100); transaction(jobsToProcess); } finally { this.processing = false; if (this.queue.length > 0) this.drain(); } }); } // Coordinated checkpoint without stalling concurrent operations public passiveCheckpoint(): void { this.writerDb.exec('PRAGMA wal_checkpoint(PASSIVE);'); } } export const shardWriter = new ShardWriteEngine('data.db'); // Periodic out-of-band WAL containment setInterval(() => { shardWriter.passiveCheckpoint(); }, 30_000); ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Bun 1.2 Native SQLite WAL Concurrent Writer Sharding Standard

Bun 1.2 Native SQLite WAL Concurrent Writer Sharding Standard defines an industrial-grade behavioral standard for backend engineering. Enforces strict write-worker thread affinity, PRAGMA tuning, and deterministic checkpoint coordination for high-throughput Bun 1.2 native SQLite microservices. Prevents SQLITE_BUSY locking stalls and unconstrained write-ahead log explosion by partitioning writes across dedicated shard workers while allowing unblocked read-only replicas in concurrent WAL mode. 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 Concurrent Mutex Contention and Uncontrolled WAL Growth Under Distributed Node/Worker Writers 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 Concurrent Mutex Contention and Uncontrolled WAL Growth Under Distributed Node/Worker Writers	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:

Services MUST configure every SQLite instance with 'PRAGMA journal_mode = WAL

Services MUST configure every SQLite instance with 'PRAGMA journal_mode = WAL;' and 'PRAGMA synchronous = NORMAL;' immediately upon instantiation in bun:sqlite.

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.