Yep, I agree and that's where I install stuff on everything other than OS X. One distinction though, I think, is that most of the stuff I install on OS X I don't want to be available system-wide. I'm (typically) not using Homebrew to install daemons that run all the time or things that serve critical system/network functions, so I've never seen a reason to make them available to the entire system. I agree that goes against the Unix way but I started preferring this way of using Homebrew after I had similar problems upgrading and even updating OS X.

Also, what if for some reason a single machine is shared by two people and they need different versions of some programs installed with Homebrew? Installing everything in /usr/local isn't going to look like a good idea then.

Homebrew is not really "manually managed" though - I prefer just things I actually manually install in /usr/local

But not to 775 it and change it's owner from root:root (both of which Homebrew does)