Data Model — Convex Schema Reference

Status: Canonical Last Updated: 2026-05-11 Source: apps/backend/convex/schema.ts

Doc Status: Excellent | ✓ All 6 checks passed

Overview

This document describes the Convex database schema for House of Legends, covering all tables from experiences (show templates) through reservations, orders, gamification, and integrations. The schema follows a hierarchical structure: experiences → experienceEvents → reservations.

Tech Stack — Key paths and import conventions
Booking Flow — Uses experiences, events, reservations
Payments — Uses reservations, payments tables
Staff POS — Uses tables, orders, menuItems
Gamification — Uses guestProfiles, challenges tables
Inquiry Forms — Uses formSessions table
Notifications — Uses notifications, notificationLogs tables

Entity Relationships

Booking Hierarchy

experiences (show templates) → experienceEvents (scheduled instances) → reservations (bookings) Related: checkIns (QR scans), bookingDrafts (in-progress bookings)

Add-ons

addOns (global add-on library) → linked to reservations.addOns[]

Staff Operations

users (staff/admin accounts) → tables (venue layout) → orders → orderItems → menuItems

Guest Engagement

guestProfiles (created on QR scan) → guestReactions, photoSubmissions → photoLikes, spinResults

Challenges

challengeConfig → challengeSubmissions

Notifications

notifications (in-app alerts)

Inquiries

formSessions (inquiry form submissions) → inquirySessions (admin overlay) → inquiryFollowUps

Payments

payments (OnePay transaction tracking) → linked to reservations

Tables

experiences (Level 1 — Show Templates)

Field	Type	Description
code	`string`	Unique code e.g. “CINE-GASTRO”
title	`string`	Show title
tagline	`string`	Short description
description	`string`	Full rich text
embeddedVideo	`string?`	YouTube/Vimeo URL
gallery	`string[]`	Image URLs
thumbnailUrl	`string?`	Admin-configurable
supportedTicketTypes	`("DINNER_THEATRE" \| "SHOW_ONLY")[]`	Ticket options
defaultDinnerPrice	`number`	VND
defaultShowOnlyPrice	`number`	VND
defaultCapacity	`number`	Max 32
status	`"ACTIVE" \| "DRAFT" \| "ARCHIVED"`	Lifecycle
slug	`string`	URL-friendly ID
createdAt, updatedAt	`number`	Unix timestamps

Indexes: by_status, by_slug, by_code

experienceEvents (Level 2 — Scheduled Instances)

Field	Type	Description
experienceId	`Id<"experiences">`	FK to template
code	`string`	Unique e.g. “TMTL260521N”
date	`string`	ISO date “2026-05-21”
time	`string`	”19:30”
dinnerPrice	`number`	Denormalized
experienceOnlyPrice	`number`	Denormalized
experienceOnlyEnabled	`boolean`	Admin toggle
actualCapacity	`number`	Override possible
bookedCount	`number`	Calculated from PAID
thumbnailUrl	`string?`	Per-event override
status	`"SCHEDULED" \| "CANCELLED" \| "SOLD_OUT"`	Event status
assignedTables	`Id<"tables">[]`	Linked tables
createdAt, updatedAt	`number`	Unix timestamps

Indexes: by_experience, by_date, by_experience_date, by_date_status, by_code

reservations (Level 3 — Bookings)

