GET /flowsheet/playlist should carry previous_show_id / next_show_id so the archive can be walked show by show

[jakebromberg]

Problem

dj-site's archived-show view has no way to step to the show that aired before or after the one being viewed, because GET /flowsheet/playlist?show_id= describes one show in isolation. tubafrenzy resolved the neighbouring show ids server-side in the same request, which is what let flowsheetRadioShowDisplayPublic.jsp render << Previous Show / Next Show >>. dj-site has no equivalent, so a listener browsing the archive must return to the weekly calendar between every set.

Bill Burton reported the gap by email on 2026-09-08, the day after rollout. Consumer: WXYC/dj-site#1394.

Desired end state

GET /flowsheet/playlist?show_id= carries previous_show_id / next_show_id, null at the ends of the archive.

What tubafrenzy did — this is the design authority

libs/core/src/main/java/org/wxyc/flowsheet/FlowsheetRadioShowRepositoryImpl.java:194-212:

handle.createQuery("SELECT MIN(ID) FROM " + TABLE_NAME + " WHERE ID > :currentId")
        .bind("currentId", currentId).mapTo(Integer.class).findOne().orElse(0));
...
handle.createQuery("SELECT MAX(ID) FROM " + TABLE_NAME + " WHERE ID < :currentId")

• Ordered by ID, the surrogate PK — no ORDER BY, no reference to SIGNON_TIME or STARTING_RADIO_HOUR.
• Zero scoping: not by DJ, not by week, not by having entries (FLOWSHEET_ENTRY_PROD is never joined), not by having ended (SIGNOFF_TIME absent — contrast findOpenShows() in the same file, which does filter on it).
• The in-progress show is included. With no sign-off predicate, the live show is a valid nextShowID target from the instant its row is inserted at signon. CurrentRadioShowDisplayServlet.java:43-48 defines "current show" as getMaxRadioShowID() and computes prev/next for it, so the legacy site navigated both to and from the live show.
• Ends handled asymmetrically, and the old end is a latent bug: both queries .orElse(0), and the JSP guards only Next (flowsheetRadioShowDisplayPublic.jsp:18-27). On the oldest show, << Previous Show renders a link to radioShowID=0, which throws RadioShowDoesNotExistException (FlowsheetRadioShowService.java:229-230) and lands the visitor on an error page. Do not port that. Null at both ends is a deliberate divergence.

Legacy IDs were hand-assigned MAX(ID)+1 at signon (AbstractJdbiRepository.java:37-42), so ID order is creation order — which for that system was airtime order.

Answered against production (2026-09-08, 18:46 PDT)

32 inversions. shows.id does not preserve airtime order across the tubafrenzy import, so the cheap tubafrenzy-style id walk is off the table — it would present shows chronologically backwards at 32 boundaries.

shows_rows | open_shows | abandoned_90d | inverted_intervals
     72893 |       2814 |          2813 |                  0

inversions
        32

tie_groups | largest_group | rows_in_ties
         1 |             5 |            5

shows_with_zero_entries: 74

So the implementation is settled, and it is the more expensive of the two branches:

• Order by (start_time, id), not by id. 32 out of 72,893 is 0.04%, but a prev/next walk exists precisely to be followed sequentially, and 32 places where "Next Show" goes backwards in time is exactly the kind of thing Bill notices.

• Row-value comparison is required, not optional. There is one tie group and it holds 5 shows. Under strict < / > on a bare start_time, all five are skipped — a five-show hole in the walk. Use the search.service.ts:178-183 form:

  (start_time, id) > ($1::timestamptz, $2)  ORDER BY start_time ASC, id ASC  LIMIT 1

• Add shows_start_time_id_idx. Confirmed no usable index exists: prod shows only shows_pkey, shows_legacy_show_id_idx, and shows_open_start_time_idx (the partial one — 80 kB, 38 scans lifetime). The write-amplification argument that governs flowsheet does not apply here: shows is 72,893 rows growing at roughly 8/day, so a second btree is free in practice.

• inverted_intervals is 0. The end_time < start_time hazard the service comments defend against does not exist in current data. Do not spend code on it.

• 74 shows have zero entries (0.1%). tubafrenzy linked to them anyway; filtering would deviate from the authority and cost a flowsheet join on every playlist read. Recommend still not filtering, but the walk will occasionally land on one, so the show view must render an empty show without looking broken.

