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/indexer.php	searchIndexTicket() / searchIndexArticle() — the one definition of each source's corpus rows, plus the dispatch subscriber that keeps tickets current
⚙️	includes/search/extract.php	Attachment text, tier 1. Its own guide: Attachment text extraction
🗄️	attachment_text (table)	The durable extracted text. search_documents holds a derived copy
⚙️	includes/services/knowledge.php	reindexForSearch() — articles are indexed from here, by direct call. See §9a
⚙️	includes/search/backfill.php	Walks tickets then articles, calling the indexer. Batching and commits only
🧰	scripts/search_backfill.php	CLI wrapper — --limit, --prune, --stats
⚙️	includes/ticket_events.php	ticketDispatchCreated() — the one place ticket.created is announced from
⚙️	workflow/includes/engine.php	Where the subscriber is hooked in, next to the notification bell's
🔌	api/tickets/search_content.php	The inbox's content search. Builds a scope; decides nothing itself. Tickets only — it skips corpus rows with no ticket_id
🔌	api/system/global_search.php	⌘K. Seven name-matching sources, then content hits as their own trailing group
🎨	assets/js/command-palette.js	ticket_content / article_content groups, rendered last
🖥️	system/search/index.php	System → Search — index status and the Rebuild button
🔌	api/system/search_status.php	Read-only counts, coverage and the server's minimum word length
🔌	api/system/search_rebuild.php	One slice of a rebuild per call. See §10
⚙️	system/includes/areas.php	The search/ card on the System landing page
🖥️	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. Staying current — the dispatch subscriber

includes/search/indexer.php subscribes to WorkflowEngine::dispatch, alongside the notification bell, in its own try/catch. An index that cannot be written must not cost somebody their ticket.

It reindexes the WHOLE ticket, on purpose

Every interesting event rebuilds the ticket's subject row, all its message rows and all its note rows, rather than the one row that changed. That looks wasteful and is the right trade:

• Ordering-immune. Some paths announce before the opening message is written and some after. Indexing "the row that just changed" would need every caller to fire at exactly the right moment, forever.
• Self-healing. Anything a missed or failed event left stale is rewritten by the next event on that ticket.
• Cheap. A ticket is a handful of rows and every write is an upsert.

The listened-for events are deliberately few: ticket.created, ticket.note_added, ticket.reply_received, ticket.subject_changed, ticket.restored, ticket.deleted. A status or priority change moves no words about, so indexing on it would be pure cost.

⚠️ Announce AFTER the commit

Both new call sites dispatch once their transaction has committed. Two reasons, and the second is the one that bites: a rolled-back ticket must never announce itself, and InnoDB does not expose uncommitted rows to MATCH...AGAINST (§7), so an indexer running inside the transaction writes rows that the very next search cannot see.

🔑 One definition, or results depend on how a ticket got indexed

searchIndexTicket() is the single description of what a ticket's corpus rows are, and searchBackfillRun() calls it — the backfill is now just "walk the tickets, batch, commit". The document construction used to be written out in both places.

If those two ever drifted, a search result would depend on whether a ticket happened to be indexed live or by a rebuild. That is close to undebuggable, because both paths look correct in isolation.

---

9a. Knowledge articles are indexed by a DIRECT CALL, not the seam

Deliberately different from tickets, and the difference is the point.

Tickets need the dispatch seam because three separate paths create them and share no code. Articles are the opposite: KnowledgeService is the only thing that writes knowledge_articles, so calling searchIndexArticle() from its five write points (save-update, save-create, archive, restore, purge) is both complete and obvious.

The events would also be the wrong hook. A newly created draft fires nothing at all — knowledge.published is withheld on purpose so a workflow does not announce a page nobody can open — yet a draft still needs indexing, because the palette deliberately shows analysts their own work in progress. "The text changed" and "tell people about it" are different questions for articles in a way they are not for tickets.

Two traps live in searchIndexArticle():

• 🔑 NULL tenant_id on an article means shared with EVERY company — the exact opposite of a ticket, where it means the Default company. This is the entire reason searchCorpusArticleScope() exists separately from searchCorpusTicketScope(). It had been written and never called.
• 🔒 audience maps onto is_internal failing CLOSED. Anything not explicitly opened to customer or public counts as internal, so a future portal-facing search cannot leak an internal article by default.

Archived articles have their row removed rather than flagged, because the command palette has always excluded them — a search that disagreed with the rest of the product would be worse than one that finds less.

---

9b. ⚠️ ticket.created did not fire for most tickets

Worth knowing, because the same trap will catch anything else that subscribes here.

Until #1070, ticket.created was dispatched from exactly one place — TicketsService::create(), the analyst path. The self-service portal and the inbound-email ingest both write their own raw INSERT INTO tickets and never went near that service, so the event fired for a minority of tickets on any real service desk.

The mailbox file even documents the mistaken belief: its ticket.reply_received dispatch is deliberately suppressed for the opening message because "ticket.created already covers that one". It did not, so a new emailed ticket announced nothing at all.

includes/ticket_events.php now builds the payload once from the stored row, and all three callers use it. Two fields are passed in rather than read, because they describe the act of creating rather than the ticket and legitimately differ per channel: created_by (analyst id, portal user id, or null for email — nobody signed in created it) and requester_email.

This changed behaviour beyond search. A workflow triggered on ticket.created that had silently never run for emailed or portal tickets starts running. Check what is configured before updating a busy install.

---

9c. 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. Add it to searchIndexTicket() if it hangs off a ticket, so live indexing and the backfill both pick it up.
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. System → Search, and why the rebuild is chunked

system/search/index.php is the administrator-facing view of the corpus. Everything on it was already obtainable from D007 — Search corpus health, but D007 is a diagnostic: it lives under Debug Tools, prints a wall of environment detail, and is not where anyone looks to answer "is search working?".

The screen reports entries, tickets indexed against tickets total, articles indexed against articles total, when the index last changed, a breakdown by kind, and — importantly — the server's minimum word length, which is invisible from the application and is the single most common cause of a search that finds nothing.

⚠️ The rebuild runs in slices

api/system/search_rebuild.php indexes 200 tickets per call and returns where it stopped. The client passes last_ticket_id back as since_ticket_id and calls again until done.

The rebuild is the one operation here that scales with the size of the installation. A single request over years of tickets would run past max_execution_time and die halfway, leaving a partly-built index and no way to tell how far it got. Slicing also gives an honest progress bar rather than a spinner that cannot say whether anything is happening.

Three details that make it work:

• searchBackfillRun() returns last_ticket_id and tickets_remaining. Without the first the caller cannot resume; without the second it cannot show progress or know when to stop.
• Knowledge articles ride on the final slice only, via the articles option. They are not keyed by ticket id, so including them in every slice would re-index all of them every time.
• The prune runs once, on the last slice. It is a single sweep over trashed tickets, not per-slice work.

Rebuilding is always safe to repeat: every write is an upsert on (source_type, source_id), so a slice that runs twice updates in place.

What this screen deliberately does NOT have

The design in Full-text search §8.4 puts two selectors here — document extraction and search backend. Neither is built, because each would offer exactly one option today. A dropdown with a single choice is furniture that implies a capability the product does not have. They belong here when there is something to choose between.

---

11. What is deliberately not built

• Attachment text — built, both tiers. See Attachment text extraction — Developer Guide: .docx/.xlsx/.pptx and text formats with no dependency, PDFs and OCR through Apache Tika. That page also explains why a pure-PHP PDF reader is the wrong turn, and the two queue bugs worth learning from.
• The two selectors on System → Search. Extraction and backend, from §8.4. Deferred until either has a second option — see §10.
• 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.