I disagree that the frontend will be specific in even a "vast" majority of cases--if only because of the obvious proliferation of mobile. And while I agree that you can often make a good-looking server-side-rendered, templated application, I will contest that you can
ever make a server-side-rendered, templated application feel correctly on a mobile device.
Even in the case where you do have a specific frontend, I contend that there's still value in the separation because it very strongly encourages (coupled with tools like, shameless plug, nestjs-data-sec[0]) the writing of very clear viewmodels where thought must be put into what exactly should be exposed over the wire in a way that commingling database objects and template logic doesn't allow.
I think server-side rendered pages are properly rated at this point, not underrated; there are places to use them but they're shrinking as other tooling get better. As an example, even I write React almost exclusively because I write React faster and with fewer errors than any templating language I see in common use except maybe Razor (and Razor implies buying into ASP.NET, which, no). React/TypeScript are hard to get wrong and easy to burn through quickly. And I would call a React page rendered through NextJS a "server-side rendered app"--but I get the feeling you would not. ;)
[0] - https://github.com/eropple/nestjs-data-sec