feat(recommendations): populate usage_history + surface per-row sparkline in rec list

[cristim]

Background

Issue #44 added GET /api/recommendations/:id/detail (internal/api/handler_recommendations.go::getRecommendationDetail). The contract includes usage_history: RecommendationUsagePoint[] — but the handler currently returns an always-empty slice with the explicit "we'll fill it later" comment:

usage_history is intentionally empty in this first pass: the collector pipeline does not yet persist time-series utilisation per recommendation. Surfacing the missing field as an empty slice (rather than a 501) keeps the drawer functional today and means the day the collector starts populating it, the frontend automatically picks it up.

The frontend already accommodates the empty case (recommendations.ts:1042-1043 falls back to a "not yet available" placeholder when usage_history.length === 0), so the day backend data lands the sparkline starts rendering automatically. But:

1. No collector populates it today. Until the collector pipeline persists time-series utilisation, the field stays empty forever, the placeholder is permanent, and the "trust the recommendation" UX value of feat(api): add GET /api/recommendations/:id/detail for the Recommendations drawer #44 is half-delivered.
2. The data is only shown in the drawer, after a row click. A per-row mini-sparkline in the rec list itself would surface the signal at the level the user actually compares recs (the table row), not behind a click.

This issue tracks closing both halves.

Part 1 — Backend: persist time-series utilisation per rec

The collector pipeline (Cost Explorer's LookbackPeriodInDays, Azure Advisor's per-rec metrics, GCP Recommender's insights) already returns lookback-window utilisation as part of generating each rec — we just don't persist it. Surface paths to investigate:

• AWS Cost Explorer (providers/aws/recommendations/parser_ri.go::parseAWSCostDetails and friends): the EstimatedSavings math implicitly uses utilisation. Whether the SDK exposes the per-instance per-day utilisation alongside the savings number needs to be checked. If not, a sibling GetReservationCoverage / GetReservationUtilization call might be the source.
• Azure Advisor: per-rec details include "Resources affected" — drilling into each resource via Azure Monitor metrics would build the time series. Heavier but doable.
• GCP Recommender: insights carry observed utilisation in their content.contentMatch blocks for compute recommendations.

Storage

A new recommendation_usage table keyed by (recommendation_id, timestamp) with cpu_pct, mem_pct columns. Populated alongside the existing recommendations table during a collection run; expired alongside the parent rec via the existing eviction-by-collected_at path.

Memory bound: a 30-day lookback × 24h hourly samples × 100 recs typical = ~72k rows/account. Sparse and small.

API change

getRecommendationDetail reads from the new table when populating RecommendationDetailResponse.usage_history. No contract change — empty slice still means "not yet available" so partial-rollout providers degrade gracefully.

Part 2 — Frontend: surface usage in the rec list by default

Today the sparkline only appears after a drawer expand. Surface it inline so users compare recs by their utilisation profile without clicking each row:

• Add a "Usage (last 30d)" column to the rec table (recommendations.ts:1147-1158) rendering a small inline SVG sparkline of the same usage_history payload — but loaded for visible recs only (lazy-load + viewport-bounded so we don't fetch detail for 200 rows at once).
• Empty usage_history → render a subtle "—" so an absent sparkline doesn't disappear into a blank cell.
• The drawer's larger sparkline stays as-is; the row-level one is a thumbnail.

Lazy-load shape

getRecommendationDetail is per-id today. Adding a batch endpoint POST /api/recommendations/usage accepting an id list and returning a {[id]: usage_history} map keeps the table-render fetch to one round-trip when the user scrolls a 50-row page into view.

If the lazy-fetch shape is contentious, a v0 that just renders the per-row sparkline ONLY for already-cached drawer-expanded recs is fine — it builds up as the user clicks around. Cheap to ship; demonstrates the feature without a new endpoint.

Acceptance

• A representative rec (one provider, one service) on a real collection run carries non-empty usage_history.
• The drawer renders the sparkline (as already wired by issue feat(api): add GET /api/recommendations/:id/detail for the Recommendations drawer #44's frontend work).
• The rec list table shows a per-row thumbnail sparkline (or "—" if data unavailable).
• A test seeds non-empty usage history, asserts both the drawer and the row render the sparkline.
• The empty-case placeholder still renders for providers that haven't been wired yet.

Sequencing

Backend (Part 1) lands first; the frontend (Part 2) is gated on at least one provider populating the field. Fine to land them as two separate PRs; this issue covers both so the work is tracked end-to-end.

If Part 1 ends up multi-provider / multi-PR, split into a per-provider tracker family at that point. For now one umbrella keeps the scope visible.

Severity / priority

p2 — closes the "trust the recommendation" loop that #44 only half-delivered. The drawer's confidence badge is meaningful only with the supporting time-series; without it, the bucket is essentially the same heuristic the pre-#44 frontend already had.

References

• Issue feat(api): add GET /api/recommendations/:id/detail for the Recommendations drawer #44 (the parent — added the endpoint with the empty-slice placeholder)
• internal/api/handler_recommendations.go:174-228 (the empty-slice comment + handler)
• internal/api/types.go::RecommendationDetailResponse (the contract)
• frontend/src/recommendations.ts:1007-1075 (drawer sparkline rendering — already wired)
• frontend/src/api/recommendations.ts:82-113 (frontend contract type + caller)
• known_issues/28_recommendations_detail_endpoint.md (the doc that captured the gap pre-feat(api): add GET /api/recommendations/:id/detail for the Recommendations drawer #44)
• Adjacent: fix(api): getRecommendationDetail 404s on hidden recs instead of returning a 'filtered-out' marker #214 (getRecommendationDetail 404s on hidden recs — same handler; whichever lands first should bypass the read-time filter for the by-id path so a hidden rec still shows its usage history with a "this rec is currently filtered" banner)