The talent manager and former husband of Kelly Clarkson died of melanoma, a form of skin cancer, on Thursday, Aug. 7