13 — Requests Log (Blueprint Screen 10, `/app/testimonials/requests`)

13 — Requests Log (Blueprint Screen 10)

Source: 02-Implementation-Blueprint.md — Screen 10 - Requests Log (/app/testimonials/requests).

Naming note: This is blueprint “Screen 10” (requests / delivery). It is not the same as the repo file 10-submission-detail-screen-6.md (blueprint Screen 6).

Product alignment: 01-Post-Purchase-Video-Testimonial-Collector-Plan.md — §6 Messaging / observability, §11 Analytics (raw events feed funnels later).

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

Prerequisites: TestimonialRequest, TestimonialRequestEvent, TestimonialCampaign (§5.5–5.6, §5.2) populated by 07-email-sms-request-delivery-pipeline.md.

---

0) Goal (one sentence)

Merchants open /app/testimonials/requests to audit every outreach row (per product / order / customer): see channel, pipeline status, failure reason, sent time, and last event time, with filters for support and debugging.

---

1) Route and file

Item	Value
Suggested file	app/routes/app.testimonial-requests.jsx (blueprint §6).
URL	/app/testimonials/requests.

Auth: authenticate.admin(request); scope all queries by shopId from session.

Nav: label “Requests” or “Outreach log” under the testimonial admin section.

---

2) Data sources

2.1 Primary row: TestimonialRequest (§5.5)

Columns to surface (direct or derived):

Blueprint UI column	Source
Request ID	TestimonialRequest.id (short preview + copy-friendly full id in detail tooltip).
Shopify product ID	shopifyProductId
Product title	Join Product on (shopId, shopifyProductId) → title; fallback "—".
Customer	customerEmail and/or customerPhone (mask phone if desired); optional customer name if stored later.
Order	Display shopifyOrderId formatted (short) or order name if you denormalize from webhook payload into a new column orderName — open: add orderNameSnapshot on request row in migration for nicer UX.
Campaign	Join TestimonialCampaign.name via campaignId.
Channel	TestimonialCampaign.channel (email | sms | both) — if “both”, show badge Email+SMS or two sub-rows — v1: single row, channel from campaign.
Status	See §3 status mapping.
Failure reason	TestimonialRequest.lastError (truncate in table; full text in modal).
Sent timestamp	sentAt
Last event timestamp	Max TestimonialRequestEvent.eventAt for requestId, or denormalized lastEventAt on request (optional perf optimization).

2.2 Events: TestimonialRequestEvent (§5.6)

Use for drill-down (optional v1): side sheet listing recent events (eventType, eventAt, payload).

---

3) Status mapping (blueprint vs schema)

Blueprint Screen 10 lists: queued, sent, delivered, opened, clicked, failed.

§5.5 lists: scheduled | sent | clicked | submitted | expired | failed (and implied pre-send states).

Define a single mapping in code (document in a small REQUEST_STATUS_LABELS map):

Internal status (DB)	Row “Status” label (UI)	Notes
scheduled	Queued	Not sent yet; matches blueprint “queued”.
sent	Sent	Initial message dispatched.
n/a	Delivered	Only if provider confirms — map from latest event delivered or leave “—” until 07 adds events.
n/a	Opened	Email open tracking — often unavailable v1; show “—” or derive from TestimonialRequestEvent if opened exists.
Use event or clickedAt	Clicked	If clickedAt set on request (06), show Clicked; else infer from clicked event.
failed	Failed	Show lastError.
submitted	Submitted	Customer completed 06; optional separate column instead of replacing “clicked”.
expired	Expired	Token/link expired per 08.

Recommendation: table shows primary TestimonialRequest.status plus optional badges for Clicked / Submitted when timestamps/events exist (avoid overloading one enum).

---

4) Loader: query + pagination + filters

4.1 Search params

Param	Purpose
page, pageSize	Offset pagination (defaults e.g. 1 / 25).
campaignId	Filter by campaign UUID.
channel	email | sms | all.
status	Filter internal statuses (multi-select optional v2).
from, to	Filter on sentAt or createdAt (UTC date-only).
q	Optional search on customerEmail, shopifyOrderId, submissionToken prefix (avoid full token exposure — prefer email/order).

4.2 Query pattern

• findMany with where: { shopId, ...filters }, orderBy: { sentAt: 'desc', createdAt: 'desc' } (nulls last handled via DB or sort in app).
• Include: campaign: { select: { name, channel } }.
• select minimal columns for table performance.

4.3 Last event time

Option A: subquery / raw SQL MAX(event_at) grouped by request_id.

Option B: in loader, batch-fetch event max for visible ids (groupBy).

Option C: maintain lastEventAt on TestimonialRequest updated whenever an event is inserted (07) — best UX, slightly more write logic.

Pick one in implementation ticket; Option C recommended if request volume is high.

---

5) UI (Polaris)

5.1 Page

• Title: “Requests log” or “Outreach requests”
• Subtitle: short explanation (“One row per product outreach sent after purchase.”)

5.2 Filters row

• Campaign Select (populate from campaigns for shop).
• Channel Select.
• Status Select (mapped labels).
• Date range (two date fields or range picker).

5.3 Table

Use IndexTable with sortable columns where cheap (sentAt, createdAt).

Columns (blueprint order):

1. Request ID (truncated + copy)
2. Product title (+ small grey Shopify product ID subtitle optional)
3. Customer
4. Order
5. Campaign
6. Channel
7. Status (+ failure icon if failed)
8. Failure reason (ellipsis; modal on click)
9. Sent at
10. Last event at

Row actions: View details → slide-over with full lastError, list of recent events, link to related submission if status=submitted and submissionId exists (add FK optional: TestimonialRequest.submissionId nullable).

---

6) Actions

This screen is read-only in v1 (no action except export optional).

Optional v2: “Resend” failed row — out of scope unless 07 defines safe retries.

---

7) CSV export (optional)

• Secondary button “Export CSV” — stream same filtered set (limit rows e.g. 10k) for merchant support.

---

8) Performance

• Index (shopId, sentAt), (shopId, campaignId), (shopId, status) on testimonial_requests (names per Prisma @@map).
• Avoid N+1: use include / batch for campaign names and product titles.

---

9) Testing checklist (acceptance criteria)

• Only current shop’s requests visible.
• Filters narrow rows correctly; pagination stable.
• Failed rows show truncated error with full text in modal.
• Sent / last event columns reasonable when events missing (show —).
• Status labels match §3 mapping doc.

---

10) Implementation order (for Cursor)

1. Confirm Prisma models §5.5–5.6 exist and indexes.
2. Implement loader with filters + pagination + joins.
3. Build IndexTable + filter UI.
4. Optional: request detail slide-over + events query.
5. Optional: lastEventAt denormalization in 07 + backfill script.

---

11) References

• 02-Implementation-Blueprint.md — Screen 10, §5.5–5.6
• 07-email-sms-request-delivery-pipeline.md — writers for requests/events
• 06-public-submission-page-screen-13.md — clickedAt / submission linkage

---

12) Note on numbering

This folder already includes 05–12 numbered plans. This file is 13-… (not 05-…).