there should be a prompt to install mods you're subscribed to when you start the game

What prompt

When you start up the game, the mods will automatically install. It can take awhile the first time if you're subscribed to a lot of mods or they are large like Auto Magic.

I don't really use mods so I don't know how they would look in-game

They will show up for purchase in the catalog and on the shelves. If you haven't played the game all the way through vanilla, you might not know which are vanilla and which are the mods, except through the descriptions on the workshop page for each mod. One of the mods that I've started using is MoreShelves. The only shelves in vanilla to use for storage is the bookshelf. MoreShelves mod adds lots more shelves, some which have lights.