Database Schema

PadawanForge uses a segmented database architecture built on Cloudflare D1 (SQLite) that separates player data into logical, focused tables for better performance, privacy, and maintainability.

Design Principles

Segmented Architecture Benefits

  • Better Performance: Smaller tables with targeted indexes for faster queries
  • Enhanced Privacy: Granular control over data visibility and access
  • Improved Maintainability: Clear separation of concerns and responsibilities
  • Scalability: Optimized data access patterns and reduced query complexity

Key Design Features

  • Privacy by Design: Personal information separated with individual visibility controls
  • Role-Based Access: Granular permissions for administrative functions
  • UUID Privacy: Public identifiers prevent data leakage and protect user identity
  • Foreign Key Integrity: Proper relationships with cascading deletes for data consistency

Core Identity Tables

players (Core Identity)

Purpose: Core authentication and identity information

CREATE TABLE players (
    id TEXT PRIMARY KEY,                    -- OAuth provider ID (Google/Discord sub)
    uuid TEXT UNIQUE NOT NULL,             -- Public UUID for privacy
    email TEXT UNIQUE NOT NULL,
    default_provider TEXT NOT NULL CHECK (default_provider IN ('google', 'discord', 'apple', 'slack')),
    is_active BOOLEAN DEFAULT TRUE,
    last_login TIMESTAMP,
    primary_role_id INTEGER DEFAULT 1,     -- FK to roles table
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (primary_role_id) REFERENCES roles(id)
);

Indexes: uuid, email, default_provider, is_active, primary_role_id, last_login, created_at

Relationships: Referenced by all other player tables via uuid

player_connected_accounts (Multi-Provider Support)

Purpose: Support for multiple OAuth providers per account

CREATE TABLE player_connected_accounts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    player_uuid TEXT NOT NULL,
    provider TEXT NOT NULL,
    provider_user_id TEXT NOT NULL,
    provider_email TEXT,
    provider_username TEXT,
    provider_avatar TEXT,
    is_primary BOOLEAN DEFAULT FALSE,
    connected_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_used TIMESTAMP,
    metadata TEXT, -- JSON for provider-specific data
    UNIQUE(provider, provider_user_id),
    FOREIGN KEY (player_uuid) REFERENCES players(uuid) ON DELETE CASCADE
);

Player Data Tables

player_profiles (Display Information)

Purpose: Public profile information and display settings

CREATE TABLE player_profiles (
    player_uuid TEXT PRIMARY KEY,
    name TEXT,                              -- Real name
    display_name TEXT,                      -- Public display name
    username TEXT,                          -- Unique username
    avatar TEXT,                           -- Avatar URL
    picture TEXT,                          -- OAuth provider picture
    bio TEXT,                              -- Profile bio
    social_links TEXT,                     -- JSON: social media links
    profile_visibility TEXT DEFAULT 'public' CHECK (profile_visibility IN ('public', 'friends', 'private')),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (player_uuid) REFERENCES players(uuid) ON DELETE CASCADE
);

Indexes: username, profile_visibility, updated_at

player_preferences (Settings & Preferences)

Purpose: User settings, UI preferences, and onboarding status

CREATE TABLE player_preferences (
    player_uuid TEXT PRIMARY KEY,
    theme TEXT DEFAULT 'system' CHECK (theme IN ('light', 'dark', 'system')),
    notifications BOOLEAN DEFAULT TRUE,
    language TEXT DEFAULT 'en',
    timezone TEXT,
    privacy_level TEXT DEFAULT 'normal' CHECK (privacy_level IN ('minimal', 'normal', 'maximum')),
    ui_preferences TEXT,                   -- JSON: UI customization settings
    game_preferences TEXT,                 -- JSON: game-specific preferences
    registration_completed BOOLEAN DEFAULT FALSE,
    tutorial_completed BOOLEAN DEFAULT FALSE,
    onboarding_completed_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (player_uuid) REFERENCES players(uuid) ON DELETE CASCADE
);

Indexes: registration_completed, tutorial_completed, updated_at

player_game_data (Progression & Achievements)

Purpose: Game progression, experience, and achievements

CREATE TABLE player_game_data (
    player_uuid TEXT PRIMARY KEY,
    level INTEGER DEFAULT 1,
    experience_points INTEGER DEFAULT 0,
    total_sessions INTEGER DEFAULT 0,
    total_correct_answers INTEGER DEFAULT 0,
    total_questions_answered INTEGER DEFAULT 0,
    best_streak INTEGER DEFAULT 0,
    current_streak INTEGER DEFAULT 0,
    achievements TEXT,                     -- JSON: unlocked achievements
    unlocked_topics TEXT,                  -- JSON: available topics
    skill_levels TEXT,                     -- JSON: topic-specific skill levels
    performance_metrics TEXT,              -- JSON: detailed performance data
    last_session_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (player_uuid) REFERENCES players(uuid) ON DELETE CASCADE
);

Indexes: level, experience_points, total_sessions, last_session_at

player_personal_info (Private Information)

Purpose: Sensitive personal data with privacy controls

CREATE TABLE player_personal_info (
    player_uuid TEXT PRIMARY KEY,
    full_name TEXT,
    date_of_birth DATE,
    location TEXT,
    occupation TEXT,
    education_level TEXT,
    interests TEXT,                        -- JSON: personal interests
    emergency_contact TEXT,                -- JSON: emergency contact info
    accessibility_needs TEXT,              -- JSON: accessibility requirements
    data_sharing_consent BOOLEAN DEFAULT FALSE,
    marketing_consent BOOLEAN DEFAULT FALSE,
    research_consent BOOLEAN DEFAULT FALSE,
    visibility_settings TEXT,              -- JSON: granular privacy controls
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (player_uuid) REFERENCES players(uuid) ON DELETE CASCADE
);

Indexes: data_sharing_consent, marketing_consent, research_consent

Role & Permission Tables

roles (System Roles)

Purpose: Define player roles with game-specific terminology

CREATE TABLE roles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT UNIQUE NOT NULL,             -- guest, padawan, game_master, guild_leader, system_admin
    display_name TEXT NOT NULL,            -- Guest, Padawan, Game Master, Guild Leader, System Admin
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Default Roles & Permissions:

  • Guest (Level 0): Demo system and documentation access only
    • Permissions: content.view, demo.access
  • Padawan (Level 1): Browse-only access to players, NPCs, and games
    • Permissions: players.view, npcs.view, games.view, content.view
  • Game Master (Level 2): Can create/remove NPCs and games
    • Permissions: npcs.view, npcs.create, npcs.delete, games.view, games.create, games.delete, content.view
  • Guild Leader (Level 3): Currently unused role, no additional permissions
  • System Admin (Level 4): Full system configuration access
    • Permissions: admin.config, admin.roles, admin.logs, admin.system, content.view

