20 — Database Design & Migrations (Blueprint §5)

20 — Database Design & Migrations (Blueprint §5)

Source: 02-Implementation-Blueprint.md — §5) Database Design (Prisma Style) including §5.1 → §5.11.

Product alignment: 01-Post-Purchase-Video-Testimonial-Collector-Plan.md — core entities for campaigns, requests, submissions, media, moderation, and analytics.

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

Related: 04-Delete All Existing Code and DB Tables.md (chat cleanup), 07-email-sms-request-delivery-pipeline.md, 06-public-submission-page-screen-13.md, 08-security-compliance-and-privacy.md.

---

0) Goal (one sentence)

Introduce the complete testimonial data model in Prisma with safe, incremental migrations so all downstream screens/APIs run on stable, indexed, tenant-scoped tables.

---

1) Scope (blueprint §5 coverage)

Implement these schema units from blueprint:

1. Shop extension fields (§5.1)
2. TestimonialCampaign (§5.2)
3. TestimonialCampaignProduct (§5.3)
4. TestimonialTemplate (§5.4)
5. TestimonialRequest (§5.5)
6. TestimonialRequestEvent (§5.6)
7. TestimonialSubmission (§5.7)
8. TestimonialMediaAsset (§5.8)
9. TestimonialProductLink (§5.9)
10. TestimonialModerationLog (§5.10)
11. TestimonialDailyAnalytics (§5.11)

Out of scope:

• Route/page implementation logic (covered by other plans).
• Historical migration from external systems.

---

2) Naming and Prisma conventions

Follow existing repo conventions:

• id String @id @default(uuid())
• shopId String @map("shop_id")
• createdAt DateTime @default(now()) @map("created_at")
• updatedAt DateTime @updatedAt @map("updated_at") (where mutable)
• @@map("snake_case_table")
• onDelete: Cascade for tenant-owned child records

Use nullable fields for phased rollout to avoid migration breakage, then tighten constraints when app flow is stable.

---

3) Model-by-model implementation notes

3.1 Extend Shop (§5.1)

Add testimonial config fields:

• testimonialEnabled
• testimonialWidgetLayout
• testimonialPrimaryColor
• testimonialAutoApprovePhotos
• testimonialConsentVersion

Recommendation:

• Keep these fields near existing shop feature flags.
• Add defaults matching blueprint.

3.2 TestimonialCampaign (§5.2)

Required:

• lifecycle/status (active|paused|archived)
• trigger (order_paid|order_fulfilled)
• delay, channel, reminders, incentives

Add:

• lastSentAt (optional but useful for Screen 2 list)

Index:

• @@index([shopId, status])

3.3 TestimonialCampaignProduct (§5.3)

Mapping table for campaign → product targeting.

Required unique:

• @@unique([campaignId, shopifyProductId])

If collection targeting is implemented, create parallel mapping model in a later migration.

3.4 TestimonialTemplate (§5.4)

Unique fallback chain support:

• @@unique([shopId, campaignId, channel, templateType])

Note:

• campaignId nullable for global defaults.

3.5 TestimonialRequest (§5.5)

Core outreach unit (one per delivered product request).

Important:

• submissionToken String @unique
• status enum-as-string
• scheduledFor + send/click/submit timestamps

Recommended additions:

• tokenExpiresAt DateTime? (from security plan)
• optional orderNameSnapshot and productTitleSnapshot for easier logs/UI

Indexes:

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

3.6 TestimonialRequestEvent (§5.6)

Event stream for delivery telemetry.

Required:

• requestId, eventType, eventAt

Index:

• @@index([requestId, eventAt])

Optional:

• add @@index([shopId, eventAt]) for analytics-heavy queries.

3.7 TestimonialSubmission (§5.7)

Main UGC entity.

Key fields:

• source links: requestId, shopifyOrderId, shopifyProductId
• content: mediaType, headline, reviewText, rating
• moderation/publication: status, published, publishedAt, featured, sortOrder
• consent: accepted state/timestamp/version

Recommended additions for analytics fidelity:

• approvedAt DateTime?
• visibility flags (visibleOnPdp, visibleOnHome, visibleOnCollection) if using Screen 7 placement toggles

Indexes:

• @@index([shopId, status])
• @@index([shopId, published])
• @@index([requestId])
• @@index([shopId, shopifyProductId, published])

3.8 TestimonialMediaAsset (§5.8)

Media processing state and playback metadata.

Required:

• submissionId
• storageProvider, storageKey
• playback/thumbnail URLs
• dimensions, duration, file size
• processing fields

Indexes:

• @@index([submissionId])
• @@index([shopId, processingStatus])

