Bun 1.2 Multi-Worker SQLite Handle Isolation and WAL Write-Lease Protocol

--- description: Bun 1.2 Multi-Worker SQLite Handle Isolation and WAL Write-Lease Protocol - Enforces thread-isolated native SQLite connection handles across Bun Web Workers and mandates deterministic WAL write-lease arbitration. Prevents native pointer corruption, segfaults from cross-thread handle transfers, and unrecoverable SQLITE_BUSY deadlocks during concurrent multi-worker writes. globs: src/**/*.worker.ts, src/**/db.ts, src/**/database.ts, src/workers/**/*.ts, src/**/worker-*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/bun-sqlite-multi-worker-concurrency-wal-lease" --- # Bun 1.2 Multi-Worker SQLite Handle Isolation and WAL Write-Lease Protocol > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Sharing Native bun:sqlite Instances Across Workers or Executing Uncoordinated Concurrent Transactions ## System Constraints You are a Principal Backend Architect enforcing strict database concurrency standards for Bun 1.2. SQLite native handles (Database instances) wrap C-level pointers tied to OS threads; they are not thread-safe and must never be shared across worker boundaries. Furthermore, SQLite in WAL mode allows multiple concurrent readers but strictly one writer at any moment. You must mandate that every worker manages its own isolated connection with WAL mode and explicit busy timeouts, and that all write operations use serialized actor messaging or BEGIN IMMEDIATE with defensive retry backoff. ## Strict Guidelines - Engineers MUST NEVER attempt to pass a native `bun:sqlite` `Database` instance across threads via `postMessage()`, `SharedArrayBuffer`, or global variables. - Each Bun `Worker` needing database access MUST open its own independent `Database` handle to the SQLite file. - Every SQLite handle MUST execute `PRAGMA journal_mode = WAL;` and `PRAGMA busy_timeout = 5000;` immediately upon connection initialization. - Write operations MUST use `BEGIN IMMEDIATE` instead of standard `BEGIN` or `BEGIN DEFERRED` to guarantee write lock acquisition at transaction start rather than upon first write. - High-concurrency systems MUST route write operations through a dedicated Single-Writer Worker (Actor Pattern), leaving reader workers strictly read-only (`PRAGMA query_only = ON;`). - Workers MUST handle `SQLITE_BUSY` exceptions explicitly with exponential jitter backoff if concurrent multi-process writes cannot be avoided. ## Bad vs Good Implementations ### Violating SQLite Thread Safety by Sharing Handles and Lacking WAL Configuration #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Bun SQLite handles are C-level native structs bound to the thread of creation. Passing them to a Worker throws a DataCloneError or crashes the runtime with memory corruption. Additionally, running standard BEGIN without WAL configuration or busy timeouts guarantees SQLITE_BUSY errors when multiple workers attempt writes simultaneously. ```typescript import { Database } from 'bun:sqlite'; // ANTI-PATTERN: Instantiating db in the main thread and attempting to pass it to a worker, // or assuming default journal mode handles multi-threaded contention. const db = new Database('app.db'); const worker = new Worker(new URL('./task.worker.ts', import.meta.url)); // Fails: Native Database handle cannot be serialized over postMessage structured clone worker.postMessage({ type: 'EXECUTE', db }); // ANTI-PATTERN inside a worker: Default deferred transaction causes SQLITE_BUSY mid-transaction export function executeWorkerTask(database: Database, data: string) { database.run('BEGIN TRANSACTION;'); database.run('INSERT INTO tasks (payload) VALUES (?)', [data]); database.run('COMMIT;'); } ``` #### Production Standard (Correct) > **Standard**: Each worker opens its own native `Database` handle, ensuring OS thread isolation. Setting `PRAGMA journal_mode = WAL;` and `PRAGMA busy_timeout = 5000;` prevents reader/writer blocking and handles transient contention. Using `db.transaction()` (which issues `BEGIN IMMEDIATE`) ensures atomic write lease acquisition without mid-stream lock upgrade deadlocks. ```typescript import { Database } from 'bun:sqlite'; // task.worker.ts: Worker instantiates its own isolated connection with WAL Pragmas declare var self: Worker; function initializeWorkerDb(dbPath: string): Database { const db = new Database(dbPath, { strict: true }); // Mandate WAL journal mode and deterministic busy timeout db.exec('PRAGMA journal_mode = WAL;'); db.exec('PRAGMA busy_timeout = 5000;'); db.exec('PRAGMA synchronous = NORMAL;'); return db; } const db = initializeWorkerDb('app.db'); // Prepared statement cached per-worker handle const insertTask = db.prepare('INSERT INTO tasks (payload) VALUES ($payload)'); self.onmessage = (event: MessageEvent<{ payload: string }>) => { const { payload } = event.data; // Use BEGIN IMMEDIATE to acquire the WAL write-lock eagerly, avoiding mid-transaction deadlocks const transaction = db.transaction((data: string) => { insertTask.run({ $payload: data }); }); try { // db.transaction in bun:sqlite defaults to BEGIN IMMEDIATE transaction(payload); self.postMessage({ success: true }); } catch (err: unknown) { self.postMessage({ success: false, error: (err as Error).message }); } }; ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Bun 1.2 Multi-Worker SQLite Handle Isolation and WAL Write-Lease Protocol

Bun 1.2 Multi-Worker SQLite Handle Isolation and WAL Write-Lease Protocol defines an industrial-grade behavioral standard for backend engineering. Enforces thread-isolated native SQLite connection handles across Bun Web Workers and mandates deterministic WAL write-lease arbitration. Prevents native pointer corruption, segfaults from cross-thread handle transfers, and unrecoverable SQLITE_BUSY deadlocks during concurrent multi-worker writes. 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: Sharing Native bun:sqlite Instances Across Workers or Executing Uncoordinated Concurrent Transactions 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	Sharing Native bun:sqlite Instances Across Workers or Executing Uncoordinated Concurrent Transactions	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 NEVER attempt to pass a native `bun

Engineers MUST NEVER attempt to pass a native `bun:sqlite` `Database` instance across threads via `postMessage()`, `SharedArrayBuffer`, or global variables.

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.