Dynamic Flight Cache

Pre-fetches and stores flight pricing data for travel products to enable fast price lookups without real-time API calls.

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)
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();

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

Dynamic Flight Caches Resource

URL: /admin/dynamic-flight-caches

Features:

• View all cache entries with filtering
• Execute searches for pending entries
• View detailed fare and segment information

Actions

Execute Searches - Bulk process all searchable entries:

Source: backend/app/Filament/Resources/DynamicFlightCaches/Actions/ExecuteSearchesAction.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

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
• CUG type filtering - searches can target specific fare groups

Related Documentation

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