I don't know, what this confusion is all about. If you have two orthonormal bases ##|u_k \rangle## and ##|v_k \rangle##, then you can express each Hilbert-space vector in terms of each of the bases using completeness relations, and the components wrt. to these bases transform by an infinite matrix. To see this, let's define
$$\psi_k=\langle u_k|\psi \rangle, \quad \psi_k'=\langle v_k|\psi \rangle.$$
Now, using the completeness of the bases
$$|\psi \rangle=\sum_{k} |u_k \rangle \langle u_k |\psi \rangle=\sum_{k} |u_k \rangle \psi_k = \sum_{k} \sum_j |v_j \rangle \langle v_j|u_k \rangle \psi_k.$$
On the other hand you find in the same way
$$|\psi \rangle = \sum_j |v_j \rangle \psi_j'.$$
Since the decomposition of the vectors wrt. to the bases is unique this implies
$$\psi_j'=\sum_k U_{jk} \psi_k \quad \text{with} \quad U_{jk} = \langle v_j|u_k \rangle.$$
It is easy to show that ##U_{jk}## is indeed a unitary "matrix" by using the orthonormality of the bases, i.e.,
$$\sum_k U_{kj}^* U_{kl}=\delta_{jl}.$$
Things change a bit when you use generalized bases, i.e., generalized eigenstates of self-adjoint operators that have a continuous spectrum (or a continuous part in their spectrum). Let's take position and momentum as an example. These eigenstates are normalized to a ##\delta## distribution and instead of sums you have to use integrals:
$$\langle x|x' \rangle=\delta(x-x'), \quad \langle p|p' \rangle=\delta(p-p'),$$
and from the commutation relations ("Heisenberg algebra"), using natural units such that ##\hbar=1##,
$$[\hat{x},\hat{p}]=\mathrm{i},$$
you get
$$\langle x|p \rangle=\frac{1}{\sqrt{2 \pi}} \exp(\mathrm{i} x p).$$
Now the formalism is analogous to the case of a true orthonormal basis. The transformation from the momentum to the position representation is simply found by inserting an identity operator:
$$\psi(x)=\langle x|\psi \rangle=\int_{\mathbb{R}} \mathrm{d} p \langle x|p \rangle \langle p|\psi \rangle=\int_{\mathbb{R}} \mathrm{d} p \frac{1}{\sqrt{2 \pi}} \exp(\mathrm{i} x p) \tilde{\psi}(p),$$
where
$$\tilde{\psi}(p)=\langle p|\psi \rangle.$$
The inverse transformation follows in the same way, leading to
$$\tilde{\psi}(p)=\int_{\mathbb{R}} \mathrm{d} x \frac{1}{\sqrt{2 \pi}} \exp(-\mathrm{i} x p) \psi(x).$$
Of course, again these Fourier transformations are unitary mappings from ##\mathrm{L}^2(\mathbb{R},\mathbb{C})
\rightarrow \mathrm{L}^2(\mathbb{R},\mathbb{C})##, ##\psi \mapsto \tilde{\psi}##.
