rewards — Consumer Layer

Schema locked 2026-06-12. 6 tables, 89 cols: loyalty_program (12), earning_rule (17), tier_level (12), reward_option (17), loyalty_account (14), points_ledger (17).

Design Principles

Tenant-scoped: the nursery owns its loyalty program. rewards.* tables use WHERE tenant_id = current_setting('app.current_tenant_id')::UUID for RLS. The nursery configures earning rules, tiers, and redemption catalog; the nursery owns the ledger and account balances. consumer_id is an FK dimension (which consumer) on loyalty_account and points_ledger — it is NEVER the RLS scope. This is the structural inverse of consumer_tenant_link (which was consumer-scoped with tenant_id as dimension).

ConsumerService is the cross-store read boundary. A consumer reads their per-tenant balances through ConsumerService (service-role aggregation over tenant-scoped data). Balances do NOT pool across nurseries — per-business loyalty is the locked model. Platform-wide aggregated points is a future query/view over the per-tenant schema, not a schema redesign.

points_ledger is the source of truth; balances are maintained caches. loyalty_account.balance_points and loyalty_account.lifetime_points are maintained caches that reconcile to SUM(amount_points) over the ledger. The pattern is identical to pos.gift_card/pos.store_credit/billing.ar_account. A correction is an 'adjust' or 'reverse' ledger row — never an UPDATE to the ledger. The ledger is append-only (insert-once): no updated_at, no deleted_at.

No stub accounts. loyalty_account.consumer_id is NOT NULL. Unclaimed stubs do not get loyalty accounts. When a stub is claimed, ConsumerService credits historical eligible sale points in a batch of 'earn' ledger rows keyed to the original sale_id. No stub-points holding table exists.

No consent table. RewardsService reads crm.customer_consent (consent_type = 'loyalty') before crediting points. Consent lives in CRM — the same pattern as notifications reading crm.customer_consent for marketing preferences. Zero consent tables in rewards.

Hard non-negative balances (v1). CHECK (balance_points >= 0) on loyalty_account and CHECK (balance_after_points >= 0) on points_ledger. Over-redemption and refund scenarios are handled by capping or a 'reverse' row — not by allowing debt. Negative-balance accounts are not supported in v1.

Rewards vs Offers boundary. reward_option is the nursery's points-funded redemption catalog ("500 points = $5 off") — always nursery-funded, always denominated in points. offers (separate module) is coupon/promo issuance, including Vrida-funded promos with settlement economics ("20% off this weekend"). Never put a promo code in reward_option; never put a points redemption in offers. The offers module is separate because Vrida-funded offers require financial settlement mechanics that points-loyalty does not.

---

Cross-Phase FK Seams

Column	Target	Status
loyalty_program.tenant_id	platform.tenant	ENFORCED FK (platform locked 2026-06-09; enforced at rewards schema creation)
earning_rule.tenant_id	platform.tenant	ENFORCED FK
tier_level.tenant_id	platform.tenant	ENFORCED FK
reward_option.tenant_id	platform.tenant	ENFORCED FK
loyalty_account.tenant_id	platform.tenant	ENFORCED FK
points_ledger.tenant_id	platform.tenant	ENFORCED FK
loyalty_account.consumer_id	consumer.consumer	ENFORCED FK (consumer locked 2026-06-11; enforced at rewards schema creation) — READY
points_ledger.sale_id	pos.sale	ENFORCED FK (pos locked 2026-06-10; enforced at rewards schema creation) — READY
reward_option.free_item_variant_ref	inventory.item_variant	LOOSE text ref (loose ref by design — reward catalog items may reference variants that are archived or replaced; RewardsService validates at redemption time)
earning_rule.scope_ref	inventory.item_variant / category code	LOOSE text ref (polymorphic target depending on rule_scope; RewardsService validates at earn time)
points_ledger.source_ref	various non-sale sources	LOOSE text ref (review ID, etc. — polymorphic; not FK-enforceable)

