When does direct image with proper support have a right adjoint?

I assume the question holds in contexts where we can glue open immersions and proper morphisms to produce $f_!$ for $f$ separated of finite type. In particular, we shall have $f_!=f_\ast$ for $f$ proper.

Non derived setting --- If $f:X\rightarrow Y$ is proper, one can ask if $f_\ast$ has a right adjoint. Note that, if $Y=\mathrm{Spec}(k)$ for an algebraically closed field, then $f_\ast$ is essentially the global section functor on $X$ (if we work with reasonnable topologies like Zariski, Nisnevich, or étale). This suggests that $f_\ast$ does not have a right adjoint in general (otherwise, it would be exact, and this would say that proper schemes have not any interesting cohomology). If $f$ is quasi-compact and quasi-separed, then $f_\ast$ preserves filtered colimits (this is the case if $f$ is proper). Hence the obstruction for $f_\ast$ to have a right adjoint is only its left exactness. There are still cases where $f_\ast$ has a right adjoint at the level of sheaves: when f is a closed immersion (for the Zariski topology), and when f is finite (for the Nisnevich topology and for the étale topology): the right exactness of $f_\ast$ is proved by contemplating the behaviour of $f_\ast$ on stalks of $Y$: for the Zariski topology, one uses that the points are the local rings, and that any quotient of a local ring is local; similarly, for the Nisnevich (resp. étale) topology, one uses the fact that the points are the henselian (resp. strictly henselian) rings, and that any finite extension of an henselian ring is still henselian. So in conclusion, it seems that we might expect $f^!$ to exists when $f$ is an immersion (for the Zariski topology), or when $f$ is quasi-finite (for the Nisnevich or étale topology).

Derived setting --- As, for an open immersion $j, j_!$ is still the extension by zero, it always has a right adjoint $j^\ast$. The problem is then again to get an adjoint of $f_!=f_\ast$ for $f$ proper. The problem is similar to the one in the non derived case, except that the obstruction is smaller: as $f_\ast$ is an exact functor between well generated triangulated categories (in the sense of Neeman; for this we have to work with unbouded complexes), $f_\ast$ has a right adjoint if and only if it preserves small direct sums (this is an instance of the Brown representability theorem). There is a nice sufficient condition for this, which I will recall. Remember that an object $X$ in a triangulated category is compact if, for any integer $n$, the functor $\mathsf{Hom}(X[n],-)$ preserves small direct sums. A triangulated category $T$ is compactly generated if it admits small sums, and if there exists a small generating family $G$ in $T$ which consists of compact objects (i.e. all the element of $G$ are compact in $T$, and for an object $M$ in $T$, one has $M=0$ iff $\mathsf{Hom}(X[n],M)=0$ for any integer $n$ and any $X$ in $G$). A compactly generated triangulated category is a basic example of a well generated triangulated category. In particular, if $F:T\rightarrow T'$ is functor between compactly generated triangulated categories, then it has a right adjoint iff it preserves small direct sums. The good news are that, for such an $F$, a sufficient condition for this is the following: assume that $F$ has a left adjoint $L:T'\rightarrow T$ and that there exists a small family $G'$ of compact generators of $T'$ such that $L$ sends the elements of $G'$ to compact objects of $T$. Then $F$ preserves small direct sums (hence, has a right adjoint).

If we come back to our functor $f_\ast$ (with $f$ proper), a sufficient condition for $f^!$ to exist is then that our derived categories of sheaves are compactly generated and that the representable sheaves form a family of compact generators: as $f^\ast$ obviously preserves representables, these assumptions give the existence of $f^!$. As for the conditions under which representables are compact or not (which is a finiteness assumption on $X$ and $Y$ themselves), some sufficient conditions have already been suggested here (the fact that representables form a generating family is obviously always true).