Shannon diversity index: Pi relates to all found species, or number of species by site?
I am trying to dig into Shannon's species diversity index to compare the species diversity between different sites

Shannon's diversity index is a measure of alpha diversity, or diversity within a community. If you are considering each site a different community (which is sounds like you are), then you would calculate Shannon diversity for each site separately, without consideration of the other sites.

What I do not understand is if this relative abundance relates to all species in a sample, or all found species?

Relative abundance is not a function of the number of species found, but the total number of observations collected (similar to sampling effort) at a given site. So, if you made 100 total observations at a site and 10 of those belonged to Species X, the relative (proportional) abundance of Species X is (10 / 100) or 0.1, regardless of the total number of different species present, and regardless of any observations from other sites.
I can't comment on your code, because I don't do tidy-verse. I guess I'm (apparently) old school and still use Vegan and LabDSV for community analysis (I may be biased since I learned community ecology from the author of the LabDSV package). But in general, for any measure of alpha-diversity, each community gets it's own value, and you would not consider any observations from other sites in that calculation, regardless of whether there is species overlap between communities.
For a more direct comparison of diversity between sites, you would use a beta-diversity index like Bray-Curtis dissimilarity, which compares each community to every other community in your analysis.