I live in Tucson Arizona (in the Sonoran Desert) almost all year round where the temperature high ranges are 98°F up to 108°F from May to August. Our PVC pipe water mainline is buried 2ft underground from the main street to the house water-meter (the main water line is approximately 75 feet in length). During the high noon, the sun heats the underground water line pipe. Therefore the problem is that the tap water temperature comes out of the showerhead and bathroom faucet at a 105°F to a warm-cool 90°F! The question to the plumber or the engineer: Are there any devices that have been developed or marketed to convert warm water of 105°F to a warm-cool water temperature between 89°F - 77°F from an electric water-holding cooling tank that can produce approximately 22 gallons of warm-cool water. Which can be connected to a water demand line. So I can take approximately a 10-minute cool shower?
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 "liquid chiller" or "recirculating chiller". 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 "save up the cold".
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)
What do you need is thermal storage.
As it happens, water is the densest thermal storage medium known to man. You need large water tanks, and the cheapest way to get those is IBC totes.
At this point, you are building a solar heat system that works in reverse. By day, you run a water-water heat exchanger between the supply water from the utility and the water in your tanks. You will need to pump this tank water.
In such desert environments you typically have cool nights, so at night, you pump the tank water up to a water-air heat exchanger, to cool it back down. Or else your water tanks will just keep getting warmer and warmer.
You could also have an actual cooling pond or tower with spargers (sprayers), and exploit the low humidity to get evaporative cooling, but I would not return pond/sparger water to the tank, as it will bring all manner of crud with it and clog your pumps. I would bring tank water through another heat exchanger, which could be as simple as some coils of aluminum tubing in the bottom of the pond. Add some sensors so the cooling loop only runs when pond water is cooler than tank water.
I’m not overly concerned with where to put the water storage. Thermal interchange via the skin of the tank won't be large enough to matter, and you could always wrap it in insulation if you had to.
Do not store potable water.
Your idea of having a tank water cooler akin to a tank water heater is not a good idea. For the same reason tank water heaters themselves are not the greatest idea.
Legionella and other bacteria will grow in the water, in certain temperature ranges from 70 to 130 F. That is why water heaters must be cranked up to 140F once anti-scald valves have been fitted. The city water coming into a hypothetical cool-off tank, is the ideal temperature range for those bacteria to grow.
For that same reason, do not mix the storage tank water with the potable water from the street. In fact, you would be able to add antifreeze to the storage tank water if needed. (But it helps to buy propylene glycol instead of ethylene, because one is a foodstuff and the other is deadly poison, much like methanol vs ethanol.)
They do make such a device. Water chillers there are several different types some use heat exchangers they have a bath of cold water / glycol that is chilled the water lines are in the cold bath a basic Refridgeration unit similar in size to the compressor on your Refrigerator cool the bath. These are somewhat common in industrial equipment that needs to be cooled There are other types that have the heat exchanger directly flowing Freon into a jacket with tubes flowing water / oil , similar principal the Freon takes the heat away this method is not as stable as the bath type but they are much smaller. No product recommendations but the 2 types I have worked on that could do what you want.
Since I am from Pennsylvania and I do not have that problem I have to go to my imagination place to come up with a solution. Since I am a tinkerer I would probably buy a small refrigerator and install a large coil of tubing in it to produce the cold water you want to have. I know of nothing that is made for what you are looking to do so you would have to "invent" a solution. Another "way out" solution may be to dig a large "hole in the ground" at least 6 feet deep and bury a long coil of 3/4" water line. At that depth, the ground temp should be around 55F degrees. A third way would be to fashion a swamp cooler a device used in your area to produce the wanted cooler water. Last, you could do as @FreeMan suggested and install an underground cistern. These 4 ideas may sound like B.S. but you may need to imagine your way to a solution.