discord-fishbowl/database_audit_migration.sql

-- Discord Fishbowl Database Audit Migration Script
-- This script addresses critical database persistence gaps identified in the audit

-- ============================================================================
-- PHASE 1: CRITICAL DATA LOSS PREVENTION
-- ============================================================================

-- Character State Persistence
CREATE TABLE character_state (
    character_id INTEGER PRIMARY KEY REFERENCES characters(id) ON DELETE CASCADE,
    mood VARCHAR(50) DEFAULT 'neutral',
    energy FLOAT DEFAULT 1.0,
    last_topic VARCHAR(200),
    conversation_count INTEGER DEFAULT 0,
    recent_interactions JSONB DEFAULT '[]'::jsonb,
    last_updated TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT ck_energy_range CHECK (energy >= 0 AND energy <= 2.0),
    CONSTRAINT ck_conversation_count CHECK (conversation_count >= 0)
);

-- Character Knowledge Areas (from enhanced_character.py)
CREATE TABLE character_knowledge_areas (
    id SERIAL PRIMARY KEY,
    character_id INTEGER REFERENCES characters(id) ON DELETE CASCADE,
    topic VARCHAR(100) NOT NULL,
    expertise_level FLOAT DEFAULT 0.0,
    last_updated TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    metadata JSONB DEFAULT '{}'::jsonb,
    
    CONSTRAINT ck_expertise_range CHECK (expertise_level >= 0 AND expertise_level <= 1.0),
    UNIQUE(character_id, topic)
);

-- Character Goals Tracking
CREATE TABLE character_goals (
    id SERIAL PRIMARY KEY,
    character_id INTEGER REFERENCES characters(id) ON DELETE CASCADE,
    goal_id VARCHAR(255) UNIQUE NOT NULL,
    description TEXT NOT NULL,
    priority VARCHAR(20) DEFAULT 'medium',
    timeline VARCHAR(50),
    status VARCHAR(20) DEFAULT 'active',
    progress FLOAT DEFAULT 0.0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP WITH TIME ZONE,
    
    CONSTRAINT ck_progress_range CHECK (progress >= 0 AND progress <= 1.0),
    CONSTRAINT ck_priority_values CHECK (priority IN ('low', 'medium', 'high', 'critical')),
    CONSTRAINT ck_status_values CHECK (status IN ('active', 'paused', 'completed', 'cancelled'))
);

-- Reflection Cycles Tracking
CREATE TABLE character_reflection_cycles (
    id SERIAL PRIMARY KEY,
    character_id INTEGER REFERENCES characters(id) ON DELETE CASCADE,
    cycle_id VARCHAR(255) UNIQUE NOT NULL,
    start_time TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    end_time TIMESTAMP WITH TIME ZONE,
    insights_generated INTEGER DEFAULT 0,
    self_modifications JSONB DEFAULT '{}'::jsonb,
    completed BOOLEAN DEFAULT FALSE,
    reflection_content TEXT,
    
    CONSTRAINT ck_insights_positive CHECK (insights_generated >= 0)
);

-- Vector Store Synchronization (add to existing memories table)
ALTER TABLE memories ADD COLUMN IF NOT EXISTS vector_store_id VARCHAR(255);
ALTER TABLE memories ADD COLUMN IF NOT EXISTS vector_backend VARCHAR(20) DEFAULT 'chromadb';
CREATE INDEX IF NOT EXISTS idx_memories_vector_store ON memories(vector_store_id);
CREATE INDEX IF NOT EXISTS idx_memories_vector_backend ON memories(vector_backend);

-- Conversation Context Persistence
CREATE TABLE conversation_context (
    conversation_id INTEGER PRIMARY KEY REFERENCES conversations(id) ON DELETE CASCADE,
    energy_level FLOAT DEFAULT 1.0,
    current_speaker VARCHAR(100),
    conversation_type VARCHAR(50) DEFAULT 'general',
    emotional_state JSONB DEFAULT '{}'::jsonb,
    topic_history JSONB DEFAULT '[]'::jsonb,
    participant_engagement JSONB DEFAULT '{}'::jsonb,
    last_updated TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT ck_energy_positive CHECK (energy_level >= 0),
    CONSTRAINT ck_conversation_type_values CHECK (conversation_type IN ('general', 'creative', 'analytical', 'emotional', 'philosophical'))
);

-- ============================================================================
-- PHASE 2: ADMINISTRATIVE & ANALYTICS
-- ============================================================================

-- Admin Audit Trail
CREATE TABLE admin_audit_log (
    id SERIAL PRIMARY KEY,
    admin_user VARCHAR(100) NOT NULL,
    session_id VARCHAR(255),
    action_type VARCHAR(50) NOT NULL,
    resource_type VARCHAR(50),
    resource_id VARCHAR(255),
    old_values JSONB,
    new_values JSONB,
    timestamp TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    ip_address INET,
    user_agent TEXT,
    success BOOLEAN NOT NULL DEFAULT TRUE,
    error_message TEXT,
    
    CONSTRAINT ck_action_type CHECK (action_type IN ('create', 'update', 'delete', 'login', 'logout', 'config_change', 'system_action'))
);