---

rewards.loyalty_program — 12 cols

Per-tenant loyalty program configuration. One program per tenant in v1.0 (one-program-per-tenant partial unique enforced). Owns the base earn rate, rounding policy, minimum redemption threshold, and expiry policy. All other rewards tables FK back to this row.

Status: 'active' (earning and redemption enabled), 'paused' (no new earning; existing balances and redemption unaffected), 'archived' (program discontinued; balances frozen).

Expiry policy: 'never' = points never expire; 'rolling_months' = each earn row expires N months after earned (per-row expires_at on points_ledger); 'calendar_year' = all points expire end of calendar year (batch sweep); 'inactivity_months' = balance expires if no activity for N months. expiry_policy_value is required when the type is 'rolling_months' or 'inactivity_months', and must be NULL for 'never' and 'calendar_year' (enforced by CHECK).

One program per tenant v1: the partial unique (tenant_id) WHERE deleted_at IS NULL enforces this. Relax for seasonal / wholesale / VIP multi-program support in a later phase (remove the unique, add a program_type discriminator).

RLS: WHERE tenant_id = current_setting('app.current_tenant_id')::UUID.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant — RLS scope
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
name	text	NOT NULL	—	Display name, e.g. 'Sprigs Rewards'
status	text	NOT NULL	'active'	CHECK (status IN ('active','paused','archived'))
points_per_dollar	numeric	NOT NULL	1	Base earn rate: points earned per dollar (cent-divisor applied; e.g. 1.0 = 1 point per $1). Overridden by earning_rule multipliers/bonuses.
points_rounding	text	NOT NULL	'floor'	CHECK (points_rounding IN ('floor','round','ceil')). Applied when points_per_dollar × sale_total_cents / 100 is fractional.
min_redeem_points	integer	NOT NULL	0	Minimum points required to redeem anything. 0 = no minimum.
expiry_policy_type	text	NOT NULL	'never'	CHECK (expiry_policy_type IN ('never','rolling_months','calendar_year','inactivity_months'))
expiry_policy_value	integer	nullable	—	Months for 'rolling_months' and 'inactivity_months'; NULL for 'never' and 'calendar_year'. CHECK ((expiry_policy_type IN ('rolling_months','inactivity_months') AND expiry_policy_value IS NOT NULL) OR (expiry_policy_type IN ('never','calendar_year') AND expiry_policy_value IS NULL))

Indexes:

• PK on id
• PARTIAL UNIQUE on (tenant_id) WHERE deleted_at IS NULL — one program per tenant v1
• on (tenant_id, status) WHERE deleted_at IS NULL — program lookup by status

---

rewards.earning_rule — 17 cols

Scoped bonus earning rules on top of the base loyalty_program.points_per_dollar rate. A separate table (not JSONB) because rules are queried at every earn event — RewardsService queries active rules matching the sale's item categories and line total. The base rate handles the simple case; earning_rule handles double-points promotions, category bonuses, signup bonuses, and birthday rewards without requiring additional tables.

rule_scope values: 'purchase' (applies to all sales, used for blanket multipliers), 'item_category' (applies to sales containing items in the category referenced by scope_ref), 'item_variant' (applies to a specific variant by slug ref), 'sale_threshold' (applies when the sale total meets threshold_cents), 'birthday' (one-time earn on birthday match — RewardsService checks consumer.consumer birth date; no birth-date column here), 'signup' (one-time earn on enrollment), 'review' (earn for a product review), 'manual' (staff-issued one-off bonus — creates a ledger row directly without a sale).

earn_type + field coherence: 'multiplier' requires multiplier IS NOT NULL and flat_bonus_points IS NULL; 'flat_bonus' requires flat_bonus_points IS NOT NULL and multiplier IS NULL (CHECK enforced).

scope_ref is a loose text ref (category code or inventory.item_variant.sku or slug). Not FK-enforced because category codes are not a locked table and item variants may be archived; RewardsService validates at earn time.

