Using ~ in `remote-repo-cache` crashes `cabal update` and isn't recognised by `cabal build`

**Describe the bug**
Having a `cabal.project` file with `remote-repo-cache: ~/.cache/cabal/my-packages` breaks `cabal update`, with the following error:

```
cabal update
Downloading the latest package list from hackage.haskell.org
~/.cache/cabal/my-packages/hackage.haskell.org/01-index.timestamp: withFile: does not exist (No such file or directory)
```
specifying as `/home/jamie/.cache/cabal/my-packages` works just fine with `cabal update`. Having done this, switching back to `~/` instead of `/home/jamie/` produces the following for `cabal build`:

```
Error: [Cabal-7160]
The package list for 'hackage.haskell.org' does not exist. Run 'cabal update' to download it.
```

This seems to affect cabal versions from at least as far back as `3.10.1.0`, I also tested with `3.10.3.0` and `3.12.1.0` with the same issue.

Start by reproducing the issue with a cabal.project containing remote-repo-cache: ~/.cache/cabal/my-packages, then run cabal update and cabal build using the affected versions. Trace how these commands resolve the configured cache path. Done means both commands consistently recognise the home-directory shorthand and use the same package cache.

Written by the indexing model from the issue text.