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.

12.5 KB
Raw Blame

Notes — Data Model

How the Notes feature stores its data. The short version: a note is not its own table — it is a row in the existing documents table, and a handful of satellite note_* tables hang off it for versioning, links, research pins, references/annotations, and synthesis. This lets notes reuse the whole Library / RAG / embedding stack (collections, chunks, semantic search) for free.

See also: DATABASE_SCHEMA.md (whole-database schema), SEMANTIC_SEARCH.md, ../library-and-rag.md.

The core idea: a note is a Document

A note is a row in documents whose source_type_id resolves to the source_types row named note (NoteService lazily creates that SourceType — display_name="Note", icon="sticky-note" — the first time a note is made). There is no boolean/enum column on documents marking "is a note"; a Document is a note iff its source type is note.

The note's own content lives in Document columns:

Column Type Note usage
id String(36) UUID, PK the note id
source_type_id FK → source_types.id points at the note source type
title Text note title
text_content Text note body (markdown)
tags JSON user tags
favorite Boolean DB column stays favorite; the API/UI call it "pinned" — same column, two names by design
document_hash String(64), unique sha256(f"{note_id}:{content}")

Every note is also placed in a per-user system collection (collections.collection_type = "notes", name "Notes") via a document_collections join row. That collection is the note's permanent home — remove_from_collection refuses to unlink a note from it — and it is what puts notes on the same collection / RAG / semantic-search rails as uploaded documents and research reports. A note may additionally belong to any number of ordinary user collections.

Entity-relationship diagram

