Room Database

What this skill does

# Room Database Skill

## Overview

This skill provides expert capabilities for Android Room persistence library. It enables designing database schemas, implementing DAOs, configuring migrations, and integrating with modern Android architecture components.

## Allowed Tools

- `bash` - Execute Gradle commands and Android build tools
- `read` - Analyze Room entities and DAO files
- `write` - Generate Room database components
- `edit` - Update existing Room configurations
- `glob` - Search for database-related files
- `grep` - Search for patterns in database code

## Capabilities

### Entity Design

1. **Entity Definition**
   - Define @Entity classes with proper annotations
   - Configure primary keys (single and composite)
   - Set up foreign key relationships
   - Configure indices for query optimization
   - Implement embedded objects

2. **Type Converters**
   - Create @TypeConverter for custom types
   - Handle Date/Time conversions
   - Convert enums to database types
   - Serialize complex objects to JSON
   - Configure global type converters

### DAO Implementation

3. **Query Methods**
   - Write @Query annotations with SQL
   - Implement @Insert, @Update, @Delete
   - Configure conflict strategies
   - Create complex JOIN queries
   - Implement pagination queries

4. **Reactive Queries**
   - Return Flow for reactive updates
   - Configure LiveData return types
   - Implement one-shot suspend functions
   - Handle nullable results
   - Create parameterized queries

### Database Configuration

5. **Database Setup**
   - Configure @Database annotation
   - Set up database builder
   - Configure pre-populated databases
   - Implement multiple databases
   - Configure in-memory databases for testing

6. **Migrations**
   - Implement Migration objects
   - Configure auto-migrations
   - Handle destructive migrations
   - Test migrations with MigrationTestHelper
   - Design fallback strategies

### Integration

7. **Hilt Integration**
   - Provide database with @Singleton
   - Inject DAOs into repositories
   - Configure database scopes
   - Handle multi-module setups

8. **Repository Pattern**
   - Implement repository interfaces
   - Handle offline-first logic
   - Configure caching strategies
   - Implement sync mechanisms

## Target Processes

This skill integrates with the following processes:

- `android-room-database.js` - Room implementation
- `offline-first-architecture.js` - Offline data strategies
- `mobile-security-implementation.js` - Secure data storage

## Dependencies

### Required

- Android Studio
- Room 2.6+
- Kotlin 1.9+
- KSP or KAPT

### Optional

- Hilt for dependency injection
- Kotlin Coroutines
- Paging 3 library

## Configuration

### Gradle Setup

```kotlin
// build.gradle.kts (app)
plugins {
    id("com.google.devtools.ksp")
}

dependencies {
    implementation(libs.room.runtime)
    implementation(libs.room.ktx)
    ksp(libs.room.compiler)

    // Optional - Paging 3 Integration
    implementation(libs.room.paging)

    // Testing
    testImplementation(libs.room.testing)
}
```

### Version Catalog

```toml
# gradle/libs.versions.toml
[versions]
room = "2.6.1"

[libraries]
room-runtime = { group = "androidx.room", name = "room-runtime", version.ref = "room" }
room-ktx = { group = "androidx.room", name = "room-ktx", version.ref = "room" }
room-compiler = { group = "androidx.room", name = "room-compiler", version.ref = "room" }
room-paging = { group = "androidx.room", name = "room-paging", version.ref = "room" }
room-testing = { group = "androidx.room", name = "room-testing", version.ref = "room" }
```

## Usage Examples

### Entity Definition

```kotlin
// data/local/entity/UserEntity.kt
package com.example.app.data.local.entity

import androidx.room.*

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

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

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

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

    @ColumnInfo(name = "created_at")
    val createdAt: Long,

    @ColumnInfo(name = "updated_at")
    val updatedAt: Long,

    @Embedded(prefix = "settings_")
    val settings: UserSettings
)

data class UserSettings(
    @ColumnInfo(name = "notifications_enabled")
    val notificationsEnabled: Boolean = true,

    @ColumnInfo(name = "theme")
    val theme: String = "system"
)
```

### Entity with Relations

```kotlin
// data/local/entity/PostEntity.kt
package com.example.app.data.local.entity

import androidx.room.*

@Entity(
    tableName = "posts",
    foreignKeys = [
        ForeignKey(
            entity = UserEntity::class,
            parentColumns = ["id"],
            childColumns = ["author_id"],
            onDelete = ForeignKey.CASCADE
        )
    ],
    indices = [Index(value = ["author_id"])]
)
data class PostEntity(
    @PrimaryKey
    @ColumnInfo(name = "id")
    val id: String,

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

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

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

    @ColumnInfo(name = "published_at")
    val publishedAt: Long?,

    @ColumnInfo(name = "is_draft")
    val isDraft: Boolean = true
)

// Relation class for queries
data class PostWithAuthor(
    @Embedded val post: PostEntity,
    @Relation(
        parentColumn = "author_id",
        entityColumn = "id"
    )
    val author: UserEntity
)
```

### Type Converters

```kotlin
// data/local/converter/Converters.kt
package com.example.app.data.local.converter

import androidx.room.TypeConverter
import java.time.Instant
import java.time.LocalDateTime
import java.time.ZoneId

class Converters {
    @TypeConverter
    fun fromTimestamp(value: Long?): LocalDateTime? {
        return value?.let {
            LocalDateTime.ofInstant(Instant.ofEpochMilli(it), ZoneId.systemDefault())
        }
    }

    @TypeConverter
    fun toTimestamp(date: LocalDateTime?): Long? {
        return date?.atZone(ZoneId.systemDefault())?.toInstant()?.toEpochMilli()
    }

    @TypeConverter
    fun fromStringList(value: List<String>?): String? {
        return value?.joinToString(",")
    }

    @TypeConverter
    fun toStringList(value: String?): List<String>? {
        return value?.split(",")?.map { it.trim() }
    }
}
```

### DAO Implementation

```kotlin
// data/local/dao/UserDao.kt
package com.example.app.data.local.dao

import androidx.room.*
import kotlinx.coroutines.flow.Flow

@Dao
interface UserDao {
    @Query("SELECT * FROM users ORDER BY display_name ASC")
    fun observeAllUsers(): Flow<List<UserEntity>>

    @Query("SELECT * FROM users WHERE id = :userId")
    fun observeUserById(userId: String): Flow<UserEntity?>

    @Query("SELECT * FROM users WHERE id = :userId")
    suspend fun getUserById(userId: String): UserEntity?

    @Query("SELECT * FROM users WHERE email = :email LIMIT 1")
    suspend fun getUserByEmail(email: String): UserEntity?

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

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertUsers(users: List<UserEntity>)

    @Update
    suspend fun updateUser(user: UserEntity)

    @Delete
    suspend fun deleteUser(user: UserEntity)

    @Query("DELETE FROM users WHERE id = :userId")
    suspend fun deleteUserById(userId: String)

    @Query("DELETE FROM users")
    suspend fun deleteAllUsers()

    @Transaction
    suspend fun replaceAllUsers(users: List<UserEntity>) {
        deleteAllUsers()
        insertUsers(users)
    }
}
```

### DAO with Relations

```kotlin
// data/local/dao/PostDao.kt
package com.example.app.data.local.dao

import androidx.room.*
import androidx.paging.PagingSource
import kotlinx.coroutines.flow.Flow

@Dao
interface PostDao {
    @Tr