Maas-Model-platform/backend/migrations/README_user_verification.md

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

概述

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

迁移文件列表

文件名	版本	描述	状态
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 表中添加实名认证相关字段

执行前检查

-- 检查字段是否已存在
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命令行

psql -U your_username -d your_database -f backend/migrations/051_add_user_verification_fields.sql

方法2：直接执行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 '认证通过时间';

验证

-- 查看新增字段
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';

回滚（如需要）

-- 删除唯一索引
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

目的

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

执行前检查

-- 检查配置是否已存在
SELECT * FROM aigcspace.system_config 
WHERE config_key = 'enable_verification_reminder';

执行命令

方法1：使用psql命令行

psql -U your_username -d your_database -f backend/migrations/052_add_verification_reminder_config.sql

方法2：直接执行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;

验证

-- 查看新增配置
SELECT * FROM aigcspace.system_config 
WHERE config_key = 'enable_verification_reminder';

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

回滚（如需要）

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个字符

执行前检查

-- 检查当前字段长度
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命令行

psql -U your_username -d your_database -f backend/migrations/053_extend_id_card_length.sql

方法2：直接执行SQL

-- 修改 id_card 字段长度
ALTER TABLE aigcspace.users 
ALTER COLUMN id_card TYPE VARCHAR(255);

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

验证

-- 验证字段长度已更改
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

回滚（如需要）

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

---

完整迁移流程

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

# 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');
"

已有环境升级

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

# 检查哪些迁移已执行
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. 备份数据

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

# 备份整个数据库
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密钥

cd backend
python generate_aes_key.py
# 将输出的密钥添加到 .env 文件

2. 配置环境变量

在 backend/.env 中添加：

AES_ENCRYPTION_KEY=your_generated_key_here

3. 重启服务

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

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

---

故障排查

问题1：字段已存在

错误信息：column "real_name" of relation "users" already exists

解决方案：

-- 检查字段是否已存在
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

解决方案：

ALTER TABLE aigcspace.users ALTER COLUMN id_card TYPE VARCHAR(255);

问题3：唯一约束冲突

错误信息：duplicate key value violates unique constraint

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

解决方案：

-- 查找重复数据
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

---

相关文档

• 安装指南
• 快速设置
• 解密工具使用指南

---

联系支持

如有问题，请查看：

1. 迁移文件中的注释
2. 本文档的故障排查部分
3. 相关日志文件