Loyalty programme

A per-store, RLS-isolated loyalty engine that matches the 2026 feature set of Smile.io, LoyaltyLion, and Yotpo Loyalty. Programme config + earn rules + VIP tiers + analytics + per-member ledger + manual adjustments — no app install required.

Mental model

loyalty_programmes  ← one row per store (programme-level config)
    ↓ FK
loyalty_rules       ← N rows, the earn triggers (purchase, signup, review, …)
    ↓
loyalty_tiers       ← N rows, the VIP tiers (Silver → Gold → Platinum)

loyalty_points      ← one row per (store, customer) — current balance
    ↓ FK
loyalty_transactions ← append-only ledger; positive = earn, negative = burn

Every table is store-scoped with an RLS policy that requires the app.current_store_id GUC to be set. Cross-tenant queries are impossible at the database level even if the application layer drops the filter.

Programme-level config (`loyalty_programmes`)

One row per store, auto-created on first access so the merchant lands on a draft programme without a “do you want to enable loyalty?” prompt.

Field	Type	What it controls
`name`	varchar(200)	Internal label — “Coffee Co Rewards”, “Brand Club”.
`points_currency_name`	varchar(40)	What shoppers see in the widget — “Stars”, “Coins”, “Smiles”.
`status`	enum	`draft` (widget hidden) · `active` (live) · `paused` (no new earn, redemption still works) · `archived`.
`points_expiry_days`	int	Null = never expire. Otherwise an hourly sweep reverses points older than this many days.
`minimum_redemption_points`	int	Floor before a shopper can redeem at checkout.
`points_to_currency_rate_minor`	numeric(8,4)	Minor units a point is worth. `1.0` = 1p per point. `2.5` = 2.5p per point.
`referral_points_referrer` / `_referred`	int	Bonus on a successful referral signup (referrer + referred each get their own bonus).
`birthday_points`	int	Annual bonus on the customer’s birthday.

Earn rules (`loyalty_rules`)

N rows per programme. The rule_type enum drives which event handler fires the rule:

`rule_type`	When it fires	Field used
`purchase`	`order.paid`	`points_per_pound` × order subtotal (in £)
`signup`	`customer.created`	`points_flat`
`review`	`review.published`	`points_flat`
`birthday`	annual cron on the customer’s DOB	`points_flat` (overrides `birthdayPoints` on programme if present)
`referral`	referred shopper places first order	`points_flat` (overrides programme-level referral fields if present)
`social_share`	`share.tracked` event	`points_flat`
`custom`	merchant-defined Zapier / Make / webhook trigger	`points_flat`

Rules can carry a multiplier (e.g. “2× points this weekend”), a time window (start_at / end_at), and an is_active toggle. Validation enforces:

purchase rules require points_per_pound > 0.
Non-purchase rules require points_flat > 0.
start_at must be before end_at.

VIP tiers (`loyalty_tiers`)

Optional. Tiers unlock bonus earn multipliers and perks for high-value shoppers.

Field	What it controls
`name`	“Silver”, “Gold”, “Platinum”
`minimum_points`	Lifetime-earned threshold to enter the tier
`points_multiplier`	Multiplier applied to every earn while the shopper is in this tier (`1.5` = 50% bonus)
`perks`	Array of strings (max 20). Storefront widget renders as a bullet list.
`badge_color` / `badge_icon`	Storefront badge styling
`position`	Sort order (low → high)

Admin endpoints (`/api/v1/loyalty`)

All endpoints require x-store-id header. Write endpoints require settings.write permission.

Method	Path	Purpose
`GET`	`/loyalty`	Get / auto-create programme
`PATCH`	`/loyalty`	Update programme settings
`GET`	`/loyalty/rules`	List earn rules
`POST`	`/loyalty/rules`	Create earn rule
`PATCH`	`/loyalty/rules/:id`	Update earn rule
`DELETE`	`/loyalty/rules/:id`	Delete earn rule
`GET`	`/loyalty/tiers`	List VIP tiers
`POST`	`/loyalty/tiers`	Create VIP tier
`PATCH`	`/loyalty/tiers/:id`	Update VIP tier
`DELETE`	`/loyalty/tiers/:id`	Delete VIP tier
`GET`	`/loyalty/analytics?windowDays=90`	Members, outstanding, earn/burn ratio, tier distribution, top earners
`GET`	`/loyalty/customers?q=&sort=&page=&pageSize=`	Paginated balance browser (search by email/name)
`POST`	`/loyalty/customers/:customerId/adjust`	Manual grant or revoke (audit-logged)

