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#
- ER Diagram
- User Models
- Stream Models
- Transaction Models
- Device Models
- Chat and Moderation Models
- Enums
- Indexes
- 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_startfor time-range queries - Array indexes on
categoriesfor category-based discovery - Unique constraints on natural keys (
viewer_id + broadcaster_idfor follows and subscriptions,stream_keyfor streams)
Migration Workflow#
Development#
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#
cd libs/aphrodite/database
# Apply pending migrations
npx prisma migrate deploy
# Check migration status
npx prisma migrate status
Resetting the Database#
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#
- Edit
libs/aphrodite/database/prisma/schema.prisma - Run
npx prisma generateto regenerate the client - Run
npx prisma migrate dev --name your_changeto create a migration - Update any affected Zod schemas in
libs/aphrodite/database/src/ - Update this documentation if models or enums change
- Run tests:
pnpm nx test @aphrodite/database