Data Model Diagram

This document presents the Bike Tours 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/operators.ts and src/lib/vendor-schema.ts. Bike Tours 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.

Recall the domain ↔ database 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.

Tour

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

Column	Type	Default	Meaning
`id`	`SERIAL`	—	Primary key
`public_id`	`VARCHAR(40)`	generated	Non-sequential public id (`p…`), unique
`slug`	`VARCHAR(180)`	—	Unique URL slug
`name`	`VARCHAR(220)`	—	Tour name
`summary` / `description`	`VARCHAR`/`TEXT`	`''`	Short tagline + full description
`category_id`	`INT`	null	Soft link → `master_items` (`kind='property_category'`)
`owner_id`	`INT`	null	Soft link → `hosts` (NULL = admin-owned)
`hero_image` / `gallery` / `videos`	`VARCHAR`/`JSONB`	`''`/`[]`	Cover image, gallery, and videos
`address` / `city` / `region` / `country`	`VARCHAR`	`''`	Location
`latitude` / `longitude`	`NUMERIC(9,6)`	null	Map coordinates
`duration_days` / `duration_nights`	`INT`	`1` / `0`	Tour length
`daily_capacity`	`INT`	`0`	Seats per start date (0 = unlimited)
`meeting_point`	`TEXT`	`''`	Where the tour starts
`difficulty`	`VARCHAR(20)`	`''`	`easy` · `moderate` · `challenging`
`min_age`	`INT`	`0`	Minimum age (0 = none)
`group_size_min` / `group_size_max`	`INT`	`1` / `0`	Party bounds (max 0 = default departure capacity)
`itinerary` / `included` / `excluded`	`JSONB`	`[]`	Day-by-day plan; what's included / excluded
`price_adult` / `price_child` / `price_infant`	`INT`	`0`	Base per-person prices, cents (departures may override)
`private_available` / `private_price`	`BOOLEAN`/`INT`	`false`/`0`	Offer a whole-group private booking + its flat price
`deposit_pct`	`INT`	`30`	Deposit percentage
`cancellation_tier`	`VARCHAR(20)`	`'moderate'`	`flexible` · `moderate` · `strict` (drives auto-refund)
`instant_booking`	`BOOLEAN`	`true`	`false` = Request to Book (operator must accept)
`status`	`VARCHAR(20)`	`'draft'`	Lifecycle status
`seo_title` / `meta_description` / `meta_keywords`	`VARCHAR`	`''`	SEO
`sort_order`	`INT`	`0`	Ordering
`created_at` / `updated_at`	`TIMESTAMPTZ`	`NOW()`	Timestamps

Departure

units — src/product/booking/schema.ts. A departure is a dated, bookable option of a tour. Per-person prices fall back to the parent tour when left NULL.

Column	Type	Default	Meaning
`id`	`SERIAL`	—	Primary key
`listing_id`	`INT`	—	FK → `listings` (`ON DELETE CASCADE`)
`name`	`VARCHAR(200)`	`''`	Optional label, e.g. "Morning departure"
`start_date`	`DATE`	null	The scheduled departure date
`start_time`	`VARCHAR(5)`	`''`	`HH:MM`
`capacity`	`INT`	`0`	Seats; `0` = fall back to `listing.group_size_max`
`price_adult` / `price_child` / `price_infant`	`INT`	null	Per-person price, cents; `NULL` = fall back to the tour
`private_price`	`INT`	null	Flat private-group price override (cents)
`currency`	`VARCHAR(3)`	`'EUR'`	ISO currency code
`status`	`VARCHAR(20)`	`'scheduled'`	`scheduled` · `cancelled`
`is_active`	`BOOLEAN`	`true`	Active flag
`sort_order`	`INT`	`0`	Ordering

Companion pricing/availability tables: seasons + season_dates define seasons; unit_rates (unique on (unit_id, season_id)) holds price_per_night per season; unit_blocks and ical_feeds manage blocked dates and external-calendar imports.

Booking

bookings — src/product/booking/schema.ts. A lightly polymorphic reservation: product_type + product_id identify what was booked ('tour' + a listings.id), and departure_id the specific departure (NULL for a private on-request booking).

