# Moderation workflow prevents unreviewed reviews from displaying

- **Pattern:** `ab-001210` (`ecommerce-reviews.moderation-trust.moderation-enforced`)
- **Severity:** critical
- **Lifecycle:** active
- **Last modified:** 2026-04-18
- **Canonical URL:** https://auditbuffet.com/patterns/ab-001210
- **License:** CC-BY-4.0 — attribute to AuditBuffet Pattern Catalog (https://auditbuffet.com/patterns/ab-001210)

## Why it matters

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.

## Severity rationale

Critical because a missing status filter exposes unmoderated, potentially harmful content to all site visitors with no privilege required beyond submitting a review.

## Remediation

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`.

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

```ts
// 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.

## Detection

- **ID:** `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`:

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

  ```ts
  // 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
  ```

## External references

- cwe CWE-284

Taxons: content-integrity

HTML version: https://auditbuffet.com/patterns/ab-001210