For me the Prodigy and Lower Decks theme songs are among the best in the franchise because they're versatile. You can have a slow, tender violin motif from the LD theme such as when Tendi was telling Mariner that the Beta shifters were her family at the end of season 2. A slightly different part gets a brassy remix as the swelling Crisis Point theme music for the Cerritos.

Not all Trek melodies do this. Voyager's got a lovely melody that feels appropriate for a grand trip homeward, but they tried using it at some big plot moments and it just felt wrong. Disco and TNG have the opposite problem where their themes are CONSTANT INTENSITY, so you don't often see them used in softer moments. (The latter is very weird to me considering we have heard softer variations of the theme, maybe I just can't think of any such uses in the series offhand).