Trip days don't exist in the database

An itinerary app has one screen that seems to demand its own data model: the timeline, with its "Day 1", "Day 2", "Day 3" and each day's stops hanging underneath. The schema that draws itself on the whiteboard is trip → days → stops, three tables, two foreign keys. In fact that's exactly what the phase table in Jornia's roadmap said when it described the first one: "Room (trips→days→stops)".

What got built in v0.2.0 was something else, and the roadmap note says so in one line: Trip → Stop, and days are "derived from the date range, not persisted (fewer rows to keep consistent when dates change)". The database has trips and stops. Days don't exist.

The impossible state that can't happen

The reason is what happens when someone edits a trip's dates. With a days table, changing "March 14 to 16" to "20 to 21" stops being an UPDATE and becomes a small reconciliation: delete the days that are no longer needed, create the ones that are missing, decide what to do with the stops hanging off the deleted days, and do all of it in a transaction so nobody sees the intermediate state. Every one of those operations is a place where you can end up with an orphaned row, a duplicated day, or a three-day trip with four rows.

Without the table, the problem isn't solved: it disappears. The trip stores startDate and endDate, and the function that draws the timeline walks that range:

var date = trip.startDate
var index = 1
while (date <= trip.endDate) {
    val dayStops = byDate[date].orEmpty().sortedWith(/* hora local; sin hora, al final */)
    days += TimelineDay(index, date, dayStops)
    date = date.plus(DatePeriod(days = 1))
    index++
}

The comment on that function sets the contract: the days are exactly the dates in the range. A day with no stops exists and shows up empty. There's no way to have one day too many or too few, because there's nowhere to store it. It's the same idea as any integrity constraint, taken to the extreme: instead of protecting an invariant, you remove the piece of data that could violate it.

One detail helps: the dates are LocalDate, with no time zone. Adding a day to a calendar date has none of the problems of adding 24 hours to an instant. The timeline's "day" is the day at the place, and each stop stores its own local date and its zone.

The bill: stops that fall outside

Now for what nobody mentions when they sell you "derive instead of persist": deriving isn't free. It costs you somewhere else, and here it costs you exactly in the case that looked like it was simplifying things.

If you shorten the trip to "20 to 21", the stops on the 14th and 15th are still in the database, because a stop stores its date and doesn't care what range the trip has. But buildTimeline only builds the days in the range, so those stops don't show up anywhere. We had to decide what to do with them, and what we decided is written down in the code and in the tests:

• They aren't deleted. They come back if the trip is extended again. There's a test for it: move the dates, the two stops stop counting, extend again, the two come back.
• You get a warning before saving. The edit form counts how many stops would fall outside the chosen range and, if there are any, opens a dialog: "2 stops fall outside the new dates and will no longer appear in the timeline. They'll come back if you extend the trip again."
• When you import a booking that falls outside the range, the trip is extended. The comment on that code says it plainly: days are derived from the range, and without extending it the stop wouldn't be visible.

That third point is the most revealing. The trip's range is the only source of truth about which days exist, so any operation that creates a stop has to remember to make sure the range covers it. With a days table, that coupling would live in a foreign key. Without it, it lives in the discipline of whoever writes the next creation flow.

Everything that counts stops has to know what the timeline knows

The subtlest cost showed up later and is documented in one specific function, plannedStops. Its comment explains the symptom that motivated it: anything that counts stops or looks for the next one has to start from the set the trip shows today, "or it will say things the timeline contradicts ("4/8 stops" on a trip that's empty inside)".

It's the logical side effect. If the timeline hides some stops but the home card counts every one in the database, the app contradicts itself. So the trip card, the next stop on the home screen, the home screen widget and the launcher's dynamic shortcut all go through the same filter before calculating anything. The plannedStops test checks the edges (the stop on the first day, the one on the last, the one on the day after the end) and that trips don't get mixed up.

And then there's the exception, which is also written down: money doesn't go through that filter. A paid flight stays paid even if its date has ended up outside the range, and the expenses screen counts it on purpose. Two questions that look like the same one ("what stops does this trip have?") have different answers depending on whether you're asking about what you see or what you owe.

What this rules out and what it doesn't

Better not to oversell it. Deriving the days works because in Jornia a day has no attributes of its own: no per-day notes, no title, nothing that lives on the day rather than on a stop. If tomorrow someone asked for a "day name" or "weather pinned per day", we'd have to revisit the decision, because a piece of data that exists only on the day has nowhere to be stored without a table. We haven't written that trigger down anywhere, and we should: it's a condition the decision depends on and that today simply happens to hold.

It's also not the right model if days need to sync between devices as entities. In an app that's local today, with Room as the only source of truth, it's the cheapest option. I don't need to argue that it's the right one in general.

What we take away

Derived data can't get out of sync. Trip days don't get corrupted, because they aren't stored. What you pay is that everything that consumes them has to derive them the same way.

The cost of deriving shows up at the edges. Not in the happy path, but at the moment the source data changes and orphans something else: the out-of-range stops are that edge. Decide what to do with them before someone loses them.

If a question has two answers, make it on purpose. plannedStops for what you see, the full sum for what you pay. The problem isn't that they differ: it's that they differ by accident.

If your data model has a table that only exists so a screen has somewhere to hang things, get in touch and we'll look at it together.