# Dating App 数据库表设计与关系

## 1. 设计范围

本次先设计注册和用户资料相关的 Function 数据库，使用 MySQL 8.0，字符集为 `utf8mb4`，排序规则为 `utf8mb4_unicode_ci`。

当前范围包括：账号注册、密码登录、微信/谷歌登录、用户资料、自我介绍视频和身份证/护照文件。

> 2026-08-31 MVP 更新：邮箱和手机号统一使用 `users.email_or_phone` 一列；MVP 不使用 OTP，新注册账号直接激活。下方旧版验证码描述不再适用于当前接口，数据库迁移见 `sql/migrate_auth_mvp_no_otp.sql`。

## 2. ER 关系图

简化关系：

```text
users (用户主表)
  │ 1
  ├──────── 0..1 user_profiles      用户资料
  ├──────── 0..1 user_locations     用户最近位置
  ├──────── 0..N auth_identities    微信/谷歌登录绑定
  ├──────── 0..N user_media         头像、视频、身份证/护照文件
  ├──────── 0..N swipes             发出的左滑/右滑/超级喜欢
  ├──────── 0..N matches            已建立的双向匹配
  ├──────── 0..N posts              Explore 帖子
  ├──────── 0..N post_likes         帖子点赞
  ├──────── 0..N post_comments      帖子评论
  └──────── 0..N chat_messages      聊天消息

verification_codes (验证码记录)
  └── 通过 target + channel 与注册账号逻辑关联
```

```mermaid
erDiagram
    USERS ||--o| USER_PROFILES : "拥有一份资料"
    USERS ||--o| USER_LOCATIONS : "拥有一个最近位置"
    USERS ||--o{ AUTH_IDENTITIES : "绑定登录方式"
    USERS ||--o{ USER_MEDIA : "上传媒体/证件"
    USERS ||--o{ SWIPES : "发起滑动"
    USERS ||--o{ MATCHES : "形成匹配"
    USERS ||--o{ POSTS : "发布帖子"
    POSTS ||--o{ POST_LIKES : "收到点赞"
    POSTS ||--o{ POST_COMMENTS : "收到评论"
    MATCHES ||--o| CONVERSATIONS : "拥有会话"
    CONVERSATIONS ||--o{ CHAT_MESSAGES : "包含消息"

    USERS {
        int id PK
        varchar email UK
        varchar phone UK
        varchar password_hash
        varchar status
        boolean email_verified
        boolean phone_verified
        datetime created_at
        datetime updated_at
    }

    USER_PROFILES {
        int id PK
        int user_id FK_UK
        varchar full_name
        varchar gender
        varchar birth_date
        varchar city
        varchar country
        text bio
    }

    AUTH_IDENTITIES {
        int id PK
        int user_id FK
        varchar provider
        varchar provider_user_id
        datetime created_at
    }

    USER_MEDIA {
        int id PK
        int user_id FK
        varchar media_type
        varchar storage_path
        varchar original_name
        varchar mime_type
        varchar review_status
        datetime created_at
    }

    VERIFICATION_CODES {
        int id PK
        varchar target
        varchar channel
        varchar code_hash
        datetime expires_at
        datetime used_at
        int attempts
        datetime created_at
    }
```

说明：`VERIFICATION_CODES` 暂时不设置 `user_id` 外键。用户还没有注册成功时，就需要先发送验证码，因此验证码通过 `target + channel`（邮箱或手机号）关联注册流程；验证成功后再更新 `USERS.email_verified` 或 `USERS.phone_verified`。

## 3. 表结构

### 3.1 users：用户主表

| 字段 | 类型 | 约束 | 说明 |
|---|---|---|---|
| `id` | INT | 主键、自增 | 用户唯一编号 |
| `email` | VARCHAR(320) | 唯一、可为空 | 邮箱账号 |
| `phone` | VARCHAR(32) | 唯一、可为空 | 手机号账号 |
| `password_hash` | VARCHAR(256) | 可为空 | 加密后的密码；第三方登录用户可以为空 |
| `status` | VARCHAR(20) | 非空 | `pending`、`active`、`suspended` |
| `email_verified` | BOOLEAN | 默认 false | 邮箱是否已验证 |
| `phone_verified` | BOOLEAN | 默认 false | 手机号是否已验证 |
| `created_at` | DATETIME | 非空 | 注册时间 |
| `updated_at` | DATETIME | 非空 | 最后更新时间 |

注册账号至少提供邮箱或手机号之一。邮箱和手机号都允许为空，是为了支持只使用第三方账号注册。

### 3.2 user_profiles：用户个人资料表

