Story 1871: making search speak every language — and never silently lose half a document

Why this exists (the catastrophe it prevents). An Indian litigation firm uploads a Hindi affidavit, a 12,000-row Excel damages sheet, an Arabic contract. Counsel searches for a Hindi phrase from the affidavit — nothing comes back. The document is on the Record, fully extracted, but invisible to search. Two silent failures cause this, and both are one line of SQL. First, the search vector was built with to_tsvector('english', …): the English text-search config runs an English stemmer and stop-word list that throws Devanagari and Arabic on the floor — they tokenize to nothing, so the document is unfindable in its own language. Second, the whole document’s text was crammed into one tsvector on the documents row. A tsvector has a hard ~1 MB length cap; a long doc or a fat spreadsheet blows past it and Postgres raises — the extraction transaction fails and retries into the dead-letter black hole, or, worse for the day a tolerance is added, the tail is silently dropped. This Story (RFD-DOC-002 S6, DR-7) fixes both: search now tokenizes with 'simple' (multilingual, no English-only stemming) and the FTS vector lives per segment, so no single vector can ever overflow and every page/slide/sheet-row is independently findable.

The analogy to hold the whole way down: a library index card system. The old design was one giant card per book, listing every word, hand-written by an English-only clerk who couldn’t read Hindi or Arabic (so those words never made it onto the card) and who, past a certain book length, simply ran out of card. The fix is not a bigger card and a polyglot clerk — it is one card per chapter, filled by a clerk who copies words verbatim in any script. No card ever overflows, the foreign-language words are all there, and a hit tells you exactly which chapter to open.

🛰 Satellite view: move the vector onto the segment, change one word

RFD-DOC-002’s panel locked DR-7: search_vector uses 'simple' so Devanagari/Arabic tokenize, and large text uses per-segment vectors to avoid silent truncation — for all docs, not just Office. S1 already gave every page row a segment identity; this Story is logic-only and rides that schema. Two coupled moves:

Move	Where	What it buys
per-segment FTS vector (`'simple'`, generated + STORED)	`documents.document_pages`	each segment carries its own multilingual tsvector + GIN index; Postgres maintains it from the page `text` — no trigger, no write-path code
drop the document-wide vector	`documents.documents`	the one `to_tsvector('english', full_text)` column + index — English-only and overflow-prone — is removed; nothing read it

This mirrors a pattern the codebase already trusts: business.matters.search_vector has been a generated 'simple' STORED tsvector since Story-1802 (mig 0131), precisely “so Devanagari/Hinglish tokens stay intact.” We are applying the same proven shape to documents, one rung down at the segment.

✈️ Airplane view: where the vector is built, and by whom

The old write path had the extraction worker run a SQL UPDATE … SET search_vector = to_tsvector('english', :full_text) on the document after persisting pages. That code is gone. The new vector is a generated column: the database computes it, per row, from the text the persist services already write.

  OLD (one vector, English, worker-written):
    persist pages ──▶ UPDATE documents
                        SET search_vector = to_tsvector('english', <ENTIRE doc text>)
                        ── raises if > ~1MB; drops Hindi/Arabic tokens

  NEW (per segment, multilingual, DB-generated):
    persist page row (text=...) ──▶ search_vector  GENERATED ALWAYS AS
                                      to_tsvector('simple', coalesce(text,'')) STORED
                                    ── one bounded vector per segment, every script kept

Because the column is GENERATED ALWAYS … STORED, there is literally nothing for the worker to do: inserting or upserting a page row makes Postgres (re)compute that row’s vector. The persist services (persist_native_result, persist_ocr_result) already write text per page and were untouched. Search becomes a query against document_pages: search_vector @@ websearch_to_tsquery('simple', q) — the exact match arm the matters search already uses — and a hit resolves straight to the matching segment (S1’s resolve_segment turns it into “slide 3” / “Sheet, row 12”).

🔬 Microscopic view: the decisions that matter

1. Why `'simple'` and not a per-document language column

The AC offered a choice: 'simple', or a per-document language config. A per-document language demands we first detect the language (an extra failure mode), assumes one language per document (a Hinglish brief or an English contract with an Arabic annexure breaks that), and buys stemming we do not want for legal text where exact tokens matter. 'simple' does unicode word-boundary tokenization with no stemming and no stop-words: it keeps every token in every script verbatim. It is strictly more robust for a multilingual, mixed-script corpus, it needs no detection step, and it is already what matters-search uses — one config across the product, not two. The lazy option is also the correct one.

2. Why per-segment is the only thing that actually removes the truncation

It is tempting to “keep one document vector but build it carefully.” That cannot work: the ~1 MB cap is on the stored tsvector, so concatenating per-segment vectors back into one document-wide vector reintroduces the exact overflow. The cap is only escaped by storing the vectors separately — one per segment, each bounded by a single page/slide/row. A 250-page document is 250 small vectors that always fit, never one giant vector that might not. That is why the document-level column is dropped, not merely re-populated: per-segment is the mechanism, not an optimisation on top of the old one.

A single segment could in theory still be enormous (one cell holding a megabyte of text). That is genuinely bounded by the segment model: S5 splits XLSX into sheet/row segments, and a PDF/slide page is inherently page-sized. The cap is a per-document problem; per-segment dissolves it for every real document.

3. Why dropping `documents.search_vector` is safe

The Story-522 day-one column was a forward-declaration: “populated by the extraction story.” No search read path was ever built on it — a repo-wide grep finds zero consumers outside the extraction write itself and one worker test. Leaving it would mean a dead, overflow-prone, English-only column sitting next to the real per-segment index, inviting the next engineer to write to the wrong one. Removing it is the honest realisation of “not one giant document vector,” and the migration’s downgrade restores it exactly (plain, worker-populated) for a clean round-trip.

✅ Verification at the system boundary

Per the repo’s “done = system boundary” rule, the new behaviour is proven with real requests against a freshly migrated DB (tests/integration/test_document_extraction_worker.py):

DP2-11 multilingual: a Devanagari document is extracted, then a Hindi phrase from it (मुआ़ज़े, अनुबंध विवाद) returns it via websearch_to_tsquery('simple', …), while a Hindi word not in the doc returns nothing — proving real tokenization, not match-anything. The old 'english' config matched none of these.
No silent truncation: a 250-segment document whose combined text exceeds the ~1 MB cap (the size that made the old single-vector to_tsvector raise) indexes every segment; a token unique to page 0 and a token unique to the last page both resolve to exactly one segment — the tail was not dropped.
Gates: ruff clean on changed files; alembic upgrade head → downgrade base → upgrade head round-trips; alembic check reports a single head and no drift; make openapi unchanged (both touched fields are exclude=True); 10/10 worker integration tests pass.

One-sentence recall: search moved from one English-only, overflow-prone vector per document to one 'simple', database-generated vector per segment — so every script is findable and no document can ever silently fall off the end of its index card.