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.

15.5 KB
Raw Blame
"""
Core research models for tasks, queries, and results.
"""

import enum
import json

from loguru import logger
from sqlalchemy import (
    JSON,
    Column,
    Enum,
    Float,
    ForeignKey,
    Index,
    Integer,
    String,
    Text,
    text,
)
from sqlalchemy.orm import relationship
from sqlalchemy.sql import operators
from sqlalchemy.types import TypeDecorator

from sqlalchemy_utc import UtcDateTime, utcnow

from ...constants import ResearchStatus
from .base import Base


class LenientJSON(TypeDecorator):
    """Read malformed legacy JSON text as absent without changing writes.

    Releases before v0.5 persisted this column as TEXT through
    ``json.dumps``, and the stock ``JSON`` result processor raises
    ``JSONDecodeError`` while such a row is being loaded whenever the stored
    text is not valid JSON (the empty string from an interrupted write, a
    truncated ``json.dumps`` output, a ``str(dict)`` repr). That raise
    happens during the query, above every handler ``try``, so
    ``GET /history/status/{research_id}`` answered 500 for the row instead of
    degrading to an empty log or metadata (#6533, #7017). A malformed
    ``research_meta`` value could also make one row take down the entire
    history list.

    Reads therefore return ``None`` for an unparseable value — the absent
    value readers of both columns already handle — and the load can no
    longer fail. Writes are untouched: the stock ``JSON`` bind path still
    serializes Python objects, so the DDL and the stored format are
    unchanged and no migration is needed.
    """

    impl = JSON
    cache_ok = True

    def coerce_compared_value(self, op, value):
        # TypeDecorator otherwise JSON-encodes a LIKE pattern as a JSON
        # string. Subscription lookups compare against the stored TEXT form,
        # so their pattern must be bound as an ordinary SQL string. Delegate
        # other operators to JSON to preserve its key/index coercion rules.
        if op in (
            operators.like_op,
            operators.not_like_op,
            operators.ilike_op,
            operators.not_ilike_op,
        ):
            return String()
        return self.impl.coerce_compared_value(op, value)

    def result_processor(self, dialect, coltype):
        def process(value):
            # Only TEXT carriers need decoding. A backend with a native
            # JSON type hands back an already-parsed object — list, dict,
            # or a scalar such as 5 / true / null — and must not be fed to
            # json.loads, which would reject the non-string scalars and
            # turn a perfectly valid value into None. LDR ships on
            # SQLite/SQLCipher only, so this is defensive, not load-bearing.
            if not isinstance(value, (str, bytes, bytearray)):
                return value
            try:
                return json.loads(value)
            except (TypeError, ValueError):
                logger.warning(
                    "Unparseable JSON value in a research_history column; "
                    "treating it as absent"
                )
                return None

        return process


class ResearchTask(Base):
    """
    Main research tasks that users create.
    This is the top-level object that contains all research activities.
    """

    __tablename__ = "research_tasks"

    id = Column(Integer, primary_key=True)
    title = Column(String(500), nullable=False)
    description = Column(Text)
    status = Column(
        String(50), default="pending"
    )  # pending, in_progress, completed, failed
    priority = Column(Integer, default=0)  # Higher number = higher priority
    tags = Column(JSON)  # List of tags for categorization
    research_metadata = Column(JSON)  # Flexible field for additional data

    # Timestamps
    created_at = Column(UtcDateTime, default=utcnow())
    updated_at = Column(UtcDateTime, default=utcnow(), onupdate=utcnow())
    started_at = Column(UtcDateTime)
    completed_at = Column(UtcDateTime)

    # Relationships
    searches = relationship(
        "SearchQuery",
        back_populates="research_task",
        cascade="all, delete-orphan",
    )
    results = relationship(
        "SearchResult",
        back_populates="research_task",
        cascade="all, delete-orphan",
    )
    reports = relationship(
        "Report", back_populates="research_task", cascade="all, delete-orphan"
    )

    def __repr__(self):
        return f"<ResearchTask(title='{self.title}', status='{self.status}')>"


class SearchQuery(Base):
    """
    Individual search queries executed as part of research tasks.
    Tracks what was searched and when.
    """

    __tablename__ = "search_queries"

    id = Column(Integer, primary_key=True)
    research_task_id = Column(
        Integer, ForeignKey("research_tasks.id", ondelete="CASCADE")
    )
    query = Column(Text, nullable=False)
    search_engine = Column(String(50))  # google, bing, duckduckgo, etc.
    search_type = Column(String(50))  # web, academic, news, etc.
    parameters = Column(JSON)  # Additional search parameters

    # Status tracking
    status = Column(
        String(50), default="pending"
    )  # pending, executing, completed, failed
    error_message = Column(Text)
    retry_count = Column(Integer, default=0)

    # Timestamps
    created_at = Column(UtcDateTime, default=utcnow())
    executed_at = Column(UtcDateTime)
    completed_at = Column(UtcDateTime)

    # Relationships
    research_task = relationship("ResearchTask", back_populates="searches")
    results = relationship(
        "SearchResult",
        back_populates="search_query",
        cascade="all, delete-orphan",
    )

    # Indexes for performance
    __table_args__ = (
        Index("idx_research_task_status", "research_task_id", "status"),
        Index("idx_search_engine", "search_engine", "status"),
    )

    def __repr__(self):
        return f"<SearchQuery(query='{self.query[:50]}...', status='{self.status}')>"


