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
invalidated_at	timestamp	When route was auto-invalidated (nullable)
outbound_overnight_days	unsigned tinyint	Calendar-day delta between the international outbound’s first-segment departure and last-segment arrival. Default 0; learned from real fares (see Overnight Outbound Self-Tuning).

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}
]

day_offset is 0-indexed: 0 is the product departure date itself, 1 is the next day, and so on. The Filament admin UI displays this as “Day N” using day_offset + 1, so the operator-facing label for day_offset=0 is “Day 1”. Cascade target dates are productDeparture + day_offset + cumulativeDelay — see Operator Workflow for how delays propagate.

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 for the persisted fare
baggage_included	boolean (nullable)	true when the base fare includes a checked bag; false when not; null pre-search
baggage_upsell_price	numeric (nullable)	Legacy ancillary price field; null for /search-only baggage resolution
baggage_ancillary_code	varchar (nullable)	Aerticket ancillary code (e.g. MBAG) captured at search time for reference
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
• Composite index on (route_id, status, fare_position, searched_at) for consecutive empty search 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

Baggage Policy (BaggagePolicy enum)

Controls how fares without an included checked bag are handled during cache population. Configured via the Flight Search Filters admin page — see Flight Search UI — Search Filter Settings.

Case	Behaviour
OFF	Do not filter by baggage. All fares are cached regardless of inclusion.
STRICT	Only cache fares that include checked baggage in the base price. needBaggage=true is sent to Aerticket so the filter happens server-side.
UPSELLABLE	Cache fares that either include baggage natively or have a bag-included sibling for the same physical itinerary inside the same /search response.

Source: backend/app/Enums/BaggagePolicy.php

Baggage Resolution

Under UPSELLABLE, DynamicFlightCachePopulatorService resolves baggage from the single /search response:

1. Native baggage — keep fares whose selected itineraries include checked baggage on every segment of every leg.
2. In-response sibling — for no-bag fares, search topFares for another fare family with the same itinerary signature, same currency, and hasBaggage() === true (e.g., a bag-included fare family alongside a no-bag one for the same physical flight). This is free: no extra round-trip.

If neither path produces a bag-included fare, the fare is dropped under UPSELLABLE and STRICT. No /search-upsell or ancillary endpoint is called during automated cache population.

Resolved fares are deduplicated twice before persistence:

• By fareId — multiple no-baggage fares can resolve to the same sibling; a later native bag-included fare must not be added twice after being promoted by an earlier sibling swap.
• By commercial identity — Aerticket may return distinct fareIds for commercially identical offerings (same physical itinerary + currency + final price). The populator builds a canonical key from itinerarySignature, currency, and final price to collapse them.

After resolution, the resolved list is re-sorted by total_price so fare positions 1..5 reflect the true customer-facing price ordering.

Source: backend/app/Services/Flights/DynamicFlightCachePopulatorService.php (findBagIncludedSibling, commercialIdentityKey)

Overnight Outbound Self-Tuning

International multi-city searches must anchor the return leg on the actual destination arrival, not on tripStart + nights. When the international outbound crosses midnight in transit, anchoring on tripStart shortens the stay by 1+ nights — the customer was sold N nights but slept N-1 (or N-2) at destination.

DynamicFlightCacheRoute.outbound_overnight_days holds the learned calendar-day delta between the outbound’s first-segment departure and last-segment arrival. The populator uses it as follows:

1. Search build — buildMultiCitySearchRequestFromRoute() adds route.outbound_overnight_days to the static day_offset of the last segment (the international return leg), so the search anchors on the actual outbound arrival. Cold-cache routes default to 0 (same behaviour as before).
2. Learn after success — learnOutboundOvernightDays(route, fare) runs once per successful multi-city search. It computes the observed delta from the returned fare’s first segment and persists it on the route only when it differs from the stored value.

The first search seeds the value; every subsequent search for the same route automatically picks the corrected return date. Single-segment / non-multi-city searches are unaffected.

