Database Documentation

This document catalogs the Tours PostgreSQL schema. Every table is created and evolved in TypeScript by idempotent ensure*Schema() functions — there is no ORM and no migration framework. Each function runs CREATE TABLE IF NOT EXISTS plus ALTER TABLE … ADD COLUMN IF NOT EXISTS for later additions, guarded so it executes once per server process and once at request time (so a fresh database never throws relation does not exist).

Conventions

No migrations: schema lives in src/product/booking/schema.ts (tours, departures, bookings, coupons, extras, reviews), src/lib/operators.ts (operators=hosts, earnings, payouts), src/lib/influencers.ts (affiliate), src/product/master/schema.ts (master data), src/lib/vendor-schema.ts (customers, commerce, settings), and src/lib/auth-schema.ts (admin users). CMS tables are created by scripts/init-db.ts.
First-run init: npm run db:init runs scripts/init-db.ts (CMS + admin) then scripts/init-core.ts (core infrastructure). Product-domain tables are provisioned by their own ensure*Schema() at request time / through the install seam.
Keys: SERIAL integer primary keys; foreign keys via REFERENCES. Some domain tables (listings, bookings) reference others by id without a formal REFERENCES constraint (a "soft" link) so provisioning order never blocks.
Naming: snake_case columns. Table and column names were deliberately kept from the original template for stability — a tour is a listings row, a departure is a units row, and an operator is a hosts row (host_id, guest_name, …).
Public ids: guest-facing entities also get a non-sequential public_id (e.g. bookings.public_id = b…, listings.public_id = p…) alongside the serial id.
Money: integer cents throughout (total, subtotal, deposit, price_adult, *_cents, …), paired with a currency column (default 'EUR').
Flexible data: arrays/objects stored as JSONB; enums stored as VARCHAR.
Common columns: is_active, sort_order, status, created_at, updated_at.

Tables are grouped below by domain.

Tours & Departures

Defined in src/product/booking/schema.ts. A tour is a listings row; a departure (a dated, bookable option of a tour) is a units row.

Table	Purpose	Key columns
`listings`	A tour	`id`, `public_id` (UQ), `slug` (UQ), `name`, `summary`, `description`, `category_id` (→ `master_items`), `owner_id` (→ `hosts`, NULL = admin-owned), `hero_image`, `gallery`/`videos` (JSONB), `address`/`city`/`region`/`country`, `latitude`/`longitude`, `duration_days`/`duration_nights`, `daily_capacity`, `meeting_point`, `difficulty`, `min_age`, `group_size_min`/`group_size_max`, `itinerary`/`included`/`excluded` (JSONB), `price_adult`/`price_child`/`price_infant`, `private_available`/`private_price`, `deposit_pct`, `cancellation_tier`, `instant_booking`, `status`, `sort_order`
`units`	A departure (dated option of a tour)	`id`, `listing_id` (→ `listings`), `name`, `start_date`, `start_time`, `capacity` (seats; 0 = fall back to `listing.group_size_max`), `price_adult`/`price_child`/`price_infant` (NULL = fall back to listing), `private_price`, `currency`, `status` (scheduled/cancelled), `is_active`, `sort_order`
`seasons`	A pricing season for a tour	`id`, `listing_id` (→ `listings`), `name`, `color`, `priority`
`season_dates`	Date ranges that make up a season	`id`, `season_id` (→ `seasons`), `start_date`, `end_date`
`unit_rates`	Per-departure price for a season	`id`, `unit_id` (→ `units`), `season_id` (→ `seasons`), `price_per_night`, `price_per_week`/`_month`, `weekend_price`, `min_nights`, `arrival_days`/`departure_days` (JSONB), UQ `(unit_id, season_id)`
`unit_blocks`	Blocked-out date ranges for a departure	`id`, `unit_id` (→ `units`), `start_date`, `end_date`, `reason`, `source` (manual/ical), `feed_id`
`ical_feeds`	External calendar imports (Airbnb/etc.)	`id`, `unit_id` (→ `units`), `name`, `url`, `last_synced_at`, `last_status`, `event_count`
`resource_days`	Per-day calendar overrides	`id`, `resource_type`, `resource_id`, `date`, `price_override`, `min_nights`, `inventory`, `note`, UQ `(resource_type, resource_id, date)`
`resource_notes`	Per-resource calendar notes	`id`, `resource_type`, `resource_id`, `body`

