show.fm Docs (internal)

Appearance

Analytics Rollup Worker ​

The Analytics Rollup Worker is the pipeline stage between raw request logging and the analytics UI. Every hour it reads raw datapoints from Workers Analytics Engine (media_requests + feed_requests) via the SQL API, applies the IAB Podcast Measurement v2.2-aligned download filter, and UPSERTs daily aggregates into Postgres over Hyperdrive. WAE is a ~90-day ingest buffer; the Postgres aggregates are the durable store the UI reads.

After each rollup it also evaluates analytics automation triggers (download milestones, download spikes) and emits native milestone notifications.

Overview ​

PropertyValue
Worker Namepodcasterplus-analytics-rollup
Source Location/workers/analytics-rollup/
TriggerCron 5 * * * * (hourly at :05 — WAE datapoints for the hour boundary have settled by then)
ReadsWAE SQL API (media_requests, feed_requests)
Writesanalytics_episode_daily, analytics_podcast_daily, analytics_episode_totals, analytics_rollup_state
DatabaseSupabase PostgreSQL via Hyperdrive (table-owner writes; no RLS write policies exist)
Queue Producerautomation-events (binding AUTOMATION_EVENTS_QUEUE)
HTTPHealth check only ({ status: 'ok', service: 'analytics-rollup' }) — no public routes

Architecture ​

┌──────────────────────────────────────────────────────────────────────────┐
│                       Hourly Rollup (5 * * * *)                          │
├──────────────────────────────────────────────────────────────────────────┤
│                                                                           │
│  WAE SQL API ──(media_requests, grouped by episode × client × range)──┐   │
│  WAE SQL API ──(feed_requests,  grouped by slug × client)──────────┐  │   │
│                                                                    │  │   │
│                          for D ∈ { today, yesterday } UTC          │  │   │
│                                                                    ▼  ▼   │
│  ┌─────────────────┐   ┌──────────────────┐   ┌───────────────────────┐  │
│  │  Episode facts  │──▶│   IAB filter     │──▶│  UPSERT daily rows +  │  │
│  │  (Hyperdrive)   │   │  (iab-filter.ts) │   │  recompute totals     │  │
│  └─────────────────┘   └──────────────────┘   └──────────┬────────────┘  │
│                                                          │               │
│                                    ┌─────────────────────┴────────────┐  │
│                                    ▼                                  ▼  │
│                     ┌───────────────────────────┐   ┌──────────────────┐ │
│                     │ Trigger evaluation        │   │ stamp            │ │
│                     │ (milestones + spikes)     │   │ rollup_state     │ │
│                     │ → automation-events queue │   └──────────────────┘ │
│                     │ → notify_analytics_       │                        │
│                     │   milestone RPC           │                        │
│                     └───────────────────────────┘                        │
│                                                                           │
└──────────────────────────────────────────────────────────────────────────┘

WAE SQL API Reads ​

src/lib/wae.ts POSTs SQL to https://api.cloudflare.com/client/v4/accounts/{CF_ACCOUNT_ID}/analytics_engine/sql with a bearer token holding Account Analytics Read (CF_ANALYTICS_API_TOKEN secret — there is no WAE read binding; reads always go through the HTTP API).

Sampling is not optional: WAE downsamples at volume, so every count and sum must be weighted by _sample_interval or high-traffic days silently undercount:

sql
SUM(double1 * _sample_interval) AS bytes,
SUM(_sample_interval)           AS requests

Queries live in src/lib/wae-queries.ts and address blobs by position — a lockstep contract with the producers:

Media rows are fetched for blob11 IN ('200','206') only, grouped by client and range header — the worker re-aggregates per client while keeping enough range detail to apply the Apple bytes=0-1 probe rule. Day strings are worker-generated ('YYYY-MM-DD'); no user input ever reaches the SQL.

The IAB v2.2-Aligned Filter ​

