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.subscriptionTier | Subscription.productId | Fast entitlement checks |
Legacy coin columns
User.playcoin_balance and Wallet.coinBalance are no longer written. Coin balances come only from the coin ledger. Both columns are due to be dropped.
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