permissions (Granular Permissions)

Purpose: Define specific system permissions

CREATE TABLE permissions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT UNIQUE NOT NULL,             -- create_npc, moderate_chat, manage_players, etc.
    description TEXT,
    category TEXT,                         -- game, admin, moderation, system
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

role_permissions (Role-Permission Mapping)

Purpose: Map permissions to roles

CREATE TABLE role_permissions (
    role_id INTEGER,
    permission_id INTEGER,
    granted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (role_id, permission_id),
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
);

Game Tables

game_sessions (Multiplayer Rooms & Lobbies)

Purpose: Manage multiplayer chat rooms and game lobbies

CREATE TABLE game_sessions (
    id TEXT PRIMARY KEY,                   -- UUID
    name TEXT NOT NULL,                    -- Room display name
    description TEXT,                      -- Room description
    max_players INTEGER DEFAULT 50,       -- Maximum room capacity
    current_players INTEGER DEFAULT 0,    -- Current player count (auto-updated)
    owner_player_id TEXT NOT NULL,        -- Room creator/owner
    room_type TEXT DEFAULT 'general',     -- general, training, study_group, etc.
    difficulty TEXT DEFAULT 'beginner',   -- beginner, intermediate, advanced
    tags TEXT,                            -- JSON: searchable tags
    room_category TEXT DEFAULT 'general', -- Category for browsing
    is_featured BOOLEAN DEFAULT FALSE,    -- Featured rooms
    is_active BOOLEAN,                    -- Soft delete flag (added in migration 0015)
    auto_delete_when_empty BOOLEAN DEFAULT FALSE, -- Auto-cleanup empty rooms
    last_activity TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Last message/activity
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (owner_player_id) REFERENCES players(uuid) ON DELETE CASCADE
);

Indexes: owner_player_id, room_type, room_category, is_featured, is_active, last_activity, created_at

player_game_sessions (Room Membership)

Purpose: Track which players are in which game sessions/rooms

CREATE TABLE player_game_sessions (
    player_uuid TEXT,
    game_session_id TEXT,
    PRIMARY KEY (player_uuid, game_session_id),
    FOREIGN KEY (player_uuid) REFERENCES players(uuid) ON DELETE CASCADE,
    FOREIGN KEY (game_session_id) REFERENCES game_sessions(id) ON DELETE CASCADE
);

Indexes: player_uuid, game_session_id

game_session_messages (Room Chat)

Purpose: Store chat messages in game sessions/rooms

CREATE TABLE game_session_messages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    game_session_id TEXT NOT NULL,
    sender_player_uuid TEXT NOT NULL,
    message_content TEXT NOT NULL,
    message_type TEXT DEFAULT 'chat' CHECK (message_type IN ('chat', 'system', 'announcement', 'whisper')),
    target_player_uuid TEXT,              -- For whisper messages
    reply_to_message_id INTEGER,          -- For threaded conversations
    is_edited BOOLEAN DEFAULT FALSE,
    edited_at TIMESTAMP,
    is_deleted BOOLEAN DEFAULT FALSE,
    deleted_at TIMESTAMP,
    message_metadata TEXT,                -- JSON: additional message data
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (game_session_id) REFERENCES game_sessions(id) ON DELETE CASCADE,
    FOREIGN KEY (sender_player_uuid) REFERENCES players(uuid) ON DELETE CASCADE,
    FOREIGN KEY (target_player_uuid) REFERENCES players(uuid) ON DELETE SET NULL,
    FOREIGN KEY (reply_to_message_id) REFERENCES game_session_messages(id) ON DELETE SET NULL
);

Indexes: game_session_id, sender_player_uuid, message_type, created_at, target_player_uuid

room_settings (Room Configuration)

Purpose: Store customizable room configurations

CREATE TABLE room_settings (
    room_id TEXT PRIMARY KEY,                         -- Associated room/game_session
    auto_delete_when_empty BOOLEAN DEFAULT TRUE,      -- Auto cleanup empty rooms
    max_idle_time INTEGER DEFAULT 3600,               -- Seconds before room considered idle
    allow_anonymous BOOLEAN DEFAULT TRUE,             -- Allow guest/demo players
    require_approval BOOLEAN DEFAULT FALSE,           -- Require owner approval to join
    moderation_level TEXT DEFAULT 'normal' CHECK (moderation_level IN ('none', 'light', 'normal', 'strict')),
    welcome_message TEXT,                             -- Custom welcome message
    room_rules TEXT,                                  -- Custom room rules
    custom_theme JSON DEFAULT '{}',                   -- Custom styling/theme data
    notification_settings JSON DEFAULT '{}',          -- Notification preferences
    npc_settings JSON DEFAULT '{}',                   -- NPC behavior configuration
    moderation_settings JSON DEFAULT '{}',            -- Moderation and filtering rules
    feature_flags JSON DEFAULT '{}',                  -- Enabled/disabled features
    rate_limits JSON DEFAULT '{}',                    -- Rate limiting configuration
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

room_npcs (NPCs in Rooms)

Purpose: Track which NPCs are active in which rooms with enhanced role management

CREATE TABLE room_npcs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    room_id TEXT NOT NULL,                            -- Game session/room identifier
    npc_id INTEGER NOT NULL,                          -- NPC assigned to this room
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     -- When NPC was added to room
    added_by TEXT,                                    -- Player UUID who added the NPC
    is_active BOOLEAN DEFAULT TRUE,                   -- Whether NPC is currently active
    role TEXT DEFAULT 'assistant' CHECK (role IN ('assistant', 'moderator', 'tutor', 'observer')),
    permissions JSON DEFAULT '{}',                    -- NPC-specific permissions in room
    interaction_count INTEGER DEFAULT 0,              -- Number of interactions in this room
    last_interaction TIMESTAMP,                       -- Last interaction timestamp
    FOREIGN KEY (npc_id) REFERENCES npcs(id) ON DELETE CASCADE,
    FOREIGN KEY (added_by) REFERENCES players(uuid) ON DELETE SET NULL,
    UNIQUE(room_id, npc_id)                          -- Prevent duplicate NPC assignments
);

room_sessions (Enhanced Room Game Sessions)

Purpose: Track active game sessions within rooms with comprehensive state management

