offers — Module Spec

1. Purpose

offers is business-issued discount/promo offers — module #25, the Consumer Layer's third real v2 build, schema-locked 2026-07-11 (superseding a stale v1-carryover placeholder count that was never a real v2 build). Definition, code pool, assignment lifecycle, and append-only redemption. This is also this build's own AI-authored-offer surface — offer.provenance admits ai_suggested/ai_auto_created, with schema-level guardrails preventing an ungoverned AI-authored discount from ever existing. Fully tenant-scoped. See PROJECT_DECISIONS #56. Reopened same day (2026-07-11) to fix a live reversal-magnitude bug (a reversal was sign-checked but never amount-checked) and to add proportional/partial reversal as a first-class capability; relocked — see PROJECT_DECISIONS #60.

2. Ownership

Owns — 7 tables, 127 columns:

Table	Cols	Role
offer	47	Definition — funding_source, discount_type (incl. buy_x_get_y, 2026-07-20), provenance, budget cap, margin floor, redemption_count
offer_code	13	Redeemable code pool
offer_assignment	15	issued/viewed/claimed/redeemed/expired/cancelled lifecycle
offer_redemption	15	Append-only — the redemption ledger, sale_id/sale_line_id the real POS seam
offer_targeting_rule	21	NEW — segment/category/engagement/geography/visit-frequency/growing-zone targeting
customer_discount_exposure	9	Per-consumer, per-period discount-exposure tracking
offer_redemption_reversal_tracker	7	NEW (2026-07-11 reopen) — lazily-created cumulative-reversal-cap counter per redemption row; mutable, tenant-scoped RLS, UNIQUE(tenant_id, redemption_id)
Total	127

Disclosed, this-fix-unrelated count drift: offer is live-confirmed at 43 columns (pre-2026-07-20), not the 42 the original 2026-07-11 build's MODULE_INDEX row recorded. The extra column, redemption_count, was 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 — not caused by this reopen's own fix. Disclosed here rather than silently folded into the math, matching this codebase's established convention for this bug class (e.g. shared's 12-column Phase 4 drift).

