Periodically clean up host_script_results and host_software_installs tables #49933

Goal

Problem

Issue 1: These tables can grow large, up to multiple GBs, mostly contain historical data and are never cleaned up. See: https://fleetdm.slack.com/archives/C062D0THVV1/p1783680253552969 (host_script_results ~6GB)

Issue 2: The size of these tables can cause unexpected performance issues. See this incident where a migration took >10 minutes to make changes to a single software title: https://fleetdm.slack.com/archives/C086V2QK76X/p1784912236868139?thread_ts=1784837456.043039&cid=C086V2QK76X

Changes

Periodically clean up historical (non-pending) entries from host_script_results and host_software_installs that are older than 30 days (or more). The deletion runs should probably be limited in size. Activities are kept for historical data for script runs/software installs, it should be fine to clean up these tables.

host_vpp_software_installs and host_in_house_software_installs are outside the scope of this ticket.

Cleanup:

• host_script_results:

  • Must keep pending scripts (exit_code IS NULL AND canceled = 0), and not delete those rows
  • Must keep rows with references to their execution_id in host_mdm_actions.lock_ref, host_mdm_actions.wipe_ref, host_mdm_actions.unlock_ref and not delete those rows (no FK).
  • Should keep rows referenced in setup_experience_status_results.script_execution_id.
  • Should keep rows referenced in host_software_installs.execution_id (uninstalls only, installs don't use the host_script_results table)
  • Could keep rows referenced in batch_activity_host_results, probably fine to delete.

• host_software_installs

  • Must keep rows that are pending (COALESCE(status,'') is pending_install or pending_uninstall).
  • Must keep rows that are the last_software_install or last_software_uninstall (which is used to determine software install status). See these queries:

    • fleet/server/datastore/mysql/software.go

      Line 3641 in 09fea47

      last_software_install AS (

    • fleet/server/datastore/mysql/software.go

      Line 3739 in 09fea47

      last_software_uninstall AS (

There is no need to check against script_upcoming_activities or software_install_upcoming_activities

What data gets lost:

• host_script_results: full script results output in past_activity, batch_activity_host_results tables
• host_software_installs: full output in install/uninstall results endpoint,

Product

• UI changes: None
• CLI (fleetctl) usage changes: None, uses the existing cleanups_then_aggregation job
• YAML changes: None
• REST API changes: None
• Fleet's agent (fleetd) changes: None
• Fleet server configuration changes: Two new options, both defaulting to 30 days, 0 disables. server.script_results_retention and server.software_install_results_retention.
• Exposed, public API endpoint changes: None
• fleetdm.com changes: None
• GitOps mode UI changes: None
• GitOps generation changes: None
• Activity changes: None
• Permissions changes: None
• Changes to paid features or tiers: None
• My device and fleetdm.com/better changes: None
• Usage statistics: None
• Other reference documentation changes: Both options need entries in docs/Configuration/fleet-server-configuration.md. Not yet written.
• First draft of test plan added
• Once shipped, requester has been notified
• Once shipped, dogfooding issue has been filed

Engineering

• Test plan is finalized
• Contributor API changes: None
• Feature guide changes: None
• Database schema migrations: Two. 20260901200004 indexes host_script_results (created_at) and batch_activity_host_results (host_execution_id). 20260901200005 indexes host_software_installs (created_at).
• Load testing: host_script_results at ~500K rows, and several software titles at ~400k rows each in host_software_installs. host_vpp_software_installs and host_in_house_software_installs are out of scope. A customer instance in GitOps apply fails on large instances: fleetctl 45s response-header timeout aborts POST /api/latest/fleet/scripts/batch #54047 had 20.7M rows in host_script_results.
• Pre-QA load test: Not performed. The sweep caps at 100k rows per tick, but the delete predicates join five and two tables respectively and haven't been measured at scale.
• Load testing/osquery-perf improvements: None

ℹ️  Please read this issue carefully and understand it. Pay special attention to UI wireframes, especially "dev notes".

Risk assessment

• Requires testing in a hosted environment: No
• Requires load testing: Yes. The delete predicates join several tables per row.
• Risk level: High
• Risk description: The sweep deletes customer data on a schedule, on by default at 30 days. A wrong preservation predicate loses data silently. Watch pending runs, scripts backing a lock, unlock, or wipe, setup experience records, and the most recent install and uninstall per host and package.

Test plan

Make sure to go through the list and consider all events that might be related to this story, so we catch edge cases earlier.

1. Create a database with historical records in host_script_results and host_software_installs.
2. Verify a pending host lock still works and completes after the cleanup.
3. Verify a pending FMA or custom package install completes after the cleanup.
4. Verify a setup experience script result and software install survive the cleanup.
5. Verify the most recent install and uninstall per host and package survive, and that host software status still renders.
6. Verify a batch script run still has correct counts after the cleanup, but output from deleted scripts is not visible.
7. Verify activities for all of the above are still visible.

Core flow

• TODO

Edge cases

Supplemental testing

Testing notes

Confirmation

1. Engineer: Added comment to user story confirming successful completion of test plan (include any special setup, test data, or configuration used during development/testing if applicable).
2. QA: Added comment to user story confirming successful completion of test plan.
3. QA: Determined whether this story needs Playwright automation.
   • Needs automation: Yes / No
   • If yes, filed a follow-up issue in the :help-qa project with status "Needs automation":