34 KiB
34 KiB
物料管理系统数据库表说明
更新时间：2026-10-09。本文前半部分保留已有库的结构说明；第 11 节说明公司隔离升级后的运行结构。表数量随升级增加审计、迁移版本及对账表，以目标库 information_schema 查询为准。
本文按照“表、保存什么、主要字段”介绍每张表的作用，后面单独列出表之间的关系。公司隔离发布使用 scripts/init.sql（空库）或 scripts/upgrades/2026-10-08-company-master-data-isolation.sql（已有库），不以旧库快照代替升级后的结构。
通用字段：id 为记录主键；organization_id 为本方经营组织 ID；created_at、updated_at 分别为创建、更新时间。表已创建与对应业务功能已完整实现是两件事，未接入当前业务流程的结构在第 9 节说明。
1. 本方组织、用户与权限
| 表 | 保存什么 | 主要字段 |
|---|---|---|
organizations |
本方公司或核算主体，例如北京公司、武汉公司；用于确定业务数据归属。 | 组织ID（id）、组织编码（code）、组织名称（name）、状态（status）、默认时区（default_timezone）、created_at、updated_at |
users |
系统登录账号和用户说明。 | 用户ID（id）、用户名（username）、密码哈希（password）、用户说明（description）、created_at |
roles |
超级管理员、仓库管理员、采购、技术、生产、销售等角色定义。 | 角色ID（id）、角色编码（code）、角色名称（name）、是否启用（enabled）、created_at、updated_at |
permissions |
查看物料、维护供应商、维护出入库等可授权的操作能力。 | 权限ID（id）、权限编码（code）、权限名称（name）、created_at |
user_roles |
用户拥有哪些角色。 | 用户ID（user_id）、角色ID（role_id）、分配时间（created_at） |
role_permissions |
角色拥有哪些权限。 | 角色ID（role_id）、权限ID（permission_id）、授权时间（created_at） |
user_organizations |
用户可访问的组织范围及默认组织。 | 用户ID（user_id）、组织ID（organization_id）、默认组织标记（is_default）、created_at |
user_warehouses |
用户可访问的仓库范围。 | 用户ID（user_id）、仓库ID（warehouse_id）、created_at |
revoked_tokens |
被撤销令牌的登记结构，供令牌失效机制使用。 | 令牌标识（jti，主键）、用户ID（user_id）、原到期时间（expires_at）、撤销时间（revoked_at） |
2. 物料与基础字典
| 表 | 保存什么 | 主要字段 |
|---|---|---|
materials |
物料基础资料，用于识别和描述物料；库存余额另存。 | 物料ID（id）、所属组织（organization_id）、物料编码（material_code）、名称（name）、图片（material_image）、规格（spec）、厂商型号（manufacturer_model）、分类ID（category_id）、基本单位ID（base_unit_id）、单位文本（unit）、状态（status）、资料版本（version）、created_at、updated_at |
material_categories |
物料大类、子类及分类定义，形成分类树。 | 分类ID（id）、所属组织（organization_id）、编码（code）、名称（name）、分类说明（description）、父分类ID（parent_id）、状态（status） |
units |
个、瓶、包、升等计量单位字典及数量精度。 | 单位ID（id）、所属组织（organization_id）、单位编码（code）、单位名称（name）、小数位数（precision_scale）、状态（status） |
code_rules |
按组织和物料分类配置的编码生成规则。 | 规则ID（id）、所属组织（organization_id）、分类ID（category_id）、编码前缀（prefix）、流水号长度（sequence_length）、下一个流水号（next_sequence）、状态（status） |
material_unit_conversions |
特定物料在两个计量单位之间的换算系数及有效期。 | 规则ID（id）、所属组织（organization_id）、物料ID（material_id）、来源单位ID（from_unit_id）、目标单位ID（to_unit_id）、换算系数（conversion_rate）、生效日期（effective_from）、失效日期（effective_to） |
tags |
物料关联的唯一码或标签，用于按标签查找物料。 | 标签ID（id）、物料ID（material_id）、标签编码（tag_code）、created_at |
3. 客户、供应商与供货关系
| 表 | 保存什么 | 主要字段 |
|---|---|---|
business_partners |
企业基本资料，多个本方组织可以共用。 | 企业ID（id）、企业名称（legal_name）、统一社会信用代码（unified_credit_code）、联系人（contact_name）、电话（contact_phone）、地址（address）、created_at、updated_at |
organization_partners |
当前组织如何与这家企业合作。 | 关系ID（id）、所属组织（organization_id）、企业ID（business_partner_id）、伙伴编码（partner_code）、供应商身份（is_supplier）、客户身份（is_customer）、供应商状态（supplier_status）、客户状态（customer_status）、供应商分类（supplier_categories）、客户分类（customer_categories）、结算币种（settlement_currency）、付款条款（payment_terms）、created_at、updated_at |
material_suppliers |
某个物料由哪个供应商供货，以及报价和供货条件。 | 记录ID（id）、所属组织（organization_id）、物料ID（material_id）、组织伙伴关系ID（organization_partner_id）、价格（price）、币种（currency）、最小订购量（minimum_order_quantity）、供货周期天数（lead_time_days）、首选标记（is_preferred）、生效日期（effective_from）、失效日期（effective_to）、状态（status） |
同一外部企业可以与北京、武汉分别建立合作关系。企业名称、联系人、地址等基础资料共用；客户/供应商身份、状态和分类按组织分别保存。同一关系可同时具有客户和供应商身份。
supplier_categories 与 customer_categories 均支持多选：material 表示“物料级（生产）”，finished_product 表示“成品级（经营买卖）”；空集合表示未分类。
4. 仓库、库位与库存
| 表 | 保存什么 | 主要字段 |
|---|---|---|
warehouses |
本方组织的仓库资料。 | 仓库ID（id）、所属组织（organization_id）、仓库编码（code）、名称（name）、状态（status）、created_at、updated_at |
warehouse_locations |
某个仓库内部的存放位置。 | 库位ID（id）、所属仓库（warehouse_id）、库位编码（code）、名称（name）、状态（status）、created_at、updated_at |
inventory_balances |
某物料在某组织、仓库、库位的当前库存余额。 | 余额ID（id）、组织ID（organization_id）、仓库ID（warehouse_id）、库位ID（location_id）、物料ID（material_id）、现存量（on_hand）、预留量（reserved）、锁定量（locked）、在途量（in_transit）、库存版本（version）、updated_at |
stock_warning_rules |
某组织、仓库、物料的库存预警规则。 | 规则ID（id）、组织ID（organization_id）、仓库ID（warehouse_id）、物料ID（material_id）、预警阈值（minimum_available）、是否启用（enabled）、created_at、updated_at |
库位通过所属仓库确定组织。库存余额按库位保存，预警规则按仓库保存。当前预警查询使用现存量口径，启用且现存量小于等于阈值时预警，阈值为 0 也有效。
5. 入库与出库单据
| 表 | 保存什么 | 主要字段 |
|---|---|---|
inbound_orders |
一次入库操作的公共信息，即入库单表头。 | 单据ID（id）、所属组织（organization_id）、仓库ID（warehouse_id）、库位ID（location_id）、入库单号（inbound_no）、操作人（operator）、入库时间（inbound_date）、经手人（handler）、供应商文本（supplier）、备注（remark）、created_at、updated_at |
inbound_items |
入库单中逐项入库的物料和数量。 | 明细ID（id）、入库单ID（inbound_order_id）、物料ID（material_id）、数量（quantity）、created_at |
outbound_orders |
一次出库操作的公共信息，即出库单表头。 | 单据ID（id）、所属组织（organization_id）、仓库ID（warehouse_id）、库位ID（location_id）、出库单号（outbound_no）、操作人（operator）、出库时间（outbound_date）、经手人（handler）、接收方文本（receiver）、备注（remark）、旧库状态字段（status）、created_at、updated_at |
outbound_items |
出库单中逐项出库的物料和数量。 | 明细ID（id）、出库单ID（outbound_order_id）、物料ID（material_id）、数量（quantity）、created_at |
一张单据可以有多条物料明细；当前仓库、库位保存在表头，明细没有独立库位。supplier、receiver、operator、handler 均为文本，不是伙伴或用户表外键。
6. 物料申请与审批
| 表 | 保存什么 | 主要字段 |
|---|---|---|
material_applications |
一次物料领用申请的申请人、用途和审批结果。 | 申请ID（id）、申请人ID（user_id）、用途（purpose）、申请状态（status）、驳回原因（rejection_reason）、审批人ID（approved_by）、created_at、updated_at |
application_items |
申请中需要领用的物料和数量。 | 明细ID（id）、申请ID（application_id）、物料ID（material_id）、申请数量（quantity） |
申请状态为 pending（待审批）、approved（已通过）、rejected（已驳回）、cancelled（已取消）。当前申请明细引用已有物料；新增物料生成编码属于另一条主数据流程。
审批选择自动出库时，当前代码生成出库单和明细、扣减库存并记录流水；仅审批模式只更新审批状态。申请表没有直接指向生成出库单的字段，也没有 organization_id 或个人库存字段。
7. 物料变更日志与库存流水
| 表 | 保存什么 | 主要字段 |
|---|---|---|
material_logs |
物料资料新增、修改等操作及修改前后快照。 | 日志ID（id）、操作类型（operation_type）、物料ID（material_id）、旧值快照（old_value）、新值快照（new_value）、操作人（operator）、说明（description）、created_at |
stock_logs |
库存增减数量、方向和来源单据。 | 流水ID（id）、物料ID（material_id）、方向（type）、数量（quantity）、来源类型（related_order_type）、来源记录ID（related_order_id）、created_at |
material_logs 描述资料变化，stock_logs 描述数量变化。库存流水的 type 为 in 或 out；来源类型包含 inbound、outbound、application，分别指向入库单、出库单、申请主键。
8. 表之间的关系
8.1 关系如何理解
| 关系 | 保存方式 | 例子 |
|---|---|---|
| 一对多 | 多的一方保存主表ID。 | 一个入库单对应多个入库明细：inbound_items.inbound_order_id → inbound_orders.id。 |
| 多对多 | 中间表同时保存两边的ID。 | 用户与角色通过 user_roles 关联；组织与企业通过 organization_partners 关联。 |
| 自关联 | 记录引用同一张表的另一条记录。 | material_categories.parent_id → material_categories.id，表示分类父子关系。 |
| 逻辑关联 | 代码按字段查询，数据库未设置外键。 | 申请人ID关联用户、库存流水按来源类型及ID关联单据。 |
8.2 当前实际存在的外键
以下按数据库元数据逐项列出。外键确保引用记录存在；记录是否同属一个组织，还需要业务层校验。
| 子表字段 | 引用主表字段 |
|---|---|
application_items.application_id |
material_applications.id |
application_items.material_id |
materials.id |
code_rules.category_id |
material_categories.id |
code_rules.organization_id |
organizations.id |
inbound_items.material_id |
materials.id |
inbound_items.inbound_order_id |
inbound_orders.id |
inbound_orders.location_id |
warehouse_locations.id |
inbound_orders.organization_id |
organizations.id |
inbound_orders.warehouse_id |
warehouses.id |
inventory_balances.location_id |
warehouse_locations.id |
inventory_balances.material_id |
materials.id |
inventory_balances.organization_id |
organizations.id |
inventory_balances.warehouse_id |
warehouses.id |
material_categories.organization_id |
organizations.id |
material_categories.parent_id |
material_categories.id |
material_suppliers.material_id |
materials.id |
material_suppliers.organization_id |
organizations.id |
material_suppliers.organization_partner_id |
organization_partners.id |
material_unit_conversions.from_unit_id |
units.id |
material_unit_conversions.material_id |
materials.id |
material_unit_conversions.organization_id |
organizations.id |
material_unit_conversions.to_unit_id |
units.id |
materials.base_unit_id |
units.id |
materials.category_id |
material_categories.id |
materials.organization_id |
organizations.id |
organization_partners.organization_id |
organizations.id |
organization_partners.business_partner_id |
business_partners.id |
outbound_items.material_id |
materials.id |
outbound_items.outbound_order_id |
outbound_orders.id |
outbound_orders.location_id |
warehouse_locations.id |
outbound_orders.organization_id |
organizations.id |
outbound_orders.warehouse_id |
warehouses.id |
revoked_tokens.user_id |
users.id |
role_permissions.permission_id |
permissions.id |
role_permissions.role_id |
roles.id |
stock_logs.material_id |
materials.id |
stock_warning_rules.material_id |
materials.id |
stock_warning_rules.organization_id |
organizations.id |
stock_warning_rules.warehouse_id |
warehouses.id |
tags.material_id |
materials.id |
units.organization_id |
organizations.id |
user_organizations.organization_id |
organizations.id |
user_organizations.user_id |
users.id |
user_roles.role_id |
roles.id |
user_roles.user_id |
users.id |
user_warehouses.user_id |
users.id |
user_warehouses.warehouse_id |
warehouses.id |
warehouse_locations.warehouse_id |
warehouses.id |
warehouses.organization_id |
organizations.id |
8.3 逻辑关联及间接关联
| 表之间的关系 | 关联字段 | 说明 |
|---|---|---|
| 申请 → 申请人 | material_applications.user_id → users.id |
当前实际库未设置用户外键，代码查询申请人资料。 |
| 申请 → 审批人 | material_applications.approved_by → users.id |
当前无外键；未审批时通常为 0。 |
| 物料日志 → 物料 | material_logs.material_id → materials.id |
当前实际库无外键。 |
| 库存流水 → 入库单 | related_order_type = inbound 时，related_order_id → inbound_orders.id |
按类型和ID配对查询。 |
| 库存流水 → 出库单 | related_order_type = outbound 时，related_order_id → outbound_orders.id |
按类型和ID配对查询。 |
| 库存流水 → 申请 | related_order_type = application 时，related_order_id → material_applications.id |
自动出库流水保存的是申请ID，不是生成的出库单ID。 |
| 库位 → 组织 | warehouse_locations.warehouse_id → warehouses.organization_id |
通过仓库间接确定组织。 |
| 物料 → 编码规则 | 组织及分类对应 code_rules |
物料只保存生成的编码文本，没有编码规则ID字段。 |
| 申请 → 自动生成的出库单 | 当前没有直接关联字段 | 属于业务生成过程，不能按现有表头字段直接定位关联单据。 |
8.4 主要唯一约束
| 表 | 唯一字段或组合 | 含义 |
|---|---|---|
organizations、roles、permissions |
code |
各自表内编码唯一。 |
users |
username |
用户名唯一。 |
business_partners |
unified_credit_code |
已填写的信用代码唯一，允许为空；企业名称没有唯一约束。 |
organization_partners |
(organization_id, business_partner_id)；(organization_id, partner_code) |
同一组织与同一企业只有一条合作关系；伙伴编码在组织内唯一。 |
materials |
(organization_id, material_code) |
物料编码在组织内唯一。 |
material_categories、units、warehouses |
(organization_id, code) |
分类、单位、仓库编码分别在组织内唯一。 |
warehouse_locations |
(warehouse_id, code) |
库位编码在仓库内唯一。 |
code_rules |
(organization_id, category_id) |
同一组织同一分类只有一条编码规则。 |
inventory_balances |
(organization_id, warehouse_id, location_id, material_id) |
同一库存维度只有一条余额。 |
stock_warning_rules |
(organization_id, warehouse_id, material_id) |
同一仓库同一物料只有一条预警规则。 |
tags |
tag_code |
标签编码全表唯一。 |
inbound_orders、outbound_orders |
inbound_no、outbound_no |
各自单据表内单号唯一。 |
user_roles |
(user_id, role_id) 联合主键 |
同一用户不会重复分配同一角色。 |
role_permissions |
(role_id, permission_id) 联合主键 |
同一角色不会重复分配同一权限。 |
user_organizations |
(user_id, organization_id) 联合主键 |
同一组织授权不会重复。 |
user_warehouses |
(user_id, warehouse_id) 联合主键 |
同一仓库授权不会重复。 |
9. 当前结构与使用范围
| 项目 | 当前情况 |
|---|---|
| 令牌撤销 | revoked_tokens 结构已建，当前认证代码未查询该表；退出登录主要由客户端清除令牌。 |
| 物料单位换算 | material_unit_conversions 结构已建，当前后端未见该表业务读写路径；库存余额支持小数，但出入库、申请明细及流水数量仍为 INT。 |
| 物料供应商条件 | material_suppliers 结构已建，用于供货、报价等需求；当前后端未见完整维护路径。 |
| 实物追踪 | tags 只有物料和标签关系，没有库位、单据明细或实物流转状态；物料表没有序列号开关。 |
| 组织范围 | 多数业务表有组织字段；当前申请表、库存流水和物料日志没有直接组织字段。角色分配也没有按组织分别保存。 |
| 伙伴资料共享 | 多个组织引用同一企业ID时，修改企业名称、联系人等基础资料会影响共享记录；合作身份、分类、状态分别保存。当前新建接口仍直接新增企业记录。 |
| 库存口径 | 结构支持现存、预留、锁定、在途数量；当前主要出入库代码修改默认组织、默认仓库、主库位的现存量。 |
| 可用库存 | 设计口径为 on_hand - reserved - locked；当前预警查询使用现存量，不能仅根据阈值字段名推断已使用可用量。 |
| 日志口径 | 物料日志实际库快照为JSON、操作类型为ENUM；库存流水编辑单据时可能重建，不是不可修改的完整审计日志。 |
| 出库状态 | 当前库保留 outbound_orders.status，但现有代码未按此列维护状态；初始化脚本没有该列。 |
| 物料类型 | 初始化脚本另有 material_types 和 materials.material_type_id；当前实际库及当前物料模型没有这两项。该表未计入本文的30张现有表。 |
| 申请单号 | 页面字段 application_no 由申请主键派生，申请表没有该列。 |
| 后续模块 | 当前库没有BOM专用表、盘点单专用表、周期提醒专用表或申请人个人库存表。 |
10. 数据库初始化
| 文件 | 用途 |
|---|---|
scripts/init.sql |
空库首次初始化，包含表结构、客户和供应商分类字段、物料分类及编码规则，以及明确标注的20家虚构企业示例数据。 |
初始化脚本直接建立分类说明字段、6 个大类和 28 个子类、父子分类关系，以及“子类编码 + 4 位流水号”的编码规则。出入库表直接包含单据编号、操作人和备注字段。
库存预警阈值为 0 是有效配置，表示库存降到 0 时预警；新库没有需要清理的历史预警规则。
已有数据库需要保留数据时，应先备份，再根据实际结构差异准备并验证增量 SQL；不要直接重复执行完整初始化脚本。
11. 公司隔离发布结构与升级（2026-10-09）
本节更新第 9 节的旧库观察：当前申请和库存流水已有显式组织字段，库存业务不再使用默认组织/仓库/库位兜底。物料日志通过所属物料限制公司；功能角色仍是全局角色，公司授权不新增公司级角色。本期公司授权覆盖唯一仓库，user_warehouses 不作为额外授权配置入口。
| 表/约束 | 发布后要求 |
|---|---|
user_organizations.default_user_id |
根据 is_default 生成，唯一键限制每个用户至多一个默认公司；服务层验证默认公司属于启用授权列表 |
warehouses |
UNIQUE(organization_id)，停用仓库仍占名额 |
warehouse_locations |
UNIQUE(warehouse_id)，停用库位仍占名额 |
inventory_balances |
余额以 (organization_id, warehouse_id, location_id, material_id) 唯一，事务锁定完整维度并条件扣减 |
inbound_orders、outbound_orders |
三个库存维度为非空归属字段；创建由后端解析，修改/删除锁定本公司原单并按原库存维度恢复原单造成的库存变化 |
stock_logs |
保存非空 organization_id/warehouse_id/location_id，索引 (organization_id, material_id, created_at)；关联单据和物料均校验公司 |
material_applications |
非空 organization_id；索引 (organization_id,user_id,created_at)、(organization_id,status,created_at)；状态更新携带原状态条件 |
organization_audit_logs |
actor_user_id、可空 organization_id、action、JSON old_value/new_value、created_at；按公司/时间和操作者/时间建索引 |
schema_migrations |
增量升级记录版本、running/completed/failed、起止时间和 JSON 说明；DDL 部分提交后禁止盲目重跑 |
schema_migration_reconciliations |
增量升级按公司保存 before/after 余额四种数量、单据/申请/流水数量，主键 (version,phase,organization_id) |
数据库普通外键只保证引用存在，不代替同公司校验。分类父级、物料分类/基本单位、编码规则分类、单据/申请物料及库存仓库/库位都由服务层验证归属，读写 SQL 同时限制公司。编码唯一范围保持组织内；出入库单号保持现有全表唯一规则。
自动出库审批在一个事务内锁定申请、扣减该公司余额、插入该公司出库单和流水、更新申请状态；库存不足、流水写入故障均回滚。服务启动只检查申请所需组织列，缺列报升级错误，不使用旧定义自动创建申请表。
scripts/checks/company-master-data-integrity.sql 是独立检查的权威来源，结果列出异常和普通用户授权回填清单。它使用临时表，不修改永久业务数据；严格只读账户若无临时表权限，应在副本派生的隔离库执行。scripts/sync-company-integrity.ps1 -Check 检查增量脚本中的内嵌检查与此文件一致。
升级发现多个仓库/库位、多个默认公司、跨组织引用、未确认授权或无法确定流水来源时先阻断，禁止自动删除、合并或猜测归属。授权清单经业务确认后显式回填，不自动给普通用户授权所有公司。新公司分类/单位/规则默认为空，独立配置。
武汉目前没有历史数据（2026-10-09 用户确认），本次没有武汉历史副本检查或授权回填对象；空库初始化及合成历史夹具升级分别验证。部署、验收证据及回退限制见 公司隔离验收与交付。