Column	Type	Default	Meaning
`id`	`SERIAL`	—	Primary key
`reference`	`VARCHAR(30)`	—	Human booking reference (unique)
`public_id`	`VARCHAR(40)`	generated	Non-sequential public id (`b…`), unique
`product_type`	`VARCHAR(20)`	—	`class` · `tour` · `accommodation`
`product_id`	`INT`	—	Soft link → the booked tour (`listings.id`)
`departure_id`	`INT`	null	Soft link → the booked departure (`units.id`)
`booking_mode`	`VARCHAR(20)`	`'per_person'`	`per_person` · `private`
`session_id`	`INT`	null	Legacy class-session id (class product)
`tour_date` / `end_date`	`DATE`	null	Departure / requested date and end date
`start_time`	`VARCHAR(5)`	`''`	`HH:MM`
`nights`	`INT`	`0`	Trip length in nights (multi-day tours)
`customer_id`	`INT`	null	FK → `customers` (`ON DELETE SET NULL`)
`guest_name` / `guest_email` / `guest_phone` / `guest_country`	`VARCHAR`	`''`	Lead traveler contact
`num_adults` / `num_children` / `num_infants`	`INT`	`1`/`0`/`0`	Party size
`guests_detail` / `price_breakdown`	`JSONB`	`[]`	Per-traveler details; itemised price lines
`subtotal` / `discount` / `fees` / `tax` / `deposit` / `total`	`INT`	`0`	Money breakdown (cents)
`currency`	`VARCHAR(3)`	`'EUR'`	ISO currency code
`coupon_id` / `coupon_code`	`INT`/`VARCHAR(60)`	null/`''`	Applied coupon, if any
`referral_discount` / `influencer_id`	`INT`	`0`/null	Influencer referral discount (cents) + attribution
`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'`	Operator acceptance: `none`·`requested`·`accepted`·`declined`
`contact_revealed`	`BOOLEAN`	`false`	Traveler PII revealed to the operator
`provider` / `provider_ref`	`VARCHAR`	`''`	Payment gateway + reference
`special_requests` / `dietary_notes`	`TEXT`	`''`	Free-text traveler notes
`source`	`VARCHAR(20)`	`'website'`	`website` · `admin` · `ical`
`created_at` / `updated_at` / `cancelled_at`	`TIMESTAMPTZ`	`NOW()`	Timestamps

Companion: booking_payments records each deposit / balance / refund movement; booking_extras snapshots chosen upsells; booking_notes / booking_emails hold the internal trail.

Operator Earnings

host_earnings — src/lib/operators.ts. One row per settled booking on an operator's tour. net = gross − commission − fees (all cents).

Column	Type	Default	Meaning
`id`	`SERIAL`	—	Primary key
`booking_id`	`INT`	—	The settled booking (unique)
`host_id`	`INT`	—	FK → `hosts` (`ON DELETE CASCADE`)
`gross_cents`	`INT`	`0`	Gross amount (cents)
`commission_cents`	`INT`	`0`	Platform commission (cents)
`fee_cents`	`INT`	`0`	Platform fees (cents)
`net_cents`	`INT`	`0`	Payable to the operator (cents)
`currency`	`VARCHAR(3)`	`'EUR'`	ISO currency code
`rate`	`NUMERIC(5,2)`	`0`	Snapshot of the commission % applied
`status`	`VARCHAR(20)`	`'pending'`	`pending` · `approved` · `paid` · `rejected`
`payout_request_id`	`INT`	null	Attached `host_payout_requests` id when batched

The effective commission % applied is resolved by getEffectiveCommission(): host-specific commission_override → active subscription plan's override → the platform_settings.default_commission_percent. Withdrawals flow through host_payout_requests (amount_cents, paypal_email, status of pending/approved/paid/rejected) and are recorded in host_payment_history.

Operator (Tour Owner)

hosts — src/lib/operators.ts. An operator is a customer; this row holds the seller-only fields (1:1 via customer_id UNIQUE).

Column	Type	Default	Meaning
`id`	`SERIAL`	—	Primary key
`customer_id`	`INT`	—	FK → `customers` (`ON DELETE CASCADE`), UNIQUE
`business_name`	`VARCHAR(200)`	`''`	Public operator/business 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-operator commission % (NULL = platform default)
`status`	`VARCHAR(20)`	`'active'`	`pending` · `active` · `suspended`
`is_verified`	`BOOLEAN`	`false`	Verified flag
`verification_status`	`VARCHAR(20)`	`'unverified'`	`unverified` · `pending` · `verified` · `rejected`
`min_payout_cents`	`INT`	`5000`	Minimum payout threshold (cents)

Additional KYC / legal / banking columns (legal_name, tax_id, bank_iban, ID metadata, registered address, agreement timestamps) are added incrementally by ALTER TABLE. Uploaded KYC files live in host_documents.

Customer (Traveler / Operator account)

customers — src/lib/vendor-schema.ts. One row per portal account; account_type distinguishes a traveler from an operator (the operator profile lives in hosts).

