Referrals and rewards

Referral rewards are a percentage of an eligible service amount. They are recorded in USD cents and become a discount on a later booking. They are not paid out in cash.

Worked example

Input	Value
Eligible order subtotal	$200.00, stored as 20000 cents
Referral reward rate	5 percent, stored as 500 basis points
Reward credited	$10.00, stored as 1000 cents

Rounding rule

All inputs are non-negative integers. The reward is:

reward_cents = (eligible_cents * percentage_basis_points + 5000) / 10000

The division is integer division that truncates toward zero. Adding 5000 before dividing by 10000 rounds half up. 10000 basis points is 100 percent. Rates outside 0 through 10000 are rejected by the database.

Eligible cents	Basis points	Integer result	USD
20000	500	1000	$10.00
1010	500	51	$0.51
1001	500	50	$0.50

20000 × 500 is 10,000,000. Adding 5000 and dividing by 10000 yields 1000. 1010 × 500 is 505,000, which is $5.05 exactly at 5 percent of $10.10, and half-up produces 51 cents.

Eligible amount

The eligible amount is the service subtotal after discounts: orders.subtotal_cents - orders.discount_cents.

• Discounts already on the qualifying order reduce the base.
• Taxes are excluded. This schema does not store tax. A future tax column stays out of subtotal_cents.
• Travel charges are excluded the same way.
• A refunded service amount reduces the base. The reversal uses this same formula on the refunded eligible cents.

The reward row stores eligible_subtotal_cents and basis_points_applied so a later policy change does not rewrite history.

Lifecycle

• PENDING waits for the booking to be confirmed. It is not spendable, and it has no ledger credit.
• AVAILABLE means a positive CREDIT ledger row exists.
• REDEEMED means the credited amount has been fully applied to later orders.
• REVERSED means reversal entries have removed the remaining credit.
• EXPIRED means an EXPIRATION entry removed an unused remainder after expires_at. A null reward_validity_days on the policy means the reward does not expire.

Attribution rules

• The referrer and the referred customer must be different users. The database rejects a self-referral.
• referred_user_id is unique. A second code does not replace the first referrer.
• The code must be active.
• The qualifying order is the referred customer's paid order. The referral row stores that order before the reward row is inserted.
• One reward exists for that referral and order. qualifying_order_id on the reward is unique, so the order cannot mint a second reward.

Ledger signs

Entry	Sign	When
CREDIT	Positive	Booking confirmed and the qualifying order is still paid.
REDEMPTION	Negative	Checkout reserves a discount.
REVERSAL	Opposite of the entry it corrects	Refund or a released reservation.
EXPIRATION	Negative	Unused available amount passes expires_at.

Rows are never updated or deleted. The trigger reward_ledger_append_only enforces that. Spendable balance is SUM(amount_cents) for the user.

Redemption

A customer may apply the available balance to a future booking's service subtotal.

• The discount cannot exceed the service subtotal.
• The discount cannot exceed the ledger balance.
• Taxes and travel, if added later, are not discountable.
• The oldest available credits are consumed first.

The checkout transaction locks the referrer with SELECT ... FROM users WHERE id = $id FOR UPDATE, computes the sum, inserts reward_redemptions as RESERVED, inserts the negative REDEMPTION ledger row, and sets orders.discount_cents. Two concurrent checkouts cannot spend the same cents. One waits, recomputes the sum, and is refused if the balance is gone.

If the payment succeeds, the redemption becomes APPLIED. That status change does not post another debit. If the payment fails and the order is cancelled, a positive REVERSAL is posted and the redemption becomes RELEASED.

Refunds

1. Lock the referrer.
2. If the refunded order had redeemed a reward, post a positive reversal of that redemption first so the credit returns to the balance.
3. If the refunded order was a qualifying order, recalculate the reward on the remaining eligible cents and post a negative reversal for the difference, up to the amount that is no longer supported.
4. Leave every previous ledger row in place.

A full refund of an unredeemed $10.00 reward posts a REVERSAL of -1000 cents. The original +1000 credit remains. The balance is zero.

If the referrer already spent the reward on a later order, that later order keeps its discount until that order itself is refunded. Step 2 runs when that later order is refunded. The qualifying refund then reverses the restored credit.