Neo-ZQYY/docs/database/BD_manual_public_rbac_tables.md

BD 手册：public Schema — RBAC 与工作流（8 表）

目标库：zqyy_app（通过 APP_DB_DSN 连接） DDL 位置：docs/database/ddl/zqyy_app__public.sql 关联文档：BD_manual_scheduled_tasks.md（调度相关 3 表）、BD_manual_member_retention_clue.md（维客线索表）

---

1. 概述

public Schema 共 12 张表，本文档覆盖其中 RBAC（角色权限）+ 工作流（任务审批）共 8 张表。其余 4 张表已有独立文档：

表	文档
scheduled_tasks、task_queue、task_execution_log	BD_manual_scheduled_tasks.md
member_retention_clue	BD_manual_member_retention_clue.md

表关系

admin_users（管理后台登录账户，roles 数组字段）
                                          
users ──┬── user_roles ── roles ── role_permissions ── permissions
        │   （用户-角色映射）  （角色）  （角色-权限映射）     （权限）
        │
        ├── tasks（任务指派，creator_id / assignee_id → users）
        │     │
        └── approvals（审批记录，approver_id → users，task_id → tasks）

---

2. 表结构

public.admin_users（9 字段）

管理后台（admin-web / tenant-admin）登录账户。通过 JWT aud=admin 或 aud=tenant-admin 认证。

字段	类型	约束	说明
id	SERIAL	PK	自增主键
username	VARCHAR(64)	NOT NULL, UNIQUE	登录用户名
password_hash	VARCHAR(256)	NOT NULL	bcrypt 密码哈希
display_name	VARCHAR(128)	可空	显示名称
site_id	BIGINT	NOT NULL	所属门店 ID
is_active	BOOLEAN	DEFAULT true	是否启用
roles	TEXT[]	NOT NULL, DEFAULT '{site_admin}'	角色数组（site_admin / tenant_admin 等）
created_at	TIMESTAMPTZ	DEFAULT NOW()	创建时间
updated_at	TIMESTAMPTZ	DEFAULT NOW()	更新时间

注意：admin_users.roles 是 TEXT 数组字段（快捷权限标记），与 roles 表的 RBAC 体系并行使用。admin-web 当前主要依赖此数组字段做权限判断。

public.users（8 字段）

小程序端用户（C 端会员），通过微信 code → JWT aud=miniapp 认证。

字段	类型	约束	说明
id	BIGSERIAL	PK	自增主键
wx_openid	TEXT	UNIQUE，可空	微信 OpenID（登录唯一标识）
mobile	TEXT	可空	手机号
nickname	TEXT	可空	用户昵称
status	INTEGER	DEFAULT 1	状态（1=正常）
site_id	BIGINT	NOT NULL	所属门店 ID
created_at	TIMESTAMPTZ	DEFAULT NOW()	创建时间
updated_at	TIMESTAMPTZ	DEFAULT NOW()	更新时间

public.roles（5 字段）

角色定义表，按门店隔离。

字段	类型	约束	说明
id	SERIAL	PK	自增主键
name	TEXT	NOT NULL, UNIQUE	角色名称（全局唯一）
description	TEXT	可空	角色描述
site_id	BIGINT	NOT NULL	所属门店 ID
created_at	TIMESTAMPTZ	DEFAULT NOW()	创建时间

public.permissions（4 字段）

权限定义表（资源+动作组合）。

字段	类型	约束	说明
id	SERIAL	PK	自增主键
resource	TEXT	NOT NULL	资源标识（如 etl、users）
action	TEXT	NOT NULL	操作标识（如 read、write、execute）
description	TEXT	可空	权限描述

public.role_permissions（2 字段）

角色-权限关联表（多对多）。

字段	类型	约束	说明
role_id	INTEGER	PK, FK → roles(id) ON DELETE CASCADE	角色 ID
permission_id	INTEGER	PK, FK → permissions(id) ON DELETE CASCADE	权限 ID

public.user_roles（3 字段）

用户-角色关联表（多对多），按门店隔离。

字段	类型	约束	说明
user_id	BIGINT	PK, FK → users(id) ON DELETE CASCADE	用户 ID
role_id	INTEGER	PK, FK → roles(id) ON DELETE CASCADE	角色 ID
site_id	BIGINT	NOT NULL	门店 ID

public.tasks（9 字段）

任务指派表，用于门店内任务管理。

