Database Schema Documentation

Overview

Elite Events uses MySQL as the primary database with Prisma ORM for type-safe database access. The schema supports a comprehensive e-commerce platform with promotions, loyalty programs, support ticketing, and affiliate systems.

Entity Relationship Diagram

Core E-commerce

+----------------+     +----------------+     +----------------+
|     User       |----<|     Order      |>----|   OrderItem    |
+----------------+     +----------------+     +----------------+
| id             |     | id             |     | id             |
| email          |     | userId         |     | orderId        |
| name           |     | status         |     | productId      |
| role           |     | total          |     | quantity       |
| password       |     | shippingAddress|     | price          |
| createdAt      |     | createdAt      |     +----------------+
+----------------+     +----------------+            |
       |                                             |
       |                                             v
       v                                     +----------------+
+----------------+                           |    Product     |
|    Address     |                           +----------------+
+----------------+     +----------------+    | id             |
| id             |     |   Category     |--->| title          |
| userId         |     +----------------+    | categoryId     |
| type           |     | id             |    | price          |
| street         |     | title          |    | discountedPrice|
| city           |     | slug           |    | stock          |
| state          |     | parentId       |    | sku            |
| zipCode        |     +----------------+    | description    |
+----------------+            |              +----------------+
                              |                     |
                              v                     v
                     +----------------+     +----------------+
                     |   (children)   |     | ProductImage   |
                     | Self-reference |     +----------------+
                     +----------------+     | id             |
                                            | productId      |
                                            | url            |
                                            | thumbnailUrl   |
                                            | order          |
                                            +----------------+

Promotions System

+----------------+     +----------------+     +----------------+
|   Promotion    |----<|   PromoCode    |>----|PromotionUsage  |
+----------------+     +----------------+     +----------------+
| id             |     | id             |     | id             |
| name           |     | promotionId    |     | promotionId    |
| type           |     | code           |     | promoCodeId    |
| discountType   |     | usageLimit     |     | userId         |
| discountValue  |     | usageCount     |     | orderId        |
| startDate      |     | isActive       |     | discountAmount |
| endDate        |     +----------------+     | usedAt         |
| isActive       |                            +----------------+
| stackable      |
| priority       |
+----------------+
       |
       +----> BogoPromotion (BOGO deals)
       +----> BundlePromotion (Bundle pricing)
       +----> FreeGiftPromotion (Free items)
       +----> TieredPromotion (Volume discounts)
       +----> FlashSale (Time-limited sales)

Loyalty & Affiliates

+----------------+     +----------------+     +----------------+
| LoyaltyProgram |---->|  LoyaltyTier   |     |CustomerLoyalty |
+----------------+     +----------------+     +----------------+
| id             |     | id             |     | id             |
| name           |     | programId      |     | userId         |
| pointsPerDollar|     | name           |     | totalPoints    |
| redemptionRate |     | minPoints      |     | lifetimePoints |
| isActive       |     | pointsMultiplier|    | currentTierId  |
+----------------+     | perks          |     +----------------+
                       +----------------+            |
                                                     v
                                            +-------------------+
                                            |LoyaltyTransaction |
                                            +-------------------+
                                            | id                |
                                            | customerLoyaltyId |
                                            | type              |
                                            | points            |
                                            | description       |
                                            | orderId           |
                                            | expiresAt         |
                                            +-------------------+

+----------------+     +----------------+
|ReferralProgram |---->|   Referral     |
+----------------+     +----------------+
| id             |     | id             |
| name           |     | programId      |
| referrerReward |     | referrerId     |
| refereeReward  |     | refereeId      |
| minPurchase    |     | referralCode   |
| isActive       |     | status         |
+----------------+     | referrerRewarded|
                       | refereeRewarded |
                       +----------------+

Support System

+----------------+     +----------------+     +----------------+
|  SupportTicket |---->| SupportMessage |---->|MessageAttachment|
+----------------+     +----------------+     +----------------+
| id             |     | id             |     | id             |
| ticketNumber   |     | ticketId       |     | messageId      |
| userId         |     | senderType     |     | fileName       |
| customerEmail  |     | senderId       |     | fileUrl        |
| subject        |     | senderName     |     | fileType       |
| description    |     | content        |     | fileSize       |
| category       |     | isInternal     |     +----------------+
| priority       |     | createdAt      |
| status         |     +----------------+
| assignedToId   |
| orderId        |     +----------------+
| productId      |     | TicketHistory  |
| createdAt      |     +----------------+
| resolvedAt     |     | id             |
+----------------+     | ticketId       |
       |               | action         |
       v               | oldValue       |
+----------------+     | newValue       |
|TicketAttachment|     | performedBy    |
+----------------+     +----------------+

Core Tables

User

Primary user account table supporting customers and admins.

