rewards — new module (2026-07-11), per-merchant points/loyalty program

7 tables, 109 columns (up from 6/102) — schema built and migration applied/verified live 2026-07-11; locked the same day (PROJECT_DECISIONS #56), then reopened again later the same day (still 2026-07-11) to fix a real bug — rewards.sync_loyalty_account_balance() had hard-enforced exact, full negation only on a reverse entry, rejecting a genuine partial return (e.g. 2 of 5 units) outright — and to add proportional/cumulative-cap partial reversal as a first-class capability, via a new mutable tracker table, loyalty_point_ledger_reversal_tracker (PROJECT_DECISIONS #60); re-locked same day, still schema-only. rewards is the per-merchant points/loyalty engine — programs, accrual rules, reward tiers, redeemable reward options, per-consumer accounts, the point ledger, and (new) a per-ledger-entry reversal tracker. Ledger-first: loyalty_point_ledger is the append-only source of truth; loyalty_account.balance_points/.lifetime_points are genuinely trigger-maintained caches (rewards.sync_loyalty_account_balance(), written into the migration), not just a documented-as-a-formula convention. Tenant-scoped throughout — consumer_id is an ordinary FK dimension here, never the RLS scope, the structural inverse of consumer.consumer_merchant_link (which is consumer-scoped with tenant_id as its own dimension). No AI features are built this pass, but every table that plausibly could be agent-proposed later already carries the standard autonomy pack (automation_source/review-status quintet), so a future AI pass needs no reopen.

Depends on platform (tenant), multi_loc (site — loyalty_program's own site-scoping), identity (actor — reviewed_by_actor_id/created_by_actor_id), consumer (consumer.consumer — bare FK, non-tenant-scoped target), and pos (sale — composite FK on loyalty_point_ledger.sale_id; requires pos.sale's own UNIQUE (id, tenant_id), added as a prerequisite by this same migration, not deferred).

PROJECT_DECISIONS entry: #56 (original 2026-07-11 build + lock) and #60 (same-day reopen — proportional/partial reversal fix, adding loyalty_point_ledger_reversal_tracker).

Global rules for this schema:

• Uniform tenant-scoping — all 7 tables carry tenant_id NOT NULL FK → platform.tenant, standard RLS <table>_tenant_isolation policy, FOR ALL TO authenticated, USING/WITH CHECK both tenant_id = current_setting('app.current_tenant_id')::uuid. Plain index on tenant_id on all 7.
• updated_at trigger-maintained via platform.set_updated_at() on 6 of 7 tables (loyalty_program, loyalty_accrual_rule, loyalty_reward_tier, reward_option, loyalty_account, and — added by the same-day reopen — loyalty_point_ledger_reversal_tracker). Not on loyalty_point_ledger — an append-only ledger fact with no updated_at column at all.
• Soft delete (deleted_at) on the same original 5 tables. Not on loyalty_point_ledger (append-only ledger fact) nor on the new loyalty_point_ledger_reversal_tracker (a small mutable counter row with no lifecycle to soft-delete).
• Append-only, 1 table: loyalty_point_ledger — REVOKE UPDATE, DELETE FROM authenticated + trigger trg_loyalty_point_ledger_append_only, reusing platform.reject_append_only_mutation() verbatim. PK default platform.uuid_generate_v7() (time-ordered, matching this codebase's append-only-ledger PK convention). The new loyalty_point_ledger_reversal_tracker is deliberately the opposite — genuinely mutable — precisely because the ledger itself cannot host a maintained running-total column (see below).
• Agent-as-actor — every *_actor_id targets identity.actor.
• Autonomy tiers, non-uniform by design. loyalty_accrual_rule and reward_option get a 5-column review seam (automation_source/review_status/review_reason/reviewed_by_actor_id/reviewed_at) but no decision_provenance and no created_by_actor_id. loyalty_program and loyalty_reward_tier get no autonomy columns at all — static program/catalog config, mirroring pricing.price_level's own "no autonomy columns" precedent. loyalty_account also gets no autonomy pack — a maintained balance record, not itself an agent-actionable decision surface; every balance change flows exclusively through loyalty_point_ledger, which DOES carry automation_source + created_by_actor_id. The new loyalty_point_ledger_reversal_tracker likewise gets no autonomy pack — a maintained cumulative-cap counter row, not itself an agent-actionable surface.
• THE ATOMIC SYNC TRIGGER — rewards.sync_loyalty_account_balance(), BEFORE INSERT ON loyalty_point_ledger, closes a real concurrency race the design's own verification found: a single atomic statement whose own UPDATE ... RETURNING ... INTO NEW.balance_after_points takes a row lock on loyalty_account, serializing concurrent writers — NOT a two-trigger (BEFORE-check / AFTER-sync) shape, which is exploitable (a second concurrent insert can read a stale pre-commit balance in the gap between the two triggers). Reopened the same day (PROJECT_DECISIONS #60) to replace an exact-full-negation-only reversal rule (a reverse entry was rejected unless it zeroed out its original exactly) with genuine proportional/cumulative-cap partial reversal: a reverse entry lazily creates a row in the new loyalty_point_ledger_reversal_tracker table snapshotting its original entry's magnitude (ON CONFLICT (tenant_id, ledger_id) DO NOTHING, so a 2nd+ partial reversal reuses the same row), then a single atomic UPDATE ... WHERE total_reversed_points + abs(:amt) <= abs(original_amount_points) ... RETURNING both caps and increments the cumulative total in one row-locking statement — a concurrent second reversal against the same original blocks on that row's lock and re-evaluates against the true post-serialization total, never a stale read-then-check-then-write race. See "The atomic sync trigger" below for the full mechanism and code.
• Composite (col, tenant_id) FK convention, used for every intra-rewards and rewards → pos FK (loyalty_program_id, sale_id, reward_option_id, loyalty_accrual_rule_id, current_tier_level_id, tier_level_id, expired_from_ledger_id, reversed_ledger_id — the last 2 self-referencing on loyalty_point_ledger itself — plus, added by the same-day reopen, loyalty_point_ledger_reversal_tracker.ledger_id). consumer_id stays a bare FK → consumer.consumer throughout — consumer.consumer is non-tenant-scoped, so no composite FK is possible or needed (the reverse direction from every other FK in this module).

Cross-Phase / Cross-Module Foreign Keys (rewards)

Column	Target	Notes
*.tenant_id (all 7 tables)	platform.tenant	NOT NULL
loyalty_program.site_id	multi_loc.site (id, tenant_id)	nullable, composite FK loyalty_program_site_tenant_fkey
loyalty_accrual_rule.loyalty_program_id, loyalty_reward_tier.loyalty_program_id, reward_option.loyalty_program_id, loyalty_account.loyalty_program_id	rewards.loyalty_program (id, tenant_id)	NOT NULL, composite
reward_option.tier_level_id, loyalty_account.current_tier_level_id	rewards.loyalty_reward_tier (id, tenant_id)	nullable, composite
loyalty_account.consumer_id	consumer.consumer.id	NOT NULL, bare — non-tenant-scoped target, no composite possible
loyalty_point_ledger.loyalty_account_id	rewards.loyalty_account (id, tenant_id)	NOT NULL, composite
loyalty_point_ledger.sale_id	pos.sale (id, tenant_id)	nullable, composite. Required pos.sale's own UNIQUE (id, tenant_id) — added by this same migration (sale_id_tenant_id_unique, Block 4 dependency resolved, not deferred; 486 live rows confirmed at migration time, zero-risk regardless of row count since id was already the sole PK)
loyalty_point_ledger.reward_option_id	rewards.reward_option (id, tenant_id)	nullable, composite
loyalty_point_ledger.loyalty_accrual_rule_id	rewards.loyalty_accrual_rule (id, tenant_id)	nullable, composite
loyalty_point_ledger.expired_from_ledger_id, .reversed_ledger_id	rewards.loyalty_point_ledger (id, tenant_id)	nullable, self-referencing composite
loyalty_point_ledger_reversal_tracker.ledger_id	rewards.loyalty_point_ledger (id, tenant_id)	NOT NULL, composite. NEW (same-day reopen, PROJECT_DECISIONS #60). UNIQUE (tenant_id, ledger_id) — one tracker row per original entry, also the ON CONFLICT target for the lazy-create insert
loyalty_accrual_rule.reviewed_by_actor_id, reward_option.reviewed_by_actor_id, loyalty_point_ledger.created_by_actor_id	identity.actor	nullable, bare (codebase-wide convention for actor FKs)

---

rewards.loyalty_program (14 cols) — the program config

Renamed from v1's loyalty_program (siblings likewise renamed: earning_rule → loyalty_accrual_rule, tier_level → loyalty_reward_tier, points_ledger → loyalty_point_ledger). v1's 12 columns preserved + 2 new: site_id (nullable) + applies_all_sites, reusing pricing.price_rule's own site-scoping shape. Extends v1's "one program per tenant" uniqueness to: one all-sites program per tenant, OR one program per (tenant, site).

Tenant-scoped. RLS enabled — loyalty_program_tenant_isolation. Soft delete: deleted_at. updated_at: trigger-maintained via platform.set_updated_at(). No autonomy columns — static config.

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained
deleted_at	timestamptz	nullable	—	Soft delete
name	text	NOT NULL	—
status	text	NOT NULL	'active'	CHECK IN (active,paused,archived)
points_per_dollar	numeric	NOT NULL	1
points_rounding	text	NOT NULL	'floor'	CHECK IN (floor,round,ceil)
min_redeem_points	integer	NOT NULL	0
expiry_policy_type	text	NOT NULL	'never'	CHECK IN (never,rolling_months,calendar_year,inactivity_months)
expiry_policy_value	integer	nullable	—	Required IFF expiry_policy_type IN ('rolling_months','inactivity_months')
site_id	UUID	nullable	—	Composite FK → multi_loc.site
applies_all_sites	boolean	NOT NULL	false	XOR with site_id

CHECK constraints (6): chk_loyalty_program_status; chk_loyalty_program_points_rounding; chk_loyalty_program_expiry_policy_type; chk_loyalty_program_expiry_policy_value (coherence between expiry_policy_type and expiry_policy_value); chk_loyalty_program_applies_all_sites_xor_site_id (applies_all_sites = false OR site_id IS NULL).

Indexes (6): PK on id; loyalty_program_id_tenant_id_unique (UNIQUE, id+tenant_id — prerequisite for every child table's composite FK); loyalty_program_tenant_id_idx; loyalty_program_tenant_id_site_id_unique (UNIQUE, WHERE deleted_at IS NULL AND site_id IS NOT NULL); loyalty_program_tenant_id_all_sites_unique (UNIQUE, WHERE deleted_at IS NULL AND applies_all_sites = true); loyalty_program_tenant_id_status_idx (partial).

---

rewards.loyalty_accrual_rule (22 cols) — how points are earned

Renamed from v1's earning_rule. All 17 v1 columns preserved verbatim + 5 net-new: the review-seam quintet (automation_source/review_status/review_reason/reviewed_by_actor_id/reviewed_at).

Tenant-scoped. RLS enabled — loyalty_accrual_rule_tenant_isolation. Soft delete: deleted_at. updated_at: trigger-maintained via platform.set_updated_at(). Autonomy: 5-column review seam only (no decision_provenance, no created_by_actor_id).

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained
deleted_at	timestamptz	nullable	—	Soft delete
loyalty_program_id	UUID	NOT NULL	—	Composite FK → rewards.loyalty_program
name	text	NOT NULL	—
rule_scope	text	NOT NULL	—	CHECK IN 8 values (purchase,item_category,item_variant,sale_threshold,birthday,signup,review,manual)
scope_ref	text	nullable	—	Loose ref — category code or item_variant sku/slug, validated at earn time
earn_type	text	NOT NULL	—	CHECK IN (multiplier,flat_bonus)
multiplier	numeric	nullable	—	Required IFF earn_type='multiplier'
flat_bonus_points	integer	nullable	—	Required IFF earn_type='flat_bonus'
threshold_cents	bigint	nullable	—	Required IFF rule_scope='sale_threshold'
priority	integer	NOT NULL	0
is_active	boolean	NOT NULL	true
active_from	timestamptz	nullable	—
active_to	timestamptz	nullable	—
automation_source	text	NOT NULL	'human'	CHECK IN (human,agent,system,seed)
review_status	text	NOT NULL	'not_required'	CHECK IN (not_required,pending,approved,rejected)
review_reason	text	nullable	—
reviewed_by_actor_id	UUID	nullable	—	FK → identity.actor
reviewed_at	timestamptz	nullable	—

CHECK constraints (8): chk_loyalty_accrual_rule_rule_scope; chk_loyalty_accrual_rule_earn_type; chk_loyalty_accrual_rule_earn_type_coherence; chk_loyalty_accrual_rule_threshold_coherence; chk_loyalty_accrual_rule_threshold_no_scope_ref (rule_scope != 'sale_threshold' OR scope_ref IS NULL); chk_loyalty_accrual_rule_automation_source; chk_loyalty_accrual_rule_review_status.

Indexes (4): PK on id; loyalty_accrual_rule_id_tenant_id_unique (UNIQUE); loyalty_accrual_rule_tenant_id_idx; loyalty_accrual_rule_program_scope_idx (partial); loyalty_accrual_rule_scope_ref_idx (partial).

---

rewards.loyalty_reward_tier (12 cols) — static tier catalog

Renamed from v1's tier_level, unchanged. Static catalog, no autonomy pack (matches loyalty_program/pricing.price_level).

Tenant-scoped. RLS enabled — loyalty_reward_tier_tenant_isolation. Soft delete: deleted_at. updated_at: trigger-maintained via platform.set_updated_at().

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained
deleted_at	timestamptz	nullable	—	Soft delete
loyalty_program_id	UUID	NOT NULL	—	Composite FK → rewards.loyalty_program
tier_code	text	NOT NULL	—
name	text	NOT NULL	—
min_points	integer	NOT NULL	—	CHECK >= 0
sort_order	integer	NOT NULL	—
earn_multiplier	numeric	nullable	—
benefit_label	text	nullable	—

CHECK constraints (1): chk_loyalty_reward_tier_min_points_nonneg.

Indexes (5): PK on id; loyalty_reward_tier_id_tenant_id_unique (UNIQUE); loyalty_reward_tier_tenant_id_idx; loyalty_reward_tier_program_tier_code_unique (UNIQUE, WHERE deleted_at IS NULL); loyalty_reward_tier_program_min_points_unique (UNIQUE, WHERE deleted_at IS NULL); loyalty_reward_tier_program_min_points_idx.

---

rewards.reward_option (22 cols) — redeemable catalog

v1's 17 columns unchanged + the same 5-column autonomy pack as loyalty_accrual_rule.

Tenant-scoped. RLS enabled — reward_option_tenant_isolation. Soft delete: deleted_at. updated_at: trigger-maintained via platform.set_updated_at(). Autonomy: 5-column review seam only.

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained
deleted_at	timestamptz	nullable	—	Soft delete
loyalty_program_id	UUID	NOT NULL	—	Composite FK → rewards.loyalty_program
name	text	NOT NULL	—
reward_type	text	NOT NULL	—	CHECK IN (discount_amount,discount_percent,free_item,perk)
points_cost	integer	NOT NULL	—	CHECK > 0
discount_amount_cents	bigint	nullable	—	Required IFF reward_type='discount_amount'
discount_percent	numeric	nullable	—	Required IFF reward_type='discount_percent'. CHECK range (0, 100]
free_item_variant_ref	text	nullable	—	Loose ref — inventory.item_variant sku/slug, validated at redemption time. Required IFF reward_type='free_item'
min_purchase_cents	bigint	nullable	—
tier_level_id	UUID	nullable	—	Composite FK → rewards.loyalty_reward_tier
is_active	boolean	NOT NULL	true
active_from	timestamptz	nullable	—
active_to	timestamptz	nullable	—
automation_source	text	NOT NULL	'human'	CHECK IN (human,agent,system,seed)
review_status	text	NOT NULL	'not_required'	CHECK IN (not_required,pending,approved,rejected)
review_reason	text	nullable	—
reviewed_by_actor_id	UUID	nullable	—	FK → identity.actor
reviewed_at	timestamptz	nullable	—

CHECK constraints (7): chk_reward_option_reward_type; chk_reward_option_reward_type_coherence (4-branch, one column set required per reward_type, others forbidden); chk_reward_option_points_cost_positive; chk_reward_option_discount_percent_range; chk_reward_option_automation_source; chk_reward_option_review_status.

Indexes (4): PK on id; reward_option_id_tenant_id_unique (UNIQUE); reward_option_tenant_id_idx; reward_option_program_active_idx (partial); reward_option_program_tier_idx (partial).

---

rewards.loyalty_account (14 cols) — the per-(consumer, tenant) account

v1 unchanged. balance_points/lifetime_points are genuinely trigger-maintained (see loyalty_point_ledger's own sync trigger below), not just documented as a formula. consumer_id NOT NULL = claimed consumers only (no stub accounts, matching v1's own "no stub-accounts" design principle).

Tenant-scoped. RLS enabled — loyalty_account_tenant_isolation. Soft delete: deleted_at. updated_at: trigger-maintained via platform.set_updated_at(). No autonomy pack — a maintained balance record, not an agent-actionable surface.

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained
deleted_at	timestamptz	nullable	—	Soft delete
loyalty_program_id	UUID	NOT NULL	—	Composite FK → rewards.loyalty_program
consumer_id	UUID	NOT NULL	—	FK → consumer.consumer (bare)
balance_points	integer	NOT NULL	0	CHECK >= 0 — defensive backstop; the real, atomic, row-locking enforcement point is loyalty_point_ledger's own sync trigger
lifetime_points	integer	NOT NULL	0	CHECK >= 0
current_tier_level_id	UUID	nullable	—	Composite FK → rewards.loyalty_reward_tier
tier_achieved_at	timestamptz	nullable	—
enrolled_at	timestamptz	NOT NULL	now()
last_earn_at	timestamptz	nullable	—
last_redeem_at	timestamptz	nullable	—

CHECK constraints (2): chk_loyalty_account_balance_points_nonneg; chk_loyalty_account_lifetime_points_nonneg.

Indexes (6): PK on id; loyalty_account_id_tenant_id_unique (UNIQUE — prerequisite for loyalty_point_ledger's composite FK); loyalty_account_tenant_id_idx; loyalty_account_tenant_id_consumer_id_unique (UNIQUE, WHERE deleted_at IS NULL); loyalty_account_consumer_id_idx; loyalty_account_program_tier_idx; loyalty_account_last_earn_at_idx (partial).

---

rewards.loyalty_point_ledger (18 cols) — append-only, THE source of truth

Renamed from v1's points_ledger. v1's 17 columns preserved (1 retargeted: created_by_user_id → created_by_actor_id) + automation_source = 18. entry_type CHECK covers v1's real 5 values (earn/redeem/expire/adjust/reverse) + promo_bonus (genuinely new). balance_after_points >= 0 is kept as a defensive backstop alongside the sync trigger, never dropped (mirrors receiving.goods_receipt_line's own precedent of keeping a CHECK backstop alongside trigger enforcement).

Tenant-scoped, append-only. RLS enabled — loyalty_point_ledger_tenant_isolation. REVOKE UPDATE, DELETE FROM authenticated + trigger trg_loyalty_point_ledger_append_only. No updated_at, no soft delete.

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	platform.uuid_generate_v7()	PK — append-only PK convention
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
loyalty_account_id	UUID	NOT NULL	—	Composite FK → rewards.loyalty_account
created_at	timestamptz	NOT NULL	now()
entry_type	text	NOT NULL	—	CHECK IN (earn,redeem,expire,adjust,reverse,promo_bonus)
amount_points	integer	NOT NULL	—	Sign enforced per entry_type — see CHECKs
balance_after_points	integer	NOT NULL	—	Trigger-derived via UPDATE ... RETURNING ... INTO NEW.balance_after_points — never caller-supplied
sale_id	UUID	nullable	—	Composite FK → pos.sale
source_type	text	NOT NULL	—	CHECK IN (sale,review,signup,birthday,manual,expiry)
source_ref	text	nullable	—	Loose ref — non-sale source identifier
reward_option_id	UUID	nullable	—	Composite FK → rewards.reward_option
loyalty_accrual_rule_id	UUID	nullable	—	Composite FK → rewards.loyalty_accrual_rule
expires_at	timestamptz	nullable	—
expired_from_ledger_id	UUID	nullable	—	Self-referencing composite FK
reversed_ledger_id	UUID	nullable	—	Self-referencing composite FK
note	text	nullable	—	Required IFF entry_type='adjust'
created_by_actor_id	UUID	nullable	—	FK → identity.actor
automation_source	text	NOT NULL	'human'	CHECK IN (human,agent,system,seed)

CHECK constraints (14, up from 13 — the same-day reopen added the 14th): chk_loyalty_point_ledger_balance_after_points_nonneg; chk_loyalty_point_ledger_entry_type; chk_loyalty_point_ledger_earn_positive (entry_type NOT IN ('earn','promo_bonus') OR amount_points > 0); chk_loyalty_point_ledger_redeem_expire_negative (entry_type NOT IN ('redeem','expire') OR amount_points < 0); chk_loyalty_point_ledger_redeem_requires_reward_option; chk_loyalty_point_ledger_expire_requires_source; chk_loyalty_point_ledger_reverse_requires_source; chk_loyalty_point_ledger_adjust_requires_note; chk_loyalty_point_ledger_source_type; chk_loyalty_point_ledger_source_coherence (3-branch — sale requires sale_id+no source_ref; manual/expiry require no sale_id; every other source_type requires source_ref+no sale_id); chk_loyalty_point_ledger_expire_source_type; chk_loyalty_point_ledger_expiry_source_entry_type; chk_loyalty_point_ledger_automation_source; chk_loyalty_point_ledger_reverse_nonzero (entry_type != 'reverse' OR amount_points != 0, added by the same-day reopen, PROJECT_DECISIONS #60 — a belt-and-suspenders backstop; in practice sync_loyalty_account_balance()'s own sign-match logic already rejects a zero-magnitude reversal first).

Indexes (5): PK on id; loyalty_point_ledger_id_tenant_id_unique (UNIQUE — prerequisite for the self-referencing composite FKs); loyalty_point_ledger_account_created_at_idx; loyalty_point_ledger_sale_id_idx (partial); loyalty_point_ledger_expires_at_idx (partial, WHERE entry_type = 'earn'); loyalty_point_ledger_reversed_ledger_id_idx (partial).

---

rewards.loyalty_point_ledger_reversal_tracker (7 cols) — cumulative-reversed-amount tracker, added by the 2026-07-11 same-day reopen

New table (PROJECT_DECISIONS #60), not part of the original 2026-07-11 build. A small, genuinely mutable counter row per original loyalty_point_ledger entry that has ever been (partially or fully) reversed — lazily created on first touch (INSERT ... ON CONFLICT (tenant_id, ledger_id) DO NOTHING, snapshotting the original's own amount_points), then atomically capped on every subsequent reversal by rewards.sync_loyalty_account_balance()'s own UPDATE ... WHERE total_reversed_points + abs(:amt) <= abs(original_amount_points) ... RETURNING. Exists as a separate table, not a maintained column on loyalty_point_ledger itself, because that ledger is append-only via platform.reject_append_only_mutation() — confirmed via pg_get_functiondef to be an unconditional RAISE EXCEPTION with no column-level exception — so a running-total column directly on the ledger would hit that trigger on its very first UPDATE. Mirrors offers.offer.budget_used_cents/.redemption_count's own established "maintained counter on a mutable row" precedent, generalized to a case with no natural existing header to hold it.

Tenant-scoped. RLS enabled — loyalty_point_ledger_reversal_tracker_tenant_isolation. updated_at: trigger-maintained via platform.set_updated_at(). No soft delete — a small mutable counter row, not a lifecycle entity. No autonomy pack.

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
ledger_id	UUID	NOT NULL	—	Composite FK → rewards.loyalty_point_ledger — the original entry being (partially) reversed
original_amount_points	integer	NOT NULL	—	Snapshotted from the original ledger row's own amount_points on first touch (lazy-create, ON CONFLICT (tenant_id, ledger_id) DO NOTHING)
total_reversed_points	integer	NOT NULL	0	Cumulative running tally across every reverse entry against this original, atomically capped in the same statement that increments it
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained

CHECK constraints (1): chk_loyalty_point_ledger_reversal_tracker_total_reversed_bounds (total_reversed_points >= 0 AND total_reversed_points <= abs(original_amount_points)) — a defensive backstop; the real, atomic enforcement point is sync_loyalty_account_balance()'s own capped UPDATE (see "The atomic sync trigger" below), mirroring this codebase's established convention of keeping a CHECK backstop alongside trigger enforcement.

Indexes (2): PK on id; loyalty_point_ledger_reversal_tracker_tenant_ledger_unique (UNIQUE, tenant_id+ledger_id — one tracker row per original entry, and the ON CONFLICT target for the lazy-create insert); loyalty_point_ledger_reversal_tracker_tenant_id_idx.

---

The atomic sync trigger

rewards.sync_loyalty_account_balance() — the mechanism keeping loyalty_account.balance_points/.lifetime_points genuinely in sync with loyalty_point_ledger, not just documented as a formula. A single atomic BEFORE INSERT statement: its own UPDATE takes a row lock on the target loyalty_account row, so a concurrent second transaction's identical UPDATE blocks until the first commits or rolls back — NEW.balance_after_points always reflects the true post-serialization value, never a stale snapshot. This closes a real concurrency race the design's own independent verification found in an earlier two-trigger (BEFORE-check / AFTER-sync) draft, where two concurrent redemptions could both read the same pre-commit balance and both pass a sufficient-balance check that only one of them should have passed.

Reopened the same day (PROJECT_DECISIONS #60) — proportional/cumulative-cap partial reversal. The original build's version of this function hard-enforced exact, full negation only on a reverse entry: reverse ALL of an earn's points or NONE, rejecting a genuine partial return (e.g. 2 of 5 units) outright. The corrected function below adds a reverse-only preamble that (a) lazily creates a row in the new loyalty_point_ledger_reversal_tracker table for the original entry being reversed, snapshotting its amount_points (ON CONFLICT (tenant_id, ledger_id) DO NOTHING, so a 2nd+ partial reversal against the same original reuses the existing row — the lazy-create SELECT itself excludes entry_type='reverse' rows, so a reversal of a reversal finds no eligible original), then (b) performs a single atomic, row-locking UPDATE ... WHERE ... RETURNING that validates in one statement: the tracker row exists (the original was found and is reversible), this reversal's sign is opposite the original's own sign (sign(original_amount_points) = -1 * sign(NEW.amount_points), which generalizes across earn(+)/redeem(-)/adjust(±) originals with no per-entry-type special-casing), and the cumulative reversed total — including this one — does not exceed the original's own magnitude. Zero rows returned ⇒ reject. A concurrent second reversal against the same original blocks on that row's lock and re-evaluates against the true post-serialization total — never a stale read-then-check-then-write race (independently proven: a standalone naive read-then-check-then-write version of this same logic was shown to actually over-reverse under 3 concurrent -200 calls against a +500 cap, ending at 600; the atomic version, under the identical load, correctly stopped at exactly 400 with 1 of the 3 rejected).

CREATE OR REPLACE FUNCTION rewards.sync_loyalty_account_balance()
RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE
  v_lifetime_delta integer := CASE WHEN NEW.entry_type = 'earn' THEN NEW.amount_points ELSE 0 END;
  v_original_amount integer;
  v_rows int;
BEGIN
  IF NEW.entry_type = 'reverse' THEN
    -- Lazily create the tracker row for the original entry being reversed
    -- (snapshotting its amount_points) -- ON CONFLICT DO NOTHING so a 2nd+
    -- partial reversal against the same original reuses the existing row.
    -- The SELECT deliberately excludes entry_type='reverse' rows -- you
    -- cannot reverse a reversal.
    INSERT INTO rewards.loyalty_point_ledger_reversal_tracker (tenant_id, ledger_id, original_amount_points)
    SELECT NEW.tenant_id, NEW.reversed_ledger_id, l.amount_points
    FROM rewards.loyalty_point_ledger l
    WHERE l.id = NEW.reversed_ledger_id AND l.tenant_id = NEW.tenant_id AND l.entry_type != 'reverse'
    ON CONFLICT (tenant_id, ledger_id) DO NOTHING;

    -- Atomic cap + increment: a single row-locking UPDATE whose own WHERE
    -- clause validates (a) the tracker row exists, (b) this reversal's sign
    -- is opposite the original's sign, and (c) the cumulative reversed total
    -- (including this one) does not exceed the original's own magnitude. A
    -- concurrent second reversal against the same original blocks on this
    -- row's lock and re-evaluates against the true post-serialization total
    -- -- no read-then-check-then-write race window.
    UPDATE rewards.loyalty_point_ledger_reversal_tracker
    SET total_reversed_points = total_reversed_points + abs(NEW.amount_points)
    WHERE tenant_id = NEW.tenant_id
      AND ledger_id = NEW.reversed_ledger_id
      AND sign(original_amount_points) = -1 * sign(NEW.amount_points)
      AND total_reversed_points + abs(NEW.amount_points) <= abs(original_amount_points)
    RETURNING original_amount_points INTO v_original_amount;

    GET DIAGNOSTICS v_rows = ROW_COUNT;
    IF v_rows = 0 THEN
      RAISE EXCEPTION 'reverse entry for reversed_ledger_id % would exceed the cumulative reversible amount, has the wrong sign, references a reversal, or was not found', NEW.reversed_ledger_id;
    END IF;
  END IF;

  UPDATE rewards.loyalty_account
  SET balance_points = balance_points + NEW.amount_points,
      lifetime_points = lifetime_points + v_lifetime_delta,
      last_earn_at = CASE WHEN NEW.entry_type = 'earn' THEN now() ELSE last_earn_at END,
      last_redeem_at = CASE WHEN NEW.entry_type = 'redeem' THEN now() ELSE last_redeem_at END
  WHERE id = NEW.loyalty_account_id
  RETURNING balance_points INTO NEW.balance_after_points;
  -- The UPDATE above takes a row lock on loyalty_account; a concurrent
  -- second transaction's identical UPDATE blocks until this one commits or
  -- rolls back, so NEW.balance_after_points always reflects the true
  -- post-serialization value, not a stale snapshot.
  IF NEW.balance_after_points < 0 THEN
    RAISE EXCEPTION 'insufficient points balance for account %', NEW.loyalty_account_id;
  END IF;
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_loyalty_point_ledger_sync_account
  BEFORE INSERT ON rewards.loyalty_point_ledger
  FOR EACH ROW EXECUTE FUNCTION rewards.sync_loyalty_account_balance();

A redemption (entry_type='redeem', negative amount_points) that would drive the balance below zero is rejected outright by the IF NEW.balance_after_points < 0 guard — fail-closed, not merely flagged. lifetime_points only ever increases (driven off entry_type='earn' specifically, not every positive-amount_points entry — promo_bonus is also positive but does not, by this trigger's own logic, add to lifetime totals since v_lifetime_delta only fires on 'earn'). A reverse entry now supports genuine proportional/partial claw-back (capped cumulatively via loyalty_point_ledger_reversal_tracker, never exceeding the original's own magnitude), superseding the original build's exact-full-negation-only rule.

---

rewards — Design Patterns Summary

Column-count reconciliation

Table	Cols
loyalty_program	14
loyalty_accrual_rule	22
loyalty_reward_tier	12
reward_option	22
loyalty_account	14
loyalty_point_ledger	18
loyalty_point_ledger_reversal_tracker	7
Total	109

Counted directly from the applied CREATE TABLE statements — the original 6 tables from packages/db/migrations/20260711000000_consumer_rewards_offers.sql (102 cols, cross-checked against the Drizzle source's own per-table comments, all 6 agreeing exactly) plus the 7-column loyalty_point_ledger_reversal_tracker added by the same-day reopen migration, packages/db/migrations/20260711030000_rewards_offers_proportional_clawback.sql. 7 tables, 109 columns, matching PROJECT_DECISIONS #60's own stated delta (+7 over the #56 baseline).

Triggers, full list (3 functions, 8 trigger objects)

Trigger	Table	Fires	Function
set_updated_at	loyalty_program, loyalty_accrual_rule, loyalty_reward_tier, reward_option, loyalty_account, loyalty_point_ledger_reversal_tracker	BEFORE UPDATE	platform.set_updated_at() (shared, reused)
trg_loyalty_point_ledger_append_only	loyalty_point_ledger	BEFORE UPDATE OR DELETE	platform.reject_append_only_mutation() (shared, reused)
trg_loyalty_point_ledger_sync_account	loyalty_point_ledger	BEFORE INSERT	rewards.sync_loyalty_account_balance() (reopened 2026-07-11, PROJECT_DECISIONS #60 — proportional/cumulative-cap reversal; see above)

Service layer

No RewardsService yet — schema-only this pass, same pattern as every other module's deferred service layer at build time. The point-earning/redemption business logic (which loyalty_accrual_rule applies to a given sale, tier progression) is entirely a future service-layer concern; the schema only guarantees the ledger stays internally consistent and the account balance stays synced.

JSONB columns

None in this schema — every column is a real typed column, no JSONB anywhere in rewards.

Open items carried forward

1. No RewardsService yet — accrual-rule application, tier progression, and redemption business logic are all unbuilt.
2. Point expiry is schema-representable (entry_type='expire', expires_at, expired_from_ledger_id) but nothing yet schedules or triggers it — a future batch job's own concern.
3. loyalty_program's site-scoping uniqueness (one all-sites program per tenant, OR one program per (tenant, site)) is a disclosed choice made at build time, not stated explicitly in the original design doc's own table walk.
4. loyalty_point_ledger.source_ref is a loose text ref (review ID, etc.) for non-sale sources — no FK, validated at write time only by the service layer once built.
5. This proportional-reversal fix (PROJECT_DECISIONS #60) is Pass 1 of a 2-pass effort — Pass 2 builds the returns module against returns-module-design-proposal-2026-07-11.md, which is the actual consumer of this capability (a return needs to claw back a proportional share of loyalty points/offer discount).