Analytics API

The analytics API is the read layer over the daily aggregates written by the Analytics Rollup Worker, plus the write surfaces for engagement CSV imports and tokenized client report links.

Source: src/api/routes/analytics/index.ts

Base Path: /api/analytics

Every endpoint is owner-only (#293 Ruling 1): live analytics are visible to the podcast owner alone. A Producer gets the same 403 a Co-host does, and both render the sample-data AnalyticsPreviewPanel instead. Reads go through the user's Supabase client; the RLS on the aggregate tables is likewise owner-scoped. The retention clamp needs effective_cap (REVOKE'd from authenticated), so it alone runs on the admin client.

Endpoints Overview

Method	Path	Minimum Role	Feature Gate	Purpose
GET	/overview	owner	--	Show-level daily series + totals + deltas
GET	/breakdown	owner	depth: analytics_advanced	Apps/countries/devices for a range
GET	/episodes	owner	--	Ranked episodes with range + launch stats
GET	/episode/:episodeId	owner	--	Per-episode series + decay curve
GET	/export	owner	analytics_export	CSV of daily rows in range
GET	/content	owner	analytics_advanced	Cadence, durations, episode-type mix
GET	/collaboration	owner	analytics_advanced	Collaborators, booking funnel, portal
GET	/episode/:episodeId/talk-time	owner	analytics_advanced	Per-speaker talk time from the transcript
GET	/imports	owner	--	List imported engagement metrics
POST	/imports	owner	analytics_engagement_imports	Import Apple/Spotify engagement CSV
DELETE	/imports	owner	analytics_engagement_imports	Remove a source's imported metrics
GET	/reports	owner	--	List client report links
POST	/reports	owner	analytics_client_reports	Create tokenized report link
POST	/reports/:reportId/revoke	owner	--	Revoke a report link

All endpoints require Authorization: Bearer <token>. Feature gates use requireFeature() and return the standard 403 feature_not_granted envelope when the account lacks the key — except retention, which never 403s (see below).

Retention Clamp (Read-Time, Every Range Route)

Retention is tiered at read time, never at collection. Every range-taking route resolves its range through resolveRange():

1. to defaults to today (UTC); from comes from an explicit date or the preset (7d/30d/90d/12m/all, default 30d).
2. clampAnalyticsRange() (src/lib/entitlements/analytics-retention.ts) floors from at today − effective_cap(account, 'analytics_retention_days'). A null cap = unlimited history. An unresolvable billing account or missing entitlement row fails closed to the free window (90 days); an effective_cap error throws (500) — never falls open.
3. The floor is additionally bounded at ANALYTICS_EPOCH (2026-07-01) — no data exists before collection went live.
4. from > to after clamping → 400 Invalid date range.

Instead of a 403, the response's range object reports what happened so the UI can render the "unlock full history" affordance:

{
	"range": {
		"from": "2026-04-06",
		"to": "2026-07-05",
		"clamped": true,
		"retentionDays": 90
	}
}

• from is the clamped-to effective start date.
• clamped is true when the requested start was floored by the retention window.
• retentionDays is the plan's window in days (null = unlimited).

Download Analytics

Overview

GET /api/analytics/overview?podcast_id=uuid&preset=30d

Middleware: requireAuth() → zValidator('query', rangeSchema) → requirePodcastRole('member')

Query Parameters:

Param	Type	Required	Description
podcast_id	UUID	Yes	Podcast to report on
preset	7d | 30d | 90d | 12m | all	No	Range preset (default 30d)
from, to	YYYY-MM-DD	No	Explicit range (overrides preset)

Response:

{
	"success": true,
	"data": {
		"series": [{ "day": "2026-07-01", "downloads": 42, "uniqueClients": 40, "feedRequests": 120 }],
		"totals": { "downloads": 1180, "feedRequests": 3400, "peakUniqueClients": 61 },
		"deltas": { "downloads": 12.5 },
		"range": { "from": "2026-06-06", "to": "2026-07-05", "clamped": false, "retentionDays": 90 },
		"collectionStart": "2026-07-01"
	}
}

The series is zero-filled over the whole range. deltas.downloads is the percent change against the previous equal-length period, or null when there is no comparable window (all-time ranges, or the previous window falls outside retention). peakUniqueClients is the max single-day uniques (uniques don't sum across days).

Breakdown

GET /api/analytics/breakdown?podcast_id=uuid&preset=30d&episode_id=uuid

Middleware: requireAuth() → zValidator('query', breakdownSchema) → requirePodcastRole('member')

Accepts the range parameters plus optional episode_id to scope to one episode. Depth is gated by a non-throwing checkFeature(c, 'analytics_advanced'):

Tier	Depth
Without analytics_advanced	Top-5 apps + countries; devices: null
With analytics_advanced	Top-15 apps, countries, and devices

Response:

{
	"success": true,
	"data": {
		"apps": { "Apple Podcasts": 410, "Overcast": 88, "other": 41 },
		"countries": { "GB": 320, "US": 199 },
		"devices": null,
		"advanced": false,
		"range": { "from": "2026-06-06", "to": "2026-07-05", "clamped": false }
	}
}

Ranked Episodes

GET /api/analytics/episodes?podcast_id=uuid&preset=30d&limit=50&offset=0

Middleware: requireAuth() → zValidator('query', episodesSchema) → requirePodcastRole('member')

Range parameters plus limit (1-100, default 50) and offset (default 0). Episodes are ranked by all-time downloads from analytics_episode_totals, with per-range downloads summed from the daily rows.

Response (per episode):

{
	"episodeId": "uuid",
	"title": "Episode 42",
	"slug": "episode-42",
	"episodeNumber": 42,
	"seasonNumber": 2,
	"publishedAt": "2026-06-20T08:00:00Z",
	"status": "published",
	"downloadsAllTime": 5120,
	"firstWeekDownloads": 1900,
	"downloadsInRange": 310,
	"firstDay": "2026-06-20"
}

Per-Episode Series

GET /api/analytics/episode/:episodeId?preset=90d

Middleware: requireAuth() → zValidator('query', rangeSchema minus podcast_id) → requirePodcastRoleByResolver('member', resolveEpisodePodcastId)

The podcast is resolved from the episode (unknown episode → 404), then the range is clamped against that podcast's account. Returns the zero-filled daily series, all-time totals, and a launch decay curve ([{ dayOffset, cumulative }], up to 91 days from the first download day) — the decay is only computed when the episode's first day is inside the retention floor.

Export CSV

GET /api/analytics/export?podcast_id=uuid&preset=90d

Middleware: requireAuth() → zValidator('query', rangeSchema) → requirePodcastRole('member') → requireFeature('analytics_export')

Returns text/csv with Content-Disposition: attachment; filename="analytics-{from}-to-{to}.csv":

day,episode_title,episode_slug,downloads,unique_clients,raw_requests,bytes_served

Titles/slugs are CSV-escaped. The range is retention-clamped like every other route.

Content & Ops Panels

Content

GET /api/analytics/content?podcast_id=uuid

Middleware: requireAuth() → zValidator('query', { podcast_id }) → requirePodcastRole('member') → requireFeature('analytics_advanced')

Zero new collection — computed from the episodes table (last 200 published). Returns publish cadence stats, typeMix (full/trailer/bonus counts), medianDaysToPublish (recording → publish, one decimal), and the episode list with durations for the trend chart.

Collaboration

GET /api/analytics/collaboration?podcast_id=uuid

Middleware: requireAuth() → zValidator('query', { podcast_id }) → requirePodcastRole('member') → requireFeature('analytics_advanced')

Returns the top-10 collaborators leaderboard (from episode_credits), the booking funnel (created → confirmed → completed → episode published — the bookings⇄episodes embed is FK-hinted episodes!bookings_episode_id_fkey to avoid PGRST201), and guest portal engagement (invited vs accessed).

General bookings are excluded from the funnel (#295 item 1). A booking made through a link with creates_episode = false has no episode and can never reach the published stage, so counting it would inflate requested/confirmed/completed against a ceiling it cannot reach and make the publish rate read as falling. The query filters on the SESSION SNAPSHOT via booking_sessions!inner(creates_episode) + .eq('booking_sessions.creates_episode', true) — !inner is required, or the filter would not restrict the parent rows. It filters on the snapshot rather than episode_id IS NULL because a deleted episode nulls episode_id on an episode-mode booking too, and those bookings SHOULD still be counted.

Talk Time

GET /api/analytics/episode/:episodeId/talk-time

Middleware: requireAuth() → requirePodcastRoleByResolver('member', resolveEpisodePodcastId) → requireFeature('analytics_advanced')

Reads the transcript artifact (edited key preferred) from R2 (MEDIA_BUCKET) and computes per-speaker share from the diarized segments, labelled via episode_transcripts.speakers. No transcript or no artifact → { "available": false, "speakers": [] } (not an error).

Engagement Imports

Apple has no analytics API and Spotify is dashboard/CSV only, so engagement enters via guided CSV import of the Apple Podcasts Connect / Spotify for Creators episode exports.

List Imported Metrics

GET /api/analytics/imports?podcast_id=uuid

Middleware: requireAuth() → zValidator('query', { podcast_id }) → requirePodcastRole('member')

Returns the most recent 2,000 analytics_platform_metrics rows with episode titles/slugs embedded.

Import CSV

POST /api/analytics/imports

Middleware: requireAuth() → zValidator('json', schema) → requirePodcastRole('admin') → requireFeature('analytics_engagement_imports')

Request Body:

Field	Type	Required	Description
podcast_id	UUID	Yes	Target podcast
source	apple | spotify	Yes	Which platform's export
csv	string (max 5,000,000)	Yes	Raw CSV text (plain POST — files are small, no presigned flow)

Parsing is strict per-source column mapping; an unrecognised format returns 400 with { "error": "...", "expected": "..." } naming the expected export. Episodes are matched by normalised title; unmatched titles are reported back (first 50). Re-import replaces: platform exports are cumulative snapshots, so the previous rows for that source are deleted before insert.

Response:

{
	"success": true,
	"data": {
		"imported": 84,
		"matchedEpisodes": 42,
		"unmatched": ["Old Episode Title"],
		"metricsFound": ["plays", "listeners"]
	}
}

Remove a Source's Import

DELETE /api/analytics/imports?podcast_id=uuid&source=apple

Middleware: requireAuth() → zValidator('query', { podcast_id, source }) → requirePodcastRole('admin') → requireFeature('analytics_engagement_imports')

Deletes all imported metrics for the source. Response: { "success": true }

Client Report Links

Shareable read-only report pages for clients (production_house+), rendered by the public (reports)/r/[token] route with server-side token validation (revoked/unknown → 404).

List Report Links

GET /api/analytics/reports?podcast_id=uuid

Middleware: requireAuth() → zValidator('query', { podcast_id }) → requirePodcastRole('member')

Returns id, token, label, date_preset, created_at, revoked_at per link, newest first.

Create Report Link

POST /api/analytics/reports

Middleware: requireAuth() → zValidator('json', schema) → requirePodcastRole('admin') → requireFeature('analytics_client_reports')

Request Body:

Field	Type	Required	Description
podcast_id	UUID	Yes	Podcast the report covers
label	string (max 100)	No	Display label (default "")
date_preset	7d | 30d | 90d | 12m	No	Frozen range preset (default 30d)

The token is 32 bytes of crypto.getRandomValues encoded base64url. Response: 201 with the created report row.

Revoke Report Link

POST /api/analytics/reports/:reportId/revoke

Middleware: requireAuth() → requirePodcastRoleByResolver('admin', resolver) — the podcast is resolved from the report row (unknown report → 404).

Sets revoked_at; the public report page 404s from then on. Response: { "success": true }

Error Responses

All endpoints follow the standard error format:

{ "error": "Description of what failed" }

Status	Meaning
400	Invalid parameters, invalid date range, or unrecognised CSV format
401	Missing or invalid authentication
403	Insufficient role, or feature gate (feature_not_granted envelope) — never retention
404	Episode or report link not found (also cross-tenant probes via resolvers)
500	Query failure or entitlement-cap resolution error (fail closed)

Related Documentation

• Analytics Measurement Methodology -- what counts as a download
• Analytics Rollup Worker -- writes the tables these routes read
• Media Delivery Worker -- raw download ingest