2026-07-20 addition (Gap-Fill Batch, item B6, PROJECT_DECISIONS #74): offer gained 4 new nullable bxgy_* columns supporting a new buy_x_get_y discount_type — see §12. offer is now 47 cols (up from 43); the offers module total is now 127 cols (up from 123).

Does NOT own: points-funded redemption (rewards.reward_option — never merge, see the boundary GUARD); Vrida→merchant settlement (platform's domain when funding_source='vrida' — offers records the fact, never the ledger).

3. Layer & Dependencies

Third Consumer Layer module, this build's own AI-authored-offer surface. Depends on: platform (tenant), consumer (consumer.id), pos (sale.id/sale_line.id, composite FKs riding this build's own pos.sale UNIQUE(id, tenant_id) prerequisite), crm (customer_segment_definition, plain FK + a dedicated cross-tenant-integrity trigger), shared (climate_zone), identity (actor). Depended on by: consumer.get_cross_tenant_activity() (a future ConsumerService read path).

4. Capabilities — honest Part D framing

The one genuine agent surface in this build: offer.provenance IN ('human_defined','ai_suggested','ai_auto_created'). An agent may draft or auto-create an offer, but chk_offer_ai_requires_guardrail makes it structurally impossible to insert a non-human_defined offer without a max_discount_percent or max_discount_amount_per_order guardrail already set — there is no code path, buggy or otherwise, that can land an ungoverned AI-authored discount in this table. The redemption-time margin-floor check (§6) is a second, independent layer at the point money actually moves, not just at offer-definition time.

5. Service Contract — OffersService

Not built this pass. Binding future requirement: OffersService.redeem() is the only sanctioned way to insert an offer_redemption row (never a direct INSERT); POSService/OrdersService will call it in parallel with RewardsService.redeem() at checkout — the two are parallel services, offer.stacking_policy governing combination.

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

offers.check_and_sync_offer_budget() is a single atomic BEFORE INSERT trigger on offer_redemption merging 3 concerns in one row-locking UPDATE: (a) the margin-floor check — fails CLOSED, not open, against a zero/NULL avg_cost_cents (RAISE EXCEPTION ... requires human review, never silently skipped); (b) the max_discount_percent/max_discount_amount_per_order guardrail check; (c) the budget-cap check + offer.budget_used_cents/offer_code.redeemed_count sync — closing the identical concurrency-race class rewards.sync_loyalty_account_balance() closes (see module_spec/rewards.md §6 for the live A/B reproduction methodology, mirrored here).

Reversal — a real bug fixed, then a real capability added (2026-07-11 reopen, PROJECT_DECISIONS #60). The trigger as originally built had NO magnitude check on a reversal, only a sign check: a $10 redemption could be "reversed" by a fabricated row claiming a $1000 discount and the old trigger would not notice, since it only guarded the aggregate budget_used_cents >= 0 floor. Live-reproduced against the exact pre-fix function body: a $10 (1000-cent) redemption, padded by an unrelated legitimate $990 redemption, was "reversed" by a fabricated -$1000 row — it succeeded, zeroing budget_used_cents by silently consuming the other redemption's budget. Fixed; and proportional/partial reversal is now a supported first-class capability, replacing the prior implied exact-negation-only shape: budget_used_cents adjusts by the exact (possibly partial) amount on every redeem/reverse, enforced against a cumulative cap via a new, lazily-created (INSERT ... ON CONFLICT DO NOTHING) tracker table, offer_redemption_reversal_tracker (mirrors rewards.loyalty_point_ledger_reversal_tracker exactly — see module_spec/rewards.md §6), using the same atomic row-locking pattern this trigger's own budget-cap check already used: a single UPDATE tracker SET total_reversed_cents = total_reversed_cents + :amt WHERE ... AND total_reversed_cents + :amt <= original_discount_amount_cents RETURNING ..., not read-then-check-then-write. redemption_count/offer_code.redeemed_count (integer COUNTS, not dollar amounts) only decrement by 1 when a reversal's cumulative total exactly equals the original discount amount (v_fully_reversed) — a genuine refinement beyond the original build, preventing a fractional count change on a partial reversal. Live-reproduced, full sequential + concurrency proof: the exact pre-fix bug scenario is now rejected (with the pre-fix bug independently reproduced first, then the function restored); partial reversal releases budget by the exact amount without decrementing redemption_count; a cumulative-exceeding reversal is rejected; the exact remainder succeeds and decrements redemption_count by exactly 1; further reversal against an exhausted original is rejected; reverse-of-reverse is rejected; 3 concurrent -400 reversals against one 1000-cent redemption resolved exactly 2-succeed/1-reject (cumulative 800). Same append-only reasoning as rewards: offer_redemption is append-only via platform.reject_append_only_mutation() (confirmed unconditional, no column-level exception), so a maintained counter column directly on the redemption row would hit that trigger on its first UPDATE — hence the separate tracker table, not a column.

max_per_consumer enforcement — a real gap found, then closed WITHOUT the tracker table this build's own OPEN_ITEMS row assumed would be needed (gap-validation fix, 2026-07-19). offer.max_per_consumer (nullable integer) was declared at this build's original 2026-07-11 lock but never referenced by check_and_sync_offer_budget() — confirmed unenforced via pg_get_functiondef during a 2026-07-19 gap-validation read-only pass (Part B, B7), which also found the module spec's own live column doesn't match the review claim that cited a customer_discount_exposure COLUMN (it's actually a separate TABLE, confirmed to genuinely exist, 9 cols, 0 rows, its own atomic-UPSERT maintenance still a documented, unbuilt OffersService requirement). 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. The fix closes the gap WITHOUT that new table: the COUNT(*) of sibling offer_redemption rows is positioned AFTER the trigger's own existing row-locking UPDATE offers.offer — 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), so a count taken after it always sees every already-committed sibling, making the naive-looking COUNT(*) race-safe by construction. Live-reproduced with a genuine 3-way concurrent race (3 backgrounded psql processes, a pg_sleep barrier forcing real overlap, max_per_consumer=2): exactly 2 of 3 committed, 1 correctly rejected. Slot-freeing rule (a genuine design decision, not just an implementation detail): a FULLY-reversed prior redemption frees its slot — reusing the reversal tracker's own total_reversed_cents = original_discount_amount_cents signal (not a fresh v_fully_reversed read, which is scoped to the row currently being inserted, not to historical siblings) — while a PARTIALLY-reversed redemption still counts against the cap. Applies to genuine 'redeem' rows only; reversal rows are exempt from the check entirely. offer_redemption.consumer_id is NOT NULL at the table level today, so the fix's defensive NEW.consumer_id IS NOT NULL guard is disclosed as currently-unreachable dead code, not a live anonymous-redemption path — see OPEN_ITEMS. Migration: packages/db/migrations/20260719000002_offers_max_per_consumer_enforcement.sql. Regression tests: L1–L6.

7. Design Rationale

• offer_targeting_rule.segment_definition_id, a plain FK plus a trigger — not composite. crm.customer_segment_definition supports NULL-tenant global segments alongside tenant-owned ones; a composite (id, tenant_id) FK can't express "OR global." offers.validate_offer_targeting_rule_segment() (mirrors pricing.trg_price_rule_validate_supersession exactly) DB-enforces same-tenant-or-global instead.
• offer_targeting_rule's 6-way discriminator CHECK. Exactly one of segment_definition_id/category_ref/engagement_threshold/geography_ref/growing_zone_code/min_visit_frequency may be set, matching rule_type — a single mutual-exclusivity CHECK rather than 6 separate tables.
• Disclosed arithmetic correction: offer_targeting_rule built as 21 cols, not the design doc's stated 17 — its own itemized column list undercounted itself by 4, omitting the standard created_at/updated_at/deleted_at triple plus created_by_actor_id every sibling autonomy-pack table in this build carries. Built with the full set, matching every sibling table.
• Settlement stays out of scope. funding_source='vrida' is a disclosed, structurally-representable-but-unenforced CHECK value — no settlement ledger exists; that's platform's domain, triggered later, not bundled in here.
• A pre-existing CHECK missed the new buy_x_get_y branch — found by this build's own live-reproduction testing, not by the original design (2026-07-20, PROJECT_DECISIONS #74, gap B6). The migration adding buy_x_get_y widened chk_offer_discount_type to permit the new value and added a dedicated chk_offer_bxgy_coherence CHECK for it, but initially missed that the OLDER, separate chk_offer_discount_type_coherence CHECK — which enumerates percent_off/amount_off/free_item+bogo's own required/forbidden columns by name — had no branch at all for buy_x_get_y. Every buy_x_get_y insert would therefore have unconditionally failed this other, unrelated CHECK regardless of the new bxgy_* columns being perfectly correct. Found the same day during this build's own live-reproduction step (not the original design pass) and fixed via a same-day follow-up migration (packages/db/migrations/20260720000008_offers_fix_bxgy_discount_type_coherence.sql), adding a 4th branch requiring free_item_variant_ref/discount_percent/discount_amount_cents all NULL when discount_type='buy_x_get_y'. 5 new regression tests cover same-item reward, different-item reward, classic bogo unaffected, and both rejection cases (the new coherence CHECK and the fixed older one) — offers-schema.spec.ts is 53/53.

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, including offer_targeting_rule's created_by_actor_id/reviewed_by_actor_id (new this build, alongside the standard automation_source/review_status/review_reason/reviewed_at set). The new offer_redemption_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 (mirrors rewards.loyalty_point_ledger_reversal_tracker).

9. Cross-Module Seams

• offers → pos: offer_redemption.sale_id → pos.sale(id, tenant_id) (composite, NOT NULL, real — v1's own "THE REDEMPTION SEAM") / .sale_line_id → pos.sale_line(id, tenant_id) (composite, nullable, the margin-floor verification target). No POS-side additive touch.
• offers → consumer: offer/offer_code/offer_assignment/offer_redemption/customer_discount_exposure .consumer_id → consumer.consumer.id (plain FKs).
• offers → crm: offer_targeting_rule.segment_definition_id → crm.customer_segment_definition.id (plain FK + trigger, see §7).
• offers → shared: offer_targeting_rule.growing_zone_code → shared.climate_zone.code.
• offers → platform: *.tenant_id → platform.tenant.id across all 7 tables — the RLS scope.
• offers internal (new, 2026-07-11): offer_redemption_reversal_tracker.redemption_id → offer_redemption(id, tenant_id) composite FK, UNIQUE(tenant_id, redemption_id) enforcing one tracker row per original redemption.
• offers → platform (deferred): Vrida-funded settlement, funding_source='vrida' disclosed-not-built.
• identity → offers: every *_actor_id → identity.actor.id.
• consumer → offers (deferred read): consumer.get_cross_tenant_activity() will union offer_assignment rows across every tenant, once ConsumerService exists.

10. Deferred / Future Items

OffersService (the HTTP controller layer) — the next build step, including the actual POSService/OrdersService checkout-time call this schema anticipates but does not yet wire. Vrida-funded settlement infrastructure remains fully deferred (platform's domain, not started).

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 offer/code/assignment/redemption structure in full and adds 3 genuinely new tables (offer_targeting_rule, customer_discount_exposure, offer_redemption_reversal_tracker) v1 lacked.

12. Buy-X-Get-Y — Quantity-Gated BOGO Generalization (B6, 2026-07-20 Gap-Fill Batch)

A new, additive discount_type='buy_x_get_y' value on offer (2026-07-20, PROJECT_DECISIONS #74, gap B6 of the 2026-07-19 gap-validation report) generalizes the classic 1-for-1 BOGO into an arbitrary quantity-gated reward: buy bxgy_qualifying_qty of the qualifying item, get bxgy_reward_qty of a reward item at bxgy_reward_discount_pct% off (100% = fully free). bxgy_reward_variant_id (nullable composite FK → inventory.item_variant(id, tenant_id)) names the reward item; NULL means the reward is the SAME item being qualified on (e.g., "buy 3 of X, get 1 of X free/discounted") — distinguishing a same-item reward from a different-item reward without needing two separate columns or discount types.

Explicitly distinct from, not a replacement or reinterpretation of, the pre-existing bogo discount_type. bogo (tied to a single free_item_variant_ref, a classic 1-for-1 free-item swap) is completely unchanged and unaffected by this addition — buy_x_get_y is a wholly separate, additive discount_type for quantity-gated generalizations (buy N get M, at any discount percent, same-item or different-item). Both discount types coexist going forward; an offer author picks whichever shape fits the promotion.

Deliberately out of scope, still deferred: multi-item bundle pricing (arbitrary combos, e.g. "any 3 for $10") is not built this pass — its own OPEN_ITEMS row was updated to reflect this, not closed.

See §7 above for the real coherence-CHECK bug this addition's own live-reproduction testing found and fixed (a pre-existing CHECK with no branch for the new discount_type), and schema_docs/offers.md's offer table section for the exact column/CHECK shape. Migrations: packages/db/migrations/20260720000006_offers_reopen_buy_x_get_y.sql + the same-day fix 20260720000008_offers_fix_bxgy_discount_type_coherence.sql. Schema-only — no OffersService consumption of this discount_type exists yet.