$\newcommand{\al}{\alpha}$This inequality is false, for any $\al\in(0,2)$.
Indeed, consider first the case $\al\ne1$. Suppose that $C_1(x)+C_2(x)=1$ for all $x>1$,
\begin{equation}
\ell(x)=e^{b\sqrt{\ln x}} \tag{1}\label{1}
\end{equation}
for some real $b$ and all $x>1$, and $f(t)=\ln^2 t$ for all $t>2$.
Then, reasoning as in your post, for all large enough $n$ we have
\begin{equation}
P(|X|>a_n)\asymp\frac1{nf(n)}\frac{\ell(a_n)}{\ell(n)}
=\frac1{n\ln^2 n}\frac{\ell(a_n)}{\ell(n)},
\end{equation}
$a_n=n^{1/\al+o(1)}$,
\begin{equation}
\frac{\ell(a_n)}{\ell(n)}=\exp\{b(\sqrt{1/\al}-1+o(1))\sqrt{\ln n}\}.
\end{equation}
Letting now $b=1$ if $\al\in(0,1)$ and $b=-1$ if $\al\in(1,2)$, we see that for all large enough $n$
\begin{equation}
\frac{\ell(a_n)}{\ell(n)}>\ln n
\end{equation}
and hence
\begin{equation}
\sum_n P(|X|>a_n)=\infty,
\end{equation}
whereas $\int_1^\infty \frac{dt}{t f(t)}<\infty$, so that the inequality
\begin{equation}
\sum_{n=1}^\infty P\big( |X| > a_n \big) \le \tilde C \int_1^\infty \frac{dt}{t f(t)}
\end{equation}
cannot hold for any real $\tilde C$.
In the case $\al=1$, instead of \eqref{1} similarly consider
\begin{equation}
\ell(x)=\exp\frac{\ln x}{\ln\ln x}
\end{equation}
for $x>e$.
First, we can observe that your integral depends solely on the behavior of $f$ on the interval $[0, 1]$. Its values outside that region do not affect the expression. So we may multiply by the cutoff function $\chi_{[0, 1]}$.
Thus we may consider this problem for elements of $L^2(\mathbb{R})$ with compact support.
We can look at the Hardy-Littlewood-Sobolev theorem on fractional integration (or more accurately, its proof). The argument found in Remark 2 here, partitioning into dyadic shells, shows that for $x > 0$, $$\left|\int_0^x \frac{f(y)}{|x - y|^{1/2}} \, dy \right| \leq \int_0^x \frac{|f(y)|}{|x - y|^{1/2}} \, dy \leq \int_{B(x, x)} \frac{|f(y)|}{|x - y|^{1/2}} \, dy \leq C x^{1/2} M f(x),$$ where $M f$ denotes the Hardy-Littlewood maximal function and $C$ is an absolute constant.
In your case, since we are integrating over $x \in [\epsilon, 1]$, we can bound this solely by a multiple of $M f$, and then the strong-type
Hardy-Littlewood $L^p$ estimate gives (as a very rough upper bound), $$\left\|\int_0^x f(y) |x - y|^{-1/2} \, dy \right\|_{L^2_x[0, 1]} \leq C \|M f(x)\|_{L^2_x} \leq C' \|f\|_{L^2} = C' \|f\|_{L^2[0, 1]},$$ invoking the support restriction on $f$.
So your integral is bounded above by the quantity $K \int_{0}^{1} |f(x)|^2 \, dx$, for some dimensional constant $K$. (And this also indicates that you don't need to take $\epsilon > 0$; you can directly integrate over $[0, 1]$.) You specified $f$ is locally $L^2$, so this quantity is well-defined and non-infinite.
Thus, for any $f \in L^2_{\text{loc}}$, you can guarantee that your integral will be finite.
Best Answer
As already stated by @Noam and @Alexandre in the comments above, $$ f(x)= - x \int_0^1 \mathrm d\tau \sqrt{-8\log \tau} \, J_1\big(x \tau \sqrt{-8\log \tau}\big),\tag{1} $$ with Bessel function $J_1$. Here, I did one further substitution in order to simplify the expression. The function $f(x)$ oscillates at large $x$, and asymptotically seems to become $$ f_\infty(x)= \sqrt 2 \cos(2 e^{-1/2} x) + c + O(x^{-1/2}),\tag{2} $$ with $c\approx -1.055$. The oscillation frequency stems from the oscillations of $J_1(x \tau \sqrt{-8\log \tau})$ in the integrand at stationary maximal argument (see comment by @Noam), which is at $\tau=e^{-1/2}=0.60653\ldots$. The prefactor of $x^{-1/2}$ is around $-0.285$.