Column	Type	Description
id	INT	Primary key (auto-increment)
email	VARCHAR(255)	Unique email address
password	VARCHAR(255)	Bcrypt hashed password
name	VARCHAR(100)	Display name
phone	VARCHAR(20)	Phone number
role	ENUM	CUSTOMER, ADMIN, MODERATOR, SUPPORT
emailVerified	DATETIME	Email verification timestamp
image	VARCHAR(500)	Avatar URL
twoFactorEnabled	BOOLEAN	2FA status
twoFactorSecret	VARCHAR(500)	TOTP secret
failedLoginAttempts	INT	Failed login counter
lockedUntil	DATETIME	Account lockout timestamp
createdAt	DATETIME	Creation timestamp
updatedAt	DATETIME	Last update timestamp

Indexes:

email (UNIQUE)

Product

Product catalog with pricing and inventory.

Column	Type	Description
id	INT	Primary key
title	VARCHAR(255)	Product name
description	TEXT	Full description
details	TEXT	Additional details
price	FLOAT	Regular price
discountedPrice	FLOAT	Sale price
sku	VARCHAR(50)	Stock keeping unit (unique)
stock	INT	Available quantity
categoryId	INT	Foreign key to Category
specifications	JSON	Product specifications
createdAt	DATETIME	Creation timestamp
updatedAt	DATETIME	Last update timestamp

Indexes:

sku (UNIQUE)
categoryId
title
price
discountedPrice
stock
createdAt
categoryId, stock
categoryId, createdAt
categoryId, discountedPrice

Order

Customer orders with status tracking.

Column	Type	Description
id	INT	Primary key
userId	INT	Foreign key to User
status	ENUM	PROCESSING, SHIPPED, DELIVERED, CANCELLED
total	FLOAT	Order total
discountAmount	FLOAT	Applied discount
appliedPromoCode	VARCHAR(50)	Used promo code
shippingAddress	TEXT	JSON shipping address
billingAddress	TEXT	JSON billing address
isDemo	BOOLEAN	Demo order flag
createdAt	DATETIME	Order timestamp
updatedAt	DATETIME	Last status update

Indexes:

userId
userId, status
createdAt DESC
isDemo

OrderItem

Individual items within an order.

Column	Type	Description
id	INT	Primary key
orderId	INT	Foreign key to Order
productId	INT	Foreign key to Product
quantity	INT	Item quantity
price	FLOAT	Price at time of purchase

Indexes:

orderId
productId

Review

Product reviews and ratings.

Column	Type	Description
id	INT	Primary key
productId	INT	Foreign key to Product
userId	INT	Foreign key to User
rating	INT	1-5 star rating
comment	TEXT	Review text
createdAt	DATETIME	Creation timestamp
updatedAt	DATETIME	Last update timestamp

Indexes:

productId
userId
productId, rating
productId, createdAt
createdAt

Promotion Tables

Promotion

Base promotion configuration.

Column	Type	Description
id	INT	Primary key
name	VARCHAR(255)	Internal name
displayName	VARCHAR(255)	Customer-facing name
description	TEXT	Promotion description
type	ENUM	PERCENTAGE, FIXED, BOGO, BUNDLE, FREE_GIFT, TIERED, FLASH_SALE
discountType	ENUM	PERCENTAGE, FIXED_AMOUNT
discountValue	FLOAT	Discount amount/percentage
startDate	DATETIME	Promotion start
endDate	DATETIME	Promotion end
isActive	BOOLEAN	Active status
usageLimit	INT	Total usage limit
usageCount	INT	Current usage count
perCustomerLimit	INT	Per-customer limit
minimumPurchase	FLOAT	Minimum cart value
maximumDiscount	FLOAT	Discount cap
stackable	BOOLEAN	Can combine with others
priority	INT	Application priority
targetType	ENUM	ALL, SPECIFIC_PRODUCTS, SPECIFIC_CATEGORIES
createdAt	DATETIME	Creation timestamp
updatedAt	DATETIME	Last update timestamp

Indexes:

startDate, endDate
isActive
type

PromoCode

Promo codes linked to promotions.

Column	Type	Description
id	INT	Primary key
promotionId	INT	Foreign key to Promotion
code	VARCHAR(50)	Unique promo code
usageLimit	INT	Code-specific limit
usageCount	INT	Current usage
isActive	BOOLEAN	Active status
expiresAt	DATETIME	Code expiration
createdAt	DATETIME	Creation timestamp

Indexes:

code (UNIQUE)
promotionId

Loyalty Tables

CustomerLoyalty

Customer loyalty status and points.

Column	Type	Description
id	INT	Primary key
userId	INT	Foreign key to User (unique)
totalPoints	INT	Current available points
lifetimePoints	INT	Total points ever earned
currentTierId	INT	Current membership tier

Indexes:

userId (UNIQUE)

LoyaltyTransaction

Points earning and redemption history.

Column	Type	Description
id	INT	Primary key
customerLoyaltyId	INT	Foreign key to CustomerLoyalty
type	VARCHAR(50)	EARN, REDEEM, EXPIRE, ADJUSTMENT
points	INT	Points (positive or negative)
description	VARCHAR(255)	Transaction description
orderId	INT	Related order (nullable)
expiresAt	DATETIME	Points expiration date
createdAt	DATETIME	Transaction timestamp

Indexes:

customerLoyaltyId
createdAt

Support Tables

SupportTicket

Customer support tickets.

Column	Type	Description
id	CUID	Primary key
ticketNumber	VARCHAR(20)	Human-readable ticket ID
userId	INT	Registered user (nullable)
customerEmail	VARCHAR(255)	Customer email
customerName	VARCHAR(100)	Customer name
subject	VARCHAR(255)	Ticket subject
description	TEXT	Initial description
category	ENUM	ORDER, PRODUCT, SHIPPING, REFUND, TECHNICAL, OTHER
priority	ENUM	LOW, MEDIUM, HIGH, URGENT
status	ENUM	OPEN, IN_PROGRESS, WAITING_CUSTOMER, RESOLVED, CLOSED
assignedToId	INT	Assigned agent
orderId	INT	Related order
productId	INT	Related product
tags	VARCHAR(255)	Comma-separated tags
internalNotes	TEXT	Agent-only notes
firstResponseAt	DATETIME	First agent response
resolvedAt	DATETIME	Resolution timestamp
closedAt	DATETIME	Closure timestamp
createdAt	DATETIME	Creation timestamp
updatedAt	DATETIME	Last update

Indexes:

ticketNumber (UNIQUE)
userId
status
priority
category
assignedToId
createdAt
orderId
productId

Authentication Tables

Account

OAuth provider accounts (NextAuth).

Column	Type	Description
id	CUID	Primary key
userId	INT	Foreign key to User
type	VARCHAR(50)	Account type
provider	VARCHAR(50)	OAuth provider
providerAccountId	VARCHAR(255)	Provider's user ID
access_token	TEXT	OAuth access token
refresh_token	TEXT	OAuth refresh token
expires_at	INT	Token expiration
token_type	VARCHAR(50)	Token type
scope	VARCHAR(255)	OAuth scope

Indexes:

provider, providerAccountId (UNIQUE)
userId

Session

Active user sessions (NextAuth).

Column	Type	Description
id	CUID	Primary key
sessionToken	VARCHAR(255)	Unique session token
userId	INT	Foreign key to User
expires	DATETIME	Session expiration
ipAddress	VARCHAR(45)	Client IP
userAgent	VARCHAR(500)	Client user agent
lastActive	DATETIME	Last activity

Indexes:

sessionToken (UNIQUE)
userId

Audit & Analytics Tables

AuditLog

System audit trail.

Column	Type	Description
id	INT	Primary key
userId	INT	Acting user
action	VARCHAR(100)	Action performed
resource	VARCHAR(100)	Resource type
resourceId	INT	Resource ID
ipAddress	VARCHAR(45)	Client IP
userAgent	TEXT	Client user agent
metadata	JSON	Additional context
createdAt	DATETIME	Action timestamp

Indexes:

userId
action
createdAt

Enums

UserRole

CUSTOMER, ADMIN, MODERATOR, SUPPORT

OrderStatus

PROCESSING, SHIPPED, DELIVERED, CANCELLED

AddressType

SHIPPING, BILLING

PromotionType

PERCENTAGE, FIXED, BOGO, BUNDLE, FREE_GIFT, TIERED, FLASH_SALE

DiscountType

PERCENTAGE, FIXED_AMOUNT

TicketCategory

ORDER, PRODUCT, SHIPPING, REFUND, TECHNICAL, OTHER

TicketPriority

LOW, MEDIUM, HIGH, URGENT

TicketStatus

OPEN, IN_PROGRESS, WAITING_CUSTOMER, RESOLVED, CLOSED

MessageSenderType

CUSTOMER, AGENT, SYSTEM

Relationships Summary

Parent	Child	Type	Description
User	Order	1:N	User's orders
User	Address	1:N	User's addresses
User	Review	1:N	User's reviews
User	Cart	1:N	Cart items
User	Wishlist	1:N	Wishlist items
User	CustomerLoyalty	1:1	Loyalty membership
Order	OrderItem	1:N	Order line items
Product	OrderItem	1:N	Product in orders
Product	ProductImage	1:N	Product images
Product	Review	1:N	Product reviews
Category	Product	1:N	Category products
Category	Category	1:N	Subcategories
Promotion	PromoCode	1:N	Promo codes
Promotion	PromotionUsage	1:N	Usage tracking
SupportTicket	SupportMessage	1:N	Ticket messages
SupportTicket	TicketHistory	1:N	Status changes

Database Migrations

Prisma manages schema migrations. To apply changes:

# Generate migration
npx prisma migrate dev --name description

# Apply to production
npx prisma migrate deploy

# Reset database (development only)
npm run db:reset

Seed Scripts

Available seed commands:

npm run db:seed-all        # Seed all data
npm run db:seed-products   # Products and categories
npm run db:seed-users      # User accounts
npm run db:seed-orders     # Order history
npm run db:seed-reviews    # Product reviews
npm run db:seed-promotions # Promotions and codes
npm run db:seed-loyalty    # Loyalty program
npm run db:seed-support    # Support tickets
npm run db:seed-affiliate  # Affiliate program