Product Entity Relationships

Complete reference for how product entities relate to each other in the database.

Entity Relationship Diagram

erDiagram
    Market {
        int id PK
        string code "ES, DE, FR"
        string name
        json supported_locales
        json country_codes
        json currency_codes
    }

    TcaiProfile {
        int id PK
        string name
        string description "nullable"
        text custom_instructions
        boolean is_default
    }

    ProductTemplate {
        int id PK
        int tcai_profile_id FK "nullable"
        string title
        string sku
        string source_locale
        json itinerary
        int duration
        json highlights
        json categories
    }

    ProductByMarket {
        int id PK
        int product_template_id FK
        int market_id FK
        string locale
        string sku
        string status
        date search_start_date
        date search_end_date
        json excluded_dates
    }

    ProductComponent {
        int id PK
        int product_template_id FK
        string component_name
        string component_type
        int sequence_order
        string inventory_type
        int inventory_id
    }

    ProductByMarketTranslation {
        int id PK
        int product_by_market_id FK
        string locale
        string title
        string url_slug
        json itinerary
    }

    ProductByMarketFlightConfig {
        int id PK
        int product_by_market_id FK
        int airport_id FK
        string type
        boolean is_active
    }

    ProductByMarketFlightLeg {
        int id PK
        int flight_config_id FK
        int leg_index
        int day_offset
        string flight_type
        int origin_airport_id FK
        int destination_airport_id FK
    }

    CmsRegion {
        int id PK
        string slug
        string image "nullable"
        int sort_order
        boolean status
    }

    CmsRegionTranslation {
        int id PK
        int cms_region_id FK
        string locale
        string name
        string description "nullable"
    }

    CmsCountry {
        int id PK
        int cms_region_id FK
        string slug
        string image "nullable"
        int sort_order
        boolean status
    }

    CmsCountryTranslation {
        int id PK
        int cms_country_id FK
        string locale
        string name
        string description "nullable"
    }

    AgentConversation {
        string id PK "UUID 36"
        int user_id FK
        string title
    }

    AgentConversationMessage {
        string id PK "UUID 36"
        string conversation_id FK
        int user_id FK
        string agent
        string role
        text content
    }

    TcaiProfile ||--o{ ProductTemplate : "has"
    Market ||--o{ ProductByMarket : "has"
    ProductTemplate ||--o{ ProductByMarket : "has"
    ProductTemplate ||--o{ ProductComponent : "has"
    ProductByMarket ||--o{ ProductByMarketTranslation : "has"
    ProductByMarket ||--o{ ProductByMarketFlightConfig : "has"
    ProductByMarketFlightConfig ||--o{ ProductByMarketFlightLeg : "has"
    CmsRegion ||--o{ CmsRegionTranslation : "has"
    CmsRegion ||--o{ CmsCountry : "has"
    CmsCountry ||--o{ CmsCountryTranslation : "has"
    CmsCountry }o--o{ ProductByMarket : "cms_country_product_by_market"
    AgentConversation ||--o{ AgentConversationMessage : "has"

Relationship Summary

Parent	Child	Cardinality	Foreign Key
TcaiProfile	ProductTemplate	1:N	tcai_profile_id (nullable, nullOnDelete)
ProductTemplate	ProductByMarket	1:N	product_template_id
ProductTemplate	ProductComponent	1:N	product_template_id
Market	ProductByMarket	1:N	market_id
ProductByMarket	ProductByMarketTranslation	1:N	product_by_market_id
ProductByMarket	ProductByMarketFlightConfig	1:N	product_by_market_id
ProductByMarketFlightConfig	ProductByMarketFlightLeg	1:N	flight_config_id
Airport	ProductByMarketFlightConfig	1:N	airport_id
Airport	ProductByMarketFlightLeg	1:N	origin_airport_id, destination_airport_id
CmsRegion	CmsRegionTranslation	1:N	cms_region_id
CmsRegion	CmsCountry	1:N	cms_region_id
CmsCountry	CmsCountryTranslation	1:N	cms_country_id
CmsCountry	ProductByMarket	M:N	cms_country_product_by_market pivot
AgentConversation	AgentConversationMessage	1:N	conversation_id

Entity Purposes

ProductTemplate

Master definition of a travel package. Defines what a trip IS.

• Stores base content in source locale
• Contains itinerary (cities, nights)
• Auto-calculates duration
• Has no airport/flight data

ProductByMarket

Market-specific configuration for selling a template.

• Links template to a specific market + locale
• Configures flight search date ranges
• Unique per template + market + locale combination

ProductByMarketTranslation

Localized content for display.

• One per ProductByMarket (primary locale)
• Contains translated titles, descriptions, itinerary
• Holds SEO metadata (url_slug, meta_*)

ProductByMarketFlightConfig

Departure airport configuration.

• One per departure airport per ProductByMarket
• Defines flight search type (multi_city, separate)
• Contains all flight legs for that departure

ProductByMarketFlightLeg

Individual flight segments.

• Ordered by leg_index
• Stores origin/destination airports
• Tracks day_offset from trip start
• Classifies as international, domestic, or arnk

ProductComponent

Components that make up a product template.

• Links to inventory items (transfers, hotels)
• Defines sequence and day placement
• Supports mandatory/optional components

TcaiProfile

AI content generation profile for product templates (TCAI = Trip Content AI).

• Stores custom instructions that guide AI when generating/refining product content
• One profile can be shared across many templates
• Exactly one profile may be marked is_default
• Deleting a profile nullifies tcai_profile_id on linked templates (nullOnDelete)

CmsRegion / CmsCountry

CMS content hierarchy for the public-facing destination pages.

• Regions group countries (e.g. “Europe” contains “Italy”, “Spain”)
• Both use a translations table for multi-locale support (name, description)
• Countries link to ProductByMarket via a pivot table with sort_order
• status boolean controls visibility on the frontend
• slug used for URL routing on the Astro frontend

AgentConversation / AgentConversationMessage

Laravel AI package tables storing admin-panel AI chat history.

• String UUIDs (36 chars) as primary keys
• Messages store agent name, role, content, plus serialized tool_calls, tool_results, usage, and meta
• Indexed on (user_id, updated_at) and (conversation_id, user_id, updated_at)

Unique Constraints

Table	Columns	Purpose
products_by_market	product_template_id, market_id, locale	One product per template/market/locale
products_by_market	sku	Unique market SKU
product_by_market_flight_configs	product_by_market_id, airport_id	One config per departure airport
product_by_market_translations	product_by_market_id, locale	One translation per locale
product_by_market_translations	locale, url_slug	Unique URL slugs per locale

Cascade Deletes

All child entities cascade delete from their parents:

flowchart TD
    TP[TcaiProfile deleted] --> TPNULL["ProductTemplate.tcai_profile_id → NULL"]
    PT[ProductTemplate deleted] --> PBM[All ProductByMarket deleted]
    PBM --> PBMT[All ProductByMarketTranslation deleted]
    PBM --> PBMFC[All ProductByMarketFlightConfig deleted]
    PBMFC --> PBMFL[All ProductByMarketFlightLeg deleted]
    CR[CmsRegion deleted] --> CRT[All CmsRegionTranslation deleted]
    CR --> CC[All CmsCountry deleted]
    CC --> CCT[All CmsCountryTranslation deleted]
    CC --> CCPBM[Pivot rows in cms_country_product_by_market deleted]

Query Examples

Get product with all relations

ProductByMarket::with([
    'productTemplate',
    'market',
    'translation',
    'flightConfigs.legs',
    'flightConfigs.airport',
])->find($id);

Find active products for a market

ProductByMarket::query()
    ->where('market_id', $marketId)
    ->where('status', 'active')
    ->with(['productTemplate', 'translation'])
    ->get();

Get all departure airports for a product

$product->flightConfigs()
    ->active()
    ->with('airport')
    ->get()
    ->pluck('airport');

Source Files

Model	Location
TcaiProfile	backend/app/Models/TcaiProfile.php
ProductTemplate	backend/app/Models/ProductTemplate.php
ProductByMarket	backend/app/Models/ProductByMarket.php
ProductByMarketTranslation	backend/app/Models/ProductByMarketTranslation.php
ProductByMarketFlightConfig	backend/app/Models/ProductByMarketFlightConfig.php
ProductByMarketFlightLeg	backend/app/Models/ProductByMarketFlightLeg.php
ProductComponent	backend/app/Models/ProductComponent.php
Market	backend/app/Models/Market.php
CmsRegion	backend/app/Models/CmsRegion.php
CmsRegionTranslation	backend/app/Models/CmsRegionTranslation.php
CmsCountry	backend/app/Models/CmsCountry.php
CmsCountryTranslation	backend/app/Models/CmsCountryTranslation.php

Related

• Products Overview - System architecture
• Product Templates - Template details
• Products by Market - Market configuration