Path B re-run: forward-look frequency analysis after 30 days of `concerts` data

[jakebromberg]

Scope

Re-run Path B of #1368's frequency analysis once the venue-events scraper has been running steadily in production for ≥30 days. Append a "Path B cross-check" section to plans/touring-events/frequency-analysis.md in wxyc-workspace.

Why a separate issue

Per #1368, Path A is the headline deliverable and runs as soon as the scraper (#1343) is deployed. Path B has to wait for two distinct things to settle:

1. Scraper stability — ≥30 nightly runs of idempotent upserts to confirm the writer behaves as expected against drifting RHP HTML. This is not about forward-window data accumulating: RHP venues publish 60–90 days ahead, so the forward window is populated by day 1. The 30-day clock is about confidence that what we're reading is stable.
2. Resolver coverage — Backfill concerts.headlining_artist_id via local artist + alias resolver #1372 populating headlining_artist_id so the SQL JOINs cleanly on the canonical FK rather than via the brittle raw-name fallback.

Encoding these in a real ticket avoids the "we'll get to it" failure mode and lets the dependency be wired into the issue graph.

Earliest run

Today is 2026-06-08; #1345 just merged. Budget no earlier than 2026-07-10 for the scraper-stability clock, and gate on #1372 having run at least once. If either condition is unmet, do not start.

Procedure

1. Confirm scraper has been running nightly for ≥30 days (check concerts.scraped_at distribution).
2. Confirm Backfill concerts.headlining_artist_id via local artist + alias resolver #1372's resolver pass has run and report headlining_artist_id coverage. No hard threshold — touring headliners are typically well-known acts and may resolve well above LML's 24% catalog ceiling, or may underperform if the alias substrate hasn't caught up. Whatever it is, report it; the raw-vs-FK delta below quantifies the impact directly.
3. Run all three of Frequency analysis: how often will the venue-events scraper surface a WXYC-relevant artist? #1368's match rules (loose / tight / tightest) against prod (read-only, per-turn auth), under both the canonical-FK join and the raw-name fallback. Six queries total. Canonical-FK shapes for loose + tight:

-- Loose: artist in library, canonical FK
SELECT date_trunc('day', c.starts_at)::date AS day, COUNT(DISTINCT c.id)
FROM wxyc_schema.concerts c
WHERE c.headlining_artist_id IN (SELECT DISTINCT artist_id FROM wxyc_schema.library)
  AND c.starts_at BETWEEN now() AND now() + interval '90 days'
  AND c.status <> 'cancelled'
GROUP BY 1 ORDER BY 1;

-- Tight: played in trailing 30d, canonical FK
SELECT date_trunc('day', c.starts_at)::date AS day, COUNT(DISTINCT c.id)
FROM wxyc_schema.concerts c
JOIN wxyc_schema.flowsheet f ON f.artist_id = c.headlining_artist_id
WHERE f.start_time >= now() - interval '30 days'
  AND c.starts_at BETWEEN now() AND now() + interval '90 days'
  AND c.status <> 'cancelled'
GROUP BY 1 ORDER BY 1;

Tightest (heavy/medium rotation in trailing 90d) joins wxyc_schema.rotation per #1368's methodology block — same canonical-FK shape, swap the flowsheet join for the rotation join.

Raw-name variants follow #1368's methodology (lower + leading-"The" strip on both sides). Run the same three rules with raw-name joins for the A/B comparison; the delta against canonical-FK is the resolver-coverage signal.

