Saas-voc-server/migrations/006_insight_action_guard_hardening.sql

CREATE OR REPLACE FUNCTION voc.validate_insight_decision_insert()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
  source_result jsonb;
  matched_insight jsonb;
  allowed_evidence_ids jsonb;
  previous_decision voc.insight_decision%ROWTYPE;
BEGIN
  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 = 'insight decision source must be a completed or partial voc_insight run in the same workspace';
  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 = 'insight decision source insight does not exist in the source analysis result';
  END IF;

  IF jsonb_array_length(NEW.reviewed_evidence_ids) = 0 THEN
    RAISE EXCEPTION USING
      ERRCODE = '23514',
      MESSAGE = 'at least one reviewed evidence ID is required';
  END IF;

  IF source_result ->> 'mode' = 'deterministic'
    AND NEW.decision <> 'needs_more_evidence'
  THEN
    RAISE EXCEPTION USING
      ERRCODE = '23514',
      MESSAGE = 'deterministic insight results require a needs_more_evidence decision';
  END IF;

  IF EXISTS (
    SELECT 1
    FROM jsonb_array_elements(NEW.reviewed_evidence_ids) AS reviewed(value)
    WHERE jsonb_typeof(reviewed.value) <> 'string'
  ) THEN
    RAISE EXCEPTION USING
      ERRCODE = '23514',
      MESSAGE = 'reviewed 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(NEW.reviewed_evidence_ids) AS reviewed(value)
    WHERE NOT EXISTS (
      SELECT 1
      FROM jsonb_array_elements(allowed_evidence_ids) AS allowed(value)
      WHERE jsonb_typeof(allowed.value) = 'string'
        AND allowed.value = reviewed.value
    )
  ) THEN
    RAISE EXCEPTION USING
      ERRCODE = '23514',
      MESSAGE = 'reviewed evidence must belong to the selected insight';
  END IF;

  IF EXISTS (
    SELECT 1
    FROM jsonb_array_elements(allowed_evidence_ids) AS allowed(value)
    WHERE jsonb_typeof(allowed.value) <> 'string'
      OR NOT EXISTS (
        SELECT 1
        FROM jsonb_array_elements(NEW.reviewed_evidence_ids) AS reviewed(value)
        WHERE reviewed.value = allowed.value
      )
  ) THEN
    RAISE EXCEPTION USING
      ERRCODE = '23514',
      MESSAGE = 'reviewed evidence must cover every evidence ID in the selected insight';
  END IF;

  IF NEW.supersedes_id IS NULL THEN
    IF NEW.version <> 1 THEN
      RAISE EXCEPTION USING
        ERRCODE = '23514',
        MESSAGE = 'the first insight decision version must be 1';
    END IF;
  ELSE
    SELECT * INTO previous_decision
    FROM voc.insight_decision
    WHERE id = NEW.supersedes_id;

    IF NOT FOUND
      OR previous_decision.workspace_id <> NEW.workspace_id
      OR previous_decision.source_analysis_id <> NEW.source_analysis_id
      OR previous_decision.source_insight_id <> NEW.source_insight_id
      OR previous_decision.version + 1 <> NEW.version
    THEN
      RAISE EXCEPTION USING
        ERRCODE = '23514',
        MESSAGE = 'superseded insight decision must be the preceding version for the same source';
    END IF;
  END IF;

  RETURN NEW;
END;
$$;

ALTER TABLE voc.action_item
  DROP CONSTRAINT IF EXISTS action_item_source_decision_kind_check;

-- NOT VALID preserves legacy rows while enforcing the source combination on new writes.
ALTER TABLE voc.action_item
  ADD CONSTRAINT action_item_source_decision_kind_check
  CHECK (
    source_kind = 'insight'
    OR (
      source_analysis_id IS NULL
      AND source_insight_id IS NULL
      AND source_decision_id IS NULL
    )
  ) NOT VALID;

CREATE OR REPLACE FUNCTION voc.validate_action_item_source_decision()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
  source_decision voc.insight_decision%ROWTYPE;
