Orphaned routes accumulating in the routes table

[vsychov]

Bug description

Hello,

I'm using headscale with autoApprovers ACL and dynamic Tailscale subnet routers which frequently change their advertised routes. To handle traffic redirection to certain domain names with dynamic DNS, I've set up a script that monitors DNS changes and restarts Tailscale, adjusting the advertise-routes flag according to the resolved DNS. Additionally, the nodes running this setup are ephemeral and periodically replaced. This setup mostly works fine, except for a single issue I've discovered.

The routes table in the database seems to be filling up with entries for routes that are no longer relevant and have no association with any machines:

SELECT
  count(*)
FROM
  routes
  LEFT JOIN machines ON routes.machine_id = machines.ID 
WHERE
  machines.ID IS NULL

This returns approximately 20k entries that are no longer needed.

I believe this to be a bug, as I would expect the routes table to be cleaned up at some point (perhaps when a node is deleted), or when the updated_at field for a route is more than 24 hours old, and both advertised and enabled fields are set to false.

At present, I'm handling the table overflow issue with the following SQL:

DELETE 
FROM
  routes 
WHERE
  "id" IN ( SELECT routes."id" FROM routes LEFT JOIN machines ON routes.machine_id = machines."id" WHERE machines."id" IS NULL ) 
  OR ( updated_at <= ( NOW( ) - INTERVAL '1 DAY' ) AND advertised = 'f' AND enabled = 'f' ) 

Might it be beneficial to add a goroutine to handle this?
If this change is approved, I would be happy to create a pull request implementing the necessary adjustments.

Thank you for your attention to this matter.

Best regards.

[github-actions]

This issue is stale because it has been open for 90 days with no activity.

[github-actions]

This issue is stale because it has been open for 90 days with no activity.

[github-actions]

This issue was closed because it has been inactive for 14 days since being marked as stale.