4. Append a "Path B cross-check" section to the memo:
   • The six queries' results.
   • Side-by-side table: Path A (one-shot HTML pull) vs Path B raw-name (30 days of scraper data) vs Path B canonical-FK.
   • Resolver-coverage delta: raw-name match count minus canonical-FK match count, per rule. Comment on whether the gap is mostly alias-substrate (need richer aliases) or artists duplicate-name groups (the known unresolvable case in Backfill concerts.headlining_artist_id via local artist + alias resolver #1372).

Acceptance

• Six Path B queries (three rules × {canonical-FK, raw-name}) run against ≥30 days of concerts data
• Results appended to the memo as a "Path B cross-check" section
• Side-by-side comparison table: Path A vs Path B raw-name vs Path B canonical-FK, all three match rules
• Resolver-coverage delta reported per match rule; this delta is the headline new number whether or not it's large
• If any rule shows a Path A vs Path B divergence of >25%, investigate root cause (sample-size, calendar drift between Path A's one-shot HTML and Path B's accumulated data, parser drift) and document the cause in the memo
• Memo notes explicitly that Path B's 30-day window is too small to confirm the bimodal seasonal pattern asserted in Path A
• Memo TL;DR updated if Path B materially changes the headline rate
• If the headline rate moves enough to shift the recommended cell in Frequency analysis: how often will the venue-events scraper surface a WXYC-relevant artist? #1368's decision matrix (<1/mo ↔ 1–4/mo ↔ >4/mo), re-run the matrix and update the venue-extension / Bandsintown-leverage recommendation

Related

• Blocked by Venue events scraper for Triangle RHP-powered sites (Cat's Cradle, Local 506) #1343 (scraper deployed and running nightly for ≥30 days)
• Blocked by Backfill concerts.headlining_artist_id via local artist + alias resolver #1372 (canonical FK populated)
• Sub-issue of Frequency analysis: how often will the venue-events scraper surface a WXYC-relevant artist? #1368 — closes Frequency analysis: how often will the venue-events scraper surface a WXYC-relevant artist? #1368's Path B acceptance bullet
• Cross-reference Frequency analysis: how often will the venue-events scraper surface a WXYC-relevant artist? #1368 for full methodology

[jakebromberg]

Resolver merged 2026-06-10

PR #1375 merged at 2026-06-10T04:37:01Z, auto-deploy run 27253514139 green across the matrix (migrate, build, deploy for concerts-artist-resolver). #1372 auto-closed via the PR's Closes #1372.

Resolver-side gating is satisfied

• Migration 0092 (normalize_artist_name(text) + three functional indexes + the partial COVERING index on concerts (id) INCLUDE (headlining_artist_raw) WHERE headlining_artist_id IS NULL) applied to prod.
• jobs/concerts-artist-resolver/ deployed; cron schedule 15 5 * * * UTC. First scheduled run: 2026-06-10 05:15 UTC.
• One-shot historical drain: not separate from the cron. The first cron run resolves every eligible row in one pass (the SELECT predicate is just headlining_artist_id IS NULL AND headlining_artist_raw IS NOT NULL); subsequent nightly runs are near-zero work in steady state.

Remaining gates

• Scraper-stability clock — still binding per this ticket's body. Today is 2026-06-09; budget no earlier than 2026-07-10 for the ≥30 nightly-runs threshold (Venue events scraper for Triangle RHP-powered sites (Cat's Cradle, Local 506) #1343 was running pre-merge so the clock may already be partially elapsed — confirm by querying SELECT MIN(scraped_at), COUNT(DISTINCT date_trunc('day', scraped_at)) FROM wxyc_schema.concerts).
• First resolver run produces FK coverage — after the 2026-06-10 05:15 UTC run, query SELECT COUNT(*) FILTER (WHERE headlining_artist_id IS NOT NULL) * 100.0 / COUNT(*) AS pct_resolved FROM wxyc_schema.concerts to capture the coverage number for the memo.

Known FK-vs-raw divergence to watch for

PR #1375's README documents a known limitation that may surface in the raw-vs-FK delta: the venue-events-scraper UPSERT overwrites headlining_artist_raw on every re-scrape (jobs/venue-events-scraper/writer.ts), but the resolver only writes on headlining_artist_id IS NULL. So if a venue swaps a headliner after first scrape, the FK stays pinned to the original artist while raw reflects the new one. The Path B "raw-name minus canonical-FK" delta will catch this — track it as a possible third bucket alongside alias-substrate gaps and artists duplicate-name groups in the resolver-coverage delta discussion (acceptance bullet 4).

[jakebromberg]

First resolver run + scraper-clock measurement (2026-06-10)

Captured the two numbers from the prior comment plus a schema constraint that means one of those queries needs to change going forward.

FK coverage — 24.44% (22 / 90)

Read at 2026-06-10 05:13 UTC (pre-scheduled cron) and again at 05:19 UTC (post-scheduled cron). Identical both times: 22 of 90 concerts have headlining_artist_id IS NOT NULL.

The 22 resolutions are from the resolver's one-shot historical drain, which ran as part of the PR auto-deploy at 04:37 UTC. The 15 5 * * * cron at 05:15 UTC either ran with zero new resolvable rows (the 04:37 drain already saturated the strict + alias substrate) or hadn't fired by 05:19 UTC — MAX(last_modified) is 05:00:41 UTC, i.e. no writes since the venue-events-scraper's 0 5 * * * run. Both states are consistent with the data, and either way 24.44% is the steady-state ceiling for the current strict + alias strategy.

That number lands within rounding of LML's documented 24.3% Discogs reconciliation ceiling — not a coincidence: both are bounded by the same artists + artist_search_alias substrate. As the substrate grows (BS#1307 alias-substrate work), this number tracks upward in lockstep with LML's number.

Scraper-stability clock — query is mis-specified for current schema

SELECT MIN(scraped_at), COUNT(DISTINCT date_trunc('day', scraped_at)) FROM wxyc_schema.concerts returns 1 distinct day, because the scraper UPSERT overwrites scraped_at on every re-scrape (jobs/venue-events-scraper/writer.ts). The concerts table has no created_at column — only scraped_at (overwritten) and last_modified (default now(), also moved on every write).

So MIN(scraped_at) measures "time since last nightly scrape", not "scraper start time". The 30-day stability clock can't be observed in-band from the current schema.

Options for measuring the ≥30-day threshold:

1. Add a first_scraped_at column populated on INSERT-only (column default + DO-NOTHING-on-conflict for that column in the writer).
2. Backfill from GHA: count distinct created_at dates of completed venue-events-scraper workflow runs.
3. Treat 2026-06-10 (today) as day 1 and budget the Path B re-run for 2026-07-10 unconditionally, ignoring any pre-merge runs of Venue events scraper for Triangle RHP-powered sites (Cat's Cradle, Local 506) #1343. Simplest; matches the original budget in the issue body.

(3) is what I'd actually do — it costs nothing and the 30-day clock is fundamentally about confidence in "what we're reading is stable", which is observed in field-by-field stability of raw_data across nights, not in a min-date number anyway. The Path B re-run procedure can spot-check stability by querying raw_data history if/when needed.

Unresolved-sample buckets (acceptance bullet 4 preview)

Beyond the three buckets the prior comment named (alias-substrate gaps, artists duplicate-name groups, scraper re-upsert vs resolver-on-NULL-only), the unresolved sample surfaces a fourth bucket: tour-suffix noise in headlining_artist_raw:

• Hiss Golden Messenger – I'm People Tour
• Crazy Chester: A Very Carrboro Tribute To The Band & The Last Waltz 50th Anniversary Tour
• Ax and the Hatchetmen: The Late Checkout Tour 2026

The resolver's normalizer (lower + leading-"The " strip, per migration 0092) doesn't strip tour-name suffixes or colon-separated event titles. "Hiss Golden Messenger" is almost certainly in the substrate; the raw row reads Hiss Golden Messenger – I'm People Tour and falls through. Worth treating as its own bucket in the Path B delta discussion — it's a normalizer-coverage gap, distinct from substrate-coverage gaps.

Clean unresolved names that look like substrate gaps proper: King Buffalo, Die Spitz, Michigander, The Menzingers, Aaron Lee Tasjan, The Bends. Some of these are plausibly in the substrate but unresolved due to alias gaps; others are genuinely absent.

Remaining gate

Scraper-stability clock: per the rationale above, treating 2026-06-10 as day 1 — earliest Path B re-run 2026-07-10.