Pipeline Flow. how one channel becomes a dot on the map

Resolve the channel identity YouTube API · 1 unit

@<handle> → channels.list?forHandle=<handle> → UC…. UC ids pass straight through with zero API cost — which is why TARGETS prefers them.

Upsert into channels DB write #1

INSERT INTO channels (channel_id, channel_name, focus, notes)
VALUES ($1, $2, $3, $4)
ON CONFLICT (channel_id) DO UPDATE SET   -- idempotent: re-running never duplicates
  channel_name = EXCLUDED.channel_name, focus = EXCLUDED.focus, notes = EXCLUDED.notes;

Every write in this pipeline is an upsert against a natural unique key — here channel_id. That single discipline is what makes the whole harvester resumable and safe to re-run daily.

List recent uploads search.list · 100 units ⚠️

The most expensive YouTube call in the app (100 of the 10,000 daily units). One call per channel per run. It returns up to FETCH_BUFFER = 50 videos; how many survive the ≥ 3-minute cut is a property of the channel, since most creators now post a heavy tail of Shorts.

Fetch durations, drop Shorts videos.list · 1 unit

Title prefilter — don't pay for off-topic videos 0 API calls

// include-words OVERRIDE excludes — decide which error you'd rather make:
// a false keep costs 1 LLM call; a false skip silently loses a map point.
INCLUDE: /restaurant|eatery|food tour|cheap eats|hidden gem|hole.in.the.wall|
          best .{0,20}in (Toronto|Scarborough|Markham|…)|I (tried|ate)|dim sum|brunch|patio/
EXCLUDE: talk·music /podcast|interview|LIVE|Q&A|reaction/ · grocery /grocery haul|Costco run|
                     convenience.store|meal prep/
         home-cooking /(at home|in my kitchen).{0,16}(cook|bake|made)/ · /recipe|how to make/
         outdoor /hiking|camping|Bruce Trail|Algonquin/
         out-of-province /New York|NYC|Montreal|Vancouver|Chicago|Buffalo|Tokyo|abroad/
per-channel: <channel> → includeOnly /<term>/   // for channels where food is the minority format

Include-words winning is the whole point: a title like "my easy weeknight recipe — and the best pizza in Little Italy" is caught by recipe and saved by best … in. Upstream measured that before this filter existed, ~60–70% of a day's LLM quota went to music shows and grocery hauls.

The out-of-province line is doing more work here than it did upstream: Toronto creators cross into Montreal, Buffalo and NYC constantly, and those videos are genuinely food reviews — they just produce restaurants region.ts will drop. Filtering them at the title costs one regex; filtering them after geocoding costs proxy bandwidth, an LLM call and a quarantined row.

Skip what's already done DB read

-- ① did this video already yield restaurants?
SELECT 1 FROM restaurants WHERE video_id = $1;
-- ② or did the LLM already confirm it has none? (zero-yield tombstone)
SELECT extraction_empty_at FROM videos WHERE video_id = $1;

Ingest metadata → videos videos.list · 1 unit · DB write #2

INSERT INTO videos (video_id, title, channel_title, description, published_at)
VALUES ($1,$2,$3,$4,$5)
ON CONFLICT (video_id) DO UPDATE SET … , updated_at = now();

Extract transcript → transcripts 0 quota (scrape) · DB write #3

INSERT INTO transcripts (video_id, transcript_data, language_code, has_english)
VALUES ($1, $2::jsonb, $3, $4)
ON CONFLICT (video_id, language_code) DO UPDATE SET transcript_data = EXCLUDED.transcript_data;

No official API exists — this scrapes YouTube's caption endpoint. It's the flakiest step: datacenter IPs get blocked (hence optional PROXY_URL for EC2), and it's cached in the DB so re-runs are free. has_english gates whether the LLM step is worth running — but it is a weaker gate than upstream's has_korean, which could just look for Hangul. English has no script of its own, so this reads the caption track's language label and trusts it.

Fetch comments → comment_threads 1 unit/page (100 threads) · DB write #4

INSERT INTO comment_threads (video_id, youtube_thread_id, top_level_comment, replies)
VALUES ($1,$2,$3::jsonb,$4::jsonb)
ON CONFLICT (youtube_thread_id) DO UPDATE SET …;

Why comments? Viewers name the restaurant ("that's the banh mi place on Spadina, not the Dundas one") and correct what the auto-captions mangled — they're free disambiguation signal for the LLM, and they matter more here than upstream, because English ASR butchers exactly the non-English restaurant names the GTA is full of.

LLM extraction — names + areas Gemini Flash · 1 req (free tier: limited/day)

prompt = title + description + transcript + comments
       + "return ONLY establishments actually REVIEWED — not dishes, not name-drops"
→ [{"name":"…","area":"Kensington Market"}, …]   // JSON array of {name, area}

Output is post-filtered: placeholder names (/no name|unnamed|didn't catch/i) are dropped before enrichment. The result carries llmResponded — distinguishing "the model said zero" from "the call died".

Places enrichment — name → coordinates Places searchText ≤3 req + Geocoding fallback

// ordered query plan — most specific first, validation on the loose end
0. scope guard: qualifyArea() + ONTARIO_BOUNDS → out-of-Ontario hits are dropped
1. "<name> Kensington Market Toronto"   // name + canonical area, area qualified to "…, Ontario, Canada"
2. "<name> Toronto"                   // name + city only
3. "<name>" + validate                // bare name — hit MUST land inside ONTARIO_BOUNDS
   // (upstream had a step 4: a Korean spell-corrector. Deleted — searchText is already
   //  fuzzy, and the corrector's failures were confident nonsense, not near-misses.)
coords: Places location.latitude/longitude directly; else geocode the address

Persist → restaurants, or tombstone → videos DB write #5

INSERT INTO restaurants (video_id, restaurant_name, area, maps_link, place_id, address,
                         short_address, latitude, longitude, category, description, last_enriched_at)
VALUES (…, now())
ON CONFLICT (video_id, restaurant_name) DO UPDATE SET … ;  -- same place re-reviewed = update, not dup

-- OR, when llmResponded ∧ names.length = 0:
UPDATE videos SET extraction_empty_at = now() WHERE video_id = $1;  -- never re-pay for this video

Un-geocoded rows (out of province, or genuinely not in Places) stay with latitude IS NULL — stored but invisible to the map. Nothing is deleted; provenance (which video said it) is always kept.

Read path — the map GET /restaurants

SELECT r.*, v.title AS video_title, v.channel_title
FROM restaurants r LEFT JOIN videos v ON r.video_id = v.video_id
WHERE r.latitude IS NOT NULL ORDER BY r.created_at DESC;

The viewer never talks to the pipeline — only to the table. That's why the map fills in live while a harvest runs.