offers — Consumer Layer

Schema locked 2026-06-12. 4 tables, 68 cols: offer (27), offer_code (13), offer_assignment (15), offer_redemption (13).

Design Principles

Tenant-scoped + funding_source flag (NOT platform-level). Every offer is AT a specific nursery — tenant_id is present and NOT NULL on all offers.* rows. The offer is issued and redeemed within one tenant's POS session. funding_source records who absorbs the cost: 'business' (the nursery funds the discount) or 'vrida' (Vrida co-funds the discount). A truly cross-tenant Vrida offer (redeemable at any nursery, tenant_id NULL) is a future feature — not v1. consumer_id is a targeting dimension (nullable on offer; NOT NULL on offer_redemption), not the RLS scope.

Merchant-funded only in v1. The funding_source enum includes 'vrida' for forward-compatibility, but the v1 service layer guards and rejects funding_source = 'vrida' offers. Vrida-funded offers (co-op promotions where Vrida reimburses the nursery for discount cost) require settlement infrastructure that is deliberately deferred to v1.5.

Settlement feeds platform — offers never owns money-movement. When funding_source = 'vrida', the Vrida→merchant credit (reimbursing the nursery for the subsidized discount) is a platform financial concern. platform already owns the Vrida↔tenant financial relationship (SaaS billing, subscription invoices). Vrida-to-merchant reimbursement is the inverse direction of the same relationship — it belongs under platform when designed. offers records the offer definition, funding_source, and redemption facts. It feeds settlement; it does not build a settlement engine. This closes the long-open settlement-economics question from PROJECT_DECISIONS "Consumer Layer Architecture (2026-06-09)": settlement is not offers' domain; it is platform's domain when built; deferred from v1.

offer_redemption is append-only (the ledger discipline). A reversal (refund/void) is a NEW row with redemption_type = 'reverse' and a signed-negative discount_amount_applied_cents, referencing the original via reversed_redemption_id. The original row is never mutated. This keeps offer.budget_used_cents reconcilable and the redemption ledger immutable and auditable — identical to the rewards.points_ledger append-only discipline.

offer.budget_used_cents is a maintained cache. It reconciles to SUM(discount_amount_applied_cents) over offer_redemption rows for this offer. The pattern is identical to loyalty_account.balance_points — a reconcilable cache, never updated directly by application logic, always reconcilable from the append-only ledger.

Consent is CRM's. OffersService reads crm.customer_consent (consent_type = 'offers') before targeting or issuing any offer. No consent table exists in offers — the same pattern as rewards reading 'loyalty' consent and notifications reading 'marketing' consent.

Distinct from pricing and rewards; all three can stack. Pricing (price_rule) = predefined auto-applied rules resolved at line-item price resolution. Rewards (reward_option) = points-funded redemption catalog, points-denominated. Offers = consumer-presents-and-claims coupon/promo at checkout — discrete issued instruments. stacking_policy on offer governs how offers combine at checkout ('exclusive' = this offer cannot be combined with another; 'stackable' = combinable; 'best_price' = apply whichever gives the bigger discount). OffersService and RewardsService are parallel services — both called by POSService at checkout, neither a sub-service of the other.

Note on PROJECT_DECISIONS service boundary (pre-design notation). PROJECT_DECISIONS.md "Consumer Layer Architecture (2026-06-09)" records POSService calling RewardsService.redeem(consumer_id, offer_id, amount). This was written before rewards and offers were designed as separate modules, when consolidation was still open. Now that both are locked/designed separately, the correct post-offers seam is two parallel calls: RewardsService.redeem(consumer_id, reward_option_id, amount) for points redemptions and OffersService.redeem(consumer_id, offer_id, sale_id) for coupon/promo redemptions. The offer_id parameter on RewardsService is stale pre-design notation; update that note at offers lock.

---

Cross-Phase FK Seams

