CRM Loyalty System — Architecture

Pseudocode / Implementation

CREATE OR REPLACE FUNCTION on_booking_earn_loyalty_points()
RETURNS TRIGGER AS $$
DECLARE
  v_program  crm_loyalty_programs%ROWTYPE;
  v_points   integer;
  v_hours    numeric;
BEGIN
  -- 1. Find active loyalty program for this facility
  SELECT * INTO v_program
  FROM crm_loyalty_programs
  WHERE facility_id = NEW.facility_id AND is_active = true;

  IF NOT FOUND THEN RETURN NEW; END IF;  -- no program, skip

  -- 2. Check earn_rules.booking.enabled
  IF NOT (v_program.earn_rules->'booking'->>'enabled')::boolean THEN
    RETURN NEW;
  END IF;

  -- 3. Deduplication: already earned for this booking?
  IF EXISTS (
    SELECT 1 FROM crm_loyalty_ledger
    WHERE source_type = 'booking' AND source_id = NEW.id
  ) THEN RETURN NEW; END IF;

  -- 4. Calculate points
  IF v_program.earn_rules->'booking'->>'type' = 'per_hour' THEN
    v_hours  := EXTRACT(EPOCH FROM (NEW.end_time - NEW.start_time)) / 3600.0;
    v_points := ROUND(v_hours * (v_program.earn_rules->'booking'->>'points')::integer);
  ELSE
    v_points := (v_program.earn_rules->'booking'->>'points')::integer;
  END IF;

  IF v_points <= 0 THEN RETURN NEW; END IF;

  -- 5. Insert ledger row
  INSERT INTO crm_loyalty_ledger (facility_id, program_id, profile_id,
    event_type, points, source_type, source_id, description, expires_at)
  VALUES (
    NEW.facility_id, v_program.id, NEW.profile_id,
    'earned', v_points, 'booking', NEW.id,
    'Court booking — ' || to_char(NEW.start_time, 'DD Mon YYYY'),
    CASE WHEN (v_program.expiry_policy->>'enabled')::boolean
      THEN now() + ((v_program.expiry_policy->>'window_days')::integer || ' days')::interval
      ELSE NULL END
  );

  -- 6. Upsert account balance
  INSERT INTO crm_loyalty_accounts
    (facility_id, program_id, profile_id, points_balance, lifetime_points)
  VALUES (NEW.facility_id, v_program.id, NEW.profile_id, v_points, v_points)
  ON CONFLICT (facility_id, profile_id)
  DO UPDATE SET
    points_balance  = crm_loyalty_accounts.points_balance  + v_points,
    lifetime_points = crm_loyalty_accounts.lifetime_points + v_points;

  -- 7. Refresh tier
  PERFORM refresh_loyalty_tier(NEW.profile_id, NEW.facility_id);

  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER trg_booking_earn_loyalty
  AFTER UPDATE ON bookings
  FOR EACH ROW
  WHEN (
    NEW.status IN ('completed','checked_in') AND
    OLD.status NOT IN ('completed','checked_in')
  )
  EXECUTE FUNCTION on_booking_earn_loyalty_points();

Pseudocode / Implementation

CREATE OR REPLACE FUNCTION refresh_loyalty_tier(
  p_profile_id uuid,
  p_facility_id uuid
) RETURNS void AS $$
DECLARE
  v_lifetime  integer;
  v_program   crm_loyalty_programs%ROWTYPE;
  v_tier      text := 'Bronze';
  v_tier_obj  jsonb;
BEGIN
  -- Get current lifetime points
  SELECT lifetime_points INTO v_lifetime
  FROM crm_loyalty_accounts
  WHERE profile_id = p_profile_id AND facility_id = p_facility_id;

  -- Get program tiers
  SELECT * INTO v_program
  FROM crm_loyalty_programs
  WHERE facility_id = p_facility_id AND is_active = true;

  -- Walk tiers (sorted ascending by threshold — enforced at app layer)
  FOR v_tier_obj IN
    SELECT value FROM jsonb_array_elements(v_program.tiers) AS t(value)
    ORDER BY (value->>'lifetime_threshold')::integer ASC
  LOOP
    IF v_lifetime >= (v_tier_obj->>'lifetime_threshold')::integer THEN
      v_tier := v_tier_obj->>'name';
    END IF;
  END LOOP;

  -- Update cached tier on account
  UPDATE crm_loyalty_accounts
  SET current_tier    = v_tier,
      tier_updated_at = now()
  WHERE profile_id = p_profile_id AND facility_id = p_facility_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Pseudocode / Implementation