Analytics response

{
  "data": {
    "windowDays": 90,
    "since": "2026-02-22T12:00:00Z",
    "members": 1284,
    "outstandingPoints": 482_300,
    "lifetimeEarned": 1_240_900,
    "outstandingLiabilityMinor": 482_300, // outstanding × pointsToCurrencyRateMinor
    "currency": "GBP",
    "window": {
      "earned": 124_500,
      "burned": 48_200,
      "earnBurnRatio": 0.39, // healthy = 0.3 – 0.7
      "transactions": 982,
    },
    "topEarners": [{ "customerId": "uuid", "balance": 12_400, "lifetimeEarned": 38_200 }],
    "tierDistribution": [
      { "tierId": "uuid", "name": "Silver", "minimumPoints": 0, "members": 940 },
      { "tierId": "uuid", "name": "Gold", "minimumPoints": 1000, "members": 280 },
      { "tierId": "uuid", "name": "Platinum", "minimumPoints": 10000, "members": 64 },
    ],
  },
}

The dashboard’s /loyalty/analytics page reads this verbatim and renders the earn/burn flow, tier distribution bars, and top-earners table.

Earn/burn ratio interpretation

Range	Reading
`< 0.1`	Shoppers aren’t redeeming — programme isn’t motivating. Consider lowering `minimum_redemption_points` or adding a cart-page redemption nudge.
`0.3 – 0.7`	Healthy. Shoppers redeem without draining the liability faster than it builds.
`> 1.0`	Burning more than earning this window — legacy points catching up. Watch outstanding liability; do not over-react if the trend stabilises.

Manual adjustment

POST /api/v1/loyalty/customers/:customerId/adjust
Content-Type: application/json
x-store-id: <uuid>

{
  "points": 500,                        // Positive = grant; negative = revoke
  "reason": "Goodwill credit for delayed order #1024"
}

Validation:

points must be non-zero.
reason is required (3–500 chars). Stored on the loyalty_transactions row so the audit trail is legible months later.
Granting points advances both balance and lifetime_earned.
Revoking points only reduces balance (lifetime stays — matches LoyaltyLion / Smile convention).
The mutation is wrapped in a tenant-scoped transaction so a race (adjust vs concurrent purchase) can’t drop one of the writes. Pushing balance negative is rejected.

Storefront widget (`/api/v1/storefront/loyalty/widget-data`)

Public, anonymous endpoint. With an x-customer-id header it also returns the shopper’s current balance and tier; without it returns the programme + tiers + headline earn rate only.

{
  "data": {
    "programmeName": "Coffee Co Rewards",
    "pointsCurrencyName": "Beans",
    "headlineEarnRate": "Earn 1 bean per £1",
    "minimumRedemption": 100,
    "pointsToCurrencyRate": 0.01,
    "tiers": [
      { "name": "Silver", "minimumPoints": 0, "perks": ["Free shipping over £30"] },
      { "name": "Gold", "minimumPoints": 500, "perks": ["Free shipping", "Early access"] },
    ],
    "customer": {
      "balance": 240,
      "lifetimeEarned": 540,
      "currentTier": { "name": "Silver", "next": "Gold", "pointsToNext": 260 },
    },
  },
}

Dashboard surfaces

Path	Purpose
`/loyalty`	Programme config (name, currency, expiry, redemption rate, referral + birthday bonuses) + earn rules + tiers (single-page CRUD).
`/loyalty/analytics`	Members + outstanding + liability + earn/burn ratio + tier distribution + top earners over a configurable window (30 / 90 / 180 / 365 / 730 days).
`/loyalty/customers`	Paginated balance browser. Search by email or name; sort by current balance or lifetime earned; per-row “Adjust” button opens the manual grant/revoke modal.

Sub-nav: apps/dashboard/components/loyalty/LoyaltyNav.vue renders a 3-tab strip + a “Preview widget” link on every loyalty page.

Schema files

packages/database/src/schema/loyalty.ts — programme + rules + tiers.
packages/database/src/schema/commerce.ts — loyaltyPoints + loyaltyTransactions (per-customer ledger; pre-dates the programme tables).
packages/database/drizzle/0098_loyalty_programmes.sql — initial migration.

RLS

Every loyalty table has a loyalty_*_tenant_isolation policy. Programme reads/writes go through withTenantContext(storeId, ...) which sets the GUC; analytics reads also go through the same wrapper. The loyalty_programmes_store_uniq index guarantees one programme per store.