Water Holding-Tank Apparatus Converting warm water to cool water coming from the main water line that dispenses warm water to a water tank
This may not be the cheapest solution, but it's still probably cheaper than or close to the price of a cistern plus all the extra plumbing to make it work. Other posters are correct, you don't want to store potable water (unless you want to treat and manage it yourself).
Go on LabX or Ebay and look for a &quot;liquid chiller&quot; or &quot;recirculating chiller&quot;. You might be able to get similar functionality with an aquarium chiller, but you need at least several hundred watts of power. Expect to pay $500-2000.
Buy or make a liquid-liquid heat exchanger (can be made with a tube-in-tube design, countercurrent flow) to keep your potable water and your coolant loop separate. Your coolant loop needs antifreeze or antifouling agent, borax will often do the trick.
Do NOT try to do this with a minifridge. Not enough power. You could try to roll your own with a window AC unit. You can dismantle it and CAREFULLY bend the cold coil away from the main unit and dip in in a liquid bath, such as a cooler. Use a fountain pump to recirculate. You'll want to make some sort of confinement to keep the liquid from boiling off too fast.
You'll also likely still need some reservoir to &quot;save up the cold&quot;.
If you want to go from ~100F-80F (-10 C) and you have a 8L/min shower, that's 80 degree-liters (aka kilocalories) a minute, roughly 5 KW, if you had to do it in real time.
On second thought, maybe the cistern isn't a bad idea after all. Also, if the water temp at the mains is significantly cooler, it might even be worthwhile digging up the water line and insulating it, if code allows for it, or just re-running it deeper (the latter will likely cost you several grand plus going back and forth with the town)