Design Rationale — Offers
Non-obvious design choices for the offers module — the WHY behind each decision.
Offers Module
Third module of the Consumer Layer. Tenant-scoped schema offers. Locked 2026-06-12.
DR-O1 — Tenant-scoped with funding_source flag (not platform-level)
Decision: All offers.* tables are TENANT-SCOPED (tenant_id NOT NULL, RLS WHERE tenant_id = current_setting('app.current_tenant_id')::UUID). funding_source ('business'/'vrida') records who absorbs the cost of the discount — it does NOT determine where the offer is redeemable. Every offer is issued and redeemed at a specific nursery. Cross-tenant Vrida offers (redeemable at any nursery, tenant_id NULL) are a future feature.
Why: Settlement invoices flow between Vrida and a specific nursery: Vrida subsidizes a discount given at Nursery X, and Nursery X is reimbursed. The specific nursery is always involved. A platform-level offer (nullable tenant_id) is a different product surface with different operational semantics — putting it in the same schema would require nullable RLS scope and bifurcated service logic. Per-nursery first; platform-level offers are a future extension.
Rejected: Option B (platform-level, cross-tenant offers). The "settlement invoices flow between Vrida and the nursery" framing (PROJECT_DECISIONS DR6) presupposes a specific nursery. Cross-tenant offers require NULL tenant_id and service-role issuance — a separate design decision when that product surface is built.
Guard: offers.* RLS predicate is ALWAYS WHERE tenant_id = current_setting('app.current_tenant_id')::UUID. Never make tenant_id nullable on any offers.* table in v1. If cross-tenant Vrida offers are introduced later, design them as a separate entry point (nullable tenant_id) with service-role handling — do not retrofit tenant-scoped tables.
DR-O2 — Merchant-funded v1; settlement deferred to v1.5; closes the open settlement-economics question
Decision: v1 offers are merchant-funded only (funding_source = 'business'). The 'vrida' value exists in the CHECK enum for forward-compatibility but the v1 service layer guards and rejects it. Settlement (Vrida→merchant credit when funding_source = 'vrida') is platform's domain — offers records the redemption fact and funding_source; it feeds settlement, does not build a settlement engine. This closes the open settlement-economics question from Consumer Layer Architecture (2026-06-09).
Why: Platform already owns the Vrida↔tenant financial relationship (SaaS billing: billing_account, subscription_invoice, payment). Vrida crediting a nursery for a co-funded discount is the inverse direction of the same relationship — a new concept that belongs under platform when designed. Building settlement infrastructure in offers would invert ownership (offers has no authority over Vrida's financial ledger) and couple offers migrations to platform financial flows. Merchant-funded offers require no settlement — they are fully self-contained at the nursery.
Rejected: Including settlement tables (credit/invoice/ledger) in the offers module. Wrong ownership — offers is a consumer-facing promotional tool; platform is the Vrida↔tenant financial layer. A settlement engine in offers would either duplicate platform's financial data or leave it dangling with no authoritative home.
Guard: No settlement/credit/invoice/funding-ledger table should ever be added to the offers schema. If Vrida-funded offers are introduced in v1.5, the settlement infrastructure belongs in platform (or a dedicated settlement sub-module under platform). offers contributes redemption-fact rows; platform reads them to compute settlement invoices.
DR-O3 — offer_redemption is append-only; reversals are new signed rows
Decision: offer_redemption has no updated_at and no deleted_at. Every redemption and every reversal is a new INSERT. A voided sale triggers a 'reverse' row with signed-negative discount_amount_applied_cents and reversed_redemption_id pointing to the original — the original row is never mutated.
Why: Redemptions are financial events. Once recorded, the row must be immutable — the same invariant as billing.ar_payment_application, rewards.points_ledger, and pos.gift_card's ledger. Mutability would allow silent budget manipulation: a 'redeem' row that gets retroactively negated would break the SUM(discount_amount_applied_cents) = budget_used_cents reconciliation invariant. Immutability is what makes budget_used_cents and redeemed_count trustworthy as maintained caches.
Rejected: Status mutation on the original redemption row (e.g. setting status = 'reversed'). This would lose the reversal audit trail, break reconciliation (you can't SUM a status field), and make the cache formula non-obvious. A new negative row is better: it appears in the ledger, carries a timestamp, and reconciliation remains a simple SUM.
Guard: Never add updated_at or deleted_at to offer_redemption. Never issue UPDATE on an offer_redemption row. If a correction is needed, INSERT a 'reverse' row with note explaining the reason. The note IS required for 'reverse' rows (enforced by CHECK) — reversal accountability is a hard constraint, not a social contract.
DR-O4 — budget_used_cents and redeemed_count as reversal-aware reconcilable caches
Decision: offer.budget_used_cents reconciles to SUM(discount_amount_applied_cents) over offer_redemption rows for this offer — a signed sum that naturally nets out reversals (reverse rows are negative). offer_code.redeemed_count reconciles to COUNT(redeem rows) - COUNT(reverse rows) for this offer_code_id — a net count that correctly decrements on reversal. Both are maintained by OffersService on every ledger insert.
Why: A gross-count-only formula for redeemed_count (counting only 'redeem' rows) would permanently exhaust a single-use code if the purchase is voided — the consumer redeemed and was reversed, but the code stays locked out. The net count (mirroring the signed-sum of budget_used_cents) ensures a voided-then-re-attempted redemption works as expected. Consistency between the two caches: both are reversal-aware.
Why (fast cache reads): O(1) reads for budget check and code-cap check at POS checkout, without scanning the full offer_redemption ledger on every transaction. Same pattern as loyalty_account.balance_points, pos.gift_card.balance_cents, billing.ar_account.balance_cents. The ledger is always the reconciliation source of truth if caches diverge.
Guard: OffersService must maintain both caches on every offer_redemption insert: increment budget_used_cents on 'redeem', decrement on 'reverse'; increment redeemed_count on 'redeem', decrement on 'reverse'. Never use gross-only counts. Budget and code-availability checks at checkout must use the cache values for O(1) performance; reconciliation (e.g. daily audit job) compares cache to SUM/COUNT over the ledger.
DR-O5 — offer_assignment is the before-redemption lifecycle table
Decision: offer_assignment exists as a separate table from offer_redemption to track the pre-use lifecycle: issued → viewed → claimed → available. It is NOT redundant with offer_redemption (post-use ledger).
Why: offer_redemption records what happened at checkout. offer_assignment records what was issued, when it was seen, and whether the consumer accepted it — state that exists before any redemption occurs and independently of whether redemption ever happens. The consumer app's "Available Offers" view requires offer_assignment (show unredemed assignments); offer_redemption cannot answer "which offers can this consumer use right now?" because it only has rows for offers already used. The source column records campaign provenance (birthday trigger, staff manual, broadcast opt-in) — audit information that predates the redemption event.
Rejected: Embedding pre-use state in offer_redemption (e.g. adding a status = 'issued' row before redemption). offer_redemption is append-only with no deleted_at — pre-use rows that never get redeemed would pollute the ledger. The append-only discipline and the issued-state discipline are incompatible in one table.
Guard: offer_assignment is the lifecycle table (mutable, has updated_at/deleted_at, tracks status transitions). offer_redemption is the ledger (append-only, insert-once). Do not merge them. If a targeted offer is cancelled before use, the offer_assignment row is soft-deleted; the offer_redemption ledger is untouched.
DR-O6 — Redemption seam on the offers side; no POS additive touch
Decision: offer_redemption.sale_id → pos.sale is the redemption seam between offers and pos. The FK lives entirely on the offers side. pos.sale has no offer_redemption_id, discount_code_applied, or any other offer seam column. pos.sale was not modified at offers lock.
Why: The reference-don't-copy pattern: the redemption references the sale. The authoritative record of what was applied is in offer_redemption; POS does not need to know. This avoids an additive touch to the locked POS schema — pos.sale is locked 2026-06-10 and has 19 tables; additive touches should be minimized. Contrast with rewards: pos.sale.points_earned and pos.sale.points_redeemed were pre-wired at POS lock as receipt-level snapshots. Offers has no equivalent — the redemption record is self-contained on the offers side.
Rejected: Adding offer_redemption_id or applied_offer_ref to pos.sale. An additive touch to POS would be necessary only if POS needs to carry the offer reference on the sale row for reporting or downstream use. All post-redemption lookups (what offer was applied to this sale?) can be answered from offer_redemption WHERE sale_id = ? — no POS column needed.
Guard: Do not add offer seam columns to pos.sale unless a compelling POS-side requirement emerges. The offer_redemption WHERE sale_id = ? reverse-lookup index is the intended query path. The seam is DONE (FK enforced at offers lock) without any POS modification.
DR-O7 — Consent lives in CRM; OffersService reads crm.customer_consent
Decision: There is no consent or opt-in table in offers. OffersService reads crm.customer_consent (consent_type = 'offers') before targeting or issuing any offer. This is the third instance of the CRM-consent pattern (after notifications reading 'marketing' consent and rewards reading 'loyalty' consent).
Why: Consent is a per-nursery customer attribute — it belongs next to the customer record in crm, not scattered across every feature module that needs it. A separate consent table in offers would create consent drift (CRM has one value, offers has another) and duplicate the consent-log audit trail that already exists in crm.customer_consent. The CRM-owns-consent pattern is locked three times now.
Guard: Do not create a consent, marketing-preferences, or opt-in table in offers. If offers consent is needed as a new consent category, add consent_type = 'offers' to crm.customer_consent — not a new table. OffersService must check consent before every targeted issuance and before sending any offer notification.
DR-O8 — Distinct from pricing and rewards; stacking_policy governs combination
Decision: The three discount mechanisms are structurally separate: (1) pricing.price_rule = predefined auto-applied pricing rules (resolved at line-item price resolution by PricingService). (2) rewards.reward_option = points-funded redemption catalog (points-denominated, nursery-funded). (3) offers.offer = consumer-presents-and-claims coupon/promo (discrete issued instruments). All three can co-apply to a single checkout. stacking_policy ('exclusive'/'stackable'/'best_price') and exclusive_group on offer govern how multiple offers combine. OffersService and RewardsService are parallel services — both called by POSService at checkout, neither a sub-service of the other.
Why: The mechanisms operate at different layers and on different triggers. Pricing resolves at line-item price resolution regardless of consumer identity. Points redemption is triggered by a consumer spending accumulated balance. Coupon/promo issuance is triggered by a consumer presenting a code or claiming an assignment. Conflating them would force unrelated state (points balance, promo codes, pricing tiers) into one table — wrong ownership at every level.
Guard: Never merge offers and rewards tables. Never merge offers and pricing.price_rule. The stacking_policy is a runtime combination policy — it does not imply schema consolidation. If a future feature seems to blend the mechanisms (e.g. "earn points on a coupon redemption"), that is a service-layer event (OffersService emits offer_redeemed → RewardsService processes earn rule) — not a schema merge.
DR-O9 — Plain coupon codes; not hashed
Decision: offer_code.normalized_code is stored as plain text (uppercased, trimmed). Coupon codes are not hashed.
Why: Coupon codes are shareable, typeable instruments — they are intended to be distributed, written on receipts, shared in emails, and entered by customers at checkout. They are NOT bearer secrets (unlike identity.api_key.token_hash, which is SHA-256 hashed because the raw key must only be shown once and never stored). Hashing a coupon code would prevent case-insensitive substring search, human-readable auditing, and support lookups. The normalized_code (uppercase/trim) enables consistent case-insensitive matching without hashing.
Rejected: Hashing coupon codes. Hashing is appropriate for secrets (API keys, tokens, passwords) where the plaintext must never be stored. Coupon codes are not secrets — they are printed on promotional materials. Applying the same hashing pattern would be security theater: it protects nothing (the "secret" is printed on a flyer) while destroying lookup utility.
Guard: Never hash normalized_code. If a future feature requires genuine single-use bearer tokens (e.g. "one-time use magic links for offers"), those belong in a separate column (hashed, like api_key.token_hash) — not in normalized_code. The distinction between coupon codes (plain, shareable) and bearer tokens (hashed, single-secret) must be maintained.