# 0008. Own full-text search index table

- **Status:** Accepted
- **Date:** 2025-11-16

## Context

Site-wide keyword search has to cover storages, locations, storage types, services, articles and static
content — six models with different columns — in three languages, with results ranked by how important the
match was.

## Decision

A single `search_index` table, one row per **record per language**, with three text columns by relevance
weight:

| Column | Weight |
|--------|--------|
| `dataLevel1` | ×3 |
| `dataLevel2` | ×2 |
| `dataLevel3` | ×1 |

MySQL FULLTEXT indexes on all three; relevance computed in SQL as the weighted sum of
`MATCH … AGAINST … IN BOOLEAN MODE`.

A model opts in by implementing `SearchableInterface` — `getSearchIndexData()`, `isSearchable()`,
`renderSearchResult()`. `ActiveRecord::afterSave()` indexes or removes it automatically.

## Alternatives

- **Elasticsearch / Meilisearch / Typesense.** Better search, another service to run, monitor and back up on
  a single-server deployment. Not proportionate.
- **`LIKE '%keyword%'` across the tables.** No ranking, no cross-model result set, and a full scan per table.
- **MySQL FULLTEXT directly on each model's table.** No unified result set, no per-model weighting, and a
  translated value inside a JSON column is not indexable that way.

## Consequences

- **One query returns ranked results across every content type**, already scoped to the current language.
- **Indexing is automatic and invisible.** Implement the interface and it happens.
- **One save writes three rows** (one per language). A model with a large `dataLevel1` triples its storage
  cost.
- **The FULLTEXT indexes cannot be declared in `getIndexes()`.** `SearchIndex::initData()` adds them with raw
  `ALTER TABLE`. A table created without `initData()` running has no full-text indexes and every search
  returns nothing — a silent, total failure.
- **Anything that writes outside ActiveRecord leaves the index stale**: imports, raw SQL, an AI translation
  run, adding a language. `./yii search-index/build` is the repair tool.
- **Words shorter than three characters are dropped** by `StringHelper::relevancyKeyword()` before the query
  is built, on top of MySQL's own `ft_min_word_len`. Short terms are unfindable.
- The keyword is interpolated into the SQL string, not bound. `relevancyKeyword()` strips every boolean-mode
  operator and quote character; that sanitization is load-bearing.
- **This is not the storage filter.** The filterable storage listing is `StorageSearchService`, a plain query
  builder. The two share no code.