字段	类型	约束	说明
id	BIGSERIAL	PK	自增主键
title	TEXT	NOT NULL	任务标题
description	TEXT	可空	任务描述
status	TEXT	DEFAULT 'pending'	状态：pending / in_progress / completed
assignee_id	BIGINT	FK → users(id)，可空	负责人 ID
creator_id	BIGINT	FK → users(id)，可空	创建人 ID
site_id	BIGINT	NOT NULL	门店 ID
created_at	TIMESTAMPTZ	DEFAULT NOW()	创建时间
updated_at	TIMESTAMPTZ	DEFAULT NOW()	更新时间

public.approvals（7 字段）

审批记录表，关联到任务。

字段	类型	约束	说明
id	BIGSERIAL	PK	自增主键
task_id	BIGINT	FK → tasks(id) ON DELETE CASCADE，可空	关联任务 ID
approver_id	BIGINT	FK → users(id)，可空	审批人 ID
status	TEXT	DEFAULT 'pending'	状态：pending / approved / rejected
comment	TEXT	可空	审批意见
site_id	BIGINT	NOT NULL	门店 ID
created_at	TIMESTAMPTZ	DEFAULT NOW()	创建时间

---

3. 约束与索引

表	约束/索引名	类型	说明
admin_users	admin_users_pkey	PK	(id)
admin_users	admin_users_username_key	UNIQUE	(username)
admin_users	idx_admin_users_site	INDEX	(site_id)
users	users_pkey	PK	(id)
users	users_wx_openid_key	UNIQUE	(wx_openid)
users	idx_users_site_id	INDEX	(site_id)
users	idx_users_mobile	INDEX	(mobile)
roles	roles_pkey	PK	(id)
roles	roles_name_key	UNIQUE	(name)
roles	idx_roles_site_id	INDEX	(site_id)
permissions	permissions_pkey	PK	(id)
permissions	permissions_resource_action_key	UNIQUE	(resource, action)
role_permissions	role_permissions_pkey	PK	(role_id, permission_id)
role_permissions	FK role_id	FK	→ roles(id) ON DELETE CASCADE
role_permissions	FK permission_id	FK	→ permissions(id) ON DELETE CASCADE
user_roles	user_roles_pkey	PK	(user_id, role_id)
user_roles	FK user_id	FK	→ users(id) ON DELETE CASCADE
user_roles	FK role_id	FK	→ roles(id) ON DELETE CASCADE
user_roles	idx_user_roles_site_id	INDEX	(site_id)
tasks	tasks_pkey	PK	(id)
tasks	FK assignee_id	FK	→ users(id)
tasks	FK creator_id	FK	→ users(id)
tasks	idx_tasks_site_id	INDEX	(site_id)
tasks	idx_tasks_status	INDEX	(status)
tasks	idx_tasks_assignee_id	INDEX	(assignee_id)
approvals	approvals_pkey	PK	(id)
approvals	FK task_id	FK	→ tasks(id) ON DELETE CASCADE
approvals	FK approver_id	FK	→ users(id)
approvals	idx_approvals_site_id	INDEX	(site_id)
approvals	idx_approvals_task_id	INDEX	(task_id)

---

4. 兼容性影响

组件	影响
admin-web	直接依赖。登录用 admin_users，权限判断用 admin_users.roles 数组
tenant-admin	直接依赖。登录也用 admin_users（aud=tenant-admin），按 site_id 隔离
后端 API	admin_users 用于 JWT 签发和验证；RBAC 表（roles/permissions/user_roles）用于细粒度权限控制
小程序	通过 users 表完成微信登录注册和用户信息管理
ETL	无直接影响

---

5. 验证 SQL

-- 1. 验证 8 张表存在
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
  AND table_name IN ('admin_users', 'users', 'roles', 'permissions',
                     'role_permissions', 'user_roles', 'tasks', 'approvals')
ORDER BY table_name;
-- 预期：8 行

-- 2. 验证 admin_users 字段数量
SELECT COUNT(*)
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'admin_users';
-- 预期：9

-- 3. 验证 admin_users.roles 默认值
SELECT column_default
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'admin_users' AND column_name = 'roles';
-- 预期：'{site_admin}'::text[]

-- 4. 验证外键关系
SELECT tc.table_name, tc.constraint_name, ccu.table_name AS references_table
FROM information_schema.table_constraints tc
JOIN information_schema.constraint_column_usage ccu ON tc.constraint_name = ccu.constraint_name
WHERE tc.table_schema = 'public'
  AND tc.constraint_type = 'FOREIGN KEY'
  AND tc.table_name IN ('approvals', 'tasks', 'role_permissions', 'user_roles')
ORDER BY tc.table_name;
-- 预期：7 行外键

-- 5. 验证 permissions 唯一约束
SELECT constraint_name
FROM information_schema.table_constraints
WHERE table_schema = 'public' AND table_name = 'permissions' AND constraint_type = 'UNIQUE';
-- 预期：permissions_resource_action_key