41 — TestimonialCampaignProduct Model & Targeting Rules (Blueprint §5.3)

41 — TestimonialCampaignProduct Model & Targeting Rules (Blueprint §5.3)

Source: 02-Implementation-Blueprint.md — §5.3 New model: TestimonialCampaignProduct.

This document is a build spec only. No code changes are implied until a task references this file.

Related: 18 (campaign create/edit), 17 (campaigns list), 07 (request generation), 20 (schema plan), 39 (request model).

---

0) Goal (one sentence)

Implement TestimonialCampaignProduct as the canonical mapping table for product-targeted campaign eligibility so webhook-driven request creation can reliably include or exclude line items.

---

1) Blueprint model recap

Blueprint §5.3 fields:

• id String @id @default(uuid())
• campaignId String @map("campaign_id")
• shopId String @map("shop_id")
• shopifyProductId String @map("shopify_product_id")
• createdAt DateTime @default(now()) @map("created_at")

Unique:

• @@unique([campaignId, shopifyProductId])

---

2) Scope boundaries

In scope

• model + migration
• campaign-targeting save/update contracts
• webhook eligibility query behavior
• list/detail UI usage for “N targeted products”

Out of scope

• collection targeting storage (covered separately if introduced)
• advanced dynamic rules (tags, price ranges, etc.)

---

3) Purpose in eligibility engine

Campaign audience rules:

1. If allProducts=true: mapping table is ignored.
2. If allProducts=false: only line items whose shopifyProductId exists in this mapping are eligible.

This table is therefore an allowlist for campaign-product relationships.

---

4) Data integrity rules

4.1 Unique pair

(campaignId, shopifyProductId) must be unique.

4.2 Tenant consistency

shopId in mapping row must match campaign’s shopId.

Enforce in application logic and optionally via composite FK pattern if desired.

4.3 Referential behavior

When campaign is deleted/archived:

• mapping rows should be removed (cascade) or ignored if campaign inactive.

Recommendation:

• cascade on campaign deletion.

---

5) Write contract (campaign edit screen)

When saving campaign targets:

1. Validate selected product IDs (normalize gid/numeric format).
2. Dedupe product list in memory.
3. Replace mapping set transactionally:
   • delete old rows for campaign
   • insert current rows

Use transaction to prevent partial audience corruption.

5.1 Save behavior with allProducts=true

Option A (recommended):

• retain existing rows but ignore them at runtime.

Option B:

• clear rows when allProducts toggled on.

Pick one and keep consistent in UI messaging.

---

6) Read/query patterns

6.1 Campaign list (Screen 2)

Need count of mapped products for scope label:

• “All products” or “N products”.

6.2 Campaign edit (Screen 3)

Need selected product IDs preloaded for picker chips.

6.3 Webhook eligibility

Given campaign and order line items:

• compare normalized line shopifyProductId against mapping set.

Avoid per-line DB query; prefetch set once per campaign.

---

7) Product ID normalization policy

Because Shopify IDs can be numeric or GID:

• choose canonical storage format for shopifyProductId in this table (recommended: string numeric or full GID consistently).
• normalize both:
  • values saved from UI picker
  • line item values from webhook payload

Mismatch here causes silent eligibility bugs.

---

8) Performance guidance

Recommended indexes:

• keep unique (campaignId, shopifyProductId)
• add optional @@index([shopId, campaignId]) if query volume grows
• use batched inserts for large target lists

During webhook processing:

• fetch all mapping rows for campaign once, convert to Set.

---

9) Edge cases

1. Product deleted from Shopify after mapping:

   • mapping can remain stale but harmless; eligibility won’t match absent line items.
   • optional cleanup job can remove stale product ids.

2. Campaign with allProducts=false and empty mapping:

   • treat as invalid for activation (block in Screen 3).

3. Duplicate writes from concurrent edits:

   • transaction + unique constraint avoid corruption.

---

10) Acceptance criteria

• Mapping rows persist correctly when selecting products.
• Duplicate product selection does not create duplicate rows.
• Eligibility engine respects mapping when allProducts=false.
• allProducts=true bypasses mapping table reliably.
• Campaign list shows correct audience scope labels.

---

11) Suggested implementation order (for Cursor)

1. Add Prisma model + migration for TestimonialCampaignProduct.
2. Add campaign edit save handler for mapping replacement.
3. Add campaign list audience count display.
4. Integrate eligibility lookup in webhook request planner.
5. Add ID normalization helper and tests.

---

12) References

• 02-Implementation-Blueprint.md — §5.3
• 18-campaign-create-edit-screen-3.md
• 17-campaigns-list-screen-2.md
• 07-email-sms-request-delivery-pipeline.md
• 39-testimonial-request-model-blueprint-section-5-5.md

---

13) Note on numbering

This folder already includes 05 through 40 plans. This file is 41-....