I'm not quite sure about Steamdeck compatibility, so I apologize if some are not. Horror isn't exactly my genre, but I'll throw some other great story-driven games out there.
The Rusty Lake/Cube Paradox series are absolutely incredible if you like puzzles/escape rooms. The collection is probably 10-15+ hours of entertainment with a single playthrough. Will also throw Gorogoa out there, but that's only about 2-3 hours.
Outlast series
What Remains of Edith Finch
Life is Strange is absolutely fantastic and mostly about time.
I've heard great things about Firewatch and Outer Wilds, but have not played either yet.
Stanley Parable is a bit dated, but it is fun if you enjoy narrated British humor or The Office.
The Bioshock Series is absolutely fantastic and plays best with a controller. I think the first is best, followed by the third.
The Metro series is great, but they are one of the most difficult single-player FPS out there. So are the STALKER games if you like modding, but those are visually a bit dated.
All of Supergiant Games (Bastion, Hades, and Transistor) are fantastic.
Other notable mentions: Stray, Remember Me,
I've heard great things about Days Gone, but it apparently the fun action part takes quite some time to get into.
You might really enjoy the original "Layers of Fear" if you like psychological horror, but it has very little spoken story and is definitely not an A-rated game. A remake also recently came out, but the original still holds up.