RLS: WHERE tenant_id = current_setting('app.current_tenant_id')::UUID.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant — RLS scope
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
loyalty_program_id	UUID	NOT NULL	—	FK → rewards.loyalty_program
name	text	NOT NULL	—	Human-readable rule name, e.g. 'Double Points on Perennials'
rule_scope	text	NOT NULL	—	CHECK (rule_scope IN ('purchase','item_category','item_variant','sale_threshold','birthday','signup','review','manual'))
scope_ref	text	nullable	—	Loose ref: category code or item_variant sku/slug. NULL for 'purchase', 'birthday', 'signup', 'review', 'manual'.
earn_type	text	NOT NULL	—	CHECK (earn_type IN ('multiplier','flat_bonus'))
multiplier	numeric	nullable	—	Earn multiplier applied to base rate (e.g. 2.0 = double points). Required when earn_type = 'multiplier'.
flat_bonus_points	integer	nullable	—	Flat bonus points added (e.g. 50). Required when earn_type = 'flat_bonus'.
threshold_cents	bigint	nullable	—	Minimum sale total (cents) for 'sale_threshold' scope. NULL for other scopes.
priority	integer	NOT NULL	0	Resolution order when multiple rules match. Higher value = higher priority.
is_active	boolean	NOT NULL	true	Soft-disable without deleting.
active_from	timestamptz	nullable	—	Rule active window start (inclusive). NULL = no start constraint.
active_to	timestamptz	nullable	—	Rule active window end (exclusive). NULL = no end constraint.

Constraints:

• CHECK ((earn_type = 'multiplier' AND multiplier IS NOT NULL AND flat_bonus_points IS NULL) OR (earn_type = 'flat_bonus' AND flat_bonus_points IS NOT NULL AND multiplier IS NULL))
• CHECK ((rule_scope = 'sale_threshold' AND threshold_cents IS NOT NULL) OR (rule_scope <> 'sale_threshold' AND threshold_cents IS NULL))
• CHECK (rule_scope <> 'sale_threshold' OR scope_ref IS NULL)

Indexes:

• PK on id
• on (loyalty_program_id, rule_scope) WHERE is_active = true AND deleted_at IS NULL — earn-time rule query
• on (scope_ref) WHERE scope_ref IS NOT NULL AND deleted_at IS NULL — category/variant bonus lookup

---

rewards.tier_level — 12 cols

Tier definitions per loyalty program (e.g. Bronze / Silver / Gold). Tier membership is determined by loyalty_account.lifetime_points (total ever earned, never decremented). RewardsService checks for tier crossing at every earn event and updates loyalty_account.current_tier_level_id when the threshold is crossed.

min_points is unique per program (partial unique enforced) to avoid ambiguous tier resolution. sort_order determines display ordering; min_points determines tier achievement.

earn_multiplier is a per-tier bonus applied on top of all other multipliers (e.g. Gold tier earns 1.25× on every transaction). NULL = no tier bonus.

RLS: WHERE tenant_id = current_setting('app.current_tenant_id')::UUID.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant — RLS scope
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
loyalty_program_id	UUID	NOT NULL	—	FK → rewards.loyalty_program
tier_code	text	NOT NULL	—	Tenant-defined code, e.g. 'bronze', 'silver', 'gold'.
name	text	NOT NULL	—	Display name, e.g. 'Bronze Member'
min_points	integer	NOT NULL	—	Lifetime points threshold to achieve this tier. CHECK (min_points >= 0)
sort_order	integer	NOT NULL	—	Display order (ascending = lowest tier first).
earn_multiplier	numeric	nullable	—	Per-tier earn multiplier applied on top of all other rates. NULL = no tier bonus.
benefit_label	text	nullable	—	Short benefit description for display, e.g. '10% bonus on every purchase'.

Constraints:

• CHECK (min_points >= 0)

Indexes:

• PK on id
• PARTIAL UNIQUE on (loyalty_program_id, tier_code) WHERE deleted_at IS NULL
• PARTIAL UNIQUE on (loyalty_program_id, min_points) WHERE deleted_at IS NULL — prevents ambiguous tier thresholds
• on (loyalty_program_id, min_points) — tier-crossing lookup at earn time (find highest tier where min_points <= account.lifetime_points)

---

rewards.reward_option — 17 cols

The nursery's points-funded redemption catalog: what a consumer can exchange their points for. Always nursery-funded (the cost is the nursery's, not Vrida's). This is what makes rewards a loyalty program rather than just a ledger.

Boundary with offers: reward_option = points-funded redemption ("500 points → $5 off", "200 points → free 4-inch plant"). offers module = coupon/promo issuance, potentially Vrida-funded ("20% off this weekend"). Never put a promo code here; never put a points exchange in offers.

reward_type + field coherence (all enforced by CHECK):

• 'discount_amount': fixed dollar off → discount_amount_cents NOT NULL, discount_percent NULL, free_item_variant_ref NULL
• 'discount_percent': percent off → discount_percent NOT NULL, others NULL
• 'free_item': free specific item → free_item_variant_ref NOT NULL (loose text ref), monetary fields NULL
• 'perk': non-monetary reward (free gift wrap, priority checkout, etc.) → all monetary fields NULL; description via name

tier_level_id gates the option to members of a specific tier or above. NULL = available to all enrolled members regardless of tier.

free_item_variant_ref is a loose text ref to inventory.item_variant (sku or slug). Not FK-enforced — variants may be archived or substituted; RewardsService validates at redemption time.

RLS: WHERE tenant_id = current_setting('app.current_tenant_id')::UUID.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant — RLS scope
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
loyalty_program_id	UUID	NOT NULL	—	FK → rewards.loyalty_program
name	text	NOT NULL	—	Display name, e.g. '$5 Off Your Purchase', 'Free 4-inch Succulent'
reward_type	text	NOT NULL	—	CHECK (reward_type IN ('discount_amount','discount_percent','free_item','perk'))
points_cost	integer	NOT NULL	—	Points required to redeem. CHECK (points_cost > 0)
discount_amount_cents	bigint	nullable	—	Fixed discount amount. Set when reward_type = 'discount_amount'.
discount_percent	numeric	nullable	—	Percentage discount (e.g. 10.00 = 10%). Set when reward_type = 'discount_percent'. CHECK (discount_percent > 0 AND discount_percent <= 100) when not null.
free_item_variant_ref	text	nullable	—	Loose text ref to inventory.item_variant (sku or slug). Set when reward_type = 'free_item'. Validated by RewardsService at redemption.
min_purchase_cents	bigint	nullable	—	Optional minimum basket total required to redeem this option. NULL = no minimum.
tier_level_id	UUID	nullable	—	FK → rewards.tier_level. Tier gate — only members at or above this tier may redeem. NULL = all enrolled members.
is_active	boolean	NOT NULL	true	Soft-disable without deleting.
active_from	timestamptz	nullable	—	Option availability window start. NULL = no start constraint.
active_to	timestamptz	nullable	—	Option availability window end. NULL = no end constraint.

Constraints:

• CHECK reward_type coherence: (reward_type = 'discount_amount' AND discount_amount_cents IS NOT NULL AND discount_percent IS NULL AND free_item_variant_ref IS NULL) OR (reward_type = 'discount_percent' AND discount_percent IS NOT NULL AND discount_amount_cents IS NULL AND free_item_variant_ref IS NULL) OR (reward_type = 'free_item' AND free_item_variant_ref IS NOT NULL AND discount_amount_cents IS NULL AND discount_percent IS NULL) OR (reward_type = 'perk' AND discount_amount_cents IS NULL AND discount_percent IS NULL AND free_item_variant_ref IS NULL)
• CHECK (points_cost > 0)
• CHECK (discount_percent IS NULL OR (discount_percent > 0 AND discount_percent <= 100))

