If you're comfortable with DFHack and the workflow plugin, you can achieve quite a lot of automation, for example:
- Clothes are no problem: tell workflow to keep (say) 10 shirts on hand, and as dwarves throw out their old ones and put new ones on, the supply drops and the clothes makers start making more.
- Chains of production are mostly no problem - just build all the workshops needed to make the items leading to soap, ask workflow to keep a couple items of each intermediate product on hand, and enough soap on hand at the end, and the whole chain will operate as needed to replace soap as it's used.
- Tell the charcoal burner to keep 10 charcoal on hand, the smelter to keep 10 iron on hand, and you'll always have iron ready to make things from as long as you have wood and iron ore stockpiled.
- Keeping manufactured furniture on hand means you can just build when you want (up to a point) and let the dwarved handle restocking.
- Shearing/milking/cheese making are automatable if you're not concerned about efficiency: just tell workflow to keep milk on hand and every time it runs short the dwarves will try to find an animal to milk.
Workflow's format - "keep N on hand" - also generally makes it possible to size stockpiles appropriately. The tricky bits are the places where workflow isn't smart enough to know what the output of a process is - making powder can be tricky, since workflow can't really tell sand from flour. So I just don't ever make flour.
If you're worried about having to keep mining manually, many things can be made from green glass, which requires no consumables. In particular, you can make green glass "gems" and encrust everything in your fortress with them, just to keep jewelers busy. For the metal ores, you can dig out large areas and just leave the ores lying on the floor; when spaces open in your stockpiles dwarves will come refill them (with wheelbarrows, optionally).
Wood is annoying, particularly if you have to go outside to get it; I think there's an autochop plugin that sends dwarves to cut more wood when it runs short, but I don't really use much wood on a regular basis, so I just have big stockpiles I refill as needed. Underground tree farms take forever to build and get producing but do solve the danger problem nicely; in principle you can wall off a piece of the caverns but I haven't needed to do this. Also every time I let my dwarves into the caverns they all take off to clean up every bone, spiderweb, or lost sock anywhere in there, so mostly I keep them shut off.
You can very efficiently train up metalworkers by telling them to keep N gauntlets (say) on hand, setting up a stockpile for all non-masterwork gauntlets, and telling the dwarves to automatically melt anything in that stockpile. Depending on which item you choose, this can require only a small amount of metal on each make/melt cycle, or even generate metal (through a bug). If you build such a training forge, it can make sense to let only non-legendary dwarves work there. The main annoyance is when a military dwarf comes and equips some non-masterwork item otherwise destined for melting.
Autobutcher is also very handy - just tell it to keep (say) 5 female and 3 male cats, and you'll have a comfortable number of cats to keep your vermin down, and a steady supply of cat meat and bones. Using up the bones can actually become a problem; I decorate random things with bone, but it's hard to keep the numbers in balance so that the decorators don't stop because they're out of bones and the bones don't accumulate. You can also tell workflow to keep a huge stock of bone trade goods on hand (they're small), but eventually you'll need to get rid of some of them to get the job restarted.
Autonestbox is handy, though you could do the same thing by hand: assign every female egg-layer to a 1x1 zone on top of a nestbox. They'll produce a constant stream of eggs until they die of old age.
Seasonal plants are annoying; pig tails only grow half the year, so unless I can arrange to have a sufficiently massive stockpile the dwarves tend to run short of fiber-making materials when they're out of season. If I can get a hold of non-seasonal surface plants I build a greenhouse ("outdoor" garden roofed in green glass) and solve that problem.
Dyes are kind of annoying too; at least one of the jobs runs into the ambiguous powder problem, and dyeing fabric is also not a job workflow can really cope with.
Animal training can be largely automatic, too. Usually if I catch something mildly interesting but dangerous in the caverns, I set my dwarves to train it in its cage. No risk, occasional warning span when it goes wild, but eventually my civilization gets good at taming whatever it is. Numerous critters like crundles I tame and then butcher. Valuable creatures like giant jaguars need a little more attention to set up a proper breeding program, but as long as your animal trainers aren't all too occupied you can just rely on them keeping the animals tame until young can be born and then permanently trained. Egg-layers like jabberers are particularly annoying to breed, since the eggs need to be kept away from hungry dwarves but the parents still need to be kept from going wild. It can be done but it's not easily automated.
Military training can be automated; others have much cleverer setups, but I just make sure every entrance passes through a barracks where a squad is permanently on active/training. The dwarves get a little grumpy about never getting breaks, but soon enough they hit legendary and stop minding. It helps to put a few beds, chairs, and tables, and have stockpiles of food and drink that pull from the main one.
My fortresses usually run in a basically automatic fashion: every time I unpause after the seasonal autosave I go around and check the non-automatic things: have the decorators run out of input and need to be told to start up again? Is it time to cycle the obsidian caster again? Does the forbidden beast silk farm need its target captive replaced? Otherwise the fortress runs basically unattended apart from whatever project I'm working on. My game mostly stops for sieges caravans and moods, which I prefer to handle interactively - though to be honest once the fort is up and running and has a supply of basically everything on hand, moods generally go fine without intervention. Sieges I usually have reasonably strong static defenses, but it always takes some military dwarves to clean up the stragglers, and then everybody pitches in to clean up the mess. (I'm still working on the perfect entry defenses that can swallow a siege and extract the iron without ever exposing a civilian to the sight of a dead body. Obsidian casting is a good place to start.) Caravans are where I get weird items that are annoying to make - wood, seeds, beverages, pets, metals not found on my map and not brought by invaders.