docs/db_schema.md

Schema Design Explanation

The database schema is designed to support the application's functionality by efficiently managing user data, chat sessions, messages, and model usage statistics. Each model is structured to ensure data integrity and facilitate quick access to relevant information, enabling seamless interactions within the Auto-Analyst application.

Database Schema

The application uses the following database models:

User

The User table is used to store user information, including their username, email, and creation date. It is defined in the init_db.py file at init_db.py#L17.

class User(Base):
    __tablename__ = 'users'
    
    user_id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String, unique=True, nullable=False)
    email = Column(String, unique=True, nullable=False)
    created_at = Column(DateTime, default=datetime.utcnow)

Chat

The Chat table is used to store chat sessions, including the chat ID, user ID, title, and creation date. It is defined in the init_db.py file at init_db.py#L25.

class Chat(Base):
    __tablename__ = 'chats'
    
    chat_id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey('users.user_id'), nullable=True)
    title = Column(String, default='New Chat')
    created_at = Column(DateTime, default=datetime.utcnow)

Message

The Message table is used to store messages, including the message ID, chat ID, sender, content, and timestamp. It is defined in the init_db.py file at init_db.py#L30.

class Message(Base):
    __tablename__ = 'messages'
    
    message_id = Column(Integer, primary_key=True, autoincrement=True)
    chat_id = Column(Integer, ForeignKey('chats.chat_id'), nullable=False)
    sender = Column(String, nullable=False)  # 'user' or 'ai'
    content = Column(Text, nullable=False)
    timestamp = Column(DateTime, default=datetime.utcnow)

ModelUsage

The ModelUsage table is used to store model usage statistics, including the usage ID, user ID, chat ID, model name, provider, prompt tokens, completion tokens, total tokens, query size, response size, cost, timestamp, and streaming status. It is defined in the init_db.py file at init_db.py#L40.

class ModelUsage(Base):
    __tablename__ = 'model_usage'
    
    usage_id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.user_id'), nullable=True)
    chat_id = Column(Integer, ForeignKey('chats.chat_id'), nullable=True)
    model_name = Column(String(100), nullable=False)
    provider = Column(String(50), nullable=False)
    prompt_tokens = Column(Integer, default=0)
    completion_tokens = Column(Integer, default=0)
    total_tokens = Column(Integer, default=0)
    query_size = Column(Integer, default=0)  # Size in characters
    response_size = Column(Integer, default=0)  # Size in characters
    cost = Column(Float, default=0.0)  # Cost in USD
    timestamp = Column(DateTime, default=datetime.utcnow)
    is_streaming = Column(Boolean, default=False)
    request_time_ms = Column(Integer, default=0)  # Request processing time in milliseconds

The models defined in this file are utilized for storing and retrieving data in the database. The database schema is managed using SQLAlchemy, which facilitates the creation and manipulation of these models. For the initialization of the database, refer to the init_db.py file.