perf(azure): batched SKU catalogue lookup to populate deferred Details fields without N+1 API calls

[cristim]

Summary

The four Azure recommendation converters (compute, database, cache, cosmosdb) populate the subset of Details derivable from the reservation recommendation payload only. SDK-catalogue-derived fields — compute Platform/Tenancy/Scope + memory/vCPU, database EngineVersion/AZConfig, cache Shards, cosmos APIType — are deliberately left empty because populating them inline would produce an N+1 API call per recommendation.

Captured in: known_issues/10_azure_provider.md ("New follow-ups surfaced during this resolution" → MEDIUM: Azure converters leave SKU-lookup Details fields empty (deferred N+1 avoidance)).

Current behaviour

Each convertAzure*Recommendation builds a partial Details:

• providers/azure/services/compute/client.go:703 → common.ComputeDetails{InstanceType: f.ResourceType} only.
• providers/azure/services/database/client.go:608 → common.DatabaseDetails{Engine: "sqlserver", InstanceClass: f.ResourceType} only.
• providers/azure/services/cache/client.go:582 → common.CacheDetails{...} partial.
• providers/azure/services/cosmosdb/client.go:617 → common.NoSQLDetails{Engine: "cosmos", ThroughputUnits: parsed} only; APIType missing.

Fields requiring SDK calls are deferred:

• Compute: armcompute.ResourceSKUsClient.ListByLocation for vCPU/MemoryGB/Tenancy/Scope.
• Database: armsql SKU catalogue for EngineVersion/AZConfig/Deployment.
• Cache: armredis for Shards/Tier.
• Cosmos: armcosmos.DatabaseAccountsClient for APIType (sql/mongodb/cassandra/gremlin/table).

Steps to reproduce

1. Run GetRecommendations against an Azure subscription.
2. Inspect any returned Recommendation.Details — compute MemoryGB == 0, database EngineVersion == "", cache Shards == 0, cosmos APIType == "".
3. UI rendering treats these as "unknown" and hides the corresponding columns.

Expected behaviour

Full Details population so the UI can render vCPU, memory, engine version, and Cosmos API type without hitting "unknown".

Proposed fix

Single-shot batched SKU lookup per region per service, called ONCE at the start of GetRecommendations for each region appearing in the response, cached in a map[skuName]SKUCatalogueEntry, then consulted as the converter builds each rec's Details. No N+1 storm, full Details population.

Per-service batch endpoints:

• armcompute.ResourceSKUsClient.ListByLocation(region)
• armsql.SKUsClient.ListByServer(...)
• armredis.SKUsClient.ListByLocation(region)
• armcosmos.DatabaseAccountsClient.ListByResourceGroup(rg)

Test plan

Per service: a fixture with SKU-specific properties + an assertion that the resulting Details is non-nil and has the expected concrete type + fields. Mock the catalogue lookup to return a known SKU entry; assert the converter consumed it correctly.

References

• Files: providers/azure/services/{compute,database,cache,cosmosdb}/client.go::convertAzure*Recommendation.
• Known-issue doc: known_issues/10_azure_provider.md → "New follow-ups surfaced during this resolution".

Severity

Medium — UI degrades to "unknown" for fields that exist in the SDK catalogue. Functional but loses the resolution the catalogue would provide.

Effort

Medium — each service is ~30 LOC for the batched lookup + cache + ~30 LOC for converter wiring + a fixture test.

[cristim]

Verification report from merge-watch-pr81 (retro-spawned)

• PR merged: perf(azure): batched SKU catalogue lookup eliminates N+1 in recommendation converters #81 (commit 5e3b54daacd9fdc376ff1749f96d571370698212)
• Deploy CI on feat/multicloud-web-frontend for the merge commit: ,,
• Verification: 3 of 4 Azure converters now populate previously-deferred Details fields via single-shot batched SKU catalogue lookup (no N+1). Compute scoped back because common.ComputeDetails has no fields for vCPU/MemoryGB — tracked as a follow-up issue.

Recommend closing this issue. (Not closed automatically — leaving the call to a human.)

[cristim]

Resolved by PR #81 for 3/4 Azure converters (Cache, Cosmos, Database). Compute case deliberately scoped out — Compute SKU enrichment tracked in #82. Recommend closure as completed per @cristim's 2026-04-27 verification comment. (triage agent wave1-A)

[cristim]

Verification

Original concern: The four Azure recommendation converters (compute, database, cache, cosmosdb) deferred SDK-catalogue-derived Details fields (compute Platform/Tenancy/Scope + memory/vCPU, database EngineVersion/AZConfig, cache Shards, cosmos APIType) because populating inline would cause an N+1 SDK call per recommendation. The ask was a single-shot batched SKU lookup per service per region, called once at the start of GetRecommendations and cached.

What merged: PR #81 (perf(azure): batched SKU catalogue lookup eliminates N+1 in recommendation converters, merged 2026-04-27). Adds sync.Once-gated lazy single-shot catalogue fetch to cache, cosmos, and database converters. Failed catalogue fetch leaves the cache nil and converters fall back gracefully with a one-time WARN. Compute scoped back because pkg/common/types.ComputeDetails lacked vCPU/MemoryGB struct fields at the time — those fields and the compute-catalogue follow-up landed later in PR #97 (perf(common): extend ComputeDetails with VCPU + MemoryGB, commit 3ad1ed2de on the branch).

Code state today (branch feat/multicloud-web-frontend):

• providers/azure/services/cache/client.go:79-80 — skuCacheOnce sync.Once + skuCacheMap fields; :629 cachedSKULookup reads/populates the cache; :596 converter consults it for ShardCount.
• providers/azure/services/cosmosdb/client.go:70 — apiTypeOnce sync.Once; :635 cachedAPIType resolves the dominant Cosmos APIType once per client lifetime; :600 converter consults it.
• providers/azure/services/database/client.go:75-76 — skuCacheOnce + skuCacheMap; :625 cachedSKULookup; :598 converter consults it for EngineVersion.
• providers/azure/services/compute/client.go:64 — capacityProviderOnce sync.Once (the compute catalogue cache, post-perf(common): extend ComputeDetails with VCPU + MemoryGB #97).
• Tests: _PopulatesShardsFromSKUCache, _PopulatesAPIType (5 sub-cases), _PopulatesEngineVersion, _PagerErrorFallsBack, _CapabilitiesErrorFallsBack, _CachedSKULookup_FetchedOnce / _CachedAPIType_FetchedOnce (the N+1 invariant pinned in tests), _AmbiguousAPIType (multi-API-type Cosmos returns empty rather than guessing).

Recommendation: close. Cache + cosmos + database fully addressed by PR #81; the originally-deferred compute fields landed in the follow-up PR #97. All four converters now populate Details lazily via single-shot catalogue lookups, with graceful fallback on fetch error and unit tests pinning the cached-once contract.