Data Model Diagram

This document presents the Tutor 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/product/booking/availability-schema.ts, src/lib/hosts.ts and src/lib/vendor-schema.ts. Tutor is a tutoring & education booking marketplace: tutors (hosts) publish Lessons and their weekly availability, and students (customers) book a lesson time. Tutor 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 AvailabilityRule {
        +int id
        +int host_id
        +int listing_id
        +smallint weekday
        +time start_time
        +time end_time
        +int slot_minutes
        +int capacity
    }
    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..*" AvailabilityRule : publishes
    Host "1" --> "0..*" ExperienceSession : teaches
    Listing "1" --> "0..*" AvailabilityRule : scoped by (optional)
    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 (Lesson)

listings — src/product/booking/schema.ts. Every listing here is a Lesson a tutor offers. Several column names (experience_type, cuisine_type, class_rules, meeting_point, menu_courses, …) are inherited from the booking engine's generic, vertical-agnostic vocabulary — the tutor pack (src/product/verticals/tutor/pack.ts) simply doesn't surface the columns it doesn't need (e.g. cuisine_type, meeting_point, menu_courses) and repurposes others (skill_level reads as study level, duration_minutes as lesson length).

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)`	—	Lesson name
`summary` / `description`	text	`''`	Short tagline / full description
`category_id`	`INTEGER`	null	→ `master_items` (subject / category taxonomy)
`owner_id`	`INTEGER`	null	→ `hosts` (`NULL` = admin-owned)
`experience_type`	`VARCHAR(20)`	`'class'`	Engine-generic label for the booking "world"; the tutor pack only uses `class` (see Developer Guide)
`hero_image` / `gallery`	`VARCHAR`/`JSONB`	`''`/`[]`	Cover image + image list
`video_url` / `videos`	`VARCHAR`/`JSONB`	`''`/`[]`	Promo video(s)
`address` / `city` / `region` / `country`	`VARCHAR`	(Italy)	Free-text location, for in-person lessons (online-only tutors leave it blank)
`location_id`	`INTEGER`	null	→ `locations` (drives Popular Destinations)
`latitude` / `longitude`	`NUMERIC(9,6)`	null	Map coordinates
`duration_minutes`	`INT`	`180`	Typical lesson length
`skill_level`	`VARCHAR(20)`	`'all'`	`all` · `beginner` · `intermediate` · `advanced`
`cuisine_type`	`VARCHAR(80)`	`''`	Engine-generic tag column, unused by the tutor pack
`whats_included` / `dietary_options`	`JSONB`	`[]`	"What's included" items · engine-generic column, unused by the tutor pack
`what_to_bring` / `class_rules`	`TEXT`	`''`	What a student should bring · lesson ground rules
`languages_spoken`	`JSONB`	`[]`	Languages the lesson is taught 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 (tutor must accept)
`status`	`VARCHAR(20)`	`'draft'`	Publication status
`meeting_point` / `itinerary` / `menu_courses`	`VARCHAR`/`JSONB`	`''`/`[]`	Engine-generic columns from the tour/dining worlds, unused by the tutor pack
`sort_order`	`INT`	`0`	Ordering
`created_at` / `updated_at`	`TIMESTAMPTZ`	`NOW()`	Timestamps

Availability Rule (weekly hours)

availability_rules — src/product/booking/availability-schema.ts. A tutor publishes recurring weekly hours plus a lesson length instead of hand-creating each lesson time; the engine expands these rules into bookable lesson times at read time and only writes an experience_sessions row once a student actually books one.

Column	Type	Default	Meaning
`id`	`SERIAL`	—	Primary key
`host_id`	`INTEGER`	—	The tutor this rule belongs to
`listing_id`	`INTEGER`	null	Scopes the rule to one Lesson; `NULL` = every Lesson the tutor owns
`weekday`	`SMALLINT`	—	`0`=Sunday … `6`=Saturday
`start_time` / `end_time`	`TIME`	—	Local wall-clock hours the tutor is available
`slot_minutes`	`INT`	`60`	Lesson length generated from this rule
`buffer_minutes`	`INT`	`0`	Gap kept free after each lesson time
`capacity`	`INT`	`1`	Students per lesson time; `1` = one-to-one
`price`	`INT`	`0`	Cents per lesson time; `0` = fall back to the listing's price
`valid_from` / `valid_to`	`DATE`	null	Optional date window the rule applies within
`is_active`	`BOOLEAN`	`true`	Active flag

Experience Session (booked lesson time)

experience_sessions — src/product/booking/schema.ts. In slot mode a row here is materialised from an availability_rules expansion only once a student books it — it is not pre-generated inventory. 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`	—	Lesson time start
`ends_at`	`TIMESTAMPTZ`	null	Lesson time end
`timezone`	`VARCHAR(60)`	`'Europe/Rome'`	IANA timezone
`seats_total`	`INT`	`12`	Capacity for this lesson time (`1` for a 1:1 lesson)
`seats_booked`	`INT`	`0`	Students booked (maintained on confirm/cancel)
`min_seats`	`INT`	`1`	Below this the lesson time may be cancelled
`max_per_booking`	`INT`	`8`	Max students per single booking
`price_per_seat`	`INT`	`0`	Per-student price (cents)
`price_private`	`INT`	`0`	Whole-lesson-time buyout price (cents)
`currency`	`VARCHAR(3)`	`'EUR'`	ISO currency
`instructor_id`	`INTEGER`	null	→ `hosts` — the tutor teaching this lesson time
`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)`	—	Engine-generic label mirroring `listings.experience_type`; the tutor pack only uses `class`
`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`); a student may book without an account
`guest_name` / `guest_email` / `guest_phone`	`VARCHAR`	`''`	Contact details captured at checkout
`num_adults` / `num_children`	`INT`	`1` / `0`	Party size (a lesson booked for a child learner vs. an adult learner)
`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 (Tutor account)

hosts — src/lib/hosts.ts. One row per tutor, 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 tutor/tutoring-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-tutor 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` · `accommodation` · `specific` (engine-generic values; the tutor pack uses `all`/`class`/`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. Students pick these at checkout (e.g. extra worksheets, a recorded-session add-on); the chosen ones snapshot into booking_extras.

Column	Type	Default	Meaning
`id`	`SERIAL`	—	Primary key
`name`	`VARCHAR(160)`	—	Add-on name (e.g. extra worksheet pack, recorded session)
`description`	`VARCHAR(400)`	`''`	Short description
`applies_to`	`VARCHAR(20)`	`'class'`	`all` · `class` · `accommodation` (engine-generic values)
`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` (engine-generic; the tutor pack only uses `class`)
`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` (engine-generic; the tutor pack only uses `class`)
`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` · `accommodation` · `specific` (engine-generic values)
`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.