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

331 lines
14 KiB
Python
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.
"""
SQLAlchemy ORM 模型定义
所有业务表均带 user_id 外键,实现用户数据隔离
"""
from datetime import datetime
from sqlalchemy import (
Column, Integer, BigInteger, String, Text, Boolean,
DateTime, Float, ForeignKey, Index, UniqueConstraint,
)
from sqlalchemy.orm import relationship
from .database import Base
def utcnow() -> datetime:
return datetime.utcnow()
class User(Base):
"""
系统用户
以用户名(username)+ 密码哈希作为身份,与机器/指纹浏览器解耦
"""
__tablename__ = "users"
id = Column(Integer, primary_key=True, autoincrement=True)
# 登录用户名(唯一)
username = Column(String(64), unique=True, nullable=False, index=True)
# 密码加盐哈希(PBKDF2,格式:盐$摘要),不存明文
password_hash = Column(String(255), nullable=False)
# 客户端显示昵称(可选)
nickname = Column(String(64), nullable=True)
# 最近一次登录的客户端设备指纹(仅作审计/展示,非身份标识)
client_code = Column(String(128), nullable=True)
# 账号是否启用
is_active = Column(Boolean, default=True, nullable=False)
# Boss直聘 账号标识(登录成功后回填,用于区分同一 Boss 账号)
boss_account = Column(String(128), nullable=True)
# 登录状态:not_login / logging_in / logged_in
login_status = Column(String(20), default="not_login", nullable=False)
created_at = Column(DateTime, default=utcnow, nullable=False)
last_seen_at = Column(DateTime, default=utcnow, onupdate=utcnow)
# 关联
resumes = relationship("Resume", back_populates="user", cascade="all, delete-orphan")
applications = relationship("JobApplication", back_populates="user", cascade="all, delete-orphan")
sessions = relationship("ApplySession", back_populates="user", cascade="all, delete-orphan")
blacklist = relationship("CompanyBlacklist", back_populates="user", cascade="all, delete-orphan")
templates = relationship("ApplyTemplate", back_populates="user", cascade="all, delete-orphan")
class Resume(Base):
"""
简历
一个用户可有多份简历,其中一份标记为主简历
"""
__tablename__ = "resumes"
id = Column(Integer, primary_key=True, autoincrement=True)
user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True)
# Boss直聘 简历 ID
boss_resume_id = Column(String(64), nullable=True)
# 简历标题
title = Column(String(128), nullable=False)
# 求职意向岗位
position = Column(String(128), nullable=True)
# 简历全文(结构化或纯文本)
content = Column(Text, nullable=True)
# 是否为主简历
is_primary = Column(Boolean, default=False, nullable=False)
created_at = Column(DateTime, default=utcnow, nullable=False)
updated_at = Column(DateTime, default=utcnow, onupdate=utcnow)
user = relationship("User", back_populates="resumes")
class ApplySession(Base):
"""
投递会话
记录一次"开始投递 → 结束投递"的完整过程
"""
__tablename__ = "apply_sessions"
id = Column(Integer, primary_key=True, autoincrement=True)
user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True)
# 会话状态:running / stopped / finished
status = Column(String(20), default="running", nullable=False)
# 搜索关键词(逗号分隔)
keywords = Column(String(512), nullable=True)
# 城市
city = Column(String(20), nullable=True)
# 薪资范围
salary_min = Column(Integer, nullable=True)
salary_max = Column(Integer, nullable=True)
# AI 评分引导词(用户自定义补充偏好)
prompt = Column(Text, nullable=True)
# 本次会话投递数量统计
applied_count = Column(Integer, default=0)
skipped_count = Column(Integer, default=0)
started_at = Column(DateTime, default=utcnow, nullable=False)
ended_at = Column(DateTime, nullable=True)
user = relationship("User", back_populates="sessions")
applications = relationship("JobApplication", back_populates="session", cascade="all, delete-orphan")
class JobApplication(Base):
"""
投递记录
"""
__tablename__ = "job_applications"
__table_args__ = (
# 同一用户不重复投递同一职位
UniqueConstraint("user_id", "job_id", name="uq_user_job"),
Index("idx_user_session", "user_id", "session_id"),
)
id = Column(Integer, primary_key=True, autoincrement=True)
user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False)
session_id = Column(Integer, ForeignKey("apply_sessions.id", ondelete="SET NULL"), nullable=True)
# Boss直聘 职位 ID
job_id = Column(String(64), nullable=False, index=True)
job_title = Column(String(128), nullable=True)
company = Column(String(128), nullable=True)
salary = Column(String(32), nullable=True)
location = Column(String(64), nullable=True)
# 投递方式:chat=立即沟通 / deliver=投递简历 / skipped=跳过
apply_type = Column(String(20), nullable=False)
# AI 匹配评分(-1 表示未评分)
ai_score = Column(Integer, default=-1)
ai_reason = Column(String(255), nullable=True)
# 跳过原因
skip_reason = Column(String(255), nullable=True)
# 职位 JD 全文(投递/跳过时归档,供复盘与面试准备);
# 超过 JD_TEXT_LIMIT 的超长 JD 不入本列,落盘到项目根目录 jd_archive/,本列留空、改存 jd_file 路径
jd_text = Column(Text, nullable=True)
# 超长 JD 归档文件路径(相对项目根目录,如 jd_archive/1_0daaa9fd.txt)
jd_file = Column(String(255), nullable=True)
# 实际发送的打招呼语(仅 chat 方式有值)
greeting = Column(String(500), nullable=True)
applied_at = Column(DateTime, default=utcnow, nullable=False)
user = relationship("User", back_populates="applications")
session = relationship("ApplySession", back_populates="applications")
class CompanyBlacklist(Base):
"""
公司黑名单
拉黑后 RPA 不再投递该公司的任何职位
"""
__tablename__ = "company_blacklist"
__table_args__ = (
# 同一用户不重复拉黑同一公司
UniqueConstraint("user_id", "company", name="uq_user_company"),
)
id = Column(Integer, primary_key=True, autoincrement=True)
user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True)
# 公司名(与投递记录中的 company 精确匹配)
company = Column(String(128), nullable=False)
created_at = Column(DateTime, default=utcnow, nullable=False)
user = relationship("User", back_populates="blacklist")
class RpaStatus(Base):
"""
客户端 RPA 状态快照(每用户一行,单设备不做设备区分)。
心跳每次携带全量状态上报:Boss 登录 / 浏览器 / 投递状态。
"""
__tablename__ = "rpa_status"
user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"),
primary_key=True)
# Boss 直聘登录状态:not_login / logging_in / logged_in / unknown
boss_login = Column(String(20), default="unknown", nullable=False)
# 指纹浏览器状态:running / stopped / unknown
browser = Column(String(20), default="unknown", nullable=False)
# 投递状态:idle / running
apply_status = Column(String(20), default="idle", nullable=False)
# 当前进度文本(如“评估:xx公司 - xx职位”)
current = Column(String(200), nullable=True)
applied = Column(Integer, default=0, nullable=False)
skipped = Column(Integer, default=0, nullable=False)
updated_at = Column(DateTime, default=utcnow, onupdate=utcnow, nullable=False)
user = relationship("User")
class RpaTask(Base):
"""
远程控制任务(云端下发 → 客户端轮询执行 → 回调更新状态)。
状态机:pending → running → done/failed;pending 超时 → expired。
去重:同类型任务未达终态(done/failed/expired)时拒绝重复下发。
"""
__tablename__ = "rpa_task"
id = Column(Integer, primary_key=True, autoincrement=True)
user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"),
nullable=False, index=True)
# start_apply / stop_apply
task_type = Column(String(20), nullable=False)
# 任务参数 JSON(start_apply 的投递参数)
params = Column(Text, nullable=True)
# pending / running / done / failed / expired
status = Column(String(20), default="pending", nullable=False)
result = Column(String(500), nullable=True)
created_at = Column(DateTime, default=utcnow, nullable=False)
updated_at = Column(DateTime, default=utcnow, onupdate=utcnow, nullable=False)
user = relationship("User")
class RpaSchedule(Base):
"""
定时任务排程(远程控制页创建,服务端调度器每日到点自动下发 RpaTask)。
kind:apply=自动投递 / activate=Boss 激活;时间均为北京时间 HH:MM。
防重复:last_start_date / last_stop_date 记录当日已下发日期,每类动作每天只触发一次。
"""
__tablename__ = "rpa_schedule"
id = Column(Integer, primary_key=True, autoincrement=True)
user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"),
nullable=False, index=True)
# apply / activate
kind = Column(String(20), nullable=False)
# 任务参数 JSON:apply=投递条件;activate=激活参数(match_text/status_filter/threshold/reply_message/max_count/dry_run)
params = Column(Text, nullable=True)
# 每日开始/结束时间 HH:MM(北京时间),结束时间到点自动下发对应 stop 任务
start_time = Column(String(5), nullable=False)
end_time = Column(String(5), nullable=False)
enabled = Column(Boolean, default=True, nullable=False)
# 最近一次开始/结束下发日期(北京日期 YYYY-MM-DD,防当日重复触发)
last_start_date = Column(String(10), nullable=True)
last_stop_date = Column(String(10), nullable=True)
# 最近一次调度结果说明(已下发任务#ID / 跳过原因)
last_result = Column(String(255), nullable=True)
created_at = Column(DateTime, default=utcnow, nullable=False)
class ApplyTemplate(Base):
"""
投递条件模板
用户保存常用投递配置,支持在本地面板和远程控制面板套用。
"""
__tablename__ = "apply_templates"
id = Column(Integer, primary_key=True, autoincrement=True)
user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"),
nullable=False, index=True)
# 模板名称(用户自定义)
name = Column(String(128), nullable=False)
# 搜索关键词(逗号分隔)
keywords = Column(String(512), nullable=True)
# 城市
city = Column(String(20), nullable=True)
# 薪资范围
salary_min = Column(Integer, nullable=True)
salary_max = Column(Integer, nullable=True)
# AI 最低匹配分
min_score = Column(Integer, default=65)
# 每日投递上限(0=不限)
daily_limit = Column(Integer, default=0)
# 是否只投递 Boss 在线的职位
boss_online_only = Column(Boolean, default=False, nullable=False)
# 是否考虑猎头职位
allow_headhunter = Column(Boolean, default=False, nullable=False)
# AI 评分引导词
prompt = Column(Text, nullable=True)
created_at = Column(DateTime, default=utcnow, nullable=False)
updated_at = Column(DateTime, default=utcnow, onupdate=utcnow, nullable=False)
user = relationship("User", back_populates="templates")
class AIConfig(Base):
"""
AI 模型配置(全局单行,id=1)
OpenAI 兼容接口,由管理页面(/view)在线维护,
客户端不再持有任何模型密钥。
"""
__tablename__ = "ai_config"
id = Column(Integer, primary_key=True, default=1)
# OpenAI 兼容接口地址,如 https://api.siliconflow.cn/v1
base_url = Column(String(255), nullable=True)
api_key = Column(String(255), nullable=True)
model = Column(String(128), nullable=True)
# 服务端调用大模型的超时(秒)
timeout_s = Column(Integer, default=300, nullable=False)
# 是否启用 AI 代理(关闭后客户端评分请求直接返回未配置)
enabled = Column(Boolean, default=True, nullable=False)
updated_at = Column(DateTime, default=utcnow, onupdate=utcnow)
class AIScoreTask(Base):
"""
AI 打分任务(客户端异步评分)
客户端发起打分 → 服务端后台调用大模型 → 结果落表 → 客户端轮询取结果。
request_key 为客户端生成的幂等键(uuid1 hex,MAC+时间戳,32 位),
重复提交同键直接返回已有任务,不会重复调用大模型。
"""
__tablename__ = "ai_score_task"
__table_args__ = (
# 同一用户内幂等键唯一(防重复调用大模型)
UniqueConstraint("user_id", "request_key", name="uq_user_request_key"),
Index("idx_user_created", "user_id", "created_at"),
)
id = Column(Integer, primary_key=True, autoincrement=True)
user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False)
# 幂等键:客户端 uuid1().hex,32 位十六进制字符串,故 VARCHAR(32)
request_key = Column(String(32), nullable=False)
resume_id = Column(Integer, ForeignKey("resumes.id", ondelete="SET NULL"), nullable=True)
# 客户端职位标识(Boss job_id,仅审计追踪用)
job_ref = Column(String(64), nullable=True)
# 职位 JD 全文(评分输入)
jd_text = Column(Text, nullable=False)
# 投递条件 JSON:keywords/city/salary_min/salary_max/prompt
conditions = Column(Text, nullable=True)
# pending / running / success / failed
status = Column(String(20), default="pending", nullable=False)
score = Column(Integer, nullable=True)
# 成功时一句话评价;失败时失败原因(客户端据此跳岗)
reason = Column(String(512), nullable=True)
# 详细错误信息(调试用)
error = Column(Text, nullable=True)
created_at = Column(DateTime, default=utcnow, nullable=False)
finished_at = Column(DateTime, nullable=True)