Indexes:

• PK on id
• on (loyalty_program_id) WHERE is_active = true AND deleted_at IS NULL — catalog query (list all redeemable options)
• on (loyalty_program_id, tier_level_id) WHERE is_active = true AND deleted_at IS NULL — tier-gated catalog lookup

---

rewards.loyalty_account — 14 cols

The per-(consumer, tenant) loyalty account. One row per consumer per nursery. Holds the current point balance and tier status as maintained caches (reconcilable against points_ledger). The consumer can have accounts at multiple nurseries (separate rows); the (consumer_id) index serves ConsumerService's cross-store aggregation read.

consumer_id NOT NULL = claimed consumers only. Unclaimed stubs do not receive loyalty_account rows. When a stub is claimed, ConsumerService issues a batch of back-dated 'earn' ledger rows for eligible historical sales, creating the account at claim time.

balance_points = spendable points (decremented by redeem/expire). lifetime_points = total ever earned (never decremented) — used for tier thresholds. Both are maintained caches; balance_points reconciles to SUM(amount_points) WHERE loyalty_account_id = ? over the ledger; lifetime_points reconciles to SUM(amount_points) WHERE entry_type = 'earn'.

current_tier_level_id and tier_achieved_at are maintained caches (history-lite). Full tier transition history is a deferred loyalty_tier_history table.

RLS: WHERE tenant_id = current_setting('app.current_tenant_id')::UUID. consumer_id is the FK dimension — never the RLS scope.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant — RLS scope
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete (account closure, not point zeroing — closing an account is distinct from zeroing balance)
loyalty_program_id	UUID	NOT NULL	—	FK → rewards.loyalty_program
consumer_id	UUID	NOT NULL	—	FK → consumer.consumer — DIMENSION (which consumer). Claimed consumers only. NOT NULL enforces no-stub-account rule.
balance_points	integer	NOT NULL	0	Spendable points. Maintained cache. CHECK (balance_points >= 0) — hard non-negative v1.
lifetime_points	integer	NOT NULL	0	Total points ever earned (not decremented by redemption/expiry). Used for tier threshold comparison. CHECK (lifetime_points >= 0)
current_tier_level_id	UUID	nullable	—	FK → rewards.tier_level — maintained cache of current tier. NULL = no tier (program has no tiers, or account below minimum threshold).
tier_achieved_at	timestamptz	nullable	—	When the current tier was first reached. Tier-history-lite; full history is a deferred table.
enrolled_at	timestamptz	NOT NULL	—	When the account was created (enrollment timestamp).
last_earn_at	timestamptz	nullable	—	Timestamp of most recent earn event. Updated by RewardsService on every earn. Used for inactivity-based expiry sweep.
last_redeem_at	timestamptz	nullable	—	Timestamp of most recent redemption.

Constraints:

• CHECK (balance_points >= 0)
• CHECK (lifetime_points >= 0)
• PARTIAL UNIQUE on (tenant_id, consumer_id) WHERE deleted_at IS NULL — one account per consumer per nursery

Indexes:

• PK on id
• PARTIAL UNIQUE on (tenant_id, consumer_id) WHERE deleted_at IS NULL
• on (consumer_id) — ConsumerService cross-store aggregation: "all loyalty accounts for this consumer across all nurseries"
• on (loyalty_program_id, current_tier_level_id) — tier-membership queries
• on (last_earn_at) WHERE deleted_at IS NULL — inactivity-expiry sweep

---

rewards.points_ledger — 17 cols

The append-only source of truth for all points movements. Insert-once: no updated_at, no deleted_at. Every earn, redemption, expiry, adjustment, and reversal is a new row. loyalty_account.balance_points is always reconcilable to SUM(amount_points) WHERE loyalty_account_id = ?.

entry_type values:

