Skip to content

Open-source event management and ticket selling platform β€” perfect for concerts, conferences, and everything in between 🎟️ If you find this project helpful, please consider giving us a star ⭐️

19.0 KB
Raw Blame

Database Schema Architecture

Overview

Hi.Events uses PostgreSQL with a multi-tenant architecture built around accounts. The database schema consists of approximately 57 migration files with core schema defined in database/migrations/schema.sql.

Core Entity Hierarchy

graph TB
    A[Account] --> B[User]
    A --> C[Organizer]
    C --> D[Event]
    D --> E[Product/Ticket]
    D --> F[Order]
    F --> G[Attendee]
    E --> H[Product Price]
    F --> I[Order Item]
    D --> J[Questions]
    D --> K[Promo Codes]
    D --> L[Capacity Assignments]

Multi-Tenancy Architecture

Account-Based Tenancy

Account (Tenant)
β”œβ”€β”€ Users (many-to-many via account_users)
β”‚   └── Role-based permissions
β”œβ”€β”€ Account Configuration
β”‚   └── Application fees
β”œβ”€β”€ Taxes and Fees (account-level)
β”œβ”€β”€ Email Templates (account-level)
β”œβ”€β”€ Organizers
β”‚   β”œβ”€β”€ Organizer Settings
β”‚   β”œβ”€β”€ Email Templates (organizer-level)
β”‚   └── Events
β”‚       β”œβ”€β”€ Event Settings
β”‚       β”œβ”€β”€ Event Statistics
β”‚       β”œβ”€β”€ Email Templates (event-level)
β”‚       β”œβ”€β”€ Products (Tickets)
β”‚       β”œβ”€β”€ Orders
β”‚       β”œβ”€β”€ Attendees
β”‚       β”œβ”€β”€ Questions
β”‚       β”œβ”€β”€ Promo Codes
β”‚       └── Webhooks

Key Principles

  1. Data Isolation: Each account's data is isolated via account_id foreign key
  2. Hierarchical Structure: Account β†’ Organizer β†’ Event β†’ Products/Orders
  3. User Access: Users can belong to multiple accounts with different roles
  4. Cascade Deletes: Parent deletion cascades to children

Core Entities

1. Account & User Management

accounts

Top-level tenant entity.

Key Fields:

  • id - Primary key
  • name - Account name
  • email - Account contact email
  • currency_code - Default currency
  • timezone - Default timezone
  • stripe_account_id - Stripe Connect ID
  • stripe_connect_setup_complete - Onboarding status
  • account_verified_at - Verification timestamp

Model: app/Models/Account.php

users

User authentication and identity.

Key Fields:

  • id - Primary key
  • email - Unique email (authentication)
  • password - Hashed password
  • first_name, last_name - User name
  • timezone - User timezone
  • email_verified_at - Email verification
  • status - User status (ACTIVE/INACTIVE)

Features:

  • JWT authentication
  • Soft deletes
  • Can belong to multiple accounts

Model: app/Models/User.php

account_users (Pivot)

Many-to-many relationship with roles.

Key Fields:

  • account_id - Foreign key to accounts
  • user_id - Foreign key to users
  • role - User role (ADMIN, ORGANIZER, etc.)
  • is_account_owner - Owner flag
  • invited_by_user_id - Invitation tracking
  • status - Invitation status
  • last_login_at - Login tracking

Features: RBAC (Role-Based Access Control)

account_configuration

Account-level configuration for fees.

Key Fields:

  • id - Primary key
  • name - Configuration name
  • is_system_default - System default flag
  • application_fees - JSONB fee structure

Migration: database/migrations/2025_02_16_163546_create_account_configuration.php

2. Event Management

organizers

Event organizers (one account can have multiple).

Key Fields:

  • id - Primary key
  • account_id - Foreign key (CASCADE delete)
  • name - Organizer name
  • email, phone, website - Contact info
  • currency - Default currency
  • timezone - Default timezone

