Android Room and DataStore Interview Questions

Room is Jetpack's database library — a thin, type-safe layer on top of SQLite. It generates boilerplate SQL code at compile time, validates your queries, and integrates with coroutines and Flow out of the box. Think of it as SQLite with all the hard parts handled for you.

// Raw SQLite — manual, error-prone, no type safety
val db = openOrCreateDatabase("users.db", Context.MODE_PRIVATE, null)
db.execSQL("CREATE TABLE users (id TEXT, name TEXT)")
val cursor = db.rawQuery("SELECT * FROM users WHERE id = ?", arrayOf(id))
// Must manually close cursor, no compile-time query validation

// Room — type-safe, compile-validated, coroutine-friendly

// 1. Entity — maps to a database table
@Entity(tableName = "users")
data class UserEntity(
    @PrimaryKey val id: String,
    val name: String,
    val email: String
)

// 2. DAO — your query interface
@Dao
interface UserDao {
    @Query("SELECT * FROM users WHERE id = :id")
    suspend fun getUser(id: String): UserEntity?  // SQL validated at BUILD time

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun upsert(user: UserEntity)

    @Query("SELECT * FROM users ORDER BY name")
    fun observeAll(): Flow<List<UserEntity>>  // live updates via Flow
}

// 3. Database — ties it together
@Database(entities = [UserEntity::class], version = 1)
abstract class AppDatabase : RoomDatabase() {
    abstract fun userDao(): UserDao
}

Room's most important advantage over raw SQLite is compile-time SQL validation. A typo in a column name or a wrong table reference in a @Query annotation causes a build error, not a runtime crash that reaches users. Raw SQLite executes query strings at runtime — a missing closing parenthesis or misspelled column name only manifests when that code path is executed, potentially in production. Room's annotation processor parses every SQL string against the actual schema at build time, turning a class of production bugs into build failures.

Type safety eliminates the raw Cursor API entirely. Instead of manually calling cursor.getString(cursor.getColumnIndexOrThrow("name")) and handling every column extraction, Room DAO methods return typed Kotlin objects: suspend fun getUser(id: Long): UserEntity?. Room generates all the Cursor extraction and ContentValues construction code. The generated code is correct by construction — the types match the schema, null handling is explicit, and there is no opportunity for column index mismatches.

Flow integration makes Room reactive by default. A DAO method returning Flow<List<UserEntity>> emits a new list every time the underlying table changes — an insert, update, or delete from any coroutine or thread triggers a fresh emission. The UI collects this flow via the ViewModel and updates automatically, with no manual notifyDataSetChanged(), no polling, and no event bus. Testing is also first-class: Room.inMemoryDatabaseBuilder() creates a fresh, isolated database in memory, runnable on the JVM without a device or emulator, cleared automatically after the test.

DAO (Data Access Object) is the interface that defines how you interact with the database. Room generates the implementation — you just declare what you want to do, and Room figures out the SQL.

@Dao
interface ProductDao {

    // INSERT — add a new row
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insert(product: ProductEntity)

    @Insert
    suspend fun insertAll(products: List<ProductEntity>)

    // UPDATE — modify existing row (matches by @PrimaryKey)
    @Update
    suspend fun update(product: ProductEntity)

    // DELETE — remove a row
    @Delete
    suspend fun delete(product: ProductEntity)

    // @Query — full SQL power for reads and writes
    @Query("SELECT * FROM products WHERE id = :id")
    suspend fun getById(id: String): ProductEntity?

    @Query("SELECT * FROM products WHERE category = :cat ORDER BY price ASC")
    fun observeByCategory(cat: String): Flow<List<ProductEntity>>

    @Query("DELETE FROM products WHERE lastUpdated < :cutoff")
    suspend fun deleteOlderThan(cutoff: Long)

    // Upsert pattern (Room 2.5+)
    @Upsert
    suspend fun upsert(product: ProductEntity)

    // OnConflict strategies:
    // REPLACE → delete old row, insert new  (most common for cache)
    // IGNORE  → skip if already exists     (idempotent inserts)
    // ABORT   → throw exception             (default)
}

@Insert generates an INSERT INTO statement for one entity or a list of entities. The onConflict parameter determines what happens when the primary key already exists. OnConflictStrategy.REPLACE deletes the existing row and inserts the new one — the most useful strategy for cache and sync patterns where you want to overwrite stale data. OnConflictStrategy.IGNORE silently skips inserts that would violate a constraint — useful for deduplication. @Update generates an UPDATE statement that matches on the primary key and updates all other fields. If the row does not exist, the update is a no-op — it does not insert.

@Delete generates a DELETE statement that matches on the primary key of the passed entity. You pass the entity object, not the ID — Room extracts the primary key from the entity's annotated field. For batch deletes based on a condition rather than an entity, use @Query("DELETE FROM users WHERE status = :status"). @Query is the escape hatch for anything the convenience annotations cannot express: complex WHERE clauses, JOINs, aggregations, sub-selects, or any SQL that returns a non-entity type like a count or sum.

@Upsert, added in Room 2.5, is a first-class insert-or-update operation. It inserts the entity if the primary key does not exist, and updates it if it does — exactly the semantics of @Insert(onConflict = REPLACE) but without the delete-and-reinsert behaviour that can cause issues with @ForeignKey CASCADE rules. The underlying SQL uses INSERT OR REPLACE on devices running SQLite 3.24+ and falls back to manual logic on older versions. For most offline-first sync patterns where you receive a server record and want to persist it regardless of whether it exists locally, @Upsert is the correct annotation.

A DAO that returns Flow automatically emits a new value whenever the underlying table changes. This creates a reactive pipeline from database → ViewModel → UI with no manual refresh needed.

// DAO — return Flow for reactive observation
@Dao
interface OrderDao {
    @Query("SELECT * FROM orders ORDER BY createdAt DESC")
    fun observeOrders(): Flow<List<OrderEntity>>
    // NOT suspend — Flow is cold, emits on every table change
}

// Repository — exposes domain models, not entities
class OrderRepository @Inject constructor(private val dao: OrderDao) {
    fun observeOrders(): Flow<List<Order>> =
        dao.observeOrders().map { entities -> entities.map { it.toDomain() } }
}

// ViewModel — converts Flow to StateFlow for UI
@HiltViewModel
class OrderViewModel @Inject constructor(repo: OrderRepository) : ViewModel() {
    val orders = repo.observeOrders()
        .stateIn(viewModelScope, SharingStarted.WhileSubscribed(5000), emptyList())
}

// Compose — collects with lifecycle awareness
val orders by vm.orders.collectAsStateWithLifecycle()

// Flow vs LiveData for Room:
// Flow:
// ✅ Works in non-Android modules (pure Kotlin)
// ✅ Powerful operators: map, filter, flatMapLatest, combine
// ✅ Coroutine-native — backpressure, cancellation built in
// ✅ No lifecycle coupling in Room — ViewModel handles that
// LiveData:
// ⚠️ Android-only — can't use in domain layer
// ⚠️ Limited operators — no flatMapLatest etc.
// ✅ Auto lifecycle-aware out of the box (but Compose doesn't need this)

A DAO method that returns Flow<T> is not a suspend function — it returns immediately with a cold flow. Room's invalidation tracker observes the queried tables and triggers a new emission every time any row in those tables changes. A single insert, update, or delete — from any coroutine, any thread — automatically causes the flow to re-query and emit the fresh result. This reactive pipeline means the UI never needs to manually request a refresh: the database is the single source of truth and the flow propagates every change.

The ViewModel converts the cold database flow into a hot StateFlow using stateIn(): val users = userDao.observeAll().stateIn(viewModelScope, SharingStarted.WhileSubscribed(5000), emptyList()). stateIn starts collecting the upstream flow and caches the latest value. The UI simply reads viewModel.users.collectAsStateWithLifecycle() and gets the current list — always the latest, always up to date. The WhileSubscribed(5000) policy stops upstream collection 5 seconds after the last UI collector disappears (the app is backgrounded), freeing the database observer and the associated resources.

Flow is preferred over LiveData for new code for several reasons. Flow works in pure Kotlin modules that have no Android dependency — the domain layer and data layer can use Flow without importing androidx.lifecycle. Flow provides a richer operator set: map, filter, combine, flatMapLatest, debounce — all composable without leaving the coroutines ecosystem. LiveData requires switchMap and MediatorLiveData for equivalent transformations. Flow also integrates natively with Compose's collectAsStateWithLifecycle(), making the data pipeline from Room to UI entirely coroutine-based.

When you change your database schema (add a column, rename a table), you must provide a migration path. Without one, Room throws an exception — or worse, destroys all user data.

// Bump version number when schema changes
@Database(entities = [UserEntity::class], version = 2)  // was 1
abstract class AppDatabase : RoomDatabase()

// Migration 1 → 2: add a 'phone' column to users table
val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(db: SupportSQLiteDatabase) {
        db.execSQL("ALTER TABLE users ADD COLUMN phone TEXT")
    }
}

// Migration 2 → 3: add an index for faster queries
val MIGRATION_2_3 = object : Migration(2, 3) {
    override fun migrate(db: SupportSQLiteDatabase) {
        db.execSQL("CREATE INDEX IF NOT EXISTS idx_users_email ON users(email)")
    }
}

// Register migrations on the builder
val db = Room.databaseBuilder(context, AppDatabase::class.java, "app.db")
    .addMigrations(MIGRATION_1_2, MIGRATION_2_3)  // chain of migrations
    .build()
// Room auto-selects the right path: v1→v3 runs MIGRATION_1_2 then MIGRATION_2_3

// FALLBACK — destroys all data (dev builds only!)
Room.databaseBuilder(...)
    .fallbackToDestructiveMigration()  // ❌ NEVER in production
    .build()

// AutoMigration (Room 2.4+) — for simple schema changes
@Database(version = 4, autoMigrations = [
    AutoMigration(from = 3, to = 4)  // Room generates it for simple adds
])

Every schema change — adding a column, renaming a table, changing a column type — requires incrementing the version number in @Database(version = N). Room detects the version mismatch between the installed database and the current schema when the database is first opened on an upgraded app. Without a registered migration for that version transition, Room throws an IllegalStateException at startup — the most common and most damaging production storage bug. Always pair a version bump with a Migration object that transforms the schema from the previous version to the new one.

A Migration(fromVersion, toVersion) object's migrate(database) method receives a SupportSQLiteDatabase and executes the SQL to transform the schema: database.execSQL("ALTER TABLE users ADD COLUMN phone TEXT"). Migrations chain automatically: a user on version 1 who skips straight to version 3 runs the 1→2 migration followed by the 2→3 migration in sequence. You never need to write a combined 1→3 migration as long as each step is registered. Room orchestrates the chain on the background thread before handing the database to the app.

fallbackToDestructiveMigration() tells Room to drop and recreate all tables when no migration is registered — effectively wiping all user data. This is acceptable during development when the schema changes frequently and data can be discarded, but it must never reach a production release. AutoMigration, added in Room 2.4, generates migration SQL automatically for simple schema changes — adding a nullable column, adding a new table, renaming a table or column (with an explicit @AutoMigrationSpec). Complex changes like column type alterations still require manually written migrations. Use AutoMigration for the common cases and manual migrations for the rest; they coexist on the same @Database.

DataStore is the modern replacement for SharedPreferences. It solves SharedPreferences' biggest problems: blocking the main thread and inconsistent behaviour across threads. DataStore is fully async and coroutine-based.

// SharedPreferences — old, synchronous, dangerous on main thread
val prefs = context.getSharedPreferences("settings", Context.MODE_PRIVATE)
prefs.edit().putString("theme", "dark").apply()   // async write (OK)
val theme = prefs.getString("theme", "light")        // synchronous read (blocks!)
// ❌ Not type-safe. ❌ Can throw on main thread. ❌ No Flow support.

// DataStore (Preferences) — async, Flow-based, type-safe keys
// implementation("androidx.datastore:datastore-preferences:1.1.0")
val THEME_KEY = stringPreferencesKey("theme")

val Context.dataStore: DataStore<Preferences> by preferencesDataStore("settings")

// Read — returns Flow, never blocks
val themeFlow: Flow<String> = context.dataStore.data
    .map { prefs -> prefs[THEME_KEY] ?: "light" }

// Write — suspend function, runs on IO thread automatically
suspend fun setTheme(theme: String) {
    context.dataStore.edit { prefs ->
        prefs[THEME_KEY] = theme
    }
}

// Comparison:
//                  SharedPreferences    DataStore
// Async            Partial (apply)      Full (suspend + Flow)
// Type-safe keys   ❌                   ✅
// Flow support     ❌                   ✅
// Error handling   Silent failures      Throws IOException
// Thread safety    ❌                   ✅
// Proto variant    ❌                   ✅ (typed objects)

SharedPreferences has a fundamental threading problem: getSharedPreferences() blocks the main thread on its first call to parse the XML file from disk. In apps with large preference files or slow storage, this contributes to cold start latency and can trigger strict mode violations. DataStore is designed from the ground up for the async model: reads return a Flow<Preferences> that is collected on a coroutine, and writes are suspend functions that never block the calling thread. The entire API surface is async — there is no way to accidentally call DataStore synchronously.

Type-safe keys replace SharedPreferences' raw string keys. val THEME_KEY = stringPreferencesKey("theme") creates a typed key that can only be used with String values. Passing an Int key to a String read would be a compile error. SharedPreferences uses raw strings: getString("theme", null) — a typo in "theme" silently returns null with no warning. DataStore key types enforce the value type at the call site, and the key object is typically defined as a top-level constant or companion object member, making it easy to find all usages.

Error visibility is another significant improvement. SharedPreferences silently swallows disk I/O errors — a failed write simply does not persist with no notification to the caller. DataStore surfaces errors as exceptions: a failed write throws an IOException that propagates through the suspend call, allowing the caller to handle it explicitly. Thread safety is built in through DataStore's single-writer coroutine model — concurrent writes are serialised automatically, eliminating the race conditions that SharedPreferences' apply() can produce. The two flavours — Preferences DataStore for key-value pairs and Proto DataStore for typed Protobuf schemas — cover the full range of settings and structured preference storage.

Proto DataStore stores a typed Protobuf object instead of key-value pairs. Use it when your stored data has structure — multiple related fields, nested types, or enums — rather than scattered individual keys.

// Preferences DataStore — good for simple, unrelated settings
val THEME_KEY = stringPreferencesKey("theme")
val FONT_SIZE = intPreferencesKey("font_size")
val NOTIFICATIONS = booleanPreferencesKey("notifications")
// Fine for 3 unrelated keys. Gets messy with 20+ keys.

// Proto DataStore — good for structured, related data
// Define schema in user_preferences.proto:
// syntax = "proto3";
// enum Theme { LIGHT = 0; DARK = 1; SYSTEM = 2; }
// message UserPreferences {
//   Theme theme = 1;
//   int32 font_size = 2;
//   bool notifications_enabled = 3;
// }

// Serializer (boilerplate, generated once)
object UserPreferencesSerializer : Serializer<UserPreferences> {
    override val defaultValue: UserPreferences = UserPreferences.getDefaultInstance()
    override suspend fun readFrom(input: InputStream) = UserPreferences.parseFrom(input)
    override suspend fun writeTo(t: UserPreferences, output: OutputStream) = t.writeTo(output)
}

// Usage — type-safe, no string keys
val Context.protoStore by dataStore("user_prefs.pb", UserPreferencesSerializer)

val prefsFlow: Flow<UserPreferences> = context.protoStore.data

suspend fun setDarkMode() {
    context.protoStore.updateData { current ->
        current.toBuilder().setTheme(Theme.DARK).build()
    }
}

Preferences DataStore is the right choice for small, unstructured sets of key-value pairs: the user's selected theme, their preferred language, a boolean flag for whether they have seen the onboarding screen, a cached user ID. These values are independent of each other and simple in type. Preferences DataStore requires no schema definition — just define typed keys and read/write them. The trade-off is that there is no compile-time guarantee that all the pieces of a related data structure are read and written together consistently.

Proto DataStore is for structured data defined in a .proto schema file: message UserSettings { string theme = 1; bool notifications_enabled = 2; repeated string blocked_users = 3; }. The schema is compiled to a Kotlin class, and reads return a typed UserSettings object — no string keys, no manual field extraction, no possibility of reading a field at the wrong type. Writing is done via a type-safe builder. This is appropriate when preferences form a logical group with nested objects, enums, or repeated fields — anything more structured than a handful of independent primitives.

Proto DataStore stores data in the binary Protobuf format rather than XML or JSON. Binary serialization is more compact and faster to parse than text formats, which matters for startup latency when reading preferences. Schema evolution is handled gracefully by Protobuf's design: adding a new field with a new field number is a backward-compatible change — old data files without the field parse successfully and return the field's default value. This makes Proto DataStore more robust than a custom JSON-in-file approach where schema changes require explicit migration logic.

Android provides EncryptedSharedPreferences and EncryptedFile (via Jetpack Security) backed by the AndroidKeyStore. Keys never leave the secure hardware — even a memory dump can't expose them.

// implementation("androidx.security:security-crypto:1.1.0-alpha06")

// EncryptedSharedPreferences — for auth tokens, session data
val masterKey = MasterKey.Builder(context)
    .setKeyScheme(MasterKey.KeyScheme.AES256_GCM)
    .build()

