SQLite FTS5(contentless)の索引が更新されず、テストは緑のままだった

この記事の内容を3行で

• SQLite の FTS5 を content='' で作ると、元テーブルの UPDATE に追随しない。しかもエラーが出ない
• 索引の健全性チェックを入れていたが、固定の語を引くだけだったので、壊れても永遠に緑だった
• 検算は「何を主張したいか」を先に決めてから、それを否定できる手段を選ぶ

何が起きていたか

このサイトは検索が主機能なのに、直した言葉が検索でヒットしない状態が長く続いていました。

# 「月景観」を「月桂冠」に直した
sqlite> SELECT text_corrected FROM segments WHERE video_id=? AND idx=?;
月桂冠を飲みながら

# 検索すると 0件
$ curl -s "https://.../?q=月桂冠" | grep -c "月桂冠を飲みながら"
0

直っているのに引けない。そして検査は通っている。この2つが同時に起きていました。

原因: contentless FTS5 は追随しない

日本語なので unicode61 トークナイザに素直に入れても切れません。 それでバイグラムに割って投入しています。

CREATE VIRTUAL TABLE seg_fts USING fts5(ts, ty, content='', tokenize='unicode61');

def bigrams(s: str) -> str:
    s = (s or "").replace(" ", "")
    return " ".join(s[i:i+2] for i in range(len(s)-1)) if len(s) > 1 else s

# 「月桂冠を飲む」→ "月桂 桂冠 冠を を飲 飲む"

問題は content=''（contentless）のほうでした。 このテーブルは元のテーブルの UPDATE に追随しません。 segments を直しても索引は古いままで、エラーも警告も出ません。

なぜテストが緑だったか

# ❌ 以前の検査
for w in ["言語", "コンピュータ", "うんちく"]:
    assert fts_query(w), f"{w} が引けない"

この3語は索引を作った最初から入っています。 つまり索引が古くなっても、永遠に緑になります。 緑が出たことと、正しいことは別でした。

その検算が通っても、使う人からの指摘は起こりうるか。 起こりうるなら、その検算は主張を裏づけていない。

直した検算

「最近直した行が、実際に引けること」を見るようにしました。

rows = con.execute('''
    SELECT s.rowid, COALESCE(s.text_corrected, s.text) AS t
      FROM segments s JOIN edit_stage e
        ON e.video_id = s.video_id AND e.idx = s.idx
     ORDER BY e.at DESC LIMIT 300''').fetchall()

bad = [r for r in rows if r["rowid"] not in fts_query(r["t"][:8])]
if bad:
    raise SystemExit(f"🚨 索引が古い: {len(bad)}/300 件が引けない")

この検査は、わざと索引をずらしたDBのコピーで実際に落ちることを確認してから本番に入れました。 落ちない検査は、無いのと同じだと思ったので。

再発防止: 手順に組み込む

# scripts/upload_db.sh
echo "=== Step 0: 検索索引を作り直す ==="
python3 scripts/build_fts.py

echo "=== Step 1: デプロイ前検査 ==="
python3 scripts/preflight_release.py --stamp --strict-e || exit 1

「直したら索引を作り直す」を記憶に頼らせないために、デプロイ手順の先頭に置きました。