# 用户实名认证功能数据库迁移指南

## 概述

本文档记录了用户实名认证功能相关的所有数据库迁移操作，包括字段添加、配置项添加和字段扩展。

## 迁移文件列表

| 文件名 | 版本 | 描述 | 状态 |
|--------|------|------|------|
| `051_add_user_verification_fields.sql` | v1.0 | 添加实名认证字段 | ✅ 必须执行 |
| `052_add_verification_reminder_config.sql` | v1.1 | 添加认证提醒配置 | ✅ 必须执行 |
| `053_extend_id_card_length.sql` | v1.2 | 扩展身份证字段长度 | ✅ 必须执行 |

---

## 迁移 051：添加实名认证字段

### 文件
`051_add_user_verification_fields.sql`

### 目的
在 `users` 表中添加实名认证相关字段

### 执行前检查
```sql
-- 检查字段是否已存在
SELECT column_name 
FROM information_schema.columns 
WHERE table_schema = 'aigcspace' 
  AND table_name = 'users' 
  AND column_name IN ('real_name', 'id_card', 'is_verified', 'verified_at');
```

### 执行命令

#### 方法1：使用psql命令行
```bash
psql -U your_username -d your_database -f backend/migrations/051_add_user_verification_fields.sql
```

#### 方法2：直接执行SQL
```sql
-- 添加真实姓名字段
ALTER TABLE aigcspace.users 
ADD COLUMN IF NOT EXISTS real_name VARCHAR(50);

-- 添加身份证号字段
ALTER TABLE aigcspace.users 
ADD COLUMN IF NOT EXISTS id_card VARCHAR(18);

-- 添加认证状态字段
ALTER TABLE aigcspace.users 
ADD COLUMN IF NOT EXISTS is_verified VARCHAR(20) DEFAULT 'unverified';

-- 添加认证时间字段
ALTER TABLE aigcspace.users 
ADD COLUMN IF NOT EXISTS verified_at TIMESTAMP;

-- 添加唯一约束（已认证用户的身份证号不能重复）
CREATE UNIQUE INDEX IF NOT EXISTS idx_users_id_card_verified 
ON aigcspace.users (id_card) 
WHERE is_verified = 'verified' AND id_card IS NOT NULL;

-- 添加注释
COMMENT ON COLUMN aigcspace.users.real_name IS '真实姓名';
COMMENT ON COLUMN aigcspace.users.id_card IS '身份证号';
COMMENT ON COLUMN aigcspace.users.is_verified IS '认证状态：unverified/verified';
COMMENT ON COLUMN aigcspace.users.verified_at IS '认证通过时间';
```

### 验证
```sql
-- 查看新增字段
SELECT column_name, data_type, character_maximum_length, column_default
FROM information_schema.columns 
WHERE table_schema = 'aigcspace' 
  AND table_name = 'users' 
  AND column_name IN ('real_name', 'id_card', 'is_verified', 'verified_at');

-- 查看唯一索引
SELECT indexname, indexdef 
FROM pg_indexes 
WHERE schemaname = 'aigcspace' 
  AND tablename = 'users' 
  AND indexname = 'idx_users_id_card_verified';
```

### 回滚（如需要）
```sql
-- 删除唯一索引
DROP INDEX IF EXISTS aigcspace.idx_users_id_card_verified;

-- 删除字段
ALTER TABLE aigcspace.users DROP COLUMN IF EXISTS verified_at;
ALTER TABLE aigcspace.users DROP COLUMN IF EXISTS is_verified;
ALTER TABLE aigcspace.users DROP COLUMN IF EXISTS id_card;
ALTER TABLE aigcspace.users DROP COLUMN IF EXISTS real_name;
```

---

## 迁移 052：添加认证提醒配置

### 文件
`052_add_verification_reminder_config.sql`

### 目的
在系统配置表中添加实名认证提醒开关

### 执行前检查
```sql
-- 检查配置是否已存在
SELECT * FROM aigcspace.system_config 
WHERE config_key = 'enable_verification_reminder';
```

### 执行命令

#### 方法1：使用psql命令行
```bash
psql -U your_username -d your_database -f backend/migrations/052_add_verification_reminder_config.sql
```

#### 方法2：直接执行SQL
```sql
-- 添加实名认证提醒开关配置
INSERT INTO aigcspace.system_config (config_key, config_value, config_type, category, description) 
VALUES ('enable_verification_reminder', 'true', 'boolean', 'feature', '是否开启实名认证提醒弹窗')
ON CONFLICT (config_key) DO NOTHING;
```

### 验证
```sql
-- 查看新增配置
SELECT * FROM aigcspace.system_config 
WHERE config_key = 'enable_verification_reminder';

-- 查看所有功能开关配置
SELECT * FROM aigcspace.system_config 
WHERE category = 'feature';
```

### 回滚（如需要）
```sql
DELETE FROM aigcspace.system_config 
WHERE config_key = 'enable_verification_reminder';
```

---

## 迁移 053：扩展身份证字段长度

### 文件
`053_extend_id_card_length.sql`

### 目的
将 `id_card` 字段从 `VARCHAR(18)` 扩展到 `VARCHAR(255)`，以支持AES加密后的数据存储

### 背景
- 原始身份证号：18位
- AES加密后：约64-128个字符（Base64编码）
- 为了安全存储，扩展到255个字符

### 执行前检查
```sql
-- 检查当前字段长度
SELECT column_name, data_type, character_maximum_length 
FROM information_schema.columns 
WHERE table_schema = 'aigcspace' 
  AND table_name = 'users' 
  AND column_name = 'id_card';

-- 检查是否有数据
SELECT COUNT(*) as total_users,
       COUNT(id_card) as users_with_id_card,
       COUNT(CASE WHEN is_verified = 'verified' THEN 1 END) as verified_users
FROM aigcspace.users;
```

