The answer is motivic stable homotopy theory! This assigns to any reasonable scheme $S$ a triangulated category $SH(S)$ whose objects represent generalized cohomology theories for $S$-schemes, and there is also a similar category $DM(S)$ whose objects represent ordinary cohomology theories.
These theories do almost everything you ask for, in particular they give a nice framework for cohomology and homology including Borel-Moore and compact support versions.
I could say a lot about this, but I have to go to sleep. Let me just point what is probably the best reference, namely the introduction to this preprint of Déglise and Cisinski:
http://www.math.univ-toulouse.fr/~dcisinsk/DM.pdf
The status of the following answer is a bit speculative, unfortunately. To be precise, I believe that all of what I say below is true; I also believe that there is no proof in the literature of some of the things I state below, and I am not enough of an expert to supply those proofs. Nevertheless I'm putting it out there in the hope that it can be helpful.
First of all, when dealing with unbounded complexes of sheaves one runs into the issue of hypercompleteness, and I have to say something about this. Let's first do the purely psychological change that instead of complexes of sheaves, we think of sheaves of complexes. Chain complexes are most naturally thought of as an $\infty$-category, once we localize at quasi-isomorphisms, so one is then led to thinking about sheaves valued in an $\infty$-category. Now a sheaf on a space $X$ in a complete 1-category $C$ is is a functor $F \colon \mathrm{Op}(X)^{op} \to C$ such that if $\{U_i \to U\}$ is an open cover of a subset $U$, then $F(U)$ is the equalizer (limit) of the two arrows $\prod_i F(U_i) \to \prod_{i,j} F(U_i \cap U_j)$. If $C$ is an $\infty$-category then the limit must be a homotopy limit, taking higher coherences into account, and then the proper definition of a $C$-valued sheaf turns out to be that the natural map from $F(U)$ to the (homotopy) limit of the cosimplicial diagram which in level $n$ is given by $\prod_{i_1,\ldots,i_n} F(U_{i_1} \cap \ldots \cap U_{i_n})$, is an isomorphism. This specializes to the usual $1$-categorical sheaf axiom when $C$ is a 1-category.
When we take $C$ to be the $\infty$-category of bounded below cochain complexes modulo quasi-isomorphism, then the $\infty$-category of $C$-valued sheaves on $X$ is an $\infty$-categorical enhancement of the derived category $D^+(X)$. But if we consider instead unbounded complexes, the analogous statement is false. Namely, the $\infty$-categorical sheaf axiom from the previous paragraph describes what's known as descent for Cech covers. To recover the classical unbounded derived category one must instead impose descent for hypercovers. In a sense this has been known since the 60's, and traditionally this has been interpreted as meaning that Cech descent produces the "wrong" answer, and hypercovers "correct" this deficiency. After Lurie, a more modern perspective is that Cech descent is for many purposes more natural. In any case, the upshot is that for a space $X$ there are two typically inequivalent notions one can consider: $\infty$-sheaves on $X$ valued in unbounded complexes of sheaves, and hypersheaves on $X$ valued in unbounded complexes. The latter category can be recovered from the former via the process of hypercompletion, and the latter produces an $\infty$-categorical enhancement of the classical unbounded derived category.
Now you mention the condition $(\ast)$ in Spaltenstein's paper, which looks like it is just an annoying technicality, and whether it can be removed using more modern homotopical machinery. I believe instead that the result is just plain false without some condition like $(\ast)$. More specifically I think that if a space $X$ satisfies condition $(\ast)$ then this forces Cech descent and hyperdescent to coincide for abelian sheaves, and that this is fundamentally the reason that $(\ast)$ appears in Spaltenstein's paper. Namely, Spaltenstein's Theorem B concerns the classical unbounded derived category, and I believe that all of these results fail in general when one works with hypersheaves. But a very general version of Spaltenstein's Theorem B should hold if one works with $\infty$-sheaves throughout, with no condition like $(\ast)$ or finiteness.
Here's one reason to believe this. Part of Spaltenstein's Theorem B is proper base change. In Higher Topos Theory, Lurie proves a very general nonabelian version of proper base change, which implies the classical one. Crucially, Lurie's version of proper base change is a theorem for $\infty$-sheaves, not hypersheaves (and he gives an example where proper base change fails for hypersheaves). In particular it implies a form of proper base change for $\infty$-sheaves of unbounded complexes on a space $X$, and not for hypersheaves. Now I should add that in Higher Topos Theory Lurie only considers proper morphisms, so he works in the setting where $f_! = f_\ast$. So he does not introduce the functors $f_!$ or $f^!$ to state his result.
Another indication that $(\ast)$ is actually about hypercompleteness is that Spaltenstein remarks that locally finite dimensional spaces satisfy $(\ast)$. But locally finite dimensional things should also be hypercomplete. More precisely, Lurie proves this statement in HTT, if "dimension" is interpreted as "homotopy dimension", a notion that he introduces. For paracompact topological spaces, "homotopy dimension" coincides with "covering dimension". Spaltenstein doesn't elaborate on what notion of dimension he's thinking of but I assume cohomological dimension.
In any case, it is true that higher category theory can be used to give constructions of $f_!$ and $f^!$, under milder hypotheses than what Spaltenstein uses. Namely, $f_!$ and $f^!$ should exist for any continuous map between locally compact Hausdorff spaces, for $\infty$-sheaves valued in any complete and cocomplete stable $\infty$-category. Unlike Lurie's proper base theorem which is fully nonabelian (ie works for sheaves of spaces), this part of the story uses stability in a crucial way. In Higher Topos Theory, Lurie proves that on a locally compact Hausdorff space $X$, $\infty$-sheaves on $X$ can be described equivalently in terms of functors taking values on open subsets of $X$, or taking values on compact subsets of $X$. If the target category is moreover stable then this can be used to construct an equivalence of $\infty$-categories between sheaves and cosheaves on $X$. There is a natural pushforward operation on cosheaves, much like the pushforward of sheaves. Translating the pushforward functor via the sheaf-cosheaf equivalence to an operation on sheaves, one recovers precisely the functor $f_!$. By the adjoint functor theorem one directly gets $f^!$, too. This is sketched in https://www.math.ias.edu/~lurie/282ynotes/LectureXXI-Verdier.pdf
In your second question it sort of sounds like you are asking about whether versions of the functors $f_!$ and $f^!$ can be useful in coherent cohomology (and you are not just asking about Grothendieck duality). Something like this is true in the setting of condensed mathematics. One version of six-functors formalism is in the final chapter of Scholze's "Condensed mathematics", but even closer to what you're looking for is I think the material from Dustin Clausen's final lecture in the Copenhagen Masterclass (you can find it on Youtube), which I will not attempt to summarize.
Best Answer
Your description of the six functors does not mention any relations between the $!$-functors and the $*$-functors or the tensor product, which is where these dualities are hiding.
Poincaré duality is a relation between the $*$-functors and the $!$-functors. Typically there are canonical isomorphisms $f_!=f_*$ when $f$ is proper and $f^!=f^*[d]$ when $f$ is nice (e.g. smooth) of relative dimension $d$. Depending on how the $!$-functors are constructed, one of these isomorphisms will hold by definition but the other one will be nontrivial to prove. When $f:X\to S$ is nice, we get isomorphisms (where $1_S$ is the unit object in $D(S)$) $$ H^n(X,M) := \mathrm{hom}(1_S, f_*f^*(M)[n]) = \mathrm{hom}(1_S,f_*f^!(M)[n-d])=:H_{d-n}^\mathrm{BM}(X,M) $$ and $$ H^n_c(X,M):= \mathrm{hom}(1_S, f_!f^*(M)[n]) = \mathrm{hom}(1_S,f_!f^!(M)[n-d])=:H_{d-n}(X,M), $$ On the other hand, there are vertical identifications when $f$ is proper.
Serre duality uses a relation between the $!$-functors and the tensor product. Namely, for $f:X\to S$, the functor $f_!$ is $D(S)$-linear; in particular we have the projection formula $f_!(A\otimes f^*(B))= f_!(A)\otimes B$. By adjunction this implies the isomorphism $$ f_*\mathrm{Hom}(A, f^!(B)) = \mathrm{Hom}(f_!(A),B). $$ One gets Serre duality when $f$ is proper (so $f_!=f_*$) and $B=1_S$ (so $f^!(B)$ is the "dualizing sheaf"). Combining the projection formula with Poincaré duality we can also deduce that $f_*$ preserves $\otimes$-dualizable objects when $f$ is both proper and nice, which gives a perfect pairing. But the usual formulation of Serre duality in terms of cohomology is specific to the derived category of quasi-coherent sheaves when $S=\mathrm{Spec}(k)$ is a field (note that in the quasi-coherent case the adjunction $(f_!,f^!)$ only exists when $f$ is proper).
There are further duality statements, such as Verdier duality, which says that $\mathrm{Hom}(-,f^!(1_S))$ is an equivalence of a certain subcategory $D_c(X)\subset D(X)$ of "constructible"/"coherent" objects with its opposite. But unlike the previous two, this statement does not directly follow from the various relations between the six functors.