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

# Data Model

> The 38-table Drizzle schema for Royal Glow — conventions, table groupings, enums, and ID formats.

# Data Model

Royal Glow runs on **Neon PostgreSQL 16** with **Drizzle ORM** (pure TypeScript,
edge-native). The schema lives in `packages/db/src/schema/` as **15 files**
defining **38 tables** that cover auth, profiles, services, scheduling,
bookings, billing, memberships, offers, CRM, leads, loyalty, notifications,
branches, and system operations.

<Info>
  Every monetary value is an **integer in paise** (₹1 = 100 paise) — never a
  float. Every timestamp is `timestamptz` (stored UTC, displayed IST). Prices
  are GST-inclusive at 18%.
</Info>

## Conventions

These rules are mandatory across every table.

| Name               | Type                 | Description                                                                                                |
| ------------------ | -------------------- | ---------------------------------------------------------------------------------------------------------- |
| `Primary keys`     | `text`               | App-generated via nanoid() (or cuid2()). No auto-increment serials — prevents enumeration attacks.         |
| `Money`            | `integer (paise)`    | ₹1,000.00 = 100000. No floating point; round at display only.                                              |
| `Timestamps`       | `timestamptz`        | Everywhere. Stored UTC, displayed IST (UTC+5:30).                                                          |
| `Date display`     | `DD/MM/YYYY`         | Via Intl.DateTimeFormat('en-IN').                                                                          |
| `Currency display` | `₹1,00,000.00`       | Indian grouping via Intl.NumberFormat('en-IN'), always 2 decimals.                                         |
| `Naming`           | `snake_case`         | Singular table names (booking, not bookings).                                                              |
| `Enums`            | `native CREATE TYPE` | Centralised in enums.ts.                                                                                   |
| `Deletes`          | `hard delete`        | No soft-delete columns. Tracked via audit\_log.                                                            |
| `Foreign keys`     | `explicit ON DELETE` | CASCADE for children, RESTRICT for references. Indexed on all FK columns.                                  |
| `Snapshots`        | `frozen copies`      | Price + service/staff name frozen on booking\_service and invoice\_item so historical records never drift. |
| `GST`              | `18% inclusive`      | SAC 999721. Back-calc: base = round(price ÷ 1.18).                                                         |

## Schema files & table groupings

The 15 schema files in `packages/db/src/schema/` map to 38 tables. The tree below
lists the actual `pgTable` names defined in each file.

<Tree>
  <Tree.Folder name="packages/db/src/schema" defaultOpen>
    <Tree.File name="enums.ts — all PostgreSQL enums (not a table)" />

    <Tree.File name="auth.ts — user, session, account, verification (4)" />

    <Tree.File name="profile.ts — customer_profile, staff_profile (2)" />

    <Tree.File name="service.ts — service_category, service, staff_service (3)" />

    <Tree.File name="schedule.ts — staff_schedule, staff_time_off, business_hour, holiday (4)" />

    <Tree.File name="booking.ts — booking, booking_service, booking_status_log, waitlist (4)" />

    <Tree.File name="invoice.ts — invoice, invoice_item (2)" />

    <Tree.File name="membership.ts — spa_membership_tier, spa_membership (2)" />

    <Tree.File name="offer.ts — offer, offer_service, offer_redemption (3)" />

    <Tree.File name="lead.ts — lead, lead_note (2)" />

    <Tree.File name="crm.ts — customer_tag, customer_tag_assignment, customer_note (3)" />

    <Tree.File name="loyalty.ts — loyalty_account, loyalty_transaction (2)" />

    <Tree.File name="notification.ts — notification, push_subscription (2)" />

    <Tree.File name="branch.ts — branch (1)" />

    <Tree.File name="system.ts — daily_sales_summary, monthly_gst_summary, audit_log, system_setting (4)" />
  </Tree.Folder>
</Tree>

<Info>
  The `auth.ts` tables (`user`, `session`, `account`, `verification`) are owned
  by Better Auth's Drizzle adapter. Don't add custom columns to them — extend
  via `customer_profile` / `staff_profile` instead. Enums are defined separately
  in `enums.ts` (not counted as a table). Totals: **15 files**, **38 tables**.
</Info>

## Key enums

Defined natively in PostgreSQL via `enums.ts`.