The seasons / unit_rates / unit_blocks / ical_feeds tables form the optional per-departure availability & pricing calendar. Departures without a season rate fall back to the departure's own price_adult/price_child, then to the tour's base prices.

Inclusions, Extras & Master Data

Inclusion library and upsells in src/product/booking/schema.ts; classification lookups in src/product/master/schema.ts.

Table	Purpose	Key columns
`amenity_groups`	Inclusion group (e.g. "What's included")	`id`, `title`, `subtitle`, `icon`, `image`, `sort_order`, `is_active`
`amenities`	A single inclusion within a group	`id`, `group_id` (→ `amenity_groups`), `title`, `subtitle`, `icon`, `image`, `sort_order`, `is_active`
`extras`	Offerable upsells (transfer, meal, wine)	`id`, `name`, `description`, `applies_to` (all/class/tour/accommodation), `price` (cents), `price_type` (flat/per_person/per_night/per_person_night), `max_qty`, `is_active`, `sort_order`
`master_items`	Lookup for tour types, categories, etc.	`id`, `kind`, `name`, `slug`, `description`, `icon`, `image`, `is_active`, `sort_order`, UQ `(kind, slug)` and `(kind, lower(name))`
`listing_master_items`	Tour ↔ master_item classification link	PK `(listing_id, master_item_id)`
`listing_nearby_places`	Points of interest near a tour	`id`, `listing_id`, `category_id` (→ `master_items` kind `nearby_place_category`), `name`, `distance`, `sort_order`

listings.category_id points at a master_items row with kind = 'property_category' (the tour category). There is no separate categories table.

Bookings, Payments & Reviews

Defined in src/product/booking/schema.ts.

Table	Purpose	Key columns
`bookings`	A reservation (lightly polymorphic)	`id`, `reference` (UQ), `public_id` (UQ), `product_type` (class/tour/accommodation), `product_id`, `departure_id` (→ `units`, NULL = private on-request), `booking_mode` (per_person/private), `session_id`, `tour_date`/`end_date`, `start_time`, `nights`, `customer_id` (→ `customers`), `guest_name`/`guest_email`/`guest_phone`/`guest_country`, `num_adults`/`num_children`/`num_infants`, `guests_detail`/`price_breakdown` (JSONB), `subtotal`/`discount`/`fees`/`tax`/`deposit`/`total` (cents), `currency`, `coupon_id`/`coupon_code`, `status`, `payment_status`, `host_status`, `contact_revealed`, `influencer_id`/`referral_discount`, `provider`/`provider_ref`, `special_requests`, `source`
`booking_payments`	Deposit / balance / refund per booking	`id`, `booking_id` (→ `bookings`), `kind` (deposit/balance/refund), `amount` (cents), `currency`, `provider`, `provider_ref`, `status` (pending/paid/failed/refunded), `paid_at`
`booking_extras`	Snapshot of chosen upsells on a booking	`id`, `booking_id` (→ `bookings`), `name`, `qty`, `unit_price` (cents), `total`
`booking_notes`	Internal staff notes on a booking	`id`, `booking_id` (→ `bookings`), `body`, `author`
`booking_emails`	Log of every message sent about a booking	`id`, `booking_id` (→ `bookings`), `email_type`, `recipient`, `subject`, `body`, `status`
`listing_reviews`	Traveler review/testimonial for a tour	`id`, `listing_id` (→ `listings`), `author_name`, `author_location`, `avatar`, `rating` (1–5), `comment`, `review_date`, `photos`/`topics` (JSONB), `is_published`, `sort_order`
`coupons`	Booking discount codes	`id`, `code` (UQ), `discount_type` (percent/fixed), `discount_value`, `applies_to` (all/class/tour/accommodation/specific), `target_ids` (JSONB), `min_amount`, `max_discount`, `max_redemptions`, `per_customer_limit`, `times_redeemed`, `combinable`, `valid_from`/`valid_until`, `is_active`

