Having worked for a business that made a serious go of running everything out of stored procedures, I have to say that lack of version control was a huge problem and effectively limited all development to a single person who held all the rules in their head.
This is essentially what we do. All of our stored procedures, views, etc are kept in *_api schemas. Those schemas just get fully dropped and reinstalled whenever we migrate. It works really well, and has basically zero cognitive load overhead for developers.
