Data Model Diagram

This document presents the PubCrawl data model at the field level: the key domain tables drawn directly from the CREATE TABLE / ALTER TABLE statements in src/product/booking/schema.ts, src/lib/hosts.ts and src/lib/vendor-schema.ts. PubCrawl stores "soft enums" as plain VARCHAR columns with documented allowed values (so they can be extended without a migration), money as integer cents, and arrays/objects as JSONB.

Domain overview

MERMAID

classDiagram
    class Host {
        +int id
        +int customer_id
        +string slug
        +string status
        +numeric commission_override
    }
    class Listing {
        +int id
        +string slug
        +int owner_id
        +int category_id
        +string experience_type
        +int deposit_pct
        +string status
    }
    class ExperienceSession {
        +int id
        +int listing_id
        +int instructor_id
        +timestamptz starts_at
        +int seats_total
        +int seats_booked
        +int price_per_seat
    }
    class Booking {
        +int id
        +string reference
        +int product_id
        +int session_id
        +int customer_id
        +string booking_mode
        +string status
        +string payment_status
        +int total
    }
    class BookingPayment {
        +int id
        +int booking_id
        +string kind
        +int amount
        +string status
    }
    class BookingRefund {
        +int id
        +int booking_id
        +int amount
        +string initiated_by
        +string status
    }
    class Customer {
        +int id
        +string slug
        +string email
        +string verification_status
    }
    class Coupon {
        +int id
        +string code
        +string discount_type
        +string applies_to
    }
    Customer "1" --> "0..1" Host : host profile
    Host "1" --> "0..*" Listing : owns
    Host "1" --> "0..*" ExperienceSession : teaches
    Listing "1" --> "0..*" ExperienceSession : scheduled as
    ExperienceSession "1" --> "0..*" Booking : reserved by
    Customer "1" --> "0..*" Booking : books
    Booking "1" --> "0..*" BookingPayment : settled by
    Booking "1" --> "0..*" BookingRefund : refunded by
    Coupon "1" --> "0..*" Booking : discounts

Listing (Crawl)

listings — src/product/booking/schema.ts.

Column	Type	Default	Meaning
`id`	`SERIAL`	—	Primary key
`public_id`	`VARCHAR(40)`	`p…`	Opaque public id used in URLs
`slug`	`VARCHAR(180)`	—	Unique URL slug
`name`	`VARCHAR(220)`	—	Crawl name
`summary` / `description`	text	`''`	Short tagline / full description
`category_id`	`INTEGER`	null	→ `master_items` (`kind='class_category'`)
`owner_id`	`INTEGER`	null	→ `hosts` (`NULL` = admin-owned)
`experience_type`	`VARCHAR(20)`	`'class'`	`class` · `tour` · `dining`
`hero_image` / `gallery`	`VARCHAR`/`JSONB`	`''`/`[]`	Cover image + image list
`video_url` / `videos`	`VARCHAR`/`JSONB`	`''`/`[]`	Promo video(s)
`address` / `city` / `region` / `country`	`VARCHAR`	(Italy)	Venue location (free text)
`location_id`	`INTEGER`	null	→ `locations` (drives Popular Destinations)
`latitude` / `longitude`	`NUMERIC(9,6)`	null	Map coordinates
`duration_minutes`	`INT`	`180`	Typical crawl length
`skill_level`	`VARCHAR(20)`	`'all'`	`all` · `beginner` · `intermediate` · `advanced`
`cuisine_type`	`VARCHAR(80)`	`''`	Cuisine tag
`whats_included` / `dietary_options`	`JSONB`	`[]`	"What's included" items · dietary options
`what_to_bring` / `class_rules`	`TEXT`	`''`	Pre-crawl notes · venue/age rules
`languages_spoken`	`JSONB`	`[]`	Languages the crawl is guided in
`cancellation_policy`	`TEXT`	`''`	Free-text policy note
`cancellation_policy_slug`	`VARCHAR(30)`	`'moderate'`	`flexible` · `moderate` · `firm` · `strict` · `non-refundable`
`deposit_pct`	`INT`	`30`	Deposit percentage at checkout
`instant_booking`	`BOOLEAN`	`true`	`false` = Request to Book (host must accept)
`status`	`VARCHAR(20)`	`'draft'`	Publication status
`meeting_point` / `itinerary` / `menu_courses`	`VARCHAR`/`JSONB`	`''`/`[]`	Tour meeting point + itinerary; dining menu
`sort_order`	`INT`	`0`	Ordering
`created_at` / `updated_at`	`TIMESTAMPTZ`	`NOW()`	Timestamps

Experience Session (Sitting)

