Just managed to resolve this after spending several hours.
No need to go into all the details, but long story short, I created a new user, logged in, and it worked. After a few more attempts with the old user I managed to get some good stack traces and post a bug report in https://bugs.kde.org/show_bug.cgi?id=510925. After looking at the stack traces more carefully, I noticed these lines that point to some database error handling in the code:
#15 0x0000772f5b85d7d4 in qt_assert_x (where=where@entry=0x772f54ccf733 “Uils::exec”, what=, file=file@entry=0x772f54cd0dc0 “./src/service/plugins/sqlite/Utils.h”, line=line@entry=53) at /usr/src/qt6-base-6.9.2-0zneon+24.04+noble+release+build121/src/corelib/global/qassert.cpp:114
#16 0x0000772f54cb354e in Utils::exec(Common::Database&, Utils::ErrorHandling, QSqlQuery&) [clone .isra.0] (eh=, query=…, database=) at /usr/src/kactivitymanagerd-6.5.0-0zneon+24.04+noble+release+build42/src/service/plugins/sqlite/Utils.h:53
then I tried cd ~/.local/share/kactivitymanagerd and mv resources resources-backup. Reboot and everything works!
After this tenure, I also found a bug that has been raised 7 years back: https://bugs.kde.org/show_bug.cgi?id=389441 and I would absolutely agree with the title of this one: kactivitymanagerd does not handle DB corruption properly. That’s too big of an impact if absolutely nothing works when something goes wrong with the DB! I was so close to a re-installation with the way my system was failing (I basically reinstalled all the neon package with some dpkg/aptitude commands), but even if I did a full re-installation, I would still face the same problem as I store my home directory on a different partition so that I don’t lose my personal settings in case of a reinstallation.