KMP SQLDelight SQLite WAL Multi-Threaded Read-Pool and Affinity Invariant

Enforces strict background thread affinity, single-writer confinement, and bounded multi-threaded read-pool leasing in Kotlin Multiplatform (KMP) SQLDelight when operating in SQLite Write-Ahead Logging (WAL) mode. Prevents SQLITE_BUSY, SQLITE_LOCKED, and POSIX thread-affinity deadlocks across concurrent iOS (NativeSqliteDriver) and Android (AndroidSqliteDriver) background coroutines. Guarantees non-blocking concurrent reads while isolating transactional mutations to an explicit serialized dispatcher.

--- description: KMP SQLDelight SQLite WAL Multi-Threaded Read-Pool and Affinity Invariant - Enforces strict background thread affinity, single-writer confinement, and bounded multi-threaded read-pool leasing in Kotlin Multiplatform (KMP) SQLDelight when operating in SQLite Write-Ahead Logging (WAL) mode. Prevents SQLITE_BUSY, SQLITE_LOCKED, and POSIX thread-affinity deadlocks across concurrent iOS (NativeSqliteDriver) and Android (AndroidSqliteDriver) background coroutines. Guarantees non-blocking concurrent reads while isolating transactional mutations to an explicit serialized dispatcher. globs: **/database/**/*.kt, **/db/**/*.kt, **/driver/**/*.kt, **/repository/**/*.kt, **/dao/**/*.kt alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/kmp-sqldelight-wal-read-pool-invariant" --- # KMP SQLDelight SQLite WAL Multi-Threaded Read-Pool and Affinity Invariant > **Severity**: CRITICAL | **Category**: mobile > **Anti-Pattern Warning**: Global Unbounded Driver Access with Unsynchronized Concurrent Reads and Mutating Transactions on Unconfined Coroutine Dispatchers ## System Constraints You are a Principal Mobile Architect and Systems Engineer specializing in Kotlin Multiplatform and low-level SQLite database internals. Your mission is to enforce the KMP SQLDelight SQLite WAL Multi-Threaded Read-Pool Invariant. You must reject any direct un-scoped execution of SQLDelight database operations, unsynchronized transaction blocks executed across Dispatchers.Default or Dispatchers.IO, and improper NativeSqliteDriver instantiation on iOS that lacks explicit maxReaderConnections configuration under WAL mode. You enforce isolated single-threaded write serialization alongside structured read-pool leasing to eliminate SQLITE_BUSY errors. ## Strict Guidelines - Engineers MUST configure SQLite Write-Ahead Logging (WAL) via 'PRAGMA journal_mode=WAL;' and set 'PRAGMA synchronous=NORMAL;' during driver creation or on the initial connection callback. - Engineers MUST configure iOS NativeSqliteDriver with explicit 'DatabaseConfiguration' specifying 'maxReaderConnections' (minimum 2, maximum matching available hardware concurrency) to prevent read starvation. - Engineers MUST confine all mutating transactions and write statements to a dedicated single-threaded CoroutineDispatcher (e.g., `newSingleThreadContext("db-writer")` or a serialized CoroutineDispatcher) to prevent POSIX thread lock contention. - Engineers MUST route concurrent read queries through a bounded read dispatcher or bounded connection pool, ensuring reads never starve or block the designated single-writer thread. - Engineers MUST NOT execute SQLDelight `transaction { ... }` blocks containing long-running I/O, network requests, or coroutine suspension points (`delay`, `suspendCancellableCoroutine`) that hold native SQLite connection mutexes open. - Engineers MUST set an explicit busy timeout (`PRAGMA busy_timeout=5000;`) on both Android and Native platforms to handle transient lock transitions safely. ## Bad vs Good Implementations ### Native Driver Creation and Dispatcher Segregation (Kotlin) #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: NativeSqliteDriver defaults to a single serialized connection without WAL mode. Executing transactions and reads across Dispatchers.Default leads to concurrent access to the same SQLite handle or thread-affinity panics on Kotlin/Native. Concurrent operations yield SQLITE_BUSY or SQLITE_LOCKED. ```kotlin // BAD: Default NativeSqliteDriver creation without WAL configuration or connection pooling. // Unbounded concurrent access from arbitrary coroutines causes SQLITE_BUSY and POSIX lock crashes. val driver = NativeSqliteDriver(AppDatabase.Schema, "app.db") val database = AppDatabase(driver) suspend fun saveUsers(users: List<User>) = withContext(Dispatchers.Default) { // Mutating transaction running on arbitrary thread pool alongside reads database.userQueries.transaction { users.forEach { database.userQueries.insertUser(it.id, it.name) } } } suspend fun getUser(id: String): User? = withContext(Dispatchers.Default) { database.userQueries.selectById(id).executeAsOneOrNull() } ``` #### Production Standard (Correct) > **Standard**: Configures NativeSqliteDriver with extended configuration providing 4 reader connections, busy timeout protection, and WAL compatibility. Isolates mutating writes to a single-threaded dispatcher (`limitedParallelism(1)`), eliminating write contention while allowing up to 4 concurrent background reads via `readDispatcher`. ```kotlin // GOOD: Explicit WAL mode configuration, multi-connection read pool on iOS, and strict dispatcher segregation. fun createiOSDriver(): SqlDriver { val schema = AppDatabase.Schema return NativeSqliteDriver( configuration = DatabaseConfiguration( name = "app.db", version = schema.version.toInt(), create = { connection -> wrapConnection(connection) { schema.create(it) } }, upgrade = { connection, oldVer, newVer -> wrapConnection(connection) { schema.migrate(it, oldVer.toLong(), newVer.toLong()) } }, extendedConfig = DatabaseConfiguration.Extended( foreignKeyConstraints = true, busyTimeout = 5_000, maxReaderConnections = 4 // Dedicated multi-threaded read pool in WAL mode ) ) ) } class DatabaseExecutor(private val database: AppDatabase) { // Single-threaded dispatcher serializes all SQLite write locks @OptIn(ExperimentalCoroutinesApi::class) private val writeDispatcher = Dispatchers.IO.limitedParallelism(1) private val readDispatcher = Dispatchers.IO.limitedParallelism(4) suspend fun <T> write(block: (AppDatabase) -> T): T = withContext(writeDispatcher) { database.transactionWithResult { block(database) } } suspend fun <T> read(block: (AppDatabase) -> T): T = withContext(readDispatcher) { block(database) } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: KMP SQLDelight SQLite WAL Multi-Threaded Read-Pool and Affinity Invariant

