A much better separation is achieved in a few Linux distributions where every package is installed in a separate directory.
All the files that might be expected by others to be in certain standard locations are sym-linked to those locations, e.g. the executables to /usr/bin,/usr/sbin,/bin or /sbin, in order to appear in PATH.
In this case you no longer need any kind of database to know which files may be safely nuked to delete any package.
Moreover, in FreeBSD there is no such separation between the "base OS packages" and "3rd party packages", implemented as a difference between root and /usr. You might have misremembered /usr/local, which is indeed a place for "3rd party packages" in all UNIX-derived operating systems.
There are many "base OS packages" that are installed in /usr/bin or in /usr/sbin.
In any FreeBSD system, you can see their source files in /usr/src/usr.bin and in /usr/src/usr.sbin.
I have been using FreeBSD for a quarter of century, since FreeBSD 2.0, and there has never been such a separation between root and /usr.
The separation between /bin and /usr/bin and the other similar pairs was made only to allow /usr to be unmounted, when it is on another device than the root device, but still have in the root file system the minimal set of tools needed for diagnosing and repairing any broken file system or network connection.
In ancient FreeBSD installations it was always recommended to have a separate small root partition, e.g. of a few hundred megabytes, and some large partitions for usr and var.
This original use has become completely obsolete, because now, for diagnosing and repairing problems, it is preferable to boot from an USB stick or from the network (using a ramdisk as root file system), and then run diagnostics or repair programs without touching even the root file system unless modifying it is intentional.
In FreBSD it might still be possible to put /usr on a different partition or device and then unmount /usr, but in many Linux distributions this traditional usage is broken, because some of the programs installed in the root directories need components installed in /usr, so when /usr is unmounted they stop working.
All the files that might be expected by others to be in certain standard locations are sym-linked to those locations, e.g. the executables to /usr/bin,/usr/sbin,/bin or /sbin, in order to appear in PATH.
In this case you no longer need any kind of database to know which files may be safely nuked to delete any package.
Moreover, in FreeBSD there is no such separation between the "base OS packages" and "3rd party packages", implemented as a difference between root and /usr. You might have misremembered /usr/local, which is indeed a place for "3rd party packages" in all UNIX-derived operating systems.
There are many "base OS packages" that are installed in /usr/bin or in /usr/sbin.
In any FreeBSD system, you can see their source files in /usr/src/usr.bin and in /usr/src/usr.sbin.
I have been using FreeBSD for a quarter of century, since FreeBSD 2.0, and there has never been such a separation between root and /usr.
The separation between /bin and /usr/bin and the other similar pairs was made only to allow /usr to be unmounted, when it is on another device than the root device, but still have in the root file system the minimal set of tools needed for diagnosing and repairing any broken file system or network connection.
In ancient FreeBSD installations it was always recommended to have a separate small root partition, e.g. of a few hundred megabytes, and some large partitions for usr and var.
This original use has become completely obsolete, because now, for diagnosing and repairing problems, it is preferable to boot from an USB stick or from the network (using a ramdisk as root file system), and then run diagnostics or repair programs without touching even the root file system unless modifying it is intentional.
In FreBSD it might still be possible to put /usr on a different partition or device and then unmount /usr, but in many Linux distributions this traditional usage is broken, because some of the programs installed in the root directories need components installed in /usr, so when /usr is unmounted they stop working.