-- System Configuration Management
CREATE TABLE system_configuration (
    id SERIAL PRIMARY KEY,
    config_section VARCHAR(100) NOT NULL,
    config_key VARCHAR(200) NOT NULL,
    config_value JSONB NOT NULL,
    config_type VARCHAR(20) DEFAULT 'json',
    created_by VARCHAR(100) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE,
    description TEXT,
    
    UNIQUE(config_section, config_key, is_active) DEFERRABLE,
    CONSTRAINT ck_config_type CHECK (config_type IN ('string', 'number', 'boolean', 'json', 'array'))
);

-- Configuration Change History
CREATE TABLE configuration_history (
    id SERIAL PRIMARY KEY,
    config_id INTEGER REFERENCES system_configuration(id),
    old_value JSONB,
    new_value JSONB,
    changed_by VARCHAR(100) NOT NULL,
    changed_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    change_reason TEXT,
    approved_by VARCHAR(100),
    applied BOOLEAN DEFAULT FALSE
);

-- Performance Metrics Storage
CREATE TABLE performance_metrics (
    id SERIAL PRIMARY KEY,
    metric_name VARCHAR(100) NOT NULL,
    metric_category VARCHAR(50) NOT NULL,
    metric_value FLOAT NOT NULL,
    metric_unit VARCHAR(20),
    character_id INTEGER REFERENCES characters(id) ON DELETE CASCADE,
    conversation_id INTEGER REFERENCES conversations(id) ON DELETE CASCADE,
    timestamp TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    metadata JSONB DEFAULT '{}'::jsonb,
    
    CONSTRAINT ck_metric_category CHECK (metric_category IN ('response_time', 'llm_usage', 'memory_operations', 'conversation_quality', 'system_health'))
);

-- Conversation Analytics
CREATE TABLE conversation_analytics (
    id SERIAL PRIMARY KEY,
    conversation_id INTEGER REFERENCES conversations(id) ON DELETE CASCADE,
    sentiment_score FLOAT,
    topic_coherence FLOAT,
    engagement_level FLOAT,
    creativity_score FLOAT,
    turn_taking_balance FLOAT,
    topic_transitions INTEGER DEFAULT 0,
    calculated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT ck_score_ranges CHECK (
        sentiment_score IS NULL OR (sentiment_score >= -1 AND sentiment_score <= 1)
        AND topic_coherence IS NULL OR (topic_coherence >= 0 AND topic_coherence <= 1)
        AND engagement_level IS NULL OR (engagement_level >= 0 AND engagement_level <= 1)
        AND creativity_score IS NULL OR (creativity_score >= 0 AND creativity_score <= 1)
        AND turn_taking_balance IS NULL OR (turn_taking_balance >= 0 AND turn_taking_balance <= 1)
    )
);

-- Message Embeddings and Metadata
CREATE TABLE message_embeddings (
    id SERIAL PRIMARY KEY,
    message_id INTEGER REFERENCES messages(id) ON DELETE CASCADE,
    embedding_vector FLOAT[],
    importance_score FLOAT,
    semantic_cluster VARCHAR(100),
    context_window JSONB DEFAULT '{}'::jsonb,
    quality_metrics JSONB DEFAULT '{}'::jsonb,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT ck_importance_range CHECK (importance_score IS NULL OR (importance_score >= 0 AND importance_score <= 1))
);

-- ============================================================================
-- PHASE 3: SECURITY & COMPLIANCE
-- ============================================================================

-- Security Events Logging
CREATE TABLE security_events (
    id SERIAL PRIMARY KEY,
    event_type VARCHAR(50) NOT NULL,
    severity VARCHAR(20) NOT NULL DEFAULT 'info',
    source_ip INET,
    user_context JSONB DEFAULT '{}'::jsonb,
    event_data JSONB DEFAULT '{}'::jsonb,
    timestamp TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    resolved BOOLEAN DEFAULT FALSE,
    resolved_by VARCHAR(100),
    resolved_at TIMESTAMP WITH TIME ZONE,
    
    CONSTRAINT ck_severity_levels CHECK (severity IN ('info', 'warning', 'error', 'critical')),
    CONSTRAINT ck_event_types CHECK (event_type IN ('auth_failure', 'auth_success', 'data_access', 'config_change', 'system_error', 'anomaly_detected'))
);

-- File Operation Audit Trail
CREATE TABLE file_operations_log (
    id SERIAL PRIMARY KEY,
    character_id INTEGER REFERENCES characters(id) ON DELETE CASCADE,
    operation_type VARCHAR(20) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_size INTEGER,
    success BOOLEAN NOT NULL,
    timestamp TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    metadata JSONB DEFAULT '{}'::jsonb,
    
    CONSTRAINT ck_operation_types CHECK (operation_type IN ('read', 'write', 'delete', 'create', 'list', 'search'))
);

