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
flight_leg_prices	jsonb NULL	Per-leg price breakdown at this recompute: [{leg_index, cache_id, flight_type, price}]. Null for pre-existing snapshots and generator rows that didn’t capture per-leg detail
land_base_price	decimal(10,2)	Mirrors offers.land_base_price
insurance_base_price	decimal(10,2) NULL	Whole-party cost of the market’s included base insurance, baked into base_price
base_price	decimal(10,2)	flight + land + insurance
policy_margin_pct	decimal(5,2)	Sticker margin from the product
effective_margin_pct	decimal(6,3)	Margin actually realised. Always equals policy_margin_pct for new snapshots (no floor)
extra_margin_captured	decimal(10,2)	Always 0 for new snapshots — no floor is applied. Column retained for historical rows and dashboard sums
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 / recalculated
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).

No price floor. Activated offers track flight cost in both directions: a cheaper flight lowers final_price back to the sticker-margin price (the same basis the generator uses) instead of holding the old price and banking the delta, and a pricier flight raises it. For every new snapshot, effective_margin_pct therefore always equals policy_margin_pct and extra_margin_captured is always 0. (A one-way ratchet that held the price on cheaper flights and captured the delta was removed so offers reflect real current cost; the two columns are kept for historical rows and dashboard compatibility.)

Source: backend/app/Services/Offers/OfferFlightUpgradeService.php

The offers:recalculate-prices command also writes here: each applied land-price recalculation creates a snapshot with reason = 'recalculated' and stamps offers.recalculated_at. The original generated snapshot is retained, so the change is auditable and reversible.

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

offers.final_price_locked_at

Nullable timestamp added to the offers table. Set when the offer is activated (becomes user-visible), by OfferObserver::updating(). It no longer floors the price — it now gates whether the reprice pipeline runs at all: the flight-upgrade pipeline only acts on offers with final_price_locked_at IS NOT NULL. While NULL, the offer is still a draft owned by the generator and the service is a no-op.

Backfilled from activated_at for already-activated offers.

Source: backend/app/Observers/OfferObserver.php, backend/app/Services/Offers/OfferFlightUpgradeService.php, backend/database/migrations/2026_05_05_091105_add_final_price_locked_at_to_offers.php

offers.margin_basis

Varchar(10) added to the offers table (#2214), storing how the offer’s margin percentage is interpreted (App\Enums\MarginBasis):

• cost — legacy markup on cost.
• sale — margin on the selling price.

The column drives every price computation: Offer::priceFromCost($basePrice, $margin, $offer->marginBasis()) picks the formula, and Offer::roundPerPaxForBasis() the rounding — so both the generator and the flight-upgrade reprice stay on the offer’s own basis.

The migration backfills every existing offer to cost (no already-created offer changes price at deploy) but sets the column default to sale so offers created from then on use margin-on-sale. An offers:migrate-margin-basis command converts existing offers between bases.

Source: backend/app/Models/Offer.php, backend/app/Console/Commands/MigrateOfferMarginBasisCommand.php, backend/database/migrations/2026_07_14_090000_add_margin_basis_to_offers_table.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 reprices shift it in both directions (a cheaper flight lowers both base_price and final_price; a pricier flight raises both, since there is no floor). 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.
• 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.