# Aphrodite Domain - Database Schema

This document provides comprehensive documentation of the Aphrodite PostgreSQL
database schema, as defined in `libs/aphrodite/database/prisma/schema.prisma`.
The schema uses Prisma ORM with PostgreSQL and manages users, streams,
transactions, devices, chat, and moderation data.

**Connection variable:** `APHRODITE_DATABASE_URL`

---

## Table of Contents

1. [ER Diagram](#er-diagram)
2. [User Models](#user-models)
3. [Stream Models](#stream-models)
4. [Transaction Models](#transaction-models)
5. [Device Models](#device-models)
6. [Chat and Moderation Models](#chat-and-moderation-models)
7. [Enums](#enums)
8. [Indexes](#indexes)
9. [Migration Workflow](#migration-workflow)

---

## ER Diagram

```
┌─────────────────────────────────────────────────────────────────────────┐
│                         USER LAYER                                       │
│                                                                          │
│  ┌─────────────────┐                     ┌─────────────────┐            │
│  │   Broadcaster    │                     │     Viewer       │            │
│  │─────────────────│                     │─────────────────│            │
│  │ id (PK)         │                     │ id (PK)         │            │
│  │ userId (UQ)     │                     │ userId (UQ)     │            │
│  │ stageName       │                     │ displayName     │            │
│  │ status          │                     │ status          │            │
│  │ verificationSt. │                     │ tokenBalance    │            │
│  │ categories[]    │                     │ ageVerified     │            │
│  │ tokenPerMinute  │                     │ totalWatchTime  │            │
│  │ totalEarnings   │                     │ totalTipped     │            │
│  └────┬──┬──┬──┬───┘                     └────┬──┬──┬──┬───┘            │
│       │  │  │  │                              │  │  │  │                │
└───────┼──┼──┼──┼──────────────────────────────┼──┼──┼──┼────────────────┘
        │  │  │  │                              │  │  │  │
        │  │  │  │     ┌────────────────┐       │  │  │  │
        │  │  │  └────►│    Follow       │◄──────┘  │  │  │
        │  │  │        │ viewerId (FK)   │          │  │  │
        │  │  │        │ broadcasterId   │          │  │  │
        │  │  │        │ notifyOnLive    │          │  │  │
        │  │  │        └────────────────┘          │  │  │
        │  │  │                                    │  │  │
┌───────┼──┼──┼────────────────────────────────────┼──┼──┼────────────────┐
│       │  │  │        STREAM LAYER                 │  │  │                │
│       │  │  │                                    │  │  │                │
│       │  │  │  ┌─────────────────┐               │  │  │                │
│       │  │  └─►│     Stream       │               │  │  │                │
│       │  │     │─────────────────│               │  │  │                │
│       │  │     │ id (PK)         │               │  │  │                │
│       │  │     │ broadcasterId   │               │  │  │                │
│       │  │     │ title           │               │  │  │                │
│       │  │     │ status          │               │  │  │                │
│       │  │     │ streamKey (UQ)  │               │  │  │                │
│       │  │     │ currentViewers  │               │  │  │                │
│       │  │     │ totalTips       │               │  │  │                │
│       │  │     └───┬──┬──┬──┬───┘               │  │  │                │
│       │  │         │  │  │  │                    │  │  │                │
│       │  │         │  │  │  └────────────────┐   │  │  │                │
│       │  │         │  │  │  ┌──────────────┐ │   │  │  │                │
│       │  │         │  │  └─►│ PrivateShow  │ │   │  │  │                │
│       │  │         │  │     │ pricePerMin  │ │   │  │  │                │
│       │  │         │  │     │ totalCost    │ │   │  │  │                │
│       │  │         │  │     └──────┬───────┘ │   │  │  │                │
│       │  │         │  │            │         │   │  │  │                │
│       │  │         │  │  ┌─────────┴──┐      │   │  │  │                │
│       │  │         │  │  │PrivateShow │      │   │  │  │                │
│       │  │         │  │  │Request     │◄─────┼───┼──┘  │                │
│       │  │         │  │  │ viewerId   │      │   │     │                │
│       │  │         │  │  └────────────┘      │   │     │                │
│       │  │         │  │                      │   │     │                │
│       │  │         │  └─►┌──────────────┐    │   │     │                │
│       │  │         │     │ ViewSession   │◄───┼───┘     │                │
│       │  │         │     │ viewerId (FK) │    │         │                │
│       │  │         │     │ duration      │    │         │                │
│       │  │         │     └──────────────┘    │         │                │
│       │  │         │                         │         │                │
│       │  │         └─►┌──────────────┐       │         │                │
│       │  │            │ ChatMessage   │◄──────┼─────────┘                │
│       │  │            │ viewerId (FK) │       │                          │
│       │  │            │ content       │       │                          │
│       │  │            └──────────────┘       │                          │
└───────┼──┼───────────────────────────────────┼──────────────────────────┘
        │  │                                   │
┌───────┼──┼───────────────────────────────────┼──────────────────────────┐
│       │  │     TRANSACTION LAYER              │                          │
│       │  │                                   │                          │
│       │  │     ┌─────────────────┐           │                          │
│       │  └────►│      Tip         │◄──────────┘ (from Viewer)           │
│       │        │ amount           │                                      │
│       │        │ platformFee      │                                      │
│       │        │ broadcasterShare │                                      │
│       │        │ deviceTriggered  │                                      │
│       │        └─────────────────┘                                      │
│       │                                                                  │
│       │        ┌─────────────────┐    ┌──────────────────┐              │
│       │        │  Subscription    │    │  TokenPurchase    │              │
│       │        │ viewerId (FK)    │    │ viewerId (FK)     │              │
│       │        │ broadcasterId    │    │ tokenAmount       │              │
│       │        │ tierId (FK)      │    │ priceUsd          │              │
│       │        │ status           │    │ paymentProvider    │              │
│       │        └────────┬────────┘    └──────────────────┘              │
│       │                 │                                                │
│       │        ┌────────┴────────┐    ┌──────────────────┐              │
│       │        │SubscriptionTier │    │     Payout        │              │
│       │        │ name             │    │ broadcasterId     │◄─── from    │
│       └───────►│ price            │    │ amount            │  Broadcaster │
│                │ billingCycle     │    │ payoutMethod       │              │
│                └─────────────────┘    └──────────────────┘              │
└─────────────────────────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────────────────────┐
│                        DEVICE LAYER                                      │
│                                                                          │
│  ┌─────────────────┐    ┌──────────────────┐    ┌──────────────────┐    │
│  │     Device       │    │   DeviceGroup     │    │  DeviceControl   │    │
│  │ broadcasterId    │    │  broadcasterId    │    │ deviceId (FK)    │    │
│  │ deviceType       │    │  name             │    │ viewerId (FK)    │    │
│  │ connectionType   │    │  synchronize...   │    │ controlType      │    │
│  │ connectionStatus │    └────────┬─────────┘    │ intensity        │    │
│  │ maxIntensity     │             │              │ sourceType       │    │
│  └────────┬────────┘    ┌────────┴─────────┐    └──────────────────┘    │
│           │              │ DeviceGroupMember │                            │
│           └─────────────►│ deviceId (FK)     │                            │
│                          │ intensityMult.    │                            │
│                          └──────────────────┘                            │
└─────────────────────────────────────────────────────────────────────────┘
```

---

## User Models

### Broadcaster

Content creators who stream on the platform.

**Table:** `broadcasters`

| Column                 | Type               | Constraints         | Description                    |
| ---------------------- | ------------------ | ------------------- | ------------------------------ |
| `id`                   | UUID               | PK, auto-generated  | Broadcaster identifier         |
| `user_id`              | String             | Unique              | External user system reference |
| `stage_name`           | String             | Required            | Public display name            |
| `bio`                  | String             | Nullable            | Profile biography              |
| `avatar_url`           | String             | Nullable            | Profile image URL              |
| `banner_url`           | String             | Nullable            | Profile banner URL             |
| `profile_video_url`    | String             | Nullable            | Profile video URL              |
| `status`               | BroadcasterStatus  | Default: PENDING    | Account status                 |
| `verification_status`  | VerificationStatus | Default: UNVERIFIED | Identity verification state    |
| `verified_at`          | DateTime           | Nullable            | When verification completed    |
| `age_verified`         | Boolean            | Default: false      | Age verification passed        |
| `id_verified`          | Boolean            | Default: false      | Government ID verified         |
| `is_online`            | Boolean            | Default: false      | Currently online               |
| `is_streaming`         | Boolean            | Default: false      | Currently streaming            |
| `last_seen_at`         | DateTime           | Nullable            | Last activity timestamp        |
| `timezone`             | String             | Default: "UTC"      | Broadcaster timezone           |
| `categories`           | String[]           | Default: []         | Content categories             |
| `tags`                 | String[]           | Default: []         | Searchable tags                |
| `languages`            | String[]           | Default: ["en"]     | Spoken languages               |
| `token_per_minute`     | Int                | Default: 0          | Private show rate              |
| `private_show_price`   | Int                | Default: 0          | Private show entry price       |
| `tip_menu_enabled`     | Boolean            | Default: true       | Tip menu active                |
| `follower_count`       | Int                | Default: 0          | Denormalized follower count    |
| `subscriber_count`     | Int                | Default: 0          | Denormalized subscriber count  |
| `total_streams`        | Int                | Default: 0          | Lifetime stream count          |
| `total_stream_minutes` | Int                | Default: 0          | Lifetime streaming minutes     |
| `total_earnings`       | Int                | Default: 0          | Lifetime earnings (tokens)     |
| `pending_payout`       | Int                | Default: 0          | Unpaid earnings                |
| `last_payout_at`       | DateTime           | Nullable            | Last payout date               |
| `created_at`           | DateTime           | Auto-generated      | Record creation time           |
| `updated_at`           | DateTime           | Auto-updated        | Last update time               |

**Indexes:** `status`, `verification_status`, `is_online`, `is_streaming`,
`categories`

**Relations:** Stream, TipMenuItem, Device, DeviceGroup, SubscriptionTier,
Payout, Tip (received), Subscription (received), Follow (followed), ViewerBlock,
StreamSchedule, StreamGoal, Recording

### Viewer

Users who watch streams and interact with broadcasters.

**Table:** `viewers`

| Column                  | Type         | Constraints        | Description                                                                                                                              |
| ----------------------- | ------------ | ------------------ | ---------------------------------------------------------------------------------------------------------------------------------------- |
| `id`                    | UUID         | PK, auto-generated | Viewer identifier                                                                                                                        |
| `user_id`               | String       | Unique             | External user system reference                                                                                                           |
| `display_name`          | String       | Required           | Public display name                                                                                                                      |
| `avatar_url`            | String       | Nullable           | Profile image URL                                                                                                                        |
| `age_verified`          | Boolean      | Default: false     | Age verification passed                                                                                                                  |
| `age_verified_at`       | DateTime     | Nullable           | When age was verified                                                                                                                    |
| `status`                | ViewerStatus | Default: ACTIVE    | Account status                                                                                                                           |
| `is_banned`             | Boolean      | Default: false     | Ban flag                                                                                                                                 |
| `ban_reason`            | String       | Nullable           | Reason for ban                                                                                                                           |
| `banned_until`          | DateTime     | Nullable           | Temporary ban expiry                                                                                                                     |
| `token_balance`         | Int          | Default: 0         | Current token balance                                                                                                                    |
| `lifetime_purchased`    | Int          | Default: 0         | Total tokens ever purchased                                                                                                              |
| `lifetime_spent`        | Int          | Default: 0         | Total tokens ever spent                                                                                                                  |
| `preferred_categories`  | String[]     | Default: []        | Content preference categories                                                                                                            |
| `show_explicit`         | Boolean      | Default: false     | Opt-in for `sensual` tier content (music-video cap). Column name retained for back-compat; the platform does not carry explicit content. |
| `notifications_enabled` | Boolean      | Default: true      | Push notification preference                                                                                                             |
| `total_watch_time`      | Int          | Default: 0         | Lifetime watch time (seconds)                                                                                                            |
| `total_tipped`          | Int          | Default: 0         | Lifetime tips sent (tokens)                                                                                                              |
| `created_at`            | DateTime     | Auto-generated     | Record creation time                                                                                                                     |
| `updated_at`            | DateTime     | Auto-updated       | Last update time                                                                                                                         |

**Indexes:** `status`, `token_balance`

**Relations:** Tip (sent), Subscription (subscribed), Follow (following),
TokenPurchase, PrivateShowRequest, ViewSession, DeviceControl, ViewerBlock
(blocked by), ChatMessage

---

## Stream Models

### Stream

Live streaming sessions.

**Table:** `streams`

| Column               | Type         | Constraints        | Description                                                                                                                                                                                                                                             |
| -------------------- | ------------ | ------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `id`                 | UUID         | PK, auto-generated | Stream identifier                                                                                                                                                                                                                                       |
| `broadcaster_id`     | UUID         | FK -> Broadcaster  | Stream owner                                                                                                                                                                                                                                            |
| `title`              | String       | Required           | Stream title                                                                                                                                                                                                                                            |
| `description`        | String       | Nullable           | Stream description                                                                                                                                                                                                                                      |
| `thumbnail_url`      | String       | Nullable           | Current thumbnail                                                                                                                                                                                                                                       |
| `preview_url`        | String       | Nullable           | Preview video URL                                                                                                                                                                                                                                       |
| `status`             | StreamStatus | Default: OFFLINE   | Stream lifecycle state                                                                                                                                                                                                                                  |
| `stream_type`        | StreamType   | Default: PUBLIC    | Access level                                                                                                                                                                                                                                            |
| `stream_key`         | String       | Unique             | RTMP ingest authentication key                                                                                                                                                                                                                          |
| `rtmp_url`           | String       | Nullable           | RTMP ingest URL                                                                                                                                                                                                                                         |
| `hls_url`            | String       | Nullable           | HLS playback URL                                                                                                                                                                                                                                        |
| `webrtc_url`         | String       | Nullable           | WebRTC signaling URL                                                                                                                                                                                                                                    |
| `is_explicit`        | Boolean      | Default: true      | True when the stream is at the `sensual` tier of the music-video cap (twerk-and-thong, lingerie, sensual choreography, strip-club partnership feeds). Column name retained for back-compat; over-cap content is never accepted regardless of this flag. |
| `categories`         | String[]     | Default: []        | Content categories                                                                                                                                                                                                                                      |
| `tags`               | String[]     | Default: []        | Searchable tags                                                                                                                                                                                                                                         |
| `age_restricted`     | Boolean      | Default: true      | Age gate required                                                                                                                                                                                                                                       |
| `current_viewers`    | Int          | Default: 0         | Real-time viewer count                                                                                                                                                                                                                                  |
| `peak_viewers`       | Int          | Default: 0         | Peak viewers this session                                                                                                                                                                                                                               |
| `total_viewers`      | Int          | Default: 0         | Total unique viewers                                                                                                                                                                                                                                    |
| `total_tips`         | Int          | Default: 0         | Tips received this stream                                                                                                                                                                                                                               |
| `total_tokens`       | Int          | Default: 0         | Tokens earned this stream                                                                                                                                                                                                                               |
| `started_at`         | DateTime     | Nullable           | Stream start time                                                                                                                                                                                                                                       |
| `ended_at`           | DateTime     | Nullable           | Stream end time                                                                                                                                                                                                                                         |
| `duration_seconds`   | Int          | Nullable           | Stream duration                                                                                                                                                                                                                                         |
| `devices_enabled`    | Boolean      | Default: true      | Interactive devices active                                                                                                                                                                                                                              |
| `device_sensitivity` | Int          | Default: 50        | Device sensitivity (0-100)                                                                                                                                                                                                                              |
| `created_at`         | DateTime     | Auto-generated     | Record creation time                                                                                                                                                                                                                                    |
| `updated_at`         | DateTime     | Auto-updated       | Last update time                                                                                                                                                                                                                                        |

**Indexes:** `broadcaster_id`, `status`, `stream_type`, `started_at`,
`categories`

**Relations:** Tip, PrivateShow, ViewSession, ChatMessage, StreamGoal, Recording

### PrivateShow

One-on-one or group private sessions within a stream.

**Table:** `private_shows`

| Column             | Type              | Constraints         | Description               |
| ------------------ | ----------------- | ------------------- | ------------------------- |
| `id`               | UUID              | PK                  | Private show identifier   |
| `stream_id`        | UUID              | FK -> Stream        | Parent stream             |
| `status`           | PrivateShowStatus | Default: PENDING    | Show lifecycle state      |
| `show_type`        | PrivateShowType   | Default: ONE_ON_ONE | Session type              |
| `price_per_minute` | Int               | Required            | Cost per minute in tokens |
| `minimum_minutes`  | Int               | Default: 1          | Minimum duration          |
| `started_at`       | DateTime          | Nullable            | Show start time           |
| `ended_at`         | DateTime          | Nullable            | Show end time             |
| `duration_seconds` | Int               | Nullable            | Show duration             |
| `total_cost`       | Int               | Default: 0          | Total tokens billed       |
| `created_at`       | DateTime          | Auto-generated      | Record creation time      |
| `updated_at`       | DateTime          | Auto-updated        | Last update time          |

**Indexes:** `stream_id`, `status`

### PrivateShowRequest

Viewer requests to join a private show.

**Table:** `private_show_requests`

| Column              | Type                     | Constraints       | Description        |
| ------------------- | ------------------------ | ----------------- | ------------------ |
| `id`                | UUID                     | PK                | Request identifier |
| `show_id`           | UUID                     | FK -> PrivateShow | Target show        |
| `viewer_id`         | UUID                     | FK -> Viewer      | Requesting viewer  |
| `status`            | PrivateShowRequestStatus | Default: PENDING  | Request state      |
| `requested_minutes` | Int                      | Required          | Requested duration |
| `message`           | String                   | Nullable          | Optional message   |
| `created_at`        | DateTime                 | Auto-generated    | Request time       |
| `responded_at`      | DateTime                 | Nullable          | Response time      |

**Unique constraint:** `(show_id, viewer_id)`

### PrivateShowParticipant

Viewers currently in a private show.

**Table:** `private_show_participants`

| Column             | Type     | Constraints       | Description            |
| ------------------ | -------- | ----------------- | ---------------------- |
| `id`               | UUID     | PK                | Participant identifier |
| `show_id`          | UUID     | FK -> PrivateShow | Show reference         |
| `viewer_id`        | UUID     | Required          | Viewer reference       |
| `joined_at`        | DateTime | Auto-generated    | Join time              |
| `left_at`          | DateTime | Nullable          | Leave time             |
| `duration_seconds` | Int      | Default: 0        | Time in show           |
| `tokens_spent`     | Int      | Default: 0        | Tokens spent           |

**Unique constraint:** `(show_id, viewer_id)`

### ViewSession

Viewer watching sessions for analytics.

**Table:** `view_sessions`

| Column             | Type     | Constraints    | Description        |
| ------------------ | -------- | -------------- | ------------------ |
| `id`               | UUID     | PK             | Session identifier |
| `viewer_id`        | UUID     | FK -> Viewer   | Watching viewer    |
| `stream_id`        | UUID     | FK -> Stream   | Watched stream     |
| `started_at`       | DateTime | Auto-generated | Session start      |
| `ended_at`         | DateTime | Nullable       | Session end        |
| `duration_seconds` | Int      | Default: 0     | Watch duration     |
| `ip_address`       | String   | Nullable       | Client IP          |
| `user_agent`       | String   | Nullable       | Client user agent  |
| `device_type`      | String   | Nullable       | Device type        |

**Indexes:** `viewer_id`, `stream_id`, `started_at`

### StreamSchedule

Scheduled stream times for advance notification.

**Table:** `stream_schedules`

| Column               | Type           | Constraints        | Description             |
| -------------------- | -------------- | ------------------ | ----------------------- |
| `id`                 | UUID           | PK                 | Schedule identifier     |
| `broadcaster_id`     | UUID           | FK -> Broadcaster  | Schedule owner          |
| `title`              | String         | Nullable           | Schedule title          |
| `scheduled_start`    | DateTime       | Required           | Start time              |
| `scheduled_end`      | DateTime       | Nullable           | End time                |
| `is_recurring`       | Boolean        | Default: false     | Repeating schedule      |
| `recurrence_rule`    | String         | Nullable           | iCal RRULE string       |
| `status`             | ScheduleStatus | Default: SCHEDULED | Schedule state          |
| `notifications_sent` | Boolean        | Default: false     | Notifications delivered |
| `created_at`         | DateTime       | Auto-generated     | Record creation time    |
| `updated_at`         | DateTime       | Auto-updated       | Last update time        |

**Indexes:** `broadcaster_id`, `scheduled_start`

### StreamGoal

Tip goals during streams.

**Table:** `stream_goals`

| Column           | Type       | Constraints         | Description          |
| ---------------- | ---------- | ------------------- | -------------------- |
| `id`             | UUID       | PK                  | Goal identifier      |
| `broadcaster_id` | UUID       | FK -> Broadcaster   | Goal owner           |
| `stream_id`      | UUID       | FK -> Stream (opt.) | Associated stream    |
| `title`          | String     | Required            | Goal title           |
| `description`    | String     | Nullable            | Goal description     |
| `target_amount`  | Int        | Required            | Token target         |
| `current_amount` | Int        | Default: 0          | Current progress     |
| `status`         | GoalStatus | Default: ACTIVE     | Goal state           |
| `completed_at`   | DateTime   | Nullable            | Completion time      |
| `created_at`     | DateTime   | Auto-generated      | Record creation time |
| `updated_at`     | DateTime   | Auto-updated        | Last update time     |

**Indexes:** `broadcaster_id`, `stream_id`

### Recording

Stream recordings for VOD.

**Table:** `recordings`

| Column             | Type     | Constraints         | Description              |
| ------------------ | -------- | ------------------- | ------------------------ |
| `id`               | UUID     | PK                  | Recording identifier     |
| `broadcaster_id`   | UUID     | FK -> Broadcaster   | Recording owner          |
| `stream_id`        | UUID     | FK -> Stream (opt.) | Source stream            |
| `title`            | String   | Required            | Recording title          |
| `description`      | String   | Nullable            | Recording description    |
| `thumbnail_url`    | String   | Nullable            | Thumbnail URL            |
| `video_url`        | String   | Required            | Video file URL           |
| `duration_seconds` | Int      | Required            | Recording duration       |
| `file_size`        | BigInt   | Required            | File size in bytes       |
| `resolution`       | String   | Nullable            | Video resolution         |
| `is_public`        | Boolean  | Default: false      | Public visibility        |
| `price`            | Int      | Default: 0          | Price in tokens (0=free) |
| `view_count`       | Int      | Default: 0          | Total views              |
| `purchase_count`   | Int      | Default: 0          | Total purchases          |
| `recorded_at`      | DateTime | Required            | Original recording date  |
| `created_at`       | DateTime | Auto-generated      | Record creation time     |

**Indexes:** `broadcaster_id`, `is_public`

---

## Transaction Models

### Tip

Token tips from viewers to broadcasters.

**Table:** `tips`

| Column                    | Type     | Constraints         | Description                    |
| ------------------------- | -------- | ------------------- | ------------------------------ |
| `id`                      | UUID     | PK                  | Tip identifier                 |
| `viewer_id`               | UUID     | FK -> Viewer        | Tip sender                     |
| `broadcaster_id`          | UUID     | FK -> Broadcaster   | Tip recipient                  |
| `stream_id`               | UUID     | FK -> Stream (opt.) | Stream where tip was sent      |
| `amount`                  | Int      | Required            | Tip amount in tokens           |
| `message`                 | String   | Nullable            | Tip message                    |
| `is_anonymous`            | Boolean  | Default: false      | Anonymous tip flag             |
| `tip_menu_item_id`        | UUID     | FK -> TipMenuItem   | Triggered menu item (optional) |
| `device_triggered`        | Boolean  | Default: false      | Tip triggers device action     |
| `device_duration_seconds` | Int      | Nullable            | Device activation duration     |
| `device_intensity`        | Int      | Nullable            | Device intensity (0-100)       |
| `platform_fee`            | Int      | Default: 0          | Platform cut (tokens)          |
| `broadcaster_share`       | Int      | Required            | Broadcaster earnings (tokens)  |
| `created_at`              | DateTime | Auto-generated      | Tip time                       |

**Indexes:** `viewer_id`, `broadcaster_id`, `stream_id`, `created_at`

### TipMenuItem

Predefined tip amounts with optional device actions.

**Table:** `tip_menu_items`

| Column                    | Type     | Constraints       | Description                 |
| ------------------------- | -------- | ----------------- | --------------------------- |
| `id`                      | UUID     | PK                | Item identifier             |
| `broadcaster_id`          | UUID     | FK -> Broadcaster | Menu owner                  |
| `label`                   | String   | Required          | Display label               |
| `description`             | String   | Nullable          | Item description            |
| `amount`                  | Int      | Required          | Token cost                  |
| `sort_order`              | Int      | Default: 0        | Display ordering            |
| `trigger_device`          | Boolean  | Default: false    | Triggers device on purchase |
| `device_duration_seconds` | Int      | Nullable          | Device activation duration  |
| `device_intensity`        | Int      | Nullable          | Device intensity (0-100)    |
| `device_pattern`          | String   | Nullable          | Named device pattern        |
| `is_active`               | Boolean  | Default: true     | Item visibility             |
| `created_at`              | DateTime | Auto-generated    | Record creation time        |
| `updated_at`              | DateTime | Auto-updated      | Last update time            |

**Indexes:** `broadcaster_id`, `is_active`

### TokenPurchase

Real money to token conversions.

**Table:** `token_purchases`

| Column                | Type          | Constraints      | Description                    |
| --------------------- | ------------- | ---------------- | ------------------------------ |
| `id`                  | UUID          | PK               | Purchase identifier            |
| `viewer_id`           | UUID          | FK -> Viewer     | Purchasing viewer              |
| `token_amount`        | Int           | Required         | Tokens purchased               |
| `price_usd_cents`     | Int           | Required         | Price in USD cents             |
| `currency`            | String        | Default: "USD"   | Payment currency               |
| `exchange_rate`       | Float         | Default: 1.0     | Currency exchange rate         |
| `payment_provider`    | String        | Required         | Provider (CCBill, Epoch, etc.) |
| `payment_method_type` | String        | Nullable         | Card, crypto, etc.             |
| `external_payment_id` | String        | Nullable         | Provider transaction ID        |
| `status`              | PaymentStatus | Default: PENDING | Transaction state              |
| `failure_reason`      | String        | Nullable         | Error description              |
| `created_at`          | DateTime      | Auto-generated   | Purchase initiated             |
| `completed_at`        | DateTime      | Nullable         | Purchase completed             |

**Indexes:** `viewer_id`, `status`, `created_at`

### Payout

Broadcaster earnings payouts.

**Table:** `payouts`

| Column               | Type         | Constraints       | Description                |
| -------------------- | ------------ | ----------------- | -------------------------- |
| `id`                 | UUID         | PK                | Payout identifier          |
| `broadcaster_id`     | UUID         | FK -> Broadcaster | Payout recipient           |
| `amount_cents`       | Int          | Required          | Payout amount in USD cents |
| `currency`           | String       | Default: "USD"    | Payout currency            |
| `payout_method`      | PayoutMethod | Required          | Bank, PayPal, crypto, etc. |
| `external_payout_id` | String       | Nullable          | Provider reference ID      |
| `payout_details`     | Json         | Nullable          | Method-specific details    |
| `status`             | PayoutStatus | Default: PENDING  | Payout state               |
| `failure_reason`     | String       | Nullable          | Error description          |
| `period_start`       | DateTime     | Required          | Earnings period start      |
| `period_end`         | DateTime     | Required          | Earnings period end        |
| `created_at`         | DateTime     | Auto-generated    | Payout initiated           |
| `processed_at`       | DateTime     | Nullable          | Processing started         |
| `completed_at`       | DateTime     | Nullable          | Payout completed           |

**Indexes:** `broadcaster_id`, `status`, `created_at`

### Subscription

Viewer subscriptions to broadcasters with tiered pricing.

**Table:** `subscriptions`

| Column                 | Type               | Constraints            | Description              |
| ---------------------- | ------------------ | ---------------------- | ------------------------ |
| `id`                   | UUID               | PK                     | Subscription identifier  |
| `viewer_id`            | UUID               | FK -> Viewer           | Subscribing viewer       |
| `broadcaster_id`       | UUID               | FK -> Broadcaster      | Subscribed broadcaster   |
| `tier_id`              | UUID               | FK -> SubscriptionTier | Subscription tier        |
| `status`               | SubscriptionStatus | Default: ACTIVE        | Subscription state       |
| `price`                | Int                | Required               | Price in tokens          |
| `current_period_start` | DateTime           | Required               | Billing period start     |
| `current_period_end`   | DateTime           | Required               | Billing period end       |
| `next_billing_date`    | DateTime           | Nullable               | Next billing date        |
| `auto_renew`           | Boolean            | Default: true          | Auto-renew enabled       |
| `cancelled_at`         | DateTime           | Nullable               | Cancellation date        |
| `cancellation_reason`  | String             | Nullable               | Cancellation reason      |
| `total_billed`         | Int                | Default: 0             | Lifetime billing total   |
| `billing_count`        | Int                | Default: 0             | Number of billing cycles |
| `created_at`           | DateTime           | Auto-generated         | Subscription created     |
| `updated_at`           | DateTime           | Auto-updated           | Last update time         |

**Unique constraint:** `(viewer_id, broadcaster_id)` **Indexes:** `viewer_id`,
`broadcaster_id`, `status`

### SubscriptionTier

Broadcaster-defined subscription tiers.

**Table:** `subscription_tiers`

| Column            | Type         | Constraints       | Description            |
| ----------------- | ------------ | ----------------- | ---------------------- |
| `id`              | UUID         | PK                | Tier identifier        |
| `broadcaster_id`  | UUID         | FK -> Broadcaster | Tier owner             |
| `name`            | String       | Required          | Tier name              |
| `description`     | String       | Nullable          | Tier description       |
| `price`           | Int          | Required          | Price in tokens        |
| `billing_cycle`   | BillingCycle | Default: MONTHLY  | Billing frequency      |
| `color`           | String       | Nullable          | Display color          |
| `icon`            | String       | Nullable          | Display icon           |
| `priority`        | Int          | Default: 0        | Display ordering       |
| `features`        | String[]     | Default: []       | Included features list |
| `is_active`       | Boolean      | Default: true     | Tier availability      |
| `is_public`       | Boolean      | Default: true     | Public visibility      |
| `max_subscribers` | Int          | Nullable          | Subscriber cap         |
| `created_at`      | DateTime     | Auto-generated    | Record creation time   |
| `updated_at`      | DateTime     | Auto-updated      | Last update time       |

**Unique constraint:** `(broadcaster_id, name)` **Indexes:** `broadcaster_id`

### Follow

Viewer follows broadcaster relationships.

**Table:** `follows`

| Column              | Type     | Constraints       | Description           |
| ------------------- | -------- | ----------------- | --------------------- |
| `id`                | UUID     | PK                | Follow identifier     |
| `viewer_id`         | UUID     | FK -> Viewer      | Following viewer      |
| `broadcaster_id`    | UUID     | FK -> Broadcaster | Followed broadcaster  |
| `notify_on_live`    | Boolean  | Default: true     | Notify when live      |
| `notify_on_content` | Boolean  | Default: false    | Notify on new content |
| `created_at`        | DateTime | Auto-generated    | Follow time           |

**Unique constraint:** `(viewer_id, broadcaster_id)` **Indexes:**
`broadcaster_id`

---

## Device Models

### Device

Interactive toy devices connected to broadcaster accounts.

**Table:** `devices`

| Column                 | Type                   | Constraints           | Description              |
| ---------------------- | ---------------------- | --------------------- | ------------------------ |
| `id`                   | UUID                   | PK                    | Device identifier        |
| `broadcaster_id`       | UUID                   | FK -> Broadcaster     | Device owner             |
| `name`                 | String                 | Required              | Device display name      |
| `device_type`          | DeviceType             | Required              | Hardware type            |
| `manufacturer`         | String                 | Nullable              | Device manufacturer      |
| `model`                | String                 | Nullable              | Device model             |
| `connection_type`      | DeviceConnectionType   | Default: BLUETOOTH    | Connection method        |
| `connection_status`    | DeviceConnectionStatus | Default: DISCONNECTED | Current connection state |
| `device_id`            | String                 | Nullable              | Hardware identifier      |
| `last_connected_at`    | DateTime               | Nullable              | Last connection time     |
| `is_active`            | Boolean                | Default: true         | Device enabled           |
| `allow_viewer_control` | Boolean                | Default: true         | Viewers can control      |
| `max_intensity`        | Int                    | Default: 100          | Maximum intensity cap    |
| `default_intensity`    | Int                    | Default: 50           | Default intensity        |
| `supported_patterns`   | String[]               | Default: []           | Available patterns       |
| `created_at`           | DateTime               | Auto-generated        | Record creation time     |
| `updated_at`           | DateTime               | Auto-updated          | Last update time         |

**Indexes:** `broadcaster_id`, `connection_status`

### DeviceGroup

Group multiple devices for synchronized control.

**Table:** `device_groups`

| Column                  | Type     | Constraints       | Description               |
| ----------------------- | -------- | ----------------- | ------------------------- |
| `id`                    | UUID     | PK                | Group identifier          |
| `broadcaster_id`        | UUID     | FK -> Broadcaster | Group owner               |
| `name`                  | String   | Required          | Group name                |
| `description`           | String   | Nullable          | Group description         |
| `synchronize_intensity` | Boolean  | Default: true     | Sync intensity across all |
| `master_device_id`      | String   | Nullable          | Master device reference   |
| `created_at`            | DateTime | Auto-generated    | Record creation time      |
| `updated_at`            | DateTime | Auto-updated      | Last update time          |

**Indexes:** `broadcaster_id`

### DeviceGroupMember

Association between devices and device groups.

**Table:** `device_group_members`

| Column                 | Type  | Constraints                 | Description              |
| ---------------------- | ----- | --------------------------- | ------------------------ |
| `id`                   | UUID  | PK                          | Membership identifier    |
| `group_id`             | UUID  | FK -> DeviceGroup (cascade) | Parent group             |
| `device_id`            | UUID  | FK -> Device (cascade)      | Member device            |
| `intensity_multiplier` | Float | Default: 1.0                | Intensity scaling factor |

**Unique constraint:** `(group_id, device_id)`

### DeviceControl

Viewer-initiated device control events.

**Table:** `device_controls`

| Column             | Type                | Constraints      | Description              |
| ------------------ | ------------------- | ---------------- | ------------------------ |
| `id`               | UUID                | PK               | Control event identifier |
| `device_id`        | UUID                | FK -> Device     | Target device            |
| `viewer_id`        | UUID                | FK -> Viewer     | Controlling viewer       |
| `control_type`     | DeviceControlType   | Required         | Command type             |
| `intensity`        | Int                 | Default: 50      | Intensity (0-100)        |
| `duration_seconds` | Int                 | Nullable         | Command duration         |
| `pattern`          | String              | Nullable         | Named pattern            |
| `source_type`      | DeviceControlSource | Required         | Trigger source           |
| `source_tip_id`    | String              | Nullable         | Associated tip ID        |
| `status`           | DeviceControlStatus | Default: PENDING | Execution state          |
| `executed_at`      | DateTime            | Nullable         | Execution time           |
| `created_at`       | DateTime            | Auto-generated   | Command creation time    |

**Indexes:** `device_id`, `viewer_id`, `created_at`

---

## Chat and Moderation Models

### ChatMessage

Stream chat messages.

**Table:** `chat_messages`

| Column           | Type            | Constraints    | Description             |
| ---------------- | --------------- | -------------- | ----------------------- |
| `id`             | UUID            | PK             | Message identifier      |
| `stream_id`      | UUID            | FK -> Stream   | Chat room stream        |
| `viewer_id`      | UUID            | FK -> Viewer   | Message author          |
| `content`        | String          | Required       | Message text            |
| `message_type`   | ChatMessageType | Default: TEXT  | Message category        |
| `is_highlighted` | Boolean         | Default: false | Highlighted message     |
| `tip_amount`     | Int             | Nullable       | Tip amount (if tip msg) |
| `is_deleted`     | Boolean         | Default: false | Soft delete flag        |
| `deleted_at`     | DateTime        | Nullable       | Deletion time           |
| `deleted_by`     | String          | Nullable       | Moderator who deleted   |
| `created_at`     | DateTime        | Auto-generated | Message time            |

**Indexes:** `stream_id`, `viewer_id`, `created_at`

### ViewerBlock

Broadcaster blocks viewer from interacting.

**Table:** `viewer_blocks`

| Column           | Type     | Constraints       | Description            |
| ---------------- | -------- | ----------------- | ---------------------- |
| `id`             | UUID     | PK                | Block identifier       |
| `broadcaster_id` | UUID     | FK -> Broadcaster | Blocking broadcaster   |
| `viewer_id`      | UUID     | FK -> Viewer      | Blocked viewer         |
| `reason`         | String   | Nullable          | Block reason           |
| `is_permanent`   | Boolean  | Default: false    | Permanent block flag   |
| `expires_at`     | DateTime | Nullable          | Temporary block expiry |
| `created_at`     | DateTime | Auto-generated    | Block creation time    |

**Unique constraint:** `(broadcaster_id, viewer_id)` **Indexes:** `viewer_id`

---

## Enums

### User Enums

| Enum                 | Values                                      |
| -------------------- | ------------------------------------------- |
| `BroadcasterStatus`  | PENDING, ACTIVE, SUSPENDED, BANNED, DELETED |
| `VerificationStatus` | UNVERIFIED, PENDING, VERIFIED, REJECTED     |
| `ViewerStatus`       | ACTIVE, SUSPENDED, BANNED, DELETED          |

### Stream Enums

| Enum                       | Values                                         |
| -------------------------- | ---------------------------------------------- |
| `StreamStatus`             | OFFLINE, STARTING, LIVE, PAUSED, ENDING, ENDED |
| `StreamType`               | PUBLIC, PRIVATE, TICKET, GROUP, FAN_CLUB       |
| `PrivateShowStatus`        | PENDING, ACTIVE, ENDED, CANCELLED              |
| `PrivateShowType`          | ONE_ON_ONE, GROUP, SPY                         |
| `PrivateShowRequestStatus` | PENDING, ACCEPTED, REJECTED, EXPIRED           |
| `ScheduleStatus`           | SCHEDULED, LIVE, COMPLETED, CANCELLED          |
| `GoalStatus`               | ACTIVE, COMPLETED, EXPIRED, CANCELLED          |

### Payment Enums

| Enum                 | Values                                                      |
| -------------------- | ----------------------------------------------------------- |
| `PaymentStatus`      | PENDING, PROCESSING, COMPLETED, FAILED, REFUNDED, CANCELLED |
| `PayoutStatus`       | PENDING, PROCESSING, COMPLETED, FAILED, CANCELLED           |
| `PayoutMethod`       | BANK_TRANSFER, PAYPAL, CRYPTO, CHECK                        |
| `SubscriptionStatus` | ACTIVE, PAUSED, CANCELLED, EXPIRED, PAST_DUE                |
| `BillingCycle`       | MONTHLY, QUARTERLY, YEARLY                                  |

### Device Enums

| Enum                     | Values                                                          |
| ------------------------ | --------------------------------------------------------------- |
| `DeviceType`             | VIBRATOR, ROTATOR, CONSTRICTION, STROKER, MULTI_FUNCTION, OTHER |
| `DeviceConnectionType`   | BLUETOOTH, WIFI, USB, NETWORK                                   |
| `DeviceConnectionStatus` | DISCONNECTED, CONNECTING, CONNECTED, ERROR                      |
| `DeviceControlType`      | VIBRATE, ROTATE, CONSTRICT, PATTERN, STOP                       |
| `DeviceControlSource`    | TIP, TIP_MENU, SUBSCRIPTION, MANUAL, GOAL                       |
| `DeviceControlStatus`    | PENDING, EXECUTING, COMPLETED, FAILED                           |

### Chat Enums

| Enum              | Values                            |
| ----------------- | --------------------------------- |
| `ChatMessageType` | TEXT, TIP, SYSTEM, EMOTE, STICKER |

---

## Indexes

The schema uses extensive indexing for query performance:

- **Foreign key indexes** on all relationship columns (`broadcaster_id`,
  `viewer_id`, `stream_id`, `show_id`, etc.)
- **Status indexes** for filtering by lifecycle state
- **Temporal indexes** on `created_at`, `started_at`, `scheduled_start` for
  time-range queries
- **Array indexes** on `categories` for category-based discovery
- **Unique constraints** on natural keys (`viewer_id + broadcaster_id` for
  follows and subscriptions, `stream_key` for streams)

---

## Migration Workflow

### Development

```bash
cd libs/aphrodite/database

# Generate Prisma client after schema changes
npx prisma generate

# Push schema changes directly (no migration history -- development only)
npx prisma db push

# Create a migration (recommended for production-track changes)
npx prisma migrate dev --name describe_your_change

# View database in browser
npx prisma studio
```

### Production

```bash
cd libs/aphrodite/database

# Apply pending migrations
npx prisma migrate deploy

# Check migration status
npx prisma migrate status
```

### Resetting the Database

```bash
cd libs/aphrodite/database

# Reset and re-apply all migrations (destructive -- development only)
npx prisma migrate reset

# Or drop and recreate via Docker
docker compose -f docker/docker-compose.dev.yml down -v
docker compose -f docker/docker-compose.dev.yml up -d
```

### Schema Change Checklist

1. Edit `libs/aphrodite/database/prisma/schema.prisma`
2. Run `npx prisma generate` to regenerate the client
3. Run `npx prisma migrate dev --name your_change` to create a migration
4. Update any affected Zod schemas in `libs/aphrodite/database/src/`
5. Update this documentation if models or enums change
6. Run tests: `pnpm nx test @aphrodite/database`
