Full Text Search

Full-text search across tickets and attachments

Status: phase 1 is PART BUILT. Searching inside ticket messages and notes works today. Attachment text does not exist yet, and nothing indexes new tickets as they arrive.

Shipped
#991	Database Verification understands full-text indexes
#992	search_documents — the corpus table
#993	Debug tool D007 — Search corpus health
#994	the one search function (includes/search/)
#995	scripts/search_backfill.php — index existing content
#996	Tickets → search → "Anywhere in the ticket"

Not built
⬜	attachment text — the whole of §3.2, §6.4 and the extractor tiers in §8.2
⬜	indexing as tickets arrive — the backfill must be run by hand for now
⬜	anything in §7 (the ruled-out options) or §8.5 (a second backend)

Prompted by discussion #53, where an evaluator asked for full-text search over ticket descriptions, replies, internal notes and history, plus indexing of PDF, Word, Excel and text attachments. §5 answers that request point by point.

⚠️ This remains a DESIGN document, and design documents rot. Where it describes something now built, the code is the authority. Its lasting value is §7 — the options considered and rejected, with the reasons — because that is the part nobody can reconstruct from the code.

---

1. What search does today

Worth stating plainly, because two of the three things the discussion asked for genuinely don't exist, and the third exists but is easy to miss.

Surface	What it searches	What it does not
Ticket search modal (api/tickets/search_tickets.php)	LIKE on ticket number, from/to address, subject	any message body, any note
Command palette (api/system/global_search.php)	7 modules — titles, names, references	any body content
Knowledge AI search (includes/knowledge/kb_ai.php)	article text, semantically, via embeddings	needs an API key; articles only

There is no FULLTEXT index anywhere in the product, and email_attachments stores filename, path, size and mime — no extracted text.

So: global search exists, but it's jump-to-a-record, not find-content-inside-records. That distinction is the whole feature.

---

2. The idea that shapes everything else

Extraction is the hard half. Indexing is not.

Turning a PDF or a Word document into plain text is genuinely difficult — encodings, scanned pages, legacy formats, hostile files. Searching text you already have is a solved problem, and MySQL 8.0 (the current floor) does it natively with no new dependency.

Almost every wrong turn below comes from reaching for a tool that solves the easy half.

---

3. Phase 1 — what would actually be built

3.1 Ticket content — the bulk of the value

Bodies and notes are already in the database: emails.body_content and ticket_notes.note_text, both LONGTEXT, both InnoDB/utf8mb4. A FULLTEXT index over them plus a query path is most of what was asked for, with no new services and no new dependencies.

Two traps decide whether it feels good or broken:

• body_content is HTML. Indexed raw, every ticket "contains" div, span, style — searching table returns everything. It needs stripped plaintext, produced once at ingest, and indexed instead. That text also gives you sane result snippets.
• innodb_ft_min_token_size defaults to 3, so two-character terms find nothing — and service desks search short codes constantly. It's a server variable requiring a restart and a full index rebuild, and on shared hosting it often can't be changed. Likewise InnoDB's stopword list, which eats "no", "not" and "can" — and "cannot print" is a real query. Document both up front; discovering them later feels like a bug.

3.2 Attachments — extraction, in optional tiers

Format	How	Needs
.txt .csv .log .md	read the file	nothing
.docx	includes/rfp_docx_parser.php already does this, dependency-free	nothing
.xlsx .pptx	same zip-of-XML shape; that parser is the template	nothing
.pdf, legacy .doc/.xls, scanned documents	an external extractor (see §8.2)	optional service

⚠️ The existing .docx parser cannot be reused as-is. It was written for RFP source documents — chosen and uploaded by a signed-in member of staff. Ticket attachments arrive from anyone who can email the service desk, and api/tickets/check_mailbox_email.php has no authentication at all. Same code, completely different threat model. See §6.4.

3.3 The extracted-text store — and why it is not the index

