External Link API

Manages the relationship between show.fm episodes and items in an externally-hosted podcast's RSS feed. The same set of three route modules powers two flows:

1. Per-episode link repair - manual link, reattempt, status polling, and unlink for episodes whose external_link_status is pending or unmatched.
2. Hosting migration (Epic 11) - commit and status endpoints for the /p/[slug]/upgrade-hosting wizard, plus a lazy-loaded full-text preview endpoint.

Base paths: /api/episodes/:id/external-link/*, /api/podcasts/:id/external-feed/*, /api/podcasts/:id/upgrade-hosting/*

Authentication: All endpoints require a Bearer token. Admin or owner role on the target podcast is required for every mutation; the /upgrade-hosting/commit endpoint additionally requires owner.

Source:

• src/api/routes/external-link/episode.ts - per-episode endpoints (mounted on /api/episodes).
• src/api/routes/external-link/podcast.ts - unmatched-feed-item listing (mounted on /api/podcasts).
• src/api/routes/external-link/migration.ts - hosting-migration commit/status/preview (mounted on /api/podcasts).

Endpoint summary

Method	Path	Role	Description
POST	/api/episodes/:id/external-link/link	admin	Manually link an episode to an RSS GUID. Server re-fetches the feed and captures enclosure + pubDate atomically.
POST	/api/episodes/:id/external-link/reattempt	admin	Reset link state to pending and re-enqueue the auto-linker.
GET	/api/episodes/:id/external-link/status	admin	Poll link state after a reattempt; light JSON, no page reload required.
DELETE	/api/episodes/:id/external-link	admin	Unlink an episode (clear captured fields + retry counters).
GET	/api/podcasts/:id/external-feed/unmatched-items	admin	List RSS items in the external feed not yet linked to any PP episode.
POST	/api/podcasts/:id/upgrade-hosting/commit	owner	Persist field-merge toggles, write reconciliation pairings via RPC, snapshot the feed into import_items, dispatch jobs, and activate the migration.
GET	/api/podcasts/:id/upgrade-hosting/status	admin	Poll progress + per-episode failures during/after the migration.
GET	/api/podcasts/:id/upgrade-hosting/preview/:episode_id	admin	Lazy-loaded full title/description preview for a single merge row; bypasses the SSR truncation.

All responses follow the project standard:

// success
{ success: true, data: { /* route-specific */ } }
// failure
{ error: 'Human-readable message', code?: 'MACHINE_CODE' }

Per-episode link state machine

episodes.external_link_status is NULL on internally-hosted episodes and one of these on externally-hosted episodes:

Status	Meaning
pending	Auto-linker has not yet resolved (or has been reset). Watchdog re-enqueues when external_link_next_attempt_at is past due.
linked	Resolved. external_guid, external_enclosure_url, external_rss_pub_date, and external_link_method (auto or manual) are populated.
unmatched	Backoff schedule exhausted. User can re-link manually or reattempt.

Authoritative write paths into these columns:

• Workers (podcast-import-executor external-link consumer, lifecycle-manager watchdog) - direct Postgres through Hyperdrive. auth.role() is NULL, which the enforce_admin_only_episode_link_writes trigger bypasses.
• Hono API mutations (POST /link, POST /reattempt, DELETE, hosting-migration commit RPC) - Supabase service-role key from the API. auth.role() is service_role, also bypassed by the trigger.
• Direct PostgREST writes (anon / authenticated callers) - gated by the trigger, which calls has_podcast_role(...,'admin') before allowing any change to the link-identity columns. This is defence-in-depth - the application paths above are the only legitimate writers.

Manual link

POST /api/episodes/:id/external-link/link
Body: { "guid": "<RSS GUID, max 2048 chars>" }

Server-side flow:

