Distros have different goals from upstream software, and upstreams all have different policies too.
For instance, plenty of upstream software never even releases security fixes in older versions, yet distributions might be committed to supporting them for 5 or 10 years in their LTS releases.
The only universal solution to this is back-porting: it reduces the risk of exposing LTS release customers to backwards compatibility issues, but increases the risk of a bad patch slightly.
If upstreams cared about the things distro customers cared about too (don't break my stuff I haven't changed), it would be much easier to put the blame solely on distros, but they don't.
I do think it's unsolvable, since it's not a technical problem, but a societal one: Debian packages already list patched CVEs in their changelogs in a standardized format.
Introducing a new tool will only add another on top of the existing tools, when nothing tells us that people will use it! (plug the xkcd on n standards -> fix with a better standard -> n +1 standards)
Basically, you want to make everyone use a single tool when humans always strive to build something better or just different :)
The best we could hope to achieve is to have the CVE MITRE database start accepting "patched in" strings from all the distributors (so as Ubuntu pushes a signed and patched package to the archive, it pings the CVE db with a new version string for eg. Ubuntu-22.04 namespace).
Even that gets tricky with dependencies and issues covering multiple packages ("this is only patched if you've updated both of these"), though that's a technically solvable problem.
I agree with pretty much everything you said, but possibly a common metadata format (both our separate tools report differently but off the same data, etc).
I also think that you’re probably right we the patched in, it’s not infinitely scalable, but I doubt it needs to be anyway
For instance, plenty of upstream software never even releases security fixes in older versions, yet distributions might be committed to supporting them for 5 or 10 years in their LTS releases.
The only universal solution to this is back-porting: it reduces the risk of exposing LTS release customers to backwards compatibility issues, but increases the risk of a bad patch slightly.
If upstreams cared about the things distro customers cared about too (don't break my stuff I haven't changed), it would be much easier to put the blame solely on distros, but they don't.