Database

This guide covers Prisma ORM operations, migrations, transactions, schema conventions, and seeding.

Setup

Source: src/config/database.ts, prisma/schema.prisma

import { prisma } from '../config/database.js';

src/config/database.ts builds the client with environment-specific logging (query, error and warn in development, error otherwise; event-based query logging when query monitoring or Prometheus metrics are on) and appends the connection pool parameters to DATABASE_URL.

The prisma singleton is imported throughout the codebase. Do not create additional PrismaClient instances.

Common Operations

Create

const user = await prisma.user.create({
  data: {
    username: 'player1',
    email: 'player@example.com',
    passwordHash: hashedPassword,
    groupId: defaultGroupId,
  },
});

Find One

// By unique field
const user = await prisma.user.findUnique({
  where: { id: userId },
  select: {
    id: true,
    username: true,
    email: true,
    balance: true,
  },
});

// null if not found (throw NotFoundError if needed)
if (!user) throw new NotFoundError('User');

Find Many

const tables = await prisma.pokerTable.findMany({
  where: {
    status: 'WAITING',
  },
  orderBy: { createdAt: 'desc' },
  take: 20,
  skip: (page - 1) * 20,
  include: {
    sessions: {
      where: { endedAt: null },
    },
  },
});

Update

// Simple update
const updated = await prisma.user.update({
  where: { id: userId },
  data: { lastLoginAt: new Date() },
});

// Atomic increment/decrement (safe for concurrent updates)
await prisma.user.update({
  where: { id: userId },
  data: { balance: { increment: amount } },
});

Delete

await prisma.user.delete({ where: { id: userId } });

// Soft delete pattern (preferred for audit trails)
await prisma.user.update({
  where: { id: userId },
  data: { isBlocked: true },
});

Count

const count = await prisma.user.count({
  where: { isBot: false, isBlocked: false },
});

Transactions

Use transactions for operations that must succeed or fail together:

// Interactive transaction (callback style)
const result = await prisma.$transaction(async (tx) => {
  // Deduct from sender
  const sender = await tx.user.update({
    where: { id: senderId },
    data: { balance: { decrement: amount } },
  });

  if (sender.balance.isNegative()) {
    throw new PokerError('Insufficient balance', 'INSUFFICIENT_BALANCE');
  }

  // Credit receiver
  await tx.user.update({
    where: { id: receiverId },
    data: { balance: { increment: amount } },
  });

  // Create audit record
  return tx.transaction.create({
    data: {
      userId: senderId,
      type: 'TRANSFER_OUT',
      amount: -amount,
      targetUserId: receiverId,
      balanceBefore: sender.balance.plus(amount),
      balanceAfter: sender.balance,
      description: `Transfer to ${receiverId}`,
    },
  });
});

Retries for Critical Balance Operations

withRetryableTransaction in src/utils/retryable-transaction.ts wraps a Prisma transaction with a configurable isolation level and retries serialization failures (P2034) with exponential backoff:

const result = await withRetryableTransaction(
  prisma,
  async (tx) => tx.user.update({ /* ... */ }),
  { isolationLevel: 'Serializable', maxRetries: 8 }
);

Transaction Timeouts

For long-running transactions, set a custom timeout:

await prisma.$transaction(async (tx) => {
  // ...
}, {
  maxWait: 5000,   // Max wait for transaction slot (ms)
  timeout: 10000,  // Max transaction duration (ms)
});

Schema Conventions

Source: prisma/schema.prisma

model User {
  // Primary key: UUID
  id              String    @id @default(uuid())

  // Unique fields
  username        String    @unique
  email           String    @unique

  // Field names: camelCase in Prisma, snake_case in DB
  passwordHash    String    @map("password_hash")
  firstName       String?   @map("first_name")
  lastName        String?   @map("last_name")

  // Monetary values: use Decimal, not Float
  balance         Decimal   @default(0) @db.Decimal(18, 2)

  // Boolean flags: explicit default
  isBlocked       Boolean   @default(false) @map("is_blocked")

  // Enums: defined in schema
  kycTier         KycTier   @default(UNVERIFIED) @map("kyc_tier")

  // Timestamps: always include
  createdAt       DateTime  @default(now()) @map("created_at")
  updatedAt       DateTime  @updatedAt @map("updated_at")

  // Relations
  transactions    Transaction[]

  // Indexes for commonly filtered fields
  @@index([groupId])
  @@index([isBot, createdAt])

  // Table name: snake_case plural
  @@map("users")
}

