Bun 1.2 Worker Thread Memory-Mapped SQLite WAL Synchronization & Checkpoint Guardrail

--- description: Bun 1.2 Worker Thread Memory-Mapped SQLite WAL Synchronization & Checkpoint Guardrail - Enforces strict isolation, shared-memory synchronization (PRAGMA mmap_size, PRAGMA wal_autocheckpoint), and dedicated checkpoint orchestration across Bun 1.2 multi-threaded Worker pools. Prevents lock starvation (SQLITE_BUSY), dirty page read skew, and index corruption caused by uncoordinated concurrent checkpoints in memory-mapped SQLite engines. globs: src/**/*.worker.ts, src/**/db.ts, src/**/database.ts, src/**/workers/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/bun-worker-sqlite-wal-mmap-sync" --- # Bun 1.2 Worker Thread Memory-Mapped SQLite WAL Synchronization & Checkpoint Guardrail > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Uncoordinated Multi-Worker Auto-Checkpointing with Unbounded Memory-Mapped I/O ## System Constraints You are a Principal Backend Systems Engineer specializing in high-throughput transactional storage engines and Bun 1.2 runtime internals. You must enforce memory safety, non-blocking lock coordination, and deterministic checkpoint lifecycles across Bun Worker threads utilizing bun:sqlite. Under no circumstances may worker threads perform autonomous WAL checkpoints or execute unbounded memory mapping that corrupts file descriptors across thread boundaries. ## Strict Guidelines - Engineers MUST ALWAYS disable automatic checkpoints (`PRAGMA wal_autocheckpoint = 0;`) on connection pools instantiated inside Bun Worker threads to prevent SQLite checkpoint lock contention. - Engineers MUST ALWAYS isolate checkpoint operations (`PRAGMA wal_checkpoint(PASSIVE)` or `wal_checkpoint(TRUNCATE)`) to a single, dedicated coordinator worker or primary event-loop thread. - Engineers MUST ALWAYS configure `PRAGMA busy_timeout` to an explicit, non-zero bound (recommended >= 5000ms) with exponential backoff before opening transactions in worker threads. - Engineers MUST NOT share raw `Database` instances across Bun Worker threads; each worker MUST instantiate its own connection using identical `PRAGMA synchronous = NORMAL;` and bounded `PRAGMA mmap_size` configurations. - Worker connection lifecycles MUST explicitly close statements and execute connection termination hooks (`db.close()`) upon receiving termination signals or worker thread exit. - Write transactions inside workers MUST use `BEGIN IMMEDIATE` or `BEGIN EXCLUSIVE` to preempt lock escalation failures and eliminate deadlocks midway through execution pipelines. ## Bad vs Good Implementations ### Worker SQLite WAL Initialization and Isolated Checkpoint Orchestration #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Each worker thread inherits default autocheckpoint behavior. Multiple workers concurrently writing will trigger internal checkpoints unpredictably, causing lock starvation (SQLITE_BUSY), unbounded memory mapping thrashing without mmap_size caps, and deadlocks from deferred transactions. ```typescript import { Database } from 'bun:sqlite'; // Anti-pattern: Default WAL autocheckpoint enabled across arbitrary workers, // infinite or unmanaged mmap_size, missing busy timeout, and non-immediate transactions. const db = new Database('production.db'); db.run('PRAGMA journal_mode = WAL;'); // Dangerous: Default wal_autocheckpoint triggers inside any concurrent worker, // causing SQLITE_BUSY and thread stalls during high-concurrency writes. export function executeWorkerTransaction(data: { id: string; payload: string }) { db.run('BEGIN TRANSACTION;'); // Prone to SQLITE_BUSY deadlock on upgrade try { db.prepare('INSERT INTO audit_log (id, payload) VALUES (?, ?)').run(data.id, data.payload); db.run('COMMIT;'); } catch (error) { db.run('ROLLBACK;'); throw error; } } ``` #### Production Standard (Correct) > **Standard**: Explicitly bounds memory mapping via mmap_size, disables wal_autocheckpoint inside standard worker threads to isolate checkpoint I/O, assigns an explicit busy_timeout, and uses immediate transactions to guarantee deterministic lock acquisition order. ```typescript import { Database } from 'bun:sqlite'; export interface WorkerDbConfig { isCoordinator?: boolean; mmapSizeBytes?: number; busyTimeoutMs?: number; } export function initializeWorkerDatabase(config: WorkerDbConfig = {}): Database { const { isCoordinator = false, mmapSizeBytes = 268435456, busyTimeoutMs = 5000 } = config; const db = new Database('production.db', { strict: true }); // Deterministic engine invariants db.run('PRAGMA journal_mode = WAL;'); db.run('PRAGMA synchronous = NORMAL;'); db.run(`PRAGMA busy_timeout = ${busyTimeoutMs};`); db.run(`PRAGMA mmap_size = ${mmapSizeBytes};`); if (!isCoordinator) { // Disable autonomous checkpoints on processing workers to prevent thread stalls db.run('PRAGMA wal_autocheckpoint = 0;'); } return db; } export function executeWorkerTransaction(db: Database, data: { id: string; payload: string }): void { const insertStmt = db.prepare('INSERT INTO audit_log (id, payload) VALUES (?1, ?2)'); // Atomic immediate acquisition eliminates lock escalation deadlocks db.transaction(() => { insertStmt.run(data.id, data.payload); }).immediate(); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Bun 1.2 Worker Thread Memory-Mapped SQLite WAL Synchronization & Checkpoint Guardrail

Bun 1.2 Worker Thread Memory-Mapped SQLite WAL Synchronization & Checkpoint Guardrail defines an industrial-grade behavioral standard for backend engineering. Enforces strict isolation, shared-memory synchronization (PRAGMA mmap_size, PRAGMA wal_autocheckpoint), and dedicated checkpoint orchestration across Bun 1.2 multi-threaded Worker pools. Prevents lock starvation (SQLITE_BUSY), dirty page read skew, and index corruption caused by uncoordinated concurrent checkpoints in memory-mapped SQLite engines. 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 Auto-Checkpointing with Unbounded Memory-Mapped I/O 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 Auto-Checkpointing with Unbounded Memory-Mapped I/O	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 disable automatic checkpoints (`PRAGMA wal_autocheckpoint = 0

Engineers MUST ALWAYS disable automatic checkpoints (`PRAGMA wal_autocheckpoint = 0;`) on connection pools instantiated inside Bun Worker threads to prevent SQLite checkpoint lock contention.

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.