> ## Documentation Index
> Fetch the complete documentation index at: https://docs.theroyalglow.in/llms.txt
> Use this file to discover all available pages before exploring further.

# Low-Level Design

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

# Low-Level Design

<Info>
  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`.
</Info>

## Booking State Machine

```text theme={null}
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)
```

<Warning>
  **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.
</Warning>

## ID & Number Formats

<Tabs>
  <Tab title="Booking">
    ```text theme={null}
    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
  </Tab>

  <Tab title="Invoice">
    ```text theme={null}
    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".
  </Tab>
</Tabs>

## GST Calculation

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

```typescript theme={null}
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

```typescript theme={null}
// 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)

<Tabs>
  <Tab title="Scheduled (former pg_cron)">
    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            |
  </Tab>

  <Tab title="Scheduled (HTTP)">
    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            |
  </Tab>

  <Tab title="Triggered">
    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         |
  </Tab>
</Tabs>

## 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.

<Tabs>
  <Tab title="Booking creation">
    Customer submits the booking form → `POST /api/bookings`.

    <Steps>
      <Step>
        Validate session (`requireSession`).
      </Step>

      <Step>
        Zod-validate the request body.
      </Step>

      <Step>
        Check slot availability (Redis cache → DB).
      </Step>

      <Step>
        Validate all service IDs exist and are active.
      </Step>

      <Step>
        Get the default staff for each service.
      </Step>

      <Step>
        Generate the booking number (`BK-RS-YYMM-H/S-XXXXX`).
      </Step>

      <Step>
        INSERT the `booking` row (status: pending).
      </Step>

      <Step>
        INSERT `booking_service` rows (price snapshot).
      </Step>

      <Step>
        Enqueue QStash job 17 (stale booking alert, +2h).
      </Step>

      <Step>
        Invalidate the Redis slot cache.
      </Step>

      <Step>
        Publish an Ably event to `admin:bookings:{branchId}`.
      </Step>

      <Step>
        Return `{ success: true, data: { bookingId, bookingNumber } }`.
      </Step>
    </Steps>
  </Tab>

  <Tab title="Booking completion">
    Receptionist clicks "Mark Complete" → `POST admin.theroyalglow.in/api/bookings/{id}/complete`.

    <Steps>
      <Step>
        Validate session (`requireRole: receptionist+`).
      </Step>

      <Step>
        Validate the booking exists and is `in_progress`.
      </Step>

      <Step>
        Calculate the invoice total (sum of `booking_service` prices).
      </Step>

      <Step>
        `splitGST(totalPaise)` → base, CGST, SGST.
      </Step>

      <Step>
        Generate the invoice number (`INV-1-YYZZ-XXXXX`).
      </Step>

      <Step>
        INSERT the `invoice` row.
      </Step>

      <Step>
        INSERT `invoice_item` rows (price + name snapshot).
      </Step>

      <Step>
        UPDATE booking status → completed.
      </Step>

      <Step>
        Calculate gems earned (`floor(total / 10000)`).
      </Step>

      <Step>
        UPSERT `loyalty_account`, INSERT `loyalty_transaction`.
      </Step>

      <Step>
        Generate the PDF (React Email → PDF → R2 upload).
      </Step>

      <Step>
        Send the invoice email via Resend (synchronous).
      </Step>

      <Step>
        Fire the Meta CAPI Purchase event (server-side).
      </Step>

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

      <Step>
        Publish an Ably event to `booking:{bookingId}`.
      </Step>

      <Step>
        Return `{ success: true, data: { invoiceId, invoiceNumber } }`.
      </Step>
    </Steps>
  </Tab>

  <Tab title="Membership session">
    Receptionist records a SPA session → `POST admin.theroyalglow.in/api/memberships/{id}/sessions`.

    <Steps>
      <Step>
        Validate session (`requireRole: receptionist+`).
      </Step>

      <Step>
        Validate the membership is active and has sufficient hours.
      </Step>

      <Step>
        Validate the service is a SPA service.
      </Step>

      <Step>
        Deduct hours from `spa_membership.used_hours_minutes`.
      </Step>

      <Step>
        INSERT the `booking` row (status: completed, total: ₹0).
      </Step>

      <Step>
        INSERT the `booking_service` row.
      </Step>

      <Step>
        INSERT the `invoice` row (type: membership\_session, total: ₹0).
      </Step>

      <Step>
        INSERT the `invoice_item` row.
      </Step>

      <Step>
        NO gems awarded (membership sessions don't earn gems).
      </Step>

      <Step>
        Return `{ success: true, data: { bookingId, remainingMinutes } }`.
      </Step>
    </Steps>
  </Tab>

  <Tab title="Lead capture">
    Customer submits the `/book` form (Meta ad landing page) → `POST /api/leads`.

    <Steps>
      <Step>
        Rate-limit check (3 per minute per IP).
      </Step>

      <Step>
        Zod-validate (`name`, `phone`, `serviceInterestedId`).
      </Step>

      <Step>
        Normalise the Indian phone number (`+91XXXXXXXXXX`).
      </Step>

      <Step>
        INSERT the `lead` row (status: new, source: meta\_ad).
      </Step>

      <Step>
        Fire the Meta CAPI Lead event (server-side).
      </Step>

      <Step>
        Enqueue QStash job 12 (lead follow-up, +48h).
      </Step>

      <Step>
        Redirect to `/?book=1&leadId={id}`. When the customer books, `lead.converted_booking_id` is set and `lead.status` → booked.
      </Step>
    </Steps>
  </Tab>
</Tabs>

## Database Indexes

Key indexes for query performance:

```sql theme={null}
-- 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);
```

## Security Implementation

<AccordionGroup>
  <Accordion title="Input validation">
    Every API route validates input with Zod `.safeParse()` before any business logic runs. Raw client input never reaches the database.
  </Accordion>

  <Accordion title="Rate limiting">
    `@upstash/ratelimit` with a sliding-window algorithm, applied in Next.js middleware before auth checks.
  </Accordion>

  <Accordion title="CSP headers">
    Strict Content-Security-Policy with nonce-based script loading, configured in `next.config.ts`.
  </Accordion>

  <Accordion title="SQL injection prevention">
    Drizzle ORM uses parameterized queries exclusively. No raw SQL string concatenation anywhere in the codebase.
  </Accordion>

  <Accordion title="CORS">
    Exact origin matching (`theroyalglow.in` only). No wildcard.
  </Accordion>

  <Accordion title="Webhook verification">
    All inbound webhooks (QStash, Meta, AiSensy) verify HMAC signatures before processing.
  </Accordion>
</AccordionGroup>

## Related Pages

<Columns cols={2}>
  <Card title="High-Level Design" href="/docs/system-design/high-level-design">
    Architecture, decisions matrix, and NFRs
  </Card>

  <Card title="Data Model" href="/docs/data-model">
    All 38 tables, enums, and conventions
  </Card>

  <Card title="Background Jobs" href="/docs/background-jobs">
    Full job inventory and heartbeats
  </Card>
</Columns>


## Related topics

- [High-Level Design](/content/docs/system-design/high-level-design.md)
- [System Design](/content/docs/system-design/index.md)
- [Tech Stack](/content/docs/tech-stack.md)
- [Overview](/content/docs/index.md)
- [Admin — Bookings](/content/docs/api-reference/admin-bookings.md)