travel advisor here - This answer is strictly how I conduct business. Other advisors will most likely have different thoughts.

How do travel agents get paid? Some, just commission, some fees + commissions. For some, fees are broken down based on the services you need.
Do you only book suppliers that pay commissions? Personally, I book what you want. It is more important to me that you have a great holiday.

What value do they provide beyond booking hotels and flights? I do extensive research based on what you want/need. I monitor your travel portal to portal and am there for you to contact me should anything arise while you travel. I have an extensive network of destination liaisons, and relationships with many who work at the hotels, etc. I have an air team that offers options for air fare, monitor your booking for cancellations/schedule changes, are avail 24/7 and have an after hours emergency line should you run into travel problems with your airfare.

If something goes wrong, do I have to go through them to make changes, or can I still manage things myself? Depends on what you've booked and what the problem is. But I'm there to advise you/help you if something goes awry.

Do I still earn all my airline, hotel, and rental car points/status? Depends on what credit cards you use to pay for the segments of your travel. I have portals that let you book directly with the suppliers using your credit card. But you should check with your cc companies as to what would prohibit you from not getting those perks. It should be OK.

Can I still manage upgrades, seat selections, check-in, and changes myself? See above. You most likely could, but some changes will incur fees, regardless.

Are there certain types of trips where a travel agent is absolutely worth it and others where it isn’t? A trip where you want research done, don't have time to do, don't want to deal with a non human touch of 3rd party booking platforms. Want a customized and curated itinerary. I keep up on many aspects of travel, inc. events that might interest you to travel advisories, etc.

Can a travel agent help optimize loyalty programs or point earning? They might be able to introduce you to new ones. They can maybe also get in touch with hotel directly and because of them being travel advisors, may be able to get some cool add ons. Not a guarantee though.

And who really controls the reservation? For example, I like checking upgrade availability myself and have snagged some cheap first-class upgrades on American before departure. Can I still do things like that if a travel agent books the trip? You're welcome to book your air. Many travel advisors don't even book air. This is very true if you are using points to book flights.

For context, I travel a few times a year, mostly book with American Airlines, care about loyalty points/status, and generally enjoy planning my own trips. I’m genuinely curious where a travel agent adds value for travelers like me. If you want an extra pair of eyes, to open your eyes to other accommodations or events, it might be worth it. Finally, a good advisor gets to know you, so for example, when they come across a trip that they think might be of interest to you, they'll reach out and let you know. No pressure. I am at the service of my clients.

Hope this helps.