What can be said about a projective morphisms that admit decomposition theorem like smooth morphisms?

I don't know how to characterize such morphisms, which I think is your first question. However, this can certainly happen, even if $f$ is not smooth. (By the way, your comment about absence of local monodromy, and purity of limit MHS isn't correct.)

Prop (Zucker). If $Y$ is a curve, then $$R f_*\mathbb{C} = \bigoplus_i R^if_* \mathbb{C}[-i]$$

Since Zucker in section of 15 of his 1979 Annals paper proves a slightly weaker statement. Let me sketch a proof using stuff that appeared since. I can flesh it out if needed.

Sketch. Let $D\subset Y$ be the discriminant, $j:U\to Y$ the complement. By the decomposition theorem of BBDG, the object above decomposes as a sum $\bigoplus L_i$, where $L_i$ are translates of pure perverse sheaves. We can assume the $L_j$ are translates of minimal extensions. By restricting to $Y-D$ and applying Deligne (Théoremes de Lefschetz...), we can identify $L_i|_U=R^if_*\mathbb{C}|_{U}[-i]$, after reindexing. It follows that $L_i=j_*R^if_*\mathbb{C}|_{U}[-i]$ for sheaves supported on $Y$. There may be other summands supported on $D$ which need to accounted for. Use the local invariant cycle theorem to get a surjection $R^if_*\mathbb{C}\to L_i$. By purity (in the sense of Hodge modules, say) we can split this. So that we can absorb all $L_k$ with support on $D$ into some $R^if_*\mathbb{C}$

Added Comment: Regarding the latest question, I think I was too hasty in my comment. The example I had in mind doesn't satisfy all your requirements, but it may still be interesting to describe. One has a pencil of genus 2 curves degenerating to a union of 2 elliptic curves at each singular fibre. Contracting one of the elliptic curves from each pair results in singular surface mapping to a curve such that the higher direct images are constant. This is probably similar to what Ulrich Naf was suggesting. (Rmk, Oct 11/20: in fact it's different.)

Here is an example where $f$ is not smooth but $Rf_* \mathbb{C}$ behaves as if it were:

Let $X$ be a hyperelliptic surface and $f$ the natural morphism to $Y \cong\mathbb{P}^1$. All reduced fibres of $f$ are elliptic curves, but there is a nonzero number of nonreduced fibres, the number dependending on $X$.

The singular cohomology of $X$ is given by $H^0(X, \mathbb{C}) \cong H^4(X,\mathbb{C}) \cong \mathbb{C}$ and $H^1(X, \mathbb{C}) \cong H^3(X, \mathbb{C}) \cong \mathbb{C}^2$. Furthermore, the restriction map $H^1(X, \mathbb{C}) \to H^1(F, \mathbb{C})$ is an isomorphism for any fibre $F$ of $f$.

It is clear that $R^0 f_* \mathbb{C}_X \cong R^2f_* \mathbb{C}_X \cong \mathbb{C}_Y$, so let us consider $R^1f_* \mathbb{C}_X$. Since $H^1(X, \mathbb{C}) \cong \mathbb{C}^2$, we get a natural map $\mathbb{C}^2_Y \to R^1f_* \mathbb{C}$. By evaluating this on stalks and using the proper base change theorem, we see that this is an isomorphism.

Finally, since we know exactly what each sheaf $R^i f_* \mathbb{C}_X$ is, the same proof as in the case $f$ smooth can be used to show that $Rf_*\mathbb{C}_X$ decomposes as a direct sum of its (shifted) cohomology sheaves.

One may ask if a similar statement holds whenever all reduced fibres are smooth (and say $f$ is flat); I did not think about this. It would also be interesting to know if there are examples with non-smooth reduced fibres. Also, note that in the example $R^1 f_* \mathbb{Z}_X$ is not a local system.