1. Resolve podcast_id from the episode (404 if missing).
2. Require hosting_type = 'external' and a configured external_rss_url.
3. Reject if the GUID is already linked to a different episode in this podcast (409); the partial unique index idx_episodes_podcast_external_guid enforces the race-safe constraint.
4. Re-fetch the feed (parseRssFeedFull) and locate the item.
5. Atomically write external_guid, external_enclosure_url, external_rss_pub_date, external_link_status='linked', external_link_method='manual', external_link_next_attempt_at=NULL.

Failure modes: 400 (podcast not external, GUID not in feed), 404 (episode/podcast missing), 409 (GUID already linked), 502/504 (feed fetch error/timeout).

Reattempt

POST /api/episodes/:id/external-link/reattempt

Resets all link-state columns to the same shape as DELETE - including external_link_attempts=0 and external_link_last_attempt_at=NULL - so the watchdog's cooldown clause does not skip the row. Sets external_link_next_attempt_at=NOW() and best-effort enqueues an attempt_link message; if the enqueue fails, the per-minute lifecycle-manager watchdog picks the row up within ~90 s.

Refuses to act on internally-hosted podcasts (400) because after migration cutover the columns are stale audit metadata; re-activating the link pipeline would re-enqueue work for an episode whose audio is now PP-hosted.

Requires published_at or scheduled_for on the episode to seed target_published_at (400 if neither is set).

Status polling

GET /api/episodes/:id/external-link/status

Returns the link state without invalidating the SvelteKit page. Used by the per-episode link panel after a reattempt to surface terminal-state messages.

{
	"success": true,
	"data": {
		"external_guid": "...",
		"external_enclosure_url": "...",
		"external_rss_pub_date": "2026-04-22T10:00:00Z",
		"link_status": "linked",
		"link_method": "auto",
		"link_attempts": 1,
		"last_attempt_at": "...",
		"next_attempt_at": null
	}
}

Unlink

DELETE /api/episodes/:id/external-link

Clears all link-identity fields and resets the retry counters (external_link_attempts=0, external_link_last_attempt_at=NULL, external_link_next_attempt_at=NOW()). The reset is critical: leaving a previous external_link_attempts=4 would have the next worker run start on the 24h backoff slot and quickly mark the episode unmatched again. Restarts the cycle from attempt=0 so the UI's "we'll try again" promise is honest. Idempotent - re-clearing already-cleared fields is a no-op.

Unmatched-feed items

GET /api/podcasts/:id/external-feed/unmatched-items

Returns RSS items in the podcast's external feed which are not yet linked to any PP episode. Used by both the per-episode manual-link UI and the migration reconciliation step.

{
	"success": true,
	"data": {
		"items": [
			{ "guid": "...", "title": "...", "pubDate": "...", "enclosureUrl": "...", "link": null }
		]
	}
}

Implementation notes (load-bearing):

• Already-linked GUIDs are excluded so the UI only surfaces pickable items.
• Linked-episode lookup is paginated through the PostgREST api.max_rows = 1000 cap so a podcast with >1000 linked episodes does not silently truncate. Same paging strategy as src/lib/server/import-items.ts and the hosting-migration commit endpoint.
• Items are returned newest-first by pubDate; items without a parseable date sort last.

Hosting migration - commit

POST /api/podcasts/:id/upgrade-hosting/commit
Body: {
  "use_rss_titles": boolean,
  "use_rss_descriptions": boolean,
  "reconciliation_pairings": [{ "episode_id": "uuid", "rss_guid": "..." }],
  "skipped_episode_ids": ["uuid", ...]
}

Owner-only. End-to-end flow:

Why preview is a staging status

podcast_imports.status='preview' is the dispatch-staging window. The commit endpoint inserts the row as preview, dispatches every queue message, then flips to running. Workers ack-skip preview parents (processItem.ts:83-99), so a queue message that fires mid-dispatch is harmless. The unique partial index idx_podcast_imports_hosting_migration_active includes preview so the active-status lock holds across the staging window.

