Maas-Model-platform/backend/scripts/create_research_tables.sql

-- 创建研究任务相关表

-- 1. 研究任务表
CREATE TABLE IF NOT EXISTS aigcspace.research_tasks (
    -- 主键与基础信息
    id SERIAL PRIMARY KEY,
    task_id VARCHAR(64) UNIQUE NOT NULL,
    user_id VARCHAR(50) NOT NULL,
    
    -- 研究配置
    model VARCHAR(50) NOT NULL DEFAULT 'qwen-deep-research',
    output_format VARCHAR(50) DEFAULT 'model_detailed_report',
    
    -- 研究内容
    initial_query TEXT NOT NULL,
    clarification_questions TEXT,
    user_clarification TEXT,
    research_goal TEXT,
    
    -- 研究结果
    research_plan TEXT,
    final_report TEXT,
    web_sites JSONB,
    reference_sources JSONB,
    
    -- 任务状态
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    current_phase VARCHAR(50),
    phase_status VARCHAR(50),
    error_message TEXT,
    
    -- Token 统计
    input_tokens INTEGER DEFAULT 0,
    output_tokens INTEGER DEFAULT 0,
    total_tokens INTEGER DEFAULT 0,
    
    -- 费用统计
    bill NUMERIC(10, 4) DEFAULT 0,
    
    -- 时间戳
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    clarified_at TIMESTAMP,
    completed_at TIMESTAMP,
    
    -- 软删除
    is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
    deleted_at TIMESTAMP,
    
    -- 外键约束
    CONSTRAINT fk_research_tasks_user_id FOREIGN KEY (user_id) 
        REFERENCES aigcspace.users(id) ON DELETE CASCADE
);

-- 创建索引
CREATE INDEX IF NOT EXISTS idx_research_tasks_user_id ON aigcspace.research_tasks(user_id);
CREATE INDEX IF NOT EXISTS idx_research_tasks_task_id ON aigcspace.research_tasks(task_id);
CREATE INDEX IF NOT EXISTS idx_research_tasks_status ON aigcspace.research_tasks(status);
CREATE INDEX IF NOT EXISTS idx_research_tasks_created_at ON aigcspace.research_tasks(created_at);
CREATE INDEX IF NOT EXISTS idx_research_tasks_is_deleted ON aigcspace.research_tasks(is_deleted);
CREATE INDEX IF NOT EXISTS idx_research_tasks_user_deleted ON aigcspace.research_tasks(user_id, is_deleted);
CREATE INDEX IF NOT EXISTS idx_research_tasks_current_phase ON aigcspace.research_tasks(current_phase);

-- 全文搜索索引
CREATE INDEX IF NOT EXISTS idx_research_tasks_query_search ON aigcspace.research_tasks 
    USING gin(to_tsvector('simple', initial_query));
CREATE INDEX IF NOT EXISTS idx_research_tasks_report_search ON aigcspace.research_tasks 
    USING gin(to_tsvector('simple', final_report));

-- 添加表注释
COMMENT ON TABLE aigcspace.research_tasks IS '研究任务表';
COMMENT ON COLUMN aigcspace.research_tasks.id IS '主键ID';
COMMENT ON COLUMN aigcspace.research_tasks.task_id IS '任务唯一标识（UUID）';
COMMENT ON COLUMN aigcspace.research_tasks.user_id IS '用户ID';
COMMENT ON COLUMN aigcspace.research_tasks.model IS '研究模型';
COMMENT ON COLUMN aigcspace.research_tasks.output_format IS '输出格式：model_detailed_report/model_summary_report';
COMMENT ON COLUMN aigcspace.research_tasks.initial_query IS '初始研究问题';
COMMENT ON COLUMN aigcspace.research_tasks.clarification_questions IS '模型反问的澄清问题';
COMMENT ON COLUMN aigcspace.research_tasks.user_clarification IS '用户的澄清回答';
COMMENT ON COLUMN aigcspace.research_tasks.research_goal IS '最终确定的研究目标';
COMMENT ON COLUMN aigcspace.research_tasks.research_plan IS '研究计划';
COMMENT ON COLUMN aigcspace.research_tasks.final_report IS '最终研究报告';
COMMENT ON COLUMN aigcspace.research_tasks.web_sites IS '搜索的网站列表';
COMMENT ON COLUMN aigcspace.research_tasks.reference_sources IS '引用文献列表';
COMMENT ON COLUMN aigcspace.research_tasks.status IS '任务状态：pending/clarifying/researching/completed/failed';
COMMENT ON COLUMN aigcspace.research_tasks.current_phase IS '当前阶段：answer/ResearchPlanning/WebResearch/KeepAlive';
COMMENT ON COLUMN aigcspace.research_tasks.phase_status IS '阶段状态：typing/finished/streamingQueries/streamingWebResult/WebResultFinished';
COMMENT ON COLUMN aigcspace.research_tasks.error_message IS '错误信息';
COMMENT ON COLUMN aigcspace.research_tasks.input_tokens IS '输入Token数';
COMMENT ON COLUMN aigcspace.research_tasks.output_tokens IS '输出Token数';
COMMENT ON COLUMN aigcspace.research_tasks.total_tokens IS '总Token数';
COMMENT ON COLUMN aigcspace.research_tasks.bill IS '消费金额（元）';
COMMENT ON COLUMN aigcspace.research_tasks.created_at IS '创建时间';
COMMENT ON COLUMN aigcspace.research_tasks.updated_at IS '更新时间';
COMMENT ON COLUMN aigcspace.research_tasks.clarified_at IS '澄清完成时间';
COMMENT ON COLUMN aigcspace.research_tasks.completed_at IS '研究完成时间';
COMMENT ON COLUMN aigcspace.research_tasks.is_deleted IS '是否已删除';
COMMENT ON COLUMN aigcspace.research_tasks.deleted_at IS '删除时间';

