006_add_fuzzy_cache_tables.sql RawHistoryFolder

1-- Database consolidation: Move fuzzy cache from binary file to database
2-- Replaces ~/.local/state/dumber/dmenu_fuzzy_cache.bin with proper SQL tables
3-- This enables single-database architecture: dumber.sqlite
4
5-- Metadata table: stores cache statistics and versioning
6CREATE TABLE IF NOT EXISTS fuzzy_cache_metadata (
7    id INTEGER PRIMARY KEY CHECK (id = 1), -- Singleton table: only one row allowed
8    version INTEGER NOT NULL,
9    entry_count INTEGER NOT NULL,
10    last_modified INTEGER NOT NULL, -- Unix timestamp
11    entries_hash TEXT NOT NULL      -- MD5 hash for cache invalidation detection
12);
13
14-- Structures table: stores serialized fuzzy search data structures
15CREATE TABLE IF NOT EXISTS fuzzy_cache_structures (
16    id INTEGER PRIMARY KEY CHECK (id = 1), -- Singleton table: only one row allowed
17    trigram_index BLOB NOT NULL,           -- Serialized trigram index: map[string][]int
18    prefix_trie BLOB NOT NULL,             -- Serialized prefix trie for fast lookups
19    sorted_index BLOB NOT NULL             -- Pre-sorted entries for ranking
20);
21
22-- Note: No indexes needed - singleton tables with single row access pattern