Pipeline Flow. 한 채널이 지도 위 점이 되기까지

Resolve the channel identity YouTube API · 1 unit

@gaypig1111 → channels.list?forHandle=gaypig1111 → UCIP3hSJruPL4dIi95lsuCZA (410K subs). 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. For 홍석천이원일 it returned 50 videos — only 20 of which are ≥ 3 minutes (the rest are 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 Gemini call; a false skip silently loses a map point.
INCLUDE: /맛집|식당|먹을텐데|또간집|노포|밥집|뷔페|횟집|술집|안주|포차|회식|국밥|해장국/
EXCLUDE: music·talk /부를텐데|만날텐데|레시피|LIVE/ · marts /마트|편의점|꿀템/
         home-cooking /집에서.{0,16}(구웠|구워|만들)/ · outdoor /한라산|등반|올레길/
         overseas /도쿄|오사카|뉴욕|일본|미국|베트남|하노이|해외/
per-channel: 성시경 → includeOnly /먹을텐데/   // 11/12 of its uploads are music/talk

For 홍석천이원일 all 20 long-form titles pass — including "이원일의 레시피!… 피자 맛집" which survives only because include-words win. Before this filter existed, ~60–70% of a day's Gemini quota went to music shows and mart hauls.

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_korean)
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_korean gates whether the LLM step is worth running.

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 ("여기 ○○집이에요!") and correct transcript typos — they're free disambiguation signal for the LLM. 홍석천이원일 videos carry hundreds of threads each.

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":"무교동"}, …]   // JSON array of {name, area}

Output is post-filtered: placeholder names (/이름\s*없|간판/) are dropped before enrichment. The result carries llmResponded — distinguishing "the model said zero" from "the call died".

Naver enrichment — name → 좌표 Naver Local Search ≤3 req + Geocode fallback

// ordered query plan — most specific first, validation on the loose end
0. overseas guard: area ~ /일본|도쿄|뉴욕|하노이…/ → skip entirely (Naver = Korea only)
1. "무교동북어국집 무교동"        // name + full area
2. "무교동북어국집 무교동"[city]   // name + first token of area
3. "무교동북어국집" + validate     // bare name — hit's address MUST contain an area token
4. errata spell-fix, last resort  // it "corrected" 퍼옹부이 → 포옹부인 (nonsense)
coords: mapx/mapy ÷ 10,000,000 → lat/lng; else geocode the address

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

INSERT INTO restaurants (video_id, restaurant_name, area, naver_link, address,
                         road_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 (overseas, or genuinely not on Naver) 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.