ADR-0011: Derived reader state in SQL, so per-reader lists can be paginated¶

Status¶

Accepted

Date¶

2026-09-02

Context¶

A reader's status in a journey is derived, not stored. JourneyProgress records when they started, whether they finished, and whether they collected the emblem; ChapterCompletion records each finished chapter. There is no status column and deliberately no last_active_at column, because either would have to be touched on every write and could silently fall out of step with the rows it summarises.

That was free while the Journeys hub returned the whole catalogue in one response: load every live journey, derive each reader's status in Python, sort the three groups in memory. One function, readers.journey_progress.derive_status, answered the question for both the hub and journey detail, which is what guaranteed the two screens could never disagree about whether a reader had started something.

The Journeys hub then became a preview with a paginated "see all" list behind it (see Journeys). Pagination changes the constraint: the database has to do the filtering and the ordering. An expression computed in Python cannot be offset into, and loading a thousand journeys to return thirty is a scan wearing a page's clothes. The catalogue is expected to reach the low thousands.

So the question is where derived per-reader state should be computed, given it now has to be queryable.

Decision Drivers¶

Must: a page of N journeys must cost the same whatever the catalogue size, in rows transferred and objects built.
Must: the group a journey appears in has to stay identical to the status journey detail reports. A reader seeing a journey under "in progress" on one screen and "not started" on another is a correctness bug, not a cosmetic one.
Must: no schema change. Status stays derived; a stored column is the thing previously rejected and nothing here changes that reasoning.
Should: behave identically on SQLite (local development) and PostgreSQL (CI and deployed), because this is logic that has to be trusted in both.
Should: keep one implementation of each rule, or make divergence detectable if not.

Considered Options¶

Option A: Derive in Python, paginate the resulting list¶

Keep derive_status as the only implementation, build every card, then slice.

Pros: one implementation of the rule, no duplication, no new SQL to review.
Cons: every request loads the whole catalogue regardless of page size, so the cost grows with the content forever. It is offset pagination that has already paid the price offset pagination is supposed to avoid. Measured at 0.14 ms per journey, so ~110 ms at 800 journeys to return three cards.

Option B: Store status and activity as columns¶

Add status and last_active_at to JourneyProgress and maintain them on write.

Pros: trivially filterable and orderable; a real keyset cursor becomes available.
Cons: reintroduces exactly the drift the derived design avoided. status depends on ChapterCompletion rows, so every completion, every unpublished piece and every chapter added to a live journey would have to write back to a summary row. A stale summary is invisible until a reader complains. Also a migration and a backfill.

Option C: Annotate the derivation in SQL, hydrate only the page in Python¶

Express status as a CASE over the reader's progress row plus an EXISTS on their completions, and activity as COALESCE(max(completion.completed_at), progress.started_at). Filter, order and paginate in the database. Build cards only for the returned rows.

Pros: page cost is independent of catalogue size; no schema change; status stays derived; plain ORM constructs that behave the same on both databases.
Cons: the rule now exists twice, once in Python for journey detail and once in SQL for the list. That is precisely the duplication derive_status was written to prevent.

Decision¶

We will use Option C, and treat the duplication as a risk to be detected rather than one to be argued away.

readers.journey_hub.journey_states(user) returns the eligible journeys annotated with status and the keys each group is ordered by. filter(status=...), order_by(...) and slicing all happen in the database.

Three things stay in Python, and only ever run over the page being returned:

chapters_count and completed_chapters_count. These must come from one set of chapters. Deriving them from two sources is what once produced a card reading "2 of 1": the denominator counted openable chapters while the numerator counted every completion. One set, one count.
current_chapter, the first openable chapter with nothing recorded against it plus its 1-based number. That needs an ordered walk of a journey's chapters, which does not express cleanly as a correlated subquery.
Card assembly, through the same readers.journeys.journey_card every other journey surface uses, so the card shape cannot fork.

Eligibility is a SQL filter too — published, and EXISTS at least one chapter whose piece is live. Skipping ineligible journeys during hydration instead would make count disagree with the rows served and hand out short pages.

The duplicated rule is pinned by tests.test_journey_list_reader.TestStatusParity, which asserts the SQL and the Python answers agree across every combination of reader state, including the asymmetry that is easiest to get wrong: every completion makes a journey in-progress, whether or not its chapter is still openable, while only completions on openable chapters count towards the number shown on the card.

Consequences¶

Positive¶

Hydration cost is independent of catalogue size: a page builds the same number of cards at 50 journeys and at 800. Request latency is not independent of it, only far less sensitive. Measured across a 16x catalogue (50 to 800 journeys), the hub went from 7.9 ms to 109.9 ms before, and from 6.7 ms to 12.7 ms after — still rising, because the counts scan, but no longer in proportion to the content.
No migration, no backfill, no summary column to keep in step.
The same annotated queryset serves the hub preview and the list, so the journey the hub shows first is by construction the first item the list returns.
The pattern generalises. Any future per-reader list — bookmarks, collected emblems, reading history — has a worked example of how to page derived state.

Negative¶

The status rule exists in two languages. A change to derive_status must be mirrored in journey_states, and the parity test is what makes that a failing build rather than a silent contract split.
The hub uses more queries than before, 9 against 4: a COUNT and a LIMIT 1 per group rather than one query for everything. Constant in the catalogue size, but a real increase.
Three COUNT queries remain, one per group, each scanning to answer, so the hub is not perfectly flat (6.7 ms to 12.7 ms across a 16x catalogue). They scan in the database and transfer no rows, which is the cheap end of the trade, but they are the first thing to look at if the catalogue gets genuinely large.
Reading journey_states requires holding both the ORM expression and the Python original in mind to check they still say the same thing.

Risks & Mitigations¶

Risk: the two derivations drift, and a reader sees a journey in one group on the hub and a different status on its detail screen. → Mitigation: TestStatusParity covers every combination of reader state, and both docstrings point at each other. A third caller must derive from one of the two, never add a third.
Risk: Case/Subquery/Exists behave differently across backends. → Mitigation: plain ORM constructs only, no raw SQL and no database-specific features; the suite runs on SQLite locally and PostgreSQL in CI, and the parity and ordering tests run on both.
Risk: the in_progress ordering key changes as the reader reads, so a row can move between pages mid-scroll. → Mitigation: documented in the API guide, with the advice to refetch from the start after recording a completion. Not solvable with offset paging; a proper fix needs a keyset cursor over a stable key, which activity is not.
Risk: ?limit= used to fetch the whole table in one request. → Mitigation: interfaces.api.pagination.DefaultLimitOffsetPagination caps it at 100, because DRF's max_limit defaults to unbounded.

ADR-0005: API conventions — gating, versioning, and the error contract the required status filter's 400 follows.
Journeys — the resulting contract: the hub, the paginated list, and why both carry reader state on the content path with optional authentication.