If the API request dies between insert-as-preview and activation, the stuck-preview watchdog in lifecycle-manager cancels the row after 15 minutes. The activation UPDATE filters on status='preview' and inspects the returned row count; if the watchdog already canceled the row, the API returns a clean 409 instead of misleading the user with a 201.

Empty-feed fast path

If the live feed contains zero items, the standard dispatch loop would insert 0 import_items, enqueue 0 worker jobs, and leave the parent stuck in running forever (no worker would ever call finaliseIfLastItem). The endpoint handles this inline:

1. Insert the row as status='preview' so the active-status lock still applies (a concurrent double-submit still trips the unique index).
2. Flip podcasts.hosting_type from external to podcasterplus (mirrors flipHostingTypeToInternal in the worker; guarded by eq('hosting_type', 'external') for idempotency).
3. Transition the row from preview to completed (filtered on status='preview' so a retry is idempotent).
4. Send rss-invalidation (type: 'hosting_migration.completed') - best-effort.

If the user supplied any pairings/skips against the empty feed, the endpoint returns 400 ("refresh the wizard and retry") rather than silently dropping them.

Drift guard

Between the pre-flight load and the commit, the host's feed can change. The endpoint refuses to commit if any PP episode currently external_link_status='linked' has a captured external_guid that is no longer in the live feed:

{
	"error": "Your RSS feed is out of sync with your linked episodes. Refresh the wizard once your host has finished updating, then try again.",
	"code": "FEED_DRIFT",
	"drifted_episodes": [{ "id": "...", "title": "...", "external_guid": "..." }]
}

The linked-episode read is paginated through the 1000-row cap; without paging, a podcast with >1000 linked episodes could have drifted rows on later pages that the guard would never see, silently letting the migration proceed with stale data.

Failure modes

Status	Condition
400	Podcast not external; conflicting pairings/skips; empty feed with non-empty pairings; pairing GUID not in feed.
400	One or more episode IDs do not belong to this podcast.
404	Podcast missing.
409	Active migration already exists (precheck or 23505 unique-violation on insert).
409	FEED_DRIFT - linked episode's GUID missing from live feed.
409	Activation matched zero rows - watchdog or concurrent cancel beat us; user retries cleanly.
500	RPC failure / drift-guard read failure / dispatch failure (parent rolled back to canceled).
502/504	Feed fetch error / timeout.

Hosting migration - status

GET /api/podcasts/:id/upgrade-hosting/status

Used by the wizard to poll progress. Returns data: null when no migration exists; on a read error, returns 5xx rather than null so a transient blip does not mislead the wizard into resetting back to the pre-flight view.

{
  "success": true,
  "data": {
    "migration_id": "uuid",
    "status": "running" | "completed" | "partial" | "failed" | "canceled" | "preview" | "pending",
    "progress": {
      "total": 23,
      "completed": 18,
      "failed": 1,
      "skipped": 0
    },
    "errors": [
      { "episode_id": "uuid"|null, "rss_guid": "...", "message": "..." }
    ],
    "started_at": "...",
    "finished_at": "...",
    "last_error": "..."
  }
}

Hosting migration - full preview

GET /api/podcasts/:id/upgrade-hosting/preview/:episode_id?rss_guid=<...>

Returns the untrimmed title and description for a single merge row, so the review step can render the full strings on demand. The pre-flight load truncates feed-item and episode descriptions to 500 chars to keep the SSR payload bounded for large back catalogues; this endpoint is the lazy-loaded escape hatch.

• Admin/owner only (same gate as commit/status).
• Tenant boundary: the role check gates the podcast, but the episode_id is still verified to belong to that podcast (403 otherwise).
• Re-fetches the feed each call - at most a handful of expansions per session, and the host's feed is authoritative.
• Returns 404 if the supplied rss_guid is no longer in the live feed (the same drift window the commit-time guard covers). The client surfaces a "feed changed - refresh" affordance.

{
	"success": true,
	"data": {
		"current_title": "...",
		"current_description": "...",
		"rss_title": "...",
		"rss_description": "..."
	}
}

