Attachment Text Extraction Developer Guide

Attachment text extraction — Developer Guide

How text gets out of a file and into the search index, what is deliberately refused, and why the refusals are the interesting part.

The user-facing page is Searching inside tickets; the corpus this feeds is Search corpus — Developer Guide; the design reasoning is Full-text search §3.2, §6.4 and §8.2.

The one-line summary: extraction splits into a dependency-free tier that ships and an external-service tier that does not exist yet, the split is drawn along a security line rather than a convenience one, and the extracted text is stored as the durable record so no file is ever read twice.

---

1. 📁 The files involved

Colour key: 🗄️ schema · ⚙️ shared service · 🔌 API · 🖥️ page · 🩺 diagnostics · 🧪 tests

🎨	File	What it does
⚙️	includes/search/extract.php	Tier 1. Format support, the guards, OOXML unzipping, normalisation
⚙️	includes/search/tika.php	Tier 2. The external extractor client — see §9
⚙️	includes/search/extract_queue.php	The pending queue, both drains, and their switches
🧰	cron/attachment_extract.php	The scheduled worker
🔌	api/system/tika_settings.php	get / save / test the connection
🖥️	system/integrations/tika.php	System → Integrations → Apache Tika
⚙️	includes/search/indexer.php	searchIndexTicketAttachments() — path containment, the cache, corpus rows, and which tier gets asked
🗄️	database/freeitsm.sql	attachment_text — the durable store
🗄️	includes/db_verify_schema.php	Its columns. ⚠️ Its PK is attachment_id, so it is also in $primaryKeys
🔌	api/system/search_status.php	Counts by outcome, for the screen below
🖥️	system/search/index.php	System → Search — shows every attachment by outcome
🧪	tests/search-extract/run.php	25 assertions. No database, no HTTP — real files in a temp directory

---

2. Two tiers, split on a security line

Tier	Formats	Needs
1	.txt .csv .log .md .json .xml .yml .ini, and .docx .xlsx .pptx	nothing
2	PDF, legacy .doc .xls .ppt, RTF, OpenDocument, .eml/.msg, images and anything scanned	an external extractor — see §9

The line is not "easy versus hard". It is whether parsing the format can be done without running a large third-party parser inside the web request.

Tier 1 formats are either already text, or a ZIP of XML that ZipArchive and a regex can handle. Tier 2 formats need a real document parser, and that is a different proposition:

• The input is hostile. An attachment arrives from anyone who can email the service desk, through api/tickets/check_mailbox_email.php, which has no authentication at all. Malformed documents are a well-trodden route to memory corruption and code execution, and this process holds the database credentials.
• FreeITSM has no Composer. A PHP PDF library would be vendored by hand and ours to patch for its lifetime. A bundled library version was itself a finding in the August 2026 security review.

So tier 2 is deliberately out of process: one extraction service, in a container, reached over HTTP. Until an install has one, those formats are recorded as unsupported, which is an honest answer rather than silence.

🔑 This is why "just add a PDF library" is the wrong instinct, even though it looks like the shortest path. The isolation boundary is the feature.

"But a PDF with a text layer is just compressed text — can't PHP do that?"

Partly, and it is the most reasonable-sounding wrong turn available, so it is worth writing down.

A text-layer PDF does keep its text in content streams, usually Flate-compressed, and PHP has zlib built in. Decompress the streams, pull out the Tj / TJ text-showing operators, done. This genuinely works on simple PDFs — the sort Word exports.

It breaks on real ones, in ways that are hard to notice:

• Font encodings. Bytes map to glyphs through /Differences arrays, CMaps and ToUnicode maps. A subsetted font with none of those resolved produces mojibake that looks like text: plausible length, indexed happily, matches nothing.
• Compressed cross-reference tables (PDF 1.5+). The xref lives inside an object stream, so naive parsing fails on a large share of modern documents.
• Encryption. Many PDFs with no password are still encrypted with an empty user password, and the streams need decrypting before anything can be read.
• Text is positioned, not flowed. TJ arrays carry kerning offsets; reassembling words needs real handling, or you get the §6 welding problem again and worse.

🔑 The objection is the failure mode, not the effort. unsupported is honest — the Search screen says so and an analyst looks elsewhere. Silent mojibake is worse than both: it looks indexed, so nobody investigates, and search quietly cannot find things it appears to cover.

If a pure-PHP tier is ever wanted, the rule has to be fail closed: handle only uncompressed-xref, unencrypted, standard-encoding files, mark everything else unsupported, and never emit doubtful text. That would catch a fair slice of "PDF printed from Word" with no service at all. It is a legitimate middle tier — it is just not this one, and it must never be the default, because a wrong-but-plausible index entry is the worst outcome search can produce.

---

3. 🗄️ attachment_text is the DURABLE RECORD, not the index

search_documents holds a derived copy of this text. attachment_text holds the result of actually opening the file, and it is the thing that must survive.

This is Full-text search §3.3, and it earns its keep twice:

A rebuild reads no files. searchBackfillRun() re-derives every corpus row, and does it without opening a single attachment. For a .docx that would merely be wasteful. For tier 2 it would mean re-OCRing every scanned PDF an installation has ever received — hours of CPU, and with a paid extraction service, an actual bill, every time somebody clicks Rebuild index.

