Database

This guide covers the database schema, relationships, migrations, and Row-Level Security (RLS) configuration.

Database Overview

Database: PostgreSQL 15+ ORM: Prisma Isolation: Row-Level Security (RLS) for multi-tenancy

Design Principles

1. Multi-Tenancy: All tables support tenant isolation via RLS
2. Audit Trail: Comprehensive logging of all mutations
3. Data Integrity: Foreign keys and constraints
4. Performance: Strategic indexes for common queries
5. Extensibility: JSON fields for flexible metadata

Schema Overview

Core Entities

The database schema is organized around these core concepts:

Tenant (root)
├── Sites (physical venues)
│   ├── Products (bookable activities)
│   │   ├── Variants (product options)
│   │   └── Resources (allocatable items)
│   ├── ScheduleTemplates (recurring availability)
│   ├── ScheduleOverrides (temporary changes)
│   └── BlackoutWindows (closures)
├── Bookings (customer reservations)
├── Payments (payment transactions)
├── Promotions (discount codes)
├── Vouchers (gift cards/store credit)
└── Configuration
    ├── PriceRulesets (pricing rules)
    ├── TaxConfiguration (tax settings)
    └── Policies (booking policies)

Key Tables

Tenant

Root entity for multi-tenancy.

model Tenant {
  id              String
  name            String
  defaultLocale   String  @default("en")
  defaultCurrency String  @default("EUR")
  createdAt       DateTime
  updatedAt       DateTime
}

Relationships:

• Has many Sites
• Has many Users (via Membership)
• Has many Products, Resources, Bookings

Site

Physical venue/location.

model Site {
  id                  String
  tenantId            String
  name                String
  tz                  String  // IANA timezone
  address             String?
  cancellation_policy Json?
  reschedule_policy   Json?
  is_poa_allowed      Boolean
  // ... location fields
}

Relationships:

• Belongs to Tenant
• Has many Products
• Has many ScheduleTemplates
• Has many Slots

Product

Bookable activity/service.

model Product {
  id                  String
  siteId              String
  tenantId            String
  name                String
  duration            Int      // minutes
  minParty            Int?
  maxParty            Int?
  baseCapacityRules   Json?
  booking_policy      Json?
  cancellation_policy Json?
  reschedule_policy   Json?
  isActive            Boolean
}

Relationships:

• Belongs to Site
• Has many Variants
• Has many ScheduleTemplates
• Has many Resources (via ProductResource)
• Has many Addons (via ProductAddon)

Variant

Product variation (different options of same product).

model Variant {
  id                String
  productId        String
  tenantId         String
  name             String
  duration         Int?
  basePrice        Decimal?
  priceRulesetId   String?  // Links to pricing rules
  maxCapacity      Int?
  isActive         Boolean
}

Relationships:

• Belongs to Product
• Has PriceRuleset (optional)
• Has SlotLabels (via VariantSlotLabel)

Resource

Allocatable entity (room, equipment, staff, item).

model Resource {
  id          String
  tenantId    String
  name        String
  resourceType String  // "room", "equipment", "staff", "item"
  capacity    Int?
  isActive    Boolean
}

Relationships:

• Belongs to Tenant
• Used by Products (via ProductResource)

Booking

Customer reservation record with comprehensive lifecycle management, saga orchestration, and multi-slot support.

model Booking {
  id                 String
  siteId             String
  productId          String
  status             String  // HOLD, TENTATIVE, PART_PAID, PAID, CONFIRMED, CHECKED_IN, COMPLETED, CANCELLED, NO_SHOW
  startsAt           DateTime
  endsAt             DateTime
  partySize          Int
  pricingHash        String?  // Ensures price consistency during checkout
  currency           String
  subtotal           Decimal?
  taxTotal           Decimal?
  grandTotal         Decimal?
  amountPaid         Decimal?
  customerName       String?
  customerEmail      String?
  customerPhone      String?
  reference          String?  @unique
  slotId             String?  // Primary slot (for single-slot bookings)

  // Saga orchestration fields
  sagaState          saga_state_enum?       // not_started, running, compensating, completed, failed, timeout, cancelled
  sagaCorrelationId  String?                // UUID linking saga execution steps
  sagaStartedAt      DateTime?              // When saga orchestration began
  compensationStatus compensation_status_enum?  // none, pending, in_progress, completed, partial, failed

  // Payment on Arrival fields
  poaStatus          String?                // PENDING_ARRIVAL, CONFIRMED_ARRIVAL, etc.
  poaConfirmedAt     DateTime?

  // Multi-slot booking support
  bookingSlots       BookingSlot[]          // Junction table for multi-slot bookings
}

