Building a Signin System for the SF Go Club using Google Sheets, Discord, QR codes, and a Scan Gun

Just because I’ve been on hiatus from work doesn’t mean I don’t ever get an itch for coding! After building software and a physical labeling system to operate the SF Go Club’s library, I was approached with another problem to solve. The club needed to track attendance in order to know which week nights were the most popular, and to encourage regular goers to sign up as club members. There existed a couple of manual approaches: one was to ask club staffers to track how many people came to the club on a given day, the other was to ask people to write their name and emails on a paper list. Both processes failed to achieve the stated goals, as staffers were too busy to track every single person, and following up on a hardly-legible list of emails was painful.

A Simple solution

At a minimum, we could replace the paper form with a webform, so this is what I started with. It’s self service, and makes it easy to re-use our guests’ emails. An easy way to abuse this might be to use a random email, or the email of a paying member. To solve this, we simply sent a confirmation link to that email and asked the member signing in to click the link.

The club cares to know whether guests are also paying members. Knowing this allows the club to follow up with prompts for membership signups, or suggested donations. Being a club of modest means with a DIY ethos, the member list is stored in a private Google sheet owned by the club. The Google API has a concept of service accounts, but I used to mistakenly believe that a service account had automatic access to all the documents available to the service account’s creator — this would have made it a less appealing solution. Thankfully, this is not the case: service accounts have their own email addresses. Google docs can be shared with that address, and the service account can then access those specific documents.

Moving all of the club’s membership accounting to my web app was well beyond the scope of this task, so accessing membership info via the Google Sheets API seemed like a perfect solution for the problem at hand.

Doing one better

Filling in a form, and checking an email can be tedious and slow, especially if several guests are trying to sign in consecutively. Inspired by my local gym, I wondered whether it would be possible for use a QR code-based approach to the sign in system. I was delighted at how simple this ended up being.
The flow could be as pared down as this:

• Guests show a personalized QR code to a scan gun.
• The app recognizes the code, and signs them in.
• Done!

I could have used a product like Envoy, but that would be more expensive, and less fun, so I decided to cook something up on my own!

Adding a secret uuid for each user is trivial in rails, and transforming it into a QR code is simple using gems such as rqrcode:

RQRCode::QRCode.new(@user.uuid)

The part that was unknown to me before hand was the behavior of the scan gun. How would the data transfer from the peripheral to the app?
I originally imagined the barcode scanner would come with software to run in the background, and trigger a piece of code of my choosing when a QR code is successfully read. This is how I discovered the difference between KBW and COM modes for USB devices. COM mode means the data from the peripheral is communicated just as a serial port. This method intake data at the operating system level — as raw bytes — allowing free manipulation of the contents. This can be useful, but in our case, using the keyboard mode was more convenient. The contents of the QR code can be captured via the keydown browser events.

var buffer = '';
function isUuid(string) {
  if (
    string.length < 36 ||
    string[8] !== '-' ||
    string[13] !== '-' ||
    string[18] !== '-' ||
    string[23] !== '-'
  ) {
    return false;
  }
  const withoutDashes = string.replaceAll('-', '');
  return Boolean(withoutDashes.match(/^[0-9a-f]+$/));
};
document.addEventListener('keydown', ({ key }) => {
  buffer += key;
  const last36 = buffer.slice(-36);
  if (isUuid(last36)) {
    const params = new URLSearchParams({ uuid: last36 }).toString();
    window.location.replace(`<%= club_visit_with_qr_code_url %>?${params}`);
  }
});

isUuid can be replaced with another function to match whatever input is expected from the scan gun. The code has some limitations, but for such a simple use case, it does the trick.

The cherry on top was communicating with other club members when someone arrives at the club. The SF Go Club has an active discord, and discord makes it delightfully easy to create a web hook, and associate a behavior with calls to that hook. To make matters even simpler, a ruby Discord client exists which makes sending a notification possible with only a handful of lines of code.

client = Discordrb::Webhooks::Client.new(url:)
client.execute do |builder|
  builder.content = if user.name.present?
    "#{user.name} has entered the dojo."
  else
    'A new member has entered the dojo.'
  end
end

Throughout this journey, I was pleasantly surprised at how easy it is to cobble together various pieces of software to create a functional, reliable sign in system to make the lives of club guests easier in the real world!