CREATE TABLE room_sessions (
    id TEXT PRIMARY KEY,                              -- Unique session identifier
    room_id TEXT NOT NULL,                            -- Associated room/game_session
    session_type TEXT DEFAULT 'lobby' CHECK (session_type IN ('lobby', 'game', 'private', 'training', 'tournament')),
    max_participants INTEGER DEFAULT 50,              -- Maximum players allowed
    current_participants INTEGER DEFAULT 0,           -- Current player count
    session_state JSON DEFAULT '{}',                  -- Current game state data
    session_config JSON DEFAULT '{}',                 -- Session configuration settings
    started_at TIMESTAMP,                             -- When session became active
    ended_at TIMESTAMP,                               -- When session completed
    started_by TEXT,                                  -- Player UUID who started session
    ended_by TEXT,                                    -- Player UUID who ended session
    ready_players TEXT DEFAULT '[]',                  -- JSON array of ready player UUIDs
    spectators TEXT DEFAULT '[]',                     -- JSON array of spectator UUIDs
    is_active BOOLEAN DEFAULT TRUE,                   -- Whether session is currently active
    is_paused BOOLEAN DEFAULT FALSE,                  -- Whether session is paused
    pause_reason TEXT,                                -- Reason for pause if applicable
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (started_by) REFERENCES players(uuid) ON DELETE SET NULL,
    FOREIGN KEY (ended_by) REFERENCES players(uuid) ON DELETE SET NULL
);

statements (Question Pool)

Purpose: Store AI-generated and curated true/false statements

CREATE TABLE statements (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    text TEXT NOT NULL,
    answer BOOLEAN NOT NULL,               -- true or false
    topic TEXT NOT NULL,
    difficulty TEXT NOT NULL CHECK (difficulty IN ('basic', 'intermediate', 'advanced')),
    subtopic TEXT,
    explanation TEXT,                      -- Optional explanation
    source TEXT,                          -- ai_generated, manual, imported
    quality_score REAL DEFAULT 0.5,       -- Quality rating 0.0-1.0
    usage_count INTEGER DEFAULT 0,        -- How many times used
    last_used TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE,
    created_by TEXT,                      -- Creator UUID or 'system'
    validated_by TEXT,                    -- Validator UUID
    validation_status TEXT DEFAULT 'pending' CHECK (validation_status IN ('pending', 'approved', 'rejected')),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Indexes: topic, difficulty, validation_status, is_active, usage_count

AI & Configuration Tables

npcs (AI-Powered Characters)

Purpose: Configure AI-powered non-player characters

CREATE TABLE npcs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    description TEXT,
    system_prompt TEXT NOT NULL,
    personality TEXT,
    avatar TEXT,
    level INTEGER DEFAULT 1,
    interaction_count INTEGER DEFAULT 0,
    enable_memory BOOLEAN DEFAULT TRUE,
    temperature REAL DEFAULT 0.7,
    max_tokens INTEGER DEFAULT 1000,
    created_by TEXT, -- Player UUID who created NPC
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    -- AI provider fields consolidated
    ai_provider TEXT DEFAULT 'local' CHECK (ai_provider IN ('openai', 'anthropic', 'google', 'local')),
    ai_model TEXT DEFAULT '@cf/meta/llama-3.1-8b-instruct',
    ai_provider_config TEXT, -- JSON for provider-specific configuration
    custom_functions TEXT, -- JSON array of custom function definitions
    function_calling_enabled BOOLEAN DEFAULT FALSE,
    -- NPC categorization fields
    category_id INTEGER,
    subcategory TEXT,
    expertise_areas TEXT, -- JSON array of expertise areas
    difficulty_level TEXT DEFAULT 'beginner' CHECK (difficulty_level IN ('beginner', 'intermediate', 'advanced', 'expert')),
    tags TEXT, -- JSON array of searchable tags
    is_featured BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    -- Bot assignment fields
    bot_token TEXT, -- For Discord/Slack bot integration
    bot_config TEXT, -- JSON for bot-specific configuration
    bot_channels TEXT, -- JSON array of allowed channels
    bot_permissions TEXT, -- JSON for bot permission settings
    is_bot_enabled BOOLEAN DEFAULT FALSE,
    -- Response configuration fields
    response_style TEXT DEFAULT 'friendly' CHECK (response_style IN ('formal', 'friendly', 'casual', 'playful')),
    enable_conversation_memory BOOLEAN DEFAULT TRUE,
    enable_personality_responses BOOLEAN DEFAULT TRUE,
    personality_traits TEXT, -- Additional personality traits beyond preset
    -- Advanced Configuration (CoSTAR framework)
    advanced_configuration TEXT, -- JSON: Embedded CoSTAR configuration for sophisticated behavior
    FOREIGN KEY (created_by) REFERENCES players(uuid) ON DELETE SET NULL,
    FOREIGN KEY (category_id) REFERENCES npc_categories(id) ON DELETE SET NULL
);

Key Fields:

  • ai_provider: AI service provider with constraint (‘openai’, ‘anthropic’, ‘google’, ‘local’)
  • ai_model: Specific model identifier (defaults to Cloudflare models)
  • personality: NPC personality description
  • response_style: Communication style (formal, friendly, casual, playful)
  • enable_conversation_memory: Whether to maintain conversation history
  • enable_personality_responses: Whether to use personality traits
  • personality_traits: Additional personality characteristics beyond preset
  • advanced_configuration: JSON field containing CoSTAR-structured configuration
  • category_id: Reference to NPC category for organization
  • level: All new NPCs start at level 1

npc_categories (NPC Organization)

Purpose: Organize NPCs into categories for discovery and management