| Name                    | Type   | Description                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| ----------------------- | ------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| `booking_status`        | `enum` | pending, confirmed, rejected, in\_progress, completed, cancelled, no\_show, rescheduled                                                                                                                                                                                                                                                                                                                                                                            |
| `lead_status`           | `enum` | new, contacted, follow\_up, booked, won, lost                                                                                                                                                                                                                                                                                                                                                                                                                      |
| `payment_status`        | `enum` | pending, paid, refunded                                                                                                                                                                                                                                                                                                                                                                                                                                            |
| `payment_method`        | `enum` | cash, upi, card, online                                                                                                                                                                                                                                                                                                                                                                                                                                            |
| `invoice_type`          | `enum` | service, membership\_purchase, membership\_session                                                                                                                                                                                                                                                                                                                                                                                                                 |
| `service_type`          | `enum` | salon, spa                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| `discount_type`         | `enum` | percentage, flat, combo\_price                                                                                                                                                                                                                                                                                                                                                                                                                                     |
| `spa_membership_status` | `enum` | active, expired, cancelled                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| `waitlist_status`       | `enum` | waiting, notified, booked, expired, cancelled                                                                                                                                                                                                                                                                                                                                                                                                                      |
| `loyalty_tx_type`       | `enum` | earned, redeemed, expired, adjusted                                                                                                                                                                                                                                                                                                                                                                                                                                |
| `leave_approval_status` | `enum` | pending, approved, rejected                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| `leave_type`            | `enum` | sick, casual, personal, other                                                                                                                                                                                                                                                                                                                                                                                                                                      |
| `staff_designation`     | `enum` | receptionist, stylist, therapist, manager                                                                                                                                                                                                                                                                                                                                                                                                                          |
| `gender`                | `enum` | male, female, other, prefer\_not\_to\_say                                                                                                                                                                                                                                                                                                                                                                                                                          |
| `branch_status`         | `enum` | operational, temporarily\_closed, opens\_soon, shutdown                                                                                                                                                                                                                                                                                                                                                                                                            |
| `audit_action`          | `enum` | create, update, delete, status\_change                                                                                                                                                                                                                                                                                                                                                                                                                             |
| `notification_type`     | `enum` | 24 values — reminder\_24h, reminder\_1h, booking\_confirmed, booking\_rescheduled, booking\_cancelled, booking\_rejected, membership\_created, membership\_session\_recorded, membership\_expiry\_30d/7d/1d, membership\_expired, membership\_hours\_low, membership\_usage\_nudge, birthday\_offer, post\_service\_followup, leave\_submitted/approved/rejected, lead\_follow\_up\_due, stale\_pending\_booking, no\_show\_check, gems\_expiry\_7d, gems\_expired |
| `notification_channel`  | `enum` | push, email                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| `notification_status`   | `enum` | pending, sent, failed                                                                                                                                                                                                                                                                                                                                                                                                                                              |

## ID & number formats

| Entity     | Format                                            | Example              |
| ---------- | ------------------------------------------------- | -------------------- |
| Booking    | `BK-{branch_code}-{YYMM}-{H\|S}-{5_random}[-M]`   | `BK-RS-2605-H-38291` |
| Invoice    | `INV-{branch_number}-{financial_year}-{5_random}` | `INV-1-2627-92921`   |
| Membership | `RG-MEM-{YY}-{branch_number}-{5_random}`          | `RG-MEM-26-1-90872`  |

`H` = salon (hair/beauty), `S` = spa. The `-M` suffix marks a membership
session booking.

## GST (18% inclusive)

Customer-facing prices already include GST. The invoice back-calculates the base
and tax from the inclusive amount.

```ts theme={null}
const GST_RATE = 0.18

function splitGST(inclusivePaise: number) {
  const basePaise = Math.round(inclusivePaise / (1 + GST_RATE))
  const gstPaise = inclusivePaise - basePaise
  return { basePaise, gstPaise, totalPaise: inclusivePaise }
}

// splitGST(118000) → { basePaise: 100000, gstPaise: 18000, totalPaise: 118000 }
// ₹1,180.00 inclusive = ₹1,000.00 base + ₹180.00 GST
```

## Loyalty (gems) rules

* **Earn:** 1 gem per ₹100 invoiced (floor), on `invoice_type = 'service'` only.
* **No gems** on `membership_purchase` or `membership_session` invoices.
* **Expiry:** 365 days from earn date, auto-deducted by the QStash gems-auto-expire job.
* **Redemption:** against specific catalogue services — not a ₹ discount.
* **Cannot combine** with an offer on the same booking.

## Booking lifecycle

```text theme={null}
pending → confirmed/rejected → in_progress → completed
                                            ↘ cancelled    (from pending/confirmed)
                                            ↘ no_show       (from confirmed, +15min after end_time)
                                            ↘ rescheduled   (from confirmed)
```

Walk-ins skip `pending` and go straight to `confirmed`. Status transitions are
recorded in `booking_status_log`.

## Business rules enforced at the DB level

<AccordionGroup>
  <Accordion title="One offer per customer per day">
    Unique constraint on `offer_redemption` (customer + date).
  </Accordion>

  <Accordion title="One active membership per customer">
    Partial unique index on `spa_membership` where `status = 'active'`.
  </Accordion>

  <Accordion title="One service type per booking">
    A booking is salon OR spa, never mixed.
  </Accordion>

  <Accordion title="Snapshot pricing">
    `booking_service.price_at_booking_paise` and `invoice_item.unit_price_paise` are frozen copies.
  </Accordion>
</AccordionGroup>

## Related pages

<Columns cols={2}>
  <Card title="Conventions" href="/docs/conventions">
    Money, dates, layering, and the API response envelope
  </Card>

  <Card title="Background Jobs" href="/docs/background-jobs">
    The cron + QStash jobs that maintain this data
  </Card>

  <Card title="API Reference" href="/docs/api-reference">
    Endpoints that read and write these tables
  </Card>
</Columns>


## Related topics

- [High-Level Design](/content/docs/system-design/high-level-design.md)
- [Deployment](/content/docs/deployment.md)
- [Conventions](/content/docs/conventions.md)
- [Low-Level Design](/content/docs/system-design/low-level-design.md)
- [Tech Stack](/content/docs/tech-stack.md)