• 'earn': points credited from a sale or non-sale source. amount_points is positive.
• 'redeem': points consumed for a reward_option. amount_points is negative. reward_option_id is required.
• 'expire': points expired per the program's expiry policy. amount_points is negative. expired_from_ledger_id references the original earn row.
• 'adjust': manual staff correction. amount_points may be positive or negative. note is required. created_by_user_id is set.
• 'reverse': system-generated reversal of a prior entry (refund, voided sale, return). amount_points is the sign-flip of the reversed row. reversed_ledger_id references the original entry.

sale_id (FK → pos.sale): set when source_type = 'sale'. This is THE seam closure with POS. The ledger row references the sale; pos.sale.points_earned/points_redeemed are the sale's own receipt-level snapshots (not the ledger).

Source coherence (CHECK): when source_type = 'sale', sale_id IS NOT NULL and source_ref IS NULL. When source_type ≠ 'sale' and source_type ≠ 'manual' and source_type ≠ 'expiry', source_ref IS NOT NULL and sale_id IS NULL. 'manual' and 'expiry' may have neither.

balance_after_points: running per-account snapshot at the time of insert. CHECK >= 0 enforces the hard non-negative constraint at the ledger level (the authoritative enforcement point).

expires_at: per-earn-row expiry timestamp. Set by RewardsService at earn time when expiry_policy_type = 'rolling_months' (computed from enrolled_at + expiry_policy_value months). The expiry sweep queries this column to issue 'expire' rows. NULL for non-earn entries and programs with 'never'/'calendar_year' expiry.

RLS: WHERE tenant_id = current_setting('app.current_tenant_id')::UUID.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant — RLS scope. Denormalized from loyalty_account for direct RLS application.
loyalty_account_id	UUID	NOT NULL	—	FK → rewards.loyalty_account
created_at	timestamptz	NOT NULL	now()	Insert timestamp — the ledger timestamp. No updated_at; no deleted_at — append-only.
entry_type	text	NOT NULL	—	CHECK (entry_type IN ('earn','redeem','expire','adjust','reverse'))
amount_points	integer	NOT NULL	—	Signed: positive for earn, negative for redeem/expire/reverse-of-earn. CHECK (entry_type <> 'earn' OR amount_points > 0); CHECK (entry_type NOT IN ('redeem','expire') OR amount_points < 0)
balance_after_points	integer	NOT NULL	—	Per-account running balance snapshot at time of insert. CHECK (balance_after_points >= 0) — authoritative non-negative enforcement.
sale_id	UUID	nullable	—	FK → pos.sale — THE SEAM. Set when source_type = 'sale'. NULL otherwise.
source_type	text	NOT NULL	—	CHECK (source_type IN ('sale','review','signup','birthday','manual','expiry')). For entry_type = 'redeem': use 'sale' + sale_id when redeemed at POS, or 'manual' for non-POS. For entry_type = 'reverse': 'sale' + sale_id when triggered by a voided sale, 'manual' when admin-triggered.
source_ref	text	nullable	—	Non-sale source identifier (e.g. review ID). Set when source_type ∉ {'sale','manual','expiry'}. NULL when sale_id is used.
reward_option_id	UUID	nullable	—	FK → rewards.reward_option. Required when entry_type = 'redeem'. CHECK (entry_type <> 'redeem' OR reward_option_id IS NOT NULL)
earning_rule_id	UUID	nullable	—	FK → rewards.earning_rule. Set when the earn was produced by a specific earning rule (for audit/analytics). NULL for base-rate earns and non-earn entries.
expires_at	timestamptz	nullable	—	Per-earn-row expiry. Set for entry_type = 'earn' when expiry_policy_type = 'rolling_months'. NULL for other entry types and 'never'/'calendar_year' programs.
expired_from_ledger_id	UUID	nullable	—	FK → rewards.points_ledger. Set for entry_type = 'expire' — references the earn row whose points are expiring. CHECK (entry_type <> 'expire' OR expired_from_ledger_id IS NOT NULL)
reversed_ledger_id	UUID	nullable	—	FK → rewards.points_ledger. Set for entry_type = 'reverse' — references the entry being reversed. CHECK (entry_type <> 'reverse' OR reversed_ledger_id IS NOT NULL)
note	text	nullable	—	Required for entry_type = 'adjust' (manual reason). Optional for others. CHECK (entry_type <> 'adjust' OR note IS NOT NULL)
created_by_user_id	UUID	nullable	—	FK → identity.identity_user. Set for entry_type = 'adjust' (staff-initiated manual adjustment).

