Thank you for the link to the Princeton group. That is encouraging. Aside from that, I share your lack of optimism about the prospects for this niche.
Most research programmers, in my experience, work in a lab for a PI. Over time, these programmers have become more valued by their team. However, they often still face a hard cap on career advancement. They generally are paid considerably less than they'd earn in the private sector, with far less opportunity for career growth. I think they often make creative contributions to research that would be "co-author" level worthy if they came from someone in an academic track, but they are frequently left off publications. They don't get the benefits that come with academic careers, such as sabbaticals, and they often work to assignment, with relatively little autonomy. The right career path and degree to build the skills required for this kind of programming is often a mismatch for the research-oriented degrees that are essential to advancement in an academic environment (including leadership roles that aren't research roles).
In short, I think there is a deep need for the emerging "research software engineer" you mention, but at this point, I can't recommend these jobs to someone with the talent to do them. There are a few edge cases (lifestyle, trailing spouse in academic, visa restrictions), but overall, these jobs are not competitive with the pay, career growth, autonomy, and even job security elsewhere (university jobs have a reputation for job security, but many research programmers are paid purely through a grant, so often these are 1-2 year appointments that can be extended only if the grant is renewed).
The Princeton group you linked to is encouraging - working for a unit of software developers who engage with researchers could be an improvement. Academia is still a long, long way away from building the career path that would be necessary to attract and keep talent in this field, though.