New submissions enter moderation queue and are not visible until approved

A directory without a moderation queue is an open invitation for spam, defamatory content, and phishing links — all published the instant a form is submitted. Every public query that lacks a WHERE status = 'approved' filter exposes every pending, rejected, and spam entry to visitors. This violates OWASP A01 (Broken Access Control) and CWE-285 (Improper Authorization): your access control policy says only approved content is public, but the data layer does not enforce it.

Add a status column defaulting to 'pending' and filter all public queries to status = 'approved'. Apply the change in the schema and every listing query:

ALTER TABLE listings ADD COLUMN status VARCHAR NOT NULL DEFAULT 'pending';
CREATE INDEX idx_listings_status ON listings(status);

// Always filter public reads
export async function getApprovedListings() {
  return db.listings.findMany({ where: { status: 'approved' } })
}

// On submission, never set status to 'approved'
await db.listings.create({ data: { ...parsed, status: 'pending' } })

Admin routes that approve or reject should update status and record approved_at / rejected_at timestamps.

• ID: moderation-queue

• Severity: critical

• What to look for: Examine the listing submission logic and database schema. Look for a status field (e.g., pending, approved, rejected). Check that submissions are created with status = 'pending' or similar, and that public queries for listings only return status = 'approved' entries. Verify that new submissions are not immediately visible on the site.

• Pass criteria: Enumerate all relevant code paths. All new submissions have a status field that defaults to pending or similar. Public listing pages and searches filter to show only approved listings. Moderators have a separate interface to view and approve pending submissions. with at least 1 verified instance.

• Fail criteria: Submissions are auto-published (status field missing or always set to approved), or new listings appear publicly before any review. A partial implementation does not count as pass.

• Skip (N/A) when: The project has no moderation feature (e.g., all submissions are automatically published and this is acceptable).

• Detail on fail: "All listings are published immediately on submission. No moderation queue. Spam entries appear on the site instantly." or "Status field exists but is not used to filter public listings — pending and approved listings both show publicly."

• Cross-reference: Compare with directory-submissions-moderation.moderation.moderation-actions-logged — the queue surfaces items for review (this check); action logging records what moderators did.

• Remediation: Implement a moderation queue:

  -- Database schema
  CREATE TABLE listings (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    description TEXT NOT NULL,
    contact_email VARCHAR(255) NOT NULL,
    status VARCHAR DEFAULT 'pending' NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    approved_at TIMESTAMP,
    rejected_at TIMESTAMP,
    rejection_reason TEXT
  );
  
  CREATE INDEX idx_listings_status ON listings(status);
  

  // API: Only fetch approved listings
  export async function getApprovedListings() {
    return db.listings.findMany({
      where: { status: 'approved' },
      orderBy: { created_at: 'desc' }
    })
  }
  
  // Admin route: Approve or reject
  export async function POST(req: Request) {
    const { listingId, action, rejectionReason } = await req.json()
  
    if (action === 'approve') {
      await db.listings.update({
        where: { id: listingId },
        data: { status: 'approved', approved_at: new Date() }
      })
    } else if (action === 'reject') {
      await db.listings.update({
        where: { id: listingId },
        data: {
          status: 'rejected',
          rejected_at: new Date(),
          rejection_reason: rejectionReason
        }
      })
    }
  }