However, I was approached this week by an attending who told me that my home Psych department (where I am getting my PhD) is prepared to offer me lab space, start-up funds, and I junior faculty title if I decide to stay at the same university for residency. This is obviously a really flattering offer, but I definitely did not have plans to stay here otherwise. I guess I'm just looking for some input from more seasoned academics on this matter. Can I (or should I even) try to leverage this offer at a more prestigious institution to set up some kind of independent research program? Or is this the best offer of this kind I can hope for?
You don't have enough information yet. They are "prepared" to make an offer, but this is meaningless unless it's a written offer. It also depends a lot on what is "mid tier", how big is the startup, and what is your scientific ambition. You can't really leverage until you have solid competing offers.
I have had long conversations with several dept chairs of psychiatry in prominent departments, several on your list. The current state of psychiatry departments, due to a relatively heavy investment of NIH funding in brain research, is that you don't really have "leverage" at major departments until you have an R01. Everyone on the job market who's hunting for a decent job with a startup has an R01. That's not to say if you are truly exceptional and don't have a funding record, people might still consider you, but the sort of offers you will get are likely subpar. Isn't it ironic that blowing more money into the system actually makes the rank and files' life more difficult?
Also, without leaving your mid tier institution, you may not discover an entirely alien world operating on the coasts. For example, a non trivial number of individuals working at Stanford have relatively large private practices that are so extremely profitable with wealthy Silicon Valley residents, their grant funding and institutional support pale in comparison in terms of their total compensation. When this sort of thing happens, your career and life expectation completely change. I.e. If I tell you you can be a very successful researcher at Stanford getting paid 150k (living in a tiny house two hours away) or a somewhat more part time researcher with total compensation 350k+, what would you choose? The nature of soft money research is that until you are at a very senior level and good at playing politics, hard money is not available until mid/late career, by which time if you pick the more lucrative track you might have been completely financially independent. You basically endow a chair for yourself. These weird things are not really available in the mid tier places. On the other hand, cost of living is vastly lower outside of the nodes. Just some food for your thought.
The issue of scientific ambition is also an interesting one. The current distribution of NIH dollars is highly inequitable. Prominent departments get a lot of it, and a lot of that go into massive center grants and multi site collaborations. If you are okay with specializing in your own niche area where your current department has some strength, apply for one R01 after another and eventually living a small town grocer life, your life is better if you stay. But if you want to one day gun for the P awards, become some international leader, raise tens of millions of dollars of private/foundation money, you will probably need a better name and better network. Even people who end up directing centers at mid level places often grow up in high end departments. Not an absolute requirement, but everyone says "mentorship is key"...which if you strip the nonsense means that the game is rigged, and all is cronyism. Will you be "in" enough if you stayed where you are? Is the endless, tiring, risky and potentially ultimately fruitless hustle what you want in life? Only you can answer that question.
My suggestion is apply everywhere, open your ears, and have at least a written offer in your hand if you want to "negotiate". You may unpleasantly discover that people don't want you as much as you think they do.