KMP SQLDelight SQLite WAL Multi-Threaded Read-Pool and Affinity Invariant defines an industrial-grade behavioral standard for mobile engineering. Enforces strict background thread affinity, single-writer confinement, and bounded multi-threaded read-pool leasing in Kotlin Multiplatform (KMP) SQLDelight when operating in SQLite Write-Ahead Logging (WAL) mode. Prevents SQLITE_BUSY, SQLITE_LOCKED, and POSIX thread-affinity deadlocks across concurrent iOS (NativeSqliteDriver) and Android (AndroidSqliteDriver) background coroutines. Guarantees non-blocking concurrent reads while isolating transactional mutations to an explicit serialized dispatcher. 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: Global Unbounded Driver Access with Unsynchronized Concurrent Reads and Mutating Transactions on Unconfined Coroutine Dispatchers 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	Global Unbounded Driver Access with Unsynchronized Concurrent Reads and Mutating Transactions on Unconfined Coroutine Dispatchers	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 configure SQLite Write-Ahead Logging (WAL) via 'PRAGMA journal_mode=WAL

Engineers MUST configure SQLite Write-Ahead Logging (WAL) via 'PRAGMA journal_mode=WAL;' and set 'PRAGMA synchronous=NORMAL;' during driver creation or on the initial connection callback.

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.