Bun 1.2 SQLite Embedded WAL Shared Memory Concurrency Guardrail

--- description: Bun 1.2 SQLite Embedded WAL Shared Memory Concurrency Guardrail - Enforces strict POSIX shared-memory (`-shm`) lock synchronization, single-writer multi-reader threading topology, and automated WAL checkpoint pacing when coordinating multi-threaded Bun worker pools accessing co-located SQLite storage. Prevents SQLITE_BUSY, database corruption, deadlocks, and disk space bloat caused by uncoordinated write transactions and rogue file descriptors. globs: src/**/*.{ts,js}, workers/**/*.{ts,js}, lib/**/*.{ts,js} alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/bun-sqlite-wal-shm-concurrency-guardrail" --- # Bun 1.2 SQLite Embedded WAL Shared Memory Concurrency Guardrail > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Spawning Multiple Bun Workers with Uncoordinated Read-Write SQLite Connections and Default Autocheckpoint Settings ## System Constraints You are a Principal Engineer enforcing the Bun 1.2 SQLite Embedded WAL Shared Memory Concurrency Guardrail. Mandate invariants regarding SQLite connection management, single-writer worker architecture, POSIX shared memory (`-shm`) consistency, PRAGMA tuning, and WAL checkpoint pacing across worker pools. ## Strict Guidelines - Engineers MUST ALWAYS initialize SQLite databases with WAL mode (`PRAGMA journal_mode = WAL;`) and synchronous normal (`PRAGMA synchronous = NORMAL;`) before spawning worker threads. - Engineers MUST configure a mandatory busy timeout (`PRAGMA busy_timeout = 5000;`) on every `bun:sqlite` Database instance across all threads to prevent immediate SQLITE_BUSY panics during file lock contention. - Engineers MUST NOT allow multiple Bun worker threads to open read-write connections against the same SQLite database file without explicit serialized write queues or a dedicated writer worker thread. - Services MUST route all mutating operations (INSERT, UPDATE, DELETE) through a single dedicated write worker or coordinator, while secondary worker threads MUST open read-only connections (`{ readonly: true }`). - Services MUST disable default engine autocheckpointing (`PRAGMA wal_autocheckpoint = 0;`) on reader pools and designate exactly one orchestrator thread to trigger passive or truncate checkpoints (`PRAGMA wal_checkpoint(PASSIVE);`) during active worker workloads. - Components NEVER terminate Bun worker processes without gracefully closing open `bun:sqlite` handles to prevent dangling POSIX locks and stale indices in the `-shm` mapping file. ## Bad vs Good Implementations ### Uncoordinated Multi-Worker Read/Write Access vs. Dedicated Single-Writer with Read-Only Worker Pool #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Creating unconfigured read-write `bun:sqlite` instances across multiple worker threads leads to concurrent lock contention, missing busy timeouts, rogue shared memory locks (-shm), and unmanaged WAL log explosion. ```typescript import { Database } from 'bun:sqlite'; // ANTI-PATTERN: Each worker thread creates an unconstrained read-write instance // without busy_timeout, journal configuration, or checkpoint orchestration. export class UnsafeWorkerStorage { private db: Database; constructor(dbPath: string) { this.db = new Database(dbPath); } public mutate(id: string, payload: string): void { // Under concurrent worker load, this causes SQLITE_BUSY, database locking, // or corrupts the POSIX shared memory table (-shm). this.db.run('INSERT INTO records (id, data) VALUES (?, ?)', [id, payload]); } public read(id: string) { return this.db.query('SELECT * FROM records WHERE id = ?').get(id); } } ``` #### Production Standard (Correct) > **Standard**: Explicitly separates read and write privileges per worker, mandates `PRAGMA busy_timeout`, enforces WAL and NORMAL synchronous persistence, and manages WAL checkpointing without thread starvation. ```typescript import { Database } from 'bun:sqlite'; export interface StorageOptions { dbPath: string; isWriterNode: boolean; busyTimeoutMs?: number; } export class ResilientWorkerStorage { private db: Database; private isWriter: boolean; constructor(options: StorageOptions) { this.isWriter = options.isWriterNode; const busyTimeout = options.busyTimeoutMs ?? 5000; // Readers open explicitly read-only; exactly one writer thread holds R/W this.db = new Database(options.dbPath, { readonly: !this.isWriter, create: this.isWriter, }); // Enforce POSIX shm invariants and lock resolution this.db.exec(`PRAGMA busy_timeout = ${busyTimeout};`); if (this.isWriter) { this.db.exec(` PRAGMA journal_mode = WAL; PRAGMA synchronous = NORMAL; PRAGMA wal_autocheckpoint = 1000; `); } } public executeWrite(id: string, payload: string): void { if (!this.isWriter) { throw new Error('ILLEGAL_MUTATION: Worker is configured as READONLY'); } const stmt = this.db.prepare('INSERT INTO records (id, data) VALUES (?1, ?2)'); stmt.run(id, payload); } public executeRead(id: string) { const stmt = this.db.prepare('SELECT id, data FROM records WHERE id = ?1'); return stmt.get(id); } public checkpoint(): void { if (this.isWriter) { // Use PASSIVE to prevent blocking active concurrent worker readers this.db.exec('PRAGMA wal_checkpoint(PASSIVE);'); } } public close(): void { this.db.close(); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Bun 1.2 SQLite Embedded WAL Shared Memory Concurrency Guardrail

Bun 1.2 SQLite Embedded WAL Shared Memory Concurrency Guardrail defines an industrial-grade behavioral standard for backend engineering. Enforces strict POSIX shared-memory (`-shm`) lock synchronization, single-writer multi-reader threading topology, and automated WAL checkpoint pacing when coordinating multi-threaded Bun worker pools accessing co-located SQLite storage. Prevents SQLITE_BUSY, database corruption, deadlocks, and disk space bloat caused by uncoordinated write transactions and rogue file descriptors. 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: Spawning Multiple Bun Workers with Uncoordinated Read-Write SQLite Connections and Default Autocheckpoint Settings 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	Spawning Multiple Bun Workers with Uncoordinated Read-Write SQLite Connections and Default Autocheckpoint Settings	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 SQLite databases with WAL mode (`PRAGMA journal_mode = WAL

Engineers MUST ALWAYS initialize SQLite databases with WAL mode (`PRAGMA journal_mode = WAL;`) and synchronous normal (`PRAGMA synchronous = NORMAL;`) before spawning worker threads.

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.