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.

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.

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.

Overview

Product

Features

Engineering

System design

Engineering standards

API reference

Application surface

Platform

Quality & security

Operations

Reference

Data Model

Data Model

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

​Data Model

​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

Data Model

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