09. Database Schema

Database Schema

Complete database schema documentation for UtilsBot+ including table structures, relationships, and data models.

📋 Table of Contents

• Overview
• Core Tables
• Data Models
• Relationships
• Migrations
• Database Operations

---

Overview

UtilsBot+ uses SQLAlchemy 2.0 with async support for database operations. The schema is designed to support:

• User management and permissions
• Game statistics tracking
• API usage monitoring
• Guild (server) configuration
• Audit logging and analytics

Database Support

• Primary: SQLite (development and small deployments)
• Production: PostgreSQL with asyncpg
• Optional: MySQL with aiomysql

---

Core Tables

Users Table

Stores Discord user information and permissions.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    discord_id VARCHAR(20) UNIQUE NOT NULL,
    username VARCHAR(100),
    is_whitelisted BOOLEAN DEFAULT FALSE,
    is_blacklisted BOOLEAN DEFAULT FALSE,
    total_commands_used INTEGER DEFAULT 0,
    first_seen_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_seen_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Key Features:

• Tracks user permissions for beta access
• Command usage statistics
• Automatic timestamp management
• Blacklist support for moderation

Guilds Table

Stores Discord server (guild) information and settings.

CREATE TABLE guilds (
    id INTEGER PRIMARY KEY,
    discord_id VARCHAR(20) UNIQUE NOT NULL,
    name VARCHAR(100),
    owner_id VARCHAR(20),
    member_count INTEGER DEFAULT 0,
    is_blacklisted BOOLEAN DEFAULT FALSE,
    settings JSON DEFAULT '{}',
    joined_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    left_at DATETIME NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Key Features:

• Guild-specific configuration via JSON settings
• Member count tracking
• Join/leave timestamp tracking
• Blacklist support for problematic servers

Game Stats Table

Tracks user performance in interactive games.

CREATE TABLE game_stats (
    id INTEGER PRIMARY KEY,
    user_discord_id VARCHAR(20) NOT NULL,
    game_type VARCHAR(50) NOT NULL,  -- 'wordle', 'trivia', etc.
    games_played INTEGER DEFAULT 0,
    games_won INTEGER DEFAULT 0,
    games_lost INTEGER DEFAULT 0,
    best_score INTEGER DEFAULT 0,
    worst_score INTEGER DEFAULT 0,
    average_score DECIMAL(5,2) DEFAULT 0.0,
    total_playtime_seconds INTEGER DEFAULT 0,
    achievements JSON DEFAULT '[]',
    last_played_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_discord_id, game_type)
);

Key Features:

• Per-game statistics tracking
• Performance metrics (best/worst/average)
• Achievement system support
• Playtime tracking

API Usage Table

Monitors external API usage for rate limiting and analytics.

CREATE TABLE api_usage (
    id INTEGER PRIMARY KEY,
    user_discord_id VARCHAR(20) NOT NULL,
    api_name VARCHAR(50) NOT NULL,  -- 'gemini', 'screenshot', 'ip-api', etc.
    endpoint VARCHAR(200),
    usage_count INTEGER DEFAULT 0,
    success_count INTEGER DEFAULT 0,
    error_count INTEGER DEFAULT 0,
    last_used_at DATETIME,
    daily_usage_count INTEGER DEFAULT 0,
    daily_reset_date DATE DEFAULT CURRENT_DATE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_discord_id, api_name)
);

Key Features:

• Per-user API usage tracking
• Success/error rate monitoring
• Daily usage limits
• Automatic daily reset

Command Logs Table

Audit trail for all command executions.

