SQLite FTS5's trigram tokenizer supports substring search, but its documented full-text behavior does not match queries shorter than three Unicode characters.
To avoid sending every normalized two-code-point query through LIKE '%...%', this blog generates adjacent bigrams in the application and stores them in a dedicated D1 FTS5 table.
This article covers gram generation, table design, the save guard, backup restoration, and the snapshot-preserving backfill.
The implementation and official references were checked on July 23, 2026.
Pipeline from normalized Japanese text through overlapping two-code-point grams to a D1 search index
*Diagram: normalize each field, generate adjacent bigrams, and assemble one index document per post.*
Index invariants
The index covers canonical, Japanese, and English titles, excerpts, bodies, and tags for published posts.
Its generation rules are:
Apply NFKC normalization, then lowercase the result.
Generate grams only inside runs of letters, numbers, and combining marks.
Never cross whitespace, punctuation, or field boundaries.
Slide by one code point and emit each adjacent pair.
Store each repeated gram only once per post.
Never expose drafts, deleted posts, or text from a save that lost its concurrency check.
The unit is a code point returned by Array.from after NFKC normalization, not a grapheme cluster.
A user-perceived character can therefore differ from the unit counted by the route.
buildSearchBigrams
buildSearchBigrams in src/lib/search-bigrams.ts extracts segments with the Unicode-property pattern [\p{L}\p{N}\p{M}]+.
Processing each segment separately prevents artificial pairs across spaces and punctuation.
text
日本語 -> 日本 / 本語ＡＩ -> ai (NFKC plus lowercase)広告 枠 -> 広告 (no artificial 告枠 pair across the space)
buildPostBigramDocument merges grams from separate fields through a Set.
toBigramFtsQuery quotes each generated gram and joins multiple grams with AND.
The public route still sends only queries that normalize to exactly two code points to this index.
For example, the helper can generate two grams from 日本語, but the three-code-point public query uses the trigram path.
Why the table retains content
migrations/0009_posts_fts_bigram.sql creates an ordinary FTS5 table that retains the generated gram document.
post_id is an ownership field and remains UNINDEXED.
The FTS5 rowid is kept equal to posts.rowid.
A plain contentless FTS5 table does not support ordinary DELETE operations.
Contentless-delete tables are available from SQLite 3.43, but introduce another runtime-version requirement.
This implementation prioritizes reproducible update, deletion, and verification SQL over saving the generated text copy.
One guarded statement after a normal save
A normal save places the post write first and the single statement returned by buildPostBigramIndexStatements immediately after it in the same D1Database.batch.
A published post with a non-empty document returns INSERT OR REPLACE.
A draft or empty document returns DELETE.
It does not issue DELETE followed by INSERT for every save.
The published statement has this essential shape:
sql
INSERT OR REPLACE INTO posts_fts_bigram(rowid, grams, post_id)SELECT rowid, ?, idFROM postsWHERE changes() > 0 AND id = ? AND status = 'published' AND updated_at = ?;
changes() reports the rows changed by the immediately preceding post write on the same connection.
A stale conditional UPDATE loses the optimistic-lock race and changes zero rows, so the following index statement also writes nothing.
The updated_at predicate additionally verifies that the changed row is the expected post version.
Timestamp equality alone is insufficient because competing writes can share a timestamp; the changes() > 0 result of the preceding write is the primary guard.
The posts update trigger removes an old bigram row when searchable fields or publication state change.
After an accepted save, the following statement inserts the current document.
After a rejected save, the update trigger did not run and the guarded index statement also stops, preserving the valid existing row.
Backup restore uses a separate bulk builder
Backup restoration replaces a catalog rather than saving one post at a time.
buildBigramRestoreStatements clears the bigram table inside the restore transaction and bulk-inserts published documents in chunks that stay below the binding limit.
Normal save and restore share buildPostBigramDocument, not the same index-statement builder.
This keeps gram generation canonical while allowing single-post saves and catalog restoration to use different write shapes.
The backfill does not clear the whole index
scripts/rebuild-search-bigrams.mjs reads every post, including drafts, and stages the complete searchable snapshot in temporary SQL tables.
It then performs these operations in one generated SQL file:
Delete orphaned or invalid index rows whose published owner no longer exists.
Delete rows for staged drafts or empty documents only when the complete staged snapshot still matches the current post.
INSERT OR REPLACE published documents only when the complete staged snapshot still matches the current post.
Drop the temporary staging tables.
There is no leading table-wide delete.
If a post changes during the backfill, the comparison of status, timestamp, canonical fields, localized fields, and tags no longer matches, so the stale staged row cannot overwrite the current index.
Verification recalculates the expected document for every published post.
It checks document content and separate missing, unexpected, and stale counts rather than comparing only the final row count.
Any nonzero mismatch fails the script.
Query routing and fallback
searchPosts trims the query and counts code points after NFKC normalization.
The primary routes are:
One code point, or two code points that generate no valid gram: localized LIKE.
Exactly two code points with a valid gram: posts_fts_bigram MATCH ?.
Three or more code points: posts_fts_ja MATCH ?.
A failed or empty bigram lookup goes directly to localized LIKE.
It deliberately skips the older prefix FTS path so a partial prefix result cannot hide a true substring match.
Only the three-or-more-code-point trigram path tries the older posts_fts prefix index before localized LIKE.
If the D1 LIKE query also fails, the bundled canonical JSON is searched across the same fields.
Separate local and remote operations
Schema migration and document backfill are separate commands.
The default verification target is local D1.
bash
pnpm run d1:migrate:localpnpm run d1:bigram:local
Remote commands require an explicit target after backup and plan review.
An already-applied numbered migration file is not edited; a correction is added as a new numbered migration.
bash
pnpm run d1:migrate:remotepnpm run d1:bigram:remote
Trade-offs
A bigram document can be larger than its source text.
FTS5 rank evaluates generated pair tokens and does not represent semantic similarity.
Symbol-heavy input and normalized two-code-point queries that leave no valid pair use LIKE.
A direct SQL update outside the managed path can remove stale grams through the trigger but cannot regenerate them, so it requires a managed resave or an explicit snapshot backfill.
