Over the Wire
Giving Saint Osmund's Recruitment Data a New Home
Written By UPC.
Edited By Champaubert, The Disney Imagination Republic

In the first installment of the How Does It Work series, I discussed some of the technical details behind our recruitment bot, Asperta. Two years later, I am returning to the topic of Asperta to discuss an interesting project that we undertook recently. It is common knowledge that, as an open-source project, anyone can host a copy of Asperta. But just because something is possible does not always make it desirable. After more than a year of self-hosting, Canton Empire (JL), Delegate and Minister of Foreign Affairs of Saint Osmund, approached the Europeian government about adopting our managed instance of Asperta. This article briefly discusses the technical challenges that JL and I overcame in importing Saint Osmund's data into our bot.

Before we begin, it is important to discuss why we took this step. It was not strictly necessary; there was nothing preventing us from having Saint Osmund start fresh. But doing so would mean that, in addition to losing historical recruitment data, every recruiter would need to register again. And frankly, this sounded like a fun technical challenge!

Asperta organizes its data into a series of database tables, which can be thought of like spreadsheets in a workbook. For example, one table stores users, and another stores metadata about recruitment channels. Data in tables is organized into columns, which store particular types of data, and rows, which represent individual records. Almost every table includes one or more columns that serve as a primary key, which can be used to identify a specific record in the table. This primary key can also be stored in other tables in order to signify a relationship between two records of different types. When primary keys are stored in another table as references, they're referred to as foreign keys. For example, each record in the telegrams table has its own primary key, but it also stores the primary keys of the user who sent the telegram batch and the recruitment channel in which it was sent. This allows telegram data to be easily aggregated by user and channel in Asperta's report function.

Luckily, most of Saint Osmund's was irrelevant for the purpose of the transfer, because it would either be outdated or overwritten by new data from the bot. We only really needed to preserve the user and telegram data. This also allowed us to dramatically reduce the amount of data we exported. For example, one major optimization we were able to apply was to attach the Discord user IDs of recruiters to their telegrams prior to exporting them. The foreign keys in the telegrams table, which referenced the user and channel tables, would point to the wrong records in the new database and would need to be overwritten. This strategy wouldn't work if we were exporting data from multiple channels, where one Discord user might recruit for multiple regions, but in this case it allowed us to skip a complicated series of internal ID comparisons when exporting and then importing the data.

Once the data was ready to import into our database, we needed to create a new recruitment channel to transfer it to. Here, JL registered a channel for recruitment normally and then provided me with the Discord channel ID. With this, I was able to find the corresponding record and insert Saint Osmund's old users into the new database. For each "new" recruiter, I tracked two separate identifiers: their Discord user ID and the primary key that the database assigned to them when they were imported. Then, when I uploaded the telegrams, I was able to compare the Discord ID on the telegram record with the user key that was just created to set the proper foreign keys in the telegrams table.

Upon validating that Asperta was working as expected, JL said that "you just did something really ******* cool." And this was pretty cool! We now have scripts and documentation to seamlessly merge self-hosted Asperta records into our database, and we've validated these tools in a production environment. This was a very interesting technical challenge, and I appreciate JL and Saint Osmund taking the time to test this process with us.