simple-web-app

commit ff1226bdec4947327494488f825b5c8e7cb13e54
parent 6e4f4c1284eadf94fdf64dc33a290dcaf701f24d
Author: Silas Brack <silasbrack@gmail.com>
Date:   Sun,  7 Jun 2026 21:03:31 +0200

feat: add porter stemming to FTS5 search

Recreate story_fts with 'porter unicode61' tokenizer so searches
like "running" match "run", "runs", etc.

Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>

Diffstat:
A
traildepot/migrations/main/U103__fts_porter_stemming.sql
 | 
34
++++++++++++++++++++++++++++++++++

1 file changed, 34 insertions(+), 0 deletions(-)
diff --git a/traildepot/migrations/main/U103__fts_porter_stemming.sql b/traildepot/migrations/main/U103__fts_porter_stemming.sql
@@ -0,0 +1,34 @@
+-- Add porter stemming to FTS5 for better search matching
+
+DROP TABLE IF EXISTS story_fts;
+
+CREATE VIRTUAL TABLE story_fts USING fts5(
+  title,
+  text,
+  content='story',
+  content_rowid='id',
+  tokenize='porter unicode61'
+);
+
+-- Repopulate with existing data
+INSERT INTO story_fts(rowid, title, text)
+SELECT id, title, text FROM story;
+
+-- Recreate triggers
+DROP TRIGGER IF EXISTS story_ai;
+DROP TRIGGER IF EXISTS story_au;
+DROP TRIGGER IF EXISTS story_ad;
+
+CREATE TRIGGER story_ai AFTER INSERT ON story BEGIN
+  INSERT INTO story_fts(rowid, title, text)
+  VALUES (new.id, new.title, new.text);
+END;
+
+CREATE TRIGGER story_au AFTER UPDATE ON story BEGIN
+  UPDATE story_fts SET title=new.title, text=new.text
+  WHERE rowid=old.id;
+END;
+
+CREATE TRIGGER story_ad AFTER DELETE ON story BEGIN
+  DELETE FROM story_fts WHERE rowid=old.id;
+END;