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.
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.
Prop
Type
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.
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.
Prop
Type
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 GSTLoyalty (gems) rules
- Earn: 1 gem per ₹100 invoiced (floor), on
invoice_type = 'service'only. - No gems on
membership_purchaseormembership_sessioninvoices. - 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
Related pages
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
Was this page helpful?