Neo-ZQYY/docs/database/BD_Manual_auth_tables.md

# BD_Manual：auth Schema 认证业务表

> 目标库：`test_zqyy_app`（通过 `APP_DB_DSN` 连接）
> 迁移脚本：
>   - `db/zqyy_app/migrations/2026-02-25__p3_create_auth_tables.sql`（建表）
>   - `db/zqyy_app/migrations/2026-02-25__p3_seed_roles_permissions.sql`（种子数据）
>   - `db/zqyy_app/migrations/2026-03-23__add_head_coach_manager_roles.sql`（新增 head_coach、manager 角色及权限映射）
>   - `db/zqyy_app/migrations/2026-03-23__add_rejection_count_and_cancelled_status.sql`（申请审核流程增强：rejection_count + cancelled 状态）
> 关联 SPEC：`miniapp-auth-system`（P3 小程序用户认证系统）

---

## 1. 变更说明

### 新增表（9 张）

| # | 表名 | 用途 | 主要字段 |
|---|------|------|---------|
| 1 | `auth.users` | 微信用户主表 | `id`(PK), `wx_openid`(UK), `wx_union_id`, `wx_avatar_url`, `nickname`, `phone`, `avatar_url`, `status`(默认 `new`), `rejection_count`(默认 0，累计被拒次数), `created_at`, `updated_at` |
| 2 | `auth.user_applications` | 用户入驻申请表 | `id`(PK), `user_id`(FK→users), `site_code`, `site_id`, `applied_role_text`, `employee_number`, `phone`, `status`(默认 `pending`，可选值: pending/approved/rejected/cancelled), `reviewer_id`, `review_note`, `created_at`, `reviewed_at` |
| 3 | `auth._archived_site_code_mapping` | [已废弃] 球房ID与门店映射表（NS4.1 重命名，替代方案：`biz.sites` + `biz.site_code_history`） | `id`(PK), `site_code`(UK), `site_id`(UK), `site_name`, `tenant_id`, `created_at` |
| 4 | `auth.roles` | 角色定义表 | `id`(PK), `code`(UK), `name`, `description`, `created_at` |
| 5 | `auth.permissions` | 权限定义表 | `id`(PK), `code`(UK), `name`, `description`, `created_at` |
| 6 | `auth.role_permissions` | 角色-权限关联表 | `role_id`(FK→roles), `permission_id`(FK→permissions)，联合主键 |
| 7 | `auth.user_site_roles` | 用户-门店-角色关联表 | `id`(PK), `user_id`(FK→users), `site_id`, `role_id`(FK→roles), `created_at`，`(user_id, site_id, role_id)` 唯一约束 |
| 8 | `auth.user_assistant_binding` | 用户-人员绑定表 | `id`(PK), `user_id`(FK→users), `site_id`, `assistant_id`(可空), `staff_id`(可空), `binding_type`, `created_at` |
| 9 | `auth.tenant_admins` | 租户管理员表（详见 `BD_Manual_tenant_admin_tables.md`） | `id`(PK), `username`(UK), `password_hash`, `display_name`, `tenant_id`, `managed_site_ids`, `is_active`, `deleted_at`, `created_by`, `created_at`, `last_login_at` |

### 约束与索引

