Figure out how to award custom forum badges automatically
I did some digging on how to create triggers for custom forum badges (e.g. to automatically award a Tor Browser Alpha Tester badge when a user posts in the Alpha Feedback category), and it looks like we'd need to:
- Enable triggered custom badge queries for our discourse instance
- Write an SQL query for each badge we create
- And choose the trigger for when to award the badge
However enabling this functionality comes with some warnings from Discourse:
Starting from Discourse 1.6 badge sql can no longer be edited by admins unless explicitly enabled.
This change was made for a couple of reasons
Security: allowing admins to enter SQL directly allows them raw access to the database, generally we are opting that raw access to the database from the web UI is a feature you opt-in for. Even though the queries only return user_ids, an admin attacker can discover any information in the database using badge queries. If column A of table Y has the letter A in it return user_id 1 else 2.
Performance: getting badge SQL “just right” is an art, it is not something that is trivial for admins to do correctly. There is huge amount of risk that people who are not experts can create enormous load on a database by entering bad SQL
The alternative would be to have forum admins manually grant badges instead, which may not be so bad depending on the volume. What do you think @gus?