Search Corpus Developer Guide

Search corpus — Developer Guide

How searching inside tickets works underneath, and the handful of things about it that will bite you if nobody tells you.

The user-facing page is Searching inside tickets. The design reasoning — including the options that were rejected — is on Full-text search.

---

1. 📁 The files involved

Colour key: 🗄️ schema · ⚙️ shared service · 🔌 API · 🖥️ page · 🎨 CSS · 🧰 script · 🩺 diagnostics · 🧪 tests · 🌍 i18n

🎨	File	What it does
🗄️	database/freeitsm.sql	search_documents — the corpus table, its two full-text indexes and the cascade foreign key
🗄️	includes/db_verify_schema.php	The same table as columns + PK, so an existing install gains it on Verification
🗄️	includes/db_verify_indexes.php	Generated. Carries the index type (key / unique / fulltext)
🗄️	includes/db_verify_index_parse.php	The shared parser, and dbVerifyIndexTypeOf()
🗄️	scripts/gen_db_verify_indexes.php	Regenerates the index list from database/freeitsm.sql
⚙️	includes/search/search.php	THE search function. Query parsing, scope→SQL, the two-query search, snippets
⚙️	includes/search/corpus.php	The only writer. Upsert/delete, HTML→plaintext, the scope constants
⚙️	includes/search/backfill.php	Rebuild the corpus from tickets / emails / ticket_notes
🧰	scripts/search_backfill.php	CLI wrapper — --limit, --prune, --stats
🔌	api/tickets/search_content.php	The inbox's content search. Builds a scope; decides nothing itself
🖥️	tickets/index.php	One new field in the search modal
🎨	assets/js/inbox.js	performContentSearch() and renderContentSearchResults()
🎨	assets/css/inbox.css	.search-result-snippet, .search-field-hint, .search-results-note
🩺	api/system/debug-tools/D007_search_corpus.php	Health check — table, indexes, FK, server settings, a live search
🧪	tests/search/run.php	41 assertions on parsing, scope and results
🧪	tests/db-verify-indexes/run.php	27 assertions that the index list understands FULLTEXT
🌍	lang/{en,pt-BR,nb,nn}/tickets.php	search_modal.content*, part_*, found_in, too_short, not_indexed

---

2. One corpus, not an index per table

search_documents holds one row per searchable unit — a ticket subject, a message, a note, and later an attachment's extracted text. They are all the same shape.

The temptation is a FULLTEXT index on emails, another on ticket_notes, and a UNION. Do not:

• Relevance scores from different full-text indexes are not comparable. Each is computed against its own index's corpus statistics, so there is no meaningful way to sort a note hit against an attachment hit.
• Pagination then has nothing coherent to page by.
• Every new searchable thing is another branch in the query, and another place to re-express the permission rules.

Adding a source is now an INSERT with a new source_type, not a new query path.

The duplication objection, answered: yes, body text is copied. But emails.body_content is HTML, and indexing markup makes every ticket "contain" div, span and style — so a stripped plaintext copy is needed anyway. Derived text is being stored either way; the only question is whether it lives in one table or three.

---

3. ⚠️ tenant_scope — because NULL means opposite things

The single most important column to understand.

• A ticket with tenant_id IS NULL belongs to the default company — activeTenantFilter() only includes it when the caller's active company is the default.
• A knowledge article with tenant_id IS NULL is shared with every company — the exact opposite.

A nullable tenant_id alone would therefore make a row's scope depend on which source_type produced it. Instead the meaning is resolved at index time and written down:

tenant_scope	Means
company	visible to tenant_id only
default	the source's NULL meant the default company
shared	the source's NULL meant every company

Use searchCorpusTicketScope() / searchCorpusArticleScope() rather than deciding this at each call site.

---

4. The permission predicate goes into the query

searchCorpusQuery() takes a scope structure, never SQL:

$scope = searchScopeForAnalyst($conn, $analystId, ['include_internal' => true]);
$res   = searchCorpusQuery($conn, $query, $scope, ['limit' => 25]);

searchScopeToSql() is the only place that becomes SQL. Two reasons, and the first is not stylistic:

Post-filtering starves results. If you search first and remove what the caller may not see afterwards, the index returns its top N by relevance, you discard most of it, and hand back three rows — while hundreds the caller was entitled to never made the top N. It fails worst for the least privileged user, who is also the least likely to be the one testing it. A portal user, who can see only their own tickets, would get an empty page almost every time.