src/lib/iab-filter.ts is pure functions, exhaustively unit-tested. Rules, in order, applied to WAE rows pre-grouped by (episode, client ipHash, UA, range) for one fixed UTC day (OP3's open implementation — op3.dev/download-calculation — is the reference where the spec is ambiguous):

  1. GET 200/206 only — enforced upstream (the SQL fetches only those statuses; the delivery worker only logs GETs).
  2. Empty/missing UA → drop.
  3. OPAWG bot UA (bots.json, or libraries.json with category bot) → drop.
  4. 60-seconds-of-audio byte threshold — Σ bytes per client-episode-day must reach minDownloadBytes(episode):
    • With file size + duration: ceil(size / duration × 60) — 60 seconds at the file's average bitrate, clamped to the file size (a sub-60s episode requires the full file).
    • Size-only fallback: ceil(size × 0.05) (5% of the file).
    • No facts at all: any Σ bytes > 2 (FALLBACK_MIN_BYTES = 3).
  5. Apple probe exception — a client whose only traffic is bytes=0-1 probes with an Apple UA (AppleCoreMedia / Apple Podcasts / iTunes) counts as one download. This is an OP3-convention divergence, documented: Apple's full fetch often lands on Apple's own cache, so the probe is the only origin-visible signal.
  6. One download per (ipHash, UA) per episode per UTC day — dedupe is structural: one qualifying client = one download.

Divergences from strict IAB v2.2 (no licensed TAG data-center IP list; rule 5; fixed UTC day where the spec permits rolling windows) are documented in the Analytics Measurement Methodology page.

App / Device Classification (vendored OPAWG) ​

src/lib/opawg.ts classifies user agents with vendored OPAWG user-agents-v2 JSON (bots.json, apps.json, devices.json, libraries.json, referrers.json) plus podcast-rss-useragents for feed pollers. Patterns are compiled once at module init and memoized per UA.

Notable first-party decisions:

  • AppleCoreMedia/ is mapped to Apple Podcasts before apps.json — OPAWG deliberately leaves it unmapped (any iOS app's media stack uses it), but on our enclosure URLs it is the Apple Podcasts download path.
  • For feed analytics, known podcast feed pollers win over the generic bot list (Spotify polls as Spotify/1.0, which bots.json flags — for feeds that poll is the signal). Pure crawlers carry a bot. slug prefix and stay bots.

The data is vendored, not fetched at runtime (deterministic rollups, no GitHub dependency). Refresh flow (src/data/opawg/VERSION.md):

bash
cd workers/analytics-rollup
pnpm run update-opawg   # re-downloads all six files, rewrites the VERSION.md stamp

Review the diff before committing — classification changes shift future rollups only; historical aggregates are not rewritten unless a day is manually re-rolled.

Recompute Model: Today + Yesterday, Idempotent ​

Every run recomputes both today's and yesterday's UTC buckets per dataset, from scratch (src/lib/rollup.ts):

  • Upserts are idempotent full-replacements (ON CONFLICT … DO UPDATE), so late datapoints and sampling drift self-heal on the next run.
  • A failed run is fully recovered by the next hour's — WAE holds ~90 days, so hours of rollup downtime lose nothing.
  • Yesterday keeps being recomputed for the first hours of a new UTC day, absorbing stragglers around the midnight boundary.

Tables Written ​

All writes go through src/db.ts using the workers Hyperdrive rule (postgres(conn, { max: 1 }), await sql.end() in finally).

TableGrainContents
analytics_episode_daily(episode, UTC day)downloads, unique_clients, raw_requests (pre-filter honesty metric), bytes_served, top-15+other apps/countries/devices JSONB
analytics_podcast_daily(podcast, UTC day)Show-level downloads + cross-episode distinct clients, breakdown maps, plus feed_requests/feed_unique_clients (feed columns upserted separately — never clobber media columns)
analytics_episode_totalsepisode (all-time)downloads, first_week_downloads, first_day/last_day — recomputed from the daily rows (which are the source of truth); survives WAE's 90-day window; drives milestones + rankings
analytics_rollup_statedatasetwatermark_day, last_run_at, last_status (ok/error), last_error — observability + idempotent re-run bookkeeping

RLS: members can SELECT aggregates for their podcasts (get_podcast_role(podcast_id) IS NOT NULL); there are no client write policies — the rollup writes as table owner over Hyperdrive. analytics_rollup_state has no policies at all (service only).

Episodes deleted between ingest and rollup are skipped (their facts row is gone; the FK would reject them). The episode's current podcast_id from facts is authoritative — podcasts can be re-parented; the datapoint's podcastId is only a fallback label.

Trigger Evaluation (Step 7) ​

After the media upserts, src/lib/triggers.ts runs crossing detection: an event fires only when a value moved across a threshold in this run (before < threshold ≤ after), so re-rolling a day can never re-fire a milestone. The before totals are captured per episode the first time this run touches it; after totals are re-read post-upsert.

Three outputs:

  1. Native milestone notifications — fixed celebration milestones 1k / 10k / 100k / 1M per episode, independent of any automation rule. Each crossing calls the notify_analytics_milestone(episode_id, milestone) SECURITY DEFINER RPC (migration 20260705213503), which notifies the podcast's owners/admins and dedupes absolutely per (recipient, episode, milestone) via a partial unique index — a milestone can only ever be celebrated once.
  2. analytics.episode_downloads_milestone rule events — for enabled rules whose configured threshold was crossed, one event per episode per run is emitted to the automation-events queue; the automation-scheduler fans it out to every milestone rule on the podcast, gates each rule against its own threshold using the event's (before, after] payload, and derives a stable (never date-suffixed) idempotency key.
  3. analytics.podcast_download_spike rule events — evaluated per podcast against today's bucket: fires when today ≥ trailingMean × multiplier (default 3×) and today ≥ min_downloads floor (default 50), where trailingMean is the mean of the prior 7 days. At most once per podcast per UTC day (the scheduler key is day-scoped).

The queue message shape is a lockstep contract with workers/automation-scheduler/src/types/env.ts (AutomationEventMessage with an analytics: { day, downloads, before?, after? } extension).

A trigger failure never fails the rollup, and a missing AUTOMATION_EVENTS_QUEUE binding degrades to "rollup without triggers".

Failure Isolation & State Stamping ​

Failures are isolated per dataset: a media_requests failure doesn't stop the feed_requests rollup (and vice versa), and trigger evaluation is wrapped separately. Each dataset's outcome is stamped into analytics_rollup_state:

  • Success → last_status = 'ok', watermark_day = today.
  • Failure → last_status = 'error' + truncated last_error, console.error (observability logs are enabled with full sampling), and the next hourly run recovers.

A missing CF_ANALYTICS_API_TOKEN skips the run entirely with a logged error.

Project Structure ​

workers/analytics-rollup/
├── wrangler.toml           # Cron, Hyperdrive, queue producer, vars
├── package.json            # postgres dependency + update-opawg script
├── tsconfig.json
└── src/
    ├── index.ts            # scheduled handler: wire deps, run, log summary
    ├── db.ts               # Hyperdrive reads/writes (facts, upserts, totals, state, RPC)
    ├── types/
    │   └── env.ts          # Environment bindings
    ├── data/opawg/         # Vendored OPAWG JSON + VERSION.md stamp
    ├── lib/
    │   ├── wae.ts          # WAE SQL API client
    │   ├── wae-queries.ts  # Day queries + row parsing (blob-position contract)
    │   ├── opawg.ts        # UA classification (bots / apps / devices / feed pollers)
    │   ├── iab-filter.ts   # The six counting rules (pure functions)
    │   ├── rollup.ts       # Orchestration: days, isolation, state stamping
    │   └── triggers.ts     # Milestone/spike crossing detection + event emission
    └── __tests__/          # iab-filter / opawg / rollup / triggers / wae suites

Bindings & Secrets ​

wrangler.toml ​

toml
name = "podcasterplus-analytics-rollup"
compatibility_flags = ["nodejs_compat"]

[triggers]
crons = ["5 * * * *"]

[[hyperdrive]]
binding = "HYPERDRIVE"
id = "a81d477ff9264805989f5a72f0354ee8"

[[queues.producers]]
queue = "automation-events"
binding = "AUTOMATION_EVENTS_QUEUE"

[vars]
CF_ACCOUNT_ID = "b8eff1b484adf398bda38644efc85bce"

[limits]
cpu_ms = 30000

[observability.logs]
enabled = true

CF_ACCOUNT_ID is a var, not a secret — it is in every dashboard URL. The SQL API token is a secret.

Secrets ​

Set via wrangler secret put:

SecretDescription
CF_ANALYTICS_API_TOKENCloudflare API token with Account Analytics Read (WAE SQL API)

Deployment ​

bash
cd workers/analytics-rollup
npx wrangler deploy

# Secrets
npx wrangler secret put CF_ANALYTICS_API_TOKEN

# Logs
npx wrangler tail

After first deploy, verify rollup rows appear and analytics_rollup_state shows last_status = 'ok' for both datasets.

Troubleshooting ​

analytics_rollup_state.last_status = 'error' ​

last_error holds the truncated failure. Common causes: WAE SQL API auth (token expired/insufficient — needs Account Analytics Read), Hyperdrive connectivity, or a schema drift against the blob-position contract. The next hourly run recovers automatically once the cause is fixed.

Aggregates Look Too Low on a Busy Day ​

Check that any manual queries against WAE also weight by _sample_interval — the worker does; ad-hoc dashboard SQL often forgets.

An App Shows as "Unknown" ​

The UA is not matched by apps.json (or the AppleCoreMedia override). Run pnpm run update-opawg to pick up upstream pattern additions; changes apply to future rollups only.

Milestone Fired Twice? ​

It cannot, by design: crossing detection means a re-roll never re-crosses, the notification RPC dedupes per (recipient, episode, milestone), and the automation-scheduler adds a stable-idempotency layer. If you see duplicates, check for manual analytics_episode_totals edits that moved a total below a milestone.

Internal documentation - Not for public distribution

show.fm Team