External-to-Internal Hosting Migration

show.fm supports two hosting modes per podcast: external (the podcast keeps its RSS feed at the original host; PP renders nothing at its rss.cdn.media/{podcast_id} feed URL) and podcasterplus (PP is the canonical host). Epic 11 closes the gap between them: a host who built up an episode catalogue on an external host can now migrate without breaking subscriber apps' GUID cache or losing PP-side metadata.

This guide covers the end-to-end flow: identity tracking on individual episodes, the wizard, the commit endpoint, the worker merge mode, and the cutover.

Why GUID matters

Podcast apps deduplicate episodes by RSS <guid>. If a migration silently inserts new episode rows with new GUIDs, every subscriber's app re-downloads the full catalogue and the "new episodes" badge fires on every old episode. Subscriber continuity requires:

• The PP-rendered feed continues to use the same GUIDs subscribers' apps already cached.
• The same <enclosure> URL convention so download caches still resolve.

The solution is per-episode identity tracking: each PP episode in an external podcast carries the RSS <guid> it represents, plus the original enclosure URL and pubDate. When the host upgrades to PP hosting, the importer matches RSS items against PP episodes by GUID and merges audio into the existing rows instead of creating duplicates.

Data model

episodes (extended in 20260428192931_…)

Column	Type	Purpose
external_guid	text	RSS <guid> from the external feed. Canonical identity for the merge.
external_enclosure_url	text	RSS <enclosure url=...> - source URL the importer streams from.
external_rss_pub_date	timestamptz	RSS <pubDate>. May differ from published_at (user-entered handoff time).
external_link_status	text CHECK IN ('pending','linked','unmatched')	State machine. NULL for internally-hosted episodes.
external_link_method	text CHECK IN ('auto','manual')	How the link was resolved. NULL until linked.
external_link_attempts	integer NOT NULL DEFAULT 0	Counter incremented on every consumer run.
external_link_last_attempt_at	timestamptz	Diagnostic only - most recent worker attempt.
external_link_next_attempt_at	timestamptz	Watchdog primary predicate. Set by the producer on enqueue; bumped by the consumer on each retry; cleared on linked / unmatched.

Indexes:

• idx_episodes_podcast_external_guid - UNIQUE (podcast_id, external_guid) WHERE external_guid IS NOT NULL. Prevents two PP episodes pointing at the same RSS item.
• idx_episodes_external_link_pending - partial index on external_link_next_attempt_at WHERE external_link_status = 'pending'. Keeps the watchdog sweep cheap.

podcast_imports (extended in 20260428202207_…)

Column	Type	Purpose
is_hosting_migration	boolean NOT NULL DEFAULT false	Flips the executor into merge mode.
migration_config	jsonb	{ use_rss_titles, use_rss_descriptions, skipped_episode_ids }.

Index - idx_podcast_imports_hosting_migration_active - UNIQUE (podcast_id) WHERE is_hosting_migration = TRUE AND status IN ('preview','pending','running','verification_required'). Race-safe single-active-migration lock. The preview status was added in 20260507230120_extend_hosting_migration_active_index_to_preview.sql so the lock holds across the commit endpoint's dispatch-staging window.

CHECK constraint - chk_migration_config_shape - minimal sanity check that migration_config has the three expected keys when non-null. The Zod schema in /commit is the authoritative validator; this is the worker's last-ditch crash guard.

RPCs and triggers (20260429073542_…, 20260429073600_…, 20260508093849_…)

• enforce_admin_only_episode_link_writes BEFORE UPDATE trigger on episodes. Bypasses on auth.role() = 'service_role' OR auth.role() IS NULL (PostgREST service-role flows + direct-Postgres worker writes); otherwise calls has_podcast_role(NEW.podcast_id, 'admin') and rejects with 42501. The NULL-role admittance is critical for the link worker and lifecycle-manager watchdog, which write through Hyperdrive (not PostgREST), with no JWT claim set.
• commit_external_link_pairings(p_podcast_id, p_pairings) SECURITY DEFINER plpgsql function that applies the full pairing batch as one transaction. Bypass uses auth.role() IS DISTINCT FROM 'service_role' (the <> operator would short-circuit on NULL); EXECUTE is REVOKEd from PUBLIC/anon and explicitly GRANTed to authenticated and service_role. No worker callsite exists.

End-to-end flow

Phase 1 - Per-episode link tracking

Every external-hosted episode published through PP gets staged for auto-linking at publish time. The SvelteKit publish-handoff action (src/routes/(app)/p/[slug]/e/[episodeSlug]/publish-handoff/+page.server.ts) sets external_link_status='pending', external_link_next_attempt_at=NOW() on the episode, and best-effort enqueues an attempt_link message via enqueueExternalEpisodeLink (src/lib/external-link/enqueue.ts).