class SearchResult(Base):
    """
    Individual search results from queries.
    Stores both the initial result and any fetched content.
    """

    __tablename__ = "search_results"

    id = Column(Integer, primary_key=True)
    research_task_id = Column(
        Integer, ForeignKey("research_tasks.id", ondelete="CASCADE")
    )
    search_query_id = Column(
        Integer, ForeignKey("search_queries.id", ondelete="CASCADE")
    )

    # Basic result information
    title = Column(String(500))
    url = Column(Text, index=True)  # Indexed for deduplication
    snippet = Column(Text)

    # Extended content
    content = Column(Text)  # Full content if fetched
    content_type = Column(String(50))  # html, pdf, text, etc.
    content_hash = Column(String(64))  # For deduplication

    # Metadata
    relevance_score = Column(Float)  # Calculated relevance
    position = Column(Integer)  # Position in search results
    domain = Column(String(255), index=True)
    language = Column(String(10))
    published_date = Column(UtcDateTime)
    author = Column(String(255))

    # Status tracking
    fetch_status = Column(String(50))  # pending, fetched, failed, skipped
    fetch_error = Column(Text)

    # Timestamps
    created_at = Column(UtcDateTime, default=utcnow())
    fetched_at = Column(UtcDateTime)

    # Relationships
    research_task = relationship("ResearchTask", back_populates="results")
    search_query = relationship("SearchQuery", back_populates="results")

    # Indexes for performance
    __table_args__ = (
        Index("idx_task_relevance", "research_task_id", "relevance_score"),
        Index("idx_content_hash", "content_hash"),
        Index("idx_domain_task", "domain", "research_task_id"),
    )

    def __repr__(self):
        return f"<SearchResult(title='{self.title[:50] if self.title else 'No title'}...', score={self.relevance_score})>"


class ResearchMode(enum.Enum):
    """Research modes available."""

    QUICK = "quick"
    DETAILED = "detailed"


class ResearchResource(Base):
    """Resources associated with research projects."""

    __tablename__ = "research_resources"

    id = Column(Integer, primary_key=True, autoincrement=True)
    # Named Index() in __table_args__ below (NOT index=True on this
    # column) so both the create_all path (fresh installs, test fixtures)
    # and the migration path (0006:286-289) produce an identically-
    # named index: ix_research_resources_research_id. Using index=True
    # would give create_all an auto-named index that diverges from the
    # migration's named one; that asymmetry previously meant fresh
    # installs had no research_id index and ran full-table scans on
    # 20+ call sites.
    research_id = Column(
        String(36),
        ForeignKey("research_history.id", ondelete="CASCADE"),
        nullable=False,
    )
    title = Column(Text)
    url = Column(Text)
    content_preview = Column(Text)
    source_type = Column(Text)
    resource_metadata = Column("metadata", JSON)
    created_at = Column(String, nullable=False)
    # `use_alter=True` breaks the model-level circular FK with Document
    # (Document.resource_id → research_resources.id, ResearchResource.document_id
    # → documents.id). Without it, every cold start emits an SAWarning from
    # `Base.metadata.sorted_tables` because SQLAlchemy can't topologically
    # order the two tables. With `use_alter=True`, this FK is emitted as a
    # post-CREATE ALTER TABLE so both tables exist by the time the
    # constraint is added. Migration 0005 already chose not to enforce
    # this FK at the DB level on existing DBs (SQLite batch-alter
    # limitation), so this only changes how `create_all()` emits the FK
    # for fresh installs going forward.
    document_id = Column(
        String(36),
        ForeignKey(
            "documents.id",
            ondelete="SET NULL",
            use_alter=True,
            name="fk_research_resources_document_id",
        ),
        nullable=True,
        index=True,
    )

    # Relationships
    research = relationship("ResearchHistory", back_populates="resources")
    document = relationship("Document", foreign_keys=[document_id])
    paper_appearance = relationship(
        "PaperAppearance",
        back_populates="resource",
        uselist=False,
        cascade="all, delete-orphan",
    )

    __table_args__ = (
        Index("ix_research_resources_research_id", "research_id"),
    )

    def __repr__(self):
        return f"<ResearchResource(title='{self.title}', url='{self.url}')>"


