Scrum4Me/prisma/migrations/20260506001700_story_logs_notify/migration.sql

-- pg_notify trigger op story_logs: emit AFTER INSERT op het gedeelde
-- 'scrum4me_changes'-channel zodat de Sync-tab op /ideas/[id] real-time
-- nieuwe IMPLEMENTATION_PLAN/COMMIT/TEST_RESULT-entries kan tonen zonder
-- handmatige refresh.
--
-- Payload-format consistent met andere triggers in deze codebase:
-- {op:'INSERT', entity:'story_log', id, story_id, product_id, idea_id?}
--
-- product_id en idea_id worden afgeleid via story → pbi → product en
-- story → pbi → idea (1:1 via Idea.pbi_id). Hierdoor kan de SSE-route
-- filteren op productAccessFilter én op user-eigen ideeën zonder extra
-- DB-call per event.

CREATE OR REPLACE FUNCTION notify_story_log_change() RETURNS TRIGGER AS $$
DECLARE
  v_product_id text;
  v_idea_id text;
  payload json;
BEGIN
  SELECT p.product_id, i.id
    INTO v_product_id, v_idea_id
    FROM stories s
    JOIN pbis p ON p.id = s.pbi_id
    LEFT JOIN ideas i ON i.pbi_id = p.id
    WHERE s.id = NEW.story_id;

  payload := json_build_object(
    'op', TG_OP,
    'entity', 'story_log',
    'id', NEW.id,
    'story_id', NEW.story_id,
    'product_id', v_product_id,
    'idea_id', v_idea_id,
    'log_type', NEW.type
  );

  PERFORM pg_notify('scrum4me_changes', payload::text);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER story_logs_notify
  AFTER INSERT ON story_logs
  FOR EACH ROW EXECUTE FUNCTION notify_story_log_change();