Developer Menu 1/4

1. Database Structure Overview

Eventiq uses a well-organized database schema centered around events, users, tickets, and transactions. This section highlights key domains first, then provides a complete table overview below (including Laravel/system tables). Core business tables primarily use 36-character IDs, while Laravel/system tables use integer keys where standard.

Database Design Principles

  • Mixed Key Strategy: Core domain tables use 36-character IDs; Laravel/system tables may use auto-increment integers
  • Audit Trails: Timestamps (created_at, updated_at) track record lifecycle
  • Relationship Integrity: Key domains use foreign keys; some links are enforced at application layer
  • Indexing Strategy: Optimized indexes for frequent query patterns
  • Schema-First Customization: Align migrations, models, validation, and API resources for each change

Core Entity Relationship Diagram (ERD)

┌─────────────────┐    ┌─────────────────┐    ┌─────────────────┐
│      Users      │    │     Events     │    │EventTicketTypes │
│─────────────────│    │─────────────────│    │─────────────────│
│ id (UUID) PK    │◄───┤ id (UUID) PK    │◄───┤ id (UUID) PK    │
│ full_name       │    │ organizer_id FK │    │ event_id FK     │
│ email           │    │ name            │    │ name            │
│ username        │    │ slug            │    │ price           │
│ role            │    │ status          │    │ number_of_tickets│
│ balance         │    │ start_date      │    │ is_free         │
│ is_organizer    │    │ end_date        │    └─────────────────┘
│ email_verified  │    │ seating_capacity│
│ kyc_verified    │    │ price           │
│ created_at      │    │ is_featured     │
│ updated_at      │    │ is_free         │
└─────────────────┘    │ created_at      │
         │              │ updated_at      │
         │              └─────────────────┘
         │                       │
         │                       │
         │              ┌─────────────────┐
         │              │ TicketPurchases │
         │              │─────────────────│
         │              │ id (UUID) PK    │
         │              │ user_id FK      │
         │              │ event_id FK     │
         │              │ trx (UNIQUE)    │
         │              │ total_quantity  │
         │              │ sub_total       │
         │              │ final_amount    │
         │              │ status          │
         │              │ gateway_id FK   │
         │              │ organizer_earning│
         │              │ created_at      │
         │              │ updated_at      │
         │              └─────────────────┘
         │                       │
         │                       │
         ▼                       ▼
┌─────────────────┐    ┌─────────────────┐    
│  Transactions   │    │   Reviews       │    
│─────────────────│    │─────────────────│   
│ id (UUID) PK    │    │ id (UUID) PK    │   
│ user_id FK      │    │ user_id FK      │  
│ type (ENUM)     │    │ event_id FK     │    
│ amount          │    │ rating          │   
│ post_balance    │    │ comment         │   
│ trx_id (UNIQUE) │    │ created_at      │   
│ details         │    │ updated_at      │    
│ created_at      │    └─────────────────┘    
│ updated_at      │                            
└─────────────────┘

Detailed Table Specifications

Users Table (users)

Purpose: Central user management for attendees, organizers, and administrators

Business Context: Multi-role user entity supporting attendee, organizer, and admin functionality with comprehensive verification and financial tracking.

Column Specifications

Column Name Data Type Constraints Description Business Rules
id UUID PRIMARY KEY Unique identifier Application-generated 36-character ID
full_name VARCHAR(255) NOT NULL User's complete name Required for registration
email VARCHAR(255) UNIQUE, NOT NULL Email address Must be valid email format
username VARCHAR(255) UNIQUE, NOT NULL Unique username Alphanumeric, min 3 chars
phone VARCHAR(255) NULLABLE Phone number International format supported
balance DECIMAL(28,8) DEFAULT 0 Wallet balance Precision for micro-transactions
role VARCHAR(255) DEFAULT 'user' System role Common values: user/admin/volunteer (organizer state tracked by is_organizer)
is_organizer INTEGER DEFAULT 0 Organizer status 0=not requested,1=requested,2=accepted,3=rejected
email_verified BOOLEAN DEFAULT FALSE Email verification Required for event creation
kyc_verified BOOLEAN DEFAULT FALSE KYC verification Required for withdrawals
social JSON NULLABLE Social media links Structured JSON object
average_rating DECIMAL(10,2) NULLABLE User rating 1.00-5.00 scale

Indexes

  • PRIMARY: id
  • UNIQUE: email
  • UNIQUE: username
  • INDEX: referred_by