Extraction writes plain text into the FreeITSM database. That text is the durable record. The index — whether that's a MySQL FULLTEXT index over it, or documents in some future engine — is a derived artefact that can always be rebuilt from it.

This is the single most important structural decision on this page, and §9.2 explains what it protects against.

3.4 Caps, status, and honest failure

Every extracted attachment carries a status: pending · extracted · truncated · too_large · unsupported · failed. That status is shown in the UI. A search that silently returns nothing because a file was never readable is worse than one that admits it.

Extraction runs off the request thread (there is already cron infrastructure). Otherwise one large attachment stalls the mailbox poller and delays every ticket queued behind it.

3.5 An open decision: history

Does phase 1 index existing tickets and attachments, or only new ones from switch-on? Backfilling is a long-running job over potentially years of data and every archived file; not backfilling means search quietly has a horizon that users will trip over. This needs answering before build, not during — and note that the discussion explicitly asked for "historical conversations", so the honest answer to that request depends on it.

---

4. The corpus — one table, not three indexes

This is the part that makes ordinary rows and extracted document text searchable as one thing rather than as two features bolted together.

4.1 Why not just index each table

The obvious build is a FULLTEXT index on emails, another on ticket_notes, another on attachment text, then UNION the results. It falls apart:

• Relevance scores from different full-text indexes are not on a common scale — each is computed against its own index's corpus statistics. You cannot meaningfully sort a note hit against an attachment hit.
• Pagination then has nothing coherent to page by, so page 2 is guesswork.
• Every new searchable thing later — knowledge articles, change descriptions, problems — is another branch in the query and another place to re-express the permission rules.

4.2 One corpus table

A row is one searchable unit, whatever it came from:

search_documents
  id           BIGINT PK
  source_type  VARCHAR(32)    -- 'ticket' (subject) | 'email' | 'note'
                              -- | 'attachment' | 'kb_article' | …
  source_id    BIGINT         -- the origin row
  ticket_id    INT NULL       -- what it hangs off, where that applies
  tenant_id    INT NULL       -- company scope, mirrored from the source
  is_internal  TINYINT(1)     -- 1 = never visible to a portal user
  visibility   VARCHAR(16)    -- third-party-message privacy rule
  title        VARCHAR(500)   -- subject, or filename
  body         MEDIUMTEXT     -- stripped plaintext
  created_at   DATETIME       -- recency ranking and date filters
  indexed_at   DATETIME
  FULLTEXT KEY ft (title, body)
  FULLTEXT KEY ft_title (title)      -- needed to search filenames/subjects alone (§4.5)
  KEY (source_type, source_id)
  KEY (ticket_id)

An email body, an internal note and the text of a PDF are now the same shape. One index, one relevance scale, one query:

SELECT   ticket_id,
         MAX(MATCH(title,body) AGAINST (:q IN BOOLEAN MODE)) AS score
FROM     search_documents
WHERE    MATCH(title,body) AGAINST (:q IN BOOLEAN MODE)
  AND    (tenant_id IS NULL OR tenant_id IN (:scope))
  AND    (:include_internal = 1 OR is_internal = 0)
GROUP BY ticket_id
ORDER BY score DESC

Two things worth noticing. The permission predicate sits in the WHERE alongside the match, so §6 falls out of the design rather than having to be remembered. And the GROUP BY collapses hits to their ticket — users think in tickets, and without it one ticket with the term in four replies floods the first page with itself.

4.3 The objection, answered

This duplicates body text. It does — but a stripped plaintext copy is needed anyway, because body_content is HTML and indexing it raw poisons every query (§3.1). Derived text is being stored either way; the only question is whether it lives in one table or three. One table wins on ranking and on having a single place where scope is expressed.

And because it's the same database, the copy is kept in step in the same transaction as the write — which is exactly the sync problem an external engine would have introduced (§7.2).

4.4 Filtering by source, and telling the user where a hit came from

Because source_type is an ordinary column, the things a search UI needs are nearly free:

• Search one kind of thing — just notes, just attachments — is AND source_type = 'note'.
• Facet counts — "48 in notes, 12 in attachments" — is one GROUP BY source_type.
• Attribution — every row already carries source_type, source_id and ticket_id, so a result can say "found in a note on ABC-123-45678" without a second lookup.

The ticket subject is its own row. source_type = 'ticket', with the subject as title. Burying it as the title of the first email would make "matched the subject" impossible to state cleanly and impossible to weight separately.

The result shape people actually want — grouped by ticket, each showing where it matched — is two bounded queries rather than one clever one:

1. rank tickets   GROUP BY ticket_id, MAX(score), LIMIT 20
2. fetch details  the matching documents for just those 20 ticket ids

Which yields "ABC-123-45678 — matched in: subject, 2 notes, 1 attachment". Doing it the other way round — fetch documents, collapse afterwards — reintroduces a top-N distortion, because the top 200 documents might collapse to only a handful of tickets.

4.5 Three MySQL constraints this design has to live with

⚠️ MATCH() must correspond exactly to a FULLTEXT index. With one index over (title, body) you can only ever match both together. Searching filenames alone therefore needs a second index on (title). Cheap, but it has to be designed in rather than discovered.

⚠️ Filenames may be better served by LIKE than by full-text at all. invoice_2026-04_ACME.pdf tokenises badly, and people search fragments of a filename — precisely what full-text is weak at and LIKE '%printer%' is good at. Filename search and content search can reasonably use different mechanisms.

⚠️ There is no field weighting inside a MySQL full-text index. If a subject hit should outrank a body hit, the score has to be composed by hand — match the title index and the combined index separately and add them with a multiplier, or carry a weight per source_type. A few lines, but not something MySQL does for you.

4.6 What it will and won't match

The most concrete question anyone can ask of a search feature is "if the document says Tower of London, what do I have to type to find it?"

Typed	Result	Why
tower London	✅ found	Word order is irrelevant — the index holds individual words, not phrases
tower in London	✅ found	"in" is an InnoDB stopword, dropped from query and index alike (and two characters, so below the token minimum anyway)
"tower of london"	✅ found	Boolean mode supports exact phrases
Twoer of London	❌ not found	No edit-distance matching whatsoever
towers London	❌ not found	MySQL does not stem — plural and singular are unrelated words to it
fortress London	❌ not found	No synonyms

Word-order independence and stopword tolerance are not fuzzy matching — they're just how tokenisation works, and they cover a lot of everyday queries. What MySQL genuinely cannot do is forgive a misspelling or a word ending.

⚠️ The plural gap will bite harder than typos. printer / printers, licence / licences — people type both constantly, and MySQL treats them as unrelated tokens. Postgres has a stemmer; MySQL does not.

Cheap mitigation: append a trailing wildcard to each term in boolean mode — +tower* +london* — which picks up plurals and most word endings for nothing. The trade is over-matching on short stems (cat* reaches catastrophe), and leading wildcards are not supported, so *tower is impossible.

🔑 This is the one honest argument for a phase 2 engine that isn't about scale. Typo tolerance is the headline feature of Meilisearch and Manticore — Twoer finds Tower there. Staying on MySQL trades forgiveness, not speed. Worth knowing that's the trade being made, rather than discovering it from a user asking why a search with one transposed letter found nothing.

4.7 What this buys later

Adding a source becomes an INSERT with a new source_type, not a new query path. Knowledge articles, change descriptions, problem records and time-entry notes can all join the same corpus without touching the search function or the permission model.

⚠️ Snippets are a MySQL gap. There is no highlighting function (unlike an engine's highlighter or Postgres's ts_headline), so snippets have to be generated in PHP from the stored plaintext, and escaped on output. Fine, but it is work, and it is the one place the corpus design doesn't save you anything.

---

5. Does this answer discussion #53?

Taking the request literally, item by item.

5.1 The three questions

