I’m afraid I don’t know much about clubs etc, but am a regular visitor there and my parents lived nearby. It’s a lovely place.
It does suffer a bit from being a bit of a suburb of Galashiels. There are primary schools in Melrose, but high school is Gala, big supermarkets are in Gala etc. Gala is nice enough but not a picturesque and “posh” as Melrose.
It’s well placed fir the Borders railway up to Edinburgh, but the train journey does take about a million years because of the number of stops. Handy for teens who want to go into Edinburgh though.
Lovely walks over the hills, and along the Tweed at Abbotsford. Lovely kid-friendly walk from Abbotsford to Bowhill. Good links to other towns, in fact the local towns very much become a part of each other, Melrose, Selkirk, Bowden, St Boswells, Kelso. etc.
It’s quite a tourist town, but still with with a good local community spirit. Most community interaction is focussed around the rugby (very, very, very big part of life in Melrose), the riding (early summer gala), and horses.
They have a lovely book festival too, usually with some big names.
Join historic Scotland to get access to all the local Abbeys etc. Smailholm tower popular with the kids. Great adventure playground and walks at Bowhill, as well as community events around festivals Easter/Halloween etc. Also a nice community theatre.
Lovely bookshop nearby in St Boswells (Main Street Hare).
The wee ice cream shop diagonally across from the Abbey does some of the best ice creams in Scotland 
Music lessons and clubs - Nomad Beat in Peebles are really really good, but about a half hour drive from Melrose. They might be able to point you at something more local though.