Relationships

  • One to Many: Events (as organizer)
  • One to Many: Ticket Purchases
  • One to Many: Transactions
  • One to Many: Reviews
  • Many to Many: Events (favorites via pivot)
  • Many to Many: Users (follows via pivot)

Events Table (events)

Purpose: Core entity for event management with comprehensive metadata for scheduling, pricing, and workflow management.

Business Context: Central to the platform, supporting multi-status workflow, flexible pricing models, and venue management.

Column Specifications

Column Name Data Type Constraints Description Business Rules
id UUID PRIMARY KEY Event identifier Application-generated 36-character ID
organizer_id VARCHAR(255) NOT NULL Event organizer Application-level relation to users.id (organizer account)
name VARCHAR(255) NOT NULL Event title Required, max 255 chars
slug VARCHAR(255) UNIQUE, NOT NULL URL-friendly name Auto-generated from name
status ENUM DEFAULT 'draft' Publication status 7-state workflow management
start_date DATETIME NULLABLE Event start time UTC timezone
end_date DATETIME NULLABLE Event end time Must be after start_date
seating_capacity INTEGER DEFAULT 0 Maximum attendees 0 = unlimited
price DECIMAL(8,2) NULLABLE Base ticket price NULL = free event
is_featured INTEGER DEFAULT 0 Featured flag 1 = featured, 0 = regular
is_free INTEGER DEFAULT 0 Free event flag 1 = free, 0 = paid
management_fee_payer ENUM DEFAULT 'user' Fee responsibility user/organizer
admin_vat INTEGER DEFAULT 0 Admin VAT percentage 0-100, applied to total
organizer_vat INTEGER DEFAULT 0 Organizer VAT 0-100, organizer tax rate
view INTEGER DEFAULT 0 View counter Analytics tracking
banner VARCHAR(255) NULLABLE Event banner image File path to image
details LONGTEXT NULLABLE Event description Rich text content
address VARCHAR(255) NULLABLE Venue address Physical location
publish_date DATETIME NULLABLE Publication date When event goes live

ENUM Values for Status Field

  • draft - Initial creation state
  • pending - Submitted for review
  • under_review - Admin review in progress
  • active - Published and available
  • disabled - Temporarily disabled
  • rejected - Rejected by admin
  • expired - Event date passed

Indexes

  • PRIMARY: id
  • UNIQUE: slug
  • Note: No explicit FK/index is defined on organizer_id in the current SQL dump

Application-Level Constraints (Recommended)

  • Validate end_date after start_date before publishing
  • Enforce organizer verification/approval rules in business logic
  • Apply admin approval checks for featured events where enabled
  • Keep free/paid event flags consistent with ticket pricing logic
  • Use seating capacity and ticket inventory together to avoid overselling

Event Ticket Types Table (event_ticket_types)

Purpose: Flexible ticket pricing and inventory management supporting multiple tiers, discount schedules, and time-based pricing.

Business Context: Enables complex pricing strategies like early-bird discounts, VIP tiers, and limited availability tickets.

Column Specifications

Column Name Data Type Constraints Description Business Rules
id UUID PRIMARY KEY Ticket type identifier Application-generated 36-character ID
event_id VARCHAR(255) FOREIGN KEY, NOT NULL Parent event References events.id, CASCADE delete
name VARCHAR(255) NULLABLE Ticket type name e.g., "VIP", "Early Bird", "General"
number_of_tickets INTEGER DEFAULT 0 Available quantity 0 = unlimited, controls inventory
price DECIMAL(10,2) DEFAULT 0 Ticket price Base price before discounts
have_discount INTEGER DEFAULT 0 Discount enabled 1 = has discount, 0 = no discount
is_free INTEGER NULLABLE Free ticket flag 1 = free, 0/NULL = paid
discount_type VARCHAR(255) NULLABLE Discount calculation percentage/fixed
discount_value VARCHAR(255) NULLABLE Discount amount Value based on discount_type
purchase_start_date VARCHAR(255) NULLABLE Sales start date Date format string
purchase_end_date VARCHAR(255) NULLABLE Sales end date Date format string
purchase_end_time VARCHAR(255) NULLABLE Sales end time Time format string
discount_start_date VARCHAR(255) NULLABLE Discount start date Date format string
discount_end_date VARCHAR(255) NULLABLE Discount end date Date format string

Business Logic

  • Ticket inventory decreases with each purchase
  • Discount schedules support time-based pricing
  • Free tickets override price calculations
  • Purchase windows control availability periods
  • Multiple ticket types per event for tiered pricing

