13 KiB
13 KiB
数据库表结构说明
性质：参考（REF） —— 记录表结构现状，以代码为准
版本：v1.1 · 日期：2026-08-26 数据库：PostgreSQL 15 总表数：86 张 关联代码：backend/app/models/ 说明：本版以代码为准全面重写，修正表数（56→86）、各表时间字段、补全缺失表、删除幽灵表
recharges。
一、表分类总览
| 分类 | 表数 | 说明 |
|---|---|---|
| 业务原始数据 | 24 | 从 BMS/ai-acc/SCRM 同步/导入的原始数据(2026-09-05 新增 4 张 BMS 五源扩容表) |
| 维度主数据 | 8 | 人员/商户/设备/分组/专项/数据源等维度 |
| 工作流数据 | 18 | 任务/待办/报告/措施/事件/制度/策略等 |
| 系统配置 | 12 | 用户/数据源/同步配置/操作日志/通知 |
| 分析配置 | 7 | 分析视图/看板/分类规则/指标/审计备注 |
| SCRM 数据 | 4 | 操作员/会话/转接/员工档案 |
| 历史追溯 | 8 | SCD Type 2 历史表（分组/专项/任职变更） |
| 运营支撑 | 9 | 排班/考勤/发票报销/知识库/预警/上游工单等 |
二、业务原始数据（24 张）
| 表名 | 模型文件 | 用途 | 时间字段 |
|---|---|---|---|
refunds |
refund.py | 退费单（线上退款） | created_at_raw / updated_at_raw / synced_at |
merchant_refunds |
merchant_refund.py | 商户号款项（v1.0 现金流快照源；2026-09-05 起保留作关联, E 统计已停用） | trade_time |
shanmao_refunds |
shanmao_refund.py | 山猫退款 | apply_time / audit_time / refund_time |
wallet_withdrawals |
wallet.py | 钱包提现 | op_time |
service_tickets |
ticket.py | BMS 服务工单 | register_time / active_time / dispatch_time / finish_time / synced_at |
external_complaints |
external_complaint.py | 12315/工信部外部投诉 | complaint_date / handle_time / matched_at |
card_complaints |
card_complaint.py | 卡商群投诉 | complaint_date / matched_at |
cs_records |
cs_record.py | ai-acc 智能助手处理记录 | operation_time / consult_time / problem_create_time |
manual_refunds |
manual_refund.py | 手动退款登记（商户号手机退款） | register_time / op_time |
upstream_tickets |
upstream_ticket.py | 跨部门上游工单 | dispatched_at / responded_at / resolved_at |
refund_wide |
refund_wide.py | 退费宽表（四路聚合加速） | created_at_raw / ticket_finish_time / mr_trade_time / wallet_op_time |
device_combos |
device_combo.py | 设备套餐 | start_date / end_date / created_at_raw / updated_at_raw |
devices |
device.py | 设备信息（领域核心） | active_time |
device_tags |
device.py | 设备标签 | — |
device_tag_rel |
device.py | 设备-标签关联 | created_at |
device_tag_batches |
device.py | 设备专项标签批次 | — |
recharges |
— | /recharges API 基于 device_combos 查询，无独立表） |
— |
merchants |
merchant.py | 商户信息 | — |
dept_info_syncs |
dept_info_sync.py | 部门信息同步 | effective_from / responded_at / closed_at |
upload_records |
upload.py | 上传/采集记录 | finished_at |
refund_record_reports |
refund_record_report.py | 退款记录报表(E 路线上退款水单, A-20260905-001/002) | refund_time |
bms_orders |
bms_order.py | 订单管理台账 | create_date |
return_trackings |
return_tracking.py | 退货跟踪 | created_at_raw |
device_flow_usage |
device_flow_usage.py | 设备月流量(审计, 唯一 SN+月) | fetched_at |
2026-09-05 新增 4 表(BMS 五源扩容, A-20260905-001 LOCKED):
refund_record_reports(退款记录报表→对账水单, P2 用) /bms_orders(订单台账) /return_trackings(退货跟踪) /device_flow_usage(设备月流量, 设备审计)。devices加 9 档案列(主商户/ICCID/批次等)。
注：
recharges曾为设计文档中的表，实际实现无独立表——由/recharges接口基于device_combos表实时查询（/device-combos/local口径），上游实时查询为占位。故不再单列。 时间字段口径：本表列出的时间为业务时间（各表实际字段名），与docs/standards/数据时间字段使用规范.md的「推荐字段」一致；refunds使用created_at_raw（updated_at_raw被 SYS 批量覆盖不可靠）。
三、维度主数据（8 张）
| 表名 | 模型文件 | 用途 |
|---|---|---|
persons |
person.py | 人员主表（含 department/group/specialty 冗余字段） |
person_aliases |
person.py | 人员别名（跨表关联用，支持归一化归并） |
groups |
group.py | 行政分组定义 |
specialties |
specialty.py | 业务专项定义（含难度系数） |
merchants |
merchant.py | 商户信息 |
devices |
device.py | 设备主数据（领域核心，含 device_tags/device_tag_rel/device_tag_batches） |
data_sources |
data_source.py | 数据源定义 |
data_source_fields |
data_source.py | 数据源字段定义 |
四、SCD Type 2 历史追溯（8 张）
| 表名 | 模型文件 | 用途 |
|---|---|---|
group_leadership_history |
group.py | 组长/副主管变更历史 |
person_group_memberships |
group.py | 人员分组归属历史 |
specialty_leadership_history |
specialty.py | 专项负责人变更历史 |
person_specialty_assignments |
specialty.py | 人员专项分配历史 |
staff_assignments |
staff_assignment.py | 员工任职/分配历史（HR 快照导入） |
weekly_section_bindings |
weekly_section_binding.py | 周报-部门绑定历史 |
metric_thresholds |
metric.py | 指标阈值配置（含生效区间） |
incidents（扩展） |
incident.py | 事件支持 valid_from/valid_to 周期化追溯 |
dim_device_combo |
dim_device_combo.py | 设备-套餐干净维度（#320，bms_orders+device_combos 合并去工厂质检；唯一键 (device_sn, valid_from)；退款 by_combo / 投诉 by_package 单一时点真身，见模型 docstring 口径说明） |
所有历史表共享 SCD Type 2 模式：
effective_from/effective_to/is_current/change_reason/source例外：dim_device_combo（#320 干净层维度）用valid_from/valid_to/is_current，无change_reason。
五、SCRM 数据（4 张）
| 表名 | 模型文件 | 用途 |
|---|---|---|
scrm_operators |
scrm_operator.py | 客服操作员 |
scrm_sessions |
scrm_session.py | 客服会话（三合一：private/group/virtual，含质量指标） |
scrm_session_transfers |
scrm_session_transfer.py | 会话转接记录 |
scrm_staff_profiles |
scrm_staff_profile.py | SCRM 员工档案 |
索引：
scrm_sessions：(person_id, start_time) / (session_type, start_time) / (question_type_name, start_time)scrm_operators：(operator_id, updated_at)
六、工作流数据（18 张）
| 表名 | 模型文件 | 用途 |
|---|---|---|
special_tasks |
task.py | 专项任务（项目式工作流） |
task_followups |
task.py | 任务跟进 |
task_devices |
task.py | 任务-设备关联（批量设备批次） |
todo_items |
todo.py | 待办事项（工单式） |
todo_followups |
todo.py | 待办跟进（时间线） |
reports |
report.py | 报告 |
weekly_reports |
weekly_report.py | 周报（含周维度 week_start/week_end/confirmed_at） |
report_templates |
report_template.py | 报告模板 |
report_comments |
report_comment.py | 报告评论 |
measures |
measure.py | 改进措施（含验证/复盘） |
incidents |
incident.py | 影响事件/异常事故（含周期化） |
incident_followups |
incident.py | 事件跟进 |
policies |
policy.py | 政策措施/制度管理 |
strategy_docs |
strategy.py | 策略发布文档 |
strategy_read_receipts |
strategy.py | 策略已读回执 |
kb_articles |
kb_article.py | 知识库/话术库 |
upstream_tickets |
upstream_ticket.py | 跨部门协作工单 |
source_groups |
source_group.py | 数据源分组 |
七、分析配置（7 张）
| 表名 | 模型文件 | 用途 |
|---|---|---|
analysis_views |
analysis_view.py | 分析视图定义（SQL 模板+图表配置） |
classification_rules |
analysis_view.py | 分类规则 |
dashboards |
dashboard.py | 看板定义（视图组合） |
audit_notes |
audit_note.py | 审计备注 |
metric_registry |
metric.py | 指标注册表（四级阈值水位） |
metric_daily_snapshots |
metric.py | 指标日快照 |
data_quality_rules |
data_quality.py | 数据质量规则 |
八、系统配置（12 张）
| 表名 | 模型文件 | 用途 |
|---|---|---|
users |
user.py | 系统用户（五级角色 role、token_version、wechat_openid/unionid） |
sync_source_configs |
sync_source_config.py | 同步源配置（含 Fernet 加密凭证） |
bms_sync_logs |
bms_sync_log.py | BMS 同步日志 |
scrm_sync_logs |
scrm_sync_log.py | SCRM 同步日志 |
complaint_sync_logs |
complaint_sync_log.py | 投诉同步日志 |
manual_refund_sync_logs |
manual_refund_sync_log.py | 手动退款同步日志 |
operation_logs |
operation_log.py | 操作审计日志 |
data_match_relations |
upload.py | 跨表关联匹配关系 |
wechat_registrations |
wechat.py | 微信扫码注册待审批记录（openid/期望账号/角色/分组/审批结果） |
wechat_accounts |
wechat.py | 微信身份与 User 的绑定关系（openid/unionid/is_primary） |
notifications |
notification.py | 站内通知 |
notification_channels |
notification_channel.py | 通知通道配置（站内信/群机器人/邮件） |
system_events |
system_event.py | 系统事务通知 |
doc_sync_configs |
doc_sync_config.py | 在线文档（腾讯文档）同步配置 |
ingest_mode_configs |
ingest_mode_config.py | 数据采集模式配置 |
八·补、运营支撑（9 张）
| 表名 | 模型文件 | 用途 |
|---|---|---|
shift_templates |
schedule.py | 排班班次模板 |
schedules |
schedule.py | 排班表 |
attendance |
schedule.py | 考勤记录（check_in_at / check_out_at） |
expense_invoices |
expense.py | 发票登记 |
reimbursements |
expense.py | 报销单 |
reimbursement_items |
expense.py | 报销明细（发票-报销关联） |
alert_rules |
alert.py | 预警规则 |
alerts |
alert.py | 预警记录 |
alert_deliveries |
alert.py | 预警投递记录 |
九、核心关系图
persons (人员主表)
├── person_aliases (别名, 1:N)
├── person_group_memberships (分组归属历史, SCD2)
├── person_specialty_assignments (专项分配历史, SCD2)
└── staff_assignments (员工任职历史, SCD2)
groups (行政分组)
├── group_leadership_history (组长变更, SCD2)
└── person_group_memberships (成员归属, SCD2)
specialties (业务专项)
├── specialty_leadership_history (负责人变更, SCD2)
└── person_specialty_assignments (人员分配, SCD2)
service_tickets / refunds / cs_records / wallet_withdrawals (业务原始数据)
└── 通过 SN (序列号) 跨表关联
└── operator → person_aliases → persons (操作员匹配)
refunds (退费单) ── 四路经营口径聚合 ──┐
wallet_withdrawals (钱包提现) ├── refund_wide (退费宽表, 聚合加速)
shanmao_refunds (山猫退款) │
manual_refunds (手动退款登记) ──────────┘
merchant_refunds (商户号款项, v1.0 快照) ── 保留供 matcher/wide 关联, 不再作对账取数
refund_record_reports (退款记录报表) ── 对账基准 E (2026-09-05 v1.1 起, 不参与经营四路累加)
└── 逐笔勾稽 A/B/C/D → matched/未登记; D 路 op_time 缺失按水单回填(auto_filled)
bms_orders (订单管理台账) / return_trackings (退货跟踪) / device_flow_usage (设备月流量)
devices (设备主表, 领域核心)
├── device_tags ─ device_tag_rel ─ devices (多对多)
├── device_tag_batches (专项标签批次, 1:N)
├── device_combos (设备套餐, 1:N)
└── refunds/service_tickets/wallet_withdrawals/merchant_refunds (通过 SN)
users ── wechat_accounts (微信身份绑定, 1:N) ── wechat_registrations (待审批)
└── todo_items/tasks/reports/measures/incidents (负责人/创建人 FK)
十、索引策略
10.1 时间字段索引
所有业务原始数据表的时间字段均建索引，支持时间范围查询：
CREATE INDEX ix_<table>_<time_field> ON <table> (<time_field>)
10.2 复合索引
SCD Type 2 历史表的 (entity_id, is_current) 复合索引加速"查当前归属"查询。
10.3 SCRM 复合索引
(person_id, start_time)：按人员查会话(session_type, start_time)：按类型查会话(question_type_name, start_time)：按问题类型查会话
10.4 其他关键索引
merchant_refunds：(merchant_id, trade_time)、merchant_order_no、device_no（对账查询）refund_wide：device_sn、created_at_raw、operator、status（四路看板聚合）wallet_withdrawals：(account_id, op_type, op_time, operator, op_amount, device_sn)业务唯一约束service_tickets：handler、register_time、status（人员绩效/时效查询）manual_refunds：register_time、device_sn、quality_status、(register_order, amount)业务唯一约束