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

Route Preparation - Creates route definitions from product flight configs
Cache Entry Creation - Creates pending entries for each searchable date
Search Execution - Calls Aerticket API and stores pricing results
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 and an optional `departure_time_from` / `departure_time_to` window (see Route departure windows)
`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	Maximum learned calendar-day delta between the international outbound’s first-segment departure and last-segment arrival. Kept in sync as `max(observed_overnight_days)` for older consumers.
`observed_overnight_days`	json	Set of all learned outbound deltas. Multi-city search issues one serial Aerticket call per observed value so fares with different overnight shapes can all be considered.

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 (reference total — see note below)
`tax`	numeric	Tax amount (reference total)
`total_price`	numeric	Total price for the persisted fare (reference total)
`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
`timed_out`	Aerticket search timed out; `SearchFlightCacheJob` rethrows so Laravel can retry	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:

Native baggage — keep fares whose selected itineraries include checked baggage on every segment of every leg.
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.observed_overnight_days holds the learned set of calendar-day deltas between the outbound’s first-segment departure and last-segment arrival. The legacy scalar outbound_overnight_days stays in sync as the maximum observed value. The populator uses them as follows:

Search build — buildMultiCitySearchRequestFromRoute() accepts one return-leg shift and adds it to the static day_offset of the last segment (the international return leg), so the search anchors on the requested outbound-arrival shape. executeMultiShiftSearchForEntry() calls it once per value in observed_overnight_days, serially, and merges the fare lists. Cold-cache routes default to [0] (same behaviour as before).
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, adds it to observed_overnight_days, and keeps outbound_overnight_days at max(observed) for compatibility.

The first search seeds the set; every subsequent search for the same route tries every known shape so same-day, +1, +2, etc. fares can all surface and the per-fare nights-at-destination filter keeps only the ones delivering the configured nights. 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, ?int $itineraryIndex = null): bool returns true when the outbound itinerary’s final-segment arrival H:i (local) falls outside the daytime window [06:00, cutoff]. It checks the given itinerary, or the row’s primary when none is passed; arrivesNextDay(?int $itineraryIndex = null) follows the same convention:

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, immediately before the ranking sort). 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::domesticItineraryFor() only binds an in-window itinerary for which DynamicFlightCache::arrivesNextDay($itineraryIndex) is false, so findDomesticFlights() skips a row with no such itinerary. 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 (for an international leg a copy of the cache row’s primary_outbound_itinerary; for a domestic leg the in-window itinerary chosen by AutoOfferGeneratorService::domesticItineraryFor(), which is not necessarily the primary — see Offers — Departure Window Filtering); OfferFlightSignature::boundLegSignaturesForOffer() and the checkout domestic-leg extractor both resolve the bound flight this way. Rows that pre-date #1806 default both snapshot columns to 1; php artisan flight-cache:recompute-primary (--route=, --dry-run, --chunk=200) backfills them with the real min-duration argmin per leg and is idempotent — it deliberately skips domestic offer_flights rows, since those bind the in-window itinerary rather than the row’s primary.

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

Route departure windows

Each route segment can carry a departure window (local HH:MM). On every route search the populator keeps a fare only when one of its outbound itineraries departs inside the first segment’s window (FlightRankingPolicy::passesDepartureWindowFare); one-way searches also send it to Aerticket as departureTimeRange, but only when both bounds are set. The window is not part of the route hash, so products sharing a route share one stored window, and findOrCreateCacheRoute() settles it every time routes are prepared (departure-rule “Queue searches”, the cache manager, flight-cache:ensure-pending-entries):

Domestic one-way routes — the stored window is overwritten with the union of the current windows of every domestic leg flying that route (same origin, destination, day_offset and trip duration) on an active flight config, plus the leg being prepared; any open bound keeps that side open (withDomesticLegsWindow()). Adding, narrowing, widening or removing a leg window therefore reaches the next search.
International and multi-city routes — widen-only merge with the incoming window (mergeRouteDepartureWindow()): the union of both, with a NULL bound on either side staying NULL.

