Ten hand-written Room migrations and exportSchema = false: auditing an Android app's database history

We're building ReClaim, a minimalist Android launcher that's on Google Play. Almost everything a user sets up in it lives in one Room database: Home favorites, folders, hidden apps and their unlock challenges, blocks, schedules, reminders, daily limits, website rules and the Home to-do list. That's 18 tables at schema version 14.

Every one of those versions after 4 has a hand-written migration, and the database is declared with exportSchema = false. That combination works until it doesn't, and when it doesn't, the user's whole setup is what breaks. This post is an audit of our own migration history: the rules we followed without writing them down, the safety net we removed on purpose, what turning off schema export cost us, and the small script we used to check ten migrations without a single migration test in the repo.

Stack: Kotlin, Jetpack Compose, Hilt, Room 2.6.1 with KSP, minSdk 26, targetSdk 36. Snippets are trimmed from the real code; // ... marks cuts. The backup post covered how the same tables get copied to a file. This one is about how they change shape.

1. What ten migrations look like

All the migrations live as private objects in the Hilt module that builds the database. Two shapes cover nearly all of them. A new feature gets a new table:

/** Adds the per-app daily budgets table without disturbing existing user data. */
private val MIGRATION_4_5 = object : Migration(4, 5) {
    override fun migrate(db: SupportSQLiteDatabase) {
        db.execSQL(
            "CREATE TABLE IF NOT EXISTS `app_budgets` (" +
                "`packageName` TEXT NOT NULL, " +
                "`dailyMinutes` INTEGER NOT NULL, " +
                "`action` TEXT NOT NULL, " +
                "`warnBeforeLimit` INTEGER NOT NULL, " +
                "PRIMARY KEY(`packageName`))",
        )
    }
}

A new option on an existing feature gets a new column:

/** Per-app knobs for the Extreme challenges: follow duration, find boards, watch rounds. */
private val MIGRATION_13_14 = object : Migration(13, 14) {
    override fun migrate(db: SupportSQLiteDatabase) {
        db.execSQL("ALTER TABLE `hidden_apps` ADD COLUMN `followSeconds` INTEGER")
        db.execSQL("ALTER TABLE `hidden_apps` ADD COLUMN `findRounds` INTEGER")
        db.execSQL("ALTER TABLE `hidden_apps` ADD COLUMN `watchRounds` INTEGER")
    }
}

The builder chains all ten:

Room.databaseBuilder(context, ReClaimDatabase::class.java, ReClaimDatabase.NAME)
    .addMigrations(
        MIGRATION_4_5, MIGRATION_5_6, MIGRATION_6_7, MIGRATION_7_8, MIGRATION_8_9,
        MIGRATION_9_10, MIGRATION_10_11, MIGRATION_11_12, MIGRATION_12_13, MIGRATION_13_14,
    )
    .build()

Nothing renames a column, drops a table or rewrites data. That's not luck. It follows from a rule we only noticed once we lined the migrations up.

2. Every new column is nullable, and null means "default"

Look at the ALTER TABLE lines again. Every added column is a bare INTEGER or TEXT, with no NOT NULL. SQLite's ALTER TABLE documentation explains why that matters: "If a NOT NULL constraint is specified, then the column must have a default value other than NULL." So a non-null Kotlin property would have needed a DEFAULT in the migration SQL. Room only puts a default into the tables it creates on a fresh install when the entity asks for one with @ColumnInfo(defaultValue = ...), so we'd also have had to keep that annotation and the migration in sync, or upgraded phones and fresh installs would end up with slightly different tables.

We never had to deal with that, because the entities say what a missing value means:

@Entity(tableName = "hidden_apps")
data class HiddenAppEntity(
    @PrimaryKey val packageName: String,
    val level: Int,
    val challengeType: String? = null,
    // Per-app challenge overrides — null means "use the level-based default". ...
    val holdSeconds: Int? = null,
    // ...
    /** Extreme: seconds the circle must be followed. Null uses 20. */
    val followSeconds: Int? = null,
    // ...
)

