Database

Overview

PostgreSQL is the single source of truth for all platform data. The schema is organized into four domains:

Domain	Tables	Purpose
Catalog	Game, Category, Author, Build, GameCategory	Game metadata, relationships, builds
Analytics	Event, DailyMetric, Session	Raw events, aggregated metrics, sessions
Discovery	ExplorationQueue, GameRank	Exploration rotation, computed rankings
Feedback	GameFeedback	Likes and dislikes
Admin	AdminUser	Dashboard authentication

---

Entity Relationship Diagram

┌──────────────┐     ┌──────────────────┐     ┌──────────────┐
│    Author    │     │   GameCategory   │     │   Category   │
├──────────────┤     ├──────────────────┤     ├──────────────┤
│ id (uuid)    │◄──┐ │ gameId           │┌──► │ id (uuid)    │
│ name         │   │ │ categoryId       ││    │ name         │
│ slug         │   │ └──────────────────┘│    │ slug         │
│ createdAt    │   │                     │    │ iconUrl      │
└──────────────┘   │ ┌──────────────────┐│    │ sortOrder    │
                   │ │      Game        ││    └──────────────┘
                   │ ├──────────────────┤│
                   └─┤ id (uuid)        ├┘
                     │ title            │
                     │ slug (unique)    │──────────────────────┐
                     │ description      │                      │
                     │ instructions     │     ┌──────────────┐ │
                     │ thumbnailUrl     │     │    Build     │ │
                     │ authorId (fk)    │     ├──────────────┤ │
                     │ visibility       │     │ id (uuid)    │ │
                     │ state            │     │ gameId (fk)  │◄┘
                     │ broker           │     │ iframeUrl    │
                     │ brokerExternalId │     │ isLive       │
                     │ likes            │     │ label        │
                     │ dislikes         │     │ createdAt    │
                     │ createdAt        │     └──────────────┘
                     │ updatedAt        │
                     └────────┬─────────┘
                              │
           ┌──────────────────┼──────────────────┐
           │                  │                  │
           ▼                  ▼                  ▼
┌──────────────────┐ ┌──────────────┐ ┌──────────────────┐
│  GameFeedback    │ │  GameRank    │ │ ExplorationQueue │
├──────────────────┤ ├──────────────┤ ├──────────────────┤
│ id (uuid)        │ │ id (uuid)    │ │ id (uuid)        │
│ gameId (fk)      │ │ gameId (fk)  │ │ gameId (fk)      │
│ sessionId        │ │ surface      │ │ surface          │
│ type (like/dis)  │ │ category     │ │ categorySlug     │
│ createdAt        │ │ platform     │ │ platform         │
└──────────────────┘ │ rank         │ │ position         │
                     │ score        │ │ uniquePlayers    │
                     │ calculatedAt │ │ threshold        │
                     └──────────────┘ │ createdAt        │
                                      └──────────────────┘

┌──────────────────┐     ┌──────────────────────────────────┐
│    Session       │     │           Event                  │
├──────────────────┤     ├──────────────────────────────────┤
│ id (uuid)        │◄────┤ id (uuid)                        │
│ deviceId         │     │ type                             │
│ platform         │     │ gameId (fk, nullable)            │
│ createdAt        │     │ categoryId (fk, nullable)        │
│ lastSeenAt       │     │ sessionId (fk)                   │
└──────────────────┘     │ deviceId (nullable)              │
                         │ platform                         │
                         │ timestamp                        │
                         │ receivedAt                       │
                         │ context (jsonb)                  │
                         └──────────────────────────────────┘

┌──────────────────────────────────────────────────────────┐
│                    DailyMetric                           │
├──────────────────────────────────────────────────────────┤
│ id (uuid)                                                │
│ gameId (fk)                                              │
│ date (date)                                              │
│ platform (desktop/mobile)                                │
│ surface (home/category)                                  │
│ plays, uniquePlayers, impressions, clicks                │
│ pageViews, loadingEnds, totalPlaytimeMs                  │
│ adsShown, likes, dislikes                                │
│ @@unique([gameId, date, platform, surface])              │
└──────────────────────────────────────────────────────────┘

---

Schema Definition (Prisma)

Catalog Domain

