Explanation
The goal of the query is to update the roles for users who were in a regular subchannel but are now in an announcement subchannel.

By default, they are all given the default role (Members). Now that the channel has been converted to an announcement channel, we need to selectively give Voiced to some users if they have the thread permission voiced_in_announcement_channels in the community root.

So the subquery

SELECT m.user
          FROM memberships AS m
          JOIN roles AS r ON m.role = r.id
          WHERE r.thread IN (
              SELECT community FROM threads WHERE id = ${threadID}
            )
            AND JSON_EXTRACT(r.permissions, 
              '$.voiced_in_announcement_channels') = true

looks for users in the community root who have the voiced_in_announcement_channels thread permission. The JOIN on roles is so we can perform a JSON_EXTRACT on the role permissions column. The WHERE statement helps limit the threads we are searching to just one. This subquery ends up in a set of user ids.

The outer query then updates the role for these users in memberships to the newly created Voiced role.

Performance
To analyze the performance, I followed @michal's test plan in the diff you linked. I wrote an EXPLAIN query. Two of the queries are of type ref, one is eq_ref, and one is const. Both const and eq_ref are regarded as being the best types of joins.

This query also only runs when a regular subchannel is converted to an announcement subchannel.

Here's a sample SELECT query you could run on production (though I'm not expecting any results since voiced_in_announcement_channels is not a thread permission on master as of yet):

SELECT *
FROM memberships
WHERE thread = ${threadID}
  AND user IN (
    SELECT m.user
    FROM memberships AS m
    JOIN roles AS r ON m.role = r.id
    WHERE r.thread IN (
        SELECT community FROM threads WHERE id = ${threadID}
      )
      AND JSON_EXTRACT(r.permissions, '$.voiced_in_announcement_channels') = true
  );

Maybe I'm misunderstanding your comment, but the expected result set of SELECT community FROM threads WHERE id = ${threadID} is just one

This is already handled by deleteRole, but they get assigned to the default role (Members)

Thanks for the explanation!

1. That test query has a syntax error on ${threadID}. Can you try again, and this time make sure to test the query before sending?
2. In additional to the other syntax feedback I previously shared, please make sure to remove AS where it's not necessary (all table aliases), and to remove the alias entirely where it's not used (such as main)? In general, please try harder to match our conventions around SQL – with every decision, look to see how it's done in the codebase. It will save both of us a ton of time and annoyance and typing.
3. Can you extract this query to a function that is named in a way that makes it clear what the query is doing?

Oops sorry, you're right

Thanks for explaining

1. That's weird. I tested before sending and I've just copied and pasted the query into TablePlus and it runs fine? Do I need to adjust it for the command line where you might be running it?

For 2 and 3, sure I'll go ahead and make that change

Perhaps I'm being unclear...

The string literal ${threadID} is not valid SQL syntax. Please provide a query I can copy-paste. I don't know which threadID to use

Completed in 0.020s! Looks good

Actually the "alternately" proposal doesn't need return await, just await