For the sake of this question, we'll model a six functor formalism in the following way. Let $\mathsf{C}$ be a category of spaces (be it the category of schemes, or topological spaces) and consider a triangulated closed symmetric monoidal category $\mathsf{D}(X)$, with identity $\mathscr{O}_X$, for each $X\in\mathsf{C}$.
Given a morphism $f:X\to Y$ in $\mathsf{C}$, we have functors $f_*,f_!:\mathsf{D}(X)\leftrightarrows \mathsf{D}(Y):f^*,f^!$ such that $f^*\dashv f_*$, $f_!\dashv f^!$, and $f^*$ is strong symmetric monoidal.
If we also suppose that $\mathsf{C}$ has a final object $S$, it is natural to define cohomology and cohomology with compact support to be $$H^i(X,M):=\hom_{\mathsf{D}(S)}(\mathscr{O}_S,p_*M[i])\qquad\text{and}\qquad H_c^i(X,M):=\hom_{\mathsf{D}(S)}(\mathscr{O}_S,p_!M[i]),$$ where $p:X\to S$ is the natural morphism. If $\mathsf{C}$ is the category of ringed spaces (or even of ringed sites, I think), this coincides with the usual definitions.
I wonder then how could we obtain some sort of duality isomorphism similar to Poincaré and Serre duality. Perhaps we could begin with $$\operatorname{Ext}^i(M,p^!\mathscr{O}_S)=\hom_{\mathsf{D}(X)}(M,p^!\mathscr{O}_S[i])=\hom_{\mathsf{D}(S)}(p_!M[-i],\mathscr{O}_S)$$ and the latter should be something like $H^{-i}_c(X,M)^\vee$, but I'm not sure how to make this precise.