Entity Relationship Diagram

This document describes the PubCrawl entity-relationship model. It focuses on the core booking-marketplace entities and the foreign keys you can verify directly in src/product/booking/schema.ts, src/lib/hosts.ts, src/lib/vendor-schema.ts and src/lib/influencers.ts. All keys are SERIAL integers; relationships are enforced with PostgreSQL REFERENCES constraints (some attribution links — e.g. bookings.session_id, bookings.product_id — are plain integer references resolved in application code rather than declared FKs).

Mermaid ERD

MERMAID

erDiagram
    customers ||--o| hosts : "is (host profile)"
    customers ||--o{ bookings : "books"
    customers ||--o{ customer_wishlist : "saves"
    customers ||--o{ listing_reviews : "writes"
    customers ||--o{ customer_support_tickets : "opens"
    customers ||--o{ conversations : "chats (guest)"

    hosts ||--o{ listings : "owns"
    hosts ||--o{ experience_sessions : "teaches"
    hosts ||--o{ host_earnings : "earns"
    hosts ||--o{ host_payout_requests : "withdraws"
    hosts ||--o{ host_subscriptions : "subscribes"
    hosts ||--o{ host_documents : "uploads"
    hosts ||--o{ conversations : "chats (host)"

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

    master_items ||--o{ listings : "categorises"
    master_items ||--o{ listing_master_items : "tags"
    listings ||--o{ listing_master_items : "tagged_by"
    listings ||--o{ listing_nearby_places : "near"

    listings ||--o{ experience_sessions : "scheduled_as"
    listings ||--o{ listing_reviews : "receives"
    listings ||--o{ listing_issues : "flagged_in"

    experience_sessions ||--o{ bookings : "reserved_by"

    bookings ||--o{ booking_extras : "adds"
    bookings ||--o{ booking_payments : "settled_by"
    bookings ||--o{ booking_refunds : "refunded_by"
    bookings ||--o{ booking_notes : "annotated_by"
    bookings ||--o{ booking_emails : "notified_by"
    coupons ||--o{ bookings : "discounts"

    host_payout_requests ||--o{ host_earnings : "pays_out"

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

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

    influencers ||--o{ influencer_referral_links : "creates"
    influencers ||--o{ influencer_bookings : "refers"
    influencers ||--o{ influencer_commissions : "earns"
    influencers ||--o{ influencer_payout_requests : "withdraws"
    bookings ||--o| influencer_bookings : "attributed_to"

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

    customers {
        int id PK
        string slug UK
        string email
        string password_hash
        string verification_status
    }
    hosts {
        int id PK
        int customer_id FK "UNIQUE"
        string slug UK
        string status "pending|active|suspended"
        numeric commission_override
    }
    listings {
        int id PK
        string public_id UK
        string slug UK
        int category_id FK "master_items"
        int owner_id FK "hosts"
        string experience_type "class|tour|dining"
        int deposit_pct
        string status
    }
    experience_sessions {
        int id PK
        string public_id UK
        int listing_id FK
        int instructor_id FK "hosts"
        timestamptz starts_at
        int seats_total
        int seats_booked
        int price_per_seat
    }
    bookings {
        int id PK
        string reference UK
        string public_id UK
        string product_type "class|tour|dining"
        int product_id FK "listings"
        int session_id FK "experience_sessions"
        int customer_id FK
        int coupon_id FK
        int influencer_id FK
        string status "pending|confirmed|cancelled|completed|no_show"
        string payment_status "unpaid|deposit_paid|paid|refunded"
        int total
    }
    booking_extras {
        int id PK
        int booking_id FK
        int qty
        int total
    }
    booking_payments {
        int id PK
        int booking_id FK
        string kind "deposit|balance|refund"
        int amount
        string status "pending|paid|failed|refunded"
    }
    booking_refunds {
        int id PK
        int booking_id FK
        int amount
        string initiated_by "customer|host|admin|system"
        string status "pending|approved|refunded|rejected|failed"
    }
    coupons {
        int id PK
        string code UK
        string discount_type "percent|fixed"
        string applies_to "all|class|tour|specific"
    }
    listing_reviews {
        int id PK
        int listing_id FK
        int customer_id FK
        numeric rating
    }
    host_earnings {
        int id PK
        int host_id FK
        int booking_id "UNIQUE"
        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"
    }
    subscription_plans {
        int id PK
        string slug UK
        int price_cents
        string interval "month|year"
    }
    host_subscriptions {
        int id PK
        int host_id FK
        int plan_id FK
        string status "active|past_due|cancelled|expired"
    }
    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 commission_type
    }
    influencer_bookings {
        int id PK
        int influencer_id FK
        int booking_id "UNIQUE"
        int amount_cents
    }
    influencer_commissions {
        int id PK
        int influencer_id FK
        int influencer_booking_id FK
        int amount_cents
        string status
    }
    blogs {
        int id PK
        string slug UK
        int category_id FK
    }

Entities

Entity	Key fields	Role
customers	`id`, `slug` (UQ), `email`	Guest/attendee account; central storefront identity
hosts	`id`, `customer_id` (UQ FK), `slug` (UQ)	Guide profile (1:1 with a customer); KYC, commission + payout settings
listings	`id`, `public_id`, `slug` (UQ), `owner_id`	A crawl (or tour / dining experience)
experience_sessions	`id`, `listing_id`, `starts_at`	A scheduled sitting with seat inventory (`seats_total`/`seats_booked`)
bookings	`id`, `reference` (UQ), `session_id`, `customer_id`	A seat reservation against a session
booking_extras / booking_payments	`id`, `booking_id`	Add-ons snapshot + the deposit/balance/refund ledger
booking_refunds	`id`, `booking_id`	Tiered-refund admin approval queue
coupons	`id`, `code` (UQ)	Booking discount code, scoped via `applies_to` + `target_ids`
listing_reviews	`id`, `listing_id`, `customer_id`	Guest rating/review of a crawl
amenity_groups / amenities	`id` / `group_id`	"What's included" two-level library
master_items / listing_master_items	`id` / PK `(listing_id, master_item_id)`	Master data (categories/tags) and the crawl↔tag link
hosts earnings & payouts	`host_id`, `booking_id`	Revenue-share ledger, payout requests + settled history
subscription_plans / host_subscriptions	`id` / `host_id`, `plan_id`	Plans a host subscribes to in order to list
conversations / messages	`host_id`, `guest_customer_id` / `conversation_id`	Guest↔host chat (contact-masked pre-booking)
influencers / influencer_bookings / commissions	`id`, `username` (UQ) / `booking_id`	Affiliate accounts, attributed bookings and commission ledger

Relationships

Relationship	From → To	Type	Foreign key
Customer → Host profile	`customers` → `hosts`	one-to-one	`hosts.customer_id` (UNIQUE)
Host → Listings	`hosts` → `listings`	one-to-many	`listings.owner_id`
Host → Session (teaches)	`hosts` → `experience_sessions`	one-to-many	`experience_sessions.instructor_id`
Category → Listings	`master_items` → `listings`	one-to-many	`listings.category_id` (`kind='class_category'`)
Listing ↔ Master items (tags)	`listing_master_items`	many-to-many	`listing_id`, `master_item_id` (composite PK)
Listing → Sessions	`listings` → `experience_sessions`	one-to-many	`experience_sessions.listing_id`
Session → Bookings	`experience_sessions` → `bookings`	one-to-many	`bookings.session_id`
Customer → Bookings	`customers` → `bookings`	one-to-many	`bookings.customer_id`
Booking → Extras	`bookings` → `booking_extras`	one-to-many	`booking_extras.booking_id`
Booking → Payments	`bookings` → `booking_payments`	one-to-many	`booking_payments.booking_id`
Booking → Refunds	`bookings` → `booking_refunds`	one-to-many	`booking_refunds.booking_id`
Coupon → Bookings	`coupons` → `bookings`	one-to-many	`bookings.coupon_id`
Listing → Reviews	`listings` → `listing_reviews`	one-to-many	`listing_reviews.listing_id`
Amenity group → Amenities	`amenity_groups` → `amenities`	one-to-many	`amenities.group_id`
Host → Earnings / Payouts	`hosts` → `host_earnings` / `host_payout_requests`	one-to-many	`host_id`
Payout request → Earnings	`host_payout_requests` → `host_earnings`	one-to-many	`host_earnings.payout_request_id`
Plan → Host subscriptions	`subscription_plans` → `host_subscriptions`	one-to-many	`host_subscriptions.plan_id`
Conversation → Messages	`conversations` → `messages`	one-to-many	`messages.conversation_id`
Host/Guest → Conversation	`hosts`/`customers` → `conversations`	one-to-many (two roles)	`conversations.host_id`, `conversations.guest_customer_id`
Influencer → Referral links	`influencers` → `influencer_referral_links`	one-to-many	`influencer_referral_links.influencer_id`
Booking → Influencer attribution	`bookings` → `influencer_bookings`	one-to-one (opt)	`influencer_bookings.booking_id` (UNIQUE), `bookings.influencer_id`
Influencer 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`

Reference/config entities (languages, currencies, countries, locations, translations, integration_connections, settings, theme_settings) and CMS entities (pages, faqs, gallery, testimonials, partners, hero_sliders, menus) hang off the platform rather than the booking spine; see the Database Documentation.