erDiagram
    source_types ||--o{ documents : "source_type_id"
    documents {
        string id PK "UUID"
        string source_type_id FK "note-type = a note"
        text   title
        text   text_content
        json   tags
        bool   favorite "aka pinned"
    }
    research_history {
        string id PK "UUID"
    }

    documents ||--o{ note_versions : "document_id (CASCADE)"
    note_versions {
        string id PK "UUID"
        string document_id FK
        string content_hash "sha256, dedup"
        enum   change_type "initial|auto_save|manual_save|pre_restore|restore"
        text   change_summary "LLM, async, nullable"
        datetime created_at
    }

    documents ||--o{ note_links : "source_document_id (CASCADE)"
    documents ||--o{ note_links : "target_document_id (CASCADE)"
    note_links {
        int    id PK
        string source_document_id FK
        string target_document_id FK
        string link_text
        bool   auto_suggested
    }

    documents        ||--o{ note_research : "document_id (CASCADE)"
    research_history ||--o{ note_research : "research_id (CASCADE)"
    note_research {
        int    id PK
        string document_id FK
        string research_id FK
        int    display_order
        bool   is_collapsed
    }

    documents        ||--o{ note_references : "note_id (CASCADE)"
    documents        |o--o{ note_references : "target_document_id (CASCADE, nullable)"
    research_history |o--o{ note_references : "target_research_id (CASCADE, nullable)"
    note_references {
        int    id PK
        string note_id FK
        string target_document_id FK "XOR target_research_id"
        string target_research_id FK "XOR target_document_id"
        text   quote "null = plain ref; set = inline annotation"
        text   prefix
        text   suffix
    }

    documents      |o--o{ note_syntheses : "result_document_id (SET NULL, nullable)"
    note_syntheses {
        int    id PK
        string result_document_id FK "nullable"
        enum   synthesis_type "merge|summarize|compare"
    }
    note_syntheses ||--o{ note_synthesis_sources : "synthesis_id (CASCADE)"
    documents      ||--o{ note_synthesis_sources : "source_document_id (CASCADE)"
    note_synthesis_sources {
        int    id PK
        int    synthesis_id FK
        string source_document_id FK
    }

    documents   ||--o{ document_collections : "document_id (CASCADE)"
    collections ||--o{ document_collections : "collection_id (CASCADE)"
    documents   ||--o{ document_chunks : "source_id (loose, no FK)"

Reading the diagram:

  • documents is the hub. Every note-facing edge points at documents, not at a dedicated notes table.
  • note_links is a document-to-document self relation expressed as two FK columns on one row (source_document_id, target_document_id) — hence the two edges from documents.
  • note_references is polymorphic: each row targets either a document or a research run, never both/neither (enforced by a CHECK, below). Both target edges are drawn optional.
  • note_syntheses.result_document_id is SET NULL, not CASCADE — the one note FK that is not cascade-deleted, so the synthesis audit record survives its result note being deleted.
  • document_chunks.source_id is a loose reference (a plain indexed String(36), not a foreign key) — the RAG chunk rows are cleaned up in application code, not by the relational cascade.

The note_* tables

All defined in database/models/note.py; created by migrations 0021_add_note_tables.py and 0022_add_note_references.py.

note_versions — version-history snapshots

  • PK id String(36) UUID (app-generated, not autoincrement — versions can be URL-shared).
  • FK document_id → documents.id CASCADE, not null.
  • title, content, tags (the snapshotted state), content_hash (SHA-256, for dedup), change_type (enum), change_summary (LLM-generated, filled async, nullable), created_at.
  • UNIQUE (document_id, content_hash); INDEX (document_id, created_at).
  • CHECK change_type IN ('initial','auto_save','manual_save','pre_restore','restore').

note_links — wiki-style [[link]] edges

  • PK id integer autoincrement.
  • FK source_document_id → documents.id CASCADE; FK target_document_id → documents.id CASCADE.
  • link_text, auto_suggested, created_at.
  • UNIQUE (source_document_id, target_document_id) (one edge per pair); INDEX on target_document_id.
  • CHECK source_document_id != target_document_id (no self-links).

note_research — pinned research runs

  • PK id integer autoincrement.
  • FK document_id → documents.id CASCADE; FK research_id → research_history.id CASCADE.
  • display_order, is_collapsed, created_at.
  • UNIQUE (document_id, research_id); UNIQUE (document_id, display_order) (prevents a concurrent-insert race producing duplicate orders — the service retries on IntegrityError).

note_references — generic reference / inline-annotation layer (migration 0022)

  • PK id integer autoincrement.
  • FK note_id → documents.id CASCADE.
  • FK target_document_id → documents.id CASCADE, nullable; FK target_research_id → research_history.id CASCADE, nullable.
  • quote, prefix, suffix (a Hypothesis-style TextQuoteSelector — quote IS NULL = a plain reference feeding a target's "Notes" panel; non-null = a highlighted inline comment).
  • CHECK ck_note_reference_single_target: (target_document_id IS NOT NULL) + (target_research_id IS NOT NULL) = 1 — exactly one target.

note_syntheses / note_synthesis_sources — AI synthesis audit

  • note_syntheses: PK id; FK result_document_id → documents.id SET NULL, nullable; synthesis_type enum; CHECK synthesis_type IN ('merge','summarize','compare').
  • note_synthesis_sources: PK id; FK synthesis_id → note_syntheses.id CASCADE; FK source_document_id → documents.id CASCADE; UNIQUE (synthesis_id, source_document_id). One synthesis has 2–5 source rows (count enforced in the service, not the DB).

A note_templates table existed in an earlier draft of migration 0021 but was removed before merge (no ORM model ever shipped). Migration 0021's downgrade() still defensively drops it if a pre-release dev DB has it.

Key behaviors

Version history — bookends, FIFO cap, atomic restore

  • A save inserts a note_versions row; identical-state saves dedup on (document_id, content_hash) (the hash is sha256(title \x00 content \x00 json(sorted(tags)))).
  • Restore is bookended and atomic. restore_with_bookends wraps three writes in one transaction: a PRE_RESTORE snapshot of the current state → apply the chosen version's title/content/tags to the Document → a RESTORE snapshot of the new state. Both bookends salt their hash with a throwaway random UUID so they always insert (their content by definition duplicates an existing row and would otherwise be dedup-absorbed).
  • FIFO cap MAX_VERSIONS_PER_NOTE = 100: the oldest non-bookend versions are pruned past 100. PRE_RESTORE/RESTORE rows are exempt from that prune (so the "restored here" trail survives heavy editing) but are separately capped at MAX_BOOKEND_VERSIONS = 40.
  • change_summary is generated by the LLM asynchronously after commit (a bounded thread pool) — the row lands with change_summary = NULL and is updated later.

Wiki-links

Parsed from note content with \[\[([^\]\n]{1,500})\]\], capped at 1000 links per note. Link text resolves to a target Document by title (ASCII-folded to mirror SQLite's LOWER()). Re-linking with different text updates the existing (source, target) row rather than duplicating it.

Reference layer & inline annotations

note_references is the single mechanism behind both the "Notes panel" on a document/research page (plain references, quote IS NULL) and Word-style inline comments (anchored references, quote set). Because a reference target is either a document or a research run, one row can't accidentally point at both. Anchors use immutable target text (rendered reports / extracted document text), so highlights can't drift — which is also why notes themselves cannot be annotated (their content is mutable).

Synthesis

NoteAIService.synthesize_notes runs the LLM over 2–5 notes and returns content + a suggested title; it does not write the audit tables. The route then creates the result note via create_note and, in one transaction, inserts a note_syntheses row plus a note_synthesis_sources row per source note (deleting the orphaned note if that fails).

RAG / semantic-search integration

When a note is indexed into a collection it is chunked and embedded like any other Document: document_chunks.source_id holds the note's documents.id, and document_collections/rag_document_status track per-collection index state. Editing a note deletes its rag_document_status row so the auto-index worker re-embeds it. This is what powers "Ask your notes" and AI/hybrid notes search. Chunk/vector cleanup is done in application code (not DB cascade) because vectors live in FAISS, outside the relational graph.

Cascade / deletion behavior

  • Delete a note (documents row): CASCADE removes its note_versions, both directions of note_links, its note_research, its note_references (as note_id and as target_document_id), and note_synthesis_sources where it was a source. Its note_syntheses.result_document_id is SET NULL (audit record survives). Separately, delete_note purges the note's FAISS vectors and document_chunks from every collection it was indexed in.
  • Delete a collection: CASCADE removes document_collections rows (drops notes from that collection) but does not delete the note Documents — a note still lives in the system "Notes" collection.
  • Delete a research run (research_history): CASCADE removes note_research pins and note_references anchored to that run (its inline annotations). The notes themselves are untouched — no note content is deleted.

Migrations

  • 0021_add_note_tables.py — revision = "0021", down_revision = "0020" (creates note_versions, note_links, note_research, note_syntheses, note_synthesis_sources).
  • 0022_add_note_references.py — revision = "0022", down_revision = "0021" (creates note_references; current migration head).

Both guard create_table with inspector.has_table(...) for idempotency, and both downgrade() functions log row counts before dropping, so an accidental downgrade is loud rather than silent.