Here's what's really going on. In classical field theory, a basic set of objects that we often consider are scalar fields $\phi:M\to \mathbb R$ where $M$ is a manifold. Now we can ask ourselves the following question:
Is there some natural notion of how a scalar field defined on a given manifold "transforms" under a coordinate transformation?
I claim that the answer is yes, and I'll attempt to justify my claim both mathematically, and physically. The bottom line is that we ultimately have to define the way in which fields transform under certain types of transformations, but any old definition will not necessarily be useful in math or physics, so we must make well-motivated definitions and then show that they are useful for modeling physical systems.
Mathematical perspective. (manifolds and coordinate charts)
Recall that a coordinate system (aka coordinate chart) on an $n$-dimensional manfiold $M$ is a (sufficiently smooth) mapping $\psi:U\to \mathbb R^n$ where $U$ is some open subset of $M$. We can use such a coordinate system to define a coordinate representation $\phi_\psi$ of the scalar field $\phi$ as
\begin{align}
\phi_\psi = \phi\circ\psi^{-1}:V\to\mathbb R
\end{align}
where $V$ is the image of $U$ under $\psi$. Now let two coordinate systems $\psi:U_1\to \mathbb R^n$ and $\psi_2:U_2\to\mathbb R^n$ be given such that $U_1\cap U_2\neq \emptyset$. The coordinate representation of $\phi$ in these two coordinate systems is $\phi_1 = \phi\circ \psi_1^{-1}$ and $\phi_2 = \phi\circ \psi_2^{-1}$.
Now consider a point $x\in U_1\cap U_2$, then $x$ is mapped to some point $x_1\in \mathbb R^n$ under $\psi_1$ and to some point $x_2\in \mathbb R^n$ under $\psi_2$. We can therefore write
\begin{align}
\phi(x) &= \phi \circ \psi_1^{-1} \circ \psi_1(x) = \phi_1(x_1) \\
\phi(x) &= \phi \circ \psi_2^{-1} \circ \psi_2(x) = \phi_2(x_2)
\end{align}
so that
\begin{align}
\phi_1(x_1) = \phi_2(x_2)
\end{align}
In other words, the value of the coordinate representation $\phi_1$ evaluated at the coordinate representation $x_1 = \psi_1(x)$ of the point $x$ agrees with the value of the coordinate representation $\phi_2$ evaluated at the coordinate representation $x_2 = \psi_2(x)$ of the same point $x$. This is one way of understanding what it means for a scalar field to be "invariant" under a change of coordinates.
If, in particular, the manifold $M$ we are considering is $\mathbb R^{3,1} = (\mathbb R^4, \eta)$, namely four-dimensional Minkowski space, then we could consider the following two coordinate systems:
\begin{align}
\psi_1(x) &= x \\
\psi_2(x) &= \Lambda x+a
\end{align}
where $\Lambda$ is a Lorentz transformation and $a\in \mathbb R^4$, then the coordinate representations $\phi_1$ and $\phi_2$ of $\phi$ are, as noted above, related by
\begin{align}
\phi_1(x) = \phi_2(\Lambda x + a)
\end{align}
If we switch notation a bit and write $\phi_1 = \phi$ and $\phi_2 = \phi'$, then this reads
\begin{align}
\phi'(\Lambda x +a) = \phi(x)
\end{align}
which is the standard expression you'll see in field theory texts.
Physical perspective.
Here's a lower-dimensional analogy. Imagine a temperature field $T:\mathbb R^2 \to \mathbb R$ on the plane that assigns a real number that we interpret as the temperature at each point on some two-dimensional surface. Suppose that this temperature field is generated by some apparatus under the surface, and suppose that we translate the apparatus by a vector $\vec a$. We could now ask ourselves:
What will the temperature field produced by the translated apparatus look like? Well, each point in the temperature distribution will be translated by the amount $\vec a$. So, for example, if the point $\vec x_0$ has temperature $T(\vec x_0) = 113^\circ\,\mathrm K$, then after the apparatus is translated, the point $\vec x_0 + \vec a$ will have the same temperature $113^\circ\,\mathrm K$ as the point $\vec x_0$ before the apparatus was translated. The mathematical way of writing this is that if $T'$ denotes the translated temperature field, then $T'$ is related to $T$ by
\begin{align}
T'(\vec x+\vec a) = T(\vec x)
\end{align}
The a similar argument could be made for a scalar field on Minkowski space, but instead of simply translating some temperature apparatus, we could imagine boosting or translating something producing some Lorentz scalar field, and we would be motivated to define the transformation law of a scalar field under Poincare transformation as
\begin{align}
\phi'(\Lambda x+a) = \phi(x)
\end{align}
Since you are going into GR I believe some elaboration might be good. In General Relativity spacetime is a smooth four dimensional Lorentzian manifold $(M,g)$.
Being a manifold means that $M$ is one Hausdorff topological space (don't mind too much about this by now) and that there is a collection of pairs $\mathcal{A}=\{(x_\lambda,U_\lambda)\}$ where $U_\lambda\subset M$ is open, $M$ is the union of all the $U_\lambda$ and $x_\lambda : U_\lambda \to \mathbb{R}^4$ is a coordinate system: i.e., it assigns to each point $p\in U_\lambda\subset M$ the coordinates
$$x_\lambda(p)=(x_\lambda^0(p),x_\lambda^1(p),x_\lambda^2(p),x_\lambda^3(p)).$$
There is the additional condition that given two coordinate systems $(x,U)$ and $(y,V)$ on $M$ if their domains overlap $U\cap V \neq \emptyset$ then $T_{y,x}=x\circ y^{-1}$ and $T_{x,y}=y\circ x^{-1}$ are both $C^\infty$ functions on $\mathbb{R}^4$. These are respectively the map that transforms $y$ coordinates in $x$ coordinates and vice versa.
In summary: being a topological space gives the notion of continuity and open sets, and the existence of these functions with the overlap condition, ensures you have local coordinates which are smoothly put together to make up the whole spacetime.
So in GR we have a clear separation between the points and their coordinates. The points lie in $M$, the coordinates lie in $\mathbb{R}^4$ and what brings them together are the coordinate systems as defined above.
Now, a real scalar field is a $C^\infty$ function $\phi : M\to \mathbb{R}$. Nothing more to say about it.
How can it be? Where the transformation law is gone? Well, I haven't introduced coordinates above. I've defined the object intrinsicaly to spacetime $M$.
Now let $p\in M$ and a coordinate system $(x,U)$ around $p$ be given, namely $p\in U$. Then, we can localy, on $U$, resolve $\phi$ in coordinates. Since $x : U\to \mathbb{R}^4$ we have that $x^{-1} : \mathbb{R}^4\to U$ takes coordinates to the corresponding point.
The composite function $\phi_{(x)}=\phi \circ x^{-1} : \mathbb{R}^4\to \mathbb{R}$ is the coordinate expression of $\phi$. Notice that it takes some coordinate values and gives $\phi$ on the point corresponding to them.
Now the issue with coordinate systems is that they are not unique nor there is any prefered one. Imagine I pick another coordinate system $(y,V)$ with $p\in U\cap V$. Then we can also get the coordinate expression $\phi_{(y)}=\phi\circ y^{-1}$.
Now focus on the overlap. You shall notice that the following happens:
$$\phi_{(y)}=\phi\circ y^{-1}=(\phi\circ x^{-1})\circ x \circ y^{-1}=\phi_{(x)}\circ (x\circ y^{-1})=\phi_{(y)}\circ T_{y,x}$$
Obviously $T_{x,y} = (T_{y,x})^{-1}$ Thus you finaly conclude that
$$\phi_{(y)}\circ T_{x,y} = \phi_{(x)}$$
This is precisely what you wrote, take a time to understand that.
When $\phi : M\to \mathbb{R}$ is already defined as a function on $M$ then this condition is satisfied as a result.
But generaly you want to define it from coordinates, because it is easier and more intuitive. Then this becomes a compatibility condition to ensure that the object you are defining is actually a well defined function on $M$, i.e., it doesn't depend on the coordinates themselves.
Best Answer
The difference between a coordinate transformation and a "frame change" is the following.
If you think of the rotation as a coordinate transformation, a diffeomorphism, you are rotating both the frame ($e^a \to M^a_{\ \ b} e^b$) and the vectors ($X^a \to M^a_{\ \ b} X^b$), so that the projection of the rotated vector on the rotated frame $X^a = \langle e^a, X \rangle$ is invariant, as you mentionned.
However, if you only rotate the frame ($e^a \to M^a_{\ \ b} e^b$, $X^a \to X^a$), you are only rotating the coordinate system, so that the projection of the initial vector on the rotated frame is behaving like an $O(n)$ vector. Equivalently, you can fix the frame and only rotate the vectors ($e^a \to e^a$, $X^a \to M^a_{\ \ b} X^b$).
How to relate this to physical theories ? Let us consider a physical differential equation $$ D(x) \phi(x) = 0 $$ where $D(x)$ is a differential operator written in $x$ coordinates. When we claim that the theory is invariant under rotations, we mean that if $\phi(x)$ is a solution, the rotated solution $\phi(M x)$ is also a solution in the same coordinate system, of the same differential operator $D(x)$ $$ \text{Rotationally invariant theory : } D(x) \phi(x) = 0 \implies D(x) \phi(M x) = 0 $$
This is the concrete "rotational invariance", rotating the solution without changing the coordinate system is also a solution of physics. Note that we could equivalently check $D(x) = D(M x)$. This is the standard verification that the differential operator is invariant under rotation.
This is to be contrasted to diffeomorphism invariance : $$ \text{Diffeomorphic invariant theory : } D(x) \phi(x) = 0 \implies D(M x) \phi(M x) = 0 $$ This is satisfied by design, because differential equations are invariant under coordinate reparametrization $x = M x'$. I think this post may be useful for details on diffeomorphism invariance, Diffeomorphism Invariance of General Relativity