val encryptedPrefs = EncryptedSharedPreferences.create(
    context,
    "secure_prefs",
    masterKey,
    EncryptedSharedPreferences.PrefKeyEncryptionScheme.AES256_SIV,
    EncryptedSharedPreferences.PrefValueEncryptionScheme.AES256_GCM
)
// Keys AND values are encrypted — even if device is rooted

encryptedPrefs.edit()
    .putString("access_token", token)
    .apply()

val token = encryptedPrefs.getString("access_token", null)

// EncryptedFile — for sensitive documents, private keys
val encryptedFile = EncryptedFile.Builder(
    context,
    File(context.filesDir, "private_data.enc"),
    masterKey,
    EncryptedFile.FileEncryptionScheme.AES256_GCM_HKDF_4KB
).build()

encryptedFile.openFileOutput().use { it.write(sensitiveBytes) }
encryptedFile.openFileInput().use { it.readBytes() }

// AndroidKeyStore — hardware-backed, key never in memory
// AES256_GCM: authenticated encryption, tamper-evident
// setUserAuthenticationRequired(true): biometric-gated key usage

// What NOT to store in EncryptedSharedPreferences:
// ❌ Encryption keys themselves (use KeyStore directly)
// ❌ Bank card numbers (use backend tokenisation)
// ✅ Auth tokens, session IDs, user preferences

EncryptedSharedPreferences from the androidx.security library is a drop-in replacement for SharedPreferences with the same read and write API, but all data is encrypted at rest. It uses AES-256-GCM for value encryption and AES-256-SIV for key encryption — both keys and values are encrypted, so an attacker who reads the preference file cannot determine what was stored even from the key names. The encryption key is a MasterKey stored in the Android Keystore, which is hardware-backed on devices with a secure element. The master key never leaves the Keystore in plaintext — encryption and decryption operations happen inside the Keystore.

Use EncryptedSharedPreferences for anything that grants access or identifies the user: access tokens, refresh tokens, session identifiers, API keys, and cached credential material. Plain SharedPreferences stores data in a world-readable XML file on non-rooted devices and trivially accessible on rooted ones. Any token stored in plain preferences can be extracted from a rooted device or via an ADB backup on older Android versions. Encryption does not make data extraction impossible on compromised devices, but it raises the cost significantly and protects against the common attack vector of ADB file pulls.

EncryptedFile from the same library encrypts arbitrary binary file content using AES-256-GCM. Use it for sensitive documents, private keys, certificates, or any binary data that should not be readable if the device storage is accessed without the user's credentials. The setUserAuthenticationRequired(true) option on the MasterKey requires the user to authenticate with biometrics or screen lock PIN before the Keystore key can be used. This means even extracting the key from the Keystore is prevented without the user's biometric — providing strong protection for high-sensitivity data like cryptographic keys for signed transactions.

Offline-first means Room is always the single source of truth. The UI observes Room via Flow — it never reads from the network directly. The network only writes to Room; Room then triggers a UI update automatically.

// Data flow: API → Room → Flow → ViewModel → UI
// UI never calls API. API only writes to Room. Room notifies UI.

class ProductRepositoryImpl @Inject constructor(
    private val api: ProductApi,
    private val dao: ProductDao,
    @IoDispatcher private val io: CoroutineDispatcher
) : ProductRepository {

    // 1. UI observes this — always from Room, never direct from API
    override fun observeProducts(): Flow<List<Product>> =
        dao.observeAll().map { it.map { e -> e.toDomain() } }

    // 2. Refresh fetches from API and writes to Room → Flow auto-emits
    override suspend fun refresh(): Result<Unit> = withContext(io) {
        runCatching {
            val fresh = api.getProducts()
            dao.upsertAll(fresh.map { it.toEntity() })
            // Room emits updated list to all Flow collectors automatically
        }
    }
}

// ViewModel — UI reads from observeProducts(), triggers refresh
@HiltViewModel
class ProductViewModel @Inject constructor(private val repo: ProductRepository) : ViewModel() {

    val products = repo.observeProducts()
        .stateIn(viewModelScope, SharingStarted.WhileSubscribed(5000), emptyList())

    init { viewModelScope.launch { repo.refresh() } }   // kick off sync on load
}

// What the user experiences:
// 1. Screen opens → cached data shows immediately (Room)
// 2. Refresh runs in background (API call)
// 3. New data written to Room → Flow emits → UI updates seamlessly
// 4. No internet? Still shows cached data, no crash

The offline-first architecture pattern treats the local database — Room — as the single source of truth for all UI data. The UI never reads directly from the network. Instead: the repository exposes a Flow from Room, the ViewModel collects it, and the UI displays whatever Room contains. The network is only used to refresh Room's contents. This separation means the UI's data pipeline is always the same: read from Room. Whether Room contains fresh server data or week-old cached data is irrelevant to the UI layer.

The flow is reactive in both directions. When Room is written to — by a background sync, by a user action, by any coroutine on any thread — the Flow emits the updated data and the UI updates automatically. No explicit reload, no manual notifyDataSetChanged(), no event bus. The repository's refresh function calls the API, receives fresh data, calls dao.upsertAll(newData), and returns. The Flow observer in the ViewModel triggers automatically, propagating the new data to the UI. The refresh function does not need to return the data — writing to Room is enough.

The offline experience is a direct consequence of this architecture. When the user opens the app with no internet, Room still contains the last-fetched data. The Flow emits immediately with the cached content — the user sees data, not an error screen or an empty list with a spinner. The refresh attempt fails with a network exception; the repository catches it, the ViewModel transitions to an offline state, and the UI shows a subtle "offline — showing cached data" indicator while preserving the cached content. When connectivity returns, the next refresh replaces the stale cache with fresh data. The architecture handles offline gracefully without explicit offline-mode code in the UI layer.

SQLite only stores primitives (text, integer, real, blob). TypeConverters tell Room how to convert complex types — like Date, List, or custom enums — to and from a storable primitive.

// Problem: SQLite can't store a Date or List directly
@Entity
data class OrderEntity(
    @PrimaryKey val id: String,
    val createdAt: Date,           // ❌ Room doesn't know how to store Date
    val tags: List<String>          // ❌ Room doesn't know how to store List
)

// Solution: TypeConverters
class Converters {
    // Date ↔ Long (timestamp in milliseconds)
    @TypeConverter
    fun dateToLong(date: Date): Long = date.time

    @TypeConverter
    fun longToDate(value: Long): Date = Date(value)

    // List<String> ↔ JSON String
    @TypeConverter
    fun listToJson(list: List<String>): String = Json.encodeToString(list)

    @TypeConverter
    fun jsonToList(json: String): List<String> = Json.decodeFromString(json)
}

// Register on the Database class
@Database(entities = [OrderEntity::class], version = 1)
@TypeConverters(Converters::class)
abstract class AppDatabase : RoomDatabase()

// Important: don't overuse TypeConverters for large nested objects
// ❌ Storing an entire User JSON blob in an Order entity
// ✅ Use a foreign key relationship instead (normalised DB)
// ✅ TypeConverters for primitives: Date, Enum, List<String>, URI

SQLite supports four storage types: TEXT, INTEGER, REAL, and BLOB. Kotlin types like LocalDate, Uri, Enum, and List<String> have no direct SQLite equivalent. @TypeConverter tells Room how to convert between the Kotlin type and a SQLite-storable primitive. Room applies converters automatically when reading and writing entities — you define the conversion logic once, and Room calls the appropriate converter whenever that type appears in an entity or DAO method.

Converters must always be written in pairs: one function converts from the Kotlin type to the storage type, and another converts back. @TypeConverter fun localDateToLong(date: LocalDate): Long = date.toEpochDay() and @TypeConverter fun longToLocalDate(value: Long): LocalDate = LocalDate.ofEpochDay(value). Register the converter class at the @Database level with @TypeConverters(Converters::class) — this applies the converters to all entities and DAOs in the database. Common converter pairs: Date↔Long (epoch milliseconds), enum↔String (name), List<String>↔JSON, Uri↔String.

TypeConverters can be overused in ways that damage data integrity and queryability. Storing a serialized JSON blob of a complex object means you cannot write a SQL WHERE clause on any of the object's fields — you can only fetch the entire blob and deserialize it in Kotlin. It also means you cannot update individual fields atomically. A List<String> serialized to JSON is reasonable for small, rarely-queried lists. A List<Order> stored as a JSON blob is a design mistake — Order should be a separate entity with a foreign key to the parent. Use TypeConverters for simple value types; use normalized schema with relationships for structured, queryable data.

Room uses @ForeignKey for referential integrity and special result classes (one-to-many with @Relation, many-to-many with a junction table) to query related data efficiently.

// ONE-TO-MANY: One User has many Orders
@Entity
data class UserEntity(@PrimaryKey val id: String, val name: String)

@Entity(foreignKeys = [ForeignKey(
    entity = UserEntity::class,
    parentColumns = ["id"],
    childColumns  = ["userId"],
    onDelete      = ForeignKey.CASCADE  // delete orders when user deleted
)])
data class OrderEntity(@PrimaryKey val id: String, val userId: String, val total: Double)

// Result class for the relationship
data class UserWithOrders(
    @Embedded val user: UserEntity,
    @Relation(parentColumn = "id", entityColumn = "userId")
    val orders: List<OrderEntity>
)

@Transaction  // ← required for @Relation queries to be atomic
@Query("SELECT * FROM users")
fun observeUsersWithOrders(): Flow<List<UserWithOrders>>

// MANY-TO-MANY: Products ↔ Tags (via junction table)
@Entity(primaryKeys = ["productId", "tagId"])
data class ProductTagCrossRef(val productId: String, val tagId: String)

data class ProductWithTags(
    @Embedded val product: ProductEntity,
    @Relation(
        parentColumn  = "id",
        entityColumn  = "id",
        associateBy   = Junction(ProductTagCrossRef::class,
                          parentColumn = "productId",
                          entityColumn = "tagId")
    )
    val tags: List<TagEntity>
)

@ForeignKey declares a relationship between entities and enforces referential integrity at the SQLite level. A foreign key on the Order entity referencing the User entity's primary key prevents inserting an order for a non-existent user and prevents deleting a user who has associated orders without explicit cascade behaviour. onDelete = ForeignKey.CASCADE propagates deletions: deleting a user automatically deletes all their orders. ForeignKey.RESTRICT prevents the parent deletion entirely if children exist. These constraints run inside SQLite — they are enforced even if your Kotlin code has a bug that attempts an inconsistent write.

@Relation defines how Room loads related entities when you define a data class combining parent and children: data class UserWithOrders(val user: UserEntity, @Relation(parentColumn = "id", entityColumn = "userId") val orders: List<OrderEntity>). Room does not generate a SQL JOIN for @Relation — it executes a separate query to fetch the children and joins them in Kotlin. This is important to understand because it means the query is two database reads, not one. For large datasets, this can be more efficient than a JOIN that produces many duplicate parent columns, but it is important to be aware of the two-query nature.

The @Transaction annotation on a DAO method containing a @Relation makes the two-query read atomic. Without @Transaction, another coroutine could insert or delete a child row between Room's first query (fetching parents) and second query (fetching children), resulting in a partially-consistent result — a parent with a child list that does not accurately reflect the database state at any single point in time. @Transaction wraps both queries in a SQLite transaction, preventing this. Always add @Transaction to any DAO method that returns a type containing @Relation fields. Many-to-many relationships use a junction entity with a composite primary key of both foreign keys and an @Relation using the junction.

Room supports in-memory databases that run on the JVM — no emulator or device needed for most DAO tests. MigrationTestHelper validates your migration scripts against exported schemas.

// androidTestImplementation("androidx.room:room-testing:2.6.1")

// Basic DAO test — in-memory database
@RunWith(AndroidJUnit4::class)
class UserDaoTest {
    private lateinit var db: AppDatabase
    private lateinit var dao: UserDao

    @Before fun setUp() {
        db = Room.inMemoryDatabaseBuilder(
            ApplicationProvider.getApplicationContext(),
            AppDatabase::class.java
        )
        .allowMainThreadQueries()   // allowed in tests only
        .build()
        dao = db.userDao()
    }

    @After fun tearDown() { db.close() }

    @Test fun insertAndRetrieve() = runTest {
        val user = UserEntity("1", "Alice", "alice@example.com")
        dao.insert(user)
        val result = dao.getUser("1")
        assertEquals("Alice", result?.name)
    }

    @Test fun observeEmitsOnInsert() = runTest {
        val collected = mutableListOf<List<UserEntity>>()
        val job = launch { dao.observeAll().collect { collected.add(it) } }
        dao.insert(UserEntity("1", "Alice", "a@b.com"))
        advanceUntilIdle()
        assertTrue(collected.any { it.size == 1 })
        job.cancel()
    }
}

// Migration test — validates your Migration objects
@RunWith(AndroidJUnit4::class)
class MigrationTest {
    @get:Rule
    val helper = MigrationTestHelper(InstrumentationRegistry.getInstrumentation(),
        AppDatabase::class.java)

    @Test fun migrate1To2() {
        helper.createDatabase("test.db", 1)
        helper.runMigrationsAndValidate("test.db", 2, true, MIGRATION_1_2)
    }
}

Room.inMemoryDatabaseBuilder(context, AppDatabase::class.java).build() creates an isolated database that exists only in memory. It is created fresh for each test, has no disk I/O overhead, needs no file cleanup between tests, and is automatically destroyed when the test process exits. In-memory databases run significantly faster than file-backed databases — the disk read/write latency is eliminated. Every DAO test should use an in-memory database to ensure isolation (no state leaks between tests) and speed.

allowMainThreadQueries() disables Room's default restriction on main-thread database access for test purposes. In tests, you often need to call DAO methods directly without the complexity of coroutine test infrastructure. It is safe in tests because test execution is sequential and the test thread can block without affecting anything. This call must never appear in production code — it disables a safety guard designed to prevent ANRs. For Flow-returning DAOs, Turbine (app.cash.turbine) provides clean assertion syntax: dao.observeUsers().test { val first = awaitItem(); assertEquals(emptyList(), first) } — far cleaner than manually collecting flows in tests.

MigrationTestHelper is the critical tool for validating migrations before they reach users. It reads the exported schema JSON files for each version and simulates the upgrade path, running your Migration objects against a real SQLite database. A SQL error in a migration — wrong column name, invalid syntax — throws during the test rather than during a user's upgrade. exportSchemas = true in the @Database annotation (and the corresponding KSP argument room.schemaLocation) instructs Room to write schema JSON files to a specified directory. Commit these files to version control — they serve as the historical record of your schema evolution and are required by MigrationTestHelper.

SharedPreferences is synchronous key-value storage using XML files -- simple but blocks the main thread on first read. DataStore (Preferences or Proto) is the modern async replacement backed by a DataStore file, exposed as a Flow. Room is for relational structured data. Files are for binary blobs like images. Choose based on what you're storing and whether you need queries.

// SharedPreferences -- synchronous, legacy, avoid for new code
val prefs = context.getSharedPreferences("settings", Context.MODE_PRIVATE)
prefs.edit().putString("theme", "dark").apply()

// DataStore -- async, type-safe, Flow-based (use this for new code)
val themeFlow: Flow<String> = dataStore.data.map { prefs -> prefs[THEME_KEY] ?: "light" }
suspend fun saveTheme(theme: String) = dataStore.edit { it[THEME_KEY] = theme }

// Room -- structured relational data with SQL queries
@Dao interface ProductDao {
    @Query("SELECT * FROM products WHERE category = :cat")
    fun getByCategory(cat: String): Flow<List<Product>>
}

SharedPreferences is the legacy key-value storage mechanism: XML-backed, synchronous on first read, and with silent error handling. For any new feature, it should not be chosen — Preferences DataStore is a direct drop-in replacement with a better threading model, explicit error handling, and type-safe keys. The only reason to touch SharedPreferences in new code is when incrementally migrating an existing codebase, using the SharedPreferencesMigration utility to move existing preference files to DataStore.

Preferences DataStore covers the "settings" use case: theme preference, notification opt-in, language selection, tutorial-shown flags, cached user ID. These are typically a handful of independent key-value pairs without complex structure. Proto DataStore is for structured preferences that benefit from a typed schema — nested objects, enums, repeated fields, or many related settings that logically belong together as a typed record. Room is for anything that requires SQL querying: filtering, sorting, joining, aggregating, or managing collections of entities with relationships. The presence of a WHERE clause or a relationship between record types is the clearest signal that Room is the right tool.

Binary and document files use the file system directly: filesDir for permanent internal storage, cacheDir for re-downloadable content, getExternalFilesDir() for user-visible files that do not require permissions on Android 10+. MediaStore is for photos, videos, and music intended to be shared with other apps or visible in the system gallery. The decision tree: user preferences → Preferences DataStore. Structured typed configuration → Proto DataStore. Structured queryable data with relationships → Room. Binary files and documents → file system APIs. Shared media → MediaStore. Tokens and credentials → EncryptedSharedPreferences or Keystore.

Cache-then-network shows cached data immediately while fetching fresh data in the background. Stale-while-revalidate is the formal name for this pattern — serve stale data while revalidating with the network.

