First off, I know that ~/ is the home directory. CDing to ~ or ~/ takes me to the home directory.

It's a Bash feature called "tilde expansion". It's a function of the shell, not the OS. You'll get different behavior with csh, for example.
To answer your question about where the information comes from: your home directory comes from the variable $HOME (no matter what you store there), while other user's homes are retrieved real-time using getpwent(). This function is usually controlled by NSS; so by default values are pulled out of /etc/passwd, though it can be configured to retrieve the information using any source desired, such as NIS, LDAP or an SQL database.
Tilde expansion is more than home directory lookup. Here's a summary:
~              $HOME
~fred          (freds home dir)

~+             $PWD       (your current working directory)
~-             $OLDPWD    (your previous directory)
~1             `dirs +1`
~2             `dirs +2`
~-1            `dirs -1`

dirs and ~1, ~-1, etc., are used in conjunction with pushd and popd.
Edited to add:
As Sean Bright pointed out in a comment, the baseline tilde behavior regarding home directories is codified as standard behavior for POSIX-compliant shells. Additionally, the wordexp() C API function is specified to implement this behavior. Though, obviously, use with caution.
