moyosapp_beta.0.0.3.3_beta/docs/DATABASE_CONFIG.md

Database Configuration Reference

Current Setup

Database Type

• Provider: Supabase (PostgreSQL)
• ORM: Prisma
• Connection: Via DATABASE_URL environment variable pointing to Supabase

Schema Location

• Prisma Schema: prisma/schema.prisma
• Migrations: prisma/migrations/
• Seed Script: prisma/seed.ts

Database Models

Core Models

1. Guest - Guest information and RSVP

   • Fields: name, inviteCode, email, phone, maxPax, isAttending, etc.
   • Relations: Song, GuestbookEntry, GalleryPhoto, Tribute, Table

2. Song - Song requests from guests

   • Fields: title, artist, votes, previewUrl, trackUrl
   • Relations: Guest, SongVote

3. GuestbookEntry - Guestbook messages

   • Fields: message, photo, audioUrl, videoUrl
   • Relations: Guest

4. GalleryPhoto - Photo gallery uploads

   • Fields: url, thumbnailUrl, caption, likes
   • Relations: Guest, PhotoComment

5. Table - Seating arrangements

   • Fields: name, capacity
   • Relations: Guest

6. Admin - Admin users

   • Fields: username, password, email, totpSecret, totpEnabled

7. Tribute - Tributes/dedications

   • Fields: guestName, dedication
   • Relations: Guest (optional)

8. Reminder - Email/SMS reminders

   • Fields: name, type, trigger, targetAudience, enabled

9. ABTest - A/B testing

   • Fields: name, type, status, variantA, variantB, splitRatio

10. CouplesPlaylist - Couple's playlist

    • Fields: title, artist, trackUrl, previewUrl, order, revealAt

Environment Variables

Required

# Supabase PostgreSQL connection string
# Get from Supabase Dashboard → Settings → Database → Connection String
# Use "Connection Pooling" mode for RUNTIME_DATABASE_URL
DATABASE_URL=postgresql://postgres:[PASSWORD]@[HOST]:5432/postgres
RUNTIME_DATABASE_URL=postgresql://postgres:[PASSWORD]@[HOST]:6543/postgres?pgbouncer=true  # Connection pooling

Optional

SHADOW_DATABASE_URL=postgresql://postgres:[PASSWORD]@[HOST]:5432/postgres  # For migrations (direct connection)

Migration Commands

# Generate Prisma client
npx prisma generate

# Create migration
npx prisma migrate dev --name migration_name

# Deploy migrations (production)
npx prisma migrate deploy

# Push schema changes (development)
npx prisma db push

# Seed database
npm run db:seed

# Open Prisma Studio
npm run db:studio

Backup & Restore

See COOLIFY_DATABASE_MIGRATION.md for detailed instructions.

Quick Backup

./scripts/backup-database.sh

Quick Restore

./scripts/restore-database.sh backups/latest

Supabase Configuration

Getting Connection Strings

1. Go to Supabase Dashboard
2. Select your project
3. Go to Settings → Database
4. Copy connection strings:
   • Direct connection (for migrations): Use port 5432
   • Connection pooling (for runtime): Use port 6543 with ?pgbouncer=true

App Environment Variables

# Direct connection (for migrations)
DATABASE_URL=postgresql://postgres:[PASSWORD]@db.[PROJECT_REF].supabase.co:5432/postgres

# Connection pooling (for runtime - recommended)
RUNTIME_DATABASE_URL=postgresql://postgres:[PASSWORD]@db.[PROJECT_REF].supabase.co:6543/postgres?pgbouncer=true

# Optional: Shadow database for migrations (if using separate database)
SHADOW_DATABASE_URL=postgresql://postgres:[PASSWORD]@db.[PROJECT_REF].supabase.co:5432/postgres

Important:

• Replace [PASSWORD] with your Supabase database password
• Replace [PROJECT_REF] with your Supabase project reference ID
• Use connection pooling (RUNTIME_DATABASE_URL) for production to avoid connection limits

Seed Data

Seed data comes from:

• data/guests.csv - Guest list
• prisma/seed.ts - Seed script

Seed includes:

• Guests from CSV
• Admin user (username: admin)
• Sample tables (Table 1-5)

Connection Pooling

For production, use RUNTIME_DATABASE_URL with a connection pooler (e.g., PgBouncer):

• Direct connection: Port 5432
• Pooled connection: Port 6543 (or configured port)

Security Notes

1. Use strong database passwords
2. Restrict database access to application only
3. Use connection pooling in production
4. Enable SSL for production connections
5. Never commit .env files with real credentials