Need a next read recommendation

With the movie adaptation of project hail mary coming out I decided to re-read the book, and it's reignited my reading itch. And I'm looking for a recommendation on what to dive into next.

Books I've loved:
• The Martian & Project Hail Mary (Weir)
• Murderbot series (Wells)
• Wool/Shift/Dust trilogy (Howey)
• Congo (Crichton)
• 12.25 (Thomason)
• Hunt for Red October (Clancy)
• City of Ember (DuPrau) – only the first one…. the second two lost the plot/vibe and made the book about rival towns and weather as the antagonist bleh
• Harry Potter 1-3 (Rowling) – lost interest after Goblet of Fire.. same as ember lost the school comming of age mystery drama and turned into wizard politics and the search for horcruxes

What I'm looking for:

• Mysteries that unfold at a good pace. I don't want glacially slow, but also don't want  constant whiplash.
• Both immediate problems to solve AND larger mysteries about the world/situation to uncover eg wool/phm
• Lighter/hopeful tone even when tense. humor is appreciated but not required
• Good narration/POV (single or multi-POV is fine, just not disconnected vignettes hated the themis files)
• Sci-fi appreciated but not required

What made me DNF:
• Stories that expand into large-scale conflicts and lose the main protagonist problem-solving focus (why I dropped Ember sequels 2-3 and later Harry Potter books)
• Characters running around accomplishing nothing but flair (looking at you Chris Chinball)
• Graphic romanc as the primary focus (romance subplots are fine but not looking for a smut fest )

I think a good non book example of what I am looking for is The video game Stray. We uncoverwhat happened to the world while surviving and solving immediate puzzles.

Considering:
The bobiverse
Dark matter
Red Rising.

Open to any genre as long as it has that propulsive mystery-solving energy! Standalones or series welcome (as long as we don't loose the plot like ember/hp) .