-- Write-time idempotency: repeated cron emissions of the same open
-- signal coalesce. The architectural fix to the duplicate-paragraph
-- symptom that motivated ADR-048.
CREATE UNIQUE INDEX governance_signals_open_dedup
  ON governance_signals (scope, scope_id, source, pattern_type)
  WHERE status = 'open';

CREATE INDEX governance_signals_org_status_idx
  ON governance_signals (org_id, status, detected_at DESC);
CREATE INDEX governance_signals_team_status_idx
  ON governance_signals (team_id, status, detected_at DESC)
  WHERE team_id IS NOT NULL;
CREATE INDEX governance_signals_severity_open_idx
  ON governance_signals (severity, detected_at DESC)
  WHERE status = 'open';
CREATE INDEX governance_signals_agent_ids_gin
  ON governance_signals USING GIN (agent_ids);

governance_signal_emit(
  p_scope TEXT,
  p_scope_id TEXT,
  p_source TEXT,
  p_pattern_type TEXT,
  p_severity TEXT,
  p_org_id TEXT,
  p_team_id UUID,
  p_agent_ids TEXT[],
  p_detail JSONB,
  p_source_ref JSONB,
  p_detected_by TEXT,
  p_expires_at TIMESTAMPTZ DEFAULT NULL
) RETURNS governance_signals

governance_signal_acknowledge(p_id, p_actor, p_actor_role, p_action_taken DEFAULT NULL)
governance_signal_resolve(p_id, p_actor, p_actor_role, p_resolution_status, p_action_taken DEFAULT NULL)
governance_signal_dismiss(p_id, p_actor, p_actor_role, p_reason DEFAULT NULL)