Files
user-system/docs/DATA_MODEL.md

625 lines
20 KiB
Markdown
Raw Permalink Normal View History

# 数据模型设计
## 实现状态说明 (2026-03-29 更新)
本文档描述的是**设计目标**数据库结构,实际实现与设计存在以下差异:
### 与实际实现的差异
| 设计表格 | 实现状态 | 说明 |
|----------|----------|------|
| user_credentials | ⚠️ 合并实现 | 密码凭证存储在 users.passwordTOTP数据在 users 表;社交账号在 user_social_accounts 表 |
| audit_logs | ⚠️ 命名差异 | 实际实现为 operation_logs 表 |
| verification_codes | ❌ 未实现 | 验证码当前在内存/Redis中管理无独立表 |
| token_blacklist | ❌ 未实现 | JWT吊销使用JTI机制无需独立表 |
| user_custom_fields | ❌ 未实现 | 当前版本未支持此功能 |
| system_configs | ⚠️ 替代方案 | 系统配置通过 config.yaml 文件管理,无数据库表 |
### 实际实现的数据库表
当前 GORM AutoMigrate 实际创建的表:
- users
- roles
- permissions
- user_roles
- role_permissions
- devices
- login_logs
- operation_logs
- user_social_accounts
- webhooks
- webhook_deliveries
- password_history
---
## 概述
本文档描述用户管理系统的核心数据库表结构和字段定义。
### 支持的数据库
| 数据库 | 用途 | 特点 |
|--------|------|------|
| **SQLite** | 默认数据库 | 无需独立部署,单文件存储,适合单机场景 |
| **PostgreSQL** | 生产环境可选 | 功能强大,支持高级特性,适合中大型应用 |
| **MySQL** | 生产环境可选 | 广泛使用,社区成熟,适合中大型应用 |
| **MongoDB** | 文档存储可选 | 灵活的文档存储,适合特定场景 |
**注意**SQLite 作为默认数据库所有表结构都兼容其他关系型数据库PostgreSQL/MySQL可通过配置文件平滑切换。
## 表结构设计
### 1. 用户表 (users)
用户基础信息表,存储用户的基本资料。
| 字段名 | 类型 | 长度 | 是否必填 | 默认值 | 说明 |
|--------|------|------|----------|--------|------|
| id | BIGINT | - | 是 | - | 用户 ID主键 |
| username | VARCHAR | 50 | 否 | NULL | 用户名(唯一索引) |
| email | VARCHAR | 100 | 否 | NULL | 邮箱(唯一索引) |
| phone | VARCHAR | 20 | 否 | NULL | 手机号(唯一索引) |
| nickname | VARCHAR | 50 | 否 | NULL | 昵称 |
| avatar | VARCHAR | 255 | 否 | NULL | 头像 URL |
| gender | TINYINT | - | 否 | 0 | 性别0-未知1-男2-女 |
| birthday | DATE | - | 否 | NULL | 生日 |
| region | VARCHAR | 50 | 否 | NULL | 所在地区 |
| bio | VARCHAR | 500 | 否 | NULL | 个性签名 |
| status | TINYINT | - | 是 | 1 | 状态0-待激活1-正常2-锁定3-禁用 |
| last_login_time | DATETIME | - | 否 | NULL | 最后登录时间 |
| last_login_ip | VARCHAR | 50 | 否 | NULL | 最后登录 IP |
| created_at | DATETIME | - | 是 | CURRENT_TIMESTAMP | 创建时间 |
| updated_at | DATETIME | - | 是 | CURRENT_TIMESTAMP ON UPDATE | 更新时间 |
| deleted_at | DATETIME | - | 否 | NULL | 删除时间(软删除) |
**索引设计:**
- PRIMARY KEY (`id`)
- UNIQUE KEY `uk_username` (`username`)
- UNIQUE KEY `uk_email` (`email`)
- UNIQUE KEY `uk_phone` (`phone`)
- KEY `idx_status` (`status`)
- KEY `idx_created_at` (`created_at`)
---
### 2. 用户凭证表 (user_credentials)
用户凭证表,存储密码、社交绑定等信息。
| 字段名 | 类型 | 长度 | 是否必填 | 默认值 | 说明 |
|--------|------|------|----------|--------|------|
| id | BIGINT | - | 是 | - | 凭证 ID主键 |
| user_id | BIGINT | - | 是 | - | 用户 ID外键 |
| credential_type | VARCHAR | 20 | 是 | - | 凭证类型password/wechat/qq/alipay/douyin/github/google |
| identifier | VARCHAR | 100 | 是 | - | 标识符openid、unionid 等) |
| credential_value | VARCHAR | 500 | 否 | NULL | 凭证值(加密后的密码等) |
| salt | VARCHAR | 100 | 否 | NULL | 盐值(用于密码加密) |
| is_primary | TINYINT | - | 是 | 1 | 是否主要凭证0-否1-是 |
| created_at | DATETIME | - | 是 | CURRENT_TIMESTAMP | 创建时间 |
| updated_at | DATETIME | - | 是 | CURRENT_TIMESTAMP ON UPDATE | 更新时间 |
**索引设计:**
- PRIMARY KEY (`id`)
- KEY `idx_user_id` (`user_id`)
- UNIQUE KEY `uk_user_type_identifier` (`user_id`, `credential_type`, `identifier`)
---
### 3. 角色表 (roles)
角色表,定义系统角色。
| 字段名 | 类型 | 长度 | 是否必填 | 默认值 | 说明 |
|--------|------|------|----------|--------|------|
| id | BIGINT | - | 是 | - | 角色 ID主键 |
| name | VARCHAR | 50 | 是 | - | 角色名称(唯一) |
| code | VARCHAR | 50 | 是 | - | 角色代码(唯一) |
| description | VARCHAR | 200 | 否 | NULL | 角色描述 |
| parent_id | BIGINT | - | 否 | NULL | 父角色 ID |
| level | INT | - | 是 | 1 | 角色层级 |
| is_system | TINYINT | - | 是 | 0 | 是否系统角色0-否1-是 |
| is_default | TINYINT | - | 是 | 0 | 是否默认角色0-否1-是 |
| status | TINYINT | - | 是 | 1 | 状态0-禁用1-启用 |
| created_at | DATETIME | - | 是 | CURRENT_TIMESTAMP | 创建时间 |
| updated_at | DATETIME | - | 是 | CURRENT_TIMESTAMP ON UPDATE | 更新时间 |
**索引设计:**
- PRIMARY KEY (`id`)
- UNIQUE KEY `uk_name` (`name`)
- UNIQUE KEY `uk_code` (`code`)
- KEY `idx_parent_id` (`parent_id`)
- KEY `idx_level` (`level`)
- KEY `idx_is_default` (`is_default`)
**初始默认角色:**
- `id=1, code='admin', name='管理员', is_system=1, is_default=0` - 系统管理员角色,拥有所有权限
- `id=2, code='user', name='普通用户', is_system=1, is_default=1` - 普通用户角色,基本权限
---
### 4. 权限表 (permissions)
权限表,定义系统权限。
| 字段名 | 类型 | 长度 | 是否必填 | 默认值 | 说明 |
|--------|------|------|----------|--------|------|
| id | BIGINT | - | 是 | - | 权限 ID主键 |
| name | VARCHAR | 50 | 是 | - | 权限名称 |
| code | VARCHAR | 100 | 是 | - | 权限代码格式resource:action |
| resource | VARCHAR | 50 | 是 | - | 资源名称 |
| action | VARCHAR | 20 | 是 | - | 操作类型read/write/delete/execute |
| description | VARCHAR | 200 | 否 | NULL | 权限描述 |
| type | VARCHAR | 20 | 是 | - | 权限类型api/page/button |
| group_id | BIGINT | - | 否 | NULL | 权限分组 ID |
| status | TINYINT | - | 是 | 1 | 状态0-禁用1-启用 |
| created_at | DATETIME | - | 是 | CURRENT_TIMESTAMP | 创建时间 |
| updated_at | DATETIME | - | 是 | CURRENT_TIMESTAMP ON UPDATE | 更新时间 |
**索引设计:**
- PRIMARY KEY (`id`)
- UNIQUE KEY `uk_code` (`code`)
- KEY `idx_resource` (`resource`)
- KEY `idx_group_id` (`group_id`)
- KEY `idx_type` (`type`)
---
### 5. 用户角色关联表 (user_roles)
用户和角色的多对多关联表。
| 字段名 | 类型 | 长度 | 是否必填 | 默认值 | 说明 |
|--------|------|------|----------|--------|------|
| id | BIGINT | - | 是 | - | 关联 ID主键 |
| user_id | BIGINT | - | 是 | - | 用户 ID |
| role_id | BIGINT | - | 是 | - | 角色 ID |
| assigned_by | BIGINT | - | 否 | NULL | 分配人 ID |
| assigned_at | DATETIME | - | 是 | CURRENT_TIMESTAMP | 分配时间 |
| expire_at | DATETIME | - | 否 | NULL | 过期时间NULL 表示永久) |
**索引设计:**
- PRIMARY KEY (`id`)
- UNIQUE KEY `uk_user_role` (`user_id`, `role_id`)
- KEY `idx_role_id` (`role_id`)
---
### 6. 角色权限关联表 (role_permissions)
角色和权限的多对多关联表。
| 字段名 | 类型 | 长度 | 是否必填 | 默认值 | 说明 |
|--------|------|------|----------|--------|------|
| id | BIGINT | - | 是 | - | 关联 ID主键 |
| role_id | BIGINT | - | 是 | - | 角色 ID |
| permission_id | BIGINT | - | 是 | - | 权限 ID |
| created_at | DATETIME | - | 是 | CURRENT_TIMESTAMP | 创建时间 |
**索引设计:**
- PRIMARY KEY (`id`)
- UNIQUE KEY `uk_role_permission` (`role_id`, `permission_id`)
- KEY `idx_permission_id` (`permission_id`)
---
### 7. 设备管理表 (devices)
用户设备管理表。
| 字段名 | 类型 | 长度 | 是否必填 | 默认值 | 说明 |
|--------|------|------|----------|--------|------|
| id | BIGINT | - | 是 | - | 设备 ID主键 |
| user_id | BIGINT | - | 是 | - | 用户 ID |
| device_id | VARCHAR | 100 | 是 | - | 设备唯一标识 |
| device_name | VARCHAR | 50 | 否 | NULL | 设备名称 |
| device_type | VARCHAR | 20 | 是 | - | 设备类型pc/mobile/tablet |
| os | VARCHAR | 50 | 否 | NULL | 操作系统 |
| browser | VARCHAR | 50 | 否 | NULL | 浏览器 |
| ip | VARCHAR | 50 | 否 | NULL | IP 地址 |
| location | VARCHAR | 100 | 否 | NULL | 地理位置 |
| is_trusted | TINYINT | - | 是 | 0 | 是否信任0-否1-是 |
| last_active_time | DATETIME | - | 否 | NULL | 最后活跃时间 |
| created_at | DATETIME | - | 是 | CURRENT_TIMESTAMP | 创建时间 |
**索引设计:**
- PRIMARY KEY (`id`)
- KEY `idx_user_id` (`user_id`)
- UNIQUE KEY `uk_device_id` (`device_id`)
- KEY `idx_last_active_time` (`last_active_time`)
---
### 8. 登录日志表 (login_logs)
用户登录日志表。
| 字段名 | 类型 | 长度 | 是否必填 | 默认值 | 说明 |
|--------|------|------|----------|--------|------|
| id | BIGINT | - | 是 | - | 日志 ID主键 |
| user_id | BIGINT | - | 否 | NULL | 用户 ID |
| login_type | VARCHAR | 20 | 是 | - | 登录方式password/code/wechat/qq/... |
| login_method | VARCHAR | 20 | 否 | NULL | 认证方式 |
| ip | VARCHAR | 50 | 否 | NULL | IP 地址 |
| location | VARCHAR | 100 | 否 | NULL | 地理位置 |
| device_id | VARCHAR | 100 | 否 | NULL | 设备 ID |
| user_agent | VARCHAR | 500 | 否 | NULL | User-Agent |
| status | TINYINT | - | 是 | - | 状态0-失败1-成功 |
| failure_reason | VARCHAR | 200 | 否 | NULL | 失败原因 |
| created_at | DATETIME | - | 是 | CURRENT_TIMESTAMP | 登录时间 |
**索引设计:**
- PRIMARY KEY (`id`)
- KEY `idx_user_id` (`user_id`)
- KEY `idx_ip` (`ip`)
- KEY `idx_status` (`status`)
- KEY `idx_created_at` (`created_at`)
**分区设计MySQL**
- 按月分区,保留最近 12 个月数据
---
### 9. 审计日志表 (audit_logs)
系统审计日志表。
| 字段名 | 类型 | 长度 | 是否必填 | 默认值 | 说明 |
|--------|------|------|----------|--------|------|
| id | BIGINT | - | 是 | - | 日志 ID主键 |
| user_id | BIGINT | - | 否 | NULL | 操作人 ID |
| action_type | VARCHAR | 50 | 是 | - | 操作类型 |
| resource_type | VARCHAR | 50 | 是 | - | 资源类型 |
| resource_id | BIGINT | - | 否 | NULL | 资源 ID |
| action | VARCHAR | 20 | 是 | - | 操作动作create/update/delete |
| old_value | TEXT | - | 否 | NULL | 操作前值 |
| new_value | TEXT | - | 否 | NULL | 操作后值 |
| ip | VARCHAR | 50 | 否 | NULL | IP 地址 |
| user_agent | VARCHAR | 500 | 否 | NULL | User-Agent |
| created_at | DATETIME | - | 是 | CURRENT_TIMESTAMP | 操作时间 |
**索引设计:**
- PRIMARY KEY (`id`)
- KEY `idx_user_id` (`user_id`)
- KEY `idx_resource_type` (`resource_type`)
- KEY `idx_created_at` (`created_at`)
**分区设计MySQL**
- 按月分区,保留最近 24 个月数据
---
### 10. 验证码表 (verification_codes)
验证码表。
| 字段名 | 类型 | 长度 | 是否必填 | 默认值 | 说明 |
|--------|------|------|----------|--------|------|
| id | BIGINT | - | 是 | - | 验证码 ID主键 |
| code | VARCHAR | 20 | 是 | - | 验证码 |
| type | VARCHAR | 20 | 是 | - | 类型register/login/reset_password/bind_phone/bind_email |
| identifier | VARCHAR | 100 | 是 | - | 标识符(邮箱或手机号) |
| expire_at | DATETIME | - | 是 | - | 过期时间 |
| used | TINYINT | - | 是 | 0 | 是否已使用0-否1-是 |
| ip | VARCHAR | 50 | 否 | NULL | IP 地址 |
| created_at | DATETIME | - | 是 | CURRENT_TIMESTAMP | 创建时间 |
**索引设计:**
- PRIMARY KEY (`id`)
- KEY `idx_identifier_type` (`identifier`, `type`)
- KEY `idx_expire_at` (`expire_at`)
---
### 11. Token 黑名单表 (token_blacklist)
Token 黑名单表。
| 字段名 | 类型 | 长度 | 是否必填 | 默认值 | 说明 |
|--------|------|------|----------|--------|------|
| id | BIGINT | - | 是 | - | 记录 ID主键 |
| user_id | BIGINT | - | 是 | - | 用户 ID |
| token | VARCHAR | 500 | 是 | - | Token |
| token_type | VARCHAR | 20 | 是 | - | Token 类型access/refresh |
| expire_at | DATETIME | - | 是 | - | 过期时间 |
| revoked_by | BIGINT | - | 否 | NULL | 吊销人 ID |
| revoked_at | DATETIME | - | 是 | CURRENT_TIMESTAMP | 吊销时间 |
**索引设计:**
- PRIMARY KEY (`id`)
- KEY `idx_user_id` (`user_id`)
- KEY `idx_expire_at` (`expire_at`)
---
### 12. 用户自定义字段表 (user_custom_fields)
用户自定义字段表。
| 字段名 | 类型 | 长度 | 是否必填 | 默认值 | 说明 |
|--------|------|------|----------|--------|------|
| id | BIGINT | - | 是 | - | 字段 ID主键 |
| user_id | BIGINT | - | 是 | - | 用户 ID |
| field_key | VARCHAR | 50 | 是 | - | 字段键名 |
| field_value | TEXT | - | 否 | NULL | 字段值JSON 格式) |
| field_type | VARCHAR | 20 | 是 | - | 字段类型string/number/boolean/date |
| created_at | DATETIME | - | 是 | CURRENT_TIMESTAMP | 创建时间 |
| updated_at | DATETIME | - | 是 | CURRENT_TIMESTAMP ON UPDATE | 更新时间 |
**索引设计:**
- PRIMARY KEY (`id`)
- KEY `idx_user_id` (`user_id`)
- UNIQUE KEY `uk_user_key` (`user_id`, `field_key`)
---
### 13. Webhook 配置表 (webhook_configs)
Webhook 配置表。
| 字段名 | 类型 | 长度 | 是否必填 | 默认值 | 说明 |
|--------|------|------|----------|--------|------|
| id | BIGINT | - | 是 | - | 配置 ID主键 |
| name | VARCHAR | 50 | 是 | - | Webhook 名称 |
| event_types | TEXT | - | 是 | - | 事件类型JSON 数组) |
| url | VARCHAR | 255 | 是 | - | 回调 URL |
| secret | VARCHAR | 100 | 否 | NULL | 签名密钥 |
| headers | TEXT | - | 否 | NULL | 自定义请求头JSON |
| is_active | TINYINT | - | 是 | 1 | 是否启用0-否1-是 |
| created_at | DATETIME | - | 是 | CURRENT_TIMESTAMP | 创建时间 |
| updated_at | DATETIME | - | 是 | CURRENT_TIMESTAMP ON UPDATE | 更新时间 |
**索引设计:**
- PRIMARY KEY (`id`)
- KEY `idx_is_active` (`is_active`)
---
### 14. Webhook 日志表 (webhook_logs)
Webhook 日志表。
| 字段名 | 类型 | 长度 | 是否必填 | 默认值 | 说明 |
|--------|------|------|----------|--------|------|
| id | BIGINT | - | 是 | - | 日志 ID主键 |
| webhook_id | BIGINT | - | 是 | - | Webhook 配置 ID |
| event_type | VARCHAR | 50 | 是 | - | 事件类型 |
| event_data | TEXT | - | 是 | - | 事件数据JSON |
| request_url | VARCHAR | 255 | 是 | - | 请求 URL |
| request_headers | TEXT | - | 否 | NULL | 请求头JSON |
| request_body | TEXT | - | 否 | NULL | 请求体JSON |
| response_status | INT | - | 否 | NULL | 响应状态码 |
| response_body | TEXT | - | 否 | NULL | 响应体 |
| retry_count | INT | - | 是 | 0 | 重试次数 |
| status | VARCHAR | 20 | 是 | - | 状态pending/success/failed |
| error_message | TEXT | - | 否 | NULL | 错误信息 |
| created_at | DATETIME | - | 是 | CURRENT_TIMESTAMP | 创建时间 |
**索引设计:**
- PRIMARY KEY (`id`)
- KEY `idx_webhook_id` (`webhook_id`)
- KEY `idx_event_type` (`event_type`)
- KEY `idx_status` (`status`)
- KEY `idx_created_at` (`created_at`)
**分区设计MySQL**
- 按月分区,保留最近 12 个月数据
---
### 15. 系统配置表 (system_configs)
系统配置表。
| 字段名 | 类型 | 长度 | 是否必填 | 默认值 | 说明 |
|--------|------|------|----------|--------|------|
| id | BIGINT | - | 是 | - | 配置 ID主键 |
| config_key | VARCHAR | 100 | 是 | - | 配置键(唯一) |
| config_value | TEXT | - | 否 | NULL | 配置值 |
| config_type | VARCHAR | 20 | 是 | - | 配置类型string/number/boolean/json |
| description | VARCHAR | 200 | 否 | NULL | 配置描述 |
| is_system | TINYINT | - | 是 | 0 | 是否系统配置0-否1-是 |
| created_at | DATETIME | - | 是 | CURRENT_TIMESTAMP | 创建时间 |
| updated_at | DATETIME | - | 是 | CURRENT_TIMESTAMP ON UPDATE | 更新时间 |
**索引设计:**
- PRIMARY KEY (`id`)
- UNIQUE KEY `uk_config_key` (`config_key`)
---
## ER 图
```mermaid
erDiagram
users ||--o{ user_credentials : "has"
users ||--o{ user_roles : "has"
users ||--o{ devices : "has"
users ||--o{ login_logs : "has"
users ||--o{ audit_logs : "has"
users ||--o{ user_custom_fields : "has"
users ||--o{ token_blacklist : "has"
roles ||--o{ user_roles : "assigned to"
roles ||--o{ role_permissions : "has"
roles ||--o{ roles : "inherits from"
permissions ||--o{ role_permissions : "assigned to"
webhook_configs ||--o{ webhook_logs : "has"
```
---
## MongoDB 结构设计
如果使用 MongoDB建议采用以下集合结构
### users 集合
```json
{
"_id": ObjectId("..."),
"username": "john_doe",
"email": "john@example.com",
"phone": "+86138xxxxxxxx",
"nickname": "John",
"avatar": "https://...",
"profile": {
"gender": 1,
"birthday": "1990-01-01",
"region": "北京",
"bio": "...",
"customFields": {
"company": "ABC Inc.",
"position": "Developer"
}
},
"credentials": [
{
"type": "password",
"hash": "...",
"salt": "...",
"isPrimary": true
},
{
"type": "wechat",
"openid": "...",
"unionid": "..."
}
],
"roles": [1, 2],
"devices": [
{
"deviceId": "...",
"deviceName": "iPhone 15",
"deviceType": "mobile",
"isTrusted": true,
"lastActiveTime": ISODate("2026-03-10T10:00:00Z")
}
],
"status": 1,
"lastLogin": {
"time": ISODate("2026-03-10T10:00:00Z"),
"ip": "192.168.1.1"
},
"createdAt": ISODate("2026-01-01T00:00:00Z"),
"updatedAt": ISODate("2026-03-10T10:00:00Z"),
"deletedAt": null
}
```
### roles 集合
```json
{
"_id": ObjectId("..."),
"name": "普通用户",
"code": "user",
"description": "普通用户角色",
"parentId": null,
"level": 1,
"isSystem": false,
"status": 1,
"permissions": [1, 2, 3],
"createdAt": ISODate("2026-01-01T00:00:00Z"),
"updatedAt": ISODate("2026-01-01T00:00:00Z")
}
```
### permissions 集合
```json
{
"_id": ObjectId("..."),
"name": "查看用户",
"code": "user:read",
"resource": "user",
"action": "read",
"description": "查看用户信息",
"type": "api",
"groupId": 1,
"status": 1,
"createdAt": ISODate("2026-01-01T00:00:00Z"),
"updatedAt": ISODate("2026-01-01T00:00:00Z")
}
```
### login_logs 集合
```json
{
"_id": ObjectId("..."),
"userId": ObjectId("..."),
"loginType": "password",
"ip": "192.168.1.1",
"location": "北京市",
"device": {
"deviceId": "...",
"deviceName": "iPhone 15",
"deviceType": "mobile",
"os": "iOS 17",
"browser": "Safari"
},
"status": 1,
"failureReason": null,
"createdAt": ISODate("2026-03-10T10:00:00Z")
}
```
---
## 索引策略
### 索引设计原则
1. **主键索引**:所有表必须有主键
2. **唯一索引**:用户名、邮箱、手机号等唯一字段
3. **复合索引**:经常一起查询的字段组合
4. **覆盖索引**:避免回表查询
5. **分区索引**:大表按时间分区
### 常用查询优化
```sql
-- 用户登录查询
SELECT * FROM users
WHERE (username = ? OR email = ? OR phone = ?)
AND status = 1;
-- 角色权限查询
SELECT p.* FROM permissions p
INNER JOIN role_permissions rp ON p.id = rp.permission_id
WHERE rp.role_id IN (SELECT role_id FROM user_roles WHERE user_id = ?);
-- 登录日志统计
SELECT DATE(created_at) as date, COUNT(*) as count
FROM login_logs
WHERE user_id = ?
GROUP BY DATE(created_at)
ORDER BY date DESC;
```
---
## 数据迁移
### 初始化脚本
提供数据库初始化 SQL 脚本,包括:
1. 建表语句
2. 初始数据(默认角色、权限)
3. 索引创建
4. 分区设置
### 版本管理
使用数据库迁移工具(如 Flyway、Liquibase管理数据库版本
- 每次数据库结构变更需要迁移脚本
- 支持版本回滚
- 记录迁移历史
---
*本文档持续更新中,如有疑问请联系技术团队。*