offers — new module (2026-07-11), merchant-issued discount/promo offers — THE AI module of this build

7 tables, 127 columns (up from 123; originally 6/115 at the 2026-07-11 build) — schema built and migration applied/verified live 2026-07-11; locked the same day (PROJECT_DECISIONS #57), then reopened again later the same day (still 2026-07-11) to fix a real, independent, live bug — offers.check_and_sync_offer_budget() had no magnitude check at all on a 'reverse' redemption row, only the pre-existing sign CHECK; a $10 redemption could be "reversed" by a fabricated $1,000 row and the trigger's own budget-cap floor check would not catch it — and to add proportional/cumulative-cap partial reversal as a first-class capability, via a new mutable tracker table, offer_redemption_reversal_tracker (PROJECT_DECISIONS #60); re-locked same day, still schema-only. This build also independently discovered (not caused, and disclosed rather than silently folded into the arithmetic) a pre-existing, this-fix-unrelated column-count drift: offers.offer is live-confirmed at 43 columns (pre-2026-07-20), not the 42 the original 2026-07-11 build's own MODULE_INDEX row recorded — the extra column, redemption_count, was genuinely added same-day by that build's own lock-gate verification fix pass (PROJECT_DECISIONS #57) but never reflected in the recorded column count at the time; see its own table section below. Reopened again 2026-07-20 (PROJECT_DECISIONS #74, Gap-Fill Batch item B6) to add a new, additive buy_x_get_y discount_type — 4 new nullable bxgy_* columns on offer (43→47 cols) plus a new chk_offer_bxgy_coherence CHECK, and a real bug fix: the PRE-EXISTING chk_offer_discount_type_coherence CHECK had no branch at all for the new value, so every buy_x_get_y insert would have unconditionally failed it — closed the same day via a follow-up migration widening that older CHECK with a 4th branch (see the offer table's own CHECK section below). offers is now 7 tables / 127 cols (up from 123). offers is Vrida's merchant-issued (and optionally AI-suggested) discount/promo engine: offer definitions, coupon codes, per-consumer assignment lifecycle, the redemption ledger, AI targeting rules, a discount-exposure rollup, and (new) a per-redemption reversal tracker. Tenant-scoped throughout, the same structural inversion as rewards. This is the AI module of the 3-schema build: an offer-generating agent is an ordinary identity.agent_identity with an ordinary identity.agent_duty_grant (permission offers:offer:propose) — zero new AI infrastructure was built. What IS new and structural: margin/discount guardrails are DB-enforced by trigger, not service-layer-hoped — see "The atomic guardrail+sync trigger" below.

Depends on platform (tenant), multi_loc (site), identity (actor), consumer (consumer.consumer — bare FK), pos (sale, sale_line — composite FKs; sale needs the same UNIQUE (id, tenant_id) prerequisite rewards needed, added once by this same migration and shared by both modules; sale_line already carries UNIQUE (id, tenant_id) since the pos reopen, PROJECT_DECISIONS #46 — no new prerequisite needed here), crm (customer_segment_definition — a plain, non-composite FK, since crm.customer_segment_definition itself lacks UNIQUE (id, tenant_id); cross-tenant integrity is enforced instead by a dedicated trigger — see below), and inventory (read-only, not FK'd: the margin-guardrail trigger reads inventory.item_variant.avg_cost_cents directly via a pos.sale_line join; offer.free_item_variant_ref/reward_option-style loose refs are text, not FK'd). No longer depends on shared — offer_targeting_rule.growing_zone_code's FK into shared.climate_zone was dropped 2026-07-18 (Phase 2, PROJECT_DECISIONS #70); the renamed attribute_ref column is now a loose, unFK'd reference — see that table's own section below.

This build also widened approvals.approval_request.source_module's CHECK to add 'offers' as a valid value (confirmed live value set was purchasing/pos/orders/identity/crm/inventory/ai/other; a CHECK widen never invalidates existing rows) — a cross-module side effect landing in this same migration, not a schema change to offers itself.

PROJECT_DECISIONS entry: #57 (original 2026-07-11 build + lock), #60 (same-day reopen — closes an independent live magnitude-check bug on reversal, adds proportional/partial reversal via offer_redemption_reversal_tracker), #70 (2026-07-18 Phase 2 reopen — offer_targeting_rule.growing_zone_code renamed attribute_ref, FK to shared.climate_zone dropped), and #74 (2026-07-20 Gap-Fill Batch, item B6 — new buy_x_get_y discount_type + a real chk_offer_discount_type_coherence coverage bug found and fixed the same day).

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.
• updated_at trigger-maintained via platform.set_updated_at() on 6 of 7 tables (offer, offer_code, offer_assignment, offer_targeting_rule, customer_discount_exposure, and — added by the same-day reopen — offer_redemption_reversal_tracker). Not on offer_redemption — an append-only ledger fact with no updated_at column.
• Soft delete (deleted_at) on the same original 5 tables. Not on offer_redemption (append-only ledger fact) nor on the new offer_redemption_reversal_tracker (a small mutable counter row with no lifecycle to soft-delete).
• Append-only, 1 table: offer_redemption — REVOKE UPDATE, DELETE FROM authenticated + trigger trg_offer_redemption_append_only, reusing platform.reject_append_only_mutation() verbatim. PK default platform.uuid_generate_v7(). The new offer_redemption_reversal_tracker is deliberately the opposite — genuinely mutable — precisely because the redemption 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. offer gets the FULL pack: created_by_actor_id + automation_source + the full review seam (review_status/review_reason/reviewed_by_actor_id/reviewed_at) + decision_provenance, plus offers-specific AI fields (provenance, status, the 2 guardrail-ceiling columns, min_margin_percent). offer_targeting_rule gets a 6-column pack (the 5-column review seam + created_by_actor_id, but no decision_provenance). offer_code, offer_assignment, offer_redemption, customer_discount_exposure, and the new offer_redemption_reversal_tracker get no autonomy columns at all — transactional instrument/lifecycle/ledger-fact/maintained-counter shapes, each deterministic given its inputs.
• THE STRUCTURAL AI GUARDRAIL. chk_offer_ai_requires_guardrail (provenance = 'human_defined' OR max_discount_percent IS NOT NULL OR max_discount_amount_per_order IS NOT NULL) — any AI-touched offer MUST carry at least one discount ceiling; NULL-safe by construction since provenance is NOT NULL. Enforced together with offers.check_and_sync_offer_budget() (see below), which merges the margin-floor check, the guardrail-ceiling check, and the budget-cap sync into one atomic BEFORE INSERT statement on offer_redemption — closing both a concurrency race and an AI margin-bypass gap the design's own verification found. Reopened the same day (PROJECT_DECISIONS #60) to close a real, independently-discovered, live bug: the function had no magnitude check at all on a 'reverse' row, only the pre-existing sign CHECK — live-reproduced by temporarily reinstalling the exact pre-fix body, a $10 (1000-cent) redemption padded by an unrelated legitimate $990 redemption on the same offer was "reversed" by a fabricated -$1000 row and succeeded, zeroing budget_used_cents by silently consuming the other redemption's budget. Now fixed via the same cumulative-cap tracker mechanism as rewards (a new offer_redemption_reversal_tracker table) — see "The atomic guardrail+sync trigger" below for the full corrected mechanism.
• offer_targeting_rule's cross-tenant-integrity trigger, trg_offer_targeting_rule_validate_segment — mirrors pricing.trg_price_rule_validate_supersession's own precedent: since segment_definition_id is a plain (non-composite) FK, a dedicated trigger enforces same-tenant-or-global at the DB level instead. See "The segment cross-tenant guard" below.

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

Column	Target	Notes
*.tenant_id (all 7 tables)	platform.tenant	NOT NULL
offer.site_id	multi_loc.site (id, tenant_id)	nullable, composite FK offer_site_tenant_fkey
offer.consumer_id, offer_code.consumer_id, offer_assignment.consumer_id, offer_redemption.consumer_id, customer_discount_exposure.consumer_id	consumer.consumer.id	bare — non-tenant-scoped target, no composite possible. NOT NULL on offer_assignment/offer_redemption/customer_discount_exposure; nullable on offer (targeted-only) and offer_code
offer_code.offer_id, offer_assignment.offer_id, offer_redemption.offer_id, offer_targeting_rule.offer_id	offers.offer (id, tenant_id)	NOT NULL, composite
offer_assignment.offer_code_id, offer_redemption.offer_code_id	offers.offer_code (id, tenant_id)	nullable, composite
offer_redemption.offer_assignment_id	offers.offer_assignment (id, tenant_id)	nullable, composite
offer_redemption.sale_id	pos.sale (id, tenant_id)	NOT NULL, composite. Shares the same sale_id_tenant_id_unique prerequisite rewards.loyalty_point_ledger.sale_id needed — added once by this migration
offer_redemption.sale_line_id	pos.sale_line (id, tenant_id)	nullable, composite. The margin-floor lookup key (→ inventory.item_variant.avg_cost_cents via join). No new prerequisite needed — sale_line already carries UNIQUE (id, tenant_id) since the pos reopen (PROJECT_DECISIONS #46)
offer_redemption.reversed_redemption_id	offers.offer_redemption (id, tenant_id)	nullable, self-referencing composite
offer_redemption_reversal_tracker.redemption_id	offers.offer_redemption (id, tenant_id)	NOT NULL, composite. NEW (same-day reopen, PROJECT_DECISIONS #60). UNIQUE (tenant_id, redemption_id) — one tracker row per original redemption, also the ON CONFLICT target for the lazy-create insert
offer_targeting_rule.segment_definition_id	crm.customer_segment_definition.id	nullable, plain (non-composite) FK — crm.customer_segment_definition lacks UNIQUE (id, tenant_id) entirely (live-confirmed). Cross-tenant integrity enforced instead by trg_offer_targeting_rule_validate_segment
offer.reviewed_by_actor_id, .created_by_actor_id, offer_targeting_rule.created_by_actor_id, .reviewed_by_actor_id	identity.actor	nullable, bare
offer.bxgy_reward_variant_id	inventory.item_variant (id, tenant_id)	nullable, composite FK. NEW (2026-07-20, PROJECT_DECISIONS #74, gap B6). NULL means the reward is the SAME item being qualified on

---

offers.offer (47 cols, FULL autonomy) — the offer definition

v1's full 27 columns preserved (explicitly walked and verified against the build's own Finding 5 — an earlier draft's "27 preserved" claim was disproven for 11 real v1 columns; every one is confirmed present here: id, tenant_id, created_at, updated_at, deleted_at, name, description, funding_source, discount_type, discount_percent, discount_amount_cents, max_discount_cents, free_item_variant_ref, min_purchase_cents, distribution_type, consumer_id, eligibility_json, stacking_policy, exclusive_group, priority, max_redemptions, max_per_consumer, budget_cents, budget_used_cents, valid_from, valid_to, is_active) + 16 new: site_id, applies_all_sites, provenance, automation_source, review_status, review_reason, reviewed_by_actor_id, reviewed_at, decision_provenance, status, max_discount_percent, max_discount_amount_per_order, excluded_categories, min_margin_percent, created_by_actor_id, redemption_count. 27 + 16 = 43. + 4 more (2026-07-20, PROJECT_DECISIONS #74, Gap-Fill Batch item B6): bxgy_qualifying_qty, bxgy_reward_qty, bxgy_reward_variant_id, bxgy_reward_discount_pct — supporting the new buy_x_get_y discount_type. 43 + 4 = 47.

Disclosed count correction (not caused by, but discovered during, the 2026-07-11 same-day reopen, PROJECT_DECISIONS #60): redemption_count was genuinely added same-day by the original build's own lock-gate verification fix pass (packages/db/migrations/20260711010000_consumer_rewards_offers_verify_fixes.sql, BLOCKER 3 fix — redemption_count integer NOT NULL DEFAULT 0 + chk_offer_redemption_count_nonneg + chk_offer_redemption_count_le_cap), but this doc's own MODULE_INDEX-cited total continued to say 42 cols until the proportional-reversal reopen re-counted from the live CREATE TABLE/ALTER TABLE history and caught the drift. Disclosed here rather than silently absorbed into the arithmetic, matching this codebase's established convention for this exact bug class (e.g. shared's 12-column Phase 4 drift).

status is a separate lifecycle from is_active (mirrors pricing.price_rule's own two-state-machine precedent) — is_active stays v1's simple boolean quick-disable toggle; status adds draft/suspended/expired distinctions the boolean can't express. max_discount_percent/max_discount_amount_per_order are guardrail ceilings, distinct from discount_percent (the offer's actual value when discount_type='percent_off') and max_discount_cents (v1's percent-off-only cap) — the guardrails apply regardless of discount_type and are what chk_offer_ai_requires_guardrail enforces for AI-touched offers.

Tenant-scoped. RLS enabled — offer_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
name	text	NOT NULL	—
description	text	nullable	—
funding_source	text	NOT NULL	'business'	CHECK IN (business,vrida)
discount_type	text	NOT NULL	—	CHECK IN (percent_off,amount_off,free_item,bogo,buy_x_get_y) — buy_x_get_y added 2026-07-20 (PROJECT_DECISIONS #74, gap B6); bogo (classic 1-for-1 free_item_variant_ref swap) is unchanged and unaffected
discount_percent	numeric	nullable	—	Required IFF discount_type='percent_off'. CHECK range (0, 100]
discount_amount_cents	bigint	nullable	—	Required IFF discount_type='amount_off'
max_discount_cents	bigint	nullable	—	Percent-off-only cap (v1) — CHECK requires discount_type='percent_off' when set
free_item_variant_ref	text	nullable	—	Loose ref — inventory.item_variant, validated at redemption time. Required IFF discount_type IN ('free_item','bogo')
min_purchase_cents	bigint	nullable	—
distribution_type	text	NOT NULL	—	CHECK IN (broadcast,targeted,code)
consumer_id	UUID	nullable	—	FK → consumer.consumer (bare). Only settable when distribution_type='targeted'
eligibility_json	jsonb	nullable	—
stacking_policy	text	NOT NULL	'exclusive'	CHECK IN (exclusive,stackable,best_price)
exclusive_group	text	nullable	—
priority	integer	NOT NULL	0
max_redemptions	integer	nullable	—
max_per_consumer	integer	nullable	—
budget_cents	bigint	nullable	—
budget_used_cents	bigint	NOT NULL	0	Trigger-maintained via offers.check_and_sync_offer_budget()
valid_from	timestamptz	nullable	—
valid_to	timestamptz	nullable	—
is_active	boolean	NOT NULL	true	Simple boolean quick-disable — separate from status
site_id	UUID	nullable	—	Composite FK → multi_loc.site. XOR with applies_all_sites
applies_all_sites	boolean	NOT NULL	false
provenance	text	NOT NULL	'human_defined'	CHECK IN (human_defined,ai_suggested,ai_auto_created)
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	—
decision_provenance	jsonb	nullable	—	Project-wide reason/evidence/confidence/memory_refs convention
status	text	NOT NULL	'draft'	CHECK IN (draft,active,suspended,expired) — separate lifecycle from is_active
max_discount_percent	numeric	nullable	—	Guardrail ceiling. CHECK range (0, 100]. Required for any non-human_defined offer unless max_discount_amount_per_order is set
max_discount_amount_per_order	bigint	nullable	—	Guardrail ceiling. Required for any non-human_defined offer unless max_discount_percent is set
excluded_categories	jsonb	nullable	—
min_margin_percent	numeric	nullable	—	CHECK range [0, 100]. The margin floor offers.check_and_sync_offer_budget() enforces at redemption time
created_by_actor_id	UUID	nullable	—	FK → identity.actor
redemption_count	integer	NOT NULL	0	Trigger-maintained via offers.check_and_sync_offer_budget() — net redemptions (redeem +1, fully-reversed reverse -1; a partial reversal does not move this). Added same-day by the original build's own lock-gate fix pass, disclosed above as a pre-existing doc-count drift
bxgy_qualifying_qty	integer	nullable	—	NEW (2026-07-20, PROJECT_DECISIONS #74, gap B6). CHECK > 0. Required IFF discount_type='buy_x_get_y' — quantity of the qualifying item that must be purchased
bxgy_reward_qty	integer	nullable	—	NEW. CHECK > 0. Required IFF discount_type='buy_x_get_y' — quantity of the reward item granted
bxgy_reward_variant_id	UUID	nullable	—	NEW. Composite FK → inventory.item_variant (id, tenant_id). NULL means the reward is the SAME item being qualified on (e.g. buy 3 of X, get 1 of X free/discounted); non-NULL names a different reward item
bxgy_reward_discount_pct	numeric	nullable	—	NEW. CHECK > 0 AND <= 100 (100 = fully free). Required IFF discount_type='buy_x_get_y' — the discount applied to each reward-qty unit

CHECK constraints (21, up from 20 — chk_offer_bxgy_coherence added 2026-07-20, PROJECT_DECISIONS #74, gap B6; the pre-existing 20 were themselves up from 18 in the original design walk, 2 added same-day by the original build's own lock-gate fix pass alongside redemption_count itself, see the disclosed count correction above): chk_offer_funding_source; chk_offer_discount_type; chk_offer_discount_type_coherence (now 4-branch, widened 2026-07-20 — one column set required per discount_type, others forbidden; the original 3 branches covered percent_off/amount_off/free_item+bogo, and a same-day follow-up migration, 20260720000008_offers_fix_bxgy_discount_type_coherence.sql, added the missing 4th branch requiring free_item_variant_ref/discount_percent/discount_amount_cents all NULL when discount_type='buy_x_get_y' — a real bug: the original B6 migration widened chk_offer_discount_type and added chk_offer_bxgy_coherence but missed that this OLDER, separate CHECK had no branch at all for the new value, so every buy_x_get_y insert would have unconditionally failed it regardless of the new bxgy_* columns being correct; found during this build's own live-reproduction testing, not the original design); chk_offer_discount_percent_range; chk_offer_max_discount_cents_percent_only; chk_offer_distribution_type; chk_offer_targeted_consumer (distribution_type = 'targeted' OR consumer_id IS NULL); chk_offer_stacking_policy; chk_offer_budget_used_nonneg; chk_offer_budget_used_le_cap (budget_used_cents <= budget_cents OR budget_cents IS NULL); chk_offer_applies_all_sites_xor_site_id; chk_offer_customer_scope_site_explicit (distribution_type != 'targeted' OR site_id IS NOT NULL OR applies_all_sites = true); chk_offer_provenance; chk_offer_ai_requires_guardrail (the structural AI guardrail — see above); chk_offer_automation_source; chk_offer_review_status; chk_offer_status; chk_offer_max_discount_percent_range; chk_offer_min_margin_percent_range; chk_offer_redemption_count_nonneg; chk_offer_redemption_count_le_cap (max_redemptions IS NULL OR redemption_count <= max_redemptions); chk_offer_bxgy_coherence (NEW, 2026-07-20 — discount_type = 'buy_x_get_y' requires bxgy_qualifying_qty/bxgy_reward_qty/bxgy_reward_discount_pct all NOT NULL; any OTHER discount_type requires all 4 bxgy_* columns NULL).

Indexes (5): PK on id; offer_id_tenant_id_unique (UNIQUE — prerequisite for every child table's composite FK); offer_tenant_id_idx (partial, WHERE deleted_at IS NULL); offer_tenant_id_is_active_idx (partial); offer_consumer_id_idx (partial); offer_distribution_type_idx (partial).

---

offers.offer_code (13 cols)

v1 unchanged. Coupon codes for distribution_type='code' offers. Plain text (typed/shared, not bearer secrets) — normalized_code for case-insensitive lookup. redeemed_count is trigger-maintained by offers.check_and_sync_offer_budget() — same treatment as offer.redemption_count: it only decrements on a reversal that fully exhausts the original redemption's discount amount (a merely-partial reversal releases budget proportionally without touching this count), per the same-day proportional-reversal reopen (PROJECT_DECISIONS #60).

Tenant-scoped. RLS enabled — offer_code_tenant_isolation. Soft delete: deleted_at. updated_at: trigger-maintained via platform.set_updated_at(). No autonomy pack.

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
offer_id	UUID	NOT NULL	—	Composite FK → offers.offer
normalized_code	text	NOT NULL	—	Case-insensitive lookup key
code_display	text	nullable	—
consumer_id	UUID	nullable	—	FK → consumer.consumer (bare)
max_redemptions	integer	nullable	—
redeemed_count	integer	NOT NULL	0	Trigger-maintained via offers.check_and_sync_offer_budget()
expires_at	timestamptz	nullable	—
status	text	NOT NULL	'active'	CHECK IN (active,exhausted,expired,revoked)

CHECK constraints (2): chk_offer_code_redeemed_count_nonneg; chk_offer_code_status.

Indexes (4): PK on id; offer_code_id_tenant_id_unique (UNIQUE); offer_code_tenant_id_normalized_code_unique (UNIQUE, WHERE deleted_at IS NULL); offer_code_offer_id_idx; offer_code_consumer_id_idx (partial).

---

offers.offer_assignment (15 cols)

v1 unchanged. Per-consumer lifecycle of a targeted offer: issued → viewed → claimed → redeemed (or expired/cancelled).

Tenant-scoped. RLS enabled — offer_assignment_tenant_isolation. Soft delete: deleted_at. updated_at: trigger-maintained via platform.set_updated_at(). No autonomy pack.

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
offer_id	UUID	NOT NULL	—	Composite FK → offers.offer
consumer_id	UUID	NOT NULL	—	FK → consumer.consumer (bare)
offer_code_id	UUID	nullable	—	Composite FK → offers.offer_code
status	text	NOT NULL	'issued'	CHECK IN (issued,viewed,claimed,redeemed,expired,cancelled)
assigned_at	timestamptz	NOT NULL	now()
viewed_at	timestamptz	nullable	—
claimed_at	timestamptz	nullable	—
expires_at	timestamptz	nullable	—
source	text	NOT NULL	—	CHECK IN (targeted,broadcast_optin,birthday,campaign,manual)
eligibility_snapshot_json	jsonb	nullable	—

CHECK constraints (2): chk_offer_assignment_status; chk_offer_assignment_source.

Indexes (5): PK on id; offer_assignment_id_tenant_id_unique (UNIQUE); offer_assignment_tenant_offer_consumer_unique (UNIQUE, tenant_id+offer_id+consumer_id, WHERE deleted_at IS NULL); offer_assignment_consumer_id_idx; offer_assignment_offer_id_status_idx (partial); offer_assignment_status_active_idx (partial, WHERE status IN ('issued','claimed')).

---

offers.offer_redemption (15 cols) — append-only ledger fact

v1's 13 columns preserved verbatim + 2 new: sale_line_id (composite FK → pos.sale_line, no new prerequisite needed — see above) + channel. sale_id stays NOT NULL (v1's "THE REDEMPTION SEAM" — required on every redemption, order-level or line-level); sale_line_id is the OPTIONAL finer-grain pointer the margin-guardrail trigger needs to look up inventory.item_variant.avg_cost_cents for a line-level discount.

reversed_redemption_id deliberately carries no uniqueness constraint — the same-day reopen (PROJECT_DECISIONS #60) makes multiple, genuinely legitimate partial reverse rows against one original redeem row a first-class case (e.g. a UNIQUE (reversed_redemption_id) WHERE redemption_type='reverse' constraint, the pre-verification design's own instinct, would incorrectly block a second, legitimate partial reversal). The cumulative cap is enforced instead by the new offer_redemption_reversal_tracker table's own atomic UPDATE, not a uniqueness constraint on the FK column — see "The atomic guardrail+sync trigger" below.

Tenant-scoped, append-only. RLS enabled — offer_redemption_tenant_isolation. REVOKE UPDATE, DELETE FROM authenticated + trigger trg_offer_redemption_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
created_at	timestamptz	NOT NULL	now()
offer_id	UUID	NOT NULL	—	Composite FK → offers.offer
consumer_id	UUID	NOT NULL	—	FK → consumer.consumer (bare)
offer_assignment_id	UUID	nullable	—	Composite FK → offers.offer_assignment
offer_code_id	UUID	nullable	—	Composite FK → offers.offer_code
sale_id	UUID	NOT NULL	—	Composite FK → pos.sale — "THE REDEMPTION SEAM" (v1)
sale_line_id	UUID	nullable	—	NEW. Composite FK → pos.sale_line — the margin-floor lookup key
redemption_type	text	NOT NULL	'redeem'	CHECK IN (redeem,reverse)
discount_amount_applied_cents	bigint	NOT NULL	—	Sign enforced per redemption_type — positive for redeem, negative for reverse
reversed_redemption_id	UUID	nullable	—	Self-referencing composite FK. Required IFF redemption_type='reverse'
redeemed_at	timestamptz	NOT NULL	now()
note	text	nullable	—	Required IFF redemption_type='reverse'
channel	text	NOT NULL	'pos'	NEW. CHECK IN (pos,online,app)

CHECK constraints (5): chk_offer_redemption_redemption_type; chk_offer_redemption_reverse_requires_original; chk_offer_redemption_amount_sign ((redemption_type = 'redeem' AND discount_amount_applied_cents > 0) OR (redemption_type = 'reverse' AND discount_amount_applied_cents < 0)); chk_offer_redemption_reverse_requires_note; chk_offer_redemption_channel.

Indexes (6): PK on id; offer_redemption_id_tenant_id_unique (UNIQUE — prerequisite for the self-referencing composite FK); offer_redemption_idempotency_unique (UNIQUE, tenant_id+offer_id+consumer_id+sale_id, WHERE redemption_type = 'redeem' — the dedup index for the redeem path); offer_redemption_offer_id_idx; offer_redemption_consumer_id_idx; offer_redemption_sale_id_idx; offer_redemption_offer_assignment_id_idx (partial); offer_redemption_reversed_redemption_id_idx (partial).

---

offers.offer_redemption_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 offer_redemption row (redemption_type='redeem') that has ever been (partially or fully) reversed — lazily created on first touch (INSERT ... ON CONFLICT (tenant_id, redemption_id) DO NOTHING, snapshotting the original's own discount_amount_applied_cents; the lazy-create SELECT requires redemption_type='redeem' on the source row, so an attempt to reverse an already-reverse row finds no eligible original and the capped UPDATE below correctly matches zero rows), then atomically capped on every subsequent reversal by offers.check_and_sync_offer_budget()'s own UPDATE ... WHERE total_reversed_cents + abs(:amt) <= original_discount_amount_cents ... RETURNING. This closes BUG B — the independent, live magnitude-check gap this same reopen found (a $10 redemption could previously be "reversed" by a fabricated $1,000 row). Exists as a separate table, not a maintained column on offer_redemption itself, because that table is append-only via platform.reject_append_only_mutation() — a running-total column directly on it would hit that trigger on its first UPDATE. Mirrors offers.offer.budget_used_cents/.redemption_count's own established "maintained counter on a mutable row" precedent, and is the offers-side twin of rewards.loyalty_point_ledger_reversal_tracker (same shape, same atomic-cap mechanism, built in the same migration).

Tenant-scoped. RLS enabled — offer_redemption_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
redemption_id	UUID	NOT NULL	—	Composite FK → offers.offer_redemption — the original redeem row being (partially) reversed
original_discount_amount_cents	bigint	NOT NULL	—	Snapshotted from the original redemption row's own discount_amount_applied_cents on first touch (lazy-create, ON CONFLICT (tenant_id, redemption_id) DO NOTHING)
total_reversed_cents	bigint	NOT NULL	0	Cumulative running tally across every reverse row 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_offer_redemption_reversal_tracker_total_reversed_bounds (total_reversed_cents >= 0 AND total_reversed_cents <= original_discount_amount_cents) — a defensive backstop; the real, atomic enforcement point is check_and_sync_offer_budget()'s own capped UPDATE (see "The atomic guardrail+sync trigger" below).

Indexes (2): PK on id; offer_redemption_reversal_tracker_tenant_redemption_unique (UNIQUE, tenant_id+redemption_id — one tracker row per original redemption, and the ON CONFLICT target for the lazy-create insert); offer_redemption_reversal_tracker_tenant_id_idx.

---

offers.offer_targeting_rule (21 cols, 6-column autonomy) — wholly NEW table, no v1 precedent

Built as 21 columns: the design proposal's own inline column walk stated "17" but itemized 19 named columns — a Finding-6-class arithmetic slip the design's own verification pass didn't catch on this particular table. Built here with the full standard triple (created_at/updated_at/deleted_at), matching every sibling table in this module rather than omitting soft-delete/update-tracking to force-fit a stale total — 19 + 2 (soft-delete/update-tracking already counted once each in the 19, so effectively created_at+updated_at+deleted_at complete the standard triple) = 21, disclosed at build time.

6 discriminator columns (segment_definition_id, category_ref, engagement_threshold, geography_ref, min_visit_frequency, attribute_ref — renamed from growing_zone_code 2026-07-18, Phase 2, PROJECT_DECISIONS #70), one per rule_type branch — chk_offer_targeting_rule_type_coherence is a CHECK the design's own verification flagged as missing in the first draft: zero coherence enforcement for this 6-way discriminator, a real regression vs. every other polymorphic-by-type table in this codebase's own established discipline.

segment_definition_id is a plain FK (not composite) — crm.customer_segment_definition lacks UNIQUE (id, tenant_id) entirely (live-confirmed). The cross-tenant-integrity trigger (trg_offer_targeting_rule_validate_segment, mirroring pricing.trg_price_rule_validate_supersession's own precedent) is written in the migration, built now rather than deferred.

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

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
offer_id	UUID	NOT NULL	—	Composite FK → offers.offer
rule_type	text	NOT NULL	—	CHECK IN (segment,category_affinity,engagement_level,geography,visit_frequency,attribute_match) — growing_zone renamed attribute_match 2026-07-18, Phase 2 (PROJECT_DECISIONS #70)
segment_definition_id	UUID	nullable	—	Plain FK → crm.customer_segment_definition. Cross-tenant integrity via trigger, not composite FK. Required IFF rule_type='segment'
category_ref	text	nullable	—	Loose ref — category code, not a locked table. Required IFF rule_type='category_affinity'
engagement_threshold	numeric	nullable	—	Required IFF rule_type='engagement_level'
geography_ref	text	nullable	—	Loose ref — geography code/region identifier. Required IFF rule_type='geography'
attribute_ref	text	nullable	—	Renamed from growing_zone_code 2026-07-18, Phase 2 (PROJECT_DECISIONS #70); FK to shared.climate_zone.code DROPPED — now a generic, unFK'd loose reference. Required IFF rule_type='attribute_match'
min_visit_frequency	integer	nullable	—	Required IFF rule_type='visit_frequency'
is_fallback	boolean	NOT NULL	false
priority	integer	NOT NULL	0
created_by_actor_id	UUID	nullable	—	FK → identity.actor
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 (4): chk_offer_targeting_rule_rule_type; chk_offer_targeting_rule_type_coherence (6-branch, one discriminator column required per rule_type, all 5 others forbidden — the Lens-D-flagged fix; the growing_zone/growing_zone_code branch was renamed attribute_match/attribute_ref 2026-07-18, Phase 2, PROJECT_DECISIONS #70); chk_offer_targeting_rule_automation_source; chk_offer_targeting_rule_review_status.

Indexes (3): PK on id; offer_targeting_rule_tenant_id_idx (partial); offer_targeting_rule_offer_id_idx (partial); offer_targeting_rule_is_fallback_idx (partial). No dedicated UNIQUE (id, tenant_id) on this table — nothing yet needs to reference it via composite FK.

Renamed 2026-07-18, Phase 2 (Nursery Vertical Extraction, PROJECT_DECISIONS #70) — the generic offer engine must not structurally depend on any vertical's reference data, so the FK to shared.climate_zone was dropped entirely; attribute_ref is now a generic loose reference (no FK), populated by whichever vertical needs it (a nursery tenant populates it with a nursery_ref.climate_zone code). A dedicated nursery.offer_targeting_extension child table was considered and rejected — zero live data, zero live consumers (no OffersService exists), and it would need its own new UNIQUE(id,tenant_id) prerequisite for no present benefit.

---

offers.customer_discount_exposure (9 cols) — maintained rolling-period counter

An atomic-UPSERT-maintained counter (mirrors ai.agent_usage_period's own precedent, applied correctly here per the design's own verification — this table was already right in the first draft). Tracks a consumer's total discount exposure per rolling period, for future fraud/cap/RFM analysis — not itself a redemption record (offer_redemption remains the source of truth; this is a reconcilable rollup).

Tenant-scoped. RLS enabled — customer_discount_exposure_tenant_isolation. No soft delete. updated_at: trigger-maintained via platform.set_updated_at(). No autonomy pack.

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
consumer_id	UUID	NOT NULL	—	FK → consumer.consumer (bare)
period_start	timestamptz	NOT NULL	—
period_end	timestamptz	NOT NULL	—	CHECK > period_start
total_discount_cents	bigint	NOT NULL	0	Atomic-UPSERT maintained (INSERT ... ON CONFLICT ... DO UPDATE SET total_discount_cents = table.total_discount_cents + EXCLUDED....), mirrors ai.agent_usage_period
redemption_count	integer	NOT NULL	0	Same atomic-UPSERT maintenance
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained

CHECK constraints (3): chk_customer_discount_exposure_total_nonneg; chk_customer_discount_exposure_count_nonneg; chk_customer_discount_exposure_period_order.

Indexes (2): PK on id; customer_discount_exposure_tenant_consumer_period_unique (UNIQUE, tenant_id+consumer_id+period_start); customer_discount_exposure_consumer_id_idx.

---

The atomic guardrail+sync trigger

offers.check_and_sync_offer_budget() — merges several concerns into one atomic BEFORE INSERT statement on offer_redemption: (a) the margin-floor check with zero-cost fail-closed rejection, (b) the max_discount_percent/max_discount_amount_per_order guardrail-ceiling check, (c) the budget-cap + max_redemptions check + offer.budget_used_cents/.redemption_count/offer_code.redeemed_count sync, (d) — added by the same-day reopen, PROJECT_DECISIONS #60 — the proportional/cumulative-cap reversal check via the new offer_redemption_reversal_tracker table, and (e) — added by a 2026-07-19 gap-validation fix pass — the offer.max_per_consumer cap, positioned AFTER the offer-row-locking UPDATE (see its own paragraph below). All evaluated inside the same row-locking UPDATE(s), so no concurrent-insert window exists between "checked" and "applied" (the same concurrency-race pattern rewards.sync_loyalty_account_balance() closes).

The margin-percent arithmetic completes the design proposal's own trigger snippet, which stopped at the zero-cost guard without specifying the actual comparison — completed here as: realized_margin = (post_discount_price - avg_cost) / post_discount_price * 100, rejected if below min_margin_percent. The code block below is the function's actual current, live body (from the same-day reopen's own full CREATE OR REPLACE) — it also incorporates 2 earlier same-day fixes this doc had never previously reflected (a disclosed, pre-existing doc-staleness gap caught while updating this section for the proportional-reversal fix, not itself part of PROJECT_DECISIONS #60): the max_discount_percent basis was corrected from the offer's own min_purchase_cents (routinely NULL, silently defeating the guard) to the actual sale amount being discounted (pos.sale_line.charged_amount_minor_units when line-scoped, else pos.sale.total_minor_units), and redemption_count/offer_code.redeemed_count capping against max_redemptions was added — both from packages/db/migrations/20260711010000_consumer_rewards_offers_verify_fixes.sql, itself predating this reopen.

The reversal treatment, specifically (PROJECT_DECISIONS #60): the 3 guardrail checks (margin floor, both discount ceilings) now explicitly gate on NEW.redemption_type = 'redeem' — previously they relied on a reversal's negative discount_amount_applied_cents happening to make each comparison vacuously true, which is fragile, not a real guarantee. A 'reverse' row instead lazily creates a row in offer_redemption_reversal_tracker for the reversed_redemption_id being reversed (snapshotting its discount_amount_applied_cents; the lazy-create SELECT requires redemption_type='redeem' on the source, so a reversal of a reversal finds no eligible original), then an atomic UPDATE ... WHERE total_reversed_cents + abs(:amt) <= original_discount_amount_cents ... RETURNING both caps and increments the cumulative total in one row-locking statement — this is what closes BUG B (the $10-redemption/$1,000-fabricated-reversal live bug). budget_used_cents still adjusts by the exact (possibly partial) amount on every redeem/reverse; redemption_count/redeemed_count (integer counts, not dollar amounts) only decrement by 1 when a reversal's own cumulative total exactly equals the original discount amount (v_fully_reversed), preventing a fractional count change on a merely-partial reversal.

CREATE OR REPLACE FUNCTION offers.check_and_sync_offer_budget()
RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE
  v_offer offers.offer%ROWTYPE;
  v_avg_cost_cents bigint;
  v_charged_amount bigint;
  v_basis_amount bigint;
  v_post_discount_price numeric;
  v_realized_margin_percent numeric;
  v_new_budget bigint;
  v_new_redemption_count integer;
  v_rows int;
  v_new_total_reversed bigint;
  v_original_discount bigint;
  v_fully_reversed boolean := false;
  v_redemption_count_delta integer;
  v_active_redemption_count integer;
BEGIN
  SELECT * INTO v_offer FROM offers.offer WHERE id = NEW.offer_id;

  -- The 3 guardrail checks below are only meaningful for a genuine 'redeem'
  -- -- explicitly gated now, rather than relying on a reversal's negative
  -- discount_amount_applied_cents happening to make each comparison
  -- vacuously true (fragile, not a real guarantee).
  IF NEW.redemption_type = 'redeem' THEN
    IF v_offer.max_discount_percent IS NOT NULL THEN
      -- Basis is the actual sale amount being discounted (the sale_line's
      -- own charged amount when line-scoped, else the full sale total),
      -- NEVER min_purchase_cents (that column is the offer's OWN
      -- eligibility threshold, not what was actually charged, and is
      -- routinely NULL).
      IF NEW.sale_line_id IS NOT NULL THEN
        SELECT charged_amount_minor_units INTO v_basis_amount FROM pos.sale_line WHERE id = NEW.sale_line_id;
      ELSE
        SELECT total_minor_units INTO v_basis_amount FROM pos.sale WHERE id = NEW.sale_id;
      END IF;
      IF v_basis_amount IS NULL OR v_basis_amount = 0 THEN
        RAISE EXCEPTION 'cannot verify max_discount_percent against an unknown or zero sale amount for offer %', NEW.offer_id;
      END IF;
      IF NEW.discount_amount_applied_cents::numeric / v_basis_amount * 100 > v_offer.max_discount_percent THEN
        RAISE EXCEPTION 'discount exceeds max_discount_percent for offer %', NEW.offer_id;
      END IF;
    END IF;

    IF v_offer.max_discount_amount_per_order IS NOT NULL
       AND NEW.discount_amount_applied_cents > v_offer.max_discount_amount_per_order THEN
      RAISE EXCEPTION 'discount exceeds max_discount_amount_per_order for offer %', NEW.offer_id;
    END IF;

    IF v_offer.min_margin_percent IS NOT NULL AND NEW.sale_line_id IS NOT NULL THEN
      SELECT iv.avg_cost_cents, sl.charged_amount_minor_units
      INTO v_avg_cost_cents, v_charged_amount
      FROM pos.sale_line sl JOIN inventory.item_variant iv ON iv.id = sl.item_variant_id
      WHERE sl.id = NEW.sale_line_id;
      IF v_avg_cost_cents IS NULL OR v_avg_cost_cents = 0 THEN
        RAISE EXCEPTION 'cannot verify margin floor against a zero-cost item for offer % — requires human review', NEW.offer_id;
      END IF;
      v_post_discount_price := GREATEST(v_charged_amount - NEW.discount_amount_applied_cents, 0);
      IF v_post_discount_price = 0 THEN
        RAISE EXCEPTION 'discount for offer % would reduce the realized price to zero, failing the margin floor', NEW.offer_id;
      END IF;
      v_realized_margin_percent := (v_post_discount_price - v_avg_cost_cents) / v_post_discount_price * 100;
      IF v_realized_margin_percent < v_offer.min_margin_percent THEN
        RAISE EXCEPTION 'discount for offer % would drop realized margin to % (floor is %)', NEW.offer_id, round(v_realized_margin_percent, 2), v_offer.min_margin_percent;
      END IF;
    ELSIF v_offer.min_margin_percent IS NOT NULL AND NEW.sale_line_id IS NULL THEN
      RAISE EXCEPTION 'offer % has a margin floor but this redemption has no sale_line_id to verify cost against', NEW.offer_id;
    END IF;
  END IF;

  IF NEW.redemption_type = 'reverse' THEN
    -- Lazily create the tracker row for the original 'redeem' being
    -- reversed (snapshotting its discount amount). The SELECT requires
    -- redemption_type='redeem' -- you cannot reverse a reversal.
    INSERT INTO offers.offer_redemption_reversal_tracker (tenant_id, redemption_id, original_discount_amount_cents)
    SELECT NEW.tenant_id, NEW.reversed_redemption_id, r.discount_amount_applied_cents
    FROM offers.offer_redemption r
    WHERE r.id = NEW.reversed_redemption_id AND r.tenant_id = NEW.tenant_id AND r.redemption_type = 'redeem'
    ON CONFLICT (tenant_id, redemption_id) DO NOTHING;

    -- Atomic cap + increment (closes BUG B: a $1,000 reversal of a $10
    -- redemption is now rejected, since 0 + 1000 > 10). A concurrent second
    -- reversal against the same original blocks on this row's lock.
    UPDATE offers.offer_redemption_reversal_tracker
    SET total_reversed_cents = total_reversed_cents + abs(NEW.discount_amount_applied_cents)
    WHERE tenant_id = NEW.tenant_id
      AND redemption_id = NEW.reversed_redemption_id
      AND total_reversed_cents + abs(NEW.discount_amount_applied_cents) <= original_discount_amount_cents
    RETURNING total_reversed_cents, original_discount_amount_cents INTO v_new_total_reversed, v_original_discount;

    GET DIAGNOSTICS v_rows = ROW_COUNT;
    IF v_rows = 0 THEN
      RAISE EXCEPTION 'reverse redemption for reversed_redemption_id % would exceed the cumulative reversible discount, references a non-redeem row, or was not found', NEW.reversed_redemption_id;
    END IF;

    v_fully_reversed := (v_new_total_reversed = v_original_discount);
  END IF;

  v_redemption_count_delta := CASE
    WHEN NEW.redemption_type = 'redeem' THEN 1
    WHEN NEW.redemption_type = 'reverse' AND v_fully_reversed THEN -1
    ELSE 0
  END;

  -- Single atomic conditional UPDATE: budget releases/consumes by the exact
  -- (possibly partial) discount amount on every redeem/reverse;
  -- redemption_count only moves on a genuine redeem or a FULLY-exhausted
  -- reverse (never fractionally).
  UPDATE offers.offer
  SET budget_used_cents = budget_used_cents + NEW.discount_amount_applied_cents,
      redemption_count = redemption_count + v_redemption_count_delta
  WHERE id = NEW.offer_id
    AND (budget_cents IS NULL OR budget_used_cents + NEW.discount_amount_applied_cents <= budget_cents
         OR NEW.redemption_type = 'reverse')
    AND (max_redemptions IS NULL OR v_redemption_count_delta <= 0
         OR redemption_count + v_redemption_count_delta <= max_redemptions)
  RETURNING budget_used_cents, redemption_count INTO v_new_budget, v_new_redemption_count;
  GET DIAGNOSTICS v_rows = ROW_COUNT;
  IF v_rows = 0 THEN
    RAISE EXCEPTION 'offer % budget or max_redemptions cap would be exceeded by this redemption', NEW.offer_id;
  END IF;

  -- gap-validation fix (2026-07-19): max_per_consumer enforcement, AFTER the
  -- row-locking UPDATE above -- see the dedicated paragraph below this code
  -- block for the full rationale and the documented slot-freeing rule.
  IF NEW.redemption_type = 'redeem' AND v_offer.max_per_consumer IS NOT NULL AND NEW.consumer_id IS NOT NULL THEN
    SELECT count(*) INTO v_active_redemption_count
    FROM offers.offer_redemption r
    WHERE r.tenant_id = NEW.tenant_id
      AND r.offer_id = NEW.offer_id
      AND r.consumer_id = NEW.consumer_id
      AND r.redemption_type = 'redeem'
      AND NOT EXISTS (
        SELECT 1 FROM offers.offer_redemption_reversal_tracker t
        WHERE t.tenant_id = r.tenant_id
          AND t.redemption_id = r.id
          AND t.total_reversed_cents = t.original_discount_amount_cents
      );
    IF v_active_redemption_count + 1 > v_offer.max_per_consumer THEN
      RAISE EXCEPTION 'offer % max_per_consumer (%) would be exceeded for consumer % (% active redemptions already)',
        NEW.offer_id, v_offer.max_per_consumer, NEW.consumer_id, v_active_redemption_count;
    END IF;
  END IF;

  IF NEW.offer_code_id IS NOT NULL THEN
    UPDATE offers.offer_code
    SET redeemed_count = redeemed_count + v_redemption_count_delta
    WHERE id = NEW.offer_code_id
      AND (max_redemptions IS NULL OR v_redemption_count_delta <= 0
           OR redeemed_count + v_redemption_count_delta <= max_redemptions);
    GET DIAGNOSTICS v_rows = ROW_COUNT;
    IF v_rows = 0 THEN
      RAISE EXCEPTION 'offer_code % max_redemptions cap would be exceeded by this redemption', NEW.offer_code_id;
    END IF;
  END IF;
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_offer_redemption_check_and_sync_budget
  BEFORE INSERT ON offers.offer_redemption
  FOR EACH ROW EXECUTE FUNCTION offers.check_and_sync_offer_budget();

max_per_consumer enforcement — a real gap found, then closed WITHOUT the tracker table this build's own original OPEN_ITEMS row assumed would be needed (gap-validation fix, 2026-07-19). offer.max_per_consumer was declared at the original 2026-07-11 lock but never referenced by this function — confirmed unenforced via pg_get_functiondef during a 2026-07-19 gap-validation read-only pass (Part B, B7). That pass's own review claim also cited a customer_discount_exposure COLUMN as evidence of a lighter-weight existing mechanism; this was corrected during the same-day fix pass — customer_discount_exposure is a separate TABLE (confirmed via \dt offers.*, 9 cols, 0 rows), not a column, and it plays no role in this enforcement. The original 2026-07-11 lock-gate OPEN_ITEMS row for this gap explicitly warned that "a live COUNT(*) query without [a lock] would reintroduce the exact concurrency race" and recommended a dedicated per-(offer,consumer) counter table instead (mirroring offer_redemption_reversal_tracker's own shape). The fix above closes the gap WITHOUT that new table: the COUNT(*) (v_active_redemption_count) is positioned AFTER the row-locking UPDATE offers.offer earlier in this same function — that lock already serializes every concurrent redemption of one offer (a second concurrent INSERT's own UPDATE blocks on the offer row until the first transaction commits and releases it), so a count taken after it always observes every already-committed sibling row, making this COUNT(*) race-safe by construction, not by accident. Live-reproduced with a genuine 3-way concurrent race: 3 backgrounded psql processes, each wrapped in its own BEGIN/pg_sleep(0.3)/INSERT/COMMIT, targeting the same consumer against an offer with max_per_consumer=2 — exactly 2 of 3 committed, 1 rejected with the exact exception text above, confirming the count-after-lock placement holds under real overlap, not just sequential execution.

Slot-freeing rule (a genuine design decision, not just an implementation detail). A FULLY-reversed prior redemption frees its slot — the NOT EXISTS subquery reuses the reversal tracker's own total_reversed_cents = original_discount_amount_cents equality (the same signal v_fully_reversed computes for the CURRENT row being reversed, but here re-derived per HISTORICAL sibling row, since v_fully_reversed itself is scoped only to whichever row is being inserted right now) — while a PARTIALLY-reversed redemption still counts against the cap (consistent with redemption_count itself only decrementing on a full reversal, never a partial one). The check applies to genuine 'redeem' rows only (NEW.redemption_type = 'redeem'); reversal rows are exempt entirely, matching the same gating already used for the 3 guardrail checks above. NEW.consumer_id IS NOT NULL is a defensive guard, not a live branch: offer_redemption.consumer_id is NOT NULL at the table level today, so no anonymous-redemption row can exist under the current schema — disclosed in OPEN_ITEMS as forward-compatible dead code, not assumed reachable. Migration: packages/db/migrations/20260719000002_offers_max_per_consumer_enforcement.sql. Regression tests: offers-schema.spec.ts section L (L1–L6).

The zero-cost fail-closed guard is the AI margin-bypass fix: if a margin floor is set on the offer but the linked item_variant.avg_cost_cents is NULL or zero, the redemption is REJECTED outright ("requires human review") rather than silently passing the margin check against an unknown or zero cost — a zero/unknown cost could otherwise let an AI-proposed discount slip through with no real margin verification at all. The magnitude fail-closed guard (PROJECT_DECISIONS #60) is the reversal-side counterpart: a 'reverse' row whose cumulative claimed amount would exceed its original redemption's own discount_amount_applied_cents is REJECTED outright by the offer_redemption_reversal_tracker's own capped UPDATE returning zero rows — closing the live bug where a $1,000 reversal of a $10 redemption previously succeeded by silently consuming an unrelated redemption's legitimate budget.

---

The segment cross-tenant guard

trg_offer_targeting_rule_validate_segment, mirroring pricing.trg_price_rule_validate_supersession exactly — a plain FK, DB-enforced same-tenant-or-global guard, needed because segment_definition_id cannot be a composite FK (crm.customer_segment_definition lacks the UNIQUE (id, tenant_id) prerequisite).

CREATE OR REPLACE FUNCTION offers.validate_offer_targeting_rule_segment()
RETURNS trigger AS $$
BEGIN
  IF NEW.segment_definition_id IS NOT NULL THEN
    IF NOT EXISTS (
      SELECT 1 FROM crm.customer_segment_definition
      WHERE id = NEW.segment_definition_id
        AND (tenant_id = NEW.tenant_id OR tenant_id IS NULL)
    ) THEN
      RAISE EXCEPTION 'offer_targeting_rule.segment_definition_id must reference a customer_segment_definition row belonging to the same tenant, or a global (tenant_id IS NULL) segment';
    END IF;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_offer_targeting_rule_validate_segment
  BEFORE INSERT OR UPDATE OF segment_definition_id, tenant_id ON offers.offer_targeting_rule
  FOR EACH ROW EXECUTE FUNCTION offers.validate_offer_targeting_rule_segment();

Allows a NULL-tenant (global/built-in) segment OR one belonging to the same tenant; rejects a different tenant's own segment.

---

offers — Design Patterns Summary

Column-count reconciliation

Table	Cols
offer	47
offer_code	13
offer_assignment	15
offer_redemption	15
offer_targeting_rule	21
customer_discount_exposure	9
offer_redemption_reversal_tracker	7
Total	127

Counted directly from the applied CREATE TABLE/ALTER TABLE statements across all migrations to date: packages/db/migrations/20260711000000_consumer_rewards_offers.sql (the original build, 6 tables), 20260711010000_consumer_rewards_offers_verify_fixes.sql (same-day lock-gate fix pass, +1 col — offer.redemption_count, a pre-existing drift this doc had never previously reflected, disclosed above), 20260711030000_rewards_offers_proportional_clawback.sql (the same-day reopen, PROJECT_DECISIONS #60, +1 table / +7 cols — offer_redemption_reversal_tracker), and 20260720000006_offers_reopen_buy_x_get_y.sql + the same-day fix 20260720000008_offers_fix_bxgy_discount_type_coherence.sql (PROJECT_DECISIONS #74, gap B6, +4 cols on offer — bxgy_qualifying_qty/bxgy_reward_qty/bxgy_reward_variant_id/bxgy_reward_discount_pct, no new table). 7 tables, 127 columns — up from 123 (originally-recorded 6/115 at the 2026-07-11 build).

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

Trigger	Table	Fires	Function
set_updated_at	offer, offer_code, offer_assignment, offer_targeting_rule, customer_discount_exposure, offer_redemption_reversal_tracker	BEFORE UPDATE	platform.set_updated_at() (shared, reused)
trg_offer_redemption_append_only	offer_redemption	BEFORE UPDATE OR DELETE	platform.reject_append_only_mutation() (shared, reused)
trg_offer_redemption_check_and_sync_budget	offer_redemption	BEFORE INSERT	offers.check_and_sync_offer_budget() (reopened 2026-07-11, PROJECT_DECISIONS #60 — proportional/cumulative-cap reversal + closes the independent magnitude-check bug; reopened again 2026-07-19, gap-validation fix — max_per_consumer enforcement, no schema change; see above)
trg_offer_targeting_rule_validate_segment	offer_targeting_rule	BEFORE INSERT OR UPDATE OF segment_definition_id, tenant_id	offers.validate_offer_targeting_rule_segment() (new — see above)

Service layer

No OffersService yet — schema-only this pass, same pattern as every other module's deferred service layer at build time. The offer-generating AI agent path (an identity.agent_identity + identity.agent_duty_grant for offers:offer:propose) needs zero new AI infrastructure — it reuses the existing A5 agent-authority passport mechanism verbatim; only the guardrail enforcement itself (the trigger above) is built this pass.

JSONB columns

offer.eligibility_json, offer.decision_provenance (project-wide reason/evidence/confidence/memory_refs convention), offer.excluded_categories, offer_assignment.eligibility_snapshot_json.

Open items carried forward

1. No OffersService yet — offer matching/eligibility evaluation, targeting-rule application, and the actual AI-proposal flow are all unbuilt.
2. offer_targeting_rule has no dedicated UNIQUE (id, tenant_id) — nothing yet needs to reference it via composite FK; add if a future consumer needs one.
3. chk_offer_ai_requires_guardrail's ceiling requirement is a floor, not a guarantee of correctness — an AI-touched offer must carry SOME ceiling, but nothing validates the ceiling value itself is sane (e.g. a 100% max_discount_percent technically satisfies the CHECK).
4. customer_discount_exposure's atomic-UPSERT maintenance is a documented service-layer build requirement — the schema only defines the shape and the uniqueness constraint the UPSERT relies on; nothing yet writes to this table.
5. approvals.approval_request.source_module's CHECK widen to include 'offers' — a cross-module side effect of this migration (see module intro above), landing in approvals, not offers itself; not restated in approvals' own docs by this pass.
6. 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).