CREATE TABLE npc_categories (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT UNIQUE NOT NULL,
    display_name TEXT NOT NULL,
    description TEXT,
    icon TEXT, -- Icon identifier or URL
    color_hex TEXT, -- Hex color code for category theming
    sort_order INTEGER DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Default Categories:

  • Science & Technology
  • Mathematics
  • Language & Literature
  • History & Culture
  • Philosophy & Ethics
  • Creative Arts
  • Business & Economics
  • General Education

npc_conversations (Chat History)

Purpose: Store NPC conversation history and context

CREATE TABLE npc_conversations (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    npc_id INTEGER NOT NULL,
    player_uuid TEXT NOT NULL,
    conversation_title TEXT,
    conversation_summary TEXT,
    message_count INTEGER DEFAULT 0,
    total_tokens INTEGER DEFAULT 0,
    conversation_context TEXT, -- JSON for maintaining conversation state
    last_message_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_archived BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(npc_id, player_uuid), -- One active conversation per NPC-player pair
    FOREIGN KEY (npc_id) REFERENCES npcs(id) ON DELETE CASCADE,
    FOREIGN KEY (player_uuid) REFERENCES players(uuid) ON DELETE CASCADE
);

Features:

  • Conversation threading and context management
  • Token usage tracking for cost optimization
  • Conversation summarization for memory efficiency
  • Archival system for old conversations

npc_configurations (Advanced Configuration)

Purpose: Store CoSTAR-structured configuration for advanced NPC behavior

CREATE TABLE npc_configurations (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    npc_id INTEGER NOT NULL,
    name TEXT NOT NULL,
    context TEXT NOT NULL,           -- Domain expertise and background
    objective TEXT NOT NULL,         -- Primary teaching goal
    style TEXT NOT NULL,             -- Communication approach
    tone TEXT NOT NULL,              -- Personality characteristics
    audience TEXT NOT NULL,          -- Target learner profile
    response_settings TEXT NOT NULL, -- JSON for response configuration
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(npc_id), -- One configuration per NPC
    FOREIGN KEY (npc_id) REFERENCES npcs(id) ON DELETE CASCADE
);

CoSTAR Configuration Fields:

  • context: Domain expertise, background knowledge, and specialization areas
  • objective: Primary teaching or interaction goals for the NPC
  • style: Communication methods, teaching approaches, and interaction patterns
  • tone: Personality characteristics, emotional style, and communication preferences
  • audience: Target learner demographics, skill levels, and learning preferences
  • response_settings: JSON configuration for response behavior (encouragement level, correction style, etc.)

Configuration Management:

  • Dual storage approach: embedded in NPC record or separate table
  • CoSTAR framework for structured AI prompt engineering
  • Sophisticated behavior customization beyond basic personality settings
  • Integration with AI service providers for enhanced responses

npc_interactions (Interaction Analytics)

Purpose: Track detailed NPC interaction metrics and performance

CREATE TABLE npc_interactions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    npc_id INTEGER NOT NULL,
    player_uuid TEXT NOT NULL,
    message TEXT NOT NULL,
    response TEXT NOT NULL,
    response_time_ms INTEGER,
    tokens_used INTEGER,
    cost_estimate REAL,
    satisfaction_rating INTEGER, -- 1-5 rating from player
    interaction_context TEXT, -- JSON for conversation context
    error_info TEXT, -- JSON for any errors during interaction
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (npc_id) REFERENCES npcs(id) ON DELETE CASCADE,
    FOREIGN KEY (player_uuid) REFERENCES players(uuid) ON DELETE CASCADE
);

Analytics Features:

  • Response time tracking for performance monitoring
  • Token usage and cost estimation
  • Player satisfaction ratings
  • Error tracking and debugging

npc_knowledge_files (File-Based Knowledge)

Purpose: Store knowledge files for NPCs (R2 bucket integration)

CREATE TABLE npc_knowledge_files (
    id INTEGER PRIMARY KEY,
    npc_id INTEGER NOT NULL,
    file_name TEXT NOT NULL,                          -- Original file name
    file_key TEXT NOT NULL,                           -- R2 storage key
    file_type TEXT NOT NULL,                          -- MIME type
    file_size INTEGER NOT NULL,                       -- File size in bytes
    file_hash TEXT,                                   -- SHA-256 hash for duplicate detection
    description TEXT,                                 -- Optional file description
    upload_status TEXT DEFAULT 'pending' CHECK (upload_status IN ('pending', 'uploading', 'completed', 'failed')),
    processing_status TEXT DEFAULT 'pending' CHECK (processing_status IN ('pending', 'processing', 'completed', 'failed')),
    error_message TEXT,                               -- Error details if processing failed
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (npc_id) REFERENCES npcs(id) ON DELETE CASCADE
);

Features:

  • R2 bucket integration for scalable file storage
  • File deduplication using SHA-256 hashes
  • Upload and processing status tracking
  • Error handling and retry mechanisms

npc_knowledge_data (Structured Knowledge)

Purpose: Store processed knowledge content for NPCs

CREATE TABLE npc_knowledge_data (
    id INTEGER PRIMARY KEY,
    file_id INTEGER NOT NULL,
    content_type TEXT NOT NULL CHECK (content_type IN ('text', 'summary', 'keywords', 'embeddings', 'metadata')),
    content_data TEXT NOT NULL,                       -- JSON or text content
    processing_status TEXT DEFAULT 'pending' CHECK (processing_status IN ('pending', 'processing', 'completed', 'failed')),
    confidence_score REAL DEFAULT 0.0,               -- Content quality/confidence score
    token_count INTEGER DEFAULT 0,                    -- Token count for AI processing
    error_message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (file_id) REFERENCES npc_knowledge_files(id) ON DELETE CASCADE
);

Content Types:

  • text: Raw extracted text content
  • summary: AI-generated content summaries
  • keywords: Extracted key terms and concepts
  • embeddings: Vector embeddings for semantic search
  • metadata: File metadata and properties

statement_pools (Educational Content)

Purpose: Store AI-generated educational statements for NPCs

CREATE TABLE statement_pools (
    id TEXT PRIMARY KEY,                              -- Unique pool identifier
    npc_id INTEGER NOT NULL,                          -- Associated NPC
    topic TEXT NOT NULL,                              -- Statement topic (History, Science, etc.)
    difficulty TEXT NOT NULL CHECK (difficulty IN ('beginner', 'intermediate', 'expert')),
    statements JSON NOT NULL,                         -- Array of statement objects
    generation_context JSON NOT NULL,                 -- AI generation parameters and context
    quality_score REAL DEFAULT 0.0,                  -- Overall pool quality score
    generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP,                             -- Optional expiration for cache management
    usage_count INTEGER DEFAULT 0,                    -- Track how often this pool is used
    is_validated BOOLEAN DEFAULT FALSE,               -- Whether pool has been quality checked
    validation_notes TEXT,                            -- Quality assurance notes
    FOREIGN KEY (npc_id) REFERENCES npcs(id) ON DELETE CASCADE
);

Features:

  • Cached AI-generated educational content
  • Quality scoring and validation system
  • Usage tracking for content optimization
  • Expiration management for content freshness

statement_usage (Usage Analytics)

Purpose: Track how educational statements are used and perform

CREATE TABLE statement_usage (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    statement_pool_id TEXT NOT NULL,                  -- Reference to statement pool
    statement_id TEXT NOT NULL,                       -- Individual statement identifier
    player_uuid TEXT NOT NULL,                        -- Player who answered
    session_id TEXT NOT NULL,                         -- Game session identifier
    correct_answer BOOLEAN NOT NULL,                  -- Whether player answered correctly
    response_time INTEGER NOT NULL,                   -- Response time in milliseconds
    difficulty_perceived TEXT,                        -- Player's perceived difficulty
    feedback_rating INTEGER,                          -- Optional 1-5 rating
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (statement_pool_id) REFERENCES statement_pools(id) ON DELETE CASCADE,
    FOREIGN KEY (player_uuid) REFERENCES players(uuid) ON DELETE CASCADE
);

npc_conversation_memory (Enhanced Memory)

Purpose: Advanced conversation memory and learning system

