Loyalty Tiers

Overview

An extension of Loyalty Cards & Membership that adds membership tiers — e.g. Silver, Gold, Platinum — with promotion based on conditions a shop defines, evaluated within a rolling time window.

This system has one intentional restriction: point mode only, because the main condition is cumulative spend from the amount_spent column, which is NULL on every row in stamp mode (a single stamp is worth one stamp regardless of basket value). If a shop switches back to stamp mode, tier membership is set aside, just like the balance is.

The entire migration set is additive only — every change is either a new table or a nullable column — so older binaries keep working against the new schema. That's what makes a "code-only rollback" safe without ever having to reverse the schema.

Core Data Structure

loyalty.tier — a program's tier ladder

• program_id is a FK with ON DELETE CASCADE, plus name

• rank is an ordering, not a threshold — because a shop may keep editing the ladder, the system must never reorder it automatically. A partial unique index (program_id, rank) WHERE status='active' lets the rank of an archived tier be reused

• conditions is JSONB holding each tier's rule set, replacing the single threshold used in the first version:

  {"join":"and","rules":[
     {"metric":"member_months","op":"gte","value":6},
     {"metric":"spend","op":"gte","value":1000},
     {"metric":"orders","op":"gte","value":5}]}

  metric supports spend, orders, points, and member_months; op supports gte, lte, and eq. JSONB was chosen over a child table because the rules are always read as a whole alongside the tier, and are never queried across tiers

• window_months is each tier's time window, which must always be paired with a number, otherwise a condition like "5,000 baht" would be meaningless — except for the member_months metric, which doesn't use this value since it's the member's age, not a cumulative amount within a window

• threshold (NUMERIC) is a legacy column kept around for rollback purposes, documented with a COMMENT ON COLUMN, and no longer read by current code

• Display: color, text_color (empty means fall back to the program's color), and bg_image_path

• is_default marks the baseline tier everyone starts at before meeting any condition, enforced by a partial unique index (program_id) WHERE is_default AND status='active' so there's only ever one, and this tier's own rules are never evaluated

loyalty.tier_history — promotion/demotion history

Records every promotion and demotion.

• from_tier_id, to_tier_id, and reason (promote, demote, or initial)
• window_value stores the window value that caused the change, so a "why was I demoted" question can be answered instantly without recomputing from data that has since moved on

Columns added to existing tables

Table	Added columns
loyalty.account	tier_id, tier_since, tier_evaluated_date
loyalty.milestone	min_tier_rank (empty means open to everyone)
loyalty.program	tier_enabled, tier_window_months (default 3, must be 1–120), tier_fallback (step_down or specific), tier_fallback_tier_id

Related Files

• apps/loyalty/migrations/006_tiers.sql — the tier and tier_history tables plus the added columns
• apps/loyalty/migrations/007_tier_rules.sql — the JSONB conditions column and migrating the old threshold into a rule
• apps/loyalty/migrations/008_tier_text_color.sql — per-tier text color
• apps/loyalty/migrations/009_tier_default.sql — the baseline tier
• docs/superpowers/specs/2026-07-28-loyalty-tier-design.md — the design spec
• docs/ROLLBACK-tier.md — every repo's commit at the point before this feature started, with a DROP script if needed

Connections to Other Services

• worker-go and cms-api-go act as the tier evaluators — reading transaction within the time window, comparing it against conditions, then updating account.tier_id and writing to tier_history
• client-api-go displays the current tier, the card's color, and rewards unlocked per min_tier_rank on the card screen in LIFF
• All of this builds on top of Loyalty Cards & Membership