Pine is cheap to buy and easy to work, but it's one of the more difficult woods to finish attractively. Someone else suggested oak, and I agree. If the final appearance is important, use something besides pine.
| I do have a Porter Cable HVLP sprayer and a small air compressor | at puts out about 8 SCFM
It's possible to get a good finish without spraying. A wipe-on poly or varnish will get you a smooth, attractive, tough finish.
| What products (stains & finishes) are used to get that | professional looking finish?
A lot of it depends on what you're comfortable with or what you've had good results with in the past. Some people like Minwax products; others don't. I like oil-based products, for example. Others don't. Some people like oil and wax; some like polyurethane.
Don't change the color of the wood unless you really need to match something that's already there. It saves you a step and generally makes your work look better. If you have bad luck with pigment stains, which are generally just very diluted paint, try a dye. I have a set of dyes that I mix with various solvents to change the colors of wood. They're easier to work with, in my experience, than stains.
I do a lot of wiping for top coats. It works well for me, especially since I don't have a sprayer. It takes time, though. So if you want to go that route, prepare SWMBO for the likely schedule ahead of time. I'm currently restoring a harpsichord, and I don't have a problem taking six weeks to finish it elegantly and properly. There's no rush on the project, and quality is king.
There are many prepared wiping coats. But you can make your own by thinning your favorite topcoat with an appropriate solvent. I make my own wiping poly by thinning 30-50% with turpentine. I make wiping varnishes the same way. Wipe on with a clean cotton rag -- old T-shirts work best. This is the best way to do many thin coats with no marks from the applicator.
The main difference between varnish and polyurethane is brittleness. Varnish produces a harder, but more brittle finish. This responds well to rubbing and abrasion to adjust the sheen (see below), but doesn't stand up well to impact. As as your wood "moves" over time (i.e., expands, contracts, warps, bends), the varnish will likely crack. In contrast, polyurethane produces a softer, flexible finish. Over time it will move with the wood, but it's harder to "rub" and isn't very resistent to scratching. It will acquire dull patches if it's constantly rubbed up against.
Adjust the sheen of your final coat with fine steel wool. Many here don't like steel wool because it leaves little metal particles behind, and they recommend using Scotchbrite pads instead, or very fine grit (600 and finer) sandpaper. Either way, make sure you've built up enough film to support the sort of abrasion you need to do. Use a sanding block to keep the pressure even.
For the final step, many people like wax. Johnson makes a wonderful paste wax and Watco makes a liquid Satin Wax that's also good. You can skip the wax step if you like.
In general avoid generic "wood finishes" unless you have time to experiment on scrap. You just don't know how those are going to behave. If you don't want to muck with abrading the final coat to get the sheen you want, it's okay. Many of us avoid "satin" and "matte" topcoat finishes because the sheen is achieved by very small particles forming an emulsion on the final film. If you put enough of that on, it will noticeably obscure the grain. But if that's too subtle a difference for you, use whatever you need.
I recently finished a pine door in my house as an experiment. I sanded to
220 (never go higher if you're going to use a poly or varnish) and then wiped on a sealer coat of 2 lb. shellac. Then I wiped on three coats of a matte oil-based polyurethane thinned 50% with mineral spirits. Then I went over the whole thing with 0000 steel wool. The results were impressive. I deliberately broke a lot of my own recommendations just to see whether they actually meant anything. The grain wasn't obscured, there were no ugly steel filings, and the sheen was just right.
What's the moral? Practice and attention to detail. A professional finish is less about specific products and specific tools and more about taking your time and paying attention to the behavior of whatever techniques and tools you like to use. If you want to use the sprayer, experiment with different products at different viscosities.
--Jay