experience_sessions — src/product/booking/schema.ts. Availability is "seats left on a date", not a free date range. bookings.session_id points here.

Column	Type	Default	Meaning
`id`	`SERIAL`	—	Primary key
`public_id`	`VARCHAR(40)`	`s…`	Opaque public id
`listing_id`	`INTEGER`	—	FK → `listings` (`ON DELETE CASCADE`)
`starts_at`	`TIMESTAMPTZ`	—	Session start
`ends_at`	`TIMESTAMPTZ`	null	Session end
`timezone`	`VARCHAR(60)`	`'Europe/Rome'`	IANA timezone
`seats_total`	`INT`	`12`	Seat capacity
`seats_booked`	`INT`	`0`	Seats claimed (maintained on confirm/cancel)
`min_seats`	`INT`	`1`	Below this the session may be cancelled
`max_per_booking`	`INT`	`8`	Max seats per single booking
`price_per_seat`	`INT`	`0`	Per-seat price (cents)
`price_private`	`INT`	`0`	Whole-session buyout price (cents)
`currency`	`VARCHAR(3)`	`'EUR'`	ISO currency
`instructor_id`	`INTEGER`	null	→ `hosts` teaching this sitting
`status`	`VARCHAR(20)`	`'scheduled'`	`scheduled` · `cancelled` · `completed`
`is_active`	`BOOLEAN`	`true`	Active flag

Booking

bookings — src/product/booking/schema.ts. reference is unique. Money in cents.

Column	Type	Default	Meaning
`id`	`SERIAL`	—	Primary key
`reference`	`VARCHAR(30)`	—	Unique human reference
`public_id`	`VARCHAR(40)`	`b…`	Opaque public id
`product_type`	`VARCHAR(20)`	—	`class` · `tour` · `dining`
`product_id`	`INTEGER`	—	→ `listings`
`session_id`	`INTEGER`	null	→ `experience_sessions`
`booking_mode`	`VARCHAR(20)`	`'per_person'`	`per_person` · `group` · `private`
`customer_id`	`INTEGER`	null	→ `customers` (`ON DELETE SET NULL`); guests may book without an account
`guest_name` / `guest_email` / `guest_phone`	`VARCHAR`	`''`	Guest contact captured at checkout
`num_adults` / `num_children`	`INT`	`1` / `0`	Party size
`price_breakdown`	`JSONB`	`[]`	Line items (recomputed server-side)
`subtotal` / `discount` / `fees` / `tax` / `deposit` / `total`	`INT`	`0`	Money (cents)
`currency`	`VARCHAR(3)`	`'EUR'`	ISO currency
`coupon_id` / `coupon_code`	`INT`/`VARCHAR`	null/`''`	Applied coupon
`influencer_id` / `referral_discount`	`INT`	null / `0`	Affiliate attribution + referral discount (cents)
`status`	`VARCHAR(20)`	`'pending'`	`pending` · `confirmed` · `cancelled` · `completed` · `no_show`
`payment_status`	`VARCHAR(20)`	`'unpaid'`	`unpaid` · `deposit_paid` · `paid` · `refunded`
`host_status`	`VARCHAR(20)`	`'none'`	`none` · `requested` · `accepted` · `declined`
`provider` / `provider_ref`	`VARCHAR`	`''`	Payment gateway + reference
`source`	`VARCHAR(20)`	`'website'`	`website` · `admin` · `ical`
`created_at` / `cancelled_at` / `deleted_at`	`TIMESTAMPTZ`	`NOW()`/null	Timestamps (soft-delete to Trash)

Money that actually moves is recorded in booking_payments (kind of deposit/balance/refund); cancellation refunds queue in booking_refunds for admin approval.

Host (Guide account)

hosts — src/lib/hosts.ts. One row per guide, 1:1 with a customers account via customer_id (UNIQUE).

Column	Type	Default	Meaning
`id`	`SERIAL`	—	Primary key
`customer_id`	`INTEGER`	—	FK → `customers` (UNIQUE, `ON DELETE CASCADE`)
`business_name`	`VARCHAR(200)`	`''`	Public business/school name
`slug`	`VARCHAR(220)`	—	Unique public slug
`bio` / `avatar` / `cover_image`	text/`VARCHAR`	`''`	Public profile
`paypal_email`	`VARCHAR(200)`	`''`	Payout email
`payout_method`	`VARCHAR(20)`	`'paypal'`	`paypal` · `bank` · `stripe`
`commission_override`	`NUMERIC(5,2)`	null	Per-host commission % (NULL = plan/platform default)
`status`	`VARCHAR(20)`	`'active'`	`pending` · `active` · `suspended`
`is_verified` / `verification_status`	`BOOLEAN`/`VARCHAR(20)`	`false`/`'unverified'`	KYC state (`unverified`/`pending`/`verified`/`rejected`)
KYC / legal / tax	various	`''`	`legal_name`, `date_of_birth`, `id_type`, `tax_id`, registered address
`min_payout_cents`	`INTEGER`	`5000`	Minimum payout threshold
`notify_prefs`	`JSONB`	booking/payout/message	Notification toggles

