Database Architecture - Prisma Postgres

Strategic Decision: Prisma Postgres as the unified managed database with built-in connection pooling, edge caching, and seamless Prisma ORM integration.

Architecture Overview

┌─────────────────────────────────────────────────────────────────┐
│                    Database Architecture                         │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│   Lambda / Vercel / Edge Functions                              │
│        │                                                        │
│        ▼                                                        │
│   ┌─────────────────────────────────────────────┐              │
│   │           Prisma Client                      │              │
│   │     Type-safe queries, migrations            │              │
│   └─────────────────────┬───────────────────────┘              │
│                         │                                       │
│                         ▼                                       │
│   ┌─────────────────────────────────────────────┐              │
│   │         Prisma Postgres                      │              │
│   │   ┌─────────────────────────────────────┐   │              │
│   │   │  Prisma Accelerate (Edge Cache)     │   │              │
│   │   │  • 300+ global edge locations       │   │              │
│   │   │  • Query-level caching              │   │              │
│   │   └─────────────────────────────────────┘   │              │
│   │   ┌─────────────────────────────────────┐   │              │
│   │   │  Connection Pooling (Built-in)      │   │              │
│   │   │  • Serverless-optimized             │   │              │
│   │   │  • No RDS Proxy needed              │   │              │
│   │   └─────────────────────────────────────┘   │              │
│   │   ┌─────────────────────────────────────┐   │              │
│   │   │  PostgreSQL Database                │   │              │
│   │   │  • Managed by Prisma                │   │              │
│   │   │  • Automatic backups                │   │              │
│   │   └─────────────────────────────────────┘   │              │
│   └─────────────────────────────────────────────┘              │
│                                                                 │
│   ┌─────────────────────────────────────────────┐              │
│   │        Redis (Optional)                      │              │
│   │     Session storage, real-time data          │              │
│   └─────────────────────────────────────────────┘              │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

Why Prisma Postgres

Single Vendor, Full Stack

vs. Self-Managed PostgreSQL

Pricing Tiers

Recommended: Pro Plan ($49/month)

• 10 million operations included
• 50 GB storage
• Daily backups (7-day retention)
• Spend limits to prevent surprises
• Sufficient for moderate airline operations

Scale Estimate for AURA

Pro plan covers this with room to grow.

Setup & Configuration

1. Create Database

# Create new Prisma Postgres database
npx prisma db create

# Or via Prisma Console
# https://console.prisma.io

2. Prisma Schema

// prisma/schema.prisma

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

// ============================================
// BOOKING DOMAIN
// ============================================

model Booking {
  id              String            @id @default(cuid())
  pnr             String            @unique @db.VarChar(6)
  status          BookingStatus     @default(CONFIRMED)
  createdAt       DateTime          @default(now()) @map("created_at")
  updatedAt       DateTime          @updatedAt @map("updated_at")
  expiresAt       DateTime?         @map("expires_at")

  // Relations
  passengers      Passenger[]
  segments        Segment[]
  payments        Payment[]
  tickets         Ticket[]

  // Contact
  contactEmail    String            @map("contact_email")
  contactPhone    String?           @map("contact_phone")

  // Pricing
  totalAmount     Decimal           @db.Decimal(10, 2) @map("total_amount")
  currency        String            @default("USD") @db.VarChar(3)

  // Audit
  channel         String            @db.VarChar(20)  // WEB, MOBILE, API
  agentId         String?           @map("agent_id")

  @@index([pnr])
  @@index([createdAt])
  @@index([contactEmail])
  @@map("bookings")
}

enum BookingStatus {
  PENDING
  CONFIRMED
  TICKETED
  CANCELLED
  FLOWN
}

model Passenger {
  id              String            @id @default(cuid())
  bookingId       String            @map("booking_id")
  booking         Booking           @relation(fields: [bookingId], references: [id], onDelete: Cascade)

  // Identity
  firstName       String            @map("first_name") @db.VarChar(50)
  lastName        String            @map("last_name") @db.VarChar(50)
  dateOfBirth     DateTime?         @map("date_of_birth") @db.Date
  gender          Gender?

  // Documents
  documentType    DocumentType?     @map("document_type")
  documentNumber  String?           @map("document_number") @db.VarChar(20)
  documentExpiry  DateTime?         @map("document_expiry") @db.Date
  nationality     String?           @db.VarChar(2)

  // Type
  passengerType   PassengerType     @default(ADULT) @map("passenger_type")

  // Relations
  tickets         Ticket[]
  seatAssignments SeatAssignment[]

  @@index([bookingId])
  @@map("passengers")
}

