Saas-voc-server/migrations/005_insight_decision_action_idempotency.sql

CREATE TABLE IF NOT EXISTS voc.insight_decision (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  public_id text NOT NULL UNIQUE,
  workspace_id bigint NOT NULL REFERENCES voc.workspace(id) ON DELETE CASCADE,
  source_analysis_id bigint NOT NULL REFERENCES voc.analysis_run(id) ON DELETE RESTRICT,
  source_insight_id text NOT NULL,
  decision text NOT NULL CHECK (decision IN ('confirmed', 'rejected', 'needs_more_evidence')),
  reviewed_evidence_ids jsonb NOT NULL DEFAULT '[]'::jsonb,
  comment text NOT NULL DEFAULT '',
  decided_by_external_id text NOT NULL,
  decided_at timestamptz NOT NULL DEFAULT now(),
  version integer NOT NULL CHECK (version > 0),
  supersedes_id bigint REFERENCES voc.insight_decision(id) ON DELETE RESTRICT,
  is_current boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT insight_decision_reviewed_evidence_ids_check CHECK (
    jsonb_typeof(reviewed_evidence_ids) = 'array'
    AND jsonb_array_length(reviewed_evidence_ids) <= 100
  ),
  CONSTRAINT insight_decision_comment_check CHECK (
    decision = 'confirmed' OR btrim(comment) <> ''
  ),
  CONSTRAINT insight_decision_source_version_unique UNIQUE (
    workspace_id,
    source_analysis_id,
    source_insight_id,
    version
  )
);

CREATE UNIQUE INDEX IF NOT EXISTS insight_decision_source_current_unique
  ON voc.insight_decision (workspace_id, source_analysis_id, source_insight_id)
  WHERE is_current;

CREATE UNIQUE INDEX IF NOT EXISTS insight_decision_supersedes_unique
  ON voc.insight_decision (supersedes_id)
  WHERE supersedes_id IS NOT NULL;

CREATE INDEX IF NOT EXISTS insight_decision_workspace_decided_idx
  ON voc.insight_decision (workspace_id, decided_at DESC, id DESC);

CREATE INDEX IF NOT EXISTS insight_decision_analysis_insight_version_idx
  ON voc.insight_decision (source_analysis_id, source_insight_id, version DESC, id DESC);

CREATE OR REPLACE FUNCTION voc.validate_insight_decision_insert()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
  source_result jsonb;
  matched_insight 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;

  IF EXISTS (
    SELECT 1
    FROM jsonb_array_elements_text(NEW.reviewed_evidence_ids) AS reviewed(id)
    WHERE NOT EXISTS (
      SELECT 1
      FROM jsonb_array_elements(
        CASE
          WHEN jsonb_typeof(matched_insight -> 'evidenceIds') = 'array' THEN matched_insight -> 'evidenceIds'
          ELSE '[]'::jsonb
        END
      ) AS allowed(value)
      WHERE jsonb_typeof(allowed.value) = 'string'
        AND allowed.value = to_jsonb(reviewed.id)
    )
  ) THEN
    RAISE EXCEPTION USING
      ERRCODE = '23514',
      MESSAGE = 'reviewed evidence must belong to 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;
$$;

DROP TRIGGER IF EXISTS insight_decision_insert_guard ON voc.insight_decision;

CREATE TRIGGER insight_decision_insert_guard
BEFORE INSERT ON voc.insight_decision
FOR EACH ROW
EXECUTE FUNCTION voc.validate_insight_decision_insert();

