Coverage for src/local_deep_research/database/models/note.py: 94%
102 statements
« prev ^ index » next coverage.py v7.15.1, created at 2026-07-19 23:35 +0000
1"""
2Note-specific models for AI-connected notes feature.
4Notes are stored as Documents with source_type='note'. This module contains
5auxiliary tables for note-specific features:
6- Version history
7- Wiki-style linking
8- Research integration
9- Synthesis tracking
11Naming note: the `note_*` table names are a service-layer scoping
12convention — every FK actually points at `documents.id`, not at a dedicated
13notes table. Renaming to `document_*` was considered during review since the
14shapes (versions, links, research-pins) could apply to any Document, but
15`change_type` enum values like `auto_save` / `pre_restore` are note-specific
16semantics and would be meaningless for research-downloaded PDFs. Revisit the
17naming only when there's a real second use case for versioning/linking on
18other Document subtypes.
19"""
21import enum
23from sqlalchemy import (
24 JSON,
25 Boolean,
26 CheckConstraint,
27 Column,
28 Enum,
29 ForeignKey,
30 Index,
31 Integer,
32 String,
33 Text,
34 UniqueConstraint,
35 text,
36)
37from sqlalchemy.orm import backref, relationship
38from sqlalchemy_utc import UtcDateTime, utcnow
40from .base import Base
43class NoteChangeType(str, enum.Enum):
44 """Valid values for NoteVersion.change_type."""
46 INITIAL = "initial"
47 AUTO_SAVE = "auto_save"
48 MANUAL_SAVE = "manual_save"
49 PRE_RESTORE = "pre_restore"
50 RESTORE = "restore"
53class NoteSynthesisType(str, enum.Enum):
54 """Valid values for NoteSynthesis.synthesis_type."""
56 MERGE = "merge"
57 SUMMARIZE = "summarize"
58 COMPARE = "compare"
61class NoteVersion(Base):
62 """
63 Stores version history for notes (documents with source_type='note').
64 Enables undo, restore, and semantic change tracking.
65 """
67 __tablename__ = "note_versions"
69 # UUID PK (String(36)) rather than autoincrement Integer — versions may
70 # be URL-shared / bookmarked, so IDs are externally visible. The other
71 # note junction tables use Integer autoincrement because they're
72 # internal-only; the asymmetry is intentional.
73 id = Column(String(36), primary_key=True)
75 # The document (note) this version belongs to
76 # Indexed via the composite idx_note_version_created below.
77 document_id = Column(
78 String(36),
79 ForeignKey("documents.id", ondelete="CASCADE"),
80 nullable=False,
81 )
83 # Snapshot of the note at this version
84 title = Column(String(500), nullable=False)
85 content = Column(Text, nullable=False)
86 tags = Column(
87 JSON, nullable=False, default=list, server_default=text("'[]'")
88 )
90 # Change metadata (see NoteChangeType)
91 change_type = Column(
92 Enum(
93 NoteChangeType,
94 values_callable=lambda obj: [e.value for e in obj],
95 name="note_change_type",
96 ),
97 nullable=False,
98 )
99 change_summary = Column(
100 Text, nullable=True
101 ) # LLM-generated description of changes
102 content_hash = Column(
103 String(64), nullable=False
104 ) # SHA-256 for deduplication
106 # Timestamps
107 created_at = Column(
108 UtcDateTime,
109 default=utcnow(),
110 server_default=utcnow(),
111 nullable=False,
112 )
114 # Relationships
115 document = relationship(
116 "Document",
117 backref=backref("note_versions", passive_deletes=True),
118 )
120 __table_args__ = (
121 Index("idx_note_version_created", "document_id", "created_at"),
122 UniqueConstraint(
123 "document_id", "content_hash", name="uix_note_version_content"
124 ),
125 # Mirror NoteChangeType members above. The ORM Enum() with
126 # values_callable suppresses SQLAlchemy's auto-CHECK, so declare
127 # it explicitly here — parity with the migration.
128 CheckConstraint(
129 "change_type IN ('initial', 'auto_save', 'manual_save', "
130 "'pre_restore', 'restore')",
131 name="ck_note_change_type",
132 ),
133 )
135 def __repr__(self):
136 doc_id = self.document_id[:8] if self.document_id else "None"
137 return f"<NoteVersion(document_id={doc_id}, id={self.id[:8] if self.id else 'None'})>"
140class NoteLink(Base):
141 """
142 Tracks wiki-style [[links]] between notes (documents with source_type='note').
143 Enables backlinks feature and knowledge graph visualization.
144 """
146 __tablename__ = "note_links"
148 id = Column(Integer, primary_key=True, autoincrement=True)
150 # Source document (the note containing the link).
151 # Not individually indexed — the UNIQUE(source, target) constraint
152 # below produces a usable index for single-column lookups on source.
153 source_document_id = Column(
154 String(36),
155 ForeignKey("documents.id", ondelete="CASCADE"),
156 nullable=False,
157 )
159 # Target document (the note being linked to).
160 # NOT individually indexed here — the explicit `idx_note_link_target`
161 # below in __table_args__ is the source of truth for this index.
162 # Pre-fix the column also carried `index=True`, which made
163 # SQLAlchemy create a second (auto-named) index over the same
164 # column. Migration 0021 already only creates `idx_note_link_target`,
165 # so production DBs are fine; this aligns the ORM declaration with
166 # what actually lives on disk.
167 target_document_id = Column(
168 String(36),
169 ForeignKey("documents.id", ondelete="CASCADE"),
170 nullable=False,
171 )
173 # The text used in the link (e.g., [[My Note Title]])
174 link_text = Column(String(500), nullable=False)
176 # Whether this link was auto-suggested by AI
177 auto_suggested = Column(
178 Boolean, default=False, server_default=text("0"), nullable=False
179 )
181 # Timestamps
182 created_at = Column(
183 UtcDateTime,
184 default=utcnow(),
185 server_default=utcnow(),
186 nullable=False,
187 )
189 # Relationships
190 source_document = relationship(
191 "Document",
192 foreign_keys=[source_document_id],
193 backref=backref("outgoing_note_links", passive_deletes=True),
194 )
195 target_document = relationship(
196 "Document",
197 foreign_keys=[target_document_id],
198 backref=backref("incoming_note_links", passive_deletes=True),
199 )
201 # Ensure unique links (one link per source-target pair) and prevent self-links
202 __table_args__ = (
203 UniqueConstraint(
204 "source_document_id", "target_document_id", name="uix_note_link"
205 ),
206 Index("idx_note_link_target", "target_document_id"),
207 CheckConstraint(
208 "source_document_id != target_document_id",
209 name="ck_note_link_no_self",
210 ),
211 )
213 def __repr__(self):
214 src_id = (
215 self.source_document_id[:8] if self.source_document_id else "None"
216 )
217 tgt_id = (
218 self.target_document_id[:8] if self.target_document_id else "None"
219 )
220 return f"<NoteLink(source={src_id}, target={tgt_id})>"
223class NoteResearch(Base):
224 """
225 Junction table tracking research runs triggered from notes.
226 Links notes (documents) to their research results.
227 """
229 __tablename__ = "note_research"
231 id = Column(Integer, primary_key=True, autoincrement=True)
233 # Foreign keys
234 # document_id is indexed by the uix_note_research_display_order UNIQUE
235 # (it leads with document_id), so no separate index is needed.
236 document_id = Column(
237 String(36),
238 ForeignKey("documents.id", ondelete="CASCADE"),
239 nullable=False,
240 )
241 research_id = Column(
242 String(36),
243 ForeignKey("research_history.id", ondelete="CASCADE"),
244 nullable=False,
245 index=True,
246 )
248 # Display settings
249 display_order = Column(
250 Integer, default=0, server_default=text("0"), nullable=False
251 )
252 is_collapsed = Column(
253 Boolean, default=False, server_default=text("0"), nullable=False
254 )
256 # Timestamps
257 created_at = Column(
258 UtcDateTime,
259 default=utcnow(),
260 server_default=utcnow(),
261 nullable=False,
262 )
264 # Relationships
265 document = relationship(
266 "Document",
267 backref=backref("note_research_runs", passive_deletes=True),
268 )
269 research = relationship(
270 "ResearchHistory",
271 backref=backref("note_context", passive_deletes=True),
272 )
274 # Ensure one entry per document-research pair, and prevent
275 # duplicate display_order from a concurrent-insert race.
276 # `link_research_to_note` computes MAX(display_order)+1 then INSERTs;
277 # without the second UniqueConstraint two concurrent calls would
278 # both observe the same MAX and insert with the same display_order.
279 # The service-layer fix retries on IntegrityError; the constraint
280 # is what makes the retry necessary (and safe).
281 __table_args__ = (
282 UniqueConstraint(
283 "document_id", "research_id", name="uix_note_research"
284 ),
285 # uix_note_research_display_order leads with document_id, so its
286 # backing btree already serves every (document_id [, display_order])
287 # read — get_note_research's ORDER BY and link_research's
288 # MAX(display_order) WHERE document_id. A separate
289 # idx_note_research_order over the identical columns would be a
290 # redundant second physical index (dead weight on the reorder
291 # write path), so it is intentionally omitted — same reasoning the
292 # note_links table applies to its source column.
293 UniqueConstraint(
294 "document_id",
295 "display_order",
296 name="uix_note_research_display_order",
297 ),
298 )
300 def __repr__(self):
301 doc_id = self.document_id[:8] if self.document_id else "None"
302 res_id = self.research_id[:8] if self.research_id else "None"
303 return f"<NoteResearch(document_id={doc_id}, research_id={res_id})>"
306class NoteReference(Base):
307 """Generic note → target reference with an optional quote anchor.
309 A row records that a note refers to a TARGET — a library document or
310 a research run (exactly one, CHECK-enforced). With a ``quote`` it is
311 an inline annotation: the quoted passage of the target's immutable
312 rendered text is highlighted, with short ``prefix``/``suffix``
313 context disambiguating repeated phrases (Hypothesis-style
314 TextQuoteSelector). Without a quote it is a plain "this note is
315 about that target" link, powering the target page's Notes panel.
317 NoteResearch remains the note-side pinning of research runs (with
318 per-note display ordering); this table is the target-side reference
319 layer both documents and research share.
320 """
322 __tablename__ = "note_references"
324 id = Column(Integer, primary_key=True, autoincrement=True)
326 note_id = Column(
327 String(36),
328 ForeignKey("documents.id", ondelete="CASCADE"),
329 nullable=False,
330 index=True,
331 )
332 target_document_id = Column(
333 String(36),
334 ForeignKey("documents.id", ondelete="CASCADE"),
335 nullable=True,
336 index=True,
337 )
338 target_research_id = Column(
339 String(36),
340 ForeignKey("research_history.id", ondelete="CASCADE"),
341 nullable=True,
342 index=True,
343 )
345 # Anchor (NULL quote = anchor-less reference).
346 quote = Column(Text, nullable=True)
347 prefix = Column(Text, nullable=True)
348 suffix = Column(Text, nullable=True)
350 created_at = Column(
351 UtcDateTime,
352 default=utcnow(),
353 server_default=utcnow(),
354 nullable=False,
355 )
357 note = relationship(
358 "Document",
359 foreign_keys=[note_id],
360 backref=backref("outgoing_references", passive_deletes=True),
361 )
362 target_document = relationship(
363 "Document",
364 foreign_keys=[target_document_id],
365 backref=backref("note_annotations", passive_deletes=True),
366 )
367 target_research = relationship(
368 "ResearchHistory",
369 backref=backref("note_annotations", passive_deletes=True),
370 )
372 __table_args__ = (
373 # Exactly one target — mirrors ck_note_reference_single_target in
374 # migration 0022 (three-way parity guarded by tests, same house
375 # style as the note_syntheses enum CHECK).
376 CheckConstraint(
377 "(target_document_id IS NOT NULL) + "
378 "(target_research_id IS NOT NULL) = 1",
379 name="ck_note_reference_single_target",
380 ),
381 )
383 def __repr__(self):
384 note_id = self.note_id[:8] if self.note_id else "None"
385 target = self.target_document_id or self.target_research_id or "None"
386 return (
387 f"<NoteReference(note={note_id}, target={target[:8]}, "
388 f"anchored={self.quote is not None})>"
389 )
392class NoteSynthesis(Base):
393 """
394 Tracks note synthesis operations (merge, compare, summarize).
395 Links synthesized notes to their source notes via NoteSynthesisSource.
396 """
398 __tablename__ = "note_syntheses"
400 id = Column(Integer, primary_key=True, autoincrement=True)
402 # The resulting synthesized note (document).
403 # `ondelete='SET NULL'` (not CASCADE) preserves the audit record even
404 # after the result note is deleted. CASCADE was considered because no
405 # route currently reads syntheses, but destroying the record — and its
406 # junction rows tying each source note to this operation — loses more
407 # than it's worth: a future "what AI operations have I run?" UI can
408 # read NULL-result rows meaningfully.
409 result_document_id = Column(
410 String(36),
411 ForeignKey("documents.id", ondelete="SET NULL"),
412 nullable=True,
413 index=True,
414 )
416 # Type of synthesis performed. Enum with `values_callable` mirrors the
417 # house style used by NoteChangeType (and DocumentStatus / EmbeddingProvider
418 # elsewhere): suppresses SQLAlchemy's auto-CHECK so the sole DB-level
419 # enforcement is the explicit CheckConstraint in __table_args__. When
420 # NoteSynthesisType grows, update both literals; TestEnumCheckParity
421 # in tests/database/test_migration_0021_note_tables.py guards the parity.
422 synthesis_type = Column(
423 Enum(
424 NoteSynthesisType,
425 values_callable=lambda obj: [e.value for e in obj],
426 name="note_synthesis_type",
427 ),
428 nullable=False,
429 )
431 # Timestamps
432 created_at = Column(
433 UtcDateTime,
434 default=utcnow(),
435 server_default=utcnow(),
436 nullable=False,
437 )
439 # Relationships
440 result_document = relationship(
441 "Document", foreign_keys=[result_document_id]
442 )
443 sources = relationship(
444 "NoteSynthesisSource",
445 cascade="all, delete-orphan",
446 backref=backref("synthesis"),
447 )
449 __table_args__ = (
450 # Mirror NoteSynthesisType members above. The ORM Enum() with
451 # values_callable suppresses SQLAlchemy's auto-CHECK, so declare
452 # it explicitly here — parity with the migration.
453 CheckConstraint(
454 "synthesis_type IN ('merge', 'summarize', 'compare')",
455 name="ck_note_synthesis_type",
456 ),
457 )
459 def __repr__(self):
460 return f"<NoteSynthesis(type={self.synthesis_type}, sources={len(self.sources)})>"
463class NoteSynthesisSource(Base):
464 """
465 Junction table linking a NoteSynthesis to its source Documents.
467 Replaces an earlier JSON-list column on NoteSynthesis (source_document_ids)
468 that lacked FK integrity — the author's original TODO on migration 0006.
469 Do NOT revert to a JSON list "for simplicity": it re-opens the stale-ID
470 class of bug, where deleting a source note leaves dangling references in
471 the synthesis record forever.
472 """
474 __tablename__ = "note_synthesis_sources"
476 id = Column(Integer, primary_key=True, autoincrement=True)
478 synthesis_id = Column(
479 Integer,
480 ForeignKey("note_syntheses.id", ondelete="CASCADE"),
481 nullable=False,
482 index=True,
483 )
484 source_document_id = Column(
485 String(36),
486 ForeignKey("documents.id", ondelete="CASCADE"),
487 nullable=False,
488 index=True,
489 )
491 created_at = Column(
492 UtcDateTime,
493 default=utcnow(),
494 server_default=utcnow(),
495 nullable=False,
496 )
498 source_document = relationship(
499 "Document", foreign_keys=[source_document_id]
500 )
502 __table_args__ = (
503 UniqueConstraint(
504 "synthesis_id",
505 "source_document_id",
506 name="uix_note_synthesis_source",
507 ),
508 )
510 def __repr__(self):
511 syn_id = self.synthesis_id if self.synthesis_id is not None else "None"
512 src_id = (
513 self.source_document_id[:8] if self.source_document_id else "None"
514 )
515 return f"<NoteSynthesisSource(synthesis={syn_id}, source={src_id})>"