I hope it helps you.
This is almost exactly what I'd like to do with recursive CTEs in one of my current projects.
They can be incredibly helpful once you grok them! And recognise when they can be used.
I've also seen the application change on top of the DB way more than I've seen the reverse, so I'm inclined to avoid putting data manipulation in the application when possible. That way it's there, for free, when we need a second application to access the same thing, or when we break off some chunk of a program into its a separate service and re-write it in Go because it turns out to be a performance bottleneck, or WTF ever.
Scaling a database is incredibly difficult, and requires a lot of expertise.
Some of the biggest engineering projects I've been a part of is removing a central db that everybody connects to in large companies.
Turns out the fastest and simplest solution, by far, was a mix of window functions and CTEs directly in the database.
I have to second this. There is so much power in relational databases that is untapped by most developers. The best part about this is that, for the most part, this type of knowledge can apply to multiple databases.
Some specific things that I want to gain a deeper understanding of are window functions[0] and recursive CTEs[1]. In particular, I've used window functions to identify peaks in sensor data (e.g., finding spikes in temperature, water level, etc), which would otherwise require iterating through rows and maintaining a bunch of state. I've never actually written a recursive CTE, but I'm pretty sure it would simplify virtually anything dealing with a hierarchy.
[0] https://www.postgresql.org/docs/12/tutorial-window.html
[1] https://www.postgresql.org/docs/12/queries-with.html