Loyalty Tiers

Column-level detail for loyalty.tier, tier_history

Overview

Membership tiers were added in migrations 006-009 of the loyalty schema. They introduce two new tables — tier (each rung of the ladder and its rules) and tier_history (every promotion and demotion) — plus new columns on three existing tables (account, milestone, program). Every migration in this group is additive: new tables and nullable-or-defaulted columns only, so a binary built before them keeps working against the newer schema, which is what makes a code-only rollback safe.

The feature is point mode only, deliberately. The rule a merchant configures measures accumulated spend inside a rolling window, and amount_spent is NULL on every stamp row — a stamp is one stamp whatever the basket cost. Switching to stamp mode therefore sets tiers aside exactly as it already sets balances and the reward ladder aside.

Table loyalty.tier

Column	Type	Nullable	Default	Description
id	SERIAL	NO	auto increment	Primary key
program_id	INTEGER	NO	-	FK to loyalty.program(id), ON DELETE CASCADE
line_oa_id	INTEGER	NO	-	LINE OA this tier belongs to
organization_id	INTEGER	NO	-	Owning organization
name	VARCHAR(60)	NO	-	Tier name, e.g. Silver, Gold
rank	INTEGER	NO	-	Ladder position (must be greater than 0) — rank, never threshold, so the list does not reorder itself while the merchant is typing
threshold	NUMERIC(12,2)	YES	-	Legacy single spend threshold in baht, superseded by conditions; retained for rollback and no longer read (migration 007)
color	VARCHAR(9)	YES	-	Tier colour
bg_image_path	TEXT	YES	-	Object path of the tier card background image
status	VARCHAR(20)	NO	'active'	Status: active or archived
created_date	TIMESTAMPTZ(3)	NO	CURRENT_TIMESTAMP	Creation timestamp
updated_date	TIMESTAMPTZ(3)	YES	-	Last update timestamp
conditions	JSONB	NO	'{"join":"and","rules":[]}'	The rule set for this tier (migration 007)
window_months	INTEGER	YES	-	Rolling window for this tier's totals in months (1-120), set per tier (migration 007)
text_color	VARCHAR(9)	YES	-	Text colour on this tier's card — NULL uses the program's (migration 008)
is_default	BOOLEAN	NO	false	The floor tier a customer sits on until rules earn them higher; its own rules are never evaluated (migration 009)

Shape of conditions

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

• metric: spend | orders | points | member_months
• op: gte | lte | eq
• window_months applies to every metric except member_months, which is account age — a duration, not a total inside a window

Table loyalty.tier_history

Column	Type	Nullable	Default	Description
id	BIGSERIAL	NO	auto increment	Primary key
account_id	INTEGER	NO	-	FK to loyalty.account(id), ON DELETE CASCADE
line_oa_id	INTEGER	NO	-	LINE OA this record belongs to
organization_id	INTEGER	NO	-	Owning organization
from_tier_id	INTEGER	YES	-	FK to loyalty.tier(id) — tier before the change (NULL on initial assignment)
to_tier_id	INTEGER	YES	-	FK to loyalty.tier(id) — tier after the change
reason	VARCHAR(20)	NO	-	Reason: promote, demote, initial
window_value	NUMERIC(12,2)	NO	0	The window total that produced the decision, so the row explains itself
created_date	TIMESTAMPTZ(3)	NO	CURRENT_TIMESTAMP	When the change happened

Columns added to existing tables

loyalty.account (migration 006)

Column	Type	Nullable	Default	Description
tier_id	INTEGER	YES	-	FK to loyalty.tier(id) — where the customer currently sits
tier_since	TIMESTAMPTZ(3)	YES	-	When they entered the current tier
tier_evaluated_date	TIMESTAMPTZ(3)	YES	-	When the tier was last evaluated

loyalty.milestone (migration 006)

Column	Type	Nullable	Default	Description
min_tier_rank	INTEGER	YES	-	Minimum tier rank needed to claim this reward — NULL means open to everybody, which every existing reward must remain

loyalty.program (migration 006)

Column	Type	Nullable	Default	Description
tier_enabled	BOOLEAN	NO	false	Whether tiers are on for this program
tier_window_months	INTEGER	NO	3	Program-level rolling window in months (1-120)
tier_fallback	VARCHAR(20)	NO	'step_down'	Demotion behaviour: step_down (one rung at a time) or specific (drop to a named tier)
tier_fallback_tier_id	INTEGER	YES	-	FK to loyalty.tier(id) — the target when tier_fallback = 'specific'

Notes

• tier check constraints: chk_loy_tier_rank (rank > 0), chk_loy_tier_threshold (threshold >= 0), chk_loy_tier_status (status IN ('active','archived')) and chk_loy_tier_window (window_months NULL or between 1 and 120)
• Other check constraints: chk_loy_tier_hist_reason (reason IN ('promote','demote','initial')), chk_loy_milestone_tier (min_tier_rank NULL or greater than 0), chk_loy_program_tier_window (tier_window_months between 1 and 120), chk_loy_program_tier_fallback (tier_fallback IN ('step_down','specific'))
• Unique: idx_loy_tier_rank on (program_id, rank) WHERE status = 'active' — one tier per rung; being partial, archiving a tier frees its rank for reuse. And idx_loy_tier_one_default on program_id WHERE is_default AND status = 'active' — at most one default per program, since two defaults would make "the tier you fall to" ambiguous
• Other indexes: idx_loy_tier_program on (program_id, rank); idx_loy_tier_hist_acct on (account_id, created_date DESC); idx_loy_account_tier on (line_oa_id, tier_id) WHERE tier_id IS NOT NULL
• Thresholds are spend, not points: threshold and the spend metric are baht of spend inside the window. Points are burned by redemptions, so a point-based threshold would demote a customer for using a reward — the same trap that rules out computing tier from balance
• Why tier_history is not optional bookkeeping: a rolling window drops customers without them doing anything, so "why did I drop?" is the support question this feature generates. Without the window_value that caused the decision, nobody can answer it
• Why conditions is JSONB rather than a child table: the rules are always read as a whole with their tier and never queried across tiers, so a table would add a join and a second CRUD surface for something that is one object — matching bonus_rule.conditions and the audience filter
• Migration 007 data move: existing threshold values were carried across into a single equivalent spend rule, so no merchant's ladder resets. threshold was made nullable rather than dropped so a rollback to pre-rules binaries still finds the column it expects; a COMMENT ON COLUMN marks it as legacy and not read