BEGIN;
CREATE TABLE public.server_probe_retirement_claims (
 receipt_id uuid PRIMARY KEY,
 operation_id uuid NOT NULL UNIQUE,
 plan_id uuid NOT NULL UNIQUE,
 connection_id uuid NOT NULL,
 source_proof text NOT NULL CHECK(source_proof ~ '^[a-f0-9]{64}$'),
 claim_token uuid NOT NULL,
 claim_xid xid8 NOT NULL DEFAULT pg_current_xact_id()
);
CREATE FUNCTION public.server_probe_retirement_claim_guard() RETURNS trigger
LANGUAGE plpgsql SET search_path=pg_catalog,public SET TimeZone='UTC' AS $$
BEGIN
 IF TG_OP='INSERT' AND NEW.claim_xid=pg_current_xact_id() AND EXISTS(
  SELECT 1 FROM public.server_probe_storage_admissions a
  JOIN public.mcp_operations o ON o.id=a.execution_id
  JOIN public.mcp_plans p ON p.id=o.plan_id AND p.connection_id=o.connection_id
  JOIN public.mcp_connections c ON c.id=o.connection_id AND c.owner_id=a.owner_id
  WHERE a.receipt_id=NEW.receipt_id AND a.identity_kind='mcp' AND a.claim_token IS NULL
   AND a.claim_attempted_at IS NOT NULL AND a.cancelled_at IS NULL AND a.purged_at IS NULL
   AND o.id=NEW.operation_id AND p.id=NEW.plan_id AND c.id=NEW.connection_id
   AND o.state='applied' AND o.verification='inconclusive' AND o.error_code IS NULL
   AND isfinite(o.finished_at) AND o.finished_at<transaction_timestamp()-interval '180 days'
   AND p.operation_id='servers.probes.run' AND p.operation_version=1 AND p.state='completed'
 ) THEN RETURN NEW; END IF;
 RAISE EXCEPTION 'diagnostic retirement claim cannot be replaced or reopened';
END $$;
CREATE TRIGGER server_probe_retirement_claim_guard BEFORE INSERT OR UPDATE OR DELETE
 ON public.server_probe_retirement_claims FOR EACH ROW EXECUTE FUNCTION public.server_probe_retirement_claim_guard();
CREATE TRIGGER server_probe_retirement_claim_no_truncate BEFORE TRUNCATE
 ON public.server_probe_retirement_claims FOR EACH STATEMENT EXECUTE FUNCTION public.server_probe_retirement_claim_guard();
CREATE FUNCTION public.server_probe_retired_source_insert_guard() RETURNS trigger
LANGUAGE plpgsql SET search_path=pg_catalog,public AS $$
BEGIN
 IF EXISTS(
  SELECT 1 FROM public.server_probe_retirement_claims r
  JOIN public.mcp_operation_replay_tombstones t ON t.operation_id=r.operation_id AND t.plan_id=r.plan_id
   AND t.connection_id=r.connection_id AND t.action_id='servers.probes.run' AND NOT t.target_global
  JOIN public.mcp_operations o ON o.id=r.operation_id AND o.plan_id=r.plan_id AND o.connection_id=r.connection_id
  JOIN public.server_probe_storage_admissions a ON a.receipt_id=r.receipt_id AND a.execution_id=r.operation_id
  WHERE r.receipt_id=NEW.receipt_id AND r.operation_id=NEW.operation_id AND r.source_proof=NEW.proof
   AND r.claim_xid<>pg_current_xact_id() AND r.claim_token::text=current_setting('mm.probe_retirement_claim',true)
   AND NEW.source->>'planId'=r.plan_id::text AND NEW.source->>'connectionId'=r.connection_id::text
   AND NEW.source->>'ownerId'=a.owner_id AND NEW.source->>'bindingProof'=a.binding_proof
   AND NEW.source->'serverIds'=to_jsonb(t.target_server_ids)
 ) THEN RETURN NEW; END IF;
 RAISE EXCEPTION 'diagnostic archive requires an exact committed retirement claim';
END $$;
CREATE TRIGGER server_probe_retired_source_insert_guard BEFORE INSERT ON public.server_probe_retired_sources
 FOR EACH ROW EXECUTE FUNCTION public.server_probe_retired_source_insert_guard();
INSERT INTO public.schema_migrations(id) VALUES ('0051_server_probe_retirement_claims') ON CONFLICT(id) DO NOTHING;
COMMIT;