Scheduled tasks: add the attachment extraction worker, and catch up
Checking rather than assuming turned up a real gap: the cron page listed
FOUR jobs and the attachment worker shipped as a fifth. Somebody setting
up a new install from that page would have configured everything except
the one added this week.
Added to the table and to both the Windows and Linux command blocks,
with a note that it is the odd one out: its absence is NOT silent
failure, because documents are still read opportunistically while people
use FreeITSM. The job keeps up with a busy desk rather than being the
only thing that works. That distinction matters on a page whose whole
premise is "these fail silently".
Full-Text-Search: #1078 and #1079 added to the shipped list.
Searching-Inside-Tickets: "if a document is not turning up" now says
System > Search names the file and the reason, rather than only implying
totals.
Search: attachment extraction tier 2 (#1076-#1077). Discussion #53 closed.
The extraction guide's §9 said "what tier 2 will need". It now describes
something that exists.
Documents the thing that shapes the whole tier: CONFIGURED and AVAILABLE
are different states. tikaExtract() returns three outcomes, and a
service being DOWN must write `pending`, never `failed` - a five-minute
outage must not blacklist every PDF that arrived during it. Also why
there is deliberately no health check before each file: the extraction
attempt IS the check, and a ping per attachment would be an extra round
trip each time.
Why both drains exist: a cron-only design does nothing at all on an
install that has not set one up, which includes every evaluation.
And §9.3 on the two bugs, because they share a shape worth recognising -
a queue that looks busy and clears nothing, which is much harder to
notice than a crash. One: the indexer would not reconsider `pending`, so
the drain could never move the depth. Two: the requeue was
indiscriminate and put files Tika is never asked about into a queue that
could not clear them. The general rule is written down: a status nothing
will ever act on is a leak, so make the worker able to terminate those.
Operational notes that will otherwise cost somebody an afternoon: use
the -full image or there is no OCR, and Tika has no authentication at
all so it must never be published.
User page: PDFs and scanned documents move from "not read" to "read once
you add a document reader", with where to set it up and where the
background-work switches live.
Full-Text-Search: all three of the discussion's original questions now
answered yes. §8.2's three tiers are two-and-a-half - the external
SEARCH ENGINE remains deliberately unbuilt and §9.5's argument still
stands.
All 13 files named verified to exist; all links resolve.
Add "Attachment text extraction — Developer Guide" (#1075)
A page of its own rather than another section on the corpus guide: the
subject carries more security reasoning than anything else in search,
and it needs room.
Covers the two tiers and, importantly, that the split is drawn on a
SECURITY line rather than a convenience one - tier 2 formats need a real
document parser, and the input arrives from anyone who can email the
service desk through an endpoint with no authentication at all.
Includes an answer to the question that will keep coming up: "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. Font encodings,
compressed cross-reference tables, empty-password encryption and
positioned-not-flowed text each break it, and the objection is the
FAILURE MODE rather than the effort: unsupported is honest, silent
mojibake looks indexed and quietly matches nothing. Records what a
pure-PHP tier would have to promise (fail closed, never emit doubtful
text) if it is ever wanted.
Documents why attachment_text is the durable record and search_documents
the derived copy, with the two things it buys - a rebuild reads no
files, and whole-ticket reindexing re-opens nothing.
Every guard with the reason it exists, including that zip bombs are
refused on DECLARED sizes before an entry is read, and that writing the
test for that guard exhausted PHP's memory twice, which is the
demonstration.
The OOXML welding trap in full, since it is the one that silently ruins
search. Plus the known gaps stated honestly: docx headers/footers/
footnotes, xlsx inline numbers, pptx speaker notes.
User page: attachments are now a table of what IS and is not read,
leading on the fact that FreeITSM says which files it could not read
rather than staying quiet.
All links resolve; all eight files named verified to exist.
Search: System → Search screen (#1074), and tidy the status tables
User page: the backfill section is replaced by "Checking on it later:
System → Search". Rebuilding no longer needs a command line, which is
the part that mattered on hosting that does not give you one. Keeps the
three cases where you WOULD rebuild, and points at the minimum word
length as the thing that explains most "search finds nothing" reports.
Full-Text-Search: #1074 moved into the SHIPPED table where it belongs -
it had landed under "not built" - and the two §8.4 selectors listed as
deliberately deferred in its place, with the reason.
Dev guide gains §10 on the screen and, more usefully, on why the rebuild
is CHUNKED: it is the one operation that scales with the install, and a
single request over years of tickets dies past max_execution_time
leaving a half-built index and no way to tell how far it got. Records
the three details that make resuming work - last_ticket_id,
tickets_remaining, and articles riding the final slice only because they
are not keyed by ticket id.
Sections renumbered (old §10 is now §11) and its stale "⌘K content
search" entry replaced, since that shipped in #1072.
All 25 files named in the file table verified to exist.
Search: ⌘K content search and knowledge articles (#1072-#1073)
Discussion #53 is now down to attachments.
Full-Text-Search: status table updated, and §5.1 gains a note on where
the three original questions actually stand. Q1 done, Q3 substantially
done, Q2 (attachments) the only one outstanding - plus the fourth
question from the thread, whether search would reach Ctrl+K, answered
yes. The original assessment table is kept, because its "honest answer
today" column is what the discussion was replied to with.
Dev guide gains §9a on why articles are indexed by a DIRECT CALL from
KnowledgeService while tickets go through the dispatch seam. Tickets
needed the seam because three paths create them and share no code;
articles have exactly one writer. And the article events are the wrong
hook anyway - a new DRAFT fires nothing at all, because
knowledge.published is deliberately withheld so workflows do not
announce a page nobody can open, yet a draft still needs indexing.
Records the two traps that had been designed for and never exercised:
NULL tenant_id on an article means shared with EVERY company, the
opposite of a ticket (which is why searchCorpusArticleScope exists and
had never been called), and audience maps onto is_internal failing
CLOSED so a future portal search cannot leak by default.
Sections renumbered so 9/9a/9b/9c read in file order.
Search: the index keeps itself current (#1070-#1071)
All three search pages said indexing was manual. It is not any more.
Searching-Inside-Tickets: the backfill is now genuinely one-off, and the
warning that new tickets stay invisible until you re-run it is replaced
by what actually happens. Adds the three cases where you WOULD run it
again - after correcting the full-text settings, on suspected drift, and
after restoring an old backup - because "never" is not true either.
"Attachment text" replaces it as the honest what-it-does-not-find entry.
Full-Text-Search: status block updated, and §3.5's open question is
marked answered. It asked whether to index history or only from
switch-on; the answer is both, and the two share one document builder.
Command-palette content search moves onto the not-built list, since the
discussion asked for it and it is genuinely still missing.
Search-Corpus-Developer-Guide gains §9 on the subscriber - why it
reindexes the WHOLE ticket rather than the changed row (ordering-immune,
self-healing, cheap), why the dispatch must come AFTER the commit, and
why the document construction moved out of the backfill: if the two
drifted, a result would depend on how a ticket happened to get indexed.
And §9a on the trap that will catch the next subscriber too:
ticket.created fired from ONE place, the analyst path, so it missed the
two channels most tickets arrive through. The mailbox file documents the
mistaken belief in a comment.
Search: the user page and developer guide for what actually shipped
The design page was carrying the whole feature on its own, which conflates two
different readers: someone who wants to search their tickets, and someone who
has to maintain the thing.
Searching-Inside-Tickets — how to use it. The new field, what a result means,
the three query forms, and honestly what it does NOT do: attachments are not
searched, and nothing indexes new tickets until the backfill is run again. Also
the two one-off setup steps, and why D007 is worth running ONCE at setup rather
than only when something looks broken -- because database search fails quietly,
and a "longest word" limit makes words like authentication unfindable while
shorter ones work perfectly.
Search-Corpus-Developer-Guide — the traps, in the order they will bite:
- one corpus rather than an index per table, because relevance scores from
different full-text indexes are not comparable
- tenant_scope, because NULL means OPPOSITE things in the sources
- the predicate goes INTO the query, and why post-filtering starves results
worst for the least privileged user
- query translation is not pass-through: requiring a term below the index
minimum makes the WHOLE query match nothing
- InnoDB not exposing uncommitted rows to MATCH, and the three ways that
surfaces
- the three server settings that break search silently, and that WAMP ships
one of them wrong in [wampmysqld64], not [mysqld]
The design page now points at both and says plainly that it is the reasoning
rather than the manual -- its lasting value is the ruled-out section, which is
the part nobody can reconstruct from the code.
Every file named in the guide verified to exist; every wiki link verified to
resolve. Both pages added to the sidebar under the command palette.
Full-text search: the page said "nothing here is built" and that is no longer true
Phase 1 is part built -- searching inside ticket messages and notes shipped as
#991-#996 -- so the opening line had become the exact failure this wiki warns
about elsewhere: a design document quietly describing a product that has moved
on.
Replaces the status block with two tables: what shipped, and what has NOT. The
second matters more, because "full-text search works" would be read as including
attachments, and attachment text is the entire hard half that does not exist
yet. Indexing also does not happen as tickets arrive -- the backfill is run by
hand.
Keeps the pre-build warning but sharpens it: where the page describes something
now built, the code is the authority. Its lasting value is the ruled-out section,
because rejected options and their reasons are the one thing nobody can
reconstruct by reading the code.
Full-text search: how it's configured, and how many engines to support
Two additions from working through the admin-facing side.
8.4 WHERE THIS IS CONFIGURED. Not a card per engine under System ->
Integrations, for two reasons. Search is singular where trackers are plural --
Jira and Azure DevOps can both be connected because a ticket can escalate to
either, but only one thing answers a search query, so per-engine cards would
describe a model that does not exist. And a search engine is not an integration
in that sense at all: integrations are external systems you hold an account with
and talk to as a peer, whereas an engine is infrastructure, the same family as
the database or the mail server.
The right precedent is already in the product -- the mailbox modal, where you
pick Microsoft/Google/IMAP and the provider-specific fields appear. One Search
screen, two independent sections (extraction and backend, per 8.0), plus index
status and a rebuild. Explicitly install-wide and admin-only: two analysts
cannot be on different engines because there is one shared index, so this is the
same class of setting as the database connection rather than a per-analyst
preference.
8.5 HOW MANY ENGINES. An earlier draft said support exactly one. That was too
conservative and ignored this codebase's own precedent: AI providers and issue
trackers both run multiple providers behind one contract, and adding Azure
DevOps as the second tracker needed no schema or core change. The surface here
is only four operations, so an adapter is plausibly 150-250 lines with no client
library, and OpenSearch forking from Elasticsearch 7.10 means one adapter likely
serves both. There is also a real-world argument: "we already run Elasticsearch"
is common, and making that admin add a second search system cuts against the
no-lock-in position.
The genuine cost is not code volume. EVERY ADAPTER IS A NEW PLACE AUTHORISATION
CAN FAIL OPEN, because each translates the permission predicate into a different
filter syntax, and a translation bug returns rows rather than raising an error --
a wrong search result looks exactly like a right one. That makes search adapters
sharper than AI adapters, where wrong output is visibly wrong.
Conclusion: design the interface for many, ship one, add on demand. The first
adapter is what proves the contract; building three against an untested
interface means rewriting three. And every adapter must pass the same permission
test suite -- fixed "this user must see exactly these ids" cases answered
identically by every backend -- which is what makes admin choice safe rather
than merely generous.
Full-text search: state exactly what it will and won't match
Answers the most concrete question anyone can ask of a search feature: if a
document says "Tower of London", what must you type to find it?
Both the natural queries work -- "tower London" and "tower in London" -- but NOT
because of fuzzy logic. Word order is irrelevant because the index holds
individual words, and "in" is an InnoDB stopword dropped from query and index
alike. Those two behaviours cover a lot of everyday searching and are easily
mistaken for fuzziness.
What MySQL genuinely cannot do is forgive a misspelling or a word ending. No
edit-distance matching, and -- the one that will bite hardest -- NO STEMMING, so
"printers" does not match "printer". People type both constantly. Postgres has a
stemmer; MySQL does not. The trailing-wildcard mitigation (+printer*) is noted
along with its cost: over-matching on short stems, and no leading wildcards.
This also supplies the one honest argument for a phase 2 engine that is not
about scale. Typo tolerance is the headline feature of Meilisearch and
Manticore. Staying on MySQL trades forgiveness rather than speed, and that is
better known now than discovered from a user asking why a search with one
transposed letter found nothing.
Cross-referenced from section 10, distinguishing this (a definite, knowable gap)
from the genuine uncertainties listed there.
Full-text search: separate the two optional services explicitly
Feedback that "optional external search" was the one unclear part, from someone
who had been through the entire design conversation -- so it will certainly be
unclear to a reader arriving cold.
The cause is that the plan contains TWO unrelated optional services and the page
never said so plainly: an EXTRACTOR (reads PDFs, turning them into text -- the
only route to PDF search, so likely wanted) and a SEARCH ENGINE (searches faster
at enormous scale -- rarely needed). The expected shape for an install wanting
the full feature is extractor and no engine.
New 8.0 states that up front, with the three properties of the engine option
that matter more than which engine it is: off by default and a setting rather
than a version; no data moves, since the engine holds only a derived copy while
the corpus stays the source of truth; and switching it off loses nothing.
Full-text search: filtering by source, attribution, and three MySQL limits
Prompted by asking whether the corpus can search JUST notes or JUST attachment
names, and whether a result can say which part of a ticket it matched. It can --
source_type is an ordinary column, so per-type filtering, facet counts and
"found in a note on ABC-123-45678" all come for free. But answering it properly
surfaced four things the design had left implicit.
TICKET SUBJECT BECOMES ITS OWN ROW (source_type 'ticket'). Buried as the title of
the first email, "matched the subject" cannot be stated cleanly to the user or
weighted separately from body text.
THE RESULT SHAPE IS TWO BOUNDED QUERIES, not one clever one: rank tickets with
GROUP BY + LIMIT, then fetch the matching documents for just those ticket ids.
That yields "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 may collapse to only a handful of tickets.
THREE MYSQL CONSTRAINTS now stated explicitly, because each would otherwise be
found during implementation:
- MATCH() must correspond exactly to a FULLTEXT index, so searching filenames
alone needs a SECOND index on (title). Added to the schema.
- Filenames may be better served by LIKE than by full-text at all: they
tokenise badly and people search fragments, which is what LIKE is good at
and full-text is weak at. The two searches can use different mechanisms.
- There is no field weighting inside a MySQL full-text index, so ranking a
subject hit above a body hit means composing the score by hand.
None of these change the shape of the design -- the corpus still works -- but all
three are the difference between it being a data model and being a usable search
experience.
Full-text search: add the corpus design, and answer #53 point by point
Two additions, both prompted by the question "can this actually cope with
searching ordinary rows AND extracted document text?"
NEW SECTION 4 -- the corpus. The page previously said "index the bodies and
notes" and "extract attachment text" without saying how those become ONE
searchable thing. The naive build (a FULLTEXT index per table, then UNION) does
not work: relevance scores from different full-text indexes are computed against
their own corpus statistics, so a note hit cannot be meaningfully sorted against
an attachment hit, pagination has nothing coherent to page by, and every new
source is another branch that must re-express the permission rules.
So: one search_documents table where a row is one searchable unit whatever its
origin -- email body, note, attachment text, knowledge article. One index, one
relevance scale, one query. The schema and the query are both on the page. Two
properties fall out of it: the permission predicate sits in the WHERE alongside
the match, so pre-filtering is the path of least resistance rather than
something to remember; and a GROUP BY collapses hits to their ticket, because
users think in tickets and one ticket with the term in four replies would
otherwise flood the first page.
The duplication objection is answered on the page: stripped plaintext is needed
anyway because body_content is HTML, so derived text is stored either way -- the
only question is one table or three.
NEW SECTION 5 -- does this answer the discussion? Their three questions answered
directly, then their requested content types and file formats mapped to what the
design does with each. Two honest results rather than a clean sweep: their
request for "historical conversations" depends on the unmade backfill decision,
and PDF -- the format they listed first -- is the one needing an optional extra
service, with scanned PDFs needing OCR on top.
Also notes that their third question named KNOWLEDGE ARTICLES, which the earlier
draft had missed. They fit the corpus as another source_type, but Knowledge
already has semantic search, so that is two search paths over one body of
content and the UI must be clear which is which.
NEW SECTION 10 -- what we are not confident about, kept separate from the design
because confidence is not uniform: MySQL relevance quality is its weakest area,
the three-character token minimum may bite hard in a product where people search
short codes, and volume behaviour is untested. With the cheap way to settle all
three first -- build the corpus and the search function only, load real data,
run real queries, before any UI or extraction work is committed.
Sections renumbered accordingly; all cross-references verified to resolve.
Blue sky: full-text search across tickets and attachments
Records where the design conversation from discussion #53 landed, including the
ideas ruled out -- which is the part that otherwise gets re-derived.
The governing insight is that extraction is the hard half and indexing is not.
MySQL 8.0 already does full-text natively; turning a PDF into text is the
genuinely difficult problem. So the useful third-party integration is a document
parser, not a search engine.
Covers what phase 1 would be (ticket bodies and notes first, attachments in
optional tiers), the security rule that the QUERY must carry the permission
controls rather than results being filtered afterwards, the settings path for
very large installs, and the anti-brick-wall rules that keep the backend
swappable.
Ruled out, with reasons: vectors as the foundation (they sit downstream of
extraction and don't remove it, cosine-in-PHP doesn't scale, semantic search is
weakest at serial numbers and error codes, and embedding every attachment ships
customer documents to a third party on a self-hosted product); an external
search engine as the STARTING point (still extracts nothing, adds a sync problem
MySQL doesn't have); post-filtering results; and any hard PDF dependency.
Two corrections to how the request was framed, marked on the page: the extracted
text must NOT be one of the things a settings screen relocates -- it stays in the
FreeITSM database and is the source of truth the index is rebuilt from, which is
what stops an engine change becoming a re-extraction project. And the
conversation had drifted attachment-heavy: ticket bodies and notes are the bulk
of the value and the cheapest part.
Also adds what the brief left out: whether history gets backfilled, whether
deletion and GDPR erasure cascade to the index, that snippets are themselves
content, and that extracted text is a plaintext copy carrying the database's
protections rather than the file's.
Listed on Blue-Sky-Thinking and in the sidebar.