34 — TestimonialDailyAnalytics Model & Rollup Jobs (Blueprint §5.11)

34 — TestimonialDailyAnalytics Model & Rollup Jobs (Blueprint §5.11)

Source: 02-Implementation-Blueprint.md — §5.11 New model: TestimonialDailyAnalytics.

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

Related: 14 (analytics screen), 19 (dashboard), 13 (requests log), 20 (schema/migrations), 24 (UTC standards).

---

0) Goal (one sentence)

Create a reliable daily rollup table so KPI-heavy screens load fast and show consistent numbers without expensive live aggregation over large event datasets.

---

1) Blueprint model recap

Blueprint fields:

• id String @id @default(uuid())
• shopId String @map("shop_id")
• date DateTime @map("date") // UTC day bucket
• requestsSent Int @default(0) @map("requests_sent")
• requestsClicked Int @default(0) @map("requests_clicked")
• submissionsCount Int @default(0) @map("submissions_count")
• approvedCount Int @default(0) @map("approved_count")
• publishedCount Int @default(0) @map("published_count")
• videoSubmissionsCount Int @default(0) @map("video_submissions_count")
• photoSubmissionsCount Int @default(0) @map("photo_submissions_count")
• createdAt DateTime @default(now()) @map("created_at")
• updatedAt DateTime @updatedAt @map("updated_at")

Unique:

• @@unique([shopId, date])

---

2) Scope

In scope

• Prisma model + migration
• Daily rollup job design
• Incremental update strategy
• Backfill/rebuild strategy
• Read query contract for analytics/dashboard

Out of scope

• Hourly or real-time stream processing
• BI warehouse export

---

3) UTC bucket standard

Use UTC day buckets only:

• Bucket key = YYYY-MM-DD 00:00:00Z
• All metrics in a row represent that UTC day

Never bucket by store-local timezone in this model (keep local formatting at UI layer).

---

4) Source-of-truth mapping for each metric

Daily field	Source table(s)	Rule
requestsSent	TestimonialRequest	count rows with sentAt on that UTC day
requestsClicked	TestimonialRequest or TestimonialRequestEvent	count distinct requests clicked on day (clickedAt preferred)
submissionsCount	TestimonialSubmission	count rows with submittedAt on day
approvedCount	TestimonialSubmission	count approvals on day (approvedAt preferred; else status-change fallback)
publishedCount	TestimonialSubmission	count rows with publishedAt on day
videoSubmissionsCount	TestimonialSubmission	submissions where mediaType='video' and submitted on day
photoSubmissionsCount	TestimonialSubmission	submissions where mediaType='photo' and submitted on day

---

5) Data quality prerequisites

For accurate daily metrics, ensure the following timestamps exist:

• sentAt
• clickedAt
• submittedAt
• approvedAt (recommended add if missing)
• publishedAt

If approvedAt is missing, either:

1. add migration for it (recommended), or
2. define fallback logic and accept reduced accuracy.

---

6) Rollup job design

6.1 Job type

Cron-triggered server job (e.g. every hour, plus nightly finalization).

6.2 Job mode

Two modes:

1. Incremental daily upsert (default)
2. Rebuild range (manual/admin maintenance)

6.3 Algorithm (incremental)

For each active shop and each target day in window:

1. calculate each metric from source tables
2. upsert TestimonialDailyAnalytics by (shopId, date)
3. log rollup result summary (counts, duration)

Use transaction per (shop, day) batch or per shop depending volume.

6.4 Window strategy

Each run recomputes:

• today
• yesterday
• optionally last 7 days (to heal delayed events)

This makes late-arriving updates converge without full rebuild.

---

7) Backfill strategy

Provide one manual job endpoint/script:

• inputs: shopId?, fromDate, toDate
• operation: delete/recompute rows for range

Use after:

• schema changes affecting metrics
• bug fixes in metric logic
• historical corrections

---

8) Read query contract for UI

8.1 Dashboard (Screen 1)

• Sum selected range for KPI cards
• Optionally fetch time series for charts

8.2 Analytics (Screen 11)

• Funnel-related cards can read from this table for speed
• If funnel needs stages not stored here (opened), combine with live event query or extend model carefully

8.3 Null/empty handling

• Missing row for day = treat as zero metrics
• UI should render zero-series gracefully

---

9) Consistency checks

Run periodic validation job:

• compare rollup totals vs live aggregate for random shops/date windows
• alert when drift exceeds threshold (e.g. >1% or >N records)

This catches silent metric regressions.

---

10) Performance and indexing

Required:

• @@unique([shopId, date])

Recommended additional index:

• @@index([date]) for global maintenance scans (optional)

Read patterns should be:

• where: { shopId, date: { gte, lte } }

---

11) Acceptance criteria

• Rollup job creates/updates rows for active days.
• Re-running job is idempotent (same outputs).
• Dashboard/analytics range sums match live-source spot checks.
• Backfill tool can rebuild a date window safely.
• UTC bucketing consistent around timezone boundaries.

---

12) Suggested implementation order (for Cursor)

1. Add model + migration.
2. Add approvedAt timestamp migration if needed for fidelity.
3. Implement rollup service with upsert-by-day.
4. Add scheduled cron route/job trigger.
5. Add rebuild command/route for maintenance.
6. Switch analytics/dashboard reads to rollups where appropriate.

---

13) References

• 02-Implementation-Blueprint.md — §5.11
• 14-analytics-blueprint-screen-11.md
• 19-dashboard-screen-1.md
• 20-database-design-and-migrations-blueprint-section-5.md
• 24-build-notes-execution-standards-blueprint-section-10.md

---

14) Note on numbering

This folder already includes 05 through 33 plans. This file is 34-....