dead rows accumulate in software_host_counts

[getvictor]

Fleet version:
4.76.0
Web browser and operating system:

---

💥  Actual behavior

Dogfood (and customer DBs) accumulate dead rows in software_host_counts when software is updated or removed in a team.

Running the following query on Dogfood:

  SELECT
      COUNT(*) as zero_count_team_rows,
      SUM(CASE WHEN team_id = 0 AND global_stats = 0 THEN 1 ELSE 0 END) as no_team_zeros,
      SUM(CASE WHEN team_id > 0 THEN 1 ELSE 0 END) as team_specific_zeros
  FROM software_host_counts
  WHERE hosts_count = 0;

Results in:

+--------------------+-------------+-------------------+
|zero_count_team_rows|no_team_zeros|team_specific_zeros|
+--------------------+-------------+-------------------+
|19210               |6581         |12629              |
+--------------------+-------------+-------------------+

🛠️ To fix

Proposed:
Clean up all rows with hosts_count = 0
OR
Never have rows with hosts_count=0 in that table in the first place. This will allow us to optimize queries that currently have to do an inefficient hosts_count > 0 when querying this table. For example, just like we do with vulnerabilities, instead of updating the existing table during vulnerabilities run, we can create a new software_host_counts table and swap out the old one.

🧑‍💻  Steps to reproduce

1. Run the above query on dogfood or customer DB.

🕯️ More info (optional)

One way we could be accumulating dead rows:

Scenario:

• Software X installed on Team 1 hosts → (software_id=100, team_id=1, hosts_count=5)
• Software X installed on Team 2 hosts → (software_id=100, team_id=2, hosts_count=10)
• Global count → (software_id=100, team_id=0, global_stats=1, hosts_count=15)

Then:

• Software uninstalled from all Team 1 hosts
• Sync runs → (software_id=100, team_id=1, hosts_count=0)

Cleanup runs:

1. ❌ cleanupSoftwareStmt: Does NOT delete software (global count is 15 (or 10), not 0)
2. ❌ cleanupOrphanedStmt: Does NOT delete Team 1 row (software still exists)

[mostlikelee]