CRM Loyalty & Points — v1.4

DDL

CREATE TABLE crm_loyalty_programs (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  facility_id uuid NOT NULL
    REFERENCES facilities(id) ON DELETE CASCADE,
  is_active boolean NOT NULL DEFAULT false,
  name text NOT NULL,
  points_name text NOT NULL DEFAULT 'pts',
  earn_rules jsonb NOT NULL DEFAULT '{}',
  tiers jsonb NOT NULL DEFAULT '[]',
  expiry_policy jsonb NOT NULL DEFAULT
    '{"enabled": false}',
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT crm_loyalty_programs_facility_unique
    UNIQUE (facility_id)
);

-- earn_rules shape:
-- {
--   "booking":  { "enabled": true,
--                 "type": "per_hour",
--                 "points": 10 },
--   "pos":      { "enabled": true,
--                 "points_per_100_units": 5 },
--   "program":  { "enabled": true,
--                 "points_per_enrollment": 50 }
-- }

-- tiers shape (array):
-- [
--   { "name": "Bronze",
--     "lifetime_threshold": 0,
--     "court_discount_pct": 0,
--     "pos_discount_pct": 0,
--     "perks": "Welcome tier" },
--   { "name": "Silver",
--     "lifetime_threshold": 500,
--     "court_discount_pct": 5,
--     "pos_discount_pct": 5,
--     "perks": "5% off all bookings" },
--   { "name": "Gold",
--     "lifetime_threshold": 1500,
--     "court_discount_pct": 10,
--     "pos_discount_pct": 10,
--     "perks": "10% off + priority booking" },
--   { "name": "Platinum",
--     "lifetime_threshold": 5000,
--     "court_discount_pct": 15,
--     "pos_discount_pct": 15,
--     "perks": "15% off + exclusive events" }
-- ]

-- expiry_policy shape:
-- { "enabled": true, "window_days": 365 }

DDL

CREATE TABLE crm_loyalty_accounts (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  facility_id uuid NOT NULL
    REFERENCES facilities(id) ON DELETE CASCADE,
  program_id uuid NOT NULL
    REFERENCES crm_loyalty_programs(id) ON DELETE CASCADE,
  profile_id uuid NOT NULL
    REFERENCES customers(id) ON DELETE CASCADE,
  points_balance integer NOT NULL DEFAULT 0,
  lifetime_points integer NOT NULL DEFAULT 0,
  current_tier text NOT NULL DEFAULT 'Bronze',
  tier_updated_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT crm_loyalty_accounts_unique
    UNIQUE (facility_id, profile_id)
);

DDL

CREATE TABLE crm_loyalty_ledger (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  facility_id uuid NOT NULL
    REFERENCES facilities(id),
  program_id uuid NOT NULL
    REFERENCES crm_loyalty_programs(id),
  profile_id uuid NOT NULL
    REFERENCES customers(id),
  event_type text NOT NULL CHECK (event_type IN (
    'earned','spent','expired','adjusted','reversed'
  )),
  points integer NOT NULL,
  source_type text NOT NULL CHECK (source_type IN (
    'booking','pos_order','program_enrollment',
    'redemption','manual','expiry'
  )),
  source_id uuid,
  description text,
  expires_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  created_by uuid REFERENCES customers(id)
);

DDL

CREATE TABLE crm_rewards (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  facility_id uuid NOT NULL
    REFERENCES facilities(id) ON DELETE CASCADE,
  program_id uuid NOT NULL
    REFERENCES crm_loyalty_programs(id) ON DELETE CASCADE,
  name text NOT NULL,
  description text,
  point_cost integer NOT NULL CHECK (point_cost > 0),
  reward_type text NOT NULL CHECK (reward_type IN (
    'credit','discount_pct','free_session',
    'merchandise','other'
  )),
  reward_value jsonb,
  is_active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now()
);

-- reward_value examples:
-- credit:       { "amount": 500 }  (in smallest currency unit)
-- discount_pct: { "pct": 20 }
-- free_session: { "minutes": 60 }
-- merchandise:  { "sku": "tshirt-m" }

DDL

CREATE TABLE crm_reward_redemptions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  facility_id uuid NOT NULL
    REFERENCES facilities(id),
  program_id uuid NOT NULL
    REFERENCES crm_loyalty_programs(id),
  reward_id uuid NOT NULL
    REFERENCES crm_rewards(id),
  profile_id uuid NOT NULL
    REFERENCES customers(id),
  ledger_entry_id uuid
    REFERENCES crm_loyalty_ledger(id),
  points_used integer NOT NULL,
  notes text,
  redeemed_at timestamptz NOT NULL DEFAULT now(),
  redeemed_by uuid NOT NULL REFERENCES customers(id)
);