Source: backend/app/Services/Flights/DynamicFlightCachePopulatorService.php (learnOutboundOvernightDays, buildMultiCitySearchRequestFromRoute)

Arrival-Time Compliance Flag

The Latest Arrival Time admin setting (flights.search.latest_arrival_time) is applied as a post-cache visual flag, not a drop at populate time. Operators wanted to distinguish “Aerticket returned nothing” from “Aerticket returned fares, but none comply with our arrival-time policy”, so the populator stores everything and the UI marks the offenders.

DynamicFlightCache::violatesLatestArrivalTime(?string $cutoff): bool returns true when the outbound leg’s final-segment arrival H:i (local) falls outside the daytime window [06:00, cutoff]:

• HH:MM > cutoff — late (afternoon past cutoff). E.g. 17:30 > 16:00.
• HH:MM < 06:00 — late (pre-dawn arrival). E.g. 00:10, regardless of cutoff.

Dates are ignored: a next-day 15:30 arrival passes a 16:00 cap because the clock reads 15:30 on landing. The 06:00 floor is hardcoded so overnight landings can’t slip through. Returns false when the cutoff is null/empty or segments are not loaded — the UI treats “unknown” as compliant.

The shared [06:00, cutoff] check lives in FareTimeWindowFilter::hhmmWithinDaytimeWindow() so the model method (cached-row path) and FareTimeWindowFilter::arrivesNoLaterThan() (live-search path) can’t drift.

Important: The offer generator enforces the same cutoff at offer creation time — see Offers — Arrival-Time Compliance. Flagged cached rows exist in the database but are not shipped to customers.

Source: backend/app/Models/DynamicFlightCache.php (violatesLatestArrivalTime), backend/app/Services/Flights/FareTimeWindowFilter.php

Same-Day Domestic Filter

A domestic flight leg must depart and land on the same local calendar date. Overnight (next-day-arrival) domestic flights must never reach a customer offer. This is a hardcoded, global, non-configurable business invariant — deliberately unlike the operator-configurable latest_arrival_time setting above.

Dropped at populate, not stored-then-flagged. This is the key contrast with Arrival-Time Compliance Flag:

	Latest Arrival Time	Same-Day Domestic
Configurable?	Yes (flights.search.latest_arrival_time)	No (permanent invariant)
Mechanism	Stored, then visually flagged in admin	Dropped before the row is written to the cache
Why	Operator can re-tune the cutoff, so raw rows must be kept to re-evaluate	Permanent rule — overnight domestic rows have no reason to exist
Scope	All routes	Domestic routes only

Because the bad state is made unrepresentable at the source, every downstream consumer is closed off at once — offer generation, the manual CreateOffer admin page, and any future code reading the cache.

Where it runs. The filter is applied response-side in executeSearchForEntry(), inside the $prioritizedFares filter chain (after the nights-at-destination filter, before pruneToBestStopCountFareResults). Overnight domestic fares are rejected before storeSegmentsFromFare() ever persists them:

// Domestic legs must be same-day (hardcoded invariant): never cache a domestic fare
// whose primary outbound itinerary lands the next calendar day. International fares
// are unaffected.
->filter(fn (FareResult $fare): bool => ! $route->is_domestic
    || FlightRankingPolicy::passesSameDayOutboundFare($fare))

Domestic-only scoping. The ! $route->is_domestic || passesSameDayOutboundFare($fare) guard mirrors the existing domestic-only Pareto-prune skip. International fares are never filtered — long-hauls legitimately cross calendar dates, especially on the return leg.

Why it can’t be a request parameter. Aerticket’s /search exposes no arrival-date, duration, or same-day field. “Same-day” is a property of the primary itinerary chosen after ranking, so it must be a response-side filter alongside the departure-window and nights-at-destination filters.

