FRC_1086_Member_Portal

Overview

The FRC 1086 Member Portal is a comprehensive web application that manages the entire member lifecycle for Blue Cheese Robotics, a FIRST Robotics Competition team based in Richmond, Virginia. It replaces fragmented spreadsheets and manual processes with a unified platform covering season applications, parental consent, compliance verification, build session management, geofenced attendance tracking, and encrypted medical profiles.

Built on Next.js 14 with the App Router, the portal uses Firebase Authentication for identity management and PostgreSQL with Row-Level Security for data access enforcement. Medical data is stored in a physically separate Cloud SQL instance with field-level AES-256-GCM encryption. The system supports 9 distinct user roles, from students and parents to mentors, coaches, and admins, each with carefully scoped data access policies enforced at the database layer.

The portal is deployed as a containerized application on Google Cloud Run, with secrets managed through GCP Secret Manager and notifications delivered via Discord webhooks across three dedicated channels. It is designed mobile-first for phone-based check-in at build sessions and responsive across all form factors.

Non-Technical Summary

Blue Cheese Robotics is a high school robotics team that competes in the FIRST Robotics Competition. Managing a team of 50+ students and mentors involves a lot of paperwork: applications, parental consent forms, medical information, volunteer clearances, attendance tracking, and scheduling build sessions. Previously, all of this was handled through scattered spreadsheets and manual processes.

The Member Portal replaces all of that with a single web application. Students and mentors apply for each season through guided forms, parents sign consent documents and waivers digitally, and coaches can track who has completed their required clearances at a glance. When the team meets for build sessions, members check in on their phones, and the app verifies they're physically at the meeting location. Attendance is recorded automatically.

Medical information like allergies and dietary restrictions is handled with extra care. It's stored in a completely separate database with encryption, and only authorized adults can access it. For example, a chaperone at a competition can see a student's allergy information, but only during that specific event and only if the student's family has opted in. The system was designed with student privacy as a top priority, following educational data protection guidelines.

Quick Highlights

• Defense-in-depth security: Row-Level Security on every table, physically isolated medical database, field-level AES-256-GCM encryption, and per-access audit logging
• 9-role authorization model: Admin, HCPS coach, lead mentor, mentor, student, volunteer, parent, alumni, and applicant, each with granular database-level access policies
• Geofenced attendance: GPS-verified check-in within ~100m of session coordinates, with mentor manual override and auto-close at session end
• Full application pipeline: Multi-step season applications with parental consent workflows, digital waiver signing, and compliance verification gates
• Medical data isolation: Separate Cloud SQL instance with no foreign keys to primary database, encrypted fields, and chaperone access scoped to event duration
• Real-time Discord notifications: Three-channel webhook system for application status, session updates, and admin alerts with no sensitive data ever transmitted

Technical Breakdown

Authentication & Authorization

Firebase Authentication provides identity management with Google sign-in, email/password, and magic link support. On first login, auth/register/route.ts auto-creates a user record in PostgreSQL keyed by Firebase UID. The AuthContext React context manages client-side auth state, while verify.ts handles server-side token verification via Firebase Admin SDK. Role assignment occurs when an admin approves a season application through the application-promote.ts service.

Every API route is wrapped by apiHandler() from api-handler.ts, which handles authentication, role-based authorization checks, error normalization, and rate limiting. Authorization is enforced at two layers: the API middleware checks role membership against constants like ADMIN_ROLES, MENTOR_ROLES, and LEADERSHIP_ROLES, while PostgreSQL RLS policies provide defense-in-depth at the database layer.

Database Architecture

The primary database uses PostgreSQL 15+ on Google Cloud SQL with Row-Level Security enabled on all tables. The queryWithRLS() and transactionWithRLS() functions in db/primary.ts set PostgreSQL session variables (app.current_user_id and app.current_user_role) before every query, allowing RLS policies to enforce access control transparently. The schema spans 9 sequential migrations (001_primary_schema.sql through 009_application_profile_staging.sql) covering 20+ tables with typed enums for roles, statuses, and categories.

