Chapter 10.2 — Entity Relationship Diagrams

1. Purpose

This chapter visualises the relationships between core entities. It is organised by module cluster: tenancy, booking, customer & supplier, ledger, invoicing & payments, integrations, audit. Each diagram is intentionally scoped — the full schema would be unreadable in one picture.

2. Conventions

||--o{ — one-to-many.
||--|| — one-to-one (rare; typically denormalised).
}o--o{ — many-to-many (junction table shown explicitly).
Optional relationships indicated by o on the optional side.
Diagrams show only owning relationships — every entity ultimately rolls up to partners via partner_id; that edge is omitted for clarity except in the tenancy diagram.

3. Tenancy & user identity

erDiagram PARTNERS ||--o{ PARTNER_USERS : memberships PARTNERS ||--o{ PARTNER_CURRENCIES : enabled_currencies PARTNERS ||--o{ GROUPS : "may belong to (consolidation)" USERS ||--o{ PARTNER_USERS : memberships USERS ||--o{ USER_MFA_METHODS : enrolled USERS ||--o{ PERSONAL_ACCESS_TOKENS : issues PARTNER_USERS ||--o{ PARTNER_USER_ROLES : assigned_roles ROLES ||--o{ PARTNER_USER_ROLES : grants ROLES ||--o{ ROLE_PERMISSIONS : has PERMISSIONS ||--o{ ROLE_PERMISSIONS : granted_via GROUPS ||--o{ GROUP_PARTNERS : members PARTNERS ||--o{ GROUP_PARTNERS : member_of

4. Customer master

erDiagram CUSTOMERS ||--o{ CUSTOMER_ADDRESSES : has CUSTOMERS ||--o{ CUSTOMER_CONTACTS : has CUSTOMERS ||--o{ CUSTOMER_CREDIT_HISTORY : audit CUSTOMERS ||--o| CUSTOMER_BALANCES : materialised CUSTOMERS ||--o{ CUSTOMER_KYC_DOCUMENTS : holds CUSTOMERS ||--o{ CUSTOMER_SANCTIONS_SCREENINGS : screened_by CUSTOMERS ||--o| CUSTOMER_PRICING_PROFILES : may_have CUSTOMERS ||--o{ CUSTOMERS : "parent_customer_id (self-ref)" CUSTOMERS ||--o| CUSTOMER_CREDIT_BALANCE : "wallet credit"

5. Supplier master

erDiagram SUPPLIERS ||--o{ SUPPLIER_ADDRESSES : has SUPPLIERS ||--o{ SUPPLIER_CONTACTS : has SUPPLIERS ||--o{ SUPPLIER_BANK_DETAILS : payout_destinations SUPPLIERS ||--o{ SUPPLIER_BANK_HISTORY : audit SUPPLIERS ||--o{ SUPPLIER_COMMISSION_RULES : commission_schedules SUPPLIERS ||--o| SUPPLIER_BALANCES : materialised SUPPLIERS ||--o{ SUPPLIER_CREDENTIALS : api_credentials SUPPLIERS ||--o{ SUPPLIER_SANCTIONS_SCREENINGS : screened_by

6. Booking cluster

erDiagram BOOKINGS ||--o{ BOOKING_PASSENGERS : carries BOOKINGS ||--o{ BOOKING_PNRS : supplier_record_locators BOOKINGS ||--o{ BOOKING_TSTS : fare_snapshots BOOKINGS ||--o{ BOOKING_SEGMENTS : "air segments" BOOKINGS ||--o{ BOOKING_ROOMS : "hotel rooms" BOOKINGS ||--o{ BOOKING_SERVICES : "transfers / insurance / tours" BOOKINGS ||--o{ BOOKING_ANCILLARIES : "bag / seat / meal" BOOKINGS ||--o{ BOOKING_TICKETS : issued_tickets BOOKINGS ||--o{ BOOKING_EMDS : ancillary_docs BOOKINGS ||--o{ BOOKING_TAX_BREAKDOWN : per_xt_lines BOOKINGS ||--o{ BOOKING_FARE_RULES : captured_rules BOOKINGS ||--o{ BOOKING_HISTORY : state_transitions BOOKINGS ||--o{ BOOKING_DOCUMENTS : "S3 refs" BOOKINGS ||--o{ BOOKING_REVENUE_RECOGNITION_LOG : recognition_audit BOOKING_TICKETS ||--o{ COMMISSION_ACCRUALS : generates BOOKING_TICKETS ||--o{ MEMOS : "may attract ADM/ACM" BOOKING_TICKETS ||--o{ REFUNDS : may_be_refunded CUSTOMERS ||--o{ BOOKINGS : buys SUPPLIERS ||--o{ BOOKINGS : supplies

7. Ledger

erDiagram CHART_OF_ACCOUNTS ||--o{ CHART_OF_ACCOUNTS_HISTORY : versioned CHART_OF_ACCOUNTS ||--o{ JOURNAL_ENTRY_LINES : referenced_by JOURNAL_ENTRIES ||--o{ JOURNAL_ENTRY_LINES : contains JOURNAL_ENTRIES ||--o| JOURNAL_ENTRIES : "reversed_by_je_id" BOOKINGS ||--o{ JOURNAL_ENTRIES : source INVOICES ||--o{ JOURNAL_ENTRIES : source PAYMENTS ||--o{ JOURNAL_ENTRIES : source PAYOUTS ||--o{ JOURNAL_ENTRIES : source REFUNDS ||--o{ JOURNAL_ENTRIES : source MEMOS ||--o{ JOURNAL_ENTRIES : source ACCOUNTING_PERIODS ||--o{ JOURNAL_ENTRIES : period_assignment FX_RATES ||--o{ JOURNAL_ENTRY_LINES : conversion

8. Invoicing & payments

erDiagram INVOICES ||--o{ INVOICE_LINES : contains INVOICES ||--o{ INVOICE_TAX_LINES : tax_breakdown INVOICES ||--o| CREDIT_NOTES : "may have credit notes" CREDIT_NOTES ||--o{ CREDIT_NOTE_LINES : contains INVOICES ||--o{ PAYMENT_INVOICE_APPLICATIONS : settled_by PAYMENTS ||--o{ PAYMENT_INVOICE_APPLICATIONS : applies PAYMENTS ||--o{ PAYMENTS : "reversed_by_payment_id" PAYOUTS ||--o{ PAYOUT_SUPPLIER_INVOICE_APPLICATIONS : applies BOOKINGS ||--o{ INVOICE_LINES : feeds CUSTOMERS ||--o{ INVOICES : owes SUPPLIERS ||--o{ SUPPLIER_INVOICES : "we owe" SUPPLIER_INVOICES ||--o{ PAYOUT_SUPPLIER_INVOICE_APPLICATIONS : settled_by BANK_ACCOUNTS ||--o{ PAYMENTS : receives_to BANK_ACCOUNTS ||--o{ PAYOUTS : sent_from CASH_DRAWERS ||--o{ PAYMENTS : "cash receipts" CASH_DRAWERS ||--o{ CASH_DRAWER_SESSIONS : open_close_events

9. Refunds, memos, commissions

erDiagram REFUNDS ||--o{ REFUND_QUOTES : history REFUNDS ||--o{ JOURNAL_ENTRIES : posts REFUNDS ||--o| BOOKINGS : "against booking" REFUNDS ||--o| BOOKING_TICKETS : "against ticket" MEMOS ||--o{ MEMO_DOCUMENTS : evidence MEMOS ||--o{ MEMO_DISPUTES : disputes MEMOS ||--o{ MEMO_RECOVERIES : "customer recovery" MEMOS ||--o| BOOKING_TICKETS : "linked to ticket" BSP_IMPORT_FILES ||--o{ MEMOS : imported_from COMMISSION_ACCRUALS ||--o| BOOKING_TICKETS : "ticket" COMMISSION_ACCRUALS ||--o| SUPPLIER_COMMISSION_RULES : "rule version" COMMISSION_SETTLEMENTS ||--o{ COMMISSION_ACCRUALS : clears COMMISSION_DISPUTES ||--o| COMMISSION_ACCRUALS : disputes

10. Reconciliation

erDiagram BANK_STATEMENT_FILES ||--o{ BANK_STATEMENT_LINES : parsed BANK_STATEMENT_LINES ||--o{ RECON_MATCHES : matched_to PAYMENTS ||--o{ RECON_MATCHES : "match target" PAYOUTS ||--o{ RECON_MATCHES : "match target" RECON_RUNS ||--o{ RECON_MATCHES : produced RECON_RUNS ||--o{ RECON_EXCEPTIONS : exceptions RECON_RUNS ||--o{ RECON_ADJUSTMENTS : adjusting_jes GATEWAY_SETTLEMENT_FILES ||--o{ GATEWAY_SETTLEMENT_LINES : parsed BSP_STATEMENT_FILES ||--o{ BSP_STATEMENT_LINES : parsed SUPPLIER_STATEMENT_FILES ||--o{ SUPPLIER_STATEMENT_LINES : parsed

11. Tax & period close

erDiagram TAX_RULES ||--o{ TAX_RULE_HISTORY : versioned TAX_RULES ||--o{ BOOKING_TAX_LINES : applies_to TAX_RULES ||--o{ INVOICE_TAX_LINES : applies_to TAX_RETURNS ||--o{ TAX_RETURN_PAYMENTS : settled_by TAX_RETURNS ||--o| ACCOUNTING_PERIODS : "covers period" ACCOUNTING_PERIODS ||--o{ PERIOD_CLOSE_RUNS : closed_by ACCOUNTING_PERIODS ||--o{ PERIOD_PRE_CLOSE_CHECKS : gate_state ACCOUNTING_PERIODS ||--o{ PERIOD_SNAPSHOTS : "TB / P&L / BS / CF" ACCOUNTING_PERIODS ||--o{ PERIOD_REOPENS : reopens ACCOUNTING_PERIODS ||--o{ JOURNAL_ENTRIES : assigns

12. Integrations

erDiagram SUPPLIERS ||--o{ SUPPLIER_CREDENTIALS : "api creds (encrypted)" SUPPLIER_CREDENTIALS ||--o{ SUPPLIER_REQUEST_LOG : "every API call" WEBHOOK_ENDPOINTS ||--o{ WEBHOOK_DELIVERIES : sent_via WEBHOOK_DELIVERIES ||--o{ WEBHOOK_DELIVERY_ATTEMPTS : retries PARTNERS ||--o{ WEBHOOK_ENDPOINTS : configures

13. Audit & history

erDiagram AUDIT_LOGS ||--o| USERS : actor AUDIT_LOGS ||--o| PERSONAL_ACCESS_TOKENS : actor AUDIT_LOGS ||--o| BOOKINGS : "target (when applicable)" AUDIT_LOGS ||--o| INVOICES : target AUDIT_LOGS ||--o| JOURNAL_ENTRIES : target AUDIT_LOGS ||--o| CUSTOMERS : target AUDIT_LOGS ||--o| SUPPLIERS : target CUSTOMERS ||--o{ CUSTOMERS_HISTORY : versioned SUPPLIERS ||--o{ SUPPLIERS_HISTORY : versioned BOOKINGS ||--o{ BOOKINGS_HISTORY : versioned CHART_OF_ACCOUNTS ||--o{ CHART_OF_ACCOUNTS_HISTORY : versioned TAX_RULES ||--o{ TAX_RULE_HISTORY : versioned

14. Notes on diagrams

These diagrams show logical relationships, not physical FK constraints in every case. For example: - audit_logs.target_entity_id is polymorphic (different target types live in the same column with target_entity_type discriminator) — no FK is enforced. - journal_entry_lines is dimensioned by customer_id, supplier_id, booking_id, etc. — multiple optional FKs. - Reference tables (airport_codes, currencies) are platform-shared; their relationships to per-partner tables are loose (no FK across partitions).

The physical FK enforcement matrix is documented in Chapter 10.3 — Table Reference.

15. Common pitfalls

⚠️ Treating polymorphic columns as FKs. The target_entity_type + target_entity_id pattern in audit logs is not a single FK; queries must join via discriminator.
⚠️ Cascading deletes through booking history. Booking history is the audit chain — never delete.
⚠️ Inferring relationships from naming. Always verify via Chapter 10.3.