rewards — Module Spec

1. Purpose

rewards is per-business loyalty — module #24, the Consumer Layer's second real v2 build, schema-locked 2026-07-11 (superseding a stale v1-carryover placeholder count that was never a real v2 build). Points, tiers, earning rules, a redemption catalog, and an append-only ledger. Fully tenant-scoped — consumer_id is a dimension on every table, never the RLS scope (the structural inverse of consumer.consumer_merchant_link). See PROJECT_DECISIONS #56. Reopened same day (2026-07-11) to fix a bug that hard-rejected genuine partial reversals and to add proportional/partial reversal as a first-class capability; relocked — see PROJECT_DECISIONS #60.

2. Ownership

Owns — 7 tables, 109 columns:

Table	Cols	Role
loyalty_program	14	Program definition — points_rounding, status lifecycle
loyalty_accrual_rule	22	Earning rules (multiplier/flat-bonus)
loyalty_reward_tier	12	Tier thresholds
reward_option	27	Points-cost redemption catalog
loyalty_account	14	One per consumer×program — balance_points/lifetime_points, both CHECK-guarded non-negative
loyalty_point_ledger	22	Append-only — every earn/redeem/adjust/expiry/reverse entry, sale_id the real POS seam
loyalty_point_ledger_reversal_tracker	7	NEW (2026-07-11 reopen) — lazily-created cumulative-reversal-cap counter per ledger row; mutable, tenant-scoped RLS, UNIQUE(tenant_id, ledger_id)
Total	109

Does NOT own: promo/coupon issuance (offers — see the reward_option vs offers boundary GUARD in CROSS_MODULE_CONTRACTS.md); the consumer identity itself (consumer.consumer — consumer_id is a plain FK dimension, not composite, since consumer.consumer is non-tenant-scoped).

3. Layer & Dependencies

Second Consumer Layer module, closing v1's pos.sale.points seam for real. Depends on: platform (tenant), consumer (consumer.id), pos (sale.id, via a new composite FK riding this build's own pos.sale UNIQUE(id, tenant_id) prerequisite), identity (actor, full autonomy-pack attribution). Depended on by: consumer.get_cross_tenant_activity() (a future ConsumerService read path).

4. Capabilities — honest Part D framing

Points issuance (entry_type='earn') is deterministic, sale-triggered. Manual adjustments (entry_type='adjust') are human-only in practice (chk_loyalty_point_ledger_adjust_requires_note requires a documented reason) but carry the standard autonomy pack for future agent-drafted correction proposals. No agent ever writes a ledger row directly today — schema-only, no RewardsService yet.

5. Service Contract — RewardsService

Not built this pass. Binding future requirement: RewardsService.earn()/.redeem() are the only sanctioned ways to insert a loyalty_point_ledger row; no caller may construct one directly. The atomic sync trigger (below) is the backstop, not a substitute for going through the service.