// Pattern: emit cache first, then network, then updated cache
fun getProducts(): Flow<Resource<List<Product>>> = flow {
    // Step 1: emit cached data immediately
    emit(Resource.Loading())
    val cached = dao.getAll()
    if (cached.isNotEmpty()) emit(Resource.Success(cached.map { it.toDomain() }))

    // Step 2: fetch fresh data from network
    runCatching { api.getProducts() }
        .onSuccess { fresh ->
            dao.upsertAll(fresh.map { it.toEntity() })
            // Step 3: emit fresh data (Room Flow would auto-emit, but explicit here)
            emit(Resource.Success(fresh.map { it.toDomain() }))
        }
        .onFailure { error ->
            // Network failed but cache available — not a fatal error
            emit(Resource.Error("Showing cached data", cached.map { it.toDomain() }))
        }
}.flowOn(Dispatchers.IO)

// Cache invalidation — when to discard stale cache
@Entity
data class ProductEntity(
    @PrimaryKey val id: String,
    val name: String,
    val cachedAt: Long = System.currentTimeMillis()  // timestamp every cache write
)

fun isCacheStale(cachedAt: Long, ttlMinutes: Int = 30): Boolean =
    System.currentTimeMillis() - cachedAt > ttlMinutes * 60_000L

// Strategy: always show cache; refresh if stale
if (isCacheStale(cached.first().cachedAt)) refresh()

The stale-while-revalidate pattern gives the best perceived performance: show whatever is in the cache immediately, then fetch fresh data in the background and update the display when it arrives. The user sees content within milliseconds of opening the screen — no loading spinner for data that was recently fetched. The background fetch is invisible if the data has not changed, and the UI transitions smoothly to fresh data if it has. This is preferable to network-first strategies where the screen shows a loading state every time, even when cached data is available.

A timestamp-based TTL determines when the cache is considered stale. Store a cachedAt: Long alongside each cached entity. In the repository, check the age before deciding whether to trigger a background refresh: if (System.currentTimeMillis() - cachedAt > MAX_CACHE_AGE_MS) { refresh() }. This prevents unnecessary network calls when the cache is fresh — scrolling through a list and opening items should not trigger a network call on every item if the cache is 30 seconds old. The TTL value is tunable per data type: user profile might be cacheable for an hour, product prices might need refreshing every few minutes.

The sealed class pattern makes cache state explicit in the UI. sealed class UiState<T> { data class Success(val data: T, val isStale: Boolean) : UiState<T>(); object Loading : UiState<T>(); data class Error(val cause: Throwable, val staleData: T?) : UiState<T>() }. When a network refresh fails, the Error state carries the stale data so the UI can display it with a warning rather than replacing valid content with an error screen. The Room Flow observer auto-propagates the cache update when it succeeds — the ViewModel does not need to merge the network result with the cached data manually.

Room has first-class Paging 3 support — a DAO method returning PagingSource gives you automatic pagination with zero boilerplate. Room handles page fetching, loading states, and error handling.

// Room DAO — return PagingSource instead of List
@Dao
interface MessageDao {
    @Query("SELECT * FROM messages ORDER BY timestamp DESC")
    fun paginate(): PagingSource<Int, MessageEntity>
    // Room generates the PagingSource implementation — no manual paging logic
}

// Repository — create Pager from DAO PagingSource
fun observeMessages(): Flow<PagingData<Message>> = Pager(
    config = PagingConfig(
        pageSize          = 20,
        enablePlaceholders = false,
        prefetchDistance  = 5
    ),
    pagingSourceFactory = { dao.paginate() }
).flow.map { pagingData -> pagingData.map { it.toDomain() } }

// ViewModel
val messages = repo.observeMessages().cachedIn(viewModelScope)

// Compose UI — collectAsLazyPagingItems handles everything
val messages = vm.messages.collectAsLazyPagingItems()
LazyColumn {
    items(messages, key = { it.id }) { msg ->
        MessageRow(msg)
    }
    // Append loading indicator
    if (messages.loadState.append is LoadState.Loading) {
        item { CircularProgressIndicator() }
    }
}

// RemoteMediator — combine Room + API for full offline paging
// Paging 3 fetches pages from Room; when Room runs out, RemoteMediator
// fetches more from the API and inserts into Room, then Paging continues from Room

Room has first-class Paging 3 integration. A DAO method annotated with @Query that returns PagingSource<Int, UserEntity> — Room generates the full PagingSource implementation automatically. You do not write the pagination logic: Room handles the offset calculation, the page size, and the boundary callbacks. The DAO simply declares @Query("SELECT * FROM users ORDER BY name") fun getAllUsersPaged(): PagingSource<Int, UserEntity> and Room generates a correct, efficient implementation backed by the real database.

The ViewModel assembles the paging pipeline: val usersPagingData = Pager(PagingConfig(pageSize = 20)) { userDao.getAllUsersPaged() }.flow.cachedIn(viewModelScope). The Pager wraps the PagingSource factory and produces a Flow<PagingData<UserEntity>>. cachedIn(viewModelScope) is critical — it prevents reloading all pages from the database on every recomposition. Without it, rotating the device or navigating away and back would reload the entire paged list from page one. The cached pages survive configuration changes and brief navigations.

In Compose, val users = viewModel.usersPagingData.collectAsLazyPagingItems() provides a LazyPagingItems object that drives a LazyColumn. It exposes users.loadState for showing loading indicators at the top and bottom of the list and handling errors with retry buttons — all without manual pagination state management. The RemoteMediator pattern extends this to network-backed pagination: Room provides the locally cached pages immediately, and when the user reaches the end of what Room has, RemoteMediator fetches the next page from the API, inserts it into Room, and Paging 3 reloads from Room. Room remains the only data source for the UI — the network only feeds Room.

WAL (Write-Ahead Logging) is a journaling mode that dramatically improves concurrent read/write performance. Room enables WAL by default since Room 2.2 — you usually don't need to configure it manually.

// Journal modes in SQLite:
// Default (DELETE): writes lock the whole database
// WAL: writes and reads can happen simultaneously

// Without WAL (default mode):
// Thread 1 writes to DB      → Thread 2 blocked, waiting
// Thread 1 finishes writing  → Thread 2 can now read
// Result: reads and writes serialised → slower with concurrent access

// With WAL:
// Thread 1 writes to WAL file   → Thread 2 can read main DB concurrently
// WAL checkpointed to main DB   → periodically, in background
// Result: reads and writes run simultaneously → much faster

// Room 2.2+ enables WAL by default — you get this for free
val db = Room.databaseBuilder(context, AppDatabase::class.java, "app.db")
    .build()
// WAL already enabled — no code needed

// Manual control (if needed)
val db = Room.databaseBuilder(context, AppDatabase::class.java, "app.db")
    .setJournalMode(JournalMode.WRITE_AHEAD_LOGGING)  // explicit WAL
    .build()

val db = Room.databaseBuilder(...)
    .setJournalMode(JournalMode.TRUNCATE)  // disable WAL (rare — e.g. shared DBs)
    .build()

// When NOT to use WAL:
// - Database shared with other processes (WAL doesn't support multi-process)
// - Very low memory devices (WAL uses slightly more memory)
// For standard single-process apps: always use WAL (Room default)

Write-Ahead Logging (WAL) is SQLite's journaling mode for concurrent access. In the default journal mode (DELETE), SQLite uses an exclusive write lock: readers must wait for all writers to finish, and writers must wait for all readers to finish. Every write operation blocks concurrent reads. In WAL mode, writes go to a separate WAL log file while readers continue reading from the main database file. Readers and writers operate simultaneously — a write does not block any read, and a read does not block any write. The WAL log is checkpointed back into the main file periodically.

Room 2.2 enables WAL mode by default for all new databases. Existing databases opened by Room 2.2+ are automatically upgraded to WAL mode on first open. No configuration is needed — the performance improvement is automatic. The practical impact in a multi-coroutine Android app is significant: a background sync writing batches of records to Room no longer blocks the ViewModel's Flow from emitting updates, and the UI reading from Room no longer delays the background sync. Concurrent DAO calls from different coroutines no longer contend on a single exclusive lock.

One important exception: WAL mode is incompatible with multiple processes sharing the same database. If a Service running in a separate process (declared with android:process=":remote") and the main app process both access the same Room database, WAL mode can cause data corruption. In this scenario, disable WAL explicitly: .setJournalMode(JournalMode.TRUNCATE) in the RoomDatabase.Builder. The better solution is to avoid cross-process database sharing entirely — use an IPC mechanism (ContentProvider, AIDL, or a bound Service) to centralise database access in one process and expose only the data needed by the other process.

Sync conflicts happen when the same record is modified both locally (offline) and remotely. The three main strategies are last-write-wins, server-wins, and three-way merge — each with different trade-offs.

// Track what needs syncing with a status field
@Entity
data class NoteEntity(
    @PrimaryKey val id: String,
    val content: String,
    val updatedAt: Long,       // timestamp for last-write-wins
    val syncStatus: SyncStatus = SyncStatus.SYNCED
)

enum class SyncStatus {
    SYNCED,        // matches server
    PENDING,       // local change not yet synced
    CONFLICT       // local and server both changed
}

// Strategy 1: Last Write Wins — compare timestamps
suspend fun syncNote(local: NoteEntity, remote: NoteDto) {
    when {
        remote.updatedAt > local.updatedAt -> {
            // Server newer — overwrite local
            dao.upsert(remote.toEntity().copy(syncStatus = SyncStatus.SYNCED))
        }
        local.updatedAt > remote.updatedAt && local.syncStatus == SyncStatus.PENDING -> {
            // Local newer — push to server
            api.updateNote(local.toDto())
            dao.upsert(local.copy(syncStatus = SyncStatus.SYNCED))
        }
        else -> {
            // Both modified — mark conflict for user resolution
            dao.upsert(local.copy(syncStatus = SyncStatus.CONFLICT))
        }
    }
}

// Strategy 2: Server always wins (simplest)
// On sync: replace all local data with server data
// ✅ No conflict logic needed
// ❌ Local changes discarded on conflict
// Good for: product catalogues, config, anything user doesn't edit

// Strategy 3: Show conflict UI (best for user-owned data)
// Mark conflicted rows, show "your version vs server version" picker
// Good for: notes, todos, documents

Sync conflicts occur when local changes and server changes affect the same record simultaneously. The first step is detecting them: add a syncStatus: SyncStatus field to entities (values: SYNCED, PENDING, CONFLICT) and an updatedAt: Long timestamp to both local and remote representations. When a sync attempt downloads a server record and finds a locally-modified record with a newer updatedAt, the system has detected a conflict. Without explicit conflict detection, one version silently overwrites the other — often the wrong one.

Server-wins is the simplest conflict resolution strategy: the server version always overwrites local changes. This is correct for read-only caches where local modifications should not exist, and for server-managed data like server-computed fields or admin-controlled content. Last-write-wins compares timestamps: the record with the newer updatedAt wins. This works well for non-collaborative data like personal notes, user settings, or preferences — where two simultaneous edits are unlikely and losing one is acceptable. The losing version is discarded silently.

For collaborative or high-stakes data — shared documents, financial records, contact details — losing either version silently is unacceptable. Mark these records with SyncStatus.CONFLICT, store both the local version and the server version in Room, and surface a conflict resolution UI. Show the user both versions — "Your version" and "Server version" — and let them choose which to keep or how to merge. This is the correct treatment for any data the user has explicitly edited and would be distressed to find overwritten. The complexity is justified by the user experience cost of silently losing their work.

Android has multiple storage locations, each with different visibility, persistence, and permission requirements. Choosing the right location avoids permission issues and data leaks.

// INTERNAL STORAGE — private to your app, always available
val filesDir   = context.filesDir          // persistent, deleted on uninstall
val cacheDir   = context.cacheDir          // deleted by OS when storage is low
val noBackupDir = context.noBackupFilesDir // excluded from auto-backup
// No permissions needed. Not visible to other apps or user.

// Room and DataStore use internal storage automatically

// EXTERNAL STORAGE — shared or app-specific
val externalFiles = context.getExternalFilesDir(Environment.DIRECTORY_PICTURES)
// App-specific external — no permission needed on Android 10+
// Visible to user in file manager. Deleted on uninstall.
// Use for: user-generated files they'd want to keep

// MediaStore — shared media (photos, music, videos)
// READ_MEDIA_IMAGES permission (Android 13+) needed to read other apps' media
// Writing YOUR app's media: no permission needed
val contentValues = ContentValues().apply {
    put(MediaStore.Images.Media.DISPLAY_NAME, "photo.jpg")
    put(MediaStore.Images.Media.MIME_TYPE, "image/jpeg")
}
val uri = context.contentResolver.insert(MediaStore.Images.Media.EXTERNAL_CONTENT_URI, contentValues)

// Summary:
// Database (Room)    → filesDir  (auto)
// App settings       → DataStore → filesDir (auto)
// Downloaded files   → cacheDir  (or externalFilesDir for user-kept)
// User photos/videos → MediaStore (shared) or externalFilesDir (private)
// Sensitive data     → filesDir + EncryptedFile

context.filesDir is the app's private internal storage directory — on the device's internal flash storage, sandboxed to the app, not accessible to other apps or the user (without root). Files here survive low-storage warnings; Android will not automatically delete them to free space. They are deleted when the user uninstalls the app or clears app data from settings. Use filesDir for data that the app genuinely needs and cannot easily regenerate: user-generated documents, encrypted private keys, processed data that took significant computation to produce.

context.cacheDir is the app's private cache directory. Android may delete files here at any time when the system is low on storage — no warning, no callback. Files survive normal operation but may disappear between sessions. Use cacheDir for content that can be re-fetched or regenerated: downloaded images (though Coil/Glide manage their own cache here), API response bodies, temporary processing files, downloaded but not-yet-processed data. If missing cache data causes a worse experience but not data loss, cacheDir is correct. If losing the data causes data loss, use filesDir.

context.getExternalFilesDir(type) stores files in the app's external storage directory. These files are visible to the user via a file manager, are deleted on uninstall, and require no storage permission on Android 10+. They are appropriate for files the user might want to export, share, or manage directly — exported reports, saved recordings, downloaded documents. MediaStore is for files intended to persist after app uninstall and be visible to all apps: photos taken by the camera, music recorded in the app, videos created by the app. Writing to MediaStore on Android 10+ requires no permission; reading other apps' MediaStore files requires READ_MEDIA_IMAGES or equivalent.

DataStore should live in a dedicated data module with an interface, not accessed directly from feature modules. This prevents tight coupling and makes it mockable in tests.

// ❌ BAD: feature module directly accesses DataStore
class ThemeViewModel(private val context: Context) : ViewModel() {
    val theme = context.dataStore.data.map { it[THEME_KEY] }
    // ❌ Context in ViewModel. ❌ Feature knows about DataStore internals.
}

// ✅ GOOD: wrap DataStore in an interface

// :core:data — the repository interface (no DataStore import here)
interface UserSettingsRepository {
    val theme: Flow<Theme>
    suspend fun setTheme(theme: Theme)
    val notificationsEnabled: Flow<Boolean>
    suspend fun setNotifications(enabled: Boolean)
}

// :core:datastore — the DataStore implementation
class UserSettingsRepositoryImpl @Inject constructor(
    private val dataStore: DataStore<Preferences>
) : UserSettingsRepository {

    private val THEME_KEY = stringPreferencesKey("theme")

    override val theme = dataStore.data
        .map { prefs -> Theme.valueOf(prefs[THEME_KEY] ?: Theme.SYSTEM.name) }

    override suspend fun setTheme(theme: Theme) {
        dataStore.edit { it[THEME_KEY] = theme.name }
    }
}

// Hilt wiring — @Singleton, one DataStore instance for the whole app
@Provides @Singleton
fun provideDataStore(@ApplicationContext ctx: Context): DataStore<Preferences> =
    ctx.dataStore

// Feature module — injects interface, never knows about DataStore
class ThemeViewModel @Inject constructor(
    private val settings: UserSettingsRepository
) : ViewModel()

DataStore should not be accessed directly from ViewModels or feature modules. Wrapping DataStore in a repository with a defined interface follows the same principle as wrapping Room: the ViewModel depends on an abstraction, not the storage implementation. interface UserSettingsRepository { fun observeSettings(): Flow<UserSettings>; suspend fun updateTheme(theme: Theme) }. Feature modules import only the interface; the :core:datastore module contains the UserSettingsRepositoryImpl that depends on DataStore. The ViewModel is testable with a FakeUserSettingsRepository that holds a MutableStateFlow — no DataStore, no disk I/O in unit tests.

A critical DataStore rule: create exactly one DataStore instance per file, per process. DataStore is not thread-safe for multiple instances pointing to the same file. Creating two DataStore objects backed by the same file name produces race conditions and data corruption — concurrent reads and writes from two independent instances can interleave in ways that corrupt the file. Enforce the singleton contract via Hilt: @Provides @Singleton fun provideUserSettingsDataStore(): DataStore<UserSettings>. If you discover multiple DataStore creations in your codebase — often introduced by developers following outdated tutorials — this is a production data-corruption risk that must be fixed immediately.

The module architecture separates concerns and enforces the dependency direction. :core:data defines repository interfaces — pure Kotlin, no DataStore dependency. :core:datastore implements those interfaces using DataStore — depends on :core:data and on AndroidX DataStore. Feature modules depend only on :core:data, never on :core:datastore. This inversion ensures feature modules do not accidentally couple to the storage implementation. Swapping DataStore for a different storage mechanism — or adding in-memory caching — only requires changing :core:datastore. The interfaces in :core:data and all feature modules are unaffected.