The leg’s own window still decides which cached row and which itinerary an offer binds — see Offers — Departure Window Filtering.

Creating Cache Entries

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

// Same keys aggregated across a product's active configs, plus configs_processed
$result = $service->createCacheEntriesForProduct($product);

// Combined: prepare routes AND create entries
// (reports routes_created, entries_created, entries_existing — NOT entries_blacked_out)
$result = $service->prepareForConfig($config);

Supplier Tour Blackout Filter

The window walk skips dates closed by a supplier-tour blackout, the same question the departure-rule path (DepartureRuleExpander) already asked. Walking the window blackout-blind spent provider search quota on dates that can never be sold and handed the offer generator flights for closed departures. The ranges are resolved once per config — they belong to the product’s tour, not to the date — and hits() tests every day of the trip (departure through departure + the product’s trip duration − 1, never less than departure+1), so a departure whose stay runs into a closure is skipped, matching the criterion the departure-rule path and the generator use.

Skips are reported in entries_blacked_out. Mind the units: entries_blacked_out counts dates (one per skipped day) while entries_created counts route-dates, so on a multi-route config the two do not sum back to the window length — multiply by the route count to compare them.

flight-cache:ensure-pending-entries mirrors this filter in its expected-entry count (walking the window and dropping config-excluded and blacked-out dates), otherwise its dry run reports work the writer will never do and the command can never reach the zero-work state it promises.

Source: backend/app/Services/Flights/DynamicFlightCachePopulatorService.php (createCacheEntriesForConfig()), backend/app/Console/Commands/EnsurePendingCacheEntriesCommand.php, backend/app/Services/Offers/SupplierTourBlackoutResolver.php

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:

Extracts route info (departure, destination, dates) from the offer’s cached OfferFlight segments
Applies admin-configured search filters from the Settings page via SettingsService::buildCheckoutSearchOptions() (airlines, max stops, fare sources, baggage). The cabin list is per product, not global: ECONOMY unless products_by_market.economy_search_cabin_mode opts into ECONOMY + ECONOMY_PREMIUM, which also makes the whole search read-only — see Economy with premium economy. Domestic legs keep ECONOMY regardless. The cache populator is unaffected and still uses the global flights.search.cabin_class_list setting
Detects multi-city vs round-trip: multi-city when inbound departure differs from outbound destination (e.g., outbound MAD->PEK but inbound PVG->MAD)
Searches domestic flight legs (one-way per leg) alongside international – failures are graceful with cached prices as fallback
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 and the product + departure airport’s flight ranking. The displayed “selected” card is the live policy winner, not the previously-bound fare when that fare is worse or invalid
Applies optional latest_arrival_time post-filter from admin settings (international only)
Returns all itinerary suboptions per leg (different departure times at the same price)
Batch-loads airports in a single query to avoid N+1
Before the upgrade walk, calls EconomyFlightCacheUpdateService::refreshSignatureMatchedRows to update total_price / base_price / tax / currency / searched_at on any completed cache row in the offer’s (route, departure_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:

Refreshes total_price / base_price / tax / currency / searched_at / request_identifier on the cache row the offer’s international OfferFlight is already linked to, using the live fare that matches the offer’s locked signature. No match in the live response means the row is left alone
Does the same per domestic leg on that leg’s own linked row; a leg that throws is logged and skipped rather than failing the whole update
Writes each leg’s live price onto its OfferFlight.price (international + every domestic leg with a live result)
Recomputes the offer price. An activated offer goes through OfferFlightUpgradeService::recordPriceRefresh(), so the change is audited in offer_price_snapshots under the caller’s refresh reason (live_search_refresh, arkana_search_refresh, …). There is deliberately no price floor: an activated offer tracks flight cost in both directions, so a cheaper flight lowers final_price back to the sticker-margin price rather than banking the delta as captured margin. A draft recomputes in place: flight_base_price = international + all domestic fares (cached price as fallback when a domestic search fails), then base_price = flight_base_price + land_base_price + insurance_base_price, which preserves the bundled included-insurance component (#2189, mirrored in OfferFlightUpgradeService)
Uses saveQuietly() / forceFill() throughout to bypass OfferObserver’s active-offer block

It creates, re-pivots and deletes nothing — no new cache rows, no OfferFlight re-linking, no segment rewrites. Those mutations were the root of the silent baggage-loss bug (#1707): the live /search does not always surface the populator’s bag upsell as a structured allowance, so a recreated row carried baggage_included = true with empty segment allowance text and the admin rendered “0 pieces” for what was originally “1 piece”. The one creation path left is findOrCreateCacheRowForFare(), called by the live-search binding walk (not by updateCacheAndOfferPrice()), and it deletes its own row again when no segment could be stored.

updateCacheAndOfferPrice() wraps its work in a single transaction. refreshSignatureMatchedRows() does not — it saves row by row.

Every write above is skipped entirely — updateCacheAndOfferPrice() and refreshSignatureMatchedRows() return early and log Skipping cache/offer writes — for a product whose economy search cabin mode is with_premium_economy, because its response carries fares no other product sharing that route_hash should inherit. Those two guard themselves, so admin actions and offers:verify-flights hit the guard too. findOrCreateCacheRowForFare() does not carry that guard: it is safe only because its single caller, the live-search binding walk, already runs behind the same read-only gate in EconomyFlightSearchService. Anything calling it from somewhere new has to check the cabin mode itself. Skipping the writes does not leave the client on a stale price: such a checkout session sets reprices_from_live_search and re-quotes its own base from the live total, the way a 3+ pax session does — see POST /checkout/{offerId}/search-economy-flights.

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:

Applies admin-configured search filters via SettingsService::buildCheckoutSearchOptions() with the product’s business cabin list — BUSINESS + ECONOMY_PREMIUM by default, BUSINESS alone when products_by_market.business_search_cabin_mode says so; see Business on mixed-cabin routes
Detects multi-city vs round-trip based on inbound/outbound airport mismatches
Searches domestic flight legs (one-way BUSINESS per leg) alongside international
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)
Sums domestic prices into $domesticTotal, which is added to every business fare card’s “+x€” extra price calculation
Returns all fare options for user selection (unlike economy which auto-selects the best fare). Applies the FlightRankingPolicy hard filters and the product + departure airport’s flight ranking, dedupes, then caps at take(50). Since PR #1955 the display-side exclusions (max-stops cap, stop-count Pareto prune, dominance curation) are lifted for all products — the selector renders the full live result set, mirroring EconomyFlightSearchService::rankDisplayFares; the two are meant to be reverted in tandem
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

After each zero-result search, DynamicFlightCacheRoute::checkAndAutoInvalidate() counts consecutive completed entries with total_price = 0.00 (using fare_position = 1 to avoid duplicates)
When the count reaches the configured threshold (default: 5), the route is set to is_active = false with an invalidated_at timestamp
SearchFlightCacheJob eager-loads the route and skips entries whose route is inactive
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:

Manual Refresh - Admin triggers re-search
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 a leg’s segments — the outbound ones from leg 1, the return ones from leg 2:

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

// Outbound-leg duration, pinned to itinerary_index 1 (NOT the row's primary
// itinerary, unlike the three helpers above). getReturnDurationMinutes() is
// the leg-2 companion.
$cache->getOutboundDurationMinutes(); // int or null

// Formatted duration — NOT the formatted form of the line above: it uses
// getBestOutboundDurationMinutes(), the shortest across all cached
// itineraries (#1801), so the two can disagree on a multi-itinerary row.
$cache->getFormattedOutboundDuration(); // "14h 30m" or null

Eager-load segments and these are free. The time and duration helpers branch on relationLoaded('segments') and read the loaded collection when it is there — the three primary-itinerary helpers above via the private firstSegment(), and getLegDurationMinutes(), getPrimaryReturnItineraryIndex(), computePrimaryItineraryIndex() and getMinDurationAcrossItineraries() on their own. Without it each call queries, and the cost is not one query but several: on a row whose primary_return_itinerary is null, getReturnArrivalTime() runs a leg-2 exists() probe, an itinerary pluck, then getLegDurationMinutes() once per cached itinerary index to pick the shortest, then the segment lookup itself — four queries at the floor, growing with the number of itineraries, before the airport eager-loads each duration lookup adds.

This matters in bulk. Resolving travel dates over a listed or exported page of offers used to cost two queries per row through this path — an outbound time and a return time — even with the relation already in memory, because these helpers re-queried unconditionally. A 15-row offers export issued thirty-one segment queries before and at most a handful now; its regression test caps the count at five.

Two families still query regardless. The ...ForItinerary variants (getOutboundArrivalTimeForItinerary, getReturnDepartureTimeForItinerary, getReturnArrivalTimeForItinerary) resolve an arbitrary itinerary index rather than the row’s bound one, and the collection accessors (getDepartureSegments, getReturnSegments, getSegmentsForLeg, getSegmentsByLeg, getSegmentsByLegAndItinerary) always go to the database. A loaded relation does not make either family free.

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
`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
`window_mismatch`	A domestic leg with a configured departure window found no COMPLETED fare whose first segment lands inside that window on the cascade’s target date — the triggering operator gets a bell notification so they can widen the window or accept the gap

Two conditions that once halted the cascade no longer do. A missing (route, target_date) row is not an error: ensurePendingEntry() materialises a PENDING row and searches it on the spot. And the search-window check runs once, against the international departure date itself (outside the window or in excluded_dates ⇒ the whole job skips): downstream legs intentionally extend past the window, because a per-leg check used to abort multi-leg itineraries the moment a domestic target date crossed the edge.

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 — naive local wall-clock times stored with no timezone conversion, see Offers — Departure Window Filtering), 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

Create Pending Cache - Materialise PENDING rows for an existing route and one or more departure dates on this list page (dates that already have a row are skipped):

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

The old Prepare Flight Searches product-page action was removed in #1695: routes and entries for a whole product are now prepared by the inline section on the product view page (Operator Workflow) or backfilled with php artisan flight-cache:ensure-pending-entries.

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, 3 tries. The timeout is derived from the multi-shift search budget rather than picked, and is coupled to SEARCHING_STALE_AFTER_MINUTES and the connection’s retry_after — see Why the timeout is 150s for the derivation and the constraints before changing any of them.

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.

Stuck SEARCHING Rows

A row is set to SEARCHING when its job is dispatched and moved on by the job itself. If the worker dies without raising an exception — container restart, OOM, network drop past the cabinet’s own retries — failed() never fires and the row sits at SEARCHING forever.

Two recovery paths handle this, both gated on the same window:

Scheduled sweep — flights:mark-stale-searching-as-failed runs every five minutes and flips stale rows to FAILED so the operator sees them in the UI and can retry. Supports --dry-run.
Operator force-reset — the manual Re-cache path in DynamicFlightCachePopulatorService deletes sibling rows and repoints offer_flights. Racing a live worker here corrupts checkout state, so DynamicFlightCache::isStaleSearching() gates the action.

The window is DynamicFlightCache::SEARCHING_STALE_AFTER_MINUTES (10 minutes). It must stay above SearchFlightCacheJob’s worst case wall clock — timeout × tries + sum(backoff) = 500s ≈ 8.3 min — or both paths can act on a worker that is still legitimately retrying. Raising the job timeout without raising this constant reintroduces that race; see Why the timeout is 150s.

Source: backend/app/Console/Commands/MarkStaleSearchingFlightCachesAsFailed.php, backend/app/Models/DynamicFlightCache.php

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

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