Database Overview
One PostgreSQL database, accessed exclusively through Prisma via the shared @playpals/db package. Every service reads and writes the same schema.
At a glance
| Engine | PostgreSQL 15 (managed in production, postgres:15.4 locally) |
| ORM | Prisma 7 with the @prisma/adapter-pg driver adapter |
| Schema | packages/database/prisma/schema.prisma |
| Generated client | packages/database/generated/prisma (git-ignored) |
| Models | 51 |
| Enums | 2 — LedgerType, NotificationType |
| Migrations | 21 |
| ID strategy | cuid() on every model |
Documentation elsewhere says MySQL
CLAUDE.md and parts of docs/infrastructure.md describe the database as MySQL. That is stale — the schema declares provider = "postgresql" and both the local Compose stack and the Terraform configuration provision PostgreSQL.
Domain map
Models group into eight domains:
| Domain | Models |
|---|---|
| Identity | User, AuthSession, Block, UserAsset, Badge, UserBadge, CreatorProfile, VerificationAsset |
| Content | Post, Media, Variant, Comment, Reaction, Mention, PostImpression, Report |
| Messaging | Conversation, ConversationUser, ConversationMessage, ConversationReadState, Channel, ChannelMember, ChannelRoom, ChannelMessage, ChannelRoomParticipant, ChannelRoomReadState |
| Live | LiveStream, LiveStreamMessage |
| Sessions | Session, Availability |
| Commerce | Product, FeaturedProduct, Order, OrderItem, Inventory, Wallet, Ledger, Payout |
| Subscriptions | Subscription, CreatorSubscription, RevenueCatEvent |
| Discovery | Game, UserGame, Trending, SearchLog, Sponsor, Link, Notification, NotificationPreference, PushNotificationToken |
Each is described in Domain Models.
Conventions
IDs — String @id @default(cuid()) everywhere. Collision-resistant, sortable by creation, and safe to expose in URLs.
Timestamps — createdAt DateTime @default(now()) and updatedAt DateTime @updatedAt on nearly every model.
Money is integer cents. monthlyPrice, Wallet.balance, Ledger.usdDelta, Payout.amount, Product.price — all cents, never floats. Play Coins are whole units in coinBalance / coinDelta. Use the helpers in apps/api/src/lib/money.ts; never introduce a float.
Enums are used sparingly. Only LedgerType and NotificationType are real Postgres enums. Everything else — Post.status, Media.status, Session.status, ChannelRoom.visibility, User.userType — is a String with the allowed values in a trailing comment.
String status columns are unenforced
status String @default("scheduled") // scheduled | active | completed | cancelledNothing stops a typo from being written. Converting these to enums would catch a class of bug at the database level; until then, treat the comment as the contract and centralise the allowed values in code.
Naming is inconsistent on User. That model uses snake_case (display_name, first_name, birthdate) while every other model uses camelCase. Historical; follow the surrounding model rather than normalising it in an unrelated change.
Indexing
Well-indexed for the read patterns that matter:
| Pattern | Index |
|---|---|
| Feed by author | Post @@index([userId]) |
| Feed by recency | Post @@index([createdAt]) |
| Comment threads | Comment @@index([postId]) |
| Follower/following lists | Follow @@index([followerId]), @@index([followingId]) |
| Notification inbox | Notification @@index([recipientId, createdAt]) |
| Unread badge | Notification @@index([recipientId, readAt]) |
| Creator's active subscribers | CreatorSubscription @@index([creatorId, status]) |
| A user's sessions by status | Session @@index([playpalId, status]), @@index([purchaserId, status]) |
| Ledger by wallet and time | Ledger @@index([walletId, createdAt]) |
Uniqueness constraints worth knowing
| Constraint | Prevents |
|---|---|
Reaction @@unique([userId, postId, commentId, kind]) | Double-liking |
Follow @@unique([followerId, followingId]) | Duplicate follows |
PostImpression @@unique([userId, postId]) | Inflated view counts |
CreatorSubscription @@unique([fanId, creatorId]) | Two subscriptions to the same creator |
Conversation.dmKey @unique | Duplicate DM threads between the same pair |
Ledger.idempotencyKey @unique | Double-posting money |
RevenueCatEvent.eventId (primary key) | Replaying a purchase webhook |
ChannelMember @@unique([userId, channelId]) | Duplicate memberships |
Block @@unique([blockerId, blockedId]) | Duplicate blocks |
Conversation.dmKey deserves a note: it is a deterministic key built from the two participant ids, so "open a DM with this person" is an upsert rather than a search.
Denormalised columns
Deliberate duplication for read performance:
| Column | Duplicates | Why |
|---|---|---|
User.isLive | LiveStream.status | Live badge in feeds without a join |
User.activeLiveStreamId | LiveStream.id | Jump straight to the active stream |
User.playcoin_balance | Wallet.coinBalance | Two sources of truth for the same number |
User.subscriptionTier | Subscription.productId | Fast entitlement checks |
playcoin_balance and Wallet.coinBalance can disagree
The same value lives on both User and Wallet, with nothing keeping them in sync. Whichever one a given code path reads determines what the user sees. Wallet is the one backed by the Ledger and should be treated as authoritative; User.playcoin_balance should be removed.
Cascade behaviour
Mostly default (restrict). Explicit cascades exist where an orphan makes no sense:
recipient User @relation(..., onDelete: Cascade) // Notification
blocker User @relation(..., onDelete: Cascade) // Block
actor User @relation(..., onDelete: SetNull) // Notification — keep the row, drop the actorBecause most relations restrict, deleting a User outright will fail on foreign keys. Account deletion (DELETE /api/v1/user/account) has to unwind relations explicitly.
Next
- Domain Models — field-level detail per domain
- Migrations & Seeding — the workflow