Their question	Honest answer today	With phase 1
"Is there already a way to enable full-text search for ticket content?"	No. There is nothing to enable — no FULLTEXT index exists anywhere in the product. Search is LIKE over ticket number, address and subject, which matches their own description.	Yes
"Is attachment indexing and searching currently supported?"	No. email_attachments holds filename, path, size and mime only. No text is ever extracted.	Yes, by format tier (§5.3)
"…are there plans to introduce a global search feature that can search across tickets, notes, comments, knowledge articles, and document attachments?"	Partly. The command palette already searches 7 modules — but titles and references only, never content.	The corpus (§4) is that feature's content half

5.2 Ticket content they asked for

Requested	Covered by	Notes
Ticket descriptions	source_type = 'email' (first message)	phase 1
Ticket replies	source_type = 'email'	phase 1
Internal notes	source_type = 'note', is_internal = 1	phase 1, and never returned to a portal user
"Comments"	same as notes — FreeITSM's term is note	phase 1
Historical conversations	same rows	⚠️ depends on the backfill decision (§3.5) — this is the one request that isn't automatically satisfied
Knowledge articles (their Q3)	source_type = 'kb_article'	fits the corpus; see §5.4
"All ticket-related content"	mostly	subject and bodies yes; time-entry notes and custom fields would be further source_types, cheap to add but not free

5.3 Formats they named

Requested	Tier	Reality
Text files	1 — no dependency	trivial
Microsoft Word	1 for .docx	legacy .doc is tier 2
Excel spreadsheets	1 for .xlsx	legacy .xls is tier 2
PDF documents	2 — optional extractor	text-based PDFs fine; scanned PDFs contain no text at all and need OCR
"Other common office document formats"	mixed	.pptx and OpenDocument are feasible at tier 1; older binary formats are tier 2 only

The honest headline for the reply: everything on their list is reachable, but PDF — the format they listed first — is the one that needs an optional extra service, and scanned PDFs need OCR on top of that. Anyone promising "PDF search" with no dependency is either shipping a fragile parser or hasn't met a scanned invoice.

5.4 Knowledge articles — a wrinkle worth naming

Knowledge already has semantic search via embeddings (includes/knowledge/kb_ai.php). Putting articles in the corpus adds lexical search over the same content. That's a complement, not a replacement — one finds "tickets like this", the other finds "the article containing this exact error code" — but it does mean two search paths over one body of content, and the UI should be clear about which is which rather than silently picking.

---

6. Security — the query carries the controls

The rule: filter inside the query, never filter the results afterwards.

6.1 Why post-filtering fails

The tempting design is "search, then remove what the user can't see". It breaks in four ways:

1. Result starvation. The index returns its top 20 by relevance; you discard what the user can't see and hand back three, or none — while hundreds of documents they are entitled to see matched but never made the top 20. A portal user, who can see only their own tickets, would get empty searches almost every time.
2. Pagination stops being coherent — offset 20 in the index isn't offset 20 after filtering, so paging produces gaps and repeats.
3. Counts either lie or leak. A pre-filter total quietly tells the user how much exists that they cannot see.
4. Over-fetching doesn't rescue it — there's no knowable multiplier, it differs per user, and for a tightly-scoped user the correct multiplier is the entire corpus.

6.2 What to do instead

The permission predicate goes into the query, as in §4.2. The corpus stores the scope keys as ordinary columns — company/tenant, ticket id, internal flag, visibility — and the search is one operation. Top-N is then correct, pagination works, counts are honest.

Crucially: FreeITSM computes the predicate; the index only applies it. Replicating policy into a second system is dangerous. Passing a computed filter to a store is ordinary. That distinction is what makes an external index safe later (§9).

includes/knowledge/kb_ai.php already works this way and is worth copying — it applies scope in the SQL before scoring, and even its "are there any embeddings?" check is scoped, because counting install-wide would take the vector path on the strength of rows the caller can't see.

