Database Documentation
This document catalogs the Tutor PostgreSQL schema. Tutor is a
white-label tutoring & education booking marketplace: hosts (tutors) publish
lessons (listings) with weekly availability; a slot becomes a session row when booked
(experience_sessions) with a fixed number of seats, and students (customers) book one or
more seats and pay a deposit or the full amount. 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(lessons, sessions, bookings),src/lib/hosts.ts(hosts, earnings, subscriptions),src/lib/vendor-schema.ts(customers, CMS, config, i18n),src/lib/influencers.ts,src/lib/customer-portal.ts,src/product/master/schema.ts,src/lib/booking/refund-schema.ts, andscripts/init-db.ts. - Keys:
SERIALinteger primary keys; foreign keys viaREFERENCES. Public-facing rows also carry an opaque, non-sequentialpublic_id(e.g.b…bookings,p…listings,s…sessions,rfd…refunds) used in URLs instead of the serial id. - Naming:
snake_casecolumns. - Money: integer minor units (cents). The booking tables use plain
INTcolumns (subtotal,discount,deposit,total); the earnings/commission ledgers use the*_centssuffix. Each is paired with acurrencycolumn (defaultEUR). - Flexible data: arrays/objects stored as
JSONB(galleries,whats_included,price_breakdown, config docs, …). - Soft enums: status/type fields are plain
VARCHARwith documented allowed values, so they can be extended without a schema change. - Common columns:
is_active,sort_order,created_at,updated_at.
Tables are grouped below by domain.
Lessons & Catalogue
Defined in src/product/booking/schema.ts and src/product/master/schema.ts. A lesson is
one listings row; a scheduled lesson time is one experience_sessions row.
| Table | Purpose | Key columns |
|---|---|---|
listings |
A lesson (tutoring subject offered by a tutor) | id, public_id, slug (UQ), name, summary, description, category_id→master_items, owner_id→hosts, experience_type (class/tour/dining), hero_image, gallery (JSONB), city, region, country, location_id→locations, latitude/longitude, duration_minutes, skill_level, cuisine_type (legacy, unused), whats_included/dietary_options/languages_spoken (JSONB), what_to_bring, class_rules, cancellation_policy_slug, deposit_pct, instant_booking, status, sort_order |
experience_sessions |
A scheduled lesson time with seat inventory | id, public_id, listing_id→listings, starts_at, ends_at, timezone, seats_total, seats_booked, min_seats, max_per_booking, price_per_seat, price_private, currency, instructor_id→hosts, status (scheduled/cancelled/completed), is_active |
listing_reviews |
Student testimonials on a lesson | id, listing_id→listings, customer_id→customers, booking_id, author_name, author_location, rating (1.0–5.0), comment, photos/topics (JSONB), review_date, is_published, sort_order |
master_items |
All master data (lesson categories, tags, …) | id, kind, name, slug, icon, image, is_active, sort_order; UQ (kind, slug) and (kind, lower(name)) |
listing_master_items |
Lesson ↔ master-item tags (subject/level/…) | PK (listing_id, master_item_id) |
listing_nearby_places |
Points of interest near the venue (in-person lessons) | id, listing_id, category_id→master_items, name, distance, sort_order |
listing_issues |
"Report an issue" queue from lesson detail | id, public_id, listing_id→listings, listing_name/listing_slug (snapshot), issue_type, comments, reporter_name/reporter_email, customer_id, status (open/reviewing/resolved/dismissed), admin_note, is_read |
amenity_groups |
"What's included" group (e.g. "Materials") | id, title, subtitle, icon, image, sort_order, is_active |
amenities |
A sub-item within a group | id, group_id→amenity_groups, title, subtitle, icon, image, sort_order, is_active |
extras |
Paid add-on catalogue (extra study materials, exam-prep pack) | id, name, description, applies_to (all/class/tour/accommodation), price (cents), price_type (flat/per_person/…), max_qty, is_active, sort_order |
resource_days |
Per-day calendar override for a session | id, resource_type (session), resource_id, date, price_override, inventory, note/description/tooltip; UQ (resource_type, resource_id, date) |
resource_notes |
Calendar-level note for a resource | id, resource_type, resource_id, body |
Bookings & Payments
Defined in src/product/booking/schema.ts and src/lib/booking/refund-schema.ts. A
booking is keyed to a session_id + seats + booking_mode; seats are claimed atomically
against experience_sessions.seats_total − seats_booked.
| Table | Purpose | Key columns |
|---|---|---|
bookings |
The reservation record (lightly polymorphic) | id, reference (UQ), public_id, product_type (class/tour/dining), product_id→listings, session_id→experience_sessions, booking_mode (per_person/group/private), customer_id→customers, guest_name/guest_email/guest_phone, num_adults/num_children, price_breakdown (JSONB), subtotal/discount/fees/tax/deposit/total (cents), currency, coupon_id/coupon_code, influencer_id/referral_discount, status (pending/confirmed/cancelled/completed/no_show), payment_status (unpaid/deposit_paid/paid/refunded), host_status, provider/provider_ref, source, deleted_at |
booking_extras |
Chosen add-ons snapshotted onto a booking | id, booking_id→bookings, name, qty, unit_price (cents), total |
booking_payments |
Deposit / balance / refund ledger | id, booking_id→bookings, kind (deposit/balance/refund), amount (cents), currency, provider/provider_ref, status (pending/paid/failed/refunded), paid_at |
booking_refunds |
Tiered-refund admin approval queue | id, public_id, booking_id→bookings, amount (cents), currency, policy (slug), pct, reason, initiated_by (customer/host/admin/system), status (pending/approved/refunded/rejected/failed/refund_pending), gateway/gateway_ref, processed_by, processed_at; UQ one open request per booking |
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 (sent/failed), error |
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 (cents), max_redemptions, per_customer_limit, times_redeemed, valid_from/valid_until, combinable, is_active |
Cancellation & refunds. Tiered policies (
flexible/moderate/firm/strict/non-refundable) are set per lesson vialistings.cancellation_policy_slug; the refund amount is computed from the policy + lead time. Both customer- and host-initiated cancellations create apendingbooking_refundsrow for admin approval; approval fires the gateway refund and releases the seats.
Hosts (Tutors)
Defined in src/lib/hosts.ts. A host is a hosts row linked 1:1 to a customers account;
listings.owner_id and experience_sessions.instructor_id point back to hosts.id.
| Table | Purpose | Key columns |
|---|---|---|
hosts |
Host/tutor 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, KYC fields (legal_name, tax_id, id_type, reg. address), payout/banking fields, languages_spoken, min_payout_cents |
host_documents |
Uploaded KYC documents (admin-reviewed) | id, host_id→hosts, kind (id_front/id_back/selfie/proof_of_address), file_url, status (pending/approved/rejected), reviewed_by, reviewed_at |
host_earnings |
Per-booking revenue-share ledger | 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 |
Host 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 |
Settled payout record | id, payout_request_id→host_payout_requests, host_id→hosts, amount_cents, paypal_email, transaction_id, status, paid_at |
subscription_plans |
Admin-managed plans to list lessons | id, name, slug (UQ), price_cents, interval (month/year), max_listings, featured_slots, commission_override, features (JSONB), is_active, sort_order |
host_subscriptions |
A host's current subscription | id, host_id→hosts, plan_id→subscription_plans, status (active/past_due/cancelled/expired), current_period_end, provider/provider_ref |
platform_settings |
Single-row monetization config (id=1) |
monetization_mode (commission/subscription/hybrid), default_commission_percent, min_payout_cents, payout_schedule, require_host_approval |
Customers (Students)
Defined in src/lib/vendor-schema.ts and src/lib/customer-portal.ts. A customer is the
student who books a lesson.
| Table | Purpose | Key columns |
|---|---|---|
customers |
Storefront/student account | id, slug (UQ), name, email, email_verified, phone/phone_code, phone_verified, password_hash, otp_code/otp_expires_at/otp_purpose, avatar, city/country_code, dietary_notes, interests, preferred_language/preferred_currency, verification_status, notification_prefs (JSONB), is_blacklisted, is_active |
customer_verification_documents |
Student ID-verification uploads | id, customer_id→customers, kind (id_front/id_back/selfie), file_url, status, reviewed_by, reviewed_at |
customer_wishlist |
Saved lessons (polymorphic) | id, customer_id→customers, product_type, product_id→listings; UQ (customer_id, product_type, product_id) |
customer_notifications |
In-app notifications | id, customer_id→customers, title, body, kind, link, is_read |
customer_support_tickets |
"Create Ticket / My Tickets" store | id, reference (UQ), customer_id→customers, subject, category (general/booking/payment/technical), message, booking_ref, status (open/pending/resolved/closed), admin_reply |
gift_cards |
Stored-value codes | id, code (UQ), initial_cents, balance_cents, currency, status, issued_to_customer_id→customers, recipient_email, expires_at |
Messaging (Student ↔ Tutor)
Defined in src/lib/hosts.ts. Student contact details in message bodies are masked to the
tutor until a booking between them reveals them.
| Table | Purpose | Key columns |
|---|---|---|
conversations |
One thread per (host, student, listing) | 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 / Affiliates
Defined in src/lib/influencers.ts. Influencers earn commission on referred bookings via
tracked referral links.
| Table | Purpose | Key columns |
|---|---|---|
influencers |
Affiliate account | id, username (UQ), email (UQ), first_name/last_name, paypal_email, commission_type (percentage/…), commission_value, referral_discount, status, password_hash, min_payout_cents, notify_prefs (JSONB) |
influencer_social_links |
Public social profiles | id, influencer_id→influencers, platform, url; UQ (influencer_id, platform) |
influencer_referral_links |
Named referral link / code | id, influencer_id→influencers, code, target_type, target_id, target_slug, label, url, clicks |
influencer_visits |
Landing-page visit log | id, influencer_id→influencers, ref_code, visitor_id, ip, path, is_unique |
influencer_clicks |
Referral-link click log | id, influencer_id→influencers, link_id→influencer_referral_links, target_type/target_id/target_slug, visitor_id |
influencer_bookings |
Attributed booking | id, influencer_id→influencers, booking_id (UQ), product_type/product_id, customer_id, amount_cents, discount_cents, currency, status |
influencer_commissions |
Commission ledger per booking | id, influencer_id→influencers, booking_id, influencer_booking_id→influencer_bookings, amount_cents, commission_type, rate, status, payout_request_id |
influencer_payout_requests |
Withdrawal request | id, influencer_id→influencers, amount_cents, currency, paypal_email, status, admin_note, requested_at, processed_at |
influencer_payment_history |
Settled payout record | 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, link, is_read |
influencer_activity_logs |
Activity audit | id, influencer_id→influencers, action, meta (JSONB), ip |
CMS / Website Content
Defined in src/lib/vendor-schema.ts, scripts/init-db.ts, src/lib/leads-schema.ts,
src/lib/partners.ts, and src/lib/seeds/*.
| Table | Purpose | Key columns |
|---|---|---|
pages |
Static CMS pages (about, legal, …) | id, slug (UQ), title, content, subtitle, banner_image, meta_title/meta_description/meta_keywords, is_active |
blogs |
Blog posts | id, slug (UQ), title, category_id→blog_categories, excerpt, content, image, author, tags (JSONB), status, is_published, published_at |
blog_categories |
Blog taxonomy | id, slug (UQ), name, description, sort_order |
faqs |
Frequently-asked questions | id, category, question, answer, sort_order, is_active |
gallery |
Media gallery items | id, title, type, url, thumbnail, category, sort_order, is_active |
testimonials |
Student testimonials | id, name, location, rating, message, image, sort_order, is_active |
partners |
Partner/brand logos (home marquee) | id, name, logo, url, slug, sort_order, is_active |
about |
Key/value content for the About page | id, key (UQ), value |
hero_sliders |
Hero slider builder (JSONB doc store) | id, name, doc (JSONB — settings + slides + layers), is_default |
builder_pages |
Visual page-builder documents | id, slug (UQ), title, doc (JSONB) |
menus |
Navigation menus (builder) | id, name, slug, location, theme, config (JSONB — items tree), status; UQ (theme, slug) |
media_assets |
Storage media library | id, name, object_key, url, mime_type, kind, size_bytes, folder, alt_text, width/height |
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 |
Localization & Reference Data
Defined in src/lib/vendor-schema.ts and src/lib/i18n-content.ts. Static UI strings are
per-language JSON in Cloudflare R2; dynamic content (lesson name/summary/description) is
machine-translated (OpenAI/Anthropic) and cached in the translation tables with
source-hash invalidation and a human-pin.
| Table | Purpose | Key columns |
|---|---|---|
translations |
Per-entity/field translated values | id, entity_type, entity_id, field, language_code, value, source_hash, status (auto/human); UQ (entity_type, entity_id, field, language_code) |
content_translations |
Source-hash-keyed machine-translation cache | id, language_code, source_hash, source, value; UQ (language_code, source_hash) |
languages |
Available UI languages | code (UQ), name, native_name, direction, flag, locale, is_default, is_active, sort_order |
currencies |
Supported currencies | code (UQ), public_id, name, symbol, native_symbol, decimals, is_default, is_active |
countries |
Country reference (phone/flag/currency) | code (UQ), code3, name, flag, phone_code, currency_code, location_hierarchy, is_default, is_active |
locations |
Hierarchical places (country→state→city) | id, public_id, country_code→countries, parent_id→locations, level_key/level_index, name, path_names (JSONB), image, tagline, is_popular |
Platform, Auth & Integrations
Defined in src/lib/vendor-schema.ts (and scripts/init-db.ts for the base users table).
| Table | Purpose | Key columns |
|---|---|---|
users |
Admin/staff login accounts | id, email (UQ), password (bcrypt), name, role, role_id→roles, avatar, is_active |
roles |
RBAC roles | id, slug (UQ), name, permissions (JSONB), is_system, is_active |
permissions |
Permission catalogue | id, key (UQ), label, group_name, description |
activity_log |
Admin activity audit | id, user_label, action, summary, entity_type, entity_id, meta (JSONB) |
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, colour fields, desktop/mobile 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 |
Per-event/channel templates | id, event, channel, subject, body, offset_minutes_before, is_active; UQ (event, channel) |
notification_logs |
Broadcast/notification dispatch log | id, channel, title, audience, status, recipient_count, scheduled_at, sent_at |
message_templates |
Saved broadcast templates per channel | id, slug, name, channel, category, subject, body, is_active |
api_tokens |
API access tokens | id, name, token_prefix, token_hash, scopes (JSONB), expires_at, revoked_at |
webhooks |
Outbound webhook endpoints | id, label, url, secret, events (JSONB), is_active |
© CreativeCape Solutions · creative-cape.com · support@creative-cape.com
Vertical Packs & Slot Availability
Four additions let one engine run any vertical.
availability_rules
A host's recurring weekly hours for slot-based listings. One row per weekday band.
| Column | Notes |
|---|---|
host_id |
Owner of the rule |
listing_id |
NULL = applies to every listing this host owns |
weekday |
0 = Sunday … 6 = Saturday |
start_time / end_time |
Local wall-clock, interpreted in hosts.timezone |
slot_minutes |
15 / 30 / 45 / 60 / 90 / 120 / … — bounded by the pack |
buffer_minutes |
Gap after each appointment |
capacity |
1 for a one-to-one appointment |
price |
Cents per slot; 0 falls back to the listing |
valid_from / valid_to |
Optional date window |
Times are stored local rather than UTC deliberately: a tutor's 09:00 must stay 09:00 across a daylight-saving change, and UTC storage would shift it twice a year.
availability_exceptions
One-off overrides on the weekly pattern — kind is closed (a holiday), open (an
extra band) or price (same hours, different rate). Unique on
(host_id, COALESCE(listing_id,0), date, kind, COALESCE(start_time,'00:00')).
experience_sessions — new columns
| Column | Notes |
|---|---|
source |
manual = a sitting the host published; rule = a slot created on booking |
rule_id |
Which availability_rules row produced it |
slot_minutes |
Length of the generated slot |
A partial unique index on (listing_id, starts_at) WHERE source = 'rule' makes
double-booking a database constraint rather than application logic: simultaneous
bookings of the same time all attempt the insert, exactly one wins, and the others read
back the winner's row before claiming seats atomically.
listings — new columns
| Column | Notes |
|---|---|
booking_mode |
seat (default, unchanged behaviour) or slot |
attributes |
JSONB, GIN-indexed — every pack-specific field lives here, so packs never add columns |
vertical_snapshots
Created on demand by the pack-switch pipeline. Holds the master_items and
listing_master_items rows replaced by a switch, so the admin panel can restore the
previous taxonomy. The last three are kept.
settings — the vertical section
{ pack, applied_at, slot_minutes, overrides } — which pack is active, and the admin's
narrowing of the pack's slot lengths and booking defaults.