Home / Blog / Room time-series

Designing a Room schema for time-series data.

Jun 15, 2026

A soil-sensor app is, underneath, a time-series app. Every reading is a set of values stamped with a moment in time, and almost everything the user sees — charts, history, trends — is a query over that series. Getting the Room schema right for that shape makes the rest of the app easy. Getting it wrong makes every chart slow and every feature a fight.

The entity: keep it flat, stamp it in UTC

A reading belongs to a session (one trip to the field) and carries a timestamp plus its measured values. I store time as epoch milliseconds in a Long, always UTC, and format it for display later — never store a formatted string you'll have to parse back.

@Entity(
    tableName = "readings",
    indices = [Index(value = ["sessionId", "takenAt"])]
)
data class ReadingEntity(
    @PrimaryKey(autoGenerate = true) val id: Long = 0,
    val sessionId: Long,
    val takenAt: Long,        // epoch millis, UTC
    val moisture: Double,
    val soilTemp: Double,
    val conductivity: Double
)

The index is the whole game

Notice the composite index on (sessionId, takenAt). Chart and history queries almost always mean "give me this session's readings, in time order, maybe within a range." That index turns those from a full-table scan into a range scan:

@Query("""
    SELECT * FROM readings
    WHERE sessionId = :id AND takenAt BETWEEN :from AND :to
    ORDER BY takenAt
""")
fun observeRange(id: Long, from: Long, to: Long): Flow<List<ReadingEntity>>

Returning a Flow means the chart subscribes once and redraws whenever new rows land — the reactive, offline-first pattern I wrote about here.

Don't render a million points

A long session can hold far more readings than a phone screen has pixels. Rather than pull everything and thin it in Kotlin, I let SQLite do the bucketing — group by a time window and average — so a season's trend line stays cheap to draw:

SELECT (takenAt / :bucketMs) AS bucket, AVG(moisture) AS moisture
FROM readings
WHERE sessionId = :id
GROUP BY bucket
ORDER BY bucket

Plan migrations on day one

Sensors gain fields. A future firmware adds a value, or you split a column. On a local-first app the database on the device is the user's data — you can't just drop and recreate it. So I write migrations from the very first version and test them, rather than leaning on destructive fallback:

val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(db: SupportSQLiteDatabase) {
        db.execSQL("ALTER TABLE readings ADD COLUMN airHumidity REAL NOT NULL DEFAULT 0")
    }
}
On an offline-first app, a careless migration doesn't reset a cache — it destroys a season of someone's irreplaceable field data.

The takeaway

Model the reading flat, stamp time as epoch millis, index for the range queries you'll actually run, bucket in SQL instead of in memory, and treat migrations as a first-class concern. Do that and time-series features — charts, ranges, trends — stop being performance problems and become one-line queries.

← All posts Next post