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.

16.3 KB
Raw Blame
"""
Note-specific models for AI-connected notes feature.

Notes are stored as Documents with source_type='note'. This module contains
auxiliary tables for note-specific features:
- Version history
- Wiki-style linking
- Research integration
- Synthesis tracking

Naming note: the `note_*` table names are a service-layer scoping
convention — every FK actually points at `documents.id`, not at a dedicated
notes table. Renaming to `document_*` was considered during review since the
shapes (versions, links, research-pins) could apply to any Document, but
`change_type` enum values like `auto_save` / `pre_restore` are note-specific
semantics and would be meaningless for research-downloaded PDFs. Revisit the
naming only when there's a real second use case for versioning/linking on
other Document subtypes.
"""

import enum

from sqlalchemy import (
    JSON,
    Boolean,
    CheckConstraint,
    Column,
    Enum,
    ForeignKey,
    Index,
    Integer,
    String,
    Text,
    UniqueConstraint,
    text,
)
from sqlalchemy.orm import backref, relationship
from sqlalchemy_utc import UtcDateTime, utcnow

from .base import Base


class NoteChangeType(str, enum.Enum):
    """Valid values for NoteVersion.change_type."""

    INITIAL = "initial"
    AUTO_SAVE = "auto_save"
    MANUAL_SAVE = "manual_save"
    PRE_RESTORE = "pre_restore"
    RESTORE = "restore"


class NoteSynthesisType(str, enum.Enum):
    """Valid values for NoteSynthesis.synthesis_type."""

    MERGE = "merge"
    SUMMARIZE = "summarize"
    COMPARE = "compare"


class NoteVersion(Base):
    """
    Stores version history for notes (documents with source_type='note').
    Enables undo, restore, and semantic change tracking.
    """

    __tablename__ = "note_versions"

    # UUID PK (String(36)) rather than autoincrement Integer — versions may
    # be URL-shared / bookmarked, so IDs are externally visible. The other
    # note junction tables use Integer autoincrement because they're
    # internal-only; the asymmetry is intentional.
    id = Column(String(36), primary_key=True)

    # The document (note) this version belongs to
    # Indexed via the composite idx_note_version_created below.
    document_id = Column(
        String(36),
        ForeignKey("documents.id", ondelete="CASCADE"),
        nullable=False,
    )

    # Snapshot of the note at this version
    title = Column(String(500), nullable=False)
    content = Column(Text, nullable=False)
    tags = Column(
        JSON, nullable=False, default=list, server_default=text("'[]'")
    )

    # Change metadata (see NoteChangeType)
    change_type = Column(
        Enum(
            NoteChangeType,
            values_callable=lambda obj: [e.value for e in obj],
            name="note_change_type",
        ),
        nullable=False,
    )
    change_summary = Column(
        Text, nullable=True
    )  # LLM-generated description of changes
    content_hash = Column(
        String(64), nullable=False
    )  # SHA-256 for deduplication

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

    # Relationships
    document = relationship(
        "Document",
        backref=backref("note_versions", passive_deletes=True),
    )

    __table_args__ = (
        Index("idx_note_version_created", "document_id", "created_at"),
        UniqueConstraint(
            "document_id", "content_hash", name="uix_note_version_content"
        ),
        # Mirror NoteChangeType members above. The ORM Enum() with
        # values_callable suppresses SQLAlchemy's auto-CHECK, so declare
        # it explicitly here — parity with the migration.
        CheckConstraint(
            "change_type IN ('initial', 'auto_save', 'manual_save', "
            "'pre_restore', 'restore')",
            name="ck_note_change_type",
        ),
    )

    def __repr__(self):
        doc_id = self.document_id[:8] if self.document_id else "None"
        return f"<NoteVersion(document_id={doc_id}, id={self.id[:8] if self.id else 'None'})>"


