moyosapp_beta.0.0.3/docs/DATABASE_CONFIG.md

Database Configuration Reference

Current Setup

Database Type

• Provider: PostgreSQL
• ORM: Prisma
• Connection: Via DATABASE_URL environment variable

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

DATABASE_URL=postgresql://user:password@host:port/database
RUNTIME_DATABASE_URL=postgresql://user:password@host:port/database  # For connection pooling

Optional

SHADOW_DATABASE_URL=postgresql://user:password@host:port/database  # For migrations

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

Coolify Configuration

Database Service

• Create PostgreSQL service in Coolify
• Note container name (e.g., postgres-CONTAINER_ID)

App Environment Variables

DATABASE_URL=postgresql://postgres:PASSWORD@postgres-CONTAINER_ID:5432/postgres
RUNTIME_DATABASE_URL=postgresql://postgres:PASSWORD@postgres-CONTAINER_ID:5432/postgres

Important: Replace CONTAINER_ID with actual container ID from Coolify.

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