Sensitive PII fields are split into separate *_sensitive tables (profile_sensitive, student_season_sensitive, mentor_season_sensitive) with tighter RLS policies restricting access to admin and HCPS coach roles only. This includes home addresses, phone numbers, dates of birth, driver details, and HCPS email addresses.

Medical Data Isolation

Medical profiles are stored on a physically separate Cloud SQL instance managed by db/medical.ts. Fields including dietary restrictions, allergies, medical notes, accommodations, and emergency medications are encrypted per-field using AES-256-GCM before storage. The encryption implementation packs IV (16 bytes), auth tag (16 bytes), and ciphertext into a single base64-encoded string. Encryption keys are retrieved from GCP Secret Manager at runtime. A dedicated medical_audit_log table records every read and write with actor, action, timestamp, and context (e.g., "chaperone_event_access"). The primary database stores only a medical_form_status cross-reference with a completion boolean and chaperone sharing preference, and no foreign keys to the medical database exist.

Season Application Pipeline

Applications follow a state machine through application_status enum states: draft → submitted → pending_consent/pending_compliance → approved → active. Student applications collect contact identity, season-specific data (subteam interests, tier, school information, transportation), and trigger parental consent and waiver signing workflows. Mentor applications route through compliance verification gates for FIRST registration, HCPS volunteer clearance, and Youth Protection Program completion. The application-promote.ts service handles the approval transition, assigning the appropriate user_role and triggering Discord notifications.

Digital Waiver System

Waiver documents are versioned and stored in waiver_documents with content, version number, and active flag. Signatures in waiver_signatures are immutable records capturing the signer's typed full legal name, IP address, timestamp, and the specific document version. When a new waiver version is published, members with signatures on older versions are prompted to re-sign. The system supports photo/video release, liability, and team-specific waivers.

Build Session & Attendance

Build sessions are created by coaches or lead mentors with date, time, location, GPS coordinates, and a configurable mentor minimum (default: 2). Session status follows a flow: needs_mentors → open (when ≥2 mentors signed up) → in_progress → completed. If a mentor cancels and drops below the minimum, the session reverts to needs_mentors and students receive Discord notifications.

Attendance uses client-side Geolocation API to verify the member is within ~100m of the session coordinates via geofence.ts Haversine distance calculation. Check-out is manual or auto-closed at session end (flagged with auto_closed close reason). Mentors can manually check in members with a recorded reason and mentor ID. Duration is calculated in minutes and attendance records are retained permanently for lifetime impact reporting.

Notification System

The discord.ts service sends webhook messages to three channels: general (application updates), sessions (session status changes, mentor coverage), and admin (compliance warnings, system alerts). Payloads contain only preferred names and event/session titles, with no PII or sensitive data ever transmitted. Send history is logged in notification_log for audit purposes.

Frontend Architecture

