richer episodes: per-episode guest/recurring cast (1160 credits from TVmaze) on detail pages + per-season JustWatch watch links; episode_guest_cast table (app-user owned, no gated DDL)

59829db2a52d57e0d395a767b4fc5768df4aa1c9 · 2026-07-26 16:14:10 -0700 · Steve
Files touched

M apps/web/app/episodes/[season]/[episode]/page.tsx
A db/ingest-guestcast.mjs
M db/schema.sql
Diff

commit 59829db2a52d57e0d395a767b4fc5768df4aa1c9
Author: Steve <steve@designerwallcoverings.com>
Date:   Sun Jul 26 16:14:10 2026 -0700

    richer episodes: per-episode guest/recurring cast (1160 credits from TVmaze) on detail pages + per-season JustWatch watch links; episode_guest_cast table (app-user owned, no gated DDL)
---
 apps/web/app/episodes/[season]/[episode]/page.tsx | 46 ++++++++++++++++--
 db/ingest-guestcast.mjs                           | 59 +++++++++++++++++++++++
 db/schema.sql                                     | 10 ++++
 3 files changed, 112 insertions(+), 3 deletions(-)

diff --git a/apps/web/app/episodes/[season]/[episode]/page.tsx b/apps/web/app/episodes/[season]/[episode]/page.tsx
index 246adf8..7d7ee56 100644
--- a/apps/web/app/episodes/[season]/[episode]/page.tsx
+++ b/apps/web/app/episodes/[season]/[episode]/page.tsx
@@ -10,7 +10,7 @@ type Params = { season: string; episode: string };
 async function getEpisode(season: number, episode: number) {
   try {
     const { rows } = await pool.query(
-      `select season_number, episode_number, title, air_date, writer, director,
+      `select id, season_number, episode_number, title, air_date, writer, director,
               summary_short_original, summary_full_original, recap_status
          from episodes where season_number=$1 and episode_number=$2 limit 1`,
       [season, episode]
@@ -41,6 +41,19 @@ async function getAdjacent(season: number, episode: number) {
   }
 }
 
+async function getGuestCast(episodeId: string) {
+  try {
+    const { rows } = await pool.query(
+      `select person_name, character_name from episode_guest_cast
+        where episode_id=$1 order by ord`,
+      [episodeId]
+    );
+    return rows;
+  } catch {
+    return [];
+  }
+}
+
 async function getSeasonEpisodes(season: number, exclude: number) {
   try {
     const { rows } = await pool.query(
@@ -85,6 +98,7 @@ export default async function EpisodePage({
   if (!ep) notFound();
   const { prev, next } = await getAdjacent(ep.season_number, ep.episode_number);
   const seasonEps = await getSeasonEpisodes(ep.season_number, ep.episode_number);
+  const guestCast = await getGuestCast(ep.id);
 
   const jsonLd = {
     "@context": "https://schema.org",
@@ -121,11 +135,11 @@ export default async function EpisodePage({
       </p>
       <p style={{ fontSize: 12.5, marginTop: -4, display: "flex", gap: 14, flexWrap: "wrap" }}>
         <a
-          href="https://www.justwatch.com/us/tv-show/90210"
+          href={`https://www.justwatch.com/us/tv-show/90210/season-${ep.season_number}`}
           target="_blank"
           rel="noopener noreferrer"
         >
-          Where to watch ↗
+          Where to watch Season {ep.season_number} ↗
         </a>
         <a
           href={`https://www.reddit.com/r/90210/search/?q=${encodeURIComponent(ep.title)}&restrict_sr=1`}
@@ -149,6 +163,32 @@ export default async function EpisodePage({
         <p style={{ color: "var(--muted)" }}>An original recap for this episode is coming soon.</p>
       )}
 
+      {guestCast.length > 0 && (
+        <div style={{ marginTop: 22 }}>
+          <h2 style={{ fontSize: 17 }}>Cast in this episode</h2>
+          <p style={{ color: "var(--muted)", fontSize: 12, marginTop: -4 }}>
+            Guest &amp; recurring cast ({guestCast.length})
+          </p>
+          <div
+            style={{
+              display: "grid",
+              gap: 6,
+              gridTemplateColumns: "repeat(auto-fill, minmax(220px, 1fr))",
+              fontSize: 13,
+            }}
+          >
+            {guestCast.map((g, i) => (
+              <div key={i}>
+                <strong>{g.person_name}</strong>
+                {g.character_name ? (
+                  <span style={{ color: "var(--muted)" }}> as {g.character_name}</span>
+                ) : null}
+              </div>
+            ))}
+          </div>
+        </div>
+      )}
+
       <nav
         style={{
           display: "flex",
diff --git a/db/ingest-guestcast.mjs b/db/ingest-guestcast.mjs
new file mode 100644
index 0000000..b6eb71b
--- /dev/null
+++ b/db/ingest-guestcast.mjs
@@ -0,0 +1,59 @@
+// Per-episode cast detail: pulls the guest/recurring cast for every episode from
+// TVmaze (factual — actor + character names) into episode_guest_cast. The app user
+// owns its DB, so it can CREATE this table itself (no superuser DDL needed).
+// Throttled to respect TVmaze's rate limit. Run: node db/ingest-guestcast.mjs
+import pg from "pg";
+const DATABASE_URL =
+  process.env.DATABASE_URL ?? "postgresql://localhost/nineoh_guide?host=/tmp";
+const pool = new pg.Pool({ connectionString: DATABASE_URL });
+const sleep = (ms) => new Promise((r) => setTimeout(r, ms));
+
+await pool.query(`
+  create table if not exists episode_guest_cast (
+    id serial primary key,
+    episode_id uuid not null references episodes(id) on delete cascade,
+    person_name text not null,
+    character_name text,
+    ord int default 0
+  )`);
+await pool.query(
+  `create index if not exists idx_egc_ep on episode_guest_cast(episode_id)`
+);
+
+const eps = (
+  await pool.query(
+    `select id, season_number, episode_number, external_ids->>'tvmaze' as tvmaze
+       from episodes where external_ids ? 'tvmaze' order by season_number, episode_number`
+  )
+).rows;
+
+let done = 0,
+  rows = 0;
+for (const ep of eps) {
+  await sleep(380); // TVmaze rate limit (>=20 req / 10s)
+  try {
+    const r = await fetch(
+      `https://api.tvmaze.com/episodes/${ep.tvmaze}?embed=guestcast`,
+      { headers: { "User-Agent": "nineoh-guide/0.1 (fan project)" } }
+    );
+    const j = await r.json();
+    const guests = j._embedded?.guestcast ?? [];
+    await pool.query(`delete from episode_guest_cast where episode_id=$1`, [ep.id]);
+    let i = 0;
+    for (const g of guests) {
+      const name = g.person?.name;
+      if (!name) continue;
+      await pool.query(
+        `insert into episode_guest_cast (episode_id, person_name, character_name, ord) values ($1,$2,$3,$4)`,
+        [ep.id, name, g.character?.name ?? null, i++]
+      );
+      rows++;
+    }
+    done++;
+    if (done % 20 === 0) console.log(`  ${done}/${eps.length} episodes, ${rows} guest credits`);
+  } catch (e) {
+    console.warn(`S${ep.season_number}E${ep.episode_number} failed: ${e.message}`);
+  }
+}
+console.log(`done: ${done}/${eps.length} episodes, ${rows} guest credits total`);
+await pool.end();
diff --git a/db/schema.sql b/db/schema.sql
index c9e8204..14916e3 100644
--- a/db/schema.sql
+++ b/db/schema.sql
@@ -80,6 +80,16 @@ create table if not exists credits (
   billing_order     int
 );
 
+-- ── Per-episode guest / recurring cast (factual, from TVmaze) ──────────────
+create table if not exists episode_guest_cast (
+  id                serial primary key,
+  episode_id        uuid not null references episodes(id) on delete cascade,
+  person_name       text not null,
+  character_name    text,
+  ord               int default 0
+);
+create index if not exists idx_egc_ep on episode_guest_cast(episode_id);
+
 -- ── News snippets (short, attributed, linked out) ──────────────────────────
 create table if not exists news_items (
   id                uuid primary key default uuid_generate_v4(),
← e200329 comprehensive pass: /show season overviews + 14 character pr · back to Nineoh Guide · loop refine 1/n: sort (newest/oldest/source) + persisted den 3c1fd3a →