model Game {
  id               String   @id @default(uuid())
  title            String
  slug             String   @unique
  description      String?  @db.Text
  instructions     String?  @db.Text
  thumbnailUrl     String?
  authorId         String
  author           Author   @relation(fields: [authorId], references: [id])
  visibility       Visibility @default(DRAFT)
  state            GameState  @default(EXPLORATION)
  broker           String?
  brokerExternalId String?
  likes            Int      @default(0)
  dislikes         Int      @default(0)
  createdAt        DateTime @default(now())
  updatedAt        DateTime @updatedAt

  categories       GameCategory[]
  builds           Build[]
  ranks            GameRank[]
  explorationQueue ExplorationQueue[]
  feedback         GameFeedback[]
  events           Event[]
  dailyMetrics     DailyMetric[]

  @@unique([broker, brokerExternalId])
  @@index([visibility, state])
  @@index([authorId])
  @@index([slug])
}

enum Visibility {
  DRAFT
  HIDDEN
  VISIBLE
}

enum GameState {
  EXPLORATION
  RANKED
}

model Author {
  id        String   @id @default(uuid())
  name      String
  slug      String   @unique
  createdAt DateTime @default(now())
  games     Game[]
}

model Category {
  id        String   @id @default(uuid())
  name      String   @unique
  slug      String   @unique
  iconUrl   String?
  sortOrder Int      @default(0)
  games     GameCategory[]
}

model GameCategory {
  gameId     String
  categoryId String
  game       Game     @relation(fields: [gameId], references: [id], onDelete: Cascade)
  category   Category @relation(fields: [categoryId], references: [id], onDelete: Cascade)

  @@id([gameId, categoryId])
}

model Build {
  id        String   @id @default(uuid())
  gameId    String
  game      Game     @relation(fields: [gameId], references: [id], onDelete: Cascade)
  iframeUrl String
  isLive    Boolean  @default(false)
  label     String?
  createdAt DateTime @default(now())

  @@index([gameId])
}

Analytics Domain

model Session {
  id         String   @id @default(uuid())
  deviceId   String
  platform   Platform
  createdAt  DateTime @default(now())
  lastSeenAt DateTime @default(now())
  events     Event[]
  feedback   GameFeedback[]

  @@index([deviceId])
}

model Event {
  id         String   @id @default(uuid())
  type       String
  gameId     String?
  game       Game?    @relation(fields: [gameId], references: [id])
  categoryId String?
  sessionId  String
  session    Session  @relation(fields: [sessionId], references: [id])
  deviceId   String?           // Anonymous device fingerprint (from localStorage UUID)
  platform   Platform
  timestamp  DateTime
  receivedAt DateTime @default(now())
  context    Json?              // Surface, position, categorySlug, etc.

  @@index([gameId, timestamp])
  @@index([type, timestamp])
  @@index([sessionId, timestamp])
  @@index([gameId, type, timestamp])
  @@index([deviceId])
}

model DailyMetric {
  id              String   @id @default(uuid())
  gameId          String
  game            Game     @relation(fields: [gameId], references: [id], onDelete: Cascade)
  date            DateTime @db.Date
  platform        Platform
  surface         String   @default("home")
  plays           Int      @default(0)
  uniquePlayers   Int      @default(0)
  impressions     Int      @default(0)
  clicks          Int      @default(0)
  pageViews       Int      @default(0)
  loadingEnds     Int      @default(0)
  totalPlaytimeMs BigInt   @default(0)
  adsShown        Int      @default(0)
  likes           Int      @default(0)
  dislikes        Int      @default(0)

  @@unique([gameId, date, platform, surface])
  @@index([gameId, date])
  @@index([date])
}

enum Platform {
  DESKTOP
  MOBILE
}

Discovery Domain

model GameRank {
  id           String   @id @default(uuid())
  gameId       String
  game         Game     @relation(fields: [gameId], references: [id], onDelete: Cascade)
  surface      String   // "home" or category slug
  platform     Platform
  rank         Int
  score        Float    // composite score from ranking algorithm
  calculatedAt DateTime @default(now())

  @@unique([gameId, surface, platform])
  @@index([surface, platform, rank])
}

model ExplorationQueue {
  id            String   @id @default(uuid())
  gameId        String
  game          Game     @relation(fields: [gameId], references: [id], onDelete: Cascade)
  surface       String   // "home" or category slug
  platform      Platform
  position      Int      // queue position (lower = shown first)
  uniquePlayers Int      @default(0)
  threshold     Int      @default(1000)
  createdAt     DateTime @default(now())

  @@unique([gameId, surface, platform])
  @@index([surface, platform, position])
}

Feedback Domain

