Policy-queued installs/scripts aren't removed when the associated policy is deleted

[iansltx]

Fleet version: 4.59.0

---

💥  Actual behavior

See below:

🧑‍💻  Steps to reproduce

Theoretical, hence the reproduce tag.

1. Create a policy with a software or script automation
2. Fail a host for that policy to queue up an install or script execution
3. Delete the policy
4. Note that the queued install or script run isn't deleted

🕯️ More info

This seems to be a bit of an edge case on timing, but we hit it in Dogfood for multiple hosts, causing installs to be reported with no associated policy or actor.

@noahtalerman @rachaelshaw I'm guessing the desired behavior is that if a policy is deleted any pending script/software automations for that policy should be removed, similar to how we dequeue scripts when they are edited/deleted, or dequeue installs when a software package is edited. Does that sound right?

Split from #22424 (/cc @allenhouchins @marko-lisica)

🛠️ To fix

Before deleting a policy, either via GitOps or via the API, delete any pending installs or script runs associated with the policy ID (which was added as a column on script runs/software installs in 4.58/4.59). The order of operations is important here because the foreign key will set to null once the underlying script or installer gets deleted.

[jmwatts]

QA Notes
Confirmed fix:
Created a policy with a software and script automation
Ran policy on host (Failed) which queued up a software install and script execution
Deleted the policy
Queued software install and script run were both cleared.