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 βοΈ
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
- Data Isolation: Each account's data is isolated via
account_idforeign key - Hierarchical Structure: Account β Organizer β Event β Products/Orders
- User Access: Users can belong to multiple accounts with different roles
- Cascade Deletes: Parent deletion cascades to children
Core Entities
1. Account & User Management
accounts
Top-level tenant entity.
Key Fields:
id- Primary keyname- Account nameemail- Account contact emailcurrency_code- Default currencytimezone- Default timezonestripe_account_id- Stripe Connect IDstripe_connect_setup_complete- Onboarding statusaccount_verified_at- Verification timestamp
Model: app/Models/Account.php
users
User authentication and identity.
Key Fields:
id- Primary keyemail- Unique email (authentication)password- Hashed passwordfirst_name,last_name- User nametimezone- User timezoneemail_verified_at- Email verificationstatus- 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 accountsuser_id- Foreign key to usersrole- User role (ADMIN, ORGANIZER, etc.)is_account_owner- Owner flaginvited_by_user_id- Invitation trackingstatus- Invitation statuslast_login_at- Login tracking
Features: RBAC (Role-Based Access Control)
account_configuration
Account-level configuration for fees.
Key Fields:
id- Primary keyname- Configuration nameis_system_default- System default flagapplication_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 keyaccount_id- Foreign key (CASCADE delete)name- Organizer nameemail,phone,website- Contact infocurrency- Default currencytimezone- Default timezone
Model: app/Models/Organizer.php
events
Core event entity.
Key Fields:
id- Primary keyaccount_id- Foreign key (CASCADE delete)organizer_id- Foreign keyuser_id- Creatortitle,description- Event detailsstart_date,end_date- Event datesstatus- EventStatus enum (DRAFT, LIVE, ARCHIVED)location,location_details- JSONB location datacurrency,timezone- Event-specific settingsshort_id- URL-friendly IDcategory- 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 messagessupport_email- Support contactrequire_attendee_details- Attendee form requirementorder_timeout_in_minutes- Reservation timeouthomepage_*_color- Branding colorsseo_*- SEO metadataticket_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 keyevent_id- Foreign key (CASCADE delete)title,description- Product detailstype- Product typeproduct_type- TICKET or OTHERsale_start_date,sale_end_date- Sale windowmin_per_order,max_per_order- Purchase limitsproduct_category_id- Categoryorder- 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 keyproduct_id- Foreign keyprice- Price amountlabel- Price tier name (e.g., "Early Bird", "VIP")sale_start_date,sale_end_date- Time-based pricinginitial_quantity_available- Starting quantityquantity_sold- Sold countquantity_available- Remainingorder- Display order
Features:
- Time-based pricing (early bird, etc.)
- Inventory tracking per tier
product_categories
Organize products into categories.
Key Fields:
id- Primary keyevent_id- Foreign keyname,description- Category detailsorder- Display order
taxes_and_fees
Account-level tax and fee definitions.
Key Fields:
id- Primary keyaccount_id- Foreign keyname- Tax/fee nametype- TAX or FEE enumcalculation_type- FIXED or PERCENTAGErate- Amount/percentageis_active- Active flagis_default- Auto-apply to new products
Features: Automatically applies default taxes to new tickets
4. Orders & Transactions
orders
Customer purchases.
Key Fields:
id- Primary keyshort_id,public_id- URL-friendly IDsevent_id- Foreign keyfirst_name,last_name,email- Customer infostatus- OrderStatus enum (RESERVED, COMPLETED, etc.)payment_status- Payment staterefund_status- Refund statepayment_gateway,payment_provider- Payment methodcurrency- Order currencytotal_before_additions- Subtotaltotal_tax,total_fee- Tax/fee totalstotal_gross- Grand totaltotal_refunded- Refund amountpromo_code_id- Applied promo codeaffiliate_id- Attributionaddress- JSONB addresstaxes_and_fees_rollup- JSONB tax breakdownpoint_in_time_data- JSONB pricing snapshotreserved_until- Reservation expirylocale- 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 keyorder_id- Foreign keyproduct_id- Foreign keyproduct_price_id- Specific price tierquantity- Item countprice- Unit priceprice_before_discount- Pre-discount pricetotal_before_additions- Subtotaltotal_tax,total_service_fee- Additionstotal_gross- Line totaltaxes_and_fees_rollup- JSONB breakdownproduct_type- TICKET or OTHER
Model: app/Models/OrderItem.php
Features: No timestamps (immutable once created)
stripe_payments
Stripe payment tracking.
Key Fields:
id- Primary keyorder_id- Foreign keypayment_intent_id- Stripe payment intentcharge_id- Stripe chargepayment_method_id- Payment methodamount_received- Actual amountconnected_account_id- Stripe Connect accountlast_error- JSON error detailsplatform- Stripe platform used
Model: app/Models/StripePayment.php
invoices
Invoice generation for orders.
Key Fields:
id- Primary keyorder_id- Foreign keyaccount_id- Foreign keyinvoice_number- Unique invoice numberissue_date,due_date- Invoice datestotal_amount- Invoice totalstatus- Invoice statusitems- JSONB line itemstaxes_and_fees- JSONB tax breakdownuuid- Public UUIDpayment_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 keyshort_id,public_id- URL-friendly IDsorder_id- Foreign key (CASCADE delete)product_id- Foreign key (CASCADE delete)product_price_id- Price tierevent_id- Foreign key (CASCADE delete)first_name,last_name,email- Attendee infostatus- AttendeeStatus enumlocale- Attendee languagenotes- 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 keyevent_id- Foreign keyshort_id- URL-friendly IDname,description- List detailsactivates_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 keyshort_id- URL-friendly IDcheck_in_list_id- Foreign keyproduct_id,attendee_id,order_id,event_id- Referencesip_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 keyevent_id- Foreign keytitle- Question texttype- QuestionTypeEnum (TEXT, CHECKBOX, etc.)required- Required flagoptions- JSONB options for multiple choicebelongs_to- ORDER or ATTENDEE levelorder- Display orderis_hidden- Hidden flagdescription- Help text
Model: app/Models/Question.php
Relationships: Many-to-many with Products
question_answers
Store answers to questions.
Key Fields:
id- Primary keyquestion_id- Foreign keyorder_id- Order-level answerattendee_id- Attendee-level answerproduct_id- Product contextanswer- 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 keyevent_id- Foreign keyname- Capacity namecapacity- Total capacityused_capacity- Used amountapplies_to- EVENT or PRODUCTSstatus- 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 keyevent_id- Foreign keycode- Promo code (indexed)discount- Discount amountdiscount_type- NONE, FIXED, PERCENTAGEapplicable_product_ids- JSONB product restrictionsexpiry_date- Expirationmax_allowed_usages- Usage limitattendee_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 keyevent_id,account_id- Foreign keysname- Affiliate namecode- Unique tracking code (indexed)email- Contact emailtotal_sales- Sale counttotal_sales_gross- Revenuestatus- AffiliateStatus enum
Model: app/Models/Affiliate.php
Features: Attribution tracking for marketing
9. Communication
email_templates
Customizable email templates.
Key Fields:
id- Primary keyaccount_id,organizer_id,event_id- Three-level hierarchytemplate_type- Template type enumsubject,body- Email contentcta- JSON call-to-actionengine- 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 keyevent_id- Foreign keysubject,message- Message contenttype- Message typerecipient_ids,product_ids,attendee_ids- JSONB targetingsent_by_user_id- Senderstatus- Send statussent_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 trackingsales_total_gross,sales_total_before_additions- Revenuetotal_tax,total_fee,total_refunded- Breakdownproducts_sold- Sold countorders_created,orders_cancelled- Order metricsattendees_registered- Attendee countversion- 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 keyevent_id,account_id,user_id- Foreign keysurl- Webhook URLevent_types- JSONB subscribed eventsstatus- WebhookStatus enumsecret- Webhook secretlast_response_code,last_response_body- Delivery trackinglast_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 keypayload- JSONB payload sentevent_type- Event typeresponse_code,response_body- Response tracking
Model: app/Models/WebhookLog.php
12. Media & Assets
images
Polymorphic image storage.
Key Fields:
id- Primary keyentity_id,entity_type- Polymorphic relationaccount_id- Foreign keytype- Image typefilename,disk,path- Storage detailssize,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 snapshotorders.taxes_and_fees_rollup- Tax breakdownorders.address- Billing/shipping addressevents.location_details- Structured locationquestions.options- Question choicespromo_codes.applicable_product_ids- Product restrictionsinvoices.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 identifierpublic_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
- JSONB: Native JSON with indexing
- pg_trgm: Trigram matching for search
- Generated Columns: Auto-incrementing IDs
- GIN Indexes: For JSONB and text search
- Database Views: Complex query optimization
- 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"