Column	Type	Default	Meaning
`id`	`SERIAL`	—	Primary key
`slug`	`VARCHAR(255)`	—	Unique public slug
`name`	`VARCHAR(255)`	—	Display name
`email`	`VARCHAR(255)`	`''`	Email address
`email_verified` / `phone_verified`	`BOOLEAN`	`false`	Verification flags
`phone` / `phone_code`	`VARCHAR`	`''`	Phone number
`password_hash`	`VARCHAR(255)`	`''`	bcrypt password hash
`otp_code` / `otp_expires_at` / `otp_purpose`	`VARCHAR`/`TIMESTAMPTZ`	`''`/null	bcrypt-hashed OTP + context
`account_type`	`VARCHAR(20)`	role tag	`traveler` (buyer) · `operator` (seller)
`preferred_language` / `preferred_currency`	`VARCHAR`	`''`	Travel preferences
`interests` / `dietary_notes` / `trip_type`	`TEXT`/`VARCHAR`	`''`	Traveler profile
`id_type` / `id_number` / `verification_status`	`VARCHAR`	`''`/`'unverified'`	ID verification
`notification_prefs`	`JSONB`	`{}`	Notification preferences
`is_blacklisted` / `is_active`	`BOOLEAN`	`false`/`true`	Block / active flags
`created_at` / `updated_at`	`TIMESTAMPTZ`	`NOW()`	Timestamps

Coupon

coupons — src/product/booking/schema.ts (booking discounts).

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` or `fixed`
`discount_value`	`INT`	`0`	Percentage or cents
`applies_to`	`VARCHAR(20)`	`'all'`	`all` · `class` · `tour` · `accommodation` · `specific`
`target_ids`	`JSONB`	`[]`	Scope target ids
`min_amount`	`INT`	`0`	Minimum order to qualify (cents)
`max_discount`	`INT`	`0`	Cap on the discount (cents)
`max_redemptions`	`INT`	`0`	Global redemption limit (0 = unlimited)
`per_customer_limit`	`INT`	`0`	Per-customer limit
`times_redeemed`	`INT`	`0`	Times used
`combinable`	`BOOLEAN`	`false`	May stack with other discounts
`valid_from` / `valid_until`	`DATE`	null	Validity window
`is_active`	`BOOLEAN`	`true`	Active flag

A second coupons variant (discount_type PERCENT, applies_to ALL/PRODUCTS/CATEGORIES, min_total_cents, …) is provisioned for the storefront in src/lib/vendor-schema.ts. Whichever ensure*Schema() runs first creates the table.

Tour Review

listing_reviews — src/product/booking/schema.ts. Traveler testimonials for the tour detail page (author is free text, not a customer FK).

Column	Type	Default	Meaning
`id`	`SERIAL`	—	Primary key
`listing_id`	`INT`	—	FK → `listings` (`ON DELETE CASCADE`)
`author_name` / `author_location`	`VARCHAR`	`''`	Reviewer name + location
`avatar`	`VARCHAR(600)`	`''`	Reviewer avatar
`rating`	`NUMERIC(2,1)`	`5`	1.0–5.0
`comment`	`TEXT`	`''`	Review text
`photos` / `topics`	`JSONB`	`[]`	Traveler photos; topic tags
`review_date`	`DATE`	null	Displayed review date
`is_published`	`BOOLEAN`	`true`	Visible on the site
`sort_order`	`INT`	`0`	Ordering

Soft Enums

Enumerations are stored as VARCHAR with documented allowed values:

Field	Table	Allowed values
`status`	`listings`	`draft` · `published` · … (lifecycle)
`difficulty`	`listings`	`easy` · `moderate` · `challenging`
`cancellation_tier`	`listings`	`flexible` · `moderate` · `strict`
`status`	`units`	`scheduled` · `cancelled`
`booking_mode`	`bookings`	`per_person` · `private`
`status`	`bookings`	`pending` · `confirmed` · `cancelled` · `completed` · `no_show`
`payment_status`	`bookings`	`unpaid` · `deposit_paid` · `paid` · `refunded` (`partially_refunded`)
`host_status`	`bookings`	`none` · `requested` · `accepted` · `declined`
`product_type`	`bookings`	`class` · `tour` · `accommodation`
`source`	`bookings`	`website` · `admin` · `ical`
`kind`	`booking_payments`	`deposit` · `balance` · `refund`
`status`	`booking_payments`	`pending` · `paid` · `failed` · `refunded`
`status`	`hosts`	`pending` · `active` · `suspended`
`verification_status`	`hosts`	`unverified` · `pending` · `verified` · `rejected`
`payout_method`	`hosts`	`paypal` · `bank` · `stripe`
`status`	`host_earnings`	`pending` · `approved` · `paid` · `rejected`
`status`	`host_payout_requests`	`pending` · `approved` · `paid` · `rejected`
`status`	`host_subscriptions`	`active` · `past_due` · `cancelled` · `expired`
`monetization_mode`	`platform_settings`	`commission` · `subscription` · `hybrid`
`discount_type`	`coupons`	`percent` · `fixed`
`applies_to`	`coupons`	`all` · `class` · `tour` · `accommodation` · `specific`
`price_type`	`extras`	`flat` · `per_person` · `per_night` · `per_person_night`
`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.