Constraints:

• CHECK (balance_after_points >= 0)
• CHECK (entry_type <> 'earn' OR amount_points > 0)
• CHECK (entry_type NOT IN ('redeem','expire') OR amount_points < 0)
• CHECK (entry_type <> 'redeem' OR reward_option_id IS NOT NULL)
• CHECK (entry_type <> 'expire' OR expired_from_ledger_id IS NOT NULL)
• CHECK (entry_type <> 'reverse' OR reversed_ledger_id IS NOT NULL)
• CHECK (entry_type <> 'adjust' OR note IS NOT NULL)
• Source coherence: CHECK ((source_type = 'sale' AND sale_id IS NOT NULL AND source_ref IS NULL) OR (source_type IN ('manual','expiry') AND sale_id IS NULL) OR (source_type NOT IN ('sale','manual','expiry') AND source_ref IS NOT NULL AND sale_id IS NULL))
• CHECK (entry_type <> 'expire' OR source_type = 'expiry')
• CHECK (source_type <> 'expiry' OR entry_type = 'expire')

Indexes:

• PK on id
• on (loyalty_account_id, created_at) — account history reads + reconciliation (SUM(amount_points))
• on (sale_id) WHERE sale_id IS NOT NULL — reverse-lookup: "what ledger entries came from this sale?"
• on (expires_at) WHERE expires_at IS NOT NULL AND entry_type = 'earn' — expiry sweep: find earn rows approaching expiry
• on (reversed_ledger_id) WHERE reversed_ledger_id IS NOT NULL — check if an entry has already been reversed (prevent double-reversal)

---

Column counts: loyalty_program(12) + earning_rule(17) + tier_level(12) + reward_option(17) + loyalty_account(14) + points_ledger(17) = 89

---

Deferred items

Item	Deferred to
Referral rewards (loyalty_referral)	Future — friend-invite earn mechanics
Missions / challenges / badges / gamification	Future — Consumer App / Engagement phase
Household / family account pooling	Future — one account per consumer per tenant v1; household grouping is a separate relationship
Full tier-transition history (loyalty_tier_history)	Future — v1 uses current_tier_level_id + tier_achieved_at (history-lite)
Per-lot FIFO expiry (points_bucket)	Future — v1 uses points_ledger.expires_at (per-earn-row expiry is sufficient for rolling/inactivity policies); exact-FIFO lot tracking deferred
Multiple programs per tenant	Future — one-program-per-tenant v1; relax for seasonal / wholesale / VIP programs (remove partial unique, add program_type discriminator)
Platform-wide pooled points	Future query / view over per-tenant balances — not a schema change; per-business schema keeps the door open
Stub-points holding table	NONE — no stub accounts; historical points credited at claim via ConsumerService batch earn
Consent / opt-in table	NONE — reads crm.customer_consent (consent_type = 'loyalty'); zero consent tables in rewards
Offers / promos / coupon codes / Vrida-funded discounts	Separate offers module — different mechanics and settlement economics
sale_line-level earn granularity	Future — v1 earns on sale total; per-line earn (different rates per product category per line) uses earning_rule with rule_scope = 'item_category' at service layer; per-line ledger rows are a later refinement
Stub / self-signup email-collision merge path	Service-layer design detail — ConsumerService must detect unclaimed stubs at claim time and execute merge before activating. Mechanics to resolve at consumer-phase build.

---