249 lines
9.0 KiB
Markdown
249 lines
9.0 KiB
Markdown
# SaaS：租户、用户、权限、日志
|
||
|
||
约定见 [00-conventions.md](./00-conventions.md)。本文件在 Phase 1 锁定，Phase 4 写迁移。
|
||
|
||
认证令牌用 Laravel Sanctum 自带的 `personal_access_tokens`，不另建 `wms_` 表。
|
||
|
||
## wms_tenant 租户
|
||
|
||
用途：租户注册表。行本身就是租户，不套 TenantContext 的自动过滤。
|
||
|
||
| 字段 | 类型 | 可空 | 默认 | 说明 |
|
||
| --- | --- | --- | --- | --- |
|
||
| id | BIGINT UNSIGNED | N | AUTO_INCREMENT | 内部主键 |
|
||
| tenant_code | VARCHAR(32) | N | | 租户编码 |
|
||
| name | VARCHAR(128) | N | | 租户名称 |
|
||
| status | VARCHAR(32) | N | active | 状态 |
|
||
| timezone | VARCHAR(64) | N | UTC | IANA 时区 |
|
||
| created_at | TIMESTAMP | Y | | |
|
||
| updated_at | TIMESTAMP | Y | | |
|
||
|
||
索引：
|
||
|
||
| 名称 | 类型 | 字段 | 支撑的查询 |
|
||
| --- | --- | --- | --- |
|
||
| uk_tenant_code | UNIQUE | (tenant_code) | 按租户编码查找 |
|
||
|
||
状态：`active` 可用，`suspended` 停用（拒绝新的业务写入）。
|
||
|
||
关联：被所有带 `tenant_code` 的表引用。
|
||
|
||
并发 / 审计：无乐观锁。不停用后物理删除。状态变更写 `wms_status_log`，`biz_type = tenant`。
|
||
|
||
## wms_user 用户
|
||
|
||
用途：租户内登录用户。
|
||
|
||
| 字段 | 类型 | 可空 | 默认 | 说明 |
|
||
| --- | --- | --- | --- | --- |
|
||
| id | BIGINT UNSIGNED | N | AUTO_INCREMENT | 内部主键 |
|
||
| tenant_code | VARCHAR(32) | N | | 租户编码 |
|
||
| user_code | VARCHAR(64) | N | | 用户编码 |
|
||
| name | VARCHAR(128) | N | | 显示名 |
|
||
| email | VARCHAR(255) | N | | 登录邮箱 |
|
||
| password | VARCHAR(255) | N | | 密码哈希，禁止写入日志 |
|
||
| status | VARCHAR(32) | N | active | 状态 |
|
||
| last_login_at | TIMESTAMP | Y | | 最近登录 |
|
||
| created_at | TIMESTAMP | Y | | |
|
||
| updated_at | TIMESTAMP | Y | | |
|
||
| deleted_at | TIMESTAMP | Y | | 软删除 |
|
||
|
||
索引：
|
||
|
||
| 名称 | 类型 | 字段 | 支撑的查询 |
|
||
| --- | --- | --- | --- |
|
||
| uk_tenant_user_code | UNIQUE | (tenant_code, user_code) | 按用户编码查找 |
|
||
| uk_tenant_email | UNIQUE | (tenant_code, email) | 登录 |
|
||
|
||
状态：`active`、`disabled`。
|
||
|
||
关联：`wms_user_role.user_code`。操作人字段一律存 `user_code`。
|
||
|
||
并发 / 审计：无乐观锁。软删除规则见约定。权限变更写 `wms_operation_log`。
|
||
|
||
## wms_role 角色
|
||
|
||
用途：租户内角色。
|
||
|
||
| 字段 | 类型 | 可空 | 默认 | 说明 |
|
||
| --- | --- | --- | --- | --- |
|
||
| id | BIGINT UNSIGNED | N | AUTO_INCREMENT | 内部主键 |
|
||
| tenant_code | VARCHAR(32) | N | | 租户编码 |
|
||
| role_code | VARCHAR(64) | N | | 角色编码 |
|
||
| name | VARCHAR(128) | N | | 角色名称 |
|
||
| created_at | TIMESTAMP | Y | | |
|
||
| updated_at | TIMESTAMP | Y | | |
|
||
| deleted_at | TIMESTAMP | Y | | 软删除 |
|
||
|
||
索引：
|
||
|
||
| 名称 | 类型 | 字段 | 支撑的查询 |
|
||
| --- | --- | --- | --- |
|
||
| uk_tenant_role_code | UNIQUE | (tenant_code, role_code) | 按角色编码查找 |
|
||
|
||
关联：`wms_user_role`、`wms_role_permission`。
|
||
|
||
并发 / 审计：被用户引用时不能软删。
|
||
|
||
## wms_permission 权限目录
|
||
|
||
用途：系统预置权限，全租户共用。不按租户复制，因此没有 `tenant_code`。
|
||
|
||
| 字段 | 类型 | 可空 | 默认 | 说明 |
|
||
| --- | --- | --- | --- | --- |
|
||
| id | BIGINT UNSIGNED | N | AUTO_INCREMENT | 内部主键 |
|
||
| permission_code | VARCHAR(64) | N | | 权限编码，如 `inbound.receive` |
|
||
| name | VARCHAR(128) | N | | 名称 |
|
||
| module | VARCHAR(64) | N | | 模块，如 `inbound` |
|
||
| created_at | TIMESTAMP | Y | | |
|
||
| updated_at | TIMESTAMP | Y | | |
|
||
|
||
索引：
|
||
|
||
| 名称 | 类型 | 字段 | 支撑的查询 |
|
||
| --- | --- | --- | --- |
|
||
| uk_permission_code | UNIQUE | (permission_code) | 按权限编码查找 |
|
||
|
||
关联：`wms_role_permission.permission_code`。数据由迁移或 Seeder 写入，不开放租户自行新增。
|
||
|
||
## wms_role_permission 角色权限
|
||
|
||
用途：租户角色与权限目录的对应。
|
||
|
||
| 字段 | 类型 | 可空 | 默认 | 说明 |
|
||
| --- | --- | --- | --- | --- |
|
||
| id | BIGINT UNSIGNED | N | AUTO_INCREMENT | 内部主键 |
|
||
| tenant_code | VARCHAR(32) | N | | 租户编码 |
|
||
| role_code | VARCHAR(64) | N | | 角色编码 |
|
||
| permission_code | VARCHAR(64) | N | | 权限编码 |
|
||
| created_at | TIMESTAMP | Y | | |
|
||
| updated_at | TIMESTAMP | Y | | |
|
||
|
||
索引：
|
||
|
||
| 名称 | 类型 | 字段 | 支撑的查询 |
|
||
| --- | --- | --- | --- |
|
||
| uk_tenant_role_permission | UNIQUE | (tenant_code, role_code, permission_code) | 防止重复授权 |
|
||
| idx_tenant_permission | INDEX | (tenant_code, permission_code) | 反查拥有某权限的角色 |
|
||
|
||
并发 / 审计：变更写 `wms_operation_log`。无软删除，收回权限即删行。
|
||
|
||
## wms_user_role 用户角色
|
||
|
||
用途：用户与角色的对应。
|
||
|
||
| 字段 | 类型 | 可空 | 默认 | 说明 |
|
||
| --- | --- | --- | --- | --- |
|
||
| id | BIGINT UNSIGNED | N | AUTO_INCREMENT | 内部主键 |
|
||
| tenant_code | VARCHAR(32) | N | | 租户编码 |
|
||
| user_code | VARCHAR(64) | N | | 用户编码 |
|
||
| role_code | VARCHAR(64) | N | | 角色编码 |
|
||
| created_at | TIMESTAMP | Y | | |
|
||
| updated_at | TIMESTAMP | Y | | |
|
||
|
||
索引：
|
||
|
||
| 名称 | 类型 | 字段 | 支撑的查询 |
|
||
| --- | --- | --- | --- |
|
||
| uk_tenant_user_role | UNIQUE | (tenant_code, user_code, role_code) | 防止重复绑定 |
|
||
| idx_tenant_role | INDEX | (tenant_code, role_code) | 查角色下的用户 |
|
||
|
||
并发 / 审计：同角色权限。无软删除。
|
||
|
||
## wms_tenant_setting 租户设置
|
||
|
||
用途：租户级配置。值用字符串，由应用解析。
|
||
|
||
| 字段 | 类型 | 可空 | 默认 | 说明 |
|
||
| --- | --- | --- | --- | --- |
|
||
| id | BIGINT UNSIGNED | N | AUTO_INCREMENT | 内部主键 |
|
||
| tenant_code | VARCHAR(32) | N | | 租户编码 |
|
||
| setting_key | VARCHAR(64) | N | | 配置键 |
|
||
| setting_value | TEXT | Y | | 配置值 |
|
||
| created_at | TIMESTAMP | Y | | |
|
||
| updated_at | TIMESTAMP | Y | | |
|
||
|
||
索引：
|
||
|
||
| 名称 | 类型 | 字段 | 支撑的查询 |
|
||
| --- | --- | --- | --- |
|
||
| uk_tenant_setting_key | UNIQUE | (tenant_code, setting_key) | 读取一项配置 |
|
||
|
||
并发 / 审计：覆盖写 `updated_at`。变更写 `wms_operation_log`。不存第三方密钥明文以外的内容；密钥加密存储留到平台对接 Phase。
|
||
|
||
## wms_number_sequence 取号
|
||
|
||
用途：租户内单号序号。见 [00-conventions.md](./00-conventions.md)。
|
||
|
||
| 字段 | 类型 | 可空 | 默认 | 说明 |
|
||
| --- | --- | --- | --- | --- |
|
||
| id | BIGINT UNSIGNED | N | AUTO_INCREMENT | 内部主键 |
|
||
| tenant_code | VARCHAR(32) | N | | 租户编码 |
|
||
| biz_type | VARCHAR(32) | N | | 单据类型，如 `inbound` |
|
||
| period | CHAR(8) | N | | `yyyyMMdd` |
|
||
| current_value | INT UNSIGNED | N | 0 | 当日已分配到的序号 |
|
||
| created_at | TIMESTAMP | Y | | |
|
||
| updated_at | TIMESTAMP | Y | | |
|
||
|
||
索引：
|
||
|
||
| 名称 | 类型 | 字段 | 支撑的查询 |
|
||
| --- | --- | --- | --- |
|
||
| uk_tenant_biz_period | UNIQUE | (tenant_code, biz_type, period) | 取号时锁这一行 |
|
||
|
||
并发 / 审计：`SELECT ... FOR UPDATE` 后递增。独立短事务。不软删除。
|
||
|
||
## wms_status_log 状态变更日志
|
||
|
||
用途：状态机审计。只追加。登录日志与 API 日志留在后续 `95-audit.md`。
|
||
|
||
| 字段 | 类型 | 可空 | 默认 | 说明 |
|
||
| --- | --- | --- | --- | --- |
|
||
| id | BIGINT UNSIGNED | N | AUTO_INCREMENT | 内部主键 |
|
||
| tenant_code | VARCHAR(32) | N | | 租户编码 |
|
||
| biz_type | VARCHAR(32) | N | | `order`、`inbound`、`outbound`、`wave`、`adjust`、`stocktake`、`tenant` |
|
||
| biz_no | VARCHAR(64) | N | | 业务单号 |
|
||
| from_status | VARCHAR(32) | Y | | 变更前，新建时可空 |
|
||
| to_status | VARCHAR(32) | N | | 变更后 |
|
||
| operator_user_code | VARCHAR(64) | Y | | 操作人，系统任务为空 |
|
||
| request_id | VARCHAR(64) | Y | | 请求标识 |
|
||
| remark | VARCHAR(500) | Y | | |
|
||
| created_at | TIMESTAMP | Y | | |
|
||
| updated_at | TIMESTAMP | Y | | 行创建后不更新 |
|
||
|
||
索引：
|
||
|
||
| 名称 | 类型 | 字段 | 支撑的查询 |
|
||
| --- | --- | --- | --- |
|
||
| idx_tenant_biz_created | INDEX | (tenant_code, biz_type, biz_no, created_at) | 查一张单据的状态历史 |
|
||
|
||
并发 / 审计：禁止更新和删除。与状态变更同一事务写入。
|
||
|
||
## wms_operation_log 操作日志
|
||
|
||
用途：主数据、权限、设置等关键变更。只追加。
|
||
|
||
| 字段 | 类型 | 可空 | 默认 | 说明 |
|
||
| --- | --- | --- | --- | --- |
|
||
| id | BIGINT UNSIGNED | N | AUTO_INCREMENT | 内部主键 |
|
||
| tenant_code | VARCHAR(32) | N | | 租户编码 |
|
||
| operator_user_code | VARCHAR(64) | Y | | 操作人 |
|
||
| action | VARCHAR(64) | N | | 如 `user.update`、`role.grant` |
|
||
| biz_type | VARCHAR(32) | N | | 对象类型 |
|
||
| biz_no | VARCHAR(64) | N | | 对象编码 |
|
||
| biz_ip | VARCHAR(64) | Y | | 来源 IP |
|
||
| request_id | VARCHAR(64) | Y | | 请求标识 |
|
||
| before_data | JSON | Y | | 变更前 |
|
||
| after_data | JSON | Y | | 变更后 |
|
||
| created_at | TIMESTAMP | Y | | |
|
||
| updated_at | TIMESTAMP | Y | | 行创建后不更新 |
|
||
|
||
索引：
|
||
|
||
| 名称 | 类型 | 字段 | 支撑的查询 |
|
||
| --- | --- | --- | --- |
|
||
| idx_tenant_biz_created | INDEX | (tenant_code, biz_type, biz_no, created_at) | 查某对象的操作历史 |
|
||
| idx_tenant_created | INDEX | (tenant_code, created_at) | 按时间查日志 |
|
||
|
||
并发 / 审计：禁止更新和删除。`before_data` / `after_data` 不得包含密码、Token。
|