-- ============================================================================
-- INDEXES FOR PERFORMANCE
-- ============================================================================

-- Character state indexes
CREATE INDEX idx_character_state_updated ON character_state(last_updated);
CREATE INDEX idx_character_knowledge_topic ON character_knowledge_areas(topic);
CREATE INDEX idx_character_goals_status ON character_goals(status, priority);
CREATE INDEX idx_reflection_cycles_completed ON character_reflection_cycles(completed, start_time);

-- Conversation indexes
CREATE INDEX idx_conversation_context_type ON conversation_context(conversation_type);
CREATE INDEX idx_conversation_context_updated ON conversation_context(last_updated);
CREATE INDEX idx_conversation_analytics_scores ON conversation_analytics(engagement_level, sentiment_score);

-- Admin and security indexes
CREATE INDEX idx_audit_log_timestamp ON admin_audit_log(timestamp);
CREATE INDEX idx_audit_log_action_type ON admin_audit_log(action_type, timestamp);
CREATE INDEX idx_security_events_severity ON security_events(severity, timestamp);
CREATE INDEX idx_security_events_resolved ON security_events(resolved, timestamp);

-- Performance metrics indexes
CREATE INDEX idx_performance_metrics_category ON performance_metrics(metric_category, timestamp);
CREATE INDEX idx_performance_metrics_character ON performance_metrics(character_id, metric_name, timestamp);

-- File operations indexes
CREATE INDEX idx_file_operations_character ON file_operations_log(character_id, timestamp);
CREATE INDEX idx_file_operations_type ON file_operations_log(operation_type, timestamp);

-- ============================================================================
-- TRIGGERS FOR AUTOMATIC UPDATES
-- ============================================================================

-- Update character_state.last_updated on any change
CREATE OR REPLACE FUNCTION update_character_state_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.last_updated = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tr_character_state_updated
    BEFORE UPDATE ON character_state
    FOR EACH ROW
    EXECUTE FUNCTION update_character_state_timestamp();

-- Update character_knowledge_areas.last_updated on change
CREATE TRIGGER tr_knowledge_areas_updated
    BEFORE UPDATE ON character_knowledge_areas
    FOR EACH ROW
    EXECUTE FUNCTION update_character_state_timestamp();

-- Update character_goals.updated_at on change
CREATE OR REPLACE FUNCTION update_character_goals_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    IF NEW.status = 'completed' AND OLD.status != 'completed' THEN
        NEW.completed_at = CURRENT_TIMESTAMP;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tr_character_goals_updated
    BEFORE UPDATE ON character_goals
    FOR EACH ROW
    EXECUTE FUNCTION update_character_goals_timestamp();

-- ============================================================================
-- DATA MIGRATION FUNCTIONS
-- ============================================================================

-- Function to migrate existing character data to new state tables
CREATE OR REPLACE FUNCTION migrate_character_state_data()
RETURNS void AS $$
BEGIN
    -- Insert default state for all existing characters
    INSERT INTO character_state (character_id, mood, energy, conversation_count)
    SELECT id, 'neutral', 1.0, 0
    FROM characters 
    WHERE id NOT IN (SELECT character_id FROM character_state);
    
    RAISE NOTICE 'Migrated % character state records', (SELECT COUNT(*) FROM character_state);
END;
$$ LANGUAGE plpgsql;

-- Function to create default system configuration
CREATE OR REPLACE FUNCTION create_default_system_config()
RETURNS void AS $$
BEGIN
    INSERT INTO system_configuration (config_section, config_key, config_value, created_by, description) VALUES
    ('conversation', 'default_energy_level', '1.0', 'system', 'Default energy level for new conversations'),
    ('conversation', 'max_conversation_length', '50', 'system', 'Maximum number of messages in a conversation'),
    ('character', 'mood_decay_rate', '0.1', 'system', 'Rate at which character mood returns to neutral'),
    ('memory', 'importance_threshold', '0.5', 'system', 'Minimum importance score for memory retention'),
    ('rag', 'similarity_threshold', '0.7', 'system', 'Minimum similarity score for memory retrieval')
    ON CONFLICT (config_section, config_key, is_active) DO NOTHING;
    
    RAISE NOTICE 'Created default system configuration';
END;
$$ LANGUAGE plpgsql;

-- ============================================================================
-- EXECUTE MIGRATION
-- ============================================================================

-- Run the migration functions
SELECT migrate_character_state_data();
SELECT create_default_system_config();

-- Create initial admin audit log entry
INSERT INTO admin_audit_log (admin_user, action_type, resource_type, new_values, success)
VALUES ('system', 'system_action', 'database_migration', '{"migration": "database_audit_gaps", "phase": "initial_migration"}', true);

COMMIT;