Skip to content

~95% on SimpleQA (e.g. Qwen3.6-27B on a 3090). Supports all local and cloud LLMs (llama.cpp, Ollama, Google, ...). 10+ search engines - arXiv, PubMed, your private documents. Everything Local & Encrypted.

7.9 KB
Raw Blame
"""
Deduplicated academic paper metadata and appearance tracking.

``Paper`` stores bibliographic data once per unique paper (deduped by
DOI / arXiv ID / PMID). ``PaperAppearance`` links papers to the
research resources that found them (many-to-one: many resources can
reference the same paper).

Journal quality is NEVER stored here — always derived at query time
via ``journal_id`` → ``journals`` table → reference DB.
"""

from sqlalchemy import (
    JSON,
    Column,
    ForeignKey,
    Index,
    Integer,
    String,
)
from sqlalchemy.orm import relationship
from sqlalchemy_utc import UtcDateTime, utcnow

from .base import Base


class Paper(Base):
    """A unique academic paper, deduplicated by DOI/arXiv ID/PMID.

    Created the first time a paper is encountered in any research
    session. If the same paper is found again (same DOI, or same
    arXiv ID when DOI is absent), the existing row is reused and
    any missing identifiers are merged in.

    Schema is minimal by design: only the columns used for dedup
    lookups (``doi``, ``arxiv_id``, ``pmid``), dashboard joins
    (``journal_id``, ``container_title``), and publication year for
    indexed filtering are real columns. Everything else (authors,
    volume, CSL-JSON, ...) goes into the ``paper_metadata`` JSON blob,
    matching the hybrid relational-JSON pattern used by OpenAlex and
    Crossref.

    ``container_title`` is the cleaned journal name the filter used to
    score the paper (post-regex-clean + abbreviation-expand + optional
    LLM-relabel). Always populated when the filter scored the journal.
    Indexed so the dashboard can GROUP BY it and batch-enrich from the
    shared read-only reference DB.

    No per-Paper quality column by design: a frozen snapshot would go
    stale if a journal is re-scored (new LLM model, bug fix, manual
    override). Instead, the dashboard resolves current quality live —
    Tier 4 via ``journals.quality`` keyed by NFKC-normalized
    ``container_title``, Tier 1-3 via the bundled reference DB.
    Don't re-introduce a ``journal_quality`` column here.
    """

    __tablename__ = "papers"

    id = Column(Integer, primary_key=True, autoincrement=True)

    # Academic identifiers — used as waterfall dedup keys.
    # UNIQUE constraints prevent concurrent writers from creating
    # duplicate rows for the same paper. SQLite allows multiple NULL
    # values in a UNIQUE column (standard SQL behavior), so papers
    # without identifiers are still permitted — they just can't be
    # deduplicated via these keys. `unique=True` already creates a
    # backing index, so no separate `index=True` or explicit Index
    # entry is needed for these columns.
    #
    # KNOWN-DEFERRED: String(255) is sufficient for the vast majority
    # of real-world DOIs (CrossRef recommends <= 200 chars). A
    # pathological dataset DOI approaching 2000 chars would fail on
    # insert rather than silently corrupt. Tracked as a post-merge
    # follow-up to bump to String(512) with a truncation guard in
    # _extract_doi.
    doi = Column(String(255), nullable=True, unique=True)
    arxiv_id = Column(String(100), nullable=True, unique=True)
    pmid = Column(String(50), nullable=True, unique=True)

    # Venue link for quality lookups (quality derived at query time).
    # Named index declared in __table_args__ below.
    journal_id = Column(
        Integer,
        ForeignKey("journals.id", ondelete="SET NULL"),
        nullable=True,
    )

    # Cleaned journal name (post-regex-clean / abbreviation-expand /
    # optional LLM-relabel) that keyed the filter's score. Dashboard
    # GROUP BY key. Named index declared in __table_args__ below.
    container_title = Column(String(500), nullable=True)

    # NOTE: no ``journal_quality`` column — see class docstring above.
    # Quality is resolved live at render time from journals.quality
    # (Tier 4) or the bundled reference DB (Tier 1-3) so that a
    # re-scored journal propagates to old papers automatically.

    # Publication year. Promoted out of the metadata JSON blob into a
    # first-class integer column so the dashboard can filter/group/sort
    # by year without paying for json_extract on every row. Named index
    # declared in __table_args__ below. Always written alongside the
    # JSON copy in paper_metadata so existing readers keep working;
    # this column is a denormalized index surface.
    year = Column(Integer, nullable=True)

    # Bibliographic fields (authors, volume, pages, container_title,
    # publisher, item_type, pmcid, csl_json, ...) stored as a single
    # JSON blob. Note: ``year`` is ALSO duplicated here as the CSL-JSON
    # source of truth; the first-class ``year`` column above is a
    # denormalized copy for indexed queries. Python attribute is
    # ``paper_metadata`` to avoid SQLAlchemy's reserved ``metadata``
    # attribute on declarative Base; the underlying column is still
    # named ``metadata`` in SQL. Mirrors the ResearchResource pattern
    # (``resource_metadata = Column("metadata", JSON)``).
    paper_metadata = Column("metadata", JSON, nullable=True)

    # Timestamps
    created_at = Column(UtcDateTime, default=utcnow(), nullable=False)
    updated_at = Column(
        UtcDateTime, default=utcnow(), onupdate=utcnow(), nullable=False
    )

    # Relationships
    appearances = relationship(
        "PaperAppearance",
        back_populates="paper",
        cascade="all, delete-orphan",
    )
    journal = relationship("Journal", backref="papers")

    __table_args__ = (
        Index("idx_papers_journal", "journal_id"),
        Index("idx_papers_container_title", "container_title"),
        Index("idx_papers_year", "year"),
    )

    def __repr__(self):
        return (
            f"<Paper("
            f"id={self.id}, "
            f"doi={self.doi!r}, "
            f"journal_id={self.journal_id})>"
        )


