Bun 1.2 SQLite WAL Checkpoint and Multi-Process Lock Orchestration Guardrail

--- description: Bun 1.2 SQLite WAL Checkpoint and Multi-Process Lock Orchestration Guardrail - Enforces deterministic write-ahead log (WAL) pragmas, synchronous checkpoint boundaries, and cross-process lock orchestration for native bun:sqlite in Bun 1.2. Eliminates SQLITE_BUSY lock starvation, unbounded WAL file growth, and transactional corruption under high-throughput edge concurrency. globs: src/**/*.ts, src/db/**/*.ts, src/database/**/*.ts, lib/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/bun-sqlite-dual-wal-orchestration-standard" --- # Bun 1.2 SQLite WAL Checkpoint and Multi-Process Lock Orchestration Guardrail > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Ad-Hoc Inline Checkpointing and Unsynchronized Multi-Worker Writes in Native bun:sqlite ## System Constraints You are a Principal Software Architect enforcing production safety invariants for Bun 1.2's native SQLite engine (bun:sqlite). You must mandate strict WAL-mode initialization pragmas, forbid ad-hoc checkpoint executions inside HTTP request paths, enforce isolated transaction boundaries, and ensure robust multi-process and multi-worker lock orchestration under heavy edge-write loads. ## Strict Guidelines - Engineers MUST ALWAYS initialize native `bun:sqlite` connections with `PRAGMA journal_mode = WAL;`, `PRAGMA busy_timeout = 10000;`, and `PRAGMA synchronous = NORMAL;` before serving writes. - Engineers MUST NOT invoke `PRAGMA wal_checkpoint` inline within request-handling loops or fast-path write transactions. - Code SHOULD isolate SQLite write operations behind serialized worker queues or explicit lock boundaries to avoid contention and SQLITE_BUSY deadlocks. - Services MUST execute WAL checkpointing asynchronously via dedicated background routines or bounded cron intervals utilizing `wal_checkpoint(PASSIVE)` or `wal_checkpoint(TRUNCATE)` with mutex protection. - Components NEVER execute raw DDL or destructive journal-mode changes on shared databases without validating that all reader handles and worker threads have yielded their connections. ## Bad vs Good Implementations ### Uncoordinated Inline Checkpointing and Default SQLite Initialization #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Opening native bun:sqlite without setting `busy_timeout` causes immediate catastrophic failures under edge concurrency with `SQLITE_BUSY: database is locked`. Executing `wal_checkpoint(TRUNCATE)` inside the request-response lifecycle creates extreme I/O pauses, blocks readers, and induces lock starvation across worker processes. ```typescript import { Database } from "bun:sqlite"; // Anti-pattern: Default SQLite connection without busy timeout, journal_mode, or synchronous pragmas. export const db = new Database("edge.db"); export async function recordTelemetry(event: Record<string, unknown>) { // Anti-pattern: Writing directly to un-tuned SQLite under concurrent load risks SQLITE_BUSY. const query = db.prepare("INSERT INTO telemetry (payload) VALUES (?)"); query.run(JSON.stringify(event)); // Anti-pattern: Synchronously forcing a TRUNCATE checkpoint inside the request handler. // Under concurrent writes, this halts all workers and can cause file corruption or process lockup. db.exec("PRAGMA wal_checkpoint(TRUNCATE);"); } ``` #### Production Standard (Correct) > **Standard**: The database instance is systematically initialized with explicit WAL pragmas, a 10-second busy timeout to prevent write rejections, and `synchronous = NORMAL` for durable yet fast edge I/O. Checkpoint execution is completely decoupled from API request handlers and guarded by single-flight execution flags. ```typescript import { Database } from "bun:sqlite"; export class EdgeDatabaseManager { private static db: Database | null = null; private static isCheckpointing = false; public static getInstance(dbPath = "edge.db"): Database { if (!this.db) { this.db = new Database(dbPath, { strict: true, create: true }); // Invariant: Establish reliable WAL concurrency and lock tolerance this.db.exec("PRAGMA journal_mode = WAL;"); this.db.exec("PRAGMA busy_timeout = 10000;"); this.db.exec("PRAGMA synchronous = NORMAL;"); this.db.exec("PRAGMA foreign_keys = ON;"); } return this.db; } public static async insertTelemetry(event: Record<string, unknown>): Promise<void> { const db = this.getInstance(); const stmt = db.prepare("INSERT INTO telemetry (payload) VALUES (?)"); // Parameterized write wrapped in managed statement lifecycle stmt.run(JSON.stringify(event)); } public static runMaintenanceCheckpoint(mode: "PASSIVE" | "RESTART" | "TRUNCATE" = "PASSIVE"): void { if (this.isCheckpointing) return; this.isCheckpointing = true; try { const db = this.getInstance(); // Checkpointing isolated to managed maintenance lifecycle with error tolerance db.exec(`PRAGMA wal_checkpoint(${mode});`); } catch (err) { if (err instanceof Error && !err.message.includes("busy")) { console.error("WAL maintenance checkpoint failed:", err); } } finally { this.isCheckpointing = false; } } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Bun 1.2 SQLite WAL Checkpoint and Multi-Process Lock Orchestration Guardrail

Bun 1.2 SQLite WAL Checkpoint and Multi-Process Lock Orchestration Guardrail defines an industrial-grade behavioral standard for backend engineering. Enforces deterministic write-ahead log (WAL) pragmas, synchronous checkpoint boundaries, and cross-process lock orchestration for native bun:sqlite in Bun 1.2. Eliminates SQLITE_BUSY lock starvation, unbounded WAL file growth, and transactional corruption under high-throughput edge concurrency. 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 Inline Checkpointing and Unsynchronized Multi-Worker Writes in Native bun:sqlite 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 Inline Checkpointing and Unsynchronized Multi-Worker Writes in Native bun:sqlite	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 native `bun

Engineers MUST ALWAYS initialize native `bun:sqlite` connections with `PRAGMA journal_mode = WAL;`, `PRAGMA busy_timeout = 10000;`, and `PRAGMA synchronous = NORMAL;` before serving writes.

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.