6. The Concurrency-Race + Proportional-Reversal Fix (this build's central guard)

rewards.sync_loyalty_account_balance() is a single atomic BEFORE INSERT trigger whose own UPDATE rewards.loyalty_account ... RETURNING balance_after_points takes a row lock, serializing concurrent redemption transactions. Live-reproduced, naive vs. fixed: a naive two-trigger (BEFORE-check / AFTER-sync) shape let a 100-point balance overshoot to -60 under 2 concurrent -80-point redemptions; the fixed single-trigger shape correctly resolved 3-of-5 concurrent -30-point redemptions against the same 100-point balance to exactly 10, rejecting the other 2 with insufficient points balance. chk_loyalty_point_ledger_balance_after_points_nonneg is the independent CHECK-layer backstop — even a hypothetical direct INSERT bypassing the trigger would still be rejected.

Proportional/partial reversal (2026-07-11 reopen, PROJECT_DECISIONS #60). The trigger originally hard-enforced exact full negation only — a genuine partial reversal (e.g. return 2 of 5 units) was rejected outright. This is now a fixed bug and a supported first-class capability: entry_type='reverse' is gated by sign(original_amount_points) = -1 * sign(NEW.amount_points), which generalizes across earn(+)/redeem(-)/adjust(+/-) with no per-type special-casing, and any magnitude up to the original is accepted. The cumulative cap is enforced by a new, lazily-created (INSERT ... ON CONFLICT DO NOTHING) tracker table, loyalty_point_ledger_reversal_tracker, via the same atomic row-locking pattern this codebase already uses for budget caps (see offers.check_and_sync_offer_budget(), §6 there): a single UPDATE tracker SET total_reversed_points = total_reversed_points + :amt WHERE ... AND total_reversed_points + :amt <= original_amount_points RETURNING ... — not a read-then-check-then-write, which is the exact race this codebase already found and fixed once before. chk_loyalty_point_ledger_reverse_nonzero is a CHECK-layer backstop against zero-magnitude reversals (in practice the sign-match logic already rejects these first). Live-reproduced, full sequential + concurrency proof: partial reversal succeeds; a cumulative-exceeding reversal is rejected; the exact remainder succeeds and zeroes the balance; further reversal against an exhausted original is rejected; wrong-sign and reverse-of-reverse are both rejected; 3 concurrent -200 reversals against one +500 earn (real backgrounded psql processes) resolved exactly 2-succeed/1-reject (cumulative 400), tracker and balance both reconciled correctly. A standalone naive read-then-check-then-write shape was independently proven to over-reverse to 600 under the identical concurrent load, confirming the atomic shape is load-bearing, not redundant. Why a separate tracker table, not a column on the ledger: loyalty_point_ledger is append-only via platform.reject_append_only_mutation() — confirmed via pg_get_functiondef to be an unconditional RAISE EXCEPTION, no column-level exception — so a maintained counter column directly on the ledger row would hit that trigger on its first UPDATE.

7. Design Rationale

• Append-only ledger, mutable summary account. loyalty_point_ledger is never UPDATEd/DELETEd (REVOKE + platform.reject_append_only_mutation()); loyalty_account.balance_points/lifetime_points are the maintained, trigger-derived summary — matches the header/line-remediation-era convention elsewhere in this codebase (e.g. subscription_invoice_line syncing its own header).
• consumer_id a plain FK, not composite. consumer.consumer is non-tenant-scoped by design; a composite (consumer_id, tenant_id) target doesn't exist to reference.
• reward_option vs offers, never merged. Points-funded redemption stays here; promo/coupon issuance (including Vrida-funded economics) stays in offers — a service-layer integration point, never a schema merge.

8. Agent Authority Mapping

No new authority mechanism — pure consumer of identity.agent_duty_grant. Every *_actor_id column across the 6 original tables → identity.actor.id. The new loyalty_point_ledger_reversal_tracker (7th table, 2026-07-11 reopen) carries no *_actor_id column at all — it's a pure numeric cumulative-cap counter, tenant-scoped RLS only, no autonomy pack.

9. Cross-Module Seams

• rewards → pos: loyalty_point_ledger.sale_id → pos.sale(id, tenant_id) (composite, nullable, real — riding the new pos.sale UNIQUE(id, tenant_id) prerequisite this build added). sale_id is required when source_type='sale' (CHECK-enforced), NULL for manual/expiry entries.
• rewards → consumer: loyalty_account.consumer_id → consumer.consumer.id (plain FK).
• rewards → platform: *.tenant_id → platform.tenant.id across all 7 tables — the RLS scope.
• rewards internal (new, 2026-07-11): loyalty_point_ledger_reversal_tracker.ledger_id → loyalty_point_ledger(id, tenant_id) composite FK, UNIQUE(tenant_id, ledger_id) enforcing one tracker row per original ledger entry.
• identity → rewards: every *_actor_id → identity.actor.id.
• consumer → rewards (deferred read): consumer.get_cross_tenant_activity() will union loyalty_account balances across every tenant a consumer is linked to, once ConsumerService exists.

10. Deferred / Future Items

RewardsService (the HTTP controller layer) — the next build step, including the actual POSService.completeSale() → RewardsService.earn() call this schema anticipates but does not yet wire. Unclaimed-stub point handling and reward-expiry mechanics (calendar-year vs. rolling vs. inactivity-based) remain undecided — see PROJECT_DECISIONS #56.

11. v1 Exclusions Re-Confirmed

None disclosed as dropped — the 7-table shape (up from 6, +1 for the new proportional-reversal cumulative-cap tracker added in the 2026-07-11 same-day reopen, PROJECT_DECISIONS #60) preserves v1's program/rule/tier/catalog/account/ledger structure in full, with the ledger's sale_id seam now a real, DB-enforced composite FK rather than a forward-ref.