Decimal Precision

Always use Decimal for monetary values, never Float. Floating-point arithmetic causes precision errors in financial calculations:

// Good
balance Decimal @db.Decimal(18, 2)

// Bad
balance Float   // DO NOT use for money

When doing arithmetic in code, use the Decimal constructor:

import { Decimal } from '@prisma/client/runtime/library';

const newBalance = new Decimal(user.balance).plus(new Decimal(amount));

src/utils/decimal-money.ts provides shared helpers such as toDecimal, roundMoneyDown, moneyToNumber, moneyToFixed and percentOfMoney.

Schema Changes and Migrations

Development Workflow

# 1. Edit prisma/schema.prisma

# 2. Generate a new migration
npx prisma migrate dev --name describe_the_change
# This applies the migration and regenerates the Prisma client.
# npm run db:migrate runs the same command and prompts for a name.

# 3. If you need to regenerate without migrating
npm run db:generate

Production Deployment

# Apply pending migrations (no data loss)
npx prisma migrate deploy

Checking Status

npx prisma migrate status

Safety Rules

Never run these against the development or production database:

• prisma migrate reset — drops all data
• prisma db push --force-reset — same effect
• Any raw DROP TABLE, TRUNCATE, or DELETE without WHERE

npm run db:push is intentionally disabled and exits with an error; use npm run db:migrate so every schema change has a migration file.

Seeding

Source: prisma/seed.ts

npm run db:seed

This is a development/test helper only. The script refuses to run unless NODE_ENV is development or test, and it creates shared local credentials for convenience.

The seed creates:

• Default user groups
• Admin user (admin / admin123)
• Test players (player1–player5 / player123)
• Bot players, table styles and sample tables
• Default settings, languages, payment systems, bot strategy profiles and agent role tiers

Run seed after a fresh database setup or if the database is accidentally wiped.

For production or staging bootstrap, set ADMIN_INITIAL_PASSWORD to a unique password and run npm run db:setup after migrations. The setup script and runtime bootstrap both fail closed outside explicit development/test environments if this password is missing or weak.

Querying with Relations

// Include related records
const user = await prisma.user.findUnique({
  where: { id: userId },
  include: {
    group: true,
    transactions: {
      orderBy: { createdAt: 'desc' },
      take: 10,
    },
  },
});

// Nested relations
const tournament = await prisma.tournament.findUnique({
  where: { id: tournamentId },
  include: {
    registrations: {
      include: {
        user: {
          select: { username: true, avatar: true },
        },
      },
    },
  },
});

Raw Queries

Use raw queries only when Prisma’s query builder is insufficient:

// Type-safe raw query
const result = await prisma.$queryRaw<{ count: number }[]>`
  SELECT COUNT(*) as count FROM users
  WHERE created_at > ${new Date(Date.now() - 86400000)}
`;

Always use tagged template literals ($queryRaw) to prevent SQL injection. Never concatenate user input into raw queries.

Connection Pooling

Prisma manages a connection pool. src/config/database.ts appends connection_limit and pool_timeout to DATABASE_URL from these optional env vars:

DATABASE_POOL_SIZE=50      # Default: 50 in production, 10 otherwise
DATABASE_POOL_TIMEOUT=30   # Seconds. Default: 10

If DATABASE_URL already contains connection_limit=, it is used as-is and these vars are ignored:

DATABASE_URL="postgresql://...?connection_limit=50&pool_timeout=30"

Key Models

Model	Purpose
User	Players and admins
UserGroup	Permission groups (admin, player, vip…)
PokerTable	Poker table configuration
TableSession	Player’s session at a table (buy-in, seat)
Game, GamePlayer, GameAction	Recorded hands, participants and actions
GameReplay	Hand replay data
Transaction	Financial ledger entries
Tournament	Tournament configuration
TournamentRegistration	Player entries in tournaments
BonusCredit	Bonus wagering requirements
Setting	DB-backed configuration values
Agent	Agent records
PaymentSystem	Configured payment providers

The schema has 130 models; prisma/schema.prisma is the full reference.

---

Back to Developer Guide