I have a bunch of G4s along with a G5.
I have a lower end G5 model(first generation single 1.8) and it's faster, more responsive, and just generally more useable than even my best G4.
With that said, I love G4s, and a lot of the reason for that is that they can be upgraded and "played with" a lot more than a typical G5.
The first thing you need to know when talking about G4 upgrades is that there were three different case styles(as well as changes within those same models) and which you have will govern what sort of upgrades you can do.
The case styles are
1. Graphite. This one looks a lot like the B&W G3. The front and side panels are a dark blue-gray color. The speaker on the front is at the bottom, and there is a grill over it. The optical drive door has an eject button in the center.
2. Quicksilver. The front and sides are a silver-gray color. The optical drive door is white, and the zip drive bezel(or blank for it) is is also white. The speaker has no grill over it.
3. Mirrored drive door(MDD). This one is pretty self explanatory. It has two optical drive bays, each of them hidden behind a "mirrored" door.
The Graphite cases actually contained three pretty dramatically different logic boards. The earliest of these(called the "Yikes!" sometimes, from its Apple code name) more properly called the "PCI Graphics" model, and is basically a B&W G3 upgraded to a G4 processor. These have 4 ram slots, and can take a maximum of 4x256mb for 1gb total ram. The next is the AGP graphic model, which was codenamed the "Sawtooth"(this name is still frequently used also). It can take 4x512mb for 2gb. The final revision is called the "Digital Audio." It loses one RAM slot for a max of 1.5gb, but gains a PCI slot. There was also a version between the Sawtooth and the DA, known as the Gigabit Ethernet, which gains(obviously) gigabit ethernet and I think the ability to power an ADC display.
The Quicksilver logic boards are similar to the Digital Audio logic boards in that they have 3 RAM slots(1.5gb max) and 4 PCI slots. The "Quicksilver 2002" model did gain the ability to use drives larger than 128gb.
All of the Graphite and Quicksilver models use 168-pin SD-RAM. I think that the Yikes! specifies PC-100, and all the others PC-133.
The MDD models are quite different internally from the earlier models. They returned to 4 RAM slots, but take(depending on the exact model) either PC-2100 or PC-2700 DDR. MDDs don't recognize modules larger than 512mb, so they still max out at 2gb RAM. The last model of the MDD added Firewire 800.
As far as upgrades go-processor cards can pretty much be freely interchanged between models in the same case(I think with the exception of the Yikes!). Quicksilver processor cards can be put in Sawtooth/DA models with a little bit of extra work. MDD processor cards can only be interchanged within other MDD models.
No G4 has USB 2 built in, so many of us will install a USB 2 PCI card. Look for an NEC Chipset on the card, and it should work with no issues.
Most of the G4 models can only take the original 802.11b "Airport" card, which has its limitations if you require wireless internet and can't connect to many modern routers. The exception to this is the Firewire 800 MDD, which takes an 802.11g "Airport Extreme" card and will work fine with many modern routers. PCI Wireless cards with the "right" chipset will work and be recognized as an Airport Extreme card. I know from personal experience that the Motorola WPCI810G will work, although I know there are others out there.
Many folks install SATA cards to allow them to install modern SATA hard drives. I've not done this, so can't give any advice on what works or what doesn't. I know that not all SATA cards will provide a bootable hard drive, so in those cases you will need to keep at least one IDE hard drive for the OS.
Finally, depending on what generation G4 you have, the stock optical drive may be somewhat limited in capabilities. I generally just go ahead and drop a DVD burner in. Most will just plug in and work without any trouble.
That's the long and short of what I generally do when upgrading a G4.