Column	Target	Status
offer.tenant_id	platform.tenant	ENFORCED FK (platform locked 2026-06-09; enforceable at offers schema creation)
offer_code.tenant_id	platform.tenant	ENFORCED FK
offer_assignment.tenant_id	platform.tenant	ENFORCED FK
offer_redemption.tenant_id	platform.tenant	ENFORCED FK
offer.consumer_id	consumer.consumer	ENFORCED FK nullable (NULL = broadcast/code offer; set = targeted single-consumer offer; consumer locked 2026-06-11)
offer_code.consumer_id	consumer.consumer	ENFORCED FK nullable (NULL = shared code; set = per-consumer code)
offer_assignment.consumer_id	consumer.consumer	ENFORCED FK NOT NULL (assignment is always to a specific consumer)
offer_redemption.consumer_id	consumer.consumer	ENFORCED FK NOT NULL (redemption is always by a specific consumer)
offer_redemption.sale_id	pos.sale	THE REDEMPTION SEAM — ENFORCED FK (pos locked 2026-06-10; FK on offers side; pos.sale has NO offer seam columns — no POS touch required; reference-don't-copy, same pattern as rewards.points_ledger.sale_id)
offer.free_item_variant_ref	inventory.item_variant	LOOSE text ref (variant may be archived or substituted at redemption time; FK would prevent catalog rows from outliving their variants; OffersService validates at redemption)

---

offers.offer — 27 cols

The offer definition. Captures the discount mechanics, funding source, eligibility parameters, distribution type, stacking policy, and validity window. One row per offer; all other offers.* tables FK back here.

discount_type values: 'percent_off' (percentage of basket or qualifying item), 'amount_off' (flat dollar amount off), 'free_item' (a specific item is free when offer applies), 'bogo' (buy-one-get-one; free_item_variant_ref identifies the free item). Type and field coherence are enforced by CHECK.

distribution_type governs how the offer reaches a consumer: 'broadcast' (visible to all active consumers of this tenant; no consumer_id or code required), 'targeted' (issued to a specific consumer_id; offer_assignment rows track per-consumer lifecycle), 'code' (consumer presents a code at checkout; code rows live in offer_code).

stacking_policy: 'exclusive' = this offer cannot be applied at the same time as any other offer in the same checkout. 'stackable' = may combine with other stackable offers. 'best_price' = if multiple offers qualify, apply only the one giving the greatest discount. exclusive_group lets a subset of stackable offers be mutually exclusive: two offers in the same exclusive_group cannot both apply even if each is 'stackable'.

budget_used_cents is a reconcilable cache maintained by OffersService: it increments on each redemption and decrements on each reversal. When budget_cents is not NULL and budget_used_cents >= budget_cents, OffersService treats the offer as exhausted and stops new redemptions. The ledger in offer_redemption is the source of truth.

Merchant-funded v1. funding_source default is 'business'. The 'vrida' value is present in the CHECK for forward-compatibility; v1 service layer rejects it. Vrida-funded offers and settlement deferred to v1.5.

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

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant — RLS scope
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
name	text	NOT NULL	—	Display name, e.g. 'Spring Weekend: 20% Off All Annuals'
description	text	nullable	—	Consumer-facing description of the offer
funding_source	text	NOT NULL	'business'	CHECK (funding_source IN ('business','vrida')). v1: OffersService guards 'vrida' — deferred to v1.5.
discount_type	text	NOT NULL	—	CHECK (discount_type IN ('percent_off','amount_off','free_item','bogo'))
discount_percent	numeric	nullable	—	Percentage discount (e.g. 20.0 = 20% off). Required when discount_type = 'percent_off'; NULL otherwise. CHECK (discount_percent IS NULL OR (discount_percent > 0 AND discount_percent <= 100))
discount_amount_cents	bigint	nullable	—	Flat dollar discount in cents (e.g. 500 = $5 off). Required when discount_type = 'amount_off'; NULL otherwise.
max_discount_cents	bigint	nullable	—	Cap on a percent-off discount (e.g. 2500 = max $25 off even if 20% of basket exceeds that). Only meaningful when discount_type = 'percent_off'. CHECK (max_discount_cents IS NULL OR discount_type = 'percent_off')
free_item_variant_ref	text	nullable	—	Loose ref → inventory.item_variant. Required when discount_type IN ('free_item','bogo'); NULL otherwise. OffersService validates at redemption.
min_purchase_cents	bigint	nullable	—	Basket threshold: offer only applies when sale total meets or exceeds this amount. NULL = no minimum.
distribution_type	text	NOT NULL	—	CHECK (distribution_type IN ('broadcast','targeted','code'))
consumer_id	UUID	nullable	—	FK → consumer.consumer. Set only when distribution_type = 'targeted' (single-consumer offer). NULL for broadcast and code offers. CHECK (distribution_type = 'targeted' OR consumer_id IS NULL)
eligibility_json	JSONB	nullable	—	Structured eligibility constraints evaluated at redemption time: new_customer_only, min_tier_level, item_category, day_of_week, hour_range, etc. NULL = no additional eligibility beyond basket/time window. OffersService evaluates at checkout; not FK-enforced.
stacking_policy	text	NOT NULL	'exclusive'	CHECK (stacking_policy IN ('exclusive','stackable','best_price')). Governs how this offer combines with others at checkout.
exclusive_group	text	nullable	—	Two offers sharing the same non-NULL exclusive_group value cannot both apply in the same checkout, even if both are 'stackable'. E.g. 'basket_discount'.
priority	integer	NOT NULL	0	Offer resolution order at checkout when multiple offers qualify. Higher value = evaluated first.
max_redemptions	integer	nullable	—	Total redemption cap across all consumers. NULL = unlimited. OffersService checks COUNT(offer_redemption WHERE offer_id AND redemption_type='redeem').
max_per_consumer	integer	nullable	—	Per-consumer redemption cap. NULL = unlimited.
budget_cents	bigint	nullable	—	Total discount budget in cents. Offer stops accepting new redemptions when budget_used_cents >= budget_cents. NULL = no budget cap.
budget_used_cents	bigint	NOT NULL	0	Maintained cache — reconciles to SUM(discount_amount_applied_cents) over offer_redemption rows for this offer. Never updated directly; maintained by OffersService.
valid_from	timestamptz	nullable	—	Offer window start (inclusive). NULL = no start constraint.
valid_to	timestamptz	nullable	—	Offer window end (exclusive). NULL = no end constraint.
is_active	boolean	NOT NULL	true	Staff soft-disable without deleting. OffersService checks is_active AND (valid_from IS NULL OR valid_from <= now()) AND (valid_to IS NULL OR valid_to > now()) before applying.

Constraints:

• Discount type ↔ field coherence:
  • CHECK ((discount_type = 'percent_off' AND discount_percent IS NOT NULL AND discount_amount_cents IS NULL AND free_item_variant_ref IS NULL) OR (discount_type = 'amount_off' AND discount_amount_cents IS NOT NULL AND discount_percent IS NULL AND free_item_variant_ref IS NULL) OR (discount_type IN ('free_item','bogo') AND free_item_variant_ref IS NOT NULL AND discount_percent IS NULL AND discount_amount_cents IS NULL))
• CHECK (discount_percent IS NULL OR (discount_percent > 0 AND discount_percent <= 100))
• CHECK (max_discount_cents IS NULL OR discount_type = 'percent_off')
• CHECK (distribution_type = 'targeted' OR consumer_id IS NULL)
• CHECK (budget_used_cents >= 0)
• CHECK (budget_used_cents <= budget_cents OR budget_cents IS NULL)

Indexes:

• PK on id
• on (tenant_id) WHERE deleted_at IS NULL
• on (tenant_id, is_active) WHERE deleted_at IS NULL — active-offers list for a tenant
• on (consumer_id) WHERE consumer_id IS NOT NULL — targeted-offer lookup by consumer
• on (distribution_type) WHERE deleted_at IS NULL

---

offers.offer_code — 13 cols

Coupon codes for distribution_type = 'code' offers. Each row is a code string that maps to an offer. A shared broadcast code (consumer_id NULL) is one row reusable by any consumer up to max_redemptions; a per-consumer code (consumer_id set) is single-use and issued to a specific consumer. normalized_code stores the uppercased/trimmed canonical form for case-insensitive lookup; code_display preserves original case for display. Coupon codes are typed or shared (not bearer secrets) — stored as plain text, not hashed.

status lifecycle: 'active' (code can be used), 'exhausted' (per-code max_redemptions reached), 'expired' (past expires_at), 'revoked' (manually invalidated by staff). OffersService evaluates all four at redemption time.

redeemed_count is a maintained cache reconciling to net redemptions: COUNT(redeem rows) - COUNT(reverse rows) for this offer_code_id in offer_redemption. Net count (not gross-redeem-only) correctly restores the available count when a redemption is reversed — a voided-then-re-attempted code is not permanently exhausted. Matches the signed-sum discipline of offer.budget_used_cents. Used by OffersService to check per-code cap and to move status to 'exhausted' when the cap is reached.

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

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant — RLS scope
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
offer_id	UUID	NOT NULL	—	FK → offers.offer
normalized_code	text	NOT NULL	—	Uppercased, trimmed coupon code (e.g. 'SPRING20'). Used for lookup. Plain text — coupons are typed/shared, not secrets.
code_display	text	nullable	—	Original-case version for display (e.g. 'Spring20'). NULL if same as normalized_code.
consumer_id	UUID	nullable	—	FK → consumer.consumer. NULL = shared broadcast code. Set = per-consumer single-use code issued to this consumer.
max_redemptions	integer	nullable	—	Per-code redemption cap. NULL = use offer-level max_per_consumer or no cap.
redeemed_count	integer	NOT NULL	0	Maintained cache — reconciles to COUNT(redeem rows) - COUNT(reverse rows) for this code in offer_redemption (net redemptions). Net count restores usability on void/reversal; matches offer.budget_used_cents signed-sum discipline.
expires_at	timestamptz	nullable	—	Code-specific expiry (may be earlier than offer.valid_to). NULL = inherit offer window.
status	text	NOT NULL	'active'	CHECK (status IN ('active','exhausted','expired','revoked'))

Constraints:

• PARTIAL UNIQUE on (tenant_id, normalized_code) WHERE deleted_at IS NULL — code must be unique per tenant
• CHECK (redeemed_count >= 0)

Indexes:

• PK on id
• PARTIAL UNIQUE on (tenant_id, normalized_code) WHERE deleted_at IS NULL
• on (offer_id) — codes for an offer
• on (consumer_id) WHERE consumer_id IS NOT NULL — per-consumer codes lookup

---

offers.offer_assignment — 15 cols

Tracks the per-consumer lifecycle of a targeted offer: issued → viewed → claimed → redeemed (or expired / cancelled). Required for distribution_type = 'targeted' offers. Also used for 'broadcast' opt-in flows (consumer actively claims a broadcast offer before presenting it at checkout). Answers what offer_redemption cannot: "which offers has this consumer been issued and not yet used?"

status lifecycle: 'issued' (offer pushed to consumer; not yet seen), 'viewed' (consumer opened/saw the offer in the app), 'claimed' (consumer actively accepted/clipped the offer), 'redeemed' (offer used — offer_redemption row exists), 'expired' (assignment-level expires_at passed before redemption), 'cancelled' (revoked by staff before use).

source records why the assignment was created: 'targeted' (staff-created direct targeting), 'broadcast_optin' (consumer opted in from a broadcast offer), 'birthday' (triggered by birthday milestone), 'campaign' (bulk campaign issuance), 'manual' (staff-issued one-off).

eligibility_snapshot_json captures the eligibility state at issue time (e.g. {tier: "Gold", new_customer: false}). Supports after-the-fact audit of why a consumer was eligible when issued.

ConsumerService cross-store read: (consumer_id) index supports ConsumerService querying a consumer's active assignments across all tenants (service-role, bypassing tenant RLS) to render the consumer app's "Available Offers" view.

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

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant — RLS scope
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
offer_id	UUID	NOT NULL	—	FK → offers.offer
consumer_id	UUID	NOT NULL	—	FK → consumer.consumer — who this offer was issued to
offer_code_id	UUID	nullable	—	FK → offers.offer_code. The per-consumer code for this assignment, if code-based. NULL for non-code targeted offers.
status	text	NOT NULL	'issued'	CHECK (status IN ('issued','viewed','claimed','redeemed','expired','cancelled'))
assigned_at	timestamptz	NOT NULL	—	When the assignment was created (issued to the consumer)
viewed_at	timestamptz	nullable	—	When the consumer first opened/saw the offer. NULL = not yet viewed.
claimed_at	timestamptz	nullable	—	When the consumer actively clipped/accepted the offer. NULL = not yet claimed.
expires_at	timestamptz	nullable	—	Assignment-specific expiry (may differ from offer.valid_to). NULL = inherit offer window.
source	text	NOT NULL	—	CHECK (source IN ('targeted','broadcast_optin','birthday','campaign','manual'))
eligibility_snapshot_json	JSONB	nullable	—	Eligibility state captured at issue time. Audit record; not evaluated at redemption.

Constraints:

• PARTIAL UNIQUE on (tenant_id, offer_id, consumer_id) WHERE deleted_at IS NULL — one active assignment per offer per consumer (relax in a later phase for re-issuable/recurring offers)

Indexes:

• PK on id
• PARTIAL UNIQUE on (tenant_id, offer_id, consumer_id) WHERE deleted_at IS NULL
• on (consumer_id) — consumer's available-offers view (ConsumerService cross-store read)
• on (offer_id, status) WHERE deleted_at IS NULL — offer's assigned/active list
• on (status) WHERE status IN ('issued','claimed') AND deleted_at IS NULL — active-assignment sweep

---

offers.offer_redemption — 13 cols

Append-only redemption ledger. Records every redemption event and every reversal event as immutable rows. A refund or void of a sale triggers a new 'reverse' row (signed-negative discount_amount_applied_cents, reversed_redemption_id pointing to the original) — the original row is never mutated. This is the source of truth for offer.budget_used_cents and per-consumer redemption counts.

redemption_type: 'redeem' = offer applied at checkout (positive amount). 'reverse' = refund/void of a prior redemption (negative amount, reversed_redemption_id required).

discount_amount_applied_cents is signed: positive for 'redeem', negative for 'reverse'. The absolute value reflects what was actually deducted from the sale total (may be less than the offer face value if min_purchase_cents near-miss or max_discount_cents cap applied).

Idempotency guard: the partial unique on (tenant_id, offer_id, consumer_id, sale_id) WHERE redemption_type = 'redeem' prevents double-redemption on POS retry. Reversals are exempt — a sale can be reversed once.

The redemption seam: sale_id → pos.sale is the seam between offers and pos. The FK lives on the offers side — pos.sale requires no new columns. This is the same reference-don't-copy pattern as rewards.points_ledger.sale_id → pos.sale.

Append-only discipline: no updated_at, no deleted_at. Any correction is a new row.

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

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant — RLS scope
created_at	timestamptz	NOT NULL	now()	Append-only — no updated_at, no deleted_at
offer_id	UUID	NOT NULL	—	FK → offers.offer
consumer_id	UUID	NOT NULL	—	FK → consumer.consumer — who redeemed
offer_assignment_id	UUID	nullable	—	FK → offers.offer_assignment. The assignment fulfilled by this redemption, if the offer was assigned. NULL for broadcast/code offers without an assignment.
offer_code_id	UUID	nullable	—	FK → offers.offer_code. The code presented at checkout, if code-based. NULL for non-code redemptions.
sale_id	UUID	NOT NULL	—	FK → pos.sale — THE REDEMPTION SEAM. The POS sale at which the offer was redeemed. FK on offers side; pos.sale has no offer seam columns.
redemption_type	text	NOT NULL	'redeem'	CHECK (redemption_type IN ('redeem','reverse'))
discount_amount_applied_cents	bigint	NOT NULL	—	Signed: positive for 'redeem', negative for 'reverse'. The actual discount deducted (may be less than offer face value after cap/threshold logic).
reversed_redemption_id	UUID	nullable	—	FK → offers.offer_redemption (self-ref). Required for 'reverse' rows — which prior redemption is being reversed. NULL for 'redeem' rows.
redeemed_at	timestamptz	NOT NULL	—	Timestamp of the redemption event.
note	text	nullable	—	Reversal reason or context. Required for 'reverse' rows (enforced by CHECK); optional for 'redeem'.

Constraints:

• CHECK (redemption_type <> 'reverse' OR reversed_redemption_id IS NOT NULL)
• CHECK ((redemption_type = 'redeem' AND discount_amount_applied_cents > 0) OR (redemption_type = 'reverse' AND discount_amount_applied_cents < 0))
• CHECK (redemption_type <> 'reverse' OR note IS NOT NULL)
• PARTIAL UNIQUE on (tenant_id, offer_id, consumer_id, sale_id) WHERE redemption_type = 'redeem' — idempotency guard: prevents double-redemption on POS retry

Indexes:

• PK on id
• PARTIAL UNIQUE on (tenant_id, offer_id, consumer_id, sale_id) WHERE redemption_type = 'redeem'
• on (offer_id) — redemption history for an offer; source for budget_used_cents reconciliation
• on (consumer_id) — consumer's redemption history
• on (sale_id) — reverse-lookup: "what offers were redeemed on this sale?"
• on (offer_assignment_id) WHERE offer_assignment_id IS NOT NULL
• on (reversed_redemption_id) WHERE reversed_redemption_id IS NOT NULL — check if a redemption has already been reversed

---

Column counts: offer(27) + offer_code(13) + offer_assignment(15) + offer_redemption(13) = 68

---

Deferred items

Item	Deferred to
Vrida-funded offers + settlement	v1.5 — funding_source = 'vrida' enum value exists for forward-compat; v1 service layer guards it. Settlement (Vrida→merchant reimbursement) is platform's domain; offers feeds it, does not build a settlement engine. Closes the open settlement-economics question from PROJECT_DECISIONS "Consumer Layer Architecture (2026-06-09)": deferred, platform-owned-when-built.
Cross-tenant Vrida offers (tenant_id NULL, redeemable at any nursery)	Future — v1 all offers are tenant-scoped. Cross-tenant Vrida offer would require nullable tenant_id and service-role issuance; design at that time.
offer_eligibility_rule table (structured eligibility rules)	Future — v1 uses eligibility_json on offer. Add a structured rule table when eligibility rules become query-heavy or need merchant-facing rule-builder UI.
A/B testing, suppression logs, audience snapshots	Future — analytics/experimentation concern
Offer conversion / attribution events	Future — reporting/analytics; offer_redemption provides the redemption fact; attribution funnel is a separate analytics concern
Offer settlement ledger / funding invoice table	NEVER in this module — offers feeds platform; platform owns the Vrida→tenant credit when built
Consent table	NONE — OffersService reads crm.customer_consent (consent_type = 'offers'); zero consent tables in offers
Stale service-boundary note in PROJECT_DECISIONS	Flag at offers lock — RewardsService.redeem(consumer_id, offer_id, amount) (line ~924) is pre-design notation; post-offers the correct seam is OffersService.redeem(consumer_id, offer_id, sale_id) called in parallel with RewardsService.redeem() from POSService