Video Library — SQL-backed feed with scroll paginationCopy Anchor Link

The debate video library (rounds, lectures, top picks) is stored in SQL and served one page at a time. Opening /videos used to download the entire dataset — every round and lecture, ~1.1 MB of JSON — before a single card could render; now the first screen costs one small page and the rest arrives as the grid is scrolled.

• Where: /videos, /videos/[category] (LEARN), and the rounds grid
• Package: debate-videos, debate-data-sync

How it fits togetherCopy Anchor Link

The JSON assets under debate-data-sync/data/videos/ remain the source of truth that the YouTube sync writes to. They are projected into the videos table, which is what the API queries:

data/videos/rounds-{policy,pf,ld,college}.json ─┐
data/videos/debate-lectures.json                ├─► buildVideoRows()  ──► videos (SQL table)
data/videos/debate-top-picks.json               ─┘   (src/videos/video-rows.ts)

The four round assets and the lectures asset are merged into one row per video id (rounds win a collision, matching the old response's de-duplication), and debate-top-picks.json sets the is_top_pick flag.

Two derived columns keep filtering cheap: season_year is the competition season a video falls in (June-to-June, 0 for legacy pre-2010 content), and search_text is the lowercased title + channel + description that free-text search matches with LIKE. published_ms is the parsed publish timestamp, because a handful of rows carry long-form dates ("May 14, 2013") that sort wrongly as text.

Serving a pageCopy Anchor Link

GET /api/videos?source=round&style=2&year=2026&q=finals&sort=Views&limit=60&offset=60&facets=1
  → { videos: [...], total, offset, limit, hasMore, facets, backend }

GET /api/videos/meta
  → { counts, lectureCategories, topics, champions, history, backend }

lib/videos/video-repository.ts owns both. Filtering, search, sorting and pagination are pushed into SQL; ties are broken by video id so paging never repeats or skips a video. facets=1 adds the per-season and per-style counts the filter dropdowns show — each dimension ignores its own filter and both ignore the search term, so every dropdown option keeps showing a live total even though the client no longer holds the whole library.

/api/videos/meta is the small, fetch-once companion: library counts for the quick-link cards, the lecture-category cards, and the season topic/champion tables. Nothing in it grows with the number of videos.

When its query includes a category, style, season, or top-picks filter, /api/videos/meta also scopes its keyword and tournament chips to that same selection. getVideoSuggestions applies the shared VideoQueryParams rules before counting title/search-text matches in the JSON fallback; the SQL path uses the equivalent buildConditions predicates. The typed query is omitted on purpose: chips describe the open category rather than the user's already narrowed result set.

JSON fallback. If the videos table is missing, unreachable, or not yet seeded, both endpoints rebuild the same rows from the JSON assets and answer from memory with identical semantics (src/videos/video-query.ts is the in-memory twin of the SQL). The response's backend field says which one answered — "sql" or "json". A fresh clone or a fresh preview database therefore still renders the full library; seeding is an optimisation, not a prerequisite.

Client pagingCopy Anchor Link

hooks/useVideoFeed.ts
  useVideoFeed(filters)   — one feed: refetches from offset 0 when the filters
                            change, appends the next page on loadMore()
  useVideoMeta()          — /api/videos/meta, fetched once
  → panels/LecturesPage.tsx, panels/DebateVideosPanel.tsx
      → hooks/useInfiniteScroll.ts — observes the sentinel below the grid and
        calls loadMore() when it comes into view

Filter state (search, season, style, sort, category, top picks) lives in useVideoState and is sent to the server. Two preferences ride alongside it as explicit id lists rather than being columns the query already understands: favourites are sent as an ids allow-list when the favourites filter is on, and hidden videos are sent as an excludeIds deny-list — both applied in filterVideoRows/computeVideoFacets (the JSON fallback) and buildConditions's inArray/notInArray (the SQL path), so a hidden video disappears from the grid and stops inflating the season/style dropdown counts. The deny-list is left off while a search is active, so an explicit search still surfaces a hidden video, as it always has — otherwise there would be no way to find it again to unhide it.

Applied on the client, but no longer stored there alone. Both live in state/videoLibrary.ts, which persists them, mirrors each change to the signed-in user's account through Tool Data Sync, and prompts a signed-out user once that their collection is browser-only. useVideoState keeps the Set<string> shape its filters need and re-reads that store on every storage event — which is what makes favourites merged from another device appear in an open grid without a reload.

Two details of that store are load-bearing:

• The record shape changed, and the old one still reads. Favourites and hidden videos shipped as a JSON array of bare video ids, which the account sync cannot key (saved_tool_records is keyed by a record's own id field). They are now { videoId, savedAt } / { videoId, hiddenAt } objects, and the reader normalizes the legacy strings per entry — a debater can build a favourites list over a whole season, and a format change that dropped it would be a silent loss visible only to them. Upgraded entries are dated to the epoch, not to the upgrade, so a season's collection is not reordered the first time the new code runs.
• Reports are keyed too. debateVideoReports pushed an unkeyed object, so a report sat in one browser where nobody could follow it up. New reports carry an id; older ones get a stable id derived from their video and submission time, so re-reading does not sync the same report as a new row each page load.

Stacked playlistsCopy Anchor Link

A stack is a small group of videos the library shows in one slot: a round and the round-analysis video made from it, a round split across two uploads, a lecture series. The grid renders one card per slot, flipped with < / >.

Where the links come from. src/videos/video-stacks.ts reads the YouTube ids out of every description (watch?v=, youtu.be/, /embed/), keeps the ones the library actually holds, and unions the pairs — a one-sided "Full Debate:" link joins the two just as a mutual pair does. Two ceilings keep a verbose uploader from chaining the library together: a description linking more than MAX_LINKS_PER_DESCRIPTION (8) library videos is a playlist dump and is ignored, and a group over MAX_STACK_SIZE (12) is a channel index and is dropped. Members are ordered round-first, then by publish date, then by id; the first member's own video id is the stack key. Against today's assets that yields 10 stacks over 29 videos.

Nothing here is hand-maintained: an editor links two videos by editing a description on YouTube. There is no override list to keep in sync.

How it is stored. buildVideoRows runs assignVideoStacks once every row exists — a stack joins two rows, so it cannot be read off one tuple — and stamps stack_key / stack_position onto each row. Both are columns on the videos table (migration 0041_video_stacks.sql) and slots 18 and 19 of the UI tuple; they are null for an unstacked video, so the tuple trims back to its old length for almost every row. A database seeded before this existed has null keys and no stacks until it is re-seeded — the JSON fallback computes them either way.

How the client gets the companion. The feed marks a row with its stack key but cannot carry the other members, so:

GET /api/videos/stacks?keys=T77G1CdZx9E,qx7Xx_6exzk
  → { stacks: { T77G1CdZx9E: [ …members, primary first ] }, backend }

hooks/useVideoStacks.ts collects the keys of the loaded feed and requests only the ones it has not asked for yet — infinite scroll appends pages continuously, and re-requesting the whole set per page would be a request per page for data that does not change. components/video-grid/video-stacks.ts then does the positional half: buildVideoSlots folds a stack into the slot its first member occupies, so the results keep the feed's order and no video is dropped; a stack whose members have not arrived yet renders as plain cards, so the grid never waits on the request. The Stacked playlists toggle (useVideoState, on by default) passes enabled: false and gives every video its own slot again.

On the watch page. app/videos/_watch/video-route-page.tsx resolves the video's own stack key with getVideoStacks([key]) and passes the members to VideoWatchPage as stack; components/watch/WatchStackPlaylist.tsx lists them under the player in stack order, current one marked, each labelled Round / Analysis / Part N from its address's variant segment. Stack members are dropped from the "Related videos" rows so they are not listed twice.

Addresses. lib/video-route.ts files a round at /videos/<season>/<tournament>/<round>/<aff>-<neg> — no vs — and a video made from it (a lecture whose title reads as that round, or one part of a split upload) one segment below, at …/<teams>/<variant>: analysis (the category, less a leading round-), part-2, or both joined. The fifth segment is served by app/videos/[category]/[event]/[matchup]/[teams]/[variant]/page.tsx. getVideoByRouteSegments still matches the previous four-segment spelling (…/dartmouth-sv-vs-michigan-pr-round-analysis, rebuilt by previousVideoRouteHref) and the older three-segment one, and the page redirects both to the canonical path.

What a big grid costsCopy Anchor Link

The grid is unbounded: every page of infinite scroll appends sixty more cards and none are recycled, so a browsed-through library holds several hundred live cards. Four things scaled with that count and made the page feel frozen while videos loaded. All four are now per-page rather than per-card:

The sidebar beside the grid mattered too: the router used to prefetch an RSC payload for each of the ~50 links in it while the feed was still loading. Its links now pass prefetch={false}, so they are fetched on click rather than on sight — which is what lets every section render expanded without the burst coming back. See docs/features/app-nav-dock.md.

packages/debate-videos/test/glowing-effect-listeners.test.tsx pins the first row of that table: mounting 120 glows must still install exactly one pointermove listener and one scroll listener, and unmounting the last one must release both.

Seeding the tableCopy Anchor Link

From a machine with wrangler credentials:

cd apps/debate-ai.com
bun run db:generate                     # migrations, after a schema change
bun run db:seed:videos                  # JSON → drizzle/seed/videos-seed.sql, applied locally
bun run db:seed:videos:d1               # same file, applied to Cloudflare D1

Or, from a deployment itself, POST /api/admin/videos/seed (admin-gated). The Worker already bundles the JSON assets, so it seeds from its own copy — no credentials and no data transfer. GET on the same route reports the row count and whether the feed is being served from SQL or the JSON fallback. A Seed videos card on /admin, next to "Recompute video stacks", calls both: the row/serving-source status renders next to the button (lib/videos/format-seed-videos-result.ts#formatSeedVideosStatus), and a run's own counts and duration replace it once it finishes (formatSeedVideosResult).

Both paths build their statements with buildVideoSeedStatements, so they load identical data. The seed is idempotent: rows are upserted by video id and any row the assets no longer carry is pruned, so re-running after a YouTube sync mirrors them exactly — and because the prune only removes rows the run did not touch, the table is never empty mid-run. The generated SQL file is not committed; regenerate it from the assets.

Admin management of published videosCopy Anchor Link

/admin used to only reach videos on their way in: the "Round videos" queue lists youtube_round_videos rows, and publishing or deleting one is the last moment an admin could touch it. Once published, a video left that queue (the listing left-joins videos and drops the matches), so a wrong title, a misparsed tournament or a video that should never have gone live could only be fixed from a database console.

The Video library card (components/admin/VideoLibraryTable.tsx) is the other half: it pages the public videos table itself.

GET    /api/admin/videos/library         q, style, source, transcript, sort, dir, page, limit
PATCH  /api/admin/videos/library/[id]    partial row — only the fields sent are written
DELETE /api/admin/videos/library/[id]    remove from the library, and exclude from resync

All three are admin-gated by getAdminAccess() like every other /api/admin/* route, and all three delegate to lib/videos/admin-library.ts so the rules live in one testable module rather than in the route handlers.

Two behaviours are worth knowing before changing this:

• An edit rewrites the derived columns. title, channel, published_at and description are what an admin sees, but the feed sorts on published_ms, season-filters on season_year and searches search_text. buildLibraryUpdate recomputes all three from the merged row, so editing a title alone still leaves search_text matching the stored channel and description, and a corrected date re-sorts the video. Writing the columns directly would silently leave the video searchable only under its old title.
• search_text is recomputed live, not from the read that started the request. search_text depends on three fields (title/channel/ description), but a patch may only touch one of them. Two admins editing different fields on the same video close together — both reading the row before either writes — would each compute search_text from their own snapshot of the field they didn't touch; title/channel themselves would still land correctly (each UPDATE only sets the columns its own patch touched), but whichever search_text write landed second would overwrite it with a stale combination that never matched the row's actual columns until a later edit happened to touch search_text's inputs again. updateLibraryVideo closes this with withLiveSearchText, which rewrites buildLibraryUpdate's computed search_text into a SQL expression that references videos.title/channel/description directly for whichever field the patch didn't touch, evaluated by SQLite when the UPDATE statement actually runs rather than by JS against an earlier read — the same "resolve against whatever is currently stored, not the caller's own snapshot" fix this codebase already applies to favoriteTools and the other account-settings lists (see /api/settings's docstring).
• A removal is recorded, not just applied. deleteLibraryVideo deletes from videos and youtube_round_videos, then upserts youtube_video_exclusions — the same table resyncYouTubeRounds reads before its channel loop. Without that row the next Monday sync would re-ingest the video and the next "Publish all" would put it back, which is exactly the loop the queue's own delete was given an exclusion row to avoid.
• An edit is recorded too. buildLibraryUpdate always sets admin_edited, so db:seed:videos (or the admin seed endpoint) leaves the row's columns alone on its next run instead of overwriting the edit with the JSON asset's stale value — the seed-side counterpart to the exclusion row above. The flag is never cleared; there is no "go back to asset-driven" action.
• "Publish all" costs a fixed handful of queries, not one per round. D1 allows a Worker invocation 1,000 queries (50 on the Workers Free plan), and a statement awaited on its own spends one of them. publishRoundVideos used to await every upsert individually, so publishing a queue of 1,200 rounds asked for 1,214 queries and the request died at the driver partway through, having already written most of the rows. The statements are the same — a videos row uses most of D1's 100-parameter budget on its own, so each round still needs its own — but they are sent through db.batch() in runs of a hundred (lib/database/query-budget.ts), which is one query per batch and one transaction per batch: the same 1,200 rounds now cost 14 queries, and a failure rolls its batch back instead of leaving the queue half-published. The admin-edited lookup, the queue clear and the stack recompute's writes go out the same way.
• A published, admin-edited round also survives a re-publish. resyncYouTubeRounds re-walks every subscribed channel's uploads since a fixed floor date on every run, with no check against videos — only an explicit admin removal (the exclusion row above) keeps a video out of the queue. So a round published (and possibly corrected here) weeks ago can resurface in youtube_round_videos and reach publishRoundVideos (lib/videos/publish-round-video.ts) again, computed fresh from the unedited YouTube listing — the "Publish all" and per-row "Publish" actions both go through it. publishRoundVideos now re-reads which of the batch's ids are already published and admin_edited immediately before writing, and skips those rows entirely rather than letting the recomputed round data overwrite the correction — the same guarantee admin_edited already gives db:seed:videos above, closed for this second writer of the table.

Each listed row also carries transcriptWords (the word count of its video_documents transcript, null when there is none or it is empty) and hasCaptions (a video_transcripts row exists in any language), read as correlated subqueries so the table never ships a transcript body. The outer video_id in those subqueries is written table-qualified by hand: drizzle renders a single-table query's columns bare, and a bare video_id inside the subquery binds to the subquery's own table, matching every row. transcript=with|without filters on the same expression, so the count and the page agree.

The transcript editor (VideoContentDialog) can seed the transcript box from /api/transcript — the same cached caption route the player uses — through captionsToTranscriptMarkdown in debate-videos: cues are regrouped into sentences, a new ## Part N (m:ss) section starts at a 20-second silence or every ten minutes, and the author is set to youtube. Nothing is saved until the editor saves. Its "paste any YouTube link" box resolves the id with parseYouTubeVideoId and opens the dialog even for an unpublished video; video_documents is keyed by id alone, so the transcript is waiting when the video is published.

Listing uses offset paging rather than the queue's (publishedAt, id) keyset cursor: this table has numbered pages and sorts on columns that are not unique (view count, channel), which a two-column cursor cannot express.

lib/videos/__tests__/admin-library.test.ts covers the merge, the derived columns, the search/filter/sort/paging, the exclusion write, and two admins editing different fields on the same video close together leaving search_text consistent with both edits, against a real in-memory SQLite database.

Known gapsCopy Anchor Link

• Imported captions are split on silences, not on speakers, so the "Part" sections only approximate speeches until an editor renames and merges them.
• The watch page only exists for published videos, so a transcript written through the "paste any link" box for an unpublished video is not visible to anyone until that video is published.
• Stacked playlists are only as good as the descriptions: a round-analysis video that names its round in the title but does not link it is not paired with it, and there is no curated override list — the fix is an edit to the YouTube description.
• stack_key/stack_position are written by the seed, so an existing database shows no stacks until it is re-seeded. Fixed: that understated the gap — a round published through the live YouTube pipeline (publish-round-video.ts#publishRoundVideos, behind the admin "Publish"/"Publish all" actions, and the legacy single-round POST /api/admin/youtube/videos/publish endpoint) was never part of the JSON assets to begin with, so a full re-seed never reached it either — it stayed unstacked forever, not just until the next seed. lib/videos/recompute-video-stacks.ts re-derives every row's placement over the whole table (a round and its analysis are commonly published weeks apart, by different pipelines, so the link can only be found by looking at everything at once) and rewrites only what changed; both publish paths call it after writing, so every future publish keeps stacking current on its own. For a database whose stacks fell behind before this wiring existed, POST /api/admin/videos/recompute-stacks (admin-gated) backfills them in one call — safe to re-run, since a row already carrying its correct placement produces no write. A Recompute stacks button next to "Resync video view counts" on /admin calls it directly, mirroring that button's own "checked N, updated M" summary line (lib/videos/format-recompute-stacks-result.ts).
• Search is LIKE-based token matching (every token must appear in title, channel or description). The previous client-side search used Fuse.js fuzzy matching, so a misspelled query that used to return near-matches now returns nothing.
• Facet counts do not deduct hidden videos: hiding a video removes it from the grid, but the season and style dropdowns still count it. Fixed: VideoQueryParams now carries an excludeIds deny-list alongside the existing ids allow-list, applied by filterVideoRows (the JSON fallback and computeVideoFacets) and by buildConditions' notInArray (the SQL path, which both the row query and facetsFromSql build on) — the same one-parameter-two-consumers shape favourites already used. DebateVideosPanel and LecturesPage send the hidden-video set as excludeIds except while a search is active, so a hidden video can still be found in order to unhide it.
• Favourites are sent to the feed as an explicit ids allow-list, so the favourites filter is bounded by how many ids fit in a request. A user with a very large collection would need the filter pushed into the query instead.
• The favourites/hidden merge is a union keyed by videoId, so un-favouriting on one device and re-favouriting on another cannot conflict — but neither can a deliberate "clear my favourites" propagate to a device that is offline at the time and still holds them; that browser's copy is pushed back up on its next sign-in.
• The seed runs as a separate step from sync-youtube, so newly synced videos reach the site only after the seed is re-run (until then the JSON fallback, not the table, is what carries them).
• A seed run is not atomic. Statements commit as they go, so an interrupted run leaves the rows it already wrote — and because the feed decides where to read from by asking whether the table has any rows, a partial table is served as if it were complete. This is not theoretical: the first run against D1 failed on an oversized statement and left exactly 100 rows. Re-running the seed converges (rows are upserted and stale ones pruned), and GET /api/admin/videos/seed reports the row count so the state is visible, but a run that fails needs a re-run rather than being left alone. Making the statements one atomic D1 batch would close this. Closed: seedVideosIntoDb (lib/videos/seed-videos-to-db.ts) now runs every generated statement — the upsert batches and the trailing prune DELETE — as one db.batch() call instead of sequential awaited db.run()s, so a mid-run failure leaves the table exactly as it was rather than partially written. Both drivers getDB() can return (drizzle-orm/d1 in production, drizzle-orm/libsql locally) support .batch(), so this is uniform across environments. lib/videos/__tests__/seed-videos-to-db.test.ts proves the rollback against a real in-memory SQLite database (a mock of db.run couldn't demonstrate an actual rollback) and adds regression coverage for the upsert/prune behavior that had none before. This has not been verified against a live production D1 instance at the video table's real row count — same "unverified against production" caveat openCaselist Sync already carries for its own API guess.
• An admin edit to a published video is written to the videos table only. The committed JSON assets under debate-data-sync/data/videos/ are still the seed's source of truth, so re-running db:seed:videos upserts the asset's version back over the edit. Removals survive a resync (the exclusion row) but not a re-seed. Persisting edits means writing them back to the assets, or teaching the seed to skip rows an admin has edited. Fixed: a new admin_edited column (migration 0047_video_admin_edited.sql, default false) is set by updateLibraryVideo (lib/videos/admin-library.ts) whenever an admin edit is applied and never cleared. buildVideoSeedStatements guards every seeded column of the ON CONFLICT upsert with CASE WHEN "admin_edited" = 1 THEN "<col>" ELSE excluded."<col>" END, so a re-seed leaves an admin-edited row's columns exactly as the admin left them instead of overwriting them with the JSON asset's value — matching the precedent deleteLibraryVideo's youtube_video_exclusions row already set for removals. updated_at stays unconditional so the row still reads as fresh and the trailing prune doesn't mistake it for one the assets dropped.
• A round published and then corrected in the admin library could be silently reverted by a later "Publish all"/per-row "Publish": the weekly and manual YouTube resyncs re-walk every subscribed channel's uploads since a fixed floor date with no check against the already-published videos table, so an admin-edited round could resurface in youtube_round_videos and reach publishRoundVideos again computed fresh from the unedited YouTube listing, overwriting the correction. Fixed: publishRoundVideos (lib/videos/publish-round-video.ts) re-reads which of the batch's ids already carry admin_edited immediately before writing and skips those rows entirely, matching the guard admin_edited already gives db:seed:videos above. lib/videos/__tests__/publish-round-video.test.ts covers first-time publish, a plain (non-admin-edited) republish, a resync that resurfaces an admin-edited round leaving its correction untouched, and a mixed batch where an untouched sibling round still publishes normally — against a real in-memory SQLite database.
• Rounds are now stored twice: this table projects the committed JSON assets for the public feed, while youtube_round_videos is the admin resync's landing table, filled straight from the YouTube API for the admin page (/api/admin/youtube/videos). Both are legitimate — different pipelines, different readers — but a round exists in both, and pointing this feed at the resync table (or seeding this one from it) would collapse the duplication.