479 lines
39 KiB
Markdown
479 lines
39 KiB
Markdown
# 一期批次1总体设计 —— 数据库设计（hr-web，ocai 口径）
|
||
|
||
- 版本：v3.0（批次1设计定稿；v3.0 变更：对齐 SRS v3.1 —— 库名 hrs/MariaDB、新增编制(F16)/项目式(F17) 5 张表与 sys_openapp 预留表（43→**49** 张）、roster_employee 证件字段扩展（D1 手工录入）、org_entry 登记链接/二维码字段（扫码入职降级）、org_leave_handover 钉钉交接占位、字典扩充）
|
||
- 状态：**设计定稿（批次1评审修订版）**
|
||
- 需求基线：`docs/00-requirement/requirement-spec.md`（SRS v3.1）+ `iteration1-function-detail.md`（F01~F15）+ approved-features.md（FEAT-B1-01~12）
|
||
- 架构基线：`docs/01-design/architecture.md`
|
||
- 规范依据：ocai 技能集 database-table-definition-spec（models/*.json + json2ddl）、crud-definition-spec、sqlor-database-module
|
||
|
||
## 1. 设计总则
|
||
|
||
1. **表定义声明式管理**：所有表以 `models/{table}.json`（summary/fields/indexes/codes 四段）定义，build.sh 中 `json2ddl mysql . > mysql.ddl.sql` 生成 DDL 导入 **hrs** 库。本文 DDL 为生成物等价示例，开发以 models JSON 为准。
|
||
2. **统一约定**：InnoDB；utf8mb4 / utf8mb4_unicode_ci；数据库 MariaDB，库名 `hrs`；主键 `id VARCHAR(32)`（getID() 雪花串，sqlor 惯例）；公共审计列 `created_by/updated_by/created_at/updated_at`；`sor.C` 不自动填时间戳，写入时显式 `created_at=curDateString()`。
|
||
3. **逻辑删除策略**：员工与组织**不做物理删除**，统一走"停用(status=inactive)/离职流程"；物理删除仅 admin 可执行且必须留审计（SRS §5.2-2）。
|
||
4. **自定义字段（EAV）**：花名册字段（F03）与组织字段（F01）采用"字段定义表 + 字段值表"EAV 方案；固定高频查询字段（组织、职位、状态、入离职日期）冗余在员工主表保证列表性能。
|
||
5. **敏感字段**：身份证号密文列 + 哈希列（确定性哈希，供唯一性与黑名单匹配），手机号明文存储、按角色脱敏展示；`roster_field_def.sensitive` 驱动字段级脱敏与可见性（SRS §2.2-4）。
|
||
6. **库划分**：单库 `hrs`，表前缀区分模块：`org_`(hr-org)、`roster_`(hr-roster)、`flow_`(hr-flow)、`contract_`(hr-contract)、`sys_`(hr-system)；appbase（users/orgs/appcodes）与 rbac 表沿用基础包自带结构，不在本文重复。
|
||
7. **预留字段约定**：二期对接预留字段一律带明确默认值与字典枚举位（如 esign_status='not_enabled'、id_source='manual'、send_channel='site'），不参与一期业务分支。
|
||
|
||
## 2. ER 图（实体关系描述）
|
||
|
||
```
|
||
┌──────────────┐
|
||
│ appbase │
|
||
│ users/orgs/ │◄── 登录用户、字典 appcodes
|
||
│ appcodes │
|
||
└──────┬───────┘
|
||
│ user_id 引用
|
||
┌─────────────┐ parent_id │ ┌──────────────────┐
|
||
│ org_unit │◄─自引用──────┼──────│ sys_data_scope │ 组织维度数据范围
|
||
│ 组织 │ │ │ sys_audit_log │ 操作日志(F12)
|
||
└──┬───┬──────┘ │ │ sys_message │ 站内消息(F15)
|
||
│ │org_field_value │ │ sys_remind_rule │ 提醒规则
|
||
│ ▼ │ │ sys_openapp │ API开放预留
|
||
│ org_field_def │ └────────▲─────────┘
|
||
│ │ │ 推送
|
||
│ ┌─────────────┐ ┌─────▼───────────────┴───┐
|
||
│ │org_job/ │ │ roster_employee │ 员工唯一事实源(F03)
|
||
│ │org_position◄─┼──│ org_id/position_id/ │
|
||
│ │org_sequence/ │ │ employee_status/证件字段组│
|
||
│ │org_grade_* │ └──┬──────┬──────┬─────────┘
|
||
│ └─────────────┘ │ │ │
|
||
│ │ │ ▼
|
||
│ roster_field_def◄────┘ roster_field_value(EAV)
|
||
│ roster_field_group roster_timeline(时间轴)
|
||
│ │
|
||
│ 异动业务(hr-org) │ 合同(hr-contract)
|
||
│ org_entry ──────────┤ contract_info ── employee_id
|
||
│ org_regularization ─┼──► contract_type / contract_template
|
||
│ org_transfer(+detail)│ contract_remind_rule
|
||
│ org_leave(+handover) │ │审批
|
||
│ org_concurrent_post │ ▼
|
||
│ org_headcount_scheme │ ┌──────────────┐
|
||
│ └ headcount_item │ │ flow_def │ 流程定义
|
||
│ └ headcount_snapshot│ │ flow_form_def│ 表单定义(引用花名册字段)
|
||
│ org_project(F17) │ │ flow_node_def│ 节点(审批人/字段权限)
|
||
│ └ org_project_post │ │ flow_role │ 审批角色
|
||
│ │审批流转 │ │ flow_instance│ 实例 ──► flow_task(待办)
|
||
└────────┴──────────────┴────►│ │ └► flow_op_log
|
||
biz_type+biz_id └──────────────┘
|
||
hr-report 无自有表：只读聚合 org_/roster_/flow_ 表，叠加 sys_data_scope 范围过滤。
|
||
```
|
||
|
||
核心关系说明：
|
||
- `roster_employee` 为全系统员工数据唯一事实源；`org_entry/regularization/transfer/leave/concurrent_post` 通过 `employee_id`（入职审批阶段可为空）关联，审批通过后经 hr-roster `roster_writeback` 更新主档。
|
||
- 所有审批类记录通过 `flow_instance_id` 关联流程实例；`flow_instance.biz_type + biz_id` 反向定位业务单据，流程完成后回调业务模块回写。
|
||
- **编制（F16）**：`org_headcount_scheme` 1─N `headcount_item`（细分额度）；`headcount_snapshot` 按日快照历史；员工异动回写后同步触发占编重算。占编范围条件以 JSON 表达（员工类型/状态/职位/职级/自定义字段），与 roster_employee + roster_field_value 联查计数。
|
||
- **项目式组织（F17）**：`org_project` 为横向组织（自引用多层级），员工经 `org_project_post` 同时挂纵向部门（roster_employee.org_id）与横向项目；横向职务体系复用 org_job 表（org_job.job_category=horizontal 区分，被花名册/薪酬引用）。
|
||
- 所有表与 appbase.users 之间为逻辑外键（operator_id/leader_id/approver_id），不建物理外键（sqlor 惯例，跨模块解耦）。
|
||
|
||
## 3. models JSON 示例（规范格式，json2ddl 输入）
|
||
|
||
`hr-roster/models/roster_employee.json`（含 D1 证件字段组扩展）：
|
||
|
||
```json
|
||
{
|
||
"summary": [
|
||
{"name": "roster_employee", "title": "员工主档", "pk": ["id"], "charset": "utf8mb4", "collate": "utf8mb4_unicode_ci"}
|
||
],
|
||
"fields": [
|
||
{"name": "id", "title": "主键", "type": "varchar", "length": 32},
|
||
{"name": "employee_no", "title": "工号", "type": "varchar", "length": 32},
|
||
{"name": "name", "title": "姓名", "type": "varchar", "length": 64},
|
||
{"name": "gender", "title": "性别", "type": "varchar", "length": 16},
|
||
{"name": "id_type", "title": "证件类型", "type": "varchar", "length": 16, "default": "id_card"},
|
||
{"name": "id_number_cipher", "title": "证件号密文", "type": "varchar", "length": 256},
|
||
{"name": "id_number_hash", "title": "证件号哈希", "type": "varchar", "length": 64},
|
||
{"name": "id_valid_from", "title": "证件有效期起", "type": "date"},
|
||
{"name": "id_valid_to", "title": "证件有效期止(长期=9999-12-31)", "type": "date"},
|
||
{"name": "id_authority", "title": "证件签发机关", "type": "varchar", "length": 128},
|
||
{"name": "id_source", "title": "证件信息来源", "type": "varchar", "length": 16, "default": "manual"},
|
||
{"name": "birthday", "title": "出生日期", "type": "date"},
|
||
{"name": "phone", "title": "手机号", "type": "varchar", "length": 20},
|
||
{"name": "email", "title": "邮箱", "type": "varchar", "length": 128},
|
||
{"name": "org_id", "title": "组织", "type": "varchar", "length": 32},
|
||
{"name": "position_id", "title": "职位", "type": "varchar", "length": 32},
|
||
{"name": "job_id", "title": "职务", "type": "varchar", "length": 32},
|
||
{"name": "grade_level_id", "title": "职级", "type": "varchar", "length": 32},
|
||
{"name": "grade_rank_id", "title": "职等", "type": "varchar", "length": 32},
|
||
{"name": "sequence_id", "title": "序列", "type": "varchar", "length": 32},
|
||
{"name": "employee_type", "title": "员工类型", "type": "varchar", "length": 16},
|
||
{"name": "employee_status", "title": "员工状态", "type": "varchar", "length": 16},
|
||
{"name": "hire_date", "title": "入职日期", "type": "date"},
|
||
{"name": "probation_end_date", "title": "试用期止(预计转正日)", "type": "date"},
|
||
{"name": "regular_date", "title": "转正日期", "type": "date"},
|
||
{"name": "leave_date", "title": "离职日期", "type": "date"},
|
||
{"name": "company_id", "title": "合同公司", "type": "varchar", "length": 32},
|
||
{"name": "work_location", "title": "办公地点", "type": "varchar", "length": 128},
|
||
{"name": "direct_leader_id", "title": "直属主管", "type": "varchar", "length": 32},
|
||
{"name": "prev_employee_id", "title": "复职关联原档案ID", "type": "varchar", "length": 32},
|
||
{"name": "remark", "title": "备注", "type": "text"},
|
||
{"name": "created_by", "title": "创建人", "type": "varchar", "length": 32},
|
||
{"name": "updated_by", "title": "更新人", "type": "varchar", "length": 32},
|
||
{"name": "created_at", "title": "创建时间", "type": "timestamp"},
|
||
{"name": "updated_at", "title": "更新时间", "type": "timestamp"}
|
||
],
|
||
"indexes": [
|
||
{"name": "uk_employee_no", "unique": true, "fields": ["employee_no"]},
|
||
{"name": "uk_id_number_hash", "unique": true, "fields": ["id_number_hash"]},
|
||
{"name": "idx_org_id", "fields": ["org_id"]},
|
||
{"name": "idx_status", "fields": ["employee_status"]},
|
||
{"name": "idx_hire_date", "fields": ["hire_date"]},
|
||
{"name": "idx_probation_end", "fields": ["probation_end_date"]},
|
||
{"name": "idx_direct_leader", "fields": ["direct_leader_id"]},
|
||
{"name": "idx_prev_employee", "fields": ["prev_employee_id"]}
|
||
],
|
||
"codes": [
|
||
{"name": "gender", "source": "appcodes", "code": "GENDER"},
|
||
{"name": "employee_type", "source": "appcodes", "code": "EMP_TYPE"},
|
||
{"name": "employee_status", "source": "appcodes", "code": "EMP_STATUS"},
|
||
{"name": "id_source", "source": "appcodes", "code": "ID_SOURCE"}
|
||
]
|
||
}
|
||
```
|
||
|
||
CRUD 侧配套 `hr-roster/json/roster_employee_list.json`（crud-definition-spec）：`tblname=roster_employee`，`params` 顶层配置 `new_data_url/update_data_url/delete_data_url` 指向 `wwwroot/api/roster_employee_{create,update,delete}.dspy`，`data_filter` 声明姓名/工号/组织/状态筛选项，`browserfields.alters` 引用 GENDER/EMP_TYPE/EMP_STATUS 字典。
|
||
|
||
## 4. 表结构设计（49 张业务表）
|
||
|
||
> 公共列约定：凡标【审计】的表均含 `created_by/updated_by/created_at/updated_at`；日志/快照类表仅含 `created_at`。DDL 示例见 §5。
|
||
> v3.0 变化：hr-org 18→23（+编制 3 表+项目式 2 表）、hr-system 7→8（+sys_openapp 预留）、roster_employee/org_entry/org_leave_handover 字段扩展；合计 **49 张**。
|
||
|
||
### 4.1 hr-org 组织人事（23 张，前缀 org_）
|
||
|
||
| # | 表 | 用途 | 关键字段 | 关键索引 |
|
||
|---|---|---|---|---|
|
||
| 1 | org_unit | 组织（F01） | id, org_code, org_name, parent_id(自引用), org_type(字典ORG_TYPE), leader_id, effective_date, expire_date(空=长期), status(active/inactive), sort_no【审计】 | uk_org_code; idx_parent_id; idx_status |
|
||
| 2 | org_unit_change | 组织变更时间轴（F01） | id, org_id, change_type(add/edit/disable/move/split/merge/delete), before_json, after_json, effective_date, operator_id, flow_instance_id, created_at | idx_org_id; idx_created_at |
|
||
| 3 | org_field_def | 组织自定义字段（F01） | id, field_code, field_name, field_type(text/number/date/select/file), options_json, required, sort_no, status【审计】 | uk_field_code |
|
||
| 4 | org_field_value | 组织字段值 EAV | id, org_id, field_id, value_text, value_date, value_file, updated_at | uk(org_id,field_id) |
|
||
| 5 | org_job | 职务（F02/F17） | id, job_code, job_name, job_category(vertical/horizontal，F17 横向职务), status, sort_no, remark【审计】 | uk_job_code; idx_job_category |
|
||
| 6 | org_position | 职位（F02） | id, position_code, position_name, job_id, sequence_id, category(字典POS_CATEGORY), dept_ids(多值,逗号分隔), description, status, sort_no【审计】 | uk_position_code; idx_job_id |
|
||
| 7 | org_sequence | 序列（F02） | id, seq_category, seq_name, grade_level_ids(关联职级类别), status【审计】 | — |
|
||
| 8 | org_grade_level | 职级（F02） | id, level_code, level_name, level_category, start_rank_id, end_rank_id, sort_no【审计】 | uk_level_code |
|
||
| 9 | org_grade_rank | 职等（F02） | id, rank_code, rank_name, rank_level(int), sort_no【审计】 | uk_rank_code |
|
||
| 10 | org_blacklist | 黑名单（F04/F07） | id, name, id_number_hash, id_number_cipher, reason, source(leave/manual), source_employee_id, created_by, created_at | idx_id_number_hash |
|
||
| 11 | org_contract_company | 合同公司（F01/F09） | id, company_code, company_name, credit_code(统一社会信用代码), legal_person, contact_info, status【审计】 | uk_company_code |
|
||
| 12 | org_entry | 入职记录（F04） | id, employee_id(可空,待入职), name, id_type, id_number_cipher/hash, id_valid_from/id_valid_to/id_authority/id_source(手工录入 D1), employee_type, org_id, position_id, company_id, work_location, hire_date, entry_status(pending_approval/pending_entry/entered/cancelled/blocked), is_rehire, register_status(none/invited/filled), **register_token, register_url, qrcode_file, register_expire**（登记链接/二维码，扫码入职降级）, flow_instance_id, remark【审计】 | idx_entry_status; idx_hire_date; idx_flow_instance_id; uk_register_token |
|
||
| 13 | org_regularization | 转正记录（F05） | id, employee_id, probation_end_date, regular_date, apply_type(self/proxy/manual), status(pending/approved/rejected/cancelled), flow_instance_id, remark【审计】 | idx_employee_id; idx_regular_date |
|
||
| 14 | org_transfer | 调动单（F06，支持批量） | id, transfer_type(promotion/demotion/position_change/org_adjust), status(pending/confirmed/cancelled), effective_date, batch_no, flow_instance_id, remark【审计】 | idx_status; idx_effective_date |
|
||
| 15 | org_transfer_detail | 调动明细（逐人） | id, transfer_id, employee_id, from_org_id, to_org_id, from_position_id, to_position_id, from_grade_level_id, to_grade_level_id, status | idx_transfer_id; idx_employee_id |
|
||
| 16 | org_leave | 离职记录（F07） | id, employee_id, leave_type(辞职/劝退/合同到期/退休), leave_reason(字典LEAVE_REASON), leave_date, apply_type(self/proxy/manual), status(pending_leave/left/cancelled), handover_status, certificate_file, blacklist_flag, flow_instance_id, remark【审计】 | idx_employee_id; idx_status; idx_leave_date |
|
||
| 17 | org_leave_handover | 离职交接项 | id, leave_id, item_type(approval/file/subordinate/permission/**dingtalk_resource(钉钉资源交接占位,D3)**/other), item_content, handover_to_id, status(pending/done), remark | idx_leave_id |
|
||
| 18 | org_concurrent_post | 兼岗（F08） | id, employee_id, org_id, position_id, start_date, end_date, status(awaiting/active/expired), flow_instance_id【审计】 | idx_employee_id; idx_status |
|
||
| 19 | org_headcount_scheme | 编制方案（F16） | id, scheme_name, cycle_type(year/quarter/month，字典HEADCOUNT_CYCLE), cycle_start, cycle_end, status(active/inactive), remark【审计】 | idx_cycle(cycle_start,cycle_end); idx_status |
|
||
| 20 | headcount_item | 编制细分项（F16） | id, scheme_id, scope_json(占编范围:员工类型/状态/职位/职级/自定义字段条件), seg_json(细分维度,可空=整体), head_limit(int), remark【审计】 | idx_scheme_id |
|
||
| 21 | headcount_snapshot | 编制快照（F16 历史回溯/实时计数） | id, scheme_id, item_id, snap_date, used_count(int), over_count(int，超编数), created_at | uk(scheme_id,item_id,snap_date); idx_snap_date |
|
||
| 22 | org_project | 项目式组织（F17） | id, project_code, project_name, parent_id(自引用多层级), owner_id(负责人), start_date, end_date, project_status(active/expired/inactive), attrs_json(项目属性), sort_no【审计】 | uk_project_code; idx_parent_id; idx_project_status |
|
||
| 23 | org_project_post | 项目任职（F17 员工挂横向组织） | id, project_id, employee_id, job_id(横向职务,引用 org_job job_category=horizontal), start_date, end_date, status(active/expired)【审计】 | uk(project_id,employee_id,job_id,start_date); idx_employee_id |
|
||
|
||
### 4.2 hr-roster 花名册（7 张，前缀 roster_）
|
||
|
||
| # | 表 | 用途 | 关键字段 | 关键索引 |
|
||
|---|---|---|---|---|
|
||
| 24 | roster_field_group | 字段分组（F03） | id, group_code, group_name(工作信息/个人信息/绩效结果/培训记录), sort_no, status【审计】 | uk_group_code |
|
||
| 25 | roster_field_def | 字段定义（F03 核心） | id, group_id, field_code, field_name, field_type(text/number/date/select/multiselect/file), options_json(字典code或自定义项), required, sensitive(0/1), editable_self(0/1), need_audit(0/1自助修改审核), apply_types(JSON适用员工类型), sort_no, status【审计】 | uk_field_code; idx_group_id |
|
||
| 26 | roster_employee | 员工主档（F03） | 见 §3 models 示例（含证件字段组/probation_end_date/prev_employee_id） | 见 §3 |
|
||
| 27 | roster_field_value | 字段值 EAV | id, employee_id, field_id, value_text, value_number(decimal(18,4)), value_date, value_file, updated_by, updated_at | uk(employee_id,field_id); idx_field_id |
|
||
| 28 | roster_empno_rule | 工号规则（F03） | id, rule_name, prefix, match_field(如company_id), match_value, seq_length, current_seq(int), status【审计】 | — |
|
||
| 29 | roster_timeline | 员工时间轴（F03/F12 联动） | id, employee_id, event_type(entry/regular/transfer/leave/concurrent/contract/field_change/headcount/project/migrate), event_date, title, content_json, source_type, source_id, created_at | idx_emp_date(employee_id,event_date) |
|
||
| 30 | roster_type_rule | 员工类型字段规则（F03 分类管理） | id, employee_type, field_rules_json(按类型必填/隐藏字段), status【审计】 | uk(employee_type) |
|
||
|
||
### 4.3 hr-flow 流程审批（7 张，前缀 flow_）
|
||
|
||
| # | 表 | 用途 | 关键字段 | 关键索引 |
|
||
|---|---|---|---|---|
|
||
| 31 | flow_form_def | 表单定义（F10） | id, form_code, form_name, biz_type(entry/regular/transfer/leave/handover/concurrent/contract_sign/contract_renew/contract_change/contract_stop/roster_self_edit/**attendance_\*桩位**), fields_json(引用roster_field_def或自定义项), status【审计】 | uk_form_code |
|
||
| 32 | flow_def | 流程定义（F10） | id, flow_code, flow_name, form_id, biz_type, match_cond_json(员工类型/部门匹配条件), version, status【审计】 | uk_flow_code; idx_biz_type |
|
||
| 33 | flow_node_def | 流程节点 | id, flow_id, node_seq, node_name, approver_type(user/role/leader/admin/flow_role), approver_value, pass_rule(any/all), field_perm_json(节点字段可读写), cc_to(抄送) | idx_flow_id |
|
||
| 34 | flow_role | 审批角色（F10） | id, role_name, flow_scope_json(可用流程), op_perm_json(操作权限), data_scope_json(数据查看范围), status【审计】 | — |
|
||
| 35 | flow_instance | 流程实例 | id, inst_no(审批编号), flow_id, form_id, biz_type, biz_id, title, initiator_id, inst_status(running/approved/rejected/cancelled), submit_data_json, start_time, finish_time | uk_inst_no; idx_biz(biz_type,biz_id); idx_initiator; idx_status |
|
||
| 36 | flow_task | 审批任务（待办/已办） | id, instance_id, node_id, node_name, approver_id, task_status(pending/approved/rejected/forwarded/cancelled), comment, handle_time, created_at | idx_approver_status(approver_id,task_status); idx_instance |
|
||
| 37 | flow_op_log | 流转日志 | id, instance_id, op_type(submit/approve/reject/forward/cancel/withdraw), operator_id, comment, created_at | idx_instance |
|
||
|
||
### 4.4 hr-contract 合同（4 张，前缀 contract_）
|
||
|
||
| # | 表 | 用途 | 关键字段 | 关键索引 |
|
||
|---|---|---|---|---|
|
||
| 38 | contract_type | 合同类型（F09 自定义） | id, type_code, type_name(劳动合同/保密协议/竞业协议…), status【审计】 | uk_type_code |
|
||
| 39 | contract_template | 合同模板（F09） | id, template_name, type_id, file_path, field_marks_json(占位符映射), status【审计】 | — |
|
||
| 40 | contract_info | 合同台账（F09） | id, contract_no, employee_id, type_id, company_id, template_id, start_date, end_date, sign_date, contract_status(active/expired/stopped/renewing), file_path, text_file_path(模板生成合同文本,D4), flow_instance_id, remind_rule_id, esign_status(not_enabled/待二期流转,D4), remark【审计】 | idx_employee; idx_end_date; idx_status |
|
||
| 41 | contract_remind_rule | 到期提醒规则（F09） | id, rule_name, days_before(int), target_role(self/leader/hr), content_template, status【审计】 | — |
|
||
|
||
### 4.5 hr-system 权限日志与通用服务（8 张，前缀 sys_）
|
||
|
||
| # | 表 | 用途 | 关键字段 | 关键索引 |
|
||
|---|---|---|---|---|
|
||
| 42 | sys_data_scope | 数据范围（F11） | id, admin_user_id(管理员), scope_type(org/field), org_ids_json(组织维度,含子树), field_ids_json(花名册字段维度), remark【审计】 | idx_admin_user |
|
||
| 43 | sys_audit_log | 操作日志（F12，只增不改） | id, module, target_type, target_id, operation(create/update/delete/import/export/approve/login/migrate), operator_id, operator_name, before_json, after_json, request_ip, created_at | idx_target(target_type,target_id); idx_operator; idx_created_at; idx_module_op(module,operation) |
|
||
| 44 | sys_message | 站内消息（F15） | id, receiver_id, msg_type(todo/approval/remind/care/notice), title, content, biz_type, biz_id, send_channel(site; sms/email预留 D9), is_read, created_at | idx_receiver_read(receiver_id,is_read); idx_created_at |
|
||
| 45 | sys_remind_rule | 通用提醒规则（F15 多场景） | id, scene_code(entry/regular/leave/retire/contract_expire/health_cert/social_insurance/custom), rule_name, days_before, content_template, target_role(admin/employee/leader), status【审计】 | uk(scene_code,rule_name) |
|
||
| 46 | sys_announcement | 企业公告（F15） | id, title, content, publish_scope_json, publish_time, status(draft/published/offline), created_by【审计】 | idx_status |
|
||
| 47 | sys_policy | 企业政策（F15） | id, title, file_path, view_scope_json, downloadable(0/1), publish_time, status, created_by【审计】 | idx_status |
|
||
| 48 | sys_care_config | 关怀配置（F15 生日/周年） | id, care_type(birthday/anniversary), template, enable(0/1), push_target(employee/leader), updated_by, updated_at | uk_care_type |
|
||
| 49 | sys_openapp | API 开放应用（一期预留，FEAT-B1-01⑦） | id, app_key, app_secret_cipher, app_name, scope_json(授权资源), rate_limit(int), status(active/inactive)【审计】 | uk_app_key |
|
||
|
||
## 5. 核心表 DDL 示例（json2ddl 生成等价物）
|
||
|
||
```sql
|
||
-- roster_employee（完整字段见 §3 models JSON；此处为等价 DDL 含证件字段组）
|
||
CREATE TABLE roster_employee (
|
||
id VARCHAR(32) NOT NULL COMMENT '主键',
|
||
employee_no VARCHAR(32) NOT NULL COMMENT '工号',
|
||
name VARCHAR(64) NOT NULL COMMENT '姓名',
|
||
gender VARCHAR(16) DEFAULT NULL COMMENT '性别(字典GENDER)',
|
||
id_type VARCHAR(16) DEFAULT 'id_card' COMMENT '证件类型',
|
||
id_number_cipher VARCHAR(256) DEFAULT NULL COMMENT '证件号密文',
|
||
id_number_hash VARCHAR(64) DEFAULT NULL COMMENT '证件号哈希(唯一)',
|
||
id_valid_from DATE DEFAULT NULL COMMENT '证件有效期起',
|
||
id_valid_to DATE DEFAULT NULL COMMENT '证件有效期止(长期=9999-12-31)',
|
||
id_authority VARCHAR(128) DEFAULT NULL COMMENT '证件签发机关',
|
||
id_source VARCHAR(16) DEFAULT 'manual' COMMENT '证件来源(字典ID_SOURCE: manual/card_reader预留)',
|
||
birthday DATE DEFAULT NULL,
|
||
phone VARCHAR(20) DEFAULT NULL,
|
||
email VARCHAR(128) DEFAULT NULL,
|
||
org_id VARCHAR(32) DEFAULT NULL COMMENT '组织',
|
||
position_id VARCHAR(32) DEFAULT NULL COMMENT '职位',
|
||
job_id VARCHAR(32) DEFAULT NULL COMMENT '职务',
|
||
grade_level_id VARCHAR(32) DEFAULT NULL COMMENT '职级',
|
||
grade_rank_id VARCHAR(32) DEFAULT NULL COMMENT '职等',
|
||
sequence_id VARCHAR(32) DEFAULT NULL COMMENT '序列',
|
||
employee_type VARCHAR(16) DEFAULT NULL COMMENT '员工类型(字典EMP_TYPE)',
|
||
employee_status VARCHAR(16) DEFAULT NULL COMMENT '状态(字典EMP_STATUS)',
|
||
hire_date DATE DEFAULT NULL,
|
||
probation_end_date DATE DEFAULT NULL COMMENT '试用期止(预计转正日,转正提醒依据)',
|
||
regular_date DATE DEFAULT NULL,
|
||
leave_date DATE DEFAULT NULL,
|
||
company_id VARCHAR(32) DEFAULT NULL COMMENT '合同公司',
|
||
work_location VARCHAR(128) DEFAULT NULL,
|
||
direct_leader_id VARCHAR(32) DEFAULT NULL COMMENT '直属主管',
|
||
prev_employee_id VARCHAR(32) DEFAULT NULL COMMENT '复职关联原档案ID(F04-5)',
|
||
remark TEXT DEFAULT NULL,
|
||
created_by VARCHAR(32) DEFAULT NULL, updated_by VARCHAR(32) DEFAULT NULL,
|
||
created_at TIMESTAMP NULL DEFAULT NULL, updated_at TIMESTAMP NULL DEFAULT NULL,
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_employee_no (employee_no),
|
||
UNIQUE KEY uk_id_number_hash (id_number_hash),
|
||
KEY idx_org_id (org_id),
|
||
KEY idx_status (employee_status),
|
||
KEY idx_hire_date (hire_date),
|
||
KEY idx_probation_end (probation_end_date),
|
||
KEY idx_direct_leader (direct_leader_id),
|
||
KEY idx_prev_employee (prev_employee_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='员工主档';
|
||
|
||
CREATE TABLE org_unit (
|
||
id VARCHAR(32) NOT NULL,
|
||
org_code VARCHAR(32) NOT NULL COMMENT '组织编码',
|
||
org_name VARCHAR(128) NOT NULL COMMENT '组织名称',
|
||
parent_id VARCHAR(32) DEFAULT NULL COMMENT '上级组织',
|
||
org_type VARCHAR(16) DEFAULT NULL COMMENT '组织类型(字典ORG_TYPE)',
|
||
leader_id VARCHAR(32) DEFAULT NULL COMMENT '负责人',
|
||
effective_date DATE DEFAULT NULL COMMENT '生效日期',
|
||
expire_date DATE DEFAULT NULL COMMENT '失效日期(空=长期)',
|
||
status VARCHAR(16) NOT NULL DEFAULT 'active' COMMENT 'active/inactive',
|
||
sort_no INT DEFAULT 0,
|
||
remark TEXT DEFAULT NULL,
|
||
created_by VARCHAR(32) DEFAULT NULL, updated_by VARCHAR(32) DEFAULT NULL,
|
||
created_at TIMESTAMP NULL DEFAULT NULL, updated_at TIMESTAMP NULL DEFAULT NULL,
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_org_code (org_code),
|
||
KEY idx_parent_id (parent_id),
|
||
KEY idx_status (status)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='组织';
|
||
|
||
CREATE TABLE flow_instance (
|
||
id VARCHAR(32) NOT NULL,
|
||
inst_no VARCHAR(32) NOT NULL COMMENT '审批编号',
|
||
flow_id VARCHAR(32) NOT NULL, form_id VARCHAR(32) NOT NULL,
|
||
biz_type VARCHAR(32) NOT NULL COMMENT '业务类型(字典FLOW_BIZ_TYPE)',
|
||
biz_id VARCHAR(32) DEFAULT NULL COMMENT '业务单据ID',
|
||
title VARCHAR(256) DEFAULT NULL,
|
||
initiator_id VARCHAR(32) NOT NULL,
|
||
inst_status VARCHAR(16) NOT NULL DEFAULT 'running' COMMENT 'running/approved/rejected/cancelled',
|
||
submit_data_json TEXT DEFAULT NULL COMMENT '提交表单数据快照',
|
||
start_time TIMESTAMP NULL DEFAULT NULL, finish_time TIMESTAMP NULL DEFAULT NULL,
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_inst_no (inst_no),
|
||
KEY idx_biz (biz_type, biz_id),
|
||
KEY idx_initiator (initiator_id),
|
||
KEY idx_status (inst_status)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='流程实例';
|
||
|
||
CREATE TABLE sys_audit_log (
|
||
id VARCHAR(32) NOT NULL,
|
||
module VARCHAR(32) NOT NULL COMMENT 'hr-org/hr-roster/...',
|
||
target_type VARCHAR(32) NOT NULL COMMENT 'roster_employee/org_unit/...',
|
||
target_id VARCHAR(32) NOT NULL,
|
||
operation VARCHAR(16) NOT NULL COMMENT 'create/update/delete/import/export/approve/login/migrate',
|
||
operator_id VARCHAR(32) NOT NULL, operator_name VARCHAR(64) DEFAULT '',
|
||
before_json TEXT DEFAULT NULL COMMENT '变更前快照',
|
||
after_json TEXT DEFAULT NULL COMMENT '变更后快照',
|
||
request_ip VARCHAR(64) DEFAULT NULL,
|
||
created_at TIMESTAMP NULL DEFAULT NULL,
|
||
PRIMARY KEY (id),
|
||
KEY idx_target (target_type, target_id),
|
||
KEY idx_operator (operator_id),
|
||
KEY idx_created_at (created_at),
|
||
KEY idx_module_op (module, operation)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='操作日志(只增)';
|
||
|
||
-- F16 编制管理（新增）
|
||
CREATE TABLE org_headcount_scheme (
|
||
id VARCHAR(32) NOT NULL,
|
||
scheme_name VARCHAR(128) NOT NULL COMMENT '方案名称',
|
||
cycle_type VARCHAR(16) NOT NULL COMMENT '周期(字典HEADCOUNT_CYCLE: year/quarter/month)',
|
||
cycle_start DATE NOT NULL, cycle_end DATE NOT NULL,
|
||
status VARCHAR(16) NOT NULL DEFAULT 'active',
|
||
remark TEXT DEFAULT NULL,
|
||
created_by VARCHAR(32) DEFAULT NULL, updated_by VARCHAR(32) DEFAULT NULL,
|
||
created_at TIMESTAMP NULL DEFAULT NULL, updated_at TIMESTAMP NULL DEFAULT NULL,
|
||
PRIMARY KEY (id),
|
||
KEY idx_cycle (cycle_start, cycle_end),
|
||
KEY idx_status (status)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='编制方案';
|
||
|
||
CREATE TABLE headcount_item (
|
||
id VARCHAR(32) NOT NULL,
|
||
scheme_id VARCHAR(32) NOT NULL COMMENT '编制方案',
|
||
scope_json TEXT NOT NULL COMMENT '占编范围条件(员工类型/状态/职位/职级/自定义字段)',
|
||
seg_json TEXT DEFAULT NULL COMMENT '细分维度(空=整体)',
|
||
head_limit INT NOT NULL DEFAULT 0 COMMENT '编制数量',
|
||
remark TEXT DEFAULT NULL,
|
||
created_by VARCHAR(32) DEFAULT NULL, updated_by VARCHAR(32) DEFAULT NULL,
|
||
created_at TIMESTAMP NULL DEFAULT NULL, updated_at TIMESTAMP NULL DEFAULT NULL,
|
||
PRIMARY KEY (id),
|
||
KEY idx_scheme_id (scheme_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='编制细分项';
|
||
|
||
CREATE TABLE headcount_snapshot (
|
||
id VARCHAR(32) NOT NULL,
|
||
scheme_id VARCHAR(32) NOT NULL,
|
||
item_id VARCHAR(32) NOT NULL COMMENT '细分项',
|
||
snap_date DATE NOT NULL COMMENT '快照日期',
|
||
used_count INT NOT NULL DEFAULT 0 COMMENT '占编人数',
|
||
over_count INT NOT NULL DEFAULT 0 COMMENT '超编数(0=未超编)',
|
||
created_at TIMESTAMP NULL DEFAULT NULL,
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_scheme_item_date (scheme_id, item_id, snap_date),
|
||
KEY idx_snap_date (snap_date)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='编制快照(历史回溯)';
|
||
|
||
-- F17 项目式组织（新增）
|
||
CREATE TABLE org_project (
|
||
id VARCHAR(32) NOT NULL,
|
||
project_code VARCHAR(32) NOT NULL COMMENT '项目编码',
|
||
project_name VARCHAR(128) NOT NULL COMMENT '项目名称',
|
||
parent_id VARCHAR(32) DEFAULT NULL COMMENT '上级项目(多层级)',
|
||
owner_id VARCHAR(32) DEFAULT NULL COMMENT '负责人',
|
||
start_date DATE DEFAULT NULL, end_date DATE DEFAULT NULL COMMENT '时间范围',
|
||
project_status VARCHAR(16) NOT NULL DEFAULT 'active' COMMENT 'active/expired/inactive(到期自动标识)',
|
||
attrs_json TEXT DEFAULT NULL COMMENT '项目属性',
|
||
sort_no INT DEFAULT 0,
|
||
created_by VARCHAR(32) DEFAULT NULL, updated_by VARCHAR(32) DEFAULT NULL,
|
||
created_at TIMESTAMP NULL DEFAULT NULL, updated_at TIMESTAMP NULL DEFAULT NULL,
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_project_code (project_code),
|
||
KEY idx_parent_id (parent_id),
|
||
KEY idx_project_status (project_status)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='项目式组织';
|
||
|
||
CREATE TABLE org_project_post (
|
||
id VARCHAR(32) NOT NULL,
|
||
project_id VARCHAR(32) NOT NULL COMMENT '项目组织',
|
||
employee_id VARCHAR(32) NOT NULL COMMENT '员工',
|
||
job_id VARCHAR(32) DEFAULT NULL COMMENT '横向职务(org_job.job_category=horizontal)',
|
||
start_date DATE DEFAULT NULL, end_date DATE DEFAULT NULL,
|
||
status VARCHAR(16) NOT NULL DEFAULT 'active' COMMENT 'active/expired',
|
||
created_by VARCHAR(32) DEFAULT NULL, updated_by VARCHAR(32) DEFAULT NULL,
|
||
created_at TIMESTAMP NULL DEFAULT NULL, updated_at TIMESTAMP NULL DEFAULT NULL,
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_proj_emp_job (project_id, employee_id, job_id, start_date),
|
||
KEY idx_employee_id (employee_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='项目任职(横向)';
|
||
```
|
||
|
||
## 6. 索引与性能策略
|
||
|
||
1. 100 人规模全表扫描亦可满足 ≤500ms，但仍按查询模式建索引：列表筛选列（status/org_id/hire_date/end_date）、EAV 值表 uk(employee_id,field_id)、待办 idx(approver_id,task_status)、转正提醒 idx(probation_end_date)。
|
||
2. 组织树、职位职级主数据一次性全量加载（≤200/≤500 行），前端缓存，无需分页。
|
||
3. EAV 查询模式：花名册列表先按主表条件分页取员工，再批量 `IN` 查值表拼装（每页 ≤50 人 × 显示字段数），避免 EAV 行转列大 JOIN。
|
||
4. 报表（hr-report）直接 SQL 聚合（sor.sqlExe 只读），100 人规模不建物化视图，满足"延迟 ≤5 分钟"（实为实时）；sys_audit_log 按月保留，冷数据归档不删除。
|
||
5. 编制计数：headcount_snapshot 以"异动事件触发 + 每日兜底扫描"双机制维护；实时查询取最新 snap_date，历史回溯按 snap_date 精确取数（F16 验收③）。
|
||
6. 唯一性：工号、组织编码、项目编码、职位/职务/职级/职等编码、审批编号、登记表 token 均唯一索引；证件号经哈希列唯一（密文为非确定性加密，哈希列承担唯一性与黑名单匹配）。
|
||
|
||
## 7. 数据字典（appbase appcodes，init/data.json 初始化）
|
||
|
||
| code | 值 | 说明 |
|
||
|---|---|---|
|
||
| GENDER | male/female/unknown | 性别 |
|
||
| ORG_TYPE | group/subsidiary/dept/team | 组织类型 |
|
||
| EMP_TYPE | formal/temp/dispatch/intern | 员工类型（正式/临时/派遣/实习） |
|
||
| EMP_STATUS | pending_entry/probation/regular/pending_leave/left | 员工状态（待入职/试用/正式/待离职/离职） |
|
||
| ID_TYPE | id_card/passport/other | 证件类型 |
|
||
| ID_SOURCE | manual/card_reader(预留,二期) | 证件信息来源（D1） |
|
||
| POS_CATEGORY | management/professional/skill | 职位类别 |
|
||
| JOB_CATEGORY | vertical/horizontal | 职务类别（F17 横向职务） |
|
||
| TRANSFER_TYPE | promotion/demotion/position_change/org_adjust | 调动类型 |
|
||
| LEAVE_REASON | personal/company/contract_expire/retire/other | 离职原因 |
|
||
| HANDOVER_TYPE | approval/file/subordinate/permission/dingtalk_resource(占位)/other | 离职交接项类型（D3） |
|
||
| CONTRACT_STATUS | active/expired/stopped/renewing | 合同状态 |
|
||
| ESIGN_STATUS | not_enabled/signing/signed(后两者二期启用) | 电子签状态（D4 预留） |
|
||
| FLOW_BIZ_TYPE | entry/regular/transfer/leave/handover/concurrent/contract_sign/contract_renew/contract_change/contract_stop/roster_self_edit/attendance_*(桩位) | 审批业务类型 |
|
||
| INST_STATUS | running/approved/rejected/cancelled | 实例状态 |
|
||
| REMIND_SCENE | entry/regular/leave/retire/contract_expire/health_cert/social_insurance/custom | 提醒场景（≥6 类，F15 验收④） |
|
||
| HEADCOUNT_CYCLE | year/quarter/month | 编制周期（F16） |
|
||
| PROJECT_STATUS | active/expired/inactive | 项目状态（F17，到期自动 expired） |
|
||
| MSG_CHANNEL | site/sms(预留)/email(预留) | 消息渠道（D9） |
|
||
| AUDIT_OP | create/update/delete/import/export/approve/login/migrate | 操作类型 |
|
||
| ENTRY_STATUS | pending_approval/pending_entry/entered/cancelled/blocked | 入职状态 |
|
||
| REGISTER_STATUS | none/invited/filled | 登记表状态 |
|
||
|
||
枚举一律不硬编码于 dspy；CRUD 下拉经 models `codes` 段引用。
|
||
|
||
## 8. staff-mgr 数据迁移策略（v1 → 一期，NFR-8）
|
||
|
||
迁移工具：`repos/hr-system` 仓内规划 `scripts/migrate_staff_mgr.py`（Python + sqlor，幂等可重跑，输出核对报告：总数一致 + 抽样字段一致）。
|
||
|
||
| v1 源表（staff-mgr） | 目标表 | 映射规则 |
|
||
|---|---|---|
|
||
| staff_department_cache | org_unit | 按树形重建 parent_id；org_code=DEPT{id}；status 映射 active/inactive |
|
||
| staff_employee | roster_employee | employee_no/name/gender/phone/email 直映；id_number AES 解密后按新方案重加密（cipher+hash），id_source='manual'，证件有效期/签发机关置空待补；department_id→org_id；position 文本→预置 org_position（无匹配则建"未定职"）；status: active→regular、probation→probation、inactive→left；hire_date 直映 |
|
||
| staff_employee_extra | roster_field_value + contract_info | education/major/school/graduation_date/previous_company/work_years → 预置 roster_field_def 字段值；technical_level/salary_grade → 预置敏感字段值；contract_type/start/end → contract_info（type_id 映射正式/外包/实习协议） |
|
||
| staff_change_log | roster_timeline | change_type→event_type(transfer/field_change)，old/new 值入 content_json，event_type 前缀 `migrate:` 标识迁移来源 |
|
||
| staff_audit_log | sys_audit_log | 字段直映，module 标记 `staff-mgr-legacy` |
|
||
|
||
策略与约束：
|
||
1. **顺序**：org_unit → org_position（预置）→ roster_field_def/group（预置）→ roster_employee → roster_field_value → contract_info → timeline/audit。
|
||
2. **幂等**：按 employee_no/org_code 去重，重跑仅补差；证件号哈希冲突时输出冲突报告人工裁决（冲突员工按离职记录保留，不入在职）。
|
||
3. **范围**：默认"在职 + 离职保留档案"全量；仅迁在职/含历史的最终范围与切换时间点待 PM 确认（SRS §11-Q3），脚本参数 `--scope=active|all` 支持两种口径。
|
||
4. **切换**：UAT 通过后一次性迁移+双库比对 → staff-mgr 服务下线、仓库冻结只读（保留一个迭代周期）。
|
||
5. 迁移全程写 sys_audit_log（operation=migrate），迁移数据在时间轴/日志中可辨识。
|
||
|
||
## 9. 与需求验收项的映射（摘选）
|
||
|
||
| 验收项 | 数据支撑 |
|
||
|---|---|
|
||
| F01-4 时间轴留痕 | org_unit_change |
|
||
| F01-5 历史架构图 | org_unit.effective_date/expire_date + org_unit_change 回放 |
|
||
| F02-1~5 五套体系 | org_job/position/sequence/grade_level/grade_rank |
|
||
| F03-1/2 自定义字段与分组 | roster_field_def/group/value |
|
||
| F03-3 多规则工号 | roster_empno_rule |
|
||
| F03-4 自助修改审核 | roster_field_def.editable_self/need_audit + flow biz_type=roster_self_edit |
|
||
| F03-6 类型差异规则 | roster_type_rule + apply_types |
|
||
| F04-4 身份证手工录入（D1） | roster_employee/org_entry 证件字段组 + id_source='manual' |
|
||
| F04-5 复职 | roster_employee.prev_employee_id |
|
||
| F04-6 黑名单拦截 | org_blacklist（id_number_hash 匹配） |
|
||
| F05-3 转正提醒 | roster_employee.probation_end_date + sys_remind_rule(scene=regular) |
|
||
| F07-3 钉钉交接占位（D3） | org_leave_handover.item_type=dingtalk_resource |
|
||
| F07-5 离职证明 | org_leave.certificate_file（files/ 存储） |
|
||
| F09-6 电子签预留（D4） | contract_info.esign_status + contract_template.text_file_path |
|
||
| F10-2 字段权限 | flow_node_def.field_perm_json |
|
||
| F11-2 双维度数据范围 | sys_data_scope(scope_type=org/field) |
|
||
| F12-3 前后对比 | sys_audit_log.before_json/after_json |
|
||
| F13-6 报表数据范围受控 | 聚合查询叠加 sys_data_scope |
|
||
| F15-4 多场景提醒（≥6 类） | sys_remind_rule(scene_code) + REMIND_SCENE 字典 |
|
||
| F16-1~6 编制 | org_headcount_scheme/headcount_item/headcount_snapshot |
|
||
| F17-1~3 项目式组织 | org_project/org_project_post + org_job(job_category=horizontal) |
|
||
| FEAT-B1-01⑦ API 开放预留 | sys_openapp（一期不启用） |
|