I've posted many times that I think the best solution is to allow interested teachers to carry, which costs nothing and is within a building principal's power in MA and many other states. Almost any other solution is either too unreliable, too expensive, or too risky.
Having an SRO helps GREATLY, but as you point out it needs to be the right person; I've worked with good ones and bad ones. The thing to remember about SROs is that "confronting a school shooter" is only their worst-case scenario; they wear many, many other hats around the building, and sometimes that skillset doesn't mesh with the kind of courage that's needed during a school shooting.
The key (and I think about this specific situation a GREAT deal, for obvious reasons) is that the person who's armed has to care about the kids in the school. They're not going to be motivated to take a pistol and go up against a rifle merely by money.
The Uvalde response was VERY weird on a lot of levels, compared to other school-shooter responses over the years. Usually, the cops do a lot better.