enum Gender {
  MALE
  FEMALE
  OTHER
}

enum DocumentType {
  PASSPORT
  NATIONAL_ID
  DRIVERS_LICENSE
}

enum PassengerType {
  ADULT
  CHILD
  INFANT
}

// ============================================
// FLIGHT & INVENTORY DOMAIN
// ============================================

model Flight {
  id              String            @id @default(cuid())
  flightNumber    String            @map("flight_number") @db.VarChar(6)
  departureDate   DateTime          @map("departure_date") @db.Date

  // Route
  origin          String            @db.VarChar(3)
  destination     String            @db.VarChar(3)

  // Times (UTC)
  scheduledDeparture DateTime       @map("scheduled_departure")
  scheduledArrival   DateTime       @map("scheduled_arrival")
  actualDeparture    DateTime?      @map("actual_departure")
  actualArrival      DateTime?      @map("actual_arrival")

  // Aircraft
  aircraftId      String?           @map("aircraft_id")
  aircraft        Aircraft?         @relation(fields: [aircraftId], references: [id])
  aircraftType    String            @map("aircraft_type") @db.VarChar(4)

  // Status
  status          FlightStatus      @default(SCHEDULED)

  // Relations
  segments        Segment[]
  inventory       Inventory[]
  seatMap         Seat[]

  @@unique([flightNumber, departureDate])
  @@index([origin, destination, departureDate])
  @@index([departureDate])
  @@map("flights")
}

enum FlightStatus {
  SCHEDULED
  BOARDING
  DEPARTED
  IN_FLIGHT
  ARRIVED
  CANCELLED
  DELAYED
}

model Inventory {
  id              String            @id @default(cuid())
  flightId        String            @map("flight_id")
  flight          Flight            @relation(fields: [flightId], references: [id])

  cabinClass      CabinClass        @map("cabin_class")
  bookingClass    String            @map("booking_class") @db.VarChar(1)

  // Availability
  capacity        Int
  sold            Int               @default(0)
  available       Int               // Computed: capacity - sold - blocked
  blocked         Int               @default(0)

  // Pricing
  baseFare        Decimal           @db.Decimal(10, 2) @map("base_fare")

  // Version for optimistic locking
  version         Int               @default(0)

  @@unique([flightId, bookingClass])
  @@index([flightId])
  @@map("inventory")
}

enum CabinClass {
  ECONOMY
  PREMIUM_ECONOMY
  BUSINESS
  FIRST
}

model Segment {
  id              String            @id @default(cuid())
  bookingId       String            @map("booking_id")
  booking         Booking           @relation(fields: [bookingId], references: [id], onDelete: Cascade)
  flightId        String            @map("flight_id")
  flight          Flight            @relation(fields: [flightId], references: [id])

  // Booking details
  bookingClass    String            @map("booking_class") @db.VarChar(1)
  cabinClass      CabinClass        @map("cabin_class")
  fareBasis       String            @map("fare_basis") @db.VarChar(15)

  // Sequence
  segmentNumber   Int               @map("segment_number")

  // Status
  status          SegmentStatus     @default(CONFIRMED)

  @@index([bookingId])
  @@index([flightId])
  @@map("segments")
}

enum SegmentStatus {
  CONFIRMED
  CHECKED_IN
  BOARDED
  FLOWN
  CANCELLED
  NO_SHOW
}

// ============================================
// AIRCRAFT & SEATS DOMAIN
// ============================================

model Aircraft {
  id              String            @id @default(cuid())
  registration    String            @unique @db.VarChar(10)
  aircraftType    String            @map("aircraft_type") @db.VarChar(4)

  // Configuration
  totalSeats      Int               @map("total_seats")
  configuration   Json              // Cabin layout details

  // Status
  status          AircraftStatus    @default(AVAILABLE)

  // MRO tracking
  totalCycles     Int               @default(0) @map("total_cycles")
  totalHours      Decimal           @default(0) @db.Decimal(10, 1) @map("total_hours")
  lastMaintenance DateTime?         @map("last_maintenance")
  nextMaintenance DateTime?         @map("next_maintenance")

  // Relations
  flights         Flight[]
  seats           Seat[]

  @@map("aircraft")
}

enum AircraftStatus {
  AVAILABLE
  IN_FLIGHT
  MAINTENANCE
  AOG  // Aircraft on Ground
}