class ResearchHistory(Base):
    """
    Research history table.
    Tracks research sessions and their progress.
    """

    __tablename__ = "research_history"

    # UUID as primary key
    id = Column(String(36), primary_key=True)
    # The search query.
    query = Column(Text, nullable=False)
    # The mode of research (e.g., 'quick_summary', 'detailed_report').
    mode = Column(Text, nullable=False)
    # Current status of the research.
    status = Column(Text, nullable=False)
    # The timestamp when the research started.
    created_at = Column(Text, nullable=False)
    # The timestamp when the research was completed.
    completed_at = Column(Text)
    # Duration of the research in seconds.
    duration_seconds = Column(Integer)
    # Path to the generated report.
    report_path = Column(Text)
    # Report content stored in database
    report_content = Column(Text)
    # Additional metadata about the research.
    research_meta = Column(LenientJSON)
    # Latest progress log message.
    progress_log = Column(LenientJSON)
    # Current progress of the research (as a percentage).
    progress = Column(Integer)
    # Title of the research report.
    title = Column(Text)

    # Optional link to chat session that triggered this research.
    # Named Index() in __table_args__ below (NOT index=True on this
    # column) so both the create_all path (fresh installs, test
    # fixtures) and the migration path (in migration 0010) produce an
    # identically-named index: ix_research_history_chat_session_id.
    # The matching ResearchResource pattern above documents the same
    # divergence risk in detail.
    chat_session_id = Column(
        String(36),
        ForeignKey("chat_sessions.id", ondelete="SET NULL"),
        nullable=True,
    )

    # Atomic counter for ChatService.add_progress_step's per-research
    # sequence_number allocation (mirrors ChatSession.message_count).
    step_count = Column(Integer, nullable=False, default=0, server_default="0")

    # Relationships
    resources = relationship(
        "ResearchResource",
        back_populates="research",
        cascade="all, delete-orphan",
    )
    chat_session = relationship(
        "ChatSession",
        foreign_keys=[chat_session_id],
        back_populates="researches",
    )
    chat_messages = relationship(
        "ChatMessage",
        back_populates="research",
        passive_deletes=True,
    )
    progress_steps = relationship(
        "ChatProgressStep",
        back_populates="research",
        passive_deletes=True,
    )

    __table_args__ = (
        Index("ix_research_history_chat_session_id", "chat_session_id"),
        # Partial unique index closing the SELECT-then-INSERT race in
        # chat/routes.py::send_message — only one in-progress research
        # per chat session, NULL chat_session_id rows unconstrained.
        # Migration 0010 mirrors this index exactly.
        Index(
            "ux_research_history_chat_session_in_progress",
            "chat_session_id",
            unique=True,
            sqlite_where=text(
                "status = 'in_progress' AND chat_session_id IS NOT NULL"
            ),
            postgresql_where=text(
                "status = 'in_progress' AND chat_session_id IS NOT NULL"
            ),
        ),
    )

    def __repr__(self):
        return f"<ResearchHistory(query='{self.query[:50]}...', status={self.status})>"


class Research(Base):
    """
    Modern research tracking with better type safety.
    """

    __tablename__ = "research"

    id = Column(Integer, primary_key=True)
    query = Column(String, nullable=False)
    status = Column(
        Enum(ResearchStatus), default=ResearchStatus.PENDING, nullable=False
    )
    mode = Column(
        Enum(ResearchMode), default=ResearchMode.QUICK, nullable=False
    )
    created_at = Column(UtcDateTime, server_default=utcnow(), nullable=False)
    updated_at = Column(
        UtcDateTime, server_default=utcnow(), onupdate=utcnow(), nullable=False
    )
    progress = Column(Float, default=0.0, nullable=False)
    start_time = Column(UtcDateTime, nullable=True)
    end_time = Column(UtcDateTime, nullable=True)
    error_message = Column(Text, nullable=True)

    def __repr__(self):
        return f"<Research(query='{self.query[:50]}...', status={self.status.value})>"


class ResearchStrategy(Base):
    """
    Track which search strategy was used for each research.
    """

    __tablename__ = "research_strategies"

    id = Column(Integer, primary_key=True)
    # FK targets research_history.id (the live UUID-keyed table) rather than
    # the dormant `research` table. save_research_strategy passes the
    # research_history UUID, so the prior Integer FK to research.id raised
    # FOREIGN KEY constraint failed on every commit once v1.6.0 turned on
    # PRAGMA foreign_keys.
    research_id = Column(
        String(36),
        ForeignKey("research_history.id", ondelete="CASCADE"),
        nullable=False,
        unique=True,
        index=True,
    )
    strategy_name = Column(String(100), nullable=False, index=True)
    created_at = Column(UtcDateTime, server_default=utcnow(), nullable=False)

    def __repr__(self):
        return f"<ResearchStrategy(research_id={self.research_id}, strategy={self.strategy_name})>"