# Dating App Database Design and Relationships

## 1. Scope

This database design covers the first Function module: account registration and user profile management.

The database is designed for MySQL 8.0 using the `utf8mb4` character set and the `utf8mb4_unicode_ci` collation.

The current scope includes:

- Registration with a unified email-or-phone field
- Password login
- WeChat and Google login bindings
- User profile information
- Introduction videos
- ID card or passport documents

> MVP update (2026-08-31): email and phone now share `users.email_or_phone`. OTP is not used, and new accounts are active immediately. Legacy verification columns may remain only for non-destructive database compatibility.

## 2. ER Diagram

Simplified relationship overview:

```text
users (user master table)
  | 1
  +-------- 0..1 user_profiles       User profile
  +-------- 0..1 user_locations      Latest user location
  +-------- 0..N auth_identities     WeChat/Google login bindings
  +-------- 0..N user_media          Images, videos, and identity documents
  +-------- 0..N swipes              Outgoing like/pass/super_like decisions
  +-------- 0..N matches             Mutual matches
  +-------- 0..N posts               Explore posts
  +-------- 0..N post_likes          Post likes
  +-------- 0..N post_comments       Post comments
  +-------- 0..N chat_messages       Chat messages

verification_codes (verification requests)
  +---- logically associated with registration through target + channel
```

```mermaid
erDiagram
    USERS ||--o| USER_PROFILES : "has one profile"
    USERS ||--o| USER_LOCATIONS : "has one latest location"
    USERS ||--o{ AUTH_IDENTITIES : "binds login methods"
    USERS ||--o{ USER_MEDIA : "uploads media"
    USERS ||--o{ SWIPES : "makes decisions"
    USERS ||--o{ MATCHES : "forms matches"
    USERS ||--o{ POSTS : "publishes posts"
    POSTS ||--o{ POST_LIKES : "receives likes"
    POSTS ||--o{ POST_COMMENTS : "receives comments"
    MATCHES ||--o| CONVERSATIONS : "has conversation"
    CONVERSATIONS ||--o{ CHAT_MESSAGES : "contains 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` does not have a direct `user_id` foreign key. A verification code may be sent before a user account has been created, so it is associated with the registration flow through `target` and `channel` (email or phone). After successful verification, the corresponding verification flag in `users` is updated.

## 3. Table Definitions

### 3.1 `users`: User Master Table

| Column | Type | Constraints | Description |
|---|---|---|---|
| `id` | INT | Primary key, auto-increment | Unique user identifier |
| `email` | VARCHAR(320) | Unique, nullable | Email account |
| `phone` | VARCHAR(32) | Unique, nullable | Phone account |
| `password_hash` | VARCHAR(256) | Nullable | Hashed password; can be empty for third-party-only accounts |
| `status` | VARCHAR(20) | Not null | `pending`, `active`, or `suspended` |
| `email_verified` | BOOLEAN | Default `false` | Whether the email has been verified |
| `phone_verified` | BOOLEAN | Default `false` | Whether the phone has been verified |
| `created_at` | DATETIME | Not null | Registration time |
| `updated_at` | DATETIME | Not null | Last update time |

At least one of email or phone is required for a normal registration. Both fields are nullable to support accounts created through third-party login providers.

### 3.2 `user_profiles`: User Profile Table

| Column | Type | Constraints | Description |
|---|---|---|---|
| `id` | INT | Primary key, auto-increment | Profile record identifier |
| `user_id` | INT | Not null, unique, foreign key | References `users.id` |
| `full_name` | VARCHAR(120) | Nullable | Name or nickname |
| `gender` | VARCHAR(30) | Nullable | Gender |
| `birth_date` | VARCHAR(10) | Nullable | Date of birth in `YYYY-MM-DD` format |
| `relationship_goal` | VARCHAR(30) | Nullable | Dating goal; new registrations require one of `serious_relationship`, `casual_dates`, `new_friends`, or `marriage` |
| `city` | VARCHAR(120) | Nullable | Current city |
| `country` | VARCHAR(120) | Nullable | Current country, used for discovery and search ordering |
| `bio` | TEXT | Nullable | User biography |