Authorization model

The Hono routes enforce the role gate. Two database backstops live under the API:

• enforce_admin_only_episode_link_writes BEFORE UPDATE trigger (supabase/migrations/20260429073542_…, hardened in 20260508093849_harden_external_link_authorization.sql). Fires per-row when any of the external_link_* columns change. Bypasses on auth.role() = 'service_role' OR auth.role() IS NULL (Hono service-role flows and direct-Postgres worker writes); rejects anon and non-admin authenticated callers.
• commit_external_link_pairings(p_podcast_id, p_pairings) RPC (supabase/migrations/20260429073600_…, hardened in 20260508093849_…). Applies the full pairing batch in a single plpgsql transaction so a mid-batch failure rolls the whole set back. Bypass condition uses auth.role() IS DISTINCT FROM 'service_role' so a NULL-role caller still hits the has_podcast_role check (the <> operator would short-circuit on NULL). EXECUTE is REVOKEd from PUBLIC/anon and explicitly GRANTed to authenticated and service_role.

The IS DISTINCT FROM vs = asymmetry between the two functions is deliberate - the trigger must admit NULL-role direct-Postgres callers (workers) while the RPC has no legitimate worker callsite.

Queue messages produced

Queue	Message type	Producer endpoint(s)
external-episode-link	attempt_link	POST /:id/external-link/reattempt
podcast-imports	import_item	POST /:id/upgrade-hosting/commit
rss-invalidation	hosting_migration.completed	POST /:id/upgrade-hosting/commit (empty-feed fast path)
rss-invalidation	import.completed	Worker-side after a successful merge cutover (see Podcast Import Executor)

Environment bindings

Binding	Type	Purpose
PODCAST_IMPORTS_QUEUE	Queue<PodcastImportMessage>	Hosting-migration commit dispatches per-item jobs.
EXTERNAL_EPISODE_LINK_QUEUE	Queue<ExternalEpisodeLinkMessage>	reattempt enqueues attempt_link messages.
RSS_INVALIDATION_QUEUE	Queue<RssInvalidationMessage>	Empty-feed fast path sends hosting_migration.completed.

TypeScript client usage

import { createApiClient } from '$api/client';
import { createClient } from '$lib/supabase/client';

const supabase = createClient();
const {
	data: { session }
} = await supabase.auth.getSession();
const token = session?.access_token!;
const client = createApiClient(fetch);

// Manual link
await client.api.episodes[':id']['external-link'].link.$post(
	{ param: { id: episodeId }, json: { guid: 'episode-guid' } },
	{ headers: { Authorization: `Bearer ${token}` } }
);

// Reattempt
await client.api.episodes[':id']['external-link'].reattempt.$post(
	{ param: { id: episodeId } },
	{ headers: { Authorization: `Bearer ${token}` } }
);

// Migration commit
await client.api.podcasts[':id']['upgrade-hosting'].commit.$post(
	{
		param: { id: podcastId },
		json: {
			use_rss_titles: true,
			use_rss_descriptions: false,
			reconciliation_pairings: [],
			skipped_episode_ids: []
		}
	},
	{ headers: { Authorization: `Bearer ${token}` } }
);

// Status poll
await client.api.podcasts[':id']['upgrade-hosting'].status.$get(
	{ param: { id: podcastId } },
	{ headers: { Authorization: `Bearer ${token}` } }
);

Related

• External-to-Internal Hosting Migration Guide - End-to-end Epic 11 flow.
• Imports API - The other producer of podcast-imports messages (back-catalogue imports).
• Podcast Import Executor worker - Queue consumer for both podcast-imports and external-episode-link.
• Lifecycle Manager worker - Stalled-link and stuck-preview watchdogs.
• Source: src/api/routes/external-link/episode.ts, podcast.ts, migration.ts.
• UI entry: src/routes/(app)/p/[slug]/upgrade-hosting/+page.svelte, src/lib/components/external-link/external-feed-link-panel.svelte.