Bun 1.2 SQLite WAL Concurrency & Native S3 Streaming Invariant

--- description: Bun 1.2 SQLite WAL Concurrency & Native S3 Streaming Invariant - Enforces strict concurrency and zero-copy data flow standards for Bun 1.2 applications using native bun:sqlite and Bun.s3. Forbids executing asynchronous network I/O or S3 streaming within synchronous SQLite WAL transaction boundaries, mandates optimal WAL pragmas, and enforces end-to-end memory-efficient streaming. globs: src/**/*.ts, src/**/*.service.ts, src/**/*.repository.ts, lib/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/bun-sqlite-wal-s3-native-storage-standard" --- # Bun 1.2 SQLite WAL Concurrency & Native S3 Streaming Invariant > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Interleaving Async S3 Network I/O Inside SQLite Write Transactions Causing Database Lock Starvation ## System Constraints You are a Principal Systems Architect specializing in Bun 1.2 runtimes, bun:sqlite internals, and high-throughput S3 cloud-native backends. Enforce strict isolation between synchronous SQLite WAL transactions and asynchronous network calls (Bun.s3). Guarantee zero-copy query patterns, reuse of prepared statements, and streaming-first S3 data transfers without intermediate buffer allocations. ## Strict Guidelines - Engineers MUST ALWAYS initialize SQLite databases with 'PRAGMA journal_mode = WAL;', 'PRAGMA synchronous = NORMAL;', and 'PRAGMA busy_timeout = 5000;' to avoid reader-writer concurrency deadlocks. - Engineers MUST NOT execute any asynchronous operations, network calls, or Bun.s3 uploads inside bun:sqlite db.transaction() blocks; SQLite transactions are synchronous and holding locks during network I/O causes fatal lock starvation. - Code MUST reuse pre-compiled Statement objects (db.prepare) for hot query paths rather than issuing inline db.query() or db.run() calls that force repeated AST parsing and compilation. - Services MUST stream request bodies directly to Bun.s3 using ReadableStream or native File blobs; engineers MUST NEVER load full payloads into memory using await file.arrayBuffer() or Buffer.from() before dispatching to S3. - Components NEVER execute raw string interpolations in SQL; all parameter bindings MUST use positional (?, ?2) or named ($name) parameters to guarantee query cache hits and eliminate SQL injection vectors. - Services using two-phase operations (SQLite state metadata + S3 file upload) MUST follow an Outbox or Pending-Verify pattern: record state as 'pending', complete the S3 stream, and then synchronously commit 'finalized' state. ## Bad vs Good Implementations ### Decoupling S3 Uploads from SQLite Write Transactions & Direct Streaming #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Executing asynchronous network I/O (S3 upload) within a synchronous SQLite transaction stalls the global write-ahead lock, causing immediate SQLITE_BUSY deadlocks for concurrent operations. Buffering the entire stream via arrayBuffer() exhausts memory under load, and inline SQL interpolation introduces injection and statement recompilation overhead. ```typescript import { Database } from "bun:sqlite"; import { s3 } from "bun"; const db = new Database("app.db"); export async function handleFileUpload(id: string, stream: ReadableStream) { // ANTI-PATTERN: Async network I/O and full RAM buffering inside a SQLite transaction lock const tx = db.transaction(async () => { const fullBuffer = await new Response(stream).arrayBuffer(); // Buffers entire file in RAM await s3.write(`artifacts/${id}.bin`, fullBuffer); // Stalls SQLite write lock during network I/O db.query(`UPDATE artifacts SET status = 'ready' WHERE id = '${id}'`).run(); // Uncached query with string interpolation }); await tx(); } ``` #### Production Standard (Correct) > **Standard**: PRAGMA journal_mode = WAL ensures readers and writers do not block each other. S3 streaming consumes the ReadableStream directly with zero intermediate array allocations. Database modifications are segregated into atomic, synchronous prepared statement executions, keeping SQLite write transactions sub-millisecond and immune to network latency. ```typescript import { Database, Statement } from "bun:sqlite"; import { s3 } from "bun"; const db = new Database("app.db", { create: true }); // Mandatory WAL optimizations db.exec("PRAGMA journal_mode = WAL;"); db.exec("PRAGMA synchronous = NORMAL;"); db.exec("PRAGMA busy_timeout = 5000;"); db.exec("PRAGMA foreign_keys = ON;"); // Pre-compiled parameterized statements const setArtifactPending: Statement<void, [id: string]> = db.prepare( "INSERT INTO artifacts (id, status) VALUES (?1, 'pending') ON CONFLICT(id) DO UPDATE SET status = 'pending'" ); const setArtifactReady: Statement<void, [id: string]> = db.prepare( "UPDATE artifacts SET status = 'ready' WHERE id = ?1" ); export async function handleFileUpload(id: string, stream: ReadableStream): Promise<void> { // Step 1: Fast, isolated synchronous metadata initialization setArtifactPending.run(id); try { // Step 2: Zero-copy direct streaming to S3 without intermediate RAM buffering await s3.write(`artifacts/${id}.bin`, stream); // Step 3: Fast, synchronous status update outside network boundary setArtifactReady.run(id); } catch (error) { db.prepare("UPDATE artifacts SET status = 'failed' WHERE id = ?1").run(id); throw error; } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Bun 1.2 SQLite WAL Concurrency & Native S3 Streaming Invariant

Bun 1.2 SQLite WAL Concurrency & Native S3 Streaming Invariant defines an industrial-grade behavioral standard for backend engineering. Enforces strict concurrency and zero-copy data flow standards for Bun 1.2 applications using native bun:sqlite and Bun.s3. Forbids executing asynchronous network I/O or S3 streaming within synchronous SQLite WAL transaction boundaries, mandates optimal WAL pragmas, and enforces end-to-end memory-efficient streaming. 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: Interleaving Async S3 Network I/O Inside SQLite Write Transactions Causing Database Lock Starvation 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	Interleaving Async S3 Network I/O Inside SQLite Write Transactions Causing Database Lock Starvation	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 SQLite databases with 'PRAGMA journal_mode = WAL

Engineers MUST ALWAYS initialize SQLite databases with 'PRAGMA journal_mode = WAL;', 'PRAGMA synchronous = NORMAL;', and 'PRAGMA busy_timeout = 5000;' to avoid reader-writer concurrency deadlocks.

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.