### 执行命令

#### 方法1：使用psql命令行
```bash
psql -U your_username -d your_database -f backend/migrations/053_extend_id_card_length.sql
```

#### 方法2：直接执行SQL
```sql
-- 修改 id_card 字段长度
ALTER TABLE aigcspace.users 
ALTER COLUMN id_card TYPE VARCHAR(255);

-- 更新注释
COMMENT ON COLUMN aigcspace.users.id_card IS '身份证号（AES加密存储）';
```

### 验证
```sql
-- 验证字段长度已更改
SELECT column_name, data_type, character_maximum_length 
FROM information_schema.columns 
WHERE table_schema = 'aigcspace' 
  AND table_name = 'users' 
  AND column_name = 'id_card';

-- 预期结果：character_maximum_length = 255
```

### 回滚（如需要）
```sql
-- 警告：回滚前确保没有加密数据，否则会丢失数据
ALTER TABLE aigcspace.users 
ALTER COLUMN id_card TYPE VARCHAR(18);
```

---

## 完整迁移流程

### 新环境部署（按顺序执行）

```bash
# 1. 进入迁移目录
cd backend/migrations

# 2. 执行迁移 051
psql -U your_username -d your_database -f 051_add_user_verification_fields.sql

# 3. 执行迁移 052
psql -U your_username -d your_database -f 052_add_verification_reminder_config.sql

# 4. 执行迁移 053
psql -U your_username -d your_database -f 053_extend_id_card_length.sql

# 5. 验证所有迁移
psql -U your_username -d your_database -c "
SELECT column_name, data_type, character_maximum_length 
FROM information_schema.columns 
WHERE table_schema = 'aigcspace' 
  AND table_name = 'users' 
  AND column_name IN ('real_name', 'id_card', 'is_verified', 'verified_at');
"
```

### 已有环境升级

如果你的环境已经执行过部分迁移，可以单独执行缺失的迁移：

```bash
# 检查哪些迁移已执行
psql -U your_username -d your_database -c "
SELECT column_name, character_maximum_length 
FROM information_schema.columns 
WHERE table_schema = 'aigcspace' 
  AND table_name = 'users' 
  AND column_name = 'id_card';
"

# 如果 character_maximum_length = 18，需要执行迁移 053
psql -U your_username -d your_database -f 053_extend_id_card_length.sql
```

---

## 数据迁移注意事项

### 1. 备份数据
在执行任何迁移前，务必备份数据库：

```bash
# 备份整个数据库
pg_dump -U your_username -d your_database -F c -f backup_before_migration_$(date +%Y%m%d_%H%M%S).dump

# 仅备份 users 表
pg_dump -U your_username -d your_database -t aigcspace.users -F c -f users_backup_$(date +%Y%m%d_%H%M%S).dump
```

### 2. 测试环境验证
先在测试环境执行迁移，验证无误后再在生产环境执行。

### 3. 停机时间
- 迁移 051、052：无需停机，可在线执行
- 迁移 053：建议在低峰期执行，预计停机时间 < 1分钟

### 4. 数据一致性
执行迁移 053 后，旧的明文身份证号将无法使用，需要用户重新提交实名认证。

### 5. 回滚计划
如果迁移失败，按照各迁移的回滚步骤执行。

---

## 迁移后配置

### 1. 生成AES密钥
```bash
cd backend
python generate_aes_key.py
# 将输出的密钥添加到 .env 文件
```

### 2. 配置环境变量
在 `backend/.env` 中添加：
```bash
AES_ENCRYPTION_KEY=your_generated_key_here
```

### 3. 重启服务
```bash
# 重启后端服务
cd backend
python main.py

# 后端会自动生成 RSA 密钥对
```

---

## 故障排查

### 问题1：字段已存在
**错误信息**：`column "real_name" of relation "users" already exists`

**解决方案**：
```sql
-- 检查字段是否已存在
SELECT column_name FROM information_schema.columns 
WHERE table_schema = 'aigcspace' AND table_name = 'users' 
AND column_name = 'real_name';

-- 如果已存在，跳过该迁移或使用 IF NOT EXISTS
```

### 问题2：数据过长
**错误信息**：`value too long for type character varying(18)`

**原因**：未执行迁移 053

**解决方案**：
```sql
ALTER TABLE aigcspace.users ALTER COLUMN id_card TYPE VARCHAR(255);
```

### 问题3：唯一约束冲突
**错误信息**：`duplicate key value violates unique constraint`

**原因**：已有重复的身份证号

**解决方案**：
```sql
-- 查找重复数据
SELECT id_card, COUNT(*) 
FROM aigcspace.users 
WHERE is_verified = 'verified' AND id_card IS NOT NULL
GROUP BY id_card 
HAVING COUNT(*) > 1;

-- 手动处理重复数据
```

---

## 版本记录

| 版本 | 日期 | 描述 | 作者 |
|------|------|------|------|
| v1.0 | 2026-03-30 | 初始版本，添加实名认证字段 | System |
| v1.1 | 2026-03-30 | 添加认证提醒配置 | System |
| v1.2 | 2026-03-31 | 扩展身份证字段长度以支持加密 | System |

---

## 相关文档

- [安装指南](../../INSTALLATION_GUIDE.md)
- [快速设置](../../SETUP_ENCRYPTION.md)
- [解密工具使用指南](./DECRYPT_TOOL_GUIDE.md)

---

## 联系支持

如有问题，请查看：
1. 迁移文件中的注释
2. 本文档的故障排查部分
3. 相关日志文件