RAW: left to DM discretion
The book fails to give the DM guidance on these matters, with phrasing like:
You might call for a Sanity saving throw [..] A failed Sanity save might result ..
So it is basically left to DM fiat. However, I do believe there is a sensible way to handle this.
Which saving throws?
There are two ways to interpret the sentence in the list, and I advise you look at it like this:
(Resisting an effect) (conferred by an attack or spell) (that deals psychic damage).
Ie. "that" refers back to "effect". Meaning you only ask for a Sanity save when the effect deals psychic damage. The effect can be an attack or spell. This would mean that only the initial save will be made with Sanity and Wisdom will be used to shake off the effect.
This will also mean that should you wish to inflict madness as part of failing a Sanity save, targets will at most get 1 of it. I think that potentially causing 10 different madnesses (though realistically 2-3 usually) might be too powerful for this spell. Also, if used on NPC-s, incredibly hard to track.
Replacing the effect
If you do this, you should do it consistently. Spells of varied power levels would result in insanity, and the three "tiers" of insanity provided in the DMG do not have that much granularity, which might result in much weaker on stronger effects than intended for those spells. Also, the affected spells would lose their individuality. I would generally recommend leaving spell effects as they are.
If you still wish to replace these effects (eg. for flavor/setting reasons), I would advise that you limit the duration of the madness to the duration of the spell (or its effect, if that makes more sense). Also, the madness table you roll on should depend on the spell level (eg. cantrip-lv2 > short-term, lv3-6 > long-term, lv7+ > indefinite).