Moderation workflow prevents unreviewed reviews from displaying

Without a status column defaulting to 'pending' and a filter on every public display query, your review page is an open broadcast channel — any submitted content, including spam, competitor attacks, and profanity, appears live to all shoppers immediately. CWE-284 (Improper Access Control) covers exactly this: the resource (a published review) is accessible to users who should not have triggered that state transition (published). Even one unfiltered display query is enough to leak pending reviews; attackers simply POST then GET before moderation runs.

Add a status column to the reviews table via migration and apply WHERE status = 'approved' to every public-facing query in lib/reviews.ts and api/reviews/route.ts.

-- Migration
ALTER TABLE reviews ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'pending';
CREATE INDEX idx_reviews_status ON reviews(status);

// lib/reviews.ts — the single place all display queries should live
export async function getApprovedReviews(productId: string) {
  return db.reviews.findMany({
    where: { product_id: productId, status: 'approved' },
    orderBy: { created_at: 'desc' }
  })
}

Search the codebase for every db.reviews.findMany and SELECT * FROM reviews call and verify each one includes the status filter. Related: ecommerce-reviews.moderation-trust.spam-detection for automated pre-screening before insert.

• ID: ecommerce-reviews.moderation-trust.moderation-enforced

• Severity: critical

• What to look for: List all review display queries and API routes. For each query, classify whether it filters by a status field (e.g., WHERE status = 'approved'). Count the ratio of filtered vs unfiltered queries. Also check the reviews database schema for a status column with at least 3 states (pending, approved, rejected).

• Pass criteria: The reviews database schema includes a status column with at least 2 states (pending and approved). At least 100% of public-facing review display queries filter by status = 'approved' — no query returns unmoderated reviews to end users. New reviews default to status = 'pending' on insert.

• Fail criteria: Reviews are automatically displayed on submission without any status filtering, or the status field exists but at least 1 public display query omits the filter. Must not pass when a status column exists but the default value is 'approved' — that bypasses moderation entirely.

• Skip (N/A) when: The project uses a fully external review service (Yotpo, Judge.me, Trustpilot) that handles moderation outside the codebase.

• Detail on fail: "Reviews table has status column but 2 of 3 display queries omit the status filter. Unmoderated reviews visible on product pages." or "No status column in reviews table. All reviews published immediately on submission."

• Cross-reference: Related to ecommerce-reviews.moderation-trust.spam-detection (automated filtering complements manual moderation) and ecommerce-reviews.moderation-trust.edit-protection (edits must re-enter moderation).

• Remediation: Add a status column via migration and filter all display queries in lib/reviews.ts and api/reviews/route.ts:

  -- Database: add status column
  ALTER TABLE reviews ADD COLUMN status VARCHAR DEFAULT 'pending';
  CREATE INDEX idx_reviews_status ON reviews(status);
  

  // API: Only display approved reviews
  const approvedReviews = await db.reviews.findMany({
    where: { product_id: productId, status: 'approved' },
    orderBy: { created_at: 'desc' }
  })
  
  // Admin: Add route to approve/reject
  // POST /api/admin/reviews/:id/approve
  // POST /api/admin/reviews/:id/reject