Dynamic Flight Cache

Pre-fetches and stores flight pricing data for travel products to enable fast price lookups without real-time API calls. Supports hybrid configurations combining international multi-city flights with domestic flights.

When to Use

• Displaying flight prices on product listing pages
• Calculating total trip costs for offers
• Price trend analysis and monitoring
• Batch pricing for seasonal campaigns

Architecture Overview

The system uses PostgreSQL as the cache storage backend (not Redis or file cache). Flight pricing data is stored in normalized tables with relationships between routes, cache entries, and flight segments.

ProductByMarket
    └── ProductByMarketFlightConfig (per departure airport)
            └── DynamicFlightCacheRoute (unique route + duration)
                    └── DynamicFlightCache (pricing per date/CUG)
                            └── DynamicFlightCacheSegment (flight details)

Data Flow

1. Route Preparation - Creates route definitions from product flight configs
2. Cache Entry Creation - Creates pending entries for each searchable date
3. Search Execution - Calls Aerticket API and stores pricing results
4. Price Retrieval - Queries completed cache entries for display

Database Schema

Routes Table (dynamic_flight_cache_routes)

Defines unique flight routes for caching. Routes are product-agnostic and identified by flight segments + trip duration.

Column	Type	Description
id	bigint	Primary key
flight_route	jsonb	Array of segments with origin, destination, day_offset
trip_duration_days	smallint	Total trip duration
is_domestic	boolean	Domestic flight flag
is_multi_city	boolean	Multi-city booking flag
segment_index	smallint	Order in trip (1-based)
is_active	boolean	Route active for caching

Flight Route JSON Structure:

[
  {"origin": "BCN", "destination": "CMB", "day_offset": 0},
  {"origin": "CMB", "destination": "MLE", "day_offset": 3},
  {"origin": "MLE", "destination": "BCN", "day_offset": 10}
]

Indexes:

• GIN index on flight_route for JSONB queries
• Unique constraint on (trip_duration_days, is_multi_city)

Cache Entries Table (dynamic_flight_caches)

Stores pricing data for specific dates and routes.

Column	Type	Description
id	bigint	Primary key
route_id	bigint	FK to routes table
provider_id	bigint	FK to providers table
departure_date	date	Trip start date
return_date	date	Trip end date (nullable)
arrival_date	date	Arrival date at destination (for land rate matching)
status	varchar	Cache entry status
searched_at	timestamp	Last search execution time
cug_type	varchar	Closed User Group type
request_identifier	varchar	Aerticket API flow_id
fare_position	smallint	Price ranking (1=cheapest)
currency	varchar	Price currency (EUR)
base_price	numeric	Base fare amount
tax	numeric	Tax amount
total_price	numeric	Total price
expires_at	timestamp	Cache expiration (nullable)
api_response_time_ms	integer	API response time

Indexes:

• Composite index on (route_id, departure_date, return_date, total_price)
• Index on status for batch processing
• Index on cug_type for filtering
• Index on expires_at for expiration queries

Cache Status Values

Status	Description	Can Search
pending	Entry created, awaiting search	Yes
searching	API call in progress	No
completed	Successfully cached	No
failed	Search failed	Yes
expired	Cache expired	Yes

Segments Table (dynamic_flight_cache_segments)

Stores detailed flight information for cached fares.

Column	Type	Description
id	bigint	Primary key
flight_cache_id	bigint	FK to caches table
leg_sequence	smallint	Leg number (1=outbound)
segment_number	integer	Segment within leg
flight_number	varchar	Airline + flight number
cabin_class	varchar	Cabin class
departure_airport_id	bigint	FK to airports
departure_time	timestamp	Departure datetime
arrival_airport_id	bigint	FK to airports
arrival_time	timestamp	Arrival datetime
operating_carrier	varchar	Operating airline code
baggage_allowance	varchar	Baggage info
duration_minutes	integer	Flight duration

Cache Population Services

DynamicFlightCachePopulatorService

Main service for preparing routes and executing searches.

Source: backend/app/Services/Flights/DynamicFlightCachePopulatorService.php

Preparing Routes

$service = app(DynamicFlightCachePopulatorService::class);

// Prepare routes for a single config
$result = $service->prepareRoutesForConfig($config);
// Returns: routes_created, routes_reused, segments, errors

// Prepare routes for entire product
$result = $service->prepareRoutesForProduct($product);
// Returns: configs_processed, routes_created, routes_reused, errors

