Bun 1.2 Embedded SQLite WAL Auto-Throttling & Checkpoint Guardrail

--- description: Bun 1.2 Embedded SQLite WAL Auto-Throttling & Checkpoint Guardrail - Enforces proactive WAL (Write-Ahead Logging) monitoring, adaptive checkpoint scheduling, and transactional backpressure in Bun 1.2 embedded SQLite workloads. Prevents WAL file unbounded growth, ephemeral container disk exhaustion, and catastrophic write amplification caused by concurrent reader-lock checkpoint starvation. globs: src/**/*.{ts,tsx}, lib/**/*.{ts,tsx}, server/**/*.{ts,tsx} alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/bun-sqlite-wal-checkpoint-auto-throttling" --- # Bun 1.2 Embedded SQLite WAL Auto-Throttling & Checkpoint Guardrail > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unbounded WAL Disk Bloat and SQLITE_BUSY Lock Contention Under Async Transaction Spikes ## System Constraints You are a Principal Systems Engineer enforcing strict Bun 1.2 `bun:sqlite` invariants. You MUST prevent runaway WAL accumulation and checkpoint starvation under asynchronous high-concurrency conditions. Ensure all SQLite configurations apply adaptive checkpoints, busy timeouts, and ingestion backpressure. ## Strict Guidelines - Engineers MUST ALWAYS initialize `bun:sqlite` instances with `PRAGMA journal_mode = WAL;`, `PRAGMA synchronous = NORMAL;`, and a nonzero `PRAGMA busy_timeout = 5000;`. - Engineers MUST NEVER execute naive unthrottled concurrent `db.run()` or `db.transaction()` loops without evaluating current WAL frame metrics. - Services MUST implement an active WAL checkpoint monitor using `PRAGMA wal_checkpoint(PASSIVE)` or `PRAGMA wal_checkpoint(RESTART)` to avoid blocking active readers. - Code MUST apply backpressure or defer new write transactions when the WAL file size exceeds a predefined high-water mark (e.g., 4000 pages or 16MB) until a checkpoint succeeds. - Components NEVER execute raw `PRAGMA wal_checkpoint(TRUNCATE)` synchronously within hot API request handlers, as this triggers severe latency spikes and write lockouts. ## Bad vs Good Implementations ### Unthrottled Concurrent Transactions with Default Checkpointing #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Default SQLite WAL autocheckpoints occur every 1000 pages, but with high concurrency, reader locks continuously starve the autocheckpoint. Lacking busy_timeout and WAL frame monitoring, the WAL file balloons to gigabytes on ephemeral container storage, ultimately freezing write transactions or crashing with ENOSPC. ```typescript import { Database } from 'bun:sqlite'; const db = new Database('app.db'); db.run('PRAGMA journal_mode = WAL;'); // ANTI-PATTERN: Rapid async writes without busy_timeout or checkpoint management. // Readers starve WAL checkpoints, causing app.db-wal to grow uncontrollably // until the container disk exhausts or SQLITE_BUSY crashes the service. export async function ingestLogs(logs: Array<{ id: string; payload: string }>) { const insert = db.prepare('INSERT INTO logs (id, payload) VALUES (?, ?)'); await Promise.all( logs.map(log => new Promise<void>((resolve) => { insert.run(log.id, log.payload); resolve(); }) ) ); } ``` #### Production Standard (Correct) > **Standard**: Defines safety PRAGMAs (`busy_timeout = 5000`, `synchronous = NORMAL`) and enforces batching via `db.transaction()`. Periodically queries `PRAGMA wal_checkpoint(PASSIVE)` to monitor WAL log pages and actively yields the event loop before executing `RESTART` checkpointing, safely throttling ingestion before disk space or lock exhaustion occurs. ```typescript import { Database } from 'bun:sqlite'; const db = new Database('app.db'); // Configure defensive pragmas db.run('PRAGMA journal_mode = WAL;'); db.run('PRAGMA synchronous = NORMAL;'); db.run('PRAGMA busy_timeout = 5000;'); db.run('PRAGMA wal_autocheckpoint = 1000;'); const insertStmt = db.prepare('INSERT INTO logs (id, payload) VALUES ($id, $payload)'); const insertBatch = db.transaction((logs: Array<{ id: string; payload: string }>) => { for (const log of logs) { insertStmt.run({ $id: log.id, $payload: log.payload }); } }); interface WalStatus { busy: number; log: number; checkpointed: number; } export class WalThrottledIngestion { private static readonly MAX_SAFE_WAL_PAGES = 4000; // ~16MB at 4KB page size private static isCheckpointing = false; public static async executeWithThrottle(logs: Array<{ id: string; payload: string }>): Promise<void> { await this.throttleIfNecessary(); insertBatch(logs); } private static async throttleIfNecessary(): Promise<void> { const status = db.query('PRAGMA wal_checkpoint(PASSIVE);').get() as WalStatus; // If un-checkpointed pages exceed threshold and checkpoint is not running, yield and attempt soft checkpoint if (status.log > this.MAX_SAFE_WAL_PAGES && !this.isCheckpointing) { this.isCheckpointing = true; try { // Yield to event loop to allow concurrent readers to release read locks await Bun.sleep(10); db.query('PRAGMA wal_checkpoint(RESTART);').run(); } finally { this.isCheckpointing = false; } } } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Bun 1.2 Embedded SQLite WAL Auto-Throttling & Checkpoint Guardrail

Bun 1.2 Embedded SQLite WAL Auto-Throttling & Checkpoint Guardrail defines an industrial-grade behavioral standard for backend engineering. Enforces proactive WAL (Write-Ahead Logging) monitoring, adaptive checkpoint scheduling, and transactional backpressure in Bun 1.2 embedded SQLite workloads. Prevents WAL file unbounded growth, ephemeral container disk exhaustion, and catastrophic write amplification caused by concurrent reader-lock checkpoint 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: Unbounded WAL Disk Bloat and SQLITE_BUSY Lock Contention Under Async Transaction Spikes 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	Unbounded WAL Disk Bloat and SQLITE_BUSY Lock Contention Under Async Transaction Spikes	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 `bun

Engineers MUST ALWAYS initialize `bun:sqlite` instances with `PRAGMA journal_mode = WAL;`, `PRAGMA synchronous = NORMAL;`, and a nonzero `PRAGMA busy_timeout = 5000;`.

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.