Relationship: one user can have zero or one profile. The profile is deleted automatically when its user is deleted.

### 3.3 `auth_identities`: Third-Party Login Binding Table

| Column | Type | Constraints | Description |
|---|---|---|---|
| `id` | INT | Primary key, auto-increment | Binding record identifier |
| `user_id` | INT | Not null, foreign key | References `users.id` |
| `provider` | VARCHAR(30) | Not null | `wechat` or `google` |
| `provider_user_id` | VARCHAR(255) | Not null | User identifier from the provider |
| `created_at` | DATETIME | Not null | Binding time |

Unique constraint: `(provider, provider_user_id)`. This prevents one WeChat or Google account from being linked to multiple users.

Relationship: one user can have multiple third-party login bindings; each binding belongs to exactly one user.

### 3.4 `verification_codes`: Verification Code Table

| Column | Type | Constraints | Description |
|---|---|---|---|
| `id` | INT | Primary key, auto-increment | Verification record identifier |
| `target` | VARCHAR(320) | Not null, indexed | Email address or phone number |
| `channel` | VARCHAR(10) | Not null | `email` or `phone` |
| `code_hash` | VARCHAR(256) | Not null | Hashed verification code; plaintext is not stored |
| `expires_at` | DATETIME | Not null | Expiration time |
| `used_at` | DATETIME | Nullable | Time the code was used |
| `attempts` | INT | Default `0` | Number of failed verification attempts |
| `created_at` | DATETIME | Not null | Generation time |

Relationship: each row represents one verification request. It is independent from a user record and expires after its validity period.

### 3.5 `user_media`: User Media and Identity Document Table

| Column | Type | Constraints | Description |
|---|---|---|---|
| `id` | INT | Primary key, auto-increment | File record identifier |
| `user_id` | INT | Not null, foreign key | References `users.id` |
| `media_type` | VARCHAR(20) | Not null | `intro_video` or `identity_document` |
| `storage_path` | VARCHAR(500) | Not null | Object-storage or filesystem path |
| `original_name` | VARCHAR(255) | Nullable | Original uploaded filename |
| `mime_type` | VARCHAR(100) | Nullable | File MIME type |
| `review_status` | VARCHAR(20) | Default `pending` | `pending`, `approved`, or `rejected` |
| `created_at` | DATETIME | Not null | Upload time |

Relationship: one user can upload multiple media records. The database stores file metadata and the storage path; the video and identity document binary data are not stored directly in MySQL.

## 4. Relationship Summary

| Parent table | Child table | Relationship | Foreign key | Delete behavior |
|---|---|---|---|---|
| `users` | `user_profiles` | One-to-zero-or-one | `user_profiles.user_id` | Cascade delete |
| `users` | `auth_identities` | One-to-many | `auth_identities.user_id` | Cascade delete |
| `users` | `user_media` | One-to-many | `user_media.user_id` | Cascade delete |
| `users` | `verification_codes` | Logical association | `target + channel` | Code expires independently |

## 5. Registration Data Flow

1. The user submits an email or phone number and password. A row is inserted into `users` with status `pending`.
2. Basic profile information is inserted into `user_profiles`.
3. The system sends a verification code and stores its hash in `verification_codes`.
4. After successful verification, `email_verified` or `phone_verified` is set to `true`, and the user status becomes `active`.
5. When the user uploads an introduction video or identity document, a record is inserted into `user_media` with review status `pending`.
6. When the user signs in with WeChat or Google, the provider account is stored in `auth_identities` and linked to the corresponding `user_id`.

## 6. Implementation Files

- Data models: [`app/models.py`](../app/models.py)
- Database connection: [`app/database.py`](../app/database.py)
- API implementation: [`app/main.py`](../app/main.py)
- MySQL database creation: [`sql/create_database.sql`](../sql/create_database.sql)
- MySQL table schema: [`sql/schema.sql`](../sql/schema.sql)