| 表 | 约束/索引名 | 类型 | 说明 |
|----|-----------|------|------|
| `users` | `uq_users_wx_openid` | UNIQUE | 微信 openid 唯一 |
| `users` | `ix_users_wx_openid` | INDEX | openid 查询加速 |
| `site_code_mapping` | `uq_site_code_mapping_site_code` | UNIQUE | 球房ID 唯一（已废弃，表已重命名为 `_archived_site_code_mapping`） |
| `site_code_mapping` | `uq_site_code_mapping_site_id` | UNIQUE | site_id 唯一映射（已废弃） |
| `site_code_mapping` | `ix_site_code_mapping_site_code` | INDEX | site_code 查询加速（已废弃） |
| `roles` | `uq_roles_code` | UNIQUE | 角色 code 唯一 |
| `permissions` | `uq_permissions_code` | UNIQUE | 权限 code 唯一 |
| `role_permissions` | PK `(role_id, permission_id)` | PRIMARY KEY | 联合主键 |
| `role_permissions` | `fk_role_permissions_role_id` | FK | → `auth.roles(id)` CASCADE |
| `role_permissions` | `fk_role_permissions_permission_id` | FK | → `auth.permissions(id)` CASCADE |
| `user_applications` | `user_applications_status_check` | CHECK | status IN ('pending', 'approved', 'rejected', 'cancelled') |
| `user_applications` | `fk_user_applications_user_id` | FK | → `auth.users(id)` CASCADE |
| `user_applications` | `ix_user_applications_user_id` | INDEX | user_id 查询加速 |
| `user_applications` | `ix_user_applications_status` | INDEX | status 过滤加速 |
| `user_site_roles` | `uq_user_site_roles_user_site_role` | UNIQUE | 防止重复分配 |
| `user_site_roles` | `fk_user_site_roles_user_id` | FK | → `auth.users(id)` CASCADE |
| `user_site_roles` | `fk_user_site_roles_role_id` | FK | → `auth.roles(id)` CASCADE |
| `user_site_roles` | `ix_user_site_roles_user_site` | INDEX | (user_id, site_id) 查询加速 |
| `user_assistant_binding` | `fk_user_assistant_binding_user_id` | FK | → `auth.users(id)` CASCADE |


### 种子数据

#### 权限（5 条）

| code | name | description |
|------|------|-------------|
| `view_tasks` | 查看任务 | 允许查看任务列表和任务详情 |
| `view_board` | 查看看板 | 允许查看数据看板概览 |
| `view_board_finance` | 查看财务看板 | 允许查看财务相关的数据看板 |
| `view_board_customer` | 查看客户看板 | 允许查看客户相关的数据看板 |
| `view_board_coach` | 查看助教看板 | 允许查看助教相关的数据看板 |

#### 角色（4 条）

| code | name | description |
|------|------|-------------|
| `coach` | 助教 | 球房助教，可查看任务和助教看板 |
| `staff` | 员工 | 球房员工，可查看任务和数据看板 |
| `head_coach` | 教练 | 主教练，负责训练助教，可查看任务和全部看板 |
| `manager` | 管理员 | 球房管理员，可查看任务和全部看板，未来将与 staff 进一步区分权限 |

> 注：`site_admin` 和 `tenant_admin` 已于 2026-03-23 从小程序 RBAC 体系中清理。租户/店铺管理员的区分通过 `auth.tenant_admins.admin_type` 列实现，不依赖 `auth.roles` 表。

#### 角色-权限映射（11 条）

| 角色 | 权限列表 | 权限数 |
|------|---------|--------|
| `coach` | `view_tasks`, `view_board_coach` | 2 |
| `staff` | `view_tasks`, `view_board` | 2 |
| `head_coach` | `view_tasks`, `view_board` | 2 |
| `manager` | `view_tasks`, `view_board`, `view_board_finance`, `view_board_customer`, `view_board_coach` | 5 |

> 注：`site_admin`（5 条）和 `tenant_admin`（5 条）的权限映射已于 2026-03-23 删除，角色体系隔离后总映射从 24 条减为 11 条（head_coach 仅分配 view_tasks + view_board，非全部 5 个权限）。详见迁移脚本 `2026-03-23__cleanup_roles_add_admin_type.sql`。

---

## 2. 兼容性影响

| 组件 | 影响 |
|------|------|
| ETL 任务 | 无影响。本次变更仅操作业务库 `auth` Schema，不涉及 ETL 库 |
| 后端 API | 直接依赖。FastAPI 后端将基于这些表实现微信登录、用户申请、审核、权限中间件等认证功能 |
| 小程序 | 间接依赖。小程序通过后端 API 间接使用这些表（登录、申请、状态查询） |
| 管理后台 | 间接依赖。管理端通过后端 API 进行申请审核操作 |
| FDW 配置 | 无影响。`fdw_etl` Schema 独立于 `auth`，但人员匹配服务会通过 FDW 查询 ETL 库的助教/员工表 |
| `public` Schema | 无影响。脚本不包含任何对 `public` 的操作 |
| 现有 `auth` Schema | 兼容。`auth` Schema 已由 P1 迁移脚本创建，本次仅在其中新增表，不修改已有对象 |

