I feel like we gave up too quickly on parameterized spectra as a reasonable (if necessarily incomplete) answer to this question; we don't get the two formalisms in the question as special cases of an overarching formalism but we do manage to make the algebraic topology side of the story look at least deceptively more like the algebraic geometry side. So I'd like to campaign a bit for this point of view; the goal is just to remedy the impression that the two stories are "seemingly orthogonal."
I am going to try to say everything below in a model-independent way. Think of a space / homotopy type $X$ as an $\infty$-groupoid. Define an $\infty$-local system on $X$ to be an $\infty$-functor $L : X \to C$, where $C$ is an $\infty$-category. Then I claim that:
The $\infty$-colimit of $L$ should be thought of as the homology of $X$ with coefficients in $L$, while the $\infty$-limit of $L$ should be thought of as the cohomology of $X$ with coefficients in $L$.
This is the most general thing I know which deserves to be called the (twisted) (nonabelian) cohomology or homology of a homotopy type (as opposed to something like a scheme).
Example. Let $X$ be discrete and let $C$ be an ordinary category. Then a functor $L : X \to C$ is just a collection $c_x$ of objects in $C$. The colimit is the coproduct $\bigsqcup_x c_x$ and the limit is the product $\prod_x c_x$. In particular, if $L$ is a constant functor with constant value $c$, then the colimit is the tensor $X \otimes c$ and the limit is the cotensor $[X, c]$. Specializing to $C = \text{Ab}$ gives the usual homology resp. cohomology of a discrete space with coefficients in an abelian group.
Example. Let $X = BG$ be the classifying space of a discrete group $G$. Then a functor $L : BG \to C$ is essentially an object $c$ of $C$ together with an action of $G$. The colimit is the (homotopy) quotient of the action and the limit is the (homotopy) fixed points of the action. Specializing to $C = \text{Ch}$ (here I mean the $\infty$-category presented by chain complexes) gives usual group homology resp. cohomology.
Example. Any object $c \in C$ defines a constant local system on any space $X$. The colimit is the tensor $X \otimes c$ and the limit is the cotensor $[X, c]$ as in the discrete case. Specializing to $C = \text{Sp}$ gives usual homology resp. cohomology with coefficients in a spectrum.
Example. Let $C = \text{Sp}$. Then a functor $L : X \to \text{Sp}$ is a parameterized spectrum. If $X$ is pointed and connected, then we can think of such a thing as a spectrum $E$, namely the spectrum associated to the basepoint, together with an action of $\Omega X$. Here the colimit and limit reproduce twisted versions of homology and cohomology with coefficients in a spectrum. In particular, if $E$ is an Eilenberg-MacLane spectrum $HA$, then the only possible twists are given by actions of $\pi_1(X)$ on $A$, and we recover homology and cohomology with coefficients in local systems in the usual sense.
Example. Let $C = \text{Spaces}$ be the $\infty$-category of spaces. Then a functor $L : X \to C$ is, by a suitable version of the Grothendieck construction, the same thing as a bundle $\pi : Y \to X$. The total space $Y$ is in fact the homology / colimit of $L$, whereas the cohomology / limit is the space of sections of $\pi$. In particular, if $L$ is the constant local system with constant value $c$, then the homology / colimit is the tensor $Y = X \times c$, with $\pi : X \times c \to X$ the natural projection, and the cohomology / limit is the cotensor $[X, c]$ (here I mean the space of maps from $X$ to $c$).
To make this look a bit more like six functors, start by assigning to each space $X$ the $\infty$-category $\text{Loc}(X)$ of $C$-valued local systems on $X$. Every map $f : X \to Y$ induces a pullback map $f^{\ast} : \text{Loc}(Y) \to \text{Loc}(X)$, and if $C$ is suitably nice this map will have a left and a right adjoint (left and right Kan extension along $f$) $f_{!}, f_{\ast} : \text{Loc}(X) \to \text{Loc}(Y)$. When $Y$ is a point these two pushforwards reproduce colimits resp. limits and hence reproduce homology resp. cohomology in the sense of the above definition. In particular when $C = \text{Sp}$ we are in fact assigning to a space $X$ the $X$-parameterized spectra, and pulling and then pushing from and then back to a point recovers the usual notion of homology resp. cohomology of $X$ with coefficients in a spectrum.