Coupon

coupons — src/product/booking/schema.ts.

Column	Type	Default	Meaning
`id`	`SERIAL`	—	Primary key
`code`	`VARCHAR(60)`	—	Unique coupon code
`description`	`VARCHAR(200)`	`''`	Internal note
`discount_type`	`VARCHAR(10)`	`'percent'`	`percent` · `fixed`
`discount_value`	`INT`	`0`	Percentage or fixed amount (cents)
`applies_to`	`VARCHAR(20)`	`'all'`	`all` · `class` · `tour` · `accommodation` · `specific`
`target_ids`	`JSONB`	`[]`	Scope target ids (when `applies_to='specific'`)
`min_amount`	`INT`	`0`	Minimum booking total to qualify (cents)
`max_discount`	`INT`	`0`	Cap on a percentage discount (cents)
`max_redemptions`	`INT`	`0`	Global limit (0 = unlimited)
`per_customer_limit`	`INT`	`0`	Per-customer limit
`times_redeemed`	`INT`	`0`	Times used
`combinable`	`BOOLEAN`	`false`	Stackable with other discounts
`valid_from` / `valid_until`	`DATE`	null	Validity window
`is_active`	`BOOLEAN`	`true`	Active flag

Extra (Add-on)

extras — src/product/booking/schema.ts. Guests pick these at checkout; the chosen ones snapshot into booking_extras.

Column	Type	Default	Meaning
`id`	`SERIAL`	—	Primary key
`name`	`VARCHAR(160)`	—	Add-on name (e.g. wine pairing, recipe book)
`description`	`VARCHAR(400)`	`''`	Short description
`applies_to`	`VARCHAR(20)`	`'class'`	`all` · `class` · `tour` · `accommodation`
`price`	`INT`	`0`	Price (cents)
`price_type`	`VARCHAR(20)`	`'flat'`	`flat` · `per_person` · `per_night` · `per_person_night`
`max_qty`	`INT`	`1`	Max units for a `flat` extra
`is_active`	`BOOLEAN`	`true`	Active flag
`sort_order`	`INT`	`0`	Ordering

Soft Enums

Enumerations are stored as VARCHAR with documented allowed values:

Field	Table	Allowed values
`experience_type`	`listings`	`class` · `tour` · `dining`
`skill_level`	`listings`	`all` · `beginner` · `intermediate` · `advanced`
`cancellation_policy_slug`	`listings`	`flexible` · `moderate` · `firm` · `strict` · `non-refundable`
`status`	`experience_sessions`	`scheduled` · `cancelled` · `completed`
`product_type`	`bookings`	`class` · `tour` · `dining`
`booking_mode`	`bookings`	`per_person` · `group` · `private`
`status`	`bookings`	`pending` · `confirmed` · `cancelled` · `completed` · `no_show`
`payment_status`	`bookings`	`unpaid` · `deposit_paid` · `paid` · `refunded`
`host_status`	`bookings`	`none` · `requested` · `accepted` · `declined`
`source`	`bookings`	`website` · `admin` · `ical`
`kind`	`booking_payments`	`deposit` · `balance` · `refund`
`status`	`booking_payments`	`pending` · `paid` · `failed` · `refunded`
`initiated_by`	`booking_refunds`	`customer` · `host` · `admin` · `system`
`status`	`booking_refunds`	`pending` · `approved` · `refunded` · `rejected` · `failed` · `refund_pending`
`discount_type`	`coupons`	`percent` · `fixed`
`applies_to`	`coupons`	`all` · `class` · `tour` · `accommodation` · `specific`
`price_type`	`extras`	`flat` · `per_person` · `per_night` · `per_person_night`
`status`	`hosts`	`pending` · `active` · `suspended`
`verification_status`	`hosts` / `customers`	`unverified` · `pending` · `verified` · `rejected`
`status`	`host_earnings`	`pending` · `approved` · `paid` · `rejected`
`monetization_mode`	`platform_settings`	`commission` · `subscription` · `hybrid`
`interval`	`subscription_plans`	`month` · `year`
`sender`	`messages`	`guest` · `host`

These values are read from the actual CREATE TABLE / ALTER TABLE comments in the schema source; because they are plain strings, admins and developers can extend them without altering the column type.