---

## 3. 回滚策略

按逆序 `DROP TABLE IF EXISTS CASCADE`（迁移脚本末尾已包含注释形式的回滚语句）：

```sql
-- 先删除种子数据（如需保留表结构）
DELETE FROM auth.role_permissions
WHERE role_id IN (SELECT id FROM auth.roles WHERE code IN ('coach', 'staff', 'head_coach', 'manager'))
  AND permission_id IN (SELECT id FROM auth.permissions WHERE code IN ('view_tasks', 'view_board', 'view_board_finance', 'view_board_customer', 'view_board_coach'));

DELETE FROM auth.roles WHERE code IN ('coach', 'staff', 'head_coach', 'manager');
DELETE FROM auth.permissions WHERE code IN ('view_tasks', 'view_board', 'view_board_finance', 'view_board_customer', 'view_board_coach');

-- 删除表（按逆序，CASCADE 处理外键依赖）
DROP TABLE IF EXISTS auth.user_assistant_binding CASCADE;
DROP TABLE IF EXISTS auth.user_site_roles CASCADE;
DROP TABLE IF EXISTS auth.user_applications CASCADE;
DROP TABLE IF EXISTS auth.role_permissions CASCADE;
DROP TABLE IF EXISTS auth.permissions CASCADE;
DROP TABLE IF EXISTS auth.roles CASCADE;
DROP TABLE IF EXISTS auth._archived_site_code_mapping CASCADE;
DROP TABLE IF EXISTS auth.users CASCADE;
```

注意：`CASCADE` 会级联删除依赖对象。如果表中已有业务数据，需先备份再执行回滚。

---

## 4. 验证 SQL

```sql
-- 1. 验证 auth Schema 下 9 张认证表全部存在
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'auth'
  AND table_name IN (
    'users', 'user_applications', '_archived_site_code_mapping',
    'roles', 'permissions', 'role_permissions',
    'user_site_roles', 'user_assistant_binding', 'tenant_admins'
  )
ORDER BY table_name;
-- 预期：返回 9 行

-- 2. 验证种子数据：5 条权限
SELECT COUNT(*) AS perm_count
FROM auth.permissions
WHERE code IN ('view_tasks', 'view_board', 'view_board_finance', 'view_board_customer', 'view_board_coach');
-- 预期：5

-- 3. 验证种子数据：4 条角色（site_admin/tenant_admin 已于 2026-03-23 删除）
SELECT COUNT(*) AS role_count
FROM auth.roles
WHERE code IN ('coach', 'staff', 'head_coach', 'manager');
-- 预期：4

-- 4. 验证角色-权限映射数量（11 条）
SELECT r.code AS role_code, COUNT(rp.permission_id) AS perm_count
FROM auth.roles r
JOIN auth.role_permissions rp ON r.id = rp.role_id
GROUP BY r.code
ORDER BY r.code;
-- 预期：coach=2, head_coach=2, manager=5, staff=2（共 11 条映射，无 site_admin/tenant_admin）

-- 5. 验证关键约束存在
SELECT conname, contype
FROM pg_constraint
WHERE conrelid IN (
    'auth.users'::regclass,
    'auth._archived_site_code_mapping'::regclass,
    'auth.user_site_roles'::regclass
)
ORDER BY conrelid::regclass::text, conname;
-- 预期：包含 uq_users_wx_openid、uq_site_code_mapping_site_code（已废弃表 _archived_site_code_mapping）、uq_site_code_mapping_site_id、uq_user_site_roles_user_site_role 等
```

---

---

## 5. 变更记录：2026-03-23 申请审核流程增强

### 5.1 变更说明

