Data Model & Integrity
Understand the data architecture and validate relationships
Data Model Architecture
Understanding the relationships between entities
Customer / Contact
User or Contact entity - the customer
email
full_name
credits_balance
member_type
Order
ROOT ENTITY
Top-level container for quote/sale lifecycle
order_number
customer_email
status
totals
booking_ids[]
project_id
LineItem
Individual items in an order - pricing source of truth
order_id*REQUIRED
booking_id
item_id*REQUIRED
quantity
prices
fulfillment_status
BookingV2
Time-based reservation for schedulable items
order_id*REQUIRED
item_id
start_at
end_at
status
location_id
Invoice
Payment document generated from Order
invoice_number
order_id*REQUIRED
booking_id
totals
stripe_payment_link
Item
Catalog item (Studio, Gear, Ticket, etc.)
name
type
pricing
capabilities
inventory_quantity
Project
Optional: groups related orders/bookings
name
owner_user
budget_usd
budget_credits
Booking Lifecycle Flow
The journey from inquiry to fulfillment
1
Inquiry
Customer requests booking → Order created (status: inquiry)
2
Quote Draft
Staff adds LineItems → Order.totals calculated
3
Quote Sent
Order status → sent, customer receives quote
4
Accepted
Customer accepts → BookingV2 created (if schedulable)
5
Invoice
Invoice generated → stripe_payment_link created
6
Paid
Payment confirmed → Booking.status → confirmed
7
Fulfilled
LineItems fulfilled (tickets issued, gear allocated)
Entity Relationships
Customer / Contact
creates
Order
1:N
Order
contains
LineItem
1:N
REQUIRED
Order
may have
BookingV2
1:N
Order
generates
Invoice
1:1
LineItem
references
Item
N:1
REQUIRED
LineItem
may link to
BookingV2
1:1
BookingV2
references
Item
N:1
Project
groups
Order
1:N
Data Model Rules
✅ Required Patterns
- • Every BookingV2 MUST have an order_id
- • Every LineItem MUST have order_id and item_id
- • Order.booking_ids[] must sync with actual BookingV2 records
- • LineItem is the SOURCE OF TRUTH for pricing, not BookingV2
- • Order.totals are CALCULATED from LineItems
⚠️ Optional Patterns
- • LineItem.booking_id is optional (only for schedulable items)
- • Order.project_id is optional (only if order belongs to project)
- • Invoice.booking_id is optional (can invoice entire order)
- • Non-schedulable items (merch, credits, memberships) have LineItems but NO BookingV2
🚫 Anti-Patterns to Avoid
- • Never store pricing in BookingV2 - always use LineItem
- • Never create BookingV2 without a parent Order
- • Never create LineItem without order_id and item_id
- • Never manually calculate totals - use calculateOrderTotals function
- • Never create orphaned records (always validate foreign keys)
Example: Studio Booking Flow
// Step 1: Customer submits inquiry
Order.create({
customer_email: "user@example.com",
status: "inquiry",
inquiry_details: "Need Studio A for 4 hours"
})// Step 2: Staff creates quote (adds line items)
LineItem.create({
order_id: order.id,
item_id: "studio_a_id",
quantity: 4, // 4 hours
unit_price_usd: 50,
line_total_usd: 200
})calculateOrderTotals(order.id) // Updates Order.totals
Order.update(order.id, { status: "sent" })// Step 3: Customer accepts quote
Order.update(order.id, { status: "accepted" })// Create booking for time slot
BookingV2.create({
order_id: order.id,
item_id: "studio_a_id",
start_at: "2026-03-15T14:00:00Z",
end_at: "2026-03-15T18:00:00Z",
status: "reserved"
})// Update Order with booking reference
Order.update(order.id, { booking_ids: [booking.id] })
LineItem.update(lineItem.id, { booking_id: booking.id })// Step 4: Staff sends invoice
Invoice.create({
order_id: order.id,
booking_id: booking.id, // optional
customer_email: order.customer_email,
totals: order.totals, // inherited from order
stripe_payment_link: stripeUrl
})// Step 5: Payment received (webhook)
Order.update(order.id, { status: "paid" })
Invoice.update(invoice.id, { status: "paid" })
BookingV2.update(booking.id, { status: "confirmed" })// Fulfill line items
fulfillOrder(order.id) // Issues tickets, allocates resources
Data Integrity Checklist
When Creating Records:
- ✓Order first, then LineItems, then BookingV2 (if schedulable)
- ✓Always validate Item exists before creating LineItem
- ✓Update Order.booking_ids when creating BookingV2
- ✓Run calculateOrderTotals after modifying LineItems
When Deleting Records:
- ✓Delete in reverse order: BookingV2, then LineItems, then Order
- ✓Delete related Invoices when deleting Order
- ✓Delete related BookingMessages when deleting Booking
- ✓Update Order.booking_ids when deleting BookingV2