Eve library

Aphrodite Domain - Database Schema

Guide

Revision 1

On this page

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
  2. User Models
  3. Stream Models
  4. Transaction Models
  5. Device Models
  6. Chat and Moderation Models
  7. Enums
  8. Indexes
  9. Migration Workflow

ER Diagram#

text
┌─────────────────────────────────────────────────────────────────────────┐
│                         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