Model: app/Models/Organizer.php

events

Core event entity.

Key Fields:

  • id - Primary key
  • account_id - Foreign key (CASCADE delete)
  • organizer_id - Foreign key
  • user_id - Creator
  • title, description - Event details
  • start_date, end_date - Event dates
  • status - EventStatus enum (DRAFT, LIVE, ARCHIVED)
  • location, location_details - JSONB location data
  • currency, timezone - Event-specific settings
  • short_id - URL-friendly ID
  • category - Event category

Model: app/Models/Event.php

Relationships:

  • Has many Products, Orders, Attendees, Questions
  • Has one EventSettings, EventStatistics
  • Belongs to Account, Organizer, User

event_settings

Detailed event configuration.

Key Fields:

  • event_id - Primary key (one-to-one)
  • pre_checkout_message, post_checkout_message - Custom messages
  • support_email - Support contact
  • require_attendee_details - Attendee form requirement
  • order_timeout_in_minutes - Reservation timeout
  • homepage_*_color - Branding colors
  • seo_* - SEO metadata
  • ticket_design_settings - JSONB ticket design

Features: Complete control over event UX

3. Products & Pricing

products (formerly tickets)

Tickets/products for events.

Key Fields:

  • id - Primary key
  • event_id - Foreign key (CASCADE delete)
  • title, description - Product details
  • type - Product type
  • product_type - TICKET or OTHER
  • sale_start_date, sale_end_date - Sale window
  • min_per_order, max_per_order - Purchase limits
  • product_category_id - Category
  • order - Display order

Model: app/Models/Product.php

Migration: Renamed from tickets in database/migrations/2024_09_20_032323_rename_tickets_to_products.php

Relationships:

  • Belongs to Event, ProductCategory
  • Has many ProductPrices
  • Many-to-many with TaxesAndFees, Questions, CapacityAssignments, CheckInLists

product_prices

Multiple pricing tiers per product.

Key Fields:

  • id - Primary key
  • product_id - Foreign key
  • price - Price amount
  • label - Price tier name (e.g., "Early Bird", "VIP")
  • sale_start_date, sale_end_date - Time-based pricing
  • initial_quantity_available - Starting quantity
  • quantity_sold - Sold count
  • quantity_available - Remaining
  • order - Display order

Features:

  • Time-based pricing (early bird, etc.)
  • Inventory tracking per tier

product_categories

Organize products into categories.

Key Fields:

  • id - Primary key
  • event_id - Foreign key
  • name, description - Category details
  • order - Display order

taxes_and_fees

Account-level tax and fee definitions.

Key Fields:

  • id - Primary key
  • account_id - Foreign key
  • name - Tax/fee name
  • type - TAX or FEE enum
  • calculation_type - FIXED or PERCENTAGE
  • rate - Amount/percentage
  • is_active - Active flag
  • is_default - Auto-apply to new products

Features: Automatically applies default taxes to new tickets

4. Orders & Transactions

orders

Customer purchases.

Key Fields:

  • id - Primary key
  • short_id, public_id - URL-friendly IDs
  • event_id - Foreign key
  • first_name, last_name, email - Customer info
  • status - OrderStatus enum (RESERVED, COMPLETED, etc.)
  • payment_status - Payment state
  • refund_status - Refund state
  • payment_gateway, payment_provider - Payment method
  • currency - Order currency
  • total_before_additions - Subtotal
  • total_tax, total_fee - Tax/fee totals
  • total_gross - Grand total
  • total_refunded - Refund amount
  • promo_code_id - Applied promo code
  • affiliate_id - Attribution
  • address - JSONB address
  • taxes_and_fees_rollup - JSONB tax breakdown
  • point_in_time_data - JSONB pricing snapshot
  • reserved_until - Reservation expiry
  • locale - Order language

Model: app/Models/Order.php

Features:

  • GIN trigram indexes on name/email for search
  • Point-in-time pricing snapshot
  • Order timeout/reservation system

