According to Purge causes recorder to stop writing to the DB until HA is restarted · Issue #117263 · home-assistant/core · GitHub this issue will be resolved in 2024.8.0 and, as I understand, can be caused by custom integrations AND/OR old databases with some unsupported foreign keys in the states table.
It has been fixed, just not available for general release yet as it is a big change and needs more general testing first.
Either way, it looks best to do the suggested
recorder:
auto_purge: false
until this is released.