Creating Cache Entries

// Create pending entries for date range
$result = $service->createCacheEntriesForConfig($config);
// Returns: entries_created, entries_existing, errors

// Combined: prepare routes AND create entries
$result = $service->prepareForConfig($config);

Executing Searches

// Execute all pending searches
$result = $service->executeSearches();

// Execute specific entries
$result = $service->executeSearches(collect([$cacheEntry]));

// Returns: total_searches, successful_searches, failed_searches, fares_cached, segments_cached, errors

DynamicFlightCacheService

Handles caching search results from manual flight searches.

Source: backend/app/Services/Flights/DynamicFlightCacheService.php

$service = app(DynamicFlightCacheService::class);

// Cache results from a search (only if matching route exists)
$faresCached = $service->cacheSearchResults($searchRequest, $searchResponse);

Configuration

Fare Caching Limits

• Maximum fares per search: 5 (positions 1-5 by price)
• Provider: Aerticket (aerticket code)

Search Parameters

Cache entries are created with:

• CUG Type: ALL (default)
• Currency: EUR
• Fare Position: 1-5 (1 = cheapest)

TTL and Expiration

The current implementation stores cache entries without automatic expiration (expires_at = null). Entries are manually refreshed through:

1. Manual Refresh - Admin triggers re-search
2. Status-Based Refresh - Re-search pending, failed, or expired entries

Expiration Scopes

// Get non-expired entries
DynamicFlightCache::active()->get();

// Get expired entries
DynamicFlightCache::expired()->get();

// Check if entry is expired
$cache->isExpired(); // false if expires_at is null

Refresh Scheduling

The DynamicFlightRefreshSchedule model supports scheduled cache refresh with different triggers:

Trigger	Description
scheduled	Regular TTL-based refresh
gap_triggered	Price gap detection
sales_triggered	Sales event
manual	Operator-initiated
calibration	Accuracy measurement

Source: backend/app/Models/DynamicFlightRefreshSchedule.php

// Find due schedules
$schedules = DynamicFlightRefreshSchedule::due()->get();

// Execute and mark complete
$schedule->markRunning();
// ... execute refresh ...
$schedule->markExecuted();

Outbound Flight Timing

Helper methods extract timing information from the first leg segments:

$cache = DynamicFlightCache::find(1);

// First segment departure time
$cache->getOutboundDepartureTime();  // Carbon instance or null

// Last segment arrival time (at destination)
$cache->getOutboundArrivalTime();    // Carbon instance or null

// Total duration in minutes
$cache->getOutboundDurationMinutes(); // int or null

// Formatted duration
$cache->getFormattedOutboundDuration(); // "14h 30m" or null

Source: backend/app/Models/DynamicFlightCache.php

Price Retrieval

Get Total Flight Price

use App\Models\DynamicFlightCache;
use App\Enums\FlightCugType;

$totalPrice = DynamicFlightCache::getTotalFlightPrice(
    flightRoute: 'BCN -> CMB, CMB -> MLE, MLE -> BCN',
    tripDuration: 11,
    tripStartDate: Carbon::parse('2025-03-01'),
    cugType: FlightCugType::ALL
);

Query Scopes

// Filter by status
DynamicFlightCache::pending()->get();
DynamicFlightCache::completed()->get();
DynamicFlightCache::searchable()->get(); // pending, failed, expired

// Filter by route type
DynamicFlightCache::domestic()->get();
DynamicFlightCache::international()->get();

// Filter by CUG type
DynamicFlightCache::forCug(FlightCugType::ALL)->get();

// Filter by date range
DynamicFlightCache::forDepartureDateRange($from, $to)->get();

Admin Interface

Two Filament resources provide access to flight cache data, both under “Inventory Management” navigation group.

Dynamic Flight Caches Resource

URL: /admin/dynamic-flight-caches

Browse and manage individual cache entries (pricing records).

Features:

• Route tabs: Top 10 most-used routes displayed as quick-filter tabs
• Product routes tab: Dynamic tab when navigating from ProductByMarket (shows only relevant routes)
• Default grouping by route with collapsible groups showing route name and duration
• Searchable multi-select route filter (replaces text input)
• Execute searches for pending entries

URL Parameters:

• ?tab=route_{id} - Filter to single route (used for products with one matching route)
• ?product_routes=12,13,14 - Filter to multiple routes (comma-separated IDs)

