# FTS5

full-text search in sqlite. powerful but has sharp edges.

## ambiguous columns in JOINs silently fail

when joining an FTS5 virtual table with a regular table, **always qualify column names**. FTS5 tables expose the same column names as the content they index, so unqualified names are ambiguous. sqlite may resolve them unpredictably or error — and if you catch/ignore the error, the query silently returns nothing.

```sql
-- WRONG — did, handle, display_name exist in both tables
-- silently fails or returns wrong data
SELECT did, handle, display_name, avatar_url
FROM actors_fts
JOIN actors ON actors.did = actors_fts.did
WHERE actors_fts MATCH ?

-- RIGHT — qualify every column
SELECT actors.did, actors.handle, actors.display_name, actors.avatar_url
FROM actors_fts
JOIN actors ON actors.did = actors_fts.did
WHERE actors_fts MATCH ?
```

this bug is especially insidious because: (1) the FTS table has data (you can verify with `WHERE did = ?`), (2) the MATCH query parses fine, (3) queries that don't touch the FTS table (like `LIKE` prefix) still work, so it looks like only certain searches are broken. the actual cause is the JOIN failing on column resolution.

discovered in typeahead ingester — display_name search appeared broken for days. handle-prefix search worked fine (doesn't use FTS), but FTS-based display_name search returned empty. root cause: unqualified column names in the FTS JOIN.

## sanitize user input for MATCH queries

FTS5 MATCH has its own query syntax — `"`, `*`, `+`, `(`, `)`, `^` are operators. user input containing these will cause query errors. strip everything except letters, digits, whitespace, `.` and `-` before building MATCH queries.

```js
// js/ts
function sanitize(q) {
  return q.replace(/[^\p{L}\p{N}\s.-]/gu, "").trim();
}
```

then build the MATCH query:
```
"sanitized term"*    -- phrase prefix search
```

## UNINDEXED columns

columns marked `UNINDEXED` are stored in the FTS table but not indexed for MATCH. useful for carrying IDs through without bloating the index:

```sql
CREATE VIRTUAL TABLE actors_fts USING fts5(
  did UNINDEXED,     -- stored but not searchable via MATCH
  handle,            -- indexed
  display_name,      -- indexed
  tokenize='unicode61 remove_diacritics 2'
);
```

you can still `SELECT did FROM actors_fts WHERE did = ?` (exact lookup), but `MATCH` won't search the `did` column.

## ALTER TABLE RENAME on FTS5

FTS5 virtual tables use shadow tables (`_content`, `_data`, `_idx`, `_docsize`, `_config`). `ALTER TABLE RENAME` renames the virtual table but the shadow tables should follow. in practice this mostly works, but to avoid any risk: create the FTS table with its final name instead of renaming.

```sql
-- safer: create with final name, populate from already-swapped source
ALTER TABLE actors_stage RENAME TO actors;  -- swap regular table first
CREATE VIRTUAL TABLE actors_fts USING fts5(...);  -- create FTS with final name
INSERT INTO actors_fts (did, handle, display_name)
  SELECT did, handle, display_name FROM actors WHERE handle != '';
```

## sources

- [typeahead](https://tangled.org/zzstoatzz.io/typeahead) — `actors_fts` ingester and search (`src/handlers/search.ts`, `services/src/server.zig`)