model Seat {
  id              String            @id @default(cuid())
  flightId        String            @map("flight_id")
  flight          Flight            @relation(fields: [flightId], references: [id])
  aircraftId      String            @map("aircraft_id")
  aircraft        Aircraft          @relation(fields: [aircraftId], references: [id])

  // Seat info
  seatNumber      String            @map("seat_number") @db.VarChar(4)
  cabinClass      CabinClass        @map("cabin_class")
  seatType        SeatType          @map("seat_type")

  // Status
  status          SeatStatus        @default(AVAILABLE)

  // Pricing
  seatFee         Decimal?          @db.Decimal(10, 2) @map("seat_fee")

  // Version for optimistic locking
  version         Int               @default(0)

  // Relations
  assignments     SeatAssignment[]

  @@unique([flightId, seatNumber])
  @@index([flightId, status])
  @@map("seats")
}

enum SeatType {
  WINDOW
  MIDDLE
  AISLE
  EXIT_ROW
  BULKHEAD
}

enum SeatStatus {
  AVAILABLE
  BLOCKED
  OCCUPIED
  HELD
}

model SeatAssignment {
  id              String            @id @default(cuid())
  seatId          String            @map("seat_id")
  seat            Seat              @relation(fields: [seatId], references: [id])
  passengerId     String            @map("passenger_id")
  passenger       Passenger         @relation(fields: [passengerId], references: [id])

  assignedAt      DateTime          @default(now()) @map("assigned_at")

  @@unique([seatId])
  @@unique([passengerId, seatId])
  @@map("seat_assignments")
}

// ============================================
// PAYMENT DOMAIN
// ============================================

model Payment {
  id              String            @id @default(cuid())
  bookingId       String            @map("booking_id")
  booking         Booking           @relation(fields: [bookingId], references: [id])

  // Amount
  amount          Decimal           @db.Decimal(10, 2)
  currency        String            @db.VarChar(3)

  // Method
  method          PaymentMethod

  // Status
  status          PaymentStatus     @default(PENDING)

  // Provider
  providerRef     String?           @map("provider_ref")
  providerData    Json?             @map("provider_data")

  // Timestamps
  createdAt       DateTime          @default(now()) @map("created_at")
  processedAt     DateTime?         @map("processed_at")

  @@index([bookingId])
  @@map("payments")
}

enum PaymentMethod {
  CREDIT_CARD
  DEBIT_CARD
  BANK_TRANSFER
  DIGITAL_WALLET
}

enum PaymentStatus {
  PENDING
  PROCESSING
  COMPLETED
  FAILED
  REFUNDED
}

// ============================================
// TICKETING DOMAIN
// ============================================

model Ticket {
  id              String            @id @default(cuid())
  ticketNumber    String            @unique @map("ticket_number") @db.VarChar(14)
  bookingId       String            @map("booking_id")
  booking         Booking           @relation(fields: [bookingId], references: [id])
  passengerId     String            @map("passenger_id")
  passenger       Passenger         @relation(fields: [passengerId], references: [id])

  // Status
  status          TicketStatus      @default(OPEN)

  // Pricing
  baseFare        Decimal           @db.Decimal(10, 2) @map("base_fare")
  taxes           Decimal           @db.Decimal(10, 2)
  totalAmount     Decimal           @db.Decimal(10, 2) @map("total_amount")
  currency        String            @db.VarChar(3)

  // Timestamps
  issuedAt        DateTime          @default(now()) @map("issued_at")
  voidedAt        DateTime?         @map("voided_at")

  @@index([bookingId])
  @@index([passengerId])
  @@map("tickets")
}

enum TicketStatus {
  OPEN
  USED
  VOID
  REFUNDED
  EXCHANGED
}

// ============================================
// LOYALTY DOMAIN (Spend-Based)
// ============================================

model LoyaltyAccount {
  id              String            @id @default(cuid())
  memberId        String            @unique @map("member_id") @db.VarChar(12)

  // Customer
  email           String            @unique
  firstName       String            @map("first_name")
  lastName        String            @map("last_name")

  // Status
  tier            LoyaltyTier       @default(MEMBER)
  tierExpiryDate  DateTime?         @map("tier_expiry_date")

  // Spend tracking (rolling 12 months)
  currentSpend    Decimal           @default(0) @db.Decimal(10, 2) @map("current_spend")
  lifetimeSpend   Decimal           @default(0) @db.Decimal(10, 2) @map("lifetime_spend")

  // Timestamps
  enrolledAt      DateTime          @default(now()) @map("enrolled_at")
  lastActivity    DateTime?         @map("last_activity")

  // Relations
  transactions    SpendTransaction[]

  @@index([email])
  @@index([tier])
  @@map("loyalty_accounts")
}

