Testing Android Room Database Migrations with Claude Code and MCP

Testing Android Room database migrations and SQLite schema evolutions on physical mobile hardware represents one of the most critical resilience challenges in contemporary Android software engineering. While standard unit tests and local Robolectric suites verify that data access objects execute basic query syntaxes, they cannot evaluate how physical storage controllers, SQLite Write-Ahead Logging checkpoints, and Android OS process interruptions behave during live database upgrades. According to Google Play vitals benchmarks, unexpected SQLite exceptions—such as missing migration paths, column constraint violations, or database corruption during schema alterations—rank among the leading causes of fatal application crashes on startup (FATAL EXCEPTION: main). When an altered schema fails to migrate cleanly on an end-user device, Room throws an unhandled IllegalStateException, immediately terminating the application before the initial UI activity can render. Alternatively, enabling destructive migration fallbacks silently wipes the local database, permanently destroying user session tokens, offline transactional queues, and cached records. By connecting autonomous terminal-based AI coding agents like Claude Code to real Android hardware through ScreenSync MCP, engineering teams can bridge the gap between static schema validation and authentic device storage execution, orchestrating deterministic multi-version upgrades, SQLCipher encryption verification, and zero-data-loss assertions without root privileges.

Key Takeaways

• Physical Storage Grounding: Executing Room migrations on authentic Android hardware evaluates flash storage I/O latency, SQLite page allocation, and OS lock contention that in-memory SQLite mocks cannot replicate.
• Automated Multi-Version Traversal: Claude Code automates complex migration paths (such as migrating directly from v1 to v4 across intermediate schema definitions) to verify backward compatibility for legacy users.
• Zero-Data-Loss Assertions: ScreenSync MCP combines Android accessibility tree extraction via get_ui_hierarchy with low-level ADB inspection, guaranteeing that cached user entities persist post-migration.
• SQLCipher & Keystore Validation: Validate hardware-backed Android Keystore master key unwrap cycles and encrypted SQLite cipher migrations without exposing database secrets.
• Elimination of Destructive Fallbacks: Claude Code detects unintended fallbackToDestructiveMigration() executions by asserting row counts across upgrades, preventing accidental data wipes.
• Unified 226-Tool MCP Ecosystem: ScreenSync MCP provides 29 phone and desktop tools (12 control_*, 3 os_*, and 14 capture/telemetry tools) alongside 197 browser tools to orchestrate full-stack mobile-to-cloud testing pipelines.

The Hidden Risks of Android Database Migrations in Production

Android database migrations risk fatal crashes, silent data corruption, and catastrophic app uninstalls when schema updates fail in production. When an altered entity lacks an explicit migration plan or contains column type mismatches, Room throws unrecoverable runtime exceptions on application startup or silently purges all stored user tables if destructive fallbacks are enabled.

To understand why automated database migration testing must be integrated into continuous deployment pipelines, software architects must examine what occurs beneath the surface of the Android framework when an application updates its SQLite schema. Unlike server-side relational databases where migrations are executed by centralized database administrators during scheduled maintenance windows, mobile database migrations execute in a hostile, decentralized environment. Every individual end-user device runs its own local SQLite instance stored within private app storage.

When a user downloads an updated APK or App Bundle from Google Play, the application version changes, but the local SQLite database file remains on the physical device flash memory. During the subsequent application launch, the Android runtime initializes the singleton RoomDatabase instance. As part of this initialization, Room executes an internal verification sequence against the SQLite master tables. If the database schema version incremented from version 2 to version 3, Room searches its registered migration graph for an executable path connecting those versions.

If the development team modified a Kotlin @Entity data class—such as adding a non-nullable column without providing a default value, changing an index constraint, altering a foreign key cascade rule, or renaming a table—without registering an explicit Migration(2, 3) instance or configuring automated AutoMigration specs, Room immediately throws a fatal exception:

java.lang.IllegalStateException: A migration from 2 to 3 was required but not found. Please provide the necessary MigrationPath via RoomDatabase.Builder.addMigration(Migration ...) or allow for destructive migrations via one of the RoomDatabase.Builder.fallbackToDestructiveMigration* methods.

Because database initialization typically occurs during dependency injection setup inside the custom Application.onCreate() or during the first ViewModel repository access in the launch Activity, this exception crashes the application immediately upon launch. For the user, the app simply flashes a white screen and crashes repeatedly.