Android Auto Backup (API 23+) automatically backs up app data to Google Drive -- up to 25MB, daily when the device is idle, charging, and on WiFi. Users restore their backup when they install the app on a new device. You configure what's included or excluded via a backup_rules XML file.

// AndroidManifest.xml -- enable and configure backup
// <application
//     android:allowBackup="true"
//     android:dataExtractionRules="@xml/data_extraction_rules"  (API 31+)
//     android:fullBackupContent="@xml/backup_rules">             (API 23-30)

// res/xml/backup_rules.xml -- include/exclude specific files
// <full-backup-content>
//   <include domain="database" path="app_database.db"/>
//   <exclude domain="sharedpref" path="session_prefs.xml"/> <!-- do not back up auth tokens -->
// </full-backup-content>

class MyBackupAgent : BackupAgentHelper() {
    override fun onCreate() {
        addHelper("prefs", SharedPreferencesBackupHelper(this, "settings"))
        addHelper("db", FileBackupHelper(this, "../databases/app.db"))
    }
}

Android Auto Backup automatically backs up app data to the user's Google Drive when the device is idle, charging, and connected to Wi-Fi. The backup includes SharedPreferences files, internal storage, and Room databases by default, up to 25MB. It runs with zero code — opt-in is automatic for apps targeting Android 6.0+. Backups restore when the user sets up a new device or reinstalls the app, providing data continuity across device changes. This is a significant user experience feature that many apps get for free.

Exclusion rules are essential. Auth tokens, session IDs, and refresh tokens backed up to Drive and restored to a new device are dangerous: the original device may still be in use with those same tokens, creating session ambiguity or enabling token theft. Cached network content should be excluded — it wastes backup quota for data that will be re-fetched anyway. Configure exclusions in res/xml/backup_rules.xml (referenced by android:fullBackupContent) or for API 31+, res/xml/data_extraction_rules.xml (referenced by android:dataExtractionRules), which separates rules for cloud backup and device-to-device (D2D) transfer scenarios.

BackupAgentHelper provides custom backup logic for cases where automatic backup is insufficient: encrypting data before it leaves the device, transforming schema-changed Room databases during restore, or selectively backing up only certain tables. Always test backup and restore in development with adb shell bmgr run to trigger a backup and adb shell bmgr restore <token> <package> to restore it to a fresh install. Untested restore paths frequently reveal crashes or incorrect behavior — a Room database migrated from v1 to v3 that restores from a v1 backup needs migration logic on restore. The BackupAgent.onRestore() callback is the place to apply that logic.

Scoped Storage (introduced in Android 10, enforced in Android 11) restricts apps to their own directories and MediaStore. You can no longer browse the entire filesystem — each app is sandboxed.

// Before Scoped Storage (Android 9 and below):
// READ_EXTERNAL_STORAGE + WRITE_EXTERNAL_STORAGE → access entire SD card

// After Scoped Storage (Android 10+):
// App-specific external → no permission needed
// Shared media (photos, music) → READ_MEDIA_IMAGES, READ_MEDIA_VIDEO, READ_MEDIA_AUDIO
// Other files → Storage Access Framework (SAF) / file picker

// 1. Reading/writing YOUR app's files — no permission
val myFile = File(context.getExternalFilesDir(null), "data.json")
myFile.writeText("hello")  // no permission needed on API 29+

// 2. Saving a photo to the gallery — no permission, use MediaStore
val values = ContentValues().apply {
    put(MediaStore.Images.Media.DISPLAY_NAME, "screenshot.jpg")
    put(MediaStore.Images.Media.MIME_TYPE, "image/jpeg")
    put(MediaStore.Images.Media.RELATIVE_PATH, "Pictures/MyApp")
}
val uri = contentResolver.insert(MediaStore.Images.Media.EXTERNAL_CONTENT_URI, values)

// 3. Opening a user-picked file — Storage Access Framework
val launcher = registerForActivityResult(ActivityResultContracts.GetContent()) { uri ->
    // uri is a content URI — read via contentResolver.openInputStream(uri)
    val input = contentResolver.openInputStream(uri!!)
}
// No READ permission needed — user explicitly chose the file

// MANAGE_EXTERNAL_STORAGE — full access (requires Play Store approval)
// Only for: file managers, antivirus apps, backup tools

Scoped Storage, enforced from Android 10 (Q), ends free-range filesystem access. Apps can no longer enumerate arbitrary files anywhere on the device. External storage is partitioned: each app has its own sandbox at /sdcard/Android/data/com.example.app/ that only it can access. This prevents privacy-invasive apps from reading another app's files, browsing the entire filesystem, or exfiltrating documents without user knowledge. The system provides structured APIs for each legitimate access pattern, replacing the blanket READ_EXTERNAL_STORAGE permission.

For app-specific files that the user might want to access or that should survive in external storage, context.getExternalFilesDir(Environment.DIRECTORY_DOCUMENTS) requires no storage permission on Android 10+. The directory is visible to the user via Files apps and cleaned up on uninstall. For adding photos or videos to the system gallery, accessible to all apps, use MediaStore: ContentResolver.insert(MediaStore.Images.Media.EXTERNAL_CONTENT_URI, contentValues). Writing to MediaStore requires no permission; reading another app's MediaStore entries requires READ_MEDIA_IMAGES (API 33+) or READ_EXTERNAL_STORAGE (older versions).

The Storage Access Framework (SAF) handles the case where the user must choose a file the app does not own. Launch ActivityResultContracts.OpenDocument() to show the system file picker. The user selects a file and the app receives a Uri with a persistent permission grant — no storage permission required. The permission grant persists across reboots if taken with ContentResolver.takePersistableUriPermission(). MANAGE_EXTERNAL_STORAGE grants full legacy filesystem access but requires explicit Play Store approval and is only permitted for genuine file manager apps, antivirus tools, and backup utilities. Requesting it for a regular app will likely result in Play Store rejection.

An e-commerce app's data layer needs offline capability, optimistic UI updates, and clear cache invalidation. The architecture: Room as the single source of truth for all displayed data, Retrofit to fetch from the network, and a Repository that orchestrates the cache-then-network pattern. The ViewModel only talks to the Repository.

// Entities -- Room tables
@Entity data class ProductEntity(@PrimaryKey val id: String, val name: String, val price: Double)
@Entity data class CartItemEntity(@PrimaryKey val id: String, val productId: String, val qty: Int)

// Repository -- cache-then-network pattern
class ProductRepository @Inject constructor(
    private val dao: ProductDao,
    private val api: ProductApi
) {
    val products: Flow<List<Product>> = dao.getAll()  // always serve from Room

    suspend fun refresh() {
        val fresh = api.getProducts()
        dao.upsertAll(fresh.map { it.toEntity() })  // upsert -- insert or update
    }
}

An e-commerce app's product catalog, cart, and order history all benefit from the offline-first Room architecture. The ViewModel always reads from Room — never directly from the network. productDao.observeProducts().stateIn(viewModelScope, WhileSubscribed(5000), emptyList()) produces a StateFlow that stays current as long as the ViewModel is active. The repository's refresh() method fetches from the API and calls productDao.upsertAll(dtos.map { it.toEntity() }). The ViewModel never changes; the Flow propagates the update automatically.

Cart operations require transactions to prevent inconsistent state. An "add item" operation that checks current quantity, increments it, and updates the row must be atomic — if interrupted between the read and the write, the cart quantity could be wrong. @Transaction suspend fun addItemToCart(productId: Long, quantity: Int) wraps the check and insert in a database transaction. If any exception occurs mid-transaction, all changes are rolled back. The user pressing "Add to Cart" rapidly sees the correct quantity — each add is atomic and the results compose correctly rather than racing.

Optimistic UI for cart actions provides the instant feedback users expect. When the user taps "Add to Cart", write to Room immediately and show the updated quantity in the UI — this is the optimistic update. Then trigger a background sync to the server. If the server accepts the change, no UI update needed (Room already shows the correct state). If the server rejects it (item out of stock, price changed), update Room with the server's authoritative state and show an error message. The user experienced a fast, responsive interaction; the occasional correction from the server is a brief exception rather than the normal latency experience.

Room automatically runs on a background thread when you use suspend functions or return Flow. You don't need withContext(Dispatchers.IO) for Room calls — Room handles threading internally.

// Room threading rules:

// 1. Suspend DAO functions — Room switches to IO thread automatically
@Dao interface UserDao {
    @Insert suspend fun insert(user: UserEntity)   // runs on Room's IO thread
    @Query(...) suspend fun getUser(id: String): UserEntity?  // same
}

// You DON'T need this — Room handles it:
suspend fun getUser(id: String) = withContext(Dispatchers.IO) {
    dao.getUser(id)   // redundant withContext — Room already does this
}
// ✅ Just call directly:
suspend fun getUser(id: String) = dao.getUser(id)  // clean, same result

// 2. Flow DAO functions — Room emits on its IO thread
@Query(...) fun observeUsers(): Flow<List<UserEntity>>
// NOT suspend — Room observes table changes on a background thread
// No withContext needed — Room handles it

// 3. allowMainThreadQueries() — ONLY in tests
Room.inMemoryDatabaseBuilder(...)
    .allowMainThreadQueries()  // lets tests call DAO without coroutines
    .build()

// 4. When DO you need withContext(IO)?
// File I/O: reading/writing raw files
// Non-suspend library calls: blocking APIs
// NOT needed for: Room, Retrofit (suspend funs), DataStore

// Rule: Room suspend functions are safe to call from viewModelScope.launch {}
// without any Dispatcher specification — they don't run on Main thread

Room manages its own threading for both suspend functions and Flow-returning DAOs. A suspend fun DAO method automatically dispatches to Room's internal coroutine dispatcher — backed by a thread pool optimised for database operations. You do not need to wrap it in withContext(Dispatchers.IO). Calling withContext(Dispatchers.IO) { dao.getUser(id) } is redundant: Room switches to its own dispatcher internally regardless of what thread the calling coroutine is on. The extra withContext adds a context switch that is unnecessary.

Flow-returning DAO methods emit on a background thread from Room's internal executor. The ViewModel collects on viewModelScope (which uses the main dispatcher), but the emissions are generated off the main thread. The coroutines machinery handles the thread hopping transparently. There is no threading configuration needed in the DAO, the repository, or the ViewModel for normal Room usage. This is a design goal of Room's coroutine integration: correct threading should be automatic, not something each developer has to remember to add.

allowMainThreadQueries() exists for tests and for cases where a blocking read on the main thread is genuinely acceptable — which in production Android development is never. Room enforces this restriction at runtime: any DAO call on the main thread in a normal database instance throws an exception. This is a safety guard, not an obstacle. Raw file I/O, on the other hand, does require withContext(Dispatchers.IO) — the file system APIs are synchronous and blocking. Reading from filesDir, writing to cacheDir, parsing a file from a URI obtained via SAF — these all block the calling thread and must be wrapped in an IO context switch or executed inside a launch(Dispatchers.IO) { } block.

Search in Room uses LIKE queries with indexes for performance, and FTS (Full-Text Search) for multi-word search. FTS gives you tokenised search — searching for "android kotlin" finds records containing both words.

// Basic LIKE search — works but slow on large tables without index
@Query("SELECT * FROM products WHERE name LIKE '%' || :query || '%'")
fun search(query: String): Flow<List<ProductEntity>>
// ❌ '%query%' can't use an index — full table scan every time

// Add index for prefix search (query%) — fast
@Entity(indices = [Index(value = ["name"])])
data class ProductEntity(@PrimaryKey val id: String, val name: String)

@Query("SELECT * FROM products WHERE name LIKE :query || '%'")
// 'query%' uses the index — fast for prefix search

// FTS (Full Text Search) — best for multi-word search
@Entity(tableName = "products")
data class ProductEntity(@PrimaryKey val id: String, val name: String, val description: String)

@Fts4(contentEntity = ProductEntity::class)  // creates FTS virtual table
@Entity(tableName = "products_fts")
data class ProductFts(val name: String, val description: String)

@Query("""SELECT products.* FROM products 
         JOIN products_fts ON products.rowid = products_fts.rowid
         WHERE products_fts MATCH :query""")
fun ftsSearch(query: String): Flow<List<ProductEntity>>

// With debounce for search-as-you-type
val searchResults = searchQuery
    .debounce(300)          // wait 300ms after last keystroke
    .distinctUntilChanged()  // don't search same query twice
    .flatMapLatest { query -> repo.search(query) }

The key distinction is between prefix search and contains search. A B-tree index on name accelerates name LIKE 'query%' because SQLite can seek to the first matching row and scan forward — it knows where to start. But name LIKE '%query%' forces a full table scan every time: SQLite must inspect every row because the match could start anywhere. On 1,000 rows the difference is imperceptible. On 100,000 rows, the indexed prefix search returns in under 1ms while the contains scan can take 200ms or more, blocking the main thread if you forget withContext(IO).

FTS (Full-Text Search) solves multi-column, multi-word search entirely. @Fts4(contentEntity = ProductEntity::class) creates a virtual shadow table that maintains an inverted index — a pre-built mapping of every token to the rows containing it. A query like MATCH 'bluetooth speaker' finds rows containing both words across all indexed columns in milliseconds, regardless of table size. Use FTS5 over FTS4 when available — it supports phrase queries, column filters, and row ranking by relevance. The debounce + flatMapLatest pattern is essential for the UI layer: debounce prevents a DB query on every keystroke, and flatMapLatest cancels the in-flight query when the user types again, ensuring the UI always shows results for the latest input only.

A bloated database slows down queries and wastes device storage. Regular pruning, smart data modelling, and avoiding storing unnecessary data keeps Room lean and fast.

// 1. TTL-based cleanup — delete old cached data
@Query("DELETE FROM products WHERE cachedAt < :cutoff")
suspend fun deleteOlderThan(cutoff: Long)

// Schedule cleanup with WorkManager (daily, in background)
val cutoff = System.currentTimeMillis() - 7 * 24 * 60 * 60_000L  // 7 days
dao.deleteOlderThan(cutoff)

// 2. Don't store binary blobs in Room
// ❌ Storing image bytes in a column bloats the database massively
@Entity data class ProductEntity(val image: ByteArray)  // ❌
// ✅ Store the URL, let Coil/Glide cache the image file
@Entity data class ProductEntity(val imageUrl: String)  // ✅

// 3. Limit list sizes — paginate instead of storing everything
@Query("SELECT * FROM products ORDER BY cachedAt DESC LIMIT :limit")
fun getRecent(limit: Int = 100): Flow<List<ProductEntity>>

// 4. VACUUM — defragment the database file
// After many deletes, SQLite file doesn't shrink — pages marked free
@Query("VACUUM")
suspend fun vacuum()
// Run after large batch deletions to reclaim disk space
// Expensive — run rarely (monthly), never on main thread

// 5. Use appropriate data types
// Store Long (8 bytes) not String for timestamps ("2024-01-15T10:30:00Z" = 22 bytes)
// Store Int status codes not String status names ("PENDING" = 7 bytes vs 1 byte)

// 6. Monitor database size
val dbFile = context.getDatabasePath("app.db")
Log.d("DB", "Size: ${dbFile.length() / 1024} KB")

Room databases grow indefinitely without active pruning. A news app that caches articles accumulates months of articles in Room, consuming hundreds of megabytes and slowing queries. TTL-based cleanup runs periodically via WorkManager: @Query("DELETE FROM articles WHERE cachedAt < :cutoff") suspend fun deleteOlderThan(cutoff: Long) called with a cutoff 7 or 30 days in the past. A PeriodicWorkRequest schedules this nightly when the device is idle. The cleanup keeps the database lean and queries fast without the user ever having to manage storage.

Never store image data as ByteArray in Room. A database of 1000 items where each item stores a 100KB thumbnail contains 100MB of binary data in the Room file. Queries slow dramatically as the database file grows. Coil, Glide, and Picasso all maintain their own disk cache in cacheDir — store the image URL as a String in Room and let the image loading library manage the binary file cache. This separates concerns correctly: Room manages structured metadata, the image library manages binary file storage. The same principle applies to any binary content: store a reference (URL, file path), not the bytes.

LIMIT-based pruning caps table size without time-based logic. @Query("DELETE FROM notifications WHERE id NOT IN (SELECT id FROM notifications ORDER BY receivedAt DESC LIMIT 100)") suspend fun pruneOldNotifications() retains only the 100 most recent rows and deletes the rest, called after each batch insert. After large deletes, run VACUUM via database.query("VACUUM", null) — SQLite marks deleted pages as free but does not shrink the file until VACUUM compacts it. Column type selection matters at scale: storing timestamps as Long (8 bytes) instead of TEXT ISO strings (20+ bytes) saves 12+ bytes per row, which is 12MB in a million-row table. Use INTEGER for booleans (1 bit stored as 1–8 bytes), REAL for floating point, and TEXT only when the value is genuinely a string.

A systematic storage code review catches the most expensive mistakes — data loss, security vulnerabilities, and performance issues — before they reach production.

// 1. ❌ Auth tokens in plain SharedPreferences
prefs.putString("token", accessToken)   // ❌ readable on rooted device
// ✅ EncryptedSharedPreferences

// 2. ❌ Missing database migration
@Database(version = 2)   // ❌ version bumped but no addMigrations() call
// ✅ .addMigrations(MIGRATION_1_2) + MigrationTestHelper test