The external-episode-link consumer in podcast-import-executor (see worker doc) resolves the link:

1. Fetches the podcast's external_rss_url.
2. Filters items to a ±24h window around target_published_at and excludes GUIDs already linked to another episode in the same podcast.
3. If exactly one candidate, links.
4. If >1, falls back to Sørensen–Dice title similarity (min_score = 0.8, min_gap = 0.15).
5. Otherwise retries on 5m → 15m → 1h → 6h → 24h. After the schedule exhausts, the row is marked unmatched.

Idempotency comes from the consumer's UPDATE … WHERE external_link_status = 'pending' filter - late-arriving original messages or watchdog re-dispatches cannot double-write.

The stalled-link watchdog in lifecycle-manager re-enqueues any pending row whose external_link_next_attempt_at is past due, recovering from rare lost queue messages within ~90 s.

Phase 2 - Manual repair UI

src/lib/components/external-link/external-feed-link-panel.svelte renders on the episode detail page for any external podcast. The host can:

• See the live link state (pending / linked / unmatched) with attempt count and next-retry timer.
• Pick from a list of unmatched feed items (GET /api/podcasts/:id/external-feed/unmatched-items) to manually link (POST /api/episodes/:id/external-link/link).
• Re-attempt a pending or unmatched row (POST /api/episodes/:id/external-link/reattempt).
• Unlink an episode (DELETE /api/episodes/:id/external-link).

All mutations go through the External Link API which enforces admin/owner role via requirePodcastRoleByResolver.

Phase 3 - The upgrade wizard

src/routes/(app)/p/[slug]/upgrade-hosting/+page.svelte is the owner-only flow. The page-server load computes a pre-flight summary:

• merge_count - feed items whose <guid> matches an existing PP episode's external_guid (will merge audio into that episode).
• create_count - feed items with no match (will create new episodes).
• reconciliation_count - PP episodes whose link status is pending / unmatched (the wizard offers manual pairing).
• merged_preview / new_episodes_preview - capped at 100 entries each; description fields trimmed to 500 chars for the SSR payload. The wizard's GET /upgrade-hosting/preview/:episode_id endpoint lazily loads full text for individual rows.
• drifted_episodes - episodes claiming linked whose captured GUID is no longer in the live feed. The wizard hard-blocks Continue until the drift clears.

Page-server reads paginate through the PostgREST api.max_rows = 1000 cap (same strategy as src/lib/server/import-items.ts) so a podcast with >1000 episodes does not silently truncate the merge/create counts.

The wizard's three-step UI: pre-flight summary → reconciliation (pair pending episodes against unmatched feed items, or skip) → review (field-merge toggles, expandable per-row diff) → Commit.

Phase 4 - Commit

POST /api/podcasts/:id/upgrade-hosting/commit is documented in detail in the External Link API reference. Key invariants:

• status='preview' is a staging window. Workers ack-skip preview parents (processItem.ts:83-99). The commit inserts as preview, dispatches every queue message with a 5s+ stagger, then flips to running with started_at=NOW(). The unique partial index includes preview so the active-status lock holds across this window.
• Pairings persist via RPC. commit_external_link_pairings applies the full batch in one transaction - a mid-batch failure rolls every change back so the user can retry cleanly.
• Drift guard refuses the commit (409 FEED_DRIFT) if any linked GUID is missing from the live feed. The race window between page load and commit is small but non-zero, which is the whole reason this guard exists.
• Empty-feed fast path handles podcasts with zero items inline: insert as preview, flip hosting_type, transition preview → completed, send invalidation. Without this the parent would stay running forever (no items, no worker call to finaliseIfLastItem).
• Activation is a row-count-checked UPDATE. If the stuck-preview watchdog canceled the row mid-dispatch, the activation update affects zero rows and the API returns a clean 409 instead of misleading the user with a 201.

Phase 5 - Worker merge mode

The podcast-import-executor consumer reads podcast_imports.is_hosting_migration for each message. When true, it first probes episodes by (podcast_id, external_guid = payload.guid):

• GUID match → processMergeMode. Streams audio into podcasts/{podcastId}/e/{existingEpisodeId}/audio/migrated.{ext}, fills cover-if-missing into cover-migrated.{ext}, and calls mergeAudioIntoExistingEpisode to update the row. The function:
  • Unconditionally rewrites: audio_url, audio_content_type, audio_file_size_bytes, audio_duration_seconds, episode_guid (overwritten with the RSS guid for subscriber-continuity), external_rss_pub_date, published_at, is_explicit.
  • Conditionally rewrites title / description per migration_config.use_rss_titles / use_rss_descriptions.
  • Fills if missing: cover_image_url, episode_number, season_number.
  • Never touches: show_notes, show_note_sections.
