Patch scan tombstones (device_patches.status='missing') accumulate unbounded

[ToddHebebrand]

Context: Split out from the compliance-count fix (PR linked below). That PR stops counting 'missing' rows as outstanding patches, but does not address the underlying data-hygiene problem: the 'missing' rows themselves keep piling up.

Mechanism: Agent scan ingestion (apps/api/src/routes/agents/patches.ts:40-43) marks all of a device's existing device_patches rows status='missing' at the start of every scan, then re-inserts the rows the current scan reports as 'pending'/'installed' (keyed on (deviceId, patchId)). Any row whose patch is not in the current scan is left at 'missing' forever. For Linux package sources the externalId is linux:<pkg>:<version> (agents/patches.ts:139-141), so every package upgrade creates a new patchId and orphans the old row → tombstones grow with churn.

Observed (US prod, 2026-05-29): pressless had 822 installed + 960 missing = 1,782 device_patches rows; the 960 missing are historical (package, version) pairs no longer current. MacBook-Pro-3: 162 installed + 306 missing.

Why it matters beyond the (now-fixed) counts:

• Unbounded row growth in device_patches per device over its lifetime.
• The 'missing' enum value is semantically a tombstone but reads like "a patch the device is missing", which is exactly what caused the compliance-count bug. The name is a foot-gun.

Options to consider:

1. On scan ingest, DELETE rows absent from the latest scan instead of marking them 'missing' (drop the tombstone concept entirely). Simplest; loses no real information since 'missing' carries none.
2. Keep tombstones but prune them (e.g. delete 'missing' rows older than N days, or older than the device's last successful scan) via the ingest path or a periodic job.
3. Rename the enum value to something honest ('stale'/'absent') to prevent future "count it as missing" mistakes — coordinate with a migration since it's a pg enum.

Acceptance: scanning a device repeatedly does not grow its device_patches row count without bound; the chosen approach is covered by a test that scans twice with a changed package set and asserts the stale rows are gone (or pruned).

Single source of truth for "outstanding" is now OUTSTANDING_DEVICE_PATCH_STATUSES in apps/api/src/db/schema/patches.ts.

[ToddHebebrand]

Design constraint discovered while prototyping the reconcile fix (the prototype was pulled from #1005 during review — the read-side count fix shipped there, this cleanup did not).

The obvious approach — on each scan, DELETE rows the scan no longer reported instead of tombstoning them — has a destructive edge that makes the naive version unsafe:

• Source buckets are coarser than providers. The agent maps several independent providers onto one device_patches.source: winget, chocolatey, homebrew all → third_party (agent/internal/heartbeat/heartbeat.go:1703-1708); apt/yum/dnf → linux; etc.
• The agent submits partial payloads on partial-provider failure. PatchManager.Scan/GetInstalled skip a failed provider (continue) and return the survivors plus a joined error; handlePatchScan (agent/internal/heartbeat/handlers_patch.go:30-39) submits as long as any items were collected.

So if a Windows box runs both winget and chocolatey and winget fails while chocolatey succeeds, the payload still carries third_party items (from choco). A reconcile that scopes its DELETE to "sources present in the payload" would then wipe winget's still-valid rows — silently, with HTTP 200, losing installedAt/failureCount/lastError history that the next scan won't restore. Scoping by the exact upserted patchId set doesn't help: winget's rows are equally "untouched" within the third_party bucket.

Implication for the fix: the API cannot determine provider-level scan authority from the current payload. Viable directions:

1. Agent sends an explicit "authoritative providers/sources" list (which providers scanned cleanly this cycle); the API only reconciles those. Most correct; needs an agent + payload-schema change.
2. Grace-period prune (API-only): only delete rows absent for ≥N consecutive scans / older than a window (e.g. lastCheckedAt older than ~24h) within scanned sources. Transient partial-provider failures self-heal inside the window; genuinely-removed packages age out. Bounds growth (the goal here) without the destructive edge, at the cost of cleanup latency. Doesn't fully solve the same-bucket case but de-risks it to "stale, not deleted".
3. Keep tombstoning but prune old 'missing' rows on a schedule (non-destructive to live state).

Option 1 is the most correct; option 2 is the cheapest safe API-only mitigation. Whatever lands needs tests for: empty/whitespace payload = no-op, a source reported with zero items, same-bucket partial-provider failure, repeat-scan idempotency, and cross-org isolation of the delete.