Field	Type	Description
eventId	`Id<"experienceEvents">`	FK to event
customerFirstName	`string`
customerLastName	`string`
customerEmail	`string`
customerPhone	`string?`
customerNote	`string?`
ticketType	`"DINNER_THEATRE" \| "SHOW_ONLY"`
quantity	`number`	Guest count
bundleId	`string?`	Bundle pricing
guests	`number`	Same as quantity
tableId	`Id<"tables">?`	Assigned table
addOns	`{addOnId, quantity}[]`	Selected add-ons
subtotal	`number`	Before discounts
totalAmount	`number`	Final price
paymentStatus	see below
status	see below
paymentMethod	`string?`
onePayOrderId	`string?`	OnePay reference
qrCode	`string?`	Generated QR
qrCodeUrl	`string?`	QR image URL
token	`string?`	Unique for QR scan
bookingExpiresAt	`number?`	Seat hold expiry
checkedInAt	`number?`	When scanned
discountAmount	`number`	Bundle discount
discountPercent	`number`
vipSurcharge	`number`
dayOfWeekSurcharge	`number`	Friday/Saturday
smallPartySurcharge	`number`	1-2 guests
bookingStep	see below	Flow tracking
createdAt, updatedAt	`number`

paymentStatus values: PENDING, PAID, REFUNDED, FAILED, CANCELLED, REFUND_PENDING status values: PENDING, PAID_CONFIRMED, CHECKED_IN, CANCELLED, REFUNDED bookingStep values: EXPERIENCE, SHOW, TICKETS, ZONE, BUNDLE, ADDONS, CUSTOMER_INFO, PAYMENT, CONFIRMATION Indexes: by_event, by_email, by_payment_status, by_expires, by_table, by_booking_step, by_token, by_vpcMerchTxnRef

addOns

Field	Type	Description
name	`string`
description	`string`
price	`number`	VND
imageUrl	`string?`
type	`"COCKTAIL" \| "FOOD" \| "UPGRADE" \| "OTHER"`
enabled	`boolean`	Global toggle
createdAt, updatedAt	`number`

Indexes: by_enabled

users (Staff/Admin)

Field	Type	Description
email	`string`	Unique
passwordHash	`string?`
role	`"ADMIN" \| "STAFF"`
name	`string`
createdAt, updatedAt	`number`

Indexes: by_email, by_role

tables

Field	Type	Description
name	`string`	”T01”, etc.
capacity	`number`
status	`"AVAILABLE" \| "OCCUPIED" \| "RESERVED" \| "MAINTENANCE"`
position	`{x, y}?`	Floor plan
createdAt, updatedAt	`number`

Indexes: by_status

menuItems

Field	Type	Description
name	`string`
description	`string`
price	`number`	VND
category	see below
station	`"KITCHEN" \| "BAR"`
imageUrl	`string?`
isAvailable	`boolean`
isFeatured	`boolean?`
createdAt, updatedAt	`number`

category values: FOOD, BEVERAGE, DESSERT, COCKTAIL, WINE, BEER Indexes: by_category, by_available, by_station

orders

Field	Type	Description
tableId	`Id<"tables">`
reservationId	`Id<"reservations">?`
eventId	`Id<"experienceEvents">?`
status	`"OPEN" \| "SUBMITTED" \| "PREPARING" \| "SERVED" \| "PAID" \| "CANCELLED"`
subtotal	`number`
totalAmount	`number`
notes	`string?`
createdAt, updatedAt	`number`

Indexes: by_table, by_status, by_event

orderItems

Field	Type	Description
orderId	`Id<"orders">`
menuItemId	`Id<"menuItems">`
quantity	`number`
unitPrice	`number`
totalPrice	`number`
notes	`string?`
status	`"PENDING" \| "PREPARING" \| "READY" \| "SERVED"`
station	`"KITCHEN" \| "BAR"`
isComp	`boolean`	Complimentary
compSource	`"SPIN" \| "PHOTO_WIN" \| "GOOGLE_REVIEW"?`
createdAt	`number`

Indexes: by_order, by_order_status, by_station_status

guestProfiles

Field	Type	Description
reservationId	`Id<"reservations">?`
tableId	`Id<"tables">?`
token	`string`	QR identifier
avatarUrl	`string?`
avatarStorageId	`string?`
nickname	`string`
country	`string`
origin	`string`
moodTags	`string[]`
bio	`string?`
experienceDate	`string`	ISO date
checkedIn	`boolean`
createdAt, updatedAt	`number`

