Frank Schwenk
3f1da92cf4
YouTube subtitles via OpenRouter become persistent, shareable recaps with timestamp jump links, PIN-protected creation, and Traefik deploy. Co-authored-by: Cursor <cursoragent@cursor.com>
106 lines
3.4 KiB
Python
106 lines
3.4 KiB
Python
from __future__ import annotations
|
|
|
|
import json
|
|
import pathlib
|
|
from typing import Any
|
|
|
|
import aiosqlite
|
|
|
|
from server.settings import settings
|
|
|
|
SCHEMA_PATH = str(pathlib.Path(__file__).resolve().parents[1] / "tools" / "schema.sql")
|
|
|
|
|
|
async def get_db() -> aiosqlite.Connection:
|
|
pathlib.Path(settings.db_path).parent.mkdir(parents=True, exist_ok=True)
|
|
db = await aiosqlite.connect(settings.db_path)
|
|
db.row_factory = aiosqlite.Row
|
|
await db.execute("PRAGMA foreign_keys = ON;")
|
|
return db
|
|
|
|
|
|
async def ensure_schema(db: aiosqlite.Connection) -> None:
|
|
schema = pathlib.Path(SCHEMA_PATH).read_text(encoding="utf-8")
|
|
await db.executescript(schema)
|
|
await db.commit()
|
|
|
|
|
|
def row_to_recap(row: aiosqlite.Row) -> dict[str, Any]:
|
|
data = dict(row)
|
|
data["sections"] = json.loads(data.pop("sections_json"))
|
|
return data
|
|
|
|
|
|
async def get_recap(db: aiosqlite.Connection, video_id: str) -> dict[str, Any] | None:
|
|
cur = await db.execute("SELECT * FROM recaps WHERE video_id = ?", (video_id,))
|
|
row = await cur.fetchone()
|
|
await cur.close()
|
|
return row_to_recap(row) if row else None
|
|
|
|
|
|
async def list_recaps(db: aiosqlite.Connection, *, limit: int = 20, offset: int = 0) -> list[dict[str, Any]]:
|
|
cur = await db.execute(
|
|
"""
|
|
SELECT video_id, title, channel, thumbnail_url, duration_sec, language,
|
|
tldr, vibe_check, watch_verdict, goldfish_note, created_at, updated_at
|
|
FROM recaps
|
|
ORDER BY created_at DESC
|
|
LIMIT ? OFFSET ?
|
|
""",
|
|
(limit, offset),
|
|
)
|
|
rows = await cur.fetchall()
|
|
await cur.close()
|
|
return [dict(row) for row in rows]
|
|
|
|
|
|
async def count_recaps(db: aiosqlite.Connection) -> int:
|
|
cur = await db.execute("SELECT COUNT(*) FROM recaps")
|
|
row = await cur.fetchone()
|
|
await cur.close()
|
|
return int(row[0]) if row else 0
|
|
|
|
|
|
async def upsert_recap(db: aiosqlite.Connection, recap: dict[str, Any]) -> None:
|
|
await db.execute(
|
|
"""
|
|
INSERT INTO recaps (
|
|
video_id, title, channel, thumbnail_url, duration_sec, language,
|
|
tldr, vibe_check, watch_verdict, goldfish_note, sections_json,
|
|
transcript_hash, model, created_at, updated_at
|
|
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
|
|
ON CONFLICT(video_id) DO UPDATE SET
|
|
title = excluded.title,
|
|
channel = excluded.channel,
|
|
thumbnail_url = excluded.thumbnail_url,
|
|
duration_sec = excluded.duration_sec,
|
|
language = excluded.language,
|
|
tldr = excluded.tldr,
|
|
vibe_check = excluded.vibe_check,
|
|
watch_verdict = excluded.watch_verdict,
|
|
goldfish_note = excluded.goldfish_note,
|
|
sections_json = excluded.sections_json,
|
|
transcript_hash = excluded.transcript_hash,
|
|
model = excluded.model,
|
|
updated_at = excluded.updated_at
|
|
""",
|
|
(
|
|
recap["video_id"],
|
|
recap["title"],
|
|
recap.get("channel"),
|
|
recap.get("thumbnail_url"),
|
|
recap.get("duration_sec"),
|
|
recap.get("language"),
|
|
recap["tldr"],
|
|
recap.get("vibe_check"),
|
|
recap["watch_verdict"],
|
|
recap.get("goldfish_note"),
|
|
json.dumps(recap["sections"], ensure_ascii=False),
|
|
recap.get("transcript_hash"),
|
|
recap.get("model"),
|
|
recap["created_at"],
|
|
recap["updated_at"],
|
|
),
|
|
)
|
|
await db.commit()
|