CREATE TABLE npc_conversation_memory (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    npc_id INTEGER NOT NULL,
    player_uuid TEXT NOT NULL,
    conversation_summary TEXT,                        -- Summarized conversation context
    last_messages TEXT,                               -- JSON array of recent messages
    interaction_count INTEGER DEFAULT 0,
    personality_adjustments JSON DEFAULT '{}',        -- Dynamic personality tweaks
    learned_preferences JSON DEFAULT '{}',            -- Player preferences learned over time
    context_embedding TEXT,                           -- Embedding for semantic search
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(npc_id, player_uuid),
    FOREIGN KEY (npc_id) REFERENCES npcs(id) ON DELETE CASCADE,
    FOREIGN KEY (player_uuid) REFERENCES players(uuid) ON DELETE CASCADE
);

Advanced Features:

  • Conversation summarization for context preservation
  • Dynamic personality adjustments based on interactions
  • Learned player preferences for personalization
  • Semantic embeddings for context retrieval

npc_bot_assignments (External Bot Integration)

Purpose: Link NPCs to external messenger bots (Discord, Slack, etc.)

CREATE TABLE npc_bot_assignments (
    id INTEGER PRIMARY KEY,
    npc_id INTEGER NOT NULL,
    bot_platform TEXT NOT NULL,           -- discord, slack, telegram, etc.
    bot_token TEXT NOT NULL,              -- Encrypted bot token
    bot_name TEXT,                        -- Display name for the bot
    bot_avatar TEXT,                      -- Bot avatar URL
    is_active BOOLEAN DEFAULT 1,
    webhook_url TEXT,                     -- Webhook URL for the platform
    settings TEXT,                        -- JSON: platform-specific settings
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (npc_id) REFERENCES npcs(id) ON DELETE CASCADE
);

Indexes: npc_id, bot_platform, is_active Unique Constraints: (npc_id, bot_platform)

ai_providers (AI Service Configuration)

Purpose: Configure and manage AI service providers

CREATE TABLE ai_providers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT UNIQUE NOT NULL,                        -- Provider name (openai, cloudflare, custom, etc.)
    display_name TEXT NOT NULL,
    provider_type TEXT NOT NULL,                      -- openai, anthropic, google, local, cloudflare, custom
    api_endpoint TEXT,                                -- API endpoint URL
    api_version TEXT,
    supported_models TEXT,                            -- JSON array of supported models
    rate_limits TEXT,                                 -- JSON for rate limiting configuration
    cost_per_token REAL,                              -- Cost per token for billing
    is_active BOOLEAN DEFAULT TRUE,
    configuration_schema TEXT,                        -- JSON schema for provider-specific config
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Features:

  • Multi-provider support with fallback mechanisms
  • Custom provider support - no constraints on provider types
  • Cloudflare AI as default for optimal performance and cost
  • Rate limiting and cost tracking
  • Configuration schema for provider-specific settings

npc_learning_data (Learning Analytics)

Purpose: Track NPC learning and improvement over time

CREATE TABLE npc_learning_data (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    npc_id INTEGER NOT NULL,
    data_type TEXT NOT NULL CHECK (data_type IN ('interaction_pattern', 'response_quality', 'user_feedback', 'performance_metric')),
    data_value TEXT NOT NULL,                         -- JSON data for the learning metric
    confidence_score REAL DEFAULT 0.0,               -- Confidence in the learning data
    source TEXT,                                      -- Source of the learning data
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (npc_id) REFERENCES npcs(id) ON DELETE CASCADE
);

npc_analytics (Performance Metrics)

Purpose: Store comprehensive NPC performance analytics

CREATE TABLE npc_analytics (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    npc_id INTEGER NOT NULL,
    metric_name TEXT NOT NULL,                        -- Metric identifier
    metric_value REAL NOT NULL,                       -- Numeric metric value
    metric_unit TEXT,                                 -- Unit of measurement
    date_recorded DATE NOT NULL,                      -- Date of metric recording
    time_period TEXT DEFAULT 'daily',                 -- Time period for aggregation
    metadata TEXT,                                    -- JSON for additional metric context
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (npc_id) REFERENCES npcs(id) ON DELETE CASCADE
);

Common Metrics:

  • Response time averages
  • User satisfaction scores
  • Token usage efficiency
  • Conversation engagement rates
  • Knowledge base utilization

NPC Preset & Inventory Tables

npc_system_prompts (Preset System Prompts)

Purpose: Store reusable system prompts for common NPC types

CREATE TABLE npc_system_prompts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    description TEXT,
    system_prompt TEXT NOT NULL,
    category TEXT,
    difficulty_level TEXT DEFAULT 'beginner' CHECK (difficulty_level IN ('beginner', 'intermediate', 'advanced', 'expert')),
    tags TEXT,                                        -- JSON array of tags
    usage_count INTEGER DEFAULT 0,
    is_featured BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    created_by TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES players(uuid) ON DELETE SET NULL
);

Default Presets:

  • Friendly Tutor: Patient and encouraging explanations
  • Socratic Mentor: Guides through questions rather than answers
  • Technical Expert: Detailed technical explanations
  • Creative Assistant: Helps with artistic and imaginative projects
  • Character Roleplay: Immersive roleplay experiences

npc_audience_presets (Target Audience Configurations)

Purpose: Store preset audience configurations for different learning levels

CREATE TABLE npc_audience_presets (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    description TEXT,
    audience_config TEXT NOT NULL,                    -- JSON configuration for audience
    category TEXT,
    difficulty_level TEXT DEFAULT 'beginner' CHECK (difficulty_level IN ('beginner', 'intermediate', 'advanced', 'expert')),
    tags TEXT,                                        -- JSON array of tags
    usage_count INTEGER DEFAULT 0,
    is_featured BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    created_by TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES players(uuid) ON DELETE SET NULL
);

Default Audiences:

  • Elementary Students (6-12): Visual learners with short attention spans
  • High School Students (13-18): Mixed learning styles with medium attention spans
  • College Students (18-25): Analytical learners with long attention spans
  • Professionals (25-65): Practical, career-focused learning
  • Hobbyists (all ages): Experiential learning for personal interest

npc_knowledge_presets (Preset Knowledge Files)

Purpose: Store reusable knowledge files for common domains

CREATE TABLE npc_knowledge_presets (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    description TEXT,
    file_key TEXT,                                    -- R2 storage key
    file_url TEXT,                                    -- Direct file URL
    file_name TEXT,
    file_type TEXT,
    file_size INTEGER,
    category TEXT,
    difficulty_level TEXT DEFAULT 'beginner' CHECK (difficulty_level IN ('beginner', 'intermediate', 'advanced', 'expert')),
    tags TEXT,                                        -- JSON array of tags
    usage_count INTEGER DEFAULT 0,
    is_featured BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    created_by TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES players(uuid) ON DELETE SET NULL
);