Relationships:

  • Belongs to Event, PromoCode, Affiliate
  • Has many OrderItems, Attendees, Invoices
  • Has one StripePayment, OrderApplicationFee

order_items

Line items in orders.

Key Fields:

  • id - Primary key
  • order_id - Foreign key
  • product_id - Foreign key
  • product_price_id - Specific price tier
  • quantity - Item count
  • price - Unit price
  • price_before_discount - Pre-discount price
  • total_before_additions - Subtotal
  • total_tax, total_service_fee - Additions
  • total_gross - Line total
  • taxes_and_fees_rollup - JSONB breakdown
  • product_type - TICKET or OTHER

Model: app/Models/OrderItem.php

Features: No timestamps (immutable once created)

stripe_payments

Stripe payment tracking.

Key Fields:

  • id - Primary key
  • order_id - Foreign key
  • payment_intent_id - Stripe payment intent
  • charge_id - Stripe charge
  • payment_method_id - Payment method
  • amount_received - Actual amount
  • connected_account_id - Stripe Connect account
  • last_error - JSON error details
  • platform - Stripe platform used

Model: app/Models/StripePayment.php

invoices

Invoice generation for orders.

Key Fields:

  • id - Primary key
  • order_id - Foreign key
  • account_id - Foreign key
  • invoice_number - Unique invoice number
  • issue_date, due_date - Invoice dates
  • total_amount - Invoice total
  • status - Invoice status
  • items - JSONB line items
  • taxes_and_fees - JSONB tax breakdown
  • uuid - Public UUID
  • payment_terms, notes - Invoice details

Model: app/Models/Invoice.php

Migration: database/migrations/2025_01_03_010511_create_invoices_table.php

5. Attendees & Check-Ins

attendees

Individual ticket holders.

Key Fields:

  • id - Primary key
  • short_id, public_id - URL-friendly IDs
  • order_id - Foreign key (CASCADE delete)
  • product_id - Foreign key (CASCADE delete)
  • product_price_id - Price tier
  • event_id - Foreign key (CASCADE delete)
  • first_name, last_name, email - Attendee info
  • status - AttendeeStatus enum
  • locale - Attendee language
  • notes - Internal notes

Model: app/Models/Attendee.php

Features:

  • GIN trigram indexes for search
  • Soft deletes

Relationships:

  • Belongs to Order, Product, Event
  • Has many AttendeeCheckIns, QuestionAnswers

check_in_lists

Multiple check-in lists per event.

Key Fields:

  • id - Primary key
  • event_id - Foreign key
  • short_id - URL-friendly ID
  • name, description - List details
  • activates_at, expires_at - Active window

Model: app/Models/CheckInList.php

Migration: database/migrations/2024_08_08_032637_create_check_in_lists_tables.php

Features: Supports multiple entry points or sessions

attendee_check_ins

Track individual check-in events.

Key Fields:

  • id - Primary key
  • short_id - URL-friendly ID
  • check_in_list_id - Foreign key
  • product_id, attendee_id, order_id, event_id - References
  • ip_address - Check-in source

Model: app/Models/AttendeeCheckIn.php

Features: Supports multiple check-ins per attendee

6. Questions & Answers

questions

Custom form questions.

Key Fields:

  • id - Primary key
  • event_id - Foreign key
  • title - Question text
  • type - QuestionTypeEnum (TEXT, CHECKBOX, etc.)
  • required - Required flag
  • options - JSONB options for multiple choice
  • belongs_to - ORDER or ATTENDEE level
  • order - Display order
  • is_hidden - Hidden flag
  • description - Help text

Model: app/Models/Question.php

Relationships: Many-to-many with Products

question_answers

Store answers to questions.

Key Fields:

  • id - Primary key
  • question_id - Foreign key
  • order_id - Order-level answer
  • attendee_id - Attendee-level answer
  • product_id - Product context
  • answer - JSONB answer data

Model: app/Models/QuestionAnswer.php