Source: backend/app/Filament/Resources/DynamicFlightCaches/

Dynamic Flight Cache Routes Resource

URL: /admin/flight-cache-routes

Browse routes (unique flight patterns) and their associated cache entries.

Features:

• Route-centric view with flight segments, duration, domestic/international flags
• View page with infolist and caches relation manager
• Read-only resource (no create/edit)

Source: backend/app/Filament/Resources/DynamicFlightCacheRoutes/

Navigating from ProductByMarket

The ProductByMarket view page includes a “View Cached Flights” action (warning color, server stack icon) that links directly to the DynamicFlightCaches resource filtered by all matching routes.

Visibility: Only shown when at least one active flight config has a matching DynamicFlightCacheRoute.

URL Generation:

• Single matching route: ?tab=route_{id} for cleaner URL
• Multiple matching routes: ?product_routes=12,13 with comma-separated IDs

This supports products with multiple departure airports (BCN, MAD) and domestic flights, collecting ALL matching route IDs from active flight configs.

Source: backend/app/Filament/Resources/ProductsByMarket/Pages/ViewProductByMarket.php

Actions

Execute Searches (Queue) - Dispatch selected entries to the flight-searches queue:

Source: backend/app/Filament/Resources/DynamicFlightCaches/Actions/ExecuteSearchesBulkAction.php

Execute Single Search - Process individual entry:

Source: backend/app/Filament/Resources/DynamicFlightCaches/Actions/ExecuteSingleSearchAction.php

Prepare Flight Searches - Create routes and entries from product:

Source: backend/app/Filament/Resources/ProductsByMarket/Actions/PrepareFlightSearchesAction.php

Value Objects

FlightRoute

Immutable value object representing a multi-segment flight route.

Source: backend/app/ValueObjects/FlightRoute.php

// Create from string
$route = FlightRoute::fromString('BCN -> CMB, CMB -> MLE');

// Create from array
$route = FlightRoute::fromArray([
    ['origin' => 'BCN', 'destination' => 'CMB', 'day_offset' => 0],
    ['origin' => 'CMB', 'destination' => 'MLE', 'day_offset' => 3],
]);

// Query methods
$route->getOrigin();        // 'BCN'
$route->getDestination();   // 'MLE'
$route->getSegmentCount();  // 2
$route->isMultiSegment();   // true
$route->containsSegment('BCN', 'CMB'); // true

Route Configuration Options

The FlightRouteConfigGenerator analyzes product template itineraries to generate flight configuration options.

Source: backend/app/Services/Flights/FlightRouteConfigGenerator.php

Option A: Multi-city + Domestic Flights (Hybrid)

One API call for international multi-city, plus individual calls for domestic flights extracted from itinerary routes with mode='flight'.

Example Route (Thailand trip from Madrid):

International multi-city: MAD → BKK → HKT → MAD (1 API call)
Domestic flights: BKK → CNX, CNX → KBV (2 API calls)
Total: 3 API calls

When to use:

• Most cost-effective for multi-destination trips
• Domestic flights are derived from itinerary routes with mode: 'flight'

Route creation:

• 1 route with is_multi_city=true, is_domestic=false for international
• N routes with is_multi_city=false, is_domestic=true for domestic

Option B: Separate Flights

Each leg is searched individually as separate one-way flights.

Example Route:

MAD → BKK (international)
BKK → CNX (domestic)
CNX → KBV (domestic)
KBV → MAD (international)
Total: 4 API calls

Route creation:

• 1 route per leg with is_multi_city=false
• is_domestic flag set based on flight_type of each leg

Airport Resolution

Main airports are prioritized when resolving cities to airports:

// Prioritizes iata_code = iata_city_code
// Bangkok: BKK (Suvarnabhumi) preferred over DMK (Don Mueang)
Airport::where('city', 'ILIKE', $city)
    ->orderByRaw('CASE WHEN iata_code = iata_city_code THEN 0 ELSE 1 END')
    ->first();

Return Flight Handling

The generator tracks the final itinerary location for the return flight, regardless of transport mode. If the last leg uses surface transport (ARNK), the return still departs from the actual final location.

Example: Itinerary ends in Phuket via ground transport from Krabi → Return flight departs from HKT (Phuket), not KBV (Krabi).

Queue-Based Search Execution

For processing large batches of searches, use the queue-based approach instead of synchronous execution.

SearchFlightCacheJob

Executes a single flight search asynchronously via the flight-searches queue.