// 3. ❌ DataStore accessed via multiple instances
val store1 = context.dataStore   // in SettingsViewModel
val store2 = context.dataStore   // in ThemeRepository — TWO instances? ❌ corruption risk
// ✅ @Singleton DataStore injected via Hilt

// 4. ❌ Room query on main thread
fun onCreate(...) {
    val user = db.userDao().getUserSync(id)  // ❌ blocking main thread
}
// ✅ suspend DAO + coroutine

// 5. ❌ Storing images as ByteArray in Room
@Entity data class ProductEntity(val thumbnail: ByteArray)  // ❌
// ✅ Store URL, let image loading library cache files

// 6. ❌ @Relation without @Transaction
@Query("SELECT * FROM users")
fun getUsersWithOrders(): Flow<List<UserWithOrders>>  // ❌ missing @Transaction
// ✅ @Transaction @Query(...)

// 7. ❌ Sensitive DB not excluded from backup
// AndroidManifest: android:allowBackup="true" with no backup_rules.xml
// ✅ backup_rules.xml excluding secure_prefs and sensitive databases

// 8. ❌ No cache TTL — database grows forever
@Entity data class SearchHistoryEntity(val query: String)  // no cachedAt ❌
// ✅ Add cachedAt: Long, schedule cleanup WorkManager

The second group of checks covers security and threading. Sensitive data — tokens, passwords, PII — must never live in regular SharedPreferences (plain XML on disk, world-readable on older APIs). It must go through EncryptedSharedPreferences or the Android Keystore. Similarly, check for database queries running on the main thread: any suspend fun in a DAO that runs without withContext(Dispatchers.IO) in tests, or any synchronous Room query without allowMainThreadQueries(), is a potential ANR. Enable StrictMode.setThreadPolicy in debug builds to catch these early — it throws a visible penalty whenever disk I/O happens on the main thread.

The third group covers correctness under failure. Check that bulk writes use transactions: ten individual inserts without a transaction trigger ten separate disk flushes and ten listener notifications — one transaction does it in a single flush. Check that migrations have tests: an untested migration that silently drops a column will pass code review but corrupt production data on upgrade. Verify that Room's schema export is committed to version control — the exported JSON is your migration contract, and a missing or stale export means your next migration starts from an unknown baseline. Finally, check for missing onConflict strategies on @Insert: the default is ABORT, which throws an exception on duplicate keys rather than replacing or ignoring, which is rarely the intended behavior for a cache.

The Repository is the single access point to all data sources — it decides whether to read from Room, the network, or DataStore. The ViewModel never touches a DAO or API directly; it always goes through the Repository.

// Without Repository — ViewModel knows too much
class ProductViewModel @Inject constructor(
    private val dao: ProductDao,    // ❌ ViewModel depends on DB layer
    private val api: ProductApi     // ❌ ViewModel depends on network layer
) : ViewModel()

// With Repository — clean separation
interface ProductRepository {
    fun observeProducts(): Flow<List<Product>>
    suspend fun refresh(): Result<Unit>
    suspend fun getProduct(id: String): Product?
}

class ProductRepositoryImpl @Inject constructor(
    private val dao: ProductDao,
    private val api: ProductApi
) : ProductRepository {

    // Room is the source of truth — UI always reads from here
    override fun observeProducts() =
        dao.observeAll().map { it.map { e -> e.toDomain() } }

    // Refresh fetches from API and writes to Room
    override suspend fun refresh() = runCatching {
        api.getProducts().also { dao.upsertAll(it.map { p -> p.toEntity() }) }
    }.map { }
}

// ViewModel — only knows about the Repository interface
@HiltViewModel
class ProductViewModel @Inject constructor(
    private val repo: ProductRepository   // ✅ depends on interface, not impl
) : ViewModel() {
    val products = repo.observeProducts()
        .stateIn(viewModelScope, SharingStarted.WhileSubscribed(5000), emptyList())
}

The interface is the critical design decision. Declaring interface ProductRepository rather than a concrete class means the ViewModel depends on an abstraction — in tests, you inject a FakeProductRepository that returns hardcoded flows without touching Room or the network. The fake takes five lines to write and the ViewModel test runs in milliseconds on the JVM. If the ViewModel depended on ProductRepositoryImpl directly, you'd need a real Room database, a real coroutine dispatcher, and an in-memory test setup to test even the simplest UI state logic. The interface boundary is the architectural decision that makes the whole testing pyramid fast.

The Repository is also the right place to enforce the domain model boundary. Room entities are database concerns — they may have denormalized columns, surrogate keys, or nullable fields forced by SQLite constraints. The ViewModel should never see ProductEntity; it should only see Product, your domain model. The Repository's map { it.toDomain() } call in the Flow pipeline performs this translation invisibly. When you change the Room schema — add a column, split a table, change a type — only the entity class and the mapper change. The ViewModel, the UI, and every test using the fake are completely unaffected.

@PrimaryKey marks the unique identifier for each row. Room supports auto-generated integer IDs, manual String UUIDs, and composite primary keys — each with different trade-offs for offline sync and performance.

// Option 1: Auto-generated Int — simple, local only
@Entity
data class NoteEntity(
    @PrimaryKey(autoGenerate = true) val id: Int = 0,
    val content: String
)
// ✅ Simple. ❌ IDs are local — clash if syncing with server.

// Option 2: UUID String — safe for sync
@Entity
data class ProductEntity(
    @PrimaryKey val id: String = UUID.randomUUID().toString(),
    val name: String
)
// ✅ Globally unique — safe to create offline and sync later
// ❌ Slightly larger storage, slower index than Int

// Option 3: Server-assigned ID — wait for server to confirm
@Entity
data class OrderEntity(
    @PrimaryKey val id: String,   // ID comes from server after creation
    val status: String
)
// ✅ Matches server ID exactly — no mapping needed
// ❌ Can't persist locally before server responds

// Option 4: Composite primary key — for relationship tables
@Entity(primaryKeys = ["userId", "productId"])
data class FavoriteEntity(
    val userId: String,
    val productId: String
)
// ✅ Enforces uniqueness of the pair — no separate ID needed

// Insert with autoGenerate — use 0 as placeholder, Room assigns real ID
val note = NoteEntity(id = 0, content = "Hello")  // 0 = "generate for me"
val newId = dao.insert(note)   // returns the assigned rowId

Auto-generate (@PrimaryKey(autoGenerate = true)) uses SQLite's ROWID mechanism — a 64-bit integer counter that increments with each insert. It's convenient for local-only data where the ID has no meaning outside the device. The risk comes with synchronization: if the same entity exists on two devices and both have auto-generated IDs, merging them produces duplicates or collisions. UUID (@PrimaryKey val id: String = UUID.randomUUID().toString()) is globally unique by design — safe to generate on-device and sync to a server without conflicts. The tradeoff is that UUID strings are larger than integers, slightly slower to index, and less human-readable in debug output.

Composite primary keys apply when no single column is unique but a combination is. @Entity(primaryKeys = ["userId", "productId"]) creates a table where each user-product pair is unique — useful for join/pivot tables in many-to-many relationships like a user's favourites list. Room enforces the uniqueness at the database level, so an attempt to insert a duplicate pair throws a constraint violation. You cannot use @PrimaryKey on individual fields when using composite keys — the constraint is declared at the entity level only. Avoid composite primary keys when you need to reference the entity from other tables: you'd have to carry both columns as a foreign key, which is more error-prone than a single surrogate key.

@Embedded flattens a nested object's fields into the parent table — one table, multiple logical groups of columns. @Relation links two separate tables via a foreign key and runs a second query to fetch the related rows.

// @Embedded — nested object stored in SAME table
data class Address(
    val street: String,
    val city: String,
    val pinCode: String
)

@Entity
data class UserEntity(
    @PrimaryKey val id: String,
    val name: String,
    @Embedded val address: Address   // street, city, pinCode as columns in users table
)
// DB columns: id | name | street | city | pinCode
// ✅ Single table, single query  ❌ Can't query across users by address easily

// Column prefix if same type embedded twice
@Embedded(prefix = "billing_")  val billingAddress: Address
@Embedded(prefix = "shipping_") val shippingAddress: Address
// billing_street | billing_city | shipping_street | shipping_city

// @Relation — linked data in SEPARATE table (two queries)
data class UserWithOrders(
    @Embedded val user: UserEntity,
    @Relation(parentColumn = "id", entityColumn = "userId")
    val orders: List<OrderEntity>
)
// Query 1: SELECT * FROM users
// Query 2: SELECT * FROM orders WHERE userId IN (ids from query 1)
// ✅ Proper normalisation  ✅ Query orders independently  ❌ Two queries

// When to use each:
// @Embedded: value objects always stored/retrieved with parent (Address, LatLng, Price)
// @Relation:  separate entities with own identity and lifecycle (User's Orders, Post's Comments)

@Embedded works by flattening a nested object's fields directly into the parent table. A data class Address(val street: String, val city: String) embedded in UserEntity produces a single table with columns street and city alongside the user fields — no JOIN required at query time. This makes it ideal for value objects that are logically part of the parent entity and always loaded together: a user's address, a product's dimensions, a coordinate pair. The limit is that the nested type must map to a fixed, flat set of columns — you cannot embed a list or a relationship with cardinality greater than one.

@Relation handles one-to-many and many-to-many associations where a JOIN would return multiple rows. Room executes two queries: one for the parent entities, one for all related children matching those parent IDs, then stitches them together in memory. This avoids the N+1 problem — you don't query children one parent at a time — but it means @Transaction is mandatory on any DAO method using @Relation. Without @Transaction, the parent query and child query run independently, and another thread could insert a child between them, producing an inconsistent result. Room's annotation processor warns you if you forget, but failing to add it is a concurrency bug waiting to happen under load.

runTest is the coroutine test builder from kotlinx-coroutines-test. It runs coroutines in a controlled test environment — skipping real delays, executing all coroutines eagerly, and making async code behave synchronously in tests.

// testImplementation("org.jetbrains.kotlinx:kotlinx-coroutines-test:1.9.0")

@RunWith(AndroidJUnit4::class)
class UserDaoTest {
    private lateinit var db: AppDatabase
    private lateinit var dao: UserDao

    @Before fun setUp() {
        db = Room.inMemoryDatabaseBuilder(
            ApplicationProvider.getApplicationContext(),
            AppDatabase::class.java
        ).build()
        dao = db.userDao()
    }
    @After fun tearDown() { db.close() }

    // runTest — wraps the test in a coroutine scope
    @Test fun insertAndRead() = runTest {
        val user = UserEntity("1", "Alice")
        dao.insert(user)                 // suspend — works inside runTest
        val result = dao.getUser("1")     // suspend — works inside runTest
        assertEquals("Alice", result?.name)
    }

    // Testing Flow with Turbine library
    // testImplementation("app.cash.turbine:turbine:1.1.0")
    @Test fun flowEmitsOnInsert() = runTest {
        dao.observeAll().test {           // .test {} is Turbine's Flow test builder
            awaitItem().also { assertTrue(it.isEmpty()) }  // initial empty emission
            dao.insert(UserEntity("1", "Alice"))
            awaitItem().also { assertEquals(1, it.size) }  // emission after insert
            cancelAndIgnoreRemainingEvents()
        }
    }

    // Testing with delay — runTest skips real time
    @Test fun debounceSearch() = runTest {
        delay(1000)   // virtual — no real 1 second wait
        // advanceTimeBy(500) / advanceUntilIdle() for precise control
    }
}

runTest from kotlinx-coroutines-test provides a controlled coroutine scope where time is virtual — you can call advanceTimeBy() or advanceUntilIdle() to drain the coroutine queue without actually waiting. For Room specifically, you must configure the test database with .allowMainThreadQueries() so Room doesn't throw when test code queries synchronously on the test thread, and use Room.inMemoryDatabaseBuilder() so each test starts with a clean slate. Pass UnconfinedTestDispatcher() as the query executor to Room's builder to keep all operations on the test coroutine — this prevents flaky timing issues where a Flow emission arrives after the test's assertion.

Turbine is a testing library that makes Flow assertions readable. Instead of manually collecting a Flow into a list and asserting on indices, Turbine's flow.test { ... } block lets you call awaitItem() sequentially — each call waits for the next emission and returns it for assertion. For Room Flows this is invaluable: you can insert a row, call awaitItem() to receive the updated list, assert on it, insert another row, call awaitItem() again. The test reads exactly like the sequence of events. cancelAndIgnoreRemainingEvents() at the end prevents Turbine from failing the test when there are unasserted emissions in the buffer.

Internal storage file I/O in modern Android uses standard Kotlin file APIs combined with coroutines. Always read/write on a background thread — file operations can block for hundreds of milliseconds.

// Writing a file — always on IO dispatcher
suspend fun saveJson(context: Context, filename: String, json: String) =
    withContext(Dispatchers.IO) {
        File(context.filesDir, filename).writeText(json)
    }

// Reading a file
suspend fun readJson(context: Context, filename: String): String? =
    withContext(Dispatchers.IO) {
        val file = File(context.filesDir, filename)
        if (file.exists()) file.readText() else null
    }

// Subdirectory — create if missing
suspend fun saveInvoice(context: Context, id: String, bytes: ByteArray) =
    withContext(Dispatchers.IO) {
        val dir = File(context.filesDir, "invoices").also { it.mkdirs() }
        File(dir, "$id.pdf").writeBytes(bytes)
    }

// Listing files
fun listInvoices(context: Context): List<String> =
    File(context.filesDir, "invoices")
        .listFiles()
        ?.map { it.name }
        ?: emptyList()

// Delete old files
suspend fun deleteOldInvoices(context: Context, cutoffMs: Long) =
    withContext(Dispatchers.IO) {
        File(context.filesDir, "invoices")
            .listFiles()
            ?.filter { it.lastModified() < cutoffMs }
            ?.forEach { it.delete() }
    }

// Cache files — use cacheDir for re-downloadable content
val cacheFile = File(context.cacheDir, "thumbnail_$id.jpg")
// OS may delete cacheDir contents when storage is low — always handle missing files

Android offers three internal storage locations with different lifecycle behaviors. context.filesDir is permanent storage — files persist until the app is uninstalled or the user explicitly clears app data. context.cacheDir is volatile — the system may delete files here when the device runs low on storage, so it's for regenerable content only (image thumbnails, compiled templates). context.getExternalFilesDir(null) is app-specific external storage — no permission required on Android 10+, but files are deleted on uninstall and the directory is visible to users browsing with a file manager. Choose based on whether the data is user-created (files dir), regenerable (cache dir), or meant to be accessible outside the app (external files dir).

When sharing a private file with another app — opening a PDF in a viewer, sharing an image with a social app — you must use FileProvider to generate a content:// URI rather than a file:// URI. On Android 7+, passing a file:// URI to an external app via an Intent throws FileUriExposedException and crashes immediately. FileProvider grants the receiving app temporary read permission scoped to the lifetime of the Intent — the permission is automatically revoked when the receiving activity finishes. Declare the FileProvider in the manifest with a file_paths.xml resource that maps path aliases to actual directories, then call FileProvider.getUriForFile() to produce the shareable URI.

A database transaction groups multiple operations so they either all succeed or all fail together. Without @Transaction, a crash between two related writes leaves the database in a corrupt, inconsistent state.

// Problem without transaction:
suspend fun placeOrder(order: OrderEntity, items: List<OrderItemEntity>) {
    orderDao.insert(order)        // succeeds
    // 💥 app crashes here
    orderItemDao.insertAll(items) // never runs → order with no items in DB!
}

// With @Transaction — atomic: all or nothing
@Dao
abstract class OrderDao {

    @Insert abstract suspend fun insertOrder(order: OrderEntity)
    @Insert abstract suspend fun insertItems(items: List<OrderItemEntity>)

    @Transaction
    open suspend fun placeOrder(order: OrderEntity, items: List<OrderItemEntity>) {
        insertOrder(order)
        insertItems(items)
        // If insertItems fails → insertOrder is automatically rolled back
        // Database remains consistent — no orphaned orders
    }
}

// useDatabase() — lower-level transaction control
suspend fun transferCredits(fromId: String, toId: String, amount: Int) {
    db.withTransaction {
        val from = dao.getUser(fromId) ?: throw Exception("User not found")
        val to   = dao.getUser(toId)   ?: throw Exception("User not found")
        if (from.credits < amount) throw Exception("Insufficient credits")
        dao.update(from.copy(credits = from.credits - amount))
        dao.update(to.copy(credits = to.credits + amount))
        // Both updates succeed or both are rolled back — credits never lost
    }
}

// @Transaction also needed for @Relation queries — makes multi-query read atomic
@Transaction
@Query("SELECT * FROM orders")
abstract fun observeOrdersWithItems(): Flow<List<OrderWithItems>>

Room transactions map directly to SQLite transactions: BEGIN TRANSACTION before the first operation and COMMIT after the last. All operations inside the lambda see a consistent snapshot — if you read a row at the start of the transaction, it has the same value at the end, regardless of writes from other threads. The @Transaction annotation on a DAO suspend function wraps the entire function body in this atomic block. For multi-table operations like inserting an order, its line items, and updating the product stock simultaneously, a transaction guarantees that either all three writes land together or none of them do — there's no intermediate state where the order exists but its items don't.

