I've found that my all-time favorite stories are slow-paced, sometimes slice-of-lifey fantasies that follow the life of a loner-hero archetype from childhood through adulthood to death.
The protagonist is (usually) a good-hearted, selfless person who lives a tragic life, has human flaws that cause them to fail to accomplish some of what they set out to do, are hated by many/most people, and dies at the end- sometimes violently, sometimes due to old age. The tone of the story never strays into the realm of bleak, pessimistic grimdark however. There's an underlying sense of idealism and hope, and a theme of inherent redemptive goodness in people, however dark or twisted the story gets.
The death at the end has meaning, and there is often an implied "after". In one story, this is the Christian Heaven. In another, the protagonist's spirit becomes a part of the river of all magic in his world- joining him with loved ones who had died in a similar way.
I haven't come across too many of these stories (like, three off the top of my head, with two more that don't meet all the points as the protag only 'dies' in a metaphorical sense, but not literally), but the ones I have come across are notably my top favorites. Won't name them because spoilers- but I would welcome recommendations if this reminds you of anything.
Edit: Also fond of stories that have a theme of "magic in the everyday". Not sure if that qualifies as niche, but I haven't seen too many of these (if you know some, tell me where they're hiding!). Studio Ghibli's slice-of-life/drama films are what I think of first; Only Yesterday is a favorite of mine. :]
