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.

-- 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/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:

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.

-- 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 != '';