| name | db-optimizer |
| description | Optimizes SQLite queries, schema design, and FTS5 usage for the Gyst knowledge base. Use when writing database queries, modifying schema, debugging slow searches, or when the user mentions "slow," "performance," "query," "index," or "database." |
| allowed-tools | Read, Grep, Glob, Bash |
| paths | src/store/**/*.ts |
Query Optimization Rules
- Always use prepared statements (db.prepare()) — they're cached and faster than db.query()
- Use EXPLAIN QUERY PLAN to verify indexes are being used
- For FTS5 BM25 queries, always specify column weights:
bm25(entries_fts, 10.0, 5.0, 1.0)
- Limit all SELECT queries. Never return unbounded results. Default LIMIT 100.
- Use covering indexes when possible
FTS5 Specific
- The porter tokenizer handles English stemming but mangles code identifiers
- ALL text must pass through codeTokenize() before FTS5 insertion
- FTS5 special characters that must be escaped in queries: " * ( ) : ^
- Escape function:
query.replace(/[\"*():^]/g, ' ')
- For prefix searches, append *
- Column filters:
title:authentication searches only the title column
Index Strategy
Current indexes:
- idx_entries_type, idx_entries_status, idx_entries_confidence
- idx_entries_error_sig, idx_entry_files_path, idx_entry_tags_tag
Consider adding:
- Composite index on (status, confidence) for filtered recall queries
- Composite index on (type, status) for convention lookups
- Index on sources(entry_id) for join performance
WAL Mode
db.run("PRAGMA journal_mode=WAL");
db.run("PRAGMA busy_timeout=5000");
db.run("PRAGMA synchronous=NORMAL");
db.run("PRAGMA cache_size=-64000");
Performance Targets
- recall() end-to-end: < 500ms
- learn() end-to-end: < 200ms
- FTS5 search over 1000 entries: < 50ms
- Database startup: < 100ms
