Trail Sense Database Persistence

1. Entity

package com.kylecorry.trail_sense.tools.{toolname}.infrastructure.persistence

import androidx.room.ColumnInfo
import androidx.room.Entity
import androidx.room.Index
import androidx.room.PrimaryKey
import com.kylecorry.trail_sense.tools.{toolname}.domain.{Model}

@Entity(
    tableName = "{table_name}",  // plural, lowercase, snake_case
    indices = [
        // Add indices for foreign keys and frequently queried columns
        // Index(value = ["parent_id"]),
        // Index(value = ["time"])
    ]
)
data class {Model}Entity(
    @ColumnInfo(name = "column_name") val property: Type,
    // ... other properties
) {
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "_id")
    var id: Long = 0

    fun to{Model}(): {Model} {
        return {Model}(
            id = id,
            // Map entity properties to domain model
        )
    }

    companion object {
        fun from(model: {Model}): {Model}Entity {
            return {Model}Entity(
                // Map domain model properties to entity
            ).also {
                it.id = model.id
            }
        }
    }
}

Index Guidelines

Add indices for:

• Foreign key columns (e.g., parent_id, group_id)
• Time-based columns if queried by time range
• Columns used in WHERE clauses frequently
• Composite indices for multi-column filters: Index(value = ["col1", "col2"])

Type Mapping

• Instant -> stored as Long (epoch millis), converter exists
• Duration -> stored as Long (millis), converter exists
• Coordinate -> split into latitude: Double, longitude: Double
• Distance -> store as Float in meters
• Enums with id property -> use existing converters or add to Converters.kt
• AppColor -> converter exists
• Lists/collections -> join to comma-separated string, parse in mapping

2. DAO

package com.kylecorry.trail_sense.tools.{toolname}.infrastructure.persistence

import androidx.room.Dao
import androidx.room.Delete
import androidx.room.Query
import androidx.room.Upsert
import kotlinx.coroutines.flow.Flow

@Dao
interface {Model}Dao {
    @Query("SELECT * FROM {table_name}")
    fun getAll(): Flow<List<{Model}Entity>>

    @Query("SELECT * FROM {table_name}")
    suspend fun getAllSync(): List<{Model}Entity>

    @Query("SELECT * FROM {table_name} WHERE _id = :id LIMIT 1")
    suspend fun get(id: Long): {Model}Entity?

    @Upsert
    suspend fun upsert(entity: {Model}Entity): Long

    @Delete
    suspend fun delete(entity: {Model}Entity)
}

Optional DAO Methods

// Filter by parent/group
@Query("SELECT * FROM {table_name} WHERE parent_id IS :parentId")
suspend fun getAllInGroup(parentId: Long?): List<{Model}Entity>

// Time-based cleanup
@Query("DELETE FROM {table_name} WHERE time < :minEpochMillis")
suspend fun deleteOlderThan(minEpochMillis: Long)

// Get latest
@Query("SELECT * FROM {table_name} ORDER BY _id DESC LIMIT 1")
suspend fun getLast(): {Model}Entity?

3. AppDatabase Updates

Add Entity to Database

In AppDatabase.kt, add entity to @Database annotation:

@Database(
    entities = [..., {Model}Entity::class],
    version = {NEXT_VERSION},  // Increment from current
    exportSchema = false
)

Add DAO Accessor

abstract fun {model}Dao(): {Model}Dao

Add Migration

Inside buildDatabase(), add migration before the return Room.databaseBuilder:

val MIGRATION_{PREV}_{NEXT} = object : Migration({PREV}, {NEXT}) {
    override fun migrate(db: SupportSQLiteDatabase) {
        db.execSQL("""
            CREATE TABLE IF NOT EXISTS `{table_name}` (
                `_id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
                `column_name` TEXT NOT NULL,
                `nullable_column` TEXT DEFAULT NULL
                -- Match column types: TEXT, INTEGER, REAL
                -- NOT NULL for non-nullable, DEFAULT NULL for nullable
            )
        """.trimIndent())

        // Add indices if defined in entity
        // db.execSQL("CREATE INDEX IF NOT EXISTS index_{table_name}_{column} ON {table_name}({column})")
    }
}

Register Migration

Add to .addMigrations():

.addMigrations(
    ...,
    MIGRATION_{PREV}_{NEXT}
)

4. Repository

package com.kylecorry.trail_sense.tools.{toolname}.infrastructure.persistence

import android.annotation.SuppressLint
import android.content.Context
import com.kylecorry.luna.coroutines.onIO
import com.kylecorry.trail_sense.main.persistence.AppDatabase
import com.kylecorry.trail_sense.tools.{toolname}.domain.{Model}
import kotlinx.coroutines.Dispatchers
import kotlinx.coroutines.flow.Flow
import kotlinx.coroutines.flow.flowOn
import kotlinx.coroutines.flow.map

class {Model}Repo private constructor(context: Context) {

    private val dao = AppDatabase.getInstance(context).{model}Dao()

    fun getAll(): Flow<List<{Model}>> = dao.getAll()
        .map { it.map { entity -> entity.to{Model}() } }
        .flowOn(Dispatchers.IO)

    suspend fun getAllSync(): List<{Model}> = onIO {
        dao.getAllSync().map { it.to{Model}() }
    }

    suspend fun get(id: Long): {Model}? = onIO {
        dao.get(id)?.to{Model}()
    }

    suspend fun add(model: {Model}): Long = onIO {
        dao.upsert({Model}Entity.from(model))
    }

    suspend fun delete(model: {Model}) = onIO {
        dao.delete({Model}Entity.from(model))
    }

    companion object {
        @SuppressLint("StaticFieldLeak")
        private var instance: {Model}Repo? = null

        @Synchronized
        fun getInstance(context: Context): {Model}Repo {
            if (instance == null) {
                instance = {Model}Repo(context.applicationContext)
            }
            return instance!!
        }
    }
}

5. Register Singleton in ToolRegistration

In {ToolName}ToolRegistration.kt, add the repo to singletons:

object {ToolName}ToolRegistration : ToolRegistration {
    override fun getTool(context: Context): Tool {
        return Tool(
            // ... other properties
            singletons = listOf(
                {Model}Repo::getInstance
            ),
            // ...
        )
    }
}

SQL Type Reference