A friend asked me to help him automate something his team does manually every morning: scraping YouTube for new videos from European automotive press channels (EN/DE/IT/FR/ES), watching them, and producing a daily readable report. Pure plumbing problem with one interesting analytical core. Here's what I'm building. Goal Daily cron job → discover new videos from a channel allowlist → transcribe → analyze → human-readable report in inbox by 7am. Stack: - Discovery: yt-dlp (CLI, more reliable than YouTube Data API for our needs but we still need API for some metadata) - Transcription: cascading strategy - YouTube transcripts first (free, instant when available), then faster-whisper large-v3 locally as fallback. Whisper API only if local becomes a bottleneck. - Analysis: 2-stage Claude API calls. Stage 1 extracts structured facts per video (entities, claims, sentiment) into JSON. Stage 2 synthesizes the day's batch into a narrative report. - State: Google Sheets (videos_log, daily_runs, etc.) - chosen over SQLite because the friend wants visibility into the pipeline without me building a UI. - Storage: Google Drive for reports. - Language/tooling: Python, uv (not pip/Poetry), Typer for CLI, dataclasses with type hints for row schemas. - Deploy: Hetzner VPS, cron-triggered. - Dev workflow: Claude Code with GSD framework (discuss → plan → execute → verify → ship per phase). Key decisions I'm second-guessing: 1. Google Sheets as state store. Solves visibility for free, but feels janky. SQLite would be cleaner but requires a separate dashboard. Anyone done this and regretted it? 2. Two-call analysis (extract → synthesize) vs single-call. I think separation gives me debuggability and lets me regenerate reports without re-processing videos. But it's 2x API cost. Worth it? 3. OAuth Desktop app + 7-day refresh token in Testing mode. Works for unattended cron because the job runs daily. But if I publish it (single user, no real "users"), do I unlock anything I actually need? 4. Cascading transcription strategy. YT transcripts → faster-whisper → API. Sound or am I over-engineering for a low-volume MVP?