Implement robust SQL database system for streak and user data management

Description

Currently, our streak and user data are managed using a JSON file. To improve scalability, data integrity, and query performance, we need to transition to a robust SQL database system. This will allow for more efficient data management, complex queries, and better data relationships.

Objectives

Implement a SQL database to replace the current JSON-based storage
Improve data integrity and consistency
Enable more complex queries and data analysis
Enhance scalability for larger user bases
Implement proper data relationships and normalization

Tasks

[ ] Choose an appropriate SQL database (e.g., PostgreSQL, SQLite)
[ ] Design the database schema
- [ ] Users table
- [ ] Streaks table
- [ ] Study sessions table
- [ ] Any other necessary tables
[ ] Implement database migrations system
[ ] Create data access layer (DAL) for database operations
[ ] Refactor existing code to use the new database system
[ ] Implement data migration from JSON to SQL database
[ ] Update all related commands and functions to use the new data access methods
[ ] Implement proper error handling and database connection management
[ ] Add database backup and restore functionality
[ ] Update documentation to reflect the new database system

Proposed Schema (Example)

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    discord_id BIGINT UNIQUE NOT NULL,
    username VARCHAR(255) NOT NULL,
    joined_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE streaks (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    current_streak INTEGER NOT NULL DEFAULT 0,
    longest_streak INTEGER NOT NULL DEFAULT 0,
    last_study_date DATE
);

CREATE TABLE study_sessions (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    start_time TIMESTAMP NOT NULL,
    end_time TIMESTAMP,
    duration INTERVAL
);

Additional Notes

Consider implementing database indexing for frequently queried fields
Plan for database backups and disaster recovery
In the future, we might want to implement database sharding for even larger scale

Definition of Done

SQL database is fully implemented and integrated with the bot
All existing functionality works with the new database system
Data migration from JSON to SQL is completed successfully
New database-related unit tests are implemented and passing
Documentation is updated to reflect the new database system
Performance tests show improvement over the previous system
Code review is completed and approved
Backup and restore procedures are documented and tested

branhoff / hooter-the-tutor

Implement robust SQL database system for streak and user data management #12