Initial Database Integration

[joshuaiokua]

Description

This issue focuses on setting up a base database using better-sqlite3 as a lightweight database solution for local development. This setup will allow users who clone the repository to easily run the project with a local SQLite database, without the need for a full database server. This database is intended for use in the feature explorations (e.g. managing conversations, favorited responses, loading pre-made conversations, etc.) and will also lay the groundwork for future migration to a more robust database like PostgreSQL (e.g., CockroachDB) when the project is ready for deployment as a full-fledged demo website.

This work is tied to work done in the below issue:

• 10

Goal

Integrate better-sqlite3 to database interactions in a local environment. The implementation should allow users to run the project seamlessly with a local SQLite database and provide the ability to load pre-made conversations into the system.

Acceptance Criteria

• [ ] Install better-sqlite3 as a dependency.
• [ ] Set up an initial SQLite database schema to manage conversations and favorited responses:
  • Define tables for conversations, responses, and favorites.
    • Example schema:
    • Conversations table:
      • id: INTEGER, PRIMARY KEY
      • title: TEXT
      • created_at: DATETIME
    • Responses table:
      • id: INTEGER, PRIMARY KEY
      • conversation_id: INTEGER, FOREIGN KEY (references Conversations)
      • response_text: TEXT
      • created_at: DATETIME
    • Favorites table:
      • id: INTEGER, PRIMARY KEY
      • response_id: INTEGER, FOREIGN KEY (references Responses)
• [ ] Implement basic CRUD operations for managing conversations and favorited responses:
  • Add logic to create, read, and delete conversations and responses in the SQLite database.
  • Enable favoriting of responses and manage this through the Favorites table.
• [ ] Implement a basic data persistence layer to interact with better-sqlite3 as needed (i.e. for sending and receiving pre-made conversations).
  • Create a module that handles the database connection and SQL queries (e.g., db.js or db.ts).
  • Ensure that the app can load pre-made conversations from a seed file or initial dataset into the SQLite database.
• [ ] Ensure compatibility with the existing frontend.
• [ ] Update the README with instructions for users to run the project locally using SQLite:
  • How to install dependencies and run the project.
  • How to initialize the database (e.g., with an npm script like npm run init-db).
  • Include any sample data or pre-made conversations as part of the setup.