Saas-voc-server/migrations/004_action_item_source_integrity.sql

CREATE OR REPLACE FUNCTION voc.validate_action_item_source_analysis()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
  source_result jsonb;
  matched_insight jsonb;
  allowed_evidence_ids jsonb;
BEGIN
  IF NEW.source_analysis_id IS NULL THEN
    IF NEW.source_insight_id IS NOT NULL THEN
      RAISE EXCEPTION USING
        ERRCODE = '23514',
        MESSAGE = 'action source insight requires a source analysis';
    END IF;
    RETURN NEW;
  END IF;

  SELECT analysis.result
  INTO source_result
  FROM voc.analysis_run analysis
  WHERE analysis.id = NEW.source_analysis_id
    AND analysis.workspace_id = NEW.workspace_id
    AND analysis.analysis_type = 'voc_insight'
    AND analysis.status IN ('completed', 'partial');

  IF NOT FOUND THEN
    RAISE EXCEPTION USING
      ERRCODE = '23514',
      MESSAGE = 'action source analysis must be a completed or partial voc_insight run in the same workspace';
  END IF;

  IF NEW.source_insight_id IS NULL OR btrim(NEW.source_insight_id) = '' THEN
    RAISE EXCEPTION USING
      ERRCODE = '23514',
      MESSAGE = 'action source insight is required';
  END IF;

  SELECT insight.value
  INTO matched_insight
  FROM jsonb_array_elements(
    CASE
      WHEN jsonb_typeof(source_result -> 'insights') = 'array' THEN source_result -> 'insights'
      ELSE '[]'::jsonb
    END
  ) AS insight(value)
  WHERE insight.value ->> 'id' = NEW.source_insight_id
  LIMIT 1;

  IF matched_insight IS NULL THEN
    RAISE EXCEPTION USING
      ERRCODE = '23514',
      MESSAGE = 'action source insight does not exist in the source analysis result';
  END IF;

  IF jsonb_typeof(NEW.evidence_ids) IS DISTINCT FROM 'array' THEN
    RAISE EXCEPTION USING
      ERRCODE = '23514',
      MESSAGE = 'action source evidence must be an array';
  END IF;
  IF jsonb_array_length(NEW.evidence_ids) = 0 THEN
    RAISE EXCEPTION USING
      ERRCODE = '23514',
      MESSAGE = 'action source evidence is required';
  END IF;
  IF EXISTS (
    SELECT 1
    FROM jsonb_array_elements(NEW.evidence_ids) AS submitted(value)
    WHERE jsonb_typeof(submitted.value) <> 'string'
  ) THEN
    RAISE EXCEPTION USING
      ERRCODE = '23514',
      MESSAGE = 'action source evidence IDs must be strings';
  END IF;

  allowed_evidence_ids := CASE
    WHEN jsonb_typeof(matched_insight -> 'evidenceIds') = 'array' THEN matched_insight -> 'evidenceIds'
    ELSE '[]'::jsonb
  END;

  IF EXISTS (
    SELECT 1
    FROM jsonb_array_elements_text(NEW.evidence_ids) AS submitted(id)
    WHERE NOT EXISTS (
      SELECT 1
      FROM jsonb_array_elements(allowed_evidence_ids) AS allowed(value)
      WHERE jsonb_typeof(allowed.value) = 'string'
        AND allowed.value = to_jsonb(submitted.id)
    )
  ) THEN
    RAISE EXCEPTION USING
      ERRCODE = '23514',
      MESSAGE = 'action source evidence must belong to the selected insight';
  END IF;

  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS action_item_source_analysis_guard ON voc.action_item;

CREATE TRIGGER action_item_source_analysis_guard
BEFORE INSERT OR UPDATE OF source_analysis_id, source_insight_id, evidence_ids, workspace_id ON voc.action_item
FOR EACH ROW
EXECUTE FUNCTION voc.validate_action_item_source_analysis();