The List Became an App: Engineering a Gate Check-In That Runs on Phones

This is the engineering companion to our AGM check-in case study. The short version of that story: check-in at our Annual General Meeting used to run off two laptops at two to five minutes per arrival. After we built a gate app, it averaged about thirty seconds, on whatever phone each staff member happened to be holding. This article covers how the app works, and in particular the parts that matter to anyone building event-day tooling on top of imperfect data.

One piece of context makes the rest make sense. The app's seed data is two workbooks that our own automation suite already produces from the core policy system: the register of participating policyholders, and a full policy extract. The data pipeline's output became the product's input. Nothing was keyed in by hand, and the decision to reuse the pipeline shaped every problem below.

Search has to survive the way names actually arrive

Nobody at a gate types a name correctly. The name on a person's ID rarely matches the name in a decades-old register exactly; staff type surnames first, skip middle names, and misspell under pressure. Most people also forget their policy numbers, but some remember them, so search accepts those too, exact or by prefix.

The implementation uses PostgreSQL trigram matching, and the detail that matters is that it takes the greater of two different similarity measures: whole-string similarity and word-level similarity. Whole-string similarity handles small misspellings; word-level similarity handles reordered and missing name parts. Either alone fails on cases the other catches. Results rank exact policy match first, then prefix match, then similarity, and when two records score equally, participating records rank higher. That tiebreaker is a small piece of domain thinking: under queue pressure, show the record that can actually be admitted.

One implementation note that saved us real grief: the name normalization code is shared between the browser-side importer and the server, and the module is kept dependency-free specifically so it can run in both. The normalization that built the search index is character-for-character the same one that runs on every query. Two slightly different normalizers is a bug class you do not want to discover at a gate.

A person is not a policy

The source data is policy-shaped. A queue is people-shaped. Someone holding four policies would naively appear as four search results, and a staff member under pressure would check in whichever one they happened to tap. So results are merged into one card per person, keyed on client number where available, and the card then pulls in that person's other policies even when only one of them matched the query, marked as unmatched. The card is complete regardless of what was typed. Identity details are filled in from whichever row happens to carry them, because the data is inconsistent about which record holds the date of birth or the phone number.

The same distinction shows up somewhere more dangerous: participation. The voting register arrives keyed by policy, but a member who holds several policies is one person, and only one of their policies may appear on the register. Resolve participation per-policy and you get the single worst failure this app could produce: a member arrives, presents the policy number they happen to remember, and is shown a red do-not-admit card while a different policy of theirs sits on the register two rows away. So participation is recomputed at the person level after every import, fanning the register out across the full set of each member's policy records. Whichever policy number a member presents, the app finds them.

Designing for the half-loaded state

Two files seed the app, and someone will inevitably upload them in the wrong order, or only one of them, twenty minutes before doors open. So the participation flag is recomputed after every import, which makes the two uploads order-independent. And there is a safety net in search: if the register is loaded but the policy book is missing, participants with no matching policy row are still findable through a separate fallback query. The half-loaded state is treated as a normal state the app works in.

A 21MB file versus a 4.5MB request limit

The full policy extract is a 21MB workbook. The hosting platform caps request bodies at 4.5MB. The obvious answers, moving hosts, adding storage infrastructure, or shrinking the file, all add moving parts to a tool that gets used intensively one day a year. Instead, the file is parsed in the browser and streamed to the server in batches of one to two thousand rows, with a progress indicator.

The import is careful about the boring things, because the boring things are what fail on event day: file kind auto-detected from headers, preview and validation before commit, upserts keyed on company plus policy number so re-uploading a newer file is safe, duplicates within a file reported rather than silently dropped, and row-level errors accumulated into a reviewable, downloadable list with an import history.

An import is an operational event, not a file upload

It is tempting to treat spreadsheet import as plumbing: accept a file, loop over rows, show a success toast. At a gate, that design leaves the operator with no answer to the questions that matter. Which extract is loaded? Did every row make it? Can I upload a corrected copy? Did the second file change who is participating? The import history and error file exist to answer those questions without opening a database or calling the developer.

Each batch records its kind and outcome, while rejected rows retain enough context to be corrected at the source. The policy rows are upserted rather than appended, so repeating an import is a supported recovery action, not a duplication hazard. Participation is recomputed only after the committed rows are available. That sequence keeps search from observing a half-applied rule change inside a batch, while the separate fallback query deals with the larger, intentional half-loaded state between the two workbooks.

We also kept the normalizer shared between import and search because imports create more than records; they create the representation search depends on. If the browser strips punctuation one way and the server strips it another, the source data can be present and still be undiscoverable. Treating normalization as part of the data contract made that mismatch structurally harder to introduce.

Self-service check-in without exposing names

A QR code lets arrivals start check-in themselves from their own phone. The constraint is obvious once stated: that page is unauthenticated, so it cannot confirm a match by displaying a name, or the AGM queue becomes a name-lookup service for anyone who scans the code. The answer is a masking function. Each name part is reduced to its first letter plus asterisks, so a person can recognise their own record while nobody can harvest names from the page. Self check-ins land in a pending queue that staff confirm, so the QR path speeds the queue up while verification stays with a person.

Scope creep that was actually scope discovery

The commit sequence tells an honest story about an event tool meeting its event, all within a single day of build: the gate app, then person-merged cards, then a responsive header because staff are on phones, then forced light mode because a dark-themed card at an outdoor gate in daylight is unreadable, then QR self check-in, then a gift and snack station with duplicate-pickup prevention. That last one is mundane and real. At an AGM, people collect a gift and refreshments, and some will try twice. The system tracks pickups per person per kind, and it counts blocked attempts rather than silently refusing, because somebody wanted to know how often it happened.

One card, one round trip, one decision

The search result is not a link to a record detail page. It is the working surface. A candidate-match query identifies the person, then lateral joins assemble participant linkage, active check-in, pending self check-in and pickup status in the same round trip. The staff member gets one stable card with the identity panel and the next valid action. There is no moment where the name has loaded but the already-checked-in warning has not.

That also shaped the undo model. Check-in is reversible, but an undo does not delete the original action. The active state is represented by the absence of an undo timestamp; reversing it adds the timestamp and actor to the history. The operational need, let staff recover from a mistaken tap immediately, and the governance need, preserve who did what, do not have to compete when the state model admits both.

We unit-tested header detection, row mapping, normalization and the participation recomputation because those are the transformations most capable of making a correct source file produce a wrong screen. The event-day interface was then tested in its real shape: a phone, in daylight, with a person waiting. That is how forced light mode became a requirement. A component can be correct in a desktop browser and unusable at the gate it was built for.

What we left out

• No editing of policy data in the app. If the data is wrong, fix it at the source and re-upload. The app is a consumer of the pipeline, and the pipeline stays the single place where the truth lives.
• No public pages beyond the masked self check-in.
• No offline mode. The failure it guards against was judged less likely than the bugs it would introduce, and the venue has connectivity.

The stack, for completeness: Next.js with TypeScript, PostgreSQL with the pg_trgm extension, Drizzle ORM, and a signed-cookie session with two roles: admin, and staff who can search and check in but nothing else. The full card is assembled in one round trip, using a candidate-match query followed by lateral joins for participant linkage, active check-in, pending self check-in, and each pickup kind.

The takeaway

The hard analytical work, deciding who qualifies to vote, was finished before this app existed, and finishing it changed nothing at the gate. The distance between a correct dataset and a usable outcome was one small application: a search box that forgives misspellings, a card that thinks in people instead of policies, and a red screen that explains the refusal. If your pipeline ends in a workbook someone prints, it probably hasn't ended.