# AI Service 数据库迁移幂等性修复

**日期**: 2026-01-30  
**类型**: 数据库迁移修复  
**影响范围**: AI Service 模块

## 问题描述

AI Service 数据库迁移脚本 `20260129_1800_create_ai_service_tables.py` 存在幂等性问题：

1. **GIN 索引错误**: JSONB 列的 GIN 索引创建失败，错误提示 `operator class "jsonb_path_ops" does not accept data type json`
2. **列类型不匹配**: 数据库中 `ai_usage_logs.extra_data` 列类型为 `json`，但 Model 定义为 `extra_metadata: JSONB`
3. **非幂等性**: 使用 `CREATE TABLE IF NOT EXISTS` 但后续的索引和注释语句未做条件检查

## 解决方案

### 1. 修复迁移脚本幂等性

**修改文件**: `server/alembic/versions/20260129_1800_create_ai_service_tables.py`

#### 1.1 添加列类型检查和转换

为所有 JSONB 列添加类型检查，如果列类型为 `json` 则转换为 `jsonb`：

```python
# ai_models 表
op.execute("""
    DO $$
    BEGIN
        IF EXISTS (
            SELECT 1 FROM information_schema.columns 
            WHERE table_name = 'ai_models' 
            AND column_name = 'config' 
            AND data_type = 'json'
        ) THEN
            ALTER TABLE ai_models ALTER COLUMN config TYPE JSONB USING config::jsonb;
        END IF;
    END $$;
""")
```

#### 1.2 条件化 GIN 索引创建

将 GIN 索引创建包装在条件检查中：

```python
op.execute("""
    DO $$
    BEGIN
        IF EXISTS (
            SELECT 1 FROM information_schema.columns 
            WHERE table_name = 'ai_usage_logs' 
            AND column_name = 'extra_metadata'
        ) THEN
            CREATE INDEX IF NOT EXISTS idx_ai_usage_logs_extra_metadata_gin 
            ON ai_usage_logs USING gin (extra_metadata jsonb_path_ops);
        END IF;
    END $$;
""")
```

#### 1.3 条件化列注释

对可能不存在的列，使用动态 SQL 添加注释：

```python
op.execute("""
    DO $$
    BEGIN
        IF EXISTS (
            SELECT 1 FROM information_schema.columns 
            WHERE table_name = 'ai_usage_logs' 
            AND column_name = 'extra_metadata'
        ) THEN
            EXECUTE 'COMMENT ON COLUMN ai_usage_logs.extra_metadata IS ''使用元数据''';
        END IF;
    END $$;
""")
```

### 2. 修复数据库列名和类型不匹配

由于 SQLModel 自动创建的表使用了旧的列名 `extra_data` (类型 `json`)，需要手动修复：

```sql
-- 重命名列
ALTER TABLE ai_usage_logs 
  RENAME COLUMN extra_data TO extra_metadata;

-- 转换类型
ALTER TABLE ai_usage_logs 
  ALTER COLUMN extra_metadata TYPE JSONB USING extra_metadata::jsonb;

-- 设置默认值
ALTER TABLE ai_usage_logs 
  ALTER COLUMN extra_metadata SET DEFAULT '{}'::jsonb;

-- 创建 GIN 索引
CREATE INDEX IF NOT EXISTS idx_ai_usage_logs_extra_metadata_gin 
  ON ai_usage_logs USING gin (extra_metadata jsonb_path_ops);
```

## 验证结果

### 表创建成功

```sql
SELECT table_name FROM information_schema.tables 
WHERE table_schema = 'public' AND table_name LIKE 'ai_%';
```

结果：
- ✅ `ai_models` (16 列)
- ✅ `ai_jobs` (21 列)
- ✅ `ai_usage_logs` (10 列)
- ✅ `ai_quotas` (9 列)

### JSONB 列和索引验证

```sql
\d ai_models
\d ai_jobs
\d ai_usage_logs
```

确认：
- ✅ `ai_models.config` - JSONB 类型，带 GIN 索引
- ✅ `ai_jobs.input_data` - JSONB 类型，带 GIN 索引
- ✅ `ai_jobs.output_data` - JSONB 类型，带 GIN 索引
- ✅ `ai_usage_logs.extra_metadata` - JSONB 类型，带 GIN 索引

## 技术要点

### PostgreSQL GIN 索引操作符类

PostgreSQL 的 GIN 索引需要指定正确的操作符类：

- **错误**: `USING gin (config)` - 对 JSONB 列会失败
- **正确**: `USING gin (config jsonb_path_ops)` - 使用 `jsonb_path_ops` 操作符类

`jsonb_path_ops` 操作符类：
- 仅支持 `@>` 操作符（包含查询）
- 索引更小，性能更好
- 适合大多数 JSONB 查询场景

### 迁移脚本幂等性最佳实践

1. **表创建**: 使用 `CREATE TABLE IF NOT EXISTS`
2. **列修改**: 先检查列是否存在及其类型
3. **索引创建**: 使用 `CREATE INDEX IF NOT EXISTS` 并检查列存在性
4. **约束添加**: 检查约束是否已存在
5. **触发器创建**: 使用 `IF NOT EXISTS` 条件

### SQLModel 自动表创建问题

SQLModel 会在应用启动时自动创建表，可能导致：
- 表结构与迁移脚本不一致
- 列名或类型不匹配
- 缺少索引或约束

**解决方案**：
- 禁用 SQLModel 自动表创建
- 完全依赖 Alembic 迁移管理表结构
- 或确保 Model 定义与迁移脚本完全一致

## 影响范围

- ✅ AI Service 所有表创建成功
- ✅ JSONB 列类型正确
- ✅ GIN 索引创建成功
- ✅ 迁移脚本完全幂等
- ✅ 可重复执行不会报错

## 相关文件

- `server/alembic/versions/20260129_1800_create_ai_service_tables.py` - 迁移脚本
- `server/app/models/ai_usage_log.py` - AIUsageLog Model 定义
- `server/app/models/ai_model.py` - AIModel Model 定义
- `server/app/models/ai_job.py` - AIJob Model 定义
- `server/app/models/ai_quota.py` - AIQuota Model 定义

## 后续建议

1. **禁用 SQLModel 自动表创建**: 在生产环境中完全依赖 Alembic 管理表结构
2. **迁移测试**: 在 CI/CD 中添加迁移幂等性测试
3. **文档更新**: 更新数据库设计文档，明确 JSONB 列的索引策略