You pretty much never want a .cpp file to be anything but PRIVATE. The only target that should be compiling the .cpp file is the target you are adding it to (there are exceptions, but those scenarios are exceedingly rare). If you add a .cpp file as PUBLIC, you are saying that it should be compiled into the current target and anything that links to it. Note that this is not talking about linking the object file produced from the .cpp file, we’re talking about compiling it. In your example, foo.cpp will be compiled as part of APP and also as part of XYZ. The object file compiled into APP will be used and the one compiled into XYZ is not used. The one compiled into APP shouldn’t be there, you only want it compiled into XYZ and linking to XYZ should then pick up that one.
For a header, the situation is considerably more involved. It has been common for IDEs to not show a header in its file lists unless the header was added to a target. Thus, it was common to see headers added directly in calls to add_executable() and add_library(). By extension, it made sense to add them using target_sources() too, but then you had to decide if PRIVATE, PUBLIC or INTERFACE was the most appropriate. Since headers don’t get compiled directly, the only real difference used to be that these keywords controlled which target(s) that file would appear under in IDEs. The usual way this played out was that a header-only library would get represented as an interface library, which in earlier versions of CMake meant you had no choice but to add headers as INTERFACE. CMake 3.19 added the ability to add headers to interface libraries as PRIVATE (or equivalently to list them in the call to add_library()), which allowed the header to be seen as part of that target in IDEs instead of the target’s consumers. For targets that were not interface libraries, headers would normally be added as PRIVATE so that those headers were associated with that target in IDE file lists.
CMake 3.23 is where things got much more interesting. That release added file sets, which fundamentally changed the recommended way to add headers to your project. Now the PRIVATE, PUBLIC and INTERFACE keywords have different impacts when used for a file set, affecting not just IDEs but also things like header search paths, installation of the headers, and verification of whether headers are self-contained. File sets brought much more powerful capabilities and meaning to headers and how they are used in a project and by consumers of that project. Fundamentally though, the high level meaning of PRIVATE, PUBLIC and INTERFACE still means something similar when defining a file set with target_sources(). PRIVATE means “only used when building this target”, INTERFACE means “only used by things that consume this target, not the target itself”, and PUBLIC merges both of those together. Thus, when you define a headers file set, you need to consider whether the headers are part of that target’s public API or not. Typically, I recommend defining two separate file sets for a target, one to contain the public headers (I often call that header set “api”) and another to contain the private headers (which I typically just call “private”). IDEs should associate headers specified in a file set with the target they are being added to by the target_sources() call in all cases, but I don’t know if they will still use the old behavior of showing non-private headers under consumers of the target.
Probably not quite what you were expecting, but hopefully that fills out a bit more of the landscape for you to explore.