Database Schema Documentation¶

Overview¶

This document provides a comprehensive overview of the Eventify Next.js database schema, including visual diagrams and detailed model descriptions.

Table of Contents¶

Architecture Overview
Entity-Relationship Diagram
Core Domain Models
User & Authentication
Organization & Venue Management
Booking System
Event System
Enumerations

Architecture Overview¶

The database follows a multi-tenant architecture centered around:

User → Organization → Venue → Premise → PremiseSlot (Bookings)

Key Features: - Multi-tenant organization management - Venue and premise booking system - Event management with ticketing - Role-based access control (user, organization, admin) - Stripe payment integration - Internationalization support

Entity-Relationship Diagram¶

Core Booking Flow¶

erDiagram
    User ||--o{ Organization : owns
    User ||--o{ PremiseSlot : books
    Organization ||--o{ Venue : manages
    Organization ||--o{ PremiseSlot : "receives payments"
    Venue ||--o{ Premise : contains
    Venue ||--o{ Manager : "managed by"
    Premise ||--o{ PremiseSlot : "has bookings"
    Premise ||--o{ PremiseOpenHours : "has schedule"
    Premise ||--o{ PremiseResource : "has images"
    Premise ||--o{ PremiseDiscount : offers

    User {
        string id PK
        string email UK
        string role "UserRole"
        string stripeCustomerId UK
        boolean confirmed
        string createdAt
        string name
        string firstname
        string lastname
        string phoneNumber
    }

    Organization {
        string id PK
        string name
        string ownerId FK
        string NIP
        string IBAN
        string bankName
        string businessRegion "BusinessRegion"
    }

    Venue {
        string id PK
        string name
        string slug UK
        string organizationId FK
        string locationMapboxId
        boolean featureCCTV
        boolean featureParking
        int featureAge
    }

    Premise {
        string id PK
        string venueId FK
        string slug UK
        string name
        string priceMode "PremisePriceMode"
        float minimalPrice
        int capacity
        boolean withConfirmation
        string amenities
    }

    PremiseSlot {
        string id PK
        string premiseId FK
        string userId FK
        string organizationId FK
        string date
        string startTime
        string endTime
        string paymentIntentId
        string status "TicketIntentStatus"
        float amount
        string type "BookingType"
    }

    PremiseOpenHours {
        string id PK
        string premiseId FK
        string day "DayOfTheWeek"
        string openTime
        string closeTime
        float price
    }

    PremiseResource {
        string id PK
        string premiseId FK
        string url
        int width
        int height
    }

    PremiseDiscount {
        string id PK
        string premiseId FK
        int duration
        float discountPercentage
    }

    Manager {
        string id PK
        string venueId FK
        string firstname
        string lastname
        string email
        string phoneNumber
    }

Authentication & User Management¶

erDiagram
    User ||--o{ Account : "has auth providers"
    User ||--o{ Session : "has sessions"
    User ||--o| Speaker : "can be"
    User ||--o{ UserSocialLink : has
    User ||--o{ UserLikedEvents : likes

    User {
        string id PK
        string email UK
        string role "UserRole"
        boolean confirmed
    }

    Account {
        string id PK
        string userId FK
        string type "ProviderType"
        string provider
        string providerAccountId
        string access_token
        string refresh_token
        int expires_at
    }

    Session {
        string id PK
        string userId FK
        string sessionToken UK
        string expires
    }

    Speaker {
        string id PK
        string userId FK
        string createdAt
    }

    UserSocialLink {
        string id PK
        string userId FK
        string network "SocialNetwork"
        string url
    }

    UserLikedEvents {
        string userId FK
        string eventId FK
        string createdAt
    }

Event System¶

erDiagram
    User ||--o{ Event : creates
    Place ||--o{ Event : hosts
    Event ||--o{ EventComment : "has comments"
    Event ||--o{ EventResource : "has resources"
    Event ||--o{ TicketIntent : "has tickets"
    Event ||--o{ UsersOnEvent : "has participants"
    Event ||--o{ SpeakersOnEvent : "has speakers"
    Event ||--o{ UserLikedEvents : "liked by"
    Speaker ||--o{ SpeakersOnEvent : "speaks at"

    User {
        string id PK
        string name
        string email
    }

    Place {
        string id PK
        string name
        string location
        string deprecated "deprecated"
    }

    Event {
        string id PK
        string topic
        string slug UK
        string status "EventStatus"
        string initiatorId FK
        string placeId FK
        string starts
        float price
        string tags
    }

    EventComment {
        string id PK
        string eventId FK
        string userId FK
        float rating
        string message
    }

    EventResource {
        string id PK
        string eventId FK
        string url
        boolean isPoster
    }

    TicketIntent {
        string id PK
        string eventId FK
        string userId FK
        string status "TicketIntentStatus"
    }

    UsersOnEvent {
        string userId FK
        string eventId FK
        string joinedAt
    }

    SpeakersOnEvent {
        string speakerId FK
        string eventId FK
        string joinedAt
    }

    Speaker {
        string id PK
        string userId FK
    }

Organization Structure¶

erDiagram
    Organization ||--o{ Address : "has addresses"
    Organization ||--o{ SocialLink : "has social links"
    Organization ||--o{ OrganizationResourse : "has resources"
    Organization ||--o{ Venue : owns
    Organization ||..o{ Place : "owns (deprecated)"

    Organization {
        string id PK
        string name
        string ownerId FK
        string IBAN
        string NIP
        string bankName
        BusinessRegion businessRegion
    }

    Address {
        string id PK
        string organizationId FK
        string country
        string city
        string street
        string building
        string zipCode
    }

    SocialLink {
        string id PK
        string organizationId FK
        SocialNetwork network
        string url
    }

    OrganizationResourse {
        string id PK
        string organizationId FK
        string url
        int width
        int height
    }

    Venue {
        string id PK
        string organizationId FK
        string name
        string slug
    }

    Place {
        string id PK
        string organizationId FK
        string name
        string deprecated "deprecated"
    }

Premise Details & Internationalization¶

erDiagram
    Premise ||--o{ PremiseInformation : "has i18n"
    Premise ||--o{ PremiseOpenHours : "has schedule"
    Premise ||--o{ PremiseDiscount : offers
    PremiseOpenHours ||..o{ PremisePricing : "has pricing (deprecated)"

    Premise {
        string id PK
        string venueId FK
        string name
        string slug
        string priceMode "PremisePriceMode"
    }

    PremiseInformation {
        string id PK
        string premiseId FK
        string locale
        string description
    }

    PremiseOpenHours {
        string id PK
        string premiseId FK
        string day "DayOfTheWeek"
        string openTime
        string closeTime
        float price
    }

    PremisePricing {
        string id PK
        string premiseOpenHoursId FK
        float priceForHour
        string startTime
        string endTime
        string deprecated "deprecated"
    }

    PremiseDiscount {
        string id PK
        string premiseId FK
        int duration
        float discountPercentage
    }

Core Domain Models¶

User Model¶

Purpose: Central entity for all user types (customers, organization owners, admins)

Key Fields: - role: UserRole (user, admin, organization) - stripeCustomerId: Integration with Stripe payments - confirmed: Email verification status - isRegisteredFromUntaxedForm: Tax-free registration tracking

Relationships: - Owns organizations (one-to-many) - Creates events (one-to-many) - Books premises via PremiseSlot (one-to-many) - Can be a speaker (one-to-one) - Has authentication accounts (one-to-many)

Special Features: - Supports multiple OAuth providers (Google, Facebook, VK, etc.) - Rich user profile with social links, interests, and professional info - Transaction counting for analytics

Organization Model¶

Purpose: Multi-tenant business entity that owns venues and receives payments

Key Fields: - NIP: Tax identification number (Poland) - IBAN: Bank account for payments - businessRegion: Geographic region (currently EMEA only) - foundationDate: Year of establishment

Relationships: - Owned by one User - Owns multiple Venues - Receives PremiseSlot payments - Has multiple Addresses (for multi-location businesses)

Business Logic: - Each organization manages its own venues and premises - Payment routing through organizationId in PremiseSlot - Social links for business presence

Venue Model¶

Purpose: Physical location containing multiple bookable premises

Key Fields: - slug: URL-friendly unique identifier - locationMapboxId: Mapbox integration for maps - featureCCTV, featureParking, featureAge: Venue features - locationTutorial: Navigation instructions

Relationships: - Belongs to one Organization - Contains multiple Premises - Has multiple Managers (contact persons) - Supports i18n via VenueInformation

Features: - Internationalization support (English/Polish) - Manager contacts for venue operations - Integration with Mapbox for location services

Premise Model¶

Purpose: Individual bookable space within a venue (e.g., conference room, sports court)

Key Fields: - slug: URL-friendly unique identifier - priceMode: PremisePriceMode (arbitrary or donation) - minimalPrice: Base pricing - withConfirmation: Requires manual approval - minimalSlotsToBook: Minimum booking duration - amenities: JSON array of features (WiFi, projector, etc.)

Relationships: - Belongs to one Venue - Has multiple PremiseSlots (bookings) - Has schedule via PremiseOpenHours - Has resources (images/videos) - Offers discounts via PremiseDiscount

Pricing Strategy: - Hourly pricing set in PremiseOpenHours - Different prices per day/time slot - Volume discounts available - Support for donation-based pricing

PremiseSlot Model¶

Purpose: Represents a booking/reservation of a premise for a specific time period

Key Fields: - paymentIntentId: Stripe Payment Intent ID - status: TicketIntentStatus (failed, progress, succeed, canceled) - amount: Total price paid - discountAmount: Applied discount - type: BookingType (via_website, blocked_by_admin, needs_confirmation)

Relationships: - Belongs to Premise - Booked by User - Payment goes to Organization

Booking Flow: 1. User selects time slots → creates PremiseSlot with status=progress 2. Stripe PaymentIntent created → paymentIntentId stored 3. Payment completed → status=succeed 4. Organization receives payment notification

Business Rules: - Multiple consecutive slots can be booked - Discounts applied based on duration - Tax calculation via calculatePriceWithTax() - Email notifications on confirmation

User & Authentication¶

Authentication Flow¶

OAuth Providers: Google, Facebook, VK
Email Verification: via VerificationRequest model
Session Management: JWT tokens via NextAuth.js
Account Linking: Multiple providers per user via Account model

Session Enhancement¶

// Session includes:
{
  user: {
    id: string,
    email: string,
    role: UserRole,
    organizationId?: string  // For organization owners
  }
}

Role-Based Access¶

user: Standard customer, can book premises
organization: Venue owner, manages venues/premises
admin: Platform administrator, full access

Organization & Venue Management¶

Venue Hierarchy¶

Organization
└── Venue 1
    ├── Premise 1 (Conference Room A)
    │   ├── PremiseOpenHours (Mon-Fri schedule)
    │   ├── PremiseResources (images)
    │   └── PremiseSlots (bookings)
    └── Premise 2 (Sports Court)
        └── ...

Manager System¶

Venues have dedicated Managers for operations: - Contact information (email, phone) - Per-venue assignment - Used for booking confirmations and support

Internationalization¶

Models with i18n support: - VenueInformation: Translated venue descriptions - PremiseInformation: Translated premise details - Locale-based content delivery (en/pl)

Booking System¶

Booking Workflow¶

sequenceDiagram
    participant User
    participant Calendar
    participant Server as Server Action
    participant Stripe
    participant Database
    participant Email

    User->>Calendar: Select time slots
    Calendar->>Server: createPremiseSlotsIntent()
    Server->>Database: Check availability
    Database-->>Server: Available slots
    Server->>Stripe: Create PaymentIntent
    Stripe-->>Server: paymentIntentId
    Server->>Database: Create PremiseSlot<br/>(status=progress)
    Database-->>Server: Slot created
    Server-->>User: Return payment details
    User->>Stripe: Complete payment
    Stripe->>Server: Webhook<br/>(payment.succeeded)
    Server->>Database: Update status=succeed
    Server->>Email: Send confirmation
    Email-->>User: Booking confirmed

Pricing Calculation¶

Base Price: - Set per hour in PremiseOpenHours.price - Different prices per day/time slot

Discounts: - Volume discounts via PremiseDiscount - Applied based on booking duration - Example: 10% off for 3+ hours

Tax Calculation: - VAT/tax added via calculatePriceWithTax() - Region-specific tax rates - Final amount stored in PremiseSlot.amount

Booking Types¶

enum BookingType {
  blocked_by_admin      // Admin-blocked time slot
  via_website          // Standard online booking
  needs_confirmation   // Requires manual approval
  ready_for_payment    // Approved, awaiting payment
}

Payment Integration¶

Stripe Integration: - User.stripeCustomerId: Customer record - PremiseSlot.paymentIntentId: Payment tracking - Webhook handling for async updates - Tax calculation and invoicing

Event System¶

Event Model (Legacy/Secondary Feature)¶

Note: Events are a secondary feature; premise booking is primary.

Purpose: Community events hosted at venues

Key Features: - Event creation with moderation workflow - Ticket sales via TicketIntent - Speaker management - Event resources (posters, images) - User participation tracking - Rating and comment system

Event Moderation¶

unmoderated → (admin review) → active/private

EventStatus.unmoderated: Pending review
EventStatus.active: Public and visible
EventStatus.private: Private event

Ticket System¶

Similar to premise bookings but for events: - TicketIntent: Event ticket purchase - Stripe integration - Status tracking (progress → succeed/failed)

Deprecated Models¶

Place Model¶

Status: ⚠️ DEPRECATED - Use Premise instead

The Place model was the original venue/premise system. It has been replaced by the Venue → Premise hierarchy for better organization.

Migration Path: - Old: Place (single entity) - New: Venue (location) → Premise (bookable space)

Note: Still exists in schema for backwards compatibility, but new features should use Venue and Premise.

PremisePricing Model¶

Status: ⚠️ DEPRECATED

Pricing is now set directly in PremiseOpenHours.price field.

Enumerations¶

UserRole¶

enum UserRole {
  user          // Standard customer
  admin         // Platform administrator
  organization  // Venue owner/manager
}

UserGender¶

enum UserGender {
  male
  female
  notSpecified
}

ProviderType¶

enum ProviderType {
  oauth         // OAuth providers (Google, Facebook, etc.)
  email         // Email/password
  credentials   // Custom credentials
}

EventStatus¶

enum EventStatus {
  unmoderated   // Pending moderation
  active        // Live and public
  private       // Private event
}

TicketIntentStatus¶

enum TicketIntentStatus {
  failed        // Payment failed
  progress      // Payment in progress
  succeed       // Payment successful
  canceled      // Canceled by user/system
}

DayOfTheWeek¶

enum DayOfTheWeek {
  MONDAY
  TUESDAY
  WEDNESDAY
  THURSDAY
  FRIDAY
  SATURDAY
  SUNDAY
}

BusinessRegion¶

enum BusinessRegion {
  AMER  // Americas
  APAC  // Asia-Pacific
  EMEA  // Europe, Middle East, Africa (currently only this is used)
}

SocialNetwork¶

enum SocialNetwork {
  linkedin
  instagram
  facebook
  threads
}

PremisePriceMode¶

enum PremisePriceMode {
  arbitrary     // Fixed/variable pricing
  donation      // Pay-what-you-want
}

BookingType¶

enum BookingType {
  blocked_by_admin      // Admin-blocked slot
  via_website          // Online booking
  needs_confirmation   // Pending approval
  ready_for_payment    // Approved, awaiting payment
}

Database Technology¶

Current State: - Provider: SQLite (Cloudflare D1) - ORM: Prisma - Migration: From PostgreSQL to D1 (branch: feature/switch-prisma-to-d1)

Cloudflare D1 Features: - Distributed SQLite - Edge computing integration - Low-latency queries - Automatic replication

Migration Tools: - scripts/migrate-existing-db.ts: PostgreSQL → D1 - scripts/seed-d1.ts: D1 database seeding - scripts/generate-d1-migration.ts: Migration generation

Indexing Strategy¶

Key Indexes:

@@index([organizationId])  // On PremiseSlot for payment queries
@@unique([provider, providerAccountId])  // On Account for auth
@@unique([identifier, token])  // On VerificationRequest
@@id([userId, eventId])  // Composite keys for many-to-many

Performance Considerations: - Foreign key indexes for joins - Unique constraints on slugs for URL routing - Composite indexes on relationship tables

Data Consistency Rules¶

Cascade Deletes¶

User Deletion: - Cascades to: Accounts, Sessions, UserSocialLinks, UserLikedEvents - Does NOT cascade to: Organizations, Events (preserves business data)

Organization Deletion: - Cascades to: Addresses, SocialLinks

Venue Deletion: - Cascades to: VenueInformation

Premise Deletion: - Cascades to: PremiseInformation

Soft Deletes¶

No soft delete implementation currently. Consider implementing for: - User accounts (GDPR compliance) - Bookings (audit trail) - Organizations (business continuity)

Query Patterns¶

Common Queries¶

Get user bookings:

const bookings = await prisma.premiseSlot.findMany({
  where: { userId },
  include: {
    premise: {
      include: {
        venue: {
          include: { organization: true }
        }
      }
    }
  }
});

Get venue with all premises:

const venue = await prisma.venue.findUnique({
  where: { slug },
  include: {
    premises: {
      include: {
        openHours: true,
        resources: true,
        discounts: true
      }
    },
    organization: true,
    manager: true
  }
});

Check slot availability:

const existingSlots = await prisma.premiseSlot.findMany({
  where: {
    premiseId,
    date: bookingDate,
    status: { not: 'canceled' },
    OR: [
      { startTime: { lte: requestedStart }, endTime: { gt: requestedStart } },
      { startTime: { lt: requestedEnd }, endTime: { gte: requestedEnd } }
    ]
  }
});

Security Considerations¶

Data Protection¶

Email Uniqueness: Enforced at database level
Stripe IDs: Unique constraints prevent duplication
Payment Intents: One-time use, tracked by ID
Session Tokens: Unique and expiring

Access Control¶

Row-Level Security: Not implemented (application-level)
Role Checks: Performed in server actions
Organization Isolation: Queries filtered by organizationId

Sensitive Data¶

Encrypted/Protected: - Stripe customer IDs - Payment intent IDs - OAuth tokens (access_token, refresh_token)

Plaintext (consider encrypting): - IBAN numbers - Phone numbers - Personal addresses

Future Considerations¶

Potential Improvements¶

Soft Deletes:
Add deletedAt timestamp
Filter queries by IS NULL
GDPR compliance
Audit Trail:
Track booking modifications
Log payment status changes
User action history
Full-Text Search:
Venue/premise search
Currently relies on external search
Consider SQLite FTS extension
Multi-Currency:
Currently assumes single currency
Add currency field to pricing
Stripe supports multi-currency
Recurring Bookings:
Weekly/monthly booking patterns
Bulk slot creation
Subscription support
Overbooking Protection:
Transaction-level locks
Optimistic concurrency control
Real-time availability updates

Development Commands¶

# Generate Prisma client
npx prisma generate

# Create migration
npx prisma migrate dev --name migration_name

# Apply migrations (production)
npx prisma migrate deploy

# Seed database
npm run seed

# Open Prisma Studio (database GUI)
npx prisma studio

# Generate D1 migration (Cloudflare)
npm run cf-typegen

Conclusion¶

This database schema supports a sophisticated venue booking platform with: - ✅ Multi-tenant organization management - ✅ Flexible pricing and discount system - ✅ Stripe payment integration - ✅ Internationalization support - ✅ Event management (secondary feature) - ✅ Role-based access control - ✅ Cloudflare D1 compatibility

The schema is designed for edge computing with Cloudflare infrastructure, providing low-latency access globally while maintaining data consistency and business logic integrity.

Database Schema Documentation¶

Overview¶

Table of Contents¶

Architecture Overview¶

Entity-Relationship Diagram¶

Core Booking Flow¶

Authentication & User Management¶

Event System¶

Organization Structure¶

Premise Details & Internationalization¶

Core Domain Models¶

User Model¶

Organization Model¶

Venue Model¶

Premise Model¶

PremiseSlot Model¶

User & Authentication¶

Authentication Flow¶

Session Enhancement¶

Role-Based Access¶

Organization & Venue Management¶

Venue Hierarchy¶

Manager System¶

Internationalization¶

Booking System¶

Booking Workflow¶

Pricing Calculation¶

Booking Types¶

Payment Integration¶

Event System¶

Event Model (Legacy/Secondary Feature)¶

Event Moderation¶

Ticket System¶

Deprecated Models¶

Place Model¶

PremisePricing Model¶

Enumerations¶

UserRole¶

UserGender¶

ProviderType¶

EventStatus¶

TicketIntentStatus¶

DayOfTheWeek¶

BusinessRegion¶

SocialNetwork¶

PremisePriceMode¶

BookingType¶

Database Technology¶

Indexing Strategy¶

Data Consistency Rules¶

Cascade Deletes¶

Soft Deletes¶

Query Patterns¶

Common Queries¶

Security Considerations¶

Data Protection¶

Access Control¶

Sensitive Data¶

Future Considerations¶

Potential Improvements¶

Development Commands¶

Related Documentation¶

Conclusion¶