Deterministic WAL Transaction Leasing for Bun 1.2 Native SQLite

--- description: Deterministic WAL Transaction Leasing for Bun 1.2 Native SQLite - Prevents transient write lock starvation (`SQLITE_BUSY`) and phantom dirty reads across multi-threaded worker pools in Bun 1.2. Mandates explicit busy-timeout configurations, synchronous transaction lease queues with deterministic rollback boundaries, and strict WAL write checkpoint isolation. 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-lease-concurrency" --- # Deterministic WAL Transaction Leasing for Bun 1.2 Native SQLite > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Ad-Hoc Unsynchronized Write Transactions Causing SQLITE_BUSY Cascades and Unbounded Thread Pool Starvation ## System Constraints You are a Principal Backend Architect enforcing strict concurrency and data integrity invariants for Bun 1.2's native `bun:sqlite` engine under Write-Ahead Logging (WAL) mode. Mandate synchronous transactional leases, explicit pragmatic locks, and zero unhandled SQLITE_BUSY exceptions across parallel worker execution pools. ## Strict Guidelines - Engineers MUST ALWAYS configure PRAGMA journal_mode = WAL, PRAGMA synchronous = NORMAL, and PRAGMA busy_timeout = 5000 immediately upon opening a `bun:sqlite` Database instance. - Engineers MUST NOT execute raw BEGIN IMMEDIATE or BEGIN EXCLUSIVE statements without acquiring an in-process asynchronous semaphore or transactional lease to serialize concurrent worker mutations. - Code MUST wrap write transactions using db.transaction(...) wrapped in a deterministic retry-backoff harness or centralized write-lease coordinator to eliminate write-lock starvation. - Services MUST ensure that read-only queries run outside write leases and utilize PRAGMA query_only = ON or separate read-only connection handles to prevent promotion lock-wait states. - Components NEVER execute long-running asynchronous network operations, file I/O, or unbounded microtasks inside an active SQLite transaction scope. ## Bad vs Good Implementations ### Uncoordinated Parallel Write Invocation vs. Leased Transaction Queue with Busy Timeout #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Fails to set WAL mode and busy_timeout, uses untracked raw SQL transactions, performs external network I/O inside the active database lock, and lacks write serialization across worker threads, resulting in immediate SQLITE_BUSY lock exhaustion. ```typescript import { Database } from "bun:sqlite"; // ANTI-PATTERN: Default connection settings without WAL or busy_timeout, // and executing writes concurrently without lease coordination. const db = new Database("app.db"); export async function executeTransfer(fromId: string, toId: string, amount: number): Promise<void> { // Uncoordinated raw SQL transaction will throw 'busy' exception if concurrent workers write db.run("BEGIN IMMEDIATE"); try { db.run("UPDATE accounts SET balance = balance - ? WHERE id = ?", [amount, fromId]); // Simulated latency: asynchronous operations inside write transactions stall WAL locking await fetch("https://audit-service.internal/log"); db.run("UPDATE accounts SET balance = balance + ? WHERE id = ?", [amount, toId]); db.run("COMMIT"); } catch (err) { db.run("ROLLBACK"); throw err; } } ``` #### Production Standard (Correct) > **Standard**: Configures WAL mode with PRAGMA busy_timeout=5000, serializes write access through an asynchronous transaction lease, utilizes Bun's native db.transaction().immediate for automatic savepoints/rollbacks, and isolates network I/O outside database locks. ```typescript import { Database } from "bun:sqlite"; import { AsyncLocalStorage } from "node:async_hooks"; // PRODUCTION FIX: Explicit WAL configuration, busy timeout, and serial write leasing export const db = new Database("app.db", { create: true }); db.exec("PRAGMA journal_mode = WAL;"); db.exec("PRAGMA busy_timeout = 5000;"); db.exec("PRAGMA synchronous = NORMAL;"); class TransactionLeaseManager { private static tail: Promise<unknown> = Promise.resolve(); public static async acquire<T>(task: (db: Database) => T): Promise<T> { let release: () => void; const nextTicket = new Promise<void>((resolve) => { release = resolve; }); const previousTicket = TransactionLeaseManager.tail; TransactionLeaseManager.tail = nextTicket; await previousTicket; try { return task(db); } finally { release!(); } } } const transferTx = db.transaction((fromId: string, toId: string, amount: number) => { const deduct = db.prepare("UPDATE accounts SET balance = balance - ? WHERE id = ?"); const credit = db.prepare("UPDATE accounts SET balance = balance + ? WHERE id = ?"); deduct.run(amount, fromId); credit.run(amount, toId); }); export async function executeTransfer(fromId: string, toId: string, amount: number): Promise<void> { // Ensure zero async operations occur within the transaction boundary itself await TransactionLeaseManager.acquire(() => { transferTx.immediate(fromId, toId, amount); }); // Network I/O or background audit is moved strictly outside the WAL transaction boundary await fetch("https://audit-service.internal/log").catch(console.error); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Deterministic WAL Transaction Leasing for Bun 1.2 Native SQLite

Deterministic WAL Transaction Leasing for Bun 1.2 Native SQLite defines an industrial-grade behavioral standard for backend engineering. Prevents transient write lock starvation (`SQLITE_BUSY`) and phantom dirty reads across multi-threaded worker pools in Bun 1.2. Mandates explicit busy-timeout configurations, synchronous transaction lease queues with deterministic rollback boundaries, and strict WAL write checkpoint isolation. 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: Ad-Hoc Unsynchronized Write Transactions Causing SQLITE_BUSY Cascades and Unbounded Thread Pool Starvation 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	Ad-Hoc Unsynchronized Write Transactions Causing SQLITE_BUSY Cascades and Unbounded Thread Pool Starvation	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 = WAL, PRAGMA synchronous = NORMAL, and PRAGMA busy_timeout = 5000 immediately upon opening a `bun

Engineers MUST ALWAYS configure PRAGMA journal_mode = WAL, PRAGMA synchronous = NORMAL, and PRAGMA busy_timeout = 5000 immediately upon opening a `bun:sqlite` Database instance.

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.