SQLite Chat Message Storage Implementaion

[SlothfulDreams]

Description

Right now the chat history is manage by react state which is pretty bad, as its not persistent and cannot manage multiple chats at once. The goal here is to integrate better-sqlite3 into the application

---

✅ Tasks

1. Database Setup & Schema

• Add better-sqlite3 dependency (preferred for Electron)
• Create chat.db with tables:
  • conversations
    • id (INTEGER PRIMARY KEY)
    • title (TEXT)
    • created_at (DATETIME)
    • updated_at (DATETIME)
  • messages
    • id (INTEGER PRIMARY KEY)
    • conversation_id (INTEGER, FOREIGN KEY)
    • content (TEXT)
    • is_user (BOOLEAN)
    • timestamp (DATETIME)
    • model_used (TEXT)
• Implement database initialization in main process

---

2. Database Service Layer

• Create src/lib/chat-db.ts with ChatDatabase class
• Implement CRUD operations for conversations and messages
• Add simple migration system for schema changes
• Handle database connection lifecycle

---

3. Chat Interface Integration

• Update ChatInterface to persist messages in SQLite
• Load conversation history on component mount
• Implement conversation management:
  • Create new conversation
  • Load conversation
  • Delete conversation
• Add conversation sidebar for browsing history

---

4. Additional

• Search functionality across chat history
• Export conversations to JSON or text
• Auto-generate conversation titles