39 — TestimonialRequest Model & Scheduling Lifecycle (Blueprint §5.5)

39 — TestimonialRequest Model & Scheduling Lifecycle (Blueprint §5.5)

Source: 02-Implementation-Blueprint.md — §5.5 New model: TestimonialRequest.

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

Related: 07 (pipeline), 06 (token submit), 13 (requests log), 32 (operational behavior), 38 (request events), 20 (schema baseline).

---

0) Goal (one sentence)

Implement TestimonialRequest as the canonical outreach unit that tracks per-product messaging lifecycle from scheduling to sent/clicked/submitted outcomes with robust retry and audit support.

---

1) Blueprint model recap

Blueprint §5.5 fields:

• identity: id, shopId, campaignId
• commerce links: shopifyOrderId, shopifyProductId, shopifyVariantId, shopifyCustomerId
• recipient: customerEmail, customerPhone
• token/link: submissionToken, submissionUrl
• state/timestamps:
  • status (scheduled|sent|clicked|submitted|expired|failed)
  • scheduledFor
  • sentAt
  • clickedAt
  • submittedAt
  • reminderCount
  • lastError
• timestamps: createdAt, updatedAt

Required indexes:

• @@index([shopId, status])
• @@index([shopId, scheduledFor])
• @@index([campaignId])
• @@index([shopId, shopifyProductId])

---

2) Scope boundaries

In scope

• model + migration
• request creation contract from webhook campaigns
• scheduling/reminder fields and lifecycle semantics
• status transitions and integrity checks

Out of scope

• provider-specific event payload storage (handled by TestimonialRequestEvent)
• submission content (handled by TestimonialSubmission)

---

3) Canonical meaning of a request row

One row represents:

• one outreach opportunity
• for one shop
• tied to one campaign
• for one specific product within an order context
• with one tokenized submission link

This row is the single control object for sending + reminder decisions.

---

4) Creation contract (from webhook/campaign engine)

When a qualifying order event is processed:

1. evaluate campaigns
2. expand to eligible line items
3. create one TestimonialRequest row per unit/rule (per your chosen quantity policy)

4.1 Required defaults on create

• status='scheduled'
• scheduledFor computed from trigger + delayDays
• submissionToken generated (high entropy)
• submissionUrl built from app URL + /t/:token
• reminderCount=0

4.2 Idempotency

Prevent duplicates with business key or upsert strategy, e.g.:

• (shopId, campaignId, shopifyOrderId, shopifyProductId, shopifyVariantId?)

Exact uniqueness strategy should be documented in pipeline code.

---

5) Status lifecycle contract

Recommended progression:

• scheduled -> sent
• sent -> clicked
• clicked -> submitted
• failure/terminal paths: failed, expired

Notes:

• clicked can happen without provider click tracking (set on /t/:token load).
• submitted set when submission API commits successfully.

Keep status progression monotonic unless explicit repair action is introduced.

---

6) Reminder policy fields

reminderCount and schedule logic should obey campaign config:

• reminders allowed only if campaign reminderEnabled
• stop when submittedAt set
• stop when reminderCount >= maxReminders

Recommendation:

• do not overload status for reminder attempts; track attempts via reminderCount + events.

---

7) Token and URL contract

7.1 Token requirements

• submissionToken unique, opaque, non-guessable
• never derived from order id alone

7.2 URL requirements

• absolute URL stored in submissionUrl
• points to public route /t/:token
• if app URL changes, decide whether to:
  • regenerate future rows only, or
  • migrate existing unresolved request URLs

Document chosen policy.

---

8) Field quality guidance

8.1 customerEmail / customerPhone

• keep nullable to support whichever channel is available
• validate formats before send attempts

8.2 lastError

• human-readable latest error summary for support UI
• full provider payload should go to events payload/logs, not only this field

8.3 shopify* IDs

• normalize ID format (gid vs numeric string) consistently across app.

---

9) Query usage by screen/process

• Sender worker: where status in (scheduled, failed?) and scheduledFor <= now
• Requests log: status/date/channel/campaign filters
• Submission flow: resolve by submissionToken, update clicked/submitted timestamps
• Analytics rollups: sent/clicked/submitted daily counts

Use required indexes to keep these queries efficient.

---

10) Compliance and retention notes

• rows contain customer contact and order identifiers; treat as personal data.
• GDPR customer redact path should scrub or delete request rows tied to customer identifiers per policy.
• shop redact should remove all rows via tenant cascade.

Align with 08-security-compliance-and-privacy.md.

---

11) Acceptance criteria

• Webhook creates correct request rows with token, URL, and schedule.
• Duplicate webhook replay does not duplicate request rows.
• Status transitions update expected timestamps (sentAt, clickedAt, submittedAt).
• Reminder count increments and respects max reminder policy.
• Requests log can diagnose failed rows using status + lastError.

---

12) Suggested implementation order (for Cursor)

1. Add Prisma model and migration for TestimonialRequest.
2. Implement token generator + submission URL builder.
3. Implement request creation service from campaign evaluation.
4. Wire sender worker to update request status/timestamps.
5. Wire token page/submit APIs to set clicked/submitted fields.
6. Integrate with events model for detailed timeline (38).

---

13) References

• 02-Implementation-Blueprint.md — §5.5
• 07-email-sms-request-delivery-pipeline.md
• 06-public-submission-page-screen-13.md
• 13-requests-log-blueprint-screen-10.md
• 38-testimonial-request-event-model-blueprint-section-5-6.md

---

14) Note on numbering

This folder already includes 05 through 38 plans. This file is 39-....