xonecas.io
6119
My

mysis / files

mysis / internal / store / store.go

9.4 KB
1// Package store provides SQLite persistence for conversation history.
2package store
3
4import (
5	"database/sql"
6	"encoding/json"
7	"fmt"
8	"path/filepath"
9	"time"
10
11	_ "github.com/mattn/go-sqlite3"
12	"github.com/rs/zerolog/log"
13	"github.com/xonecas/mysis/internal/config"
14	"github.com/xonecas/mysis/internal/provider"
15)
16
17// Store handles database operations.
18type Store struct {
19	db *sql.DB
20}
21
22// Session represents a conversation session.
23type Session struct {
24	ID           string
25	Name         *string
26	Provider     string
27	Model        string
28	CreatedAt    time.Time
29	LastActiveAt time.Time
30}
31
32// Message represents a stored message.
33type Message struct {
34	ID         int64
35	SessionID  string
36	Role       string
37	Content    string
38	ToolCallID *string
39	ToolCalls  []provider.ToolCall
40	Reasoning  *string
41	CreatedAt  time.Time
42}
43
44// Open opens the database connection and ensures schema exists.
45func Open() (*Store, error) {
46	dataDir, err := config.EnsureDataDir()
47	if err != nil {
48		return nil, fmt.Errorf("ensure data dir: %w", err)
49	}
50
51	dbPath := filepath.Join(dataDir, "mysis.db")
52	db, err := sql.Open("sqlite3", dbPath+"?_journal_mode=WAL&_foreign_keys=1")
53	if err != nil {
54		return nil, fmt.Errorf("open database: %w", err)
55	}
56
57	store := &Store{db: db}
58	if err := store.initSchema(); err != nil {
59		_ = db.Close()
60		return nil, fmt.Errorf("init schema: %w", err)
61	}
62
63	return store, nil
64}
65
66// Close closes the database connection.
67func (s *Store) Close() error {
68	return s.db.Close()
69}
70
71// initSchema creates tables if they don't exist.
72func (s *Store) initSchema() error {
73	// Create tables
74	_, err := s.db.Exec(`
75		CREATE TABLE IF NOT EXISTS sessions (
76			id TEXT PRIMARY KEY,
77			name TEXT UNIQUE,
78			provider TEXT NOT NULL,
79			model TEXT NOT NULL,
80			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
81			last_active_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
82		);
83
84		CREATE TABLE IF NOT EXISTS messages (
85			id INTEGER PRIMARY KEY AUTOINCREMENT,
86			session_id TEXT NOT NULL,
87			role TEXT NOT NULL,
88			content TEXT NOT NULL,
89			tool_call_id TEXT,
90			tool_calls TEXT,
91			reasoning TEXT,
92			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
93			FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE
94		);
95
96		CREATE TABLE IF NOT EXISTS session_credentials (
97			session_id TEXT PRIMARY KEY,
98			username TEXT NOT NULL,
99			password TEXT NOT NULL,
100			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
101			updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
102			FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE
103		);
104
105		CREATE INDEX IF NOT EXISTS idx_messages_session 
106		ON messages(session_id, created_at);
107	`)
108	return err
109}
110
111// CreateSession creates a new session.
112func (s *Store) CreateSession(id, provider, model string, name *string) error {
113	query := `
114		INSERT INTO sessions (id, name, provider, model, created_at, last_active_at)
115		VALUES (?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
116	`
117	_, err := s.db.Exec(query, id, name, provider, model)
118	if err != nil {
119		return fmt.Errorf("create session: %w", err)
120	}
121	return nil
122}
123
124// GetSession retrieves a session by ID.
125func (s *Store) GetSession(id string) (*Session, error) {
126	query := `
127		SELECT id, name, provider, model, created_at, last_active_at
128		FROM sessions
129		WHERE id = ?
130	`
131
132	var sess Session
133	var name sql.NullString
134	err := s.db.QueryRow(query, id).Scan(
135		&sess.ID,
136		&name,
137		&sess.Provider,
138		&sess.Model,
139		&sess.CreatedAt,
140		&sess.LastActiveAt,
141	)
142	if err == sql.ErrNoRows {
143		return nil, nil
144	}
145	if err != nil {
146		return nil, fmt.Errorf("get session: %w", err)
147	}
148
149	if name.Valid {
150		sess.Name = &name.String
151	}
152
153	return &sess, nil
154}
155
156// GetSessionByName retrieves a session by name.
157func (s *Store) GetSessionByName(name string) (*Session, error) {
158	query := `
159		SELECT id, name, provider, model, created_at, last_active_at
160		FROM sessions
161		WHERE name = ?
162	`
163
164	var sess Session
165	var nameVal sql.NullString
166	err := s.db.QueryRow(query, name).Scan(
167		&sess.ID,
168		&nameVal,
169		&sess.Provider,
170		&sess.Model,
171		&sess.CreatedAt,
172		&sess.LastActiveAt,
173	)
174	if err == sql.ErrNoRows {
175		return nil, nil
176	}
177	if err != nil {
178		return nil, fmt.Errorf("get session by name: %w", err)
179	}
180
181	if nameVal.Valid {
182		sess.Name = &nameVal.String
183	}
184
185	return &sess, nil
186}
187
188// ListSessions returns all sessions ordered by most recent.
189func (s *Store) ListSessions(limit int) ([]Session, error) {
190	query := `
191		SELECT id, name, provider, model, created_at, last_active_at
192		FROM sessions
193		ORDER BY last_active_at DESC
194		LIMIT ?
195	`
196
197	rows, err := s.db.Query(query, limit)
198	if err != nil {
199		return nil, fmt.Errorf("list sessions: %w", err)
200	}
201	defer func() { _ = rows.Close() }()
202
203	var sessions []Session
204	for rows.Next() {
205		var sess Session
206		var name sql.NullString
207		if err := rows.Scan(
208			&sess.ID,
209			&name,
210			&sess.Provider,
211			&sess.Model,
212			&sess.CreatedAt,
213			&sess.LastActiveAt,
214		); err != nil {
215			return nil, fmt.Errorf("scan session: %w", err)
216		}
217
218		if name.Valid {
219			sess.Name = &name.String
220		}
221
222		sessions = append(sessions, sess)
223	}
224
225	return sessions, rows.Err()
226}
227
228// TouchSession updates the last_active_at timestamp.
229func (s *Store) TouchSession(id string) error {
230	query := `UPDATE sessions SET last_active_at = CURRENT_TIMESTAMP WHERE id = ?`
231	_, err := s.db.Exec(query, id)
232	return err
233}
234
235// SaveMessage stores a message in the database.
236func (s *Store) SaveMessage(sessionID string, msg provider.Message) error {
237	// Marshal tool calls to JSON if present
238	var toolCallsJSON *string
239	if len(msg.ToolCalls) > 0 {
240		data, err := json.Marshal(msg.ToolCalls)
241		if err != nil {
242			return fmt.Errorf("marshal tool calls: %w", err)
243		}
244		jsonStr := string(data)
245		toolCallsJSON = &jsonStr
246	}
247
248	// Handle optional fields
249	var toolCallID *string
250	if msg.ToolCallID != "" {
251		toolCallID = &msg.ToolCallID
252	}
253
254	// Reasoning: NULL if empty, otherwise the value
255	var reasoning interface{}
256	if msg.Reasoning == "" {
257		reasoning = nil
258	} else {
259		reasoning = msg.Reasoning
260	}
261
262	query := `
263		INSERT INTO messages (session_id, role, content, tool_call_id, tool_calls, reasoning)
264		VALUES (?, ?, ?, ?, ?, ?)
265	`
266	_, err := s.db.Exec(query, sessionID, msg.Role, msg.Content, toolCallID, toolCallsJSON, reasoning)
267	if err != nil {
268		return fmt.Errorf("save message: %w", err)
269	}
270
271	// Touch session to update last_active_at
272	return s.TouchSession(sessionID)
273}
274
275// LoadMessages retrieves all messages for a session.
276func (s *Store) LoadMessages(sessionID string) ([]provider.Message, error) {
277	query := `
278		SELECT role, content, tool_call_id, tool_calls, reasoning, created_at
279		FROM messages
280		WHERE session_id = ?
281		ORDER BY created_at ASC
282	`
283
284	rows, err := s.db.Query(query, sessionID)
285	if err != nil {
286		return nil, fmt.Errorf("load messages: %w", err)
287	}
288	defer func() { _ = rows.Close() }()
289
290	var messages []provider.Message
291	for rows.Next() {
292		var msg provider.Message
293		var toolCallID sql.NullString
294		var toolCallsJSON sql.NullString
295		var reasoning sql.NullString
296		var createdAt string
297
298		if err := rows.Scan(&msg.Role, &msg.Content, &toolCallID, &toolCallsJSON, &reasoning, &createdAt); err != nil {
299			return nil, fmt.Errorf("scan message: %w", err)
300		}
301
302		// Parse timestamp (SQLite CURRENT_TIMESTAMP uses ISO 8601 / RFC3339)
303		if t, err := time.Parse(time.RFC3339, createdAt); err == nil {
304			msg.CreatedAt = t
305		} else {
306			log.Warn().Err(err).Str("timestamp", createdAt).Msg("Failed to parse message timestamp")
307		}
308
309		if toolCallID.Valid {
310			msg.ToolCallID = toolCallID.String
311		}
312
313		if toolCallsJSON.Valid {
314			if err := json.Unmarshal([]byte(toolCallsJSON.String), &msg.ToolCalls); err != nil {
315				return nil, fmt.Errorf("unmarshal tool calls: %w", err)
316			}
317		}
318
319		if reasoning.Valid {
320			msg.Reasoning = reasoning.String
321		}
322
323		messages = append(messages, msg)
324	}
325
326	return messages, rows.Err()
327}
328
329// DeleteSession deletes a session and all its messages.
330func (s *Store) DeleteSession(id string) error {
331	query := `DELETE FROM sessions WHERE id = ?`
332	_, err := s.db.Exec(query, id)
333	return err
334}
335
336// DeleteSessionByName deletes a session by name and all its messages.
337func (s *Store) DeleteSessionByName(name string) error {
338	query := `DELETE FROM sessions WHERE name = ?`
339	result, err := s.db.Exec(query, name)
340	if err != nil {
341		return fmt.Errorf("delete session by name: %w", err)
342	}
343
344	rowsAffected, err := result.RowsAffected()
345	if err != nil {
346		return fmt.Errorf("get rows affected: %w", err)
347	}
348
349	if rowsAffected == 0 {
350		return fmt.Errorf("session '%s' not found", name)
351	}
352
353	return nil
354}
355
356// SaveCredentials stores game credentials for a session.
357func (s *Store) SaveCredentials(sessionID, username, password string) error {
358	query := `
359		INSERT INTO session_credentials (session_id, username, password, created_at, updated_at)
360		VALUES (?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
361		ON CONFLICT(session_id) DO UPDATE SET
362			username = excluded.username,
363			password = excluded.password,
364			updated_at = CURRENT_TIMESTAMP
365	`
366	_, err := s.db.Exec(query, sessionID, username, password)
367	if err != nil {
368		return fmt.Errorf("save credentials: %w", err)
369	}
370	return nil
371}
372
373// GetCredentials retrieves game credentials for a session.
374func (s *Store) GetCredentials(sessionID string) (username, password string, err error) {
375	query := `SELECT username, password FROM session_credentials WHERE session_id = ?`
376	err = s.db.QueryRow(query, sessionID).Scan(&username, &password)
377	if err == sql.ErrNoRows {
378		return "", "", nil
379	}
380	if err != nil {
381		return "", "", fmt.Errorf("get credentials: %w", err)
382	}
383	return username, password, nil
384}