How passesSameDayOutboundFare() evaluates. It resolves the fare’s primary outbound itinerary via FlightRankingPolicy::primaryItineraryIndexForLeg($fare, 1) (the same itinerary the populator stores and binds), then compares the date portion of the first segment’s departureDateTime to the last segment’s arrivalDateTime. No UTC conversion — Aerticket emits local times — so a red-eye (depart 23:30, land 00:40 next day) correctly fails. Missing segments return true (compliant) so a data gap never silently drops a fare. This matches FareTimeWindowFilter / arrivesNextDay() semantics.

Observability. When the populator drops overnight domestic fares it emits a single log line per (route, date), with no persistent per-date “reason” record — consistent with every other search-time filter, which all discard silently:

DynamicFlightCachePopulator: filtered out overnight domestic fares
  route_id, route_code, departure_date, overnight_dropped (count)

This lets an operator see why a domestic date came back with fewer / no cached flights (the rows simply won’t exist). It is observational only and does not affect the filtering.

Consumption-time backstop. A defensive backstop remains in offer generation: AutoOfferGeneratorService::findDomesticFlights() rejects rows where DynamicFlightCache::arrivesNextDay() is true before binding. This is defense-in-depth (not the primary mechanism) for any overnight domestic row that still exists — e.g. cached before this filter shipped, or pending re-search. Such legacy rows are left to natural re-search to replace; the backstop keeps generation safe in the meantime. See Offers — Domestic Leg Date Derivation.

Source: backend/app/Services/Offers/FlightRankingPolicy.php (passesSameDayOutboundFare), backend/app/Services/Flights/DynamicFlightCachePopulatorService.php (executeSearchForEntry), backend/app/Models/DynamicFlightCache.php (arrivesNextDay)

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)
itinerary_index	smallint	Alternative-itinerary index within a leg; multiple direct alternatives for one route+date each get their own index (default 1)
segment_number	integer	Segment within leg
flight_number	varchar	Flight number without carrier prefix (e.g. “1860”)
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
airline_name	varchar(100)	Airline display name (nullable)
baggage_allowance	varchar	Baggage info
duration_minutes	integer	Flight duration

Unique key: (flight_cache_id, leg_sequence, itinerary_index, segment_number). Pairing leg_sequence with itinerary_index is what lets one cache hold several distinct same-day direct alternatives for a route+date (each its own itinerary_index under leg_sequence=1) without colliding. Consumers must read a single flight by pinning both leg_sequence and itinerary_index — reading by leg_sequence alone flattens every alternative into one fake multi-segment leg. The chosen itinerary is snapshotted immutably on the bound OfferFlight.itinerary_index_outbound (a copy of the cache row’s primary_outbound_itinerary); OfferFlightSignature::boundLegSignaturesForOffer() and the checkout domestic-leg extractor both resolve the bound flight this way.

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

Economy Flight Cache Update (Checkout)

During checkout, a live economy flight search re-prices the offer before the user proceeds. Two services handle this:

EconomyFlightSearchService performs the live Aerticket API search:

1. Extracts route info (departure, destination, dates) from the offer’s cached OfferFlight segments
2. Applies admin-configured search filters from the Settings page via SettingsService::buildCheckoutSearchOptions() (airlines, max stops, fare sources, baggage) with forced ECONOMY cabin class
3. Detects multi-city vs round-trip: multi-city when inbound departure differs from outbound destination (e.g., outbound MAD->PEK but inbound PVG->MAD)
4. Searches domestic flight legs (one-way per leg) alongside international — failures are graceful with cached prices as fallback
5. Selects the best fare under FlightRankingPolicy — baggage, max-stops, layover, return-departure, departure-window and nights filters first, then the best available stop-count tier, total round-trip duration, price, stops, and outbound-arrival tiebreaks. The displayed “selected” card is the live policy winner, not the previously-bound fare when that fare is worse or invalid
6. Applies optional latest_arrival_time post-filter from admin settings (international only)
7. Returns all itinerary suboptions per leg (different departure times at the same price)
8. Batch-loads airports in a single query to avoid N+1
9. Before the upgrade walk, calls EconomyFlightCacheUpdateService::refreshSignatureMatchedRows to update total_price / base_price / tax / searched_at on any cache row in the offer’s (route, departure_date, return_date, CUG) bucket whose per-leg signature matches a live fare. No new rows are created — this only lets cheaper-than-cached live fares become valid upgrade targets when a matching cache row already exists

