Domain Models
Field-level notes on the models you will actually touch. The schema itself (packages/database/prisma/schema.prisma) is the source of truth — this page explains the parts that are not obvious from reading it.
Identity
User
The largest model in the schema, and the hub of nearly every relation.
| Field | Notes |
|---|---|
email | Unique, required |
username | Unique, nullable — set during onboarding, not at registration |
display_name, first_name, last_name, bio, birthdate, gender | Profile; snake_case on this model only |
password | bcrypt hash |
role | "user" by default — coarse platform role |
userType | "user" or "playpal" — this is the creator flag |
onboarded | Gates the onboarding flow |
tokenVersion | Incremented to revoke every issued JWT. See Authentication |
coinFrozenAt, coinFrozenReason | Set by an admin coin freeze: no tipping, store spending or cash-out until cleared |
monthlyPrice | Creator subscription price in cents, default 499 ($4.99) |
revenueCatUserId | Unique link to RevenueCat |
subscriptionTier | null | "pro" | "elite" — platform tier, not creator subscriptions |
biometricConsentAt, biometricConsentVersion, faceEnrolledAt | Face enrollment consent record. The selfie itself is an encrypted VerificationAsset. See Biometrics |
stripeAccountId | Stripe Connect account, unique |
payoutsEnabled | Gates inclusion in a payout run |
isLive, activeLiveStreamId | Denormalised live state |
playcoin_balance | Legacy, not written. Coin balances come from the coin ledger |
role and userType are two separate concepts. userType is the one that determines whether someone is a creator.
AuthSession
One row per device sign-in: device name, IP, user agent, isActive. Powers the "active sessions" and "login history" screens. For the mobile app it is also the refresh session: refreshTokenHash, previousTokenHash + rotatedAt (2-minute rotation grace) and a sliding expiresAt. Refresh requires isActive; access tokens are revoked by tokenVersion. See Authentication.
Badge / UserBadge
Badges differentiate user subtypes. The PlayPal badge marks creators; other badges are earned. A user picks one to display and the rest sit in an earned tab.
CreatorProfile
One per user (userId is unique), created on the first creator-onboarding call. It holds no PII: legal name, DOB, SSN, address and bank numbers live only in Stripe. See Creator Onboarding & KYC.
| Field | Notes |
|---|---|
verificationStatus | NOT_STARTED → IN_PROGRESS → SUBMITTED → APPROVED | REJECTED; REVOKED when an admin revokes a PlayPal with forfeiture |
idDocumentType | license | state_id | passport |
submittedAt, reviewedAt, reviewedById, rejectionReason | Admin review |
tosAcceptedAt | When the Stripe Connected Account Agreement was accepted (Stripe holds the IP and user agent) |
bankLast4, bankName | Display only, as returned by Stripe |
stripeRequirementsDue | Stripe's requirement descriptions, never values |
APPROVED is what makes a user a creator: approval sets User.userType = "playpal" and the PlayPal badge.
VerificationAsset
One row per uploaded ID side, plus at most one reference selfie per user. The image itself is AES-256-GCM ciphertext in the private KYC bucket.
| Field | Notes |
|---|---|
type, side | id_document (front | back) or selfie (front). Creator review only ever touches id_document |
storageKey | kyc/<uuid>: opaque, no user id, no extension |
iv, authTag | AES-GCM parameters for the image |
wrappedKey, keyVersion | Per-image data key, wrapped by KYC_ENCRYPTION_KEY version keyVersion |
Rows are deleted, along with the bucket object, on retake, on rejection and on account deletion.
VerificationAccessLog
Append-only audit of admin views of ID documents and of approve/reject decisions: adminId, subjectId, assetId, action, ip. It has no foreign keys on purpose, so the log outlives the assets and users it refers to.
Block
@@unique([blockerId, blockedId]), cascading on both sides. Feed, profile, search, discover, trending, comment, notification, and DM queries filter against it via getBlockedUserIds, which returns the union of both directions — see Moderation.
Content
Post
| Field | Notes |
|---|---|
body | @db.Text |
type | "text" | "clip" | "photos" — also read by ogun to pick image variants |
visibility | "public" | "private" | "followers", nullable |
commentable | Author can disable comments |
status | "pending" until media finishes processing, then "ready" |
moderationStatus | Nullable; no moderation pipeline is wired up yet |
gameId | Optional game tag |
Media and Variant
Media is the original upload; Variant rows are the generated sizes.
Media field | Notes |
|---|---|
type | "image" | "video" |
originalUrl, storageKey | Location in Spaces |
status | "pending" → "ready" | "failed" |
errorMessage | Populated on failure — check here first when processing breaks |
muxAssetId, muxPlaybackId | Video only |
order | Position in the composer (0–4); indexed with postId |
width, height, aspectRatio | Display size after EXIF / video rotation |
placeholder | Base64 thumbhash |
focalX, focalY | 0..1 crop focal point |
rotation | Rotation the pipeline applied (diagnostic) |
posterUrl | Video poster frame |
durationMs | Milliseconds (rows from before the v3 fix may hold seconds) |
altText | Optional accessibility description |
metaVersion | 3 once processed by the v3 pipeline |
Variant is unique on (mediaId, type, format). v3 types are w320, w640, w1080, w1440, full (WebP), og (JPEG) and poster-w320/640/1080 (WebP, video); thumb, medium, story, cover are legacy. See Media Pipeline.
LinkPreview
A link card, unfurled server-side and referenced by Post.linkPreviewId (set null if the preview is deleted). Rows are immutable snapshots: a refresh after the 24h cache TTL creates a new row, so posts keep the card they were published with.
| Field | Notes |
|---|---|
url, urlHash | Normalized URL and its sha256 (the cache key) |
finalUrl, canonicalUrl | Where the fetch ended after redirects; the page's declared canonical |
displayDomain | Destination domain shown on the card (punycode where deceptive) |
title, description, siteName | Sanitized, capped at 200 / 300 / 80 |
cardType, cardHint | large | small | text; the page's twitter:card |
status | pending → ready | failed; only ready renders |
imageSourceUrl | Third-party image URL. Server-side only, never selected for clients |
imageStatus, imageWidth/Height, imagePlaceholder, imageFocalX/Y, imageVariants | Our re-encoded copy (WebP w320/w640/w1080) |
Clients get linkPreviewPublicSelect from @playpals/db. See Link Previews.
Comment
parentId makes a comment a reply. Threads are one level deep: the API always stores a reply against its top-level comment. Deleting a comment cascades to its replies and mentions. Reaction and Report rows do not cascade, so deleteComment clears reactions and detaches reports itself.
Reaction
Polymorphic — either postId or commentId is set. kind is the reaction type.
@@unique([userId, postId, commentId, kind], name: "unique_reaction")This does not stop duplicates. One of postId/commentId is always NULL, and Postgres treats NULLs as distinct in a unique index. "One reaction per user per target" is enforced in services/reaction.ts instead.
PostImpression
@@unique([userId, postId]) means one impression per user per post, ever. viewDurationMs and videoWatchMs accumulate engagement depth rather than adding rows.
Mention
Polymorphic across postId and commentId, cascading from both, with separate relations for the mentioned user and the mentioner.
Messaging
Two parallel systems that do not share models.
Direct messages
Conversation ──< ConversationUser ──> User
│
├──< ConversationMessage
└──< ConversationReadState| Model | Notes |
|---|---|
Conversation | dmKey is a unique deterministic key derived from the participant pair, so opening a DM is an upsert |
ConversationMessage | type is "text" or "shared_post"; sharedPostId links the shared post. Soft-deleted via deletedAt |
ConversationReadState | lastReadAt + lastReadMsgId per user per conversation — drives unread counts |
Channels
Channel ──< ChannelRoom ──< ChannelMessage
│ ├──< ChannelRoomParticipant (live presence)
│ └──< ChannelRoomReadState
└──< ChannelMember| Model | Notes |
|---|---|
Channel | Owned by a creator (userId) |
ChannelRoom | type: chat | voice | video | e-date. visibility: public | members | vip | private — enforced by canAccessRoom |
ChannelRoomParticipant | Live state: isMuted, isCameraOff, isDeafend, isSpeaking. Ephemeral in nature but stored |
ChannelMember | Membership with a role |
ChannelRoom.visibility is the channel paywall. See Livestreaming & Voice.
Sessions
Session
A paid 1-on-1 session or e-date (kind) between a PlayPal (playpalId) and a booker (purchaserId).
| Field | Notes |
|---|---|
startDate, endDate, duration | Duration in minutes, stored as well as derivable — keep them consistent |
blockedUntil | endDate plus the PlayPal's buffer: what the exclusion constraints compare |
status | SessionStatus: HELD, CONFIRMED, IN_PROGRESS, COMPLETED, CANCELLED, NO_SHOW_USER, NO_SHOW_PLAYPAL, EXPIRED |
holdExpiresAt | While HELD: when the reservation lapses |
productId, storePurchaseId, creditId | What paid for it |
cancelledById, outcome | Who cancelled; how it ended (drives credits and earnings) |
channelRoomId | The private room, created when it's confirmed |
Exclusion constraints Session_no_playpal_overlap and Session_no_purchaser_overlap (raw SQL in 20261101120000_session_booking; Prisma doesn't model them) make overlapping holding sessions impossible. See Sessions and e-dates.
private room visibility grants access only to these two users, and only from 5 minutes before the start until the end, while the session is CONFIRMED or IN_PROGRESS.
SessionEvent
Append-only audit of a session (sessionId, actorId?, type, metadata, createdAt). A trigger (session_event_append_only, migration 20261103120000_session_events) refuses updates; rows go when their session does.
Availability
A PlayPal's hours per kind (ONE_ON_ONE | EDATE):
| Shape | Fields used |
|---|---|
| Recurring weekly hours | dayOfWeek (0 = Monday), startTime, endTime |
| A date's own hours (override) | date, startTime, endTime, blocked: false |
| A blocked calendar date | date, blocked: true |
startTime / endTime are wall-clock strings ("09:00", end may be "24:00") in the PlayPal's CreatorBookingSettings.timezone (or User.timezone, the device's). Alongside: CreatorSessionSettings (per kind: enabled, buffer, minimum notice, booking horizon) and CreatorOffering (per kind and length; productId prices it once booking is paid).
Commerce
Coin ledger
CoinAccount, CoinTransaction and CoinEntry hold every Play Coin balance and movement. Triggers make them append-only and keep them balanced; see Monetization. Write to them only through postCoinTransaction.
| Model | Fields worth knowing |
|---|---|
CoinAccount | userId (null for system accounts), kind (CoinAccountKind), balance (trigger-maintained, user accounts only) |
CoinTransaction | type, idempotencyKey (unique), requestHash, actorId, reason, reversesId, metadata |
CoinEntry | amount (signed, non-zero), balanceAfter |
CoinPackage | productId (PK), coins (bonus included), bonusCoins, label, badge, sortOrder, offered. Server-side coin config; no prices |
CoinLot | Coins that entered PURCHASED together (one purchase or carry-over) and how many are remaining. Spends draw lots oldest first |
Tip | senderId, recipientId, targetType, postId/liveStreamId (no FK), coins, message, messageHiddenAt, recipientBucket, coinTransactionId. Never deleted; only the hidden-message fields change, and reversedAt/reversalTransactionId are set once if an admin reverses it |
TipFunding | What paid for a tip: sender bucket, coins, and the lotId for purchased coins. Append-only |
EarningLot | Pending earnings that mature together: from one tip (tipId) or an admin credit / tip reversal (sourceTransactionId; a CHECK requires one). coins, remaining, maturesAt, status (pending | matured | reversed) |
CoinCashout | A PlayPal's cash-out request: coins, cents at centsPer1000, status (requested | scheduled | paid | cancelled | returned), payoutId, and the request, close and return transaction ids |
ReconciliationRun | One run of the ledger checks: source (scheduled | manual), status (ok | drift | error), checks (JSON [{name, ok, detail}]) |
FraudFlag | kind, status (open | cleared | actioned), senderId, recipientId, tipId, details |
CoinRefund | One refund per store transaction: status (refunded | reversed), coins, and where they came back from (fromPurchase, fromBuyerBalance, fromRecipients, toDebt), the clawback and reversal transaction ids. Never deleted |
CoinRefundReversal | Pending earnings taken from one tip's recipient for a refund. Append-only |
CoinPurchase | One verified store purchase. Unique (store, storeTransactionId); coins, quantity, environment, price in micros, currency, commission/tax percentages, verifiedVia, coinTransactionId |
Wallet and Ledger
Wallet holds the legacy coinBalance (no longer written) and balance in cents. Ledger is the append-only record of anansi's USD movements.
Ledger fields worth knowing:
| Field | Purpose |
|---|---|
type | LedgerType enum — the one place status is genuinely enforced |
coinDelta, usdDelta | Signed changes |
revenueCatEventId, stripeEventId, stripeChargeId, stripeInvoiceId, stripeTransferId, stripePayoutId | External references for reconciliation |
idempotencyKey | Unique — the guard against double-posting |
memo | Human-readable note |
Never update a ledger row. Corrections are new ADJUSTMENT entries.
Payout
One row per creator per period: amount (cents), status (pending → completed | failed), stripeTransferId, stripePayoutId. Written by anansi.
Product, Order, OrderItem, Inventory
The in-app store — avatars, frames, banners, profile backgrounds. Inventory records what a user owns; UserAsset records what they have equipped.
Subscriptions
Two distinct models, easy to confuse:
| Model | Meaning |
|---|---|
Subscription | The user's platform tier (pro, elite) |
CreatorSubscription | A fan's subscription to a specific creator |
CreatorSubscription is the one row access checks read (canAccessRoom for vip rooms): status = "active" and expiresAt in the future. source says where it comes from:
"pass": 30-day VIP passes (VipPass);expiresAtis the end of the fan's last pass."revenuecat_legacy": an old auto-renewingvip_sub_*subscription, kept until the store expires it.
The Stripe-era fields (stripeSubscriptionId, currentPeriodEnd) are nullable and unused. Payouts no longer read this table at all: earnings come from CreatorEarning.
@@unique([fanId, creatorId]) — one row per pair.
PlayPal store purchases
| Model | Meaning |
|---|---|
PriceTier | A store product a PlayPal can price at: kind (VIP_PASS | SESSION | EDATE), USD priceCents, durationDays, offered |
CheckoutIntent | What a buyer is about to pay for (buyer, PlayPal, product), made before the store sheet opens |
StorePurchase | One verified store transaction; @@unique([store, storeTransactionId]), refundedAt |
VipPass | One pass: startsAt/expiresAt, chained per fan and PlayPal; revokedAt on refund |
PurchaseCredit | A paid-for product not yet attached to anything, used by the buyer's next checkout of it |
CreatorEarning | A PlayPal's USD from one purchase, net of tax, store commission and platform fee. @@unique([source, sourceId]); status ESCROW → PENDING → AVAILABLE → SCHEDULED → PAID, or REVERSED |
Post.visibility "vip" marks a post for the author's subscribers; Media.isPrivate says its files are private storage served through signed URLs (set at upload for a VIP post, and flipped by ogun's media-visibility job when the post's visibility changes).
AgeVerification (one per user) records the e-date age check: PENDING → APPROVED or REJECTED, with reviewer and reason. The license photo itself is a VerificationAsset of type age_document, deleted at the decision. See Age Verification.
CreatorEarning has CHECKs that its parts add up to grossCents and that only SCHEDULED/PAID rows carry a payoutId. Payout splits its amount into subscriptionCents, sessionCents and coinCents. See Monetization.
RevenueCatEvent
model RevenueCatEvent {
eventId String @id
processedAt DateTime @default(now())
type String?
payload Json?
}The primary key skips webhook events already handled. type and payload keep the whole event for audit and reprocessing (coin refunds are stored here until clawbacks are handled); rows from before they were added have them null.
Discovery
| Model | Notes |
|---|---|
Game | Synced from IGDB. igdbId unique, igdbCoverImageId used to skip re-uploading unchanged covers. genres and platforms are String[] |
UserGame | A user's favourite games, chosen during onboarding |
Trending | Materialised trending entries |
SearchLog | Every query, indexed on query and createdAt — powers trending searches |
Sponsor | Sponsored placements |
Link | Creator's external links (merch, socials) |
Notifications
Notification, NotificationPreference, and PushNotificationToken. Covered in Notifications.
Inspecting the schema
cd packages/database
pnpm exec prisma studio # browse data
pnpm exec prisma validate # check the schema
pnpm exec prisma format # canonical formatting