Ticket Purchases Table (ticket_purchases)

Purpose: Transaction-level records capturing complete purchase lifecycle including payment processing, discounts, and financial settlements.

Business Context: Critical for revenue tracking, refund processing, and organizer commission calculations.

Column Specifications

Column Name Data Type Constraints Description Business Rules
id UUID PRIMARY KEY Purchase identifier Application-generated 36-character ID
trx VARCHAR(255) UNIQUE, NOT NULL Transaction reference Human-readable transaction ID
user_id VARCHAR(255) NOT NULL Purchaser Logical relation to users.id
event_id VARCHAR(255) NOT NULL Purchased event Logical relation to events.id
selected_date DATE NULLABLE Event date selected For multi-day events
total_quantity INTEGER DEFAULT 0 Total tickets Sum of all ticket types
sub_total DECIMAL(10,2) DEFAULT 0 Subtotal amount Before fees and discounts
final_amount DECIMAL(10,2) DEFAULT 0 Final amount paid After all calculations
status VARCHAR(255) DEFAULT 'pending' Purchase status Workflow state management
gateway_id VARCHAR(255) NULLABLE Payment gateway References gateways.id
coupon_id VARCHAR(255) NULLABLE Applied coupon References coupons.id
discount_amount DECIMAL(10,2) NULLABLE Discount value Total discount applied
management_fee DECIMAL(10,2) NULLABLE Platform fee Platform commission
organizer_earning DECIMAL(10,2) DEFAULT 0 Organizer revenue Net amount to organizer
refund_status VARCHAR(255) NULLABLE Refund state pending/approved/rejected
payment_proof LONGTEXT NULLABLE Payment evidence For manual verification

Purchase Status Workflow

  • status is a VARCHAR field, so values are controlled by application logic
  • Common values used by deployments include pending, completed, failed, canceled, and refunded

Transactions Table (transactions)

Purpose: Comprehensive financial ledger tracking all monetary movements including deposits, withdrawals, earnings, and system adjustments.

Business Context: Essential for accounting, audit trails, and financial reporting. Maintains complete transaction history for compliance and business intelligence.

Column Specifications

Column Name Data Type Constraints Description Business Rules
id UUID PRIMARY KEY Transaction identifier Application-generated 36-character ID
user_id VARCHAR(255) NOT NULL Account owner Logical relation to users.id
type ENUM NOT NULL Transaction category 8 transaction types defined
amount DECIMAL(28,8) NOT NULL Transaction amount Positive for credits, negative for debits
post_balance DECIMAL(28,8) NOT NULL Balance after transaction Account balance post-transaction
trx_id VARCHAR(255) UNIQUE, NOT NULL Reference number External transaction reference
details TEXT NULLABLE Transaction description Human-readable details

Transaction Types and Business Logic

  • withdraw - User requests funds withdrawal
  • deposit - User adds funds to wallet
  • bonus - Promotional credits or rewards
  • add balance - Manual balance adjustments by admin
  • subtract balance - Manual deductions by admin
  • income - Organizer earnings from ticket sales
  • payment - Ticket purchase payments
  • refund - Refund processing

Financial Integrity Rules

  • All amounts stored with high precision (28,8) for micro-transactions
  • Post_balance always reflects accurate account state
  • Transaction IDs are globally unique and traceable
  • Prefer append-only/immutable patterns for audit-sensitive financial records
  • Chronological ordering maintained via timestamps

Database Implementation Notes

Indexing Strategy

The current schema uses practical indexes on primary keys, unique business keys, and selected lookup columns:

Primary Indexes

  • Primary keys on all tables (mixed char(36) and bigint, depending on module)
  • Unique constraints on key business fields such as users.email, users.username, events.slug, ticket_purchases.trx
  • Composite indexes where query patterns require them (example: ticket_purchases(user_id, event_id))

Practical Notes

  • Some foreign key columns are indexed by design; others are not and can be added based on workload
  • Before adding indexes, validate with real query logs and EXPLAIN
  • Keep index additions in migrations so web and mobile API remain in sync across environments

Data Integrity Constraints

  • Foreign Keys: Applied on major relationship tables where present in migrations
  • Application Validation: Additional business rules are validated in Laravel requests/services
  • Unique Constraints: Prevent duplicate data across business keys
  • NOT NULL Constraints: Ensure required data presence
  • ENUM Constraints: Validate allowed values

Scalability Considerations

  • Scale Path: Add indexes and caching first for read-heavy screens
  • Read Replicas: Optional for high-traffic deployments
  • Queue + Cache: Use queue workers and cache stores for expensive operations
  • Archive Strategy: Move historical finance/audit data when datasets grow

