Bun 1.2 Native SQLite WAL Clustered Snapshot Isolation Standard

Q: How does adopting this rule prevent AI hallucinations?

This rule explicitly declares what the AI must avoid (Deferred Read-to-Write Lock Escalation and Unbounded Snapshots Under Clustered Bun Concurrency) and prescribes verified positive examples. Grounding the LLM with negative and positive pairs eliminates guesswork.

Q: Which AI coding environments are supported?

This specification is multi-platform compatible: Cursor (.cursor/rules/*.mdc & .cursorrules), Anthropic Claude Code (CLAUDE.md), Windsurf (.windsurfrules), Codex / Cline (AGENTS.md), and GitHub Copilot (.github/copilot-instructions.md).

Q: Can I combine this rule with other technology stacks?

Yes. Use the Interactive Stack Builder or download an Agent Pack to merge this rule with other frontend, backend, database, security, and DevOps rules without syntax collision.

Bun 1.2 Native SQLite WAL Clustered Snapshot Isolation Standard

--- description: Bun 1.2 Native SQLite WAL Clustered Snapshot Isolation Standard - Enforces deterministic MVCC read snapshot isolation and eliminates write lock escalation deadlocks across high-throughput Bun worker clusters sharing native SQLite storage. Mandates explicit PRAGMA WAL initialization, strict IMMEDIATE transaction boundaries for all mutating flows, and deterministic snapshot closure to prevent WAL file accumulation and SQLITE_BUSY starvation. globs: src/**/*.{ts,tsx}, server/**/*.{ts,tsx}, workers/**/*.{ts,tsx}, lib/**/*.{ts,tsx} alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/bun-sqlite-wal-clustered-snapshot-isolation" --- # Bun 1.2 Native SQLite WAL Clustered Snapshot Isolation Standard > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Deferred Read-to-Write Lock Escalation and Unbounded Snapshots Under Clustered Bun Concurrency ## System Constraints You are a Principal Software Architect enforcing the Bun 1.2 Native SQLite WAL Clustered Snapshot Isolation Standard. All code utilizing Bun's native `bun:sqlite` module across multi-threaded or clustered worker processes must guarantee zero write lock escalation, bounded reader lifecycles, and resilient busy handling. ## Strict Guidelines - Engineers MUST ALWAYS initialize every native Bun `Database` instance with `PRAGMA journal_mode = WAL;`, `PRAGMA synchronous = NORMAL;`, and an explicit `PRAGMA busy_timeout = 5000;` before running application queries. - Engineers MUST ALWAYS initiate mutating transactions using immediate isolation (`db.transaction(...).immediate()` or `BEGIN IMMEDIATE`) to acquire the SQLite RESERVED lock upfront and eliminate runtime lock escalation deadlocks. - Engineers MUST NOT execute mutating statements (`INSERT`, `UPDATE`, `DELETE`) inside a deferred transaction context (`BEGIN DEFERRED` or default `db.transaction(...)`). - Code MUST finalize statement execution and avoid keeping transaction scopes open across asynchronous I/O, network calls, or timer boundaries to prevent WAL checkpoint starvation. - Services MUST execute read-only queries in point-in-time snapshot statements without holding open transaction handles that block SQLite `-wal` and `-shm` frame truncations. - Components NEVER instantiate multiple unsynchronized write loops targeting the same SQLite database file without exponential backoff and jitter for `SQLITE_BUSY` conditions. ## Bad vs Good Implementations ### Naive Deferred Transaction with Lock Escalation Failure vs. Resilient WAL Immediate Isolation #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Default transactions in SQLite are DEFERRED. If two clustered Bun processes read concurrently and both attempt to write, both attempt to upgrade to a RESERVED write lock simultaneously, causing immediate lock escalation failure (SQLITE_BUSY/SQLITE_LOCKED). Omitting busy_timeout and WAL mode leads to instant failure and uncoordinated file locking. ```typescript import { Database } from "bun:sqlite"; // Anti-pattern: Missing PRAGMAs, deferred transaction escalation, lock exhaustion export class OrderService { private db: Database; constructor(dbPath: string) { this.db = new Database(dbPath); // Missing WAL mode, synchronous=NORMAL, and busy_timeout } async processOrder(orderId: string, amount: number) { // Default deferred transaction starts as read-only lock const tx = this.db.transaction(() => { const account = this.db.query("SELECT balance FROM accounts WHERE id = 1").get() as { balance: number }; if (account.balance < amount) throw new Error("Insufficient funds"); // Lock escalation here triggers SQLITE_BUSY in multi-worker environments this.db.query("UPDATE accounts SET balance = balance - ? WHERE id = 1").run(amount); this.db.query("INSERT INTO orders (id, amount) VALUES (?, ?)").run(orderId, amount); }); return tx(); } } ``` #### Production Standard (Correct) > **Standard**: The database connection is tuned with WAL and a 5000ms busy_timeout, allowing parallel reads while writes wait safely. Using `tx.immediate()` forces SQLite to acquire the write lock at the start, preventing concurrent workers from experiencing deadlocks during execution. ```typescript import { Database } from "bun:sqlite"; export class OrderService { private db: Database; constructor(dbPath: string) { this.db = new Database(dbPath, { strict: true }); // Mandate WAL MVCC, safe synchronous mode, and worker timeout handling this.db.exec(` PRAGMA journal_mode = WAL; PRAGMA synchronous = NORMAL; PRAGMA busy_timeout = 5000; PRAGMA cache_size = -64000; `); } processOrder(orderId: string, amount: number): void { // IMMEDIATE transaction acquires RESERVED lock upfront, preventing mid-transaction deadlocks const tx = this.db.transaction(() => { const account = this.db.query( "SELECT balance FROM accounts WHERE id = ?1" ).get(1) as { balance: number } | null; if (!account || account.balance < amount) { throw new Error("Insufficient funds or invalid account"); } this.db.query( "UPDATE accounts SET balance = balance - ?1 WHERE id = ?2" ).run(amount, 1); this.db.query( "INSERT INTO orders (id, amount) VALUES (?1, ?2)" ).run(orderId, amount); }); // Invoke with explicit immediate mode semantics tx.immediate(); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Bun 1.2 Native SQLite WAL Clustered Snapshot Isolation Standard

Bun 1.2 Native SQLite WAL Clustered Snapshot Isolation Standard defines an industrial-grade behavioral standard for backend engineering. Enforces deterministic MVCC read snapshot isolation and eliminates write lock escalation deadlocks across high-throughput Bun worker clusters sharing native SQLite storage. Mandates explicit PRAGMA WAL initialization, strict IMMEDIATE transaction boundaries for all mutating flows, and deterministic snapshot closure to prevent WAL file accumulation and SQLITE_BUSY starvation. 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: Deferred Read-to-Write Lock Escalation and Unbounded Snapshots Under Clustered Bun Concurrency 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	Deferred Read-to-Write Lock Escalation and Unbounded Snapshots Under Clustered Bun Concurrency	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 initialize every native Bun `Database` instance with `PRAGMA journal_mode = WAL

Engineers MUST ALWAYS initialize every native Bun `Database` instance with `PRAGMA journal_mode = WAL;`, `PRAGMA synchronous = NORMAL;`, and an explicit `PRAGMA busy_timeout = 5000;` before running application queries.

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.