Bun 1.2 SQLite Concurrent WAL Checkpoint Sharding Standard

--- description: Bun 1.2 SQLite Concurrent WAL Checkpoint Sharding Standard - Enforces strict isolation between write-ahead log (WAL) checkpoint operations and edge read paths in Bun 1.2 SQLite architectures. Prevents event-loop thread blocking and tail-latency spikes by mandating dedicated read-only connection pools, explicit PRAGMA tuning, and offloaded non-blocking passive checkpoints. globs: src/**/*.{ts,js} alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/bun-sqlite-concurrent-wal-checkpoint-sharding-standard" --- # Bun 1.2 SQLite Concurrent WAL Checkpoint Sharding Standard > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Running Synchronous TRUNCATE Checkpoints on Shared Single-Connection SQLite Handles ## System Constraints You are a Principal Engineer enforcing the Bun 1.2 SQLite Concurrent WAL Checkpoint Sharding Standard. You must ensure that SQLite databases deployed at the edge maintain zero-stall reader latency and deterministic memory footprints under heavy write contention by decoupling reader handles, writer handles, and checkpoint workers. ## Strict Guidelines - Engineers MUST ALWAYS isolate SQLite read transactions to a separate, read-only Database instance configured with PRAGMA query_only = ON. - Engineers MUST NOT execute PRAGMA wal_checkpoint(FULL) or PRAGMA wal_checkpoint(TRUNCATE) directly on foreground request threads. - Code SHOULD configure PRAGMA journal_mode = WAL and tune PRAGMA synchronous = NORMAL to eliminate fsync stalls on reader threads. - Services MUST execute WAL maintenance using PRAGMA wal_checkpoint(PASSIVE) or schedule non-blocking checkpoint tasks via background Worker threads. - Components NEVER execute raw long-running transactions across asynchronous await boundaries on writer instances, preventing reader lock escalation and WAL frame explosion. ## Bad vs Good Implementations ### Shared Connection with Blocking Foreground Checkpoint vs. Decoupled Read/Write Handles with Passive Checkpointing #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Executing PRAGMA wal_checkpoint(TRUNCATE) on a shared database handle forces an exclusive lock on the WAL file. This stalls concurrent readers, degrades edge p99 latency, and blocks the single Bun event loop thread. ```typescript import { Database } from 'bun:sqlite'; // ANTI-PATTERN: Single connection handling both reads, writes, and blocking checkpoints export const db = new Database('app.db', { create: true }); db.run('PRAGMA journal_mode = WAL;'); export function recordEvent(event: Record<string, unknown>) { const tx = db.transaction(() => { db.prepare('INSERT INTO events (payload) VALUES (?)').run(JSON.stringify(event)); }); tx(); // Blocking checkpoint run in the request hot path stalls all concurrent readers db.run('PRAGMA wal_checkpoint(TRUNCATE);'); } export function fetchEvents(limit: number) { return db.prepare('SELECT * FROM events ORDER BY id DESC LIMIT ?').all(limit); } ``` #### Production Standard (Correct) > **Standard**: Separating the writer and reader instances prevents thread starvation. Using PASSIVE checkpointing ensures that WAL pages are transferred to the main database file only when pages are unpinned, guaranteeing zero stalls for active readers. ```typescript import { Database } from 'bun:sqlite'; const DB_PATH = 'app.db'; // PROD PATTERN: Dedicated write handle with tuned pragmas const writerDb = new Database(DB_PATH, { create: true }); writerDb.run('PRAGMA journal_mode = WAL;'); writerDb.run('PRAGMA synchronous = NORMAL;'); writerDb.run('PRAGMA busy_timeout = 5000;'); // Dedicated read-only handle with zero checkpoint interference const readerDb = new Database(DB_PATH, { readonly: true }); readerDb.run('PRAGMA query_only = ON;'); readerDb.run('PRAGMA busy_timeout = 2000;'); export function recordEvent(event: Record<string, unknown>): void { const insert = writerDb.prepare('INSERT INTO events (payload) VALUES (?)'); insert.run(JSON.stringify(event)); } export function fetchEvents(limit: number): unknown[] { return readerDb.prepare('SELECT * FROM events ORDER BY id DESC LIMIT ?').all(limit); } // Passive non-blocking checkpoint scheduled outside critical paths export function runPassiveMaintenance(): void { const result = writerDb.prepare('PRAGMA wal_checkpoint(PASSIVE);').get() as { busy: number; log: number; checkpointed: number; }; if (result && result.busy === 1) { // Log high contention without interrupting ongoing reads console.warn('[WAL Maintenance] Checkpoint deferred due to active transactions'); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Bun 1.2 SQLite Concurrent WAL Checkpoint Sharding Standard

Bun 1.2 SQLite Concurrent WAL Checkpoint Sharding Standard defines an industrial-grade behavioral standard for backend engineering. Enforces strict isolation between write-ahead log (WAL) checkpoint operations and edge read paths in Bun 1.2 SQLite architectures. Prevents event-loop thread blocking and tail-latency spikes by mandating dedicated read-only connection pools, explicit PRAGMA tuning, and offloaded non-blocking passive checkpoints. 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: Running Synchronous TRUNCATE Checkpoints on Shared Single-Connection SQLite Handles 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	Running Synchronous TRUNCATE Checkpoints on Shared Single-Connection SQLite Handles	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 isolate SQLite read transactions to a separate, read-only Database instance configured with PRAGMA query_only = ON.

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.