# 表结构设计

<cite>
**本文档引用的文件**
- [user.py](file://backend/app/models/user.py)
- [query.py](file://backend/app/models/query.py)
- [citation_record.py](file://backend/app/models/citation_record.py)
- [query_task.py](file://backend/app/models/query_task.py)
- [subscription.py](file://backend/app/models/subscription.py)
- [initial_migration.py](file://backend/alembic/versions/488d0bd5ab01_initial_migration.py)
- [database.py](file://backend/app/database.py)
- [config.py](file://backend/app/config.py)
</cite>

## 目录
1. [简介](#简介)
2. [项目结构](#项目结构)
3. [核心组件](#核心组件)
4. [架构概览](#架构概览)
5. [详细组件分析](#详细组件分析)
6. [依赖分析](#依赖分析)
7. [性能考虑](#性能考虑)
8. [故障排除指南](#故障排除指南)
9. [结论](#结论)

## 简介

本文件详细描述GEO项目的数据库表结构设计，涵盖用户查询监控平台的核心数据模型。该系统采用PostgreSQL作为主要数据库，使用SQLAlchemy ORM进行对象关系映射，支持异步数据库操作。系统包含五个核心表：users（用户表）、queries（查询表）、citation_records（引用记录表）、query_tasks（查询任务表）和subscriptions（订阅表），这些表通过外键关系相互关联，形成完整的业务数据模型。

## 项目结构

GEO项目的数据库层采用分层架构设计，主要包含以下组件：

```mermaid
graph TB
subgraph "数据库层"
DB[(PostgreSQL数据库)]
Engine[异步引擎]
Session[会话管理]
end
subgraph "ORM模型层"
User[User模型]
Query[Query模型]
Citation[CitationRecord模型]
Task[QueryTask模型]
Subscription[Subscription模型]
end
subgraph "配置层"
Config[配置管理]
Alembic[迁移管理]
end
Config --> Engine
Engine --> DB
Session --> DB
User --> DB
Query --> DB
Citation --> DB
Task --> DB
Subscription --> DB
Alembic --> DB
```

**图表来源**
- [database.py:1-29](file://backend/app/database.py#L1-L29)
- [config.py:4-17](file://backend/app/config.py#L4-L17)

**章节来源**
- [database.py:1-29](file://backend/app/database.py#L1-L29)
- [config.py:4-17](file://backend/app/config.py#L4-L17)

## 核心组件

### 数据库连接配置

系统使用异步PostgreSQL连接，配置参数如下：
- 数据库URL: `postgresql+asyncpg://postgres:postgres123@db:5432/geo_platform`
- Redis连接: `redis://redis:6379/0`
- JWT密钥: `your-secret-key-change-in-production`
- Playwright浏览器路径: `/ms-playwright`

### 异步数据库引擎

使用SQLAlchemy异步引擎创建数据库连接池，支持：
- 异步会话管理
- 连接池配置
- 自动事务处理
- 连接生命周期管理

**章节来源**
- [config.py:7](file://backend/app/config.py#L7)
- [database.py:6-18](file://backend/app/database.py#L6-L18)

## 架构概览

GEO系统的数据库架构采用标准的三层设计模式：

```mermaid
erDiagram
USERS {
uuid id PK
string email UK
string password_hash
string name
string plan
integer max_queries
boolean is_active
timestamp created_at
timestamp updated_at
}
QUERIES {
uuid id PK
uuid user_id FK
string keyword
string target_brand
jsonb brand_aliases
jsonb platforms
string frequency
string status
timestamp last_queried_at
timestamp next_query_at
timestamp created_at
timestamp updated_at
}
CITATION_RECORDS {
uuid id PK
uuid query_id FK
string platform
boolean cited
integer citation_position
text citation_text
jsonb competitor_brands
text raw_response
timestamp queried_at
}
QUERY_TASKS {
uuid id PK
uuid query_id FK
string platform
string status
text error_message
timestamp scheduled_at
timestamp started_at
timestamp completed_at
}
SUBSCRIPTIONS {
uuid id PK
uuid user_id FK
string plan
string status
date start_date
date end_date
numeric amount
string payment_method
string payment_id
timestamp created_at
}
USERS ||--o{ QUERIES : "拥有"
QUERIES ||--o{ CITATION_RECORDS : "包含"
QUERIES ||--o{ QUERY_TASKS : "包含"
USERS ||--o{ SUBSCRIPTIONS : "拥有"
```

**图表来源**
- [initial_migration.py:24-37](file://backend/alembic/versions/488d0bd5ab01_initial_migration.py#L24-L37)
- [initial_migration.py:40-56](file://backend/alembic/versions/488d0bd5ab01_initial_migration.py#L40-L56)
- [initial_migration.py:62-78](file://backend/alembic/versions/488d0bd5ab01_initial_migration.py#L62-L78)
- [initial_migration.py:81-94](file://backend/alembic/versions/488d0bd5ab01_initial_migration.py#L81-L94)
- [initial_migration.py:97-111](file://backend/alembic/versions/488d0bd5ab01_initial_migration.py#L97-L111)

## 详细组件分析

### 用户表 (users)

用户表是整个系统的核心实体，存储平台用户的基本信息和账户状态。

#### 字段定义

| 字段名 | 数据类型 | 约束条件 | 描述 |
|--------|----------|----------|------|
| id | UUID | 主键, 非空, 默认值 | 用户唯一标识符 |
| email | String(255) | 唯一, 非空 | 用户邮箱地址 |
| password_hash | String(255) | 非空 | 用户密码哈希值 |
| name | String(100) | 可空 | 用户姓名 |
| plan | String(20) | 非空, 默认值: "free" | 用户套餐类型 |
| max_queries | Integer | 非空, 默认值: 5 | 最大查询次数限制 |
| is_active | Boolean | 非空, 默认值: true | 账户激活状态 |
| created_at | Timestamp | 非空, 默认值: NOW() | 创建时间 |
| updated_at | Timestamp | 非空, 默认值: NOW() | 更新时间 |

#### 约束和索引

- 主键: id (UUID)
- 唯一约束: email
- 外键: 无直接外键关系
- 关系: 与查询表(一对多)、订阅表(一对多)

#### 业务规则

- 用户必须提供唯一的邮箱地址
- 默认免费套餐，最大查询次数为5次
- 账户默认激活状态
- 时间戳自动管理

**章节来源**
- [user.py:14-33](file://backend/app/models/user.py#L14-L33)
- [initial_migration.py:24-37](file://backend/alembic/versions/488d0bd5ab01_initial_migration.py#L24-L37)

### 查询表 (queries)

查询表记录用户的搜索关键词和品牌监控配置。

#### 字段定义

| 字段名 | 数据类型 | 约束条件 | 描述 |
|--------|----------|----------|------|
| id | UUID | 主键, 非空, 默认值 | 查询记录唯一标识符 |
| user_id | UUID | 外键, 非空 | 关联用户ID |
| keyword | String(200) | 非空 | 搜索关键词 |
| target_brand | String(100) | 非空 | 目标品牌名称 |
| brand_aliases | JSONB | 非空, 默认值: [] | 品牌别名列表 |
| platforms | JSONB | 非空, 默认值: ["wenxin","kimi"] | 监控平台列表 |
| frequency | String(20) | 非空, 默认值: "weekly" | 查询频率 |
| status | String(20) | 非空, 默认值: "active" | 查询状态 |
| last_queried_at | Timestamp | 可空 | 最后查询时间 |
| next_query_at | Timestamp | 可空 | 下次查询时间 |
| created_at | Timestamp | 非空, 默认值: NOW() | 创建时间 |
| updated_at | Timestamp | 非空, 默认值: NOW() | 更新时间 |

#### 约束和索引

- 主键: id (UUID)
- 外键: user_id → users.id (级联删除)
- 索引: idx_queries_user_id, idx_queries_status, idx_queries_next_query_at
- 关系: 与用户表(多对一)、引用记录表(一对多)、查询任务表(一对多)

#### 业务规则

- 必须关联有效用户
- 平台默认监控"wenxin"和"kimi"
- 状态默认为"active"
- 支持定时查询调度
- 级联删除确保数据一致性

**章节来源**
- [query.py:14-48](file://backend/app/models/query.py#L14-L48)
- [initial_migration.py:40-56](file://backend/alembic/versions/488d0bd5ab01_initial_migration.py#L40-L56)

### 引用记录表 (citation_records)

引用记录表存储每次查询的具体结果和竞争情报。

#### 字段定义

| 字段名 | 数据类型 | 约束条件 | 描述 |
|--------|----------|----------|------|
| id | UUID | 主键, 非空, 默认值 | 引用记录唯一标识符 |
| query_id | UUID | 外键, 非空 | 关联查询ID |
| platform | String(50) | 非空 | 查询平台名称 |
| cited | Boolean | 非空, 默认值: false | 是否被引用 |
| citation_position | Integer | 可空 | 引用位置 |
| citation_text | Text | 可空 | 引用文本内容 |
| competitor_brands | JSONB | 非空, 默认值: [] | 竞争品牌列表 |
| raw_response | Text | 可空 | 原始响应内容 |
| queried_at | Timestamp | 非空, 默认值: NOW() | 查询时间 |

#### 约束和索引

- 主键: id (UUID)
- 外键: query_id → queries.id (级联删除)
- 索引: idx_citation_records_query_id, idx_citation_records_queried_at, idx_citation_records_platform
- 关系: 与查询表(多对一)

#### 业务规则

- 必须关联有效查询
- 引用状态默认未引用
- 支持JSON格式的竞争品牌数据
- 按平台和查询时间建立索引优化查询性能
- 级联删除确保查询历史完整清理

**章节来源**
- [citation_record.py:14-35](file://backend/app/models/citation_record.py#L14-L35)
- [initial_migration.py:62-78](file://backend/alembic/versions/488d0bd5ab01_initial_migration.py#L62-L78)

### 查询任务表 (query_tasks)

查询任务表管理异步查询任务的状态和执行信息。

#### 字段定义

| 字段名 | 数据类型 | 约束条件 | 描述 |
|--------|----------|----------|------|
| id | UUID | 主键, 非空, 默认值 | 任务唯一标识符 |
| query_id | UUID | 外键, 非空 | 关联查询ID |
| platform | String(50) | 非空 | 执行平台 |
| status | String(20) | 非空, 默认值: "pending" | 任务状态 |
| error_message | Text | 可空 | 错误信息 |
| scheduled_at | Timestamp | 非空, 默认值: NOW() | 调度时间 |
| started_at | Timestamp | 可空 | 开始执行时间 |
| completed_at | Timestamp | 可空 | 完成时间 |

#### 约束和索引

- 主键: id (UUID)
- 外键: query_id → queries.id (级联删除)
- 索引: idx_query_tasks_status
- 关系: 与查询表(多对一)

#### 业务规则

- 必须关联有效查询
- 任务状态默认为"pending"
- 支持任务执行跟踪
- 状态变更自动记录时间戳
- 级联删除确保任务历史清理

**章节来源**
- [query_task.py:14-34](file://backend/app/models/query_task.py#L14-L34)
- [initial_migration.py:81-94](file://backend/alembic/versions/488d0bd5ab01_initial_migration.py#L81-L94)

### 订阅表 (subscriptions)

订阅表管理用户的付费订阅信息和状态。

#### 字段定义

| 字段名 | 数据类型 | 约束条件 | 描述 |
|--------|----------|----------|------|
| id | UUID | 主键, 非空, 默认值 | 订阅记录唯一标识符 |
| user_id | UUID | 外键, 非空 | 关联用户ID |
| plan | String(20) | 非空 | 套餐类型 |
| status | String(20) | 非空, 默认值: "active" | 订阅状态 |
| start_date | Date | 非空 | 订阅开始日期 |
| end_date | Date | 非空 | 订阅结束日期 |
| amount | Numeric(10,2) | 可空 | 支付金额 |
| payment_method | String(50) | 可空 | 支付方式 |
| payment_id | String(255) | 可空 | 支付ID |
| created_at | Timestamp | 非空, 默认值: NOW() | 创建时间 |

#### 约束和索引

- 主键: id (UUID)
- 外键: user_id → users.id (级联删除)
- 关系: 与用户表(多对一)

#### 业务规则

- 必须关联有效用户
- 状态默认为"active"
- 支持多种支付方式
- 日期范围确保订阅有效性
- 级联删除确保订阅历史清理

**章节来源**
- [subscription.py:14-36](file://backend/app/models/subscription.py#L14-L36)
- [initial_migration.py:97-111](file://backend/alembic/versions/488d0bd5ab01_initial_migration.py#L97-L111)

## 依赖分析

### 外键关系图

```mermaid
graph LR
Users["Users表<br/>主键: id"] --> |外键: user_id| Queries["Queries表<br/>主键: id"]
Queries --> |外键: query_id| CitationRecords["CitationRecords表<br/>主键: id"]
Queries --> |外键: query_id| QueryTasks["QueryTasks表<br/>主键: id"]
Users --> |外键: user_id| Subscriptions["Subscriptions表<br/>主键: id"]
style Users fill:#e1f5fe
style Queries fill:#f3e5f5
style CitationRecords fill:#e8f5e8
style QueryTasks fill:#fff3e0
style Subscriptions fill:#fce4ec
```

**图表来源**
- [initial_migration.py:55](file://backend/alembic/versions/488d0bd5ab01_initial_migration.py#L55)
- [initial_migration.py:74](file://backend/alembic/versions/488d0bd5ab01_initial_migration.py#L74)
- [initial_migration.py:92](file://backend/alembic/versions/488d0bd5ab01_initial_migration.py#L92)
- [initial_migration.py:110](file://backend/alembic/versions/488d0bd5ab01_initial_migration.py#L110)

### 关系类型分析

#### 一对一关系
- users ↔ subscriptions: 一个用户对应一个当前有效的订阅记录

#### 一对多关系
- users → queries: 一个用户可以有多个查询记录
- users → subscriptions: 一个用户可以有多个订阅记录
- queries → citation_records: 一个查询可以有多个引用记录
- queries → query_tasks: 一个查询可以有多个任务

#### 多对多关系
- 通过中间表实现的多对多关系不存在于当前设计中

### 级联删除策略

所有外键关系都设置了级联删除策略：
- 删除用户时，自动删除其所有查询、订阅记录
- 删除查询时，自动删除其所有引用记录和任务

**章节来源**
- [initial_migration.py:55](file://backend/alembic/versions/488d0bd5ab01_initial_migration.py#L55)
- [initial_migration.py:74](file://backend/alembic/versions/488d0bd5ab01_initial_migration.py#L74)
- [initial_migration.py:92](file://backend/alembic/versions/488d0bd5ab01_initial_migration.py#L92)
- [initial_migration.py:110](file://backend/alembic/versions/488d0bd5ab01_initial_migration.py#L110)

## 性能考虑

### 索引策略

系统为关键查询字段建立了专门的索引：

#### 查询表索引
- idx_queries_user_id: 提升按用户查询的性能
- idx_queries_status: 支持状态过滤查询
- idx_queries_next_query_at: 优化定时任务调度

#### 引用记录表索引
- idx_citation_records_query_id: 提升按查询获取记录的性能
- idx_citation_records_queried_at: 支持时间序列查询
- idx_citation_records_platform: 优化平台过滤

#### 查询任务表索引
- idx_query_tasks_status: 支持任务状态统计和调度

### 数据类型优化

- 使用UUID作为主键，避免序列号暴露业务信息
- JSONB类型存储动态配置，支持高效查询和更新
- 数值类型使用Numeric精确存储金额数据
- 时间戳使用带时区的DateTime确保时区一致性

### 查询优化建议

1. **批量操作**: 对于大量数据的插入和更新，使用批量操作减少数据库往返
2. **分页查询**: 对于列表查询，实现分页机制避免一次性加载过多数据
3. **缓存策略**: 结合Redis实现热点数据缓存
4. **连接池管理**: 合理配置连接池大小以平衡性能和资源使用

## 故障排除指南

### 常见问题及解决方案

#### 数据库连接问题
- **症状**: 应用启动时报数据库连接错误
- **原因**: DATABASE_URL配置不正确或数据库服务未启动
- **解决**: 检查.env文件中的DATABASE_URL配置，确认数据库服务正常运行

#### 外键约束冲突
- **症状**: 删除用户时报外键约束错误
- **原因**: 存在相关记录导致级联删除失败
- **解决**: 确保级联删除策略正确配置，检查数据完整性

#### 索引性能问题
- **症状**: 查询响应缓慢
- **原因**: 缺少必要的索引或索引设计不当
- **解决**: 分析查询计划，添加适当的索引

#### 数据类型转换错误
- **症状**: JSONB字段操作报错
- **原因**: 数据类型不匹配或格式错误
- **解决**: 确保JSONB数据格式正确，使用适当的序列化方法

### 调试工具

1. **数据库监控**: 使用PostgreSQL内置监控工具查看查询性能
2. **日志分析**: 启用SQLAlchemy日志输出分析查询执行情况
3. **性能分析**: 使用EXPLAIN ANALYZE分析慢查询

**章节来源**
- [config.py:7](file://backend/app/config.py#L7)
- [initial_migration.py:114-127](file://backend/alembic/versions/488d0bd5ab01_initial_migration.py#L114-L127)

## 结论

GEO项目的数据库表结构设计体现了现代Web应用的最佳实践：

### 设计优势

1. **清晰的数据模型**: 五个核心表覆盖了完整的业务场景
2. **合理的外键关系**: 确保数据一致性和完整性
3. **高效的索引策略**: 针对常见查询模式优化性能
4. **异步数据库支持**: 提供良好的并发处理能力
5. **灵活的数据类型**: JSONB支持动态配置需求

### 业务逻辑实现

- 用户权限控制通过用户表的激活状态实现
- 查询配额控制通过用户表的max_queries字段实现
- 订阅管理通过订阅表的日期范围和状态字段实现
- 数据清理通过级联删除策略自动维护

### 扩展性考虑

系统设计具有良好的扩展性：
- 新增表时可复用现有的UUID主键模式
- JSONB字段支持未来功能的动态配置
- 异步架构支持水平扩展
- 清晰的关系设计便于添加新的业务实体

这个数据模型为GEO平台提供了坚实的基础，能够支持用户查询监控、竞争情报分析和订阅管理等核心业务功能。