A bookings row is keyed by product_type + product_id ('tour' + a listings.id) and, for a specific departure, departure_id (→ units). These are soft links (no REFERENCES constraint). customer_id is the traveler and is a real FK. A second coupons variant is also provisioned in src/lib/vendor-schema.ts for the storefront; the booking flow uses the columns above.

Operators (Tour Owners)

Defined in src/lib/operators.ts. An operator is a customer — the hosts row holds the seller-only fields (payout method, commission, verification, status). The platform collects all booking payments centrally and pays hosts out manually.

Table	Purpose	Key columns
`hosts`	An operator profile (1:1 with a customer)	`id`, `customer_id` (→ `customers`, UQ), `business_name`, `slug` (UQ), `bio`, `avatar`, `paypal_email`, `commission_override`, `status` (pending/active/suspended), `is_verified`, `verification_status`, `payout_method`, KYC/legal/banking fields
`host_documents`	Uploaded KYC documents, reviewed by admin	`id`, `host_id` (→ `hosts`), `kind` (id_front/id_back/selfie/proof_of_address), `file_url`, `status`, `reviewed_by`, `reviewed_at`
`host_earnings`	Earnings ledger — one row per settled booking	`id`, `booking_id` (UQ), `host_id` (→ `hosts`), `gross_cents`, `commission_cents`, `fee_cents`, `net_cents`, `currency`, `rate`, `status` (pending/approved/paid/rejected), `payout_request_id`
`host_payout_requests`	Operator withdrawal request	`id`, `host_id` (→ `hosts`), `amount_cents`, `currency`, `paypal_email`, `status` (pending/approved/paid/rejected), `admin_note`, `requested_at`, `processed_at`
`host_payment_history`	Record of a paid-out payout	`id`, `host_id` (→ `hosts`), `payout_request_id` (→ `host_payout_requests`), `amount_cents`, `paypal_email`, `transaction_id`, `status`, `paid_at`
`platform_settings`	Singleton monetization config (`id = 1`)	`monetization_mode` (commission/subscription/hybrid), `default_commission_percent`, `min_payout_cents`, `payout_schedule`, `require_host_approval`
`subscription_plans`	Admin-managed operator plans	`id`, `name`, `slug` (UQ), `price_cents`, `interval` (month/year), `max_listings`, `featured_slots`, `commission_override`, `features` (JSONB), `is_active`, `sort_order`
`host_subscriptions`	An operator's current plan	`id`, `host_id` (→ `hosts`), `plan_id` (→ `subscription_plans`), `status` (active/past_due/cancelled/expired), `current_period_end`, `provider`, `provider_ref`
`conversations`	Traveler ↔ operator thread (per tour)	`id`, `host_id` (→ `hosts`), `guest_customer_id` (→ `customers`), `listing_id`, `booking_id`, `last_message_at`, `guest_unread`, `host_unread`, UQ `(host_id, guest_customer_id, COALESCE(listing_id,0))`
`messages`	A chat message	`id`, `conversation_id` (→ `conversations`), `sender` (guest/host), `body`, `read_at`

Influencers (Affiliate)

Defined in src/lib/influencers.ts — an affiliate program that attributes bookings to a referring influencer and mirrors the operator earnings → payout lifecycle.

