You’re asking for a lot of different things that don’t align, so instead of trying to guess what you need, let me just throw a few things out there:

• if you want transcoding that isn’t constantly tied to GPU, you want an AMD chip of some sort. If you’re trying to be efficient, an APU model.
• you probably want to just merge all your data into a single disk array. Running multiple on one system is pointless and inefficient.
• separate your CPU needs from storage. Like you said, maybe you just need an ultra low power NAS, and then different machine to handle compute.
• skip SSD for any network storage. Yes it’s more power efficient, but if you’re talking bulk storage, you’re wasting resources for smaller volumes where it won’t matter if transferred over the network. SSD is good for local-only for the most part.

This is from 2023, but this might still be a good read on how to get to a very low watt idle build, there’s a few more post on other older builds on that blog as well: https://mattgadient.com/7-watts-idle-on-intel-12th-13th-gen-the-foundation-for-building-a-low-power-server-nas/

I’m looking at the future and what might be good replacement that offers a blend of power-efficiency, flexibility, and storage cost.

Any modern CPU will improve energy efficiency. AMD AM4 platform and Intel N100 are very cheap. AMD SP3 platform is also very cheap and has a ton of PCIe lanes and memory expandability for gpus, NVMe, and running lots of VMs.

For storage cost, used hdds are currently $8/TB, generic NVMe is currently $45, and used enterprise SSDs are $50/TB, decide which you want to invest in depending on your needs. Used enterprise drives will be the fastest and most durable for databases and RAID.

https://diskprices.com/

https://www.ebay.com/sch/i.html?_nkw=pm983+m.2

SSD prices are expected to decrease faster than HDD prices, and will probably overtake HDDs for value in ~5 years.

About dGPUs, Intel A310 is the best transcoding Gpu out there. Used Nvidia datacenter gpus probably have the best vram/$ and pytorch compatibility for AI

The other responses have so far talked about hardware setup, so I’m not going to do that. Instead I’m looking at your software setup: VMs can be comparatively power inefficient compared to containers, specially for always-on services that idle often.

Perhaps my recent NAS/home server build can serve as a bit of an inspiration for you:

• AMD Ryzen 8500G (8 cores, much more powerful than your two CPUs, with iGPU)
• Standard B650 mainboard, 32 GB RAM
• 2 x used 10 TB HDDs in a ZFS pool (mainboard has 4x SATA ports)
• Debian Bookworm with Docker containers for applications (containers should be more efficient than VMs).
• Average power consumption of 19W. Usually cooled passively.

I don’t think it’s more efficient to separate processing and storage so I’d only go for that if you want to play around with a cluster. I would also avoid SD cards as a root FS, as they tend to die early and catastrophically.