It turns /usr/local/Homebrew into a git repository; not /usr/local itself. Whatever it links into /usr/local/{bin,include,lib,sbin,share} is just a symlink into /usr/local/Cellar.
Are you sure about that? I currently have it installed in ~/brew and commands link from ~/brew/Cellar into ~/brew/bin and there is a .git directory in ~/brew. I used to put it in /usr/local and it irked me that there was a /usr/local/.git. That was the main reason I stopped putting it there.
They used to put the repo in /user/local but moved it to /usr/local/Homebrew a few years ago.
Yes, I'm sure, it exactly like that on this very machine where I type this comment. However, the Apple Silicon version installed into /opt/homebrew creates the repo directly in its prefix, just like yours installed into ~/brew. Here the content of Homebrew dir is mixed with Cellar, Frameworks and Caskroom, which are normally one directory up. So it looks like it behaves differently when the prefix is /usr/local.
It used to turn /usr/local into a Git repository, at least until that got locked down with SIP.
While /usr is locked down with SIP, /usr/local is not, it is perfectly writable; brew does create new directories there and changes ownership of existing ones.