Error handling inside a transaction is straightforward: any uncaught exception causes an automatic rollback. Room re-throws the exception after rolling back, so your repository can catch it and return a failure result. For manual rollback control — for instance, when a validation check mid-transaction determines the operation should not proceed — throw any exception inside the transaction lambda. Room treats all exceptions identically: rollback and rethrow. The important performance note is that long-running transactions block other writers for their entire duration. Keep transaction bodies short: fetch the data you need before the transaction, perform the writes inside it, and avoid network calls or heavy computation within a transaction block.

LIKE does a sequential scan — it checks every row. FTS (Full-Text Search) uses a pre-built inverted index — like a book's index vs reading every page. FTS is orders of magnitude faster on large tables.

// LIKE — full table scan, O(n)
@Query("SELECT * FROM notes WHERE content LIKE '%' || :q || '%'")
fun searchLike(q: String): Flow<List<NoteEntity>>
// 100 rows: ~1ms  |  10,000 rows: ~100ms  |  1,000,000 rows: ~10 seconds
// ❌ '%query%' never uses an index — SQLite must read every row

// FTS4 — inverted index, O(log n)
@Entity(tableName = "notes")
data class NoteEntity(@PrimaryKey val id: String, val title: String, val content: String)

@Fts4(contentEntity = NoteEntity::class)
@Entity(tableName = "notes_fts")
data class NoteFts(val title: String, val content: String)

@Transaction
@Query("""SELECT notes.* FROM notes 
         INNER JOIN notes_fts ON notes.rowid = notes_fts.rowid
         WHERE notes_fts MATCH :q
         ORDER BY rank""")
fun searchFts(q: String): Flow<List<NoteEntity>>
// 1,000,000 rows: still ~1ms  (uses tokenised inverted index)

// FTS special syntax:
// "android kotlin"  → must contain BOTH words
// "android OR kotlin" → contains either
// "android*"         → prefix match (android, androidy, androidx)
// "title:android"    → search only in title column

// FTS5 — more features (Room supports @Fts4 and @Fts5)
@Fts5(contentEntity = NoteEntity::class)
@Entity(tableName = "notes_fts5")
data class NoteFts5(val title: String, val content: String)
// FTS5 adds relevance ranking via 'rank' column — sort by relevance

LIKE '%query%' implements a contains search in SQL — it finds rows where the column contains the query string anywhere. The leading wildcard prevents index use: SQLite cannot skip to matching rows because the match could start at any position. This forces a full table scan — every row is compared against the pattern. For tables under a few thousand rows this is fast enough. For tables with tens of thousands of rows, or when the search is executed on every keystroke, the cumulative effect is noticeable latency and battery drain from unnecessary I/O.

FTS (Full-Text Search) is SQLite's built-in inverted index for text columns. Declare an FTS entity: @Fts4(contentEntity = ProductEntity::class) @Entity data class ProductFts(val name: String, val description: String). Room creates a separate FTS index table and keeps it in sync with the content entity. Queries use the MATCH operator: @Query("SELECT * FROM products JOIN ProductFts ON products.rowid = ProductFts.rowid WHERE ProductFts MATCH :query"). The FTS index tokenises text at insert time and builds an inverted map from tokens to rows — queries are near-constant time regardless of table size. FTS5 adds relevance ranking via ORDER BY rank and better performance than FTS4.

The search UX requires two coroutine operators. debounce(300) on the search query flow prevents a database query on every keystroke — it waits 300ms after the last keystroke before emitting the query, so a user typing "kotlin" triggers one query at the end rather than six queries for "k", "ko", "kot", etc. flatMapLatest cancels the previous search coroutine when a new query arrives: searchQueryFlow.debounce(300).flatMapLatest { query -> dao.search(query) }. If the previous query is still executing when a new one arrives, it is cancelled immediately — no risk of stale results from a slow earlier query arriving after a faster later query.

SQLite supports multi-process access but Room's in-memory state (invalidation tracker, WAL) breaks with multiple processes. Room provides enableMultiInstanceInvalidation as a partial solution — but it comes with significant trade-offs.

// Problem: Two processes, one SQLite file
// Process 1 (app): reads from Room, caches query results
// Process 2 (service): writes new data to SQLite
// Result: Process 1's Flow NEVER updates — its invalidation tracker is separate!

// Solution 1: enableMultiInstanceInvalidation (Room 2.3+)
val db = Room.databaseBuilder(ctx, AppDatabase::class.java, "app.db")
    .enableMultiInstanceInvalidation()  // uses IPC to notify other processes of changes
    .build()
// ✅ Flow in Process 1 now invalidates when Process 2 writes
// ❌ Higher overhead — IPC calls on every write
// ❌ WAL mode must be disabled (WAL doesn't support multi-process)
// ❌ More complex debugging — race conditions across processes

val db = Room.databaseBuilder(ctx, AppDatabase::class.java, "app.db")
    .enableMultiInstanceInvalidation()
    .setJournalMode(JournalMode.TRUNCATE)  // WAL not supported with multi-instance
    .build()

// Solution 2: Avoid multi-process entirely (preferred)
// Keep the background service in the same process (no ':remote' in Manifest)
// Use foreground service or WorkManager (same process)
// Only use separate processes for true isolation (crash containment)

// ContentProvider bridge — for legitimate multi-process DB access
// Wrap Room behind a ContentProvider
// Process 2 queries the ContentProvider → ContentProvider reads Room → returns cursor
// Oldest pattern, most compatible, but lots of boilerplate