To circumvent these crashes, some engineering teams enable fallbackToDestructiveMigration() on their database builders. While this prevents the immediate crash, the cure is often worse than the disease. Destructive migration instructs Room to drop all existing tables, foreign keys, triggers, and indices, creating a completely empty schema from scratch. For an e-commerce, banking, or enterprise field utility app, this wipes unsynced customer carts, cached profile tokens, draft records, and offline event logs. The user is logged out, uncommitted work is lost forever, and negative store reviews follow.

Room Architecture: Schemas, Hashes, and SQLite State Machines

Room architecture validates database integrity by hashing the current entity definition and comparing that hash against the identity hash recorded in the room_master_table SQLite metadata table during database initialization. Any discrepancy between compile-time schemas and on-disk tables immediately aborts initialization with an illegal state exception unless an explicit migration path bridges the versions.

To systematically test database upgrades, developers must understand the internal mechanics of the Room persistence library and how it enforces SQLite schema guarantees. When the Room annotation processor (ksp or kapt) compiles an Android project, it inspects every @Database class and its corresponding @Entity definitions.

1. The Schema JSON Artifact and Identity Hashes

During compilation, Room generates two critical artifacts:

1. Schema JSON Files: If room.schemaLocation is configured in build.gradle.kts, Room outputs a JSON representation of the database schema (e.g., 1.json, 2.json). This file contains the exact table definitions, columns, data affinities (INTEGER, TEXT, REAL, BLOB), nullability flags, foreign key actions, and index definitions, alongside an auto-generated Identity Hash.
2. AppDatabase_Impl.java: The generated implementation class that implements your abstract Room database. Inside this generated class, Room hardcodes two 32-character hexadecimal string constants: mIdentityHash and mLegacyIdentityHash.

// Auto-generated by Room Annotation Processor (excerpt from AppDatabase_Impl.java)
public final class AppDatabase_Impl extends AppDatabase {
    private static final String IDENTITY_HASH = "8f3a9e4b7c1d2e5a6b0c9d8e7f6a5b4c";
    private static final String LEGACY_IDENTITY_HASH = "1a2b3c4d5e6f7a8b9c0d1e2f3a4b5c6d";

    @Override
    protected SupportSQLiteOpenHelper createOpenHelper(DatabaseConfiguration configuration) {
        final SupportSQLiteOpenHelper.Callback _openCallback = new RoomOpenHelper(configuration, new RoomOpenHelper.Delegate(2) {
            @Override
            public void createAllTables(SupportSQLiteDatabase _db) {
                _db.execSQL("CREATE TABLE IF NOT EXISTS `users` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `email` TEXT NOT NULL, `name` TEXT NOT NULL)");
                _db.execSQL("CREATE TABLE IF NOT EXISTS room_master_table (id INTEGER PRIMARY KEY,identity_hash TEXT)");
                _db.execSQL("INSERT OR REPLACE INTO room_master_table (id,identity_hash) VALUES(42, '8f3a9e4b7c1d2e5a6b0c9d8e7f6a5b4c')");
            }
            // ...
        }, "8f3a9e4b7c1d2e5a6b0c9d8e7f6a5b4c", "1a2b3c4d5e6f7a8b9c0d1e2f3a4b5c6d");
        return configuration.sqliteOpenHelperFactory.create(configuration);
    }
}

2. The room_master_table SQLite State Machine

When the database opens on a physical device, RoomOpenHelper executes the following state machine:

• Version Verification: It queries PRAGMA user_version. If user_version == 0, this is a fresh install; Room executes createAllTables() and writes the IDENTITY_HASH into the special internal table room_master_table.
• Migration Execution: If user_version is greater than 0 but less than the compile-time @Database(version = N), Room locates the required migration instances. It opens an atomic SQLite transaction (BEGIN EXCLUSIVE TRANSACTION), executes each migration step sequentially, updates PRAGMA user_version = N, and writes the new IDENTITY_HASH into room_master_table.
• Integrity Validation: After migrations finish, Room queries the actual SQLite schema using PRAGMA table_info, PRAGMA foreign_key_list, and PRAGMA index_list. It computes an identity hash from the live tables and compares it against mIdentityHash. If a single column is missing, has the wrong affinity, or has mismatched nullability, Room rolls back or aborts with:

java.lang.IllegalStateException: Room cannot verify the data integrity. Looks like you've changed schema but forgot to update the version number. You can simply fix this by increasing the version number.
Expected: TableInfo{name='users', columns={id=Column{name='id', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='null'}, email=Column{name='email', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}, phone=Column{name='phone', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}}}
Found: TableInfo{name='users', columns={id=Column{name='id', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='null'}, email=Column{name='email', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}}}