6.3 An index is a second copy of the content

Today an attachment's text is protected by the ticket it hangs off: the only route to it is a download endpoint that checks ticket access. Extracting that text creates a searchable copy that lives outside that check. Get the gating wrong and someone finds the contents of a payslip via a search snippet without ever having access to the ticket.

Two consequences that are easy to miss:

• Snippets are content. A highlighted fragment must come from the same scoped row that passed the filter — never from a cache or a shared store.
• Deletion must cascade. When a ticket is deleted, or a GDPR erasure runs, the corpus rows have to go too. A "deleted" ticket that is still searchable by its contents is a data-protection incident, not a bug. (Erasure tooling is itself still outstanding — see the F11 finding.)

6.4 Extraction is parsing hostile input

Inbound email is unauthenticated, so the extractor parses attacker-controlled files by definition.

• UPLOAD_MAX_BYTES is 10 MB of compressed file. XML compresses ten to one routinely and far more with repetitive markup, so a small .docx can hold a gigabyte of XML. The existing parser calls getFromName(), which decompresses an entry into a string with no size check at all.
• A legitimate 200-page document is a problem too: that parser then builds a full DOM from the whole string, several times the XML's size, against a typical memory_limit of 128 M. A PHP fatal is served as HTTP 200 with a broken body, so it fails quietly.

Mitigations: stream with XMLReader rather than DOMDocument (constant memory whatever the size); enforce a hard byte ceiling while reading the zip entry, treating the size declared in the zip header as attacker-controlled and not to be trusted; cap the extracted text; and run extraction in a process whose death doesn't matter.

An external extractor helps here for a reason that isn't obvious: it moves hostile parsing out of the PHP process entirely, so a zip bomb kills a container that restarts rather than the mailbox poller.

6.5 A new copy with different protection

Worth stating explicitly: extracted text is plaintext in the database, derived from a file that may have been sensitive, access-controlled, or even password-protected. It inherits the database's protections, not the file's. That's an acceptable trade for a search feature — but it should be a decision, not a side effect.

---

7. Ruled out, and why

7.1 Vectors / embeddings as the foundation — no

The idea: embed attachment content and search by similarity. Rejected on four grounds, in order of weight.

