are there families of function that are:$\def\R{\mathbb R}$
Smooth, i.e. $C^\infty(\R\to\R)$, and
Complete, i.e. they can point-wise approximate* any piece-wise continuous function $\R\to\R$ almost everywhere$^1$, and
Are orthogonal w.r.t. to whatever product, and
Each function has compact support.
*) Almost everywhere. I am mostly interested in approximations of "real-world" function like used in engineering and physics, less in pathological constructs.
As far as I understand, no such families are known, so is there any reason / proof for why such families do not exist? Or is this topic of current research?
There are procedures that yield infinitely many such families, solutions of some differential operators, one example being trigonometric functions, other example is Bessel functions, other one is polynomials. However neither thereof satisfies 4.
And if you require 2., 3. and 4. then the closest you can get are Daubechies' wavelets? They can be tailored to be $C^n$ for any finite $n$ but are not $C^\infty$.
I have no research background, but I wonder about this since I heard about Daubechies' wavelets decades ago...
Here are some general remarks:
Points 1. and 3. imply that there is some countable subset of $C_C^\infty(\Bbb R)$ that is dense in $L^2(\Bbb R)$. You can then do the Gramm-Schmidt procedure on this subset to recover an ONB of $L^2(\Bbb R)$ that consists only of $C_C^\infty$ functions.
(The Gramm-Schmidt procedure works inductively, for a sequence $v_n\neq0$ let $e_1=\frac{v_1}{\|v_1\|}$ and for $e_n$ consider first $v_n$, if it is linearly dependent on the previous $v_1,...,v_{n-1}$ throw it out, else let $e_n' = v_n - \sum_{i=1}^{n-1} \langle v_n, e_i\rangle e_i$ and then $e_n := \frac{e_n'}{\| e_n'\|}$.)
Now since you have an ONB $e_n$ of $C_C^\infty$ functions any $L^2$ function $f$ can be written as: $$f=\sum_{n=1}^\infty \langle f, e_n\rangle\, e_n$$ where the sum converges in $L^2$ sense. It follows that you then get a subsequence $N_k$ so that $$\lim_{k\to\infty}\sum_{n=1}^{N_k} \langle f, e_n\rangle e_n$$ approximates $f$ pointwise almost everywhere by remark 2. The finite sum above is however a $C_C^\infty$ function as it is a finite linear combination of such functions.
To summarise: Yes, such families of functions exist. Given some random family of $L^2$ functions, the only thing you need in order to extract an ONB satisfying your properties are 1) closed under linear combinations and 2) dense in $L^2$. There are many such families, as these two properties are not very rare.