settlement: db-level trigger blocks is_published=true UPDATE/INSERT when prompt satisfies Part A + Part B; supports session-local override with audit note

77d9a4b1fcd075a5b50f93ced63f77c263e77f37 · 2026-05-13 12:23:14 -0700 · SteveStudio2
Files touched

A sql/settlement_publish_trigger.sql
Diff

commit 77d9a4b1fcd075a5b50f93ced63f77c263e77f37
Author: SteveStudio2 <stevestudio2@SteveStacStudio.lan>
Date:   Wed May 13 12:23:14 2026 -0700

    settlement: db-level trigger blocks is_published=true UPDATE/INSERT when prompt satisfies Part A + Part B; supports session-local override with audit note
---
 sql/settlement_publish_trigger.sql | 116 +++++++++++++++++++++++++++++++++++++
 1 file changed, 116 insertions(+)

diff --git a/sql/settlement_publish_trigger.sql b/sql/settlement_publish_trigger.sql
new file mode 100644
index 0000000..8eaa85c
--- /dev/null
+++ b/sql/settlement_publish_trigger.sql
@@ -0,0 +1,116 @@
+-- Settlement pre-publish trigger for spoon_all_designs
+--
+-- Fires BEFORE UPDATE when is_published flips FALSE → TRUE. Inspects the
+-- design's prompt for the Part A + Part B keyword combination defined in
+-- ~/.claude/skills/settlement/SKILL.md. RAISES EXCEPTION if both parts are
+-- satisfied, blocking the publish at the DB layer.
+--
+-- This is a defense-in-depth catch — the JS-side settlement-gate.js fires
+-- before image generation, but Gemini sometimes hallucinates prohibited
+-- imagery (see 2026-05-13 incident: design 3544 rendered banana leaves
+-- despite a "no leaves" prompt). The trigger is a second wall — even if
+-- a downstream admin tool flips is_published=TRUE on a row whose prompt
+-- already smells like a Part A + Part B violation, the trigger blocks it.
+--
+-- LIMITATION: this is a TEXT check on the prompt. It will NOT catch the
+-- 3544-style case where the prompt is clean but the IMAGE is dirty. For
+-- that we still need a post-gen vision pass (settlement-checker.ts).
+--
+-- To apply:  psql dw_unified -f sql/settlement_publish_trigger.sql
+-- To revert: DROP TRIGGER settlement_publish_check ON spoon_all_designs;
+--            DROP FUNCTION settlement_publish_check();
+-- To bypass for a single update (emergency, audited):
+--            SET LOCAL settlement.allow_override = 'true';
+
+CREATE OR REPLACE FUNCTION settlement_publish_check() RETURNS trigger AS $$
+DECLARE
+  pt text;
+  -- Part A keyword groups
+  has_leaves boolean;
+  has_directional boolean;
+  has_open_space boolean;
+  has_single_color boolean;
+  -- Part B
+  prohibited text[];
+  prohibited_hit text;
+  -- Acceptable
+  has_acceptable boolean;
+  -- Operator override
+  override_flag text;
+BEGIN
+  -- Only fire on the false → true publish flip. Lets unpublish + edit pass.
+  IF OLD.is_published IS NOT DISTINCT FROM NEW.is_published THEN
+    RETURN NEW;
+  END IF;
+  IF NEW.is_published IS NOT TRUE THEN
+    RETURN NEW;
+  END IF;
+
+  -- Audited override: SET LOCAL settlement.allow_override = 'true' before
+  -- the UPDATE. The session-local setting writes its presence to the notes
+  -- field so the override is traceable.
+  override_flag := current_setting('settlement.allow_override', true);
+  IF override_flag = 'true' THEN
+    NEW.notes := COALESCE(NEW.notes || E'\n', '') ||
+      'settlement.allow_override=true at ' || now()::text;
+    RETURN NEW;
+  END IF;
+
+  pt := lower(COALESCE(NEW.prompt, ''));
+
+  -- Part A — needs all three
+  has_leaves      := pt ~ '\m(palm|frond|banana leaf|monstera|leaves|leaf|foliage|jungle)\M';
+  has_directional := pt ~ '\m(directional|varied angle|tossed|scatter|rotated|multi-angle|overlapping|turning leaves|fanning)\M';
+  has_open_space  := pt ~ '\m(open space|breathing room|breathing space|scatter|tossed|sparse)\M';
+  has_single_color := pt ~ '\m(monochrome|single ink|one color|one-color|black ink|single-color|silhouette|tonal)\M';
+
+  -- Part B — any one
+  prohibited := ARRAY['banana', 'banana pod', 'grape', 'bird', 'birds', 'parrot', 'toucan', 'butterfly', 'butterflies', 'moth'];
+  prohibited_hit := NULL;
+  FOR i IN 1..array_length(prohibited, 1) LOOP
+    IF pt LIKE '%' || prohibited[i] || '%' THEN
+      prohibited_hit := prohibited[i];
+      EXIT;
+    END IF;
+  END LOOP;
+
+  -- Acceptable cues — at least one
+  has_acceptable := pt ~ '\m(tree trunk|tree trunks|branch|branches|twig|bark|monkey|sloth|lemur|orangutan|gibbon|tarsier|fruit|cherry|apple|pear|pomegranate|fig|orange)\M';
+
+  -- Decision: BLOCK only when (Part A all 3) AND (Part B any) AND not acceptable.
+  -- Part A here = leaves+directional+open_space+(NOT single_color).
+  IF has_leaves AND has_directional AND has_open_space AND (NOT has_single_color)
+     AND prohibited_hit IS NOT NULL THEN
+    RAISE EXCEPTION
+      'Settlement violation on publish of design id=%: prompt satisfies Part A (directional %s + open space + multi-color) AND Part B (%). To override, set local: SET LOCAL settlement.allow_override = ''true''; — and document why in notes.',
+      NEW.id,
+      (CASE WHEN pt LIKE '%palm%' THEN 'palm' WHEN pt LIKE '%banana%' THEN 'banana' WHEN pt LIKE '%frond%' THEN 'frond' ELSE 'leaves' END),
+      prohibited_hit;
+  END IF;
+
+  -- NEEDS_REVIEW (Part A satisfied without Part B but no acceptable cue) is
+  -- borderline. We log it but don't block — the JS gate already catches this
+  -- pre-gen. Trigger writes a soft notes flag for audit instead.
+  IF has_leaves AND has_directional AND has_open_space AND (NOT has_single_color)
+     AND prohibited_hit IS NULL AND NOT has_acceptable THEN
+    NEW.notes := COALESCE(NEW.notes || E'\n', '') ||
+      'settlement: Part A satisfied without acceptable cue — review pattern for tree-trunk/branch presence (' || now()::text || ')';
+  END IF;
+
+  RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+DROP TRIGGER IF EXISTS settlement_publish_check ON spoon_all_designs;
+CREATE TRIGGER settlement_publish_check
+  BEFORE UPDATE OF is_published ON spoon_all_designs
+  FOR EACH ROW
+  EXECUTE FUNCTION settlement_publish_check();
+
+-- Also fire on INSERT for any row that comes in with is_published=true.
+DROP TRIGGER IF EXISTS settlement_insert_check ON spoon_all_designs;
+CREATE TRIGGER settlement_insert_check
+  BEFORE INSERT ON spoon_all_designs
+  FOR EACH ROW
+  WHEN (NEW.is_published IS TRUE)
+  EXECUTE FUNCTION settlement_publish_check();
← 45ff467 wallco-ai: gate butterfly-trellis + drunk-animals batches th · back to Wallco Ai · wallco.ai: wall-fit crop tool on /design/:id (W×H input, til 5fee57a →