• No GUID match → standard create path, but the new episode is inserted with external_guid = payload.guid so the migration's bookkeeping is complete.

Worker writes use direct Postgres through Hyperdrive - auth.role() is NULL, which the enforce_admin_only_episode_link_writes trigger bypasses.

Phase 6 - Cutover

sendSideEffects (in the worker's processItem.ts) is called from finaliseIfLastItem when the parent import lands a terminal state. For hosting migrations:

• Only on outcome === 'completed' - a partial migration would publish an incomplete feed to subscribers. The user retries failed items via the existing POST /api/imports/:id/retry-failed; once those land, the parent re-enters completed and this block fires again.
• flipHostingTypeToInternal(podcastId) flips podcasts.hosting_type from external to podcasterplus (guarded by WHERE hosting_type = 'external' for idempotency on retry).
• On success: emits rss-invalidation (type: 'import.completed') and the completion email.
• On cutover failure: flips the import to failed with Hosting cutover failed: … as last_error, skips RSS invalidation and email (RSS is moot - hosting is still external - and a "complete" email would be misleading). The wizard surfaces the failure; the user re-runs migration to retry the cutover. Merge mode is idempotent (R2 key keyed on episode_id, UPDATE filter on hosting_type='external').

Once hosting_type is podcasterplus, the show's rss.cdn.media/{podcast_id} feed becomes authoritative and renders the merged catalogue. The rss-invalidation message clears the KV cache so subscriber apps refresh on the next poll.

Watchdogs (self-healing)

Three independent sweeps run every minute in lifecycle-manager:

Sweep	What it rescues	Key signal
sweepStalledImports	import_items rows whose podcast-imports queue message never reached the consumer.	attempts=0 past pi.dispatch_end_at; attempts>0 past per-attempt threshold.
sweepStalledExternalLinks	episodes whose external-episode-link message was lost (initial or retry).	external_link_status='pending' AND external_link_next_attempt_at < NOW() - 90s grace.
sweepStuckHostingMigrationPreviews	podcast_imports rows wedged in preview after a dead commit request.	is_hosting_migration=true AND status='preview' AND started_at IS NULL AND created_at < NOW() - 15min.

See the Lifecycle Manager worker doc for the per-sweep constants and logs.

Failure handling

Failure	User-visible outcome	Recovery
Feed fetch fails at link time	Episode stays pending; backoff retries 5m → 24h.	Auto-recovers when host serves the feed. Manual reattempt available.
0 / >1 candidates after schedule	Episode → unmatched.	Manual link via the episode detail panel.
Wizard pre-flight detects drift	Wizard hard-blocks Continue with the drifted list.	User waits for host to finish republishing, refreshes wizard.
Commit endpoint dies mid-dispatch	Row stuck in preview.	Stuck-preview watchdog cancels after 15min; user retries.
Activation matches zero rows	API returns 409, asks user to retry.	User retries; the cancellation already released the active-status lock.
Per-item merge fails (transient)	Cloudflare Queues retries with backoff (max 5).	Auto-recovers within the backoff schedule.
Per-item merge fails (terminal)	Item → failed with failure_reason. Parent lands partial.	User retries via POST /api/imports/:id/retry-failed; cutover fires on next completed finalisation.
Cutover throws after all items succeed	Import → failed with Hosting cutover failed: …. No invalidation, no email.	User re-runs the migration wizard; merge mode is idempotent.

Build constraints

• No deferrals. Epic 11 is pre-release: no v2 phases, no feature flags. Every gap a user can hit must be handled inline.
• Subscriber continuity is the non-negotiable. The <guid> is the contract with the subscriber app's cache; never silently change it. The drift guard, the unique partial index on (podcast_id, external_guid), and the GUID-then-create flow in the worker all exist to protect this invariant.
• Idempotency at every layer. R2 keys keyed on stable episode IDs; SQL UPDATEs filtered on prior status so retries are no-ops; queue consumers atomic on status='pending'. The cutover UPDATE itself is filtered on hosting_type='external'.

Related

• External Link API - per-episode and migration endpoints.
• Imports API - back-catalogue counterpart (same queue, different is_hosting_migration flag).
• Podcast Import Executor worker - both queue consumers; merge-mode pipeline.
• Lifecycle Manager worker - three fast-lane watchdogs.
• RSS Feed Worker - KV invalidation on cutover.
• Source: src/api/routes/external-link/, src/lib/external-link/, src/routes/(app)/p/[slug]/upgrade-hosting/, workers/podcast-import-executor/src/external-link/.
• Migrations: 20260428192931_…, 20260428202207_…, 20260429073542_…, 20260429073600_…, 20260507230120_…, 20260508093849_….