Table	Purpose	Key columns
`influencers`	Affiliate account	`id`, `username` (UQ), `email` (UQ), `first_name`/`last_name`, `paypal_email`, `commission_type` (percentage/fixed), `commission_value`, `referral_discount`, `status`, `password_hash`, `min_payout_cents`
`influencer_referral_links`	Trackable referral link	`id`, `influencer_id` (→ `influencers`), `code`, `target_type` (site/tour/…), `target_id`, `target_slug`, `label`, `url`, `clicks`
`influencer_social_links`	Social profiles	`id`, `influencer_id` (→ `influencers`), `platform`, `url`, UQ `(influencer_id, platform)`
`influencer_visits`	Visit tracking	`id`, `influencer_id` (→ `influencers`), `ref_code`, `visitor_id`, `ip`, `path`, `is_unique`
`influencer_clicks`	Referral-link clicks	`id`, `influencer_id` (→ `influencers`), `link_id` (→ `influencer_referral_links`), `target_type`, `target_id`, `visitor_id`
`influencer_bookings`	A booking attributed to an influencer	`id`, `influencer_id` (→ `influencers`), `booking_id` (UQ), `product_type`, `product_id`, `customer_id`, `amount_cents`, `discount_cents`, `status`
`influencer_commissions`	Commission ledger per attributed sale	`id`, `influencer_id` (→ `influencers`), `booking_id`, `influencer_booking_id` (→ `influencer_bookings`), `amount_cents`, `commission_type`, `rate`, `status`, `payout_request_id`
`influencer_payout_requests`	Influencer withdrawal request	`id`, `influencer_id` (→ `influencers`), `amount_cents`, `paypal_email`, `status`, `requested_at`, `processed_at`
`influencer_payment_history`	Record of a paid influencer payout	`id`, `influencer_id` (→ `influencers`), `payout_request_id` (→ `influencer_payout_requests`), `amount_cents`, `transaction_id`, `status`, `paid_at`
`influencer_notifications`	In-app notifications	`id`, `influencer_id` (→ `influencers`), `type`, `title`, `body`, `is_read`
`influencer_activity_logs`	Activity audit	`id`, `influencer_id` (→ `influencers`), `action`, `meta` (JSONB), `ip`

Customers, Accounts & Auth

Defined in src/lib/vendor-schema.ts (customers, gift cards, RBAC) and src/lib/auth-schema.ts (admin users).

Table	Purpose	Key columns
`customers`	Traveler and operator accounts	`id`, `slug` (UQ), `name`, `email`, `password_hash`, `account_type` (traveler/operator role tag), `email_verified`/`phone_verified`, `otp_code`/`otp_expires_at`/`otp_purpose`, travel prefs (`preferred_language`, `interests`, `dietary_notes`, `trip_type`, …), ID/verification fields, `notification_prefs` (JSONB), `is_blacklisted`, `is_active`
`customer_verification_documents`	Traveler ID verification uploads	`id`, `customer_id` (→ `customers`), `kind` (id_front/id_back/selfie), `file_url`, `status`, `reviewed_by`, `reviewed_at`
`customer_notifications`	In-app notification per customer	`id`, `customer_id` (→ `customers`), `title`, `body`, `kind`, `is_read`
`gift_cards`	Stored-value codes	`id`, `code` (UQ), `initial_cents`, `balance_cents`, `currency`, `status`, `issued_to_customer_id` (→ `customers`), `recipient_email`, `expires_at`
`users`	Admin/staff login accounts	`id`, `email` (UQ), `password` (bcrypt), `name`, `role_id` (→ `roles`), `avatar`, `is_active`
`roles`	RBAC roles	`id`, `slug` (UQ), `name`, `permissions` (JSONB), `is_system`, `is_active`
`permissions`	Permission catalog	`id`, `key` (UQ), `label`, `group_name`, `description`
`activity_log`	Admin activity audit	`id`, `user_label`, `action`, `summary`, `entity_type`, `entity_id`, `meta` (JSONB)

CMS / Website Content

Defined in scripts/init-db.ts (plus admin enhancements added by ALTER TABLE in src/lib/vendor-schema.ts).

