SQLite-Wasm OPFS Multi-Tab Concurrency & SyncAccessHandle Lease Protocol

--- description: SQLite-Wasm OPFS Multi-Tab Concurrency & SyncAccessHandle Lease Protocol - Enforces crash-safe multi-tab SQLite-Wasm persistence over the Origin Private File System (OPFS) without file handle collisions or silent database corruption. Mandates dedicated Web Worker isolation, the SAHPool VFS or Web Lock lease orchestration, and strict validation of cross-origin isolation headers for SharedArrayBuffer atomic primitives. globs: src/**/*.{ts,tsx,js,mjs}, public/**/*.{js,mjs} alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/sqlite-wasm-opfs-multitab-concurrency" --- # SQLite-Wasm OPFS Multi-Tab Concurrency & SyncAccessHandle Lease Protocol > **Severity**: CRITICAL | **Category**: frontend > **Anti-Pattern Warning**: Direct OPFS SyncAccessHandle Acquisition Across Multiple Tabs Causing NoModificationAllowedError and Database Corruption ## System Constraints You are a Principal Software Architect enforcing production invariants for SQLite-Wasm on the Origin Private File System (OPFS). Your role is to prevent cross-tab race conditions, database file corruption, and unhandled `NoModificationAllowedError` exceptions. You must reject any direct OPFS sync-access handle allocation on the main thread or uncoordinated multi-tab instances that fail to use `opfs-sahpool` VFS or Web Locks API (`navigator.locks`) with SharedArrayBuffer synchronization. ## Strict Guidelines - Engineers MUST ALWAYS isolate SQLite-Wasm execution within a dedicated Web Worker; SQLite VFS operations using synchronous access handles MUST NEVER be invoked from the main thread UI context. - Engineers MUST enforce Cross-Origin-Opener-Policy (same-origin) and Cross-Origin-Embedder-Policy (require-corp) headers in deployment configurations to guarantee `self.crossOriginIsolated` is true and `SharedArrayBuffer` is available. - Engineers MUST NOT utilize the standard single-client `'opfs'` VFS when multi-tab read/write concurrency is required; they MUST register and configure the `'opfs-sahpool'` (SyncAccessHandle Pool) VFS or implement a central SharedWorker connection multiplexer. - Engineers MUST ALWAYS implement lock lease arbitration via `navigator.locks.request` with explicit lease timeouts when performing transactional checkpointing or non-pooled OPFS file operations. - Workers MUST catch and cleanly recover from `NoModificationAllowedError` or lock contention timeouts, converting handle exhaustion into structured queue delays rather than throwing uncaught exceptions to caller components. ## Bad vs Good Implementations ### Naive Direct OPFS VFS in Web Worker vs. Atomic SAHPool VFS Configuration #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Instantiating `OpfsDb` directly targets the non-pooled OPFS VFS, which attempts to obtain an exclusive `FileSystemSyncAccessHandle`. When a user opens a second tab or concurrent worker, the browser throws an unrecoverable `NoModificationAllowedError`, terminating the worker and potentially leaving incomplete WAL frames or corrupt journal states. ```typescript import sqlite3InitModule from '@sqlite.org/sqlite-wasm'; // ANTI-PATTERN: Default 'opfs' VFS takes an exclusive lock on the DB file. // Opening this across two tabs immediately throws NoModificationAllowedError // and risks database corruption when locks race. export async function initDatabase(): Promise<any> { const sqlite3 = await sqlite3InitModule({ print: console.log, printErr: console.error, }); if (!('opfs' in sqlite3)) { throw new Error('OPFS VFS is unavailable'); } // Fails on second concurrent tab because SQLite can't acquire an exclusive FileSystemSyncAccessHandle const db = new sqlite3.oo1.OpfsDb('/workspace.sqlite3'); return db; } ``` #### Production Standard (Correct) > **Standard**: This solution validates `self.crossOriginIsolated` before initialization, adopts `installOpfsSAHPoolVfs` to distribute access through pre-allocated sync access handles across concurrent tabs, wraps initialization in `navigator.locks.request` to prevent race conditions during pool provisioning, and configures WAL mode with a 5000ms busy timeout to smoothly resolve contention. ```typescript import sqlite3InitModule, { type Database } from '@sqlite.org/sqlite-wasm'; interface InitOptions { poolName?: string; clearOnInit?: boolean; } export async function initConcurrentOpfsDatabase(options: InitOptions = {}): Promise<Database> { // 1. Invariant: Multi-tab atomic coordination requires crossOriginIsolated and SharedArrayBuffer if (!self.crossOriginIsolated) { throw new Error('Database initialization rejected: Security headers COOP/COEP must be enabled for crossOriginIsolated environment.'); } const sqlite3 = await sqlite3InitModule({ print: (msg: string) => console.debug('[SQLite-Wasm]', msg), printErr: (err: string) => console.error('[SQLite-Wasm:Error]', err), }); const poolName = options.poolName ?? 'prod-sahpool'; // 2. Install SyncAccessHandle Pool VFS which multiplexes handles across tabs via SharedArrayBuffer const poolUtil = await sqlite3.installOpfsSAHPoolVfs({ name: poolName, clearOnInit: options.clearOnInit ?? false, initialCapacity: 6, }); // 3. Acquire atomic lease via Web Locks API to prevent SAH pool initialization races return await navigator.locks.request(`db-lease:${poolName}`, { mode: 'shared' }, async () => { const db = new poolUtil.OpfsSAHPoolDb('/app_persistent_store.db'); // Enable Write-Ahead Logging (WAL) and busy timeout for non-blocking multi-reader concurrency db.exec('PRAGMA journal_mode = WAL;'); db.exec('PRAGMA busy_timeout = 5000;'); db.exec('PRAGMA synchronous = NORMAL;'); return db; }); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: SQLite-Wasm OPFS Multi-Tab Concurrency & SyncAccessHandle Lease Protocol

SQLite-Wasm OPFS Multi-Tab Concurrency & SyncAccessHandle Lease Protocol defines an industrial-grade behavioral standard for frontend engineering. Enforces crash-safe multi-tab SQLite-Wasm persistence over the Origin Private File System (OPFS) without file handle collisions or silent database corruption. Mandates dedicated Web Worker isolation, the SAHPool VFS or Web Lock lease orchestration, and strict validation of cross-origin isolation headers for SharedArrayBuffer atomic primitives. 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: Direct OPFS SyncAccessHandle Acquisition Across Multiple Tabs Causing NoModificationAllowedError and Database Corruption 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	Direct OPFS SyncAccessHandle Acquisition Across Multiple Tabs Causing NoModificationAllowedError and Database Corruption	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-Wasm execution within a dedicated Web Worker

Engineers MUST ALWAYS isolate SQLite-Wasm execution within a dedicated Web Worker; SQLite VFS operations using synchronous access handles MUST NEVER be invoked from the main thread UI context.

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.