Reported listings move to suspended status, not hard-deleted

Hard-deleting reported listings destroys the evidence needed to investigate abuse patterns, respond to legal holds, and meet GDPR Art. 17 obligations (which require a deliberate erasure decision, not automatic deletion on report). CWE-778 (Insufficient Logging) applies: if the record is gone, you cannot determine whether the report was legitimate, who submitted the listing, or whether the reporter is gaming the system to suppress competitors.

Move reported listings to a 'suspended' status rather than deleting them. Keep the row and the report record intact for moderator review:

// app/api/listings/[id]/report/route.ts
await db.$transaction([
  db.listingReports.create({
    data: { listing_id: listingId, reason, details, reported_by: req.auth?.userId ?? 'anonymous' }
  }),
  db.listings.update({
    where: { id: listingId },
    data: { status: 'suspended' }
  })
])

return Response.json({ message: 'Report received. Listing hidden pending review.' })

In the admin moderation queue, show suspended listings alongside their reports. Moderators should have three options: reinstate (set status = 'approved'), permanently reject (status = 'rejected'), or escalate for legal review — never auto-delete on report submission.

• ID: reported-listings-suspend

• Severity: medium

• What to look for: Examine the report/flag mechanism. When a user or moderator reports a listing as spam/inappropriate, check that: (1) the listing is suspended (hidden from public view) but not deleted, (2) it remains in the database for audit purposes, (3) moderators can review the report and decide whether to unsuspend or permanently delete.

• Pass criteria: Enumerate all relevant code paths. Reported listings are moved to a suspended state and hidden from public view. The listing record remains intact for audit purposes. with at least 1 verified instance.

• Fail criteria: Reported listings are immediately deleted, or there's no way to recover or review reported listings.

• Skip (N/A) when: The project has no report/flag feature.

• Detail on fail: "When a listing is reported as spam, it's hard-deleted. There's no audit trail and no way to review the report." or "Reported listings remain visible to the public."

• Remediation: Suspend instead of delete:

  -- Database schema
  ALTER TABLE listings ADD COLUMN status VARCHAR DEFAULT 'approved';
  -- Statuses: approved, pending, rejected, suspended, deleted
  
  CREATE TABLE listing_reports (
    id SERIAL PRIMARY KEY,
    listing_id INT NOT NULL REFERENCES listings(id),
    reported_by VARCHAR,
    reason VARCHAR(255),
    details TEXT,
    created_at TIMESTAMP DEFAULT NOW(),
    reviewed_at TIMESTAMP,
    moderator_id VARCHAR
  );
  

  // app/api/listings/[id]/report/route.ts
  export async function POST(req: Request) {
    const { listingId } = params
    const { reason, details } = await req.json()
  
    // Log the report
    await db.listingReports.create({
      data: {
        listing_id: listingId,
        reason,
        details,
        reported_by: req.auth?.userId || 'anonymous'
      }
    })
  
    // Suspend the listing
    await db.listings.update({
      where: { id: listingId },
      data: { status: 'suspended' }
    })
  
    return Response.json({ message: 'Listing reported and suspended' })
  }
  
  // Admin: Review reports
  export async function GET(req: Request) {
    const reports = await db.listingReports.findMany({
      where: { reviewed_at: null },
      include: { listing: true }
    })
  
    return Response.json(reports)
  }