CREATE OR REPLACE FUNCTION voc.enforce_insight_decision_append_only()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  IF OLD.workspace_id IS DISTINCT FROM NEW.workspace_id
    OR OLD.source_analysis_id IS DISTINCT FROM NEW.source_analysis_id
    OR OLD.source_insight_id IS DISTINCT FROM NEW.source_insight_id
    OR OLD.decision IS DISTINCT FROM NEW.decision
    OR OLD.reviewed_evidence_ids IS DISTINCT FROM NEW.reviewed_evidence_ids
    OR OLD.comment IS DISTINCT FROM NEW.comment
    OR OLD.decided_by_external_id IS DISTINCT FROM NEW.decided_by_external_id
    OR OLD.decided_at IS DISTINCT FROM NEW.decided_at
    OR OLD.version IS DISTINCT FROM NEW.version
    OR OLD.supersedes_id IS DISTINCT FROM NEW.supersedes_id
    OR OLD.created_at IS DISTINCT FROM NEW.created_at
    OR NOT OLD.is_current
    OR NEW.is_current
  THEN
    RAISE EXCEPTION USING
      ERRCODE = '23514',
      MESSAGE = 'insight decisions are append-only; only the current flag may be retired';
  END IF;

  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS insight_decision_append_only_guard ON voc.insight_decision;

CREATE TRIGGER insight_decision_append_only_guard
BEFORE UPDATE ON voc.insight_decision
FOR EACH ROW
EXECUTE FUNCTION voc.enforce_insight_decision_append_only();

ALTER TABLE voc.action_item
  ADD COLUMN IF NOT EXISTS source_decision_id bigint,
  ADD COLUMN IF NOT EXISTS source_kind text,
  ADD COLUMN IF NOT EXISTS creation_key text;

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM pg_constraint
    WHERE conname = 'action_item_source_decision_fkey'
      AND conrelid = 'voc.action_item'::regclass
  ) THEN
    ALTER TABLE voc.action_item
      ADD CONSTRAINT action_item_source_decision_fkey
      FOREIGN KEY (source_decision_id)
      REFERENCES voc.insight_decision(id)
      ON DELETE RESTRICT;
  END IF;
END;
$$;

UPDATE voc.action_item
SET source_kind = CASE
      WHEN source_analysis_id IS NOT NULL THEN 'insight'
      ELSE 'rule_action'
    END
WHERE source_kind IS NULL;

UPDATE voc.action_item
SET creation_key = public_id
WHERE creation_key IS NULL OR btrim(creation_key) = '';

ALTER TABLE voc.action_item
  ALTER COLUMN source_kind SET DEFAULT 'rule_action',
  ALTER COLUMN source_kind SET NOT NULL,
  ALTER COLUMN creation_key SET NOT NULL;

ALTER TABLE voc.action_item
  DROP CONSTRAINT IF EXISTS action_item_source_kind_check;

ALTER TABLE voc.action_item
  ADD CONSTRAINT action_item_source_kind_check
  CHECK (source_kind IN ('insight', 'raw_feedback', 'rule_action'));

ALTER TABLE voc.action_item
  DROP CONSTRAINT IF EXISTS action_item_source_decision_kind_check;

ALTER TABLE voc.action_item
  ADD CONSTRAINT action_item_source_decision_kind_check
  CHECK (source_decision_id IS NULL OR source_kind = 'insight');

CREATE UNIQUE INDEX IF NOT EXISTS action_item_workspace_creation_key_unique
  ON voc.action_item (workspace_id, creation_key);

CREATE INDEX IF NOT EXISTS action_item_workspace_source_kind_idx
  ON voc.action_item (workspace_id, source_kind, id DESC);

CREATE INDEX IF NOT EXISTS action_item_source_decision_idx
  ON voc.action_item (source_decision_id, id DESC)
  WHERE source_decision_id IS NOT NULL;

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_decision_id IS NULL THEN
    RETURN NEW;
  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 EXISTS (
    SELECT 1
    FROM jsonb_array_elements_text(NEW.evidence_ids) AS evidence(id)
    WHERE NOT (source_decision.reviewed_evidence_ids ? evidence.id)
  ) 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 source_decision_id, source_analysis_id, source_insight_id, workspace_id
ON voc.action_item
FOR EACH ROW
EXECUTE FUNCTION voc.validate_action_item_source_decision();