Notes search should use case-insensitive substring matching instead of unindexed full-text search #328

Summary

The brainstorming bullet asks whether notes search should be FTS or a string search, and whether an ILIKE filter needs an index. Notes already have a search box, but it runs Postgres full-text search in its weakest form: no index, no language config, whole-lexeme matching only. For short private annotations a plain case-insensitive substring match looks like the better fit, and the per-user scoping makes an index unnecessary today.

Current behaviour

• NoteFilter.search is CharFilter(field_name="text", lookup_expr="search") (radis/notes/filters.py:12), rendered as a single "Search notes" input with a Search button (radis/notes/filters.py:26-28,36).
• With django.contrib.postgres installed (radis/settings/base.py:69) the search lookup compiles to to_tsvector(COALESCE(text, '')) @@ plainto_tsquery(q) with no config, i.e. the database's default_text_search_config (verified against Django 6.0.5 source, django/contrib/postgres/lookups.py and search.py; uv.lock pins 6.1.1, not checked). The runtime default config in the pgvector/pgvector:pg17 image (docker-compose.base.yml:41, no initdb args) was not verified; it is typically english.
• Consequences: the tsvector is recomputed per row on every request (the only notes migration creates no index on text, radis/notes/migrations/0001_initial.py:41); stemming and stop-word removal follow the DB default rather than the note's language, so with an english default a German note is stemmed with English rules and a query of only stop words ("no", "the") becomes an empty tsquery and matches nothing; substrings, partial words and identifiers such as accession numbers never match.
• The list is scoped to the requesting user before the filter runs (radis/notes/views.py:27-28), there is at most one note per (owner, report) (radis/notes/models.py:29-34), and text is capped at 10000 characters by form validation (radis/notes/models.py:26). The scanned set per query is therefore small and already index-driven via the owner FK and the composite unique index.
• Meta.fields = ("text",) (radis/notes/filters.py:15-17) generates a second, unused exact-match ?text= parameter. This is a side effect of 78589f0 (2025-03-30, "Improve searching notes"), which renamed the declared filter from text to search and so stopped shadowing the generated one.
• Report search, by contrast, is GIN-indexed and language-aware (radis/pgsearch/models.py:21,28,54-55, code_to_language in radis/pgsearch/utils/language_utils.py:84). Notes were not covered by that work.
• The same lookup_expr="search" sits on CollectionFilter.name (radis/collections/filters.py:10, name is 64 chars) and SubscriptionFilter.name (radis/subscriptions/filters.py:15, 100 chars); both lists are owner-scoped too (radis/collections/views.py:48, radis/subscriptions/views.py:55). The lookup came in with each feature (d2b9154, ec7fc4d, ee08b6d) and survived 78589f0, so it looks like a house pattern rather than an oversight.
• No test exercises ?search= on the notes list (radis/notes/tests/test_views.py), nor ?name= on collections or subscriptions.
• The user guide advertises the feature (docs/user-docs/user-guide.md:104).

Proposal

Switch NoteFilter.search to lookup_expr="icontains", drop text from Meta.fields (fields = (), as SubscribedItemFilter already does at radis/subscriptions/filters.py:58-60), add view tests, and do not add an index. icontains is already used for other free-text filters (radis/reports/filters.py:30-32, radis/subscriptions/filters.py:32,37).

On the bullet's index question: a trigram GIN index is per table, not per user, so user scoping does not make it smaller. User scoping is instead the reason no index is needed at all -- Postgres narrows by owner_id first, and ILIKE over one user's notes (at most one per annotated report) costs less than the request overhead.

The trade-off to be aware of: stemmed FTS matches inflected forms and is order-independent across words ("nodules lung" matches "lung nodule"); a plain substring match is not. The stop-word and no-config problems are the defects; loss of stemming is the cost.

Options

A -- icontains, no index (recommended). One-line filter change plus tests, no migration, language-agnostic, matches IDs and fragments.

B -- icontains plus pg_trgm GIN index. Needs TrigramExtension() in a migration and an index on Upper("text") with gin_trgm_ops, because Django's icontains emits UPPER(text) LIKE UPPER(%s); an index on the bare column would not be used. pg_trgm ships with the official postgres image the pgvector image builds on (pinned digest not inspected). Only pays off once a cross-user or admin listing exists; today radis/notes/admin.py registers a bare ModelAdmin with no search_fields.

C -- Proper FTS. SearchVectorField on Note, GIN index, config from note.report.language via code_to_language, grouped queries as in pgsearch. Heaviest option and still no substring or ID matching.

Open questions

1. Multi-word queries: treat the input as one substring, or split on whitespace and AND one icontains per term via a method= on the filter to keep today's order-independent AND semantics? Without the split, some existing searches silently return fewer results.
2. Was whole-word/stemmed matching intentional for notes? If stemming matters, Option C or a hybrid Q(icontains) | Q(search) is the alternative, at the cost of keeping the language problem.
3. Apply the same switch to CollectionFilter.name and SubscriptionFilter.name in the same PR, or separately? FTS on a 64-char name is odd, but it widens scope beyond the bullet.

Implementation notes

• Tests to add in radis/notes/tests/test_views.py: case-insensitive substring hit; no cross-owner leak; empty ?search= returns all notes; a stop-word-only query returns matches (the user-visible fix); ?text= no longer filters. Query persistence across pages already works via {% querystring %} in the shared pagination component (adit_radis_shared/common/templates/cotton/pagination.html:9,20,26), not tested today.
• Allow admins to withdraw and restore reports #308 appends tests at the end of the same file and edits NoteListView.get_queryset; place new tests next to test_note_list_view_filters_by_owner (line 38) or rebase after Allow admins to withdraw and restore reports #308 merges, and keep the report__withdrawn_at__isnull=True filter.
• Out of scope: matching the report body shown under each note (radis/notes/templates/notes/_note_list_item.html:6). That would need the pgsearch tsvector and a separate issue.
• No note counts from any deployment were available; the smallness argument rests on the unique (owner, report) constraint, not on data.

Related

• Searching using non English characters #180 (closed) -- report FTS under the wrong config dropped non-English characters; notes search runs with no config at all.
• Allow admins to withdraw and restore reports #308 (open PR, report withdrawal) -- touches NoteListView.get_queryset and the notes test file; see implementation notes.
• Make the FTS candidate query single-table with a search projection #292 and Add Hybrid search to radis #199 concern report search only.