Promotions, Loyalty, Vouchers & Change Money

Read Conventions first.

id, business_id, name, description
promotion_type NOT NULL - CHECK IN (‘item’,‘category’,‘brand’,‘supplier’,‘happy_hour’,‘invoice_tier’,‘membership_tier’,‘birthday’,‘payment_method’,‘quantity_break’,‘coupon’)
is_active; start_date/end_date NULL
priority DEFAULT 100; is_exclusive DEFAULT false
discount_method CHECK IN (‘percentage’,‘fixed’) NULL; discount_value NULL; max_discount_amount NULL
qty_break_buy_quantity, qty_break_free_quantity NULL; qty_break_discount_percent NULL
code NULL - coupon code - UNIQUE (business_id, code)
max_uses_total, max_uses_per_customer NULL; times_used DEFAULT 0
happy_hour_start_time, happy_hour_end_time NULL
membership_condition CHECK IN (‘any_member’,‘non_member’,‘specific_tier’) NULL
birthday_basis CHECK IN (‘birthdate’,‘loyalty_anniversary’) NULL
min_matched_spend_amount, min_matched_quantity NULL
created_by, created_at, updated_at; index (business_id, is_active, promotion_type)

All composite-PK join tables, promotion_id CASCADE:

promotion_target_items - (promotion_id, item_id)
promotion_target_item_groups - (promotion_id, item_group_id)
promotion_target_categories - (promotion_id, item_category_id)
promotion_target_brands - (promotion_id, brand_id)
promotion_target_suppliers - (promotion_id, supplier_id) → business_partners
promotion_target_loyalty_tiers - (promotion_id, tier_id) → loyalty_bonus_tiers CASCADE
promotion_target_payment_methods - (promotion_id, payment_method_id) CASCADE
promotion_branches - (promotion_id, branch_id) - where the promotion applies
promotion_happy_hour_days - (promotion_id, day_of_week); day_of_week smallint CHECK BETWEEN 0 AND 6 (0 = Sunday)

Spend thresholds for an invoice_tier promotion.

ar_invoice_promotion_applications - what was actually discounted on an invoice. id, ar_invoice_id CASCADE; line_index integer NULL - position within the request’s Lines[] slice, NOT a FK to ar_invoice_lines.id; NULL = header-level (invoice_tier or coupon); promotion_id NOT NULL; promotion_name snapshot; discount_amount NOT NULL, created_at; index (ar_invoice_id)
promotion_coupon_redemptions - id, promotion_id NOT NULL, business_id, customer_id NULL; reference_type DEFAULT ‘ar_invoice’; reference_id NOT NULL; discount_amount, created_by, redeemed_at; UNIQUE (promotion_id, reference_id) - one redemption per document

One-shot all-or-nothing lifecycle.

id, business_id, code - UNIQUE (business_id, code)
amount NULL - CHECK (amount IS NULL OR amount > 0); NULL while unactivated
status DEFAULT ‘active’ - CHECK IN (‘unactivated’,‘active’,‘redeemed’,‘void’); ‘unactivated’ = pre-printed/registered, no value yet
expiry_date NULL (auto-computed from business_settings.gift_voucher_expiry_months at activation if not set)
customer_id NULL
issued_reference_type NOT NULL, issued_reference_id, issued_by
redeemed_reference_type NULL, redeemed_reference_id NULL, redeemed_at
created_at

loyalty_point_transactions - append-only ledger; business_partners.loyalty_points_balance is the running total. id, business_id, customer_id NOT NULL; type CHECK IN (‘earn’,‘redeem’); points NOT NULL; balance_after NOT NULL; lkr_value NOT NULL; spend_amount DEFAULT 0; reference_type, reference_id NULL; journal_entry_id NULL; created_by, created_at; indexes (customer_id, created_at DESC), (business_id, created_at DESC)
loyalty_bonus_tiers - monthly-spend thresholds that multiply earning. id, business_id; min_monthly_spend CHECK >= 0 - UNIQUE index (business_id, min_monthly_spend); bonus_multiplier CHECK >= 0; created_at

Small-change credit kept on the customer’s account instead of handed over.

id, business_id, customer_id NOT NULL
type CHECK IN (‘earn’,‘redeem’); amount; balance_after
reference_type, reference_id, journal_entry_id NULL
cost_center_id
created_by, created_at; indexes (customer_id, created_at DESC), (business_id, created_at DESC)