supabase_schema_with_logs.sql

-- HR 对话质量评估系统 - Supabase 数据库表结构（含日志）
-- 在 Supabase SQL Editor 中执行此脚本

-- ============================================
-- 1. 对话历史表（已有）
-- ============================================
CREATE TABLE IF NOT EXISTS conversation_history (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    session_id TEXT NOT NULL,
    user_message TEXT NOT NULL,
    assistant_message TEXT NOT NULL,
    analysis_report JSONB,
    context_summary JSONB,
    metadata JSONB,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_conversation_history_session_id ON conversation_history(session_id);
CREATE INDEX IF NOT EXISTS idx_conversation_history_created_at ON conversation_history(created_at DESC);

-- ============================================
-- 2. API 访问日志表（新增）
-- ============================================
CREATE TABLE IF NOT EXISTS api_access_logs (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    timestamp TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    -- 请求信息
    endpoint TEXT NOT NULL,
    method TEXT NOT NULL,
    path TEXT,
    query_params JSONB,

    -- 客户端信息
    client_ip TEXT,
    user_agent TEXT,
    session_id TEXT,

    -- 性能指标
    response_time_ms INTEGER,
    status_code INTEGER,

    -- 附加信息
    metadata JSONB
);

-- 索引优化
CREATE INDEX IF NOT EXISTS idx_api_logs_timestamp ON api_access_logs(timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_api_logs_endpoint ON api_access_logs(endpoint);
CREATE INDEX IF NOT EXISTS idx_api_logs_session_id ON api_access_logs(session_id);
CREATE INDEX IF NOT EXISTS idx_api_logs_status_code ON api_access_logs(status_code);

-- 分区（按月）- 可选，大数据量时使用
-- CREATE TABLE api_access_logs_y2025m01 PARTITION OF api_access_logs
--     FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

-- ============================================
-- 3. 错误日志表（新增）
-- ============================================
CREATE TABLE IF NOT EXISTS error_logs (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    timestamp TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    -- 错误信息
    error_type TEXT NOT NULL,  -- 'validation', 'model_load', 'api_error', etc.
    error_message TEXT NOT NULL,
    error_stack TEXT,

    -- 上下文
    endpoint TEXT,
    session_id TEXT,
    user_input TEXT,

    -- 请求详情
    request_data JSONB,

    -- 元数据
    metadata JSONB
);

CREATE INDEX IF NOT EXISTS idx_error_logs_timestamp ON error_logs(timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_error_logs_type ON error_logs(error_type);
CREATE INDEX IF NOT EXISTS idx_error_logs_session_id ON error_logs(session_id);

-- ============================================
-- 4. 模型性能日志表（新增）
-- ============================================
CREATE TABLE IF NOT EXISTS model_performance_logs (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    timestamp TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    -- 模型信息
    model_name TEXT NOT NULL,
    model_type TEXT NOT NULL,  -- 'sbert', 'sentiment', 'compliance', 'llm'

    -- 性能指标
    load_time_ms INTEGER,
    inference_time_ms INTEGER,
    total_time_ms INTEGER,

    -- 输入输出
    input_text TEXT,
    output_summary TEXT,

    -- 附加信息
    metadata JSONB
);

CREATE INDEX IF NOT EXISTS idx_model_perf_timestamp ON model_performance_logs(timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_model_perf_model_name ON model_performance_logs(model_name);
CREATE INDEX IF NOT EXISTS idx_model_perf_type ON model_performance_logs(model_type);

-- ============================================
-- 5. 调试日志表（新增）- 用于开发调试
-- ============================================
CREATE TABLE IF NOT EXISTS debug_logs (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    timestamp TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    -- 日志信息
    level TEXT NOT NULL,  -- 'DEBUG', 'INFO', 'WARNING', 'ERROR'
    logger TEXT NOT NULL,  -- 模块名称，如 'intelligence_analyzer'
    message TEXT NOT NULL,

    -- 上下文
    session_id TEXT,
    function_name TEXT,
    line_number INTEGER,

    -- 结构化数据
    data JSONB,

    -- 附加信息
    metadata JSONB
);

CREATE INDEX IF NOT EXISTS idx_debug_logs_timestamp ON debug_logs(timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_debug_logs_level ON debug_logs(level);
CREATE INDEX IF NOT EXISTS idx_debug_logs_logger ON debug_logs(logger);
CREATE INDEX IF NOT EXISTS idx_debug_logs_session_id ON debug_logs(session_id);

-- ============================================
-- 6. 用户行为分析表（新增）
-- ============================================
CREATE TABLE IF NOT EXISTS user_behavior_logs (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    timestamp TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    -- 用户标识
    session_id TEXT NOT NULL,
    user_id TEXT,  -- 可选，如果有多用户系统

    -- 行为类型
    action_type TEXT NOT NULL,  -- 'chat', 'evaluate', 'config_check', etc.
    action_detail TEXT,

    -- 内容
    input_text TEXT,
    intent_detected TEXT,
    scenario_detected TEXT,

    -- 结果
    success BOOLEAN,
    error_message TEXT,

    -- 元数据
    metadata JSONB
);

CREATE INDEX IF NOT EXISTS idx_user_behavior_timestamp ON user_behavior_logs(timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_user_behavior_session_id ON user_behavior_logs(session_id);
CREATE INDEX IF NOT EXISTS idx_user_behavior_action_type ON user_behavior_logs(action_type);

-- ============================================
-- 启用行级安全性 (RLS)
-- ============================================
ALTER TABLE api_access_logs ENABLE ROW LEVEL SECURITY;
ALTER TABLE error_logs ENABLE ROW LEVEL SECURITY;
ALTER TABLE model_performance_logs ENABLE ROW LEVEL SECURITY;
ALTER TABLE debug_logs ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_behavior_logs ENABLE ROW LEVEL SECURITY;

-- 允许所有操作（演示环境）
CREATE POLICY "Enable all access for api_access_logs" ON api_access_logs FOR ALL USING (true);
CREATE POLICY "Enable all access for error_logs" ON error_logs FOR ALL USING (true);
CREATE POLICY "Enable all access for model_performance_logs" ON model_performance_logs FOR ALL USING (true);
CREATE POLICY "Enable all access for debug_logs" ON debug_logs FOR ALL USING (true);
CREATE POLICY "Enable all access for user_behavior_logs" ON user_behavior_logs FOR ALL USING (true);

-- ============================================
-- 实用函数
-- ============================================

-- 清理旧日志（保留最近 N 天）
CREATE OR REPLACE FUNCTION cleanup_old_logs(days_to_keep INTEGER DEFAULT 30)
RETURNS TABLE (
    api_logs_deleted BIGINT,
    error_logs_deleted BIGINT,
    debug_logs_deleted BIGINT,
    model_perf_logs_deleted BIGINT
)
LANGUAGE plpgsql
AS $$
DECLARE
    api_count BIGINT;
    error_count BIGINT;
    debug_count BIGINT;
    model_count BIGINT;
BEGIN
    -- 清理 API 日志
    DELETE FROM api_access_logs
    WHERE timestamp < NOW() - (days_to_keep || ' days')::INTERVAL;
    GET DIAGNOSTICS api_count = ROW_COUNT;

    -- 清理错误日志（保留更久）
    DELETE FROM error_logs
    WHERE timestamp < NOW() - ((days_to_keep * 2) || ' days')::INTERVAL;
    GET DIAGNOSTICS error_count = ROW_COUNT;

    -- 清理调试日志（保留更短时间）
    DELETE FROM debug_logs
    WHERE timestamp < NOW() - ((days_to_keep / 2) || ' days')::INTERVAL;
    GET DIAGNOSTICS debug_count = ROW_COUNT;

    -- 清理模型性能日志
    DELETE FROM model_performance_logs
    WHERE timestamp < NOW() - (days_to_keep || ' days')::INTERVAL;
    GET DIAGNOSTICS model_count = ROW_COUNT;

    RETURN QUERY SELECT api_count, error_count, debug_count, model_count;
END;
$$;

-- 获取 API 统计
CREATE OR REPLACE FUNCTION get_api_statistics(hours_ago INTEGER DEFAULT 24)
RETURNS TABLE (
    endpoint TEXT,
    request_count BIGINT,
    avg_response_time NUMERIC,
    error_rate NUMERIC,
    p95_response_time NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        endpoint,
        COUNT(*) as request_count,
        AVG(response_time_ms) as avg_response_time,
        COUNT(*) FILTER (WHERE status_code >= 400) * 100.0 / COUNT(*) as error_rate,
        PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY response_time_ms) as p95_response_time
    FROM api_access_logs
    WHERE timestamp >= NOW() - (hours_ago || ' hours')::INTERVAL
    GROUP BY endpoint
    ORDER BY request_count DESC;
END;
$$;

-- 获取错误统计
CREATE OR REPLACE FUNCTION get_error_summary(hours_ago INTEGER DEFAULT 24)
RETURNS TABLE (
    error_type TEXT,
    error_count BIGINT,
    most_common_message TEXT
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        error_type,
        COUNT(*) as error_count,
        mode() WITHIN GROUP (ORDER BY error_message) as most_common_message
    FROM error_logs
    WHERE timestamp >= NOW() - (hours_ago || ' hours')::INTERVAL
    GROUP BY error_type
    ORDER BY error_count DESC;
END;
$$;

-- 搜索日志
CREATE OR REPLACE FUNCTION search_logs(
    search_term TEXT,
    log_type TEXT DEFAULT 'all',  -- 'api', 'error', 'debug', 'all'
    hours_ago INTEGER DEFAULT 24,
    limit_count INTEGER DEFAULT 100
)
RETURNS TABLE (
    log_type TEXT,
    log_id UUID,
    timestamp TIMESTAMP WITH TIME ZONE,
    summary TEXT
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- API 日志
    IF log_type = 'api' OR log_type = 'all' THEN
        RETURN QUERY
        SELECT
            'api'::TEXT as log_type,
            id as log_id,
            timestamp,
            endpoint || ' - ' || status_code::TEXT as summary
        FROM api_access_logs
        WHERE (
            search_term ILIKE '%' || search_term || '%'
            OR endpoint ILIKE '%' || search_term || '%'
            OR path ILIKE '%' || search_term || '%'
        )
        AND timestamp >= NOW() - (hours_ago || ' hours')::INTERVAL
        LIMIT limit_count;
    END IF;

    -- 错误日志
    IF log_type = 'error' OR log_type = 'all' THEN
        RETURN QUERY
        SELECT
            'error'::TEXT as log_type,
            id as log_id,
            timestamp,
            error_type || ' - ' || LEFT(error_message, 50) as summary
        FROM error_logs
        WHERE error_message ILIKE '%' || search_term || '%'
        AND timestamp >= NOW() - (hours_ago || ' hours')::INTERVAL
        LIMIT limit_count;
    END IF;

    -- 调试日志
    IF log_type = 'debug' OR log_type = 'all' THEN
        RETURN QUERY
        SELECT
            'debug'::TEXT as log_type,
            id as log_id,
            timestamp,
            level || ' - ' || logger || ' - ' || LEFT(message, 50) as summary
        FROM debug_logs
        WHERE message ILIKE '%' || search_term || '%'
        AND timestamp >= NOW() - (hours_ago || ' hours')::INTERVAL
        LIMIT limit_count;
    END IF;
END;
$$;

-- ============================================
-- 定时清理任务（可选）
-- ============================================
-- 创建一个函数来定期清理日志
CREATE OR REPLACE FUNCTION auto_cleanup_logs()
RETURNS VOID
LANGUAGE plpgsql
AS $$
BEGIN
    -- 清理 7 天前的调试日志
    DELETE FROM debug_logs
    WHERE timestamp < NOW() - INTERVAL '7 days';

    -- 清理 30 天前的 API 日志
    DELETE FROM api_access_logs
    WHERE timestamp < NOW() - INTERVAL '30 days';

    -- 清理 90 天前的模型性能日志
    DELETE FROM model_performance_logs
    WHERE timestamp < NOW() - INTERVAL '90 days';

    -- 错误日志保留 180 天
    DELETE FROM error_logs
    WHERE timestamp < NOW() - INTERVAL '180 days';
END;
$$;

-- 在 Supabase Dashboard 中设置 cron job 来调用这个函数
-- 或者使用 pg_cron 扩展（如果可用）
-- SELECT cron.schedule('cleanup-logs', '0 2 * * *', 'SELECT auto_cleanup_logs()');

-- ============================================
-- 视图 - 方便查询
-- ============================================

-- 综合日志视图
CREATE OR REPLACE VIEW v_recent_logs AS
SELECT
    'api' as log_type,
    timestamp,
    endpoint as title,
    status_code::TEXT as detail,
    session_id
FROM api_access_logs
WHERE timestamp >= NOW() - INTERVAL '24 hours'

UNION ALL

SELECT
    'error' as log_type,
    timestamp,
    error_type as title,
    LEFT(error_message, 50) as detail,
    session_id
FROM error_logs
WHERE timestamp >= NOW() - INTERVAL '24 hours'

UNION ALL

SELECT
    'debug' as log_type,
    timestamp,
    logger as title,
    level || ' - ' || LEFT(message, 30) as detail,
    session_id
FROM debug_logs
WHERE timestamp >= NOW() - INTERVAL '24 hours'

ORDER BY timestamp DESC
LIMIT 100;

-- ============================================
-- 注释
-- ============================================
COMMENT ON TABLE api_access_logs IS 'API 访问日志';
COMMENT ON TABLE error_logs IS '错误日志';
COMMENT ON TABLE model_performance_logs IS '模型性能日志';
COMMENT ON TABLE debug_logs IS '调试日志';
COMMENT ON TABLE user_behavior_logs IS '用户行为日志';

COMMENT ON FUNCTION cleanup_old_logs IS '清理旧日志，默认保留 30 天';
COMMENT ON FUNCTION get_api_statistics IS '获取 API 统计信息';
COMMENT ON FUNCTION get_error_summary IS '获取错误摘要';
COMMENT ON FUNCTION search_logs IS '搜索日志内容';
COMMENT ON FUNCTION auto_cleanup_logs IS '自动清理日志';
COMMENT ON VIEW v_recent_logs IS '最近 24 小时的综合日志视图';