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	The extractor. Format support, the guards, OOXML unzipping, normalisation
⚙️	includes/search/indexer.php	searchIndexTicketAttachments() — path containment, the cache, corpus rows
🗄️	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 — shipped	.txt .csv .log .md .json .xml .yml .ini, and .docx .xlsx .pptx	nothing
2 — not built	PDF, legacy .doc .xls .ppt, images, anything scanned	an external extractor

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. What tier 2 will need

The seam is already here, which was the point of building tier 1 first:

1. A setting: extractor URL and timeout. §8.4 of the design sketches the screen; only the status half is built.
2. attTextSupports() gains the tier-2 formats when a URL is configured — it must stay format-aware, not "send everything and hope".
3. A queue. OCR takes seconds to minutes per page. It cannot run inside the mailbox poll or a request; status = 'pending' exists for exactly this and is currently never written.
4. extractor on each row becomes the service name, so an install that adds a service later can find every unsupported row and redo just those.

Nothing in the corpus, the search function or the UI changes. That is what the durable-store split in §3 buys.

---

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