Trail Sense Database Persistence

Trail-Sense Database Persistence

Add Room database persistence for a domain model following Trail-Sense patterns.

File Locations

app/src/main/java/com/kylecorry/trail_sense/tools/{toolName}/
├── domain/
│   └── {Model}.kt                    # Domain model (if not already exists)
└── infrastructure/persistence/
    ├── {Model}Entity.kt              # Room entity
    ├── {Model}Dao.kt                 # DAO interface
    └── {Model}Repo.kt                # Repository

Also update:

• app/src/main/java/com/kylecorry/trail_sense/main/persistence/AppDatabase.kt
• app/src/main/java/com/kylecorry/trail_sense/main/persistence/Converters.kt (if new types needed)
• {ToolName}ToolRegistration.kt - register repo singleton

Workflow

1. Inspect nearby persistence packages and the target domain model. This step is complete when table name, columns, nullability, indices, and conversion rules are known.
2. Create the Entity with mapping functions. This step is complete when every persisted domain property round-trips through from() and to{Model}().
3. Create the DAO interface. This step is complete when repository-needed reads, upserts, and deletes have DAO methods.
4. Add the entity, DAO accessor, version bump, and migration to AppDatabase. This step is complete when the migration SQL exactly matches the entity columns, nullability, primary key, and indices.
5. Create the Repository. This step is complete when public methods expose domain models, run blocking work on IO, and map DAO entities only inside the repository.
6. Register the repo singleton in ToolRegistration. This step is complete when the tool owns the repository lifecycle.
7. Validate the persistence change with the narrowest useful build or test. This step is complete when ./scripts/build.sh or a focused unit test passes, or the exact blocker is reported.

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

• Inspect nearby entities before choosing stored types. Entity fields, converter use, and migration SQL must produce the same Room schema.
• Instant -> SQL INTEGER epoch millis; entity field may use Instant with converter or Long mapping
• Duration -> SQL INTEGER millis; entity field may use Duration with converter or Long mapping
• Coordinate -> split into latitude: Double, longitude: Double
• Distance -> store as Float in meters
• Enums with id property -> SQL INTEGER; use existing converters or manual id mapping
• AppColor -> SQL INTEGER; 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