Source: backend/app/Services/Checkout/EconomyFlightSearchService.php

EconomyFlightCacheUpdateService updates the database with fresh results:

1. Creates new DynamicFlightCache entries for the top 5 fares (sorted by price) for both international and domestic legs
2. Stores segments with itinerary_index for multiple suboptions per leg
3. Resolves airport codes to city codes via toCityCode() for route matching (e.g., PEK -> BJS) since DynamicFlightCacheRoute stores city codes but segments store airport codes
4. Re-links the offer’s OfferFlight records to the selected fare’s new cache entries (international and domestic separately)
5. Updates individual OfferFlight.price for each leg independently (international + each domestic leg with a live result)
6. Recalculates the offer’s flight_base_price as international + all domestic fares (using cached price as fallback when domestic search fails)
7. Safely deletes old cache entries only if they are not linked to any other OfferFlight
8. Uses saveQuietly() consistently to bypass OfferObserver’s active-offer block

All operations run inside a database transaction. Batch airport loading (single whereIn query) prevents N+1 when storing segments across multiple fares.

Source: backend/app/Services/Checkout/EconomyFlightCacheUpdateService.php

Related endpoint: POST /checkout/{offerId}/search-economy-flights — see Checkout API

Business Flight Search (Checkout)

When the user selects a business class upgrade, BusinessFlightSearchService performs a live Aerticket API search with the same patterns as economy:

1. Applies admin-configured search filters via SettingsService::buildCheckoutSearchOptions() with forced BUSINESS cabin class
2. Detects multi-city vs round-trip based on inbound/outbound airport mismatches
3. Searches domestic flight legs (one-way BUSINESS per leg) alongside international
4. If no business fares found for a domestic leg, falls back to the cached economy price (already available from the economy search that runs first)
5. Sums domestic prices into $domesticTotal, which is added to every business fare card’s “+x€” extra price calculation
6. Returns all fare options for user selection (unlike economy which auto-selects the best fare). Applies the FlightRankingPolicy hard filters + comparator and then take(10) — Pareto pruning is intentionally not applied because business responses are narrower than economy and the cheapest+fastest fare tends to dominate everything else, collapsing the selector to one card
7. Computes per-fare leg durations via LegDuration::forLeg using IANA airport timezones (loaded once via loadAirportMapForFares). Replaces the previous segments->sum('duration') which under-reported long-haul wallclock-fallback segments and ignored layovers — the duration shown on each business card now matches the ranking-policy duration used to order them

The searchBusinessFlightsRaw() method is used for post-payment flight rebooking (admin-triggered). It applies the same filters and multi-city detection but skips domestic search (rebooking is for international legs only).

Source: backend/app/Services/Checkout/BusinessFlightSearchService.php

Related endpoint: POST /checkout/{offerId}/business-flights — see Checkout API

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)

Route Auto-Invalidation

Routes that consistently return zero results from Aerticket are automatically deactivated to prevent wasted L2B (Look-to-Book) API calls.

How It Works

1. After each zero-result search, DynamicFlightCacheRoute::checkAndAutoInvalidate() counts consecutive completed entries with total_price = 0.00 (using fare_position = 1 to avoid duplicates)
2. When the count reaches the configured threshold (default: 5), the route is set to is_active = false with an invalidated_at timestamp
3. SearchFlightCacheJob eager-loads the route and skips entries whose route is inactive
4. dispatchSearchJobs() also skips entries with inactive routes before dispatching

Invalidation Settings

Config file: config/dynamic-flight-cache.php -> route_invalidation