The defaults (20 seconds, 8 boards, 4 rounds) live in Kotlin, not in the schema. An existing row picks up the new column as NULL, which already means "the default", so the migration has no data to backfill. The same pattern shows up on reminders.onExpireAction ("null for the default"), app_budgets.weeklyMinutes ("null when no weekly cap is set") and tasks.categoryId. Kotlin's = true on ScheduleEntity.enabled is a constructor default only. It never reaches SQL, which is fine because no migration adds that column.

The habit pays off a second time in the backup file. A backup written before a column existed simply doesn't have that key, and the reader treats "missing" and "null" the same way:

private fun JSONObject.optIntOrNull(key: String): Int? =
    if (!has(key) || isNull(key)) null else optInt(key)

private fun JSONObject.toHiddenEntity() = HiddenAppEntity(
    packageName = getString("packageName"), level = getInt("level"),
    // ...
    followSeconds = optIntOrNull("followSeconds"), findRounds = optIntOrNull("findRounds"),
    // ...
)

On the writing side, put(key, null) doesn't write a null. The org.json docs say "If the value is null, any existing mapping for name is removed." So new and old backups look the same on disk for an unset override. One schema rule, two formats that tolerate it.

3. The safety net we took out

The first commit in the repo already sat at version 7, and its builder ended like this:

.addMigrations(MIGRATION_4_5, MIGRATION_5_6, MIGRATION_6_7)
.fallbackToDestructiveMigration()

The commit that added backup and restore deleted that last line. Room's migration guide spells out both sides of the choice: "If Room can't find a migration path to upgrade an existing database on a device to the current version, an IllegalStateException occurs." The destructive fallback avoids the crash by recreating the tables, which for a launcher means a phone that suddenly forgets every hidden app, block and limit. For an app whose job is self-control, quietly wiping the blocks is about the worst way to fail. So we chose to fail loudly. A crash on open leaves the old database file untouched, and it's a bug we can fix in an update. A wipe can't be undone.

That choice has two edges we hadn't written down:

• Anything older than version 4 has no path. Versions 1 to 3 predate the repo, so we can't say from code whether any installed copy still has one. If it does, it now crashes on open instead of starting empty.
• Downgrades crash too. Nothing handles 14 to 13. That only matters if an older build is installed over a newer one, which for us mostly means development phones.

The guide lists narrower tools for both: fallbackToDestructiveMigrationFrom "to fall back to destructive recreation only when migrating from specific versions", and fallbackToDestructiveMigrationOnDowngrade. We haven't added either yet. A phone still on version 1 to 3 would predate the backup feature, which arrived with version 8, so a targeted wipe there really would lose everything. A launcher that crashes every time it opens is still worse, but that's a decision to make on purpose, not something to leave to whatever the builder does by default.

4. Never edit a shipped migration

Room's default open helper sits on the platform SQLiteOpenHelper, and its onUpgrade reference has two lines worth taping to the monitor. The good news: "This method executes within a transaction. If an exception is thrown, all changes will automatically be rolled back." The warning: "You should NOT modify an existing migration step from version X to X+1 once a build has been released containing that migration step. If a migration step has an error and it runs on a device, the step will NOT re-run itself in the future if a fix is made to the migration step."

We checked whether we'd ever broken that rule. Across every commit that touched the database module, the only removed lines are the old addMigrations(...) call and the fallback. Each migration body is byte-for-byte what it was when it was added. We also checked the other half of the contract: every commit that changed an entity's columns also bumped version.

5. What exportSchema = false cost

@Database(
    entities = [ /* 18 entities */ ],
    version = 14,
    exportSchema = false,
)
abstract class ReClaimDatabase : RoomDatabase() { /* 15 DAOs */ }

The @Database reference says schema export is on by default "but you can disable it for databases when you don't want to keep history of versions (like an in-memory only database)." Ours is the opposite of in-memory, and we turned it off anyway. That shut two doors.

First, auto-migrations. The AutoMigration reference is blunt: "If an auto migration is defined for a database, then androidx.room.Database.exportSchema must be set to true." Most of our ten migrations are exactly the shape Room can generate: a new table, or a new nullable column. We wrote them by hand because we had no schema files to generate them from.