CREATE TABLE command_logs (
    id INTEGER PRIMARY KEY,
    user_discord_id VARCHAR(20) NOT NULL,
    guild_discord_id VARCHAR(20),
    command_name VARCHAR(100) NOT NULL,
    command_args JSON DEFAULT '{}',
    execution_time_ms INTEGER,
    success BOOLEAN DEFAULT TRUE,
    error_message TEXT NULL,
    ip_address VARCHAR(45),
    user_agent TEXT,
    executed_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Key Features:

• Complete command audit trail
• Performance monitoring
• Error tracking
• Security logging

---

Data Models

SQLAlchemy Models

The database uses SQLAlchemy ORM models located in models/database.py:

from sqlalchemy import Column, Integer, String, Boolean, DateTime, JSON, Text
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql import func

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    
    id = Column(Integer, primary_key=True)
    discord_id = Column(String(20), unique=True, nullable=False)
    username = Column(String(100))
    is_whitelisted = Column(Boolean, default=False)
    is_blacklisted = Column(Boolean, default=False)
    total_commands_used = Column(Integer, default=0)
    first_seen_at = Column(DateTime, default=func.now())
    last_seen_at = Column(DateTime, default=func.now())
    created_at = Column(DateTime, default=func.now())
    updated_at = Column(DateTime, default=func.now(), onupdate=func.now())

class Guild(Base):
    __tablename__ = 'guilds'
    
    id = Column(Integer, primary_key=True)
    discord_id = Column(String(20), unique=True, nullable=False)
    name = Column(String(100))
    owner_id = Column(String(20))
    member_count = Column(Integer, default=0)
    is_blacklisted = Column(Boolean, default=False)
    settings = Column(JSON, default=dict)
    joined_at = Column(DateTime, default=func.now())
    left_at = Column(DateTime, nullable=True)
    created_at = Column(DateTime, default=func.now())
    updated_at = Column(DateTime, default=func.now(), onupdate=func.now())

---

Relationships

Entity Relationship Diagram

┌─────────────┐    ┌─────────────┐    ┌─────────────┐
│    Users    │    │   Guilds    │    │ Game Stats  │
│             │    │             │    │             │
│ discord_id  │    │ discord_id  │    │ user_id     │
│ username    │ ── │ name        │    │ game_type   │
│ whitelisted │    │ owner_id    │ ── │ games_won   │
│ blacklisted │    │ settings    │    │ best_score  │
└─────────────┘    └─────────────┘    └─────────────┘
       │                   │                   │
       │                   │                   │
       └─────────────────┬─┘                   │
                         │                     │
                    ┌─────────────┐    ┌─────────────┐
                    │ API Usage   │    │Command Logs │
                    │             │    │             │
                    │ user_id     │    │ user_id     │
                    │ api_name    │    │ guild_id    │
                    │ usage_count │    │ command     │
                    │ last_used   │    │ success     │
                    └─────────────┘    └─────────────┘

Key Relationships

1. Users ↔ Game Stats: One-to-many (one user can have stats for multiple games)
2. Users ↔ API Usage: One-to-many (one user can use multiple APIs)
3. Users ↔ Command Logs: One-to-many (one user can execute many commands)
4. Guilds ↔ Command Logs: One-to-many (commands can be executed in guilds)

---

Migrations

Database Initialization

Use the migration scripts to set up the database:

# Initialize database with all tables
python migrations/init_db.py

# Populate with sample data (optional)
python migrations/populate_data.py

Migration Scripts

migrations/init_db.py:

import asyncio
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
from models.database import Base, User, Guild, GameStats, APIUsage

async def init_database():
    """Initialize database with all tables."""
    engine = create_async_engine(DATABASE_URL)
    
    async with engine.begin() as conn:
        # Create all tables
        await conn.run_sync(Base.metadata.create_all)
    
    print("✅ Database initialized successfully")

if __name__ == "__main__":
    asyncio.run(init_database())

Schema Updates

For schema changes, create new migration files:

# Example migration for adding new column
python migrations/add_user_preferences.py

---

Database Operations

Common Queries

Get or Create User:

async def get_or_create_user(session: AsyncSession, discord_id: str) -> User:
    """Get existing user or create new one."""
    user = await session.get(User, {'discord_id': discord_id})
    if not user:
        user = User(discord_id=discord_id)
        session.add(user)
        await session.commit()
    return user

Update Game Stats:

async def update_game_stats(session: AsyncSession, user_id: str, 
                          game_type: str, won: bool, score: int):
    """Update user's game statistics."""
    stats = await session.get(GameStats, {'user_discord_id': user_id, 'game_type': game_type})
    
    if not stats:
        stats = GameStats(user_discord_id=user_id, game_type=game_type)
        session.add(stats)
    
    stats.games_played += 1
    if won:
        stats.games_won += 1
    else:
        stats.games_lost += 1
    
    if score > stats.best_score:
        stats.best_score = score
    
    await session.commit()

Track API Usage:

async def track_api_usage(session: AsyncSession, user_id: str, api_name: str):
    """Track API usage for rate limiting."""
    usage = await session.get(APIUsage, {'user_discord_id': user_id, 'api_name': api_name})
    
    if not usage:
        usage = APIUsage(user_discord_id=user_id, api_name=api_name)
        session.add(usage)
    
    usage.usage_count += 1
    usage.last_used_at = datetime.utcnow()
    
    await session.commit()
    return usage

Performance Optimization

Indexes:

-- Performance indexes
CREATE INDEX idx_users_discord_id ON users(discord_id);
CREATE INDEX idx_guilds_discord_id ON guilds(discord_id);
CREATE INDEX idx_game_stats_user_game ON game_stats(user_discord_id, game_type);
CREATE INDEX idx_api_usage_user_api ON api_usage(user_discord_id, api_name);
CREATE INDEX idx_command_logs_user ON command_logs(user_discord_id);
CREATE INDEX idx_command_logs_executed_at ON command_logs(executed_at);

Connection Pooling:

# Async engine with connection pooling
engine = create_async_engine(
    DATABASE_URL,
    pool_size=20,
    max_overflow=0,
    pool_pre_ping=True,
    pool_recycle=3600
)

---

🔗 Related Pages

• Architecture Overview - System design and data flow
• Configuration Guide - Database configuration
• Developer Guide - Working with the database in code
• Deployment Guide - Production database setup

---

📝 What's Next?

• Set up your development environment
• Configure database connection
• Learn about the bot architecture