Source: backend/app/Jobs/SearchFlightCacheJob.php

Configuration:

• Queue: flight-searches
• Tries: 3
• Timeout: 90 seconds
• Backoff: 30s, 60s, 120s

Usage:

use App\Jobs\SearchFlightCacheJob;

// Dispatch single search
SearchFlightCacheJob::dispatch($cacheId, $userId)
    ->onQueue('flight-searches');

// Dispatch via service (recommended)
$service = app(DynamicFlightCachePopulatorService::class);
$result = $service->dispatchSearchJobs($cacheEntries, $userId);
// Returns: ['dispatched' => int]

Bulk Dispatch via Admin

Select cache entries in the Dynamic Flight Caches table and use Execute Searches bulk action. This dispatches jobs to the queue with user attribution for activity logging.

Activity Logging

All flight search operations are logged to both the database (Activity Logs) and structured logs (Grafana Loki).

FlightSearchActivityLogger

Centralized service for consistent logging across all flight search operations.

Source: backend/app/Services/Flights/FlightSearchActivityLogger.php

Log Name: flight-searches

Activity Events

Event	Trigger	Subject
prepared	PrepareFlightSearchesAction	ProductByMarket
dispatched	ExecuteSearchesBulkAction	None (batch operation)
completed	SearchFlightCacheJob success	DynamicFlightCache
failed	SearchFlightCacheJob failure	DynamicFlightCache

Structured Log Context

All logs include:

• action - Event type (prepare_searches, dispatch_searches, search_completed, search_failed)
• user_id, user_name - User attribution
• cache_id, route_id - Resource identifiers
• route - Flight route string
• departure_date, return_date - Search dates

Querying Activity Logs

use Spatie\Activitylog\Models\Activity;

// Get all flight search activities
Activity::inLog('flight-searches')->get();

// Get activities for specific user
Activity::inLog('flight-searches')
    ->causedBy($user)
    ->get();

// Get activities for specific cache entry
Activity::inLog('flight-searches')
    ->forSubject($cacheEntry)
    ->get();

// Get recent failures
Activity::inLog('flight-searches')
    ->where('event', 'failed')
    ->latest()
    ->limit(10)
    ->get();

Grafana Loki Queries

# All flight search operations
{app="volare"} |= "flight-searches"

# Failed searches
{app="volare"} | json | action="search_failed"

# Searches by user
{app="volare"} | json | user_id="123"

# Specific route
{app="volare"} | json | route=~".*BCN.*CMB.*"

Business Rules

• Maximum 5 fares cached per search (positions 1-5 by price)
• Routes are product-agnostic - shared across products with same flight pattern
• Day offsets determine actual flight dates from trip start date
• Multi-city routes stored as single combined entry for international legs
• Domestic routes stored separately for individual pricing
• CUG type filtering - searches can target specific fare groups
• Main airports prioritized - BKK over DMK when resolving cities

Troubleshooting

Wrong Airport Selected

Issue: DMK selected instead of BKK for Bangkok

Cause: Query returning first match instead of main airport

Solution: Verify airport has matching iata_code and iata_city_code:

SELECT * FROM airports
WHERE city ILIKE 'Bangkok'
ORDER BY CASE WHEN iata_code = iata_city_code THEN 0 ELSE 1 END;

Return Flight From Wrong City

Issue: Return flight departing from Krabi instead of Phuket

Cause: Last destination in itinerary reached by ARNK (surface transport), not tracked

Solution: The generator now always includes final itinerary location regardless of transport mode.

Domestic Flights Not Appearing in Option A

Issue: Option A shows only international legs

Cause: Itinerary routes missing mode: 'flight'

Check: Verify itinerary has routes with mode='flight':

$itinerary = $template->itinerary;
foreach ($itinerary as $item) {
    $routes = $item['routes'] ?? [];
    foreach ($routes as $route) {
        if ($route['mode'] === 'flight') {
            // This creates a domestic leg
        }
    }
}

Separate Flights All Marked International

Issue: Option B creates routes with wrong is_domestic flag

Cause: Flight leg flight_type not set correctly

Check: Verify legs have correct flight_type (‘international’, ‘domestic’, ‘arnk’):

$config->legs->each(fn ($leg) => dump($leg->flight_type));

Related Documentation

• Products By Market - Product configuration with flight configs
• AerTicket Integration - Flight search API service
• Flight Search UI - Interactive search interface