model GameFeedback {
  id        String       @id @default(uuid())
  gameId    String
  game      Game         @relation(fields: [gameId], references: [id], onDelete: Cascade)
  sessionId String
  session   Session      @relation(fields: [sessionId], references: [id])
  type      FeedbackType
  createdAt DateTime     @default(now())

  @@unique([gameId, sessionId])
  @@index([gameId])
}

enum FeedbackType {
  LIKE
  DISLIKE
}

Admin Domain

model AdminUser {
  id           String   @id @default(uuid())
  email        String   @unique
  passwordHash String
  role         AdminRole @default(EDITOR)
  createdAt    DateTime @default(now())
  lastLoginAt  DateTime?
}

enum AdminRole {
  VIEWER
  EDITOR
  ADMIN
}

---

Indexes Strategy

Query Patterns & Supporting Indexes

Query	Used By	Index
Games by rank for a surface + platform	Home page, category pages	GameRank(surface, platform, rank)
Games by author	Author page	Game(authorId)
Game by slug	Game detail page	Game(slug) unique
Events by game in time range	Metric aggregation	Event(gameId, timestamp)
Events by type in time range	Global metrics	Event(type, timestamp)
Session events ordered	Playtime computation	Event(sessionId, timestamp)
Daily metrics for a game	Admin dashboard	DailyMetric(gameId, date)
Exploration queue ordering	Exploration slot filling	ExplorationQueue(surface, platform, position)
Deduplication check	Broker import	Game(broker, brokerExternalId) unique
Feedback uniqueness	Like/dislike toggle	GameFeedback(gameId, sessionId) unique

---

Event Table Partitioning

For MVP, use native PostgreSQL declarative partitioning by month:

CREATE TABLE events (
  id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  type       VARCHAR(50) NOT NULL,
  game_id    UUID REFERENCES games(id),
  session_id UUID NOT NULL REFERENCES sessions(id),
  timestamp  TIMESTAMPTZ NOT NULL,
  received_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  context    JSONB
) PARTITION BY RANGE (timestamp);

-- Create monthly partitions
CREATE TABLE events_2026_01 PARTITION OF events
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE events_2026_02 PARTITION OF events
  FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

-- Auto-create future partitions via pg_partman or a cron job

See Events Pipeline for retention policy.

---

Migrations

Prisma handles schema migrations:

# Development: apply changes directly
npx prisma db push

# Production: generate migration files
npx prisma migrate dev --name <description>

# Deploy to production
npx prisma migrate deploy

Migration Guidelines

Rule	Reason
Never rename columns in production	Causes downtime. Add new column, backfill, remove old
Always add indexes concurrently	CREATE INDEX CONCURRENTLY to avoid table locks
Add columns as nullable first	Avoids full table rewrite
Test migrations against a production-size dataset	Catch slow migrations before deploy

---

Seed Data

Development seed creates:

Entity	Count	Notes
Categories	~15	Action, Puzzle, Racing, Strategy, etc.
Authors	~20	Mock studio names
Games	~100	Mix of Draft, Hidden, Visible states
Builds	1 per game	With mock iframe URLs
Events	~10K	Spread across games and sessions
DailyMetrics	~30 days	Pre-computed for dashboard testing

npx tsx scripts/seed.ts

---

Connection Pooling

Serverless environments (Vercel) create a new database connection per function invocation. Without pooling, this quickly exhausts PostgreSQL’s connection limit.

MVP: Neon’s Built-in Pooling

Neon provides a pooled connection string out of the box using PgBouncer:

# Direct connection (for migrations)
DATABASE_URL="postgresql://user:pass@ep-xxx.neon.tech/playupi?sslmode=require"

# Pooled connection (for application queries)
DATABASE_URL_POOLED="postgresql://user:pass@ep-xxx-pooler.neon.tech/playupi?sslmode=require"

Prisma configuration:

datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL_POOLED")  // Pooled for queries
  directUrl = env("DATABASE_URL")          // Direct for migrations
}

Growth+: PgBouncer / Supavisor

When running on dedicated PostgreSQL, add a connection pooler (PgBouncer or Supavisor) in front of the database. Target pool size: 20-50 connections per API instance.

---

Backup & Recovery

Phase	Strategy
MVP	Managed database provider handles backups (Neon: 7-day PITR, Supabase: daily snapshots)
Growth	Daily pg_dump to S3/R2 + managed provider backups
Scale	Continuous WAL archiving to S3, PITR to any second