class PaperAppearance(Base):
    """Links a Paper to the ResearchResource that found it.

    Each search result (ResearchResource) can reference at most one
    paper. The same paper can appear across many research sessions,
    each creating a separate PaperAppearance row — but the paper's
    metadata is stored only once in the ``papers`` table.
    """

    __tablename__ = "paper_appearances"

    id = Column(Integer, primary_key=True, autoincrement=True)
    paper_id = Column(
        Integer,
        ForeignKey("papers.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    # unique=True enforces: each ResearchResource row appears in at
    # most one PaperAppearance. This is intentional — a resource
    # represents ONE search-result row for ONE paper. If a research
    # session retries and creates a new ResearchResource, it gets a
    # new resource_id and can link to a (possibly different) paper
    # without conflict. Removing this UNIQUE would allow the same
    # resource to claim multiple papers, which is nonsensical in the
    # domain model. unique=True already creates a backing unique
    # index — no separate index=True or explicit Index() entry.
    resource_id = Column(
        Integer,
        ForeignKey("research_resources.id", ondelete="CASCADE"),
        nullable=False,
        unique=True,
    )
    # Which search engine found this paper for this resource.
    # KNOWN-DEFERRED: currently written by normalize_citation output
    # but not read by any route or service. Retained for future
    # per-engine analytics (e.g., "how many predatory journals via
    # arxiv vs openalex"). Do not remove without confirming no planned
    # consumer exists.
    source_engine = Column(String(50), nullable=True)
    created_at = Column(UtcDateTime, default=utcnow(), nullable=False)

    # Relationships
    paper = relationship("Paper", back_populates="appearances")
    resource = relationship(
        "ResearchResource", back_populates="paper_appearance"
    )

    def __repr__(self):
        return (
            f"<PaperAppearance("
            f"paper_id={self.paper_id}, "
            f"resource_id={self.resource_id})>"
        )