There are fee free cards that give cash back as statement credits (AMEX Blue iirc). No limitations on what you can spend it on. The Apple Card does 2% cash back which you can just transfer to your bank account.
The Amazon card requires a Prime membership, but gives 5% back on anything bought at Amazon. I bought my last TV using the 5% back I had received.
Then there are top tier cards like the Chase Sapphire or Cap One Venture X that have yearly fees. But, if you take 1+ trips/year they immediately pay for themselves and more (credit for global entry, yearly statement credit for travel that almost equals the yearly fee, lounge entry, etc...). I routinely use points from the Venture X to cover travel expenses like tickets, rentals, hotels, eating out, etc...
https://frugalprofessor.com/bank-of-america-customized-cash-...
To your point, it's not free money at all: the credit card companies are collecting fees, and the merchants are passing them on to you. This is a way to claw a part of that back - if you don't use a rewards card, you're paying _even more_.
Amazon gives you 5% back for using their credit card, it's criminal not to use it.
If you buy a lot of equipment or expensive equipment - B&H credit card covers sales tax! I.e. 10% for my area! (I don't use it since I don't buy that much, but still it's an option)
I know I could probably min-max this into more by juggling different cards for things like Amazon and Costco but I'm lazy and don't want to think.