Bun 1.2 Multi-Worker SQLite WAL2 Concurrency and Deterministic Checkpoint Coordination

--- description: Bun 1.2 Multi-Worker SQLite WAL2 Concurrency and Deterministic Checkpoint Coordination - Enforces deterministic checkpoint coordination and transaction boundaries across native Bun background workers using SQLite WAL2 mode. Prevents read-lock starvation, checkpoint livelocks, and uncommitted page corruption caused by uncoordinated PRAGMA wal_checkpoint execution across isolated thread runtimes. globs: src/**/*.ts, workers/**/*.ts, lib/db/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/bun-sqlite-wal2-worker-checkpoint-coordination" --- # Bun 1.2 Multi-Worker SQLite WAL2 Concurrency and Deterministic Checkpoint Coordination > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Uncoordinated Multi-Threaded Checkpointing and Concurrent Unsafe Write Allocations Under WAL2 ## System Constraints You are a Principal Systems Architect specializing in high-concurrency runtime environments and embedded databases. Enforce strict concurrency invariants for Bun 1.2 multi-worker architectures utilizing bun:sqlite under WAL2 journal mode. Mandate single-writer or message-coordinated write leases, passive checkpoint scheduling, read-only worker isolation, and strict PRAGMA configurations to eliminate lock contention, uncommitted page corruption, and WAL growth explosions. ## Strict Guidelines - Engineers MUST ALWAYS isolate SQLite write handles and checkpointing triggers to a single designated coordinator worker or orchestrator thread. - Engineers MUST NOT instantiate multi-worker write connections without setting PRAGMA busy_timeout to a deterministic threshold (minimum 5000ms). - Workers performing purely analytical or read tasks MUST ALWAYS initialize Bun Database instances with { readonly: true }. - Services MUST NEVER invoke blocking PRAGMA wal_checkpoint(TRUNCATE) or PRAGMA wal_checkpoint(RESTART) concurrently from generic worker threads. - Checkpoints SHOULD ALWAYS be executed using non-blocking PRAGMA wal_checkpoint(PASSIVE) via a dedicated coordinator, scheduled strictly when active read transactions clear. - Transactions modifying data across workers MUST ALWAYS begin with db.transaction(() => ...).immediate() or BEGIN IMMEDIATE to avoid intermediate deadlocks during lock escalation under WAL2. - Components NEVER share mutable bun:sqlite Database handles across Worker boundaries; inter-worker communication must proceed via structured message passing or SharedArrayBuffer synchronization primitives. ## Bad vs Good Implementations ### Uncoordinated Multi-Worker Writes and Blocking WAL Checkpoints #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Each worker thread opens an independent writable handle with WAL2, starts a deferred transaction that risks SQLite lock escalation deadlocks, and invokes a blocking PRAGMA wal_checkpoint(TRUNCATE). This stalls active reader workers, leads to SQLITE_BUSY storms, and risks checkpoint livelock under sustained concurrent load. ```typescript import { Database } from 'bun:sqlite'; // ANTI-PATTERN: Spawning uncoordinated writable DB instances across multi-threaded workers // Each worker attempts blocking checkpoints and default deferred transactions. export function handleWorkerTask(dbPath: string, payload: unknown) { const db = new Database(dbPath); db.run('PRAGMA journal_mode = WAL2;'); // Unsafe concurrent deferred transaction allows lock escalation deadlock const insert = db.prepare('INSERT INTO telemetry (data) VALUES (?)'); insert.run(JSON.stringify(payload)); // Catastrophic: Worker arbitrarily issues blocking TRUNCATE checkpoint, // starving concurrent worker readers and causing SQLITE_BUSY / lock corruption db.run('PRAGMA wal_checkpoint(TRUNCATE);'); db.close(); } ``` #### Production Standard (Correct) > **Standard**: Worker connections enforce read-only access by default unless explicitly designated as the writer coordinator. Pragmas explicitly set busy_timeout and synchronous=NORMAL. Writes utilize immediate transactions to prevent lock escalation failures. Checkpoints are non-blocking (PASSIVE) and isolated from reader execution paths. ```typescript import { Database } from 'bun:sqlite'; // PRODUCTION FIX: Strict separation of read workers, immediate transactions, // and designated passive checkpoint coordination. export interface WorkerDbConfig { dbPath: string; isWriterCoordinator?: boolean; } export class WorkerDatabaseManager { private db: Database; constructor(config: WorkerDbConfig) { this.db = new Database(config.dbPath, { readonly: !config.isWriterCoordinator, create: config.isWriterCoordinator ?? false, }); // Invariant: Mandatory busy timeout and WAL2 pragmas this.db.run('PRAGMA busy_timeout = 5000;'); if (config.isWriterCoordinator) { this.db.run('PRAGMA journal_mode = WAL2;'); this.db.run('PRAGMA synchronous = NORMAL;'); } } // Strictly controlled write transaction with immediate lock reservation public writeRecord(table: string, payload: Record<string, unknown>): void { const stmt = this.db.prepare(`INSERT INTO ${table} (data) VALUES (?)`); // Force immediate lock to eliminate SQLITE_BUSY escalation midway const runImmediateTx = this.db.transaction((dataStr: string) => { stmt.run(dataStr); }).immediate; runImmediateTx(JSON.stringify(payload)); } // Only executed by designated coordinator thread during idle cycles public coordinatePassiveCheckpoint(): { busy: number; log: number; checkpointed: number } { const row = this.db.query('PRAGMA wal_checkpoint(PASSIVE);').get() as { busy: number; log: number; checkpointed: number; }; return row; } public close(): void { this.db.close(); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Bun 1.2 Multi-Worker SQLite WAL2 Concurrency and Deterministic Checkpoint Coordination

Bun 1.2 Multi-Worker SQLite WAL2 Concurrency and Deterministic Checkpoint Coordination defines an industrial-grade behavioral standard for backend engineering. Enforces deterministic checkpoint coordination and transaction boundaries across native Bun background workers using SQLite WAL2 mode. Prevents read-lock starvation, checkpoint livelocks, and uncommitted page corruption caused by uncoordinated PRAGMA wal_checkpoint execution across isolated thread runtimes. 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-Threaded Checkpointing and Concurrent Unsafe Write Allocations Under WAL2 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-Threaded Checkpointing and Concurrent Unsafe Write Allocations Under WAL2	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 isolate SQLite write handles and checkpointing triggers to a single designated coordinator worker or orchestrator thread.

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.