Capacitor 7 Background Worker SQLite Concurrency & WAL Isolation Invariant

--- description: Capacitor 7 Background Worker SQLite Concurrency & WAL Isolation Invariant - Enforces architectural separation of SQLite connections between Capacitor 7 UI WebViews and native background workers. Mandates Write-Ahead Logging (WAL) mode, calibrated busy timeouts, and serialized background write queues to eliminate SQLITE_BUSY deadlocks and UI thread starvation. globs: src/**/*.{ts,tsx}, ios/App/App/**/*.swift, android/app/src/main/java/**/*.java alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/capacitor-7-background-worker-sqlite-concurrency-invariant" --- # Capacitor 7 Background Worker SQLite Concurrency & WAL Isolation Invariant > **Severity**: CRITICAL | **Category**: mobile > **Anti-Pattern Warning**: Sharing Unbuffered Single-Connection SQLite Handles Across UI WebViews and Headless Background Workers in Default Rollback Journal Mode ## System Constraints You are a Principal Software Architect enforcing SQLite concurrency invariants in Capacitor 7 hybrid mobile applications. You must mandate strict separation between primary UI WebView database operations and headless native background workers (e.g., Background Fetch, Geolocation Sync, Push Notification handlers). Ensure all connections enforce Write-Ahead Logging (WAL) mode, set explicit busy timeouts, and route background writes through isolated connections or sequential transaction queues to avoid ANR events and fatal SQLITE_BUSY errors. ## Strict Guidelines - Engineers MUST ALWAYS initialize SQLite databases with 'PRAGMA journal_mode = WAL;' and 'PRAGMA synchronous = NORMAL;' upon initial connection setup. - Engineers MUST ALWAYS configure an explicit busy timeout ('PRAGMA busy_timeout = 10000;') on every distinct connection handle before executing queries. - Engineers MUST NOT share a single active SQLiteConnection instance between the main Capacitor WebView thread and headless background tasks. - Background worker operations MUST execute write operations inside serialized transaction blocks and yield execution checkpoints periodically. - Services MUST instantiate a dedicated connection pool identifier for background workers (e.g., 'app_db_background') separate from the primary UI connection identifier ('app_db_ui'). - Components NEVER execute raw bulk insert or sync mutations directly on the UI thread's active database handle. ## Bad vs Good Implementations ### Capacitor SQLite Connection Management in Background Workers #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Reusing the shared UI database connection from background tasks without WAL mode or busy timeouts blocks read operations on the main thread, resulting in catastrophic UI lag, ANRs on Android, and watchdog terminations on iOS. ```typescript import { CapacitorSQLite, SQLiteConnection } from '@capacitor-community/sqlite'; // ANTI-PATTERN: Shared singleton connection without WAL configuration or busy timeout // Causes SQLITE_BUSY deadlocks and freezes the UI thread during background sync export class SyncService { private static sqlite: SQLiteConnection = new SQLiteConnection(CapacitorSQLite); private static sharedDbName = 'production_db'; public static async executeBackgroundSync(payloads: Array<{ id: string; data: string }>): Promise<void> { // Reusing the same connection instance used by UI components const db = await this.sqlite.retrieveConnection(this.sharedDbName, false); // Default rollback journal (DELETE) locks the whole database for the entire loop for (const item of payloads) { await db.run('INSERT OR REPLACE INTO sync_records (id, data) VALUES (?, ?)', [item.id, item.data]); // UI thread attempts to read during this loop will fail with SQLITE_BUSY or trigger ANR } } } ``` #### Production Standard (Correct) > **Standard**: Configures PRAGMA journal_mode = WAL and PRAGMA busy_timeout = 10000 on an isolated connection, allowing concurrent reads from the UI WebView while safely batching background worker writes into an atomic transaction. ```typescript import { CapacitorSQLite, SQLiteConnection, SQLiteDBConnection } from '@capacitor-community/sqlite'; // PRODUCTION INVARIANT: Dedicated background connection handle, WAL mode enabled, // explicit busy_timeout set, and bulk operations atomicized within an isolated transaction. export class SyncService { private static sqlite: SQLiteConnection = new SQLiteConnection(CapacitorSQLite); private static readonly DB_NAME = 'production_db'; private static async getIsolatedBackgroundConnection(): Promise<SQLiteDBConnection> { const isConn = (await this.sqlite.isConnection(this.DB_NAME, false)).result; const db = isConn ? await this.sqlite.retrieveConnection(this.DB_NAME, false) : await this.sqlite.createConnection(this.DB_NAME, false, 'no-encryption', 1, false); if (!(await db.isDBOpen()).result) { await db.open(); } // Invariant: Enforce WAL mode and 10s busy timeout to prevent write-lock starvation await db.execute('PRAGMA journal_mode = WAL;'); await db.execute('PRAGMA synchronous = NORMAL;'); await db.execute('PRAGMA busy_timeout = 10000;'); return db; } public static async executeBackgroundSync(payloads: Array<{ id: string; data: string }>): Promise<void> { const db = await this.getIsolatedBackgroundConnection(); try { // Execute atomic transaction batch to minimize write lock residency const statements = payloads.map((item) => ({ statement: 'INSERT OR REPLACE INTO sync_records (id, data) VALUES (?, ?);', values: [item.id, item.data] })); await db.executeTransaction(statements); } catch (error) { console.error('[SyncService] Background synchronization failed:', error); throw error; } } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Capacitor 7 Background Worker SQLite Concurrency & WAL Isolation Invariant

Capacitor 7 Background Worker SQLite Concurrency & WAL Isolation Invariant defines an industrial-grade behavioral standard for mobile engineering. Enforces architectural separation of SQLite connections between Capacitor 7 UI WebViews and native background workers. Mandates Write-Ahead Logging (WAL) mode, calibrated busy timeouts, and serialized background write queues to eliminate SQLITE_BUSY deadlocks and UI thread 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: Sharing Unbuffered Single-Connection SQLite Handles Across UI WebViews and Headless Background Workers in Default Rollback Journal Mode 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	Sharing Unbuffered Single-Connection SQLite Handles Across UI WebViews and Headless Background Workers in Default Rollback Journal Mode	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;' and 'PRAGMA synchronous = NORMAL;' upon initial connection setup.

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.