class NoteLink(Base):
    """
    Tracks wiki-style [[links]] between notes (documents with source_type='note').
    Enables backlinks feature and knowledge graph visualization.
    """

    __tablename__ = "note_links"

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

    # Source document (the note containing the link).
    # Not individually indexed — the UNIQUE(source, target) constraint
    # below produces a usable index for single-column lookups on source.
    source_document_id = Column(
        String(36),
        ForeignKey("documents.id", ondelete="CASCADE"),
        nullable=False,
    )

    # Target document (the note being linked to).
    # NOT individually indexed here — the explicit `idx_note_link_target`
    # below in __table_args__ is the source of truth for this index.
    # Pre-fix the column also carried `index=True`, which made
    # SQLAlchemy create a second (auto-named) index over the same
    # column. Migration 0021 already only creates `idx_note_link_target`,
    # so production DBs are fine; this aligns the ORM declaration with
    # what actually lives on disk.
    target_document_id = Column(
        String(36),
        ForeignKey("documents.id", ondelete="CASCADE"),
        nullable=False,
    )

    # The text used in the link (e.g., [[My Note Title]])
    link_text = Column(String(500), nullable=False)

    # Whether this link was auto-suggested by AI
    auto_suggested = Column(
        Boolean, default=False, server_default=text("0"), nullable=False
    )

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

    # Relationships
    source_document = relationship(
        "Document",
        foreign_keys=[source_document_id],
        backref=backref("outgoing_note_links", passive_deletes=True),
    )
    target_document = relationship(
        "Document",
        foreign_keys=[target_document_id],
        backref=backref("incoming_note_links", passive_deletes=True),
    )

    # Ensure unique links (one link per source-target pair) and prevent self-links
    __table_args__ = (
        UniqueConstraint(
            "source_document_id", "target_document_id", name="uix_note_link"
        ),
        Index("idx_note_link_target", "target_document_id"),
        CheckConstraint(
            "source_document_id != target_document_id",
            name="ck_note_link_no_self",
        ),
    )

    def __repr__(self):
        src_id = (
            self.source_document_id[:8] if self.source_document_id else "None"
        )
        tgt_id = (
            self.target_document_id[:8] if self.target_document_id else "None"
        )
        return f"<NoteLink(source={src_id}, target={tgt_id})>"