Live indexing reads no files either. searchIndexTicket() reindexes the whole ticket on every event (see the corpus guide §9 for why). Without the cache, a ticket with ten attachments would re-open and re-unzip all ten every time somebody added a note.

first sight of the file   →  extract  →  attachment_text  →  search_documents
every time after that     →            attachment_text    →  search_documents

Column	Why
attachment_id	The PK. One row per attachment, so re-indexing is a lookup
status	The outcome, carried as a fact — see §4
extractor	builtin today. Kept so a tier-2 install can find everything the built-in tier could only mark unsupported, and redo exactly those
extracted_text	The text itself
chars	Length, for the screen

ON DELETE CASCADE from email_attachments: the extracted text is meaningless once the file it came from has gone.

---

4. 🔎 Status is a fact, and it is shown

Every attachment ends up with one of these, and System → Search lists the totals:

Status	Means
extracted	Text was read in full
truncated	Read, but longer than the character cap
too_large	The file is over the size limit; never opened
unsupported	No extractor handles this format — every PDF, today
failed	An extractor tried and could not
pending	Queued for an extractor that runs off the request thread (tier 2)

This is §3.4 of the design, and the reasoning is worth restating: a search that silently returns nothing, because a file was never readable, is worse than one that admits the file could not be read. The first teaches an analyst that the information is not there. The second tells them where to look instead.

extracted with empty text is a legitimate outcome — a document containing only images reads fine and yields nothing. It is not failed.

---

5. 🛡️ The guards, and why each exists

Every one of these is because the file came from a stranger.

5.1 Zip bombs — refused on DECLARED size

const ATT_TEXT_MAX_UNZIPPED_BYTES = 104857600;   // 100 MB total
const ATT_TEXT_MAX_ZIP_ENTRIES    = 2000;

A 40 KB .docx can declare terabytes of uncompressed XML. ZipArchive will hand it over one entry at a time until the process dies.

🔑 The guard sums statIndex()['size'] across the archive and refuses BEFORE reading a single entry. Not "unpack it and see how big it got" — unpacking is precisely what kills you.

That is not theoretical. Writing the test for this guard exhausted PHP's memory limit twice: first building a 100 MB string, then because ZipArchive buffers everything added until close(). The test now uses an injected small cap, and the production limits are unchanged.

5.2 Size and length caps

const ATT_TEXT_MAX_FILE_BYTES = 20971520;   // 20 MB — never opened above this
const ATT_TEXT_MAX_CHARS      = 200000;     // text kept per attachment

Plain text files are read with a bounded file_get_contents($path, false, null, 0, $maxChars * 4) rather than whole — there is no reason to pull 20 MB into memory to discard nearly all of it. The * 4 is multi-byte headroom.

5.3 Path containment — copied, not reinvented

The check is lifted verbatim from api/tickets/get_attachment.php:

$realBase = realpath($baseDir);
$realFile = realpath($baseDir . '/' . $row['file_path']);
if ($realBase === false || $realFile === false
    || strncmp($realFile, $realBase . DIRECTORY_SEPARATOR, strlen($realBase) + 1) !== 0) { … }

file_path comes out of the database, so this is not reachable from a request parameter today. That is exactly why it must be here: a row written before the upload rules existed, or by anyone with a foothold in the database, must not be able to make the indexer read config.php and put its contents in a searchable table. Reading is only half the damage; indexing it is the other half.

realpath() on both sides resolves symlinks, and is required on Windows where the base arrives with forward slashes and returns with backslashes.

5.4 Inline images are skipped

WHERE a.is_inline = 0 OR a.is_inline IS NULL. A signature logo is not a document, and without this it would be "extracted" on every reply in a thread to produce nothing.

---

6. ⚠️ The OOXML trap that silently ruins search

Office formats wrap every run of text in its own element:

<w:r><w:t>Please</w:t></w:r><w:r><w:t>reboot</w:t></w:r>

Strip the tags without putting something in their place and you get Pleasereboot — a word that exists in no dictionary and matches neither search term. The document appears indexed, the row has plausible length, and it is unfindable.

$s = preg_replace('/<[^>]*>/', ' ', $raw);   // ← a SPACE, not ''

tests/search-extract/run.php asserts both halves: that QuarterlyFirewall does not appear, and that Quarterly Firewall does. The negative alone would pass on an empty string.

Which entries are read

'docx' => ['word/document.xml'],
'xlsx' => ['xl/sharedStrings.xml'],   // cell text, stored once each
'pptx' => ['ppt/slides/'],            // trailing slash = every entry under it

Known gaps, stated honestly: .docx headers, footers, footnotes and comments live in their own parts and are not read. .xlsx numbers stored inline rather than in the shared string table are not captured, and neither are formulas. .pptx speaker notes are a separate part and are skipped. All are additive fixes — another entry in that map — and none is worth guessing at until somebody reports it.

---

7. Where it hooks in

