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.

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

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.

packages/db/src/schema

enums.ts — all PostgreSQL enums (not a table)

auth.ts — user, session, account, verification (4)

profile.ts — customer_profile, staff_profile (2)

service.ts — service_category, service, staff_service (3)

schedule.ts — staff_schedule, staff_time_off, business_hour, holiday (4)

booking.ts — booking, booking_service, booking_status_log, waitlist (4)

invoice.ts — invoice, invoice_item (2)

membership.ts — spa_membership_tier, spa_membership (2)

offer.ts — offer, offer_service, offer_redemption (3)

lead.ts — lead, lead_note (2)

crm.ts — customer_tag, customer_tag_assignment, customer_note (3)

loyalty.ts — loyalty_account, loyalty_transaction (2)

notification.ts — notification, push_subscription (2)

branch.ts — branch (1)

system.ts — daily_sales_summary, monthly_gst_summary, audit_log, system_setting (4)

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.

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.

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

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

One offer per customer per day

Unique constraint on offer_redemption (customer + date).

One active membership per customer

Partial unique index on spa_membership where status = 'active'.

One service type per booking

A booking is salon OR spa, never mixed.

Snapshot pricing

booking_service.price_at_booking_paise and invoice_item.unit_price_paise are frozen copies.

Conventions

Money, dates, layering, and the API response envelope

Background Jobs

The cron + QStash jobs that maintain this data

API Reference

Endpoints that read and write these tables

Getting started

System design

Standards

Application surface

Platform

Quality & security

Operations

Delivery workflow

Conventions

Schema files & table groupings

Key enums

ID & number formats

GST (18% inclusive)

Loyalty (gems) rules

Booking lifecycle

Business rules enforced at the DB level

Conventions

Background Jobs

API Reference

​Conventions

​Schema files & table groupings

​Key enums

​ID & number formats

​GST (18% inclusive)

​Loyalty (gems) rules

​Booking lifecycle

​Business rules enforced at the DB level

​Related pages

Conventions

Background Jobs

API Reference

Conventions

Schema files & table groupings

Key enums

ID & number formats

GST (18% inclusive)

Loyalty (gems) rules

Booking lifecycle

Business rules enforced at the DB level

Related pages