SQLite itself supports multi-process access via file-level locking, but Room adds an in-memory invalidation tracker that breaks under multi-process scenarios. When process A writes to the database, Room's tracker in process A marks affected tables as dirty and notifies all active Flow collectors in process A. Process B has its own Room instance, its own tracker, and no awareness of process A's write — its Flows never emit the updated data. This means that if your main process and a background process (common with WorkManager's separate process, or a remote service) both use Room, the background process's writes are invisible to the main process's live queries until the app restarts or the database connection is recreated.

The recommended solution for multi-process database access is to route all writes through a single process using a ContentProvider or a bound Service. The ContentProvider approach is simpler: one process owns the Room database and exposes a ContentProvider; all other processes query and write through the ContentProvider's URI-based API. An alternative is MultiInstanceInvalidationService, enabled via .enableMultiInstanceInvalidation() in Room's builder — it uses a background service to broadcast invalidation signals across processes, restoring cross-process reactivity. It adds overhead and is not recommended unless you genuinely need multiple processes observing the same live data simultaneously.

apply() writes asynchronously — it returns immediately and queues the write. commit() writes synchronously — it blocks until the write is flushed to disk. In modern Android you should use DataStore instead of both, but this distinction is still a common interview question.

// apply() — asynchronous, fire-and-forget
prefs.edit()
    .putString("username", "alice")
    .apply()
// ✅ Returns immediately — doesn't block
// ✅ Writes are queued and batched
// ❌ No confirmation — you can't know if the write succeeded
// ❌ If process killed immediately after apply(), write may be lost

// commit() — synchronous, blocking
val success = prefs.edit()
    .putString("username", "alice")
    .commit()   // blocks current thread until written to disk
// ✅ Returns Boolean — true if successful
// ❌ Blocks the thread — NEVER call on main thread (ANR risk)
// ❌ On main thread, even 5ms disk write can cause jank

// When to use which (for legacy code maintaining SharedPreferences):
// apply()  → almost always — async is fine for preferences
// commit() → if you MUST confirm the write before proceeding
//            (e.g., writing before process intentionally terminates)
//            Always on background thread if using commit()

// Modern answer: use DataStore instead
suspend fun saveUsername(name: String) {
    dataStore.edit { it[USERNAME_KEY] = name }
    // ✅ Async (suspend), ✅ Returns after write, ✅ Throws on failure
}

apply() performs an asynchronous write: it commits changes to the in-memory SharedPreferences immediately — so a subsequent getString() call in the same process sees the new value — but schedules the actual disk write on a background thread. This is safe for most use cases and never blocks the calling thread. commit() performs a synchronous write: the calling thread blocks until the data is fully written to disk and returns a boolean indicating success or failure. Calling commit() on the main thread risks an ANR if the disk is slow (common on low-end devices, first write after install, or when the device storage is nearly full). The only legitimate use of commit() is when you need to guarantee the write has landed before proceeding — for example, writing a crash flag before launching a dangerous operation.

Both methods have a subtler problem: SharedPreferences loads the entire file into memory on first access and keeps it there. For large preference files this is wasteful; for frequently-written preferences it causes contention. DataStore solves both: writes are always asynchronous (suspend functions), reads are always via a Flow (never blocking), and the file uses Protocol Buffers or a typed preferences schema rather than an untyped XML map. The migration path from SharedPreferences to DataStore is mechanical — DataStore provides a SharedPreferencesMigration class that reads all existing key-value pairs from the old file on first access and writes them into the DataStore, then deletes the XML file. After migration, all reads and writes go through DataStore's coroutine API.

Drafts need to survive process death, so SavedStateHandle alone isn't enough for large data. The complete solution combines Room (persistent storage) with SavedStateHandle (lightweight session state) and auto-save with debounce.

// Room entity for drafts
@Entity(tableName = "drafts")
data class DraftEntity(
    @PrimaryKey val id: String,
    val content: String,
    val lastModified: Long = System.currentTimeMillis()
)

// ViewModel — auto-saves with debounce
@HiltViewModel
class DraftViewModel @Inject constructor(
    private val dao: DraftDao,
    private val saved: SavedStateHandle
) : ViewModel() {

    private val draftId = saved.get<String>("draftId") ?: UUID.randomUUID().toString()

    private val _content = MutableStateFlow("")
    val content: StateFlow<String> = _content

    init {
        // Restore draft from Room on launch
        viewModelScope.launch {
            dao.getDraft(draftId)?.let { _content.value = it.content }
        }

        // Auto-save with 500ms debounce — saves while typing
        viewModelScope.launch {
            _content
                .debounce(500)
                .distinctUntilChanged()
                .collect { text ->
                    if (text.isNotBlank())
                        dao.upsert(DraftEntity(draftId, text))
                }
        }
    }

    fun onTextChanged(text: String) { _content.value = text }

    fun submit() {
        viewModelScope.launch {
            // Submit logic...
            dao.delete(draftId)   // clean up after successful submit
        }
    }
}

// Compose UI — just collect the StateFlow
var text by remember { mutableStateOf("") }
LaunchedEffect(Unit) { vm.content.collect { text = it } }
TextField(value = text, onValueChange = { text = it; vm.onTextChanged(it) })

The auto-save mechanism needs debouncing to avoid writing to Room on every keystroke. A MutableStateFlow holding the draft content, combined with .debounce(500) and .distinctUntilChanged(), produces a downstream flow that only emits when the user pauses typing for 500ms. This flow drives a viewModelScope.launch that calls repository.saveDraft(content). The result is a system that feels instant to the user — the UI updates immediately via the StateFlow — but writes to disk at most twice per second regardless of typing speed. Collecting the debounced flow in viewModelScope ensures the save is automatically cancelled if the user navigates away before the debounce period elapses.

Process death recovery requires Room, not SavedStateHandle alone. SavedStateHandle survives the back stack but not process death from memory pressure — on a low-RAM device, the process can be killed while the app is in the background. The correct pattern is to persist the draft to Room on every auto-save tick, then load it from Room when the screen opens: if a draft row exists for the current item ID, pre-populate the editor. A lastModified timestamp on the draft row lets you show a "resume draft from 3 minutes ago?" prompt rather than silently overwriting what the user was typing. On explicit save or discard, delete the draft row so future opens start clean.

Room can export a JSON snapshot of your database schema to a file. These schema files are version-controlled alongside your code and enable MigrationTestHelper to validate your migrations without running them on a real device.

// Enable schema export in build.gradle.kts
android {
    defaultConfig {
        javaCompileOptions {
            annotationProcessorOptions {
                arguments += mapOf(
                    "room.schemaLocation" to "$projectDir/schemas"
                )
            }
        }
    }
}

// For KSP (modern, recommended):
ksp {
    arg("room.schemaLocation", "$projectDir/schemas")
}

// After build, Room generates: schemas/com.example.AppDatabase/1.json
// Content (simplified):
// {
//   "formatVersion": 1,
//   "database": {
//     "version": 1,
//     "entities": [{ "tableName": "users", "columns": [...] }]
//   }
// }

// Commit schemas to source control — git add schemas/
// Every version bump creates a new JSON file
// schemas/1.json, schemas/2.json, schemas/3.json

// Using schemas in migration tests
@RunWith(AndroidJUnit4::class)
class MigrationTest {
    @get:Rule
    val helper = MigrationTestHelper(
        InstrumentationRegistry.getInstrumentation(),
        AppDatabase::class.java
    )

    @Test fun migrate1To3() {
        helper.createDatabase("test.db", 1)       // creates v1 from schemas/1.json
        helper.runMigrationsAndValidate(           // runs migrations, validates against schemas/3.json
            "test.db", 3, true,
            MIGRATION_1_2, MIGRATION_2_3
        )
    }
}

Schema export produces a JSON file that captures the exact state of your database at a given version: every table, every column with its type and constraints, every index, and every view. The file is generated at build time by Room's annotation processor when room.schemaLocation is configured in your build.gradle. This JSON is the baseline for writing migrations — when you bump the version number, you diff the new export against the old one to understand exactly what changed and write the migration SQL accordingly. Without the export, you're guessing at the previous schema from memory or git history, which leads to migrations that work in dev but fail for users upgrading from two or three versions back.

The schema files must be committed to version control, not gitignored. Each version gets its own file: schemas/1.json, schemas/2.json, and so on. Room's MigrationTestHelper in your test suite uses these files to validate migrations: it creates a database at version N using the old schema, applies your migration, and verifies the resulting schema matches version N+1's export exactly. This test catches the most dangerous migration bugs — a migration that successfully runs SQL but produces the wrong schema (wrong column type, missing index, wrong nullability). Run migration tests in CI so a broken migration never reaches production.

A read-through cache is transparent to callers — they just call getProduct(id) and get data. The repository internally checks Room first, fetches from the network only on a cache miss, and saves back to Room for next time.

// Read-through cache — callers don't know where data comes from
class ProductRepositoryImpl @Inject constructor(
    private val dao: ProductDao,
    private val api: ProductApi,
    @IoDispatcher private val io: CoroutineDispatcher
) {
    suspend fun getProduct(id: String): Product = withContext(io) {
        // Step 1: Check Room cache
        val cached = dao.getProduct(id)
        if (cached != null) {
            return@withContext cached.toDomain()   // Cache hit — return immediately
        }

        // Step 2: Cache miss — fetch from network
        val remote = api.getProduct(id)

        // Step 3: Save to Room for next time
        dao.insert(remote.toEntity())

        remote.toDomain()   // Return fresh data
    }

    // With staleness check — refresh if cache is old
    suspend fun getProductFresh(id: String, maxAgeMs: Long = 300_000): Product {
        val cached = dao.getProduct(id)
        val isStale = cached == null ||
            System.currentTimeMillis() - cached.cachedAt > maxAgeMs

        return if (!isStale) {
            cached!!.toDomain()
        } else {
            api.getProduct(id).also { dto ->
                dao.insert(dto.toEntity().copy(cachedAt = System.currentTimeMillis()))
            }.toDomain()
        }
    }
}

// ViewModel — single call, cache is transparent
fun loadProduct(id: String) {
    viewModelScope.launch {
        val product = repo.getProduct(id)  // doesn't know if from cache or network
        _state.value = UiState.Success(product)
    }
}

Staleness detection is what separates a useful cache from one that shows stale data indefinitely. Add a lastFetched: Long column to your cached entities storing the Unix timestamp of the last successful network fetch. In the repository's cache-check logic, compare System.currentTimeMillis() - lastFetched against a threshold (e.g. 15 minutes for a product catalog, 30 seconds for a live feed). If the cache is fresh, emit Room data only. If it's stale, emit Room data immediately for instant display, then trigger a background refresh. The UI shows real content in under 20ms rather than a loading spinner, and silently updates when fresh data arrives via the Room Flow emission.

Error handling in a read-through cache requires explicit design. If the cache is populated and the network refresh fails — the server is down, the user is offline — the correct behavior is to keep showing the cached data and surface a subtle "last updated 2 hours ago" indicator rather than replacing content with an error screen. Model this with a sealed class result: CacheResult.Fresh(data), CacheResult.Stale(data, error), and CacheResult.Empty(error). The UI maps each state to the appropriate presentation — showing data with a warning is almost always better than showing nothing. Reserve the full error state for Empty only, when there is literally no cached data to display.

@RawQuery lets you build a query string at runtime instead of at compile time. Useful for dynamic filters, sorting, and search combinations that can't be expressed with static @Query annotations.

// @Query — static, validated at compile time
@Query("SELECT * FROM products WHERE category = :cat ORDER BY price ASC")
fun getByCategory(cat: String): Flow<List<ProductEntity>>
// ❌ Can't dynamically change 'ORDER BY price' to 'ORDER BY name'

// @RawQuery — dynamic, built at runtime
@Dao
interface ProductDao {
    @RawQuery(observedEntities = [ProductEntity::class])  // needed for Flow support
    fun rawQuery(query: SupportSQLiteQuery): Flow<List<ProductEntity>>
}

// Build dynamic query safely (prevents SQL injection)
fun buildProductQuery(
    category: String? = null,
    sortBy: String = "name",
    ascending: Boolean = true
): SupportSQLiteQuery {
    val args = mutableListOf<Any>()
    var sql = "SELECT * FROM products"
    category?.let { sql += " WHERE category = ?"; args.add(it) }
    val col   = if (sortBy in listOf("name", "price")) sortBy else "name"  // whitelist!
    val order = if (ascending) "ASC" else "DESC"
    sql += " ORDER BY $col $order"
    return SimpleSQLiteQuery(sql, args.toTypedArray())
}

// Usage in Repository
fun observeProducts(category: String?, sort: String, asc: Boolean) =
    dao.rawQuery(buildProductQuery(category, sort, asc))

// ⚠️ Security: NEVER interpolate user input directly into SQL string
// ✅ Use ? placeholders for values
// ✅ Whitelist column names before using in ORDER BY (can't use ? for column names)

@RawQuery trades compile-time safety for runtime flexibility. Room cannot validate a dynamically-constructed SQL string at build time, so a typo in a column name or a malformed WHERE clause becomes a runtime exception rather than a build error. To partially recover type safety, annotate @RawQuery(observedEntities = [ProductEntity::class]) — this tells Room which tables the query touches so it can still trigger Flow re-emissions when those tables change. Without observedEntities, a @RawQuery returning a Flow never emits updates after the initial load, because Room doesn't know which tables to watch.

SQL injection is the critical security concern with @RawQuery. Never concatenate user-supplied input directly into the query string — a malicious value like ' OR '1'='1 in a search field would return all rows or worse. Always use SupportSQLiteQuery with bound parameters: SimpleSQLiteQuery("SELECT * FROM products WHERE category = ?", arrayOf(userInput)). The ? placeholder is handled by SQLite's parameterized query mechanism, which sanitizes the input before binding it. Treat @RawQuery as a last resort — cover it with a unit test using an in-memory database for every query variation you support, since the annotation processor won't catch your mistakes at build time.

Room performance issues usually come from missing indexes, loading too much data at once, or doing heavy work on the main thread. The fix is a combination of indexing, pagination, and profiling.

// Step 1: Diagnose — enable query logging
val db = Room.databaseBuilder(ctx, AppDatabase::class.java, "app.db")
    .setQueryCallback(RoomDatabase.QueryCallback { sql, args ->
        Log.d("RoomQuery", "SQL: $sql, Args: $args")
    }, Executors.newSingleThreadExecutor())
    .build()

// Step 2: Check for missing indexes — EXPLAIN QUERY PLAN
// Run in DB Browser for SQLite or adb shell:
// EXPLAIN QUERY PLAN SELECT * FROM products WHERE category = 'shoes' ORDER BY price
// If output shows "SCAN TABLE products" → full scan, needs index
// If output shows "SEARCH TABLE products USING INDEX" → good

// Step 3: Add indexes for frequently queried columns
@Entity(indices = [
    Index(value = ["category"]),                    // WHERE category = ?
    Index(value = ["category", "price"]),           // WHERE category = ? ORDER BY price
    Index(value = ["userId"])                         // WHERE userId = ? (foreign key)
])
data class ProductEntity(...)

// Step 4: Paginate — never load 50,000 rows at once
@Query("SELECT * FROM products ORDER BY name LIMIT :pageSize OFFSET :offset")
suspend fun getPage(pageSize: Int, offset: Int): List<ProductEntity>
// Or use @PagingSource — let Paging 3 manage pagination

// Step 5: Select only needed columns
data class ProductSummary(val id: String, val name: String, val price: Double)
@Query("SELECT id, name, price FROM products ORDER BY name")
fun observeSummaries(): Flow<List<ProductSummary>>
// Don't load 50 columns for a list that shows 3

Missing indexes are the most common cause of slow Room queries on large datasets. Every column used in a WHERE clause, ORDER BY, or JOIN condition is a candidate for an index. Add them declaratively: @Entity(indices = [Index("category"), Index(value = ["userId", "createdAt"])]). A composite index on (userId, createdAt) accelerates queries that filter by user and sort by date — the two most common operations in a feed. Use EXPLAIN QUERY PLAN in the Android Studio Database Inspector to verify your indexes are being used: output starting with "SEARCH TABLE ... USING INDEX" confirms the index is active; "SCAN TABLE" means it's doing a full table scan and you need to add or adjust an index.

Loading too much data at once is the second major performance sink. A query returning 50,000 rows into a List allocates all of them in memory simultaneously, triggering GC pressure and potentially an OOM on low-RAM devices. The fix is Paging 3: replace fun getItems(): Flow<List<ItemEntity>> with fun getItemsPaged(): PagingSource<Int, ItemEntity>. Room generates the PagingSource implementation automatically. Paging loads only the rows needed for the current viewport plus a small prefetch buffer, keeping memory flat regardless of total dataset size. For the specific case of search, combine Paging with FTS: the FTS query narrows the result set before Paging pages through it, so even a broad search term doesn't load thousands of rows at once.

AutoMigration (Room 2.4+) generates migration SQL automatically for simple schema changes — adding columns, adding tables, renaming with @RenameColumn. Complex changes like splitting a table or changing column types still require manual migrations.

// AutoMigration — Room generates the SQL for you
@Database(
    entities = [UserEntity::class],
    version = 3,
    autoMigrations = [
        AutoMigration(from = 1, to = 2),              // simple add column
        AutoMigration(from = 2, to = 3, spec = AppDatabase.Migration2to3::class)
    ]
)
abstract class AppDatabase : RoomDatabase() {

    // Spec needed when Room can't infer intent (rename vs delete+add)
    @RenameColumn(tableName = "users", fromColumnName = "user_name", toColumnName = "name")
    class Migration2to3 : AutoMigrationSpec
}

// ✅ AutoMigration handles:
// - Adding a new column with default value
// - Adding a new table
// - Renaming a column (@RenameColumn spec)
// - Renaming a table (@RenameTable spec)
// - Deleting a column (@DeleteColumn spec)

// ❌ Manual Migration required for:
// - Changing a column's type (TEXT → INTEGER)
// - Splitting one table into two
// - Merging two tables into one
// - Complex data transformations during migration
// - Adding a NOT NULL column without a default value

// Manual Migration for complex changes
val MIGRATION_3_4 = object : Migration(3, 4) {
    override fun migrate(db: SupportSQLiteDatabase) {
        // Complex: split users into users + user_profiles
        db.execSQL("CREATE TABLE user_profiles AS SELECT id, bio, avatar FROM users")
        db.execSQL("ALTER TABLE users DROP COLUMN bio")
    }
}

AutoMigration handles structural changes that have unambiguous SQL translations. Adding a nullable column, deleting a column with @DeleteColumn, renaming a column with @RenameColumn, renaming a table with @RenameTable — these all map to a single ALTER TABLE statement and Room generates it automatically. The spec annotation goes on the database class: @Database(autoMigrations = [AutoMigration(from = 2, to = 3, spec = MyDb.Migration2to3::class)]) where the spec interface carries the structural change annotation. Room validates that the generated migration produces a schema matching the version 3 export — if it doesn't, the build fails, not the user's database.

Manual migration is required for anything that involves transforming data rather than just changing structure. Splitting one table into two, merging columns, computing a new column's value from existing data, changing a column's type — these require you to write the SQL yourself. The manual migration class extends Migration(from, to) and overrides migrate(database: SupportSQLiteDatabase). A common pattern for type changes: create a new temp table with the correct schema, INSERT INTO temp SELECT ... FROM old with the transformation inline, drop the old table, rename temp to the original name. Always test manual migrations with MigrationTestHelper — it validates the final schema and runs the migration against a real SQLite file, not just in memory.

Offline-first favourites need optimistic UI (instant heart toggle), local persistence (Room), and background sync (WorkManager). The user sees instant feedback — the sync is invisible.

// Room entity
@Entity(tableName = "favourites",
    foreignKeys = [ForeignKey(ProductEntity::class, ["id"], ["productId"], onDelete = ForeignKey.CASCADE)])
data class FavouriteEntity(
    @PrimaryKey val productId: String,
    val syncStatus: String = "PENDING"   // PENDING | SYNCED
)

// Repository — optimistic toggle
suspend fun toggleFavourite(productId: String) {
    val exists = dao.isFavourite(productId)
    if (exists) {
        dao.delete(productId)             // immediate local delete
    } else {
        dao.insert(FavouriteEntity(productId))  // immediate local insert
    }
    scheduleSync()                          // queue background sync
}

fun observeIsFavourite(productId: String): Flow<Boolean> =
    dao.observeIsFavourite(productId)

fun scheduleSync() {
    WorkManager.getInstance(context).enqueueUniqueWork(
        "fav-sync",
        ExistingWorkPolicy.REPLACE,            // debounce rapid toggles
        OneTimeWorkRequestBuilder<FavSyncWorker>()
            .setConstraints(Constraints(requiresNetwork = true))
            .setInitialDelay(2, TimeUnit.SECONDS)  // wait for more toggles
            .build()
    )
}

// ViewModel — heart icon reacts instantly to Room Flow
val isFav = repo.observeIsFavourite(productId)
    .stateIn(viewModelScope, SharingStarted.WhileSubscribed(5000), false)

fun onHeartTap() { viewModelScope.launch { repo.toggleFavourite(productId) } }

Optimistic UI is the pattern that makes offline-first feel instant. When the user taps the favourite heart, update Room immediately and flip the UI — don't wait for the network. Launch a background coroutine to sync the change to the server. If the sync succeeds, nothing changes from the user's perspective (the UI was already correct). If it fails, roll back the local Room state and show a brief error snackbar. The user experiences a zero-latency tap response in the 99% case and a one-second rollback in the 1% failure case, which is far better than a loading spinner on every favourite action.

Sync conflict resolution requires a strategy decision. The simplest is last-write-wins using a lastModified timestamp: when syncing, compare the server timestamp against the local timestamp and keep whichever is newer. This works well for favourites because conflicts are rare and the cost of losing one is low. For more sensitive data, server-wins is safer: treat the server as the source of truth and overwrite local changes on sync. Store pending unsynced changes in a separate pending_sync table rather than modifying the main entity directly — this lets you track exactly what needs to be pushed and retry failed syncs via WorkManager's exponential backoff, without corrupting the displayed data during the retry window.

RemoteMediator bridges your network API and Room. When Paging 3 runs out of data in Room, RemoteMediator fetches the next page from the API and saves it to Room — Paging then reads from Room seamlessly.

// RemoteMediator — fetches from API, writes to Room
@OptIn(ExperimentalPagingApi::class)
class ProductRemoteMediator @Inject constructor(
    private val api: ProductApi,
    private val db: AppDatabase
) : RemoteMediator<Int, ProductEntity>() {

    override suspend fun load(
        loadType: LoadType,
        state: PagingState<Int, ProductEntity>
    ): MediatorResult {
        val page = when (loadType) {
            LoadType.REFRESH -> 1                   // start from beginning
            LoadType.PREPEND -> return MediatorResult.Success(endOfPaginationReached = true)
            LoadType.APPEND -> {
                val lastItem = state.lastItemOrNull()
                    ?: return MediatorResult.Success(endOfPaginationReached = true)
                // Calculate next page from last loaded item
                db.remoteKeyDao().getPage(lastItem.id) + 1
            }
        }

        return try {
            val response = api.getProducts(page = page, pageSize = state.config.pageSize)
            db.withTransaction {
                if (loadType == LoadType.REFRESH) db.productDao().clearAll()
                db.productDao().insertAll(response.items.map { it.toEntity() })
            }
            MediatorResult.Success(endOfPaginationReached = response.items.isEmpty())
        } catch (e: Exception) {
            MediatorResult.Error(e)
        }
    }
}

// Wire up with Pager
val products = Pager(
    config = PagingConfig(pageSize = 20),
    remoteMediator = productRemoteMediator,
    pagingSourceFactory = { db.productDao().paginate() }  // always reads from Room
).flow.cachedIn(viewModelScope)

RemoteMediator is triggered by Paging 3 when the user scrolls near the end of the locally cached pages, or on the initial load when the cache is empty. Its load(loadType, state) function receives a LoadType — REFRESH (pull-to-refresh or initial load), PREPEND (scroll to top), or APPEND (scroll to bottom) — and a PagingState containing the last loaded page and item. The mediator fetches the appropriate page from the network and writes it to Room via a transaction that also updates the RemoteKeys table. After the write, Paging's PagingSource (backed by Room) automatically invalidates and re-queries, so the new items appear in the list without any manual refresh call.

The RemoteKeys table is what enables correct pagination after app restart or process death. Without it, you'd have to re-fetch from page 1 on every fresh launch even if Room has 200 cached items. RemoteKeys stores the next and previous page keys for each cached item, keyed by item ID. On REFRESH, clearing both the data table and the remote keys table together in a single transaction ensures the two stay in sync — stale remote keys pointing to deleted rows cause incorrect page offsets on the next load. Return MediatorResult.Error(throwable) on network failure: Paging surfaces this as a LoadState.Error that your UI can show as a retry button without clearing the existing cached content.

Room maps Kotlin nullable types to SQLite NULL values. A String? column can be NULL in the database; String cannot. This aligns with Kotlin's null safety — Room enforces the contract at the database boundary.

// Nullable columns — SQLite NULL maps to Kotlin null
@Entity
data class UserEntity(
    @PrimaryKey val id: String,
    val name: String,              // NOT NULL in SQLite — Room enforces this
    val phone: String? = null,    // NULL allowed — optional field
    val avatar: String? = null    // NULL allowed — user may not have avatar
)

// DAO queries with nullable results
@Dao
interface UserDao {
    // Return type nullable — row may not exist
    @Query("SELECT * FROM users WHERE id = :id")
    suspend fun getUser(id: String): UserEntity?   // null if not found

    // Nullable column in query
    @Query("SELECT * FROM users WHERE phone IS NOT NULL")
    fun observeUsersWithPhone(): Flow<List<UserEntity>>

    // COALESCE — provide default for null in query
    @Query("SELECT id, COALESCE(phone, 'N/A') AS phone FROM users")
    suspend fun getUsersWithDefaultPhone(): List<UserProjection>
}

// Using nullable results safely in Kotlin
suspend fun loadUser(id: String) {
    val user = dao.getUser(id) ?: return  // Elvis operator — handle null
    // user is UserEntity (non-null) here
    val phone = user.phone ?: "No phone"   // nullable column handled safely
}

// Kotlin default values in entity = NOT the same as nullable
val count: Int = 0   // stored as 0 in SQLite — NOT NULL, with default 0
val count: Int? = null  // stored as NULL in SQLite

Room maps Kotlin's type system to SQLite's type affinity rules. A String? property maps to a TEXT column that allows NULL; a String (non-nullable) maps to a TEXT column with a NOT NULL constraint. Room enforces non-nullability at the ORM layer — if the database somehow contains a NULL in a non-nullable column (from a raw SQL insert or a migration error), Room throws a NullPointerException when reading that row. Always match your Kotlin nullability to your actual data: if a column was added in a migration with no DEFAULT value, existing rows have NULL for that column, so the Kotlin property must be nullable even if new rows always provide a value.

Default column values bridge the gap between existing rows and new schema constraints. When adding a non-null column in a migration, you have two options: add it as nullable (ALTER TABLE ADD COLUMN new_col TEXT) and handle nulls in code, or add it with a DEFAULT (ALTER TABLE ADD COLUMN new_col TEXT NOT NULL DEFAULT 'unknown'). SQLite applies the DEFAULT to all existing rows at migration time, so after the migration every row has a value and the Kotlin property can be non-nullable. Use @ColumnInfo(defaultValue = "unknown") to declare the default in the Room entity — this ensures Room's generated INSERT statements omit the column when no value is provided, letting SQLite apply the default rather than inserting null.

Notification history is a classic append-heavy workload. Design it with efficient indexes for the most common queries (unread count, recent list), and automatic cleanup to prevent unbounded growth.

@Entity(
    tableName = "notifications",
    indices = [
        Index(value = ["isRead"]),            // fast unread count query
        Index(value = ["receivedAt"]),        // fast ORDER BY receivedAt
        Index(value = ["type"])               // fast filter by type
    ]
)
data class NotificationEntity(
    @PrimaryKey val id: String,
    val title: String,
    val body: String,
    val type: String,      // "ORDER_UPDATE" | "PROMO" | "CHAT"
    val isRead: Boolean = false,
    val receivedAt: Long = System.currentTimeMillis(),
    val deepLink: String? = null
)

@Dao
interface NotificationDao {
    @Insert(onConflict = OnConflictStrategy.IGNORE)   // idempotent — FCM may deliver twice
    suspend fun insert(n: NotificationEntity)

    @Query("SELECT * FROM notifications ORDER BY receivedAt DESC LIMIT 50")
    fun observeRecent(): Flow<List<NotificationEntity>>

    @Query("SELECT COUNT(*) FROM notifications WHERE isRead = 0")
    fun observeUnreadCount(): Flow<Int>    // drives badge on app icon

    @Query("UPDATE notifications SET isRead = 1 WHERE id = :id")
    suspend fun markRead(id: String)

    @Query("UPDATE notifications SET isRead = 1")
    suspend fun markAllRead()

    // Keep only last 100 notifications — cleanup old ones
    @Query("""DELETE FROM notifications WHERE id NOT IN
        (SELECT id FROM notifications ORDER BY receivedAt DESC LIMIT 100)""")
    suspend fun pruneOld()
}

// Schedule daily cleanup with WorkManager
val prune = PeriodicWorkRequestBuilder<NotificationPruneWorker>(1, TimeUnit.DAYS).build()

The data model for notification history should be append-only: each row represents an immutable event. Columns include id (UUID, primary key), type (enum/string), title, body, deepLink, receivedAt (Long timestamp), and isRead (Boolean, default false). Index receivedAt DESC for the common query pattern of "show most recent first." Index isRead only if you display unread count frequently — a partial index WHERE isRead = 0 is more efficient than a full column index for a boolean with heavily skewed distribution.

Retention management prevents unbounded table growth. A WorkManager periodic task running daily deletes rows older than 30 days: @Query("DELETE FROM notifications WHERE receivedAt < :cutoff"). Batch-marking as read should use a single UPDATE rather than N individual updates: @Query("UPDATE notifications SET isRead = 1 WHERE isRead = 0") marks all unread in one statement. For unread count display, use a dedicated query returning a single integer: @Query("SELECT COUNT(*) FROM notifications WHERE isRead = 0") as a Flow<Int> — it re-emits automatically when any notification is marked read, keeping the badge count in sync without any manual invalidation.

Production storage profiling uses Android Studio's Database Inspector for Room, StrictMode for main-thread I/O, and Firebase Performance Monitoring for real-world timing data across all users.

// TOOL 1: Room Query Callback — log slow queries in development
Room.databaseBuilder(...)
    .setQueryCallback({ sql, _ ->
        Log.d("SlowQuery", sql)
    }, Executors.newSingleThreadExecutor())
    .build()

// TOOL 2: Android Studio Database Inspector
// View → Tool Windows → App Inspection → Database Inspector
// Run queries live on device/emulator, see table contents
// Track query execution time per query

// TOOL 3: StrictMode — catch main-thread disk access
StrictMode.setThreadPolicy(
    StrictMode.ThreadPolicy.Builder()
        .detectDiskReads().detectDiskWrites()
        .penaltyLog()              // log instead of crash in production profiling
        .build()
)
// Prints stack trace whenever disk I/O happens on main thread

// TOOL 4: Firebase Performance — real-world timing
suspend fun searchProducts(query: String): List<Product> {
    val trace = Firebase.performance.newTrace("room_product_search")
    trace.start()
    val result = dao.search(query)
    trace.stop()   // captured in Firebase dashboard — p50, p95, p99 timing
    return result.map { it.toDomain() }
}

// COMMON FIXES after profiling:
// 1. Slow SELECT  → add index (EXPLAIN QUERY PLAN shows full scan)
// 2. Slow INSERT  → batch inserts (insertAll vs 1000 individual inserts)
// 3. Main thread  → move to withContext(IO) or fix Room threading
// 4. Large results → paginate (LIMIT/OFFSET or Paging 3)
// 5. Bloated DB   → VACUUM after deletes, prune old data
// 6. Too many cols → project only needed columns in SELECT

Android Studio's Database Inspector is the first debugging tool to reach for: it lets you browse tables, run live queries, and see Room's query log in a running app attached over USB. For production, Firebase Performance Monitoring custom traces let you measure real-world query latency: wrap your DAO calls with FirebasePerformance.startTrace("room_query_products") and stop the trace after the query completes. A p95 latency spike in production that doesn't reproduce in dev often points to a missing index — the dev database has 100 rows, production has 100,000, and the query plan is completely different at scale.

StrictMode.setVmPolicy and StrictMode.setThreadPolicy in your debug Application class catch entire classes of storage issues at development time: main-thread disk reads, leaked SQLite cursors, untagged network calls, and file URI exposure. Enable the strictest policies in debug builds and treat every StrictMode violation as a bug to fix immediately — they rarely matter on the developer's fast device but reliably cause ANRs and battery drain on the slower devices your users actually have. For Room specifically, the Database Inspector's "Explain" feature runs EXPLAIN QUERY PLAN on any query you type, showing immediately whether a full table scan is happening and which index would fix it.

Migrating from SharedPreferences to DataStore requires reading existing prefs values and writing them to DataStore once — then permanently switching to DataStore. The SharedPreferencesMigration API handles this automatically.

// DataStore provides a built-in SharedPreferences migration helper
// It reads from SharedPreferences on first DataStore access
// then deletes the SharedPreferences file after successful migration

val Context.dataStore: DataStore<Preferences> by preferencesDataStore(
    name = "settings",
    produceMigrations = { context ->
        listOf(
            SharedPreferencesMigration(
                context,
                sharedPreferencesName = "app_settings"   // old prefs file name
            )
        )
    }
)

// What happens on first DataStore access:
// 1. DataStore checks if migration is needed
// 2. Reads ALL values from SharedPreferences "app_settings"
// 3. Writes them to DataStore with the same keys
// 4. Deletes the original SharedPreferences file
// 5. Subsequent accesses use DataStore only

// Migrate specific keys only (exclude sensitive data)
SharedPreferencesMigration(
    context,
    sharedPreferencesName = "app_settings",
    keysToMigrate = setOf("theme", "language", "font_size")
    // "auth_token" NOT in the list — stays in SharedPreferences or migrate separately
)

// Custom key mapping — rename keys during migration
SharedPreferencesMigration(context, "app_settings") { prefs: SharedPreferencesView, current: MutablePreferences ->
    if (prefs.contains("dark_mode")) {
        // Old key was "dark_mode" — new key is "theme"
        val isDark = prefs.getBoolean("dark_mode", false)
        current[stringPreferencesKey("theme")] = if (isDark) "dark" else "light"
    }
    current
}

DataStore's SharedPreferencesMigration handles the mechanical part of migration automatically. On the first read from DataStore, it checks whether the old SharedPreferences file exists, reads all key-value pairs from it, writes them into DataStore using a mapper function you provide (to rename keys or transform values), then deletes the XML file. The migration happens exactly once and is transparent to callers — they call dataStore.data.first() and always get the current preferences, whether they come from the migrated SharedPreferences data or from DataStore directly.

The trickiest part of the migration is the mapping function. SharedPreferences is an untyped map of Any? values — booleans, strings, ints all coexist under string keys. Proto DataStore requires a strongly-typed schema (a .proto file). Preferences DataStore is closer to SharedPreferences but still requires explicit key types: val THEME_KEY = stringPreferencesKey("theme"). The migration mapper reads the old SharedPreferencesView and writes to a MutablePreferences: mutablePreferences[THEME_KEY] = sharedPrefs.getString("theme") ?: "system". In a multi-module project, ensure the DataStore instance is a singleton — creating multiple DataStore instances pointing to the same file causes data corruption. Provide it via Hilt as a @Singleton in the app module.

A shopping cart needs local persistence (works offline), atomic quantity updates (no race conditions), and reliable server sync. Room @Transaction + coroutines handles all three.

@Entity(tableName = "cart_items")
data class CartItemEntity(
    @PrimaryKey val productId: String,
    val quantity: Int,
    val price: Double,
    val name: String,
    val isDirty: Boolean = true    // true = needs sync with server
)

@Dao
abstract class CartDao {
    @Query("SELECT * FROM cart_items")
    abstract fun observeCart(): Flow<List<CartItemEntity>>

    @Query("SELECT SUM(quantity * price) FROM cart_items")
    abstract fun observeTotal(): Flow<Double>

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    abstract suspend fun upsert(item: CartItemEntity)

    @Query("UPDATE cart_items SET quantity = quantity + :delta, isDirty = 1 WHERE productId = :id")
    abstract suspend fun adjustQuantity(id: String, delta: Int)  // atomic increment!

    @Query("DELETE FROM cart_items WHERE productId = :id")
    abstract suspend fun remove(id: String)

    // Atomic add: insert if absent, increment if present
    @Transaction
    open suspend fun addToCart(item: CartItemEntity) {
        val existing = getItem(item.productId)
        if (existing != null) {
            adjustQuantity(item.productId, 1)
        } else {
            upsert(item.copy(quantity = 1))
        }
    }

    @Query("SELECT * FROM cart_items WHERE productId = :id")
    abstract suspend fun getItem(id: String): CartItemEntity?

    @Query("UPDATE cart_items SET isDirty = 0")
    abstract suspend fun markSynced()
}

Atomic quantity updates are the critical correctness requirement. Never read the current quantity into memory, increment it, and write it back — this is a read-modify-write race condition. Two coroutines doing this concurrently can both read quantity=2, both compute quantity=3, and both write 3, losing one increment. The correct approach is a SQL UPDATE with arithmetic: @Query("UPDATE cart_items SET quantity = quantity + :delta WHERE productId = :id"). SQLite serializes writes, so this operation is inherently atomic — quantity is incremented or decremented in a single database operation with no intermediate state visible to other readers.

The sync strategy for a shopping cart needs to handle the "add to cart while offline" case gracefully. Store a syncStatus column on each cart item: SYNCED, PENDING_ADD, PENDING_UPDATE, PENDING_DELETE. Local operations update Room and set the status to PENDING. A WorkManager task with a network constraint runs whenever connectivity is available, reads all PENDING rows, syncs them to the server in a batch request, and updates their status to SYNCED on success. On conflict (item sold out, price changed), the server response dictates resolution — update the local Room entry with the server's authoritative state and surface a notification to the user that their cart was updated.

Room 2.7+ supports Kotlin Multiplatform — the same Room code can run on Android, iOS, and Desktop. This means your entire data layer can be shared across platforms, eliminating duplicate database code.

// Room 2.7+ with KMP — shared commonMain code
// build.gradle.kts (shared module)
// kotlin { sourceSets { commonMain.dependencies {
//   implementation("androidx.room:room-runtime:2.7.0")
// } } }

// Entity — in commonMain (shared across platforms)
@Entity
data class ProductEntity(
    @PrimaryKey val id: String,
    val name: String,
    val price: Double
)

// DAO — in commonMain
@Dao
interface ProductDao {
    @Query("SELECT * FROM products")
    fun observeAll(): Flow<List<ProductEntity>>

    @Upsert
    suspend fun upsertAll(products: List<ProductEntity>)
}

// Database — in commonMain
@Database(entities = [ProductEntity::class], version = 1)
abstract class AppDatabase : RoomDatabase() {
    abstract fun productDao(): ProductDao
}

// Platform-specific builder — in androidMain / iosMain
// androidMain:
fun createDatabase(context: Context) =
    Room.databaseBuilder(context, AppDatabase::class.java, "app.db").build()

// iosMain:
fun createDatabase() =
    Room.databaseBuilder<AppDatabase>(
        name = NSHomeDirectory() + "/app.db",
        factory = { AppDatabase::class.instantiateImpl() }
    ).build()

// Same DAO used by both Android and iOS ViewModels
// No duplicate database code across platforms

Room KMP works by sharing the DAO interfaces and entity definitions in the commonMain source set. The platform-specific driver initialization happens in androidMain and iosMain respectively: AndroidSqliteDriver on Android, NativeSqliteDriver on iOS. Both drivers implement the same SqlDriver interface from SQLite.kt, so the shared Room code compiles identically on both platforms. The database class and all DAO implementations are generated by Room's KSP annotation processor running in the shared module — you write your entities and DAOs once and get working SQLite persistence on both platforms.

The practical limitations of Room KMP are mostly around tooling maturity. As of Room 2.7+, the feature is stable but some Room features that depend on Android-specific APIs — like SupportSQLiteDatabase extensions or the Database Inspector — work only on Android. iOS debugging requires native SQLite tools. Coroutines work identically on both platforms since they're pure Kotlin. The bigger architectural win is that your repository layer, use cases, and ViewModels (or equivalent presentation layer) can all live in commonMain, sharing not just the data access code but the entire business logic. Platform-specific code is reduced to UI and driver initialization — typically under 20% of total codebase size.

Room database corruption is rare but happens when the SQLite file is incomplete — usually from a process kill during a write. Detecting corruption, recovering gracefully, and preventing data loss requires a deliberate strategy.

// Common causes of corruption:
// 1. Process killed during a write that isn't WAL-protected
// 2. Low disk space during write
// 3. App sharing a DB file written by multiple processes
// 4. Manual file manipulation (backup/restore gone wrong)

// Detection: Room throws SQLiteDatabaseCorruptException
val db = Room.databaseBuilder(ctx, AppDatabase::class.java, "app.db")
    .build()

// Catch in repository
suspend fun getProducts() = try {
    dao.getAll()
} catch (e: SQLiteDatabaseCorruptException) {
    handleCorruption(e)
    emptyList()
}

fun handleCorruption(e: SQLiteDatabaseCorruptException) {
    // Log to crash reporting (Crashlytics)
    FirebaseCrashlytics.getInstance().recordException(e)

    // Recovery option 1: delete and recreate (data loss)
    context.deleteDatabase("app.db")
    // App will recreate on next DB access
    // ❌ All local data lost — acceptable for cache-only data

    // Recovery option 2: restore from backup (if you maintain one)
    val backup = File(context.filesDir, "app.db.backup")
    if (backup.exists()) {
        context.getDatabasePath("app.db").delete()
        backup.copyTo(context.getDatabasePath("app.db"))
    }
}

// Prevention: WAL mode (default in Room 2.2+) greatly reduces corruption risk
// WAL checkpoints are atomic — partial writes are rolled back automatically

// Optional: periodic backup before risky operations
suspend fun createBackup() = withContext(Dispatchers.IO) {
    db.close()  // flush WAL before backup
    context.getDatabasePath("app.db")
        .copyTo(File(context.filesDir, "app.db.backup"), overwrite = true)
}

SQLite database corruption is rare but has specific triggers: the process being killed mid-write (power loss, OOM kill during a transaction), the SQLite file being modified by external tools, storage hardware failures, or — most commonly in Android — the database file being opened by multiple processes without enableMultiInstanceInvalidation(). Room detects corruption when a query throws SQLiteDatabaseCorruptException. The correct response is to catch this exception at the repository layer, log it to your crash reporting tool (Crashlytics) with device metadata, and initiate recovery rather than letting the exception propagate to the UI as a crash.

Recovery strategy depends on how critical the local data is. For a pure cache (data that can be re-fetched from the server), the recovery is straightforward: delete the corrupt database file, let Room recreate it with the current schema via fallbackToDestructiveMigration(), and trigger a fresh sync from the network. For data with local-only content (drafts, offline-created records), deletion loses user data — implement a WAL checkpoint and SQLite's PRAGMA integrity_check before deciding to delete. Configure fallbackToDestructiveMigrationOnDowngrade() as a safety net for users who install a downgraded app version, and fallbackToDestructiveMigrationFrom() to specify specific old versions that are too far back to migrate safely.

A note-taking app is the canonical data storage design challenge — it touches every storage mechanism and requires careful decisions about what to persist where, how to sync, and what to encrypt.

// ROOM — main data store
@Entity(tableName = "notes", indices = [Index("updatedAt"), Index("isPinned")])
data class NoteEntity(
    @PrimaryKey val id: String = UUID.randomUUID().toString(),
    val title: String,
    val content: String,
    val isPinned: Boolean = false,
    val isEncrypted: Boolean = false,
    val syncStatus: SyncStatus = SyncStatus.PENDING,
    val createdAt: Long = System.currentTimeMillis(),
    val updatedAt: Long = System.currentTimeMillis()
)

// FTS for search
@Fts4(contentEntity = NoteEntity::class)
@Entity(tableName = "notes_fts")
data class NoteFts(val title: String, val content: String)

// DATASTORE — user preferences
// sort order (MODIFIED_DATE | CREATED_DATE | TITLE)
// default view (list | grid)
// auto-lock timeout
// sync frequency preference

// ENCRYPTED STORAGE — for locked notes
// content of isEncrypted notes stored AES-256 encrypted
// encryption key in AndroidKeyStore, unlocked by biometric
// EncryptedFile for export/backup of encrypted notes

// SYNC DESIGN:
// Write: Room first → schedule WorkManager sync (requiresNetwork)
// Read:  Room always → background sync refreshes when stale
// Conflict: last-write-wins on updatedAt timestamp
// Deleted: soft delete (deletedAt timestamp) → server hard-deletes after 30 days

// BACKUP:
// backup_rules.xml: include "notes.db", exclude "secure_prefs"
// Encrypted notes: backed up encrypted — key stays on device (per-device encryption)
// noBackupFilesDir: for encryption keys cache

The note entity is the core of the schema: id (UUID), title, body, createdAt, updatedAt, folderId (nullable foreign key), isPinned, isArchived, syncStatus. A companion FTS table (@Fts4(contentEntity = NoteEntity::class)) enables instant full-text search across title and body. The syncStatus column (SYNCED / PENDING_UPLOAD / PENDING_DELETE) drives the sync engine without requiring a separate pending-changes table. Index updatedAt DESC for the default sort, folderId for folder filtering, and isPinned for the pinned-first display logic.

The sync architecture must handle concurrent edits from multiple devices. Use a vector clock or a simple serverVersion integer per note: when pushing a local change, send the last known server version alongside the new content. The server rejects the update with a 409 Conflict if its current version is higher — meaning another device edited the note since the last sync. On conflict, fetch the server version, present a merge UI, and let the user choose which version to keep or merge manually. WorkManager handles the sync loop: a periodic task every 15 minutes for background sync, an immediate one-shot task triggered on every local save for near-real-time sync when online. Store sync errors in a separate table so failed syncs are retried with exponential backoff rather than silently dropped.