class NoteResearch(Base):
    """
    Junction table tracking research runs triggered from notes.
    Links notes (documents) to their research results.
    """

    __tablename__ = "note_research"

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

    # Foreign keys
    # document_id is indexed by the uix_note_research_display_order UNIQUE
    # (it leads with document_id), so no separate index is needed.
    document_id = Column(
        String(36),
        ForeignKey("documents.id", ondelete="CASCADE"),
        nullable=False,
    )
    research_id = Column(
        String(36),
        ForeignKey("research_history.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )

    # Display settings
    display_order = Column(
        Integer, default=0, server_default=text("0"), nullable=False
    )
    is_collapsed = Column(
        Boolean, default=False, server_default=text("0"), nullable=False
    )

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

    # Relationships
    document = relationship(
        "Document",
        backref=backref("note_research_runs", passive_deletes=True),
    )
    research = relationship(
        "ResearchHistory",
        backref=backref("note_context", passive_deletes=True),
    )

    # Ensure one entry per document-research pair, and prevent
    # duplicate display_order from a concurrent-insert race.
    # `link_research_to_note` computes MAX(display_order)+1 then INSERTs;
    # without the second UniqueConstraint two concurrent calls would
    # both observe the same MAX and insert with the same display_order.
    # The service-layer fix retries on IntegrityError; the constraint
    # is what makes the retry necessary (and safe).
    __table_args__ = (
        UniqueConstraint(
            "document_id", "research_id", name="uix_note_research"
        ),
        # uix_note_research_display_order leads with document_id, so its
        # backing btree already serves every (document_id [, display_order])
        # read — get_note_research's ORDER BY and link_research's
        # MAX(display_order) WHERE document_id. A separate
        # idx_note_research_order over the identical columns would be a
        # redundant second physical index (dead weight on the reorder
        # write path), so it is intentionally omitted — same reasoning the
        # note_links table applies to its source column.
        UniqueConstraint(
            "document_id",
            "display_order",
            name="uix_note_research_display_order",
        ),
    )

    def __repr__(self):
        doc_id = self.document_id[:8] if self.document_id else "None"
        res_id = self.research_id[:8] if self.research_id else "None"
        return f"<NoteResearch(document_id={doc_id}, research_id={res_id})>"


class NoteReference(Base):
    """Generic note → target reference with an optional quote anchor.

    A row records that a note refers to a TARGET — a library document or
    a research run (exactly one, CHECK-enforced). With a ``quote`` it is
    an inline annotation: the quoted passage of the target's immutable
    rendered text is highlighted, with short ``prefix``/``suffix``
    context disambiguating repeated phrases (Hypothesis-style
    TextQuoteSelector). Without a quote it is a plain "this note is
    about that target" link, powering the target page's Notes panel.

    NoteResearch remains the note-side pinning of research runs (with
    per-note display ordering); this table is the target-side reference
    layer both documents and research share.
    """

    __tablename__ = "note_references"

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

    note_id = Column(
        String(36),
        ForeignKey("documents.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    target_document_id = Column(
        String(36),
        ForeignKey("documents.id", ondelete="CASCADE"),
        nullable=True,
        index=True,
    )
    target_research_id = Column(
        String(36),
        ForeignKey("research_history.id", ondelete="CASCADE"),
        nullable=True,
        index=True,
    )

    # Anchor (NULL quote = anchor-less reference).
    quote = Column(Text, nullable=True)
    prefix = Column(Text, nullable=True)
    suffix = Column(Text, nullable=True)

    created_at = Column(
        UtcDateTime,
        default=utcnow(),
        server_default=utcnow(),
        nullable=False,
    )

    note = relationship(
        "Document",
        foreign_keys=[note_id],
        backref=backref("outgoing_references", passive_deletes=True),
    )
    target_document = relationship(
        "Document",
        foreign_keys=[target_document_id],
        backref=backref("note_annotations", passive_deletes=True),
    )
    target_research = relationship(
        "ResearchHistory",
        backref=backref("note_annotations", passive_deletes=True),
    )

    __table_args__ = (
        # Exactly one target — mirrors ck_note_reference_single_target in
        # migration 0022 (three-way parity guarded by tests, same house
        # style as the note_syntheses enum CHECK).
        CheckConstraint(
            "(target_document_id IS NOT NULL) + "
            "(target_research_id IS NOT NULL) = 1",
            name="ck_note_reference_single_target",
        ),
    )

    def __repr__(self):
        note_id = self.note_id[:8] if self.note_id else "None"
        target = self.target_document_id or self.target_research_id or "None"
        return (
            f"<NoteReference(note={note_id}, target={target[:8]}, "
            f"anchored={self.quote is not None})>"
        )


class NoteSynthesis(Base):
    """
    Tracks note synthesis operations (merge, compare, summarize).
    Links synthesized notes to their source notes via NoteSynthesisSource.
    """

    __tablename__ = "note_syntheses"

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

    # The resulting synthesized note (document).
    # `ondelete='SET NULL'` (not CASCADE) preserves the audit record even
    # after the result note is deleted. CASCADE was considered because no
    # route currently reads syntheses, but destroying the record — and its
    # junction rows tying each source note to this operation — loses more
    # than it's worth: a future "what AI operations have I run?" UI can
    # read NULL-result rows meaningfully.
    result_document_id = Column(
        String(36),
        ForeignKey("documents.id", ondelete="SET NULL"),
        nullable=True,
        index=True,
    )

    # Type of synthesis performed. Enum with `values_callable` mirrors the
    # house style used by NoteChangeType (and DocumentStatus / EmbeddingProvider
    # elsewhere): suppresses SQLAlchemy's auto-CHECK so the sole DB-level
    # enforcement is the explicit CheckConstraint in __table_args__. When
    # NoteSynthesisType grows, update both literals; TestEnumCheckParity
    # in tests/database/test_migration_0021_note_tables.py guards the parity.
    synthesis_type = Column(
        Enum(
            NoteSynthesisType,
            values_callable=lambda obj: [e.value for e in obj],
            name="note_synthesis_type",
        ),
        nullable=False,
    )

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

    # Relationships
    result_document = relationship(
        "Document", foreign_keys=[result_document_id]
    )
    sources = relationship(
        "NoteSynthesisSource",
        cascade="all, delete-orphan",
        backref=backref("synthesis"),
    )

    __table_args__ = (
        # Mirror NoteSynthesisType members above. The ORM Enum() with
        # values_callable suppresses SQLAlchemy's auto-CHECK, so declare
        # it explicitly here — parity with the migration.
        CheckConstraint(
            "synthesis_type IN ('merge', 'summarize', 'compare')",
            name="ck_note_synthesis_type",
        ),
    )

    def __repr__(self):
        return f"<NoteSynthesis(type={self.synthesis_type}, sources={len(self.sources)})>"


class NoteSynthesisSource(Base):
    """
    Junction table linking a NoteSynthesis to its source Documents.

    Replaces an earlier JSON-list column on NoteSynthesis (source_document_ids)
    that lacked FK integrity — the author's original TODO on migration 0006.
    Do NOT revert to a JSON list "for simplicity": it re-opens the stale-ID
    class of bug, where deleting a source note leaves dangling references in
    the synthesis record forever.
    """

    __tablename__ = "note_synthesis_sources"

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

    synthesis_id = Column(
        Integer,
        ForeignKey("note_syntheses.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    source_document_id = Column(
        String(36),
        ForeignKey("documents.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )

    created_at = Column(
        UtcDateTime,
        default=utcnow(),
        server_default=utcnow(),
        nullable=False,
    )

    source_document = relationship(
        "Document", foreign_keys=[source_document_id]
    )

    __table_args__ = (
        UniqueConstraint(
            "synthesis_id",
            "source_document_id",
            name="uix_note_synthesis_source",
        ),
    )

    def __repr__(self):
        syn_id = self.synthesis_id if self.synthesis_id is not None else "None"
        src_id = (
            self.source_document_id[:8] if self.source_document_id else "None"
        )
        return f"<NoteSynthesisSource(synthesis={syn_id}, source={src_id})>"