Key Fields:

• status: Extended lifecycle states including TENTATIVE (pending payment), PART_PAID (partial payment received), CHECKED_IN (customer arrived)
• pricingHash: SHA-256 hash of pricing calculation preventing price manipulation between hold and confirmation
• sagaState: Tracks distributed transaction state for booking confirmation/cancellation workflows
• sagaCorrelationId: UUID enabling correlation of saga steps across services and audit logs
• compensationStatus: Tracks rollback state if saga fails (e.g., refund processing, capacity release)
• slotId: Primary slot reference (maintained for backward compatibility with single-slot bookings)
• bookingSlots: Multi-slot booking support via junction table (see BookingSlot below)

Relationships:

• Belongs to Site and Product
• Has one Slot (primary slot for single-slot bookings)
• Has many BookingSlots (for multi-slot bookings)
• Has many BookingAddons
• Has one ReservationHold
• Has one NoShowTracking
• Has many PaymentTransactions
• Has many PromotionApplications
• Has TaxItems (via booking_tax_item)
• Has one SagaExecution (via sagaCorrelationId)

BookingSlot (Junction Table)

Junction table supporting bookings that span multiple time slots with atomic capacity management.

model BookingSlot {
  id           String   @id @default(cuid())
  bookingId    String   @map("booking_id")
  slotId       String   @map("slot_id")
  capacityUsed Int      @map("capacity_used")
  resourceId   String?  @map("resource_id")
  createdAt    DateTime @default(now()) @map("created_at")

  booking      Booking  @relation(fields: [bookingId], references: [id], onDelete: Cascade)
  slot         Slot     @relation(fields: [slotId], references: [id])

  @@unique([bookingId, slotId])
  @@index([slotId])
  @@index([bookingId])
  @@index([resourceId])
  @@map("booking_slot")
}

Purpose: Enables bookings to reserve capacity across multiple consecutive time slots (e.g., 2-hour session spanning two 1-hour slots).

Key Features:

• Atomic Capacity Locking: All slots are locked/released together during booking lifecycle
• Resource Tracking: Optional resource assignment per slot (e.g., specific laser tag arena)
• Cascade Deletion: Automatically removes slot associations when booking is deleted
• Unique Constraint: Prevents duplicate booking-slot associations

Example Use Case:

Booking: Extended Laser Tag Session (2 hours)
- Slot 1: 14:00-15:00, capacityUsed: 4
- Slot 2: 15:00-16:00, capacityUsed: 4
Both slots are atomically reserved/released together

Slot

Generated time slot for availability.

model Slot {
  id         String
  siteId     String
  productId  String
  startsAt   DateTime
  endsAt     DateTime
  capacity   Int
  labelCodes String[]  // Slot labels
}

Relationships:

• Belongs to Site and Product
• Has many Bookings

Generation: Slots are generated from ScheduleTemplates, considering overrides and blackouts.

PaymentTransaction

Payment record.

model PaymentTransaction {
  id            String
  tenantId      String
  bookingId     String
  amount        Decimal
  currency      String
  status        String  // pending, completed, failed, refunded
  paymentMethod String
  provider      String  // stripe, paytrail
  providerId    String? // External transaction ID
  metadata      Json?
}

Relationships:

• Belongs to Booking
• Links to external payment providers

Promotion

Discount code definition.

