Kotlin Multiplatform Room SQLite Atomic Migration Standard

--- description: Kotlin Multiplatform Room SQLite Atomic Migration Standard - Enforces strict transactional atomicity and defensive foreign key management for Room database migrations across Android and iOS targets. Guarantees that any unexpected mid-migration process death rolls back schema changes completely without leaving the client SQLite database in an inconsistent or unrecoverable bricked state. globs: **/commonMain/**/database/**, **/commonMain/**/migration/**, **/commonMain/**/*.kt, **/shared/**/db/** alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/kmp-room-sqlite-atomic-migration-standard" --- # Kotlin Multiplatform Room SQLite Atomic Migration Standard > **Severity**: CRITICAL | **Category**: mobile > **Anti-Pattern Warning**: Non-Transactional Multi-Statement Migrations Leading to Unrecoverable Client Database Corruption ## System Constraints You are a Principal Mobile Architect enforcing absolute atomicity in Kotlin Multiplatform (KMP) Room SQLite schema migrations. In an offline-first mobile environment, process death during DDL operations can corrupt the schema and cause permanent crash loops upon restart. You must ensure all migrations are executed inside explicit transactions, handle PRAGMA foreign_keys outside DDL transactions per SQLite specifications, avoid mutating production data without idempotency safeguards, and validate schema snapshots rigorously. ## Strict Guidelines - Engineers MUST ALWAYS wrap non-Room-managed raw SQLite statements within an explicit `execSQL("BEGIN TRANSACTION")` / `execSQL("COMMIT")` block with `execSQL("ROLLBACK")` in the exception path if using low-level SQLite drivers, or rely on Room's `Migration` contracts where `useConnection` or `SupportSQLiteDatabase.beginTransaction()` is mandated. - Engineers MUST NOT disable foreign key constraints (`PRAGMA foreign_keys = OFF`) inside an active transaction, as SQLite ignores foreign key pragma adjustments once a transaction has started. - Engineers MUST ALWAYS implement table rebuilds following the 12-step SQLite table alteration procedure: create new temporary table, copy data, drop old table, rename temporary table to target name, and recreate indexes/triggers. - Code MUST verify table integrity and foreign key constraints via `PRAGMA foreign_key_check` before committing destructive or structural data migrations. - Services MUST never rely on `fallbackToDestructiveMigration()` in production builds without an explicit, remote-controlled killswitch or diagnostic opt-in. - Components NEVER execute long-running migrations synchronously on the main UI dispatcher; migrations must be wired via appropriate background dispatchers during initialization. ## Bad vs Good Implementations ### Table Schema Alteration Migration in KMP Room #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: The migration performs table replacement sequentially without verifying foreign keys, fails to account for transactional state when invoking PRAGMA commands, and leaves the database unrecoverable if OS kills the app process after DROP TABLE. ```kotlin import androidx.room.migration.Migration import androidx.sqlite.SQLiteConnection import androidx.sqlite.execSQL val MIGRATION_1_2 = object : Migration(1, 2) { override fun migrate(connection: SQLiteConnection) { // ANTI-PATTERN: Executing non-atomic multi-step schema mutations // If process dies between DROP and RENAME, user data is irrecoverably lost connection.execSQL("PRAGMA foreign_keys = OFF;") // Ineffective if driver already opened transaction connection.execSQL("CREATE TABLE users_new (id INTEGER PRIMARY KEY NOT NULL, name TEXT NOT NULL, email TEXT NOT NULL DEFAULT '')") connection.execSQL("INSERT INTO users_new (id, name) SELECT id, name FROM users") connection.execSQL("DROP TABLE users") // Mid-point failure here permanently destroys the users table connection.execSQL("ALTER TABLE users_new RENAME TO users") connection.execSQL("PRAGMA foreign_keys = ON;") } } ``` #### Production Standard (Correct) > **Standard**: Executes standard SQLite table modernization defensively with temporary table staging, restores indexes, and validates referential constraints via PRAGMA foreign_key_check, guaranteeing atomic rollback by the underlying connection harness on error. ```kotlin import androidx.room.migration.Migration import androidx.sqlite.SQLiteConnection import androidx.sqlite.execSQL import androidx.sqlite.use val MIGRATION_1_2 = object : Migration(1, 2) { override fun migrate(connection: SQLiteConnection) { // SQLite requires foreign_keys modification outside an active transaction // In Room KMP SQLiteConnection, migrate() is executed inside a transaction by default. // We implement an atomic table migration preserving referential integrity. connection.execSQL(""" CREATE TABLE IF NOT EXISTS `users_temp` ( `id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `name` TEXT NOT NULL, `email` TEXT NOT NULL DEFAULT '' ) "".trimIndent()) connection.execSQL(""" INSERT INTO `users_temp` (`id`, `name`) SELECT `id`, `name` FROM `users` "".trimIndent()) connection.execSQL("DROP TABLE `users`") connection.execSQL("ALTER TABLE `users_temp` RENAME TO `users`") connection.execSQL("CREATE INDEX IF NOT EXISTS `index_users_email` ON `users` (`email`)") // Verify foreign key integrity before transaction commits connection.prepare("PRAGMA foreign_key_check").use { stmt -> if (stmt.step()) { throw IllegalStateException("Foreign key violation detected during MIGRATION_1_2") } } } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Kotlin Multiplatform Room SQLite Atomic Migration Standard

Kotlin Multiplatform Room SQLite Atomic Migration Standard defines an industrial-grade behavioral standard for mobile engineering. Enforces strict transactional atomicity and defensive foreign key management for Room database migrations across Android and iOS targets. Guarantees that any unexpected mid-migration process death rolls back schema changes completely without leaving the client SQLite database in an inconsistent or unrecoverable bricked state. 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: Non-Transactional Multi-Statement Migrations Leading to Unrecoverable Client 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	Non-Transactional Multi-Statement Migrations Leading to Unrecoverable Client 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 wrap non-Room-managed raw SQLite statements within an explicit `execSQL("BEGIN TRANSACTION")` / `execSQL("COMMIT")` block with `execSQL("ROLLBACK")` in the exception path if using low-level SQLite drivers, or rely on Room's `Migration` contracts where `useConnection` or `SupportSQLiteDatabase.beginTransaction()` is mandated.

Engineers MUST ALWAYS wrap non-Room-managed raw SQLite statements within an explicit `execSQL("BEGIN TRANSACTION")` / `execSQL("COMMIT")` block with `execSQL("ROLLBACK")` in the exception path if using low-level SQLite drivers, or rely on Room's `Migration` contracts where `useConnection` or `SupportSQLiteDatabase.beginTransaction()` is mandated.

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.