Bun 1.2 Multi-Worker SQLite Isolated WAL Checkpoint Coordinator Standard

--- description: Bun 1.2 Multi-Worker SQLite Isolated WAL Checkpoint Coordinator Standard - Enforces architectural isolation of SQLite WAL checkpoint execution in multi-threaded Bun 1.2 deployments to prevent unbounded WAL bloat and unrecoverable SQLITE_BUSY starvation. Mandates explicit disabling of worker-level auto-checkpoints and directs all PRAGMA wal_checkpoint operations through a singleton background coordinator worker. globs: src/**/*.ts, src/db/**/*.ts, workers/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/bun-sqlite-multi-worker-wal-coordinator" --- # Bun 1.2 Multi-Worker SQLite Isolated WAL Checkpoint Coordinator Standard > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Concurrent Worker Checkpoints Causing SQLITE_BUSY Cascades and Unbounded WAL Bloat ## System Constraints You are a Principal Engineer enforcing the Bun 1.2 Embedded SQLite Multi-Worker WAL Checkpoint Standard. Mandate that multi-threaded or multi-worker Bun processes disable SQLite automatic checkpoints across standard reader/writer instances and serialize all WAL maintenance strictly within an isolated coordinator thread. ## Strict Guidelines - Engineers MUST ALWAYS set 'PRAGMA wal_autocheckpoint = 0;' on all Bun SQLite instances initialized in application workers. - Engineers MUST serialize all PRAGMA wal_checkpoint operations through a single, dedicated coordinator worker to avoid SQLITE_BUSY contention. - Workers MUST NOT execute PRAGMA wal_checkpoint(FULL), PRAGMA wal_checkpoint(TRUNCATE), or PRAGMA wal_checkpoint(PASSIVE) directly during request/task handling. - The checkpoint coordinator SHOULD favor 'PRAGMA wal_checkpoint(PASSIVE);' under active load and reserve 'PRAGMA wal_checkpoint(TRUNCATE);' for low-concurrency or idle windows. - Database connections across all workers MUST configure a non-zero busy_timeout (e.g., 'PRAGMA busy_timeout = 5000;') to prevent immediate query termination during concurrent read-lock periods. ## Bad vs Good Implementations ### Anti-Pattern: Concurrent Worker Auto-Checkpoints and In-Band Checkpointing #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Each worker maintains SQLite's default autocheckpoint behavior (1000 pages). When multiple workers cross this threshold simultaneously or attempt ad-hoc TRUNCATE checkpoints during active queries, thread-level race conditions cause catastrophic SQLITE_BUSY lockups and reader starvation. ```typescript import { Database } from 'bun:sqlite'; // Invoked across multiple Bun worker threads export function getWorkerDb(path: string): Database { const db = new Database(path, { strict: true }); db.run('PRAGMA journal_mode = WAL;'); // DANGER: Default wal_autocheckpoint (1000 pages) is active on every worker. // Workers concurrently hit checkpoint thresholds, triggering SQLITE_BUSY locks. return db; } export function flushWorkerLogs(db: Database): void { // DANGER: In-band truncate checkpoint on an active reader/writer thread // halts sibling workers and causes lockups. db.run('PRAGMA wal_checkpoint(TRUNCATE);'); } ``` #### Production Standard (Correct) > **Standard**: Explicitly disabling autocheckpoints across workers stops uncoordinated lock contention. All checkpoint executions are delegated to an isolated coordinator instance that uses PASSIVE non-blocking passes with explicit busy-detection and backoff. ```typescript import { Database } from 'bun:sqlite'; // 1. Worker database initialization export function createWorkerConnection(path: string): Database { const db = new Database(path, { strict: true }); db.run('PRAGMA journal_mode = WAL;'); db.run('PRAGMA busy_timeout = 5000;'); // Disable implicit autocheckpoint on worker threads db.run('PRAGMA wal_autocheckpoint = 0;'); return db; } // 2. Dedicated Singleton Background Coordinator (coordinator-worker.ts) export class WalCheckpointCoordinator { private db: Database; private intervalId: Timer | null = null; constructor(dbPath: string) { this.db = new Database(dbPath, { strict: true }); this.db.run('PRAGMA busy_timeout = 5000;'); this.db.run('PRAGMA wal_autocheckpoint = 0;'); } public start(intervalMs: number = 30000): void { this.intervalId = setInterval(() => this.executeControlledCheckpoint(), intervalMs); } public executeControlledCheckpoint(): void { try { // Execute non-blocking PASSIVE checkpoint during normal runtime const result = this.db.query('PRAGMA wal_checkpoint(PASSIVE);').get() as { busy: number; log: number; checkpointed: number; }; if (result && result.busy === 1) { // Readers active; coordinator backs off without blocking workers return; } } catch (err) { console.error('Safe WAL checkpoint encountered error:', err); } } public close(): void { if (this.intervalId) clearInterval(this.intervalId); // Clean flush on coordinator shutdown this.db.run('PRAGMA wal_checkpoint(TRUNCATE);'); this.db.close(); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Bun 1.2 Multi-Worker SQLite Isolated WAL Checkpoint Coordinator Standard

Bun 1.2 Multi-Worker SQLite Isolated WAL Checkpoint Coordinator Standard defines an industrial-grade behavioral standard for backend engineering. Enforces architectural isolation of SQLite WAL checkpoint execution in multi-threaded Bun 1.2 deployments to prevent unbounded WAL bloat and unrecoverable SQLITE_BUSY starvation. Mandates explicit disabling of worker-level auto-checkpoints and directs all PRAGMA wal_checkpoint operations through a singleton background coordinator worker. 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: Concurrent Worker Checkpoints Causing SQLITE_BUSY Cascades and Unbounded WAL 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	Concurrent Worker Checkpoints Causing SQLITE_BUSY Cascades and Unbounded WAL 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 set 'PRAGMA wal_autocheckpoint = 0

Engineers MUST ALWAYS set 'PRAGMA wal_autocheckpoint = 0;' on all Bun SQLite instances initialized in application workers.

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.