enum LoyaltyTier {
  MEMBER      // $0
  SILVER      // $1,000
  GOLD        // $2,500
  PLATINUM    // $5,000
}

model SpendTransaction {
  id              String            @id @default(cuid())
  accountId       String            @map("account_id")
  account         LoyaltyAccount    @relation(fields: [accountId], references: [id])

  // Transaction
  type            SpendType
  bookingRef      String?           @map("booking_ref")
  amount          Decimal           @db.Decimal(10, 2)
  currency        String            @db.VarChar(3)

  // Timestamp
  transactionDate DateTime          @default(now()) @map("transaction_date")
  qualifyingPeriodStart DateTime    @map("qualifying_period_start")
  qualifyingPeriodEnd   DateTime    @map("qualifying_period_end")

  @@index([accountId])
  @@index([transactionDate])
  @@map("spend_transactions")
}

enum SpendType {
  FLIGHT
  ANCILLARY
  PARTNER
  ADJUSTMENT
}

3. Environment Configuration

# .env
DATABASE_URL="prisma+postgres://accelerate.prisma-data.net/?api_key=your_api_key"

# For direct connections (migrations, Prisma Studio)
DIRECT_DATABASE_URL="postgresql://user:pass@your-db.prisma-data.net:5432/db"

4. Prisma Client Setup

// lib/prisma.ts
import { PrismaClient } from '@prisma/client'

const globalForPrisma = globalThis as unknown as { prisma: PrismaClient }

export const prisma = globalForPrisma.prisma || new PrismaClient({
  log: process.env.NODE_ENV === 'development'
    ? ['query', 'error', 'warn']
    : ['error'],
})

if (process.env.NODE_ENV !== 'production') {
  globalForPrisma.prisma = prisma
}

Prisma Accelerate (Edge Caching)

Built into Prisma Postgres - configure caching at the query level:

Cache Strategies

// TTL-based caching (time-to-live)
const flights = await prisma.flight.findMany({
  where: {
    origin: 'CCS',
    destination: 'MIA',
    departureDate: new Date('2024-06-15'),
  },
  cacheStrategy: {
    ttl: 300,  // Cache for 5 minutes
  },
})

// Stale-While-Revalidate (SWR)
const availability = await prisma.inventory.findMany({
  where: { flightId },
  cacheStrategy: {
    ttl: 60,          // Fresh for 1 minute
    swr: 300,         // Serve stale for 5 more minutes while revalidating
  },
})

// No caching for real-time data
const booking = await prisma.booking.findUnique({
  where: { pnr },
  // No cacheStrategy = always fresh
})

Cache Invalidation

// Invalidate specific queries after booking
import { prismaAccelerate } from '@prisma/extension-accelerate'

// After a booking is confirmed
await prisma.$accelerate.invalidate({
  tags: [`flight:${flightId}`, `route:${origin}-${destination}`],
})

Caching Strategy by Data Type

CRUD Operations

Booking Operations

// Create booking with nested relations
async function createBooking(data: CreateBookingInput): Promise<Booking> {
  return prisma.booking.create({
    data: {
      pnr: generatePNR(),
      contactEmail: data.email,
      contactPhone: data.phone,
      totalAmount: data.totalAmount,
      currency: data.currency,
      channel: data.channel,
      passengers: {
        create: data.passengers.map(p => ({
          firstName: p.firstName,
          lastName: p.lastName,
          dateOfBirth: p.dateOfBirth,
          passengerType: p.type,
        })),
      },
      segments: {
        create: data.segments.map((s, i) => ({
          flightId: s.flightId,
          bookingClass: s.bookingClass,
          cabinClass: s.cabinClass,
          fareBasis: s.fareBasis,
          segmentNumber: i + 1,
        })),
      },
    },
    include: {
      passengers: true,
      segments: { include: { flight: true } },
    },
  })
}

// Retrieve booking (no cache - real-time)
async function getBookingByPNR(pnr: string): Promise<Booking | null> {
  return prisma.booking.findUnique({
    where: { pnr },
    include: {
      passengers: {
        include: { seatAssignments: { include: { seat: true } } },
      },
      segments: {
        include: { flight: true },
        orderBy: { segmentNumber: 'asc' },
      },
      tickets: true,
      payments: true,
    },
  })
}

Inventory with Optimistic Locking

