EMSC Portal

Four Apps Script systems had grown up separately, each with its own login, its own spreadsheet, and its own idea of who a committee member was. Staff re-entered the same person into three places, and nobody could answer a question that crossed two systems.

Why it was rebuilt

Four Apps Script systems had each solved a real problem, and each had solved it alone. A committee member existed three times over — in the registry, in the inventory system's borrow records, in an event's participant list — under three slightly different spellings. Nobody could answer a question that crossed two systems without opening both and reconciling by eye.

The rebuild was not motivated by the technology. It was motivated by the reconciliation work, which had quietly become somebody's weekly job.

What changed

One Postgres database with row-level security replaced four spreadsheets. A single permission model — organisation roles mapped to module-and-action pairs — replaced four separate ideas of who was allowed to do what. Staff sign in once, with a Google account or a password, and the portal decides what they can see.

Notifications moved from someone manually chasing people to scheduled digests over the LINE Messaging API, which is where the committee already talks. Email goes out through Resend when a message needs to be a record rather than a nudge.

How the permission model works

Every module declares its actions in one registry. An admin bypasses everything. Everyone else gets there through active assignments: member → organisation roles → granted (role, module, action) rows.

Pages call a guard that redirects on failure; API routes check and return 403. No route re-implements the rule, which means a permission bug is fixed in one place rather than found in six. Adding a module means adding it to the registry, seeding per-level defaults in the same migration, and writing an integration test — the module is not considered done without one.

The database workflow

Any schema change moves through four steps in order: write a migration, reset the database from scratch, regenerate the TypeScript types from the live schema, then build. Skipping the type regeneration is the single most common way to break the build, so the order is not a suggestion.

The point of the sequence is that drift between the database and the code is caught by the compiler, on my machine, rather than by a committee member hitting an error at 11pm before an event.

What I kept from the Apps Script era

Prototyping in a low-code tool was the right first move, not a mistake to correct. Apps Script let the committee use a working system within days and made the real requirements visible — which fields people actually filled in, which steps they skipped, which reports anyone ever opened. The rebuild inherited all of that instead of guessing at it.

The cost of the low-code stage was real but bounded: it stopped scaling at exactly the point where the systems needed to talk to each other.

Engineering notes

• Over 1,200 automated tests, including an integration test for every permission-gated module.
• All external input is validated with zod at the API boundary; route handlers stay thin and business logic lives in per-domain modules.
• Deployed on Vercel, with migrations applied deliberately rather than by CI — a rename migration would otherwise break production in the window between the deploy and the schema push.