ASGC OS

ASGC is a 15-member student government with weekly office-hour requirements. Before ASGC OS, our Director of Board Affairs tallied those hours by hand, which took about 5 hours every week.

Office hours were only one piece. Meetings, agenda deadlines, funding requests and club paperwork were scattered, so it was hard to see who owned what, and nothing kept a trail of who changed a record. The data was sensitive too: budgets, receipts, private contact details and attendance.

I built ASGC OS, one invite-only app for the whole board. Members check in to office hours from their phones with a location check and a selfie, and the app reminds them before shifts and when they fall behind on hours. For any week, admins can pull a report grouped by role and a CSV export that opens cleanly in Excel.

Around office hours, I built tools for the rest of the board's work: meetings with agenda deadlines, a minutes library and agenda PDFs, tasks and projects, funding requests that go to a board vote at or above a set amount, expenses, grants, and club charters with Inter-Club Council attendance and quorum. Admins also get bulk member import, term rollover, MFA resets and an audit log, and every member can review and revoke their trusted devices.

ASGC OS is a Next.js 16 app on Supabase, and the database is the authorization boundary: Postgres row-level security plus explicit grants. All 51 tables have row-level security turned on, roles are scoped to a term, and hiding a button never counts as a permission. Requests run with the member's own session wherever the database should decide what each person sees, and the service-role key never leaves the server. The diagram below follows one check-in from the phone to the audit log.

A check-in calls a Postgres function that computes the haversine distance to the office on the server, so the phone's own math never counts. Inside the radius the session opens, inside a small grace band it opens with a flag, and beyond that it is refused. The row keeps only the distance in meters and 2 flags, never raw coordinates, and a partial unique index allows one open session per member.

Audit triggers run as SECURITY DEFINER with a pinned search_path and EXECUTE revoked, so a member's action writes the audit row while an ordinary member can never read or edit the log. A trigger on Supabase's own users table enforces the invite list even when someone calls Auth directly with the public key. Admin pages require a second factor, check-in selfies open through 5-minute signed links, and the text-code rate limiter stores only HMAC-SHA256 hashes, never raw IPs or phone numbers. AI summaries of committee notes, and the tasks suggested from them, save as drafts until a person clicks Approve.

The hardest question sounded simple: when did someone actually leave? Phones pause background tabs, people close tabs, and a scheduled job can run late. Across 7 migrations I tried presence timeouts of 15, 60 and 1 minutes. A late scheduler could hand out extra minutes, and a strict timeout locked people out during the day whenever a phone paused the page.

The fix was to split the rule in two. Leaving the office area still closes a session at the next location check, but inactivity only counts after 5 PM office time, with a 15-minute window. When the server closes a stale session, it backdates the checkout to 15 minutes after the last heartbeat (never before 5 PM), so a late cron run can never add time and a late heartbeat can never revive the session. The backstop runs from GitHub Actions every 5 minutes, the shortest interval it allows.

Then I caught a quieter bug in the reports. Once automatic checkouts got their own status, the weekly rollups still counted only manual ones, so those sessions earned zero credit. One more migration fixed all 3 rollups: the member view, the admin report and the reminder queue.

The board adopted ASGC OS after one demo and ran its office hours, meetings and agendas on it. For our 15 members, weekly attendance reporting went from about 5 hours of hand-tallying to a 10-minute export.

Behind that: 116 migrations with about 22,000 lines of SQL, 123 API route handlers, 41 pages, and more than 200 unit tests that all pass. Branch protection requires lint, type checks, tests, a production build and a dependency audit to pass on every pull request, and 13 SQL smoke-check suites test the access rules. The phase ledger records all 50 phases of my original build plan as complete.

The database checks belong in CI. Today the unit tests mock Supabase and the SQL smoke checks run by hand, so I'd rebuild the schema from all 116 migrations and run the full role-by-role access matrix on every pull request.

I'd also finish the public-release checklist: publish from a clean history after a full secret scan and ask ASGC or the district to decide on a license. Then I'd measure the targets I set at the start, like a phone check-in in under 30 seconds and a weekly compliance review in under 5 minutes.