Understanding this exact state machine is vital: Room does not perform fuzzy matching. Every character of the SQL schema definition on disk must match the compiled Kotlin annotation model perfectly.

Why In-Memory Unit Tests Fail to Catch Real Hardware Migration Bugs

In-memory migration unit tests fail to catch real hardware migration bugs because synthetic SQLite drivers do not emulate physical flash storage latency, Write-Ahead Logging checkpoint contention, or OEM disk constraints. Furthermore, in-memory test fixtures cannot evaluate Android Keystore-backed SQLCipher encryption key unlocking, foreground thread ANR timeouts, or real-device process death interruptions during active transactions.

For years, Android documentation has advised engineering teams to rely on MigrationTestHelper in instrumented JUnit test suites. While MigrationTestHelper is an essential first step for basic SQL syntax verification, it creates a dangerous illusion of safety for production systems.

1. In-Memory vs Authentic Flash I/O Latency

In-memory SQLite databases operate entirely within RAM allocated to the test runner process. Memory reads and writes execute with sub-microsecond latency. However, on authentic physical hardware, mobile devices utilize UFS (Universal Flash Storage) or eMMC flash memory chips. Writing large migration datasets—such as copying 50,000 cached records into a newly normalized relational structure—requires extensive physical flash block writes.

If a migration runs on the main thread during initial app launch, a migration that executes in 12 milliseconds in RAM can require 6,200 milliseconds on a budget Android device running low-tier eMMC flash storage. This triggers an immediate Android OS Application Not Responding (ANR) dialog, killing the application before the migration can commit.

2. WAL Checkpoint Contention and Multi-Process Locks

Modern Android applications configure SQLite with Write-Ahead Logging (PRAGMA journal_mode=WAL). In WAL mode, SQLite writes changes to a secondary -wal file instead of altering the main .db file directly. Checkpointing consolidates these changes back into the main database file.

In-memory test helpers use standard Rollback Journals (journal_mode=MEMORY or journal_mode=DELETE), completely bypassing WAL checkpoint state machines. On real devices, if a background WorkManager task, content provider, or Firebase service holds an open read transaction against the old database file while Room attempts an exclusive BEGIN EXCLUSIVE migration transaction, SQLite throws SQLiteDatabaseLockedException. In-memory tests never encounter this lock contention.

3. Keystore-Backed SQLCipher Encryption Failure

In regulated enterprise applications (financial services, healthcare, and security utilities), Room databases are encrypted using SQLCipher. SQLCipher derives its encryption keys from the Android Keystore hardware security module (KeyMint or TEE).

In-memory test suites run on desktop JVMs or standard emulators with mock security providers, where hardware key derivation cannot be replicated. On physical hardware, OEM battery savers or lockscreen states can cause the Android Keystore daemon to delay key unwrap operations. If Room initializes before the Keystore delivers the decrypted passphrase, SQLCipher throws net.sqlcipher.database.SQLiteException: file is not a database, causing instant failure.

For architectural guidance on handling sudden OS termination during active database transactions, explore our deep dive on testing Android process death with Claude Code and MCP.

Step-by-Step Architecture: Connecting Claude Code to Android SQLite via ScreenSync MCP

Connecting Claude Code to Android SQLite subsystems requires pairing the ScreenSync MCP desktop hub with a physical Android device over local network debugging. Claude Code issues non-root ADB commands to install versioned APKs, inject test records, trigger database upgrades, and inspect SQLite files, while native MediaProjection video streaming visually confirms data population without ADB bandwidth contention.

The core architectural advantage of ScreenSync MCP during database migration testing lies in its clean separation between command execution and visual telemetry:

Step 1: Workstation Environment Configuration

Ensure your development workstation runs Node.js 18 or higher, with the Android SDK Platform-Tools (adb) added to your system PATH. Unpack the ScreenSync MCP desktop hub distribution and launch the server:

# On Windows (PowerShell)
cd C:\tools\screensync-hub
.\start-hub.bat

# On macOS / Linux (Terminal)
cd ~/tools/screensync-hub
chmod +x start-hub.sh
./start-hub.sh

The desktop hub initializes on port 3000 and generates your secure local authentication token (SCREEN_SYNC_TOKEN). For comprehensive workstation configuration guidelines, review the official ScreenSync MCP setup guide at /setup.html.

