38 — TestimonialRequestEvent Model & Delivery Event Stream (Blueprint §5.6)

38 — TestimonialRequestEvent Model & Delivery Event Stream (Blueprint §5.6)

Source: 02-Implementation-Blueprint.md — §5.6 New model: TestimonialRequestEvent.

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

Related: 07 (delivery pipeline), 13 (requests log screen), 32 (operational workflow), 34 (daily analytics rollups), 20 (schema).

---

0) Goal (one sentence)

Implement TestimonialRequestEvent as a reliable, append-only timeline of messaging events so delivery observability, troubleshooting, and analytics are accurate and auditable.

---

1) Blueprint model recap

Blueprint §5.6 fields:

• id String @id @default(uuid())
• shopId String @map("shop_id")
• requestId String @map("request_id")
• eventType String @map("event_type") // queued | sent | delivered | opened | clicked | failed
• eventAt DateTime @default(now()) @map("event_at")
• providerMessageId String? @map("provider_message_id")
• payload Json?

Index:

• @@index([requestId, eventAt])

---

2) Scope boundaries

In scope

• event model + migration
• event write helper contract
• provider webhook event ingestion mapping
• event dedupe rules
• requests log timeline usage

Out of scope

• cross-provider normalization for every vendor-specific edge case
• external event bus integration

---

3) Event taxonomy (canonical)

Base events from blueprint:

• queued
• sent
• delivered
• opened
• clicked
• failed

Optional internal events (if needed):

• scheduled (if you want pre-queue observability)
• submitted (mirrors request status transition after form submit)
• retrying

Keep a central enum/constants module for event types used by sender, webhook callbacks, and UI.

---

4) Event write contract

Create a shared helper:

appendRequestEvent({ shopId, requestId, eventType, eventAt?, providerMessageId?, payload? })

Rules:

1. request must belong to shopId.
2. event rows are append-only.
3. if eventAt omitted, default now.
4. payload should be compact and sanitized (no secrets).

Use this helper everywhere to avoid inconsistent event writes.

---

5) Event sources

5.1 Internal system-generated events

• queued when worker picks due request
• sent when provider accepts send
• failed when provider call errors
• clicked when token page is opened (if using event stream in addition to clickedAt)

5.2 Provider callback events

Webhook/callback handlers map provider statuses into canonical events:

• provider delivered -> delivered
• provider open -> opened
• provider click -> clicked (if tracked at provider level)
• provider bounce/reject -> failed

Store original provider payload in payload with redaction as needed.

---

6) Deduplication and idempotency

Provider callbacks can replay. Add dedupe logic:

6.1 Preferred approach

Before insert, check recent duplicate by:

• requestId
• eventType
• providerMessageId (when present)
• near-identical eventAt window

6.2 Optional hard guarantee

Add unique constraint on a synthetic hash column (future) if duplicate volume becomes a problem.

6.3 Replay-safe behavior

Duplicate event should be no-op and still return success response to provider.

---

7) Timeline semantics

Requests log (13) and support tooling should treat events as timeline, not single status source.

7.1 Latest event

• last event by eventAt can be used for “Last event timestamp”.

7.2 Status reconciliation

• TestimonialRequest.status remains primary operational status.
• event stream provides detailed audit and provider-level context.

---

8) Payload policy

payload Json guidelines:

• include provider response identifiers and normalized metadata
• exclude secrets/API keys
• truncate oversized payloads if needed
• redact personal data not needed for debugging

Keep payload size manageable to avoid DB bloat.

---

9) Query patterns

9.1 Request detail timeline

where: { requestId, shopId } ordered eventAt desc.

9.2 Rollup/analytics

Use grouped counts by eventType + day (UTC), or rely on TestimonialDailyAnalytics rollup jobs.

9.3 Operational debugging

Filter by:

• eventType=failed
• date range
• campaign/request ids

---

10) Integration points

1. 07 sender worker writes queued/sent/failed.
2. 06 token page can write clicked event.
3. provider callback route writes delivered/opened/clicked.
4. 13 requests log displays last event + event timeline.
5. 34 rollup job consumes events (optional for clicked/opened counts).

---

11) Acceptance criteria

• Every send attempt writes at least one event row.
• Provider callback events map to canonical event types.
• Duplicate callback replay does not create duplicate timeline entries.
• Requests log can show accurate last-event timestamp.
• Event payloads are present but sanitized.

---

12) Suggested implementation order (for Cursor)

1. Add Prisma model and migration for TestimonialRequestEvent.
2. Implement shared append helper.
3. Integrate helper into sender worker.
4. Implement provider callback mapping and dedupe.
5. Add timeline view in requests log.
6. Add event-based analytics rollup hooks if needed.

---

13) References

• 02-Implementation-Blueprint.md — §5.6
• 07-email-sms-request-delivery-pipeline.md
• 13-requests-log-blueprint-screen-10.md
• 32-email-sms-operational-detail-blueprint-section-4.md
• 34-testimonial-daily-analytics-model-blueprint-section-5-11.md

---

14) Note on numbering

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