Source Collection

CREATE TABLE IF NOT EXISTS sources (
    id INTEGER PRIMARY KEY,
    source_key TEXT UNIQUE NOT NULL,
    platform TEXT,
    source_type TEXT,
    display_name TEXT,
    query TEXT,
    url TEXT,
    cadence TEXT DEFAULT 'daily',
    active INTEGER DEFAULT 1,
    quality_score REAL DEFAULT 0,
    last_checked_at TEXT,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS items (
    id INTEGER PRIMARY KEY,
    canonical_key TEXT UNIQUE NOT NULL,
    platform TEXT,
    source_key TEXT,
    source_name TEXT,
    item_type TEXT,
    title TEXT,
    summary TEXT,
    text TEXT,
    url TEXT,
    author TEXT,
    published_at TEXT,
    first_seen_at TEXT NOT NULL,
    last_seen_at TEXT NOT NULL,
    language TEXT,
    raw_json TEXT,
    content_hash TEXT,
    delivered_at TEXT
);

CREATE TABLE IF NOT EXISTS metric_snapshots (
    id INTEGER PRIMARY KEY,
    item_id INTEGER NOT NULL REFERENCES items(id),
    collected_at TEXT NOT NULL,
    views INTEGER,
    likes INTEGER,
    comments INTEGER,
    shares INTEGER,
    saves INTEGER,
    bookmarks INTEGER,
    reposts INTEGER,
    quotes INTEGER,
    stars INTEGER,
    forks INTEGER,
    score INTEGER,
    raw_metrics_json TEXT
);

CREATE TABLE IF NOT EXISTS item_scores (
    id INTEGER PRIMARY KEY,
    item_id INTEGER NOT NULL REFERENCES items(id),
    run_date TEXT NOT NULL,
    relevance_score REAL,
    velocity_score REAL,
    content_fit_score REAL,
    novelty_score REAL,
    combined_score REAL,
    format_label TEXT,
    reason_codes TEXT,
    created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS daily_brief_items (
    id INTEGER PRIMARY KEY,
    brief_date TEXT NOT NULL,
    item_id INTEGER NOT NULL REFERENCES items(id),
    rank INTEGER,
    section TEXT NOT NULL,
    resurface_reason TEXT,
    delivered INTEGER DEFAULT 0,
    delivered_at TEXT,
    created_at TEXT NOT NULL,
    UNIQUE(brief_date, item_id, section)
);

CREATE TABLE IF NOT EXISTS research_ideas (
    id INTEGER PRIMARY KEY,
    idea_key TEXT UNIQUE NOT NULL,
    idea_type TEXT NOT NULL,
    title TEXT,
    hook TEXT,
    thesis TEXT,
    outline TEXT,
    source_item_ids TEXT,
    target_platform TEXT,
    status TEXT DEFAULT 'new',
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS topic_briefings (
    id INTEGER PRIMARY KEY,
    brief_date TEXT NOT NULL,
    generated_at TEXT NOT NULL,
    source_window_start TEXT NOT NULL,
    topic_count INTEGER DEFAULT 0,
    status TEXT DEFAULT 'generated',
    output_path TEXT,
    summary_json TEXT
);

CREATE TABLE IF NOT EXISTS topic_briefing_topics (
    id INTEGER PRIMARY KEY,
    briefing_id INTEGER NOT NULL REFERENCES topic_briefings(id),
    rank INTEGER NOT NULL,
    item_id INTEGER,
    topic_key TEXT NOT NULL,
    topic TEXT NOT NULL,
    visible_description TEXT,
    detailed_brief_path TEXT,
    enriched_brief_path TEXT,
    status TEXT DEFAULT 'proposed',
    selected_at TEXT,
    created_at TEXT NOT NULL,
    UNIQUE(briefing_id, topic_key)
);

CREATE TABLE IF NOT EXISTS runs (
    id INTEGER PRIMARY KEY,
    run_date TEXT NOT NULL,
    started_at TEXT NOT NULL,
    completed_at TEXT,
    raw_count INTEGER DEFAULT 0,
    new_item_count INTEGER DEFAULT 0,
    updated_item_count INTEGER DEFAULT 0,
    selected_count INTEGER DEFAULT 0,
    failure_count INTEGER DEFAULT 0,
    duration_seconds REAL,
    status TEXT DEFAULT 'running',
    summary_json TEXT
);

{
    "platform": "github",           # youtube, reddit, github, arxiv, x, instagram, tiktok, rss, hacker_news
    "canonical_id": "owner/repo",   # platform-specific unique key used to build canonical_key
    "title": "Item title",
    "url": "https://...",
    "author": "name or handle",
    "channel_or_source": "optional label",
    "published_at": "ISO8601 or None",
    "snippet": "first 300 chars of body",
    "raw_json": {},
    "metrics": {
        "stars": None,
        "forks": None,
        "score": None,
        "comments": None,
        "views": None,
        "likes": None,
        "shares": None,
        "saves": None
    }
}

import feedparser

def fetch_youtube_channel(channel_id, name, since_hours=48):
    url = f"https://www.youtube.com/feeds/videos.xml?channel_id={channel_id}"
    d = feedparser.parse(url)
    items = []
    for entry in d.entries[:10]:
        video_id = entry.get("yt_videoid", "")
        if not is_recent(entry.get("published", ""), since_hours):
            continue
        items.append({
            "platform": "youtube",
            "canonical_id": video_id,
            "title": entry.title,
            "url": f"https://www.youtube.com/watch?v={video_id}",
            "author": name,
            "channel_or_source": name,
            "published_at": entry.get("published"),
            "snippet": entry.get("summary", "")[:300],
            "metrics": {}
        })
    return items

import urllib.request, json, datetime as dt

def fetch_subreddit(subreddit, limit=25, min_score=20):
    url = f"https://www.reddit.com/r/{subreddit}/.json?limit={limit}&t=day"
    req = urllib.request.Request(url, headers={"User-Agent": "research-agent/1.0"})
    with urllib.request.urlopen(req, timeout=15) as r:
        data = json.loads(r.read())
    items = []
    for post in data["data"]["children"]:
        p = post["data"]
        if p.get("score", 0) < min_score:
            continue
        items.append({
            "platform": "reddit",
            "canonical_id": p["id"],
            "title": p["title"],
            "url": f"https://reddit.com{p['permalink']}",
            "author": p.get("author", ""),
            "channel_or_source": subreddit,
            "published_at": dt.datetime.utcfromtimestamp(p["created_utc"]).isoformat(),
            "snippet": p.get("selftext", "")[:300],
            "metrics": {"score": p["score"], "comments": p["num_comments"]}
        })
    return items

import urllib.request, json, urllib.parse, os

def fetch_github(query, max_results=10):
    token = os.environ.get("GITHUB_TOKEN", "")
    headers = {"Accept": "application/vnd.github.v3+json"}
    if token:
        headers["Authorization"] = f"token {token}"
    encoded = urllib.parse.quote(query)
    url = f"https://api.github.com/search/repositories?q={encoded}&sort=stars&order=desc&per_page={max_results}"
    req = urllib.request.Request(url, headers=headers)
    with urllib.request.urlopen(req, timeout=15) as r:
        data = json.loads(r.read())
    items = []
    for repo in data.get("items", []):
        items.append({
            "platform": "github",
            "canonical_id": repo["full_name"],
            "title": repo["full_name"],
            "url": repo["html_url"],
            "author": repo["owner"]["login"],
            "channel_or_source": query,
            "published_at": repo.get("pushed_at"),
            "snippet": (repo.get("description") or "")[:300],
            "metrics": {"stars": repo["stargazers_count"], "forks": repo["forks_count"]}
        })
    return items

import urllib.request, json, datetime as dt

def fetch_hn(limit=30, min_score=50):
    with urllib.request.urlopen("https://hacker-news.firebaseio.com/v0/topstories.json", timeout=10) as r:
        ids = json.loads(r.read())[:limit]
    items = []
    for item_id in ids:
        try:
            with urllib.request.urlopen(f"https://hacker-news.firebaseio.com/v0/item/{item_id}.json", timeout=5) as r:
                item = json.loads(r.read())
            if item.get("score", 0) < min_score:
                continue
            items.append({
                "platform": "hacker_news",
                "canonical_id": str(item_id),
                "title": item.get("title", ""),
                "url": item.get("url", f"https://news.ycombinator.com/item?id={item_id}"),
                "author": item.get("by", ""),
                "channel_or_source": "hacker_news",
                "published_at": dt.datetime.utcfromtimestamp(item.get("time", 0)).isoformat(),
                "snippet": "",
                "metrics": {"score": item["score"], "comments": item.get("descendants", 0)}
            })
        except Exception:
            continue
    return items

import urllib.request, urllib.parse, xml.etree.ElementTree as ET

def fetch_arxiv(query, max_results=10):
    encoded = urllib.parse.quote(query)
    url = f"http://export.arxiv.org/api/query?search_query={encoded}&max_results={max_results}&sortBy=submittedDate"
    with urllib.request.urlopen(url, timeout=20) as r:
        root = ET.fromstring(r.read())
    ns = {"atom": "http://www.w3.org/2005/Atom"}
    items = []
    for entry in root.findall("atom:entry", ns):
        arxiv_id = entry.find("atom:id", ns).text.split("/abs/")[-1]
        items.append({
            "platform": "arxiv",
            "canonical_id": arxiv_id,
            "title": entry.find("atom:title", ns).text.strip(),
            "url": entry.find("atom:id", ns).text.strip(),
            "author": (entry.find("atom:author/atom:name", ns) or ET.Element("x")).text or "",
            "channel_or_source": "arxiv",
            "published_at": entry.find("atom:published", ns).text,
            "snippet": entry.find("atom:summary", ns).text.strip()[:300],
            "metrics": {}
        })
    return items

import feedparser, hashlib

def fetch_rss(url, name, max_items=10):
    d = feedparser.parse(url)
    items = []
    for entry in d.entries[:max_items]:
        link = entry.get("link", "")
        url_hash = hashlib.sha256(link.encode()).hexdigest()[:16]
        items.append({
            "platform": "rss",
            "canonical_id": url_hash,
            "title": entry.get("title", ""),
            "url": link,
            "author": entry.get("author", ""),
            "channel_or_source": name,
            "published_at": entry.get("published", ""),
            "snippet": entry.get("summary", "")[:300],
            "metrics": {}
        })
    return items

def github_velocity(repo, now):
    created_at = parse_time(repo["created_at"])
    days_old = max((now - created_at).total_seconds() / 86400, 0.1)
    return (repo.get("stargazers_count") or 0) / days_old