schema.sql

CREATE TABLE IF NOT EXISTS papers (
    arxiv_id TEXT PRIMARY KEY,
    title TEXT NOT NULL,
    authors TEXT NOT NULL,
    abstract TEXT NOT NULL,
    source TEXT NOT NULL,  -- 'arxiv' or 'huggingface'
    categories TEXT,
    pdf_url TEXT,
    html_url TEXT,
    published_date TEXT NOT NULL,
    created_at TEXT DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS daily_picks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    paper_id TEXT NOT NULL REFERENCES papers(arxiv_id),
    date TEXT NOT NULL,
    keyword_score REAL,
    llm_score REAL,
    llm_reason TEXT,
    bitable_record_id TEXT,
    created_at TEXT DEFAULT (datetime('now')),
    UNIQUE(paper_id, date)
);

CREATE TABLE IF NOT EXISTS user_marks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    paper_id TEXT NOT NULL REFERENCES papers(arxiv_id),
    marked_at TEXT,
    report_status TEXT DEFAULT 'pending',  -- pending/generating/done
    report_doc_url TEXT,
    created_at TEXT DEFAULT (datetime('now')),
    UNIQUE(paper_id)
);