Second, migration tests. The guide's testing section starts with "Migrations are often complex, and an incorrectly defined migration can cause your app to crash," and its MigrationTestHelper works from exported schema files: "For this artifact to work, you must first export your database's schema." Our only unit tests cover usage-time math. Nothing in the repo runs a migration.

There's also a gap we can't fully close later. One commit took the schema from 9 straight to 13 and added four migrations at once, so versions 10, 11 and 12 never existed in any commit. Versions 4 to 6 predate the repo. Even with export switched on today, we can only regenerate schema files for the versions git can still build: 7, 8, 9, 13 and 14.

6. Checking the migrations without a test harness

Room checks a migration by opening the database and comparing the resulting tables with what the entities expect. We approximated that outside Android with a short Python script. For each commit where the version changed, it reads the entity classes from git, builds CREATE TABLE statements the way Room's generated code does (TEXT for strings, INTEGER for Int, Long and Boolean, NOT NULL for non-null properties, the same primary keys), and loads them into an in-memory SQLite database. Then it starts from the version 7 schema, runs the real migration SQL taken from DatabaseModule.kt in order, and compares the two sides at every version a commit can rebuild:

def schema(db):
    out = {}
    for (t,) in db.execute("select name from sqlite_master "
                           "where type='table' and name not like 'sqlite_%'"):
        rows = db.execute(f"pragma table_info(`{t}`)").fetchall()
        out[t] = sorted(r[1:] for r in rows)  # name, type, notnull, default, pk
    return out

# migrated = v7 schema + MIGRATION_7_8 ... MIGRATION_N; fresh = entities at version N
diff = [t for t in set(migrated) | set(fresh) if migrated.get(t) != fresh.get(t)]

All four checkpoints matched: 7 to 8 and 7 to 9 (11 tables), 7 to 13 and 7 to 14 (18 tables). To make sure the comparison could fail at all, we broke the migrations on purpose. Dropping the mathHard column from 7 to 8 showed up as a hidden_apps difference, and making tasks.createdAt nullable in 9 to 10 showed up on tasks.

That's evidence, not proof. The script imitates Room's check rather than running it, so it would miss anything Room compares that we didn't model. We have no indices or foreign keys, so the gap is small today, but it'll grow. It can't see versions 4 to 6 or 10 to 12 on their own. It also checks structure, not data: none of our migrations move data, so there was nothing else to check, but the first one that does will need a real test.

7. What's still open

None of these changes have shipped yet:

• Turn schema export on. From Room 2.6.0 the Room Gradle Plugin can set a schemaDirectory, and the files it writes "should be checked into the repository to be used for validation and auto-migrations." Version 14 becomes the first file. Heads-up if you go looking: the current migration guide shows Room 3 artifacts, so on Room 2.6 follow the 2.6 release notes for the plugin setup.
• Add MigrationTestHelper tests from 14 onward, with real rows inserted before each migration and read back after.
• Decide on versions 1 to 3 and downgrades instead of leaving them as an accidental crash.
• No foreign keys anywhere. tasks.categoryId, folder_apps.folderId and schedule_apps.scheduleId are plain integers. Deletes are kept consistent by repository code, which is fine until two code paths disagree.
• The backup format checks for a key, not a version. Backups carry formatVersion: 2, but the reader never compares that number; it only checks that the file looks like a backup. A future format 3 opened by an older app would be read as if it were format 2.

Takeaways

• Make new columns nullable and let null mean "default". The migration becomes one line, there's nothing to backfill, and every reader of old data, backups included, gets the same rule for free.
• Think hard before removing fallbackToDestructiveMigration. Removing it was right for us, but it turns every gap in your version history into a crash. Know where your gaps are.
• Treat a shipped migration as read-only, and check it with git log -p if you're not sure.
• Leave exportSchema on. It costs a JSON file per version. Turning it off cost us auto-migrations, migration tests and, for a few versions, any way to get those files back.

ReClaim is free on Google Play if you'd like to see what all those tables are for. The hidden-app levels behind most of these columns are explained in our guide on how to hide apps on Android.

This post was drafted with AI assistance and reviewed by the ReClaim team.