CREATE OR REPLACE FUNCTION redeem_loyalty_points(
  p_profile_id  uuid,
  p_facility_id uuid,
  p_reward_id   uuid,
  p_staff_id    uuid,
  p_notes       text DEFAULT NULL
) RETURNS uuid AS $$  -- returns redemption id
DECLARE
  v_account   crm_loyalty_accounts%ROWTYPE;
  v_reward    crm_rewards%ROWTYPE;
  v_ledger_id uuid;
  v_redemp_id uuid;
BEGIN
  -- Lock account row to prevent concurrent race conditions
  SELECT * INTO v_account
  FROM crm_loyalty_accounts
  WHERE profile_id = p_profile_id AND facility_id = p_facility_id
  FOR UPDATE;

  SELECT * INTO v_reward
  FROM crm_rewards
  WHERE id = p_reward_id AND facility_id = p_facility_id AND is_active = true;

  -- Guard: reward not found
  IF NOT FOUND THEN
    RAISE EXCEPTION 'Reward not found or inactive' USING ERRCODE = 'P0001';
  END IF;

  -- Guard: insufficient balance
  IF v_account.points_balance < v_reward.point_cost THEN
    RAISE EXCEPTION 'Insufficient balance: % pts available, % pts required',
      v_account.points_balance, v_reward.point_cost
    USING ERRCODE = 'P0002';
  END IF;

  -- 1. Insert negative ledger row
  INSERT INTO crm_loyalty_ledger
    (facility_id, program_id, profile_id, event_type, points,
     source_type, description, created_by)
  VALUES (
    p_facility_id, v_account.program_id, p_profile_id,
    'spent', -v_reward.point_cost,
    'redemption', 'Redeemed: ' || v_reward.name, p_staff_id
  )
  RETURNING id INTO v_ledger_id;

  -- 2. Decrement spendable balance (NOT lifetime_points)
  UPDATE crm_loyalty_accounts
  SET points_balance = points_balance - v_reward.point_cost
  WHERE id = v_account.id;

  -- 3. Insert redemption record
  INSERT INTO crm_reward_redemptions
    (facility_id, program_id, reward_id, profile_id,
     ledger_entry_id, points_used, notes, redeemed_by)
  VALUES (
    p_facility_id, v_account.program_id, p_reward_id, p_profile_id,
    v_ledger_id, v_reward.point_cost, p_notes, p_staff_id
  )
  RETURNING id INTO v_redemp_id;

  RETURN v_redemp_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Pseudocode / Implementation

-- pg_cron job (registered per facility on program activation):
-- SELECT cron.schedule(
--   'expire-loyalty-' || facility_id,
--   '0 2 * * *',
--   format('SELECT expire_loyalty_points(%L)', facility_id)
-- );

CREATE OR REPLACE FUNCTION expire_loyalty_points(p_facility_id uuid)
RETURNS void AS $$
DECLARE
  v_row RECORD;
BEGIN
  FOR v_row IN
    SELECT l.profile_id,
           SUM(ABS(l.points)) AS points_to_expire
    FROM crm_loyalty_ledger l
    WHERE l.facility_id   = p_facility_id
      AND l.event_type    = 'earned'
      AND l.expires_at    < now()
      AND NOT EXISTS (
        SELECT 1 FROM crm_loyalty_ledger l2
        WHERE l2.source_type = 'expiry'
          AND l2.source_id   = l.id
      )
    GROUP BY l.profile_id
  LOOP
    -- Insert negative expiry ledger row
    INSERT INTO crm_loyalty_ledger
      (facility_id, program_id, profile_id, event_type, points, source_type, description)
    SELECT p_facility_id, program_id, v_row.profile_id,
           'expired', -v_row.points_to_expire, 'expiry',
           'Points expired — ' || to_char(now(), 'DD Mon YYYY')
    FROM crm_loyalty_accounts
    WHERE profile_id = v_row.profile_id AND facility_id = p_facility_id;

    -- Decrement spendable balance (floor at 0)
    UPDATE crm_loyalty_accounts
    SET points_balance = GREATEST(0, points_balance - v_row.points_to_expire)
    WHERE profile_id   = v_row.profile_id
      AND facility_id  = p_facility_id;
  END LOOP;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;