model Promotion {
  id                    String
  tenantId             String
  code                  String
  name                  String
  promotionType         String  // percentage, fixed
  scopeType             String  // site, product
  discountValue         Decimal?
  minSpendAmount        Decimal?
  maxDiscountAmount     Decimal?
  usageLimitTotal       Int?
  usageLimitPerCustomer Int?
  currentUsageCount     Int
  isStackable           Boolean
  priority              Int
  startsAt              DateTime
  expiresAt             DateTime?
  isActive              Boolean
}

Relationships:

• Belongs to Tenant (and optionally Site)
• Applied via PromotionApplication

Voucher

Gift card or store credit.

model Voucher {
  id                String
  tenantId          String
  code              String
  voucherType       String  // gift_card, store_credit
  originalValue     Decimal
  currentBalance    Decimal
  usageLimit        Int?
  currentUsageCount Int
  customerEmail     String?
  expiresAt         DateTime?
  isActive          Boolean
  isRedeemed        Boolean
}

Relationships:

• Belongs to Tenant
• Applied via PromotionApplication

Schedule Management

ScheduleTemplate

Recurring availability pattern.

model ScheduleTemplate {
  id             String
  tenantId       String
  siteId         String?
  productId      String?
  tz             String      // Timezone
  recurrenceKind String      // daily, weekly, monthly
  byDay          Int?        // Day of week
  byMonth        Int[]        // Months
  byMonthDay     Int[]        // Days of month
  exdates        DateTime[]   // Excluded dates
  startsAt       DateTime     // Start time
  endsAt         DateTime     // End time
}

Usage: Templates generate slots for date ranges. Combined with overrides and blackouts.

ScheduleOverride

Temporary schedule modification.

model ScheduleOverride {
  id             String
  tenantId       String
  siteId         String?
  productId      String?
  dateFrom       DateTime
  dateTo          DateTime
  startsAt       DateTime    // New start time
  endsAt          DateTime    // New end time
  recurrenceKind  String
  // ... recurrence fields
}

Usage: Temporarily changes schedule for specific periods.

BlackoutWindow

Period where availability is blocked.

model BlackoutWindow {
  id             String
  tenantId       String
  siteId         String?
  productId      String?
  kind           String      // closure, maintenance, holiday
  dateFrom       DateTime
  dateTo          DateTime
  recurrenceKind String
  // ... recurrence fields
}

Usage: Blocks slots during blackout periods.

Pricing Tables

PriceRuleset

Collection of pricing rules.

model PriceRuleset {
  id          String
  tenantId    String
  name        String
  currency    String
  isActive    Boolean
  // ... rules via PriceRule
}

Relationships:

• Has many PriceRules
• Used by Variants

PriceRule

Individual pricing rule.

model PriceRule {
  id              String
  priceRulesetId  String
  ruleType        String  // base, party_size_tier, temporal, etc.
  conditions      Json     // Rule-specific conditions
  modifierType    String
  modifierValue   Decimal?
  priority        Int
  isActive        Boolean
}

Rule Types:

• base: Fixed base price
• party_size_tier: Party size-based pricing
• time_window: Temporal modifiers
• participant_type_pricing: Per participant type
• label_modifier: Label-based modifiers

Saga Pattern Tables

SagaExecution

Saga orchestrator execution record tracking distributed transaction state.

model SagaExecution {
  id              String
  tenantId        String
  sagaType        String               // booking_confirmation, booking_cancellation, payment_compensation
  aggregateId     String               // Booking ID or other aggregate root
  correlationId   String @unique       // Unique UUID for correlation across systems
  status          saga_state_enum      // not_started, running, compensating, completed, failed, timeout, cancelled
  compensationStatus compensation_status_enum?  // none, pending, in_progress, completed, partial, failed
  parameters      Json                 // Saga input parameters
  context         Json?                // Execution context and intermediate results
  retryCount      Int @default(0)
  maxRetries      Int @default(3)
  startedAt       DateTime?
  completedAt     DateTime?
  nextRetryAt     DateTime?
  lastError       String?
  createdAt       DateTime @default(now())
  updatedAt       DateTime @updatedAt

  checkpoints     SagaCheckpoint[]

  @@index([tenantId, aggregateId])
  @@index([correlationId])
  @@index([status, nextRetryAt])
}

Key Features:

