schema: wire run_hardware_metrics to system_gpu_link and system_software

[AlbinoGeek]

Parent: #8 — Additional Performance Metrics
Depends on: #20 (system_gpu_link), #19 (system_software)

Update run_hardware_metrics to reference system_gpu_link (via compound FK) and system_software. This wires the per-run hardware measurements to the full hardware context established by the prior sub-issues.

Schema change

Add two FK references to the existing run_hardware_metrics table:

ALTER TABLE run_hardware_metrics
    ADD COLUMN system_hardware_id INT NULLABLE,
    ADD COLUMN slot_index         INT NULLABLE,
    ADD COLUMN system_software_id INT NULLABLE REFERENCES system_software,
    ADD CONSTRAINT fk_run_hardware_metrics_system_gpu_link
        FOREIGN KEY (system_hardware_id, slot_index)
        REFERENCES system_gpu_link (system_hardware_id, slot_index);

Nullable on first addition to avoid breaking existing rows; set NOT NULL once backfill is confirmed clean or if starting fresh.

Run context binding

At run time, the harness records:

• (system_hardware_id, slot_index) — identifies the system_gpu_link row active for this run (which GPU in which slot on which host)
• system_software_id — the system_software row created at startup for this run (software environment snapshot)

Both must be populated before inserting any run_hardware_metrics rows.

Query path for full hardware context

A three-way join from any run_hardware_metrics row gives complete hardware context:

SELECT r.*, sg.*, gw.*, sh.*, ss.*, it_native.description, it_actual.description
FROM run_hardware_metrics r
JOIN system_gpu_link sg ON (r.system_hardware_id, r.slot_index) = (sg.system_hardware_id, sg.slot_index)
JOIN gpu_hardware gw ON sg.gpu_hardware_id = gw.gpu_hardware_id
JOIN system_hardware sh ON sg.system_hardware_id = sh.system_hardware_id
JOIN system_software ss ON r.system_software_id = ss.system_software_id
LEFT JOIN interface_type it_native ON sg.slot_native_interface_type_id = it_native.interface_type_id
LEFT JOIN interface_type it_actual ON sg.actual_interface_type_id = it_actual.interface_type_id;

Multi-GPU future compatibility

This design is forward-compatible with a future run_gpu_usage join table for multi-GPU runs. When that is scoped, run_gpu_usage will reference (system_hardware_id, slot_index) per GPU used per run; run_hardware_metrics will reference run_gpu_usage.run_id instead of carrying the compound FK directly. No schema changes to the other tables required.

Acceptance criteria

• Migration adds system_hardware_id, slot_index, and system_software_id to run_hardware_metrics
• FK constraint to system_gpu_link is enforced
• Harness populates both FKs on every run record insert
• Existing test suite passes (no regression from FK addition)
• Migration is reversible

— Bastion