Patterns for using Stored Procedures

I want to introduce some stored procedures and have their code version controlled within dbt. Ideally dbt would CREATE OR REPLACE all of the stored procedures when it deploys to implement any updates.

Are there any established best practices for using stored procedures in dbt?

One solution would be to create a macro that generates the stored procedure but ideally I would like the stored procedures to exist in the database so they could be utilized by users doing ad-hoc queries.