xonecas.io
62159
Sy

symb / files

symb / internal / store / store.go

10.0 KB
1// Package store provides a SQLite-backed cache for web fetch and search results.
2package store
3
4import (
5	"database/sql"
6	"fmt"
7	"strings"
8	"sync"
9	"time"
10
11	"github.com/rs/zerolog/log"
12	_ "modernc.org/sqlite" // register sqlite driver
13)
14
15const schema = `
16CREATE TABLE IF NOT EXISTS fetch_cache (
17	url       TEXT PRIMARY KEY,
18	result    TEXT NOT NULL,
19	created   INTEGER NOT NULL
20);
21
22CREATE TABLE IF NOT EXISTS search_cache (
23	query    TEXT PRIMARY KEY,
24	result   TEXT NOT NULL,
25	created  INTEGER NOT NULL
26);
27
28CREATE INDEX IF NOT EXISTS idx_fetch_created ON fetch_cache(created);
29CREATE INDEX IF NOT EXISTS idx_search_created ON search_cache(created);
30
31CREATE TABLE IF NOT EXISTS sessions (
32	id      TEXT PRIMARY KEY,
33	title   TEXT NOT NULL DEFAULT '',
34	created INTEGER NOT NULL,
35	updated INTEGER NOT NULL
36);
37
38CREATE TABLE IF NOT EXISTS messages (
39	id          INTEGER PRIMARY KEY AUTOINCREMENT,
40	session_id  TEXT NOT NULL REFERENCES sessions(id),
41	role        TEXT NOT NULL,
42	content     TEXT NOT NULL DEFAULT '',
43	reasoning   TEXT NOT NULL DEFAULT '',
44	tool_calls  TEXT NOT NULL DEFAULT '[]',
45	tool_call_id TEXT NOT NULL DEFAULT '',
46	created     INTEGER NOT NULL
47);
48
49CREATE INDEX IF NOT EXISTS idx_messages_session ON messages(session_id, id);
50
51CREATE TABLE IF NOT EXISTS file_deltas (
52	id          INTEGER PRIMARY KEY AUTOINCREMENT,
53	session_id  TEXT NOT NULL,
54	turn_id     INTEGER NOT NULL,
55	file_path   TEXT NOT NULL,
56	op          TEXT NOT NULL,
57	old_content BLOB,
58	created     INTEGER NOT NULL
59);
60
61CREATE INDEX IF NOT EXISTS idx_deltas_turn ON file_deltas(session_id, turn_id);
62`
63
64// Cache is a SQLite-backed cache for web results and session storage.
65type Cache struct {
66	mu  sync.Mutex
67	db  *sql.DB
68	ttl time.Duration
69}
70
71// Open creates or opens a cache database at the given path.
72// ttl controls how long entries remain fresh.
73func Open(dbPath string, ttl time.Duration) (*Cache, error) {
74	db, err := sql.Open("sqlite", dbPath)
75	if err != nil {
76		return nil, fmt.Errorf("open cache db: %w", err)
77	}
78
79	// SQLite pragmas for performance.
80	for _, pragma := range []string{
81		"PRAGMA journal_mode = WAL",
82		"PRAGMA synchronous = NORMAL",
83		"PRAGMA busy_timeout = 5000",
84		"PRAGMA foreign_keys = ON",
85	} {
86		if _, err := db.Exec(pragma); err != nil {
87			db.Close()
88			return nil, fmt.Errorf("pragma %q: %w", pragma, err)
89		}
90	}
91
92	// Migrate: drop old search_cache with keywords column and recreate.
93	// This is a cache, so losing data is acceptable.
94	if hasColumn(db, "search_cache", "keywords") {
95		if _, err := db.Exec("DROP TABLE search_cache"); err != nil {
96			log.Warn().Err(err).Msg("failed to drop search_cache for migration")
97		}
98	}
99
100	if _, err := db.Exec(schema); err != nil {
101		db.Close()
102		return nil, fmt.Errorf("create schema: %w", err)
103	}
104
105	// Migrate: add token count columns to messages table.
106	if !hasColumn(db, "messages", "input_tokens") {
107		if _, err := db.Exec("ALTER TABLE messages ADD COLUMN input_tokens INTEGER NOT NULL DEFAULT 0"); err != nil {
108			db.Close()
109			return nil, fmt.Errorf("add messages.input_tokens: %w", err)
110		}
111		if _, err := db.Exec("ALTER TABLE messages ADD COLUMN output_tokens INTEGER NOT NULL DEFAULT 0"); err != nil {
112			db.Close()
113			return nil, fmt.Errorf("add messages.output_tokens: %w", err)
114		}
115	}
116
117	c := &Cache{
118		db:  db,
119		ttl: ttl,
120	}
121	c.purgeStale()
122	return c, nil
123}
124
125// DB returns the underlying *sql.DB for shared access (e.g. delta tracker).
126func (c *Cache) DB() *sql.DB {
127	if c == nil {
128		return nil
129	}
130	return c.db
131}
132
133// Close closes the database.
134func (c *Cache) Close() error {
135	if c == nil {
136		return nil
137	}
138	return c.db.Close()
139}
140
141// --- Fetch cache ---
142
143// GetFetch returns a cached fetch result for the given URL, or "" if miss/stale.
144// Safe to call on a nil receiver (returns miss).
145func (c *Cache) GetFetch(url string) (string, bool) {
146	if c == nil {
147		return "", false
148	}
149	c.mu.Lock()
150	defer c.mu.Unlock()
151
152	cutoff := time.Now().Add(-c.ttl).Unix()
153	var result string
154	err := c.db.QueryRow(
155		"SELECT result FROM fetch_cache WHERE url = ? AND created > ?",
156		url, cutoff,
157	).Scan(&result)
158	if err != nil {
159		return "", false
160	}
161	return result, true
162}
163
164// SetFetch stores a fetch result. No-op on nil receiver.
165func (c *Cache) SetFetch(url, result string) {
166	if c == nil {
167		return
168	}
169	c.mu.Lock()
170	defer c.mu.Unlock()
171
172	_, err := c.db.Exec(
173		"INSERT OR REPLACE INTO fetch_cache (url, result, created) VALUES (?, ?, ?)",
174		url, result, time.Now().Unix(),
175	)
176	if err != nil {
177		log.Warn().Err(err).Str("url", url).Msg("failed to cache fetch result")
178	}
179}
180
181// --- Search cache ---
182
183// GetSearch returns a cached search result for the exact query, or "" if miss/stale.
184// Safe to call on a nil receiver (returns miss).
185func (c *Cache) GetSearch(query string) (string, bool) {
186	if c == nil {
187		return "", false
188	}
189	c.mu.Lock()
190	defer c.mu.Unlock()
191
192	normalized := normalizeQuery(query)
193	cutoff := time.Now().Add(-c.ttl).Unix()
194	var result string
195	err := c.db.QueryRow(
196		"SELECT result FROM search_cache WHERE query = ? AND created > ?",
197		normalized, cutoff,
198	).Scan(&result)
199	if err != nil {
200		return "", false
201	}
202	return result, true
203}
204
205// SearchCachedContent looks for a cached result whose text content contains
206// enough of the query keywords. This searches the actual cached results, not
207// the original queries — so if the answer already exists in any cached result,
208// we return it instead of making a new API call.
209func (c *Cache) SearchCachedContent(query string) (string, bool) {
210	if c == nil {
211		return "", false
212	}
213	c.mu.Lock()
214	defer c.mu.Unlock()
215
216	queryKw := tokenize(query)
217	if len(queryKw) < 2 {
218		return "", false
219	}
220
221	cutoff := time.Now().Add(-c.ttl).Unix()
222	rows, err := c.db.Query(
223		"SELECT result FROM search_cache WHERE created > ?",
224		cutoff,
225	)
226	if err != nil {
227		return "", false
228	}
229	defer rows.Close()
230
231	var bestResult string
232	var bestScore float64
233	var bestHits int
234
235	for rows.Next() {
236		var result string
237		if err := rows.Scan(&result); err != nil {
238			continue
239		}
240		resultLower := strings.ToLower(result)
241		score, hits := contentOverlap(queryKw, resultLower)
242		if score > bestScore {
243			bestScore = score
244			bestHits = hits
245			bestResult = result
246		}
247	}
248
249	// Require at least 75% of query keywords found in content AND at least 3 hits.
250	if bestScore >= 0.75 && bestHits >= 3 {
251		return bestResult, true
252	}
253	return "", false
254}
255
256// SetSearch stores a search result. No-op on nil receiver.
257func (c *Cache) SetSearch(query, result string) {
258	if c == nil {
259		return
260	}
261	c.mu.Lock()
262	defer c.mu.Unlock()
263
264	normalized := normalizeQuery(query)
265	_, err := c.db.Exec(
266		"INSERT OR REPLACE INTO search_cache (query, result, created) VALUES (?, ?, ?)",
267		normalized, result, time.Now().Unix(),
268	)
269	if err != nil {
270		log.Warn().Err(err).Str("query", query).Msg("failed to cache search result")
271	}
272}
273
274// hasColumn checks if a table has a specific column.
275func hasColumn(db *sql.DB, table, column string) bool {
276	rows, err := db.Query(fmt.Sprintf("PRAGMA table_info(%s)", table)) //nolint:gosec // table name is hardcoded by caller
277	if err != nil {
278		return false
279	}
280	defer rows.Close()
281	for rows.Next() {
282		var cid int
283		var name, typ string
284		var notNull, pk int
285		var dflt sql.NullString
286		if err := rows.Scan(&cid, &name, &typ, &notNull, &dflt, &pk); err != nil {
287			continue
288		}
289		if name == column {
290			return true
291		}
292	}
293	return false
294}
295
296// --- Helpers ---
297
298// purgeStale removes entries older than the TTL.
299func (c *Cache) purgeStale() {
300	cutoff := time.Now().Add(-c.ttl).Unix()
301	for _, table := range []string{"fetch_cache", "search_cache"} {
302		res, err := c.db.Exec(
303			fmt.Sprintf("DELETE FROM %s WHERE created <= ?", table), //nolint:gosec // table name is hardcoded
304			cutoff,
305		)
306		if err != nil {
307			log.Warn().Err(err).Str("table", table).Msg("failed to purge stale cache")
308			continue
309		}
310		if n, _ := res.RowsAffected(); n > 0 {
311			log.Info().Int64("deleted", n).Str("table", table).Msg("purged stale cache entries")
312		}
313	}
314}
315
316// normalizeQuery lowercases and trims a query string.
317func normalizeQuery(q string) string {
318	return strings.ToLower(strings.TrimSpace(q))
319}
320
321// stopWords are common words filtered out during tokenization.
322var stopWords = map[string]bool{
323	"a": true, "an": true, "the": true, "is": true, "are": true,
324	"was": true, "were": true, "be": true, "been": true, "being": true,
325	"have": true, "has": true, "had": true, "do": true, "does": true,
326	"did": true, "will": true, "would": true, "could": true, "should": true,
327	"may": true, "might": true, "shall": true, "can": true,
328	"for": true, "and": true, "but": true, "or": true, "nor": true,
329	"not": true, "so": true, "yet": true, "to": true, "of": true,
330	"in": true, "on": true, "at": true, "by": true, "with": true,
331	"from": true, "as": true, "into": true, "about": true, "between": true,
332	"through": true, "during": true, "before": true, "after": true,
333	"above": true, "below": true, "up": true, "down": true, "out": true,
334	"off": true, "over": true, "under": true, "again": true, "then": true,
335	"once": true, "here": true, "there": true, "when": true, "where": true,
336	"why": true, "how": true, "what": true, "which": true, "who": true,
337	"whom": true, "this": true, "that": true, "these": true, "those": true,
338	"i": true, "me": true, "my": true, "we": true, "our": true,
339	"you": true, "your": true, "he": true, "him": true, "his": true,
340	"she": true, "her": true, "it": true, "its": true, "they": true,
341	"them": true, "their": true,
342}
343
344// tokenize splits a query into lowercase keywords, filtering stop words and short tokens.
345func tokenize(query string) []string {
346	words := strings.Fields(strings.ToLower(strings.TrimSpace(query)))
347	var out []string
348	for _, w := range words {
349		w = strings.Trim(w, ".,;:!?\"'()-[]{}") // strip punctuation
350		if len(w) < 2 || stopWords[w] {
351			continue
352		}
353		out = append(out, w)
354	}
355	return out
356}
357
358// contentOverlap checks how many of the query keywords appear anywhere in the
359// lowercased result text. Returns fraction and count.
360func contentOverlap(queryKw []string, resultLower string) (float64, int) {
361	if len(queryKw) == 0 {
362		return 0, 0
363	}
364	hits := 0
365	for _, kw := range queryKw {
366		if strings.Contains(resultLower, kw) {
367			hits++
368		}
369	}
370	return float64(hits) / float64(len(queryKw)), hits
371}