Why Favorites Can't Be a Column on the Gas Station Table

Image of the author

Germán Bustamante @germanbustamante

Fuelio's favorites screen on Android, showing a starred station with its cheapest-price badge

Why Favorites Can’t Be a Column on the Gas Station Table

Fuelio caches gas stations in Room so the list still works offline and repaints instantly from disk before the network call even returns. The obvious way to add a “favorite” flag felt like nothing: add isFavorite: Boolean to GasStationEntity, flip it on tap, done in an afternoon.

I didn’t ship that, and the reason is entirely in how the cache itself gets refreshed.

The write pattern that makes a column unsafe

GasStationRepositoryImpl.getGasStationsByLocation is stale-while-revalidate: it emits whatever’s cached for that province immediately, then fetches the network’s current list and replaces the cache — not merges into it, replaces it:

@Transaction
suspend fun replaceGasStationsByProvince(provinceId: String, gasStations: List<GasStationEntity>) {
    deleteGasStationsByProvince(provinceId)
    insertGasStations(gasStations)
}

That’s deliberate: stations that closed or that the upstream feed stopped listing shouldn’t linger in the cache forever. But it means every row for that province is destroyed and rebuilt on every successful refresh — including whatever boolean I’d bolted onto it. Star a station, pull to refresh a few minutes later, and the star silently vanishes. Not a crash, not an error — just a user’s saved station quietly forgetting it was saved, on a schedule tied to network timing they have no visibility into.

The fix: a table that the refresh never touches

Favorites live in their own table, keyed only by the station’s ID:

@Entity(tableName = "favorite_stations")
data class FavoriteStationEntity(@PrimaryKey val gasStationId: String, val addedAt: Long)

Reading them back is a plain join against whatever’s currently cached:

@Query(
    """
    SELECT gs.* FROM GasStationEntity gs
    INNER JOIN favorite_stations f ON gs.id = f.gasStationId
    ORDER BY f.addedAt DESC
    """,
)
fun observeFavoriteStations(): Flow<List<GasStationEntity>>

replaceGasStationsByProvince can delete and reinsert GasStationEntity rows as often as it wants — favorite_stations never gets touched, so the favorite survives every refresh, every province switch, every app restart.

The trade-off the join forces you to admit

An INNER JOIN only returns rows that exist on both sides. A favorite whose province hasn’t been fetched yet — or whose station has genuinely disappeared from the upstream feed — simply won’t appear. That’s correct behavior, but it’s a real gap between “you have 5 favorites” and “you can currently see 5 favorites,” and the easy mistake here is to let the join quietly hide that difference.

I made the gap a first-class value instead of a silent count mismatch:

data class FavoriteStationsResult(val stations: List<GasStationBO>, val totalFavoriteCount: Int) {
    val unresolvedCount: Int get() = (totalFavoriteCount - stations.size).coerceAtLeast(0)
}

The favorites screen shows unresolvedCount as a banner instead of just rendering a shorter list and hoping nobody notices they’re missing three stations they starred last month. It’s the same instinct that shaped the rest of the repository’s error handling here: GasStationsResult carries an explicit isFromCache flag rather than making the caller guess, and a failed background refresh routes to a stale-data banner instead of blanking a screen that already has content on it. Once you’ve decided the cache boundary is something the UI should be told about rather than hidden from, the favorites gap is just one more instance of the same rule, not a special case.

Takeaways