guest_history

Natural key. No FK: registration rows are archived to backup.registration and then purged, but this row must outlive them.

Snapshot at check-out time; rooms can be renumbered or deleted later.

REVENUE_CALC_TYPE = 1 basis (Gross Room): frozen SUM of the posted room-charge transaction amounts, which equal sp_rate_schedule_breakdown.room. Until V20260918.10.4 this read registration_rates.room, a column nothing ever writes, so it fell back to room_rate and reported the same number as rate_revenue. Visits written before that keep the old value: guest_history is write-once, and sp_rebuild_guest_history is the only way to recompute one.

REVENUE_CALC_TYPE = 0 basis. Frozen SUM(registration_rates.room_rate) = the rack/contract rate, GROSS. All three bases (this, room_revenue, revenue_room_net) are stored so flipping REVENUE_CALC_TYPE does not require a rebuild.

Frozen SUM(transactions.acc_amount) where post_type = ‘P’ AND active, across public.transactions and backup.transactions.

{category, name, amount} per department_group category (room/fb/other). guest.revenues rolls this up to {category, amount}.

true when this row was built from backup.* (historical backfill) rather than from the live registration at check-out.

REVENUE_CALC_TYPE = 2 basis for this stay. Room only and NET of every itemizer on the room-charge department (= BASE_PRICE), summed over the stay nights. Preferred source is the BASE_PRICE frozen on the posted room-charge transactions (public.transactions UNION backup.transactions), because the department itemizer set is not versioned and recomputing an old stay with today’s rates would invent numbers. Falls back to fn_itemizer_base_amount for nights with no usable posting. Because the frozen figure wins, this column can disagree with room_revenue when the rate was edited after the night was posted: room_revenue follows registration_rates as it stands today, while this follows what was actually charged. That is deliberate – a past stay should report what the guest really paid. NULL = not backfilled; readers use COALESCE(revenue_room_net, room_revenue).