Table	Purpose	Key columns
`pages`	Static CMS pages (privacy, terms, …)	`id`, `slug` (UQ), `title`, `content`, `meta_title`, `meta_description`, `is_active`
`blogs`	Blog posts	`id`, `slug` (UQ), `title`, `category_id` (→ `blog_categories`), `excerpt`, `content`, `image`, `author`, `tags` (JSONB), `is_published`, `published_at`
`blog_categories`	Blog taxonomy	`id`, `slug` (UQ), `name`, `description`, `sort_order`
`gallery`	Media gallery items	`id`, `title`, `type`, `url`, `thumbnail`, `category`, `sort_order`, `is_active`
`hero_slides`	Home hero slider	`id`, `eyebrow`, `title`, `highlight`, `subtitle`, `image`, CTA label/href fields, `sort_order`, `is_active`
`testimonials`	Customer testimonials	`id`, `name`, `location`, `rating`, `message`, `image`, `tour`, `sort_order`, `is_active`
`about`	Key/value content for the About page	`id`, `key` (UQ), `value`
`contacts`	Contact-form submissions	`id`, `name`, `email`, `phone`, `message`, `is_read`
`enquiries`	Enquiry/lead submissions	`id`, `name`, `email`, `phone`, `subject`, `category`, `preferred_date`, `message`, `status`, `is_read`
`newsletter`	Newsletter subscribers	`id`, `email` (UQ), `name`, `is_active`, `subscribed_at`

Integrations, Settings & Licensing

Defined in src/lib/vendor-schema.ts.

Table	Purpose	Key columns
`integration_connections`	Per-channel provider config (encrypted)	`id`, `channel` (EMAIL/STORAGE/PAYMENT/…), `provider`, `label`, `is_active`, `is_primary`, `config`/`meta` (JSONB, secrets encrypted), `last_tested_ok`
`app_settings`	Branding + app config (single row, `id=1`)	`app_name`, `app_icon`, color fields, download URLs, store badges, `versioning` (JSONB)
`theme_settings`	Theme options (single JSONB row, `id=1`)	`id=1`, `data` (JSONB)
`app_license`	Domain license record (single row, `id=1`)	`id=1`, `data` (JSONB LicenseRecord)
`notification_templates` / `notification_logs`	Per-event templates + dispatch log	`event`, `channel`, `subject`, `body` / `channel`, `title`, `audience`, `status`, `recipient_count`
`message_templates`	Saved broadcast templates per channel	`id`, `name`, `channel`, `subject`, `body`, `is_active`
`api_tokens` / `webhooks`	API access + outbound webhooks	`token_prefix`, `token_hash`, `scopes` / `url`, `secret`, `events` (JSONB)
`media_assets`	Storage media library	`id`, `object_key`, `url`, `mime_type`, `kind`, `size_bytes`, `folder`, `width`/`height`
`translations`	Multi-language field values	`entity_type`, `entity_id`, `field`, `language_code`, `value`, UQ `(entity_type, entity_id, field, language_code)`
`currencies`, `languages`, `countries`, `locations`	Reference/localization data	code (UQ), name, flags, hierarchy (`parent_id` / `country_code`)

Store (Physical Goods — Optional Commerce)

src/lib/vendor-schema.ts also ships a full storefront schema reused from the ecommerce base. These coexist with the tours tables and power optional product sales, inventory, shipping, and order fulfilment.

Table group	Tables
Products	`products`, `product_categories`, `brands`, `attributes`, `product_reviews`, `stock_movements`
Orders	`orders`, `order_items`, `order_returns`, `order_refunds`, `order_invoices`, `shipments`, `shipping_labels`
Shipping	`shipping_methods`, `shipping_zones`, `shipping_rates`, `delivery_slots`, `pickup_locations`, `warehouses`, `stock_transfers`
Carts	`customer_carts`, `stock_subscriptions`
Reviews	`vendor_reviews`

Documents (Optional Domain)

src/product/document/schema.ts provisions a document/attachment domain (documents, document_versions, document_downloads, customer_documents, document_settings, ai_usage, customer_oauth_accounts). It builds on the shared booking foundation (ensureBookingSchema()) and hangs off customers; it is provisioned only where its ensureDocumentSchema() runs.