Entity Relationship Diagram

This document describes the Tours entity-relationship model. It focuses on the core tour, booking and operator entities and the foreign keys you can verify directly in src/product/booking/schema.ts, src/lib/operators.ts, src/lib/influencers.ts and src/lib/vendor-schema.ts. All keys are SERIAL integers; most relationships are enforced with PostgreSQL REFERENCES constraints. A few domain links (bookings → listings, bookings → units, listings.owner_id → hosts, listings.category_id → master_items, bookings.coupon_id → coupons) are soft (an integer id column with no REFERENCES) so provisioning order never blocks — these are noted below.

Remember the mapping: a tour is a listings row, a departure is a units row, an operator is a hosts row, and a traveler is a customers row.

Mermaid ERD

MERMAID

erDiagram
    customers ||--o| hosts : "is (operator)"
    customers ||--o{ bookings : "books"
    customers ||--o{ conversations : "chats (guest)"
    customers ||--o{ customer_verification_documents : "uploads"

    hosts ||--o{ listings : "owns (owner_id, soft)"
    hosts ||--o{ host_documents : "submits"
    hosts ||--o{ host_earnings : "earns"
    hosts ||--o{ host_payout_requests : "requests"
    hosts ||--o{ host_payment_history : "paid_via"
    hosts ||--o{ host_subscriptions : "subscribes"
    hosts ||--o{ conversations : "chats (host)"

    subscription_plans ||--o{ host_subscriptions : "sold_as"

    master_items ||--o{ listings : "categorizes (soft)"
    master_items ||--o{ listing_master_items : "tags"
    listings ||--o{ listing_master_items : "classified_by"
    listings ||--o{ listing_nearby_places : "near"

    listings ||--o{ units : "has departures"
    listings ||--o{ seasons : "prices by"
    seasons ||--o{ season_dates : "spans"
    units ||--o{ unit_rates : "priced"
    seasons ||--o{ unit_rates : "for"
    units ||--o{ unit_blocks : "blocks"
    units ||--o{ ical_feeds : "syncs"
    listings ||--o{ listing_reviews : "receives"

    amenity_groups ||--o{ amenities : "contains"

    listings ||--o{ bookings : "booked as (product_id, soft)"
    units ||--o{ bookings : "departure (departure_id, soft)"
    bookings ||--o{ booking_payments : "settles"
    bookings ||--o{ booking_extras : "adds"
    bookings ||--o{ booking_notes : "annotated"
    bookings ||--o{ booking_emails : "notified"
    coupons ||--o{ bookings : "discounts (coupon_id, soft)"

    host_payout_requests ||--o{ host_payment_history : "disbursed"
    host_payout_requests ||--o{ host_earnings : "batches (soft)"

    conversations ||--o{ messages : "contains"

    influencers ||--o{ influencer_referral_links : "owns"
    influencers ||--o{ influencer_bookings : "attributed"
    influencers ||--o{ influencer_commissions : "earns"
    influencers ||--o{ influencer_payout_requests : "requests"
    influencer_referral_links ||--o{ influencer_clicks : "clicked"
    influencer_bookings ||--o{ influencer_commissions : "generates"
    bookings ||--o{ influencer_bookings : "referred (booking_id, soft)"

    blog_categories ||--o{ blogs : "classifies"

    customers {
        int id PK
        string slug UK
        string email
        string password_hash
        string account_type
    }
    hosts {
        int id PK
        int customer_id FK "UNIQUE"
        string business_name
        string status "pending|active|suspended"
        numeric commission_override
    }
    listings {
        int id PK
        string public_id UK
        string slug UK
        int owner_id "hosts.id (soft)"
        int category_id "master_items.id (soft)"
        int price_adult "cents"
        int group_size_max
        string status
    }
    units {
        int id PK
        int listing_id FK
        date start_date
        int capacity
        int price_adult "cents (NULL = fall back)"
        string status "scheduled|cancelled"
    }
    seasons {
        int id PK
        int listing_id FK
        int priority
    }
    unit_rates {
        int id PK
        int unit_id FK
        int season_id FK
        int price_per_night
    }
    bookings {
        int id PK
        string reference UK
        string public_id UK
        string product_type "class|tour|accommodation"
        int product_id "listings.id (soft)"
        int departure_id "units.id (soft)"
        int customer_id FK
        date tour_date
        int total "cents"
        string status "pending|confirmed|cancelled|completed|no_show"
        string payment_status "unpaid|deposit_paid|paid|refunded"
    }
    booking_payments {
        int id PK
        int booking_id FK
        string kind "deposit|balance|refund"
        int amount
        string status "pending|paid|failed|refunded"
    }
    host_earnings {
        int id PK
        int booking_id "UNIQUE"
        int host_id FK
        int net_cents
        string status "pending|approved|paid|rejected"
    }
    host_payout_requests {
        int id PK
        int host_id FK
        int amount_cents
        string status "pending|approved|paid|rejected"
    }
    listing_reviews {
        int id PK
        int listing_id FK
        string author_name
        numeric rating
    }
    coupons {
        int id PK
        string code UK
        string applies_to "all|class|tour|accommodation|specific"
    }
    conversations {
        int id PK
        int host_id FK
        int guest_customer_id FK
        int listing_id
    }
    messages {
        int id PK
        int conversation_id FK
        string sender "guest|host"
    }
    influencers {
        int id PK
        string username UK
        string email UK
        string status
    }
    influencer_bookings {
        int id PK
        int influencer_id FK
        int booking_id "UNIQUE"
        int amount_cents
    }
    blogs {
        int id PK
        string slug UK
        int category_id FK
    }

Entities

Entity	Key fields	Role
customers	`id`, `slug` (UQ), `account_type`	Traveler or operator account; central identity for the portal
hosts	`id`, `customer_id` (UQ FK)	Operator profile (1:1 with a customer); approval + payout settings
master_items	`id`, `kind`, `slug`, UQ `(kind, slug)`	Lookup rows: tour categories, types, nearby-place labels
listings	`id`, `public_id`/`slug` (UQ), `owner_id`	A tour
units	`id`, `listing_id`, `start_date`	A departure (dated option) of a tour
seasons / season_dates	`id`, `listing_id` / `season_id`	Pricing seasons and their date ranges
unit_rates	`id`, UQ `(unit_id, season_id)`	Per-departure price for a season
unit_blocks / ical_feeds	`id`, `unit_id`	Availability blocks; external calendar imports
amenity_groups / amenities	`id` / `group_id`	Two-level inclusion library
bookings	`id`, `reference`/`public_id` (UQ)	A reservation (traveler + tour + departure)
booking_payments	`id`, `booking_id`	Deposit / balance / refund entries for a booking
booking_extras / booking_notes	`id`, `booking_id`	Chosen upsells; internal staff notes
listing_reviews	`id`, `listing_id`	Traveler review/testimonial for a tour
coupons	`id`, `code` (UQ)	Booking discount code, scoped via `applies_to` + `target_ids`
host_earnings	`id`, `booking_id` (UQ), `host_id`	Revenue-share ledger entry per settled booking
host_payout_requests	`id`, `host_id`	Operator withdrawal request
subscription_plans / host_subscriptions	`id` / `host_id`, `plan_id`	Operator plans and current subscription
conversations / messages	`host_id`, `guest_customer_id` / `conversation_id`	Traveler ↔ operator chat
influencers	`id`, `username`/`email` (UQ)	Affiliate account
influencer_bookings / _commissions	`influencer_id`, `booking_id`	Attributed bookings and their commissions

Relationships

Relationship	From → To	Type	Foreign key
Customer → Operator profile	`customers` → `hosts`	one-to-one	`hosts.customer_id` (UNIQUE)
Operator → Tours	`hosts` → `listings`	one-to-many (soft)	`listings.owner_id`
Master item → Tours	`master_items` → `listings`	one-to-many (soft)	`listings.category_id`
Tour ↔ Master items	`listing_master_items`	many-to-many	`listing_id`, `master_item_id` (composite PK)
Tour → Departures	`listings` → `units`	one-to-many	`units.listing_id`
Tour → Seasons	`listings` → `seasons`	one-to-many	`seasons.listing_id`
Season → Season dates	`seasons` → `season_dates`	one-to-many	`season_dates.season_id`
Departure + Season → Rate	`units` / `seasons` → `unit_rates`	one-to-many	`unit_rates.unit_id`, `unit_rates.season_id` (UQ pair)
Departure → Blocks / iCal	`units` → `unit_blocks` / `ical_feeds`	one-to-many	`unit_blocks.unit_id`, `ical_feeds.unit_id`
Inclusion group → Inclusions	`amenity_groups` → `amenities`	one-to-many	`amenities.group_id`
Customer → Bookings	`customers` → `bookings`	one-to-many	`bookings.customer_id`
Tour → Bookings	`listings` → `bookings`	one-to-many (soft)	`bookings.product_id` (with `product_type = 'tour'`)
Departure → Bookings	`units` → `bookings`	one-to-many (soft)	`bookings.departure_id`
Coupon → Bookings	`coupons` → `bookings`	one-to-many (soft)	`bookings.coupon_id`
Booking → Payments	`bookings` → `booking_payments`	one-to-many	`booking_payments.booking_id`
Booking → Extras / Notes / Emails	`bookings` → `booking_extras`/`_notes`/`_emails`	one-to-many	`booking_id`
Tour → Reviews	`listings` → `listing_reviews`	one-to-many	`listing_reviews.listing_id`
Operator → Earnings	`hosts` → `host_earnings`	one-to-many	`host_earnings.host_id`
Booking → Earning	`bookings` → `host_earnings`	one-to-one (soft)	`host_earnings.booking_id` (UNIQUE)
Operator → Payout requests	`hosts` → `host_payout_requests`	one-to-many	`host_payout_requests.host_id`
Payout request → Payment	`host_payout_requests` → `host_payment_history`	one-to-many	`host_payment_history.payout_request_id`
Plan → Subscriptions	`subscription_plans` → `host_subscriptions`	one-to-many	`host_subscriptions.plan_id`
Operator/Guest → Conversation	`hosts`/`customers` → `conversations`	one-to-many (two roles)	`conversations.host_id`, `conversations.guest_customer_id`
Conversation → Messages	`conversations` → `messages`	one-to-many	`messages.conversation_id`
Influencer → Referral links	`influencers` → `influencer_referral_links`	one-to-many	`influencer_referral_links.influencer_id`
Referral link → Clicks	`influencer_referral_links` → `influencer_clicks`	one-to-many	`influencer_clicks.link_id`
Influencer → Attributed booking	`influencers` → `influencer_bookings`	one-to-many	`influencer_bookings.influencer_id`
Attributed booking → Commission	`influencer_bookings` → `influencer_commissions`	one-to-many	`influencer_commissions.influencer_booking_id`
Blog category → Blogs	`blog_categories` → `blogs`	one-to-many	`blogs.category_id`

The document domain (documents, document_versions, customer_documents, …) and the optional storefront (products, orders, …) follow the same patterns and hang off customers; see src/product/document/schema.ts and src/lib/vendor-schema.ts.