player_npc_inventory (Player Inventory System)

Purpose: Store players’ reusable NPC configurations and templates

CREATE TABLE player_npc_inventory (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    player_uuid TEXT NOT NULL,
    name TEXT NOT NULL,
    description TEXT,
    system_prompt TEXT,
    audience TEXT,                                    -- Target audience configuration
    file_key TEXT,                                    -- R2 storage key for knowledge files
    file_url TEXT,                                    -- Direct file URL
    file_name TEXT,
    file_type TEXT,
    file_size INTEGER,
    is_public BOOLEAN DEFAULT FALSE,                  -- Whether other players can use this
    usage_count INTEGER DEFAULT 0,
    last_used_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (player_uuid) REFERENCES players(uuid) ON DELETE CASCADE
);

npc_preset_usage (Usage Tracking)

Purpose: Track how presets are used for analytics and recommendations

CREATE TABLE npc_preset_usage (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    preset_type TEXT NOT NULL CHECK (preset_type IN ('system_prompt', 'audience', 'knowledge', 'inventory')),
    preset_id INTEGER NOT NULL,
    npc_id INTEGER,                                   -- NPC that used this preset
    player_uuid TEXT NOT NULL,                        -- Player who used the preset
    used_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (npc_id) REFERENCES npcs(id) ON DELETE SET NULL,
    FOREIGN KEY (player_uuid) REFERENCES players(uuid) ON DELETE CASCADE
);

Enhanced Communication Tables

chat_messages (Enhanced Chat System)

Purpose: Store lobby/room chat messages with advanced features

CREATE TABLE chat_messages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    lobby_id TEXT NOT NULL,                           -- Lobby/room identifier
    player_uuid TEXT,                                 -- Sender (null for system messages)
    npc_id INTEGER,                                   -- NPC sender (if applicable)
    message TEXT NOT NULL,                            -- Message content
    message_type TEXT DEFAULT 'text' CHECK (message_type IN ('text', 'system', 'action', 'npc', 'command', 'announcement')),
    parent_message_id INTEGER,                        -- For threaded conversations
    thread_depth INTEGER DEFAULT 0,                   -- Thread nesting level
    attachments JSON DEFAULT '[]',                    -- File attachments
    reactions JSON DEFAULT '{}',                      -- Message reactions (emoji counts)
    mentions JSON DEFAULT '[]',                       -- Player mentions in message
    is_edited BOOLEAN DEFAULT FALSE,                  -- Edit status  
    edited_at TIMESTAMP,                              -- Edit timestamp
    edit_count INTEGER DEFAULT 0,                     -- Number of edits
    is_deleted BOOLEAN DEFAULT FALSE,                 -- Soft delete flag
    deleted_at TIMESTAMP,                             -- Deletion timestamp
    deleted_by TEXT,                                  -- Who deleted the message
    is_pinned BOOLEAN DEFAULT FALSE,                  -- Message pinning status
    pinned_by TEXT,                                   -- Who pinned the message
    metadata JSON DEFAULT '{}',                       -- Additional message metadata
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (player_uuid) REFERENCES players(uuid) ON DELETE SET NULL,
    FOREIGN KEY (npc_id) REFERENCES npcs(id) ON DELETE SET NULL,
    FOREIGN KEY (parent_message_id) REFERENCES chat_messages(id) ON DELETE SET NULL,
    FOREIGN KEY (deleted_by) REFERENCES players(uuid) ON DELETE SET NULL,
    FOREIGN KEY (pinned_by) REFERENCES players(uuid) ON DELETE SET NULL
);

Advanced Features:

  • Threaded conversations with nesting levels
  • Message reactions and emoji support
  • Player mentions and notifications
  • Message editing with history tracking
  • Soft deletion with audit trails
  • Message pinning for important content
  • NPC and system message support

message_threads (Conversation Threading)

Purpose: Organize threaded conversations and group chats

CREATE TABLE message_threads (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    thread_title TEXT,                                -- Optional thread title
    thread_type TEXT DEFAULT 'conversation' CHECK (thread_type IN ('conversation', 'announcement', 'support', 'group_chat')),
    participants JSON NOT NULL DEFAULT '[]',          -- Array of participant UUIDs
    moderators JSON DEFAULT '[]',                     -- Array of moderator UUIDs
    thread_status TEXT DEFAULT 'active' CHECK (thread_status IN ('active', 'closed', 'archived', 'locked')),
    last_message_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_message_id INTEGER,                          -- Reference to latest message
    message_count INTEGER DEFAULT 0,                  -- Total messages in thread
    participant_count INTEGER DEFAULT 0,              -- Number of participants
    is_archived BOOLEAN DEFAULT FALSE,                -- Archive status
    is_private BOOLEAN DEFAULT FALSE,                 -- Private thread status
    created_by TEXT NOT NULL,                         -- Thread creator UUID
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES players(uuid) ON DELETE CASCADE
);

direct_messages (Enhanced Private Messages)

Purpose: Store private messages between players with advanced features

CREATE TABLE direct_messages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    sender_player_uuid TEXT NOT NULL,
    recipient_player_uuid TEXT NOT NULL,
    subject TEXT,
    content TEXT NOT NULL,
    message_type TEXT DEFAULT 'private' CHECK (message_type IN ('private', 'system', 'broadcast')),
    is_read BOOLEAN DEFAULT FALSE,
    read_at TIMESTAMP,
    parent_message_id INTEGER,                        -- For threaded conversations
    attachments TEXT,                                 -- JSON array of attachment references
    metadata TEXT,                                    -- JSON for additional message data
    is_deleted_by_sender BOOLEAN DEFAULT FALSE,
    is_deleted_by_recipient BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    -- Enhanced fields from v1.2.6
    conversation_id TEXT,                             -- Group conversations by ID
    priority TEXT DEFAULT 'normal' CHECK (priority IN ('low', 'normal', 'high', 'urgent')),
    delivery_status TEXT DEFAULT 'sent' CHECK (delivery_status IN ('pending', 'sent', 'delivered', 'failed')),
    delivery_attempts INTEGER DEFAULT 0,
    expires_at TIMESTAMP,                             -- Message expiration
    is_archived_by_sender BOOLEAN DEFAULT FALSE,
    is_archived_by_recipient BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (parent_message_id) REFERENCES direct_messages(id) ON DELETE SET NULL
);

Enhanced Features:

  • Conversation grouping with IDs
  • Message priority levels
  • Delivery status tracking
  • Message expiration
  • Individual archival by sender/recipient

