Offers History

Two append-only tables capture the full history of an offer’s flight selection and pricing over its lifetime:

• offer_flight_bindings — every flight (cache row) the offer has been bound to.
• offer_price_snapshots — every price computation the offer has gone through.

Both are written by OfferFlightUpgradeService (at checkout / admin recalc time) and seeded by AutoOfferGeneratorService (at offer creation). The existing offer_flights table remains the read-side projection of “current binding” — consumers reading the live fare keep working unchanged.

offer_flight_bindings

Append-only history of the cache row each leg points at, one row per (offer, leg, bound_at). Exactly one row per (offer_id, leg_index) carries is_current = true.

Column	Type	Notes
id	bigint PK
offer_id	FK → offers.id	cascadeOnDelete
leg_index	smallint	0 = international, 1+ = domestic
dynamic_flight_cache_id	FK → dynamic_flight_caches.id	restrictOnDelete
price	decimal(10,2)	Cache row’s total_price at bind time
is_current	boolean	Live binding flag
source	varchar(32)	generator / live_search_upgrade / arkana_search_upgrade / manual
bound_at	timestamp	When this binding became current
replaced_at	timestamp NULL	Set when is_current flips to false
created_at / updated_at	timestamps

Indexes:

• ofb_offer_leg_bound_idx on (offer_id, leg_index, bound_at).
• ofb_current_unique — Postgres partial unique index: UNIQUE (offer_id, leg_index) WHERE is_current = true. Forces upgrade flows to flip the prior current row before inserting a new one.

Backfill at migration time: every existing offer_flights row produced a source = 'generator' current binding with bound_at = offer.created_at.

Source: backend/app/Models/OfferFlightBinding.php, backend/database/migrations/2026_05_05_091106_create_offer_flight_bindings_table.php

offer_price_snapshots

Append-only price history. One row per audited recompute.

Column	Type	Notes
id	bigint PK
offer_id	FK → offers.id	cascadeOnDelete
flight_base_price	decimal(10,2)	Sum of offer_flights.price at recompute time
land_base_price	decimal(10,2)	Mirrors offers.land_base_price
base_price	decimal(10,2)	flight + land
policy_margin_pct	decimal(5,2)	Sticker margin from the product
effective_margin_pct	decimal(6,3)	Margin actually realised after the floor
extra_margin_captured	decimal(10,2)	final_price - would_be_final_at_policy_margin, default 0
final_price	decimal(10,2)	Customer-facing total
marketing_price_per_pax	decimal(10,2)	final_price / pax_count, rounded
reason	varchar(32)	generated / live_search_upgrade / live_search_refresh / arkana_search_upgrade / arkana_search_refresh / manual
triggered_by_binding_id	FK → offer_flight_bindings.id NULL	Set on upgrade; null on plain refresh
recorded_at	timestamp	When the snapshot was taken
created_at / updated_at	timestamps

Indexes: ops_offer_recorded_idx on (offer_id, recorded_at).

Backfill at migration time: one snapshot per existing offer using the values currently on the offers row, with reason = 'generated' and effective_margin_pct = policy_margin_pct (no upgrades had happened yet).

The two margin columns capture the floor rule. When a cheaper-flight upgrade fires on a locked offer, final_price is held and effective_margin_pct rises above policy_margin_pct. The delta is pre-computed into extra_margin_captured so dashboards can sum it without recomputing.

Source: backend/app/Models/OfferPriceSnapshot.php, backend/database/migrations/2026_05_05_091107_create_offer_price_snapshots_table.php

offers.final_price_locked_at

Nullable timestamp added to the offers table. Set when the offer is activated (becomes user-visible). While NULL, the offer is still draft and the flight-upgrade pipeline treats it as unlocked — the floor rule is skipped. Once set, final_price is floored.

Backfilled from activated_at for already-activated offers.

Source: backend/database/migrations/2026_05_05_091105_add_final_price_locked_at_to_offers.php

Dropped: offers_unique_constraint

The composite unique key on (product_by_market_id, departure_airport_id, departure_date, base_price, cug_type, room_type) was dropped (migration 2026_05_05_103817_drop_base_price_from_offers_unique_constraint.php).

base_price is now a moving column on activated offers — flight-cost upgrades shift it (cheaper flight grows margin and leaves final_price held but lowers base_price; pricier flight raises both). Including a moving column in the unique key blocked the “Recalculate offer” admin action and the live-search auto-upgrade whenever two offers happened to converge on the same base_price.

A tighter (product, airport, date, cug, room) constraint is left for a follow-up — existing data has multiple offers per slot at different base_price values, and a cleanup pass needs to decide which to keep before any tighter unique index can be enforced.

Read patterns

• Live binding for a leg: OfferFlightBinding::where('offer_id', $id)->where('leg_index', $i)->where('is_current', true).
• Latest price: order offer_price_snapshots by recorded_at DESC, id DESC.
• Captured-margin total for an offer: SUM(extra_margin_captured) over its snapshots.
• Read-side flight projection: offer_flights is still the table to join for “what flight is this offer on right now”. The bindings table is for history; the projection is for live reads.

Related

• Offer Flight Upgrade Service — writers of these tables.
• Offers — pricing formula, lifecycle, SKU format.