admin/012-kaopeilian
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
test
012-kaopeilian/backend/数据库架构-统一版.md
111 442ac78b56
Some checks failed
continuous-integration/drone/push Build is failing
Details
sync: 同步服务器最新代码 (2026-01-27) 
更新内容：
- 后端 AI 服务优化（能力分析、知识点解析等）
- 前端考试和陪练界面更新
- 修复多个 prompt 和 JSON 解析问题
- 更新 Coze 语音客户端
2026-01-27 10:03:28 +08:00

36 KiB
Raw Permalink Blame History

考培练系统统一数据库架构设计

数据库基本信息

  • 数据库名称：kaopeilian
  • 字符集：utf8mb4
  • 排序规则：utf8mb4_unicode_ci
  • 存储引擎：InnoDB

更新历史

  • 2026-01-17：SCRM系统对接API功能
    • users表新增wework_userid字段（VARCHAR(64) UNIQUE），用于存储企微员工userid
    • 新增SCRM对接API：获取员工岗位、获取岗位课程、搜索知识点、获取知识点详情
  • 2025-11-11：员工同步功能更新
    • users表email字段改为可空（支持没有邮箱的员工）
    • 新增员工同步功能，从钉钉员工表同步在职员工
  • 2025-10-16：系统增强功能数据库更新
    • exam_mistakes表新增字段：mastery_status、difficulty、wrong_count、mastered_at（用于错题掌握状态）
    • courses表确认字段：student_count、is_new（用于课程学员统计）
    • tasks、task_courses、task_assignments表已完整实施（任务管理功能）
    • system_logs表已完整实施（系统日志功能）
  • 2025-10-14：courses表新增broadcast_audio_url和broadcast_generated_at字段（用于播课功能）
  • 2025-10-13：新增practice_scenes陪练场景表（用于陪练中心功能）
  • 2025-09-30：新增exam_mistakes错题记录表
  • 2025-09-30：course_exam_settings表新增essay_count字段（问答题数量）
  • 2025-09-27：知识点表结构重大简化，废弃material_knowledge_points中间表
  • 2025-09-27：knowledge_points表新增material_id、type、source、topic_relation字段
  • 2025-09-27：course_materials表新增created_by、updated_by审计字段
  • 2025-09-27：统一远程和本地数据库结构，确保字段约束一致性
  • 2025-09-22：为positions表添加skills、level、sort_order字段
  • 2025-09-22：为users表添加school（学校）和major（专业）字段
  • 2025-09-22：为knowledge_points表添加created_by、updated_by审计字段
  • 2025-09-22：新增material_knowledge_points关联表，支持资料与知识点的关联管理（已废弃）

一、用户管理模块

1.1 用户表 (users)

CREATE TABLE `users` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `username` VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
    `email` VARCHAR(100) NULL UNIQUE COMMENT '邮箱（可选）',
    `phone` VARCHAR(20) UNIQUE COMMENT '手机号',
    `password_hash` VARCHAR(200) NOT NULL COMMENT '密码哈希',
    `full_name` VARCHAR(100) COMMENT '姓名',
    `gender` VARCHAR(10) COMMENT '性别: male/female',
    `avatar_url` VARCHAR(500) COMMENT '头像URL',
    `bio` TEXT COMMENT '个人简介',
    `school` VARCHAR(100) COMMENT '学校',
    `major` VARCHAR(100) COMMENT '专业',
    `wework_userid` VARCHAR(64) UNIQUE COMMENT '企微员工userid（用于SCRM系统对接）',
    `role` VARCHAR(20) DEFAULT 'trainee' COMMENT '系统角色: admin, manager, trainee',
    `is_active` BOOLEAN DEFAULT TRUE COMMENT '是否激活',
    `is_verified` BOOLEAN DEFAULT FALSE COMMENT '是否验证',
    `last_login_at` DATETIME COMMENT '最后登录时间',
    `password_changed_at` DATETIME COMMENT '密码修改时间',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `is_deleted` BOOLEAN DEFAULT FALSE COMMENT '是否删除',
    `deleted_at` DATETIME COMMENT '删除时间',
    INDEX idx_role (role),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';

1.2 团队表 (teams)

CREATE TABLE `teams` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL UNIQUE COMMENT '团队名称',
    `code` VARCHAR(50) NOT NULL UNIQUE COMMENT '团队代码',
    `description` TEXT COMMENT '团队描述',
    `team_type` VARCHAR(50) DEFAULT 'department' COMMENT '团队类型: department, project, study_group',
    `is_active` BOOLEAN DEFAULT TRUE COMMENT '是否激活',
    `leader_id` INT COMMENT '负责人ID',
    `parent_id` INT COMMENT '父团队ID',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (leader_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (parent_id) REFERENCES teams(id) ON DELETE CASCADE,
    INDEX idx_team_type (team_type),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='团队表';

1.3 用户团队关联表 (user_teams)

CREATE TABLE `user_teams` (
    `user_id` INT NOT NULL,
    `team_id` INT NOT NULL,
    `role` VARCHAR(50) DEFAULT 'member' COMMENT '团队角色: member, leader',
    `joined_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '加入时间',
    PRIMARY KEY (user_id, team_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户团队关联表';

二、组织与岗位管理模块

2.1 岗位表 (positions)

CREATE TABLE `positions` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL COMMENT '岗位名称',
    `code` VARCHAR(100) NOT NULL UNIQUE COMMENT '岗位编码',
    `description` TEXT COMMENT '岗位描述',
    `parent_id` INT NULL COMMENT '上级岗位ID',
    `status` VARCHAR(20) NOT NULL DEFAULT 'active' COMMENT '状态: active/inactive',
    `skills` JSON NULL COMMENT '核心技能',
    `level` VARCHAR(20) NULL COMMENT '岗位等级: junior/intermediate/senior/expert',
    `sort_order` INT DEFAULT 0 COMMENT '排序',
    `is_deleted` BOOLEAN DEFAULT FALSE COMMENT '是否删除',
    `deleted_at` DATETIME NULL COMMENT '删除时间',
    `created_by` INT NULL COMMENT '创建人ID',
    `updated_by` INT NULL COMMENT '更新人ID',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_id) REFERENCES positions(id) ON DELETE SET NULL,
    INDEX idx_positions_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='岗位表';

2.2 岗位成员表 (position_members)

CREATE TABLE `position_members` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `position_id` INT NOT NULL COMMENT '岗位ID',
    `user_id` INT NOT NULL COMMENT '用户ID',
    `role` VARCHAR(50) DEFAULT 'member' COMMENT '岗位角色',
    `joined_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '加入时间',
    `is_deleted` BOOLEAN DEFAULT FALSE COMMENT '是否删除',
    `deleted_at` DATETIME NULL COMMENT '删除时间',
    UNIQUE KEY `idx_position_user` (`position_id`, `user_id`),
    FOREIGN KEY (position_id) REFERENCES positions(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='岗位成员关联表';

2.3 岗位课程表 (position_courses)

CREATE TABLE `position_courses` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `position_id` INT NOT NULL COMMENT '岗位ID',
    `course_id` INT NOT NULL COMMENT '课程ID',
    `course_type` VARCHAR(20) NOT NULL DEFAULT 'required' COMMENT '课程类型: required/optional',
    `priority` INT DEFAULT 0 COMMENT '优先级',
    `is_deleted` BOOLEAN DEFAULT FALSE COMMENT '是否删除',
    `deleted_at` DATETIME NULL COMMENT '删除时间',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `idx_position_course` (`position_id`, `course_id`),
    FOREIGN KEY (position_id) REFERENCES positions(id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
    INDEX idx_course_id (course_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='岗位课程关联表';

三、课程管理模块

3.1 课程表 (courses)

CREATE TABLE `courses` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(200) NOT NULL COMMENT '课程名称',
    `description` TEXT COMMENT '课程描述',
    `category` ENUM('technology', 'management', 'business', 'general') DEFAULT 'general' COMMENT '课程分类',
    `status` ENUM('draft', 'published', 'archived') DEFAULT 'draft' COMMENT '课程状态',
    `cover_image` VARCHAR(500) COMMENT '封面图片URL',
    `duration_hours` FLOAT COMMENT '课程时长(小时)',
    `difficulty_level` INT COMMENT '难度等级(1-5)',
    `tags` JSON COMMENT '标签列表',
    `published_at` DATETIME COMMENT '发布时间',
    `publisher_id` INT COMMENT '发布人ID',
    `broadcast_audio_url` VARCHAR(500) COMMENT '播课音频URL',
    `broadcast_generated_at` DATETIME COMMENT '播课生成时间',
    `broadcast_status` VARCHAR(20) COMMENT '播课生成状态: pending/generating/completed/failed',
    `broadcast_task_id` VARCHAR(100) COMMENT 'Coze工作流任务ID',
    `broadcast_error_message` TEXT COMMENT '生成失败错误信息',
    `sort_order` INT DEFAULT 0 COMMENT '排序顺序',
    `is_featured` BOOLEAN DEFAULT FALSE COMMENT '是否推荐',
    `student_count` INT DEFAULT 0 COMMENT '学员数量',
    `is_new` BOOLEAN DEFAULT TRUE COMMENT '是否新课程',
    `is_deleted` BOOLEAN DEFAULT FALSE COMMENT '是否删除',
    `deleted_at` DATETIME COMMENT '删除时间',
    `created_by` INT COMMENT '创建人ID',
    `updated_by` INT COMMENT '更新人ID',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_category (category),
    INDEX idx_is_featured (is_featured),
    INDEX idx_is_deleted (is_deleted),
    INDEX idx_sort_order (sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='课程表';

3.2 课程资料表 (course_materials)

CREATE TABLE `course_materials` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `course_id` INT NOT NULL COMMENT '课程ID',
    `name` VARCHAR(200) NOT NULL COMMENT '资料名称',
    `description` TEXT COMMENT '资料描述',
    `file_url` VARCHAR(500) NOT NULL COMMENT '文件URL',
    `file_type` VARCHAR(50) NOT NULL COMMENT '文件类型',
    `file_size` INT NOT NULL COMMENT '文件大小(字节)',
    `sort_order` INT DEFAULT 0 COMMENT '排序顺序',
    `is_deleted` BOOLEAN DEFAULT FALSE COMMENT '是否删除',
    `deleted_at` DATETIME COMMENT '删除时间',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
    INDEX idx_course_id (course_id),
    INDEX idx_is_deleted (is_deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='课程资料表';

3.3 知识点表 (knowledge_points)

CREATE TABLE `knowledge_points` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `course_id` INT NOT NULL COMMENT '课程ID',
    `material_id` INT COMMENT '关联资料ID',
    `name` VARCHAR(200) NOT NULL COMMENT '知识点名称',
    `description` TEXT COMMENT '知识点描述',
    `type` VARCHAR(50) DEFAULT '概念定义' COMMENT '知识点类型',
    `source` TINYINT(1) DEFAULT 0 COMMENT '来源：0=手动，1=AI分析',
    `topic_relation` VARCHAR(200) COMMENT '与主题的关系描述',
    `is_deleted` BOOLEAN DEFAULT FALSE COMMENT '是否删除',
    `deleted_at` DATETIME COMMENT '删除时间',
    `created_by` INT COMMENT '创建人ID',
    `updated_by` INT COMMENT '更新人ID',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
    FOREIGN KEY (material_id) REFERENCES course_materials(id) ON DELETE SET NULL,
    INDEX idx_course_id (course_id),
    INDEX idx_material_id (material_id),
    INDEX idx_is_deleted (is_deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='知识点表';

3.4 成长路径表 (growth_paths)

CREATE TABLE `growth_paths` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(200) NOT NULL COMMENT '路径名称',
    `description` TEXT COMMENT '路径描述',
    `target_role` VARCHAR(100) COMMENT '目标角色',
    `courses` JSON COMMENT '课程列表[{course_id, order, is_required}]',
    `estimated_duration_days` INT COMMENT '预计完成天数',
    `is_active` BOOLEAN DEFAULT TRUE COMMENT '是否启用',
    `sort_order` INT DEFAULT 0 COMMENT '排序顺序',
    `is_deleted` BOOLEAN DEFAULT FALSE COMMENT '是否删除',
    `deleted_at` DATETIME COMMENT '删除时间',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_is_active (is_active),
    INDEX idx_is_deleted (is_deleted),
    INDEX idx_sort_order (sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='成长路径表';

四、考试模块

4.1 题目表 (questions)

CREATE TABLE `questions` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `course_id` INT NOT NULL COMMENT '课程ID',
    `question_type` VARCHAR(20) NOT NULL COMMENT '题目类型: single_choice, multiple_choice, true_false, fill_blank, essay',
    `title` TEXT NOT NULL COMMENT '题目标题',
    `content` TEXT COMMENT '题目内容',
    `options` JSON COMMENT '选项（JSON格式）',
    `correct_answer` TEXT NOT NULL COMMENT '正确答案',
    `explanation` TEXT COMMENT '答案解释',
    `score` FLOAT DEFAULT 10.0 COMMENT '分值',
    `difficulty` VARCHAR(10) DEFAULT 'medium' COMMENT '难度等级: easy, medium, hard',
    `tags` JSON COMMENT '标签（JSON格式）',
    `usage_count` INT DEFAULT 0 COMMENT '使用次数',
    `correct_count` INT DEFAULT 0 COMMENT '答对次数',
    `is_active` BOOLEAN DEFAULT TRUE COMMENT '是否启用',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
    INDEX idx_course_id (course_id),
    INDEX idx_question_type (question_type),
    INDEX idx_difficulty (difficulty),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='题目表';

4.2 课程考试设置表 (course_exam_settings)

CREATE TABLE IF NOT EXISTS course_exam_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    course_id INT NOT NULL UNIQUE COMMENT '课程ID',
    
    -- 题型数量设置
    single_choice_count INT NOT NULL DEFAULT 10 COMMENT '单选题数量',
    multiple_choice_count INT NOT NULL DEFAULT 5 COMMENT '多选题数量',
    true_false_count INT NOT NULL DEFAULT 5 COMMENT '判断题数量',
    fill_blank_count INT NOT NULL DEFAULT 0 COMMENT '填空题数量',
    essay_count INT NOT NULL DEFAULT 0 COMMENT '问答题数量',
    
    -- 考试参数设置
    duration_minutes INT NOT NULL DEFAULT 60 COMMENT '考试时长(分钟)',
    difficulty_level INT NOT NULL DEFAULT 3 COMMENT '难度系数(1-5)',
    passing_score INT NOT NULL DEFAULT 60 COMMENT '及格分数',
    
    -- 其他设置
    is_enabled BOOLEAN NOT NULL DEFAULT TRUE COMMENT '是否启用',
    show_answer_immediately BOOLEAN NOT NULL DEFAULT FALSE COMMENT '是否立即显示答案',
    allow_retake BOOLEAN NOT NULL DEFAULT TRUE COMMENT '是否允许重考',
    max_retake_times INT COMMENT '最大重考次数',
    
    -- 审计字段
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by INT COMMENT '创建人ID',
    updated_by INT COMMENT '更新人ID',
    is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
    deleted_at DATETIME,
    deleted_by INT COMMENT '删除人ID',
    
    FOREIGN KEY (course_id) REFERENCES courses(id),
    INDEX ix_course_exam_settings_id (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='课程考试设置表';

4.3 错题记录表 (exam_mistakes)

CREATE TABLE IF NOT EXISTS exam_mistakes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    
    -- 核心关联字段（必需）
    user_id INT NOT NULL COMMENT '用户ID',
    exam_id INT NOT NULL COMMENT '考试ID',
    round INT DEFAULT 1 COMMENT '考试轮次(1/2/3)',  -- 2026-01-27新增：标识错题产生于哪一轮
    question_id INT COMMENT '题目ID（AI生成的题目可能为空）',
    knowledge_point_id INT COMMENT '关联的知识点ID',
    
    -- 题目核心信息（必需）
    question_content TEXT NOT NULL COMMENT '题目内容',
    correct_answer TEXT NOT NULL COMMENT '正确答案',
    user_answer TEXT COMMENT '用户答案',
    question_type VARCHAR(20) COMMENT '题型(single/multiple/judge/blank/essay)',  -- 2025-10-12新增
    
    -- 审计字段
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (exam_id) REFERENCES exams(id) ON DELETE CASCADE,
    FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE SET NULL,
    FOREIGN KEY (knowledge_point_id) REFERENCES knowledge_points(id) ON DELETE SET NULL,
    
    INDEX idx_user_id (user_id),
    INDEX idx_exam_id (exam_id),
    INDEX idx_exam_round (exam_id, round),  -- 2026-01-27新增：支持按考试和轮次查询
    INDEX idx_knowledge_point_id (knowledge_point_id),
    INDEX idx_question_type (question_type)  -- 2025-10-12新增
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='错题记录表';

核心字段说明：

  • 包含9个核心字段：user_id、exam_id、round、question_id、knowledge_point_id、question_content、correct_answer、user_answer、question_type
  • round 字段（2026-01-27新增）：标识错题产生于哪一轮考试，用于多轮错题重考时只获取上一轮的错题
  • question_id 可为空：AI动态生成的题目可能不在 questions 表中
  • knowledge_point_id 可为空：用于关联知识点，支持错题重考功能
  • question_type 用于记录题型，支持错题按题型筛选和统计（2025-10-12新增）
  • 外键级联删除：用户或考试删除时，相关错题记录也删除
  • 外键置空：题目或知识点删除时，外键置为NULL但保留错题记录

4.4 考试记录表 (exams)

CREATE TABLE `exams` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL COMMENT '用户ID',
    `course_id` INT NOT NULL COMMENT '课程ID',
    `exam_name` VARCHAR(255) NOT NULL COMMENT '考试名称',
    `question_count` INT DEFAULT 10 COMMENT '题目数量',
    `total_score` FLOAT DEFAULT 100.0 COMMENT '总分',
    `pass_score` FLOAT DEFAULT 60.0 COMMENT '及格分',
    `start_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '开始时间',
    `end_time` DATETIME COMMENT '结束时间',
    `duration_minutes` INT DEFAULT 60 COMMENT '考试时长（分钟）',
    `score` FLOAT COMMENT '最终得分（兼容字段）',
    -- 三轮考试得分字段（2025-10-12新增）
    `round1_score` FLOAT COMMENT '第一轮得分',
    `round2_score` FLOAT COMMENT '第二轮得分',
    `round3_score` FLOAT COMMENT '第三轮得分',
    `is_passed` BOOLEAN COMMENT '是否通过',
    `status` VARCHAR(20) DEFAULT 'started' COMMENT '状态: started, submitted, timeout',
    `questions` JSON COMMENT '题目数据（JSON格式）',
    `answers` JSON COMMENT '答案数据（JSON格式）',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_course_id (course_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='考试记录表';

三轮考试机制说明（2025-10-12更新）：

  • 一条考试记录包含三个轮次得分字段：round1_score、round2_score、round3_score
  • 第一轮考试：创建exam记录，设置round1_score
  • 第二轮考试：复用同一条exam记录，更新round2_score
  • 第三轮考试：复用同一条exam记录，更新round3_score和score（最终得分）
  • score字段用于兼容旧代码，通常存储最后完成的轮次得分
  • 成绩报告优先使用round1_score作为主要展示数据

4.5 考试结果详情表 (exam_results)

CREATE TABLE `exam_results` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `exam_id` INT NOT NULL COMMENT '考试ID',
    `question_id` INT NOT NULL COMMENT '题目ID',
    `user_answer` TEXT COMMENT '用户答案',
    `is_correct` BOOLEAN DEFAULT FALSE COMMENT '是否正确',
    `score` FLOAT DEFAULT 0.0 COMMENT '得分',
    `answer_time` INT COMMENT '答题时长（秒）',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (exam_id) REFERENCES exams(id) ON DELETE CASCADE,
    FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE,
    INDEX idx_exam_id (exam_id),
    INDEX idx_question_id (question_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='考试结果详情表';

五、陪练模块

5.1 陪练场景表 (practice_scenes)

CREATE TABLE `practice_scenes` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(200) NOT NULL COMMENT '场景名称',
    `description` TEXT COMMENT '场景描述',
    `type` VARCHAR(50) NOT NULL COMMENT '场景类型: phone/face/complaint/after-sales/product-intro',
    `difficulty` VARCHAR(50) NOT NULL COMMENT '难度等级: beginner/junior/intermediate/senior/expert',
    `status` VARCHAR(20) DEFAULT 'active' COMMENT '状态: active/inactive',
    `background` TEXT COMMENT '场景背景设定',
    `ai_role` TEXT COMMENT 'AI角色描述',
    `objectives` JSON COMMENT '练习目标数组',
    `keywords` JSON COMMENT '关键词数组',
    `duration` INT DEFAULT 10 COMMENT '预计时长（分钟）',
    `usage_count` INT DEFAULT 0 COMMENT '使用次数',
    `rating` DECIMAL(3,1) DEFAULT 0.0 COMMENT '评分',
    `created_by` INT COMMENT '创建人ID',
    `updated_by` INT COMMENT '更新人ID',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `is_deleted` BOOLEAN DEFAULT FALSE,
    `deleted_at` DATETIME,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_type (type),
    INDEX idx_difficulty (difficulty),
    INDEX idx_status (status),
    INDEX idx_is_deleted (is_deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='陪练场景表（陪练中心功能）';

说明：

  • 用于陪练中心功能，存储预设的陪练场景
  • 场景类型：phone（电话销售）、face（面对面销售）、complaint（客户投诉）、after-sales（售后服务）、product-intro（产品介绍）
  • 难度等级：beginner（入门）、junior（初级）、intermediate（中级）、senior（高级）、expert（专家）
  • objectives和keywords字段使用JSON格式存储数组
  • 对话历史由Coze平台管理，不存储在本地数据库

5.2 陪练场景表（旧版本 - training_scenes）

CREATE TABLE `training_scenes` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL COMMENT '场景名称',
    `description` TEXT COMMENT '场景描述',
    `category` VARCHAR(50) NOT NULL COMMENT '场景分类',
    `ai_config` JSON COMMENT 'AI配置（如Coze Bot ID等）',
    `prompt_template` TEXT COMMENT '提示词模板',
    `evaluation_criteria` JSON COMMENT '评估标准',
    `status` ENUM('DRAFT', 'ACTIVE', 'INACTIVE') DEFAULT 'DRAFT' COMMENT '场景状态',
    `is_public` BOOLEAN DEFAULT TRUE COMMENT '是否公开',
    `required_level` INT COMMENT '所需用户等级',
    `is_deleted` BOOLEAN DEFAULT FALSE COMMENT '是否删除',
    `deleted_at` DATETIME COMMENT '删除时间',
    `created_by` BIGINT COMMENT '创建人ID',
    `updated_by` BIGINT COMMENT '更新人ID',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_category (category),
    INDEX idx_is_public (is_public),
    INDEX idx_is_deleted (is_deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='陪练场景表';

5.2 陪练会话表 (training_sessions)

CREATE TABLE `training_sessions` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL COMMENT '用户ID',
    `scene_id` INT NOT NULL COMMENT '场景ID',
    `coze_conversation_id` VARCHAR(100) COMMENT 'Coze会话ID',
    `start_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '开始时间',
    `end_time` DATETIME COMMENT '结束时间',
    `duration_seconds` INT COMMENT '持续时长（秒）',
    `status` ENUM('CREATED', 'IN_PROGRESS', 'COMPLETED', 'CANCELLED', 'ERROR') DEFAULT 'CREATED' COMMENT '会话状态',
    `session_config` JSON COMMENT '会话配置',
    `total_score` FLOAT COMMENT '总分',
    `evaluation_result` JSON COMMENT '评估结果详情',
    `created_by` BIGINT COMMENT '创建人ID',
    `updated_by` BIGINT COMMENT '更新人ID',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (scene_id) REFERENCES training_scenes(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_scene_id (scene_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='陪练会话表';

5.3 陪练消息表 (training_messages)

CREATE TABLE `training_messages` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `session_id` INT NOT NULL COMMENT '会话ID',
    `role` ENUM('USER', 'ASSISTANT', 'SYSTEM') NOT NULL COMMENT '消息角色',
    `type` ENUM('TEXT', 'VOICE', 'SYSTEM') NOT NULL COMMENT '消息类型',
    `content` TEXT NOT NULL COMMENT '消息内容',
    `voice_url` VARCHAR(500) COMMENT '语音文件URL',
    `voice_duration` FLOAT COMMENT '语音时长（秒）',
    `message_metadata` JSON COMMENT '消息元数据',
    `coze_message_id` VARCHAR(100) COMMENT 'Coze消息ID',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (session_id) REFERENCES training_sessions(id) ON DELETE CASCADE,
    INDEX idx_session_id (session_id),
    INDEX idx_role (role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='陪练消息表';

5.4 陪练报告表 (training_reports)

CREATE TABLE `training_reports` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `session_id` INT NOT NULL UNIQUE COMMENT '会话ID',
    `user_id` INT NOT NULL COMMENT '用户ID',
    `overall_score` FLOAT NOT NULL COMMENT '总体得分',
    `dimension_scores` JSON NOT NULL COMMENT '各维度得分',
    `strengths` JSON NOT NULL COMMENT '优势点',
    `weaknesses` JSON NOT NULL COMMENT '待改进点',
    `suggestions` JSON NOT NULL COMMENT '改进建议',
    `detailed_analysis` TEXT COMMENT '详细分析',
    `transcript` TEXT COMMENT '对话文本记录',
    `statistics` JSON COMMENT '统计数据',
    `created_by` BIGINT COMMENT '创建人ID',
    `updated_by` BIGINT COMMENT '更新人ID',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (session_id) REFERENCES training_sessions(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='陪练报告表';

六、数据库设计原则

5.1 主键规范

  • 所有表使用INT作为主键，满足当前规模与 ORM 定义
  • 所有主键均设置为AUTO_INCREMENT

5.2 外键约束

  • 所有外键关系都明确定义
  • 删除策略：
    • CASCADE：级联删除（用于强关联关系）
    • SET NULL：置空（用于弱关联关系）

5.3 索引策略

  • 所有外键字段自动创建索引
  • 常用查询字段创建索引（如status, type等）
  • 唯一约束字段自动创建唯一索引

5.4 字段命名规范

  • 使用下划线命名法（snake_case）
  • 布尔字段使用is_前缀
  • 时间字段使用_at后缀
  • JSON字段明确标注数据结构

5.5 软删除设计

  • 使用is_deleted和deleted_at字段实现软删除
  • 保留数据完整性，便于数据恢复

5.6 审计字段

  • created_at：创建时间
  • updated_at：更新时间
  • created_by：创建人ID
  • updated_by：更新人ID

positions - 岗位表（2025-09-22新增）

CREATE TABLE positions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL COMMENT '岗位名称',
    code VARCHAR(100) NOT NULL UNIQUE COMMENT '岗位编码',
    description TEXT COMMENT '岗位描述',
    parent_id INT COMMENT '上级岗位ID',
    status VARCHAR(20) DEFAULT 'active' COMMENT '状态',
    sort_order INT DEFAULT 0 COMMENT '排序',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by INT COMMENT '创建人ID',
    updated_by INT COMMENT '更新人ID',
    is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
    deleted_at DATETIME,
    FOREIGN KEY (parent_id) REFERENCES positions(id) ON DELETE SET NULL,
    INDEX ix_positions_name (name),
    INDEX ix_positions_code (code),
    INDEX ix_positions_id (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='岗位表';

position_members - 岗位成员关联表（2025-09-22新增）

CREATE TABLE position_members (
    id INT AUTO_INCREMENT PRIMARY KEY,
    position_id INT NOT NULL COMMENT '岗位ID',
    user_id INT NOT NULL COMMENT '用户ID',
    role VARCHAR(50) COMMENT '成员角色（预留字段）',
    joined_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '加入时间',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by INT COMMENT '创建人ID',
    updated_by INT COMMENT '更新人ID',
    is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
    deleted_at DATETIME,
    FOREIGN KEY (position_id) REFERENCES positions(id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    UNIQUE KEY uix_position_user (position_id, user_id, is_deleted),
    INDEX ix_position_members_id (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='岗位成员关联表';

position_courses - 岗位课程关联表（2025-09-22新增）

CREATE TABLE position_courses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    position_id INT NOT NULL COMMENT '岗位ID',
    course_id INT NOT NULL COMMENT '课程ID',
    course_type VARCHAR(20) NOT NULL DEFAULT 'required' COMMENT '课程类型：required必修/optional选修',
    priority INT DEFAULT 0 COMMENT '优先级/排序',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by INT COMMENT '创建人ID',
    updated_by INT COMMENT '更新人ID',
    is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
    deleted_at DATETIME,
    FOREIGN KEY (position_id) REFERENCES positions(id),
    FOREIGN KEY (course_id) REFERENCES courses(id),
    UNIQUE KEY uix_position_course (position_id, course_id, is_deleted),
    INDEX ix_position_courses_id (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='岗位课程关联表';

六、性能优化建议

  1. 分表策略

    • training_messages表可能增长较快，考虑按月分表
    • exam_results表可考虑按年分表

  2. 缓存策略

    • 用户信息使用Redis缓存
    • 课程列表使用Redis缓存
    • 热门题目使用Redis缓存

  3. 查询优化

    • 使用分页查询避免大量数据加载
    • 合理使用JOIN，避免N+1查询
    • 统计类查询考虑使用物化视图

七、初始化数据（轻医美连锁业务）

系统初始化时会插入以下业务数据：

7.1 默认用户账号

  • 超级管理员：superadmin / Superadmin123!
  • 系统管理员：admin / Admin123!
  • 测试学员：testuser / TestPass123!

7.2 轻医美连锁岗位体系

系统预置了完整的轻医美连锁岗位层级结构：

岗位层级树

区域经理 (region_manager) - expert级别
└── 店长 (store_manager) - senior级别
    ├── 美容顾问 (beauty_consultant) - intermediate级别
    ├── 医美咨询师 (medical_beauty_consultant) - senior级别
    ├── 美容技师 (beauty_therapist) - intermediate级别
    ├── 护士 (nurse) - intermediate级别
    ├── 前台接待 (receptionist) - junior级别
    └── 市场专员 (marketing_specialist) - intermediate级别

7.3 轻医美专业课程体系

技术类课程

  • 皮肤生理学基础 (16课时) - 学习皮肤结构、功能和常见问题
  • 医美产品知识与应用 (20课时) - 了解各类医美产品的成分和功效
  • 美容仪器操作与维护 (24课时) - 掌握美容仪器的操作方法
  • 医美项目介绍与咨询 (30课时) - 了解各类医美项目原理和效果

业务类课程

  • 轻医美销售技巧 (16课时) - 学习专业销售话术和成交技巧
  • 客户服务与投诉处理 (12课时) - 提升服务意识和投诉处理能力
  • 社媒营销与私域运营 (16课时) - 学习社交媒体品牌推广

管理类课程

  • 门店运营管理 (20课时) - 学习门店日常管理和团队建设

通用类课程

  • 卫生消毒与感染控制 (8课时) - 学习医美机构卫生标准

7.4 岗位课程配置示例

系统已预设各岗位的必修和选修课程：

  • 店长：门店运营管理、轻医美销售技巧、客户服务与投诉处理、卫生消毒与感染控制（全部必修）
  • 美容顾问：皮肤生理学基础、医美产品知识与应用、轻医美销售技巧、客户服务与投诉处理（必修）；社媒营销与私域运营（选修）
  • 美容技师：皮肤生理学基础、美容仪器操作与维护、卫生消毒与感染控制（必修）；医美产品知识与应用（选修）
  • 医美咨询师：医美项目介绍与咨询、皮肤生理学基础、医美产品知识与应用、轻医美销售技巧、客户服务与投诉处理（全部必修）

7.5 数据初始化脚本

  • 初始化SQL脚本：/scripts/init_database_unified.sql - 包含完整的表结构和初始数据
  • 轻医美种子数据脚本：/scripts/seed_beauty_data.py - 专门用于插入轻医美业务数据

八、系统配置模块

8.1 AI服务配置表 (ai_config)

2026-01-21 新增：遵循《瑞小美AI接入规范》，将 API Key 等敏感配置存储在数据库中

CREATE TABLE IF NOT EXISTS `ai_config` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `config_key` VARCHAR(100) NOT NULL UNIQUE COMMENT '配置键名',
    `config_value` TEXT COMMENT '配置值',
    `description` VARCHAR(255) COMMENT '配置说明',
    `is_encrypted` TINYINT(1) DEFAULT 0 COMMENT '是否加密存储',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='AI 服务配置表';

预置配置项：

config_key 说明 示例值
AI_PRIMARY_API_KEY 4sapi.com 通用 Key（Gemini/DeepSeek） sk-xxx...
AI_ANTHROPIC_API_KEY 4sapi.com Claude 专属 Key sk-xxx...
AI_PRIMARY_BASE_URL 首选服务商 API 地址 https://4sapi.com/v1
AI_FALLBACK_API_KEY OpenRouter 备选 Key sk-or-xxx...
AI_FALLBACK_BASE_URL 备选服务商 API 地址 https://openrouter.ai/api/v1
AI_DEFAULT_MODEL 默认 AI 模型 claude-opus-4-5-20251101-thinking
AI_TIMEOUT AI 请求超时时间（秒） 120

配置加载优先级：

  1. 数据库 ai_config 表（推荐）
  2. 环境变量（fallback）

使用说明：

  • AIService 初始化时自动从数据库读取配置
  • 支持运行时动态更新配置，无需重启服务
  • 遵循规范：禁止在代码中硬编码 API Key
Reference in New Issue View Git Blame Copy Permalink