`migrations/010_image_flags.sql`

28 lines
-- User-submitted flags on breed_images. Visitors who see an off-topic /
-- low-quality / wrong-breed photo can flag it; admin reviews the queue and
-- decides whether to mark the row dead. Complements the regex-based
-- title_pollution.js and the planned vision audit by catching pollution
-- those passes miss.

BEGIN;

CREATE TABLE IF NOT EXISTS breed_image_flags (
  id              BIGSERIAL PRIMARY KEY,
  image_id        BIGINT NOT NULL REFERENCES breed_images(id) ON DELETE CASCADE,
  reason          TEXT NOT NULL CHECK (reason IN (
                    'wrong_breed', 'off_topic', 'low_quality',
                    'duplicate', 'offensive', 'other'
                  )),
  notes           TEXT,           -- optional free-text from the reporter
  reporter_ip     INET,           -- rate-limit + abuse triage; not displayed
  resolved_at     TIMESTAMPTZ,
  resolved_action TEXT CHECK (resolved_action IN ('dismissed','marked_dead', NULL)),
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_image_flags_unresolved
  ON breed_image_flags(created_at) WHERE resolved_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_image_flags_image
  ON breed_image_flags(image_id);

COMMIT;