HW-list: split hardware_status (migration + aggregations)

[alanpeixinho]

Part of #1989

Split hardware_status → hardware_build_status + hardware_test_status.

• Models + migration
• process_pending_aggregations (lab = coalesce misc.lab / misc.runtime / origin; advisory lock)
• prune_db, seed_test_data, update_db, delete_unused_hardware_status
• Aggregation unit tests

Out of scope: listing API + frontend.

Branch: feat/hardware-origin-lab-filter-migration-ingester

Must land before API/UI — old queries still use hardware_status.

[alanpeixinho]

Change of approach

Branch feat/hardware-origin-lab-filter-migration-ingester is dropped, replaced by feat/hardware-daily-aggregates.

The two-grain split stays. It now lands as new tables beside hardware_status instead of replacing it, filled by a scheduled recompute from raw instead of by the incremental path.

Same: grains keyed (build_origin, build_lab, platform, checkout_id) and (test_origin, test_lab, platform, checkout_id); lab from coalesce(misc.lab, misc.runtime, origin) for builds, coalesce(misc.runtime, origin) for tests; advisory lock; listing API and frontend out of scope.

Changed:

Proposed	Now
hardware_status split in two, old table dropped in the same migration	hardware_daily_builds + hardware_daily_tests added, hardware_status untouched and still read
Incremental counters in process_pending_aggregations, deduped via ProcessedListingItems	recompute_hardware_daily rebuilds a day from raw, no dedupe state
prune_db, seed_test_data, update_db, delete_unused_hardware_status updated	Untouched

New column checkout_day, the checkout start day in UTC, so a date range can be filtered on the aggregate alone.

Why: the original migration dropped hardware_status with no data migration, so the listing was empty until a backfill finished and rollback meant restoring a dropped table. Rewriting a day wholesale is idempotent by construction, so the ledger is unnecessary and a grain change needs no backfill. Late-arriving tests and settling statuses are absorbed by the next rebuild instead of drifting. Cost is bounded: hourly over 2 days, nightly over 60, locked per table and day, and a day producing no rows is rolled back so pruned raw cannot erase a complete aggregate.

Behaviour: one compatibles label per platform, the longest chain any lab reported. Today a platform can appear twice when labs disagree; after the read path switches it appears once.

Next: point the listing at the daily tables and expose the build lab and test lab filters (#1989), then delete hardware_status with its ingest path. Until then both systems run in parallel, the cost of keeping this migration additive.