Indexes: by_reservation, by_experience_date, by_token

guestReactions

Field	Type	Description
fromProfileId	`Id<"guestProfiles">`
toProfileId	`Id<"guestProfiles">`
reactionType	`"WAVE" \| "CHEERS" \| "HEART"`
experienceDate	`string`
createdAt	`number`

Indexes: by_to_profile, by_from_profile, by_experience_date

bookingDrafts

Field	Type	Description
sessionId	`string`	User auth subject
eventId	`Id<"experienceEvents">?`
experience	`string?`
ticketType	`"DINNER_THEATRE" \| "SHOW_ONLY"?`
quantity	`number?`
reservationId	`Id<"reservations">?`
bookingExpiresAt	`number?`
addOns	`{addOnId, quantity}[]?`
bundle	`string?`
guests	`number?`
customerInfo	`{firstName, lastName, email, phone}?`
currentStep	see below?
expiresAt	`number`	Auto-cleanup
createdAt, updatedAt	`number`

currentStep values: EXPERIENCE, SHOW, TICKETS, BUNDLE, ADDONS, CUSTOMER_INFO, PAYMENT, CONFIRMATION Indexes: by_session, by_expires

formSessions

Field	Type	Description
sessionId	`string`	localStorage UUID
formType	see below
data	`string`	JSON
submitted	`boolean`
expiresAt	`number`	7 days
createdAt, updatedAt	`number`

formType values: CONTACT, VENUE_RENTAL, PRIVATE_EVENTS, WORKSHOPS, ARTIST_PROPOSAL, HOST_AN_EVENT, FRENCH_MENTALIST, DINNER_THEATER, CHECKOUT, RESERVATION, PROFILE, EXPERIENCE, ADDON Indexes: by_session_type, by_expires, by_data_search

inquirySessions

Field	Type	Description
formSessionId	`Id<"formSessions">`
formType	same as formSessions
status	`"NEW" \| "READ" \| "REPLIED" \| "ARCHIVED"`
adminNotes	`string?`
reviewedBy	`string?`	Clerk user ID
reviewedAt	`number?`
createdAt, updatedAt	`number`

Indexes: by_formSession, by_formType, by_status, by_formType_status

inquiryFollowUps

Field	Type	Description
inquiryId	`Id<"inquirySessions">`
authorId	`string`	Clerk user ID
authorName	`string`
content	`string`
createdAt	`number`

Indexes: by_inquiry

challengeConfig

Field	Type	Description
challengeType	`"PHOTO_WALL" \| "LUCKY_SPIN" \| "GOOGLE_REVIEW"`
enabled	`boolean`
maxValue	`number?`
prizeDescription	`string?`
steps	`{order, text, imageUrl?}[]`
activeForDates	`string[]`
createdAt, updatedAt	`number`

Indexes: by_type

photoSubmissions

Field	Type	Description
profileId	`Id<"guestProfiles">`
orderId	`Id<"orders">?`
tableId	`Id<"tables">`
imageUrl	`string`
caption	`string?`
likeCount	`number`
status	`"ACTIVE" \| "HIDDEN"`
winner	`boolean`
experienceDate	`string`
createdAt, updatedAt	`number`

Indexes: by_experience_date, by_profile, by_status, by_table_experience, by_likes

photoLikes

Field	Type	Description
submissionId	`Id<"photoSubmissions">`
profileId	`Id<"guestProfiles">`
createdAt	`number`

Indexes: by_submission, by_profile_submission, by_profile

spinPrizes

Field	Type	Description
label	`string`
prizeType	`"MENU_ITEM" \| "DISCOUNT" \| "FREE_ITEM"`
menuItemId	`Id<"menuItems">?`
discountPercent	`number?`
weight	`number`
enabled	`boolean`
createdAt, updatedAt	`number`

Indexes: by_enabled

spinResults