Step 2: Register ScreenSync MCP with Claude Code

In your terminal, register the ScreenSync MCP stdio server with Claude Code using the following CLI command:

claude mcp add screensync node "/path/to/screensync-hub/screensync-hub.js"

For developers utilizing Claude Desktop, register the server within your claude_desktop_config.json:

{
  "mcpServers": {
    "screensync": {
      "command": "node",
      "args": ["/path/to/screensync-hub/screensync-hub.js"],
      "env": {
        "SCREEN_SYNC_TOKEN": "your_authenticated_token_here"
      }
    }
  }
}

(Note: ScreenSync MCP integrates seamlessly with local stdio MCP clients including Claude Code, Claude Desktop, Cursor, and VS Code agent mode. ChatGPT's web app requires remote HTTPS endpoints and does not support local stdio servers).

Step 3: Install the Android App and Connect Real Hardware

1. Install the free ScreenSync MCP Android app from Google Play (package com.screensync.mcp, compatible with Android 7.0 and later).
2. Launch the app and allow it to discover your workstation hub via local mDNS, or scan the console QR code.
3. Tap Start Projection to authorize Android's native MediaProjection stream. This enables real-time visual frame perception at approximately 94 ms median latency over your local LAN. Video frames remain strictly on your local network and are never transmitted to EpsolDev servers. Screenshots explicitly requested by an AI agent during testing are routed to that agent's model provider for inference.
4. Connect the device via USB or enable Wireless Debugging in Developer Options. Confirm connectivity with adb devices. To ensure your physical device is properly calibrated for autonomous command execution, read can Claude Code test Android apps on real mobile devices.

Step 4: The 226-Tool Inspection Arsenal

ScreenSync MCP provides 226 MCP tools:

• 29 Phone & Desktop Tools: 12 mobile device control tools (control_tap, control_type, control_swipe, control_key, control_launch_app), 3 desktop OS tools (os_mouse_click, os_type, os_hotkey), and 14 capture and telemetry tools (get_latest_screenshot, get_ui_hierarchy, get_logcat, record_screen, get_device_status).
• 197 Browser Tools: The web_* suite operates through an authenticated Chrome or Edge browser extension (Manifest V3), allowing Claude Code to cross-reference mobile database migrations with backend cloud sync dashboards in real time. For a full inventory, consult our complete Android MCP tool reference deep dive.

Practical Autonomous QA Workflows: Validating Multi-Version Migrations and SQLCipher

Practical autonomous database migration QA workflows systematically validate incremental and multi-version schema jumps, data transformation fidelity, and cryptographic encryption. Claude Code automates legacy APK installation, populates test datasets, initiates application updates, triggers migrations, and verifies that UI components cleanly render persisted entities without data loss or transaction rollbacks.

Workflow 1: Linear Upgrade (v1 to v2) & Column Addition with Default Value

In this scenario, database version 1 contained a UserEntity with id and name. Version 2 introduces an email column (TEXT NOT NULL DEFAULT '[email protected]') and an avatar_url column (TEXT NULL).

Developer Prompt:
"Claude, install our v1 release APK on the connected Android test phone. Launch the app, populate 5 test user records via UI input, and confirm they persist. Next, install our v2 update APK without clearing app data. Launch the v2 app, trigger the Room migration, and assert that all 5 users render with their existing names and default email addresses. Check logcat for Room integrity verification."

Step 1: Install Baseline APK (Version 1)

Claude Code executes the package installation over ADB:

# Install version 1 APK preserving clean sandbox
adb install -r -d ./build/outputs/apk/debug/app-v1-debug.apk

Step 2: Seed Test Data via Mobile Control Tools

Claude Code invokes control_launch_app with package com.example.screensync.db, then uses control_type and control_tap to create test records:

• User 1: "Alice Smith"
• User 2: "Bob Jones"
• User 3: "Charlie Brown"

Claude Code calls get_ui_hierarchy to assert that all 3 user cards are visible in the main RecyclerView.

Step 3: Install Updated APK (Version 2) Without Data Wipe

Claude Code installs the version 2 build using the -r flag to keep local application data intact:

# Install v2 update over existing installation
adb install -r ./build/outputs/apk/debug/app-v2-debug.apk

Step 4: Launch and Observe Room Migration Execution

Claude Code initiates the updated application and immediately streams logs via get_logcat filtering for Room|SQLite:

adb shell am start -n com.example.screensync.db/.ui.MainActivity

Logcat returns:

RoomOpenHelper: Running migration: Migration{startVersion=1, endVersion=2}
SupportSQLite: Executing SQL: ALTER TABLE `users` ADD COLUMN `email` TEXT NOT NULL DEFAULT '[email protected]'
SupportSQLite: Executing SQL: ALTER TABLE `users` ADD COLUMN `avatar_url` TEXT DEFAULT NULL
RoomOpenHelper: Validating database schema...
RoomOpenHelper: Schema validation successful. Identity hash: 8f3a9e4b7c1d2e5a6b0c9d8e7f6a5b4c

Step 5: Assert Persisted Data Fidelity in UI Hierarchy

Claude Code calls get_ui_hierarchy and inspects the accessibility node tree:

<node class="androidx.recyclerview.widget.RecyclerView" resource-id="com.example.screensync.db:id/recycler_users">
  <node class="android.view.ViewGroup" index="0">
    <node class="android.widget.TextView" resource-id="com.example.screensync.db:id/txt_name" text="Alice Smith" />
    <node class="android.widget.TextView" resource-id="com.example.screensync.db:id/txt_email" text="[email protected]" />
  </node>
</node>

Claude Code confirms zero data loss: all three original users remain intact with their assigned default email values. For more details on structural node validation, see how AI agents use Android UI trees not screenshots.

Workflow 2: Multi-Version Direct Jump (v1 to v4) Skipping Intermediate Releases

A critical production bug arises when users who missed multiple app updates upgrade directly from an ancient version (e.g., v1) to the current release (e.g., v4). Room must execute the entire chain of migration steps: 1 -> 2, 2 -> 3, and 3 -> 4.

Developer Prompt:
"Claude, test a multi-version jump. Install v1, populate seed records, and then directly install v4. Verify that Room executes migrations 1->2, 2->3, and 3->4 in a single atomic transaction. Verify that our new foreign key constraints on the 'orders' table are validated without throwing SQLiteConstraintException."

Step 1: Execute Seed Installation and Populate Complex Entities

Claude Code installs v1, inserts parent user entities, and triggers the update to v4 directly:

adb install -r -d ./build/outputs/apk/debug/app-v1-debug.apk
# Seed data via UI automation...
adb install -r ./build/outputs/apk/debug/app-v4-debug.apk
adb shell am start -n com.example.screensync.db/.ui.MainActivity

Step 2: Validate Atomic Migration Chain in Logcat

Claude Code queries get_logcat and inspects the transaction log:

RoomOpenHelper: Processing migration chain: [Migration{1,2}, Migration{2,3}, Migration{3,4}]
SQLiteDatabase: BEGIN EXCLUSIVE
RoomOpenHelper: Executing migration from 1 to 2...
RoomOpenHelper: Executing migration from 2 to 3...
RoomOpenHelper: Executing migration from 3 to 4...
SupportSQLite: Executing SQL: CREATE TABLE IF NOT EXISTS `orders` (`order_id` TEXT PRIMARY KEY NOT NULL, `user_id` INTEGER NOT NULL, FOREIGN KEY(`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE)
SQLiteDatabase: COMMIT
RoomOpenHelper: Migration chain completed successfully. Updated PRAGMA user_version to 4.

If intermediate migrations were missing (for example, if the developer wrote Migration(1, 2) and Migration(3, 4) but omitted Migration(2, 3)), Room immediately halts, throwing an unhandled exception before corrupting database state.

Workflow 3: Destructive Fallback Detection and Data Preservation Assertions

In this safety audit workflow, Claude Code detects whether any unintended destructive migration occurred by comparing row counts before and after the update.

Developer Prompt:
"Claude, verify that our v3 to v4 update does not trigger destructive fallback under any condition. Count rows in the 'customers' table in v3, perform the upgrade, and assert that row count post-upgrade equals exactly the pre-upgrade count."

Step 1: Query Pre-Upgrade Row Count via SQLite CLI

On debuggable builds, Claude Code queries SQLite directly via ADB shell:

adb shell "run-as com.example.screensync.db sqlite3 /data/data/com.example.screensync.db/databases/app_database.db 'SELECT COUNT(*) FROM customers;'"
# Returns: 42

Step 2: Install Update and Query Post-Upgrade Row Count

Claude Code installs v4, launches the app to trigger migration, and executes the row count query again:

adb install -r ./build/outputs/apk/debug/app-v4-debug.apk
adb shell am start -n com.example.screensync.db/.ui.MainActivity
adb shell "run-as com.example.screensync.db sqlite3 /data/data/com.example.screensync.db/databases/app_database.db 'SELECT COUNT(*) FROM customers;'"
# Returns: 42

If the return value dropped to 0, Claude Code immediately flags a CRITICAL FAULT: fallbackToDestructiveMigration dropped the customer table.

Workflow 4: Encrypted Database (SQLCipher) Keystore Migration and Re-keying

Validating database upgrades on encrypted SQLite instances requires asserting that the SQLCipher PRAGMA key succeeds and that schema transformations execute properly within the encrypted container.

Developer Prompt:
"Claude, test our encrypted SQLCipher Room database migration from v2 to v3. Verify that the Android Keystore provides the encryption passphrase, that the database unlocks successfully, and that the new encrypted index is created without memory leaks."

Claude Code observes the SQLCipher initialization handshake:

SQLCipher: Initializing SQLCipher v4.5.4 with SQLite 3.39.2
SQLCipher: Executing PRAGMA key = '*****'
SQLCipher: Executing PRAGMA cipher_page_size = 4096
SQLCipher: Executing PRAGMA kdf_iter = 256000
RoomOpenHelper: Encrypted database opened successfully.
RoomOpenHelper: Executing Migration{2, 3}...
SupportSQLite: Executing SQL: CREATE INDEX IF NOT EXISTS `index_users_email` ON `users` (`email`)
RoomOpenHelper: Schema validation successful.

Claude Code captures a visual frame via get_latest_screenshot to confirm that the dashboard renders immediately without requesting the user to re-enter a master password.

For offline applications that synchronize local SQLite records with remote servers, check out our comprehensive guide on testing Android offline mode and sync with Claude Code and MCP.

Defensive Kotlin Architecture: Resilient Room Database Setup & Migration Scripts

Defensive Kotlin Room architecture ensures migration resilience through strict schema exports, comprehensive migration test helpers, explicit transaction blocks, and fail-safe database builders that reject destructive migrations in production. Developers isolate table modifications in atomic SQLite transactions, verify foreign key integrity with explicit PRAGMAs, and safeguard user datasets against silent purges.

To build production Android applications that never crash during database schema upgrades, engineering teams must implement defensive architectural patterns. Below is a complete, production-grade Kotlin implementation demonstrating a multi-version Room database with automated AutoMigrations, explicit manual migrations, and defensive builder configurations:

package com.example.screensync.db

import android.content.Context
import androidx.room.*
import androidx.room.migration.AutoMigrationSpec
import androidx.room.migration.Migration
import androidx.sqlite.db.SupportSQLiteDatabase

// ==========================================
// 1. Entity Definitions Across Schema Versions
// ==========================================

@Entity(
    tableName = "users",
    indices = [Index(value = ["email"], unique = true)]
)
data class UserEntity(
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "id")
    val id: Long = 0,

    @ColumnInfo(name = "name")
    val name: String,

    @ColumnInfo(name = "email", defaultValue = "[email protected]")
    val email: String,

    @ColumnInfo(name = "avatar_url", defaultValue = "NULL")
    val avatarUrl: String? = null,

    @ColumnInfo(name = "created_at", defaultValue = "CURRENT_TIMESTAMP")
    val createdAt: Long = System.currentTimeMillis()
)

@Entity(
    tableName = "orders",
    foreignKeys = [
        ForeignKey(
            entity = UserEntity::class,
            parentColumns = ["id"],
            childColumns = ["user_id"],
            onDelete = ForeignKey.CASCADE
        )
    ],
    indices = [Index(value = ["user_id"])]
)
data class OrderEntity(
    @PrimaryKey
    @ColumnInfo(name = "order_id")
    val orderId: String,

    @ColumnInfo(name = "user_id")
    val userId: Long,

    @ColumnInfo(name = "amount_cents")
    val amountCents: Long,

    @ColumnInfo(name = "order_status")
    val orderStatus: String
)

// ==========================================
// 2. Data Access Objects (DAOs)
// ==========================================

@Dao
interface UserDao {
    @Query("SELECT * FROM users ORDER BY id ASC")
    suspend fun getAllUsers(): List<UserEntity>

    @Insert(onConflict = OnConflictStrategy.ABORT)
    suspend fun insertUser(user: UserEntity): Long

    @Query("SELECT COUNT(*) FROM users")
    suspend fun getUserCount(): Int
}

// ==========================================
// 3. Database Class with Auto & Manual Migrations
// ==========================================

@Database(
    entities = [UserEntity::class, OrderEntity::class],
    version = 3,
    exportSchema = true,
    autoMigrations = [
        // Version 2 to 3 handled automatically by Room with rename spec
        AutoMigration(from = 2, to = 3, spec = AppDatabase.AutoMigration2To3Spec::class)
    ]
)
abstract class AppDatabase : RoomDatabase() {

    abstract fun userDao(): UserDao

    @RenameColumn(tableName = "orders", fromColumnName = "status", toColumnName = "order_status")
    class AutoMigration2To3Spec : AutoMigrationSpec

    companion object {
        const val DATABASE_NAME = "screensync_app.db"

        // Manual Migration from Version 1 to Version 2
        // Scenario: Added 'email' (NOT NULL with default) and 'avatar_url' (NULL) to 'users' table
        val MIGRATION_1_2 = object : Migration(1, 2) {
            override fun migrate(db: SupportSQLiteDatabase) {
                // Execute atomic DDL modifications
                db.execSQL(
                    "ALTER TABLE `users` ADD COLUMN `email` TEXT NOT NULL DEFAULT '[email protected]'"
                )
                db.execSQL(
                    "ALTER TABLE `users` ADD COLUMN `avatar_url` TEXT DEFAULT NULL"
                )
                db.execSQL(
                    "ALTER TABLE `users` ADD COLUMN `created_at` INTEGER NOT NULL DEFAULT 0"
                )
                // Add unique index on email
                db.execSQL(
                    "CREATE UNIQUE INDEX IF NOT EXISTS `index_users_email` ON `users` (`email`)"
                )
            }
        }

        // Factory Method with Defensive Production Safeguards
        fun buildDatabase(context: Context, isDebugBuild: Boolean): AppDatabase {
            val builder = Room.databaseBuilder(
                context.applicationContext,
                AppDatabase::class.java,
                DATABASE_NAME
            )
                .addMigrations(MIGRATION_1_2)
                .addCallback(object : RoomDatabase.Callback() {
                    override fun onOpen(db: SupportSQLiteDatabase) {
                        super.onOpen(db)
                        // Enable foreign key constraints in SQLite
                        db.execSQL("PRAGMA foreign_keys = ON;")
                        // Enforce optimal WAL checkpointing
                        db.execSQL("PRAGMA synchronous = NORMAL;")
                    }
                })

            // STRICT DEFENSIVE RULE: Never allow destructive fallback in release builds!
            if (isDebugBuild) {
                // Permitted only in development staging builds to prevent dev lockouts
                // builder.fallbackToDestructiveMigration()
            }

            return builder.build()
        }
    }
}

When managing sensitive user files or backing up SQLite database files, review our guide on testing Android scoped storage with Claude Code and MCP.

Common Pitfalls, OEM File Locks, and WAL Checkpoint Troubleshooting

Common pitfalls in mobile database migration testing include uncommitted Write-Ahead Logging files, SQLite schema syntax discrepancies between SQLite and Room annotations, and background thread lockups on low-end OEM hardware. Resolving these issues requires executing explicit WAL checkpoint PRAGMAs, auditing generated SQLite create statements, and profiling disk I/O latency under simulated device stress.

When automating Room database migrations on physical Android hardware with Claude Code, software engineering teams frequently encounter four technical obstacles:

1. The Phantom Migration Failure: Dangling -wal and -shm Files

A perplexing failure occurs when an automated test pushes a pre-seeded legacy SQLite database (app_database.db) to /data/data/<pkg>/databases/ via ADB, but Room fails to read the seeded records or immediately crashes.

Root Cause: In SQLite Write-Ahead Logging mode, the complete database consists of three files: app_database.db, app_database.db-wal, and app_database.db-shm. If a developer copies only the .db file, any transactions that were committed in the WAL file but not yet checkpointed back into the main database are lost or cause corruption.

Remediation: Before extracting or seeding an SQLite database file over ADB, force an explicit passive or truncate checkpoint:

# Force SQLite to commit all WAL pages back into the main database file
adb shell "run-as com.example.screensync.db sqlite3 /data/data/com.example.screensync.db/databases/app_database.db 'PRAGMA wal_checkpoint(TRUNCATE);'"

Once truncated, the .db file is fully self-contained and can be backed up or restored safely.

2. Discrepancies Between Room Annotations and SQLite DDL

When writing manual migrations, developers frequently make subtle SQL syntax errors that pass SQLite execution but fail Room's post-migration validation:

• Default Value Quotes: In Room, a string default value must be wrapped in quotes in the entity annotation (defaultValue = "'[email protected]'"). If the SQL migration runs DEFAULT [email protected] without quotes, SQLite treats it as a column reference or expression, failing Room's hash match.
• INTEGER vs INT Affinity: SQLite allows INT, INTEGER, TINYINT, BIGINT. But Room expects INTEGER affinity (3). If a migration script runs CREATE TABLE users (id INT PRIMARY KEY), Room flags an identity hash mismatch.

Remediation: Inspect Room's auto-generated SQL in your build directory (build/generated/ksp/debug/resources/). Room outputs the exact SQL query it expects. Copy that exact SQL string directly into your Migration class.

3. OEM Battery Savers Killing Background Migration Workers

On budget devices manufactured by Samsung, Xiaomi, or Oppo, heavy migrations executing inside a CoroutineWorker can be terminated mid-execution if the device screen locks. The vendor power manager detects sustained flash I/O without active user touches and kills the process (killProcessGroup).

Remediation: For large migrations involving hundreds of thousands of records, use an Android foreground service with a clear progress notification (ServiceInfo.FOREGROUND_SERVICE_TYPE_DATA_SYNC). Configure Claude Code to assert that the migration finishes before triggering device lock events.

4. Database Deadlocks During Process Death Restoration

If the operating system kills an app under memory pressure while Room is executing a migration, and the user re-opens the app while SQLite holds a lock, the second instance may encounter SQLiteDatabaseLockedException.

Remediation: Ensure Room database access is governed by a thread-safe singleton, and configure SQLite's busy timeout to allow transient locks to resolve cleanly:

db.execSQL("PRAGMA busy_timeout = 3000;") // Wait up to 3000 ms before throwing locked exception

Security, Privacy, and Network Data Flow Architecture

ScreenSync MCP ensures enterprise security and privacy during database migration testing by keeping all screen capture video and local SQLite data confined to the developer's local area network. Visual frames stream directly from the Android device to the desktop hub on port 3000, and no database contents or video streams ever touch external cloud servers.

Executing autonomous testing on authentic physical smartphones containing real SQLite databases requires rigorous privacy guarantees:

1. Local LAN Frame Isolation

ScreenSync MCP never sends your Android video stream or raw device telemetry to EpsolDev servers or external cloud brokers. The Android app captures screen frames using Android's native MediaProjection API (available on Android 7.0 and later, requiring explicit user consent every session, with zero root and zero ADB overhead). Display frames stream locally over Wi-Fi directly to your workstation hub on port 3000 at a median latency of approximately 94 ms.

2. Model Provider Transmission Transparency

When an AI agent (such as Claude Code) invokes get_latest_screenshot to visually inspect a screen, that specific image frame is transmitted solely to that agent's model provider (e.g., Anthropic) for vision processing. Raw SQLite database files and decrypted database contents are never transmitted across the network unless explicitly queried by the developer in the terminal prompt.

3. Non-Root Security Boundaries

ScreenSync MCP operates strictly within standard Android security boundaries. Screen capture requires no root access, and device control tools (control_tap, control_type, control_swipe, control_launch_app) run via standard ADB debugging over USB or authenticated wireless pairing.

4. Best Practices for Production Data Handling

When testing database migrations with AI agents:

• Use Synthetic Seed Data: Always populate test databases with synthetic fixture data rather than copying live production databases containing real user credentials or personally identifiable information (PII).
• Rotate Staging Encryption Keys: If testing SQLCipher or Android Keystore integrations, use dedicated staging keys that are isolated from production key rings.

Conclusion & Next Steps

Testing Android Room database migrations and SQLite schema upgrades on physical hardware is indispensable for maintaining five-star application reliability and preventing catastrophic launch crashes in production. By combining the autonomous reasoning capabilities of Claude Code with the real-time device control and screen perception of ScreenSync MCP, mobile engineering teams can validate complex multi-version upgrades, eliminate destructive fallbacks, and guarantee zero data loss across millions of devices.

ScreenSync MCP provides everything required to modernize your mobile QA pipeline:

• Download the free ScreenSync MCP Android app on Google Play for your physical test devices.
• Download the desktop hub distribution and review the complete ScreenSync MCP setup guide to connect your workstation in minutes.
• Experience the power of 226 unified MCP tools across mobile hardware and browser automation, transforming how your engineering team builds, tests, and ships Android applications.