Files
2026-09-03 15:47:50 +08:00

298 lines
19 KiB
SQL
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
-- ============================================================
-- Boss直聘 AI 投递系统 - 数据库初始化脚本(必须手动执行)
-- 与 server/models.py 保持一一对应,模型变更时需同步修改本脚本
--
-- 说明:
-- 1. 服务端【不自动建表】,数据库结构完全由本脚本控制
-- 2. 初始化方式:
-- a) Docker:全新数据卷首次启动时自动执行(已挂载到
-- /docker-entrypoint-initdb.d);已有数据卷需手动执行方式 b
-- b) 手动:docker exec -i jianli-boss-mysql mysql -uroot -p<密码> < docs/schema.sql
-- 3. 应用层时间统一写入 UTC(datetime.utcnow),DEFAULT CURRENT_TIMESTAMP
-- 仅对手工插入的数据生效
-- ============================================================
CREATE DATABASE IF NOT EXISTS jianli_boss
DEFAULT CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
USE jianli_boss;
-- ------------------------------------------------------------
-- 1. 系统用户(账号密码体系,身份与机器/指纹浏览器解耦)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(64) NOT NULL COMMENT '登录用户名',
password_hash VARCHAR(255) NOT NULL COMMENT '密码加盐哈希(PBKDF2,格式:盐$摘要),不存明文',
nickname VARCHAR(64) NULL COMMENT '客户端显示昵称',
client_code VARCHAR(128) NULL COMMENT '最近一次登录的设备指纹(仅审计/展示,非身份标识)',
is_active TINYINT(1) NOT NULL DEFAULT 1 COMMENT '账号是否启用',
boss_account VARCHAR(128) NULL COMMENT 'Boss直聘账号标识(登录成功后回填)',
login_status VARCHAR(20) NOT NULL DEFAULT 'not_login' COMMENT 'Boss登录状态:not_login/logging_in/logged_in',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
last_seen_at DATETIME NULL COMMENT '最后活跃时间(心跳刷新,≤90s 判定 RPA 在线)',
UNIQUE KEY uq_users_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='系统用户';
-- ------------------------------------------------------------
-- 2. 简历(一个用户多份,其中一份为主简历)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS resumes (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
boss_resume_id VARCHAR(64) NULL COMMENT 'Boss直聘简历ID',
title VARCHAR(128) NOT NULL COMMENT '简历标题',
position VARCHAR(128) NULL COMMENT '求职意向岗位',
content TEXT NULL COMMENT '简历全文(AI整理后的结构化文本)',
is_primary TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否为主简历(用于JD匹配)',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
KEY ix_resumes_user_id (user_id),
CONSTRAINT fk_resumes_user FOREIGN KEY (user_id)
REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='简历';
-- ------------------------------------------------------------
-- 3. 投递会话(一次"开始投递 → 结束投递"的完整过程)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS apply_sessions (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'running' COMMENT '会话状态:running/stopped/finished',
keywords VARCHAR(512) NULL COMMENT '搜索关键词(逗号分隔)',
city VARCHAR(20) NULL COMMENT '城市名称',
salary_min INT NULL COMMENT '最低薪资(K)',
salary_max INT NULL COMMENT '最高薪资(K)',
prompt TEXT NULL COMMENT 'AI评分引导词',
applied_count INT NOT NULL DEFAULT 0 COMMENT '本次会话投递数',
skipped_count INT NOT NULL DEFAULT 0 COMMENT '本次会话跳过数',
started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
ended_at DATETIME NULL,
KEY ix_apply_sessions_user_id (user_id),
CONSTRAINT fk_sessions_user FOREIGN KEY (user_id)
REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='投递会话';
-- ------------------------------------------------------------
-- 4. 投递记录(含公司/岗位/薪资/JD全文/打招呼语归档)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS job_applications (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
session_id INT NULL COMMENT '所属会话(会话删除时置NULL)',
job_id VARCHAR(64) NOT NULL COMMENT 'Boss直聘职位唯一ID',
job_title VARCHAR(128) NULL COMMENT '岗位名',
company VARCHAR(128) NULL COMMENT '公司名',
salary VARCHAR(32) NULL COMMENT '薪资(如 15-25K·14薪)',
location VARCHAR(64) NULL COMMENT '工作地点',
apply_type VARCHAR(20) NOT NULL COMMENT '投递方式:chat=立即沟通/deliver=投递简历/skipped=跳过',
ai_score INT NOT NULL DEFAULT -1 COMMENT 'AI匹配评分(-1=未评分)',
ai_reason VARCHAR(255) NULL COMMENT 'AI评分理由',
skip_reason VARCHAR(255) NULL COMMENT '跳过原因',
jd_text TEXT NULL COMMENT '职位JD全文(投递/跳过时归档;超长时本列留空、落盘到 jd_archive/,改存 jd_file 路径)',
jd_file VARCHAR(255) NULL COMMENT '超长JD归档文件路径(相对项目根目录,如 jd_archive/1_xxx.txt)',
greeting VARCHAR(500) NULL COMMENT '实际发送的打招呼语(仅chat方式有值)',
applied_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '投递时间',
UNIQUE KEY uq_user_job (user_id, job_id) COMMENT '同一用户不重复投递同一职位',
KEY ix_job_applications_job_id (job_id),
KEY idx_user_session (user_id, session_id),
CONSTRAINT fk_jobs_user FOREIGN KEY (user_id)
REFERENCES users (id) ON DELETE CASCADE,
CONSTRAINT fk_jobs_session FOREIGN KEY (session_id)
REFERENCES apply_sessions (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='投递记录';
-- ------------------------------------------------------------
-- 5. 投递条件模板(本地面板 + 远程控制共用)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS apply_templates (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
name VARCHAR(128) NOT NULL COMMENT '模板名称',
keywords VARCHAR(512) NULL COMMENT '搜索关键词(逗号分隔)',
city VARCHAR(20) NULL COMMENT '城市名称',
salary_min INT NULL COMMENT '最低薪资(K)',
salary_max INT NULL COMMENT '最高薪资(K)',
min_score INT NOT NULL DEFAULT 65 COMMENT 'AI最低匹配分',
daily_limit INT NOT NULL DEFAULT 0 COMMENT '每日投递上限(0=不限)',
boss_online_only TINYINT(1) NOT NULL DEFAULT 0 COMMENT '只投递Boss在线的职位',
allow_headhunter TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否考虑猎头职位',
prompt TEXT NULL COMMENT 'AI评分引导词',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
KEY ix_apply_templates_user_id (user_id),
CONSTRAINT fk_apply_templates_user FOREIGN KEY (user_id)
REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='投递条件模板';
-- ------------------------------------------------------------
-- 5. 公司黑名单(拉黑后 RPA 不再投递该公司)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS company_blacklist (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
company VARCHAR(128) NOT NULL COMMENT '公司名(与投递记录 company 精确匹配)',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '拉黑时间',
UNIQUE KEY uq_user_company (user_id, company),
KEY ix_blacklist_user_id (user_id),
CONSTRAINT fk_blacklist_user FOREIGN KEY (user_id)
REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='公司黑名单';
-- ------------------------------------------------------------
-- 6. RPA 状态快照(每用户一行,心跳携带全量状态上报)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS rpa_status (
user_id INT PRIMARY KEY,
boss_login VARCHAR(20) NOT NULL DEFAULT 'unknown' COMMENT 'Boss登录状态',
browser VARCHAR(20) NOT NULL DEFAULT 'unknown' COMMENT '浏览器状态',
apply_status VARCHAR(20) NOT NULL DEFAULT 'idle' COMMENT '投递状态 idle/running',
current VARCHAR(200) NULL COMMENT '当前进度文本',
applied INT NOT NULL DEFAULT 0 COMMENT '本次投递数',
skipped INT NOT NULL DEFAULT 0 COMMENT '本次跳过数',
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
CONSTRAINT fk_rpa_status_user FOREIGN KEY (user_id)
REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='RPA 状态快照';
-- ------------------------------------------------------------
-- 7. 远程控制任务(云端下发 → 客户端轮询执行 → 回调更新)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS rpa_task (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
task_type VARCHAR(20) NOT NULL COMMENT 'start_apply/stop_apply',
params TEXT NULL COMMENT '任务参数 JSON',
status VARCHAR(20) NOT NULL DEFAULT 'pending' COMMENT 'pending/running/done/failed/expired',
result VARCHAR(500) NULL COMMENT '执行结果',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
KEY ix_rpa_task_user (user_id),
KEY ix_rpa_task_user_status (user_id, status),
CONSTRAINT fk_rpa_task_user FOREIGN KEY (user_id)
REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='远程控制任务';
-- ------------------------------------------------------------
-- 7b. 定时任务排程(远程控制页创建,服务端调度器每日到点自动下发 rpa_task)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS rpa_schedule (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
kind VARCHAR(20) NOT NULL COMMENT 'apply=自动投递/activate=Boss激活',
params TEXT NULL COMMENT '任务参数 JSON(投递条件/激活参数)',
start_time VARCHAR(5) NOT NULL COMMENT '每日开始时间 HH:MM(北京时间)',
end_time VARCHAR(5) NOT NULL COMMENT '每日结束时间 HH:MM(北京时间)',
enabled TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否启用',
last_start_date VARCHAR(10) NULL COMMENT '最近开始下发日期(北京 YYYY-MM-DD,防当日重复)',
last_stop_date VARCHAR(10) NULL COMMENT '最近结束下发日期(北京 YYYY-MM-DD,防当日重复)',
last_result VARCHAR(255) NULL COMMENT '最近一次调度结果说明',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
KEY ix_rpa_schedule_user (user_id),
CONSTRAINT fk_rpa_schedule_user FOREIGN KEY (user_id)
REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='定时任务排程';
-- ------------------------------------------------------------
-- 8. AI 模型配置(全局单行,id=1,/view 管理页在线维护)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS ai_config (
id INT NOT NULL DEFAULT 1 PRIMARY KEY,
base_url VARCHAR(255) NULL COMMENT 'OpenAI兼容接口地址,如 https://api.siliconflow.cn/v1',
api_key VARCHAR(255) NULL COMMENT '模型 API Key',
model VARCHAR(128) NULL COMMENT '模型名,如 deepseek-ai/DeepSeek-V3',
timeout_s INT NOT NULL DEFAULT 300 COMMENT '服务端调用大模型超时(秒)',
enabled TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否启用AI代理',
updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='AI模型配置(全局单行)';
-- ------------------------------------------------------------
-- 9. AI 打分任务(客户端异步评分:开始打分 + 轮询结果)
-- request_key 为客户端 uuid1().hex(MAC+时间戳,32 位),故 VARCHAR(32)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS ai_score_task (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
request_key VARCHAR(32) NOT NULL COMMENT '幂等键:客户端uuid1().hex(MAC+时间戳,32位)',
resume_id INT NULL COMMENT '主简历ID(服务端从库中取内容)',
job_ref VARCHAR(64) NULL COMMENT 'Boss职位ID(审计追踪用)',
jd_text TEXT NOT NULL COMMENT '职位JD全文(评分输入)',
conditions TEXT NULL COMMENT '投递条件JSON:keywords/city/salary_min/salary_max/prompt',
status VARCHAR(20) NOT NULL DEFAULT 'pending' COMMENT 'pending/running/success/failed',
score INT NULL COMMENT '成功时的匹配分(0-100)',
reason VARCHAR(512) NULL COMMENT '成功时一句话评价;失败时失败原因',
error TEXT NULL COMMENT '详细错误信息(调试用)',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
finished_at DATETIME NULL,
UNIQUE KEY uq_user_request_key (user_id, request_key),
KEY idx_user_created (user_id, created_at),
CONSTRAINT fk_ai_score_task_user FOREIGN KEY (user_id)
REFERENCES users (id) ON DELETE CASCADE,
CONSTRAINT fk_ai_score_task_resume FOREIGN KEY (resume_id)
REFERENCES resumes (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='AI打分任务(客户端异步评分)';
-- -- ============================================================
-- -- 附:老库升级脚本(已建过表、缺少新增字段时执行)
-- -- ============================================================
-- ALTER TABLE job_applications
-- ADD COLUMN jd_text TEXT NULL COMMENT '职位JD全文',
-- ADD COLUMN greeting VARCHAR(500) NULL COMMENT '实际发送的打招呼语';
--
-- 老库新增公司黑名单表:
-- CREATE TABLE IF NOT EXISTS company_blacklist (
-- id INT AUTO_INCREMENT PRIMARY KEY,
-- user_id INT NOT NULL,
-- company VARCHAR(128) NOT NULL,
-- created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
-- UNIQUE KEY uq_user_company (user_id, company),
-- KEY ix_blacklist_user_id (user_id),
-- CONSTRAINT fk_blacklist_user FOREIGN KEY (user_id)
-- REFERENCES users (id) ON DELETE CASCADE
-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- v1.11 老库升级(AI 服务端代理:ai_config + ai_score_task 两表),
-- 完整可重复执行版本见 docs/upgrade_1.11.sql。
-- v1.18 老库升级(job_applications 新增 jd_file:超长 JD 落盘归档路径),
-- 完整可重复执行版本见 docs/upgrade_jd_file.sql。
-- 老库城市字段改名(城市代码 → 城市名称):
ALTER TABLE apply_sessions
CHANGE COLUMN city_code city VARCHAR(20) NULL COMMENT '城市名称';
-- v1.8 老库升级(引导词 + 投递模板),完整可重复执行版本见 docs/upgrade_1.8.sql:
-- ALTER TABLE apply_sessions
-- ADD COLUMN prompt TEXT NULL COMMENT 'AI评分引导词' AFTER salary_max;
-- CREATE TABLE IF NOT EXISTS apply_templates (
-- id INT AUTO_INCREMENT PRIMARY KEY,
-- user_id INT NOT NULL,
-- name VARCHAR(128) NOT NULL COMMENT '模板名称',
-- keywords VARCHAR(512) NULL,
-- city VARCHAR(20) NULL,
-- salary_min INT NULL,
-- salary_max INT NULL,
-- min_score INT NOT NULL DEFAULT 65,
-- daily_limit INT NOT NULL DEFAULT 0,
-- boss_online_only TINYINT(1) NOT NULL DEFAULT 0,
-- allow_headhunter TINYINT(1) NOT NULL DEFAULT 0,
-- prompt TEXT NULL,
-- created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
-- updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-- KEY ix_apply_templates_user_id (user_id),
-- CONSTRAINT fk_apply_templates_user FOREIGN KEY (user_id)
-- REFERENCES users (id) ON DELETE CASCADE
-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;