question_and_answer_views (View)

Materialized view for efficient querying.

Model: app/Models/QuestionAndAnswerView.php

Purpose: Pre-joined data for question/answer reporting

7. Capacity Management

capacity_assignments

Manage capacity limits.

Key Fields:

  • id - Primary key
  • event_id - Foreign key
  • name - Capacity name
  • capacity - Total capacity
  • used_capacity - Used amount
  • applies_to - EVENT or PRODUCTS
  • status - Active status

Model: app/Models/CapacityAssignment.php

Migration: database/migrations/2024_07_14_031511_create_capacity_assignments_and_associated_tables.php

Features: Share capacity across multiple products

8. Promotions & Marketing

promo_codes

Discount codes.

Key Fields:

  • id - Primary key
  • event_id - Foreign key
  • code - Promo code (indexed)
  • discount - Discount amount
  • discount_type - NONE, FIXED, PERCENTAGE
  • applicable_product_ids - JSONB product restrictions
  • expiry_date - Expiration
  • max_allowed_usages - Usage limit
  • attendee_usage_count, order_usage_count - Usage tracking

Model: app/Models/PromoCode.php

Features:

  • Product-specific codes
  • Usage tracking
  • JSONB index on applicable_product_ids

affiliates

Affiliate tracking.

Key Fields:

  • id - Primary key
  • event_id, account_id - Foreign keys
  • name - Affiliate name
  • code - Unique tracking code (indexed)
  • email - Contact email
  • total_sales - Sale count
  • total_sales_gross - Revenue
  • status - AffiliateStatus enum

Model: app/Models/Affiliate.php

Features: Attribution tracking for marketing

9. Communication

email_templates

Customizable email templates.

Key Fields:

  • id - Primary key
  • account_id, organizer_id, event_id - Three-level hierarchy
  • template_type - Template type enum
  • subject, body - Email content
  • cta - JSON call-to-action
  • engine - liquid (Liquid template engine)
  • is_active - Active flag

Model: app/Models/EmailTemplate.php

Migration: database/migrations/2025_08_28_100704_create_email_templates_table.php

Features: Account β†’ Organizer β†’ Event override hierarchy

messages

Bulk messaging to attendees.

Key Fields:

  • id - Primary key
  • event_id - Foreign key
  • subject, message - Message content
  • type - Message type
  • recipient_ids, product_ids, attendee_ids - JSONB targeting
  • sent_by_user_id - Sender
  • status - Send status
  • sent_at - Sent timestamp

Model: app/Models/Message.php

10. Analytics & Statistics

event_statistics

Aggregate statistics per event.

Key Fields:

  • event_id - Primary key (one-to-one)
  • unique_views, total_views - View tracking
  • sales_total_gross, sales_total_before_additions - Revenue
  • total_tax, total_fee, total_refunded - Breakdown
  • products_sold - Sold count
  • orders_created, orders_cancelled - Order metrics
  • attendees_registered - Attendee count
  • version - Optimistic locking

Model: app/Models/EventStatistic.php

Features: Optimistic locking via version field

event_daily_statistics

Daily breakdown.

Key Fields: Same as event_statistics plus date

Model: app/Models/EventDailyStatistic.php

11. Webhooks & Integration

webhooks

Outgoing webhook configuration.

Key Fields:

  • id - Primary key
  • event_id, account_id, user_id - Foreign keys
  • url - Webhook URL
  • event_types - JSONB subscribed events
  • status - WebhookStatus enum
  • secret - Webhook secret
  • last_response_code, last_response_body - Delivery tracking
  • last_triggered_at - Last trigger time

Model: app/Models/Webhook.php

Migration: database/migrations/2025_02_09_074739_create_webhooks_table.php

webhook_logs

Audit log for deliveries.

Key Fields:

  • webhook_id - Foreign key
  • payload - JSONB payload sent
  • event_type - Event type
  • response_code, response_body - Response tracking

