Database Overview

Y3NKO uses PostgreSQL via Supabase with Prisma as the ORM.

Architecture

┌─────────────────────────────────────────────────────────────────┐
│                       APPLICATION                               │
│                                                                 │
│  Next.js Server Components & Server Actions                     │
└─────────────────────────────────────────────────────────────────┘
                              │
                              ▼
┌─────────────────────────────────────────────────────────────────┐
│                       PRISMA ORM                                │
│                                                                 │
│  - Type-safe queries                                            │
│  - Auto-generated types                                         │
│  - Migration management                                         │
└─────────────────────────────────────────────────────────────────┘
                              │
                              ▼
┌─────────────────────────────────────────────────────────────────┐
│                    SUPABASE (PostgreSQL)                        │
│                                                                 │
│  - Managed PostgreSQL 15                                        │
│  - Connection pooling (PgBouncer)                               │
│  - Automatic backups                                            │
└─────────────────────────────────────────────────────────────────┘

Connection Setup

Connection Strings

Supabase provides two connection strings:

# Pooled connection (port 6543) - for application queries
DATABASE_URL="postgresql://postgres.[ref]:[password]@aws-0-[region].pooler.supabase.com:6543/postgres?pgbouncer=true"

# Direct connection (port 5432) - for migrations
DIRECT_URL="postgresql://postgres.[ref]:[password]@aws-0-[region].pooler.supabase.com:5432/postgres"

Prisma Configuration

// prisma/schema.prisma
datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")
  directUrl = env("DIRECT_URL")
}
 
generator client {
  provider = "prisma-client-js"
}

Prisma Client

// lib/db.ts
import { PrismaClient } from '@prisma/client'
 
const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined
}
 
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
}

The singleton pattern prevents multiple Prisma Client instances during hot reload in development.

Entity Relationships

┌──────────────┐     ┌──────────────┐     ┌──────────────┐
│    User      │────<│   Booking    │>────│   Listing    │
└──────────────┘     └──────────────┘     └──────────────┘
       │                    │                    │
       │                    │                    │
       ▼                    ▼                    ▼
┌──────────────┐     ┌──────────────┐     ┌──────────────┐
│   Profile    │     │   Payment    │     │   Image      │
└──────────────┘     └──────────────┘     └──────────────┘
       │                                         │
       │                                         ▼
       ▼                                  ┌──────────────┐
┌──────────────┐                          │   Amenity    │
│   Favorite   │                          └──────────────┘
└──────────────┘

Core Models

Model	Purpose
User	User accounts with role
Profile	User profile details
Listing	Accommodations and activities
ListingImage	Listing photos
Amenity	Available amenities
Booking	Reservation records
Payment	Payment transactions
Favorite	Saved listings
Destination	Featured destinations

Key Commands

# Generate Prisma Client after schema changes
npx prisma generate
 
# Create and apply migrations
npx prisma migrate dev --name description
 
# Push schema changes without migration (dev only)
npx prisma db push
 
# Reset database (deletes all data)
npx prisma migrate reset
 
# Open visual database browser
npx prisma studio
 
# Seed the database
npx prisma db seed

TypeScript Integration

Prisma generates types from your schema:

import { User, Listing, Booking } from '@prisma/client'
 
// Include relations
import { Prisma } from '@prisma/client'
 
type ListingWithImages = Prisma.ListingGetPayload<{
  include: { images: true }
}>
 
type BookingWithDetails = Prisma.BookingGetPayload<{
  include: {
    user: { include: { profile: true } }
    listing: true
    payments: true
  }
}>

Performance Tips

Select only needed fields:

await prisma.listing.findMany({
  select: { id: true, title: true, slug: true }
})

Use includes wisely:

// Only include what you need
await prisma.listing.findUnique({
  where: { id },
  include: {
    images: { where: { isPrimary: true }, take: 1 }
  }
})

Batch operations:

await prisma.$transaction([
  prisma.booking.create({ data: bookingData }),
  prisma.payment.create({ data: paymentData })
])

Use indexes (defined in schema):

@@index([region, city])
@@index([status])

Troubleshooting

Connection Issues

If you see connection errors:

Check DATABASE_URL is correct
Verify Supabase project is not paused
Check IP allowlist in Supabase settings

Migration Issues

# If migrations are out of sync
npx prisma migrate resolve --applied "migration_name"
 
# Recreate migration history (dev only)
npx prisma migrate reset

Type Errors

# Regenerate Prisma Client
npx prisma generate

Accessibility Schema Reference