| 字段 | 类型 | 约束 | 说明 |
|---|---|---|---|
| `id` | INT | 主键、自增 | 资料记录编号 |
| `user_id` | INT | 非空、唯一、外键 | 关联 `users.id` |
| `full_name` | VARCHAR(120) | 可为空 | 姓名/昵称 |
| `gender` | VARCHAR(30) | 可为空 | 性别 |
| `birth_date` | VARCHAR(10) | 可为空 | 生日，格式 `YYYY-MM-DD` |
| `relationship_goal` | VARCHAR(30) | 可为空 | 交友目标；新注册必须从 `serious_relationship`、`casual_dates`、`new_friends`、`marriage` 中单选 |
| `city` | VARCHAR(120) | 可为空 | 所在城市 |
| `country` | VARCHAR(120) | 可为空 | 所在国家，用于推荐和搜索排序 |
| `bio` | TEXT | 可为空 | 个人简介 |

关系：一个用户最多有一份个人资料；删除用户时级联删除资料。

### 3.3 auth_identities：第三方登录绑定表

| 字段 | 类型 | 约束 | 说明 |
|---|---|---|---|
| `id` | INT | 主键、自增 | 绑定记录编号 |
| `user_id` | INT | 非空、外键 | 关联 `users.id` |
| `provider` | VARCHAR(30) | 非空 | `wechat` 或 `google` |
| `provider_user_id` | VARCHAR(255) | 非空 | 第三方平台用户编号 |
| `created_at` | DATETIME | 非空 | 绑定时间 |

唯一约束：`provider + provider_user_id`，防止同一个微信或谷歌账号绑定多个用户。

关系：一个用户可以绑定多个第三方登录方式；一条第三方绑定记录只属于一个用户。

### 3.4 verification_codes：验证码表

| 字段 | 类型 | 约束 | 说明 |
|---|---|---|---|
| `id` | INT | 主键、自增 | 验证码记录编号 |
| `target` | VARCHAR(320) | 非空、索引 | 邮箱地址或手机号 |
| `channel` | VARCHAR(10) | 非空 | `email` 或 `phone` |
| `code_hash` | VARCHAR(256) | 非空 | 验证码哈希值，不保存明文验证码 |
| `expires_at` | DATETIME | 非空 | 过期时间 |
| `used_at` | DATETIME | 可为空 | 使用时间，未使用时为空 |
| `attempts` | INT | 默认 0 | 验证失败次数 |
| `created_at` | DATETIME | 非空 | 生成时间 |

关系：验证码属于一次验证请求，不直接绑定用户。验证成功后更新 `users` 表的验证状态。

### 3.5 user_media：用户媒体和身份文件表

| 字段 | 类型 | 约束 | 说明 |
|---|---|---|---|
| `id` | INT | 主键、自增 | 文件记录编号 |
| `user_id` | INT | 非空、外键 | 关联 `users.id` |
| `media_type` | VARCHAR(20) | 非空 | `intro_video` 或 `identity_document` |
| `storage_path` | VARCHAR(500) | 非空 | 文件存储地址 |
| `original_name` | VARCHAR(255) | 可为空 | 原始文件名 |
| `mime_type` | VARCHAR(100) | 可为空 | 文件类型 |
| `review_status` | VARCHAR(20) | 默认 pending | `pending`、`approved`、`rejected` |
| `created_at` | DATETIME | 非空 | 上传时间 |

关系：一个用户可以上传多个文件。数据库只保存文件地址和审核信息，视频、身份证和护照文件本身不直接存进数据库。

## 4. 关系汇总

| 主表 | 从表 | 关系 | 外键 | 删除策略 |
|---|---|---|---|---|
| `users` | `user_profiles` | 一对零或一 | `user_profiles.user_id` | 删除用户时级联删除 |
| `users` | `auth_identities` | 一对多 | `auth_identities.user_id` | 删除用户时级联删除 |
| `users` | `user_media` | 一对多 | `user_media.user_id` | 删除用户时级联删除 |
| `users` | `verification_codes` | 逻辑关联 | `target + channel` | 验证码独立过期 |

## 5. 注册流程对应的数据变化

1. 用户提交邮箱/手机号和密码，写入 `users`，初始状态为 `pending`。
2. 用户填写个人信息，写入 `user_profiles`。
3. 系统发送验证码，写入 `verification_codes`，只保存验证码哈希。
4. 验证成功后，更新 `users.email_verified` 或 `users.phone_verified`，并将状态改为 `active`。
5. 用户上传视频或证件，在 `user_media` 中保存文件记录，初始审核状态为 `pending`。
6. 微信/谷歌登录时，在 `auth_identities` 保存第三方账号和对应的 `user_id`。

