"""字段 comment 作为数据字典来源；历史记录用外键保留而非级联删除。""" from datetime import datetime from typing import Any from sqlalchemy import Boolean, DateTime, ForeignKey, Integer, JSON, String, Text, UniqueConstraint from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column from app.shared.common import utcnow class Base(DeclarativeBase): pass class Identity: id: Mapped[int] = mapped_column(primary_key=True, comment="本地整数主键，不接受客户端指定") created_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow, comment="创建时间；保存 UTC 以便跨时区排序") class Department(Identity, Base): __tablename__ = "department" name: Mapped[str] = mapped_column(String(100), unique=True, comment="部门名称；唯一，避免统计重复归类") active: Mapped[bool] = mapped_column(Boolean, default=True, comment="停用标记；保留历史外键") class Role(Identity, Base): __tablename__ = "role" code: Mapped[str] = mapped_column(String(20), unique=True, comment="五种固定角色代码") display_name: Mapped[str] = mapped_column(String(30), comment="中文角色名称") class User(Identity, Base): __tablename__ = "sys_user" username: Mapped[str] = mapped_column(String(50), unique=True, comment="登录名；唯一且不含密码") real_name: Mapped[str] = mapped_column(String(80), comment="显示姓名") password_hash: Mapped[str] = mapped_column(Text, comment="带盐 scrypt 哈希；绝不返回客户端") department_id: Mapped[int] = mapped_column(ForeignKey("department.id"), comment="所属部门；必须存在") role_id: Mapped[int] = mapped_column(ForeignKey("role.id"), comment="所属角色；固定权限边界") active: Mapped[bool] = mapped_column(Boolean, default=True, comment="停用后现有令牌也不能访问") class RefreshSession(Identity, Base): __tablename__ = "refresh_session" user_id: Mapped[int] = mapped_column(ForeignKey("sys_user.id"), comment="令牌持有人") token_hash: Mapped[str] = mapped_column(String(64), unique=True, comment="只存刷新令牌 SHA256，防止数据库泄露直接登录") expires_at: Mapped[datetime] = mapped_column(DateTime, comment="刷新会话到期 UTC") revoked_at: Mapped[datetime | None] = mapped_column(DateTime, comment="撤销时间；轮换或退出时写入") class AgentConfig(Identity, Base): __tablename__ = "agent_config" __table_args__ = (UniqueConstraint("node_name", "version"),) node_name: Mapped[str] = mapped_column(String(40), comment="固定图节点标识；与流程注册名一致") name: Mapped[str] = mapped_column(String(80), comment="界面中文名称") enabled: Mapped[bool] = mapped_column(Boolean, default=True, comment="可选模型/反馈能力开关，不允许关闭核心判定") version: Mapped[int] = mapped_column(Integer, default=1, comment="不可变版本；修改生成新记录") config_json: Mapped[dict] = mapped_column(JSON, default=dict, comment="经过白名单校验的运行参数；无密钥") class AuditRule(Identity, Base): __tablename__ = "audit_rule" __table_args__ = (UniqueConstraint("code", "version"),) code: Mapped[str] = mapped_column(String(60), comment="七类内置规则代码") name: Mapped[str] = mapped_column(String(80), comment="规则中文名称") version: Mapped[int] = mapped_column(Integer, default=1, comment="不可变配置版本，用于历史回放") enabled: Mapped[bool] = mapped_column(Boolean, default=True, comment="可选规则开关，核心检查不可绕过") severity: Mapped[str] = mapped_column(String(12), default="warning", comment="warning/error/critical，规则具体分支可升级") scope_json: Mapped[dict] = mapped_column(JSON, default=dict, comment="适用类型和部门；核心检查不允许缩小范围") params_json: Mapped[dict] = mapped_column(JSON, default=dict, comment="数值阈值和格式参数；禁止可执行表达式") class Reimbursement(Identity, Base): __tablename__ = "reimbursement" number: Mapped[str] = mapped_column(String(30), unique=True, comment="BX+日期+六位序号；唯一约束防并发重复") user_id: Mapped[int] = mapped_column(ForeignKey("sys_user.id"), comment="提交员工；用于所有权与禁止自审") department_id: Mapped[int] = mapped_column(ForeignKey("department.id"), comment="创建时部门快照，历史归属不会随用户调岗变化") type: Mapped[int] = mapped_column(Integer, comment="1差旅/2招待/3办公") title: Mapped[str] = mapped_column(String(200), comment="报销说明标题") description: Mapped[str] = mapped_column(Text, default="", comment="补充业务说明") total_cents: Mapped[int] = mapped_column(Integer, comment="含税申请额，以整数分保存避免浮点误差") status: Mapped[str] = mapped_column(String(30), default="draft", index=True, comment="白名单状态机控制，终态不可回滚") revision: Mapped[int] = mapped_column(Integer, default=1, comment="编辑/提交版本，后台旧任务不得回写新版本") submitted_at: Mapped[datetime | None] = mapped_column(DateTime, comment="最近实际提交时间，统计近期频率") class Invoice(Identity, Base): __tablename__ = "invoice" reimbursement_id: Mapped[int] = mapped_column(ForeignKey("reimbursement.id"), index=True, comment="归属报销单") filename: Mapped[str] = mapped_column(String(250), comment="原始显示文件名，不作为存储路径") file_key: Mapped[str] = mapped_column(String(100), comment="受控随机文件键，防路径遍历") file_hash: Mapped[str] = mapped_column(String(64), index=True, comment="实际字节 SHA256；只检测同内容重复") content_type: Mapped[str] = mapped_column(String(40), comment="魔数验证后的 MIME") active: Mapped[bool] = mapped_column(Boolean, default=True, comment="移除采用停用，历史规则结果和原始文件仍可追溯") declared_fields: Mapped[dict] = mapped_column(JSON, default=dict, comment="用户补录来源，不覆盖历史模型提取快照") net_cents: Mapped[int | None] = mapped_column(Integer, comment="已填报未税金额，null 表示未知") tax_cents: Mapped[int | None] = mapped_column(Integer, comment="已填报税额，null 不等同零税") class SequenceCounter(Base): __tablename__ = "sequence_counter" date_key: Mapped[str] = mapped_column(String(8), primary_key=True, comment="业务时区日期，按日重置序号") value: Mapped[int] = mapped_column(Integer, comment="事务内原子递增；不能超过六位") class AuditRun(Identity, Base): __tablename__ = "audit_run" __table_args__ = (UniqueConstraint("reimbursement_id", "run_no"),) reimbursement_id: Mapped[int] = mapped_column(ForeignKey("reimbursement.id"), index=True, comment="被审核单据") revision: Mapped[int] = mapped_column(Integer, comment="提交时版本，后台写入前再次比较") run_no: Mapped[int] = mapped_column(Integer, comment="历史轮次，不覆盖旧结果") status: Mapped[str] = mapped_column(String(20), default="queued", comment="queued/running/completed/failed/cancelled；不是路由枚举") snapshot_json: Mapped[dict] = mapped_column(JSON, default=dict, comment="输入、规则、Agent、政策与统计时点快照") result_json: Mapped[dict] = mapped_column(JSON, default=dict, comment="完整结果，保留两个风险引擎与降级信息") decision: Mapped[str | None] = mapped_column(String(30), comment="四种确定性路由；未完成为 null") risk_level: Mapped[int] = mapped_column(Integer, default=0, comment="0未评估/1低/2中/3高") compliance_status: Mapped[str | None] = mapped_column(String(10), comment="pass/partial/fail；未知为 null") started_at: Mapped[datetime | None] = mapped_column(DateTime, comment="实际开始 UTC") finished_at: Mapped[datetime | None] = mapped_column(DateTime, comment="实际结束 UTC") error_code: Mapped[str | None] = mapped_column(String(80), comment="稳定错误码，不向界面暴露敏感异常") class AgentExecution(Identity, Base): __tablename__ = "agent_execution" __table_args__ = (UniqueConstraint("audit_id", "node_name", "attempt"),) audit_id: Mapped[int] = mapped_column(ForeignKey("audit_run.id"), index=True, comment="关联审核") node_name: Mapped[str] = mapped_column(String(40), comment="节点标识") attempt: Mapped[int] = mapped_column(Integer, comment="实际调用序号，首个为1") status: Mapped[str] = mapped_column(String(20), comment="success/degraded/failed/skipped") duration_ms: Mapped[int] = mapped_column(Integer, comment="真实计时毫秒") input_json: Mapped[dict] = mapped_column(JSON, default=dict, comment="必要输入摘要；不包含文件 base64 或密钥") output_json: Mapped[dict] = mapped_column(JSON, default=dict, comment="节点结果与证据") error_code: Mapped[str | None] = mapped_column(String(80), comment="错误或降级原因") token_usage: Mapped[dict] = mapped_column(JSON, default=dict, comment="提供方实际返回的 token 统计，未提供时为空") class RuleEvaluation(Identity, Base): __tablename__ = "rule_evaluation" audit_id: Mapped[int] = mapped_column(ForeignKey("audit_run.id"), index=True, comment="所属审核历史") invoice_id: Mapped[int | None] = mapped_column(ForeignKey("invoice.id"), comment="逐发票检查关联；合计检查为空") rule_code: Mapped[str] = mapped_column(String(80), comment="规则 code") rule_version: Mapped[int] = mapped_column(Integer, comment="当时版本") passed: Mapped[bool] = mapped_column(Boolean, comment="业务是否通过；跳过仍独立标识") severity: Mapped[str] = mapped_column(String(12), comment="未通过项的严重程度") execution_status: Mapped[str] = mapped_column(String(20), comment="success/skipped/failed/partial") message: Mapped[str] = mapped_column(Text, comment="人可读依据，避免只有布尔结论") details_json: Mapped[dict] = mapped_column(JSON, default=dict, comment="阈值、实际值、政策出处") class ApprovalChain(Identity, Base): __tablename__ = "approval_chain" name: Mapped[str] = mapped_column(String(100), comment="审批链名称") route: Mapped[str] = mapped_column(String(30), comment="to_supervisor/to_finance/to_manual") type: Mapped[int | None] = mapped_column(Integer, comment="报销类型，空为不限") department_id: Mapped[int | None] = mapped_column(ForeignKey("department.id"), comment="部门，空为不限") amount_min_cents: Mapped[int | None] = mapped_column(Integer, comment="金额下限，空为不限") amount_max_cents: Mapped[int | None] = mapped_column(Integer, comment="金额上限，空为不限") priority: Mapped[int] = mapped_column(Integer, default=0, comment="同明确程度时的配置优先级") nodes_json: Mapped[list] = mapped_column(JSON, comment="顺序审批人数组，必须非空") version: Mapped[int] = mapped_column(Integer, default=1, comment="配置版本，执行中使用冻结快照") active: Mapped[bool] = mapped_column(Boolean, default=True, comment="停用不影响已启动轮次") class ApprovalRound(Identity, Base): __tablename__ = "approval_round" reimbursement_id: Mapped[int] = mapped_column(ForeignKey("reimbursement.id"), comment="审批对象") revision: Mapped[int] = mapped_column(Integer, comment="当前提交版本") audit_id: Mapped[int] = mapped_column(ForeignKey("audit_run.id"), unique=True, comment="每个审核只启动一个审批轮次") chain_snapshot: Mapped[dict] = mapped_column(JSON, comment="审批链冻结，配置修改不改变在途任务") status: Mapped[str] = mapped_column(String(20), default="active", comment="active/approved/rejected/returned") class ApprovalRecord(Identity, Base): __tablename__ = "approval_record" __table_args__ = (UniqueConstraint("round_id", "node_index"),) round_id: Mapped[int] = mapped_column(ForeignKey("approval_round.id"), index=True, comment="明确所属轮次，不能复用旧记录") node_index: Mapped[int] = mapped_column(Integer, comment="从0开始的链中位置") approver_id: Mapped[int | None] = mapped_column(ForeignKey("sys_user.id"), comment="指定审批人，系统自动批准为空") result: Mapped[str | None] = mapped_column(String(20), comment="null待办；approve/reject/return") opinion: Mapped[str] = mapped_column(Text, default="", comment="审批理由") decided_at: Mapped[datetime | None] = mapped_column(DateTime, comment="签署 UTC") class KnowledgeDocument(Identity, Base): __tablename__ = "knowledge_document" title: Mapped[str] = mapped_column(String(200), comment="政策名称，不使用模型编造标题") category: Mapped[str] = mapped_column(String(40), default="policy", comment="政策分类用于检索过滤") version: Mapped[int] = mapped_column(Integer, default=1, comment="修改正文递增版本") content: Mapped[str] = mapped_column(Text, comment="完整原文") active: Mapped[bool] = mapped_column(Boolean, default=True, comment="只有启用政策参与当前检索") index_status: Mapped[str] = mapped_column(String(20), default="not_indexed", comment="not_indexed/queued/indexed/failed") embedding_model: Mapped[str | None] = mapped_column(String(100), comment="实际建索引的模型；查询必须一致") class KnowledgeChunk(Identity, Base): __tablename__ = "knowledge_chunk" document_id: Mapped[int] = mapped_column(ForeignKey("knowledge_document.id"), index=True, comment="原文来源") document_version: Mapped[int] = mapped_column(Integer, comment="块对应政策版本") position: Mapped[int] = mapped_column(Integer, comment="原文分块顺序") text: Mapped[str] = mapped_column(Text, comment="原始片段，留作审计引用") vector_json: Mapped[list | None] = mapped_column(JSON, comment="真实向量；未索引为 null") dimension: Mapped[int | None] = mapped_column(Integer, comment="实际向量维度；必须一致") class OutboxEvent(Identity, Base): __tablename__ = "outbox_event" event_type: Mapped[str] = mapped_column(String(30), comment="audit/approval/index") business_key: Mapped[str] = mapped_column(String(160), unique=True, comment="业务幂等键，与变更同事务写入") payload_json: Mapped[dict] = mapped_column(JSON, comment="版本和资源 ID，不包含凭据") status: Mapped[str] = mapped_column(String(20), default="pending", index=True, comment="pending/processing/done/failed") attempts: Mapped[int] = mapped_column(Integer, default=0, comment="消费者领取次数") lease_until: Mapped[datetime | None] = mapped_column(DateTime, comment="崩溃后可回收的租约") next_attempt_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow, comment="指数退避后的下次处理时点") error_code: Mapped[str | None] = mapped_column(String(80), comment="持久失败原因") class Alert(Identity, Base): __tablename__ = "alert" dedup_key: Mapped[str] = mapped_column(String(160), unique=True, comment="同异常去重，不每次扫描重复创建") type: Mapped[str] = mapped_column(String(40), comment="风险、降级、失败、超时等分类") severity: Mapped[str] = mapped_column(String(12), comment="warning/error/critical") audit_id: Mapped[int | None] = mapped_column(ForeignKey("audit_run.id"), comment="相关审核，可空") reimbursement_id: Mapped[int | None] = mapped_column(ForeignKey("reimbursement.id"), comment="相关单据，可空") status: Mapped[str] = mapped_column(String(20), default="open", comment="open/acknowledged/resolved") message: Mapped[str] = mapped_column(Text, comment="可读原因") ack_by: Mapped[int | None] = mapped_column(ForeignKey("sys_user.id"), comment="确认人") resolved_by: Mapped[int | None] = mapped_column(ForeignKey("sys_user.id"), comment="解决人") acknowledged_at: Mapped[datetime | None] = mapped_column(DateTime, comment="确认时间") resolved_at: Mapped[datetime | None] = mapped_column(DateTime, comment="解决时间") opinion: Mapped[str] = mapped_column(Text, default="", comment="处理意见，不改写原风险") class OperationLog(Identity, Base): __tablename__ = "operation_log" actor_id: Mapped[int | None] = mapped_column(ForeignKey("sys_user.id"), comment="操作人，后台系统事件为空") action: Mapped[str] = mapped_column(String(80), index=True, comment="稳定事件名称用于筛选") resource_type: Mapped[str] = mapped_column(String(40), comment="资源类型") resource_id: Mapped[int | None] = mapped_column(Integer, comment="资源 ID，不做跨类型外键") request_id: Mapped[str] = mapped_column(String(80), comment="请求追踪 ID") trace_id: Mapped[str] = mapped_column(String(80), index=True, comment="全流程追踪标识") before_json: Mapped[dict] = mapped_column(JSON, default=dict, comment="变更前必要快照，敏感项脱敏") after_json: Mapped[dict] = mapped_column(JSON, default=dict, comment="变更后必要快照，敏感项脱敏") class HumanFeedback(Identity, Base): __tablename__ = "human_feedback" audit_id: Mapped[int] = mapped_column(ForeignKey("audit_run.id"), comment="被纠正的审核") approval_record_id: Mapped[int] = mapped_column(ForeignKey("approval_record.id"), comment="必须关联本人已完成的人工审批") actor_id: Mapped[int] = mapped_column(ForeignKey("sys_user.id"), comment="反馈人") corrected_fields: Mapped[dict] = mapped_column(JSON, comment="独立反馈不篡改原始审核证据") reason: Mapped[str] = mapped_column(Text, comment="人工判断依据") model_update_needed: Mapped[bool] = mapped_column(Boolean, comment="仅迭代建议，不代表发生训练") class CacheEntry(Base): __tablename__ = "cache_entry" key: Mapped[str] = mapped_column(String(64), primary_key=True, comment="模型/参数/版本/输入的 SHA256") value_json: Mapped[Any] = mapped_column(JSON, comment="合法响应或向量缓存，不缓存失败") expires_at: Mapped[datetime] = mapped_column(DateTime, comment="TTL 到期 UTC") namespace: Mapped[str] = mapped_column(String(20), comment="llm/embed/query 等前缀") model_version: Mapped[str] = mapped_column(String(100), comment="模型隔离，换模型不可复用旧向量")