Key	Env Var	Default	Description
enabled	DYNAMIC_CACHE_ROUTE_INVALIDATION_ENABLED	true	Enable/disable auto-invalidation
consecutive_empty_threshold	DYNAMIC_CACHE_EMPTY_THRESHOLD	5	Consecutive zero-result searches before deactivation

Route States

State	is_active	invalidated_at	Description
Active	true	null	Normal operation, receives search jobs
Auto-Invalidated	false	timestamp	Deactivated by the system after threshold reached
Manually Inactive	false	null	Deactivated by an admin via the panel

Use wasAutoInvalidated() to distinguish auto-invalidated routes from manually deactivated ones.

Reactivation

Reactivate a route from the admin panel (view page header action or table row action) or programmatically:

$route->reactivate(); // Sets is_active = true, invalidated_at = null

Reactivation clears the invalidated_at timestamp. If the route keeps returning empty results, it will be auto-invalidated again after reaching the threshold.

Source: backend/app/Models/DynamicFlightCacheRoute.php

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

// Return arrival-home time: last segment of leg_sequence 2 of the bound
// return itinerary (when the traveler lands back at origin)
$cache->getReturnArrivalTime();      // Carbon or null (null for one-way)

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

Operator Workflow (ProductByMarket View)

The primary operator-facing flow lives inline on the ProductByMarket view page as a “Dynamic Flight Cache” section. It replaces the previous “Cache Matching Flights” header action and is now the recommended way to drive ad-hoc searches for a specific product.

Visibility: Section is rendered only when the product has at least one active flight config and both search_start_date and search_end_date are set.

Source: backend/app/Livewire/Filament/ProductsByMarket/DynamicFlightCacheManager.php, backend/app/Filament/Resources/ProductsByMarket/Schemas/ProductByMarketInfolist.php (Section::make('Dynamic Flight Cache'))

Form

Two multi-select fields drive the cascade:

Field	Source
Departure airports	All airports of the product’s active flight configs. Pre-selected on mount; deselect to narrow.
Departure dates	Every date in [search_start_date, search_end_date] that is not in the product’s excluded_dates.

Clicking Search Flights dispatches one CascadeFlightSearchForDateJob per (active config × selected date) to the flight-searches queue. Dates outside the product’s search window or in the excluded list are skipped with a warning notification.

Cascade Job

CascadeFlightSearchForDateJob walks one config’s legs in day_offset order for one departure date, executing the matching DynamicFlightCache row for each leg sequentially and folding each leg’s actual arrival back into a running cumulativeDelay. Downstream legs are searched at productDeparture + leg.day_offset + cumulativeDelay, so an overnight international arrival pushes every domestic leg one day forward and matches the cache rows that the auto-offer generator’s findDomesticFlights() will later look for (see Offers — Domestic Leg Date Derivation).

Source: backend/app/Jobs/CascadeFlightSearchForDateJob.php, backend/app/Services/Flights/LegDelayCascadeResolver.php

Configuration:

• Queue: flight-searches
• Tries: 1 (no retry — partial cascades are visible in the activity log)
• Timeout: 600 seconds (sized for ~3-4 sequential Aerticket searches at ~30 s each)

Cascade Halt Conditions

The job stops walking the leg list (and leaves downstream legs as PENDING) when any of these conditions hit. The stop reason is logged to both Log::warning() and the flight-searches activity log with a cascade_partial event.

Reason	Cause
route_not_found	No active DynamicFlightCacheRoute matches the leg under its config + trip duration
target_date_outside_search_window	Cumulative delay pushed the leg past search_end_date or onto an excluded date
cache_entry_missing	No DynamicFlightCache row exists for the resolved (route, target_date) — operator needs to run “Prepare Flight Searches” first
search_failed	executeSearchForEntry() threw
search_not_completed	Cache row ended in a status other than COMPLETED
no_results	Aerticket returned an empty fare set — the row is COMPLETED but total_price=0 and has no segments, so getOutboundArrivalTime() is null and the cascade can’t compute the next leg’s date

