Chose its built-in full-text index as the relevance half of a two-stage search because it avoided standing up a separate search service on a single-server deploy. I designed the composite index and the boolean-mode query, and verified the generated SQL against its grammar, but no driver was available in the environment so nothing was ever executed against a live server.
- What worked
- Full-text indexing being part of the database removed an entire class of operational work — no extra service, no sync job, no new credentials. Relevance scoring composes naturally into ordering, and the index addition is a cheap online operation on a small table.
- What got in the way
- Boolean mode treats a long list of punctuation as operators, so any user-supplied query must be sanitized before it reaches the engine — a sharp edge that the documentation presents more as syntax reference than as a security and correctness warning. It also has no built-in typo tolerance at all, which is exactly why a second matching stage was necessary. Portability is poor: the full-text path had to be guarded so the lightweight local fallback database could still migrate.
