Saas-voc-server/migrations/003_action_item_insight_source.sql

ALTER TABLE voc.analysis_run
  DROP CONSTRAINT IF EXISTS analysis_run_analysis_type_check;

ALTER TABLE voc.analysis_run
  ADD CONSTRAINT analysis_run_analysis_type_check
  CHECK (analysis_type IN ('voice', 'pain_point', 'feature', 'scenario', 'risk', 'report', 'voc_insight'));

ALTER TABLE voc.action_item
  ADD COLUMN IF NOT EXISTS source_insight_id text,
  ADD COLUMN IF NOT EXISTS evidence_ids jsonb NOT NULL DEFAULT '[]'::jsonb,
  ADD COLUMN IF NOT EXISTS validation_metric text NOT NULL DEFAULT '';

ALTER TABLE voc.action_item
  DROP CONSTRAINT IF EXISTS action_item_evidence_ids_check;

ALTER TABLE voc.action_item
  ADD CONSTRAINT action_item_evidence_ids_check
  CHECK (jsonb_typeof(evidence_ids) = 'array' AND jsonb_array_length(evidence_ids) <= 100);

CREATE INDEX IF NOT EXISTS action_item_workspace_source_insight_idx
  ON voc.action_item (workspace_id, source_insight_id, id DESC)
  WHERE source_insight_id IS NOT NULL;

CREATE OR REPLACE FUNCTION voc.validate_action_item_source_analysis()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  IF NEW.source_analysis_id IS NULL THEN
    RETURN NEW;
  END IF;

  PERFORM 1
  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;

  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, workspace_id ON voc.action_item
FOR EACH ROW
EXECUTE FUNCTION voc.validate_action_item_source_analysis();