I had two Arch Linux machines in short time where pacmans database was deleted by accident (it resides under /var/lib/pacman/). Afterwards pacman doesn’t know about any installed packages, which I realized when no updates where available ;-) To verify this situation you can use pacman -Qe to list all installed pacakges.

If you lost the database, of course your system continues working but it’s hard to recover the state of the database. If you don’t have a recent backup you can check for installed and/or upgraded packages in /var/log/pacman.log

# search for ALPM messages containing installed or upgreaded and write each package name to probably_installed_pkgs.txt
grep ALPM /var/log/pacman.log | awk '/(installed|upgraded)/ {print $4}' | sort | uniq > probably_installed_pkgs.txt

# install each of these packages
for i in (cat probably_installed_pkgs.txt)
    echo "$i"
    sudo pacman --noconfirm -S --needed --overwrite "*" $i
    echo "----"
end

--noconfirm sets pacman to non-interactive
--needed prevents reinstalls, in case the pkg was already installed before as dependency
--overwrite tells pacman to ignore already existing files

This fixed most of misery. But I also had a bunch of packages installed which came from the AUR repos. To install them I wrote this second loop:

for i in (cat probably_installed_pkgs.txt)
  # check if pacman is able to find the package in the standard repos
  # otherwise install it with yay from the AUR
  pacman -Ss $i || yay --noconfirm --needed -S --overwrite '*' $i
end

Of course you can combine them into one.

In the end I save the result in a file.

# save all installed pkg names
yay -Qe > installed_pkgs.txt
# count the installed pkgs
yay -Qe | wc -l >> installed_pkgs.txt

This could be done via cronjob or aor a pacman post hook