Why doesn't the tilde (~) expand inside double quotes?
The reason, because inside double quotes, tilde ~ has no special meaning, it's treated as literal.
POSIX defines Double-Quotes as:
Enclosing characters in double-quotes ( "" ) shall preserve the literal value of all characters within the double-quotes, with the exception of the characters dollar sign, backquote, and backslash,
...
The application shall ensure that a double-quote is preceded by a backslash to be included within double-quotes. The parameter '@' has special meaning inside double-quotes
Except $, `, \ and @, others characters are treated as literal inside double quotes.
Tilde expansion is defined by POSIX as:
A "tilde-prefix" consists of an unquoted <tilde> character at the beginning of a word, followed by all of the characters preceding the first unquoted <slash> in the word, or all the characters in the word if there is no <slash>. In an assignment, multiple tilde-prefixes can be used: [...] following the <equals-sign> of the assignment, following any unquoted <colon>, or both. [...] If none of the characters in the tilde-prefix are quoted, the characters in the tilde-prefix following the <tilde> are treated as a possible login name from the user database. [...] If the login name is null (that is, the tilde-prefix contains only the tilde), the tilde-prefix is replaced by the value of the variable HOME. If HOME is unset, the results are unspecified. [...]
So the shortest answer is "because it's defined that way": quoting any of the characters in the prefix, including the ~, suppresses expansion.
It also defines the expansion as always resulting in a single word, so quoting would be unnecessary:
The pathname resulting from tilde expansion shall be treated as if quoted to prevent it being altered by field splitting and pathname expansion.
Where some of the path requires quoting, but the rest is a tilde prefix, you can combine tilde expansion and ordinary quoting straightforwardly:
$ cat ~/"file name with spaces"
On the broader "why": since there's no conceivable use for word-splitting ~, that should be the default behaviour, rather than requiring it to be quoted. Because there's no need to quote it, giving ~ a special meaning inside quotes would be an unnecessary complication. And, of course, historical reasons mean it couldn't be changed now even if that were desirable.
~ originates in the C-shell, long before it was added to the Korn shell and later added to the POSIX shell specification.
In the C-shell, ~ was a globbing operator (expanded by the same routine as the one expanding *.txt for instance), so like the rest of the globbing was not performed inside double quotes.