I'm curious how different types of chord qualities affect chord progressions, for example:
Can an add9 chord be added into a C F G progression?
As others have mentioned, you can substitute any chord quality you like for anything, but since you've asked for a theoretical approach, here are some observations I've come to understand about this kind of substitution:
If you have a basic progression, you could substitute a major 6th chord for a major triad. You could do the same with an add9 chord for a major triad. I mention these two examples because they tend to really not have any effect on the underlying harmonic progression of the piece. Add9 chords, in particular, can be so subtle as to not be perceived by even some musicians (especially when voiced as an add2). They're a pretty "safe" choice of substitution. Most chords in a diatonic chord progression have the major 9th/2nd above the root, so it'll usually sound pretty normal.
Now, in a more general sense, chord qualities can be substituted any way you like, but some substitutions sound different than others. Take your basic C F G progression. If I play a Cadd9 instead of a C, most people wouldn't really notice. If I played a C6 chord, they still might not really notice, but it might sound darker to them. If I played a Cmaj7 chord, they would probably notice that it sounds dreamier/jazzier. If I played a Cm(maj13♭9♯11), well, let's just say they'd notice.
One of the most common places to substitute chords is on the dominant (G in our example). Instead of a G, I might play a Gsus. Or a G7. Or a G7♭9. You get the idea. Jazz, in particular, is a genre where lots of these substitutions occur, and they're often not so subtle :)
What's more, you don't have to only substitute out chord qualities. Instead of playing C Am Dm G C, I might play Em7 E♭°7 Dm9 G7(♯9♭13) C6/9. Some of those chords don't have the same roots as the chords they're taking the place of, but because they can fulfill the same harmonic function as the originals, they make sense in the new progression.
The best way to tell whether a certain substitution is something you like is to play it a lot and evaluate it. Maybe you like playing 12-tone matrices instead of actual chords. If so, go ahead. The gods of music theory aren't going to stop you. There are no hard-and-fast rules about what is "allowed", so whatever sounds good to you is valid. You can even add in chords between other chords, so long as you know what you're doing.
And finally, if you're playing in a group of people, make sure everyone's on the same page about what chords you're going to be playing. If the lead sheet says G7, then you might play G9sus while the guitarist plays G7♯11. That conflict may be too spicy for most audiences.
Hope you're not asking what the 'rules' are!
The most used (and most accepted as) 'qualities' are major, minor, augmented, diminished and dominant. Other extensions, suspensions, additions aren't necessarily considered 'qualities'.
As we keep saying, do anything you wish - add extra notes to simple triads, move from X to Y, etc. No-one is going to have you lined up against the wall for it - although they may move out of earshot!
The most used quality will be dominant seventh, which is a ♭7 note added to a triad, making teh change up a 4th sound heralded. Augmented chords - where the 5th is sharpened, have the same sort of effect.
Diminished have the propensity to change tack completely, and move the line somewhere unexpected. Major/minr pretty well speak for themselves.
Your 'add9' can go pretty well anywhere - your choice - but hard and fast rules - don't bother! As piiperi says, give things a try - no-ones going to get hurt - and at the same time, give the lesser used chords - ♭5, ♯9, et al a sounding out.