`migrations/011_vision_audit.sql`

49 lines
-- Phase 3 of the gallery quality cleanup: vision-based per-image audit
-- using local Ollama (llava). Phase 1 = regex title scrub, Phase 2 = multi-
-- source ingest + subcategory crawler. This migration adds the columns the
-- vision auditor writes back to.
--
-- Verdict vocabulary (matches src/audit/vision_audit.js prompt):
--   real_photo     -- actual photograph of a real specimen
--   art            -- painting / drawing / sculpture / statue / figurine /
--                    trading card / illustration / engraving / woodcut / AI
--   wrong_subject  -- real photo but not of the species (carriage, monument,
--                    flowers, landscape with horse barely visible, etc.)
--   low_quality    -- blurry / tiny / unrecognizable
--   unclear        -- llava genuinely could not tell (also used for errors)
--
-- Strategy:
--   art + wrong_subject -> dead_at = NOW(), category_match suffix
--   low_quality + unclear + real_photo -> kept (low quality > nothing)

BEGIN;

ALTER TABLE breed_images
  ADD COLUMN IF NOT EXISTS vision_audit_at      TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS vision_audit_verdict TEXT,
  ADD COLUMN IF NOT EXISTS vision_audit_notes   TEXT;

-- Constrain the verdict to the allowed values so a buggy run can't poison
-- the column with arbitrary llava chatter.
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint WHERE conname = 'breed_images_vision_verdict_chk'
  ) THEN
    ALTER TABLE breed_images
      ADD CONSTRAINT breed_images_vision_verdict_chk
      CHECK (vision_audit_verdict IS NULL
             OR vision_audit_verdict IN ('real_photo','art','wrong_subject','low_quality','unclear'));
  END IF;
END $$;

-- Batch-picking index — partial index on rows that still need auditing.
CREATE INDEX IF NOT EXISTS idx_breed_images_vision_pending
  ON breed_images(id) WHERE vision_audit_at IS NULL;

-- Read index for verdict breakdown reports.
CREATE INDEX IF NOT EXISTS idx_breed_images_vision_verdict
  ON breed_images(vision_audit_verdict) WHERE vision_audit_verdict IS NOT NULL;

COMMIT;