Review submission prevents duplicate submissions from same user per product

Without a unique constraint on (user_id, product_id), a single user can flood your product with five-star reviews — or a malicious competitor can script hundreds of one-star submissions. CWE-799 (Improper Control of Interaction Frequency) names this exact failure. Beyond rating manipulation, duplicate reviews distort your aggregate score fed to schema.org AggregateRating, poisoning the star display that Google indexes. The fix is two-layer: a database constraint that survives any future code path, plus an application-level check that returns a user-facing error immediately.

Add a unique constraint in prisma/schema.prisma (or a raw SQL migration) and guard the insert in api/reviews/submit.

-- Migration: enforce one review per user per product
ALTER TABLE reviews
  ADD CONSTRAINT unique_user_product UNIQUE (user_id, product_id);

// api/reviews/submit
const existing = await db.reviews.findFirst({
  where: { user_id: userId, product_id: productId }
})
if (existing) {
  return Response.json({ error: 'You have already reviewed this product' }, { status: 409 })
}

The database constraint is the hard stop; the application check is the early exit that returns a readable error instead of a constraint violation.

• ID: ecommerce-reviews.review-collection.duplicate-prevention

• Severity: high

• What to look for: Enumerate all duplicate-prevention mechanisms: (1) database unique constraint on (user_id, product_id), (2) application-level check before insert, (3) UI disabling of form for already-reviewed products. Count how many layers of protection exist (minimum 1 required, at least 2 recommended).

• Pass criteria: At least 1 duplicate prevention mechanism enforces that a user can submit no more than 1 review per product, either via a database unique constraint on (user_id, product_id) or an application-level existence check before insert, with user-facing error messaging on duplicate attempt.

• Fail criteria: No duplicate prevention mechanism exists at any layer — a user can submit multiple reviews for the same product without any block or warning.

• Skip (N/A) when: Reviews are anonymous and not tied to user accounts (no user_id column in reviews table or no auth required for review submission).

• Detail on fail: "0 of 3 duplicate-prevention layers found. Database schema has no unique constraint on (user_id, product_id). No application check before insert. No UI disabling for reviewed products." or "Form has no check — user can resubmit and create duplicate reviews."

• Cross-reference: Related to ecommerce-reviews.moderation-trust.moderation-enforced (moderation catches some duplicates but is not a substitute for prevention).

• Remediation: Add a unique constraint to your reviews table in prisma/schema.prisma or via SQL migration and check before allowing submission in api/reviews/submit:

  -- Database-level constraint
  ALTER TABLE reviews
  ADD CONSTRAINT unique_user_product UNIQUE (user_id, product_id);
  

  At application level, check before allowing submission:

  // api/reviews/submit
  const existing = await db.reviews.findFirst({
    where: { user_id: userId, product_id: productId }
  })
  
  if (existing) {
    return { error: 'You have already reviewed this product' }
  }