Fix $R$ an $E_{\infty}$ ring spectrum which admits a "six functor formalism" over a suitable class of spaces (by which I mean a context in which what I'm about to say can be made correct).

Let $X$ be a manifold and consider the projection $X \to pt$. Now replace the point by the homotopically equivalent embedding $j: X \to \mathbb{R}^n$ for some $n$. Denote by $i:U \to \mathbb{R}^n$ the inclusion of the complement. Now consider the exact triangle in local cohomology for the constant sheaf $R$ on $\mathbb{R}^n$. We get:

$$j_{!}j^! R \to R \to i_*i^*R$$

The LHS (I hope) may be interpreted as the Thom spectra of the normal bundle of the embedding via a tubular neighborhood argument. If everything so far makes sense an $R$-orientation on the normal bundle is precisely choosing a trivilization $D_R(X):=j^! R\cong R_X$ which is precisely what's needed for Poincare duality for cohomology with coefficients in $R$. The right hand side of this triangle is confusing and I have no idea how it relates to story of orientations and Thom spectra (I mean, it looks like cohomology of the unit sphere bundle with coefficients in R but that's not really what I need...).

The rest of the story is also blurred for me. Almost entirely due to the fact that I have almost no idea about any of six functors and I'm basically flying blind. Is there any **modern source treating the theory of (locally constant) sheaves of spectra in an $\infty$ categorical way?**

notthe setting of the book of May-Sigurdsson. This is articulated in Ben Wieland's very nice answer to the question I linked. $\endgroup$ – Dan Petersen Jun 29 '17 at 15:38