KMP Atomic SQLite WAL Re-Keying & Hardware-Backed Key Rotation Standard

--- description: KMP Atomic SQLite WAL Re-Keying & Hardware-Backed Key Rotation Standard - Enforces atomic, crash-resilient cryptographic key rotation for SQLCipher and SQLite databases operating in WAL mode across Android Keystore and iOS Secure Enclave/Keychain. Mandates full WAL truncation checkpoints, connection pool evacuation, and two-phase key state markers to prevent unrecoverable database corruption and torn-key reads. globs: **/database/**/*.kt, **/storage/**/*.kt, **/security/**/*.kt, **/crypto/**/*.kt alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/kmp-atomic-sqlite-wal-rekeying-standard" --- # KMP Atomic SQLite WAL Re-Keying & Hardware-Backed Key Rotation Standard > **Severity**: CRITICAL | **Category**: mobile > **Anti-Pattern Warning**: Executing in-place PRAGMA rekey on active WAL connections without checkpoint truncation or atomic key-phase synchronization. ## System Constraints You are a Principal Mobile Systems Architect enforcing the KMP Atomic SQLite WAL Re-Keying Standard. In Kotlin Multiplatform applications using SQLCipher/SQLite in Write-Ahead Logging (WAL) mode, re-keying is inherently non-atomic across process crashes if WAL frames remain unmerged or connection pools remain open. You must enforce strict connection drainage, explicit WAL checkpoint truncation (TRUNCATE), hardware keystore two-phase state commitment, and rollback protection. ## Strict Guidelines - Engineers MUST ALWAYS execute a synchronous `PRAGMA wal_checkpoint(TRUNCATE)` prior to calling `PRAGMA rekey` or attaching migration tables to eliminate residual WAL frames encrypted under the old key. - Engineers MUST NOT execute re-keying operations on shared connection pools or active background coroutines; all reader and writer connections MUST be drained and locked behind an exclusive migration mutex. - Code SHOULD verify database integrity via `PRAGMA quick_check` immediately following the re-keying operation and before committing the new key alias as active in hardware storage. - Services MUST persist a two-phase rotation token (`REKEY_IN_PROGRESS` vs `REKEY_COMMITTED`) in Android KeyStore (SharedPreferences backed by EncryptedSharedPreferences/MasterKey) and iOS Keychain with `kSecAttrAccessibleAfterFirstUnlockThisDeviceOnly`. - Components NEVER expose SQLite raw memory keys or retain unencrypted byte arrays in KMP heap allocations beyond the scoped cryptographic execution block. ## Bad vs Good Implementations ### Unsafe in-place WAL re-keying vs. atomic two-phase checkpointed rotation #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Executing PRAGMA rekey directly on an active WAL database causes torn writes. If background threads write frames or if the application crashes before checkpointing, WAL frames encrypted with the old key cannot be recovered by the new key, causing irreversible database corruption. ```kotlin class DatabaseRekeyService(private val db: SQLiteDatabase, private val keyProvider: KeyProvider) { // ANTI-PATTERN: Re-keying while active WAL frames and pooled connections exist fun rotateDatabaseKey() { val newKey = keyProvider.generateAndStoreNewKey() // Immediately replaces old key // Throws or corrupts if background threads are writing to WAL frames db.execSQL("PRAGMA rekey = '${newKey.toHex()}';") // If the process crashes here, WAL frames are encrypted under oldKey, but master record has newKey } } ``` #### Production Standard (Correct) > **Standard**: Acquires an exclusive lock on the connection pool, truncates the WAL file completely, records a pending rotation state in hardware-backed storage, executes the re-keying, verifies database integrity, and zeroizes key buffers in memory. ```kotlin class DatabaseRekeyService( private val pool: DatabaseConnectionPool, private val keyManager: HardwareKeyManager ) { suspend fun rotateKeySafely(): Result<Unit> = pool.withExclusiveLock { conn -> val (oldKey, candidateKey) = keyManager.prepareCandidateKey() try { // 1. Drain & truncate WAL file to prevent mixed-key frames conn.rawQuery("PRAGMA wal_checkpoint(TRUNCATE);", null).use { it.moveToFirst() } conn.execSQL("PRAGMA locking_mode = EXCLUSIVE;") // 2. Mark hardware rotation state as PENDING keyManager.markRotationPending(candidateKey.alias) // 3. Atomically rekey conn.execSQL("PRAGMA rekey = ?", arrayOf(candidateKey.rawBytes)) // 4. Validate page integrity conn.rawQuery("PRAGMA quick_check;", null).use { cursor -> if (!cursor.moveToFirst() || cursor.getString(0) != "ok") { throw SQLiteException("Integrity check failed post-rekey") } } // 5. Finalize state in Hardware Keystore / Secure Enclave keyManager.commitKeyPromotion(candidateKey.alias) Result.success(Unit) } catch (e: Throwable) { keyManager.rollbackCandidateKey(candidateKey.alias) Result.failure(e) } finally { conn.execSQL("PRAGMA locking_mode = NORMAL;") candidateKey.rawBytes.fill(0.toByte()) // Zeroize sensitive memory } } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: KMP Atomic SQLite WAL Re-Keying & Hardware-Backed Key Rotation Standard

KMP Atomic SQLite WAL Re-Keying & Hardware-Backed Key Rotation Standard defines an industrial-grade behavioral standard for mobile engineering. Enforces atomic, crash-resilient cryptographic key rotation for SQLCipher and SQLite databases operating in WAL mode across Android Keystore and iOS Secure Enclave/Keychain. Mandates full WAL truncation checkpoints, connection pool evacuation, and two-phase key state markers to prevent unrecoverable database corruption and torn-key reads. 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: Executing in-place PRAGMA rekey on active WAL connections without checkpoint truncation or atomic key-phase synchronization. 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	Executing in-place PRAGMA rekey on active WAL connections without checkpoint truncation or atomic key-phase synchronization.	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 execute a synchronous `PRAGMA wal_checkpoint(TRUNCATE)` prior to calling `PRAGMA rekey` or attaching migration tables to eliminate residual WAL frames encrypted under the old key.

Engineers MUST ALWAYS execute a synchronous `PRAGMA wal_checkpoint(TRUNCATE)` prior to calling `PRAGMA rekey` or attaching migration tables to eliminate residual WAL frames encrypted under the old key.

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.