3.9 TestimonialProductLink (§5.9)

Secondary product links.

Keep unique:

• @@unique([submissionId, shopifyProductId])

Primary binding remains TestimonialSubmission.shopifyProductId.

3.10 TestimonialModerationLog (§5.10)

Audit trail.

Fields:

• submissionId, action, reason, actorType, actorEmail

Index:

• @@index([submissionId, createdAt])

Policy:

• insert-only (enforced at application layer).

3.11 TestimonialDailyAnalytics (§5.11)

Rollup table for fast dashboard/analytics.

Unique:

• @@unique([shopId, date])

Used when live aggregates become slow.

---

4) Relations and cascade strategy

Recommended relation chain:

• Shop -> all testimonial models (onDelete: Cascade)
• TestimonialCampaign -> TestimonialCampaignProduct, TestimonialRequest, optionally TestimonialTemplate
• TestimonialRequest -> TestimonialRequestEvent, optional 1:1/1:n submission link
• TestimonialSubmission -> TestimonialMediaAsset, TestimonialProductLink, TestimonialModerationLog

Rules:

• Keep requestId nullable on submission for manual/admin-added entries.
• Use onDelete: SetNull where historical integrity is preferred over hard cascade.

---

5) Migration sequencing (safe rollout)

Use multiple small migrations rather than one huge change.

Migration A — Base schema

• Add Shop testimonial fields (§5.1)
• Add TestimonialCampaign, TestimonialCampaignProduct, TestimonialTemplate

Migration B — Request pipeline

• Add TestimonialRequest, TestimonialRequestEvent
• Add required indexes + token unique

Migration C — Submission pipeline

• Add TestimonialSubmission, TestimonialMediaAsset, TestimonialProductLink

Migration D — Moderation + analytics

• Add TestimonialModerationLog, TestimonialDailyAnalytics
• Add optional analytics fields (approvedAt) and placement flags if chosen

Migration E — Hardening (optional)

• Tighten constraints once app paths are stable
• Add check constraints (where supported) for enums/rating ranges

---

6) Backfill and compatibility strategy

Given this repo was chat-first:

• No testimonial legacy data expected; new tables can start empty.
• Keep nullable references initially to avoid deploy-time failures.
• If adding denormalized snapshots (productTitleSnapshot, etc.), fill at write time only; no immediate backfill required.

For existing shops:

• Defaults on Shop fields should keep behavior safe (testimonialEnabled=true, conservative moderation defaults).

---

7) Enum strategy (string vs DB enum)

Blueprint uses string comments for statuses/types.

Recommendation:

• Start with String + strict app-level constants (fast iteration).
• Optionally migrate to native DB enums later when values stabilize.

Define central constants module:

• campaign status/trigger/channel
• request status/event types
• submission status/media type
• moderation actions

---

8) Data integrity checks

Add server-side constraints (and optional DB checks) for:

• rating between 1 and 5 when present
• delayDays 0..60
• reminderDelayDays >= 0
• maxReminders >= 0
• minVideoLength <= maxVideoLength (if fields added on shop)

Do not rely on UI validation alone.

---

9) Performance baseline

Before shipping high-volume stores:

• Validate query plans for:
  • pending submissions list
  • requests log filtered by date/status
  • dashboard KPI aggregates
• Add composite indexes only where query profiler indicates benefit.

Keep index count balanced to avoid write overhead in high-event tables.

---

10) Testing checklist

• Prisma migrate applies cleanly on fresh DB.
• Prisma migrate applies on existing chat-era DB without destructive side-effects beyond intended drops from cleanup plan.
• Relation cascades behave as expected on shop deletion/redact.
• Unique constraints block duplicate campaign-product mappings and duplicate submission tokens.
• Core read queries for screens 2/5/7/10 perform under acceptable latency on seed dataset.

---

11) Implementation order (for Cursor)

1. Align with 04-Delete All Existing Code and DB Tables.md cleanup state.
2. Apply Migration A through D in sequence.
3. Regenerate Prisma client.
4. Add schema constants/types in app code.
5. Wire routes progressively (campaigns -> requests -> submission -> moderation -> analytics).
6. Add reconciliation/rollup jobs once screens are live.

---

12) References

• 02-Implementation-Blueprint.md — Section 5 (§5.1 to §5.11)
• 04-Delete All Existing Code and DB Tables.md — baseline cleanup
• 07-email-sms-request-delivery-pipeline.md
• 06-public-submission-page-screen-13.md
• 12-moderation-settings-screen-9.md
• 14-analytics-blueprint-screen-11.md

---

13) Note on numbering

This folder already includes 05 through 19 plans. This file is 20-....