A Japanese two-code-point bigram index on D1 and FTS5
# An implementation record for generating normalized adjacent code-point pairs and safely storing, updating, and backfilling them in an ordinary D1 FTS5 table.
SQLite FTS5's trigram tokenizer is useful for substring search, but its documented full-text behavior does not match substrings shorter than three Unicode characters. To avoid sending every two-code-point query such as Japanese “広告” or “日記”, or the normalized term “AI”, through LIKE '%...%', this blog generates overlapping bigrams in the application and stores them in a separate D1 FTS5 table. This article is limited to gram generation, table design, write consistency, and backfilling existing posts. The implementation was checked on July 22, 2026.
Define the invariants first
The index follows these rules:
• Index canonical, Japanese, and English titles, excerpts, bodies, and tags for published posts.
• Apply NFKC normalization and lowercasing.
• Generate grams only inside runs of letters, numbers, and combining marks.
• Never create a gram across a field boundary, space, or punctuation boundary.
• Slide by one code point and emit each adjacent pair.
• Store a duplicate gram only once per post.
• Never expose drafts, deleted posts, or text from a write 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 routing code, which is why this implementation is described precisely as a two-code-point index.
Generate grams with buildSearchBigrams
buildSearchBigrams in src/lib/search-bigrams.ts extracts segments with the Unicode-property pattern [\p{L}\p{N}\p{M}]+. It then slides over each segment by one code point.
text
日本語 -> 日本 / 本語ＡＩ -> ai (NFKC plus lowercase)広告 枠 -> 広告 (no artificial 告枠 pair across the space)
buildPostBigramDocument processes every field independently and merges the output through a Set. This prevents a false pair from joining the end of a title to the beginning of an excerpt. On the query side, toBigramFtsQuery quotes every generated pair and joins multiple pairs with AND.
Use an ordinary FTS5 table
migrations/0009_posts_fts_bigram.sql creates the dedicated table:
The grams column stores the application-generated pairs separated by spaces. post_id is only an identity value, so it is UNINDEXED, and the FTS rowid is kept equal to posts.rowid.
A contentless table was considered first. A plain contentless FTS5 table does not support ordinary DELETE operations, while contentless-delete support begins with SQLite 3.43 and introduces a runtime-compatibility requirement. This repository prioritizes reproducible update and deletion behavior, so it uses an ordinary FTS5 table that retains the generated gram document. At this corpus size, deterministic cleanup and restoration matter more than saving that copy of the generated text.
Do not let a rejected post write update the index
On a normal save, the post write is followed by the DELETE and INSERT statements returned by buildPostBigramIndexStatements, all in the same D1 batch. A published post with a non-empty gram document is reinserted. A draft produces only a deletion. Backup restore calls the same builder rather than maintaining a second implementation.
The critical fence is updated_at. Admin editing uses optimistic concurrency, so a stale conditional UPDATE can change zero rows. If the index statements were guarded only by post ID, the article write could fail while grams from the unsaved body replaced the valid index. Both bigram statements therefore require a posts row with the newly saved updated_at value. When the conditional write loses, both index statements also affect zero rows and the previous index remains valid.
Database triggers remove a stale bigram row when searchable post fields are changed outside the application write path. They do not invent and insert replacement grams because the canonical generator lives in TypeScript. Normal admin saves and backup restore regenerate in the same batch; a direct external SQL update requires an explicit rebuild afterward.
Backfill existing posts explicitly
The migration creates the table and cleanup triggers but does not generate grams for existing rows. scripts/rebuild-search-bigrams.mjs reads posts, clears the index, inserts generated documents for published rows only, and verifies the final row count.
The default target is local D1. Production is selected only with an explicit --remote flag, and the package scripts keep local and remote migration/backfill commands separate. SQL string literals escape single quotes by doubling them, and drafts are excluded. Separating schema migration from backfill makes the indexed count observable before the public query path is considered ready.
Query routing and verification
Only a normalized query containing exactly two code points and at least one valid generated pair reaches posts_fts_bigram MATCH ?. A missing table or failed query falls back to the existing FTS index and then localized LIKE. One code point uses LIKE; three or more code points use the trigram index. The broader search operations and fallback policy are documented in the companion article.
Regression tests cover Unicode normalization, field boundaries, deduplication, quoted MATCH expressions, draft exclusion, the shared save/restore builder, SQL escaping, update and delete cleanup, and preservation of the previous index after a rejected stale update. The local D1 workflow also applies the migration, backfills published rows, and verifies the indexed count.
Remaining trade-offs
A bigram document can be larger than its source text. FTS5 rank evaluates generated pair tokens; it is not semantic similarity. Symbol-only input and queries that produce no valid pair continue to fallback. A direct SQL update outside the application removes stale grams but cannot regenerate them, so an explicit backfill or a save through the managed application path is required.
