I ran the numbers for this a while ago. I live where we have proper winters (currently -22c). I wanted something simple just with solar thermal and water pumps (no heat pump). Sand batteries work at an industrial level, but for domestic use you want something simple so that means just water.

A 100m3 (100,000 litres or 26,500 gallons) cylindrical water tank (approx 5x5m) buried and insulated with 50cm of XPS could provide around 4000kWh of deliverable heat throughout winter. Which would be more than enough for heating and domestic hot water for my house.

In the summer you'd use solar thermal to charge it to 85c. In the winter you'd run water through underfloor heating and discharge it to 35c (so you just need a mixer valve and pump).

The structural engineering part of it isn't actually that complicated (with a garden on top, not a house). You can buy plastic water tanks of that size, it just needs to be buried and have XPS foam placed around it.

Because it's volume, it scales up well. An extra one meter in each direction would increase the volume by around 60%, but you have a lower overall heat loss, so the heat capacity would more than double.

The important part of it is the XPS foam though, without this the loses are too great and you don't retain any heat. This is why insulating your foundation and slab is so effective.

So…geothermal? I wish this was possible too but I don’t see how it will work scientifically. Water is one of the chemicals that have one of the highest thermal mass/specific heat (maybe 1/3 of salt hydrates). Even then, you have to bury a crapton of water underground. This design mentioned in the article is more for short term, like 12 hours storage (since they’re accommodating for solar in nighttime)

You seem to be describing ground sourced heat pumps. If you wanted, you could insulate a a chunk of foundation or earth to avoid heat loss. But just the ground under your building seems to work well enough.