A SQL fragment in the interface welds it to MySQL. FreeITSM computes the predicate; the backend merely applies it. Replicating policy into a second system is dangerous — passing a computed filter to a dumb store is ordinary.

⚠️ An unspecified scope fails closed. Leave include_internal unset and internal notes are hidden, not exposed. tests/search/run.php asserts this deliberately rather than relying on it.

---

5. Query translation is not pass-through

⚠️ The single most surprising thing in this feature.

MySQL will not index words below innodb_ft_min_token_size. In boolean mode, requiring a term that is not in the index makes the entire query match nothing — so passing a user's words through verbatim as +word turns "printer in the office" into zero results.

searchParseQuery() therefore:

• reads the server's minimum at runtime (it is not the same everywhere — WAMP ships 0, stock MySQL is 3)
• drops terms below it and returns them in dropped, so the UI can say "ignored: in, of" rather than showing an empty page
• strips any boolean operators the user typed — they are ours to add, not theirs to inject
• adds a trailing wildcard to each term, the documented mitigation for MySQL having no stemmer: printer then finds printers. It over-matches on short stems, which is the accepted trade

Keep the user-facing language tiny — words, "phrases", -exclusion. The moment engine syntax reaches the UI, the engine has leaked into the product.

⚠️ Exclusion is per-document, not per-ticket. battery -swells still returns a ticket whose subject matches without the excluded word; a ticket only disappears when every one of its matching documents is excluded.

---

6. Two queries, in this order

1. rank    GROUP BY ticket, MAX(score), LIMIT n
2. detail  fetch the matching documents for just those tickets

The other order — fetch documents, collapse afterwards — reintroduces a top-N distortion, because the top 200 documents may collapse to a handful of tickets.

MATCH() must name exactly the columns of a full-text index, which is why there are two: ft_search_docs (title, body) and ft_search_docs_title (title). Searching titles alone is impossible without the second one.

There is no field weighting inside a MySQL full-text index. Ranking a subject hit above a body hit means composing the score by hand.

---

7. ⚠️ InnoDB and uncommitted rows

Rows written inside an uncommitted transaction are invisible to MATCH ... AGAINST. The full-text cache is flushed at commit.

Consequences you will meet:

• An indexer cannot write a row and search for it in the same transaction.
• A test that inserts, searches and rolls back returns zero for everything — and will appear to pass its negative control while proving nothing.
• searchBackfillRun() therefore commits in batches, and D007 writes a real probe row and deletes it in a finally block rather than using a rollback.

---

8. 🩺 Settings that break search silently

Three server variables decide which words exist at all. None errors when wrong.

Variable	Stock	If wrong
innodb_ft_max_token_size	84	Words longer than it are unindexed — authentication, configuration
innodb_ft_min_token_size	3	Words shorter than it are unindexed — short codes, abbreviations
innodb_ft_enable_stopword	ON	Common words dropped from the index

⚠️ WAMP ships max_token_size=10, in the [wampmysqld64] section of my.ini — not [mysqld], which is at the bottom of that file and unused. Every FreeITSM install on WAMP has long words silently unfindable until it is changed.

D007 reads all three and says the fix in plain English. Prefer running it to re-deriving any of this.

⚠️ After changing them, existing full-text indexes must be rebuilt.

---

9. Adding a new source

1. Add a SEARCH_SOURCE_* constant in includes/search/corpus.php.
2. Call searchCorpusUpsert() with the right tenant_scope (§3) and is_internal.
3. Extend the backfill if the source pre-dates the change.
4. Add a part_* translation key in every complete locale, in the same commit.

Nothing in includes/search/search.php needs touching — that is the point of one corpus.

---

10. What is deliberately not built

• Attachment text. The extractor tiers, the caps and the hostile-input handling are all designed on Full-text search §3.2, §6.4 and §8.2, and none of it exists.
• Indexing as tickets arrive. The backfill is run by hand.
• A second search backend. The seam is built so it could be added; the argument for not writing one until an install needs it is §8.5 there.
• Portal search. is_internal is stored, but whether requesters get content search at all is an open product question — nothing here should be read as sufficient to expose it.