PiqueTickets Database Schema¶
This document provides a comprehensive overview of the database schema for the PiqueTickets API, visualized using Mermaid ERD diagrams.
Overview¶
The PiqueTickets database is organized around several core entities: - Shows: The central entity representing events/performances - Tickets: Different ticket types available for each show - Orders: Customer purchases and transactions - Producers: Show organizers who create and manage events - Venues: Physical locations where shows take place - Performers: Artists who participate in shows - Subscribers: Email list subscribers - Users: System users (Django auth)
Core Ticketing System¶
This diagram shows the main ticketing workflow from Shows to Orders:
erDiagram
Show ||--o{ Ticket : "has"
Show ||--o{ TicketPromoCode : "has"
Show ||--o| TicketPageDesign : "has"
Show ||--o{ Order : "receives"
Show ||--o{ TicketAttendee : "has attendees"
Show ||--o{ FeaturedShow : "can be featured"
Ticket ||--o{ TicketOrder : "ordered via"
TicketOrder }o--o{ TicketAttendee : "includes"
TicketOrder }o--o{ Order : "part of"
Order }o--o| TicketPromoCode : "may use"
Show {
int id PK
string title
text description
datetime door_time
datetime start_time
datetime end_time
string time_zone
string age_restriction
file image
file banner_image
file square_image
url featured_video
string facebook_campaign_id
boolean published
string slug UK
datetime created_at
string refund_policy
boolean is_deleted
datetime deleted_at
datetime draft_created_at
datetime draft_updated_at
int venue_id FK
int producer_id FK
int author_id FK
}
Ticket {
int id PK
string name
text description
int quantity
decimal price
boolean is_donation_based
int show_id FK
int author_id FK
}
TicketPromoCode {
int id PK
string code
decimal discount
int show_id FK
}
TicketPageDesign {
int id PK
string primary_color
string secondary_color
file background_image
int show_id FK
}
TicketOrder {
int id PK
uuid uuid
int quantity
decimal donation_amount
decimal price_per_ticket
decimal total_price
decimal discount_amount
string promo_code
boolean is_vip
int ticket_id FK
}
TicketAttendee {
int id PK
uuid uuid UK
string first_name
string last_name
email email
boolean is_checked_in
int show_id FK
}
Order {
int id PK
string session_id
string payment_intent_id
string charge_id
string first_name
string last_name
email email
datetime time_of_purchase
decimal total
decimal payment_processing_fees
decimal platform_fees
boolean success
decimal promo_code_discount
int show_id FK
int promo_code_id FK
}
FeaturedShow {
int id PK
int order
boolean is_active
datetime created_at
datetime updated_at
int show_id FK
}Show Relationships¶
This diagram shows how Shows connect to Venues, Producers, and Performers:
erDiagram
Show }o--|| Venue : "takes place at"
Show }o--|| Producer : "organized by"
Show }o--o{ Performer : "features"
Show }o--|| User : "created by"
Venue {
int id PK
string name
text description
string address
string city
string state
string zip_code
string phone
string slug UK
url website
file venue_image
boolean is_active
}
Producer {
int id PK
string name
string country
url website
text bio
file image
datetime created_at
datetime updated_at
string slug UK
url instagram
url facebook
url twitter
url youtube
url ticktock
boolean is_active
}
Performer {
int id PK
string name
text bio
file image
string city
string slug UK
url website
url facebook
url twitter
url instagram
url youtube
url tiktok
}
User {
int id PK
string username
string email
string first_name
string last_name
boolean is_active
boolean is_staff
boolean is_superuser
}Producer Management System¶
This diagram shows the Producer-related entities and their relationships:
erDiagram
Producer ||--o| ProducerFinancial : "has financial info"
Producer ||--o{ UserProducerAssociation : "associated with"
Producer ||--o| OnboardingSession : "may have"
Producer ||--o{ ProducerInterest : "tracked interests"
User ||--o{ UserProducerAssociation : "manages"
User ||--|| UserProfile : "has profile"
ProducerFinancial {
int id PK
string stripe_account_id
url stripe_account_link
boolean onboarding_completed
boolean charges_enabled
boolean payouts_enabled
datetime last_status_check
int producer_id FK
}
UserProducerAssociation {
int id PK
int user_id FK
int producer_id FK
}
OnboardingSession {
int id PK
string token UK
email email
datetime created_at
datetime expires_at
boolean is_used
int producer_id FK
}
UserProfile {
int id PK
boolean can_publish_shows
datetime created_at
datetime updated_at
int user_id FK
}
ProducerInterest {
int id PK
string name
email email
string phone
string company_name
string city
string state
string events_per_year
json show_types
text show_types_other
json current_platform
text current_platform_other
text platform_challenges
text desired_features
boolean interested_in_marketing
text additional_info
datetime created_at
datetime updated_at
string ip_address
text user_agent
string status
text notes
}Subscriber & Email System¶
This diagram shows the subscriber management and email tracking systems:
erDiagram
Subscriber ||--o{ SubscriberMetaData : "has metadata"
Subscriber ||--o{ SubscriberDemographics : "has demographics"
Subscriber {
int id PK
string first_name
string last_name
email email UK
datetime created_at
}
SubscriberMetaData {
int id PK
ip ip_address
string ip_country
string ip_city
string ip_region
float ip_latitude
float ip_longitude
text user_agent
datetime created_at
string url_referer
int subscriber_id FK
}
SubscriberDemographics {
int id PK
date date_of_birth
string gender
string location
text interests
datetime created_at
int subscriber_id FK
}
EmailTracking {
int id PK
email recipient
string subject
text body
text html_body
email from_email
string status
datetime sent_at
datetime delivered_at
datetime failed_at
text error_message
string message_id
datetime created_at
datetime updated_at
}Check-in System¶
This diagram shows the check-in session management:
erDiagram
CheckInSession }o--|| Show : "for"
CheckInSession {
int id PK
string session_id UK
datetime created_at
datetime updated_at
string token UK
int show_id FK
}
Show ||--o{ CheckInSession : "has sessions"Database Indexes¶
The following indexes are defined to optimize query performance:
Show Model Indexes¶
(published, start_time)- For querying upcoming published shows(slug)- For URL-based lookups(venue, start_time)- For venue-specific show queries(producer, created_at)- For producer show management(created_at)- For chronological ordering
Order Model Indexes¶
(session_id)- For Stripe session lookups(payment_intent_id)- For Stripe payment intent lookups(email, show)- For customer order history(show, success)- For successful show orders(time_of_purchase)- For chronological ordering(success, time_of_purchase)- For successful orders in time ranges
TicketAttendee Model Indexes¶
(uuid)- For unique attendee identification(email, show)- For attendee lookups(show, is_checked_in)- For check-in status queries
Key Relationships Summary¶
- Show-centric Design: Shows are the central entity, connecting to:
- Tickets (one-to-many)
- Orders (one-to-many)
- Venue (many-to-one)
- Producer (many-to-one)
- Performers (many-to-many)
-
User/Author (many-to-one)
-
Order Flow:
- Orders contain multiple TicketOrders
- TicketOrders reference specific Tickets
- TicketOrders connect to TicketAttendees
-
Orders may use TicketPromoCodes
-
Producer System:
- Producers can have financial information (Stripe integration)
- Users can be associated with Producers
- UserProfiles control publishing permissions
-
OnboardingSessions manage producer onboarding
-
Subscriber System:
- Subscribers have associated metadata and demographics
-
EmailTracking monitors all email communications
-
Check-in System:
- CheckInSessions are created for shows
- TicketAttendees track check-in status
Notes¶
- The system uses Django's built-in User model for authentication
- Rich text fields use CKEditor5 for content formatting
- Images are converted to WebP format for optimization
- Slugs are auto-generated for SEO-friendly URLs
- Stripe integration for payment processing
- AWS SES integration for email delivery tracking