Public site search is a separate feature from the web search used to collect sources for AI-assisted drafting.
This blog searches only published posts stored in D1 and selects FTS5 trigram, an application-generated bigram index, or localized LIKE according to query shape.
This article summarizes the primary routes, fallback order, index synchronization, and the boundary between local and remote operations.
The implementation and official references were checked on July 23, 2026.
Search routing that sends one-, two-, and longer-code-point queries to LIKE, bigram, and trigram paths
*Diagram: select one primary route from the normalized query and fall through only after an empty result or failure. Results from multiple routes are not merged.*
Separate site search from web research
/search reads titles, excerpts, bodies, and tags stored in the D1 posts table.
It does not call SearXNG or the external page-extraction service.
Public search therefore remains independent of an external search provider, while source collection for AI drafts stays in the admin workflow.
The searchable fields include title_ja, excerpt_ja, content_ja, title_en, excerpt_en, and content_en in addition to the canonical columns.
An English title remains searchable even when the canonical title is Japanese.
Select a primary route
searchPosts trims the query and counts code points after NFKC normalization.
It also calls toBigramFtsQuery to determine whether a two-code-point input produces a valid gram.
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 ? with tokenize='trigram'.
SQLite documents that a trigram full-text query shorter than three Unicode characters does not match a substring.
The split route follows that tokenizer boundary rather than forcing short input through trigram FTS.
The trigram index
migrations/0010_rebuild_localized_fts.sql rebuilds the legacy contentless indexes as ordinary FTS5 tables that retain their indexed text.
Each index document combines canonical, Japanese, and English values of the same field type and stores published posts only.
INSERT, UPDATE, and DELETE triggers synchronize rows through the shared posts.rowid.
The legacy name posts_fts_ja remains for compatibility, but its indexed content is bilingual.
Queries of three or more code points use phrase MATCH and return at most 30 rows ordered by FTS5 rank.
The bigram index
Bigram documents NFKC-normalize and lowercase each field, then generate adjacent code-point pairs inside letter, number, and combining-mark segments.
They do not cross whitespace, punctuation, or field boundaries.
A normal save places one bigram statement immediately after the post write in the same D1Database.batch.
The statement is INSERT OR REPLACE for a published document or DELETE for a draft or empty document.
The statement requires both changes() > 0 from the immediately preceding post write and the expected updated_at row.
A conditional update that loses its optimistic-lock race changes zero rows, so unsaved attempted text cannot change the index.
The timestamp is an additional version check, not a substitute for changes() because competing writes can share a timestamp.
Backup restoration uses a separate bulk builder rather than repeating the normal-save builder.
Normal save, restore, and snapshot backfill share the gram document generated by buildPostBigramDocument.
Fallback order
The implementation does not merge result sets.
It returns the first non-empty result from the selected route 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 over the same fields.
The bigram path skips prefix FTS so a partial prefix match cannot hide the substring fallback.
Failures use bounded identifiers such as posts_fts_bigram_failed, posts_fts_ja_failed, and posts_fts_fallback_failed.
Migration and backfill operations
D1 migrations are forward-only.
An already-applied numbered migration is not edited and replayed by deleting its history row; a correction is added as a new numbered migration.
Apply and verify schema and bigram backfill locally first:
bash
pnpm run d1:migrate:localpnpm run d1:bigram:local
The backfill does not clear the complete index first.
It stages complete snapshots for every post, upserts published documents only while the current rows still match, conditionally removes drafts, empty documents, and orphans, then verifies document content and missing, unexpected, and stale counts.
Remote operations require an explicit target after backup and plan review:
bash
pnpm run d1:migrate:remotepnpm run d1:bigram:remote
Limits
This is substring-oriented site search, not semantic retrieval.
A symbol-heavy two-code-point input can produce no valid gram and therefore use LIKE.
The D1 query limits globally to 30 rows before an optional tag filter is applied in the application, so a matching tagged article outside the global top 30 can be omitted.
