Arch Linux

/etc/profile may be sourced by several /bin/sh-compatible shells. For Arch most of the time this will be bash, because it is the default login shell. However you can also install other shells which will use /etc/profile, for example ksh or dash.

/etc/profile and all scripts sourced by it should be POSIX-compatible (or specifically test for the executing shell) to allow this. The POSIX specification can be found at [1]. Relevant here is the "Shell & Utilities" volume.
PS1 is described in Section 2.5.3 [2]. There is no expansion for patterns like \h described there, so a PS1 definition expecting to use these should not go into /etc/profile.
Thus it is logical to put PS1='[\u@\h \W]\$ ' in /etc/bash.bashrc instead of /etc/profile. However since /etc/bash.bashrc is sourced after the files in /etc/profile.d/ this leads to the behaviour you see, since your PS1 definition is replaced by that from /etc/bash.bashrc.

This could probably be "fixed" by doing some automatic detection (only set PS1 in /etc/bash.bashrc if it still has the default value used by bash). The bashrc you linked to seems to do this:
[ "$PS1" = "\\s-\\v\\\$ " ] && PS1="[\u@\h \W]\\$ "
On the other hand, having to modify /etc/bash.bashrc if wanting to change PS1 for bash globally seems to be very reasonable and KISS.

Regarding POSIX-compatibility and your /etc/profile.d/any.sh example:
I already mentioned that \u, \h, \W might not get expanded as expected by every shell that uses /etc/profile.
Apart from that, POSIX' (and e.g. dash's) echo does not have a -e flag.
Also the script should be #!/bin/sh (if any) instead of #!/bin/bash if placed in /etc/profile.d/.
So these again are reasons for going with /etc/bash.bashrc (or a script sourced from there) for what you are doing instead of /etc/profile.d/.
Of course that might only be academical depending on which login shells are actually used on your system. For systems where everybody uses bash, it won't make a real difference, but if you use different Unixes with different login- and /bin/sh-shells you notice POSIX-compatibility and scripts having the correct shebang (#!) being a nice thing.

[1] https://pubs.opengroup.org/onlinepubs/9699919799/
[2] https://pubs.opengroup.org/onlinepubs/9699919799/utilities/V3_chap02.html#tag_18_05_03

> On the other hand, having to modify /etc/bash.bashrc if wanting to change PS1 for bash globally seems to be very reasonable and KISS.

In this case, we need to modify bashrc and then support it for any distro: for Debian, CentOS, Arch... When we just put /etc/profile.d/any.sh - we don't need to support by our IaC's the distro files - this is KISS

Yeah, I can see that this might be a bit easier in your case (although removing a line from /etc/bash.bashrc if it starts with PS1= shouldn't be hard). I don't know what the maintainers will decide about this.

Just keep in mind, that your any.sh would mangle e.g. the ksh prompt on any distro (not just Arch):
$ ksh -l
Welcome to Arch Linux 5.17.6-arch1-1

033[1;33m[u@h W]$ 033[0mps u -p $$
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
swm 297490 0.0 0.0 8952 4292 pts/6 S 16:29 0:00 ksh -l
033[1;33m[u@h W]$ 033[0m

> Yeah, I can see that this might be a bit easier in your case (although removing a line from /etc/bash.bashrc if it starts with PS1= shouldn't be hard). I don't know what the maintainers will decide about this.
It's not hard, but when we do distro changes like this - we loose future upgrades for this file (the new file from package will be .pacnew), and should maintain this file by self
What I mean is easy to explain via systemd man

>
Packages should install their configuration files in /usr/lib/ (distribution packages) or /usr/local/lib/ (local installs). Files in /etc/ are reserved for the local administrator, who may use this
logic to override the configuration files installed by vendor packages. It is recommended to prefix all filenames with a two-digit number and a dash, to simplify the ordering of the files.

If the administrator wants to disable a configuration file supplied by the vendor, the recommended way is to place a symlink to /dev/null in the configuration directory in /etc/, with the same
filename as the vendor configuration file. If the vendor configuration file is included in the initrd image, the image has to be regenerated.
>

So if my team need to modify some values in, for example, systemd unit, we create /etc/systemd/unit_name.service.d/override.conf, and this will be "overriding of unit options", not "override of whole unit"
For example, if openvpn unit in package (distro changes) changed the group - we receive this update, because we change StartLimitBurst option - nothing will break

> Just keep in mind, that your any.sh would mangle e.g. the ksh prompt on any distro (not just Arch):
Thanks, nobody who I knew don't use third-party shells on servers