• Saga States (saga_state_enum): Comprehensive lifecycle tracking

  • not_started: Saga created but not yet started
  • running: Saga executing forward steps
  • compensating: Saga rolling back due to failure
  • completed: Saga completed successfully
  • failed: Saga failed and compensation completed/failed
  • timeout: Saga exceeded timeout threshold
  • cancelled: Saga cancelled by user/admin

• Compensation States (compensation_status_enum): Rollback tracking

  • none: No compensation needed
  • pending: Compensation required but not started
  • in_progress: Actively compensating
  • completed: Compensation finished successfully
  • partial: Some compensation steps succeeded, others failed
  • failed: Compensation failed

• Retry Logic: Exponential backoff with configurable max retries

• Correlation: UUID-based correlation for distributed tracing

• Context Preservation: Stores intermediate results for resume capability

Common Saga Types:

• booking_confirmation: Hold → Payment → Capacity Lock → Confirm
• booking_cancellation: Payment Refund → Capacity Release → Status Update
• payment_compensation: Reverse payment on booking failure

SagaCheckpoint

Individual step in saga execution with compensation support.

model SagaCheckpoint {
  id               String @id @default(cuid())
  tenantId         String
  sagaExecutionId  String
  stepName         String               // hold_capacity, validate_pricing, process_payment, etc.
  stepOrder        Int                  // Execution order (determines compensation order)
  status           saga_state_enum      // pending, running, completed, failed, compensating
  output           Json?                // Step output data
  errorMessage     String?
  retryCount       Int @default(0)
  compensationData Json?                // Data needed for compensation/rollback
  compensatedAt    DateTime?
  createdAt        DateTime @default(now())
  updatedAt        DateTime @updatedAt

  sagaExecution    SagaExecution @relation(fields: [sagaExecutionId], references: [id], onDelete: Cascade)

  @@index([sagaExecutionId, stepOrder])
  @@index([tenantId])
}

Key Features:

• Ordered Execution: Steps execute in stepOrder sequence
• Compensation Support: Stores compensationData for rollback
• Idempotency: Each step can be safely retried
• Reverse Compensation: Steps compensated in reverse order (LIFO)

Example Saga Flow:

Step 1 (order: 1): hold_capacity → completed
Step 2 (order: 2): validate_pricing → completed
Step 3 (order: 3): process_payment → failed
  ↓ Trigger Compensation (reverse order)
Step 2: compensate_pricing → completed
Step 1: compensate_capacity (release) → completed

MessageOutbox

Outbox Pattern implementation for reliable event delivery with at-least-once guarantees.

model MessageOutbox {
  id            String @id
  tenantId      String
  messageType   String               // booking.created, booking.confirmed, payment.processed, etc.
  aggregateId   String              // Booking ID, Payment ID, etc.
  aggregateType String              // booking, payment, promotion, etc.
  eventVersion  Int @default(1)      // Event schema version
  eventData     Json                 // Complete event payload
  metadata      Json? @default("{}")  // Correlation IDs, trace context, etc.
  status        String @default("pending")  // pending, processing, sent, failed, dead_letter
  scheduledAt   DateTime @default(now())    // When to process (for delayed events)
  processedAt   DateTime?
  retryCount    Int @default(0)
  maxRetries    Int @default(3)
  nextRetryAt   DateTime?
  lastError     String?
  createdAt     DateTime @default(now())
  updatedAt     DateTime @updatedAt

  @@index([status, scheduledAt])
  @@index([tenantId, aggregateType, aggregateId])
  @@index([messageType, status])
}

Purpose: Ensures reliable delivery of domain events to external systems (email, webhooks, analytics) with transactional guarantees.

Key Features:

• Transactional Write: Events written in same transaction as business logic
• At-Least-Once Delivery: Guaranteed delivery with retry mechanism
• Idempotency: Each message has unique ID for deduplication
• Dead Letter Queue: Failed messages moved to dead_letter status after max retries
• Ordered Processing: Messages processed in scheduledAt order per aggregate
• Exponential Backoff: Retry delays increase exponentially (1s, 2s, 4s, 8s, etc.)

Workflow:

