Migrate AI Chatbot History from in-memory to stored in SQLite Database Table

[Mirasii]

Problem Statement

Currently, the AI chatbot history is stored in-memory. This causes context loss whenever the bot process restarts or crashes. We need to migrate this to a persistent SQLite database to ensure continuity for users.

Acceptance Criteria

1. Database Schema

• A local SQLite database file (e.g., chatbot.db) must be initialized on startup.
• Create a chat_history table with the following schema:
  • id: INTEGER PRIMARY KEY AUTOINCREMENT
  • channel_id: TEXT (Index for fast lookups)
  • role: TEXT (e.g., 'user', 'assistant')
  • content: TEXT
  • created_at: DATETIME DEFAULT CURRENT_TIMESTAMP

2. Functional Requirements

• Persistence: History must survive a full process restart.
• Write Path: Every message exchange (user input and bot response) must be appended to the database.
• Read Path: The bot must retrieve the most recent $X$ messages for a specific channel_id to provide context to the LLM.
• Cleanup: The database should only store up to 50 messages per channel, including responses from the LLM

3. Technical Constraints (TypeScript/Node.js)

• Non-blocking I/O: All database operations must be asynchronous (using async/await) to avoid blocking the Discord.js event loop.
• Type Safety: Interface definitions must be created for the Database rows.
• Connection Pooling: Use a single persistent connection or a managed pool to handle concurrent message writes.

4. Verification & Testing

• Verify that emojis and special characters are stored and retrieved correctly (UTF-8).
• Confirm that context is correctly limited to the last $N$ messages to prevent token limit errors.
• Manual Test: Send messages, restart the bot, and verify the bot still remembers the previous conversation.

Example code already exists for pterodactyl integration using the database service.