This is an anwer to your last paragraph about L-trivial maps in other geometric categories. If you are only interested in schemes it doesn't tell you anything interesting.

One thing that the cotangent complex is good at is measuring connectivity of a morphism of simplicial rings. This also holds without any finiteness assumptions on the ring.

Recall that a morphism $f \colon A \to B$ of simplicial rings is called n-connective if it induces isomorphisms $\pi_i (A) \to \pi_i (B)$ in degrees $< n$ and a surjection $\pi_n (A) \to \pi_n (B)$ in degree $n$. There then is a result that states that if $f$ is $n$-connective, then the homology of the relative contangent complex $L_{B/A}$ vanishes in degrees $\leq n$. (I hope I got all the indices right.) So in particular, any equivalence of simplicial rings is L-trivial.

One way of intepreting your question is to ask when the converse holds. What do I know if a morphism of simiplicial rings is L-trivial? There is a partial converse to the statement above. Namely, if a morphism $f \colon A \to B$ induces an isomorphism $\pi_0(A) \to \pi_0(B)$ and is L-trivial, then it is an equivalence! I find this pretty suprprising, as L is only a linear piece of data, but still manages to detect equivalences.