Operating D1 site search with FTS5: trigram, bigram, and LIKE
# An implementation and operations note for multilingual site search: trigram for longer terms, a bigram index for two code points, and bounded LIKE fallback.
Public site search is not the same feature as the web search used to collect sources for AI-assisted drafting. This blog searches only published rows stored in D1 and selects FTS5 trigram, an application-generated bigram index, or LIKE according to query length. This article documents that routing and the operational rules that keep the indexes consistent. The implementation and cited documentation were checked on July 22, 2026.
Keep site search separate from web research
/search reads titles, excerpts, article bodies, and tags from the D1 posts table. It does not call SearXNG or the page-extraction service used by the admin drafting workflow. Public search therefore does not depend on an external search provider, and its responsibility stays separate from source collection for AI drafts.
The searchable document is multilingual. In addition to the canonical columns, it includes title_ja, excerpt_ja, content_ja, title_en, excerpt_en, and content_en. An English title must remain searchable even when the canonical title is Japanese.
Route by normalized query length
searchPosts trims the query and counts code points after NFKC normalization before choosing its primary path.
One code point: use LIKE across the ten canonical, Japanese, English, and tag fields.
Two code points: query posts_fts_bigram with MATCH. The generation of that index is covered in the separate implementation article, “A Japanese two-code-point bigram index on D1 and FTS5.”
Three or more code points: query the tokenize='trigram' table named posts_fts_ja, order by FTS5 rank, and return at most 30 rows.
SQLite documents that full-text queries shorter than three Unicode characters do not match rows in a trigram-tokenized table. Splitting the route is therefore a response to a documented tokenizer boundary, not a relevance tweak based on guesswork.
Rebuild the legacy contentless indexes
The original posts_fts and posts_fts_ja migrations created contentless tables with content='', but their update triggers attempted ordinary DELETE statements. That did not match SQLite's documented deletion contract for a plain contentless table. The original index documents also omitted the English columns.
migrations/0010_rebuild_localized_fts.sql removes the legacy triggers and recreates both virtual tables as ordinary FTS5 tables that retain their indexed text. Canonical, Japanese, and English values of each field type are combined into the same indexed column, while posts.rowid remains the shared key. INSERT, UPDATE, and DELETE triggers remove old rows by rowid and insert only published posts. The legacy posts_fts_ja name remains for application compatibility; its indexed content is now bilingual.
Preserve results when the preferred path is unavailable
If the primary query fails or returns no rows, the implementation tries the older posts_fts prefix query and then LIKE across all localized fields. When no D1 binding exists, the file-backed path performs an in-memory substring search over the same fields.
Fallback is not silent. Bounded log identifiers such as posts_fts_bigram_failed, posts_fts_ja_failed, and posts_fts_fallback_failed make a missing migration or query failure observable without exposing article data. A one-character LIKE query may require a scan, so it is kept as a narrow fallback and the D1 query is capped at 30 results.
Keep post writes and index writes together
The ordinary FTS5 tables stay synchronized through database triggers. Bigram documents are generated in TypeScript, so the post write, old-index removal, and current-index insertion are submitted in one D1Database.batch. Cloudflare documents that batch statements run sequentially and that a failed statement aborts or rolls back the sequence.
There is an additional concurrency guard. If an admin conditional UPDATE loses its optimistic-lock race and changes zero rows, the attempted article text must not replace the valid bigram document. Both bigram statements therefore require the newly saved updated_at value. Backup restore calls the same index builder instead of maintaining a second copy of the generation logic.
Verification and limits
The behavior suite covers localized LIKE search, English terms in the trigram table, removal of an old term after update, cleanup after post deletion, fallback when the bigram table is unavailable, and preservation of the old index after a rejected stale update. Local migrations and the existing-post backfill are separate explicit commands.
This remains substring-oriented site search, not semantic retrieval. FTS5 rank is not a complete model of reader intent. Symbol-heavy queries and one-code-point terms can reach LIKE, and index size and fallback frequency should be measured again as the corpus grows.
