Enable FTS5 Porter stemmer for skill search

[Aaronontheweb]

Problem

The current FTS5 full-text search does not apply stemming, so queries like "closed deals" return zero results even though close-deal exists with description "Mark a Phobos or Support deal as won..."

Tested against live instance with Netclaw.SkillClient:

Query	Results
"closed deals"	None
"close deal"	close-deal
"close"	close-deal
"closing"	Likely none (untested, same root)

Users naturally search with inflected forms ("closed", "closing", "deals") that don't exact-match the indexed tokens.

Proposal

Enable the FTS5 Porter tokenizer on the skills_fts virtual table. This normalizes tokens to their root form, so "closed", "closing", "close" all match.

Change in DatabaseInitializer.cs — the FTS5 table creation:

-- Before
CREATE VIRTUAL TABLE IF NOT EXISTS skills_fts USING fts5(name, description, category);

-- After
CREATE VIRTUAL TABLE IF NOT EXISTS skills_fts USING fts5(name, description, category, tokenize='porter unicode61');

The porter unicode61 tokenizer applies Porter stemming on top of Unicode tokenization. This is a one-line change but requires rebuilding the FTS table (drop + recreate, or a migration).

Impact

This would make skill discovery significantly more natural for both human users and LLM agents searching the feed.

[Aaronontheweb]

Recommended approach: add a lightweight migration system

The skill-server currently uses CREATE TABLE IF NOT EXISTS for schema setup, which can't handle altering existing tables (like changing the FTS5 tokenizer).

Memorizer has a simple migration pattern that would work well here:

1. A schema_version table tracking applied migrations:

CREATE TABLE IF NOT EXISTS schema_version (
    version INT PRIMARY KEY,
    name TEXT NOT NULL,
    applied_at TEXT NOT NULL
);

2. Numbered SQL files in a migrations/ directory:

migrations/
  001_initial_schema.sql
  002_fts5_porter_stemmer.sql

3. A SchemaMigrator that on startup:
   • Creates the schema_version table if missing
   • Reads which migrations have been applied
   • Applies any new ones in order
   • Records each applied migration

For reference, here's Memorizer's implementation: SchemaMigrator.cs

The FTS5 tokenizer change would then be migration 002:

-- 002_fts5_porter_stemmer.sql
DROP TABLE IF EXISTS skills_fts;

CREATE VIRTUAL TABLE skills_fts USING fts5(
    name,
    description,
    category,
    tokenize='porter unicode61'
);

-- Re-populate from existing data
INSERT INTO skills_fts(rowid, name, description, category)
SELECT s.id, s.name, sv.description, COALESCE(sv.category, '')
FROM skills s
JOIN skill_versions sv ON sv.skill_id = s.id AND sv.is_latest = 1;

This also future-proofs the server for any schema changes down the road.

[Aaronontheweb]

This depends on #44 (migration system) being in place first. The FTS5 tokenizer change would then be the first real migration applied via that system.