Updated July 23, 2026: The current route sends one code point to localized LIKE, a valid two-code-point gram to the dedicated bigram index, and three or more code points to trigram FTS5.
A two-code-point query that cannot produce a valid gram also uses LIKE.
Japanese text cannot be segmented by spaces alone.
Shipping a morphological analyzer and its dictionary is possible in some systems, but it was not chosen for this blog's Worker bundle and operational constraints.
Instead, the implementation combines D1's SQLite FTS5 trigram tokenizer, an application-generated bigram index, and localized LIKE according to query shape.
Query routing that selects LIKE, FTS5, or overlapping-gram indexes by normalized query shape
*Diagram: select one primary path from the normalized query, then fall through only after an empty result or failure. Result sets are not merged.*
Current trigram schema
The original migrations created posts_fts and posts_fts_ja as contentless tables with content=''.
Their update triggers attempted ordinary DELETE, which did not match SQLite's documented deletion contract for a plain contentless table.
The indexed documents also omitted the English columns.
migrations/0010_rebuild_localized_fts.sql drops the legacy triggers and virtual tables, then recreates ordinary FTS5 tables that retain their indexed text.
The INSERT trigger also indexes only published posts.
The UPDATE trigger deletes the OLD.rowid and inserts the current document only if the updated post remains published.
The DELETE trigger removes OLD.rowid.
Using rowid rather than slug ensures that an old index row is removed even when the slug changes.
Escaping MATCH input
FTS5 MATCH can interpret punctuation in a term such as Next.js as query syntax.
toFtsQuery removes quotes, normalizes whitespace, and wraps the complete query in double quotes.
ts
function toFtsQuery(query: string) { const clean = query.replace(/["']/g, " ").replace(/\s+/g, " ").trim(); if (!clean) return ""; return `"${clean.replace(/"/g, '""')}"`;}
The trigram path filters to published posts and returns at most 30 rows ordered by FTS5 rank.
sql
SELECT posts.*FROM posts_fts_jaJOIN posts ON posts.rowid = posts_fts_ja.rowidWHERE posts.status = 'published' AND posts_fts_ja MATCH ?ORDER BY rankLIMIT 30;
Routing one, two, and three code points
SQLite documents that a trigram full-text query shorter than three Unicode characters does not match rows.
searchPosts therefore trims the query and chooses a route from the NFKC-normalized code-point count and the bigram generation result.
One code point:LIKE '%...%' across ten canonical, Japanese, English, and tag fields.
Two code points with a valid gram:posts_fts_bigram MATCH ?.
Two code points without a valid gram: localized LIKE.
Three or more code points:posts_fts_ja MATCH ?.
Array.from(clean.normalize("NFKC")) determines the route count.
Bigram generation additionally lowercases the normalized input and emits pairs only inside letter, number, and combining-mark segments, so some two-code-point strings produce no valid gram.
Fallback differs by primary path
The implementation does not merge result sets.
It uses the first non-empty result from the selected path and falls through only on zero rows or an exception.
One code point or gramless two-code-point input: begin with localized LIKE.
Bigram two-code-point input: go directly to localized LIKE after an empty or failed bigram query.
Three or more code points: try the older posts_fts prefix query after an empty or failed trigram query, then localized LIKE.
A failed D1 LIKE query: search the bundled canonical JSON across canonical, Japanese, English, and tag fields.
The bigram path deliberately skips prefix FTS so a partial prefix result cannot hide the substring results that the fallback is meant to recover.
Failures are logged with bounded identifiers such as posts_fts_bigram_failed, posts_fts_ja_failed, and posts_fts_fallback_failed.
Synchronization responsibilities
Database triggers synchronize the ordinary trigram table.
Bigram documents are generated in TypeScript, so one guarded bigram statement follows the post write in the same D1Database.batch.
The normal-save builder returns one statement: INSERT OR REPLACE for a published document, or DELETE for a draft or empty document.
Both forms require changes() > 0 from the immediately preceding post write and the expected updated_at row.
A conditional write that loses the optimistic-lock race changes zero rows, so attempted unsaved text cannot update the bigram index.
Backup restore uses a separate bulk builder.
It shares buildPostBigramDocument, not the normal-save index-statement builder.
Forward-only migration workflow
D1 applies unapplied numbered migration files in order.
An already-applied migration file is not edited and replayed; a correction is added as a new numbered migration with its own verification steps.
Validate schema and backfill locally first:
bash
pnpm run d1:migrate:localpnpm run d1:bigram:local
Before remote execution, create an export or admin backup and verify the target database and planned files.
Then use the explicit remote scripts:
bash
pnpm run d1:migrate:remotepnpm run d1:bigram:remote
Deleting a numbered migration history row to force replay is not the normal repair workflow.
Limits
This is substring-oriented site search, not semantic retrieval.
FTS5 rank is not a complete model of reader intent.
The D1 query first limits globally to 30 rows and the application applies a requested tag filter afterward, so a matching tagged article outside the global top 30 can be omitted.
That filter can move into SQL before the corpus grows enough for the ordering to matter.
