Bun 1.2 Native SQLite WAL2 Multi-Worker Checkpoint Concurrency Standard

--- description: Bun 1.2 Native SQLite WAL2 Multi-Worker Checkpoint Concurrency Standard - Enforces deterministic multi-threaded SQLite access in Bun 1.2 using dual-log WAL2 mode with isolated worker connection pools. Prevents read-lock starvation and WAL file bloat during microsecond-latency event-sourcing ingest by isolating checkpoint operations to an autonomous maintenance worker. 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-wal2-checkpoint-concurrency-standard" --- # Bun 1.2 Native SQLite WAL2 Multi-Worker Checkpoint Concurrency Standard > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Inline Checkpointing and Standard WAL Reader Starvation in Shared SQLite Worker Threads ## System Constraints You are a Principal Backend Architect enforcing Bun 1.2 Native SQLite WAL2 Multi-Worker Checkpoint Concurrency Standard. You must reject any SQLite implementation in Bun that executes ad-hoc checkpoints on hot ingest pathways, shares a single `Database` instance across Bun worker boundaries, or omits strict WAL2 pragma configurations with resilient busy timeouts. ## Strict Guidelines - Engineers MUST ALWAYS configure `PRAGMA journal_mode = WAL2;` at database initialization before dispatching concurrent worker threads. - Engineers MUST ALWAYS configure `PRAGMA busy_timeout = 5000;` and `PRAGMA synchronous = NORMAL;` on every worker connection instance. - Engineers MUST isolate `bun:sqlite` `Database` instances to their respective thread or `Worker`; passing open Database instances across thread boundaries is strictly forbidden. - Engineers MUST NOT execute inline checkpoints (`PRAGMA wal_checkpoint`) inside transaction loops, event ingestion handlers, or read pipelines. - Services MUST run a dedicated checkpoint coordinator thread or worker that invokes `PRAGMA wal_checkpoint(PASSIVE)` or `wal_checkpoint(RESTART)` deterministically based on frame thresholds. - Workers MUST finalize prepared statements or wrap operations in deterministic scopes to avoid leaving open read transactions that impede WAL log switching. ## Bad vs Good Implementations ### Non-deterministic Checkpointing and Shared Connection Anti-Pattern #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Using standard WAL mode with inline `FULL` checkpoints blocks concurrent readers during ingest. In high-throughput workloads, long-running readers starve the checkpoint, leading to unbounded `-wal` file growth and query latency degradation. ```typescript import { Database } from "bun:sqlite"; // ANTI-PATTERN: Standard WAL mode with uncoordinated inline checkpoints and shared connection export const db = new Database("events.db"); db.run("PRAGMA journal_mode = WAL;"); export async function ingestEvent(id: string, payload: string) { const insert = db.prepare("INSERT INTO events (id, payload) VALUES (?, ?)"); insert.run(id, payload); // DANGEROUS: Inline checkpoint on hot path locks readers and causes catastrophic stalls const frameCount = db.query("PRAGMA wal_checkpoint(FULL)").get(); return frameCount; } ``` #### Production Standard (Correct) > **Standard**: Configures dual-log WAL2 mode allowing seamless checkpointing across two files without locking microsecond writes or starving reads. Database connections are worker-isolated with explicit busy timeouts, and checkpoints execute exclusively on a decoupled timer. ```typescript import { Database } from "bun:sqlite"; // Factory for worker-isolated connections with strict WAL2 invariants export function createWorkerConnection(dbPath: string): Database { const db = new Database(dbPath, { create: true }); db.run("PRAGMA journal_mode = WAL2;"); db.run("PRAGMA synchronous = NORMAL;"); db.run("PRAGMA busy_timeout = 5000;"); db.run("PRAGMA cache_size = -64000;"); // 64MB memory cache return db; } // Isolated ingest pipeline without inline checkpoints export class EventIngestor { private db: Database; private insertStmt: ReturnType<Database["prepare"]>; constructor(dbPath: string) { this.db = createWorkerConnection(dbPath); this.insertStmt = this.db.prepare("INSERT INTO events (id, payload, created_at) VALUES (?, ?, ?)"); } public ingest(id: string, payload: string): void { this.insertStmt.run(id, payload, Date.now()); } public close(): void { this.insertStmt.finalize(); this.db.close(); } } // Dedicated checkpoint coordinator worker loop export function runCheckpointCoordinator(dbPath: string, intervalMs: number = 1000): void { const coordDb = createWorkerConnection(dbPath); setInterval(() => { try { // WAL2 allows writer-checkpoint alternation using RESTART without starving active readers coordDb.run("PRAGMA wal_checkpoint(RESTART);"); } catch (err) { console.error("Non-blocking checkpoint pass deferred:", err); } }, intervalMs); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Bun 1.2 Native SQLite WAL2 Multi-Worker Checkpoint Concurrency Standard

Bun 1.2 Native SQLite WAL2 Multi-Worker Checkpoint Concurrency Standard defines an industrial-grade behavioral standard for backend engineering. Enforces deterministic multi-threaded SQLite access in Bun 1.2 using dual-log WAL2 mode with isolated worker connection pools. Prevents read-lock starvation and WAL file bloat during microsecond-latency event-sourcing ingest by isolating checkpoint operations to an autonomous maintenance worker. 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: Inline Checkpointing and Standard WAL Reader Starvation in Shared SQLite Worker Threads 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	Inline Checkpointing and Standard WAL Reader Starvation in Shared SQLite Worker Threads	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 configure `PRAGMA journal_mode = WAL2

Engineers MUST ALWAYS configure `PRAGMA journal_mode = WAL2;` at database initialization before dispatching concurrent worker threads.

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.