## 7. Meet and Search Extension Tables (2026-08-25)

| Table | Key columns | Relationship and purpose |
|---|---|---|
| `user_locations` | `user_id`, `latitude`, `longitude` | One-to-one with a user; supports nearby search and distance ordering; city and county from location requests are saved in `user_profiles` |
| `swipes` | `actor_id`, `target_id`, `direction` | Stores `like`/`pass`/`super_like`; one decision per user pair, deleted on undo |
| `matches` | `user1_id`, `user2_id`, `matched_at` | Created after reciprocal `like` or `super_like`; IDs are stored in ascending order to keep each pair unique |

Privacy rule: only `profile_image` and `intro_video` media appear in Meet/Search responses. An `identity_document` can only be downloaded by its owner. Content-based image search depends on ML and remains scheduled for after the Function stage.

## 8. Explore Feed Extension Tables (2026-08-26)

| Table | Key columns | Purpose |
|---|---|---|
| `posts` | `user_id`, `content`, `post_type`, `content_format`, `feeling`, `location_name`, `location_latitude`, `location_longitude` | Stores post text, Markdown mode, feeling, and a snapshot of the selected place |
| `post_media` | `post_id`, `media_type`, `storage_path`, `position` | Stores up to nine images or one video per post |
| `post_likes` | `post_id`, `user_id` | Stores likes; one user can like a post only once |
| `post_comments` | `post_id`, `user_id`, `content` | Stores comments and cascades when a post is deleted |

Image/video attachments are already stored through `post_media` after post creation. Music, live streaming, and ML-based post classification remain future work.

## 9. Chat Conversation Extension Tables (2026-08-26)

| Table | Key columns | Purpose |
|---|---|---|
| `conversations` | `match_id` | At most one conversation belongs to a mutual match |
| `chat_messages` | `conversation_id`, `sender_id`, `content` | Stores text messages accessible only to the two matched users |

Chat authorization comes from `matches`: users cannot create a conversation or send messages without a mutual match. Message recall uses a `deleted_at` marker; the record remains for history while the body is hidden from clients.

## 10. Login Session Table (required by Login_Registration.pdf)

`user_sessions` stores refresh-token sessions without storing the plaintext token:

| Column | Description |
|---|---|
| `user_id` | User who owns the session |
| `refresh_token_hash` | Hashed refresh token |
| `expires_at` | 30-day default expiration |
| `revoked_at` | Set during logout or token rotation |

Access tokens default to 15 minutes and refresh tokens to 30 days. The app calls `/api/v1/auth/me` at startup. Refresh uses token rotation, so the previous refresh token becomes invalid immediately.

## 11. Post Media and AI Feed Questions (2026-08-27)

| Table | Key columns | Purpose |
|---|---|---|
| `post_media` | `post_id`, `media_type`, `storage_path` | Stores post image/video files; one post supports up to 9 images or 1 video |
| `feed_questions` | `code`, `question_text`, `answer_type` | Stores preference questions with boolean or text answers |
| `user_question_answers` | `user_id`, `question_id`, answer fields | Stores one latest answer per user and question |

Deleting a post cascades its `post_media` records and the API removes the physical files. Deleting a user or question cascades its answers. The later ML stage can use these structured answers as user-interest and matching features.

## 12. Country and Compatibility Ranking (2026-09-01)

`user_profiles.country` stores the user's country. Meet, user search, and the Explore feed group results by same city, same country, and other countries; results within a group are ranked by compatibility percentage. Compatibility is calculated from existing `user_interests` and mutually answered `user_question_answers`, so no persisted score column is added and scores cannot become stale when profile data changes.

`user_profiles.county` stores the district/county, separately from the nation-level `country`. The location endpoint updates it together with `user_profiles.city` and the coordinates. Existing users may have a NULL county.