async function bookInventory(
  flightId: string,
  bookingClass: string,
  quantity: number
): Promise<boolean> {
  return prisma.$transaction(async (tx) => {
    const inventory = await tx.inventory.findUnique({
      where: {
        flightId_bookingClass: { flightId, bookingClass },
      },
    })

    if (!inventory || inventory.available < quantity) {
      throw new Error('Insufficient inventory')
    }

    const updated = await tx.inventory.updateMany({
      where: {
        flightId,
        bookingClass,
        version: inventory.version,
      },
      data: {
        sold: { increment: quantity },
        available: { decrement: quantity },
        version: { increment: 1 },
      },
    })

    if (updated.count === 0) {
      throw new Error('Concurrent modification - retry')
    }

    return true
  })
}

Flight Search with Caching

async function searchFlights(params: {
  origin: string
  destination: string
  date: Date
  passengers: number
}): Promise<FlightSearchResult[]> {
  const flights = await prisma.flight.findMany({
    where: {
      origin: params.origin,
      destination: params.destination,
      departureDate: params.date,
      status: 'SCHEDULED',
      inventory: {
        some: { available: { gte: params.passengers } },
      },
    },
    include: {
      inventory: {
        where: { available: { gte: params.passengers } },
        orderBy: { baseFare: 'asc' },
      },
    },
    orderBy: { scheduledDeparture: 'asc' },
    cacheStrategy: {
      ttl: 60,      // Fresh for 1 minute
      swr: 300,     // Stale-while-revalidate for 5 minutes
    },
  })

  return flights.map(flight => ({
    flightNumber: flight.flightNumber,
    departure: flight.scheduledDeparture,
    arrival: flight.scheduledArrival,
    lowestFare: flight.inventory[0]?.baseFare,
    availability: flight.inventory.map(i => ({
      class: i.bookingClass,
      available: i.available,
      fare: i.baseFare,
    })),
  }))
}

Performance Targets

Migration Workflow

# Development: Create and apply migrations
npx prisma migrate dev --name add_loyalty_system

# Production: Apply pending migrations
npx prisma migrate deploy

# Generate client
npx prisma generate

# Open Prisma Studio (GUI)
npx prisma studio

Backup & Recovery

Prisma Postgres Backups

Point-in-Time Recovery

Available on Pro and Business plans through Prisma Console.

Query Insights & Optimization

Prisma Postgres includes built-in query analysis:

// Enable query insights
const prisma = new PrismaClient({
  log: [
    { level: 'query', emit: 'event' },
  ],
})

prisma.$on('query', (e) => {
  console.log(`Query: ${e.query}`)
  console.log(`Duration: ${e.duration}ms`)
})

AI-Powered Recommendations

• Pro plan: 100 AI recommendations/month
• Business plan: Unlimited recommendations

Technology Stack Summary

Cost Comparison

Prisma Postgres Pro ($49/month)

• 10M operations included
• 50 GB storage
• Built-in connection pooling
• Edge caching included
• Query insights included

vs. Aurora + RDS Proxy (~$300-400/month)

• Aurora Serverless v2: $150-250
• RDS Proxy: $20-50
• ElastiCache Redis: $50-100
• CloudWatch: $10-20
• Separate caching solution needed

Prisma Postgres saves ~$250/month while providing a simpler architecture.

Research Topics

• Prisma Postgres region availability
• Multi-region deployment patterns
• Cache invalidation strategies at scale
• Direct connection use cases
• Prisma Studio for operations team
• Query optimization with AI recommendations

---

Alternative Consideration: CockroachDB

See Tech Stack Options for detailed comparison.

When to Consider CockroachDB

CockroachDB Advantages

• Distributed SQL: ACID compliance across regions
• No Split-Brain: Automatic leader election
• PostgreSQL Compatible: Works with Prisma (with adjustments)
• Global Tables: Data replicated everywhere
• Regional Tables: Data pinned to specific regions

CockroachDB Considerations

• Higher Cost: ~$300+/month minimum (dedicated)
• Latency Overhead: Cross-region consistency adds latency
• Operational Complexity: More infrastructure to manage
• Learning Curve: Different tuning from standard PostgreSQL

Migration Path

If CockroachDB becomes necessary:

1. Both databases are PostgreSQL-compatible
2. Prisma works with CockroachDB (with provider change)
3. Schema migration possible with tooling
4. Consider for Phase 2 (multi-region expansion)

// Future: CockroachDB configuration
datasource db {
  provider = "cockroachdb"  // Change from "postgresql"
  url      = env("DATABASE_URL")
}

Current Decision: Prisma Postgres for launch. Re-evaluate when multi-region becomes critical.