Field	Type	Description
profileId	`Id<"guestProfiles">`
orderId	`Id<"orders">`
tableId	`Id<"tables">`
prizeId	`Id<"spinPrizes">`
displayText	`string`
experienceDate	`string`
createdAt, updatedAt	`number`

Indexes: by_experience_date, by_table_experience, by_profile

challengeSubmissions

Field	Type	Description
profileId	`Id<"guestProfiles">`
orderId	`Id<"orders">`
tableId	`Id<"tables">`
challengeType	`"GOOGLE_REVIEW"`
screenshotUrl	`string`
status	`"PENDING" \| "APPROVED" \| "REJECTED"`
rewardMenuItemId	`Id<"menuItems">?`
reviewedBy	`Id<"users">?`
reviewedAt	`number?`
notes	`string?`
experienceDate	`string`
createdAt, updatedAt	`number`

Indexes: by_status, by_experience_date, by_table_experience

notifications

Field	Type	Description
type	`"ORDER_READY" \| "NEW_RESERVATION" \| "EXPERIENCE_REMINDER" \| "ALERT" \| "SYSTEM"`
title	`string`
message	`string`
isRead	`boolean`
priority	`"LOW" \| "MEDIUM" \| "HIGH"`
metadata	`string?`	JSON
createdAt	`number`

Indexes: by_read, by_type, by_created

notificationLogs

Field	Type	Description
notificationType	see below
channel	`"EMAIL" \| "WHATSAPP"`
recipient	`string`
subject	`string?`
status	`"SUCCESS" \| "FAILED"`
errorMessage	`string?`
reservationId	`Id<"reservations">?`
createdAt	`number`

notificationType values: EMAIL_CONFIRMATION, EMAIL_CANCELLATION, WHATSAPP_CONFIRMATION, EMAIL_ADMIN_NEW_BOOKING Indexes: by_reservation, by_status, by_created

zohoSyncLogs

Field	Type	Description
operationType	see below
entityType	see below
entityId	`string?`	Zoho ID
status	`"SUCCESS" \| "FAILED"`
errorMessage	`string?`
reservationId	`Id<"reservations">?`
createdAt	`number`

operationType values: CONTACT_UPSERT, DEAL_CREATE, INVOICE_CREATE, INVOICE_MARK_PAID entityType values: CONTACT, DEAL, INVOICE Indexes: by_reservation, by_status, by_created

payments

Field	Type	Description
reservationId	`Id<"reservations">`
vpcMerchTxnRef	`string`	OnePay reference
vpcTransactionNo	`string?`
amount	`number`
currency	`string`
status	`"PENDING" \| "SUCCESS" \| "FAILED"`
responseCode	`string?`
message	`string?`
card	`string?`
cardNum	`string?`
createdAt, updatedAt	`number`

Indexes: by_vpcMerchTxnRef

checkIns

Field	Type	Description
ticketId	`string`	Reservation token
eventId	`Id<"experienceEvents">`
checkedInAt	`number`	Unix timestamp
checkedInBy	`string?`	Staff user ID

Indexes: by_ticket, by_event

Foundation

Guest-Facing

Operations

Integrations

Admin

Project

​Data Model — Convex Schema Reference

​Overview

​Related specs

​Entity Relationships

​Booking Hierarchy

​Add-ons

​Staff Operations

​Guest Engagement

​Challenges

​Notifications

​Inquiries

​Payments

​Tables

​experiences (Level 1 — Show Templates)

​experienceEvents (Level 2 — Scheduled Instances)

​reservations (Level 3 — Bookings)

​addOns

​users (Staff/Admin)

​tables

​menuItems

​orders

​orderItems

​guestProfiles

​guestReactions

​bookingDrafts

​formSessions

​inquirySessions

​inquiryFollowUps

​challengeConfig

​photoSubmissions

​photoLikes

​spinPrizes

​spinResults

​challengeSubmissions

​notifications

​notificationLogs

​zohoSyncLogs

​payments

​checkIns