Indexes: sender_player_uuid, recipient_player_uuid, conversation_id, is_read, created_at, message_type, parent_message_id, priority, delivery_status

Note on Chat Systems

The documentation previously referenced lobbies and lobby_messages tables, but the actual implementation uses the game_sessions system for chat functionality. Chat happens within game sessions, and messages are stored in game_session_messages (defined in migration 0004). This provides a unified system for both gaming and chat features.

notifications (Enhanced User Notifications)

Purpose: Manage user notifications and alerts with advanced features

CREATE TABLE notifications (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    recipient_player_uuid TEXT NOT NULL,
    title TEXT NOT NULL,
    message TEXT NOT NULL,
    notification_type TEXT DEFAULT 'info' CHECK (notification_type IN ('info', 'success', 'warning', 'error', 'achievement')),
    priority TEXT DEFAULT 'normal' CHECK (priority IN ('low', 'normal', 'high', 'urgent')),
    is_read BOOLEAN DEFAULT FALSE,
    read_at TIMESTAMP,
    action_url TEXT,
    action_label TEXT,
    metadata TEXT,                                    -- JSON: additional notification data
    expires_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    -- Enhanced fields from v1.2.6
    category TEXT DEFAULT 'general',                  -- Notification category
    subcategory TEXT,                                 -- Subcategory for filtering
    source TEXT,                                      -- Source system/component
    is_dismissed BOOLEAN DEFAULT FALSE,               -- Whether user dismissed
    dismissed_at TIMESTAMP,                           -- When dismissed
    action_data JSON DEFAULT '{}',                    -- Additional action data
    delivery_method TEXT DEFAULT 'in_app' CHECK (delivery_method IN ('in_app', 'email', 'push', 'sms')),
    delivery_status TEXT DEFAULT 'delivered',         -- Delivery confirmation
    related_entity_type TEXT,                         -- Type of related entity
    related_entity_id TEXT                            -- ID of related entity
);

Enhanced Features:

  • Categorization and subcategorization
  • Source tracking for notification origin
  • Dismissal tracking separate from read status
  • Multiple delivery methods support
  • Entity relationship tracking
  • Enhanced action data storage

Indexes: recipient_player_uuid, is_read, is_dismissed, notification_type, priority, category, source, delivery_method, created_at, expires_at, related_entity_type, related_entity_id

Views and Computed Data

player_complete (Unified Player View)

Purpose: Backwards compatibility view combining all player data while respecting privacy settings

CREATE VIEW player_complete AS
SELECT 
    p.id,
    p.uuid,
    p.email,
    p.default_provider as provider,
    p.is_active,
    p.last_login,
    p.created_at,
    
    -- Profile data (respecting visibility)
    CASE WHEN prof.profile_visibility = 'private' THEN NULL ELSE prof.display_name END as display_name,
    CASE WHEN prof.profile_visibility = 'private' THEN NULL ELSE prof.username END as username,
    CASE WHEN prof.profile_visibility = 'private' THEN NULL ELSE prof.avatar END as avatar,
    CASE WHEN prof.profile_visibility = 'private' THEN NULL ELSE prof.bio END as bio,
    
    -- Game data
    gd.level,
    gd.experience_points,
    gd.total_sessions,
    gd.best_streak,
    
    -- Preferences (non-sensitive)
    pref.theme,
    pref.language,
    pref.tutorial_completed,
    
    -- Role information
    r.name as role_name,
    r.display_name as role_display_name

FROM players p
LEFT JOIN player_profiles prof ON p.uuid = prof.player_uuid
LEFT JOIN player_game_data gd ON p.uuid = gd.player_uuid  
LEFT JOIN player_preferences pref ON p.uuid = pref.player_uuid
LEFT JOIN roles r ON p.primary_role_id = r.id;

Advanced Analytics Views (v1.2.6)

npc_storage_analytics (NPC Storage Utilization)

CREATE VIEW npc_storage_analytics AS
SELECT 
    n.id as npc_id,
    n.name as npc_name,
    COUNT(DISTINCT nkf.id) as knowledge_files_count,
    SUM(nkf.file_size) as total_storage_bytes,
    COUNT(CASE WHEN nkf.processing_status = 'completed' THEN 1 END) as processed_files,
    COUNT(DISTINCT sp.id) as statement_pools_count,
    SUM(sp.usage_count) as total_pool_usage,
    COUNT(su.id) as total_statement_interactions,
    AVG(sp.quality_score) as avg_pool_quality,
    COUNT(DISTINCT ncm.id) as conversation_memories_count
FROM npcs n
LEFT JOIN npc_knowledge_files nkf ON n.id = nkf.npc_id AND nkf.is_active = TRUE
LEFT JOIN statement_pools sp ON n.id = sp.npc_id
LEFT JOIN statement_usage su ON sp.id = su.statement_pool_id
LEFT JOIN npc_conversation_memory ncm ON n.id = ncm.npc_id
GROUP BY n.id, n.name;

statement_pool_performance (Educational Content Analytics)

CREATE VIEW statement_pool_performance AS
SELECT 
    sp.id,
    sp.npc_id,
    sp.topic,
    sp.difficulty,
    sp.quality_score,
    sp.usage_count,
    sp.is_validated,
    COUNT(su.id) as total_interactions,
    AVG(CASE WHEN su.correct_answer THEN 1.0 ELSE 0.0 END) as accuracy_rate,
    AVG(su.response_time) as avg_response_time_ms,
    COUNT(DISTINCT su.player_uuid) as unique_players,
    MIN(su.timestamp) as first_used,
    MAX(su.timestamp) as last_used,
    JULIANDAY('now') - JULIANDAY(sp.generated_at) as age_days
FROM statement_pools sp
LEFT JOIN statement_usage su ON sp.id = su.statement_pool_id
GROUP BY sp.id;

room_activity_analytics (Room Management Analytics)

CREATE VIEW room_activity_analytics AS
SELECT 
    rs.room_id,
    rs.session_type,
    rs.max_participants,
    rs.current_participants,
    rs.is_active,
    COUNT(DISTINCT rn.id) as assigned_npcs,
    COUNT(CASE WHEN rn.is_active THEN 1 END) as active_npcs,
    COUNT(cm.id) as total_messages,
    COUNT(DISTINCT cm.player_uuid) as unique_message_senders,
    AVG(rn.interaction_count) as avg_npc_interactions,
    MAX(cm.timestamp) as last_message_at,
    CASE 
        WHEN rst.auto_delete_when_empty THEN 'auto-cleanup'
        ELSE 'persistent'
    END as cleanup_policy,
    rst.moderation_level,
    rst.allow_anonymous