• The open-show backlog is confirmed and unchanged: 2,814 open, 2,813 of them older than 90 days. The end_time IS NULL trap below stands exactly as written.

Index and tie detail (now confirmed, kept for the implementer)

There is no usable index on start_time. Complete inventory for wxyc_schema.shows:

Index	Columns	Type	Predicate	Migration
shows_pkey	id	btree unique	—	0000_rare_prima.sql:115
shows_legacy_show_id_idx	legacy_show_id	btree unique	—	0034_legacy_id_columns.sql:14
shows_open_start_time_idx	start_time	btree	WHERE end_time IS NULL	0154_shows-open-start-time-idx.sql:47

That partial index covers under 4% of the table and excludes exactly the closed shows a visitor walks. 0154:4-6 states it: "The only two indexes on the table are shows_legacy_show_id_idx … and the primary key, so every end_time IS NULL read is a full scan." Two start_time-ordered lookups today are two sequential scans of 72,736 rows (0154:22, restated schema.ts:2594). Needed: CREATE INDEX shows_start_time_id_idx ON wxyc_schema.shows (start_time, id), non-partial, ~2-3 MB.

And strict < / > on a bare start_time would skip both rows of every tie. start_time is defaultNow().notNull() (schema.ts:2582) with no uniqueness constraint, and ties are documented production data in two shipped code sites that already tie-break on id:

flowsheet.service.ts:1458-1461 — "id is a deterministic tie-break for the second-granularity collisions the legacy ETL produces (see the 00:55:35/00:55:41 pairs in production's open-show tail)."

flowsheet.service.ts:839-841 (getShowsInTimeWindow) — "an unstable secondary order would reshuffle between identical requests."