-- 2. 研究阶段日志表
CREATE TABLE IF NOT EXISTS aigcspace.research_phase_logs (
    -- 主键与基础信息
    id SERIAL PRIMARY KEY,
    task_id VARCHAR(64) NOT NULL,
    
    -- 阶段信息
    phase VARCHAR(50) NOT NULL,
    status VARCHAR(50) NOT NULL,
    content TEXT,
    
    -- 额外信息
    extra_data JSONB,
    
    -- 时间戳
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    -- 外键约束
    CONSTRAINT fk_research_phase_logs_task_id FOREIGN KEY (task_id) 
        REFERENCES aigcspace.research_tasks(task_id) ON DELETE CASCADE
);

-- 创建索引
CREATE INDEX IF NOT EXISTS idx_research_phase_logs_task_id ON aigcspace.research_phase_logs(task_id);
CREATE INDEX IF NOT EXISTS idx_research_phase_logs_phase ON aigcspace.research_phase_logs(phase);
CREATE INDEX IF NOT EXISTS idx_research_phase_logs_created_at ON aigcspace.research_phase_logs(created_at);

-- 添加表注释
COMMENT ON TABLE aigcspace.research_phase_logs IS '研究阶段日志表';
COMMENT ON COLUMN aigcspace.research_phase_logs.id IS '主键ID';
COMMENT ON COLUMN aigcspace.research_phase_logs.task_id IS '关联的研究任务ID';
COMMENT ON COLUMN aigcspace.research_phase_logs.phase IS '阶段名称：answer/ResearchPlanning/WebResearch/KeepAlive';
COMMENT ON COLUMN aigcspace.research_phase_logs.status IS '阶段状态';
COMMENT ON COLUMN aigcspace.research_phase_logs.content IS '阶段输出内容';
COMMENT ON COLUMN aigcspace.research_phase_logs.extra_data IS '额外数据（deep_research信息）';
COMMENT ON COLUMN aigcspace.research_phase_logs.created_at IS '创建时间';

-- 3. 研究统计表
CREATE TABLE IF NOT EXISTS aigcspace.research_statistics (
    -- 主键与基础信息
    id SERIAL PRIMARY KEY,
    user_id VARCHAR(50) NOT NULL,
    stat_date TIMESTAMP NOT NULL,
    
    -- 统计数据
    total_researches INTEGER DEFAULT 0,
    completed_researches INTEGER DEFAULT 0,
    failed_researches INTEGER DEFAULT 0,
    
    -- Token 统计
    total_input_tokens INTEGER DEFAULT 0,
    total_output_tokens INTEGER DEFAULT 0,
    total_tokens INTEGER DEFAULT 0,
    
    -- 费用统计
    total_cost NUMERIC(10, 4) DEFAULT 0,
    
    -- 报告类型统计
    detailed_reports INTEGER DEFAULT 0,
    summary_reports INTEGER DEFAULT 0,
    
    -- 时间戳
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    -- 外键约束
    CONSTRAINT fk_research_statistics_user_id FOREIGN KEY (user_id) 
        REFERENCES aigcspace.users(id) ON DELETE CASCADE,
    
    -- 唯一约束
    CONSTRAINT uk_research_statistics UNIQUE (user_id, stat_date)
);

-- 创建索引
CREATE INDEX IF NOT EXISTS idx_research_statistics_user_id ON aigcspace.research_statistics(user_id);
CREATE INDEX IF NOT EXISTS idx_research_statistics_stat_date ON aigcspace.research_statistics(stat_date);
CREATE INDEX IF NOT EXISTS idx_research_statistics_user_date ON aigcspace.research_statistics(user_id, stat_date);

-- 添加表注释
COMMENT ON TABLE aigcspace.research_statistics IS '研究统计表';
COMMENT ON COLUMN aigcspace.research_statistics.id IS '主键ID';
COMMENT ON COLUMN aigcspace.research_statistics.user_id IS '用户ID';
COMMENT ON COLUMN aigcspace.research_statistics.stat_date IS '统计日期';
COMMENT ON COLUMN aigcspace.research_statistics.total_researches IS '研究总数';
COMMENT ON COLUMN aigcspace.research_statistics.completed_researches IS '完成的研究数';
COMMENT ON COLUMN aigcspace.research_statistics.failed_researches IS '失败的研究数';
COMMENT ON COLUMN aigcspace.research_statistics.total_input_tokens IS '总输入Token数';
COMMENT ON COLUMN aigcspace.research_statistics.total_output_tokens IS '总输出Token数';
COMMENT ON COLUMN aigcspace.research_statistics.total_tokens IS '总Token数';
COMMENT ON COLUMN aigcspace.research_statistics.total_cost IS '总费用（元）';
COMMENT ON COLUMN aigcspace.research_statistics.detailed_reports IS '详细报告数量';
COMMENT ON COLUMN aigcspace.research_statistics.summary_reports IS '摘要报告数量';
COMMENT ON COLUMN aigcspace.research_statistics.created_at IS '创建时间';
COMMENT ON COLUMN aigcspace.research_statistics.updated_at IS '更新时间';