🗄️ 数据库设计师 | 步骤 5/7 | 24.5s
1. 数据库组合
推荐：PostgreSQL + Redis + 对象存储/文件服务器。
PostgreSQL 作为主库，适合强一致工单流转、复杂 JOIN、JSONB 测试数据、审计追溯；Redis 用于扫码幂等锁、短期会话/权限缓存；附件、测试日志、图片放对象存储，库内只存 URL/hash。后续若测试时序数据极大，可引入 TimescaleDB 扩展。
2. 核心表
work_order、work_order_item、process_route、process_step、operation_task、test_record、material、material_batch、material_binding、equipment、workstation、exception_record、repair_record、trace_event、app_user、role、permission、user_role、role_permission。
3. 关键表结构概要
work_order(id pk, order_no unique, product_model, batch_no, qty,
route_id fk, status, plan_start, plan_end, created_by, created_at)
work_order_item(id pk, work_order_id fk, sn unique, status,
current_step_id fk, frozen boolean, completed_at)
process_route(id pk, code, name, version, status, effective_from)
process_step(id pk, route_id fk, seq_no, code, name, type,
required_role_id, need_test bool, is_qc bool)
operation_task(id pk, work_order_id fk, item_id fk, step_id fk,
status, operator_id fk, workstation_id fk, device_id fk,
started_at, finished_at, result, locked bool)
test_record(id pk, task_id fk, item_id fk, step_id fk,
test_item, standard, lower_limit, upper_limit, value, unit,
result, raw_data jsonb, attachment_url, attachment_hash, tested_by, tested_at)
material_binding(id pk, task_id fk, item_id fk, material_id fk,
material_batch_id fk, supplier_id, qty, bound_by, bound_at)
exception_record(id pk, work_order_id fk, item_id fk, task_id fk,
type, level, status, reason, action, decision, owner_id,
created_at, closed_at)
repair_record(id pk, exception_id fk, item_id fk, action,
repaired_by, repaired_at, retest_task_id fk, result)
trace_event(id pk, entity_type, entity_id, sn, work_order_id fk,
task_id fk, action, before_state, after_state, actor_id fk,
role_code, workstation_id fk, device_id fk, material_batch_id fk,
occurred_at, result, reason, source_ip, attachment_hash,
previous_hash, current_hash)
app_user(id pk, username unique, name, status)
role(id pk, code unique, name)
permission(id pk, code unique, name)
user_role(user_id fk, role_id fk, pk(user_id,role_id))
role_permission(role_id fk, permission_id fk, pk(role_id,permission_id))4. 主外键关系
工单 work_order 绑定路线 process_route；路线包含多个 process_step；工单生成多个 work_order_item/SN；每个 SN 按工序生成 operation_task；任务关联测试、物料、异常、返修；所有关键动作追加到 trace_event。
5. 索引设计
create index idx_wo_no on work_order(order_no);
create index idx_item_sn on work_order_item(sn);
create index idx_task_item_step on operation_task(item_id, step_id);
create index idx_task_wo_status on operation_task(work_order_id, status);
create index idx_test_item_time on test_record(item_id, tested_at desc);
create index idx_mat_batch on material_binding(material_batch_id, item_id);
create index idx_ex_item_status on exception_record(item_id, status);
create index idx_event_sn_time on trace_event(sn, occurred_at desc);
create index idx_event_wo_time on trace_event(work_order_id, occurred_at desc);
create index idx_event_actor_time on trace_event(actor_id, occurred_at desc);
create index idx_event_device_time on trace_event(device_id, occurred_at desc);外键列必须建索引，追溯查询禁止 SELECT *，上线前用 EXPLAIN ANALYZE 验证。
6. 一致性与历史版本
状态流转用数据库约束+应用状态机双控；扫码开工对 (item_id, step_id) 加唯一约束防重复；测试提交后 locked=true 禁改。业务表可更新，trace_event 只追加；管理员修正生成更正事件。工艺路线和工序采用 version + effective_from，历史工单固定引用旧版本。
7. 归档与性能
trace_event、test_record 按月/季度分区；已关闭工单超过周期归档到历史表或冷库。常用追溯接口可建物化视图：SN 全链路、批次异常率、设备参与记录。启用 pg_stat_statements 监控慢查询；.NET 使用连接池，报表导出走异步任务，避免拖慢生产事务。