For a vector $\bf{V}$, timelike, null and spacelike are defined
\begin{align}
\text{timelike:}&\;\;\;\;\mathbf{V}\cdot\mathbf{V} = g_{\mu\nu}V^\mu V^\nu < 0 \\
\text{null:}&\;\;\;\;\mathbf{V}\cdot\mathbf{V} = g_{\mu\nu}V^\mu V^\nu = 0 \\
\text{spacelike:}&\;\;\;\;\mathbf{V}\cdot\mathbf{V} = g_{\mu\nu}V^\mu V^\nu > 0 \\
\end{align}
using the (- + + +) metric signature. Notice these classes of curves are mutually exclusive. Every curve is either timelike, null, or spacelike (using the exclusive "or").
The OP correctly finds the components of the tangent vector to null trajectories by setting $\mathbf{V}\cdot\mathbf{V}=0$, for $t\rightarrow\infty$. They resemble the null trajectories as $t\rightarrow -\infty$. Indeed, the lightcones in the figure provided by Carroll at $t\rightarrow\pm\infty$ both have boundaries oriented at $45^\text{o}$, but "open" in different directions due to the set of timelike curves they are defined to contain (discussed below).
Consider $t\rightarrow\infty$ so the metric is approximately $ds^2=dt^2-dx^2$. Then the trajectory described by the four-velocity $\mathbf{V} = \mathbf{\partial_x}$ is timelike:
\begin{equation}
\mathbf{V}\cdot\mathbf{V} = \mathbf{\partial_x}\cdot\mathbf{\partial_x} = g_{xx} = -1 < 0\;\;\;\;(\text{timelike})
\end{equation}
So while the lightcones are defined by the same $45^\text{o}$ null curves, they must now open in the $x$-direction (as opposed to up, in the $+t$-direction) in order to contain the timelike curves. Moreover, there is no motion in the $t$-coordinate for this timelike trajectory: $V^t = \frac{dt}{ds}=0$, and as specified by Carroll, $(t, x)$ and $(t, x+1)$ are identified. So the particle "loops" back around and ends up at its starting point. Hence this is a closed timelike curve.
Furthermore, I am not sure how to intuitively justify the fact that $g_{\mu\nu}V^\mu V^\nu < 0$
implies that $V$
is timelike in a case like this.
A timelike curve is defined such that it's four-velocity, $\mathbf{V}$, satisfies $g_{\mu\nu}V^\mu V^\nu < 0$.
How do we know that these are the curves along which massive objects can move, in general? To me, it only makes sense in Minkowski space.
Recall that every event in a spacetime manifold has a neighborhood which is homeomorphic to an open subset of Minkowski space. That is, locally a curved spacetime looks like Minkowski space. If you agree massive objects move along timelike curves in Minkowski space, we can "sew" local neighborhoods of Minkowski space together to conclude massive objects have timelike trajectories in arbitrarily curved spacetimes.
EDIT: For the closed timelike trajectory, questions like "when" or "where" does this particle exist depend on the coordinate system. Using the $(t,x)$ coordinate system, the particle exists only at one time. However, the particle itself experiences non-zero proper time along its timelike trajectory:
\begin{equation}
d\tau^2 = -ds^2 = -g_{\mu\nu}dx^\mu dx^\nu = -g_{xx}(dx^1)^2 = dx^2
\end{equation}
Integrating along one loop: $x = 0 \rightarrow x=1$, we can find the proper time experienced by the particle for that segment of its trajectory.
\begin{equation}
\Delta\tau = \int_\text{one loop}d\tau = \int_0^1 dx = 1\,\text{sec.}
\end{equation}
The more important issue here is the loss of causality! By taking this loop, the particle can end up in its own proper past! These curves show up occasionally in GR, see wiki: closed timelike curves. Often such curves are contained within an event horizon, so that the spacetime is causally well-behaved (chronological censorship).