1. It doesn't remove the extraction problem — it adds a step. Embeddings take text as input. Something still has to open the zip, survive the 200-page document and solve PDF. Every hazard in §6.4 is unchanged.
2. It doesn't scale in this stack. kb_ai.php selects every in-scope row including the full embedding JSON, decodes each in PHP and computes cosine in a loop. Correct for a few hundred Knowledge articles. But a 200-page document can't be one vector — token limits force chunking, so it becomes hundreds of vectors of 1,536 floats each. MySQL 8.0 has no vector type and no approximate-nearest-neighbour index (that's 9.0+), so there is no fix short of an external vector store.
3. Semantic search is weakest at exactly these queries. People search documents for serial numbers, error codes, PO numbers, a person's name, an exact contract phrase. Embeddings capture aboutness and are lossy by design — ask for a serial number and you get documents vaguely about hardware. The request was find the document, not find documents on a similar theme.
4. Privacy. Embedding every attachment means sending every customer document to a third-party API, on a product sold as self-hosted. Every AI feature in FreeITSM today is opt-in, per-feature, bring-your-own-key — but search is core. If search only works once you've shipped everything to OpenAI, search has silently become an AI feature.

✅ Where vectors are right: "have we seen this before?" across ticket bodies, where wording differs but the problem matches. That's the Knowledge gap-analysis pattern already in the product — a good future feature, and a different one.

7.2 An external search engine as the starting point — no (but see §9)

Elasticsearch/OpenSearch, Solr, Manticore, Meilisearch and Typesense are all real options. As a first move they're wrong, because:

• They don't extract anything either. You'd still need the extractor, so it's two extra services rather than one.
• They introduce a sync problem that MySQL doesn't have. Dual-write, reindex tooling, stale detection and a bootstrap path for existing installs — none of which exists when the corpus lives in the same database and is written in the same transaction.
• The benefits (typo tolerance, faceting, ranking at scale) only pay off at volume most installs will never reach.

7.3 Post-filtering results — no

Covered in §6.1. It is the intuitive design and it is wrong; it fails worst for the least privileged users, which is also the group least likely to be testing it.

7.4 Bundling a PDF parser as a hard dependency — no

FreeITSM has no Composer and vendors by hand, so a bundled parser becomes ours to patch for its lifetime — and a bundled library version was itself a finding in the August 2026 security review. More importantly, ext/imap vanishing in PHP 8.4 already demonstrated what a hard dependency costs when it disappears. Anything of this shape must be optional and degrade honestly.

---

8. The path for very large installs

8.0 First, the thing that is easy to misread

There are two entirely separate optional services in this plan, doing unrelated jobs. Conflating them is the most likely way to misunderstand the whole page.

Optional service	Job	Likely uptake
Extractor (e.g. Apache Tika)	Reads PDFs and legacy Office files, turning them into plain text	Common — it's the only route to PDF search
Search engine (e.g. Manticore)	Searches faster, once the corpus is enormous	Rare — most installs never need it

They are independent. The expected shape for an install that wants the full feature is extractor, no engine.

And on the engine specifically, three properties matter more than the technology:

• Off by default, chosen on a settings screen. It is a setting, not a version — nobody is upgraded into it, and nobody has to refuse an upgrade to avoid it (§8.3).
• No data moves. Tickets, notes, attachments and the corpus all stay in the FreeITSM database. The engine holds only a derived copy for fast lookup — a book's index versus the book. Remove the index and the book is untouched.
• Reversible. Switching it off falls back to MySQL with nothing lost, because the corpus is the source of truth and the engine's copy is rebuildable from it (§9.2).

8.1 What is configurable — and what deliberately isn't

📌 A correction to the brief. The natural way to describe this is "a settings screen where you configure where things go" — but the extracted text and the corpus must not be among the things it relocates. They stay in the FreeITSM database. What's configurable is which extractor runs and which backend answers queries. §9.2 explains why moving the text is the one change that would create a genuine trap.

Setting	Options	Default
Extractor	none · built-in PHP only · external service (URL)	built-in PHP
Search backend	MySQL full-text · whichever engines are supported (§8.5)	MySQL full-text
Extraction caps	max file size, max extracted text, per-file time budget	conservative
Backfill	off · run once · scheduled	off

8.2 Three optional tiers

1. Nothing extra — PHP extraction for docx/xlsx/text, MySQL full-text. Runs on any PHP-capable host, which is most installs.
2. + an extraction service — PDFs, legacy Office formats, OCR for scanned documents. Apache Tika is the obvious candidate: Apache-2.0, a simple HTTP API (send bytes, get text), around a thousand formats. Needs Docker or a JVM. docker-compose.yml currently runs two services, so this is a small, optional third.
3. + an external search engine — scale, typo tolerance, faceting. Manticore is the most natural fit for this stack because it speaks the MySQL wire protocol, so the existing PDO connection works with no new client library.

Each tier is additive and opt-in. An install that stops at tier one has no unused tables — the corpus is identical either way, so the data model never forks.

8.3 Phase 2 is a setting, not a version

The failure to avoid is Phase 2 arriving as an upgrade that carries everyone into it. Both backends ship in the same codebase behind one interface — not a fork, not a download — so an install can switch, try it, and switch back, and nobody is stranded on an old version to avoid it.

This is existing house style: bring-your-own-key AI, the CA-bundle fallback to a shipped cacert.pem, and the rejected-attachment keep-or-drop setting all work this way.

8.4 Where this is configured — one screen, not a card per engine

The instinct is to add cards under System → Integrations, one for OpenSearch, one for Manticore, and so on. That's the wrong shape, for two reasons.

Search is singular; trackers are plural. Jira and Azure DevOps can both be connected at once, because a ticket can escalate to either — so a card each is honest. Only one thing can answer a search query. Cards per engine would imply you can run two side by side, and the UI would be describing a model that doesn't exist.

A search engine isn't an integration in that sense. Integrations are external systems you hold an account with and talk to as a peer. A search engine is infrastructure — the same family as the database, the CA bundle or the mail server. Nobody has an "OpenSearch account"; it's a component of your own install.

The right precedent is already in the product: the mailbox modal. You choose a provider — Microsoft 365 / Google / IMAP — and the provider-specific fields appear. One screen, one selector, conditional detail:

System → Search

  Document extraction
    [ none | built-in only | external service ]      → URL, timeout if external

  Search backend
    [ MySQL (built-in) | … supported engines … ]     → host, port, credentials if external

  Index status
    12,431 documents · last built 3 minutes ago · [ Rebuild ]

Two sections because extraction and searching are independent choices (§8.0) — the common configuration is an extractor, with the backend left on MySQL.

⚠️ It is install-wide and admin-only, not a per-analyst preference. Two analysts cannot be on different engines: there is one shared index behind them. This is the same class of setting as the database connection, not the same class as the left-panel preference.

8.5 How many engines to support

Offering admins a choice is right, and this codebase has already proved the pattern twice — AI providers (Anthropic / OpenAI / OpenRouter) and issue trackers, where adding Azure DevOps as the second provider required no schema or core change. There's a real argument beyond preference, too: "we already run Elasticsearch" is common in larger organisations, and telling that admin to stand up a second search system alongside it cuts against the whole no-lock-in position.

The surface is small enough to make it plausible. A backend needs exactly four operations — upsert a document, delete a document, query with filters returning ids and scores, and bulk rebuild. No faceting, no aggregations, no highlighting (snippets are generated in PHP anyway, §4.7). At that surface each adapter is plausibly 150–250 lines of PHP with no client library: Manticore over the MySQL wire protocol via existing PDO, the rest as JSON over curl. OpenSearch forked from Elasticsearch 7.10, so for a surface this small one adapter likely serves both.

🔴 But the real cost isn't lines of code — it's that every adapter is a new place authorisation can fail open. Each one translates the permission predicate into a different filter syntax: Manticore's SQL WHERE, OpenSearch's bool.filter.terms, Meilisearch's filter expression. A translation bug doesn't raise an error — it returns rows the user should never have seen, and a wrong search result looks exactly like a right one. That makes search adapters sharper than AI adapters, where wrong output is visibly wrong. It is §6 all over again, with a fresh surface per engine.

Secondary costs: the engines disagree on typo tolerance, stemming, phrase syntax and wildcards, so capability drift multiplies §9.5's bifurcation risk; and with no CI in the repo, verifying each adapter means standing it up and testing by hand.

So: design the interface for many, ship one, add on demand.

• Designing for N costs nothing beyond the seam already required (§9.1, §9.3).
• The first real adapter is what proves the contract. Build three against an untested interface and you rewrite three when the predicate turns out to need a shape nobody anticipated. Jira → Azure DevOps is the precedent: one provider proved the contract, the second confirmed it held.
• ⚠️ Every adapter must pass the same permission test suite before it ships — a fixed set of "this user must see exactly these ids" cases that every backend answers identically. That is what makes admin choice safe rather than merely generous.

---

9. Not painting yourself into a corner

Three rules make the backend swappable. They cost almost nothing up front and are expensive to retrofit.

9.1 One search function, and nothing else writes a search query

Every caller goes through a single function taking a query plus a scope, returning ranked {type, id, score, snippet}. If MATCH … AGAINST appears in six endpoints, changing engines is a rewrite. In one file, it's writing a second implementation of one file.

9.2 The corpus text is the source of truth, not the index

This is the anti-brick-wall rule. If the only copy of extracted text lives inside the search engine, changing engines means re-extracting every attachment — re-running an extractor over tens of thousands of PDFs, possibly with OCR, possibly on files since archived. That's a project. With the text in search_documents, rebuilding an index is a SELECT loop, against any engine, forever.

9.3 The permission predicate is a data structure, not SQL

A SQL fragment welds the interface to MySQL. A plain structure — tenant ids, visibility flags, ticket ids — is translated by each backend into its own filter syntax. Same predicate, two translations, one source of truth.

9.4 What is not free, even done perfectly

• Ranking order changes between engines. Results reshuffle and users notice.
• Query syntax differs. So keep the user-facing language deliberately small — words, "quoted phrases", -exclusion — and translate it inside the adapter. Expose raw engine operators in the UI and the engine has leaked into the product.
• Sync machinery is genuinely new work in phase 2. Good design saves rewriting the callers, not building the reindex job.
• MySQL's quirks vanish — the three-character minimum, the stopword list — which is an improvement, but it is a behaviour change.

9.5 Don't build phase 2 until someone needs it

Build the seam; ship only the MySQL backend. Each further backend is a permanent maintenance tax — every search feature afterwards is implemented once per backend or built to the lowest common denominator. §8.5 covers how many to support once that day comes, and why the answer is design for many, ship one, add on demand.

⚠️ And watch for the product bifurcating: if phase 2 brings typo tolerance and the UI starts assuming it, MySQL installs get a quietly degraded experience and the majority path becomes the neglected one. Rule: don't ship search UI that only one backend can serve.

9.6 Measure, don't guess

Instrument the search function from day one — query, result count, elapsed time. The trigger to migrate should be a graph, not a hunch. What bites first is rarely raw row count; it's concurrency, wanting accurate totals, and complex boolean queries combined with narrow filters.

---

10. What we are not confident about

Stated separately from the design, because confidence isn't uniform across it.

• Relevance quality is MySQL's weakest area — no BM25 tuning, no real field weighting, no fuzzy matching. It will find the right documents; whether the best one ranks first is much less certain, and users are calibrated by Google. §4.6 sets out exactly what it will and won't match — the no-stemming limitation in particular is a definite, knowable gap rather than an uncertainty, and the trailing-wildcard mitigation should be tested for over-matching before it's adopted.
• The three-character minimum could be the difference between "good" and "why doesn't this find anything" in a product where people search short codes and error fragments. It's a documented caveat, not a solved problem.
• Behaviour at real install volumes is untested.

The cheap way to settle all three before committing: build the corpus table and the search function only — no UI, no extraction. Populate from real data, then run real queries: a serial number, an error code, a phrase from a note, a surname. That is a short spike, and it answers the relevance question with evidence rather than argument, before any of the surrounding work is committed to.

---

11. Open questions

1. History — backfill existing tickets and attachments, or index from switch-on only? (§3.5) The discussion explicitly asked for historical conversations, so this one has a customer waiting on it.
2. Should searches be audited? Staff searching customer content is arguably an auditable event; nothing logs it today.
3. Do portal users get content search at all, or only over their own tickets — and is that a setting?
4. Knowledge articles: one search box or two? Lexical in the corpus and semantic in kb_ai.php answer different questions over the same content (§5.4).
5. Encrypted or password-protected attachments — skip and mark, presumably, but it should be explicit.
6. What ranks a ticket? A match in the subject, the first message, and a five-year-old note are not equally interesting, and a naive index treats them alike.

---

12. Why it's blue sky

Phase 1 is genuinely buildable and would answer most of what was asked in #53. It's parked rather than scheduled because the shape of the work is decided but the scope isn't: the history question (§3.5) and the portal-users question (§11, item 3) both change what gets built, and the security surface in §6 deserves the same standard as the August 2026 review rather than being bolted on.

Phase 2 is parked more firmly, and should stay that way until a real install hits a real wall. The seam is worth building early precisely so that day is a one-file change rather than a rewrite.