Model: app/Models/WebhookLog.php

12. Media & Assets

images

Polymorphic image storage.

Key Fields:

  • id - Primary key
  • entity_id, entity_type - Polymorphic relation
  • account_id - Foreign key
  • type - Image type
  • filename, disk, path - Storage details
  • size, mime_type - File metadata

Model: app/Models/Image.php

Features: Attach to any entity via polymorphic relationship

Key Architectural Patterns

1. Soft Deletes

Nearly all entities use soft deletes via deleted_at timestamp.

Benefits:

  • Data recovery
  • Audit trails
  • Referential integrity

2. Timestamps

All tables have created_at and updated_at (except order_items).

3. Foreign Key Cascades

Common cascade behaviors:

-- Event deletion cascades to children
ALTER TABLE products ADD CONSTRAINT FOREIGN KEY (event_id)
    REFERENCES events(id) ON DELETE CASCADE;

-- Order deletion cascades to attendees
ALTER TABLE attendees ADD CONSTRAINT FOREIGN KEY (order_id)
    REFERENCES orders(id) ON DELETE CASCADE;

4. JSONB for Flexible Data

Extensive use of PostgreSQL JSONB:

  • orders.point_in_time_data - Pricing snapshot
  • orders.taxes_and_fees_rollup - Tax breakdown
  • orders.address - Billing/shipping address
  • events.location_details - Structured location
  • questions.options - Question choices
  • promo_codes.applicable_product_ids - Product restrictions
  • invoices.items - Invoice line items

Benefits: Schema flexibility without migrations

5. GIN Trigram Indexes

For fast full-text search:

CREATE INDEX orders_first_name_trgm ON orders
    USING gin(first_name gin_trgm_ops);

Tables: orders, attendees Columns: first_name, last_name, email, public_id

PostgreSQL Extension: pg_trgm (trigram matching)

6. Short IDs & Public IDs

User-facing entities use URL-friendly IDs:

  • short_id - Short identifier
  • public_id - Public identifier

Purpose: Obfuscate internal IDs, friendly URLs

7. Optimistic Locking

Event statistics use version field:

UPDATE event_statistics
SET sales_total = sales_total + 100, version = version + 1
WHERE event_id = 123 AND version = 5;

Prevents: Race conditions in high-concurrency updates

8. Point-in-Time Data

Orders preserve pricing at creation time:

orders.point_in_time_data = {
  "products": [...],
  "event_settings": {...},
  "promo_code": {...}
}

Benefits: Historical accuracy after event changes

Database Technology

PostgreSQL-Specific Features

  1. JSONB: Native JSON with indexing
  2. pg_trgm: Trigram matching for search
  3. Generated Columns: Auto-incrementing IDs
  4. GIN Indexes: For JSONB and text search
  5. Database Views: Complex query optimization
  6. CHECK Constraints: Enum validation

Schema Evolution

  • 57+ migrations: Iterative development
  • Major refactors: tickets β†’ products (2024)
  • Recent additions: email templates, webhooks, invoices (2025)

Entity Relationship Summary

erDiagram
    Account ||--o{ User : "has many (via pivot)"
    Account ||--o{ Organizer : "has many"
    Account ||--o{ TaxAndFee : "has many"
    Organizer ||--o{ Event : "has many"
    Event ||--o{ Product : "has many"
    Event ||--o{ Order : "has many"
    Event ||--o{ Question : "has many"
    Event ||--|| EventSettings : "has one"
    Product ||--o{ ProductPrice : "has many"
    Product }o--o{ TaxAndFee : "many-to-many"
    Product }o--o{ Question : "many-to-many"
    Order ||--o{ OrderItem : "has many"
    Order ||--o{ Attendee : "has many"
    Order ||--o| StripePayment : "has one"
    Order ||--o{ Invoice : "has many"
    Attendee ||--o{ AttendeeCheckIn : "has many"
    Attendee ||--o{ QuestionAnswer : "has many"

Related Documentation