Costelloe was born in Limerick, County Limerick and was a part of the youth team at Aisling Annacotty before joining Galway United under-15 side in 2017.[2] He made his first team debut for Galway a year later on 31 August 2018, aged 15, when he came on as a substitute in the 2–0 defeat to Finn Harps.[2] This broke the record for the youngest every player to play for the first team. A day later he signed a first team contract having impressed for manager, Alan Murphy's, under-17 side.[3]
In February 2021, it was announced that Costelloe had signed a contract for Premier League side Burnley having trained and featured for the under-18 and under-23 setups since the previous summer.[4] He signed a deal with Steve Stone's Academy side until June 2022.[4] After two successful campaigns in the Academy, he signed a new contract with Burnley in the summer of 2022.[5] He finished as top scorer for the under-23 side with twelve goals and also made the bench for the first team for the last seven games of the Premier League season as Burnley were relegated.[5]
Costelloe made his first team debut on 29 July 2022, starting the first league game of the season in a 1–0 win over Huddersfield Town at the Kirklees Stadium.[6] It was also Vincent Kompany's managerial debut for the Clarets.[6]
On 13 January 2023, Costelloe joined EFL League Two side Bradford City on loan for the remainder of the 2022-23 season.[7]
In August 2023, he signed on loan for St Johnstone of the Scottish Premiership.[8] After being recalled on 1 January 2024, Costelloe immediately joined fellow Scottish side Dundee on loan until the end of the season.[9] Costelloe made his debut for the Dark Blues on 20 January in a Scottish Cup game away to Kilmarnock.[10] On 21 May 2024, Burnley said the player would be returning once the loan ended.[11]
In August 2024, Costelloe joined League Two side Accrington Stanley on a season-long loan deal.[12] He made his debut from the bench in the league opener against Doncaster Rovers.[13] Costelloe scored his first goal for Stanley on 1 October against Cheltenham Town, and followed that up four days later with a crucial double which gave the Reds a league win over Morecambe.[14][15]