1. Business transaction completes → Insert to message_outbox
2. Worker polls for pending messages → Update status to 'processing'
3. Attempt delivery → Mark 'sent' or retry with backoff
4. If max retries exceeded → Move to 'dead_letter' for manual review

Common Message Types:

• booking.created: New booking hold created
• booking.confirmed: Booking confirmed and paid
• booking.cancelled: Booking cancelled with refund
• payment.processed: Payment successfully captured
• email.booking_confirmation: Trigger confirmation email
• webhook.external_system: Notify external integration

Usage: Tracks saga steps for booking orchestration with compensation support.

Enum Types

The platform uses PostgreSQL enum types for type-safe state management across critical workflows.

saga_state_enum

Defines the lifecycle states for saga execution and compensation workflows.

CREATE TYPE saga_state_enum AS ENUM (
  'not_started',
  'running',
  'compensating',
  'completed',
  'failed',
  'timeout',
  'cancelled'
);

State Descriptions:

• not_started: Saga has been created but execution has not begun

  • Use Case: Scheduled sagas or queued for execution
  • Next States: running, cancelled

• running: Saga is actively executing forward steps

  • Use Case: Normal saga execution in progress
  • Next States: completed, compensating, failed, timeout

• compensating: Saga encountered failure and is rolling back completed steps

  • Use Case: Payment failed after capacity lock, need to release capacity
  • Next States: failed, partial (via compensationStatus)

• completed: Saga finished successfully, all steps completed

  • Use Case: Booking confirmed, payment captured, capacity locked
  • Terminal State: No further transitions

• failed: Saga failed and compensation is complete (or compensation failed)

  • Use Case: Unrecoverable error, manual intervention may be needed
  • Terminal State: Logged for review

• timeout: Saga exceeded maximum execution time threshold

  • Use Case: External service timeout, deadlock detection
  • Next States: compensating, failed

• cancelled: Saga was explicitly cancelled by user or admin

  • Use Case: User abandoned checkout, admin cancelled operation
  • Terminal State: May trigger compensation

State Transition Diagram:

not_started → running → completed ✓
                 ↓
              compensating → failed ✗
                 ↓
              timeout → failed ✗
                 ↓
              cancelled ✗

Usage in Code:

// Update saga state
await db.sagaExecution.update({
  where: { correlationId },
  data: {
    status: 'running',
    startedAt: new Date(),
  },
});

// Trigger compensation
await db.sagaExecution.update({
  where: { correlationId },
  data: {
    status: 'compensating',
    compensationStatus: 'pending',
  },
});

compensation_status_enum

Tracks the state of compensation (rollback) operations when a saga fails.

CREATE TYPE compensation_status_enum AS ENUM (
  'none',
  'pending',
  'in_progress',
  'completed',
  'partial',
  'failed'
);

Status Descriptions:

• none: No compensation required (saga completed successfully or not yet started)

  • Use Case: Happy path, no failures occurred
  • Saga States: not_started, running, completed

• pending: Compensation is required but has not started yet

  • Use Case: Saga failed, compensation queued
  • Saga States: compensating
  • Next Status: in_progress

• in_progress: Compensation steps are currently executing

  • Use Case: Rolling back payment, releasing capacity
  • Saga States: compensating
  • Next Status: completed, partial, failed

• completed: All compensation steps succeeded

  • Use Case: Refund processed, capacity released, booking cancelled
  • Saga States: failed (saga failed but compensated cleanly)
  • Terminal Status: Safe to retry original operation

• partial: Some compensation steps succeeded, others failed

  • Use Case: Refund succeeded but email notification failed
  • Saga States: failed
  • Terminal Status: Requires manual review

• failed: Compensation failed, system in inconsistent state

  • Use Case: Payment reversal failed, critical error
  • Saga States: failed
  • Terminal Status: Urgent manual intervention required

Compensation State Diagram:

none (no failure)

pending → in_progress → completed ✓ (clean rollback)
                  ↓
               partial ⚠️ (needs review)
                  ↓
               failed ✗ (critical issue)

Usage in Code:

