yt-channel-scraper/docs/superpowers/plans/2026-07-26-platform-build.md

yt-scraper Platform Implementation Plan

Execution mode: Subagent-driven + inline, autonomous, self-iterating. User mandated "completa todo el proceso de manera agentica y subagentica creando y resolviendo todos los problemas y dudas en un loop auto iterativo." Proceed without further checkpoints; verify with tests and fix in a loop until green.

Goal: Turn the CLI scraper into a local content-mining platform: CLI features (no AI) + FastAPI/Alpine webapp + .bat launcher + opencode subagent.

Architecture: Monorepo. Store extends with non-destructive migration (segments FTS5 + metadata + cookies + jobs). New modules segments/cookies/export/analysis/monitor. FastAPI app at src/yt_scraper/webapp/ serves a no-build Alpine SPA. .bat launcher auto-scans ports.

Tech: Python ≥3.10, FastAPI, uvicorn, Alpine.js + Tailwind + Chart.js (CDN), SQLite FTS5, yt-dlp, matplotlib, Jinja2.

Spec: docs/superpowers/specs/2026-07-26-platform-design.md

---

File map & responsibilities

File	Responsibility	Status
src/yt_scraper/store.py	Add migration (new cols + 3 new tables), segment/cookie/job queries	modify
src/yt_scraper/segments.py	store_segments, populate_fts, search, backfill_from_markdown	new
src/yt_scraper/cookies.py	Netscape parse/validate, vault CRUD, resolve_active_path	new
src/yt_scraper/export.py	json/csv/srt/html exporters	new
src/yt_scraper/analysis.py	word freq / top-N / timeline (data + optional matplotlib)	new
src/yt_scraper/monitor.py	watch_loop(config, interval, once)	new
src/yt_scraper/cli.py	Click group + subcommands; fix cookies bug	modify
src/yt_scraper/extract.py	unchanged (already accepts cookies)	-
src/yt_scraper/webapp/app.py	FastAPI factory + static + healthz	new
src/yt_scraper/webapp/api.py	REST routers	new
src/yt_scraper/webapp/jobs.py	scrape job runner + SSE bus	new
src/yt_scraper/webapp/static/{index.html,app.js,styles.css}	SPA	new
templates/gallery.html.j2	HTML export gallery	new
start-server.bat, stop-server.bat	launcher	new
.opencode/agent/webapp-builder.md, .opencode/goals/webapp-build.md	subagent	new
tests/	cover migration, segments, cookies, export, analysis, cli	new/extend

---

Key interfaces (contract between modules)

# segments.py
@dataclass
class Hit: video_id: str; channel_id: str; title: str; start_sec: float; end_sec: float; snippet: str; rank: float
def store_segments(store, video_id, segments: list[Segment]) -> None
def search(store, query: str, channel_id: str|None=None, limit: int=50) -> list[Hit]
def backfill_from_markdown(store, md_root: Path, log=print) -> int  # returns n videos

# cookies.py
@dataclass
class CookieMeta: id:str; filename:str; label:str|None; added_at:str; expires_at:str|None; is_active:bool; has_session:bool; cookie_count:int
def parse_netscape(path) -> tuple[bool, dict]  # (ok, {expires_at, has_session, count})
def import_file(store, path) -> str            # id
def import_text(store, text, label) -> str
def set_active(store, cookie_id) -> None
def list_vault(store) -> list[CookieMeta]
def delete(store, cookie_id) -> None
def resolve_active_path(store) -> str|None
def auto_import_dir(store, dir_path) -> None   # import loose .txt in cookies/

# export.py
def export_json(store, segments_store_fn, channel_id, out_path) -> Path
def export_csv(store, channel_id, out_path) -> Path
def export_srt(store, video_id, out_path) -> Path
def export_html(store, channel_id, out_path, template_path) -> Path

# analysis.py
def word_frequency(store, channel_id=None) -> dict[str,int]
def top_words(store, n=50, channel_id=None) -> list[tuple[str,int]]
def term_timeline(store, term, channel_id=None) -> list[tuple[str,int]]  # (YYYY-MM, count)
def render_wordcloud(freq, out_path) -> None  # matplotlib

# monitor.py
def watch_loop(cfg, interval_seconds, channel_id=None, once=False) -> None

---

Tricky bits (resolved decisions)

• FTS5 sync: manual populate_fts (delete+insert) after store_segments. No triggers (simpler, idempotent). FTS uses external-content transcript_fts with content='transcript_segments'; queries via MATCH + join.
• Migration: ALTER TABLE ADD COLUMN is guarded by PRAGMA table_info checks (idempotent). New tables via CREATE TABLE IF NOT EXISTS.
• Backfill parse: re-use existing .md format: lines **MM:SS** · text under ### Title (MM:SS) chapter headers; frontmatter between --- fences has YAML metadata. Reuse parse.Segment + chapters.Section.
• Cookie active: single row is_active=1; set_active flips all others to 0 in one tx.
• Click structure: convert top-level @click.command to @click.group with the original scrape flow under scrape command (default invocation preserved via a wrapper), plus search/export/audio/channels/watch/analyze/re-render.
• Port scan: PowerShell System.Net.Sockets.TcpListener bind-test 8000..8100.
• SSE: EventSourceResponse-style via sse-starlette OR a hand-rolled streaming Response with text/event-stream. Use sse-starlette (add to deps) — cleaner.

---

Task list (build order)

A1 — Foundation

• T1: Store migration (new cols + transcript_segments + transcript_fts + cookies_meta + scrape_jobs) + queries. Test: migration idempotent, columns exist, tables exist.
• T2: segments.py (store_segments, populate_fts, search). Test: store + search returns Hit.
• T3: cookies.py. Test: parse real cookie file, validate, active toggling, resolve path.
• T4: Fix cli.py cookies bug (thread cookies through _process_one; fallback to active vault cookie).
• T5: backfill_from_markdown. Test: parse one real .md, assert segments inserted.

A2 — Feature modules

• T6: export.py (json/csv/srt/html) + gallery.html.j2. Test: each format round-trips.
• T7: analysis.py (freq, top, timeline, wordcloud). Test: counts on synthetic segments.
• T8: monitor.py (watch_loop, --once). Test: single iteration with mocked discover.

A3 — CLI wiring

• T9: Convert cli.py to Click group; add search, export, audio, channels, watch, analyze, re-render, --all-channels. Smoke-test each via CliRunner.

A4 — Tests green

• T10: Full pytest green; integration tests marked.

B — Webapp (subagent: webapp-builder)

• T11: .opencode/agent/webapp-builder.md + .opencode/goals/webapp-build.md.
• T12: webapp backend (app.py, api.py, jobs.py) — all routers + SSE.
• T13: SPA (index.html, app.js, styles.css) — 9 views, dark command-center, drag-drop cookies, Chart.js, SSE scrape console.
• T14: Verify /healthz + smoke each endpoint; auto-import real cookie on startup.

C — Launcher + deps

• T15: pyproject.toml optional deps (web, analysis); .gitignore (cookies/, .run/, data/analysis/).
• T16: start-server.bat + stop-server.bat (PowerShell port scan).
• T17: End-to-end: start-server.bat → browser → /healthz 200 → dashboard loads.

Verification loop

• Run pytest, fix until green. Run start-server.bat, confirm health + SPA. Iterate.