Why SQLite FTS5's default tokenizer drops your Japanese substrings (and the one-line fix)

If you're building any kind of personal-memory layer on top of SQLite — Claude Code conversation history, notes app, indexed knowledge base — there's a sharp edge in FTS5 that takes most people by surprise the first time they hit it. The default tokenizer ( unicode61 ) silently drops most Japanese substring queries. The fix is one line of SQL. But the failure mode is invisible enough that you can ship a personal search tool, use it for weeks, and never realize half your content is unreachable. This post walks through: The failure, reproducible in 20 lines of Python The one-line fix ( tokenize='trigram' ) and what it actually does under the hood A two-layer Git + SQLite design that uses this index in production for ~800 Claude Code conversations A separate FTS5 footgun around the - character that breaks time-blocking -style queries A free GitHub sample at the end if you want to run the same approach against your own data The failure, reproducible in 20 lines Spin up a fresh SQLite FTS5 table with the default settings and insert a single multilingual sentence: import sqlite3 conn = sqlite3 . connect ( " :memory: " ) conn . execute ( """ CREATE VIRTUAL TABLE notes USING fts5(content) """ ) conn . execute ( """ INSERT INTO notes(content) VALUES ( ' Tried time-blocking with the new 朝の運用フロー — ' ' the 9-11 slot worked but the 午後 part collapsed again. ' ) """ ) for q in [ " time " , " blocking " , " 朝の運用 " , " 午後 " ]: hits = conn . execute ( " SELECT count(*) FROM notes WHERE content MATCH ? " , ( q ,) ). fetchone ()[ 0 ] print ( f " { q !r} : { hits } hit(s) " ) Output: 'time': 1 hit(s) 'blocking': 1 hit(s) '朝の運用': 0 hit(s) '午後': 0 hit(s) Same row. Same content. English queries land, Japanese substring queries don't. That's not a bug, it's the default tokenizer behavior — and the default doesn't print a warning about it. The reason: unicode61 segments text on whitespace and unicode word-break properties. English words have spaces between them, so individual tokens are reco