The Next.js 14 App Router organizes pages into two route groups: (auth) for login (no sidebar) and (portal) for all authenticated pages (with sidebar navigation). The component library uses 49 shadcn/ui components with Tailwind CSS and brand colors (#2a33b7 Blue, #f9e639 Yellow). Custom hooks useApi and usePaginatedApi provide SWR-style data fetching. Form validation uses Zod schemas from validations.ts. The layout is mobile-first, optimized for phone-based check-in at build sessions.

Systems Used

• Row-Level Security Authorization: PostgreSQL session variables (app.current_user_id, app.current_user_role) enforce role-based access at the database layer across all 20+ tables, with policies for admin, coach, mentor, student, parent, and alumni access tiers.
• Medical Data Isolation Architecture: Physically separate Cloud SQL instance with field-level AES-256-GCM encryption (IV + auth tag + ciphertext packed as base64), per-access audit logging, and no foreign key references from the primary database.
• Geofenced Attendance System: Client-side Geolocation API with Haversine distance calculation verifying proximity within configurable radius of session GPS coordinates, with mentor manual override and auto-close fallbacks.
• Season Application Pipeline: State machine workflow progressing through draft, submitted, pending_consent, pending_compliance, approved, and active states with role-appropriate gates and Discord notifications at each transition.
• Compliance Verification Framework: Tracks FIRST registration (self-reported → mentor-verified), HCPS volunteer clearance (coach-managed with expiration dates), and Youth Protection Program status per mentor per season.
• Digital Waiver System: Versioned documents with immutable typed-name signatures recording IP address and timestamp, automatic re-signature prompts when new waiver versions are published.
• Discord Webhook Notification Service: Three-channel architecture (general, sessions, admin) with PII-free payloads, send history logging, and notification types covering application status, session updates, compliance warnings, and consent reminders.
• Firebase Authentication Integration: Auto-registration on first Firebase login, role assignment on application approval, support for Google sign-in, email/password, and magic link authentication methods.
• Standardized API Handler Middleware: apiHandler() wrapper providing authentication verification, role-based authorization, RLS context injection, error normalization, and rate limiting for all API routes.
• Database Migration System: Sequential SQL migration runner (migrate.js) with status tracking, supporting both local PostgreSQL and Cloud SQL environments, integrated into dev server startup.

Impact & Results

• Consolidated 5 manual workflows (applications, consent, compliance, attendance, medical) into a single platform, eliminating spreadsheet tracking for a 50+ member team
• Automated compliance monitoring for mentor clearances across 3 verification types (FIRST registration, HCPS clearance, YPP), with expiration tracking and Discord alerts
• Built enterprise-grade data protection for minor student data: isolated medical database, field-level encryption, RLS on every table, and comprehensive audit logging, aligned with FERPA requirements
• Enabled real-time session management across distributed build locations with geofenced check-in, automatic mentor coverage tracking, and instant Discord notifications when session status changes
• Reduced administrative overhead for coaches and lead mentors by automating parental consent collection, waiver signing, and application review workflows with status-driven pipelines
• Created comprehensive governance documentation including a 12-section data governance policy and HCPS staff acknowledgment form, establishing clear data handling practices for the organization

Deep Dive

Architecture Philosophy

The portal was designed with a defense-in-depth security model appropriate for handling minor student data. Rather than relying solely on application-level authorization checks, the system enforces access control at multiple layers: Firebase Authentication for identity, API middleware for role-based routing, and PostgreSQL Row-Level Security for data-level enforcement. This means even if application code has a bug that bypasses a check, the database itself will refuse unauthorized access.

Database Design Decisions

The decision to use PostgreSQL RLS over application-level authorization was driven by the sensitivity of the data. Session variables (app.current_user_id and app.current_user_role) are set via SET LOCAL within each transaction, ensuring they're scoped to the current query and automatically cleared. The schema uses typed enums extensively: user_role with 9 values, application_status with 8 states, session_status with 5 states, providing compile-time-like safety at the database layer.

Sensitive PII was split into separate *_sensitive tables rather than using column-level security because PostgreSQL's column-level RLS is limited. This split allows the main profile tables to be accessible to team leadership for operational purposes (roster views, session management) while restricting PII access to admin and HCPS coach roles only.

Medical Data Isolation Rationale

A separate Cloud SQL instance for medical data was chosen over schema-level isolation within the same database. This provides several guarantees: separate credentials (compromising the primary DB credentials cannot access medical data), separate backup schedules, separate audit trails in GCP Cloud Audit Logs, and the ability to apply different network policies. The encryption layer uses AES-256-GCM with per-field encryption rather than full-disk or tablespace encryption, because the threat model includes authorized database administrators who should not have access to plaintext medical data without going through the application layer.

The encryption packing format (IV + auth tag + ciphertext → base64) was chosen to be self-contained: each encrypted field carries everything needed for decryption except the key. This avoids the need for a separate IV/nonce table and simplifies field-level operations.

Application State Machine

The season application pipeline implements a directed state machine with carefully controlled transitions. The application_status enum defines valid states (draft, submitted, pending_consent, pending_compliance, approved, active, denied, withdrawn), and the API layer enforces valid transitions. For example, an application can only move from submitted to approved, denied, pending_consent, or pending_compliance. A denied application can be re-opened to submitted but an approved application can only be denied (not reverted to pending). This prevents accidental state corruption and ensures audit trail integrity.

Geofence Implementation

Attendance check-in uses the browser's Geolocation API on the client side, sending coordinates to the server which calculates Haversine distance from the session's GPS coordinates. The ~100m default radius accounts for GPS accuracy variance on mobile devices. The decision to perform geolocation client-side rather than server-side (IP geolocation) was made because IP-based location is too imprecise for building-level verification, and the team meets at multiple locations (Power Train Control Solutions, Deep Run High School, Regency Mall).

The mentor manual override system exists as a fallback for connectivity issues, as members in metal buildings or basements may have poor GPS signal. Overrides are logged with the overriding mentor's ID and a reason field, creating an audit trail that discourages misuse.

Notification Architecture

Discord was chosen as the notification channel because it's the team's existing communication platform. The three-channel separation (general, sessions, admin) prevents alert fatigue by routing notifications to audiences who need them. Webhook payloads are deliberately minimal (only preferred names and session/event titles) to prevent PII leakage if webhook URLs are ever compromised. The notification_log table records send history without storing payload content.

Deployment & Infrastructure

The Docker multi-stage build produces a minimal Alpine-based image running as a non-root user (nextjs:1001). Next.js is configured for standalone output, which bundles only the required Node.js modules. Firebase client environment variables (NEXT_PUBLIC_*) are inlined at build time, while server-side secrets (database credentials, encryption keys) are injected at runtime via Cloud Run environment variables and GCP Secret Manager references.

Cloud SQL connections use Unix sockets on Cloud Run (via the built-in Cloud SQL connector) and TCP on local development. The dev.sh script supports both modes: --local for local PostgreSQL and --proxy for Cloud SQL Auth Proxy access to production data.

vv1 — v1.0 — Core Platform

vv1 Overview

The FRC 1086 Member Portal is a comprehensive web application that manages the entire member lifecycle for Blue Cheese Robotics, a FIRST Robotics Competition team based in Richmond, Virginia. It replaces fragmented spreadsheets and manual processes with a unified platform covering season applications, parental consent, compliance verification, build session management, geofenced attendance tracking, and encrypted medical profiles.

Built on Next.js 14 with the App Router, the portal uses Firebase Authentication for identity management and PostgreSQL with Row-Level Security for data access enforcement. Medical data is stored in a physically separate Cloud SQL instance with field-level AES-256-GCM encryption. The system supports 9 distinct user roles, from students and parents to mentors, coaches, and admins, each with carefully scoped data access policies enforced at the database layer.

The portal is deployed as a containerized application on Google Cloud Run, with secrets managed through GCP Secret Manager and notifications delivered via Discord webhooks across three dedicated channels. It is designed mobile-first for phone-based check-in at build sessions and responsive across all form factors.

vv1 Technical Breakdown

Authentication & Authorization

Firebase Authentication provides identity management with Google sign-in, email/password, and magic link support. On first login, auth/register/route.ts auto-creates a user record in PostgreSQL keyed by Firebase UID. The AuthContext React context manages client-side auth state, while verify.ts handles server-side token verification via Firebase Admin SDK. Role assignment occurs when an admin approves a season application through the application-promote.ts service.

Every API route is wrapped by apiHandler() from api-handler.ts, which handles authentication, role-based authorization checks, error normalization, and rate limiting. Authorization is enforced at two layers: the API middleware checks role membership against constants like ADMIN_ROLES, MENTOR_ROLES, and LEADERSHIP_ROLES, while PostgreSQL RLS policies provide defense-in-depth at the database layer.

Database Architecture

The primary database uses PostgreSQL 15+ on Google Cloud SQL with Row-Level Security enabled on all tables. The queryWithRLS() and transactionWithRLS() functions in db/primary.ts set PostgreSQL session variables (app.current_user_id and app.current_user_role) before every query, allowing RLS policies to enforce access control transparently. The schema spans 9 sequential migrations (001_primary_schema.sql through 009_application_profile_staging.sql) covering 20+ tables with typed enums for roles, statuses, and categories.

Sensitive PII fields are split into separate *_sensitive tables (profile_sensitive, student_season_sensitive, mentor_season_sensitive) with tighter RLS policies restricting access to admin and HCPS coach roles only. This includes home addresses, phone numbers, dates of birth, driver details, and HCPS email addresses.

Medical Data Isolation

Medical profiles are stored on a physically separate Cloud SQL instance managed by db/medical.ts. Fields including dietary restrictions, allergies, medical notes, accommodations, and emergency medications are encrypted per-field using AES-256-GCM before storage. The encryption implementation packs IV (16 bytes), auth tag (16 bytes), and ciphertext into a single base64-encoded string. Encryption keys are retrieved from GCP Secret Manager at runtime. A dedicated medical_audit_log table records every read and write with actor, action, timestamp, and context (e.g., "chaperone_event_access"). The primary database stores only a medical_form_status cross-reference with a completion boolean and chaperone sharing preference, and no foreign keys to the medical database exist.

Season Application Pipeline

Applications follow a state machine through application_status enum states: draft → submitted → pending_consent/pending_compliance → approved → active. Student applications collect contact identity, season-specific data (subteam interests, tier, school information, transportation), and trigger parental consent and waiver signing workflows. Mentor applications route through compliance verification gates for FIRST registration, HCPS volunteer clearance, and Youth Protection Program completion. The application-promote.ts service handles the approval transition, assigning the appropriate user_role and triggering Discord notifications.

Digital Waiver System

Waiver documents are versioned and stored in waiver_documents with content, version number, and active flag. Signatures in waiver_signatures are immutable records capturing the signer's typed full legal name, IP address, timestamp, and the specific document version. When a new waiver version is published, members with signatures on older versions are prompted to re-sign. The system supports photo/video release, liability, and team-specific waivers.

Build Session & Attendance

Build sessions are created by coaches or lead mentors with date, time, location, GPS coordinates, and a configurable mentor minimum (default: 2). Session status follows a flow: needs_mentors → open (when ≥2 mentors signed up) → in_progress → completed. If a mentor cancels and drops below the minimum, the session reverts to needs_mentors and students receive Discord notifications.

Attendance uses client-side Geolocation API to verify the member is within ~100m of the session coordinates via geofence.ts Haversine distance calculation. Check-out is manual or auto-closed at session end (flagged with auto_closed close reason). Mentors can manually check in members with a recorded reason and mentor ID. Duration is calculated in minutes and attendance records are retained permanently for lifetime impact reporting.

Notification System

The discord.ts service sends webhook messages to three channels: general (application updates), sessions (session status changes, mentor coverage), and admin (compliance warnings, system alerts). Payloads contain only preferred names and event/session titles, with no PII or sensitive data ever transmitted. Send history is logged in notification_log for audit purposes.

Frontend Architecture

The Next.js 14 App Router organizes pages into two route groups: (auth) for login (no sidebar) and (portal) for all authenticated pages (with sidebar navigation). The component library uses 49 shadcn/ui components with Tailwind CSS and brand colors (#2a33b7 Blue, #f9e639 Yellow). Custom hooks useApi and usePaginatedApi provide SWR-style data fetching. Form validation uses Zod schemas from validations.ts. The layout is mobile-first, optimized for phone-based check-in at build sessions.