searchIndexTicketAttachments() is pass 4 of searchIndexTicket(), alongside the subject, messages and notes. It therefore inherits everything the corpus guide §9 describes for free: it runs on every ticket event via the dispatch subscriber, it is ordering-immune, and it is self-healing.

The corpus row is shaped like any other:

Field	Value
source_type	attachment
source_id	the attachment id
ticket_id	the ticket it hangs off — so it groups with that ticket's other hits
title	the filename
body	the extracted text
is_internal	0 — an attachment is as visible as its ticket; it is not a note

Only extracted and truncated produce a corpus row. unsupported, too_large and failed are recorded in attachment_text and shown on the Search screen, but there is nothing to search.

---

8. 🧪 Testing it

php tests/search-extract/run.php — 25 assertions, no database, no HTTP. It builds real files in a temp directory and reads them back, because the risky part of this feature is file handling rather than SQL.

The assertions worth keeping when you change anything:

Assertion	Guards against
adjacent OOXML runs do not weld together	§6, the silent one
…and are separated properly	the negative above passing on an empty string
a PDF is unsupported, not failed	the honest-failure contract
a zip bomb is refused	§5.1
CONTROL — the same archive reads under a larger cap	proving the refusal is the guard, not an unreadable file
an empty file is extracted, not failed	image-only documents
a .docx that is not a zip fails cleanly	never throwing into the ticket's indexing

---

9. Tier 2 — the external extractor

Built in #1076–#1077. Configured at System → Integrations → Apache Tika; drained on terms set in Tickets → Settings → Indexing.

Nothing in the corpus, the search function or the search UI changed to add it. That is what the durable-store split in §3 bought.

File	Its job
includes/search/tika.php	The client. Settings, format list, tikaExtract(), tikaPing()
includes/search/extract_queue.php	The queue: depth, drain, opportunistic drain, the switches
cron/attachment_extract.php	The scheduled worker
api/system/tika_settings.php	get / save / test
system/integrations/tika.php	The screen

9.1 🔑 Configured and AVAILABLE are different states

The single most important thing in tier 2. tikaExtract() returns three outcomes, not two:

Outcome	Written	Meaning
ok	extracted / truncated	it read the file
retry = true	pending	transport error, timeout, or HTTP 5xx
retry = false	failed	HTTP 4xx — Tika answered and refused

⚠️ A service being down must never write failed. If a five-minute outage marked every PDF that arrived during it as failed, those files would be blacklisted permanently and never looked at again — the index would be quietly wrong forever and nothing would say so. Only Tika answering that it cannot read something is terminal.

There is deliberately no health check before each file. That would be an extra HTTP round trip per attachment; the extraction attempt is the check. tikaPing() exists only for the Test button on the settings screen.

9.2 Two ways the queue drains, and why both

cron/attachment_extract.php   25 per run   the real answer for a real install
opportunistic                  3 per pass  on the back of a request somebody made anyway

A cron-only design does nothing at all on an installation that has not set one up — which includes every evaluation, and any host that does not offer cron. Opportunistic draining means attachment searching works out of the box; the cron means it keeps up under load. Each has its own switch, both default ON, and the cron refuses to run when its switch is off — a scheduled task somebody set up and forgot is still subject to the setting.

The opportunistic hook currently lives in api/system/search_status.php: somebody looking at the search index is the ideal moment to read a few documents, because they are already there and they care about this exact thing.

9.3 ⚠️ Two bugs worth knowing, because they share a shape

Both produced a queue that looks busy and clears nothing — which is much harder to notice than a crash.

The indexer would not reconsider pending. It re-extracted rows that were new or unsupported, and the requeue wrote pending. So the drain reindexed the ticket, the ticket declined to reconsider the row, and the depth never moved. Caught by the cron's own "nothing cleared" warning. Both statuses are now in the reconsider list, and the reason is written down at the top of that block.

The requeue was indiscriminate. Configuring Tika flipped every unsupported row to pending, including a .ogg voice recording and an .html file that Tika is never asked about — so they sat in the queue forever. Two fixes: the requeue filters by tikaHandles(), and extractQueueDrain() self-heals anything already stuck by sending it back to unsupported.

🔑 The lesson for anything queue-shaped: a status that nothing will ever act on is a leak. Make the worker able to recognise and terminate those, because sooner or later something will put one there.

9.4 Operational notes

• apache/tika:latest-full, not apache/tika. The smaller image has no OCR, so scanned documents come back empty with nothing to explain why.
• 🔒 Tika has no authentication whatsoever. Bind it to loopback or a private container network; never publish the port. The settings screen says so in as many words.
• The size cap (ATT_TEXT_MAX_FILE_BYTES) applies to both tiers — there is no point shipping 200 MB across a network to be told it is large.
• Turning the extractor on requeues everything previously marked unsupported that it can handle, so an install does not have to wait for something to touch each old ticket.
• extractor on each row records which tier read it (builtin / tika), which is what makes that requeue targeted rather than a full re-read.

---

See also

• Searching inside tickets — the user-facing page
• Search corpus — Developer Guide — the table this feeds, and the indexing seam
• Full-text search — the design, including the options ruled out
• Safe file uploads — how the files got there in the first place