Well, it's complicated, and depends on exactly what part of the Manhattan Project one is looking at. Each of the sites had different purposes and that led to different kinds of internal organizations. The major "production sites" like Oak Ridge and Hanford had huge workforces, most of them in the categories of "construction" and "operation." The "construction" people largely didn't know the purpose of what they were building — they were just following blueprints and instructions — and the "operations" people would know functionally what they had to do but often not what the meaning of it was (the most famous example of this are the Calutron Girls at Oak Ridge, who knew to turn a knob one way if a needle went too far in one direction, and the other way if it went the other direction, but had no idea what the knob was controlling or what the needle was indicating). These were the sites with the heaviest internal compartmentalization, with a tiny number of people actually aware of the project goals (my guess is around 1%, but this is just a guess; we don't have numbers for this kind of thing).

The research sites, like the Berkeley Rad Lab, Chicago Met Lab, and Columbia SAM Labs, were different. Here you had smaller communities dominated by scientists and engineers, usually with considerable knowledge about what they were trying to do. E.g., at Berkeley and Columbia they were researching different methods for enriching uranium, and at Chicago they were figuring out how to construct nuclear reactors. Most of these people likely knew what the ultimate goal of the project was, but if they were limited to that site they might not know what the project was doing outside of that site. They might know there are other sites — e.g., Project Y, or Project X — but they were in theory not even supposed to know where these places were or what they were doing there. Elaborate rules were developed to govern what kind of information could be exchanged between different sites, the first detailed "classification rules," in principle.

Los Alamos (Project Y) is its own special case. It was a research and production site, intended for the most sensitive work relating to bomb design and use. So in many ways it is where the "outputs" of all of the other sites (in terms of materials, like enriched uranium, plutonium, and polonium, but also research knowledge, like the health physics from the Rochester Project) were brought together.

This did raise the problems you indicate about how much knowledge a given group ought to have of the work of another group. The impracticality of perfect compartmentalization in such a situation, where the work of one group necessarily had impacts on the group of another, was a frequently-raised objection by scientists, and every scientist of significance on the Manhattan Project ended up with wartime stories about how they find ways around the compartmentalization rules in order to get their jobs done.

At Los Alamos, there was a sort of meta-compartmentalization applied over the whole lab. There was the secret city itself, where the families of the workers lived, and was already pretty controlled (gates, guards, badges, etc.). But then there was an inner "Technical Area" that was itself gated and guarded, where the work was being done. Colored badges were used to differentiate the broad level of knowledge one had access to. With a yellow badge, you could enter the tech area but had no access to classified information — this was for security. With a blue badge, you could learn some classified information, but not of a technical nature — it was for warehouse employees, people who needed to know schedules and quantities of things, but not detailed data. With a red badge, you could know a lot of information about a specific part of the project, but no more — this was for secretaries and technicians, people who were important to implementing the work but who weren't expected to do anything more than follow orders. And finally, with a white badge, you were allowed to know what they were doing at Los Alamos, and could know as much as your division leader thought you needed to know. So this kicked the "how much compartmentalization" was there up to your boss, essentially, and given that your boss was a scientist trying to accomplish the same ends as you, in principle that meant they could move that bar as necessary. There were some items of information deemed so classified that division leaders were not allowed to disseminate them further ("Top Secret-Limited"), but these tended to be things like, "when exactly will the bomb be ready to use" and other "operational" information.

There was also a colloquium series that was designed to be a compartmentalization-free zone — so long as you had a white badge, anyway — designed to encourage cross-pollination of ideas. The list of topics discussed is an interesting overview — some are quite "basic science," others are quite "applied," and give a sense of what would have been "knowable" to anyone with a white badge. General Groves had been very dubious about the colloquium idea, but Oppenheimer pushed for it, and argued that the scientists would need something like this. (The list of topics, as an aside, comes from the FBI file of the spy Klaus Fuchs, somewhat vindicating Groves' concerns...!)

There were over 300 different sites in the United States alone that had some role in the Manhattan Project, ranging from the mega-sites like Oak Ridge and Hanford to the mega-secret sites like Los Alamos but also lots of things "in between." No single explanation can cover how all of the sites worked, but hopefully the above gives you a sense of it. The way I put it is that secrecy is not a policy but a goal, and it is made into (some kind of) reality through practices that are imposed. Throughout the Manhattan Project, both temporally and spatially, one finds these practices evolving, being applied differently, being sometimes challenged, etc. — there isn't "one thing" going on here, but a series of different implementations that sort of cluster around certain poles. Compartmentalization ("need to know") is one of those "poles," but it had many different ways of being implemented, even within a single site.

There's more that could be said on this, for sure. I talk quite a bit about Manhattan Project secrecy practices in chapter 2 of my 2021 book, Restricted Data: The History of Nuclear Secrecy in the United States.