| 对象 | 变更类型 | 说明 |
|------|---------|------|
| `auth.users.rejection_count` | 新增字段 | `integer NOT NULL DEFAULT 0`，累计被管理员拒绝的申请次数，达到 3 次自动将 `status` 设为 `disabled` |
| `auth.user_applications.status` CHECK 约束 | 修改 | 新增 `'cancelled'` 可选值（用户主动取消申请），约束名 `user_applications_status_check` |

### 5.2 业务规则

- 管理员拒绝申请时：`rejection_count += 1`，第 3 次自动将 `users.status` 设为 `disabled`
- 用户主动取消（`cancelled`）不计入 `rejection_count`
- `cancelled` 状态的申请不在管理端申请列表中显示
- `disabled` 用户不允许重新申请，需管理员手动解除（功能待开发）

### 5.3 兼容性影响

| 组件 | 影响 |
|------|------|
| ETL | 无影响，不涉及 ETL 库 |
| 后端 API | 直接依赖。`reject_application` 增加 rejection_count 累加逻辑；新增 `cancel_application` 服务和 `/api/xcx/cancel-application` 端点；`/api/xcx/me` 返回 `latestApplication` 详情 |
| 小程序 | 间接依赖。reviewing 页展示申请详情+重新申请按钮；no-permission 页区分 rejected/disabled 状态 |
| 管理后台 | 间接依赖。拒绝端点自动累加 rejection_count 并触发禁用；申请列表排除 cancelled |

### 5.4 回滚策略

```sql
-- 回滚 rejection_count 字段
ALTER TABLE auth.users DROP COLUMN IF EXISTS rejection_count;

-- 回滚 status CHECK 约束（恢复为不含 cancelled）
ALTER TABLE auth.user_applications DROP CONSTRAINT IF EXISTS user_applications_status_check;
ALTER TABLE auth.user_applications
  ADD CONSTRAINT user_applications_status_check
  CHECK (status IN ('pending', 'approved', 'rejected'));

-- 注意：回滚前需确认无 status='cancelled' 的记录，否则约束添加会失败
-- UPDATE auth.user_applications SET status = 'pending' WHERE status = 'cancelled';
```

### 5.5 验证 SQL

```sql
-- 1. 验证 rejection_count 字段存在且默认值正确
SELECT column_name, data_type, column_default, is_nullable
FROM information_schema.columns
WHERE table_schema = 'auth' AND table_name = 'users' AND column_name = 'rejection_count';
-- 预期：integer, 0, NO

-- 2. 验证 CHECK 约束包含 cancelled
SELECT conname, pg_get_constraintdef(oid) AS constraint_def
FROM pg_constraint
WHERE conrelid = 'auth.user_applications'::regclass
  AND conname = 'user_applications_status_check';
-- 预期：包含 'cancelled'

-- 3. 验证现有数据 rejection_count 默认值
SELECT COUNT(*) AS users_with_zero_rejection
FROM auth.users
WHERE rejection_count = 0;
-- 预期：等于 users 表总行数（所有现有用户默认 0）

-- 4. 验证 cancelled 状态可正常写入（dry-run 验证）
-- INSERT INTO auth.user_applications (user_id, site_code, site_id, applied_role_text, phone, status)
-- VALUES (1, 'TEST', 1, '测试', '13800000000', 'cancelled');
-- 预期：不报 CHECK 约束错误
```

---

<!-- AI_CHANGELOG
| 日期 | Prompt | 变更 |
|------|--------|------|
| 2026-03-23 | 角色路由+页面权限守卫 | 更新角色表从 4 条到 6 条，新增 head_coach/manager 角色及权限映射，更新验证 SQL 和回滚策略 |
| 2026-03-23 | 申请审核流程增强 | users 新增 rejection_count 字段；user_applications.status CHECK 约束增加 cancelled；新增第 5 节变更记录 |
| 2026-03-23 | 角色体系隔离+店铺管理员 | auth.roles 删除 site_admin/tenant_admin（小程序 RBAC 不需要）；角色从 6 条减为 4 条；role_permissions 从 24 条减为 11 条（head_coach 仅 view_tasks+view_board） |
-->