Security Considerations

  • Secrets Management: Keep keys and credentials in environment variables
  • Auditability: Preserve immutable transaction/history records where required
  • PII Handling: Limit access to user profile and KYC-related fields
  • Access Controls: Enforce role/permission checks in admin and API layers
  • Backup Strategy: Set automated backups and restoration drills per environment

Database Migration Strategy

Version Control

  • All schema changes managed through Laravel migrations
  • Timestamped migration files for chronological ordering
  • Rollback capabilities for all changes
  • Environment-specific migration execution

Deployment Considerations

  • Zero-Downtime Migrations: Schema changes without service interruption
  • Backward Compatibility: Support for multiple app versions
  • Data Migration: Structured data transformation processes
  • Testing Strategy: Comprehensive migration testing

Performance Monitoring and Optimization

Query Optimization

  • EXPLAIN ANALYZE for query performance analysis
  • Slow query logging and monitoring
  • Index usage statistics and optimization
  • Query plan caching strategies

Database Health Metrics

  • Connection pool utilization
  • Query execution time trends
  • Index efficiency metrics
  • Storage growth projections

Admins Table (admins)

Purpose: Backend administration users

  • id (UUID) - Primary key
  • username, email - Admin credentials
  • status - Admin account status

Supporting Tables

  • Event Categories (event_categories) - Event categorization
  • Event Locations (event_locations) - Venue information
  • Coupons (coupons) - Discount codes
  • Reviews (reviews) - User ratings and feedback
  • Notifications (notifications) - System notifications
  • Gateways (gateways) - Payment gateway configurations
  • Settings (settings) - Application configuration

Key Relationships

Users (1) → (Many) Events (organizer_id)
Events (1) → (Many) Event Ticket Types (event_id)
Events (1) → (Many) Ticket Purchases (event_id)
Users (1) → (Many) Ticket Purchases (user_id)
Users (1) → (Many) Transactions (user_id)

Complete SQL Schema Overview

Accounts and Access

  • users: primary user entity with auth/profile/role/location fields.
  • admins: admin authentication table.
  • roles, permissions, model_has_roles, model_has_permissions, role_has_permissions: RBAC tables.
  • personal_access_tokens: Sanctum API tokens.
  • sessions, password_reset_tokens: login sessions and password reset flow.
  • kyc_submissions: KYC documents and status for users.

Event Domain

  • events: event details, status, dates, fee settings, SEO fields.
  • event_locations: per-event location metadata (country/city/lat/lon/address).
  • event_guests: speakers/guests for events.
  • event_categories: category master list.
  • category_events: event-category pivot table.
  • event_volunteers: event-volunteer assignment.
  • organizer_volunteers: organizer-team mapping.
  • event_user_favorites: favorite events by users.
  • reviews: attendee review and rating per event.

Ticketing Domain

  • event_ticket_types: sellable ticket definitions and inventory.
  • ticket_purchases: purchase header and payment status.
  • ticket_purchase_items: purchase item lines.
  • single_tickets: generated individual tickets for QR scan.

Finance and Billing

  • transactions: ledger entries (withdraw, deposit, income, payment, refund, etc.).
  • deposits: user deposit records.
  • withdrawals, withdraw_settings: payout requests and rules.
  • subscriptions, plans: organizer plan purchase and plan config.
  • coupons: discount rules used in subscription/event purchases.
  • gateways, currencies: payment providers and currency setup.
  • referral_commissions: referral earning records.

Communication and Support

  • messages: user-organizer messaging.
  • notifications: Laravel notification storage.
  • tickets, ticket_replies, ticket_categories: support ticket module.
  • contact_messages: contact form submissions.
  • newsletters: newsletter subscribers.
  • email_templates: email template definitions.

Content and CMS

  • blogs, blog_categories: blog module.
  • pages, sections, page_sections: CMS page builder.
  • menu_items: frontend menu structure.
  • settings: global configuration store.
  • languages: language packs and locale settings.

System/Internal

  • migrations: migration history.
  • jobs, job_batches, failed_jobs: queue system.
  • cache, cache_locks: cache driver tables.

Developer Tips Before Editing Schema

  1. Start from migration + model + request validation together.
  2. Keep ID format compatibility across related fields (char(36)/varchar(255) in current schema).
  3. For ticket changes, update both purchase header and line-item logic.
  4. For event changes, review impacts on API, organizer views, and volunteer flow.