Coverage for src/local_deep_research/research_library/notes/services/note_service.py: 92%
949 statements
« prev ^ index » next coverage.py v7.16.2, created at 2026-10-22 21:20 +0000
1"""
2Note Service - CRUD operations for notes stored as Documents.
4Notes are Documents with source_type='note'. This service provides
5note-specific operations while leveraging the Document infrastructure.
6"""
8import atexit
9import hashlib
10import json
11import random
12import re
13import threading
14import time
15import unicodedata
16import uuid
17from concurrent.futures import ThreadPoolExecutor
18from typing import Any, Dict, List, Optional
20from loguru import logger
21from sqlalchemy import delete, func, or_
22from sqlalchemy.exc import IntegrityError
23from sqlalchemy.orm import Session, joinedload
25from ....database.models import (
26 Collection,
27 Document,
28 DocumentChunk,
29 DocumentCollection,
30 NoteChangeType,
31 NoteLink,
32 NoteReference,
33 NoteResearch,
34 NoteVersion,
35 RagDocumentStatus,
36 SourceType,
37)
38from ....database.session_context import get_user_db_session, safe_rollback
39from ....database.thread_local_session import thread_cleanup
40from ...utils import escape_like
43def escape_markdown_link_label(label: str) -> str:
44 """Escape a string so it is safe as the LABEL of a ``[label](url)``
45 markdown link.
47 Provenance/comment notes embed untrusted text — a research query, or a
48 library document's title (which for downloaded documents comes from
49 third-party HTML/PDF metadata) — as the label of an auto-generated
50 link. Without escaping, a label like ``evil](https://attacker/x`` ends
51 the label at its first ``]`` and lets the rest re-target the rendered
52 link at an attacker URL (a phishing/content-integrity issue when the
53 note is later rendered as markdown, and a syntax-injection risk in the
54 PDF/LaTeX/Quarto exporters). Escape the four link-structural
55 metacharacters (backslash first) and neutralise newlines so the label
56 can never break out of the brackets.
57 """
58 return (
59 str(label)
60 .replace("\\", "\\\\")
61 .replace("[", "\\[")
62 .replace("]", "\\]")
63 .replace("(", "\\(")
64 .replace(")", "\\)")
65 .replace("\r", " ")
66 .replace("\n", " ")
67 )
70def _capture_request_db_password(username: str) -> Optional[str]:
71 """Capture the encrypted-DB password from the request thread.
73 Thin wrapper over the shared
74 ``database.session_passwords.capture_request_db_password`` (kept as a
75 module-local name so the many call sites in this file stay unchanged).
76 Needed because ``ThreadPoolExecutor.submit`` does not copy the calling
77 thread's ``ContextVar`` snapshot, so background workers otherwise lose
78 the password and every encrypted-DB write is swallowed.
79 """
80 from ....database.session_passwords import capture_request_db_password
82 return capture_request_db_password(username)
85# Bounded thread pool for AI change-summary generation. The change summary is
86# the only call inside update_note that hits a remote LLM, so leaving it on
87# the request thread pays a full round-trip on every content-changing save.
88# We persist the version snapshot synchronously with change_summary=None,
89# then enqueue an UPDATE that fills the summary once the LLM returns. Users
90# see their save reflected in history immediately; the summary populates
91# moments later when they open the version panel.
92_summary_executor: ThreadPoolExecutor | None = None
93_summary_executor_lock = threading.Lock()
96def _get_summary_executor() -> ThreadPoolExecutor:
97 global _summary_executor
98 with _summary_executor_lock:
99 if _summary_executor is None:
100 _summary_executor = ThreadPoolExecutor(
101 max_workers=4,
102 thread_name_prefix="note_summary_",
103 )
104 return _summary_executor
107# Soft cap on pending change-summary tasks. ThreadPoolExecutor's
108# internal queue is unbounded; combined with the per-task (old_content,
109# new_content) capture (up to 50 MB each), a scripted burst of saves
110# could pile up hundreds of MB before the 4 workers drain it. Drop the
111# task with a log line once the queue exceeds the cap — losing the AI
112# change summary is a non-essential degradation; the snapshot row
113# itself already landed at update_note commit time. Per-user write
114# rate-limit (``_notes_write_limit`` at 60/min) provides the primary
115# defence; this is belt-and-suspenders for unexpected hot paths.
116_SUMMARY_QUEUE_SOFT_CAP = 100
119def _submit_summary_task(fn, *args, **kwargs) -> None:
120 """Submit a change-summary task with queue-depth backpressure.
122 On overload, drops the task and logs at WARNING level — better than
123 silent unbounded queue growth. The snapshot row is unaffected;
124 change_summary stays NULL until the next save fills it.
125 """
126 executor = _get_summary_executor()
127 # ThreadPoolExecutor exposes its work queue via the private
128 # ``_work_queue`` attribute. The qsize() reading is approximate
129 # (queue is concurrent-safe but the value can be stale by the
130 # time we read it) but the soft cap intentionally tolerates drift.
131 try:
132 qsize = executor._work_queue.qsize()
133 except Exception:
134 qsize = 0
135 if qsize >= _SUMMARY_QUEUE_SOFT_CAP:
136 logger.warning(
137 "Summary executor queue at {} items (cap {}); dropping task. "
138 "NoteVersion.change_summary will stay NULL for this save.",
139 qsize,
140 _SUMMARY_QUEUE_SOFT_CAP,
141 )
142 return
143 # Wrap in thread_cleanup so the worker clears its thread-local DB
144 # session AND the cached (username, password) entry in
145 # _thread_credentials when it returns. The summary pool's threads are
146 # long-lived (they never die), so cleanup_dead_threads never sweeps
147 # them — without this the plaintext SQLCipher password the worker
148 # captured would sit in the pool thread's dict for the whole process
149 # lifetime (the #4182 credential-hygiene class). Mirrors the idiom
150 # every other executor in the codebase uses (e.g. news_strategy).
151 try:
152 executor.submit(thread_cleanup(fn), *args, **kwargs)
153 except RuntimeError:
154 # The pool was shut down (graceful restart / atexit) between the
155 # _get_summary_executor() read above and this submit. change_summary is
156 # best-effort, so drop it rather than let the RuntimeError bubble up and
157 # 500 a note save whose content already committed durably.
158 logger.warning(
159 "Summary executor shut down before task submit; "
160 "NoteVersion.change_summary will stay NULL for this save."
161 )
164def _shutdown_summary_executor() -> None:
165 # Race fix: the matching getter at _get_summary_executor holds the
166 # lock for its read-modify-write of _summary_executor; the shutdown
167 # path must hold the same lock or a request thread can observe a
168 # half-replaced reference (e.g. submit() into an already-shut-down
169 # executor → RuntimeError) during gunicorn graceful shutdown.
170 global _summary_executor
171 with _summary_executor_lock:
172 if _summary_executor is not None:
173 _summary_executor.shutdown(wait=True)
174 _summary_executor = None
177atexit.register(_shutdown_summary_executor)
180def _populate_change_summary_async(
181 username: str,
182 dbpw: Optional[str],
183 version_id: str,
184 old_content: str,
185 new_content: str,
186) -> None:
187 """Background worker: ask the LLM for a change summary and UPDATE the
188 already-persisted NoteVersion row.
190 ``dbpw`` is the encrypted-DB password captured on the request thread by
191 ``_capture_request_db_password`` and threaded in explicitly because
192 stdlib ``ThreadPoolExecutor`` does not propagate the request's
193 ``ContextVar`` snapshot — without it, ``get_user_db_session`` raises
194 ``DatabaseSessionError`` on every encrypted-DB install. It's named
195 ``dbpw`` rather than ``db_password`` to avoid a gitleaks generic-secret
196 false positive (that rule matches a ``password=<8+ char literal>`` shape);
197 the value is a passthrough credential, never a hardcoded secret.
199 Exceptions are logged but never raised — this runs on a thread pool and
200 a failure here must not crash the worker.
201 """
202 try:
203 from .note_ai_service import NoteAIService
205 # Pass dbpw so the LLM-settings lookup inside
206 # summarize_changes_sync -> _get_llm() can open the encrypted DB on
207 # this worker thread (no request context to resolve the password
208 # from).
209 ai_service = NoteAIService(username, dbpw=dbpw)
210 # summarize_changes_sync, NOT asyncio.run(summarize_changes(...)):
211 # bridging the async core on a throwaway loop per summary is the
212 # #6293 hazard, not a safe bridge. The langchain OpenAI/Anthropic
213 # async client is cached once per process with no loop in its
214 # cache key, so a second loop reusing a keep-alive connection
215 # opened by the uvicorn request loop fails with "Event loop is
216 # closed" after the request was already sent (the openai SDK
217 # silently re-sends it), and the closed loop can leave an orphaned
218 # transport fd behind. (_close_base_llm in utilities/llm_utils.py
219 # may still run asyncio.run(async_httpx.aclose()) on this thread
220 # for a freshly built per-instance Ollama client; that's safe
221 # because that client was never driven on any loop.)
222 # The sync path shares its prompt, shortcuts and teardown with the
223 # async core; only the LLM call differs. The executor thread
224 # blocks for this one task, exactly as it did before.
225 summary = ai_service.summarize_changes_sync(old_content, new_content)
226 if not summary: 226 ↛ 227line 226 didn't jump to line 227 because the condition on line 226 was never true
227 return
229 with get_user_db_session(username, password=dbpw) as session:
230 row = session.query(NoteVersion).filter_by(id=version_id).first()
231 if row is None: 231 ↛ 232line 231 didn't jump to line 232 because the condition on line 231 was never true
232 logger.debug(
233 "Version {} disappeared before summary landed; skipping",
234 version_id[:8],
235 )
236 return
237 # Fill-only guard: this worker is only ever scheduled for a
238 # freshly inserted snapshot (change_summary=None). Refuse to
239 # overwrite an existing summary so a caller bug (e.g. passing
240 # a dedup-absorbed historical version id) can't corrupt the
241 # audit trail.
242 if row.change_summary is not None:
243 logger.warning(
244 "Version {} already has a change summary; refusing to "
245 "overwrite it",
246 version_id[:8],
247 )
248 return
249 row.change_summary = summary
250 session.commit()
251 logger.debug(
252 "Filled change summary for note version {}", version_id[:8]
253 )
254 except Exception:
255 logger.exception(
256 "Async change-summary worker failed for version {}",
257 version_id[:8] if version_id else "<none>",
258 )
261# Regex for parsing [[wiki-style links]]. The negated class is bounded by
262# MAX_LINK_TEXT_LENGTH so an unclosed ``[[`` doesn't capture multi-MB of
263# body and ship it as a SQL parameter twice (exact + startswith) against
264# the unindexed LOWER(title) column. It also excludes newlines (``\n``): a
265# note title is a single-line String(500), so a ``[[`` whose closing
266# ``]]`` is on a later line is an unclosed bracket, not a link. This
267# mirrors the frontend's ``WIKILINK_TARGET = [^\]\n]+`` in
268# web/static/js/services/formatting.js so parse (which creates NoteLink
269# rows) and render (which draws the link) agree — previously the backend
270# admitted newlines and created links the UI never rendered.
271MAX_LINK_TEXT_LENGTH = 500
272LINK_PATTERN = re.compile(r"\[\[([^\]\n]{1,500})\]\]")
274# Cap on parsed wiki-links per note. Without this, a pathological note
275# (e.g. 50 MB of `[[A]]\n` lines = millions of matches) would spawn
276# millions of NoteLink rows on save and have each one trigger 2-3
277# database queries through ``_resolve_link_internal``. 1000 covers any
278# realistic note (Roam/Obsidian power users typically have <100 links
279# per note); anything beyond is silently truncated and a warning logged
280# rather than failing the save.
281MAX_LINKS_PER_NOTE = 1000
283# Mirrors SQLite's built-in lower(): ASCII-only case folding. Link
284# resolution folds titles in SQL, and the batched exact-title lookup has
285# to match lower(title) values computed BY THE DATABASE back to
286# Python-side link texts — so the Python fold must be the database's.
287# str.lower() folds full Unicode ('Ü' → 'ü') and would diverge.
288_SQLITE_LOWER_TABLE = str.maketrans(
289 "ABCDEFGHIJKLMNOPQRSTUVWXYZ", "abcdefghijklmnopqrstuvwxyz"
290)
293def _fold_title_ascii(text: str) -> str:
294 return (text or "").translate(_SQLITE_LOWER_TABLE)
297# SQLITE_MAX_VARIABLE_NUMBER is 999 on older SQLite builds; chunk IN()
298# lists well below it so a 1000-link note can't overflow the bind-param
299# limit.
300_IN_CLAUSE_CHUNK = 500
302# Caps on per-note many-to-many list params accepted by the route
303# layer. Mirror MAX_TAGS_PER_NOTE in spirit: bound how many DocumentCollection
304# / NoteResearch rows a single create or reorder request can spawn.
305MAX_COLLECTIONS_PER_NOTE = 50
306MAX_RESEARCH_PER_NOTE = 200
308# Mirrors BaseExporter.MAX_CONTENT_SIZE at exporters/base.py:63. Kept inline
309# rather than imported because exporters/__init__.py unconditionally pulls in
310# weasyprint via all 5 exporter modules — importing BaseExporter would drag a
311# heavy dependency into note CRUD. Extract to a dep-light constants module if
312# sharing ever becomes worthwhile.
313NOTE_CONTENT_MAX_BYTES = 50 * 1024 * 1024 # 50 MB
315# Cap on version-history rows per note. Auto-save can otherwise grow
316# note_versions unboundedly — a near-50 MB note with small changes x 100 saves
317# = ~5 GB. Pure FIFO: simplest rule with useful value; the `initial` version
318# can be pruned, but the user still has 100 recent snapshots to fall back on.
319MAX_VERSIONS_PER_NOTE = 100
320# Audit bookends (PRE_RESTORE / RESTORE) are excluded from the normal version
321# prune so the "restored here" trail survives heavy editing — but they still
322# need a ceiling, or repeated restores grow note_versions without bound. Keep
323# the most recent N bookends.
324MAX_BOOKEND_VERSIONS = 40
326# PRE_RESTORE / RESTORE are the audit bookends excluded from version-dedup
327# (their hashes are salted) and from the prune pool (the "restored here" trail
328# survives heavy editing).
329_AUDIT_BOOKEND_TYPES = (
330 NoteChangeType.PRE_RESTORE.value,
331 NoteChangeType.RESTORE.value,
332)
335def _assert_content_size(content: str) -> None:
336 """Raise ValueError if content isn't a string or exceeds
337 NOTE_CONTENT_MAX_BYTES.
339 The type check matters for direct API callers: a truthy non-string
340 body value (e.g. ``{"content": 123}``) passes the route layer's
341 ``if not content`` guard, and without this check ``.encode()`` raised
342 an AttributeError that surfaced as an opaque 500 instead of the 400
343 the routes' ``except ValueError`` mapping produces.
344 """
345 if not isinstance(content, str):
346 raise ValueError("content must be a string")
347 if content and len(content.encode("utf-8")) > NOTE_CONTENT_MAX_BYTES:
348 raise ValueError(
349 f"Note content exceeds maximum size "
350 f"({NOTE_CONTENT_MAX_BYTES // (1024 * 1024)} MB)"
351 )
354# Mirrors MAX_TAG_LENGTH in note-detail.js. The frontend caps the input
355# field, but direct API callers can still submit arbitrary tag strings.
356# Backend validation is the only stop against a 10MB tag landing in the
357# JSON ``tags`` column.
358MAX_TAG_LENGTH = 50
360# Cap the number of tags per note. Without this, ``_validate_tags`` accepts
361# an unbounded list — the 50-char per-tag limit then permits multi-MB of
362# tag JSON per note, and snapshots store the tag list per version, so the
363# 100-version cap multiplies the bloat.
364MAX_TAGS_PER_NOTE = 50
367def _validate_tags(tags: Optional[List[str]]) -> None:
368 """Raise ValueError if ``tags`` isn't a list of short strings.
370 None passes (callers may pass None to mean "don't update tags"). An
371 empty list passes. Anything else: must be a list, every entry must
372 be a string, and each string must be ``<= MAX_TAG_LENGTH`` chars.
373 """
374 if tags is None:
375 return
376 if not isinstance(tags, list):
377 raise ValueError("tags must be a list")
378 if len(tags) > MAX_TAGS_PER_NOTE:
379 raise ValueError(f"too many tags (max {MAX_TAGS_PER_NOTE})")
380 for tag in tags:
381 if not isinstance(tag, str):
382 raise ValueError("each tag must be a string")
383 if len(tag) > MAX_TAG_LENGTH:
384 raise ValueError(
385 f"tag exceeds maximum length ({MAX_TAG_LENGTH} chars)"
386 )
389# Cap on note title length. ``Document.title`` is a plain ``Text`` column
390# (unbounded), but the title is interpolated raw into LLM synthesis prompts
391# (note_ai_service.py:729) and into ``LOWER(title) == ?`` wiki-link lookups
392# (note_service._resolve_link_internal) on every save. Without a cap a
393# direct API caller could send a 50 MB title and DoS both paths.
394MAX_TITLE_LENGTH = 500
397def _validate_title(title: Optional[str]) -> None:
398 """Raise ValueError if ``title`` isn't a short non-blank string.
400 None passes (callers may pass None to "don't update title"). Empty
401 string passes (the route layer's own ``if not title`` check still
402 rejects it on create). Whitespace-only strings (``" "``,
403 ``"\\t\\n"``) are explicitly rejected so the slug generator does not
404 produce an empty slug and the wiki-link resolver doesn't match an
405 invisible title via ``[[ ]]``. Anything else: must be a string and
406 at most ``MAX_TITLE_LENGTH`` characters.
407 """
408 if title is None:
409 return
410 if not isinstance(title, str): 410 ↛ 411line 410 didn't jump to line 411 because the condition on line 410 was never true
411 raise ValueError("title must be a string")
412 # Reject whitespace-only titles. Empty string falls through to the
413 # route-layer guard; whitespace-only is truthy and would slip past
414 # ``if not title`` checks.
415 if title and not title.strip():
416 raise ValueError("title must not be blank or whitespace-only")
417 if len(title) > MAX_TITLE_LENGTH:
418 raise ValueError(
419 f"title exceeds maximum length ({MAX_TITLE_LENGTH} chars)"
420 )
423class NoteService:
424 """Service for managing notes (Documents with source_type='note')."""
426 DEFAULT_NOTES_COLLECTION_NAME = "Notes"
427 SOURCE_TYPE_NAME = "note"
429 def __init__(self, username: str):
430 """Initialize note service for a user."""
431 self.username = username
433 @staticmethod
434 def _generate_slug(title: str) -> str:
435 """Generate a URL-friendly slug from a title.
437 Folds Latin accents to ASCII via stdlib NFKD normalization
438 (``café`` → ``cafe``). Scripts with no ASCII form (CJK, Cyrillic,
439 Arabic) and emoji drop out at the ``encode("ascii", "ignore")`` step.
440 A real transliterator is avoided on purpose: the licensed options
441 (text-unidecode / python-slugify) are copyleft/GPL, and the slug is a
442 non-critical convenience field.
444 A title that reduces to nothing gets ``note-<12 hex>`` derived from
445 the title itself, NOT the bare ``"note"`` placeholder: that constant
446 gave every CJK-, Cyrillic-, Arabic- or emoji-titled note the same
447 slug, so two differently titled notes were indistinguishable in the
448 autocomplete payload that carries it. Same shape as the fallback
449 ``security.filename_sanitizer`` uses for an upload stem werkzeug drops
450 (#6108). Whitespace is collapsed first so the fallback treats padding
451 and runs the way the ASCII branch above already does.
453 Only a title with no content at all still returns ``"note"``.
454 """
455 ascii_title = (
456 unicodedata.normalize("NFKD", title or "")
457 .encode("ascii", "ignore")
458 .decode("ascii")
459 )
460 slug = ascii_title.lower()
461 slug = re.sub(r"\s+", "-", slug)
462 slug = re.sub(r"[^a-z0-9\-]", "", slug)
463 slug = re.sub(r"-+", "-", slug)
464 slug = slug.strip("-")
465 if slug:
466 return slug[:500]
468 collapsed = re.sub(r"\s+", " ", title or "").strip()
469 if not collapsed:
470 return "note"
471 # Normalized and case-folded first, so the fallback answers the way the
472 # ASCII branch above does. That one lowercases and NFKD-folds, so
473 # ``Hello`` and ``hello`` share a slug; hashing the raw text would give
474 # ``Привет`` and ``привет`` two, and a precomposed vs decomposed
475 # spelling of one title two more, for a title that never changed.
476 # ``casefold`` on a lone surrogate is a no-op, so the guard below still
477 # does its job.
478 #
479 # surrogatepass: a title round-tripped through a filesystem or a
480 # lenient client can carry a lone surrogate, which plain UTF-8
481 # encoding raises on. It only feeds the digest — the slug stays ASCII.
482 #
483 # 12 hex characters is 48 bits, deliberately truncated: this is a
484 # convenience label recomputed per request, never stored and never a
485 # lookup key, so a collision costs a duplicate label, not a wrong note.
486 digest = hashlib.sha256(
487 unicodedata.normalize("NFKC", collapsed)
488 .casefold()
489 .encode("utf-8", errors="surrogatepass")
490 ).hexdigest()[:12]
491 return f"note-{digest}"
493 @staticmethod
494 def _compute_content_hash(content: str) -> str:
495 """Compute SHA-256 hash of content."""
496 return hashlib.sha256(content.encode("utf-8")).hexdigest()
498 @staticmethod
499 def _compute_version_hash(
500 title: Optional[str],
501 content: Optional[str],
502 tags: Optional[List[str]],
503 ) -> str:
504 """Compute the dedup hash for a NoteVersion snapshot.
506 JSON-encodes the whole ``[title, content, sorted-tags]`` triple and
507 hashes that, so title-only / content-only / tag-only edits each get a
508 distinct hash and aren't silently deduped against an existing snapshot.
509 Tags are sorted first so reordering alone doesn't churn the hash.
511 Why JSON the WHOLE triple rather than a NUL-joined string: title and
512 content can each contain a literal NUL byte — a JSON ``\\u0000`` in the
513 request body decodes to ``\\x00`` and survives SQLite TEXT storage
514 untouched (SQLite is length-prefixed, not NUL-terminated), and nothing
515 validates it away — so a NUL separator is NOT injection-proof. Pre-fix,
516 ``title="MyTitle", content="Hello\\x00World"`` and
517 ``title="MyTitle\\x00Hello", content="World"`` produced the identical
518 NUL-joined payload and hash, silently dedup-absorbing a genuinely
519 different save out of version history. JSON escapes every control
520 character (NUL included) as ``\\uXXXX`` and quotes/escapes each element,
521 making the encoding injective across all three fields.
523 Hash-format note: this serialization differs from older builds', so the
524 hash changes for previously-written snapshots. The only consumer is the
525 (document_id, content_hash) dedup check, so the sole effect is a
526 one-time extra version row the first time a note is re-saved in a state
527 that already has an old-format snapshot — benign.
528 """
529 title_s = "" if title is None else title
530 content_s = "" if content is None else content
531 sorted_tags = sorted(t for t in (tags or []) if isinstance(t, str))
532 payload = json.dumps(
533 [title_s, content_s, sorted_tags], ensure_ascii=False
534 )
535 return hashlib.sha256(payload.encode("utf-8")).hexdigest()
537 def _get_note_source_type_id(self, session: Session) -> str:
538 """Get the source_type ID for notes, creating it if needed."""
539 from . import get_note_source_type_id
541 source_type_id = get_note_source_type_id(session)
542 if source_type_id: 542 ↛ 546line 542 didn't jump to line 546 because the condition on line 542 was always true
543 return source_type_id
545 # Create it if it doesn't exist (shouldn't happen normally)
546 source_type = SourceType(
547 id=str(uuid.uuid4()),
548 name=self.SOURCE_TYPE_NAME,
549 display_name="Note",
550 description="User-created notes with AI-enhanced features",
551 icon="sticky-note",
552 )
553 session.add(source_type)
554 session.flush()
555 return source_type.id
557 def _get_or_create_notes_collection(self, session: Session) -> str:
558 """Get or create the default Notes collection.
560 Serialised by the per-user init lock to prevent two concurrent
561 first-note creations (e.g. two browser tabs both creating their
562 first note simultaneously) from each seeing no ``collection_type='notes'``
563 row and inserting a duplicate. Matches the locking pattern used
564 by ``ensure_default_library_collection`` and
565 ``ensure_research_history_collection`` in ``database/library_init.py``;
566 there is no UNIQUE constraint at the schema level that would
567 catch the race otherwise.
569 The create path COMMITS inside the lock, for two reasons (both
570 matching the ``library_init`` reference pattern):
572 1. Lock scope: a flush-only insert leaves the row invisible to
573 other sessions until the caller commits — after the lock is
574 released — so a second request entering the lock in that window
575 would still see no row and insert a duplicate.
576 2. Read-only callers: routes like ask-context reach this via
577 ``get_notes_collection_id`` and never commit; the request
578 teardown ROLLS BACK the shared session, discarding the row
579 after its id was already returned to the client.
581 Because the commit flushes the caller's whole session, callers
582 must resolve the collection BEFORE staging unrelated writes on
583 the same session (create_note does).
584 """
585 from ....database.library_init import _get_user_init_lock
587 with _get_user_init_lock(self.username):
588 collection = (
589 session.query(Collection)
590 .filter_by(collection_type="notes")
591 .first()
592 )
594 if collection:
595 return collection.id
597 collection_id = str(uuid.uuid4())
598 collection = Collection(
599 id=collection_id,
600 name=self.DEFAULT_NOTES_COLLECTION_NAME,
601 description="Default collection for notes",
602 collection_type="notes",
603 )
604 session.add(collection)
605 try:
606 session.commit()
607 except Exception:
608 session.rollback()
609 raise
610 # The collection-engine cache may hold this user's pre-creation
611 # (empty) engine list from a search within the TTL; drop it so
612 # the next research offers the Notes collection as a tool (#6609).
613 from ....web_search_engines.search_engines_config import (
614 invalidate_collection_engines_cache,
615 )
617 invalidate_collection_engines_cache(self.username)
618 logger.info("Created default Notes collection: {}", collection_id)
619 return collection_id
621 def get_notes_collection_id(self) -> Optional[str]:
622 """Get the default Notes collection ID.
624 Returns the collection ID or None if it cannot be determined.
625 """
626 try:
627 with get_user_db_session(self.username) as session:
628 return self._get_or_create_notes_collection(session)
629 except Exception:
630 logger.exception("Error getting notes collection ID")
631 return None
633 def get_notes_index_status(self) -> Dict[str, Any]:
634 """Pre-flight context for "Ask your notes": the Notes collection id,
635 the total note count, and how many notes are indexed into it.
637 The UI uses this to warn when there is nothing to search — no notes
638 at all, or notes that haven't been embedded yet — before kicking off
639 a research run pinned to the Notes collection.
640 """
641 collection_id = self.get_notes_collection_id()
642 note_count = self.count_notes()
643 indexed_count = 0
644 if collection_id: 644 ↛ 651line 644 didn't jump to line 651 because the condition on line 644 was always true
645 with get_user_db_session(self.username) as session:
646 indexed_count = (
647 session.query(DocumentCollection)
648 .filter_by(collection_id=collection_id, indexed=True)
649 .count()
650 )
651 return {
652 "collection_id": collection_id,
653 "note_count": note_count,
654 "indexed_count": indexed_count,
655 }
657 def _is_note(self, session: Session, document: Document) -> bool:
658 """Check if a document is a note."""
659 source_type = (
660 session.query(SourceType)
661 .filter_by(id=document.source_type_id)
662 .first()
663 )
664 return source_type and source_type.name == self.SOURCE_TYPE_NAME
666 def _get_note_in_session(
667 self, session: Session, note_id: str
668 ) -> Optional[Document]:
669 """Fetch a Document by id and return it only if it is a note.
671 Centralizes the fetch + ``_is_note`` guard prelude that every
672 mutating note method hand-copied — and which was half-copied as a
673 bug on the collection helpers (they originally skipped the
674 ``_is_note`` check, letting a non-note Document acquire note rows).
675 Returns ``None`` when the id doesn't exist or the Document isn't a
676 note; callers map that to their own bail value.
678 Still routes through ``self._is_note`` so the existing
679 ``patch.object(NoteService, '_is_note')`` monkeypatches keep firing.
680 """
681 document = session.query(Document).filter_by(id=note_id).first()
682 if document is None or not self._is_note(session, document):
683 return None
684 return document
686 @staticmethod
687 def _escape_like(text: str) -> str:
688 """Escape LIKE/ILIKE wildcards so user input matches literally.
690 Thin alias for the shared :func:`escape_like` so every notes query
691 path uses the one canonical escaping rule.
692 """
693 return escape_like(text)
695 @staticmethod
696 def _rollback_quietly(session) -> None:
697 """Roll back a session after a failed flush/commit, swallowing any
698 rollback error. The per-user request session is shared and is NOT
699 rolled back by get_user_db_session on exception, so callers that catch
700 IntegrityError (and want to retry or surface a clean error) must roll
701 back explicitly or the next statement reuses a poisoned session.
703 Keeps the None-guard (call sites pass ``locals().get("session")``,
704 which may be None) and delegates the actual rollback/log to the
705 shared :func:`safe_rollback` helper.
706 """
707 if session is None: 707 ↛ 708line 707 didn't jump to line 708 because the condition on line 707 was never true
708 return
709 safe_rollback(session, "note_service")
711 def create_note(
712 self,
713 title: str,
714 content: str,
715 tags: Optional[List[str]] = None,
716 collection_ids: Optional[List[str]] = None,
717 ) -> str:
718 """
719 Create a new note (stored as a Document).
721 Args:
722 title: Note title
723 content: Note content (markdown)
724 tags: Optional list of tags
725 collection_ids: Optional list of collection IDs
727 Returns:
728 The created note/document ID
729 """
730 _validate_title(title)
731 _assert_content_size(content)
732 _validate_tags(tags)
733 if collection_ids is not None:
734 if not isinstance(collection_ids, list): 734 ↛ 735line 734 didn't jump to line 735 because the condition on line 734 was never true
735 raise ValueError("collection_ids must be a list")
736 if len(collection_ids) > MAX_COLLECTIONS_PER_NOTE:
737 raise ValueError(
738 f"too many collection_ids (max {MAX_COLLECTIONS_PER_NOTE})"
739 )
740 # Element type check: an unhashable element (dict/list) would
741 # otherwise raise TypeError in the de-dupe set comprehension
742 # below — an opaque 500 instead of the routes' ValueError→400.
743 for cid in collection_ids:
744 if not isinstance(cid, str):
745 raise ValueError("each collection_id must be a string")
746 try:
747 with get_user_db_session(self.username) as session:
748 note_id = str(uuid.uuid4())
749 source_type_id = self._get_note_source_type_id(session)
750 content_hash = self._compute_content_hash(
751 f"{note_id}:{content}"
752 )
754 # Resolve the default Notes collection BEFORE staging the
755 # note: the get-or-create commits when it has to create the
756 # collection (see its docstring), and committing after
757 # session.add(document) would break this method's
758 # one-transaction atomicity for the note itself. A lazily
759 # created SourceType (flushed above) riding along on that
760 # commit is fine — it's the same kind of idempotent init
761 # row as the collection.
762 default_collection_id = self._get_or_create_notes_collection(
763 session
764 )
766 # Create Document with source_type='note'
767 document = Document(
768 id=note_id,
769 source_type_id=source_type_id,
770 document_hash=content_hash,
771 file_size=len(content.encode("utf-8")),
772 file_type="note",
773 title=title,
774 text_content=content,
775 tags=tags or [],
776 # DB column stays `favorite` (the flag is shared with the
777 # Document model); the notes API/UI vocabulary for it is
778 # `pinned` (see the `"pinned": document.favorite` response
779 # mapping). One column, two names by design.
780 favorite=False,
781 character_count=len(content),
782 word_count=len(content.split()),
783 )
784 session.add(document)
786 # Add to default Notes collection
787 doc_coll = DocumentCollection(
788 document_id=note_id,
789 collection_id=default_collection_id,
790 indexed=False,
791 )
792 session.add(doc_coll)
794 # Add to additional collections. De-dupe and skip ids that
795 # don't exist: a repeated id would violate the
796 # (document_id, collection_id) UNIQUE and a bogus id would
797 # FK-violate — either one aborting the entire note creation
798 # with an opaque error.
799 if collection_ids:
800 requested = {cid for cid in collection_ids if cid} - {
801 default_collection_id
802 }
803 valid_ids = {
804 row[0]
805 for row in session.query(Collection.id).filter(
806 Collection.id.in_(requested)
807 )
808 }
809 for coll_id in valid_ids:
810 session.add(
811 DocumentCollection(
812 document_id=note_id,
813 collection_id=coll_id,
814 indexed=False,
815 )
816 )
818 # Atomicity: Document + DocumentCollection + INITIAL
819 # version snapshot + outgoing links all land in one
820 # transaction. Pre-fix, the document committed first and
821 # the snapshot + link reparse ran in fresh sessions, so a
822 # crash between writes left a note with no INITIAL audit
823 # row and no parsed [[wiki-links]] — same shape as the
824 # bug that update_note already fixed.
825 self._create_version_snapshot_in_session(
826 session,
827 note_id=note_id,
828 title=title,
829 content=content,
830 tags=tags,
831 change_type=NoteChangeType.INITIAL.value,
832 change_summary="Initial version",
833 )
834 self._parse_and_update_links_in_session(
835 session, note_id, content
836 )
838 try:
839 session.commit()
840 except Exception:
841 session.rollback()
842 raise
844 # Don't log the title — note titles can carry sensitive
845 # content (e.g. medical, personal). Note id alone is
846 # enough to correlate with surrounding events.
847 logger.info("Created note {}", note_id)
849 return note_id
851 except Exception:
852 # Same atomicity guarantee as update_note: the cached
853 # request/thread session does NOT auto-rollback. The
854 # in-session helpers (snapshot, link reparse) call
855 # session.flush() at the end, so a downstream failure leaves
856 # the document INSERT durably flushed without ever committing
857 # properly. Rollback explicitly so create_note is atomic.
858 # locals().get avoids NameError when the failure happens before
859 # `with ... as session` binds; _rollback_quietly no-ops on None.
860 self._rollback_quietly(locals().get("session"))
861 logger.exception("Error creating note")
862 raise
864 def note_exists(self, note_id: str) -> bool:
865 """Cheap existence + is-note guard for route-level 404 checks.
867 Projects only ``Document.id`` filtered by the note source type —
868 no ``text_content`` hydration (a plain non-deferred column, up to
869 50 MB per row via ``get_note``) and none of ``get_note``'s
870 aggregate side queries. Use this wherever the caller only needs
871 to know the note is real; ``get_note`` stays for handlers that
872 consume the payload.
873 """
874 if not note_id: 874 ↛ 875line 874 didn't jump to line 875 because the condition on line 874 was never true
875 return False
876 with get_user_db_session(self.username) as session:
877 note_source_type_id = self._get_note_source_type_id(session)
878 if note_source_type_id is None: 878 ↛ 879line 878 didn't jump to line 879 because the condition on line 878 was never true
879 return False
880 return (
881 session.query(Document.id)
882 .filter(
883 Document.id == note_id,
884 Document.source_type_id == note_source_type_id,
885 )
886 .first()
887 is not None
888 )
890 def get_note(self, note_id: str) -> Optional[Dict[str, Any]]:
891 """Get a note by ID.
893 The response includes an ``outgoing_links`` map (link_text →
894 target_id, target_title) so the frontend can resolve
895 ``[[wiki-links]]`` to the correct target Document at render
896 time, regardless of whether the target has since been renamed.
897 Pre-fix the frontend resolved by title text alone via
898 ``navigateToNoteByTitle``, so renaming a linked note silently
899 broke every existing ``[[OldTitle]]`` in other notes' content.
900 """
901 try:
902 with get_user_db_session(self.username) as session:
903 document = self._get_note_in_session(session, note_id)
904 if document is None:
905 return None
907 result = self._document_to_note_dict(document, session)
909 # Outgoing-link resolution table for the frontend wiki-link
910 # renderer. Keep this lean (only the link_text and the
911 # target_id/title) — the full backlinks payload is served
912 # by GET /api/notes/<id>/outgoing-links on demand.
913 outgoing_links = (
914 session.query(
915 NoteLink.link_text,
916 Document.id,
917 Document.title,
918 )
919 .join(NoteLink, Document.id == NoteLink.target_document_id)
920 .filter(NoteLink.source_document_id == note_id)
921 .all()
922 )
923 result["outgoing_links"] = [
924 {
925 "link_text": row.link_text,
926 "target_id": row.id,
927 "target_title": row.title,
928 }
929 for row in outgoing_links
930 ]
931 return result
933 except Exception:
934 logger.exception("Error getting note {}", note_id)
935 raise
937 def update_note(
938 self,
939 note_id: str,
940 title: Optional[str] = None,
941 content: Optional[str] = None,
942 tags: Optional[List[str]] = None,
943 favorite: Optional[bool] = None,
944 _link_overrides: Optional[Dict[str, str]] = None,
945 _append_link_title: Optional[str] = None,
946 ) -> bool:
947 """Update a note.
949 Naming note: ``favorite`` matches ``Document.favorite`` (the column
950 backing this state). The user-facing UI vocabulary is "pin"; the
951 wire format keeps ``pinned`` for that reason and the route layer
952 translates. Don't rename the kwarg back without also updating the
953 Document column.
955 ``_append_link_title`` (internal, used by ``accept_suggested_link``):
956 when set, the new body is derived from the note's CURRENT content —
957 loaded fresh inside this method's write transaction — by appending a
958 ``[[title]]`` wiki-link, rather than being passed in precomputed by the
959 caller. This keeps the read-modify-write atomic and closes the
960 lost-update window where a concurrent save committed after the caller's
961 precondition read would otherwise be clobbered. Mutually exclusive with
962 ``content``.
963 """
964 _validate_title(title)
965 # Empty title is allowed on create's route-level guard, but here on
966 # update an explicit empty string would erase a previously valid
967 # title with no semantic meaning. Reject it.
968 if title is not None and not title:
969 raise ValueError("title must not be empty")
970 if content is not None:
971 _assert_content_size(content)
972 _validate_tags(tags)
973 try:
974 with get_user_db_session(self.username) as session:
975 document = self._get_note_in_session(session, note_id)
976 if document is None:
977 return False
979 # Append-mode (accept_suggested_link): derive the new body from
980 # the FRESHLY loaded row inside this write transaction, rather
981 # than from a value the caller read in an earlier, now-closed
982 # session. Doing the read-modify-write in one transaction closes
983 # the lost-update window where a concurrent save committed
984 # between the caller's precondition read and this write would
985 # otherwise be silently clobbered.
986 _append_reparse_only = False
987 if _append_link_title is not None:
988 current = document.text_content or ""
989 # If the exact wiki-link text is already in the body, don't
990 # DUPLICATE the line — but STILL reparse links so the target
991 # NoteLink is created/updated. This covers two cases:
992 # (a) a concurrent second accept of the SAME suggestion
993 # (double-submit / two tabs / a retry after a
994 # processed-but-timed-out response) whose first accept
995 # already appended the line and created the NoteLink —
996 # the reparse is then an idempotent no-op; and
997 # (b) accepting a suggestion for a link the user had TYPED
998 # while it was still unresolved (no NoteLink row exists
999 # yet, e.g. the target note was created later). Skipping
1000 # the reparse here would make the user's explicit accept
1001 # a silent no-op that never creates the link.
1002 if f"[[{_append_link_title}]]" in current:
1003 content = None
1004 _append_reparse_only = True
1005 else:
1006 content = (
1007 current.rstrip() + f"\n\n[[{_append_link_title}]]\n"
1008 )
1009 _assert_content_size(content)
1011 content_changed = (
1012 content is not None and content != document.text_content
1013 )
1014 title_changed = title is not None and title != document.title
1015 # Tags are part of the version state (they participate in
1016 # _compute_version_hash). A tag-only edit must produce a
1017 # snapshot — otherwise the hash improvement is dead code on
1018 # this path and the user's version history silently misses
1019 # tag-only changes.
1020 #
1021 # Use sorted() so reordering the same tag set
1022 # (e.g. ["b","a"] vs ["a","b"]) doesn't flip the trigger.
1023 # _compute_version_hash already sorts before hashing, so an
1024 # order-only "change" would just create a dedup-absorbed
1025 # snapshot trigger, wasting work; aligning the comparison
1026 # keeps trigger and hash consistent.
1027 tags_changed = tags is not None and sorted(
1028 t for t in tags if isinstance(t, str)
1029 ) != sorted(
1030 t for t in (document.tags or []) if isinstance(t, str)
1031 )
1032 old_content = document.text_content
1034 if title is not None:
1035 document.title = title
1036 if content is not None:
1037 document.text_content = content
1038 document.character_count = len(content)
1039 document.word_count = len(content.split())
1040 document.document_hash = self._compute_content_hash(
1041 f"{note_id}:{content}"
1042 )
1043 document.file_size = len(content.encode("utf-8"))
1044 if tags is not None:
1045 document.tags = tags
1046 if favorite is not None:
1047 document.favorite = favorite
1049 # Atomicity: document update + version snapshot + link
1050 # rewrite all share the same transaction. Pre-fix, each
1051 # ran in its own session, so a crash between them left a
1052 # committed content change with no MANUAL_SAVE row
1053 # or with zero outgoing links. The AI change-summary
1054 # generation still runs off-thread — it's enqueued AFTER
1055 # commit so a failed transaction won't leave a stranded
1056 # background task.
1057 version_id: Optional[str] = None
1058 version_created = False
1059 # (0, 0) means "prune wasn't run this call" as well as "ran
1060 # but nothing was over cap" — both cases skip the post-commit
1061 # log the same way, so a single sentinel default covers both.
1062 prune_counts: "tuple[int, int]" = (0, 0)
1063 # The version-snapshot INSERT is flushed inside
1064 # _create_version_snapshot_in_session, so a concurrent
1065 # identical-content save's UNIQUE(uix_note_version_content)
1066 # collision can fire at THAT flush, not only at the commit
1067 # below. Wrap the whole snapshot + link-reparse + commit block
1068 # so it is caught and recovered in one place — previously the
1069 # recovery guarded only commit(), so a flush-time collision
1070 # 500'd a benign concurrent save (the recovery was dead code).
1071 try:
1072 if content_changed or title_changed or tags_changed:
1073 (
1074 version_id,
1075 version_created,
1076 ) = self._create_version_snapshot_in_session(
1077 session,
1078 note_id=note_id,
1079 title=document.title,
1080 content=document.text_content,
1081 tags=document.tags,
1082 change_type=NoteChangeType.MANUAL_SAVE.value,
1083 change_summary=None,
1084 )
1085 # Counts are only bound here, not logged — the
1086 # delete is merely flushed, and this whole block can
1087 # still roll back below (IntegrityError recovery) or
1088 # in the outer handler. Logging happens after
1089 # session.commit() succeeds, so the log never claims
1090 # a prune a rollback undid.
1091 prune_counts = self._prune_versions_in_session(
1092 session, note_id
1093 )
1095 if content_changed or _append_reparse_only:
1096 # In append-reparse-only mode `content` is None (the body
1097 # already held the link text); reparse the CURRENT body
1098 # so the override still binds the NoteLink to the target.
1099 self._parse_and_update_links_in_session(
1100 session,
1101 note_id,
1102 content
1103 if content is not None
1104 else (document.text_content or ""),
1105 link_overrides=_link_overrides,
1106 )
1108 # Stale-vector fix: when the note's content or title
1109 # changes, mark its embeddings stale (both indexed-state
1110 # sources) so the auto-index worker re-embeds AND the RAG
1111 # status report doesn't keep showing it as indexed. See
1112 # _mark_note_stale_for_reindex_in_session.
1113 if content_changed or title_changed:
1114 self._mark_note_stale_for_reindex_in_session(
1115 session, note_id
1116 )
1118 session.commit()
1119 # Only reachable once the transaction holding the prune
1120 # deletes has actually committed — see
1121 # _prune_versions_in_session for why this can't log
1122 # from inside the prune itself.
1123 self._log_version_prune(note_id, prune_counts)
1124 except IntegrityError as exc:
1125 # Concurrency: two same-note saves carrying byte-identical
1126 # content both pass the in-session dedup pre-check in
1127 # _create_version_snapshot_in_session (neither sees the
1128 # other's uncommitted row), then race to INSERT the same
1129 # (document_id, content_hash). The winner commits; the
1130 # loser hits UNIQUE(uix_note_version_content) — at the
1131 # snapshot flush above or at the commit. That
1132 # collision means the identical version already exists and
1133 # the winner already persisted the identical document
1134 # content — so the user's save IS reflected. Recover
1135 # (rollback + return success) instead of 500'ing.
1136 #
1137 # Discriminator: only the version-dedup constraint carries
1138 # "content_hash" (SQLite reports the offending columns,
1139 # Postgres the constraint name uix_note_version_content —
1140 # both contain "content_hash"). Any other IntegrityError
1141 # (e.g. a NoteLink uix_note_link violation from the link
1142 # reparse) must still surface, so re-raise it.
1143 self._rollback_quietly(session)
1144 err = (
1145 str(exc.orig).lower() if exc.orig else str(exc).lower()
1146 )
1147 if "content_hash" not in err:
1148 raise
1149 logger.debug(
1150 "Concurrent identical-content save of note {} "
1151 "collided on the version-dedup UNIQUE; the existing "
1152 "version already represents this content. Treating as "
1153 "success.",
1154 note_id,
1155 )
1156 # The winner persisted the identical VERSIONED state
1157 # (title/content/tags — the fields in the version
1158 # hash), but ``favorite`` is not hashed: a loser that
1159 # also carried a pin change had it rolled back with
1160 # the rest of its transaction, so returning success
1161 # here would silently drop the pin. Re-apply it in a
1162 # fresh transaction — a favorite-only UPDATE cannot
1163 # collide on the version constraint. A failure here
1164 # propagates (outer handler) rather than reporting a
1165 # success the DB doesn't reflect.
1166 if favorite is not None:
1167 refetched = self._get_note_in_session(session, note_id)
1168 if ( 1168 ↛ 1174line 1168 didn't jump to line 1174 because the condition on line 1168 was always true
1169 refetched is not None
1170 and refetched.favorite != favorite
1171 ):
1172 refetched.favorite = favorite
1173 session.commit()
1174 return True
1176 logger.info("Updated note {}", note_id)
1178 # Post-commit: enqueue the LLM change-summary worker. The
1179 # snapshot row already exists with change_summary=None; the
1180 # worker UPDATEs it when the LLM returns. Only schedule it
1181 # for a genuinely NEW row: when the dedup check absorbed
1182 # the snapshot into an existing version (e.g. the user
1183 # reverted to an exact prior state), version_id points at a
1184 # HISTORICAL row whose change_summary the worker would
1185 # overwrite with a description of this much later edit.
1186 if (
1187 version_id
1188 and version_created
1189 and content_changed
1190 and content is not None
1191 ):
1192 # NB: intentionally NOT gated on `old_content` being
1193 # truthy. A first-content edit of a previously blank/NULL
1194 # note (old_content "" or None) is still worth summarizing
1195 # — the change-summary path handles it cheaply (`if not
1196 # old_content: return "Note created"`, no LLM call). Gating
1197 # on truthy old_content left that MANUAL_SAVE version's
1198 # change_summary NULL forever.
1199 # Capture the DB password from the request thread now;
1200 # the worker runs on a stdlib ThreadPoolExecutor which
1201 # does NOT copy ContextVar state, so the worker cannot
1202 # locate the password itself.
1203 dbpw = _capture_request_db_password(self.username)
1204 if dbpw is None:
1205 # Without the password the worker can't open the
1206 # encrypted DB to write the summary. Pre-fix this
1207 # was silently dropped by the worker itself; log
1208 # explicitly and skip the submit so operators can
1209 # see WHY summaries are missing.
1210 logger.warning(
1211 "Skipping AI change-summary for version {}: "
1212 "DB password not available in request thread.",
1213 version_id,
1214 )
1215 else:
1216 _submit_summary_task(
1217 _populate_change_summary_async,
1218 self.username,
1219 dbpw,
1220 version_id,
1221 old_content,
1222 content,
1223 )
1225 return True
1227 except Exception:
1228 # ``get_user_db_session``'s context manager does NOT rollback
1229 # on exception (it returns a cached request- or thread-scoped
1230 # session whose lifecycle is managed elsewhere). Helpers like
1231 # ``_create_version_snapshot_in_session`` call ``session.flush()``
1232 # at the end so a downstream failure (e.g. in link reparse)
1233 # would otherwise leave the document UPDATE durably flushed
1234 # without ever committing properly. Rollback explicitly here
1235 # so the atomicity guarantee holds.
1236 # ``session`` is bound iff we entered the with block; locals().get
1237 # avoids NameError when the failure happens before then, and
1238 # _rollback_quietly no-ops on None.
1239 self._rollback_quietly(locals().get("session"))
1240 logger.exception("Error updating note {}", note_id)
1241 raise
1243 def delete_note(self, note_id: str) -> bool:
1244 """Delete a note.
1246 Before the row is removed we collect the set of collections this note
1247 was indexed into, because the ``Document → DocumentCollection``
1248 cascade fires on delete and would otherwise hide them. After the
1249 delete commits we purge the corresponding ``DocumentChunk`` rows for
1250 each indexed collection via
1251 ``LibraryRAGService.purge_document_chunks`` — NOT
1252 ``remove_document_from_rag``, which looks the document up by its
1253 (now cascade-deleted) ``DocumentCollection`` join row and would
1254 silently no-op.
1256 Both halves of the RAG state are purged: the FAISS vectors first
1257 (``purge_document_vectors`` — replace-on-reindex can never fire
1258 again for a deleted document id, and the shared search flows
1259 rehydrate snippets straight from the ``DocumentChunk`` rows in the
1260 (encrypted) DB by id, with no Document-existence check, so
1261 lingering vectors would serve the deleted note's text
1262 indefinitely), then the ``DocumentChunk`` rows
1263 (``purge_document_chunks``). The ghost-hit filter in
1264 ``NoteAIService.semantic_search`` / ``find_similar_passages``
1265 remains as the safety net for cleanup failures and pre-fix
1266 leftovers.
1267 """
1268 try:
1269 indexed_collection_ids: List[str] = []
1270 with get_user_db_session(self.username) as session:
1271 document = self._get_note_in_session(session, note_id)
1272 if document is None:
1273 return False
1275 # Capture (before the cascade destroys them) every collection
1276 # where this note might have vectors to purge: the UNION of
1277 # DocumentCollection.indexed=True AND collections that actually
1278 # have chunk rows. The chunk-row half is essential — an edited
1279 # note has indexed=False during the edit->reindex window while
1280 # its pre-edit chunks/vectors still exist, so gating on the
1281 # indexed flag alone would strand orphaned vectors.
1282 indexed_rows = (
1283 session.query(DocumentCollection.collection_id)
1284 .filter_by(document_id=note_id, indexed=True)
1285 .all()
1286 )
1287 chunk_rows = (
1288 session.query(DocumentChunk.collection_name)
1289 .filter_by(source_type="document", source_id=note_id)
1290 .distinct()
1291 .all()
1292 )
1293 indexed_collection_ids = list(
1294 {row[0] for row in indexed_rows}
1295 | {
1296 row[0].removeprefix("collection_")
1297 for row in chunk_rows
1298 if row[0]
1299 }
1300 )
1302 session.delete(document)
1303 session.commit()
1304 logger.info("Deleted note {}", note_id)
1306 # Best-effort FAISS / chunk cleanup. Failures must not surface
1307 # to the user (the DB delete already committed) but should be
1308 # logged for ops visibility.
1309 if indexed_collection_ids:
1310 dbpw = _capture_request_db_password(self.username)
1311 from ...services.rag_service_factory import get_rag_service
1313 for collection_id in indexed_collection_ids:
1314 try:
1315 # A FRESH service per collection, built via the
1316 # factory so it resolves THAT collection's stored
1317 # embedding model/provider (get_rag_service reads
1318 # Collection settings; a hardcoded-default
1319 # LibraryRAGService would compute the wrong index
1320 # hash and silently purge nothing for any
1321 # non-default-embedding collection). Per-collection
1322 # instances also avoid one collection's cached
1323 # ``rag_index_record`` / loaded vector index
1324 # carrying over into another collection's
1325 # ``purge_document_vectors`` call.
1326 with get_rag_service(
1327 self.username,
1328 collection_id=collection_id,
1329 db_password=dbpw,
1330 ) as rag:
1331 # Vectors BEFORE chunk rows: purge_document_vectors
1332 # -> VectorIndex.delete looks up the DocumentChunk
1333 # rows by (source_type, source_id, collection_name)
1334 # to learn which FAISS int ids to remove, and
1335 # deletes those matching rows itself. The rows
1336 # must still exist when this runs, or the lookup
1337 # finds nothing and the vectors (and their
1338 # persisted text) are stranded in the FAISS store
1339 # forever. purge_document_chunks below then only
1340 # has to catch whatever purge_document_vectors
1341 # didn't match (e.g. no current FAISS index).
1342 rag.purge_document_vectors(note_id, collection_id)
1343 # purge_document_chunks, not
1344 # remove_document_from_rag: the Document (and
1345 # its DocumentCollection join row) is already
1346 # cascade-deleted, so the join-row lookup in
1347 # remove_document_from_rag would no-op and
1348 # leave the chunks orphaned.
1349 rag.purge_document_chunks(note_id, collection_id)
1350 except Exception:
1351 # exception=True keeps the stack trace. `dbpw` is a
1352 # live local, but frame-local values render only under
1353 # the opt-in LDR_LOGURU_DIAGNOSE dev flag — every
1354 # persistent sink sets diagnose=False — so the password
1355 # cannot leak into logs in normal operation.
1356 logger.opt(exception=True).error(
1357 "Failed to remove RAG entries for note {} "
1358 "from collection {}",
1359 note_id,
1360 collection_id,
1361 )
1363 # Guaranteed backstop: delete any DocumentChunk rows the per-
1364 # collection purge above could not reach (get_rag_service failing
1365 # because the request-thread password is unavailable, a transient
1366 # FAISS/DB error, a stored-config mismatch). chunk_text holds the
1367 # note's content and collection search rehydrates hits purely by
1368 # DocumentChunk.id — a surviving row would keep serving the deleted
1369 # note's text. The matching orphaned vector then resolves to no row
1370 # and is filtered out. Mirrors DocumentDeletionService's backstop.
1371 try:
1372 with get_user_db_session(self.username) as sweep_session:
1373 sweep_session.query(DocumentChunk).filter_by(
1374 source_type="document", source_id=note_id
1375 ).delete(synchronize_session=False)
1376 sweep_session.commit()
1377 except Exception:
1378 logger.opt(exception=True).error(
1379 "Failed to sweep residual chunk rows for note {}", note_id
1380 )
1382 return True
1384 except Exception:
1385 logger.exception("Error deleting note {}", note_id)
1386 raise
1388 def _build_filtered_notes_query(
1389 self,
1390 session: Session,
1391 collection_id: Optional[str],
1392 search: Optional[str],
1393 pinned_only: bool,
1394 ):
1395 """Build the filtered Document query used by both ``list_notes``
1396 and ``count_notes``. Centralising the filter shape keeps the
1397 list response and its ``total`` count in lockstep.
1398 """
1399 source_type_id = self._get_note_source_type_id(session)
1400 query = session.query(Document).filter(
1401 Document.source_type_id == source_type_id
1402 )
1404 if collection_id: 1404 ↛ 1405line 1404 didn't jump to line 1405 because the condition on line 1404 was never true
1405 query = query.join(DocumentCollection).filter(
1406 DocumentCollection.collection_id == collection_id
1407 )
1409 if search:
1410 search_pattern = f"%{self._escape_like(search)}%"
1411 query = query.filter(
1412 or_(
1413 Document.title.ilike(search_pattern, escape="\\"),
1414 Document.text_content.ilike(search_pattern, escape="\\"),
1415 )
1416 )
1418 if pinned_only:
1419 query = query.filter(Document.favorite.is_(True))
1421 return query
1423 def count_notes(
1424 self,
1425 collection_id: Optional[str] = None,
1426 search: Optional[str] = None,
1427 pinned_only: bool = False,
1428 ) -> int:
1429 """Count notes matching the same filter as ``list_notes``.
1431 Used by the list endpoint so the frontend can render real
1432 pagination controls (page-N-of-M, "load more" with bounded
1433 progress, etc.) without having to call ``list_notes`` with a
1434 huge limit just to compute the size.
1435 """
1436 with get_user_db_session(self.username) as session:
1437 return self._build_filtered_notes_query(
1438 session, collection_id, search, pinned_only
1439 ).count()
1441 def list_notes(
1442 self,
1443 collection_id: Optional[str] = None,
1444 search: Optional[str] = None,
1445 pinned_only: bool = False,
1446 limit: int = 100,
1447 offset: int = 0,
1448 ) -> List[Dict[str, Any]]:
1449 """List notes with optional filtering."""
1450 try:
1451 with get_user_db_session(self.username) as session:
1452 query = self._build_filtered_notes_query(
1453 session, collection_id, search, pinned_only
1454 )
1456 query = query.order_by(
1457 Document.favorite.desc(),
1458 Document.updated_at.desc(),
1459 # Stable tiebreaker: without it, notes sharing an
1460 # updated_at (bulk-created) have no guaranteed order, so
1461 # offset pagination ("Load more") could repeat or skip
1462 # rows between pages; mirrors the mention/link panels.
1463 Document.id.desc(),
1464 )
1466 query = query.offset(offset).limit(limit)
1467 documents = query.all()
1469 if not documents:
1470 return []
1472 doc_ids = [d.id for d in documents]
1474 # Batched aggregates: replaces 3 per-row sub-queries inside
1475 # _document_to_note_dict with 3 grouped queries total. Default
1476 # limit=100 went from 300 extra queries/page to 3.
1477 collection_counts = dict(
1478 session.query(
1479 DocumentCollection.document_id,
1480 func.count(DocumentCollection.id),
1481 )
1482 .filter(DocumentCollection.document_id.in_(doc_ids))
1483 .group_by(DocumentCollection.document_id)
1484 .all()
1485 )
1486 research_counts = dict(
1487 session.query(
1488 NoteResearch.document_id,
1489 func.count(NoteResearch.id),
1490 )
1491 .filter(NoteResearch.document_id.in_(doc_ids))
1492 .group_by(NoteResearch.document_id)
1493 .all()
1494 )
1495 indexed_rows = (
1496 session.query(DocumentCollection)
1497 .filter(
1498 DocumentCollection.document_id.in_(doc_ids),
1499 DocumentCollection.indexed.is_(True),
1500 )
1501 .all()
1502 )
1503 indexed_by_doc: Dict[str, DocumentCollection] = {}
1504 for row in indexed_rows: 1504 ↛ 1506line 1504 didn't jump to line 1506 because the loop on line 1504 never started
1505 # Mirror prior .first() semantics: first indexed row wins
1506 indexed_by_doc.setdefault(row.document_id, row)
1508 return [
1509 self._document_to_note_dict(
1510 doc,
1511 session,
1512 _collection_count=collection_counts.get(doc.id, 0),
1513 _research_count=research_counts.get(doc.id, 0),
1514 _indexed_coll=indexed_by_doc.get(doc.id),
1515 include_full_content=False,
1516 )
1517 for doc in documents
1518 ]
1520 except Exception:
1521 logger.exception("Error listing notes")
1522 raise
1524 def add_to_collection(self, note_id: str, collection_id: str) -> bool:
1525 """Add a note to a collection."""
1526 try:
1527 with get_user_db_session(self.username) as session:
1528 # Only operate on notes — every other mutating method guards
1529 # with _is_note, but these collection helpers did not, so a
1530 # non-note Document id could get note-collection rows attached.
1531 doc = self._get_note_in_session(session, note_id)
1532 if doc is None:
1533 return False
1535 # Validate the target collection exists before inserting the
1536 # join row. Otherwise a stale/bogus collection_id trips the FK
1537 # constraint and surfaces as a generic 500 instead of a clean
1538 # 404 (mirrors create_note, which filters to valid ids).
1539 collection = (
1540 session.query(Collection)
1541 .filter_by(id=collection_id)
1542 .first()
1543 )
1544 if collection is None:
1545 raise LookupError( # noqa: TRY301 — except logs and re-raises to the route as a 404
1546 f"Collection {collection_id} not found"
1547 )
1549 existing = (
1550 session.query(DocumentCollection)
1551 .filter_by(document_id=note_id, collection_id=collection_id)
1552 .first()
1553 )
1555 if existing:
1556 return False
1558 doc_coll = DocumentCollection(
1559 document_id=note_id,
1560 collection_id=collection_id,
1561 indexed=False,
1562 )
1563 session.add(doc_coll)
1564 session.commit()
1565 logger.info(
1566 f"Added note {note_id} to collection {collection_id}"
1567 )
1568 return True
1570 except IntegrityError:
1571 # TOCTOU on UNIQUE(document_id, collection_id): a concurrent add of
1572 # the same note+collection raced past the existing-row check above.
1573 # get_user_db_session already rolled the shared session back; return
1574 # the idempotent "already a member" outcome (route → 409) instead of
1575 # letting the constraint violation surface as a 500.
1576 return False
1577 except Exception:
1578 logger.exception("Error adding note {} to collection", note_id)
1579 raise
1581 def remove_from_collection(self, note_id: str, collection_id: str) -> bool:
1582 """Remove a note from a collection.
1584 Raises ValueError when ``collection_id`` is the system Notes
1585 collection: it is the persistent home of every note (see
1586 ``_get_or_create_notes_collection``), and the deletion services
1587 rely on that invariant — a note unlinked from Notes but still in
1588 a user collection would be hard-deleted as an "orphan" when that
1589 collection is deleted.
1590 """
1591 try:
1592 was_indexed = False
1593 with get_user_db_session(self.username) as session:
1594 doc = self._get_note_in_session(session, note_id)
1595 if doc is None:
1596 return False
1598 collection = (
1599 session.query(Collection)
1600 .filter_by(id=collection_id)
1601 .first()
1602 )
1603 if (
1604 collection is not None
1605 and collection.collection_type == "notes"
1606 ):
1607 raise ValueError( # noqa: TRY301 — needs the db session; except logs and re-raises
1608 "Cannot remove a note from the Notes collection — "
1609 "it is the permanent home of every note"
1610 )
1612 doc_coll = (
1613 session.query(DocumentCollection)
1614 .filter_by(document_id=note_id, collection_id=collection_id)
1615 .first()
1616 )
1618 link_existed = doc_coll is not None
1619 if doc_coll:
1620 # Purge if this collection is flagged indexed OR the note
1621 # still has chunk rows here — the chunk-row half catches the
1622 # edit->reindex window where indexed=False but the pre-edit
1623 # chunks/vectors still exist. Captured before the link is
1624 # deleted.
1625 was_indexed = bool(doc_coll.indexed) or (
1626 session.query(DocumentChunk.id)
1627 .filter_by(
1628 source_type="document",
1629 source_id=note_id,
1630 collection_name=f"collection_{collection_id}",
1631 )
1632 .first()
1633 is not None
1634 )
1635 session.delete(doc_coll)
1636 session.commit()
1637 logger.info(
1638 f"Removed note {note_id} from collection {collection_id}"
1639 )
1640 else:
1641 # The link is already gone (a concurrent removal). Do NOT
1642 # return early: a racing index_document could have (re)created
1643 # chunk rows for this (note, collection) AFTER the link was
1644 # deleted, and there is no other route-level retry for that
1645 # pair — an early return would leave the removed note's
1646 # plaintext searchable here forever. Fall through to the
1647 # unconditional backstop chunk sweep below (which removes the
1648 # plaintext rows); the full RAG vector purge is skipped since
1649 # there is no link to unlink.
1650 was_indexed = False
1652 # Best-effort FAISS + chunk cleanup for the collection the note was
1653 # unlinked from — mirrors delete_note. Without it, the note's chunk
1654 # rows and vectors linger in that collection's index, so the removed
1655 # note stays fully searchable there. Vectors BEFORE chunk rows (the
1656 # int-id lookup needs the rows to still exist). Uses get_rag_service
1657 # so THAT collection's stored embedding model resolves the right
1658 # index hash. Failures are logged, not surfaced (the unlink already
1659 # committed).
1660 if was_indexed:
1661 dbpw = _capture_request_db_password(self.username)
1662 from ...services.rag_service_factory import get_rag_service
1664 try:
1665 with get_rag_service(
1666 self.username,
1667 collection_id=collection_id,
1668 db_password=dbpw,
1669 ) as rag:
1670 rag.purge_document_vectors(note_id, collection_id)
1671 rag.purge_document_chunks(note_id, collection_id)
1672 except Exception:
1673 # exception=True keeps the stack trace. `dbpw` (the DB
1674 # password) is a live local, but frame-local values render
1675 # only under the opt-in LDR_LOGURU_DIAGNOSE dev flag — every
1676 # persistent sink sets diagnose=False — so it cannot leak.
1677 logger.opt(exception=True).error(
1678 "Failed to purge RAG entries for note {} removed from "
1679 "collection {}",
1680 note_id,
1681 collection_id,
1682 )
1684 # Guaranteed backstop, scoped to THIS collection only (the note
1685 # survives in its other collections): delete any DocumentChunk rows
1686 # the purge above could not reach, so the unlinked note's text stops
1687 # surfacing in this collection's search even when the RAG purge
1688 # failed. Mirrors DocumentDeletionService's scoped backstop.
1689 try:
1690 with get_user_db_session(self.username) as sweep_session:
1691 sweep_session.query(DocumentChunk).filter_by(
1692 source_type="document",
1693 source_id=note_id,
1694 collection_name=f"collection_{collection_id}",
1695 ).delete(synchronize_session=False)
1696 sweep_session.commit()
1697 except Exception:
1698 logger.opt(exception=True).error(
1699 "Failed to sweep residual chunk rows for note {} in "
1700 "collection {}",
1701 note_id,
1702 collection_id,
1703 )
1704 # False when the link was already gone (nothing to unlink), True when
1705 # this call performed the removal — preserves the prior contract.
1706 return link_existed
1708 except Exception:
1709 logger.exception("Error removing note {} from collection", note_id)
1710 raise
1712 def get_note_collections(self, note_id: str) -> List[Dict[str, Any]]:
1713 """Get collections a note belongs to."""
1714 try:
1715 with get_user_db_session(self.username) as session:
1716 # Use JOIN to avoid N+1 query issue
1717 results = (
1718 session.query(Collection, DocumentCollection)
1719 .join(
1720 DocumentCollection,
1721 Collection.id == DocumentCollection.collection_id,
1722 )
1723 .filter(DocumentCollection.document_id == note_id)
1724 .all()
1725 )
1727 return [
1728 {
1729 "id": coll.id,
1730 "name": coll.name,
1731 # The UI hides the remove control for the system
1732 # Notes collection (every note's persistent home;
1733 # remove_from_collection refuses it server-side).
1734 "collection_type": coll.collection_type,
1735 "indexed": dc.indexed,
1736 "chunk_count": dc.chunk_count or 0,
1737 }
1738 for coll, dc in results
1739 ]
1741 except Exception:
1742 logger.exception("Error getting collections for note {}", note_id)
1743 raise
1745 def get_note_research(self, note_id: str) -> List[Dict[str, Any]]:
1746 """Get research runs triggered from a note."""
1747 try:
1748 with get_user_db_session(self.username) as session:
1749 note_research = (
1750 session.query(NoteResearch)
1751 .options(joinedload(NoteResearch.research))
1752 .filter_by(document_id=note_id)
1753 .order_by(NoteResearch.display_order.asc())
1754 .all()
1755 )
1757 return [
1758 {
1759 "id": nr.id,
1760 "research_id": nr.research_id,
1761 "search_engine": (nr.research.research_meta or {})
1762 .get("submission", {})
1763 .get("search_engine")
1764 if nr.research
1765 else None,
1766 "research_mode": nr.research.mode
1767 if nr.research
1768 else None,
1769 "query_used": nr.research.query
1770 if nr.research
1771 else None,
1772 "display_order": nr.display_order,
1773 "is_collapsed": nr.is_collapsed,
1774 "created_at": nr.created_at.isoformat()
1775 if nr.created_at
1776 else None,
1777 }
1778 for nr in note_research
1779 ]
1781 except Exception:
1782 logger.exception("Error getting research for note {}", note_id)
1783 raise
1785 # Max retries on display_order UNIQUE collision. The old value of 5
1786 # was contention-fragile: colliding threads re-read MAX(display_order)
1787 # in lockstep, so under 6+ concurrent same-note links one thread could
1788 # lose all 5 races and 500 the user (dropping the link). Each retry can
1789 # only fail because another writer landed at the same display_order, and
1790 # the number of concurrent NoteResearch inserts is itself bounded by
1791 # MAX_RESEARCH_PER_NOTE — so a budget comfortably above that, combined
1792 # with the randomized jitter below, makes losing every race effectively
1793 # impossible while never masking a non-retryable error.
1794 _LINK_RESEARCH_MAX_RETRIES = MAX_RESEARCH_PER_NOTE + 10
1796 def link_research_to_note(
1797 self,
1798 note_id: str,
1799 research_id: str,
1800 ) -> int:
1801 """Link a research run to a note.
1803 Concurrency: two same-user requests (e.g. double-click or two
1804 tabs) can both observe the same MAX(display_order) and try to
1805 insert with the same display_order. The UNIQUE constraint on
1806 (document_id, display_order) added in migration 0021 turns the
1807 race into an IntegrityError; we catch it, rollback, recompute
1808 MAX, and retry up to a small bound. The
1809 (document_id, research_id) UniqueConstraint is separate — that
1810 one signals "research already linked", which surfaces as
1811 IntegrityError too. We distinguish the two by looking for the
1812 ``display_order`` token in the error: SQLite reports the offending
1813 COLUMNS (not the constraint name) and Postgres reports the constraint
1814 name ``uix_note_research_display_order`` — both contain
1815 ``display_order``, while the duplicate-link and FK errors do not. Only
1816 the display_order collision is retryable; everything else surfaces
1817 immediately so a legitimate duplicate link (or an FK violation) isn't
1818 masked by burning retries.
1819 """
1820 for attempt in range(self._LINK_RESEARCH_MAX_RETRIES): 1820 ↛ 1921line 1820 didn't jump to line 1921 because the loop on line 1820 didn't complete
1821 session = None
1822 try:
1823 with get_user_db_session(self.username) as session:
1824 # Every other mutating method guards with _is_note;
1825 # without this, a NoteResearch row can be attached to
1826 # any Document (e.g. an uploaded PDF) — orphaned rows
1827 # the notes UI never renders.
1828 doc = self._get_note_in_session(session, note_id)
1829 if doc is None:
1830 raise ValueError( # noqa: TRY301 — needs the db session; except rolls back and re-raises
1831 f"Note {note_id} not found"
1832 )
1834 # Enforce MAX_RESEARCH_PER_NOTE where rows are created
1835 # (this is the only NoteResearch creation site). The
1836 # cap previously existed only in reorder_note_research,
1837 # so a note could grow past it and then every reorder
1838 # of the (necessarily full) list failed on the same
1839 # cap — permanently un-reorderable.
1840 existing_count = (
1841 session.query(func.count(NoteResearch.id))
1842 .filter_by(document_id=note_id)
1843 .scalar()
1844 )
1845 if existing_count >= MAX_RESEARCH_PER_NOTE:
1846 raise ValueError( # noqa: TRY301 — needs the db session; except rolls back and re-raises
1847 f"too many linked researches "
1848 f"(max {MAX_RESEARCH_PER_NOTE})"
1849 )
1851 # Use MAX(display_order) + 1, not COUNT, so deleting an
1852 # earlier link can't make the next insert reuse an
1853 # existing display_order. Research runs CASCADE-delete
1854 # NoteResearch rows when a ResearchHistory is removed,
1855 # so COUNT < MAX+1 is a real possibility.
1856 max_order = (
1857 session.query(
1858 func.coalesce(
1859 func.max(NoteResearch.display_order), -1
1860 )
1861 )
1862 .filter_by(document_id=note_id)
1863 .scalar()
1864 )
1866 note_research = NoteResearch(
1867 document_id=note_id,
1868 research_id=research_id,
1869 display_order=max_order + 1,
1870 )
1871 session.add(note_research)
1872 session.commit()
1873 logger.info(
1874 "Linked research {} to note {}", research_id, note_id
1875 )
1876 return note_research.id
1878 except IntegrityError as exc:
1879 # The shared per-user request session is NOT rolled back by the
1880 # context manager on exception (it only closes a session it
1881 # created), so without an explicit rollback the next retry reuses
1882 # a poisoned session and fails identically.
1883 self._rollback_quietly(session)
1884 msg = str(exc.orig).lower() if exc.orig else str(exc).lower()
1885 # Only a (document_id, display_order) collision is retryable.
1886 # The duplicate (document_id, research_id) link and FK violations
1887 # contain no "display_order" token → surface immediately.
1888 if "display_order" not in msg:
1889 raise
1890 if attempt + 1 == self._LINK_RESEARCH_MAX_RETRIES:
1891 logger.exception(
1892 "Giving up linking research {} to note {} after "
1893 "{} display_order collisions",
1894 research_id,
1895 note_id,
1896 self._LINK_RESEARCH_MAX_RETRIES,
1897 )
1898 raise
1899 logger.debug(
1900 "display_order collision linking research {} to note "
1901 "{} (attempt {}); retrying",
1902 research_id,
1903 note_id,
1904 attempt + 1,
1905 )
1906 # Backoff with randomized jitter so colliding threads
1907 # don't re-read MAX(display_order) in perfect lockstep and
1908 # collide again on the very next attempt. Spreading the
1909 # retries breaks the synchronized contention that made a
1910 # fixed, no-backoff budget exhaustible under load.
1911 # noqa S311: jitter for retry-backoff scheduling, not a
1912 # security/crypto context — non-CSPRNG randomness is fine.
1913 time.sleep(random.uniform(0, 0.01 * (attempt + 1))) # noqa: S311
1914 continue
1915 except Exception:
1916 self._rollback_quietly(session)
1917 logger.exception("Error linking research to note {}", note_id)
1918 raise
1920 # Unreachable — the loop either returns or raises.
1921 raise RuntimeError("link_research_to_note exhausted retries")
1923 def get_notes_for_research(
1924 self, research_id: str
1925 ) -> Optional[List[Dict[str, Any]]]:
1926 """List the notes linked to a research run, newest link first.
1928 The reverse direction of ``get_research_for_note`` — powers the
1929 Notes panel on the research results page. Returns ``None`` when
1930 the research run doesn't exist (the route maps that to 404,
1931 distinct from "exists but has no notes yet" → ``[]``).
1933 Column-projected like ``get_backlinks``: ``text_content`` can be
1934 megabytes per note, and this renders a card list that needs only
1935 a short preview.
1936 """
1937 from ....database.models import ResearchHistory
1939 preview_len = self.LIST_CONTENT_PREVIEW_CHARS
1940 with get_user_db_session(self.username) as session:
1941 if (
1942 session.query(ResearchHistory.id)
1943 .filter_by(id=research_id)
1944 .first()
1945 is None
1946 ):
1947 return None
1948 rows = (
1949 session.query(
1950 Document.id,
1951 Document.title,
1952 func.substr(Document.text_content, 1, preview_len + 1),
1953 Document.updated_at,
1954 NoteResearch.created_at,
1955 )
1956 .join(NoteResearch, NoteResearch.document_id == Document.id)
1957 .filter(NoteResearch.research_id == research_id)
1958 .order_by(NoteResearch.created_at.desc())
1959 .all()
1960 )
1961 notes = []
1962 for note_id, title, preview, updated_at, linked_at in rows:
1963 preview = self._truncate_preview(preview, preview_len)
1964 notes.append(
1965 {
1966 "id": note_id,
1967 "title": title,
1968 "content_preview": preview,
1969 "updated_at": updated_at.isoformat()
1970 if updated_at
1971 else None,
1972 "linked_at": linked_at.isoformat()
1973 if linked_at
1974 else None,
1975 }
1976 )
1977 return notes
1979 def _create_note_with_cleanup(
1980 self, title, content, tags, link_fn, context_label
1981 ):
1982 """Create a note and run its post-create ``link_fn`` step
1983 atomically: if linking raises, delete the just-created note so no
1984 orphan survives, then re-raise the original error.
1986 Shared by ``create_note_for_research`` /
1987 ``create_note_for_document`` — the two paths that create-then-link,
1988 where a mid-step failure would otherwise leave a note the user
1989 never asked for. ``context_label`` is the full link-step phrase up
1990 to (and including) its preposition, so the log reads naturally
1991 (e.g. ``"Linking research <id> to"`` → "... to fresh note ...").
1992 """
1993 note_id = self.create_note(title=title, content=content, tags=tags)
1994 try:
1995 link_fn(note_id)
1996 except Exception:
1997 logger.exception(
1998 "{} fresh note {} failed; removing the note so the "
1999 "operation stays all-or-nothing",
2000 context_label,
2001 note_id,
2002 )
2003 try:
2004 self.delete_note(note_id)
2005 except Exception:
2006 # Both halves failed — surface the original linking error;
2007 # the orphan note is visible (not silently lost data).
2008 logger.exception(
2009 "Cleanup of orphan note {} also failed", note_id
2010 )
2011 raise
2012 return note_id
2014 def create_note_for_research(
2015 self,
2016 research_id: str,
2017 title: Optional[str] = None,
2018 content: Optional[str] = None,
2019 tags: Optional[List[str]] = None,
2020 quote: Optional[str] = None,
2021 prefix: Optional[str] = None,
2022 suffix: Optional[str] = None,
2023 ) -> str:
2024 """Create a note and link it to a research run, all-or-nothing.
2026 Used by the results page ("Add note", "Save as note", clip). The
2027 research row is checked FIRST so a bogus id gets a clean
2028 ``LookupError`` (route → 404) instead of creating an orphan note
2029 and then FK-failing on the link. If the link step fails anyway
2030 (e.g. a concurrent research delete), the just-created note is
2031 removed so the caller never observes a half-done operation.
2033 ``title``/``content`` default to a starter derived from the
2034 research query, so the "Add note" button needs no user input
2035 before navigating into the editor.
2036 """
2037 from ....database.models import ResearchHistory
2039 with get_user_db_session(self.username) as session:
2040 research = (
2041 session.query(ResearchHistory.id, ResearchHistory.query)
2042 .filter_by(id=research_id)
2043 .first()
2044 )
2045 if research is None:
2046 raise LookupError("Research not found")
2048 query_text = (research.query or "").strip()
2049 if not title:
2050 title = f"Notes on: {query_text}"[:MAX_TITLE_LENGTH]
2051 if not content:
2052 label = escape_markdown_link_label(query_text or research_id)
2053 content = (
2054 f"> Notes on the research run "
2055 f"[{label}](/results/{research_id}).\n\n"
2056 )
2058 def _link(new_note_id):
2059 self.link_research_to_note(new_note_id, research_id)
2060 if quote:
2061 # An inline annotation: also record the quote anchor so the
2062 # results page can highlight the passage (NULL-quote
2063 # references are plain links and skip this).
2064 self._add_reference(
2065 new_note_id,
2066 target_research_id=research_id,
2067 quote=quote,
2068 prefix=prefix,
2069 suffix=suffix,
2070 )
2072 return self._create_note_with_cleanup(
2073 title, content, tags, _link, f"Linking research {research_id} to"
2074 )
2076 def _add_reference(
2077 self,
2078 note_id: str,
2079 target_document_id: Optional[str] = None,
2080 target_research_id: Optional[str] = None,
2081 quote: Optional[str] = None,
2082 prefix: Optional[str] = None,
2083 suffix: Optional[str] = None,
2084 ) -> int:
2085 """Insert a NoteReference row (see the model for semantics)."""
2086 with get_user_db_session(self.username) as session:
2087 ref = NoteReference(
2088 note_id=note_id,
2089 target_document_id=target_document_id,
2090 target_research_id=target_research_id,
2091 quote=quote,
2092 prefix=prefix,
2093 suffix=suffix,
2094 )
2095 session.add(ref)
2096 try:
2097 session.commit()
2098 except Exception:
2099 self._rollback_quietly(session)
2100 raise
2101 return ref.id
2103 def get_notes_for_document(
2104 self, document_id: str
2105 ) -> Optional[List[Dict[str, Any]]]:
2106 """List the notes referencing a library document, newest first.
2108 Returns ``None`` when the document doesn't exist (route → 404).
2109 Column-projected like ``get_notes_for_research``; a note with
2110 several references (e.g. two annotations) appears once.
2111 """
2112 preview_len = self.LIST_CONTENT_PREVIEW_CHARS
2113 with get_user_db_session(self.username) as session:
2114 if (
2115 session.query(Document.id).filter_by(id=document_id).first()
2116 is None
2117 ):
2118 return None
2119 rows = (
2120 session.query(
2121 Document.id,
2122 Document.title,
2123 func.substr(Document.text_content, 1, preview_len + 1),
2124 Document.updated_at,
2125 func.max(NoteReference.created_at),
2126 )
2127 .join(NoteReference, NoteReference.note_id == Document.id)
2128 .filter(NoteReference.target_document_id == document_id)
2129 .group_by(Document.id)
2130 .order_by(func.max(NoteReference.created_at).desc())
2131 .all()
2132 )
2133 notes = []
2134 for note_id, title, preview, updated_at, linked_at in rows:
2135 preview = self._truncate_preview(preview, preview_len)
2136 notes.append(
2137 {
2138 "id": note_id,
2139 "title": title,
2140 "content_preview": preview,
2141 "updated_at": updated_at.isoformat()
2142 if updated_at
2143 else None,
2144 "linked_at": linked_at.isoformat()
2145 if linked_at
2146 else None,
2147 }
2148 )
2149 return notes
2151 def create_note_for_document(
2152 self,
2153 document_id: str,
2154 title: Optional[str] = None,
2155 content: Optional[str] = None,
2156 tags: Optional[List[str]] = None,
2157 quote: Optional[str] = None,
2158 prefix: Optional[str] = None,
2159 suffix: Optional[str] = None,
2160 ) -> str:
2161 """Create a note referencing a library document, all-or-nothing.
2163 The document-side twin of ``create_note_for_research``. Targets
2164 must be non-note documents: annotating a NOTE is deliberately
2165 unsupported — notes are mutable, so quote anchors would drift on
2166 every edit (the re-anchoring problem); library documents' extracted
2167 text is immutable, like research reports.
2168 """
2169 with get_user_db_session(self.username) as session:
2170 row = (
2171 session.query(Document.id, Document.title)
2172 .filter_by(id=document_id)
2173 .first()
2174 )
2175 if row is None:
2176 raise LookupError("Document not found")
2177 note_source_type_id = self._get_note_source_type_id(session)
2178 is_note = (
2179 session.query(Document.id)
2180 .filter_by(id=document_id, source_type_id=note_source_type_id)
2181 .first()
2182 is not None
2183 )
2184 if is_note:
2185 raise ValueError(
2186 "notes cannot be annotated — their content is mutable, "
2187 "so quote anchors would drift on edit; annotate library "
2188 "documents or research reports instead"
2189 )
2190 doc_title = (row.title or "").strip() or document_id
2192 if not title:
2193 title = f"Notes on: {doc_title}"[:MAX_TITLE_LENGTH]
2194 if not content:
2195 label = escape_markdown_link_label(doc_title)
2196 content = (
2197 f"> Notes on the document "
2198 f"[{label}](/library/document/{document_id}).\n\n"
2199 )
2201 def _link(new_note_id):
2202 self._add_reference(
2203 new_note_id,
2204 target_document_id=document_id,
2205 quote=quote,
2206 prefix=prefix,
2207 suffix=suffix,
2208 )
2210 return self._create_note_with_cleanup(
2211 title,
2212 content,
2213 tags,
2214 _link,
2215 f"Referencing document {document_id} from",
2216 )
2218 def get_annotations_for_target(
2219 self,
2220 document_id: Optional[str] = None,
2221 research_id: Optional[str] = None,
2222 ) -> List[Dict[str, Any]]:
2223 """List the anchored references (inline annotations) for a target.
2225 Existence of the target is the caller's concern (the routes check
2226 it for the 404); a valid target with no anchors returns ``[]``.
2227 """
2228 preview_len = self.LIST_CONTENT_PREVIEW_CHARS
2229 with get_user_db_session(self.username) as session:
2230 q = (
2231 session.query(
2232 NoteReference.note_id,
2233 NoteReference.quote,
2234 NoteReference.prefix,
2235 NoteReference.suffix,
2236 NoteReference.created_at,
2237 Document.title,
2238 func.substr(Document.text_content, 1, preview_len + 1),
2239 )
2240 .join(Document, Document.id == NoteReference.note_id)
2241 .filter(NoteReference.quote.isnot(None))
2242 )
2243 if document_id is not None:
2244 q = q.filter(NoteReference.target_document_id == document_id)
2245 else:
2246 q = q.filter(NoteReference.target_research_id == research_id)
2247 annotations = []
2248 for row in q.order_by(NoteReference.created_at.asc()).all():
2249 (
2250 note_id,
2251 quote,
2252 prefix,
2253 suffix,
2254 created_at,
2255 title,
2256 preview,
2257 ) = row
2258 preview = self._truncate_preview(preview, preview_len)
2259 annotations.append(
2260 {
2261 "note_id": note_id,
2262 "quote": quote or "",
2263 "prefix": prefix or "",
2264 "suffix": suffix or "",
2265 "created_at": created_at.isoformat()
2266 if created_at
2267 else None,
2268 "note_title": title,
2269 "comment_preview": preview,
2270 }
2271 )
2272 return annotations
2274 def has_annotation(
2275 self,
2276 note_id: str,
2277 document_id: Optional[str] = None,
2278 research_id: Optional[str] = None,
2279 ) -> bool:
2280 """True when an anchored reference ties note_id to the target
2281 (the DELETE routes' precondition)."""
2282 with get_user_db_session(self.username) as session:
2283 q = session.query(NoteReference.id).filter(
2284 NoteReference.note_id == note_id,
2285 NoteReference.quote.isnot(None),
2286 )
2287 if document_id is not None:
2288 q = q.filter(NoteReference.target_document_id == document_id)
2289 else:
2290 q = q.filter(NoteReference.target_research_id == research_id)
2291 return q.first() is not None
2293 def update_note_research(
2294 self,
2295 note_id: str,
2296 research_id: str,
2297 is_collapsed: Optional[bool] = None,
2298 ) -> bool:
2299 """Update a NoteResearch row. Currently supports toggling is_collapsed.
2301 Returns True if the row was found and updated, False otherwise.
2302 """
2303 try:
2304 with get_user_db_session(self.username) as session:
2305 nr = (
2306 session.query(NoteResearch)
2307 .filter_by(document_id=note_id, research_id=research_id)
2308 .first()
2309 )
2310 if not nr:
2311 return False
2312 if is_collapsed is not None:
2313 nr.is_collapsed = bool(is_collapsed)
2314 session.commit()
2315 return True
2316 except Exception:
2317 logger.exception(
2318 f"Error updating note_research for {note_id}/{research_id}"
2319 )
2320 raise
2322 def reorder_note_research(
2323 self, note_id: str, research_ids: List[str]
2324 ) -> bool:
2325 """Atomically reorder NoteResearch rows for a note so their
2326 display_order matches the position in research_ids.
2328 Validates every id belongs to the note before updating.
2329 Returns True on success.
2331 Two-pass write because of the
2332 ``UniqueConstraint('document_id', 'display_order')`` added in
2333 migration 0021: assigning the final order directly would hit
2334 an intermediate state where two rows share the same
2335 display_order (e.g. swapping idx 0 and 2 would try to put
2336 idx-2 at 0 while idx-0 is still at 0). The first pass moves
2337 every row into the negative display_order range — guaranteed
2338 not to collide with the positive target values — and the
2339 second pass assigns the final positive values.
2340 """
2341 if not isinstance(research_ids, list): 2341 ↛ 2342line 2341 didn't jump to line 2342 because the condition on line 2341 was never true
2342 raise ValueError("research_ids must be a list")
2343 if len(research_ids) > MAX_RESEARCH_PER_NOTE: 2343 ↛ 2344line 2343 didn't jump to line 2344 because the condition on line 2343 was never true
2344 raise ValueError(
2345 f"too many research_ids (max {MAX_RESEARCH_PER_NOTE})"
2346 )
2347 # A duplicate id collapses in a set, so a set-equality check alone would
2348 # accept e.g. ['A','B','A'] for a note linking {A,B} and then assign A
2349 # two positions — silently corrupting the order. Reject duplicates.
2350 if len(research_ids) != len(set(research_ids)):
2351 return False
2352 session = None
2353 try:
2354 with get_user_db_session(self.username) as session:
2355 existing = (
2356 session.query(NoteResearch)
2357 .filter_by(document_id=note_id)
2358 .all()
2359 )
2360 existing_ids = {nr.research_id for nr in existing}
2361 if set(research_ids) != existing_ids:
2362 return False
2364 by_id = {nr.research_id: nr for nr in existing}
2365 # Pass 1: park every row in the negative range so the
2366 # second pass can assign 0..N-1 without colliding with
2367 # the existing positive display_orders.
2368 for idx, rid in enumerate(research_ids):
2369 by_id[rid].display_order = -(idx + 1)
2370 session.flush()
2371 # Pass 2: final positive values.
2372 for idx, rid in enumerate(research_ids):
2373 by_id[rid].display_order = idx
2374 session.commit()
2375 return True
2376 except Exception:
2377 # Roll back so a failed flush/commit doesn't leave the shared
2378 # request session poisoned for the rest of the request.
2379 self._rollback_quietly(session)
2380 logger.exception("Error reordering research for note {}", note_id)
2381 raise
2383 # Sentinel used by _document_to_note_dict to distinguish "caller passed
2384 # None as the precomputed value (i.e. document is not indexed)" from
2385 # "caller did not pass anything; query the DB". Plain None can't carry
2386 # both meanings.
2387 _UNSET = object()
2389 # Upper bound on body size returned in list-view responses. Frontend
2390 # only ever shows the first ~150 chars; shipping the entire ``text_content``
2391 # column meant a 200-note page could download multiple MB. The 300-char
2392 # cap leaves headroom over the UI cap so the truncation isn't visible
2393 # at the cut-off boundary.
2394 LIST_CONTENT_PREVIEW_CHARS = 300
2396 def _document_to_note_dict(
2397 self,
2398 document: Document,
2399 session: Session,
2400 _collection_count: Any = _UNSET,
2401 _research_count: Any = _UNSET,
2402 _indexed_coll: Any = _UNSET,
2403 include_full_content: bool = True,
2404 ) -> Dict[str, Any]:
2405 """Convert a Document to a note dict.
2407 The three ``_``-prefixed kwargs let list_notes pre-batch the
2408 aggregate queries (count of collections / research links / first
2409 indexed-collection row) into 3 grouped SQL statements instead of
2410 3*N per-row sub-queries. Single-doc callers (get_note) leave them
2411 unset and pay the per-row cost.
2412 """
2413 if _collection_count is self._UNSET:
2414 _collection_count = (
2415 session.query(DocumentCollection)
2416 .filter_by(document_id=document.id)
2417 .count()
2418 )
2419 if _research_count is self._UNSET:
2420 _research_count = (
2421 session.query(NoteResearch)
2422 .filter_by(document_id=document.id)
2423 .count()
2424 )
2425 if _indexed_coll is self._UNSET:
2426 _indexed_coll = (
2427 session.query(DocumentCollection)
2428 .filter_by(document_id=document.id, indexed=True)
2429 .first()
2430 )
2432 collection_count = _collection_count
2433 research_count = _research_count
2434 indexed_coll = _indexed_coll
2436 text_content = document.text_content or ""
2437 if include_full_content:
2438 content_value: Optional[str] = text_content
2439 content_preview: Optional[str] = None
2440 else:
2441 content_value = None
2442 content_preview = self._truncate_preview(
2443 text_content, self.LIST_CONTENT_PREVIEW_CHARS
2444 )
2446 return {
2447 "id": document.id,
2448 "title": document.title,
2449 "content": content_value,
2450 "content_preview": content_preview,
2451 "tags": document.tags or [],
2452 "pinned": document.favorite,
2453 "is_indexed": indexed_coll is not None,
2454 "chunk_count": indexed_coll.chunk_count if indexed_coll else 0,
2455 "last_indexed_at": indexed_coll.last_indexed_at.isoformat()
2456 if indexed_coll and indexed_coll.last_indexed_at
2457 else None,
2458 "created_at": document.created_at.isoformat()
2459 if document.created_at
2460 else None,
2461 "updated_at": document.updated_at.isoformat()
2462 if document.updated_at
2463 else None,
2464 "collection_count": collection_count,
2465 "research_count": research_count,
2466 }
2468 # =========================================================================
2469 # Version History Methods
2470 # =========================================================================
2472 def _create_version_snapshot_in_session(
2473 self,
2474 session: Session,
2475 note_id: str,
2476 title: str,
2477 content: str,
2478 tags: Optional[List[str]],
2479 change_type: str,
2480 change_summary: Optional[str] = None,
2481 ) -> "tuple[str, bool]":
2482 """Create a NoteVersion row in an existing session.
2484 Caller is responsible for committing. Used by ``restore_with_bookends``
2485 to keep PRE_RESTORE + content update + RESTORE in a single
2486 transaction so process death between writes can't lose the audit
2487 trail. Dedup is preserved via the (document_id, content_hash)
2488 check; on a hit, returns the existing version id without inserting.
2490 Returns ``(version_id, created)``: ``created`` is False when the
2491 dedup check absorbed the snapshot into an existing row. Callers
2492 that schedule the async change-summary worker MUST check it —
2493 passing a dedup-absorbed (historical) version id to the worker
2494 would overwrite that old row's change_summary with a description
2495 of a much later edit.
2497 Note that ``content_hash`` is computed over title + content + tags
2498 via ``_compute_version_hash`` so title-only or tag-only edits
2499 produce a distinct hash and aren't silently deduped against an
2500 existing snapshot. The column name stays ``content_hash`` for
2501 schema compatibility but its semantics are "version-state hash".
2502 """
2503 # NoteVersion.content/tags are NOT NULL (note.py), but the source
2504 # this snapshots — Document.text_content — IS nullable, and the
2505 # restore bookend path passes document fields through uncoerced.
2506 # Coerce here so the version table is never stricter than the row
2507 # it captures (a NULL would raise IntegrityError on flush). Title
2508 # is already NOT NULL upstream.
2509 content = content or ""
2510 tags = tags or []
2511 # PRE_RESTORE and RESTORE are audit bookends: by definition their
2512 # state hash matches an existing version row (PRE_RESTORE = current
2513 # state, which IS the prior MANUAL_SAVE; RESTORE = the target
2514 # version's state). Dedup + UNIQUE(document_id, content_hash) would
2515 # silently drop the bookend, erasing the "user restored here" audit
2516 # signal. Salt the hash with a fresh UUID for these change types so
2517 # each row is guaranteed to insert.
2518 #
2519 # INVARIANT WARNING for future contributors: for PRE_RESTORE and
2520 # RESTORE rows, ``content_hash`` is NOT reproducible from
2521 # ``(title, content, tags)`` via _compute_version_hash — the salt
2522 # is hash-only and is NOT persisted into the ``tags`` column
2523 # (clean user-facing data). Any integrity checker that walks
2524 # NoteVersion rows and recomputes hashes MUST filter on
2525 # ``change_type NOT IN ('pre_restore', 'restore')`` before
2526 # comparing — otherwise it will see every restore event as
2527 # corrupted. Removing the salt to "fix" the divergence would
2528 # silently reintroduce the audit-row-drop bug this guards
2529 # against; if you find yourself reaching for that, add a
2530 # change_type column to the UNIQUE constraint instead.
2531 is_audit_bookend = change_type in _AUDIT_BOOKEND_TYPES
2532 if is_audit_bookend:
2533 salted_tags = list(tags or []) + [
2534 f"__{change_type}__{uuid.uuid4()}"
2535 ]
2536 content_hash = self._compute_version_hash(
2537 title, content, salted_tags
2538 )
2539 else:
2540 content_hash = self._compute_version_hash(title, content, tags)
2541 existing = (
2542 session.query(NoteVersion)
2543 .filter_by(document_id=note_id, content_hash=content_hash)
2544 .first()
2545 )
2546 if existing:
2547 return (existing.id, False)
2549 version_id = str(uuid.uuid4())
2550 version = NoteVersion(
2551 id=version_id,
2552 document_id=note_id,
2553 title=title,
2554 content=content,
2555 tags=tags or [],
2556 change_type=change_type,
2557 change_summary=change_summary,
2558 content_hash=content_hash,
2559 )
2560 session.add(version)
2561 session.flush()
2562 return (version_id, True)
2564 def _mark_note_stale_for_reindex_in_session(
2565 self, session: Session, note_id: str
2566 ) -> None:
2567 """Mark a note's embeddings stale after a content/title change.
2569 Two indexed-state sources must move together:
2571 * ``DocumentCollection.indexed`` — the legacy flag the auto-index
2572 worker scans (it runs with ``force_reindex=False``, so a stale
2573 ``indexed=True`` makes it short-circuit and keep serving the
2574 pre-edit embedding).
2575 * ``RagDocumentStatus`` — row-existence is the *canonical* "indexed"
2576 marker that the RAG status route and ``get_rag_stats`` read.
2578 ``index_document`` writes BOTH on index; the edit path must clear
2579 BOTH or the status report shows an edited note as still-indexed
2580 until (and unless) a re-index actually runs. Deleting the status
2581 row matches the model's "no row = not indexed" contract — an edited
2582 note genuinely isn't currently indexed; the worker re-creates the
2583 row when it re-embeds. Caller commits.
2584 """
2585 session.query(DocumentCollection).filter_by(document_id=note_id).update(
2586 {DocumentCollection.indexed: False},
2587 synchronize_session=False,
2588 )
2589 session.query(RagDocumentStatus).filter_by(document_id=note_id).delete(
2590 synchronize_session=False
2591 )
2593 def _prune_versions_in_session(
2594 self, session: Session, note_id: str
2595 ) -> "tuple[int, int]":
2596 """Prune ordinary and restore-bookend versions to independent caps.
2598 Caller is responsible for committing. Used by both ``update_note``
2599 (after its in-session snapshot write) and ``restore_with_bookends``
2600 (which adds PRE_RESTORE + RESTORE rows without consuming the ordinary
2601 version budget).
2603 Returns ``(ordinary_pruned, bookend_pruned)`` — the number of rows
2604 this call's own DELETE statements actually removed, counted from
2605 the rows the database itself reports deleting (``DELETE ...
2606 RETURNING``; same SQLite/SQLCipher baseline the chat service's
2607 ``UPDATE ... RETURNING`` counters already rely on). The prune
2608 selects the ids of the oldest rows over the cap, then deletes
2609 exactly those ids: when a concurrently-committed transaction has
2610 removed some of them in the SELECT→DELETE window, this call's
2611 DELETE matches fewer rows and that smaller, honest number is
2612 returned — never the fetched count, which the previous
2613 per-instance ``session.delete()`` implementation reported even
2614 when its DELETE matched nothing (SQLAlchemy only warns on that
2615 mismatch for non-versioned mappers). The counts therefore never
2616 over-report what this call removed; they say nothing about rows
2617 other transactions deleted, and can be lower than the excess the
2618 caps imply when concurrent pruners shared the work.
2620 The deletes run in the caller's transaction but are NOT committed
2621 here: if that transaction later rolls back, these rows survive.
2622 Callers must NOT log these counts themselves until after their own
2623 ``session.commit()`` succeeds — logging here (before the commit)
2624 would claim a prune that a subsequent rollback undoes, turning the
2625 one operator-facing signal for version loss into a false positive
2626 on exactly the failure paths it exists to surface. See
2627 ``update_note`` and ``restore_with_bookends`` for the post-commit
2628 logging call.
2629 """
2630 ordinary_pruned = 0
2631 ordinary_total = (
2632 session.query(NoteVersion)
2633 .filter_by(document_id=note_id)
2634 .filter(NoteVersion.change_type.notin_(_AUDIT_BOOKEND_TYPES))
2635 .count()
2636 )
2637 excess = ordinary_total - MAX_VERSIONS_PER_NOTE
2638 if excess > 0:
2639 # Audit bookends have their own cap below and never reduce the
2640 # number of ordinary snapshots retained for the user.
2641 oldest_ids = [
2642 row_id
2643 for (row_id,) in session.query(NoteVersion.id)
2644 .filter_by(document_id=note_id)
2645 .filter(NoteVersion.change_type.notin_(_AUDIT_BOOKEND_TYPES))
2646 .order_by(NoteVersion.created_at.asc(), NoteVersion.id.asc())
2647 .limit(excess)
2648 .all()
2649 ]
2650 if oldest_ids: 2650 ↛ 2672line 2650 didn't jump to line 2672 because the condition on line 2650 was always true
2651 # Delete by the fetched primary keys and count what the
2652 # DELETE actually matched — the rows the DB reports via
2653 # RETURNING — not what the SELECT fetched. Under a
2654 # concurrently-committed delete of the same rows, the ORM's
2655 # per-instance session.delete() path only WARNS on the
2656 # rowcount mismatch yet would report the fetched count.
2657 # synchronize_session=False leaves these rows persistent in the
2658 # session identity map until commit — do not touch NoteVersion
2659 # instances after this call.
2660 result = session.execute(
2661 delete(NoteVersion)
2662 .where(NoteVersion.id.in_(oldest_ids))
2663 .returning(NoteVersion.id)
2664 .execution_options(synchronize_session=False)
2665 )
2666 ordinary_pruned = len(result.all())
2668 # Separately bound the bookend pool. Excluding bookends from the prune
2669 # above keeps the restore trail, but each restore writes two
2670 # un-prunable rows, so without this ceiling repeated restores grow
2671 # note_versions without limit. Keep the most recent MAX_BOOKEND_VERSIONS.
2672 bookend_pruned = 0
2673 bookend_total = (
2674 session.query(NoteVersion)
2675 .filter_by(document_id=note_id)
2676 .filter(NoteVersion.change_type.in_(_AUDIT_BOOKEND_TYPES))
2677 .count()
2678 )
2679 bookend_excess = bookend_total - MAX_BOOKEND_VERSIONS
2680 if bookend_excess > 0:
2681 oldest_bookend_ids = [
2682 row_id
2683 for (row_id,) in session.query(NoteVersion.id)
2684 .filter_by(document_id=note_id)
2685 .filter(NoteVersion.change_type.in_(_AUDIT_BOOKEND_TYPES))
2686 .order_by(NoteVersion.created_at.asc(), NoteVersion.id.asc())
2687 .limit(bookend_excess)
2688 .all()
2689 ]
2690 if oldest_bookend_ids: 2690 ↛ 2699line 2690 didn't jump to line 2699 because the condition on line 2690 was always true
2691 # Same delete-matched counting as the ordinary prune above.
2692 result = session.execute(
2693 delete(NoteVersion)
2694 .where(NoteVersion.id.in_(oldest_bookend_ids))
2695 .returning(NoteVersion.id)
2696 .execution_options(synchronize_session=False)
2697 )
2698 bookend_pruned = len(result.all())
2699 return (ordinary_pruned, bookend_pruned)
2701 def _log_version_prune(
2702 self, note_id: str, prune_counts: "tuple[int, int]"
2703 ) -> None:
2704 """Log a prune event. Call ONLY after the caller's transaction has
2705 committed successfully — see ``_prune_versions_in_session`` for why.
2707 Emits aggregate counts only (never note content or titles), and
2708 only when at least one row was actually pruned, so log volume
2709 stays at most one line per save/restore that triggers a prune —
2710 not one line per pruned version.
2711 """
2712 ordinary_pruned, bookend_pruned = prune_counts
2713 if ordinary_pruned or bookend_pruned:
2714 logger.info(
2715 "note version prune: note_id={} ordinary={} bookend={}",
2716 note_id,
2717 ordinary_pruned,
2718 bookend_pruned,
2719 )
2721 def restore_with_bookends(
2722 self, note_id: str, version_id: str
2723 ) -> "tuple[bool, Optional[str]]":
2724 """Restore a note to a previous version atomically.
2726 Wraps PRE_RESTORE snapshot + content update + RESTORE snapshot in
2727 a single transaction. Earlier code did three separate sessions —
2728 process death between writes could leave a restored note with
2729 no audit-trail row. With this method, either all three writes
2730 land or none do.
2732 Returns ``(success, error_code)``:
2733 * ``(True, None)`` — restore complete
2734 * ``(False, "note_not_found")`` — no document with that id, or
2735 it isn't a note
2736 * ``(False, "version_not_found")`` — version id doesn't exist
2737 for this note
2738 * ``(False, "internal_error")`` — exception during restore;
2739 check logs
2741 The route layer maps these to HTTP statuses.
2742 """
2743 try:
2744 with get_user_db_session(self.username) as session:
2745 document = self._get_note_in_session(session, note_id)
2746 if document is None:
2747 return (False, "note_not_found")
2749 version = (
2750 session.query(NoteVersion)
2751 .filter_by(document_id=note_id, id=version_id)
2752 .first()
2753 )
2754 if not version:
2755 return (False, "version_not_found")
2757 # Capture for post-session use (link re-parse)
2758 v_title = version.title
2759 v_content = version.content
2760 v_tags = version.tags
2762 # PRE_RESTORE snapshot of the current document state
2763 self._create_version_snapshot_in_session(
2764 session,
2765 note_id,
2766 document.title,
2767 document.text_content,
2768 document.tags,
2769 NoteChangeType.PRE_RESTORE.value,
2770 )
2772 # Apply the restore to the document
2773 document.title = v_title
2774 document.text_content = v_content
2775 document.tags = v_tags
2776 content_str = v_content or ""
2777 document.character_count = len(content_str)
2778 document.word_count = len(content_str.split())
2779 document.document_hash = self._compute_content_hash(
2780 f"{note_id}:{content_str}"
2781 )
2782 document.file_size = len(content_str.encode("utf-8"))
2784 # RESTORE snapshot marking the post-restore state
2785 self._create_version_snapshot_in_session(
2786 session,
2787 note_id,
2788 v_title,
2789 v_content,
2790 v_tags,
2791 NoteChangeType.RESTORE.value,
2792 change_summary=f"Restored to version {version_id[:8]}",
2793 )
2795 # Prune to cap; with bookends the count can exceed the
2796 # cap by 2 in a single transaction. The deletes are only
2797 # flushed here — this whole transaction can still roll
2798 # back below (stale-reindex or link-reparse failure), so
2799 # the counts are logged only after session.commit()
2800 # succeeds, not here. See _prune_versions_in_session.
2801 prune_counts = self._prune_versions_in_session(session, note_id)
2803 # Stale-vector fix (mirrors update_note): a restore changes
2804 # content/title, so mark its embeddings stale across both
2805 # indexed-state sources — the auto-index worker re-embeds
2806 # and the RAG status report stops showing the pre-restore
2807 # state as indexed. See _mark_note_stale_for_reindex_in_session.
2808 self._mark_note_stale_for_reindex_in_session(session, note_id)
2810 # Re-parse wiki-links INSIDE the restore transaction (against
2811 # the freshly-restored content), exactly like update_note —
2812 # NOT in a separate post-commit session. A post-commit reparse
2813 # raced a concurrent update_note: that save wrote correct
2814 # NoteLink rows for its NEW content inside its own transaction,
2815 # then this stale reparse (computed from the restored content)
2816 # reverted them, leaving the link graph (backlinks/outgoing/
2817 # unlinked-mentions) inconsistent with Document.text_content.
2818 # Trade-off: a reparse failure now rolls the whole restore back
2819 # (the same contract update_note already has) instead of
2820 # logging and leaving a committed-but-mis-linked restore — an
2821 # acceptable, consistent change for link-graph correctness.
2822 self._parse_and_update_links_in_session(
2823 session, note_id, v_content or ""
2824 )
2826 session.commit()
2827 self._log_version_prune(note_id, prune_counts)
2829 return (True, None)
2831 except Exception:
2832 logger.exception(
2833 f"Error restoring version {version_id} for note {note_id}"
2834 )
2835 return (False, "internal_error")
2837 # Note: get_note_versions / get_version / restore_version were removed
2838 # in PR #3277 — the route layer (web/routers/notes.py) reimplements these
2839 # queries directly because they need fine-grained control over the
2840 # session lifetime (e.g., the restore route's PRE_RESTORE/RESTORE
2841 # bookend snapshots). The service-layer versions had zero callers.
2842 # Re-add them only when a non-route caller (CLI, scheduled task)
2843 # actually needs them.
2845 # =========================================================================
2846 # Wiki Linking Methods
2847 # =========================================================================
2849 # NOTE: ``_parse_and_update_links_in_session`` is used by both
2850 # ``create_note`` and ``update_note`` so the content insert/update +
2851 # link rewrite land in a single transaction (atomicity).
2852 def _parse_and_update_links_in_session(
2853 self,
2854 session: Session,
2855 note_id: str,
2856 content: str,
2857 link_overrides: Optional[Dict[str, str]] = None,
2858 ) -> List[Dict[str, Any]]:
2859 """Parse [[wiki-style links]] and update NoteLink rows in an
2860 existing session.
2862 Caller is responsible for committing. Used by ``update_note`` and
2863 ``create_note`` so the content update + link rewrite land in one
2864 transaction — a failure mid-parse rolls the content change back
2865 with it. The standalone ``_parse_and_update_links`` keeps its own
2866 session and is used by ``restore_with_bookends`` which has
2867 explicitly chosen to reparse outside its restore transaction
2868 (documented at the call site).
2870 ``link_overrides`` (lowercased link text → target_document_id) lets
2871 a caller bind a specific ``[[text]]`` to an exact target id.
2872 ``accept_suggested_link`` passes it so a suggestion accepted for one
2873 of several same-titled notes links to THAT note — not whichever the
2874 title resolver happens to pick.
2876 Resolution priority per link: (1) ``link_overrides``; (2) the id this
2877 exact link text resolved to before (the rename-safety cache, now
2878 PREFERRED over a fresh lookup so an accepted/established link stays
2879 id-stable across reparse instead of being silently retargeted to the
2880 oldest same-titled note); (3) a fresh title resolution for brand-new
2881 links. Every id-based target is verified to still be an existing
2882 note before use.
2883 """
2884 # Parse + dedupe targets (first appearance wins) BEFORE applying
2885 # the cap. The cap exists to bound per-save DB work, and duplicate
2886 # texts resolve once regardless — counting each repetition against
2887 # the cap let 1000 repetitions of one ``[[A]]`` silently evict a
2888 # later distinct ``[[B]]``.
2889 parsed_targets: List[str] = []
2890 seen_texts: set[str] = set()
2891 truncated = 0
2892 for raw_link_text in LINK_PATTERN.findall(content):
2893 if len(parsed_targets) >= MAX_LINKS_PER_NOTE:
2894 truncated += 1
2895 continue
2896 # Canonical target text = the portion before the first pipe
2897 # of the Obsidian/Roam ``[[Target|Display]]`` alias syntax.
2898 # The display half is presentation-only; resolution, the
2899 # rename-safety cache key, the stored ``link_text`` and the
2900 # ``resolved_links`` payload all key off the target so the
2901 # frontend linkMap (built from outgoing_links.link_text in
2902 # note-detail.js) matches what ``processWikiLinks`` looks up
2903 # after it splits on the pipe. Storing the raw aliased text
2904 # here would leave every aliased link unresolvable on the
2905 # client and fall back to a slower title search.
2906 target_text, _sep, _display = raw_link_text.partition("|")
2907 target_text = target_text.strip()
2908 if not target_text:
2909 # Empty/whitespace target ("[[ ]]", "[[|alias]]") is not a
2910 # link — the frontend renders it as a literal. Pre-fix the
2911 # raw text was passed through to the resolver, whose
2912 # empty-prefix startswith('') matched EVERY note and
2913 # persisted a phantom NoteLink to the user's oldest note.
2914 continue
2915 text_lc = target_text.lower()
2916 if text_lc in seen_texts:
2917 continue
2918 seen_texts.add(text_lc)
2919 parsed_targets.append(target_text)
2920 if truncated:
2921 logger.warning(
2922 "note {} has more than {} distinct wiki-links; dropping "
2923 "{} matches past the cap to bound per-save DB work",
2924 note_id,
2925 MAX_LINKS_PER_NOTE,
2926 truncated,
2927 )
2929 # Existing rows serve three purposes: the rename-safety cache
2930 # (lowercased link text → target id, so renaming a target doesn't
2931 # nuke the link on the source's next save), the diff base below,
2932 # and preservation of per-row state (id, created_at, and the
2933 # auto_suggested badge from an accepted AI suggestion) on links
2934 # that survive the reparse. If multiple existing rows share the
2935 # same link_text (shouldn't happen — UniqueConstraint is on
2936 # (source, target), not (source, link_text)), the first wins.
2937 existing_rows = (
2938 session.query(NoteLink).filter_by(source_document_id=note_id).all()
2939 )
2940 existing_link_targets: Dict[str, str] = {}
2941 for row in existing_rows:
2942 key = (row.link_text or "").lower().strip()
2943 existing_link_targets.setdefault(key, row.target_document_id)
2944 rows_by_target: Dict[str, NoteLink] = {
2945 row.target_document_id: row for row in existing_rows
2946 }
2948 note_source_type_id = self._get_note_source_type_id(session)
2949 overrides = {
2950 (k or "").lower().strip(): v
2951 for k, v in (link_overrides or {}).items()
2952 }
2954 # Batch-validate every id-based candidate (priorities 1 and 2) in
2955 # one query instead of a per-link primary-key lookup — so a
2956 # stale/deleted/non-note id never resurrects a link, at O(1)
2957 # round-trips instead of O(links).
2958 candidate_ids: set[str] = set()
2959 for target_text in parsed_targets:
2960 text_lc = target_text.lower()
2961 for cid in (
2962 overrides.get(text_lc),
2963 existing_link_targets.get(text_lc),
2964 ):
2965 if cid and cid != note_id:
2966 candidate_ids.add(cid)
2967 valid_targets: Dict[str, str] = {}
2968 candidate_list = sorted(candidate_ids)
2969 for start in range(0, len(candidate_list), _IN_CLAUSE_CHUNK):
2970 chunk = candidate_list[start : start + _IN_CLAUSE_CHUNK]
2971 valid_targets.update(
2972 session.query(Document.id, Document.title)
2973 .filter(
2974 Document.id.in_(chunk),
2975 Document.source_type_id == note_source_type_id,
2976 )
2977 .all()
2978 )
2980 def _valid_note_target(doc_id):
2981 """Return {id, title} if doc_id is an existing note (and not
2982 the source itself), else None."""
2983 if not doc_id or doc_id == note_id:
2984 return None
2985 title = valid_targets.get(doc_id)
2986 return {"id": doc_id, "title": title} if title is not None else None
2988 # Priority-3 pre-pass: batch the exact-title strategy for texts
2989 # priorities 1-2 don't cover. One IN() query over lower(title)
2990 # replaces a per-link table scan; ordering + setdefault implements
2991 # the oldest-wins tie-break (see _resolve_link_internal). The
2992 # mapping back from DB rows to link texts uses _fold_title_ascii,
2993 # which mirrors the database's lower().
2994 fresh_texts = [
2995 t
2996 for t in parsed_targets
2997 if not _valid_note_target(overrides.get(t.lower()))
2998 and not _valid_note_target(existing_link_targets.get(t.lower()))
2999 ]
3000 exact_by_fold: Dict[str, Dict[str, Any]] = {}
3001 folded_list = sorted({_fold_title_ascii(t) for t in fresh_texts})
3002 for start in range(0, len(folded_list), _IN_CLAUSE_CHUNK):
3003 chunk = folded_list[start : start + _IN_CLAUSE_CHUNK]
3004 rows = (
3005 session.query(Document.id, Document.title)
3006 .filter(
3007 Document.source_type_id == note_source_type_id,
3008 func.lower(Document.title).in_(chunk),
3009 )
3010 .order_by(Document.created_at.asc(), Document.id.asc())
3011 .all()
3012 )
3013 for doc_id, title in rows:
3014 exact_by_fold.setdefault(
3015 _fold_title_ascii(title), {"id": doc_id, "title": title}
3016 )
3018 resolved_links: List[Dict[str, Any]] = []
3019 desired: Dict[str, str] = {} # target id → link text (first wins)
3020 prefix_memo: Dict[str, Optional[Dict[str, Any]]] = {}
3021 for target_text in parsed_targets:
3022 text_lc = target_text.lower()
3023 # Priority 1: an explicit override (accept_suggested_link binds
3024 # the exact intended target id, immune to title collisions).
3025 target = _valid_note_target(overrides.get(text_lc))
3026 # Priority 2: the id this exact link text previously resolved to.
3027 # Preferring it over a fresh title lookup keeps an accepted /
3028 # established link pinned to its note across reparse and
3029 # keeps a link alive when its target was renamed.
3030 if not target:
3031 target = _valid_note_target(existing_link_targets.get(text_lc))
3032 # Priority 3: fresh title resolution — batched exact match,
3033 # then the (rare) per-text prefix fallback.
3034 if not target:
3035 fold = _fold_title_ascii(target_text)
3036 target = exact_by_fold.get(fold)
3037 if not target:
3038 if fold not in prefix_memo: 3038 ↛ 3042line 3038 didn't jump to line 3042 because the condition on line 3038 was always true
3039 prefix_memo[fold] = self._resolve_link_prefix(
3040 session, target_text, note_source_type_id
3041 )
3042 target = prefix_memo[fold]
3043 if (
3044 target
3045 and target["id"] != note_id
3046 and target["id"] not in desired
3047 ):
3048 desired[target["id"]] = target_text
3049 resolved_links.append(
3050 {
3051 "link_text": target_text,
3052 "target_id": target["id"],
3053 "target_title": target["title"],
3054 }
3055 )
3057 # Apply as a diff instead of delete-all + recreate: surviving rows
3058 # keep their id, created_at and auto_suggested flag, and an
3059 # unchanged link set writes zero rows.
3060 for target_id, row in rows_by_target.items():
3061 if target_id not in desired:
3062 session.delete(row)
3063 for target_id, link_text in desired.items():
3064 new_text = link_text[:MAX_LINK_TEXT_LENGTH]
3065 row = rows_by_target.get(target_id)
3066 if row is None:
3067 session.add(
3068 NoteLink(
3069 source_document_id=note_id,
3070 target_document_id=target_id,
3071 link_text=new_text,
3072 auto_suggested=False,
3073 )
3074 )
3075 elif row.link_text != new_text: 3075 ↛ 3076line 3075 didn't jump to line 3076 because the condition on line 3075 was never true
3076 row.link_text = new_text
3078 session.flush()
3079 return resolved_links
3081 def _parse_and_update_links(
3082 self, note_id: str, content: str
3083 ) -> List[Dict[str, Any]]:
3084 """Standalone version: opens its own session and commits.
3086 Kept for callers that intentionally manage link reparse outside
3087 their primary transaction (notably ``restore_with_bookends``, where
3088 a link-resolution failure is non-fatal and the restore must not
3089 roll back).
3090 """
3091 try:
3092 with get_user_db_session(self.username) as session:
3093 resolved = self._parse_and_update_links_in_session(
3094 session, note_id, content
3095 )
3096 session.commit()
3097 logger.debug(
3098 f"Updated links for note {note_id}: {len(resolved)} links"
3099 )
3100 return resolved
3101 except Exception:
3102 # Use logger.exception so the stack trace lands in the log.
3103 # Link parsing failures are non-fatal (callers continue), but
3104 # silently dropping the cause makes recurring failures
3105 # invisible to operators.
3106 logger.exception("Failed to parse links for note {}", note_id)
3107 return []
3109 def _resolve_link_internal(
3110 self, session: Session, link_text: str
3111 ) -> Optional[Dict[str, Any]]:
3112 """Resolve a link to a note document.
3114 Supports the Obsidian/Roam pipe alias syntax
3115 ``[[Title|Display Text]]``: the target is everything before the
3116 first ``|``; ``Display Text`` is rendering-only and ignored at
3117 resolution time. Without this split, a link like
3118 ``[[My Note|see here]]`` would search for a literal note titled
3119 "my note|see here" — which never exists — and the link would
3120 silently fall back to a stale cached target id on every save.
3122 Two notes can have the same title (e.g. duplicate "TODO" notes).
3123 Tie-break on ``created_at`` ascending so the link resolves to the
3124 OLDEST match. Resolving to the newest instead reads as more
3125 recency-intuitive, but it silently re-points every existing
3126 ``[[Title]]`` the moment a new note reuses that title (link drift by
3127 spooky-action-at-a-distance); oldest is stable and deterministic
3128 (without the order_by, ``.first()`` is at the DB's discretion and can
3129 flip between calls). Real per-target disambiguation — capturing the
3130 picked note's id at autocomplete time — is tracked as follow-up work.
3131 """
3132 # Strip pipe-alias before lookup. Use partition rather than
3133 # split so a target containing additional pipes (rare but
3134 # legal in markdown) doesn't get further mangled.
3135 target_text, sep, _display = link_text.partition("|")
3136 if sep:
3137 link_text = target_text
3138 link_text = link_text.strip()
3139 # Degenerate target ("[[ ]]" / "[[|alias]]"): an empty key must
3140 # never reach the lookup strategies — a LIKE '%' prefix pattern
3141 # would "resolve" to EVERY note (deterministically the user's
3142 # oldest one). The frontend treats an empty target as a literal,
3143 # not a link; mirror that here.
3144 if not link_text:
3145 return None
3146 source_type_id = self._get_note_source_type_id(session)
3148 # Strategy 1: Exact title match. Fold BOTH sides in SQL: folding
3149 # the link text in Python instead diverges for non-ASCII, because
3150 # SQLite's lower() folds ASCII only while str.lower() folds full
3151 # Unicode — a byte-identical title like "Über Alles" could then
3152 # never match its own link (lower('Über Alles') = 'Über alles',
3153 # but Python sent 'über alles'). SQL-side folding keeps ASCII
3154 # case-insensitivity and makes exact-typed non-ASCII titles
3155 # resolve. (Case-insensitive matching of the non-ASCII letters
3156 # themselves would need a custom collation on the connection.)
3157 document = (
3158 session.query(Document)
3159 .filter(
3160 Document.source_type_id == source_type_id,
3161 func.lower(Document.title) == func.lower(link_text),
3162 )
3163 .order_by(Document.created_at.asc(), Document.id.asc())
3164 .first()
3165 )
3166 if document:
3167 return {"id": document.id, "title": document.title}
3169 return self._resolve_link_prefix(session, link_text, source_type_id)
3171 def _resolve_link_prefix(
3172 self, session: Session, link_text: str, source_type_id: int
3173 ) -> Optional[Dict[str, Any]]:
3174 """Strategy 2: partial (prefix) title match — but only for targets
3175 long enough to be a meaningful prefix. A 1-2 char target like
3176 ``[[i]]`` would otherwise prefix-match and silently persist a
3177 NoteLink to an unrelated note ("Ideas", "Interview notes", ...);
3178 below the minimum length an exact match (Strategy 1) is the only
3179 acceptable resolution. Reuses the same floor as the
3180 unlinked-mention scan.
3182 ``link_text`` must already be stripped. Both sides are folded in
3183 SQL for the same non-ASCII reason as Strategy 1; the pattern is
3184 LIKE-escaped so a target containing wildcards (``[[%]]``,
3185 ``[[_]]``) matches literally instead of over-matching every note.
3186 """
3187 if len(link_text) < self.MIN_MENTION_TITLE_LEN:
3188 return None
3189 prefix_pattern = self._escape_like(link_text) + "%"
3190 document = (
3191 session.query(Document)
3192 .filter(
3193 Document.source_type_id == source_type_id,
3194 func.lower(Document.title).like(
3195 func.lower(prefix_pattern), escape="\\"
3196 ),
3197 )
3198 .order_by(Document.created_at.asc(), Document.id.asc())
3199 .first()
3200 )
3201 if document:
3202 return {"id": document.id, "title": document.title}
3204 return None
3206 # Number of preview chars sent in backlinks / outgoing-links payloads.
3207 # Used by the column-projection SUBSTR so we don't pull full
3208 # text_content (up to 50 MB per Document row) just to slice 200
3209 # chars in Python.
3210 LINK_CONTENT_PREVIEW_CHARS = 200
3212 @staticmethod
3213 def _truncate_preview(raw: Optional[str], preview_len: int) -> str:
3214 """Trim a preview string to ``preview_len`` chars, appending "..."
3215 when the source was longer.
3217 The link-preview queries project ``func.substr(..., 1, preview_len
3218 + 1)`` from the DB — the +1 is the sentinel: SQLite SUBSTR returns
3219 at most preview_len+1 chars, so getting the full +1 char means the
3220 original text_content was longer than preview_len and the preview
3221 should be ellipsized. (SUBSTR uses 1-based indexing via length.)
3222 """
3223 s = raw or ""
3224 return s[:preview_len] + "..." if len(s) > preview_len else s
3226 def get_backlinks(self, note_id: str) -> List[Dict[str, Any]]:
3227 """Get notes that link to the given note.
3229 Errors propagate to the caller so the route layer can return a
3230 proper 500 via ``handle_api_error`` — silently returning ``[]``
3231 on a DB failure would be indistinguishable from "no backlinks"
3232 and mask production issues.
3234 Uses ``func.substr`` to project only the preview prefix of
3235 ``text_content`` from the DB. Pre-fix the JOIN loaded full
3236 Document rows including the unbounded ``text_content`` blob
3237 for every backlink source — at 20 backlinks averaging 100 kB
3238 each, the DB transferred ~2 MB just to slice 4 kB of response.
3239 """
3240 with get_user_db_session(self.username) as session:
3241 preview_len = self.LINK_CONTENT_PREVIEW_CHARS
3242 results = (
3243 session.query(
3244 Document.id,
3245 Document.title,
3246 Document.created_at,
3247 func.substr(
3248 Document.text_content, 1, preview_len + 1
3249 ).label("preview"),
3250 NoteLink.link_text,
3251 )
3252 .join(NoteLink, Document.id == NoteLink.source_document_id)
3253 .filter(NoteLink.target_document_id == note_id)
3254 # Deterministic order so the backlinks panel doesn't reshuffle
3255 # between loads (DB row order is otherwise unspecified).
3256 .order_by(Document.title.asc(), Document.id.asc())
3257 .all()
3258 )
3260 backlinks = []
3261 for row in results:
3262 preview = self._truncate_preview(row.preview, preview_len)
3263 backlinks.append(
3264 {
3265 "id": row.id,
3266 "title": row.title,
3267 "link_text": row.link_text,
3268 "content_preview": preview,
3269 "created_at": row.created_at.isoformat()
3270 if row.created_at
3271 else None,
3272 }
3273 )
3275 return backlinks
3277 def get_outgoing_links(self, note_id: str) -> List[Dict[str, Any]]:
3278 """Get notes that this note links to (outgoing links).
3280 Errors propagate to the caller — see ``get_backlinks`` rationale.
3281 Uses ``func.substr`` column projection for the same reason.
3282 """
3283 with get_user_db_session(self.username) as session:
3284 preview_len = self.LINK_CONTENT_PREVIEW_CHARS
3285 results = (
3286 session.query(
3287 Document.id,
3288 Document.title,
3289 Document.created_at,
3290 func.substr(
3291 Document.text_content, 1, preview_len + 1
3292 ).label("preview"),
3293 NoteLink.link_text,
3294 NoteLink.auto_suggested,
3295 )
3296 .join(NoteLink, Document.id == NoteLink.target_document_id)
3297 .filter(NoteLink.source_document_id == note_id)
3298 # Deterministic order so the outgoing-links panel is stable
3299 # across loads.
3300 .order_by(Document.title.asc(), Document.id.asc())
3301 .all()
3302 )
3304 outgoing = []
3305 for row in results:
3306 preview = self._truncate_preview(row.preview, preview_len)
3307 outgoing.append(
3308 {
3309 "id": row.id,
3310 # Stable FK to the target note. The frontend
3311 # (processWikiLinks) keys its [[link_text]] -> id map on
3312 # target_id to resolve wiki-links by id rather than by a
3313 # fragile title search; without it that lookup was always
3314 # empty and every link fell back to title matching.
3315 "target_id": row.id,
3316 "title": row.title,
3317 "link_text": row.link_text,
3318 "content_preview": preview,
3319 "created_at": row.created_at.isoformat()
3320 if row.created_at
3321 else None,
3322 # True when the link was created by accepting an
3323 # AI suggestion (vs. typed by hand). Lets the UI
3324 # badge AI-suggested links.
3325 "auto_suggested": bool(row.auto_suggested),
3326 }
3327 )
3329 return outgoing
3331 # Titles shorter than this are too generic to mention-match usefully
3332 # (they'd flag almost every note), so unlinked-mention scanning skips them.
3333 MIN_MENTION_TITLE_LEN = 3
3335 def get_unlinked_mentions(
3336 self, note_id: str, limit: int = 20
3337 ) -> List[Dict[str, Any]]:
3338 """Find other notes whose text mentions this note's title but that
3339 don't yet link to it — Obsidian-style "unlinked mentions".
3341 Pure lexical (case-insensitive substring on the title), so it needs no
3342 embeddings/LLM. Notes that already link here are excluded; very short
3343 titles are skipped to avoid matching everything.
3344 """
3345 with get_user_db_session(self.username) as session:
3346 note_source_type_id = self._get_note_source_type_id(session)
3347 if not note_source_type_id: 3347 ↛ 3348line 3347 didn't jump to line 3348 because the condition on line 3347 was never true
3348 return []
3349 note = (
3350 session.query(Document)
3351 .filter_by(id=note_id, source_type_id=note_source_type_id)
3352 .first()
3353 )
3354 title = (note.title or "").strip() if note else ""
3355 if len(title) < self.MIN_MENTION_TITLE_LEN:
3356 return []
3358 # Notes that already link to this note — exclude them.
3359 linked_source_ids = {
3360 row[0]
3361 for row in session.query(NoteLink.source_document_id)
3362 .filter_by(target_document_id=note_id)
3363 .all()
3364 }
3366 # Escape LIKE wildcards in the title so e.g. a "50%" title can't
3367 # match every note.
3368 escaped = self._escape_like(title)
3369 preview_len = self.LINK_CONTENT_PREVIEW_CHARS
3370 query = session.query(
3371 Document.id,
3372 Document.title,
3373 func.substr(Document.text_content, 1, preview_len + 1).label(
3374 "preview"
3375 ),
3376 ).filter(
3377 Document.source_type_id == note_source_type_id,
3378 Document.id != note_id,
3379 Document.text_content.ilike(f"%{escaped}%", escape="\\"),
3380 )
3381 if linked_source_ids:
3382 # Exclude already-linking notes in SQL. Done in Python
3383 # after a ``limit * 3`` over-fetch, a page whose first
3384 # candidates were mostly already linked silently returned
3385 # fewer than ``limit`` mentions even though more existed beyond
3386 # the over-fetch window.
3387 query = query.filter(
3388 Document.id.notin_(list(linked_source_ids))
3389 )
3390 rows = (
3391 # Deterministic order: with no ORDER BY the limited slice was
3392 # whatever order the DB returned, so repeated calls could
3393 # surface different mentions. Newest-updated first, id as
3394 # a stable tiebreaker; mirrors the other link/list panels.
3395 query.order_by(Document.updated_at.desc(), Document.id)
3396 .limit(limit)
3397 .all()
3398 )
3400 return [
3401 {
3402 "id": row.id,
3403 "title": row.title,
3404 "content_preview": self._truncate_preview(
3405 row.preview, preview_len
3406 ),
3407 }
3408 for row in rows
3409 ]
3411 def accept_suggested_link(
3412 self, note_id: str, target_note_id: str
3413 ) -> Optional[Dict[str, Any]]:
3414 """Accept an AI-suggested link by inserting [[Target Title]] into the
3415 note content and creating a NoteLink marked auto_suggested=True.
3417 Returns the updated note dict on success. Returns None only for a
3418 benign precondition that means "nothing to accept" — the note or
3419 target is missing, they are the same note, either is not a note, or
3420 the target title is only wiki-link syntax. A genuine write failure
3421 (e.g. update_note's content-size ValueError, or a DB error) is RAISED,
3422 not collapsed into the same None, so the route can map it to the right
3423 status instead of a misleading 404.
3424 """
3425 try:
3426 with get_user_db_session(self.username) as session:
3427 source = session.query(Document).filter_by(id=note_id).first()
3428 target = (
3429 session.query(Document).filter_by(id=target_note_id).first()
3430 )
3431 if not source or not target:
3432 return None
3434 if source.id == target.id:
3435 logger.warning("Cannot self-link note {}", note_id)
3436 return None
3438 # Check both exist as notes
3439 source_type_id = self._get_note_source_type_id(session)
3440 if ( 3440 ↛ 3444line 3440 didn't jump to line 3444 because the condition on line 3440 was never true
3441 source.source_type_id != source_type_id
3442 or target.source_type_id != source_type_id
3443 ):
3444 return None
3446 # If the note already links to this target (e.g. a hand-typed
3447 # [[Target]]), don't append a duplicate wiki-link or relabel the
3448 # user's link as AI-suggested — accepting is a no-op.
3449 already_linked = (
3450 session.query(NoteLink)
3451 .filter_by(
3452 source_document_id=note_id,
3453 target_document_id=target_note_id,
3454 )
3455 .first()
3456 is not None
3457 )
3458 if already_linked:
3459 return self.get_note(note_id)
3461 # Strip bracket characters from the target title before
3462 # interpolating into the [[...]] wiki-link syntax.
3463 # A title containing "]]" would otherwise corrupt the
3464 # link tokens (e.g. "foo]] [[bar" → "[[foo]] [[bar]]"
3465 # which the parser would split into two separate links).
3466 # Also drop any "|" alias portion: the parser partitions
3467 # the link text on the FIRST pipe and resolves only the
3468 # pre-pipe part, so a title containing "|" would make the
3469 # override key below ("a|b") never match the parser's
3470 # lookup key ("a") — silently bypassing the exact-id
3471 # binding and falling back to title resolution.
3472 # Also collapse internal whitespace (newlines included):
3473 # LINK_PATTERN's character class excludes newlines, so a
3474 # title containing one would append a "[[a\nb]]" literal the
3475 # parser can't match — no NoteLink created, yet the method
3476 # would report success and each retry would append more dead
3477 # text. `" ".join(split())` folds any whitespace run
3478 # (newlines/tabs/multiple spaces) to a single space.
3479 target_title = " ".join(
3480 (target.title or "")
3481 .replace("[", "")
3482 .replace("]", "")
3483 .partition("|")[0]
3484 .split()
3485 )
3486 # Degenerate titles ("|", "[[", empty) strip down to "".
3487 # Appending "[[]]" would persist a dead literal in the
3488 # note body (the parser skips empty link text), so refuse
3489 # instead of silently writing an unlinkable artifact.
3490 # Return None (the method's failure contract — mirrors
3491 # the not-found and self-link branches above).
3492 if not target_title:
3493 logger.warning(
3494 "Cannot accept link {} -> {}: target title contains "
3495 "only wiki-link syntax characters",
3496 note_id,
3497 target_note_id,
3498 )
3499 return None
3501 # Guard the duplicate-title case: if the note already has a
3502 # link whose text matches this title but points to a
3503 # DIFFERENT note (e.g. a hand-typed [[TODO]] resolved to an
3504 # older same-titled note), appending another [[TODO]] would
3505 # not add a second edge — the parser dedupes link text with
3506 # Python str.lower(), so the diff would RETARGET the single
3507 # 'todo' edge to this note and delete the user's existing
3508 # link to the other one. Bare [[title]] can't express two
3509 # different targets for the same text, so refuse rather than
3510 # silently destroy the existing edge.
3511 norm_title = target_title.lower()
3512 sibling_links = (
3513 session.query(NoteLink)
3514 .filter(
3515 NoteLink.source_document_id == note_id,
3516 NoteLink.target_document_id != target_note_id,
3517 )
3518 .all()
3519 )
3520 if any(
3521 (lnk.link_text or "").lower() == norm_title
3522 for lnk in sibling_links
3523 ):
3524 logger.warning(
3525 "Cannot accept link {} -> {}: the note already links "
3526 "'{}' to a different note; bare [[title]] can't "
3527 "disambiguate same-titled targets",
3528 note_id,
3529 target_note_id,
3530 target_title,
3531 )
3532 return None
3534 # Session A above performed only precondition reads. The body
3535 # append itself is NOT computed here from source.text_content:
3536 # that value would be stale by the time update_note's separate
3537 # write transaction runs, so a save landing in between would be
3538 # lost. update_note re-reads the current body and appends inside
3539 # its own transaction (see _append_link_title) — read and write
3540 # are then atomic.
3542 # update_note handles the body append (from the note's CURRENT
3543 # content), versioning, link re-parsing and hash update.
3544 # Bind the appended [[target_title]] to the EXACT target id via an
3545 # override so a title shared by several notes — or a title we had
3546 # to strip brackets from above — links to THIS note, not whichever
3547 # the title resolver would pick. The override id is
3548 # preferred on this parse, and the resulting link's id is then
3549 # preferred on every later reparse (the rename-safety cache), so
3550 # the binding is durable.
3551 self.update_note(
3552 note_id,
3553 _append_link_title=target_title,
3554 _link_overrides={target_title.lower().strip(): target_note_id},
3555 )
3557 # Flag the newly-created NoteLink as auto_suggested=True so UX can
3558 # distinguish it from user-typed links.
3559 with get_user_db_session(self.username) as session:
3560 link = (
3561 session.query(NoteLink)
3562 .filter_by(
3563 source_document_id=note_id,
3564 target_document_id=target_note_id,
3565 )
3566 .first()
3567 )
3568 if link: 3568 ↛ 3572line 3568 didn't jump to line 3572
3569 link.auto_suggested = True
3570 session.commit()
3572 return self.get_note(note_id)
3574 except Exception:
3575 # Real write failures (update_note's ValueError content cap, a DB
3576 # IntegrityError, etc.) must NOT be swallowed into the same None
3577 # the benign preconditions return — that hid genuine failures
3578 # behind a misleading "Link could not be accepted" 404. Log for
3579 # context and re-raise so the route maps it (ValueError -> 400,
3580 # anything else -> 500).
3581 logger.exception(
3582 "Error accepting suggested link {} on {}",
3583 target_note_id,
3584 note_id,
3585 )
3586 raise
3588 def resolve_link(self, link_text: str) -> Optional[Dict[str, Any]]:
3589 """Resolve a [[link]] text to a note.
3591 Public wrapper around ``_resolve_link_internal``. Caps ``link_text``
3592 at ``MAX_LINK_TEXT_LENGTH`` defensively even though ``LINK_PATTERN``
3593 already bounds it — the public route hands its body in untouched.
3594 """
3595 if isinstance(link_text, str) and len(link_text) > MAX_LINK_TEXT_LENGTH:
3596 link_text = link_text[:MAX_LINK_TEXT_LENGTH]
3597 try:
3598 with get_user_db_session(self.username) as session:
3599 return self._resolve_link_internal(session, link_text)
3600 except Exception:
3601 # A DB/session failure must NOT collapse into the same None a
3602 # genuinely unresolvable link returns — the route maps None to a
3603 # 404 "Note not found", which would present an outage as the note
3604 # not existing. Log for context and re-raise so the route's
3605 # handle_api_error surfaces a 500 (same rationale as
3606 # accept_suggested_link above).
3607 logger.exception("Error resolving link")
3608 raise
3610 def search_notes_for_linking(
3611 self, query: str, exclude_note_id: Optional[str] = None, limit: int = 10
3612 ) -> List[Dict[str, Any]]:
3613 """Search notes for the [[link]] autocomplete feature."""
3614 try:
3615 with get_user_db_session(self.username) as session:
3616 source_type_id = self._get_note_source_type_id(session)
3617 search_pattern = f"%{self._escape_like(query)}%"
3619 query_obj = session.query(Document).filter(
3620 Document.source_type_id == source_type_id,
3621 Document.title.ilike(search_pattern, escape="\\"),
3622 )
3624 if exclude_note_id:
3625 query_obj = query_obj.filter(Document.id != exclude_note_id)
3627 documents = (
3628 query_obj.order_by(Document.updated_at.desc())
3629 .limit(limit)
3630 .all()
3631 )
3633 return [
3634 {
3635 "id": doc.id,
3636 "title": doc.title,
3637 "slug": self._generate_slug(doc.title),
3638 }
3639 for doc in documents
3640 ]
3642 except Exception:
3643 # Returning [] here would turn an infrastructure failure into an
3644 # HTTP 200 with an empty autocomplete — indistinguishable from
3645 # "no matching notes". Log for context and re-raise so the route
3646 # surfaces a 500 (matches semantic_search's fix in
3647 # note_ai_service, which removed this exact swallow as masking
3648 # outages).
3649 logger.exception(
3650 "Error searching notes for linking (query_len={})",
3651 len(query) if query else 0,
3652 )
3653 raise