// Start compensation
await db.sagaExecution.update({
  where: { correlationId },
  data: {
    status: 'compensating',
    compensationStatus: 'in_progress',
  },
});

// Mark compensation complete
await db.sagaExecution.update({
  where: { correlationId },
  data: {
    status: 'failed', // saga failed but compensated
    compensationStatus: 'completed',
  },
});

Combined Saga + Compensation States:

Saga State	Compensation Status	Meaning
running	none	Normal execution
compensating	pending	Failure detected, compensation queued
compensating	in_progress	Actively rolling back
failed	completed	Saga failed, clean rollback
failed	partial	Saga failed, incomplete rollback ⚠️
failed	failed	Saga failed, rollback failed ✗
completed	none	Success ✓

Row-Level Security (RLS)

Overview

All tenant-scoped tables have RLS enabled to enforce multi-tenant isolation at the database level.

RLS Policy Pattern

-- Example policy for Booking table
CREATE POLICY tenant_isolation ON booking
  FOR ALL
  USING (tenant_id = current_setting('app.current_tenant_id')::text);

Setting Tenant Context

// In NestJS middleware or service
import { withTenant } from '@booking/db/rls-context';

await withTenant(tenantId, async () => {
  // All queries automatically filtered by tenant
  const bookings = await db().booking.findMany();
  // Only returns bookings for this tenant
});

RLS Benefits

1. Security: Prevents data leakage at database level
2. Simplicity: No application-level filtering needed
3. Performance: Database-optimized filtering
4. Safety: Cannot be bypassed accidentally

Tables with RLS

All tables with tenantId field have RLS:

• Booking
• Product
• Site
• Resource
• Promotion
• Voucher
• PaymentTransaction
• And many more...

Indexes

Common Indexes

Booking indexes:

CREATE INDEX idx_booking_tenant_status ON booking(tenant_id, status);
CREATE INDEX idx_booking_starts_at ON booking(starts_at);
CREATE INDEX idx_booking_slot_id ON booking(slot_id);

Slot indexes:

CREATE INDEX idx_slot_product_starts ON slot(product_id, starts_at);
CREATE INDEX idx_slot_site_starts ON slot(site_id, starts_at);

Availability queries:

-- Optimized for availability queries
CREATE INDEX idx_slot_lookup ON slot(site_id, product_id, starts_at);

Index Strategy

1. Foreign Keys: Automatically indexed
2. Tenant Isolation: All tenant queries filtered
3. Date Ranges: Indexed for availability queries
4. Status Queries: Composite indexes for common filters

Migrations

Creating Migrations

cd packages/db

# Create migration from schema changes
pnpm prisma migrate dev --name your_migration_name

This:

1. Creates migration file in prisma/migrations/
2. Applies migration to database
3. Generates Prisma client
4. Updates migration history

Migration Files

Migrations are stored in:

packages/db/prisma/migrations/
├── 20250101000000_initial/
│   └── migration.sql
├── 20250102000000_add_pricing/
│   └── migration.sql
└── ...

Applying Migrations

Development:

pnpm run migrate:dev

Production:

pnpm run migrate:deploy

Migration Best Practices

1. Always Review: Check generated SQL before committing
2. Test Locally: Apply migrations in development first
3. Backup Production: Always backup before production migrations
4. Idempotent: Ensure migrations can be re-run safely
5. RLS Policies: Include RLS policy updates in migrations

Data Access Layer (DAL)

Prisma Client

Generated client provides type-safe database access:

import { db } from '@booking/db/rls-context';

const booking = await db().booking.findUnique({
  where: { id: bookingId },
  include: { BookingAddon: true },
});

Tenant Context Helpers

import { withTenant } from '@booking/db/rls-context';

await withTenant(tenantId, async () => {
  // All queries automatically filtered
  const bookings = await db().booking.findMany();
});

Transaction Support

await db().$transaction(async (tx) => {
  await tx.booking.create({ data: ... });
  await tx.paymentTransaction.create({ data: ... });
});

Relationships Summary

Key Relationships

Tenant → Sites → Products → Variants:

• Hierarchical organization
• Each level can have configuration

Product → Resources:

• Many-to-many via ProductResource
• Resources can be shared

Booking → Slot:

• One booking per slot (within capacity)
• Slots generated from schedules

Booking → Payments:

• One booking, many payments (for mixed payments)
• Payment status tracked

Booking → Promotions:

• Many promotions can apply (if stackable)
• Via PromotionApplication

Data Constraints

Foreign Keys

All relationships enforced via foreign keys:

• Cascading deletes where appropriate
• Restrict deletes for critical relationships

Unique Constraints

Booking reference:

reference String? @unique

Slot uniqueness:

@@unique([siteId, productId, startsAt])

Check Constraints

Capacity:

CHECK (capacity > 0)

Dates:

CHECK (starts_at < ends_at)

Amounts:

CHECK (amount >= 0)

Audit Trail

Audit Logging

Multiple audit tables track changes:

booking_audit_log:

• All booking state changes
• User attribution
• Previous and new states
• Timestamps and metadata

admin_override_log:

• Admin overrides
• Reason tracking
• Policy bypasses

Immutability

Audit logs are append-only:

• No updates or deletes
• Complete history
• Compliance ready

Performance Considerations

Query Optimization Best Practices

Avoid N+1 Queries

Never fetch records in a loop. Use batch queries instead:

// Bad: N+1 pattern — one query per slot
const slots = await Promise.all(
  slotIds.map((id) => tx.slot.findUnique({ where: { id } })),
);

// Good: Single batch query
const slots = await tx.slot.findMany({
  where: { id: { in: slotIds } },
  select: { id: true, capacity: true },
});
const slotMap = new Map(slots.map((s) => [s.id, s]));

Similarly for raw SQL, batch with ANY($1::uuid[]) and GROUP BY instead of per-row queries.

Use Select Clauses

Always specify select when you don't need the full record. This reduces data transfer and memory usage:

// Bad: Fetches all 20+ columns
const bookings = await tx.booking.findMany({ where: filters });

// Good: Only fetches what's needed
const bookings = await tx.booking.findMany({
  where: filters,
  select: { id: true, startsAt: true, grandTotal: true },
});

For counts, use _count instead of including full relations:

// Bad: Loads all booking records just to count them
const slots = await tx.slot.findMany({
  include: { Booking: { where: { status: { in: activeStatuses } } } },
});
const count = slot.Booking.length;

// Good: Database counts the records
const slots = await tx.slot.findMany({
  select: {
    id: true,
    capacity: true,
    _count: {
      select: { Booking: { where: { status: { in: activeStatuses } } } },
    },
  },
});
const count = slot._count.Booking;

Prefer Prisma Over Raw SQL

Use Prisma ORM for simple queries. Reserve $queryRawUnsafe for complex joins or aggregations that Prisma can't express:

// Prefer: Type-safe, no SQL injection risk
const booking = await tx.booking.findFirst({
  where: { id: bookingId, tenantId },
  select: { slotId: true, partySize: true },
});

// Only when needed: Complex aggregation
const staffCounts = await tx.$queryRawUnsafe<
  Array<{ skill_id: string; count: bigint }>
>(
  `SELECT rs.skill_id, COUNT(DISTINCT rs.resource_id) as count
   FROM resource_skill rs JOIN resource r ON r.id = rs.resource_id
   WHERE rs.skill_id = ANY($1::uuid[]) AND r.site_id = $2
   GROUP BY rs.skill_id`,
  skillIds,
  siteId,
);

Capacity Management

Slot Generation:

• Generated on-demand
• Cached for performance
• Incremental updates

Availability Queries:

• Optimized indexes
• Cached results
• Efficient date range queries

Backup and Recovery

Backup Strategy

1. Regular Backups: Automated daily backups
2. Transaction Logs: Point-in-time recovery
3. Migration History: Track schema changes

Recovery Procedures

1. Restore from Backup: Latest backup restore
2. Replay Migrations: Apply migrations in order
3. Data Validation: Verify data integrity

Next Steps

• Review Architecture Guide for system design
• Check Setup Guide for development environment
• Explore Testing Guide for test strategies
• See API Reference for data models