BEGIN
  IF NEW.source_kind IS DISTINCT FROM 'insight' THEN
    IF NEW.source_analysis_id IS NOT NULL
      OR NEW.source_insight_id IS NOT NULL
      OR NEW.source_decision_id IS NOT NULL
    THEN
      RAISE EXCEPTION USING
        ERRCODE = '23514',
        MESSAGE = 'non-insight actions cannot carry insight analysis, insight, or decision sources';
    END IF;

    RETURN NEW;
  END IF;

  IF NEW.source_analysis_id IS NULL
    OR NEW.source_insight_id IS NULL
    OR btrim(NEW.source_insight_id) = ''
    OR NEW.source_decision_id IS NULL
  THEN
    RAISE EXCEPTION USING
      ERRCODE = '23514',
      MESSAGE = 'insight actions require analysis, insight, and decision sources';
  END IF;

  SELECT decision.*
  INTO source_decision
  FROM voc.insight_decision decision
  WHERE decision.id = NEW.source_decision_id
    AND decision.workspace_id = NEW.workspace_id
    AND decision.source_analysis_id = NEW.source_analysis_id
    AND decision.source_insight_id = NEW.source_insight_id;

  IF NOT FOUND THEN
    RAISE EXCEPTION USING
      ERRCODE = '23514',
      MESSAGE = 'action source decision must match the action workspace, analysis, and insight';
  END IF;

  IF NOT source_decision.is_current THEN
    RAISE EXCEPTION USING
      ERRCODE = '23514',
      MESSAGE = 'action source decision must be the current decision version';
  END IF;

  IF source_decision.decision = 'rejected' THEN
    RAISE EXCEPTION USING
      ERRCODE = '23514',
      MESSAGE = 'rejected insight decisions cannot create actions';
  END IF;

  IF source_decision.decision = 'needs_more_evidence' THEN
    IF NEW.action_type <> 'data_quality' OR btrim(NEW.validation_metric) = '' THEN
      RAISE EXCEPTION USING
        ERRCODE = '23514',
        MESSAGE = 'needs_more_evidence decisions require a data_quality action and validation metric';
    END IF;
  ELSIF NEW.action_type = 'data_quality' THEN
    RAISE EXCEPTION USING
      ERRCODE = '23514',
      MESSAGE = 'confirmed insight decisions require a formal action type';
  END IF;

  IF jsonb_typeof(NEW.evidence_ids) IS DISTINCT FROM 'array'
    OR jsonb_array_length(NEW.evidence_ids) = 0
  THEN
    RAISE EXCEPTION USING
      ERRCODE = '23514',
      MESSAGE = 'insight action evidence must be a non-empty array';
  END IF;

  IF EXISTS (
    SELECT 1
    FROM jsonb_array_elements(NEW.evidence_ids) AS evidence(value)
    WHERE jsonb_typeof(evidence.value) <> 'string'
  ) THEN
    RAISE EXCEPTION USING
      ERRCODE = '23514',
      MESSAGE = 'insight action evidence IDs must be strings';
  END IF;

  IF EXISTS (
    SELECT 1
    FROM jsonb_array_elements(NEW.evidence_ids) AS evidence(value)
    WHERE NOT EXISTS (
      SELECT 1
      FROM jsonb_array_elements(source_decision.reviewed_evidence_ids) AS reviewed(value)
      WHERE reviewed.value = evidence.value
    )
  ) THEN
    RAISE EXCEPTION USING
      ERRCODE = '23514',
      MESSAGE = 'action evidence must be included in the reviewed decision evidence';
  END IF;

  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS action_item_source_decision_guard ON voc.action_item;

CREATE TRIGGER action_item_source_decision_guard
BEFORE INSERT OR UPDATE OF
  workspace_id,
  source_kind,
  source_analysis_id,
  source_insight_id,
  source_decision_id,
  action_type,
  validation_metric,
  evidence_ids
ON voc.action_item
FOR EACH ROW
EXECUTE FUNCTION voc.validate_action_item_source_decision();