FROM room_sessions rs
LEFT JOIN room_npcs rn ON rs.room_id = rn.room_id
LEFT JOIN chat_messages cm ON rs.room_id = cm.lobby_id
LEFT JOIN room_settings rst ON rs.room_id = rst.room_id
GROUP BY rs.room_id;

communication_analytics (Messaging Statistics)

CREATE VIEW communication_analytics AS
SELECT 
    DATE(created_at) as activity_date,
    'direct_messages' as message_type,
    COUNT(*) as total_messages,
    COUNT(CASE WHEN is_read = TRUE THEN 1 END) as read_messages,
    COUNT(DISTINCT sender_player_uuid) as unique_senders,
    COUNT(DISTINCT recipient_player_uuid) as unique_recipients,
    AVG(CASE WHEN read_at IS NOT NULL THEN 
        (julianday(read_at) - julianday(created_at)) * 24 * 60 
    END) as avg_read_time_minutes
FROM direct_messages
GROUP BY DATE(created_at)

UNION ALL

SELECT 
    DATE(timestamp) as activity_date,
    'chat_messages' as message_type,
    COUNT(*) as total_messages,
    0 as read_messages,
    COUNT(DISTINCT player_uuid) as unique_senders,
    0 as unique_recipients,
    0 as avg_read_time_minutes
FROM chat_messages
WHERE player_uuid IS NOT NULL
GROUP BY DATE(timestamp);
CREATE VIEW popular_system_prompts AS
SELECT id, name, description, category, difficulty_level, usage_count, is_featured
FROM npc_system_prompts 
WHERE is_active = TRUE
ORDER BY usage_count DESC, is_featured DESC;

Migration History

Current Migration Structure (v1.2.6)

PadawanForge now uses a consolidated migration architecture with 9 logical groups:

  1. 0001_foundation_schema.sql

    • File storage system with upload sessions and chat attachments
    • Platform statements and announcements system
    • Notifications and messaging infrastructure
    • Migration stats tracking table

  2. 0002_player_system.sql (45 indexes, 13 tables)

    • Core player identity and multi-provider OAuth support
    • Segmented player architecture: profiles, preferences, game data, personal info
    • Role-based access control with granular permissions
    • Experience tracking and player relationships
    • Comprehensive player views for backward compatibility

  3. 0003_npc_system.sql (25 indexes, 7 tables)

    • AI-powered NPC system with multiple provider support
    • Conversation history and context management
    • NPC categorization and bot assignment capabilities
    • Learning data and analytics tracking
    • AI provider configuration and management

  4. 0004_game_system.sql (26 indexes, 6 tables)

    • Game session management for multiplayer rooms
    • Player session tracking with roles and permissions
    • Session events and chat message systems
    • Invitation and analytics tracking
    • Real-time activity monitoring

  5. 0005_system_config.sql (20 indexes, 7 tables)

    • User AI settings and system configuration
    • System metrics and logging infrastructure
    • Feature flags and API usage tracking
    • Scheduled tasks and background job management
    • Performance monitoring and health views

  6. 0006_demo_system.sql (11 indexes, 3 tables, 1 view, 1 trigger)

    • Demo sessions for guest/anonymous play
    • Demo session activities and metrics
    • Expired demo sessions view
    • Automatic timestamp update trigger for demo sessions
    • Migration stats tracking

  7. 0007_npc_storage.sql (20 indexes, 4 tables, 3 views, 6 triggers)

    • NPC knowledge files and R2 bucket integration
    • Statement pools for educational content caching
    • Enhanced conversation memory and learning systems
    • Knowledge processing and analytics views
    • Automated timestamp management and usage tracking triggers

  8. 0008_communication_infrastructure.sql (40 indexes, 5 tables, 3 views, 8 triggers)

    • Room NPC management and assignments
    • Enhanced room session tracking with state management
    • Advanced chat messaging with threading and reactions
    • Enhanced direct messaging and notifications systems
    • Communication analytics and monitoring
    • Real-time activity feeds and automated triggers

  9. 0009_inventory_system.sql (25 indexes, 5 tables, 3 views)

    • NPC preset system prompts and audience configurations
    • Player NPC inventory for reusable configurations
    • Knowledge presets and usage tracking
    • Default preset data for common use cases
    • Analytics views for preset popularity and usage

Total Database Structure:

  • 60 Tables across all migration groups
  • 212 Indexes for optimal query performance
  • 18 Triggers for automatic updates and data consistency
  • 9 Views for complex queries and analytics
  • Advanced Features: R2 integration, conversation memory, preset system, and comprehensive analytics

Legacy Migration History (Pre-v1.2.4)

The original 18 migrations were consolidated into the above 5 groups for better maintainability:

  • Eliminated migration conflicts and foreign key issues
  • Improved deployment reliability and rollback capabilities
  • Streamlined database maintenance and monitoring
  • Enhanced performance through strategic index optimization

Best Practices

Data Access Patterns

  1. Use UUIDs: Always reference players by UUID, never by provider ID
  2. Respect Privacy: Check visibility settings before exposing personal data
  3. Efficient Queries: Use appropriate indexes and avoid N+1 queries
  4. Cascading Deletes: Rely on foreign key constraints for data integrity

Performance Optimization

  1. Targeted Indexes: Create indexes for common query patterns
  2. Query Optimization: Use EXPLAIN to analyze query performance
  3. Data Archival: Implement archival strategies for old session data
  4. Connection Pooling: Manage database connections efficiently

Security Considerations

  1. Input Validation: Sanitize all user inputs before database operations
  2. Parameter Binding: Use prepared statements to prevent SQL injection
  3. Access Control: Implement role-based access to sensitive data
  4. Audit Logging: Track all administrative actions and data changes

Maintenance Guidelines

  1. Regular Backups: Implement automated backup strategies
  2. Migration Testing: Test all schema changes in staging environments
  3. Performance Monitoring: Track query performance and optimization opportunities
  4. Data Cleanup: Regular cleanup of expired sessions and temporary data

This segmented database architecture provides PadawanForge with a robust, scalable, and privacy-focused data layer that supports the platform’s gaming, social, and administrative features while maintaining optimal performance and security.

Demo System Migration (0006_demo_system.sql)

The demo system is now fully separated into its own migration file. It includes:

  • demo_sessions: Tracks all anonymous/guest demo sessions and their metadata
  • demo_session_activities: Logs detailed analytics for demo user actions
  • demo_session_metrics: Stores performance and conversion metrics for demo sessions
  • Indexes for efficient querying of demo data
  • expired_demo_sessions view for cleanup and analytics
  • update_demo_sessions_updated_at trigger for timestamp management

This separation improves maintainability and allows the demo system to evolve independently from the core schema.

← Architecture Overview Session Management & RBAC →
PadawanForge v1.4.1