Bun 1.2 SQLite WAL2 Auto-Checkpoint Starvation Mitigation

--- description: Bun 1.2 SQLite WAL2 Auto-Checkpoint Starvation Mitigation - Enforces cooperative backpressure and explicit frame threshold throttling on embedded SQLite (WAL/WAL2 mode) under Bun 1.2. Prevents unbounded disk bloat and severe read latency degradation caused by high-concurrency write loops starving background checkpoint threads. globs: src/db/**/*.ts, src/repositories/**/*.ts, src/services/**/*.ts, src/**/*.sqlite.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/bun-sqlite-wal2-checkpoint-starvation-backpressure" --- # Bun 1.2 SQLite WAL2 Auto-Checkpoint Starvation Mitigation > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unbounded Concurrent Write Loops Starving WAL Checkpoints and Causing Frame Bloat ## System Constraints You are a Principal Software Architect enforcing Bun 1.2 SQLite WAL/WAL2 checkpoint reliability. Mandate cooperative backpressure gates on all high-throughput write paths to prevent WAL checkpoint starvation and disk bloat. ## Strict Guidelines - Engineers MUST ALWAYS evaluate WAL frame saturation via `PRAGMA wal_checkpoint(PASSIVE)` before permitting queued write transactions to proceed. - Engineers MUST NOT execute unbounded `Promise.all` or tight synchronous write loops against `bun:sqlite` without yielding control to the event loop. - Code SHOULD enforce an asynchronous backpressure queue that pauses write dispatches when checkpoint backlog exceeds defined frame thresholds (e.g., > 1,000 frames). - Services MUST ensure read transactions are transient and never held open indefinitely across asynchronous ticks, which pins the WAL minimum transaction frame. - Components NEVER execute blocking `PRAGMA wal_checkpoint(TRUNCATE)` within critical read/write hot-paths; checkpoint maintenance must run cooperatively. ## Bad vs Good Implementations ### Unbounded Write Batching Causing Checkpoint Starvation vs Cooperative Backpressure Coordinator #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Executing rapid consecutive write transactions without yielding or inspecting WAL frame accumulation continuously monopolizes the SQLite write lock. In WAL/WAL2 mode, checkpoints cannot progress past active transactions, causing unbounded WAL frame accumulation, severe read degradation, and eventual process crash due to disk exhaustion. ```typescript import { Database } from 'bun:sqlite'; export class MetricRepository { private db = new Database('metrics.db'); constructor() { this.db.exec('PRAGMA journal_mode = WAL2;'); } // ANTI-PATTERN: Ingesting high-concurrency writes in an unthrottled loop. // SQLite cannot perform background passive checkpoints while write transactions // continuously acquire EXCLUSIVE/RESERVED locks, causing the WAL log to explode to tens of GBs. async ingestBatch(records: Array<{ id: string; value: number }>): Promise<void> { const insert = this.db.prepare('INSERT INTO metrics (id, value) VALUES ($id, $value)'); // Starvation: Saturated event loop and back-to-back transaction writes prevent WAL recycling for (const record of records) { this.db.transaction(() => { insert.run({ $id: record.id, $value: record.value }); })(); } } } ``` #### Production Standard (Correct) > **Standard**: Batches write operations into discrete chunks and monitors SQLite WAL checkpoint progress with `PRAGMA wal_checkpoint(PASSIVE)`. When uncheckpointed frames exceed the safe threshold (1,000 frames), it applies progressive exponential backoff and explicit event-loop yields (`Bun.sleep`), allowing the SQLite engine to flush dirty pages to the database file without starving active readers. ```typescript import { Database } from 'bun:sqlite'; export class MetricRepository { private db: Database; private checkCounter = 0; private readonly CHECK_INTERVAL = 250; private readonly MAX_UNCHECKPOINTED_FRAMES = 1000; constructor() { this.db = new Database('metrics.db'); this.db.exec('PRAGMA journal_mode = WAL2;'); this.db.exec('PRAGMA synchronous = NORMAL;'); } private async yieldForCheckpointBackpressure(): Promise<void> { // Inspect checkpoint status: [busy, log, checkpointed] const status = this.db.query< [number, number, number], [] >('PRAGMA wal_checkpoint(PASSIVE);').values()[0]; if (!status) return; const [, totalFrames, checkpointedFrames] = status; const uncheckpointed = totalFrames - checkpointedFrames; // If write frames outpace passive checkpoint progress, back off to yield disk I/O if (uncheckpointed >= this.MAX_UNCHECKPOINTED_FRAMES) { let attempts = 0; while (attempts < 5) { await Bun.sleep(10 * Math.pow(2, attempts)); const retryStatus = this.db.query<[number, number, number], []>('PRAGMA wal_checkpoint(PASSIVE);').values()[0]; if (retryStatus && (retryStatus[1] - retryStatus[2]) < this.MAX_UNCHECKPOINTED_FRAMES) { break; } attempts++; } } } async ingestBatch(records: Array<{ id: string; value: number }>): Promise<void> { const insert = this.db.prepare('INSERT INTO metrics (id, value) VALUES ($id, $value)'); const batchTx = this.db.transaction((chunk: Array<{ id: string; value: number }>) => { for (const record of chunk) { insert.run({ $id: record.id, $value: record.value }); } }); const CHUNK_SIZE = 100; for (let i = 0; i < records.length; i += CHUNK_SIZE) { const chunk = records.slice(i, i + CHUNK_SIZE); batchTx(chunk); this.checkCounter += chunk.length; if (this.checkCounter >= this.CHECK_INTERVAL) { this.checkCounter = 0; await this.yieldForCheckpointBackpressure(); // Yield execution to allow Bun microtask queue and OS page cache to breathe await Bun.sleep(0); } } } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Bun 1.2 SQLite WAL2 Auto-Checkpoint Starvation Mitigation

Bun 1.2 SQLite WAL2 Auto-Checkpoint Starvation Mitigation defines an industrial-grade behavioral standard for backend engineering. Enforces cooperative backpressure and explicit frame threshold throttling on embedded SQLite (WAL/WAL2 mode) under Bun 1.2. Prevents unbounded disk bloat and severe read latency degradation caused by high-concurrency write loops starving background checkpoint threads. 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 Concurrent Write Loops Starving WAL Checkpoints and Causing Frame Bloat 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 Concurrent Write Loops Starving WAL Checkpoints and Causing Frame Bloat	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 evaluate WAL frame saturation via `PRAGMA wal_checkpoint(PASSIVE)` before permitting queued write transactions to proceed.

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.