The no_results halt is load-bearing: a connection that Aerticket cannot price stops the cascade outright, so downstream domestics are not searched and stay PENDING. This is intentional — burning API calls on a leg whose international predecessor isn’t actually flyable wastes L2B budget.

Inline Cache Table

Below the form, the section renders the same table component as the standalone Dynamic Flight Caches list page (DynamicFlightCachesTable::configure()), so all columns, filters, and the arrival-compliance + baggage badges documented under Admin Interface apply identically here.

Three behaviours are specific to the inline view:

• Polls every 5 s so cascade progress streams in without manual refresh.
• Shows the cheapest in-window fare per (route, date). When the leg has a configured departure window (departure_time_from / to on ProductByMarketFlightLeg), resolveMatchingCacheIds() runs one query per target — whereDate('departure_date', ...) plus whereHas('segments', ...) on the first outbound segment with whereTime for either bound — and picks the row with the lowest total_price. When no window is configured, the cheapest row of any fare_position wins. The previous strict fare_position = 1 filter hid valid offers from operators when the cheapest globally was off-window (e.g. LIM→CUZ at 04:50 €238 hiding the in-window 10:10 €255 at fare_position=23); the picker now mirrors what AutoOfferGeneratorService::findDomesticFlights() actually selects, so the table truthfully previews what an offer for this PBM will use.
• Per-route target dates shift with the cascade. The component reuses LegDelayCascadeResolver to walk each (config, productDeparture) pair the same way the job does: the international leg appears at the operator-selected date, but each downstream domestic leg appears at the date the cascade will search once the international row completes. While the international leg is still PENDING, downstream legs use a zero further-delay assumption (planned best-case) and snap to the real shifted date once the international result lands. Rows are ordered by leg trigger order via a CASE route_id WHEN ... THEN day_offset expression so the international leg surfaces first, followed by domestics in day_offset order.
• Cache invalidation probes every fare_position. resolveCascadePlanVersion() MAX(updated_at) over all cache rows for the active route ids — not just fare_position = 1 — so an update to a higher-position row (which the picker can now select) invalidates the cached plan immediately instead of waiting for the next 5-second poll.

Source: backend/app/Livewire/Filament/ProductsByMarket/DynamicFlightCacheManager.php (scopeTableQuery, resolveCascadePlan, resolveMatchingCacheIds)

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 (hidden when filtering by product)
• Product route tabs: When navigating from ProductByMarket, shows an aggregate “Product Routes” tab plus individual route tabs (replaces the top-10 tabs)
• Searchable route column with database-aware query (ILIKE for PostgreSQL, LIKE for SQLite)
• 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
• Arrival Compliance badge — computed against flights.search.latest_arrival_time. OK (green check) when within [06:00, cutoff], Late (> HH:MM) (red warning) when the outbound-leg final arrival violates the window, — (gray) when no cutoff is configured. Computed via DynamicFlightCache::violatesLatestArrivalTime().
• Baggage column — green Included / allowance string when the fare covers a checked bag, gray Not included otherwise.

URL Parameters:

• ?product_routes=12,13,14 - Filter to product-specific routes (comma-separated IDs, shows individual route tabs)

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
• Three-state status column: Active (green), Auto-Invalidated (warning), Inactive (red)
• Route Health infolist section showing consecutive empty search count and invalidation timestamp
• View page with infolist, cache statistics, and caches relation manager
• Header actions: Reactivate (for inactive routes) and Deactivate (for active routes)
• Table row action: Reactivate (for inactive routes)
• Default filter shows active routes only
• invalidated_at column available as toggleable column

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:

Always uses ?product_routes=12,13 with comma-separated route IDs, so the list page shows product-specific route tabs instead of the global top-10.

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

Behavior:

• Eager-loads the route relationship and skips entries whose route is inactive (deactivated or auto-invalidated)
• On zero-result searches, triggers route auto-invalidation check (see Route Auto-Invalidation)

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
• Routes auto-invalidated after 5 consecutive empty searches (configurable) to save L2B API costs

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