## 6. 当前实现文件

- 数据模型：[app/models.py](../app/models.py)
- 数据库连接：[app/database.py](../app/database.py)
- 接口实现：[app/main.py](../app/main.py)
- MySQL 建库脚本：[sql/create_database.sql](../sql/create_database.sql)

## 7. Meet 与 Search 扩展表（2026-08-25）

| 表 | 关键字段 | 关系与用途 |
|---|---|---|
| `user_locations` | `user_id`、`latitude`、`longitude` | 与用户一对一；用于附近的人和距离排序；位置请求的城市、区县保存在 `user_profiles` |
| `swipes` | `actor_id`、`target_id`、`direction` | 记录 `like`/`pass`/`super_like`；同一用户对唯一，撤销时删除记录 |
| `matches` | `user1_id`、`user2_id`、`matched_at` | 双方相互喜欢（`like` 或 `super_like`）后生成；用户编号按小到大保存，确保一对用户只有一个匹配 |

隐私规则：`user_media` 中只有 `profile_image` 和 `intro_video` 会出现在 Meet/Search 响应里；`identity_document` 仅文件所属用户可访问。图片内容相似搜索依赖 ML，留到 Function 阶段完成后实现。

## 8. Explore 信息流扩展表（2026-08-26）

| 表 | 关键字段 | 用途 |
|---|---|---|
| `posts` | `user_id`、`content`、`post_type`、`content_format`、`feeling`、`location_name`、`location_latitude`、`location_longitude` | 保存帖子文字、Markdown 格式、心情和所选地点的快照 |
| `post_media` | `post_id`、`media_type`、`storage_path`、`position` | 保存帖子图片或视频，最多 9 张图片或 1 个视频 |
| `post_likes` | `post_id`、`user_id` | 记录用户对帖子的点赞；同一用户对同一帖子只能点赞一次 |
| `post_comments` | `post_id`、`user_id`、`content` | 保存帖子评论，删除帖子时级联删除 |

图片/视频附件已通过 `post_media` 接入；帖子创建后再上传媒体。音乐、直播和 ML 帖子分类尚未接入。

## 9. Chat 会话扩展表（2026-08-26）

| 表 | 关键字段 | 用途 |
|---|---|---|
| `conversations` | `match_id` | 一个互相匹配关系最多对应一个会话 |
| `chat_messages` | `conversation_id`、`sender_id`、`content` | 保存文本消息；消息只能由会话双方访问 |

聊天权限来自 `matches` 表：没有互相喜欢形成的匹配关系，不能创建会话或发送消息。消息撤回采用 `deleted_at` 标记，历史记录仍保留但对客户端隐藏正文。

## 10. 登录会话表（Login_Registration.pdf 要求）

`user_sessions` 保存 refresh token 会话，不保存明文 token：

| 字段 | 说明 |
|---|---|
| `user_id` | 会话所属用户 |
| `refresh_token_hash` | refresh token 的哈希值 |
| `expires_at` | 30 天默认过期时间 |
| `revoked_at` | logout 或 token 轮换后的撤销时间 |

access token 默认 15 分钟，refresh token 默认 30 天。应用启动时调用 `/api/v1/auth/me`；refresh 接口采用轮换策略，旧 refresh token 立即失效。

## 11. 帖子媒体与 AI Feed 问题（2026-08-27）

| 表 | 关键字段 | 用途 |
|---|---|---|
| `post_media` | `post_id`、`media_type`、`storage_path` | 保存帖子图片/视频的文件信息，一条帖子最多 9 张图片或 1 个视频 |
| `feed_questions` | `code`、`question_text`、`answer_type` | 保存系统提供的偏好问题，支持布尔和文字回答 |
| `user_question_answers` | `user_id`、`question_id`、回答内容 | 保存用户答案；同一用户对同一问题只有一条最新答案 |

删除帖子时级联删除 `post_media` 数据库记录，同时由接口删除实际文件。删除用户或问题时级联删除对应答案。AI 阶段将使用这些结构化答案训练或计算用户兴趣与匹配特征。

## 12. 国家和匹配度推荐（2026-09-01）

`user_profiles.country` 保存用户所在国家。Meet、用户搜索和 Explore 信息流按同城、同国家、其他国家分组；同组内按匹配百分比排序。匹配百分比由现有 `user_interests` 和双方共同回答的 `user_question_answers` 实时计算，因此不新增持久化分数字段，避免用户资料变化后产生过期分数。

`user_profiles.county` 保存区县，和国家 `country` 不同；与经纬度一同通过位置接口提交。位置接口同步更新 `user_profiles.city`，供同城推荐使用。历史用户的区县可为空。