Local Listening Analytics

Local analytics with no service behind it

Two tables. activity_event records what happened; event_entity — one row per (event, contributor) — exists only so the "top contributors" query never has to join:

@Entity("activity_event")
data class ActivityEventEntity(
    @PrimaryKey(autoGenerate = true) val eventId: Long = 0,
    val timestamp: LocalDateTime = now(),
    val itemId: String = "", val groupId: String? = null,
    val durationSecond: Long = 0, val listenedSecond: Long = 0,
)

@Entity(
    tableName = "event_entity", primaryKeys = ["eventId", "entityId"],
    foreignKeys = [ForeignKey(ActivityEventEntity::class, ["eventId"], ["eventId"],
    indices = [Index("eventId"), Index("entityId"), Index("timestamp", "entityId")],
)
data class EventEntityRow(val eventId: Long, val entityId: String, val timestamp: LocalDateTime)

One item can have several contributors, so the second table is one row per (event, contributor), written in the same transaction and taking the id the parent insert returned:

@Transaction suspend fun insertEventWithEntities(...): Long {
    val timestamp = now()      // ONE read of the clock, shared by both rows
    val eventId = insertActivityEvent(ActivityEventEntity(timestamp = timestamp, ...))
    entityIds.forEach { insertEventEntityRow(EventEntityRow(eventId, it, timestamp)) }
    return eventId
}

Traps

Leaving the timestamp off the child row forces a join on the hottest query you have. Every window here is WHERE timestamp BETWEEN :start AND :end; with the copy, the top-contributors query never touches the parent table at all:

SELECT entityId, COUNT(*) AS playCount FROM event_entity
WHERE timestamp BETWEEN :startTimestamp AND :endTimestamp
GROUP BY entityId ORDER BY playCount DESC LIMIT 100

The duplication is safe because events are append-only — nothing edits a timestamp after the row is written, so the copy cannot drift from the original — and both copies come from one local variable; reading the clock twice, instead, could straddle a boundary between them. Index the pair the query filters and groups on, not each column separately: the composite Index("timestamp", "entityId") makes that scan cheap, while eventId alone serves the foreign key and its cascade — single-column indices look thorough and do not help.

One threshold is not enough — you need a floor and a ceiling. A play should not count until the user has heard enough of it to mean something, and past a point it should count whole:

val percent = currentPositionMillis / (item.durationSeconds * 1000f)
if (percent < 0.2f) return                         // too little: no event at all
val listened = if (percent >= 0.8f) item.durationSeconds.toLong()   // near the end: count it whole
               else currentPositionMillis / 1000

Without the floor, every skip is an event and the top-items chart charts what the user skipped past. Without the ceiling, anyone leaving before the last few seconds never records a complete listen and total-time reads low forever. Tune both, keep both.

Store bare ids in the event, resolve them at read time — but that enrichment step quietly shortens the chart. The event table grows without bound, so every extra column is paid thousands of times over, and titles/artwork copied at write time become names matching nothing once they change; keep projections id-shaped ((itemId, playCount, totalListeningTime), (entityId, playCount) — hand-written, so also where a converted column loses its converter, see stored-timestamp-is-a-local-wall-clock). The natural join back, mapNotNull { repository.getById(it.id) ?: return@mapNotNull null }, silently drops every id whose row was since deleted — a "top 100" becomes a top 60 with no error. This codebase drops unknown tracks but fetches contributors first.

Cap in SQL, not in Kotlin. LIMIT 100 in the query means the database sorts and discards; .take(100) after the fact materializes, converts and boxes every row first — invisible at a few thousand events, which is why the wrong one survives. The cap belongs to the consumer though: once the same grouped query feeds a share-of-the-whole, the cut tail inflates it — unbounded-for-shares-capped-for-lists is a second query with no LIMIT, not a merge.

Parameterise the window from the start; "last N days" is an argument, not a query. The first screen only ever asks for the last 7, 30 or 90 days, so the obvious method is queryTopItemsLastXDays(n) over a timestamp > :cutoff query. Write the general form instead:

// adapted — the only place "last X days" exists; there is no LastXDays SQL anywhere
suspend fun queryTopItemsLastXDays(x: Int) =
    databaseDao.queryTopItemsInRange(startTimestamp = now().beforeXDays(x), endTimestamp = now())

Measured here: zero LastXDays queries against thirteen …InRange ones — a period navigator needing arbitrary spans, backward stepping and period comparison later needed no new query, datasource or method, only a different argument: a feature instead of a migration (one-snapshot-per-period-not-many-flows is the coherence it still needed).

Aggregate over the source rows, and skip null grouping keys explicitly. Counting plays is COUNT(*), total time SUM(listenedSecond); computing either in Kotlin from a page of events answers for that page only, and paged readers are what these tables invite. groupId is nullable too, so GROUP BY over it outranks every real group with a "no group" bucket — hence AND groupId IS NOT NULL. A per-contributor total the child row does not carry still costs a join — and will not sum back up, both intentional: total time is per-EVENT, so reading it back per contributor means a second query and result class, not a column bolted onto the lean one, and each contributor of a shared item earns that item's FULL listenedSecond, not a fraction, so contributors summed routinely exceed the period's real total on purpose; forcing them to agree reports a number nobody asked for.

Give the user an off switch and a delete path; check the switch at the write site or events accumulate forever. Deletes need two shapes — a bounded prune (DELETE FROM activity_event WHERE timestamp < :cutoff) and a full clear; the cascade takes the child with the parent, so only the prune costs elsewhere: "first ever seen" via MIN(timestamp) now means since the cutoff (first-ever-not-first-in-window).

Verifying it

Run these from the root of the repository containing your DAO — from this skills repo both commands below print 0 and half-look like a pass.

1. Confirm the window is a parameter, not a query shape — 0 and 13 here. A LastXDays hit in a DAO is a second query someone must generalise later; above the DAO it is the shortcut calling the range form, which is what you want:

   grep -rn "LastXDays" --include='*Dao*.kt' . | grep -v '/build/' | wc -l
   grep -rn -E "suspend fun (get|query)[A-Za-z]*InRange" --include='*Dao*.kt' . | grep -v '/build/' | wc -l
   

2. Verify the cascade by counting, not by reading the annotation. Run SELECT (SELECT COUNT(*) FROM activity_event), (SELECT COUNT(*) FROM event_entity); either side of a parent delete; a child count that does not move is a cascade that is not there.

3. Compare each enriched list's size against its own count query — a "top 100" rendering 60 rows while the count disagrees is enrichment eating the difference, and the only detection for it.