# Database architecture

Prefix `alya_hs_`. CPT `alya_booking` is the operational booking row (source of truth for WP). Custom tables are mirrors.

## CPT

`alya_booking` + post meta (`_alya_booking_id`, hotel/flight fields, payment_status, holder JSON, raw JSON).

## Custom tables (BackOfficeSchema + Schema)

agencies, agency_users, customers, hotels (cache), destinations, pricing_rules, booking_index, booking_rooms, booking_guests, booking_prices, booking_policies, payments, audit_log, credit_tx, api_logs, webhook_events, booking_status_log, documents, email_log, flight_sessions, booking_amendments, flight_services.

## Identifiers

- Supplier: `nuitee_booking_id`  
- Internal: `alya_ref` (Numbering)  
- Agency: default id 1  
- Customer: upsert from holder email  

No real PII dumped in this audit.

`credit_tx`: schema only. `default_commission` stored, not consumed by PricingEngine.

Schema version constant: `1.1.21-perf-idx`.

---

## Relationships (anonymized)

```
WP user ──< alya_hs_agency_users >── alya_hs_agencies (id=1 in test)
Traveler holder email → alya_hs_customers (upsert)
alya_booking (CPT) ← booking_index / rooms / guests / prices / policies
booking_index.nuitee_booking_id = LiteAPI bookingId
alya_hs_payments.cpt / transaction ref
alya_hs_documents.booking
alya_hs_webhook_events.booking_id
alya_hs_flight_sessions.session
```

Example (DEMONSTRATION ONLY, not a live dump):

| Object | Example |
|--------|---------|
| CPT | `alya_booking` post 35811 (format as seen on payments UI) |
| Supplier id | LiteAPI `bookingId` string |
| Payment | method TRANSACTION_ID, status succeeded |
| Agency | id 1, country DZ, currency EUR |

No holder emails or names are copied here.

## Indexes

`alya_hs_api_logs`: created_at, channel, level, http_status, prebook_id, booking_id. Hotels cache: city, updated_at (`1.1.21-perf-idx`).

## Helpdesk

Separate `alya_hs_helpdesk_*` (tickets, messages, sessions, logs, notifications, categories).