`scripts/bad-examples-schema.sql`

63 lines
-- bad_examples — permanent, isolated anti-pattern registry for wallco.ai.
-- "Keep all bad/deleted items in its own db so we never create the same error."
-- The negative mirror of the `elements` (good-reference) library.
--
-- Design rules:
--   * ONE row per design_id. defect_tags accumulates every defect signal.
--   * Stores the PROVENANCE that produced the error (prompt / negative_prompt /
--     seed / generator / category / dims) so generation can learn the anti-pattern.
--   * local_path is preserved — the bad FILE is never deleted; this row points to it.
--   * Lives in dw_unified but is NEVER written to data/designs.json, so catalog
--     rebuilds / publishes cannot clobber it. Append-and-merge only.

CREATE TABLE IF NOT EXISTS bad_examples (
  design_id         BIGINT PRIMARY KEY,
  defect_tags       TEXT[] NOT NULL DEFAULT '{}',   -- {edges_fail, user_removed, ghost, bleed, heal_derivative, repeat_break_lr, repeat_break_tb, ...}
  severity          INT,                            -- worst known severity 0-3
  category          TEXT,
  prompt            TEXT,                           -- the prompt that produced the bad design
  negative_prompt   TEXT,
  seed              BIGINT,
  generator         TEXT,                           -- replicate / comfy / pil-mid-heal / gemini-...
  width_in          NUMERIC,
  height_in         NUMERIC,
  dominant_hex      TEXT,
  parent_design_id  BIGINT,
  local_path        TEXT,                           -- preserved bad file (NEVER deleted)
  scores            JSONB,                          -- lens / structural scores when available
  notes             TEXT,                           -- original defect detail
  source            TEXT,                           -- backfill | repeat-break-scan | agent-audit | bulk-reject | edges-agent
  design_created_at TIMESTAMPTZ,
  recorded_at       TIMESTAMPTZ DEFAULT now()
);
CREATE INDEX IF NOT EXISTS bad_examples_defect_idx ON bad_examples USING GIN(defect_tags);
CREATE INDEX IF NOT EXISTS bad_examples_seed_idx   ON bad_examples(seed);
CREATE INDEX IF NOT EXISTS bad_examples_cat_idx    ON bad_examples(category);
CREATE INDEX IF NOT EXISTS bad_examples_gen_idx    ON bad_examples(generator);

-- Backfill from every currently-bad-or-deleted design, merging defect tags.
INSERT INTO bad_examples
  (design_id, defect_tags, category, prompt, negative_prompt, seed, generator,
   width_in, height_in, dominant_hex, parent_design_id, local_path, notes, source, design_created_at)
SELECT id,
  ( ARRAY[]::text[]
    || CASE WHEN user_removed THEN ARRAY['user_removed'] ELSE '{}'::text[] END
    || CASE WHEN notes ILIKE '%EDGES_FAIL%' THEN ARRAY['edges_fail'] ELSE '{}'::text[] END
    || CASE WHEN notes ILIKE '%EDGES_SCAN_ERROR%' THEN ARRAY['edges_scan_error'] ELSE '{}'::text[] END
    || CASE WHEN notes ILIKE '%ghost%' THEN ARRAY['ghost'] ELSE '{}'::text[] END
    || CASE WHEN notes ILIKE '%bleed%' THEN ARRAY['bleed'] ELSE '{}'::text[] END
    || CASE WHEN notes ILIKE '%mid_heal%' OR notes ILIKE '%MID_HEALED%' OR generator ILIKE '%heal%'
            THEN ARRAY['heal_derivative'] ELSE '{}'::text[] END
  ),
  category, prompt, negative_prompt, seed, generator,
  width_in, height_in, dominant_hex, parent_design_id, local_path, notes, 'backfill', created_at
FROM spoon_all_designs
WHERE user_removed = TRUE
   OR notes ILIKE '%EDGES_FAIL%' OR notes ILIKE '%EDGES_SCAN_ERROR%'
   OR notes ILIKE '%ghost%'      OR notes ILIKE '%bleed%'
   OR notes ILIKE '%mid_heal%'   OR notes ILIKE '%MID_HEALED%' OR generator ILIKE '%heal%'
ON CONFLICT (design_id) DO UPDATE
  SET defect_tags = (SELECT array_agg(DISTINCT t)
                     FROM unnest(bad_examples.defect_tags || EXCLUDED.defect_tags) AS t),
      recorded_at = now();