The live tie-generator is still accruing — internal.route.ts:216-224: "start_time is NOW() — the webhook-delivery instant … these placeholder values now PERSIST until Phase 6a retires this webhook." (Not the ETL: jobs/flowsheet-etl/job.ts:177 reads tubafrenzy's per-row SIGNON_TIME and skips unparseable rows, and deliberately avoids the hour-rounded STARTING_RADIO_HOUR.)

Use the row-value form, per the in-repo precedent at search.service.ts:178-183 — same direction on both keys, ORDER BY <col> <dir>, id <dir>:

(start_time, id) > ($1::timestamptz, $2)  ORDER BY start_time ASC, id ASC  LIMIT 1

The trap: do not filter on end_time IS NULL

Whatever ordering you pick, resist adding a "skip open shows" predicate. flowsheet.service.ts:773-777 puts it in block capitals:

"A NULL end_time is not 'still on the air.'" It has two causes this column cannot distinguish: the show is genuinely live, or its show_end delivery was dropped and the column stayed NULL permanently.

0154:22-26: 2,814 open shows on 2026-08-21, of which 2,813 are legacy ETL imports stretching back to 2006 — all but one with NULL primary_dj_id, exactly one started in the last 90 days. Filtering on open-ness would exclude 2,813 historical shows to exclude one live one.

If you do need "is this the on-air show", it is not a bare max(shows.id). getOpenShows subtracts a terminal-marker carve-out (flowsheet.service.ts:1546-1563): is_current = (id === max(shows.id)) && !isLatestEntryShowEnd(id), because a show whose show_end webhook was lost holds max(id) forever. Note isLatestEntryShowEnd carries a self-warning that it "is not self-safe" without an end_time guard (:1668-1678); every current caller supplies one.

Recommendation: point next_show_id at the live show, matching tubafrenzy. If you cache the response, exclude the newest show from the cache exactly as RadioShowDisplayServlet.java:81-91 did — the real hazard is freezing a null next_show_id on the newest show after a later show starts, not linking to a live one.

Known-degenerate rows a walk will land on

• Empty shows are real, with a named instance: tests/unit/services/flowsheet.getOpenShows.sql.test.ts:88-90 — "production show 74840 is open with zero entries." LIKELY_ABANDONED_ENTRY_THRESHOLD = 4 exists because sub-4-entry shows are common. tubafrenzy linked to them anyway; filtering would be a deviation from the authority and would cost a flowsheet join on every playlist read. Recommend not filtering.
• Inverted intervals are real: flowsheet.service.ts:1607-1613 — "Legacy rows carrying an add_time earlier than their show's start_time are not hypothetical." No CHECK constraint, no repair migration.
• NULL start_time is structurally impossible (NOT NULL DEFAULT now(), schema.ts:2582).
• No data-quality audit of shows exists; no migration has ever touched its row data.

Contract: fix the drift in the same PR

ShowPlaylist at wxyc-shared/api.yaml:1651-1670 is referenced only by this route, and there is no exported TypeScript type for it — dj-site's hand-written ShowPlaylistWire isn't drift-avoidance, it's the only option. Six drifts, all verified:

1. entries is declared FlowsheetEntryResponse (v1); the route emits projectEntriesV2 — the v2 discriminated union (flowsheet.controller.ts:1388).
2. Declared specialty_show; emitted specialty_show_name (flowsheet.service.ts:2112-2116).
3. No required: list at all; the route always emits.
4. No identifier field declared — neither id nor show_id. The route spreads the shows row, so the key is id.
5. Six undeclared emitted columns: getShowMetadata does db.select().from(shows) (:2097), so primary_dj_id, specialty_id, legacy_show_id, legacy_dj_name, legacy_dj_id, dj_name_override all ship undocumented.
6. show_djs $refs OnAirDJ, which requires dj_name: string; the route emits it nullable (:2101-2104).

Adding two fields validates without touching the schema (no additionalProperties: false), so it is technically additive — but they would be the 7th and 8th undeclared fields on a response the spec already misdescribes six ways. Fixing it is documentation-only, no runtime behavior moves, and it is small.

Acceptance criteria

• The inversion query is run and recorded. 32 inversions — see above. Ordering key is (start_time, id).
• Row-value comparison (start_time, id), not strict </> on a bare column — there is a real 5-show tie group.
• previous_show_id / next_show_id on the response; null at both ends, with a test for each.
• No end_time predicate anywhere in the implementation.
• shows_start_time_id_idx added in the same migration (non-partial, (start_time, id)).
• EXPLAIN showing both lookups are index scans on the new index, pasted in the PR.
• The on-air-show decision stated; Archived show view has no "Previous Show" / "Next Show" navigation dj-site#1394 is waiting on it.
• api.yaml's ShowPlaylist drift fixed alongside; dj-site's ShowPlaylistWire and EMPTY_SHOW_PLAYLIST (types.ts:50-59) gain the two fields.

Related

• Archived show view has no "Previous Show" / "Next Show" navigation dj-site#1394 — the consumer, blocked on this. Answer the on-air-show question there.
• Archived show view drops the ROTATION / REQUEST / EXCLUSIVE capsules, though the endpoint already sends all three flags dj-site#1393 — the badges half of the same user report; no backend work needed for that one.

[jakebromberg]

Investigation: the original sketch was wrong, not merely incomplete

Body rewritten. Four things changed, in descending order of consequence.

1. tubafrenzy ordered by id, not start_time, and I proposed the opposite. FlowsheetRadioShowRepositoryImpl.java:194-212 is MIN(ID) WHERE ID > :currentId / MAX(ID) WHERE ID < :currentId — no ORDER BY, no reference to SIGNON_TIME. Legacy ids were hand-assigned MAX(ID)+1 at signon (AbstractJdbiRepository.java:37-42), so id order was airtime order there. Whether Backend's shows.id preserves that across the import is the single question that decides this ticket, and it is now the first acceptance criterion.

2. There is no usable index on start_time. The only one is partial — WHERE end_time IS NULL (0154:47) — covering under 4% of 72,736 rows. 0154:4-6 says outright that before it, "the only two indexes on the table are shows_legacy_show_id_idx … and the primary key." So "two single-row lookups ordered by start_time" is two sequential scans today. Under id ordering, shows_pkey serves both as index-only scans and no new index is needed at all.

3. Strict < / > on a bare start_time skips both rows of every tie, and ties are documented production data. Two shipped sites already tie-break on id for this exact reason — flowsheet.service.ts:1458-1461 ("second-granularity collisions the legacy ETL produces … the 00:55:35/00:55:41 pairs in production's open-show tail") and :839-841. The live generator is still accruing: internal.route.ts:216-224 — the webhook stub writes start_time = NOW(), and "these placeholder values now PERSIST until Phase 6a retires this webhook." Not the ETL, which reads per-row SIGNON_TIME and deliberately avoids the hour-rounded STARTING_RADIO_HOUR.

4. An end_time IS NULL filter would be a trap. flowsheet.service.ts:773-777 in block capitals: "A NULL end_time is not 'still on the air.'" 2,814 open shows at 2026-08-21, of which 2,813 are legacy imports back to 2006 whose show_end never arrived; exactly one started in the last 90 days. And is_current is not a bare max(shows.id) — getOpenShows subtracts a terminal-marker carve-out (:1546-1563), because a show whose show_end webhook was lost holds max(id) forever.

Smaller findings

• The legacy "Previous Show" link is itself buggy and should not be ported: both queries .orElse(0), but the JSP guards only Next (flowsheetRadioShowDisplayPublic.jsp:18-27), so on the oldest show it links to radioShowID=0 → RadioShowDoesNotExistException → error page. Null-at-both-ends is a deliberate divergence.
• Caching hazard, already solved once. RadioShowDisplayServlet.java:81-91 deliberately excludes the most-recent show from its cache because its nextShowID is still 0 and would freeze the link off for 24h. If we cache, copy that.
• Empty shows are real — tests/unit/services/flowsheet.getOpenShows.sql.test.ts:88-90 names "production show 74840 is open with zero entries." tubafrenzy linked to them anyway; filtering would deviate from the authority and cost a flowsheet join on every playlist read.
• Inverted intervals are real — flowsheet.service.ts:1607-1613: "Legacy rows carrying an add_time earlier than their show's start_time are not hypothetical." No CHECK constraint, no repair migration, no data-quality audit of shows has ever been done.
• The contract drift is worse than dj-site's doc comment records — six items, not four; see the body. Notably the published ShowPlaylist declares no identifier field at all, and there is no exported TypeScript type for it anywhere in wxyc-shared, which is why dj-site hand-writes ShowPlaylistWire.

Incidental, unrelated to this ticket

jobs/flowsheet-etl/job.ts:369 uses LEAD(start_time) OVER (ORDER BY start_time) with no id tie-break — nondeterministic on exactly the ties above. Low impact (the job is unscheduled), same defect class. Not filed.

[jakebromberg]

Production results (read 2026-09-08, 18:46 PDT)

The deciding query came back 32, so the cheap branch is closed. Body updated; ordering is (start_time, id) with a row-value comparison and a new index.

shows_rows | open_shows | abandoned_90d | inverted_intervals
     72893 |       2814 |          2813 |                  0

inversions: 32
tie_groups: 1   largest_group: 5   rows_in_ties: 5
shows_with_zero_entries: 74

Reading these one at a time:

32 inversions. shows.id does not carry airtime order across the import, so tubafrenzy's MIN(ID) WHERE ID > n cannot be ported as-is. It is only 0.04% of the table, but a prev/next control exists to be followed in sequence, and 32 points where "Next Show" moves backwards in time is the kind of thing that gets reported as a bug.

The tie group is real and it is not a rounding error. One group, five shows sharing a start_time. Strict < / > on a bare start_time skips every row of a tie, so the naive form would put a five-show hole in the walk. The row-value comparison is required, not defensive.

Zero inverted intervals. end_time < start_time does not occur in current data. The hazard flowsheet.service.ts:1607-1613 defends against is real for entry timestamps versus show start, but not for the show interval itself — no code needed for it here.

74 empty shows, 0.1%. Small enough that filtering them isn't worth a flowsheet join on every playlist read, and tubafrenzy didn't filter either — but large enough that a walk will hit one, so the show view needs to render an empty show without looking broken.

The open-show backlog is unchanged since the 2026-08-21 measurement: 2,814 open, 2,813 older than 90 days. Nothing has been cleaned up, which is consistent with BS#2235's operator UI having no product surface yet. The end_time IS NULL trap stands.

Index confirmation. shows carries exactly three indexes in prod — shows_pkey (8,448,955 scans, 4,480 kB), shows_legacy_show_id_idx (15,149 scans, 4,480 kB), and shows_open_start_time_idx (38 scans lifetime, 80 kB, the partial one). No general start_time index exists, confirming the sketch's two lookups would be sequential scans today.

On cost: shows is 72,893 rows growing at roughly 8/day, so the write-amplification argument that governs flowsheet (and that migrations 0083, 0144 and BS#2080 each turned on) simply does not apply. A second btree here is free in practice.

Incidental, from the same probe

flowsheet-etl and rotation-etl last ran 2026-08-08 22:00 PDT — 30 days ago, which independently corroborates internal.route.ts:216-224: Phase 3 of the decommission unscheduled those jobs, so the webhook stub's start_time = NOW() placeholders are the live tie generator and will keep accruing until Phase 6a. That is the mechanism behind the tie group above, and it means tie count grows over time rather than staying at 5.