33 — TestimonialModerationLog Model & Audit Workflow (Blueprint §5.10)

33 — TestimonialModerationLog Model & Audit Workflow (Blueprint §5.10)

Source: 02-Implementation-Blueprint.md — §5.10 New model: TestimonialModerationLog.

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

Related: 10 (submission detail actions), 11 (published actions), 12 (moderation settings), 08 (security/compliance), 20 (schema baseline).

---

0) Goal (one sentence)

Ensure every moderation or publication decision on testimonial content is captured in an immutable, tenant-scoped audit trail usable for support, compliance, and incident investigation.

---

1) Blueprint model recap

Blueprint fields:

• id String @id @default(uuid())
• shopId String @map("shop_id")
• submissionId String @map("submission_id")
• action String // approve | reject | archive | unpublish | feature
• reason String? @db.Text
• actorType String @map("actor_type") // merchant | system
• actorEmail String? @map("actor_email")
• createdAt DateTime @default(now()) @map("created_at")

Index:

• @@index([submissionId, createdAt])

---

2) Scope

In scope

• Prisma model implementation and migration
• Write-path integration for all moderation actions
• Read/query usage for admin detail timeline
• Immutability guarantees (application-level)

Out of scope

• Full legal retention policy beyond app-level defaults
• External SIEM export (optional future)

---

3) Action taxonomy (canonical)

Start with blueprint actions:

• approve
• reject
• archive
• unpublish
• feature

Recommended extensions (optional but useful):

• publish
• unfeature
• reopen (move rejected back to pending)
• auto_reject (system moderation rule)
• auto_approve_photo

If extensions are added, keep a central action constants list.

---

4) Write contract (where logs must be created)

Create one log row for each successful state-changing intent:

1. Screen 6 detail actions (approve, reject, flag, publish, etc.)
2. Screen 7 published management actions (unpublish, feature, reorder-related if needed)
3. Screen 5 bulk moderation actions
4. System actions from moderation automation (Screen 9 rules)

4.1 Transaction rule

State change and log insertion must be in the same DB transaction:

• If status update fails -> no log
• If log insert fails -> rollback state update

This prevents audit drift.

---

5) Actor rules

5.1 actorType

• merchant for admin UI actions
• system for automatic rule-based actions

5.2 actorEmail

• required for merchant when available from session
• nullable for system

5.3 reason

• required for reject (UI enforce + server enforce)
• optional for others

---

6) Immutability standard

Treat this model as append-only:

• No update endpoint
• No delete endpoint
• No “edit reason” after insert

If correction needed:

• insert another log row with corrective action and reason.

This aligns with audit expectations from 08-security-compliance-and-privacy.md.

---

7) Query patterns

7.1 Submission detail timeline

where: { submissionId, shopId } orderBy: { createdAt: "desc" } take: 20 (pagination optional)

7.2 Troubleshooting query

Filter by:

• shopId
• action
• date range
• actorType

Optional admin support screen can be added later; not required now.

---

8) Privacy/compliance notes

• reason can include free text; avoid storing raw customer PII when possible.
• For GDPR customer redaction:
  • do not break audit chain,
  • but sanitize reason if it accidentally contains personal data (policy decision in 08).

Do not expose moderation logs in storefront/public APIs.

---

9) Migration and index guidance

9.1 Schema migration

Add model exactly with mapped snake_case columns.

9.2 Indexes

Required:

• @@index([submissionId, createdAt])

Recommended:

• @@index([shopId, createdAt]) for support/reporting lookups.

---

10) Acceptance criteria

• Every moderation state mutation creates exactly one corresponding log row.
• Reject action cannot complete without reason and log.
• Bulk actions create one log row per submission id.
• Log rows are never edited/deleted by application code.
• Submission detail timeline displays logs in reverse chronological order.

---

11) Suggested implementation order (for Cursor)

1. Add Prisma model + migration.
2. Add constants for action values and actor types.
3. Implement helper appendModerationLog(...).
4. Integrate helper in all relevant route actions.
5. Add timeline section in Screen 6 detail loader/UI.
6. Add tests for transaction integrity and required reject reason.

---

12) References

• 02-Implementation-Blueprint.md — §5.10
• 10-submission-detail-screen-6.md
• 11-published-content-screen-7.md
• 12-moderation-settings-screen-9.md
• 08-security-compliance-and-privacy.md

---

13) Note on numbering

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