Data Model

Reference for the core entities that run the ERP — what they represent, the primary key / main foreign keys, life-cycle states, and the permissions that gate reads and writes. Source of truth is supabase/migrations/*.sql; the TypeScript projection lives in src/integrations/supabase/types.ts (generated) and the domain types in src/types/index.ts.

Naming conventions

Tables use PascalCase, quoted: public."Booking".
Columns are camelCase, also quoted in SQL: "bookingNo", "totalAmount".
Primary keys are TEXT with app-generated UUIDs (except a few newer tables that default gen_random_uuid()::text).
Timestamps are timestamp(3) without time zone on legacy tables, TIMESTAMPTZ on newer ones.

Entity map (top ~10 entities)

erDiagram
    Customer ||--o{ Booking : "places"
    Agent ||--o{ Booking : "sells (optional B2B)"
    TravelGroup ||--o{ Booking : "groups together"
    Booking ||--o{ BookingPassenger : "has passengers"
    Booking ||--o{ Payment : "receives"
    Booking ||--o{ Invoice : "bills"
    Booking ||--o{ VisaCase : "triggers visa"
    Booking ||--o{ TicketRecord : "triggers tickets"
    TravelGroup ||--o{ GroupInvoice : "issues to payers"
    TravelGroup ||--o{ GroupHotelAssignment : "assigns hotels"
    Supplier ||--o{ SupplierTransaction : "booked against"
    Account ||--o{ JournalLine : "debited/credited"
    JournalEntry ||--|{ JournalLine : "has 2+ balanced lines"
    Payment }o--|| Account : "lands in"
    AccountingPeriod ||--o{ JournalEntry : "contains (by date)"
    BankStatementImport ||--o{ BankStatementLine : "has lines"
    BankStatementLine }o--o| JournalEntry : "matched to"
    TDSRate ||--o{ TDSDeduction : "applied via"
    TDSDeduction }o--|| Payment : "deducted on"
    HotelInventory ||--o{ GroupHotelAssignment : "allocated to groups"

Core entities

Booking

Represents: One customer's (or partner's) purchase of travel services, attached to a travel group.

Aspect	Detail
Table	`public."Booking"` — `supabase/migrations/20260113130121_remote_schema.sql:91`
PK	`id` TEXT
FKs	`customerId` → `Customer`, `groupId` → `TravelGroup`, `agentId` → `Agent` (nullable), `quotationId` → `Quotation`, `payerId` → `Customer`
Status fields	`status` ∈ `pending \| pending_finance \| confirmed \| cancelled \| needs_correction`, plus `opsApprovalStatus` and `financeApprovalStatus` (both `pending \| approved \| rejected \| cancelled \| needs_correction`)
Lifecycle	Draft → PendingOpsReview → PendingFinanceApproval → Approved, with branches to OnHold / Cancelled. See 01 · Customer lifecycle and Bookings.
Read	`bookings.view`
Write	`bookings.create`, `bookings.edit`, `bookings.delete` (all RLS-enforced via `auth_user_has_permission()`)

Customer

Represents: An end-traveller — whether they booked directly or through a B2B partner. Stores passport + KYC fields, GSTIN, Aadhaar, PAN, plus optional agent linkage.

Aspect	Detail
Table	`public."Customer"` — `supabase/migrations/20260113130121_remote_schema.sql:157`
PK	`id` TEXT
Party code	`customerCode` — `CU-000123`, `UNIQUE NOT NULL`, given by the trigger `Customer_party_code` from `customer_code_seq`, permanent. Partners (`Agent.partnerCode`, `BP-`), suppliers (`Supplier.supplierCode`, `SU-`) and employees (`EmployeeProfile.employeeCode`, `EM-`) work the same way. PTY-001, PTY-002, migration `20261001100000_every_party_has_a_code.sql`.
FKs	`agentId` → `Agent` (optional — which partner introduced them), `sourceAgentId` (original source)
Lifecycle	No explicit states — soft-deleted via `deletedAt`. Status is implicit through related bookings.
Read	`customers.view`
Write	`customers.create`, `customers.edit`, `customers.delete`

Supplier

Represents: A third-party vendor (airline, hotel, visa consultant, food caterer, transport, general). Has its own ledger (SupplierTransaction) for AP aging.

Aspect	Detail
Table	`public."Supplier"` — `supabase/migrations/20260330110000_suppliers.sql:2`
PK	`id` TEXT
Categories	`ticketing \| visa \| hotel \| food \| transport \| general` (`src/types/index.ts:102`)
Child	`SupplierTransaction` (debit/credit/refund/adjustment)
Read	`inventory.view` (see Sidebar drift note in `docs/PERMISSIONS.md` §7 — intended target is `suppliers.view`)
Write	`suppliers.create`, `suppliers.edit`, `suppliers.delete`

Partner (Agent)

Represents: A B2B partner — a travel sub-agent who books on behalf of their own customers. Distinct from the staff User who logs in — a partner typically has one User account whose userId is linked here.

Aspect	Detail
Table	`public."Agent"` — `supabase/migrations/20260113130121_remote_schema.sql:34`
PK	`id` TEXT
FKs	`userId` → `User` (optional — the portal login)
Status	`active \| inactive \| suspended \| pending`
Fields	`commissionRate`, `creditLimit`, `totalBookings`, `totalRevenue`
Read	`agents.view`
Write	`agents.create`, `partners.edit`, `partners.delete` (legacy names retained for RLS; see `docs/PERMISSIONS.md` §8.1)

TravelGroup (Group)

Represents: A scheduled group departure — the aggregate that flights, hotels, ground transfers, and passengers attach to. A booking is always for a group.

Aspect	Detail
Table	`public."TravelGroup"` — `supabase/migrations/20260113130121_remote_schema.sql:593`
PK	`id` TEXT
FKs	`payerCustomerId` → `Customer` (the customer who funds the whole group, e.g. a VIP family); many child tables (`GroupHotelAssignment`, `GroupFlightOption`, `GroupInvoice`, etc.)
Group types	`UMRAH \| ZIYARAT \| UMRAH_ZIYARAT \| HAJ \| HAJ_ZIYARAT \| GENERAL_DOMESTIC \| GENERAL_INTERNATIONAL` (`src/types/index.ts:252`)
Status	`planning \| open \| full \| departed \| completed \| closed \| cancelled`, with an override field (`statusOverride`) for manual intervention
Read	`groups.view`
Write	`groups.create`, `groups.edit`, `groups.delete`, `group_pricing.edit` (rate sheet), `group_invoices.*` (invoice lifecycle)

HotelInventory (Hotel)

Represents: A hotel room-night inventory record — typically leased from a supplier for a date range and a city (Makkah / Madinah / elsewhere).

Aspect	Detail
Table	`public."HotelInventory"` — `supabase/migrations/20260113130121_remote_schema.sql:285` (plus hotel-specific migrations `20260331100000_hotel_inventory_supplier_link.sql`, `20260331130000_hotel_inventory_lease_dates.sql`, `20260403000000_hotel_assignment_dates.sql`)
PK	`id` TEXT
FKs	`supplierId` → `Supplier`, `accountId` → `Account` (GL cost account)
Child	`GroupHotelAssignment` — which group is placed in which hotel for which date range
Read	`hotels.view`
Write	`hotels.create`, `hotels.edit`, `hotels.delete`, `hotels.export`

Voucher (numbered JournalEntry)

Represents: A finance voucher — receipt, payment, contra, or manual journal. In this schema there is no separate Voucher table; a voucher is a JournalEntry with voucherNo populated.

Aspect	Detail
Table	`public."JournalEntry"` — base at `supabase/migrations/20260113130121_remote_schema.sql:323`; `voucherNo` added by `supabase/migrations/20260330170000_account_hierarchy_and_vouchers.sql`
PK	`id` TEXT
Key fields	`voucherNo`, `memo`, `referenceType`, `referenceId`, `approvalStatus`, `currency`, `fxRate`, `originalAmount`
Idempotency	`memo` may be tagged `[idem:<key>]` — see `src/lib/api.ts:2128-2155`
Read	`finance.view`
Write	`finance.create`, `finance.edit`; approval / reversal gated by `finance.journals.approve` (default) and the break-glass `finance.journals.approve_own` / `finance.journals.reverse_own` — see maker-checker in `supabase/migrations/20260418150000_journal_maker_checker_overrides.sql`

JournalEntry

Represents: The header of a double-entry ledger posting. Paired with 2+ JournalLine rows that sum to zero.

Aspect	Detail
Table	`public."JournalEntry"` — `supabase/migrations/20260113130121_remote_schema.sql:323`
PK	`id` TEXT
Extensions	`currency` + `fxRate` (`20260412050000_journal_entry_currency.sql`, `20260418060325_journal_fx_rate.sql`), `originalAmount` (`20260412060000`), `approvalStatus` (`20260412070000`, default `pending` per `20260418110000`), `reversedEntryId`, `reversalReason` (`20260330180000_journal_reversal_tracking.sql`)
Period lock	Enforced by trigger — `supabase/migrations/20260418150000_journal_period_lock_trigger.sql`
Maker-checker	Creator cannot approve / reverse own entries without override permission

JournalLine

Represents: One debit-or-credit line on a journal entry. Enforces double-entry via the sum-to-zero invariant.

Aspect	Detail
Table	`public."JournalLine"` — `supabase/migrations/20260113130121_remote_schema.sql:334`
PK	`id` TEXT
FKs	`entryId` → `JournalEntry`, `accountId` → `Account`
Extensions	`originalDebit` / `originalCredit` for multi-currency (`20260418150000_journal_line_original_amounts.sql`)

Account

Represents: A chart-of-accounts ledger account. Hierarchical (group vs. ledger) with parentId self-reference. Canonical chart seeded by supabase/migrations/20260403040000_seed_chart_of_accounts.sql + canonicalised by 20260412020000_chart_of_accounts_canonical.sql.

Aspect	Detail
Table	`public."Account"` — base at `supabase/migrations/20260113130121_remote_schema.sql:22`, extended with `parentId`, `isGroup`, `groupType`, `openingBalance`, `openingBalanceSide`, `sortOrder`, etc.
PK	`id` TEXT
Unique	`code`
Types	`ASSET \| LIABILITY \| EQUITY \| INCOME \| EXPENSE` (see `AccountType` enum)
Read	`finance.view`
Write	`finance.edit` (RLS — `supabase/migrations/20260330150000_account_rls_policies.sql`)

FinancialAccount vs Account

There is a separate FinancialAccount table (supabase/migrations/20260401100000_accounts.sql) representing payment instruments (bank account, card, cash, digital wallet). It is linked to a GL Account via glAccountId. Think of FinancialAccount as "the physical bank account where money actually sits" and Account as "the GL line that tracks it".

AccountingPeriod

Represents: A calendar window (usually a month) that can be open, locked, or closed for posting. Journal entries are assigned to a period by date.

Aspect	Detail
Table	`public."AccountingPeriod"` — `supabase/migrations/20260330190000_accounting_periods.sql:2`
PK	`id` TEXT
Status	`open \| locked \| closed`
Trigger	`supabase/migrations/20260418150000_journal_period_lock_trigger.sql` rejects posts into locked/closed periods
Read	`finance.view`
Write	Coarse `finance.edit` today; granular `finance.periods.create \| .lock \| .unlock \| .close` and `finance.years.create \| .close` are seeded but not all wired in code yet — see `docs/PERMISSIONS.md` §4.4 and §8.10

Invoice

Represents: A customer-facing bill for a booking. Installment-capable (installmentNo).

Aspect	Detail
Table	`public."Invoice"` — `supabase/migrations/20260113130121_remote_schema.sql:305`
PK	`id` TEXT
FKs	`bookingId` → `Booking`
Status	`InvoiceStatus` enum — `OPEN \| PAID \| PARTIAL \| VOID` (see migration 20260411070000 for B2B extensions)
Sibling tables	`GroupInvoice` (for group-level billing to a payer, `supabase/migrations/20260418000000_group_pricing_and_invoices.sql:25`), `AgentInvoice` (B2B partner invoices, `supabase/migrations/20260409140000_agent_portal_v2.sql:7`)
Read / Write	Bookings → `bookings.view`; Group invoices → `group_invoices.view \| .create \| .edit \| .issue \| .cancel`; Agent invoices follow partner portal ownership checks

Payment

Represents: A cash / bank / card / UPI / cheque receipt against a booking.

Aspect	Detail
Table	`public."Payment"` — `supabase/migrations/20260113130121_remote_schema.sql:444`
PK	`id` TEXT
FKs	`bookingId` → `Booking`, `invoiceId` → `Invoice` (optional), `sourceAccountId` (`20260418123000_payment_source_account.sql`)
Method	`cash \| bank_transfer \| card \| cheque \| upi \| wallet`
Status	`pending \| verified \| rejected \| refunded`
Razorpay	`razorpayPaymentId` UNIQUE (dedup key for webhook — `supabase/migrations/20260418114458_razorpay_payment_fields.sql`)
Read	`finance.view`
Write	`finance.create` (record), `finance.payments.verify \| .reject \| .refund` (workflow — many still coarse-gated today; see `docs/PERMISSIONS.md` §8.10)

BankReconciliation

Represents: A bank statement import and its parsed lines, each of which can be matched to a JournalEntry / Payment / LedgerEntry.

Aspect	Detail
Tables	`public."BankStatementImport"`, `public."BankStatementLine"` — `supabase/migrations/20260418150000_bank_reconciliation.sql:15-68`
PK	`id` TEXT on each
FKs	`Import.financialAccountId` → `Account`, `Line.importId` → `BankStatementImport`, `Line.matchedEntryId` → (`journal_entry \| payment \| ledger_entry`) discriminated by `matchedEntryType`
Status	Import: `imported \| reconciled \| partial`; Line: `unmatched \| matched \| ignored`
Read	`finance.view` (RLS — `supabase/migrations/20260418150000_bank_reconciliation.sql:93`)
Write	`finance.edit`

TdsChallan

Represents: TDS (Tax Deducted at Source) infrastructure — rate master, per-deduction rows, and the plumbing for 26Q quarterly filing. Per-challan header is part of TDSDeduction.

Aspect	Detail
Tables	`public."TDSRate"`, `public."TDSDeduction"` (+ a couple of columns on `Supplier` for defaults) — `supabase/migrations/20260418150000_tds_scaffolding.sql`
PK	`id` TEXT (uuid default) on each
TDSRate keys	`section` (e.g. `194C`, `194J`, `194Q`), `deducteeType` (`individual \| huf \| company \| any`), `effectiveFrom` — combined unique
TDSDeduction	links to the `Payment` / `SupplierTransaction` that was deducted on, plus Form 281 challan + Form 16A certificate refs
GL	`2401 TDS Payable` under Duties & Taxes
Read	`finance.tds.view`
Write	`finance.tds.deduct`; export 26Q via `finance.tds.export` (permissions migration `20260418150100_tds_permissions.sql`)

GstReturn

Represents: GST (India) compliance surface. As of 2026-04-18 the schema provides primitives — GSTIN on Customer / Supplier, hsnCode / sacCode on line-item-like tables, GST amount + rate on Booking, and per-line GST on group expenses. A dedicated GstReturn table is not yet present; returns (GSTR-1 / GSTR-3B) are computed on demand from the journals via src/lib/gstReports.ts. Filing to the GSTN portal is a stub (src/lib/gstnApiClient.ts).

Aspect	Detail
Primitive columns	`Customer.gstNumber`, `Supplier.gstNumber`, `QuotationLineItem.hsnCode / sacCode`, `SupplierTransaction.hsnCode / sacCode`, `Invoice.hsnCode / sacCode` — `supabase/migrations/20260418150000_gst_primitives.sql`
Other GST migrations	`20260415090000_booking_gst_enabled.sql`, `20260415100000_gst_input_credit_and_supplier_gst.sql`, `20260415110000_group_expense_gst_and_journal.sql`
Read	`finance.reports.gst_summary.view` (seeded, per `docs/PERMISSIONS.md` §4.4)
Write	Coarse `finance.create / finance.edit` today; dedicated return-filing permissions are future work

Supporting entities (quick reference)

Entity	Table	Purpose	Read	Write
Quotation	`public."Quotation"` + `QuotationLineItem`	Price quote that can become a booking	`quotations.view`	`quotations.create`
Lead	`public."Lead"` + `LeadAttachment`	Inbound enquiry	`leads.view`	`leads.edit`
VisaCase	`public."VisaCase"` + `VisaDocument` + `VisaStatusHistory`	Per-passenger visa processing	`visa.view`	`visa.create`, `visa.edit`
TicketRecord	`public."TicketRecord"` + `TicketDocument` + `TicketIssueApproval`	Air ticket issuance	`tickets.view`	`tickets.edit`, `tickets.approve`
BookingPassenger	`public."BookingPassenger"` + `BookingPassengerFlight` + `BookingPassengerHotel`	Passenger manifest per booking	`bookings.view`	`bookings.edit`
CustomerRequest	`public."CustomerRequest"` + attachments + notes	Service requests (customer / ops)	`requests.view`	`requests.create`, `requests.edit`
FinancialAccount	`public."FinancialAccount"` + `FinancialAccountTransaction`	Physical bank / wallet / card instruments	`finance.view`	`finance.edit`
LedgerEntry	`public."LedgerEntry"`	Per-booking running ledger (mirrors journal but booking-scoped)	`finance.view`	via `finance.create / .edit` postings
GroupInvoice	`public."GroupInvoice"`	Payer-level invoices at group level	`group_invoices.view`	`group_invoices.create / .edit / .issue / .cancel`
CommunicationsLog	`public."CommunicationsLog"`	Any outbound message (WhatsApp, SMS, email)	Bookings / customer views	module-specific

State machines (reference)

The state rules live in the rulebook — 01 · Customer lifecycle for bookings, 06 · Visa for visa cases, AIR §24 for tickets — and are enforced by the database, not by advisory validators:

Booking — Draft → PendingOpsReview → PendingFinanceApproval → Approved, with OnHold and Cancelled branches.
Visa — NotStarted → Applied → UnderProcess → Issued | Rejected, plus SentToEmbassy detour.
Ticket — NotReady → NameUpdatePending → OnHold | FinanceCleared → Issued; issuance blocked unless finance-cleared or a GM exception proof exists.
GroupInvoice — DRAFT → ISSUED → PAID, with CANCELLED terminal (credit note posts reversal). See src/types/index.ts:369.

Respect the server-side validators

These are not advisory, and they are not checked in the browser. A booking's status can only change through submit_booking, ops_decide_booking, finance_decide_booking, resubmit_booking or the cancellation flow; a visa case's only through change_visa_status; a ticket's only through issue_ticket and its siblings. A trigger refuses any other status change from a browser session, and every transition writes a *StatusHistory row.

One phone per person

User.phone, Agent.phone (where Agent.userId is the login) and Customer.phone (where Customer.userId is the login) are one person's one number. The triggers User_phone_sync, Agent_phone_sync and Customer_phone_sync copy a change to the other two, as typed; numbers compare on the last ten digits (phone_match_key()), a blank never overwrites, and a staff login's number changes on the login only. Customer.agentId (the partner who booked a customer) and PartnerContact are other people and are not synced. The copies are audited with metadata.reason set from the transaction-local app.audit_reason (ACC-076, migration 20261002120000_one_phone_per_person.sql; differences saved before it: runbook).

Drift notes

Legacy permission names like partners.create | .edit | .delete are retained for RLS compatibility (docs/PERMISSIONS.md §8.1); new code should prefer the agents.* family.
A number of granular permissions are seeded but not wired yet (finance report exports, workflow splits, admin splits) — docs/PERMISSIONS.md §4.4 and §8.10 list them. Until wired, coarse permissions (finance.create, finance.edit, admin.view, admin.edit) still gate those actions.
AgentWallet / wallet payment methods were removed in supabase/migrations/20260411060000_remove_wallet_system.sql; the wallet enum value on Payment.method remains for historical rows.