Database System - openguard-bot/openguard GitHub Wiki

Database System

AIMod uses a robust PostgreSQL database system with Redis caching for optimal performance. This document covers the complete database architecture, models, operations, and migration processes.

🗄️ Database Architecture

PostgreSQL Primary Database

Connection Configuration:

DATABASE_URL = "postgresql://aimod_user:password@localhost:5432/aimod_bot"

# Connection pooling
async def get_pool():
    return await asyncpg.create_pool(
        dsn=DATABASE_URL,
        min_size=5,
        max_size=20,
        command_timeout=60
    )

Key Features:

  • ACID Compliance: Full transaction support
  • JSON/JSONB Support: Flexible configuration storage
  • Async Operations: Non-blocking database operations
  • Connection Pooling: Efficient resource management
  • Prepared Statements: Optimized query performance

Redis Caching Layer

Cache Configuration:

REDIS_URL = "redis://localhost:6379"

# Cache client
redis_client = redis.from_url(REDIS_URL, decode_responses=True)

# TTL Cache for frequently accessed data
guild_cache = TTLCache(maxsize=100, ttl=300)  # 5 minutes

Caching Strategy:

  • Guild Configuration: 5-minute TTL
  • User Permissions: 1-minute TTL
  • AI Decisions: In-memory cache
  • Session Data: Redis-based sessions

📊 Database Schema

Core Tables

1. Guild Configuration (guild_config)

CREATE TABLE guild_config (
    guild_id BIGINT NOT NULL,
    key VARCHAR(255) NOT NULL,
    value JSONB,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (guild_id, key)
);

Common Configuration Keys:

  • ENABLED: AI moderation enabled/disabled
  • AI_MODEL: Selected AI model
  • RULES_TEXT: Server rules for AI context
  • PREFIX: Command prefix
  • AI_EXCLUDED_CHANNELS: Channels excluded from moderation
  • AI_CHANNEL_RULES: Channel-specific rules

2. User Infractions (user_infractions)

