I feel like this process and many variations of it (for example, the NIP-41 and NIP-109 proposals) can all be described in the same NIP and be supported in various degrees, with different security models for different people.
The NIP-41 idea can only be done by people that are very serious about their key security, while NIP-109
... can be just a fallback for when nothing else works (just delete my key).
The key insight here is that people become used to the practice of from time to time, scanning the network for some special events that signal that some keys may have changed, and then they stop following the old one and start following the next. This can be done inside their main social app or on a standalone app or website.
But all these possibilities are not to be seen as "key rotation". They are, as the text for the NIP-41 proposal says, best-effort attempts to minimize damage from losing keys.
More thoughts on this from past month:
