Database schema details, booking state machine, background job specifications, and sequence diagrams for Royal Glow.

Low-Level Design

This document captures the mechanics: the booking state machine, ID/number formats, GST and gems math, all 19 background jobs, request lifecycles, indexes, and security implementation. Money is always integer paise; status transitions are always logged in booking_status_log.

Booking State Machine

pending
  ├── confirmed   (receptionist approves + assigns staff)
  ├── rejected    (receptionist rejects)
  └── cancelled   (customer cancels before confirmation)

confirmed
  ├── in_progress  (service started)
  ├── cancelled    (customer cancels, within window)
  ├── rescheduled  (customer reschedules)
  └── no_show      (customer didn't arrive, +15min after end_time)

in_progress
  └── completed    (service done, invoice generated, gems awarded)

Walk-ins skip pending — they go directly to confirmed. Every status transition is recorded in booking_status_log with timestamp, actor (user ID), and reason.

ID & Number Formats

BK-{branch_code}-{YYMM}-{H|S}-{5_random}[-M]

Examples:
  BK-RS-2605-H-38291    ← Salon booking, Rayasandra, May 2026
  BK-RS-2605-S-72841    ← SPA booking
  BK-RS-2605-S-91023-M  ← SPA membership session

H = salon (hair/beauty)
S = spa
-M suffix = membership session

INV-{branch_number}-{financial_year}-{5_digit_random}

Example: INV-1-2627-92921
  ← Branch 1, Financial Year 2026-27, random 92921

Indian financial year runs April–March. A date in May 2026 → FY "2627". A date in February 2026 → FY "2526".

GST Calculation

All prices are GST-inclusive (18%, SAC 999721). Back-calculate the taxable base:

const GST_RATE = 0.18

function splitGST(inclusivePaise: number) {
  const basePaise = Math.round(inclusivePaise / (1 + GST_RATE))
  const gstPaise = inclusivePaise - basePaise
  const cgstPaise = Math.floor(gstPaise / 2)   // CGST = half of GST
  const sgstPaise = gstPaise - cgstPaise         // SGST = remainder
  return { basePaise, gstPaise, cgstPaise, sgstPaise, totalPaise: inclusivePaise }
}

CGST and SGST are equal halves (intra-state, Karnataka). The base is rounded; GST is the remainder — so base + gst always reconstructs the original inclusive amount exactly.

Gems (Loyalty) Calculation

// Earn rate: 1 gem per ₹100 invoiced (floor)
function calculateGemsEarned(invoiceTotalPaise: number): number {
  return Math.floor(invoiceTotalPaise / 10000)  // 10000 paise = ₹100
}

// Only on invoice_type = 'service'
// NOT on membership purchases or membership sessions
// Gems expire 365 days from earn date

No-Show Policy Logic

No-show count (last 90 days)	Action
1–3	CRM tag "No-Show Risk", no booking restriction
4+	`booking_requires_approval = true` on `customer_profile` → Manager must approve future bookings

Recovery: 3 consecutive completed bookings reset no_show_count. Walk-in no-shows do NOT count.

Background Jobs (19 Total)

QStash scheduled jobs — including the seven that were formerly pg_cron.

Job	Schedule (UTC)	What It Does
1. Nightly sales summary	`0 18 * * *` (11:30 PM IST)	Aggregates daily revenue into `daily_sales_summary`
2. Membership auto-expire	`30 18 * * *` (midnight IST)	Sets `status = 'expired'` on memberships past `expires_at`
3. Offer auto-expire	`0 18 * * *`	Sets `is_active = false` on offers past `end_date`
4. Session cleanup	`0 21 * * 0` (2:30 AM IST Sunday)	Deletes expired Better Auth sessions
5. pprd sync	`30 19 * * *` (1 AM IST)	Triggers GitHub Actions to reset pprd from prod
6. Monthly GST summary	`0 18 1 * *` (11:30 PM IST, 1st of month)	Aggregates monthly GST into `monthly_gst_summary`
7. Gems auto-expire	`0 18 * * *`	Offsets earned transactions older than 365 days

QStash scheduled jobs delivered as HTTP callbacks.

Job	Schedule	What It Does
8. Appointment reminders	Every 15 min	Sends push + email for bookings in next 24h and 1h
9. Membership expiry alerts	Daily	Sends alerts at 30d/7d/1d before expiry
10. Birthday emails	Daily	Sends birthday offer to customers with birthday today
11. Membership usage nudges	Weekly	Nudges members with >50% hours unused
12. Lead follow-ups	Every 4h	Alerts receptionist about leads with no contact in 48h
13. Daily sales report	Daily 9 PM IST	Sends Slack + email report to owner/manager
14. Weekly report	Monday 9 AM IST	Sends weekly summary
15. Gems expiry reminder	Daily	Push notification 7 days before gems expire

QStash triggered jobs fired by API routes with a delay.

Job	Trigger	Delay	What It Does
16. Post-service follow-up	Booking completed	+24h	Sends review request email/WhatsApp
17. Stale booking alert	Booking created (pending)	+2h	Alerts receptionist if still pending
18. No-show check	Booking confirmed	+15min after end_time	Marks no-show if customer didn't arrive
19. Membership expired notice	Membership expires	+1h	Sends expiry notice to customer

Realtime Channels (Ably)

Channel	Who Subscribes	Events
`booking:{bookingId}`	Customer	`status_changed`
`admin:bookings:{branchId}`	Receptionist/Manager	`booking_created`, `booking_updated`
`admin:schedule:{date}`	Manager	`schedule_updated`, `leave_approved`

Token Auth: clients receive a scoped Ably JWT from POST /api/ably/token. The token is scoped to only the channels the user is allowed to subscribe to (based on their role and user ID).

Request Lifecycles

The four core write paths, step by step.

Customer submits the booking form → POST /api/bookings.

Validate session (requireSession).

Zod-validate the request body.

Check slot availability (Redis cache → DB).

Validate all service IDs exist and are active.

Get the default staff for each service.

Generate the booking number (BK-RS-YYMM-H/S-XXXXX).

INSERT the booking row (status: pending).

INSERT booking_service rows (price snapshot).

Enqueue QStash job 17 (stale booking alert, +2h).

Invalidate the Redis slot cache.

Publish an Ably event to admin:bookings:{branchId}.

Return { success: true, data: { bookingId, bookingNumber } }.

Receptionist clicks "Mark Complete" → POST admin.theroyalglow.in/api/bookings/{id}/complete.

Validate session (requireRole: receptionist+).

Validate the booking exists and is in_progress.

Calculate the invoice total (sum of booking_service prices).

splitGST(totalPaise) → base, CGST, SGST.

Generate the invoice number (INV-1-YYZZ-XXXXX).

INSERT the invoice row.

INSERT invoice_item rows (price + name snapshot).

UPDATE booking status → completed.

Calculate gems earned (floor(total / 10000)).

UPSERT loyalty_account, INSERT loyalty_transaction.

Generate the PDF (React Email → PDF → R2 upload).

Send the invoice email via Resend (synchronous).

Fire the Meta CAPI Purchase event (server-side).

Enqueue QStash job 16 (post-service follow-up, +24h).

Publish an Ably event to booking:{bookingId}.

Return { success: true, data: { invoiceId, invoiceNumber } }.

Receptionist records a SPA session → POST admin.theroyalglow.in/api/memberships/{id}/sessions.

Validate session (requireRole: receptionist+).

Validate the membership is active and has sufficient hours.

Validate the service is a SPA service.

Deduct hours from spa_membership.used_hours_minutes.

INSERT the booking row (status: completed, total: ₹0).

INSERT the booking_service row.

INSERT the invoice row (type: membership_session, total: ₹0).

INSERT the invoice_item row.

NO gems awarded (membership sessions don't earn gems).

Return { success: true, data: { bookingId, remainingMinutes } }.

Customer submits the /book form (Meta ad landing page) → POST /api/leads.

Rate-limit check (3 per minute per IP).

Zod-validate (name, phone, serviceInterestedId).

Normalise the Indian phone number (+91XXXXXXXXXX).

INSERT the lead row (status: new, source: meta_ad).

Fire the Meta CAPI Lead event (server-side).

Enqueue QStash job 12 (lead follow-up, +48h).

Redirect to /?book=1&leadId={id}. When the customer books, lead.converted_booking_id is set and lead.status → booked.

Database Indexes

Key indexes for query performance:

-- Booking queries
CREATE INDEX idx_booking_customer ON booking(customer_id);
CREATE INDEX idx_booking_branch_date ON booking(branch_id, booking_date);
CREATE INDEX idx_booking_status ON booking(status);

-- Availability queries
CREATE INDEX idx_booking_staff_date ON booking(assigned_staff_id, booking_date)
  WHERE status IN ('confirmed', 'in_progress');

-- Membership queries
CREATE INDEX idx_membership_customer_active ON spa_membership(customer_id)
  WHERE status = 'active';

-- Loyalty queries
CREATE INDEX idx_loyalty_tx_account ON loyalty_transaction(loyalty_account_id);
CREATE INDEX idx_loyalty_tx_expiry ON loyalty_transaction(expires_at)
  WHERE transaction_type = 'earn' AND is_expired = false;

-- Lead queries
CREATE INDEX idx_lead_status ON lead(status);
CREATE INDEX idx_lead_source ON lead(source);

Was this page helpful?

Low-Level Design

Low-Level Design

Booking State Machine

ID & Number Formats

GST Calculation

Gems (Loyalty) Calculation

No-Show Policy Logic

Background Jobs (19 Total)

Realtime Channels (Ably)

Request Lifecycles

Database Indexes

Security Implementation

High-Level Design

Data Model

Background Jobs

On this page

Low-Level Design

Input validation

Rate limiting

CSP headers

SQL injection prevention

CORS

Webhook verification

High-Level Design

Data Model

Background Jobs

On this page