CREATE TABLE user_infractions (
    id SERIAL PRIMARY KEY,
    guild_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
    rule_violated VARCHAR(50),
    action_taken VARCHAR(100),
    reasoning TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

Indexes:

CREATE INDEX idx_user_infractions_guild_user ON user_infractions(guild_id, user_id);
CREATE INDEX idx_user_infractions_timestamp ON user_infractions(timestamp);
CREATE INDEX idx_user_infractions_action ON user_infractions(action_taken);

3. Appeals (appeals)

CREATE TABLE appeals (
    id SERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    guild_id BIGINT NOT NULL,
    infraction_id INTEGER REFERENCES user_infractions(id),
    reason TEXT NOT NULL,
    status VARCHAR(20) DEFAULT 'PENDING',
    admin_response TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

Appeal Statuses:

  • PENDING: Awaiting review
  • APPROVED: Appeal accepted
  • DENIED: Appeal rejected
  • UNDER_REVIEW: Being investigated

4. Global Bans (global_bans)

CREATE TABLE global_bans (
    user_id BIGINT PRIMARY KEY,
    reason TEXT NOT NULL,
    banned_by BIGINT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

5. Moderation Logs (moderation_logs)

CREATE TABLE moderation_logs (
    id SERIAL PRIMARY KEY,
    guild_id BIGINT NOT NULL,
    moderator_id BIGINT,
    target_user_id BIGINT NOT NULL,
    action_type VARCHAR(50) NOT NULL,
    reason TEXT,
    duration_seconds INTEGER,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

Analytics Tables

6. Command Logs (command_logs)

CREATE TABLE command_logs (
    id SERIAL PRIMARY KEY,
    guild_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    command_name VARCHAR(100) NOT NULL,
    timestamp TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

7. Guild Settings (guild_settings)

CREATE TABLE guild_settings (
    guild_id BIGINT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    member_count INTEGER DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

Security Tables

8. Bot Detection Config (botdetect_config)

CREATE TABLE botdetect_config (
    guild_id BIGINT PRIMARY KEY,
    enabled BOOLEAN DEFAULT false,
    keywords JSONB DEFAULT '[]',
    action VARCHAR(50) DEFAULT 'timeout',
    timeout_duration INTEGER DEFAULT 3600,
    log_channel BIGINT,
    whitelist_roles JSONB DEFAULT '[]',
    whitelist_users JSONB DEFAULT '[]',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

9. User Data (user_data)

CREATE TABLE user_data (
    user_id BIGINT PRIMARY KEY,
    username VARCHAR(255),
    discriminator VARCHAR(10),
    avatar_url TEXT,
    first_seen TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    last_seen TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    message_count INTEGER DEFAULT 0,
    infraction_count INTEGER DEFAULT 0
);

API and Integration Tables

10. Guild API Keys (guild_api_keys)

CREATE TABLE guild_api_keys (
    guild_id BIGINT PRIMARY KEY,
    api_provider VARCHAR(100),
    encrypted_api_key TEXT,
    encrypted_github_auth_info TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

11. Blog Posts (blog_posts)

CREATE TABLE blog_posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    author_id BIGINT NOT NULL,
    published BOOLEAN DEFAULT false,
    slug VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

🔧 Database Operations

Connection Management

from database.connection import get_pool, get_connection, get_transaction

# Get connection pool
pool = await get_pool()

# Execute simple query
async with get_connection() as conn:
    result = await conn.fetch("SELECT * FROM guild_config WHERE guild_id = $1", guild_id)

# Execute transaction
async with get_transaction() as conn:
    await conn.execute("INSERT INTO user_infractions (...) VALUES (...)")
    await conn.execute("UPDATE user_data SET infraction_count = infraction_count + 1")

CRUD Operations

Create/Update Operations:

async def set_guild_config(guild_id: int, key: str, value: Any):
    """Set or update guild configuration."""
    async with get_connection() as conn:
        await conn.execute("""
            INSERT INTO guild_config (guild_id, key, value, updated_at)
            VALUES ($1, $2, $3, CURRENT_TIMESTAMP)
            ON CONFLICT (guild_id, key)
            DO UPDATE SET value = $3, updated_at = CURRENT_TIMESTAMP
        """, guild_id, key, json.dumps(value))

Read Operations:

async def get_guild_config(guild_id: int, key: str, default=None):
    """Get guild configuration value."""
    # Check cache first
    cache_key = f"guild_config:{guild_id}:{key}"
    cached_value = await get_cache(cache_key)
    if cached_value is not None:
        return json.loads(cached_value)
    
    # Query database
    async with get_connection() as conn:
        result = await conn.fetchval(
            "SELECT value FROM guild_config WHERE guild_id = $1 AND key = $2",
            guild_id, key
        )
    
    if result is not None:
        value = json.loads(result) if isinstance(result, str) else result
        # Cache the result
        await set_cache(cache_key, json.dumps(value), ttl=300)
        return value
    
    return default

Delete Operations:

async def delete_guild_config(guild_id: int, key: str):
    """Delete guild configuration."""
    async with get_connection() as conn:
        await conn.execute(
            "DELETE FROM guild_config WHERE guild_id = $1 AND key = $2",
            guild_id, key
        )
    
    # Invalidate cache
    cache_key = f"guild_config:{guild_id}:{key}"
    await delete_cache(cache_key)

Advanced Queries

User Infraction History:

async def get_user_infractions(guild_id: int, user_id: int, limit: int = 10):
    """Get user's infraction history."""
    async with get_connection() as conn:
        return await conn.fetch("""
            SELECT id, timestamp, rule_violated, action_taken, reasoning
            FROM user_infractions
            WHERE guild_id = $1 AND user_id = $2
            ORDER BY timestamp DESC
            LIMIT $3
        """, guild_id, user_id, limit)

Moderation Statistics:

async def get_moderation_stats(guild_id: int, days: int = 30):
    """Get moderation statistics for the past N days."""
    async with get_connection() as conn:
        return await conn.fetch("""
            SELECT 
                action_taken,
                COUNT(*) as count,
                DATE_TRUNC('day', timestamp) as date
            FROM user_infractions
            WHERE guild_id = $1 
            AND timestamp >= CURRENT_TIMESTAMP - INTERVAL '%s days'
            GROUP BY action_taken, DATE_TRUNC('day', timestamp)
            ORDER BY date DESC
        """, guild_id, days)

🔄 Migration System

JSON to PostgreSQL Migration

The migration system converts legacy JSON files to PostgreSQL:

async def migrate_guild_config():
    """Migrate guild_config.json to PostgreSQL."""
    if not os.path.exists("wdiscordbot-json-data/guild_config.json"):
        return
    
    with open("wdiscordbot-json-data/guild_config.json", "r") as f:
        data = json.load(f)
    
    async with get_connection() as conn:
        for guild_id_str, config in data.items():
            guild_id = int(guild_id_str)
            for key, value in config.items():
                await conn.execute("""
                    INSERT INTO guild_config (guild_id, key, value)
                    VALUES ($1, $2, $3)
                    ON CONFLICT (guild_id, key) DO NOTHING
                """, guild_id, key, json.dumps(value))

Schema Migrations

Migration Scripts:

# Run all migrations
./migrate_json_to_postgresql.py

# Test migration
./test_postgresql_migration.py

# Setup fresh database
./setup_postgresql.sh

Migration Validation:

async def validate_migration():
    """Validate that migration was successful."""
    # Check record counts
    json_count = len(load_json_data("guild_config.json"))
    
    async with get_connection() as conn:
        db_count = await conn.fetchval(
            "SELECT COUNT(DISTINCT guild_id) FROM guild_config"
        )
    
    assert json_count == db_count, f"Migration failed: {json_count} != {db_count}"

🚀 Performance Optimization

Indexing Strategy

Primary Indexes:

-- Guild-based queries
CREATE INDEX idx_guild_config_guild_id ON guild_config(guild_id);
CREATE INDEX idx_user_infractions_guild_id ON user_infractions(guild_id);

-- User-based queries
CREATE INDEX idx_user_infractions_user_id ON user_infractions(user_id);
CREATE INDEX idx_appeals_user_id ON appeals(user_id);

-- Time-based queries
CREATE INDEX idx_user_infractions_timestamp ON user_infractions(timestamp);
CREATE INDEX idx_command_logs_timestamp ON command_logs(timestamp);

-- Composite indexes for common queries
CREATE INDEX idx_user_infractions_guild_user_time ON user_infractions(guild_id, user_id, timestamp);

Query Optimization

Prepared Statements:

# Pre-compile frequently used queries
GET_GUILD_CONFIG = "SELECT value FROM guild_config WHERE guild_id = $1 AND key = $2"
INSERT_INFRACTION = """
    INSERT INTO user_infractions (guild_id, user_id, timestamp, rule_violated, action_taken, reasoning)
    VALUES ($1, $2, $3, $4, $5, $6)
"""

Connection Pooling:

# Optimal pool configuration
POOL_CONFIG = {
    "min_size": 5,      # Minimum connections
    "max_size": 20,     # Maximum connections
    "max_queries": 50000,  # Queries per connection
    "max_inactive_connection_lifetime": 300,  # 5 minutes
    "command_timeout": 60  # Query timeout
}

Caching Strategy

Multi-Level Caching:

  1. Application Cache: In-memory TTL cache for hot data
  2. Redis Cache: Distributed cache for session data
  3. Database Cache: PostgreSQL query cache

Cache Invalidation:

async def invalidate_guild_cache(guild_id: int):
    """Invalidate all cached data for a guild."""
    patterns = [
        f"guild_config:{guild_id}:*",
        f"guild_settings:{guild_id}",
        f"user_permissions:{guild_id}:*"
    ]
    
    for pattern in patterns:
        keys = await redis_client.keys(pattern)
        if keys:
            await redis_client.delete(*keys)

🔒 Security Considerations

Data Encryption

API Key Encryption:

from cryptography.fernet import Fernet

def encrypt_api_key(api_key: str) -> str:
    """Encrypt API key for storage."""
    key = os.getenv("ENCRYPTION_KEY").encode()
    fernet = Fernet(key)
    return fernet.encrypt(api_key.encode()).decode()

def decrypt_api_key(encrypted_key: str) -> str:
    """Decrypt API key for use."""
    key = os.getenv("ENCRYPTION_KEY").encode()
    fernet = Fernet(key)
    return fernet.decrypt(encrypted_key.encode()).decode()

SQL Injection Prevention

Parameterized Queries:

# SAFE - Uses parameterized query
async def safe_query(guild_id: int, user_id: int):
    async with get_connection() as conn:
        return await conn.fetch(
            "SELECT * FROM user_infractions WHERE guild_id = $1 AND user_id = $2",
            guild_id, user_id
        )

# UNSAFE - String concatenation (never do this)
# query = f"SELECT * FROM user_infractions WHERE guild_id = {guild_id}"

Access Control

Database Permissions:

-- Create restricted user for application
CREATE USER aimod_app WITH PASSWORD 'secure_password';

-- Grant only necessary permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO aimod_app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO aimod_app;

-- Revoke dangerous permissions
REVOKE CREATE ON SCHEMA public FROM aimod_app;
REVOKE DROP ON ALL TABLES IN SCHEMA public FROM aimod_app;

Next: Dashboard System - Web dashboard architecture and API documentation