FRC 1086 Team Portal

v3.0: Multi-Tenant Platform

v3.0 Highlights

• Rebuilt the team portal end-to-end on Cloudflare Pages + Workers + D1 with Vite 6, React 19, TanStack Router, Hono, Drizzle, and Better Auth, retiring the prior Next.js/Firebase/Cloud Run/PostgreSQL stack while preserving all member-lifecycle workflows and adding 52 modules under a stage-flagged release system.
• Designed and shipped a server-owned permission registry with resource:verb naming, role inheritance, and default-deny enforcement at the safeRouter layer, replacing ad-hoc tier-and-predicate gates across the API surface and exposing virtual /wiki/roles/$key and /wiki/permissions/$key pages from the registry.
• Architected a typed AUDIT_EVENTS registry covering 27 event types across five severity levels with per-event retention, cf-ray request correlation, and a single audit() helper, eliminating free-form audit strings and consolidating discord_audit_log into a coherent observability surface.
• Built a pluggable WikiPageProvider pattern with federated glossary aggregation and an AST-walked markdown decorator that auto-inserts glossary tooltips, enabling any subsystem (permissions, audit, modules, forms) to register virtual wiki pages dispatched by namespace while still allowing user-written pages to override at the same slug.
• Engineered reliability primitives including a unified ApiError shape, an idempotency_keys table for POST deduplication, a retry_queue for best-effort integrations (Discord, Workspace) with bounded exponential backoff, and a /api/health/deep probe covering downstream dependencies.
• Implemented a Discord bot at /api/discord/interactions with signed-request verification, slash commands ported from the team's previous NoctBot, OAuth identity linking, and a nightly cron job that reconciles role drift against the canonical roster, all gated behind a DISCORD_DRY_RUN env flag that audit-logs intended writes instead of executing them.
• Stood up a separate demo environment on a dedicated Cloudflare Pages project with its own D1 instances, demo outbox table for capturing side-effect calls, persona-switching via /api/demo/switch, and a lint-demo-policy script that walks safeRouter metadata to prevent demo data from leaking into production routes.
• Designed a 1Password-backed credential vault reachable through a Discord slash command, backed by a real Cloudflare Container running a Node sidecar (the 1Password SDK cannot load in the Workers runtime), minting time-boxed, email-locked share links instead of exposing secrets directly.
• Built Portal Atlas, an audience-filtered system-diagram platform rendering five Mermaid-authored views (architecture, security, lifecycle, data, operations) with per-record Verified/Declared truth-state enforcement applied server-side, not just visually.
• Cut CI wall-clock time from roughly 21 minutes to about 265 seconds by sharding a single serial quality-checks job into four parallel jobs plus a 4-way test matrix.
• Redesigned the platform as a multi-tenant product: each FIRST Robotics organization now runs its own isolated Cloudflare deployment (separate databases, secrets, and module choices) instead of the portal being hardcoded to one team, while keeping a tenant-prefixed storage contract for the few resources that are shared.
• Shipped three new admin-facing systems in the same cycle: a Policy Center for Code of Conduct acknowledgment, a 15-template admin-editable email system, and a Discord trivia game with an auto-generated question bank, growing the module registry from 46 to 52 modules.

v3.0 Quick Highlights

• Multi-tenant by deployment, not by row: each team gets its own Cloudflare Pages deployment, D1 databases, and secrets; no shared database split by a tenant column
• Policy Center, email templates, and Discord trivia: a Code of Conduct acknowledgment flow, 15 admin-editable notification templates, and a daily Discord trivia game with an auto-generated question bank
• Cloudflare-native architecture: Vite 6 + React 19 + TanStack Router SPA delivered by a Hono Worker on Cloudflare Pages advanced mode, with two Cloudflare D1 databases (primary + medical) accessed via Drizzle ORM
• 52-module registry with stage flag: Hardcoded server-owned MODULES registry (33 release + 19 alpha) with a 3-stage lifecycle (alpha/beta/release) enforced by middleware on every request, allowing incremental production rollout
• Permission system with role inheritance: Server-owned PERMISSIONS + ROLES registries with resource:verb naming, two-axis role model (membership + per-season overlay + evergreen), default-deny enforcement at the safeRouter layer
• Typed audit registry: 27 event types across five severity levels with per-event retention windows, cf-ray request correlation, and a single audit() helper replacing free-form action strings
• Simplified auth surface: Cut from five sign-in paths down to two, six-digit email OTP and magic link, removing email/password, both OAuth providers, and a Cloudflare Access integration that had gone provably inert
• Dual-boundary field encryption: AES-256-GCM on a physically isolated medical D1 and a separate encryption boundary for non-medical PII (profile_sensitive, season_sensitive, guardians) in the primary D1
• Pluggable wiki providers: Any subsystem registers a WikiPageProvider exposing virtual pages; resolver dispatches by namespace; federated glossary with AST-walked markdown decorator inserts tooltip definitions automatically
• Reliability primitives: Unified ApiError shape, idempotency keys on POST, retry queue for best-effort integrations, /api/health/deep liveness probe
• Discord bot + webhooks: Signed-interactions endpoint at /api/discord/interactions with ported NoctBot slash commands plus three-channel webhook notifications; all writes gated by DISCORD_DRY_RUN
• Demo environment with outbox dispatch: Separate Pages project, isolated D1 instances, outbox table captures side-effect calls, lint script enforces production-vs-demo data-leak prevention
• Five cron jobs: Attendance auto-close, compliance expiry/digest, parent-token cleanup, Discord role drift reconciliation, RSVP reminders, and yearly season rollover, all observable via the cron_runs table
• 1Password-backed credential vault: A real Cloudflare Container (Durable Object-bound Node sidecar) mints 24-hour email-locked share links for team credentials, reachable through a Discord slash command
• Portal Atlas diagram platform: Five audience-filtered system diagrams (architecture, security, lifecycle, data, operations) with Visual/Outline/Evidence tabs and server-side Verified/Declared truth-state enforcement per record
• Registry-driven document generator: Branded print documents and social graphics generated client-side, with generation snapshots persisted to R2

v3.0 Overview

v3.0 repositions the portal as a managed, multi-tenant platform for FIRST Robotics organizations rather than a single team's internal tool. Multi-tenancy is at the deployment level, not the database row level: there is no shared database split by a tenant_id column. Each team gets its own Cloudflare Pages deployment, hostname, portal-primary and portal-medical D1 databases, secrets, and module choices. Blue Cheese Robotics (Team 1086) is tenant #1 and the flagship tenant, not a hardcoded assumption baked into the platform. Provisioning stays operator-run: a team does not create a tenant through a public sign-up flow.

The v2.0 rebuild moves the FRC 1086 Team Portal off the Next.js / Firebase / Google Cloud Run / PostgreSQL stack and onto a fully Cloudflare-native architecture: Vite 6 building a React 19 SPA, TanStack Router for file-based routing, Zustand for client state, and a Hono app running in a Cloudflare Pages advanced-mode Worker (functions/_worker.ts). Persistence sits on two Cloudflare D1 (SQLite) databases accessed via Drizzle ORM: portal-primary for member data and portal-medical for encrypted medical profiles.

Identity moved from Firebase Authentication to Better Auth. The sign-in surface has since been simplified further: email/password, Google OAuth, Microsoft Entra OAuth, and Cloudflare Access were all removed on 2026-08-11, leaving exactly two paths, six-digit email OTP and magic link. Discord OAuth remains available for account linking only. HCPS coach acknowledgment is still enforced per session via CURRENT_HCPS_ACK_VERSION before sensitive operations.

The scope has grown to a 52-module registry (33 release + 19 alpha) with the same hardcoded 3-stage lifecycle flag (alpha | beta | release). Middleware enforces the stage at request time: alpha gates writes to admin-only, beta surfaces a one-time popup per (user, module, stageChangedAt), release is normal. New systems land at alpha and graduate via deploy. Recent additions include a Policy Center for Code of Conduct acknowledgment, an admin-editable email template system, and a Discord trivia game.

Cross-cutting concerns now live in registries rather than scattered constants. The permissions registry pairs a resource:verb naming convention with a two-axis role model (membership + per-season overlay + evergreen roles) and decides every gate at the safeRouter layer with default-deny. The audit registry types 27 event keys across five severity levels with per-event retention and correlates every row to a request via cf-ray. The modules, forms, integrations, and reports registries follow the same shape: server-owned, lint-and-test-enforced, exposed to the wiki via a pluggable WikiPageProvider interface.

The portal is deployed at portal.bluecheeserobotics.org on Cloudflare Pages. The public demo at portal.mreynolds.dev/demo now runs entirely client-side: a vite build --mode demo bundle backed by sql.js (WebAssembly SQLite) serves a fictitious team (Brass Ridge Robotics, FRC 86753) with no session and no /api calls, replacing the earlier D1-backed demo project and its seed/reset cron. The Discord bot is a signed-interactions endpoint at /api/discord/interactions with slash commands ported from the team's previous NoctBot.

The module count grew from 46 to 52 (33 release, 19 alpha) as a document generator, an audience-filtered system-diagram platform (Portal Atlas), a 1Password-backed credential vault running on a real Cloudflare Container, a Policy Center, an admin-editable email template system, and a Discord trivia game all joined the registry, alongside season-scoped sponsor and merchandise programs that ship outside it. A leadership-appointments table derives access tier from recorded terms rather than a role string, and the CI pipeline stays sharded down from roughly 21 minutes to about 265 seconds.

v3.0 Role Summary

• Served as sole engineer and platform architect for the v2.0 rebuild, owning design, implementation, and deployment end-to-end. Made the architectural call to migrate off Google Cloud Run + PostgreSQL onto a fully Cloudflare-native stack: Cloudflare Pages with advanced-mode Workers, two Cloudflare D1 databases, and Better Auth replacing Firebase. Designed and shipped four cross-cutting registries (permissions, audit, modules, wiki providers) plus the reliability primitives layer (ApiError, idempotency, retry queue, deep health probe).
• Built the file-based TanStack Router route tree, the Hono application bootstrap with safeRouter + requestId + rateLimit + requireAuth middleware chain, and all 20 feature routers. Designed the Drizzle schema for both D1 instances across 14 migrations, including the 0013_phase0_plumbing migration that lays the schema groundwork for permissions, audit redesign, modules, forms engine, account lifecycle, reports, role lifecycle, and the locality model. Wrote the Discord interactions endpoint with signature verification and ported NoctBot slash commands. Implemented the dry-run integration pattern (DISCORD_DRY_RUN) and the demo outbox dispatch.
• Set up the vitest workerd pool for Cloudflare-runtime test parity, the demo Pages project with its own wrangler.demo.toml config and dedicated D1 instances, the lint-demo-policy script that walks safeRouter metadata, and the cron-observability pattern (cron_runs table + recordCronRun wrapper + admin dashboard tile). Authored the per-module wiki content (overview/pages/instructions/definitions) for the standardized provider pattern and the federated glossary with AST-walked markdown decoration.
• Extended the platform well past the original registry with a document generator, the Portal Atlas diagram system, a credential vault backed by a real Cloudflare Container sidecar, and season-scoped sponsor and merchandise programs. Led the authentication simplification that cut the sign-in surface from five paths to two, and the leadership-appointments model that replaced role-string-based access tiers.

v3.0 Technical Breakdown

Runtime Architecture

The portal runs as a single Cloudflare Pages project in advanced mode: functions/_worker.ts is the Worker entry, and the presence of that file disables Pages file routing entirely. The Worker's fetch handler routes /api/discord/interactions to a dedicated Hono app (Discord's signed POST has no Origin header, so it bypasses the CORS chain), delegates the rest of /api/* to the main Hono app at functions/app.ts, and falls through to the Pages ASSETS binding for everything else with an SPA fallback to index.html on 404. The scheduled handler routes five cron patterns by controller.cron string and wraps each one in recordCronRun() so the admin dashboard shows per-job rows.

Hono Middleware Chain

The main app composes requestId (mirrors cf-ray or generates a UUID), cors (origins read per-request from PUBLIC_APP_URL), Better Auth pass-through, rateLimit, and requireAuth. Routes are mounted on a SafeRouter wrapper that requires every handler to declare demoPolicy metadata: the lint-demo-policy script walks this metadata to prove demo and live paths don't leak data. 60 feature routers (members, sessions, attendance, applications, compliance, policy, tickets, history, alumni, audit, admin, notifications, permissions, merch, lifecycle, roles, forms, form-engine, certifications, modules, trivia, hub, roster, profile, passkeys, provisioning, announcements, sponsors, workspace, publications, onboarding, demo, health, and more) each scope themselves to a basePath. The cfAccess router from v2.0 is gone: Cloudflare Access has no Access header, JWT, router, binding, or middleware registered anywhere in the runtime.

Permission System (Phase A1)

Permissions are decided by buildViewer() + permissionsOf() in functions/lib/permissions/decide.ts. Roles come in three classes: membership via season_members, overlay via season_members.role, and evergreen via a dedicated user_evergreen_roles table. requirePermission() middleware enforces default-deny at the safeRouter level; hasPermission() and can() helpers are available in handlers. The /api/me endpoint hydrates the client with the viewer's resolved permission set so the frontend can show/hide gates without round-tripping.

Audit System (Phase A2)

The AUDIT_EVENTS registry types every audit event with key, severity (debug/info/notice/warning/critical), retention days, and a description. The single audit() helper (used everywhere) inserts a row with request_id populated from the request context, normalizes actor identity, and is the only path into audit_log. The Discord audit log was consolidated into the same table with a discriminator column. The legacy auditAccess() helper still exists for backward compatibility and is being swept in Phase B.

MODULES Registry + Stage Flag (Phase A3)

Every visible module is declared in functions/lib/modules/registry.ts with id, label, description, stage (alpha | beta | release), stageChangedAt timestamp, owner email, and a standardized wiki block containing overview plus optional pages, instructions, and definitions. Middleware reads the stage and enforces it: alpha = reads for all + writes 403 for non-admin; beta = full access + one-time popup per (user, module, stageChangedAt) tracked in beta_acks; release = normal. The registry-shape test verifies every entry carries a wiki overview.

Wiki Content Providers (Phase A4)

The WikiPageProvider interface lives in functions/lib/wiki/. Any subsystem registers a provider exposing virtual pages by namespace; the wiki resolver dispatches by prefix (/wiki/modules/*, /wiki/permissions/*, /wiki/audit/*, /wiki/forms/*) and falls through to user-written pages when there's no provider match. A federated glossary aggregates terms from every provider, and an AST-walked markdown decorator finds glossary mentions in any rendered wiki page and wraps them in tooltip components.

Reliability Primitives (Phase A5)

The ApiError class in functions/lib/errors.ts is the canonical error shape: code, message, and HTTP status. Every API response uses it. The idempotency_keys table stores hash-deduped POST mutations keyed by Idempotency-Key header. The retry_queue table holds best-effort operations (Discord posts, Workspace provisioning) for bounded exponential-backoff retries when the integration is down. /api/health/deep probes downstream dependencies (D1, KV, Discord, Workspace) and reports per-dependency status.

Database Architecture

Two Cloudflare D1 SQLite databases. portal-primary (DB_PRIMARY) holds members, season_members, applications, sessions, attendance, audit_log, idempotency_keys, retry_queue, notification_log, discord_audit_log, cron_runs, and the form-engine tables. portal-medical (DB_MEDICAL) holds encrypted medical profiles isolated by separate credentials and migrations. Drizzle ORM provides typed queries via getPrimaryDb(env) / getMedicalDb(env). Migrations are applied with wrangler d1 migrations apply per database.

Field-Level Encryption

Two encryption boundaries, both AES-256-GCM with IV (16B) + auth tag (16B) + ciphertext packed as base64 per field. The MEDICAL_ENCRYPTION_KEY secret encrypts allergies, dietary restrictions, medications, and notes in portal-medical. The SENSITIVE_ENCRYPTION_KEY secret encrypts non-medical PII (profile_sensitive, student_season_sensitive, mentor_season_sensitive, guardians) in portal-primary. Keys are bound as Worker secrets, never co-located with data.

Auth (Email OTP + Magic Link)

Better Auth's plugins array now registers exactly emailOTP and magicLink. Email/password, Google OAuth, Microsoft Entra OAuth, and Cloudflare Access were all removed on 2026-08-11: the password path closed a server-side registration a UI change had already made unreachable the day before, neither OAuth provider had a live sign-in button left pointing at it, and Cloudflare Access had been provably inert since CF_ACCESS_TEAM_DOMAIN came unset, meaning its background probe always reported unavailable regardless of any Cloudflare-side policy. Discord OAuth is still registered but link-only: the client calls authClient.linkSocial, which requires an existing session, and a before-hook separately rejects Discord at /sign-in/social so a direct HTTP caller can't turn the integration into a hidden sign-in path. requireActivation and tierForIdentity middleware still enforce HCPS coach acknowledgment per session against CURRENT_HCPS_ACK_VERSION.

Discord Bot

The portal absorbed the team's previous standalone NoctBot. /api/discord/interactions is a signed-interactions endpoint with Ed25519 signature verification per Discord's API contract. Slash commands live in functions/lib/discordCommands.ts: /ping, /whois, plus role-linking. The OAuth flow exchanges authorization codes for identify-scope tokens; users can opt out of /whois via the discord_whois_opt_out column. A nightly cron job at 0 4 * * * calls runDiscordRoleReconciliation which diffs intended roles (computed from discord_role_map seeded in migration 0008) against actual Discord state and corrects drift. DISCORD_DRY_RUN env flag short-circuits every write to a discord_audit_log row.

Demo Environment

The teamportal-demo Pages project uses a separate wrangler.demo.toml config with isolated portal-demo-primary and portal-demo-medical D1 instances. The demo path routes side-effects (Discord, Workspace, email) through a demo_outbox table instead of executing them. Persona switching goes through /api/demo/switch. The lint-demo-policy script walks the safeRouter metadata to verify every route declares a demoPolicy and that production paths don't import demo helpers.

Frontend Architecture

React 19 SPA with TanStack Router (file-based routes under src/routes, routeTree.gen.ts regenerated at dev time). Zustand stores under src/core/stores hold cross-route state (auth, demo, member data). The design system uses Tailwind CSS 4 with semantic tokens in src/globals.css (text-status-success, bg-surface-container-low, etc.). Raw color scales are linted against. PWA support via vite-plugin-pwa, with useCredentials keeping session cookies attached to the manifest fetch; navigation fallback explicitly excludes /api/, /go/ token-redemption links, and /dev-preview/ to avoid serving a stale precached shell right after a deploy.

Document Generator

Registry-driven document generation lives in the documents feature module (alpha stage). Branded print documents render as browser-native HTML rather than a server PDF pipeline; fixed-pixel social graphics export client-side to PNG via canvas. opentype.js and the @resvg/resvg-js / @resvg/resvg-wasm pair handle font metrics and SVG-to-raster conversion respectively. Generation snapshots persist to R2, giving each document a re-downloadable history rather than a one-shot export.

Portal Atlas

A server catalog (functions/lib/diagrams/catalog.ts) drives five audience-filtered diagram views, architecture, security, lifecycle, data, and operations, each rendered through a shared viewer component with Visual, Outline, and Evidence tabs. Diagrams are authored in Mermaid and rendered through the mermaid package; a WikiPageProvider (portalAtlasProvider.ts) exposes them into the federated wiki namespace. Every record on a diagram carries a Verified or Declared truth-state badge, and the catalog strips records outside a given viewer's access server-side rather than hiding them client-side, so the same diagram degrades safely for a lower-permission viewer instead of just visually redacting nodes a determined reader could still inspect in the DOM.

Credential Vault

The /credential Discord slash command (functions/lib/discordCommands.ts, handled in functions/api/discord/interactions.ts) fronts a 1Password-backed vault with role-based reveal and an approval workflow. Since the 1Password SDK cannot load inside workerd, the actual vault calls run in a real Cloudflare Container declared in wrangler.toml (class_name = "CredentialRunner", built from services/credential-runner/Dockerfile) and reached through a Durable Object binding (CRED_RUNNER). The container runs a Node process wrapping the 1Password op CLI and mints 24-hour email-locked share links rather than returning secret values directly to Discord or the browser.

Sponsor and Merch Systems

The sponsor program (sponsorProgramCatalog.ts, sponsorShape.ts, sponsorshipPacket.ts) scopes tiers and benefits to a season via seasons.id, surfaced through an admin engagement dashboard at /app/manage/impact. Merch Studio (src/features/merch/) is a separate pipeline: Printful import automation, an approval-gated design lab built around a four-colorway system that resolves ink roles to garment hexes, and print-ready export. Neither system has a MODULES registry entry yet, unlike the document generator and Portal Atlas, which do.

Query Cache and Leadership Appointments

A TanStack Query layer now sits over the existing typed API client, clearing durable query state on sign-out and invalidating on mutation completion, closing a stale-cache class of bug that used to need a manual page refresh after bulk approvals. Separately, leadership tier now derives from a leadership_appointments table (coach-recordable terms, audit events, history backfill) instead of a role string on the member record; season-to-season carryover is bounded to reviews that actually happened, gated by a reapply deadline, and coach standing plus HCPS volunteer clearance gate the minor-PII permission cluster independently of whatever season role a mentor currently holds.

CI Pipeline

The Quality checks workflow (.github/workflows/checks.yml) splits what used to be one serial job into static-checks, type-check, build, and mutation-proof running in parallel, plus a test matrix sharded four ways (npm run test:shard) and aggregated by a final checks job. Wall-clock time dropped from roughly 21 minutes to about 265 seconds.

Multi-Tenant Deployment Model

Multi-tenancy is implemented at the deployment level, not the database row level. There is no shared database filtered by a tenant_id column. Each FIRST Robotics organization runs its own Cloudflare Pages deployment with its own hostname, portal-primary and portal-medical D1 databases, Worker secrets, and module choices. functions/lib/tenantManifest.ts defines TenantManifest, the typed shape of what a pre-provisioning intake form collects (team identity, legal entity, color seed, district configuration); it is deliberately dependency-free from the app's DB, Hono, and Worker runtime graph so scripts/generate-tenant-config.ts and other tooling can import it standalone. That script turns a completed manifest into the new deployment's wrangler.<slug>.toml and eventual teamShape.ts (TeamIdentity) runtime values. Cross-tenant sharing is the exception, not the default: the few Cloudflare KV and R2 resources that are shared infrastructure are only reachable through a generated TENANT_STORAGE_PREFIX (functions/lib/tenantStorage.ts, tenantSlug.ts). Provisioning a tenant is operator-run, not a public sign-up flow.

Public Demo: Client-Side SQLite

The public demo at portal.mreynolds.dev/demo no longer talks to a backend. npm run build:demo runs vite build --mode demo to dist-demo/; src/core/api/transportMode.ts pins the transport to 'demo' so the bundle runs entirely in-browser for a fictitious team (Brass Ridge Robotics, FRC 86753) with no session and no /api calls, backed by sql.js (WebAssembly SQLite) per visitor. A dedicated demo Worker (functions/_worker.demo.ts) serves the static bundle and answers any /demo/api/* request with 410. This replaced the earlier teamportal-demo Pages project, which ran its own isolated D1/KV/R2 bindings and an outbox-dispatch pattern; that backend, its seed/reset cron, and its demo personas were removed from the codebase once the client-side build shipped.

v3.0 Systems Used

• MODULES Registry with 3-Stage Lifecycle: Server-owned registry of 52 modules with hardcoded alpha | beta | release stage and stageChangedAt timestamp. Middleware enforces stage per request; alpha gates writes to admin, beta surfaces a one-time popup, release is normal. Every entry carries standardized wiki content (overview/pages/instructions/definitions), test-enforced.
• Permission Registry with Role-Based Decider: Two-axis role model (season membership + per-season overlay + evergreen) feeds buildViewer() + permissionsOf(). Default-deny enforcement at the safeRouter layer via requirePermission(). resource:verb naming convention. Virtual wiki pages at /wiki/roles/$key and /wiki/permissions/$key.
• Typed AUDIT_EVENTS Registry: 27 event types with severity (debug/info/notice/warning/critical), per-event retention, cf-ray request correlation. Single audit() helper. Consolidated discord_audit_log into the main audit_log via discriminator.
• Pluggable WikiPageProvider Pattern: Interface in functions/lib/wiki/. Subsystems register providers; resolver dispatches by namespace. Federated glossary aggregates terms; AST-walked markdown decorator wraps glossary mentions in tooltips. User pages override virtual at same slug.
• Reliability Primitives: Unified ApiError shape (code + message + http_status). idempotency_keys table for POST dedup. retry_queue table for best-effort integration retries with bounded exponential backoff. /api/health/deep probes downstream dependencies.
• Cloudflare D1 Dual Database: portal-primary + portal-medical SQLite databases via Drizzle ORM. preview_database_id support for vitest workerd pool and Pages preview. 14 migrations applied via wrangler d1 migrations apply.
• Dual-Boundary Field Encryption: AES-256-GCM on medical D1 (MEDICAL_ENCRYPTION_KEY) and on non-medical PII in primary D1 (SENSITIVE_ENCRYPTION_KEY). IV (16B) + auth tag (16B) + ciphertext packed as base64 per field. Keys bound as Worker secrets.
• Better Auth with Email OTP + Magic Link: Sign-in cut to two paths on 2026-08-11: six-digit email OTP (primary) and magic link (fallback). Email/password, both OAuth providers, and Cloudflare Access removed the same day, the last after a background probe confirmed it had gone provably inert. Discord OAuth remains, link-only. Per-session HCPS coach acknowledgment unchanged.
• Hono Worker on Pages Advanced Mode: functions/_worker.ts entry. /api/* to Hono, everything else to Pages ASSETS with SPA fallback. scheduled() routes five cron patterns by controller.cron string.
• SafeRouter with Demo Policy Linting: Every route declares demoPolicy metadata. lint-demo-policy script walks metadata to enforce production-vs-demo separation. Lint runs in CI.
• Discord Bot with Slash Commands: /api/discord/interactions endpoint with Ed25519 signature verification. /ping, /whois, OAuth identity linking. DISCORD_DRY_RUN gates writes. Cron job reconciles role drift nightly using seeded discord_role_map.
• Demo Environment with Outbox Dispatch: Separate Pages project, wrangler.demo.toml config, isolated D1 instances. Side-effects (Discord, Workspace, email) routed through demo_outbox table. Persona switch via /api/demo/switch.
• Cron Observability: cron_runs table + recordCronRun() wrapper records every scheduled run with start time, duration, status, records-touched, and error message. Admin dashboard tile surfaces failures.
• TanStack Router File-Based Routing: File-based routes under src/routes, routeTree.gen.ts regenerated by Vite plugin. Per-route loaders prefetch data. _portal.* share the portal shell; login.* use the bare login shell.
• Machined Blue Design System: Tailwind CSS 4 with semantic tokens in a globals.css @theme block, no raw color-scale usage. Five status levels (success/warning/error/info/neutral). No-line rule bans borders for visual sectioning in favor of tonal shifts, spacing, and shadows. Light and dark are co-equal palettes driven by a [data-color-mode] token cascade, not a dark: variant. Space Grotesk display font under the always-on flagship theme (Manrope as pre-flagship fallback) plus Inter sans. WCAG AA contrast enforced throughout.
• PWA with Service Worker: vite-plugin-pwa registers from src/main.tsx and reloads every open client when a new worker takes control. useCredentials keeps session cookies attached to the manifest fetch. Navigation fallback excludes /api/, /go/ (pre-auth Discord deep-link redemption that must always hit the network), and /dev-preview/ (avoids serving a stale precached shell right after a deploy).
• Document Generator: Registry-driven branded print documents rendered as browser-native HTML, plus fixed-pixel social graphics exported client-side to PNG via canvas. Generation snapshots persist to R2 for history and re-download.
• Portal Atlas: Server-catalog-driven diagrams across five audience-filtered views (architecture, security, lifecycle, data, operations), each with Visual/Outline/Evidence tabs. Records carry a Verified/Declared truth-state badge, and records outside a viewer's access are stripped server-side.
• Credential Vault: 1Password-backed vault with role-based reveal and approval workflow, reachable via a /credential Discord command. A real Cloudflare Container (CredentialRunner, Durable Object-bound) runs a Node + 1Password CLI sidecar, since the 1Password SDK cannot load directly in workerd. Mints 24-hour email-locked share links.
• Sponsor and Merchandise Programs: Season-scoped sponsor tiers and benefits tied to seasons.id, with an admin engagement dashboard. Merch Studio handles Printful import automation, an approval-gated design lab with a four-colorway system, and print-ready exports.
• Typed Client Query Cache: TanStack Query layered over the existing typed API client. Durable query state clears on sign-out; invalidation ties to mutation completion to close stale-cache drift that previously needed a manual refresh.
• Leadership Appointment Tier System: Leadership tier derives from a dedicated leadership_appointments table (coach-recordable terms, audit events, history backfill) instead of a role string. Season carryover is bounded to real prior reviews with a reapply deadline; coach standing and HCPS clearance gate the minor-PII permission cluster independently of season role.
• Sharded CI Pipeline: The Quality checks workflow splits static-checks, type-check, build, and mutation-proof into parallel jobs alongside a 4-way sharded test matrix, aggregated by a checks job. Cut CI wall-clock from roughly 21 minutes to about 265 seconds.
• Multi-Tenant Deployment Model: Each team is a tenant with its own Cloudflare Pages deployment, hostname, portal-primary/portal-medical D1 databases, and secrets, not a shared database split by tenant_id. functions/lib/tenantManifest.ts defines the pre-provisioning intake shape; scripts/generate-tenant-config.ts turns it into the new deployment's Wrangler config. Shared KV/R2 access goes through a generated TENANT_STORAGE_PREFIX. Provisioning stays operator-run, no public tenant sign-up.
• Policy Center: Code of Conduct acknowledgment flow where the document itself is an ordinary wiki page authored through the normal draft/review/publish flow. Signing records acknowledgment but is not a hard gate: the compliance dashboard shows it missing-until-signed without blocking portal access.
• Admin-Editable Email Templates: 15 notification templates (application, consent, compliance, event, season-rollover) editable at /developer/integrations/email-templates with an allow-listed {variable} placeholder set per template. Sign-in mail stays hardcoded. Outbound email capped at 5 per recipient per 10 minutes, with exceptions for one-time decision and capability links.
• Discord Trivia: Daily trivia round posted to Discord with per-answer buttons and a server leaderboard, drawn from an admin-reviewed question bank at /app/admin/trivia. A daily job auto-generates draft questions from recorded team history; every draft starts disabled until an admin turns it on.

v3.0 Impact Results

• Eliminated entire GCP cost center by migrating off Google Cloud Run + two Cloud SQL instances + VPC connector + Secret Manager onto Cloudflare's free-tier-friendly Pages + Workers + D1, removing the IAP tunnel dance for local development and the org-policy override workflow
• Built a 52-module portal with a server-owned MODULES registry and 3-stage lifecycle flag (alpha/beta/release), enabling incremental production rollout per feature without code changes: alpha for admin-only verification, beta for opt-in user testing, release for general availability
• Centralized authorization across the API surface with a permission registry and role-based decider, replacing dozens of ad-hoc ['admin','hcps_coach'].includes(role) gates with a default-deny requirePermission() middleware that pulls from the registry and audit-logs every decision
• Locked down audit observability with a typed AUDIT_EVENTS registry covering 27 event keys at five severity levels, cf-ray request correlation for end-to-end tracing, and per-event retention policies to keep audit_log bounded
• Hardened the reliability story with a unified ApiError shape, idempotency-key dedup on POSTs, a retry queue for best-effort integrations (Discord, Workspace) with bounded exponential backoff, and a /api/health/deep probe that covers every downstream dependency
• Doubled the encryption boundary by adding field-level AES-256-GCM encryption for non-medical PII in the primary database (SENSITIVE_ENCRYPTION_KEY) alongside the existing medical encryption (MEDICAL_ENCRYPTION_KEY), with both keys bound as Worker secrets
• Replaced an external Discord bot with an in-portal signed-interactions endpoint, retiring the standalone NoctBot service while preserving its slash commands (/ping, /whois) and adding role-drift reconciliation via a nightly cron job
• Stood up a parallel demo environment on a dedicated Pages project with its own D1 instances, outbox dispatch for side-effects, persona switching, and a lint script that walks safeRouter metadata to prevent demo and production data from crossing
• Cut the sign-in attack surface from five paths to two by removing email/password, both OAuth providers, and a Cloudflare Access integration that had gone provably inert, leaving email OTP and magic link as the only ways in
• Sharded CI from roughly 21 minutes to about 265 seconds by splitting one serial quality-checks job into four parallel jobs plus a 4-way test matrix
• Grew the module registry from 46 to 52 modules by adding a document generator, an audience-filtered diagram platform (Portal Atlas), a 1Password-backed credential vault running on a real Cloudflare Container, season-scoped sponsor and merchandise programs, a Policy Center, an admin-editable email template system, and a Discord trivia game
• Repositioned the product from single-team tool to multi-tenant platform: Blue Cheese Robotics is now tenant #1 on a deployment-isolated architecture (separate database, secrets, and config per tenant) rather than the product's hardcoded boundary, with operator-run provisioning and a public product site explaining the managed offering

v3.0 Deep Dive

Why Cloudflare?

The v1.0 portal ran on Google Cloud Run with a VPC connector reaching two private Cloud SQL instances, GCP Secret Manager for medical encryption keys, Cloud Build pipelines, and an IAP tunnel for local development against production-like data. It worked, but it cost real money on the team's nonprofit credits, required an org-policy override to allow unauthenticated Cloud Run traffic (since the policy was clamped to prevent allUsers bindings), and forced the developer to keep a jump host in us-east4-c running just to reach Cloud SQL through the tunnel. The choice to migrate to Cloudflare was driven by total cost of ownership: D1 + Pages + Workers fits inside the free tier for the team's load, eliminates the VPC connector charge, and makes npm run build && wrangler pages deploy the entire deployment story.

Pages Advanced Mode vs Pages Functions

Cloudflare Pages supports two function models: file-based Pages Functions (functions/[path].ts) and advanced mode (a single functions/_worker.ts entry that takes over everything). The portal uses advanced mode because Pages Functions doesn't support a scheduled() handler: cron jobs on file-routed Pages projects fire against a no-op handler. By owning the Worker entry directly, the portal gets file-based routing for assets (via the ASSETS binding) plus a fully featured Worker for API and cron, all in a single project. Cron patterns are configured per project in the Pages dashboard, not in wrangler.toml (Pages rejects [triggers] in the config file).

SafeRouter and the Demo Policy Lint

Every route handler attaches to a SafeRouter (a Hono wrapper) that requires demoPolicy metadata at declaration time: 'read', 'write', 'mutate-with-outbox', or 'demo-only'. A custom lint script (scripts/lint-demo-policy.ts) walks every router export, asserts every route has a policy, and verifies that demo-only routes don't appear under production-only routers and vice versa. This catches the entire class of bug where a developer might accidentally call discord.sendWebhook() from a route that the demo persona switcher can hit, which would leak demo events into a real Discord channel.

Better Auth Migration Decisions

Better Auth was chosen over Lucia and Clerk because it runs cleanly in the Cloudflare Workers runtime without a separate backend and exposes a typed Drizzle adapter for D1. The baseURL is set per-environment from PUBLIC_APP_URL (Better Auth needs an absolute URL to resolve redirects deterministically). The sign-in surface itself has since been cut down hard: email/password, Google OAuth, Microsoft Entra OAuth, and TOTP MFA are all gone as of 2026-08-11, leaving email OTP and magic link as the only two paths. The password removal closed a server-side registration a UI change had already made unreachable the day before; neither OAuth provider had a live button pointing at it by the time its server registration went too. What stayed: Discord OAuth, but link-only, and a tenant-pinning guard that used to protect the Microsoft branch against the nOAuth class of vulnerability (an attacker-controlled Entra tenant minting an id_token with an arbitrary, unverified email claim, which Better Auth's implicit account-linking would otherwise trust) is kept in the account-linking allow-list as general-purpose protection for any social provider added later, Discord included, were it ever to gain a sign-in path rather than just a link path.

Why Cloudflare Access Came Out

Cloudflare Access used to gate the admin shell as defense-in-depth: a background probe on the login page checked a CF-Access-protected endpoint and conditionally rendered a "Continue with Cloudflare Access" button for enrolled users. It was removed 2026-08-11 after the probe was found to be provably inert: CF_ACCESS_TEAM_DOMAIN had come unset when CF Access came off the hostname, so the probe always reported unavailable no matter what policy state existed on Cloudflare's side. Rather than fix a defense-in-depth layer nobody could reach, the team cut it, along with the rest of the now-narrowed sign-in surface.

D1 Sharding Strategy

Medical data lives in a separate D1 (portal-medical) for the same reasons it lived in a separate Cloud SQL instance in v1.0: separate credentials (compromising the primary binding cannot access medical data), separate migration history, separate backup cadence. No foreign keys cross the boundary; the primary database stores only a medical_form_status cross-reference with a completion boolean and chaperone sharing preference. The two databases are bound separately as DB_PRIMARY and DB_MEDICAL on the Worker, and the encryption keys are also distinct (MEDICAL_ENCRYPTION_KEY for medical, SENSITIVE_ENCRYPTION_KEY for non-medical PII).

Stage Flag Rollout Pattern

The alpha | beta | release flag isn't just metadata; it's enforced by middleware that runs before any route handler. When a non-admin user POSTs to an alpha module, the middleware short-circuits with a 403 and audits the attempt. When a user GETs a beta module they haven't acknowledged, the middleware injects a header telling the frontend to render the one-time popup; once dismissed (recorded in beta_acks keyed on stageChangedAt), they get full access. Bumping stageChangedAt re-fires the popup on the next stage change, so successive promotions can each carry their own opt-in messaging.

Wiki Provider Pattern

The WikiPageProvider interface deliberately mirrors how the modules and permissions registries already expose wiki blocks. The resolver dispatches by namespace prefix (/wiki/modules/$id, /wiki/permissions/$key, /wiki/roles/$key, /wiki/audit/$event, /wiki/forms/$key) and falls back to user-written pages from the wiki_pages table only if no provider matches. User pages can override a virtual page at the same slug if a contributor wants to hand-author a richer version. The federated glossary is built by gathering definitions from every provider plus an AST-walked pass over user wiki pages that detects glossary terms in body content and wraps them in tooltip components.

Reliability Failure Modes

The retry queue is for best-effort operations only (Discord posts, Workspace provisioning calls, outbound webhooks). It is explicitly not for atomic operations that need transactional guarantees. The pattern: a handler executes its primary action, then enqueues integration side-effects to retry_queue with a next_run_at timestamp. A cron job (or in-request flush if the queue is short) drains the queue with bounded exponential backoff. Idempotency keys cover the orthogonal failure mode where the client retries a POST after a timeout: the handler hashes the request body and looks up idempotency_keys to decide whether to replay the previous response or execute fresh.

Why Per-Tenant Deployment, Not a Shared Database

The multi-tenant pivot could have been built the conventional SaaS way: one Worker, one set of D1 databases, a tenant_id column on every table, and row-level filtering in every query. That was rejected. A single shared database means one compromised binding or one missed WHERE tenant_id = ? clause exposes every team's medical records and guardian PII at once, and the existing encryption boundaries (MEDICAL_ENCRYPTION_KEY, SENSITIVE_ENCRYPTION_KEY) are Worker secrets scoped per deployment, not per row. Tenant-as-deployment keeps the blast radius of any one tenant's compromise, misconfiguration, or data request to that tenant's own deployment: its own portal-primary and portal-medical D1 instances, its own secrets, its own hostname. The cost is operational: every new tenant is a new Cloudflare Pages project provisioned by hand from a TenantManifest intake shape, not a row inserted into a customers table. scripts/generate-tenant-config.ts turns that manifest into the new deployment's wrangler.<slug>.toml, and the few resources Cloudflare doesn't let you fully isolate per-project (shared KV namespaces, shared R2 buckets) are reachable only through a generated TENANT_STORAGE_PREFIX, never by a bare key.

Why the Public Demo Moved Client-Side

The original demo ran on a second Cloudflare Pages project (teamportal-demo) with its own D1/KV/R2 bindings and an outbox table that captured side-effect calls instead of executing them. That was real infrastructure to provision, secure, and reset on a cron. Once the product needed a public, no-signup demo reachable from the marketing site, running a second backend deployment per visitor stopped making sense. The demo now builds as a separate Vite mode (vite build --mode demo to dist-demo/) that pins its transport to an in-browser sql.js (WebAssembly SQLite) database: no session, no /api calls, and a self-destructing service worker that evicts whatever the origin served in its full-portal era. A dedicated Worker route at /demo serves the bundle and answers any stray /demo/api/* request with 410. The tradeoff is that the demo can only simulate client-observable behavior, not real server-side effects like Discord delivery, but it dropped the demo's infrastructure footprint to zero and removed an entire class of demo-data-leaking-into-production risk that the old lint-demo-policy script existed specifically to catch.

What's Next

The legacy auditAccess() helper in functions/lib/authz.ts still exists alongside the newer audit() + AUDIT_EVENTS registry; the Phase B sweep to retire it hasn't landed. requirePermission() adoption has grown but is not yet universal. The forms-engine, a skeleton at the time of the last sync, is now built: a 19-field-type catalog with JSONLogic conditional logic, R2 attachments, and external-recipient magic links for guardian consent. A reports system now exists at functions/lib/reports as well, alongside the newer document generator, Portal Atlas, and credential vault work described above.

---

v2.0: Cloudflare Rebuild

v2.0 Highlights

• Rebuilt the team portal end-to-end on Cloudflare Pages + Workers + D1 with Vite 6, React 19, TanStack Router, Hono, Drizzle, and Better Auth, retiring the prior Next.js/Firebase/Cloud Run/PostgreSQL stack while preserving all member-lifecycle workflows and adding 46 modules under a stage-flagged release system.
• Designed and shipped a server-owned permission registry with resource:verb naming, role inheritance, and default-deny enforcement at the safeRouter layer, replacing ad-hoc tier-and-predicate gates across the API surface and exposing virtual /wiki/roles/$key and /wiki/permissions/$key pages from the registry.
• Architected a typed AUDIT_EVENTS registry covering 27 event types across five severity levels with per-event retention, cf-ray request correlation, and a single audit() helper, eliminating free-form audit strings and consolidating discord_audit_log into a coherent observability surface.
• Built a pluggable WikiPageProvider pattern with federated glossary aggregation and an AST-walked markdown decorator that auto-inserts glossary tooltips, enabling any subsystem (permissions, audit, modules, forms) to register virtual wiki pages dispatched by namespace while still allowing user-written pages to override at the same slug.
• Engineered reliability primitives including a unified ApiError shape, an idempotency_keys table for POST deduplication, a retry_queue for best-effort integrations (Discord, Workspace) with bounded exponential backoff, and a /api/health/deep probe covering downstream dependencies.
• Implemented a Discord bot at /api/discord/interactions with signed-request verification, slash commands ported from the team's previous NoctBot, OAuth identity linking, and a nightly cron job that reconciles role drift against the canonical roster, all gated behind a DISCORD_DRY_RUN env flag that audit-logs intended writes instead of executing them.
• Stood up a separate demo environment on a dedicated Cloudflare Pages project with its own D1 instances, demo outbox table for capturing side-effect calls, persona-switching via /api/demo/switch, and a lint-demo-policy script that walks safeRouter metadata to prevent demo data from leaking into production routes.
• Designed a 1Password-backed credential vault reachable through a Discord slash command, backed by a real Cloudflare Container running a Node sidecar (the 1Password SDK cannot load in the Workers runtime), minting time-boxed, email-locked share links instead of exposing secrets directly.
• Built Portal Atlas, an audience-filtered system-diagram platform rendering five Mermaid-authored views (architecture, security, lifecycle, data, operations) with per-record Verified/Declared truth-state enforcement applied server-side, not just visually.
• Cut CI wall-clock time from roughly 21 minutes to about 265 seconds by sharding a single serial quality-checks job into four parallel jobs plus a 4-way test matrix.

v2.0 Quick Highlights

• Cloudflare-native architecture: Vite 6 + React 19 + TanStack Router SPA delivered by a Hono Worker on Cloudflare Pages advanced mode, with two Cloudflare D1 databases (primary + medical) accessed via Drizzle ORM
• 46-module registry with stage flag: Hardcoded server-owned MODULES registry (25 release + 21 alpha) with a 3-stage lifecycle (alpha/beta/release) enforced by middleware on every request, allowing incremental production rollout
• Permission system with role inheritance: Server-owned PERMISSIONS + ROLES registries with resource:verb naming, two-axis role model (membership + per-season overlay + evergreen), default-deny enforcement at the safeRouter layer
• Typed audit registry: 27 event types across five severity levels with per-event retention windows, cf-ray request correlation, and a single audit() helper replacing free-form action strings
• Simplified auth surface: Cut from five sign-in paths down to two, six-digit email OTP and magic link, removing email/password, both OAuth providers, and a Cloudflare Access integration that had gone provably inert
• Dual-boundary field encryption: AES-256-GCM on a physically isolated medical D1 and a separate encryption boundary for non-medical PII (profile_sensitive, season_sensitive, guardians) in the primary D1
• Pluggable wiki providers: Any subsystem registers a WikiPageProvider exposing virtual pages; resolver dispatches by namespace; federated glossary with AST-walked markdown decorator inserts tooltip definitions automatically
• Reliability primitives: Unified ApiError shape, idempotency keys on POST, retry queue for best-effort integrations, /api/health/deep liveness probe
• Discord bot + webhooks: Signed-interactions endpoint at /api/discord/interactions with ported NoctBot slash commands plus three-channel webhook notifications; all writes gated by DISCORD_DRY_RUN
• Demo environment with outbox dispatch: Separate Pages project, isolated D1 instances, outbox table captures side-effect calls, lint script enforces production-vs-demo data-leak prevention
• Five cron jobs: Attendance auto-close, compliance expiry/digest, parent-token cleanup, Discord role drift reconciliation, RSVP reminders, and yearly season rollover, all observable via the cron_runs table
• 1Password-backed credential vault: A real Cloudflare Container (Durable Object-bound Node sidecar) mints 24-hour email-locked share links for team credentials, reachable through a Discord slash command
• Portal Atlas diagram platform: Five audience-filtered system diagrams (architecture, security, lifecycle, data, operations) with Visual/Outline/Evidence tabs and server-side Verified/Declared truth-state enforcement per record
• Registry-driven document generator: Branded print documents and social graphics generated client-side, with generation snapshots persisted to R2

v2.0 Overview

The v2.0 rebuild moves the FRC 1086 Team Portal off the Next.js / Firebase / Google Cloud Run / PostgreSQL stack and onto a fully Cloudflare-native architecture: Vite 6 building a React 19 SPA, TanStack Router for file-based routing, Zustand for client state, and a Hono app running in a Cloudflare Pages advanced-mode Worker (functions/_worker.ts). Persistence sits on two Cloudflare D1 (SQLite) databases accessed via Drizzle ORM: portal-primary for member data and portal-medical for encrypted medical profiles.

Identity moved from Firebase Authentication to Better Auth. The sign-in surface has since been simplified further: email/password, Google OAuth, Microsoft Entra OAuth, and Cloudflare Access were all removed on 2026-08-11, leaving exactly two paths, six-digit email OTP and magic link. Discord OAuth remains available for account linking only. HCPS coach acknowledgment is still enforced per session via CURRENT_HCPS_ACK_VERSION before sensitive operations.

The scope expanded from five workflows to a 46-module registry (25 release + 21 alpha) with a hardcoded 3-stage lifecycle flag (alpha | beta | release). Middleware enforces the stage at request time: alpha gates writes to admin-only, beta surfaces a one-time popup per (user, module, stageChangedAt), release is normal. New systems land at alpha and graduate via deploy.

Cross-cutting concerns now live in registries rather than scattered constants. The permissions registry pairs a resource:verb naming convention with a two-axis role model (membership + per-season overlay + evergreen roles) and decides every gate at the safeRouter layer with default-deny. The audit registry types 27 event keys across five severity levels with per-event retention and correlates every row to a request via cf-ray. The modules, forms, integrations, and reports registries follow the same shape: server-owned, lint-and-test-enforced, exposed to the wiki via a pluggable WikiPageProvider interface.

The portal is deployed at portal.bluecheeserobotics.org on Cloudflare Pages, with a parallel teamportal-demo Pages project carrying isolated D1 instances and an outbox dispatch pattern that captures Discord, Workspace, and email side-effects instead of executing them. The Discord bot is a signed-interactions endpoint at /api/discord/interactions with slash commands ported from the team's previous NoctBot.

The module count has held at 46 (25 release, 21 alpha) even as the underlying mix shifted: a document generator, an audience-filtered system-diagram platform (Portal Atlas), and a 1Password-backed credential vault running on a real Cloudflare Container joined the registry, alongside season-scoped sponsor and merchandise programs that ship outside it. A leadership-appointments table now derives access tier from recorded terms rather than a role string, and the CI pipeline was sharded down from roughly 21 minutes to about 265 seconds.

v2.0 Role Summary

• Served as sole engineer and platform architect for the v2.0 rebuild, owning design, implementation, and deployment end-to-end. Made the architectural call to migrate off Google Cloud Run + PostgreSQL onto a fully Cloudflare-native stack: Cloudflare Pages with advanced-mode Workers, two Cloudflare D1 databases, and Better Auth replacing Firebase. Designed and shipped four cross-cutting registries (permissions, audit, modules, wiki providers) plus the reliability primitives layer (ApiError, idempotency, retry queue, deep health probe).
• Built the file-based TanStack Router route tree, the Hono application bootstrap with safeRouter + requestId + rateLimit + requireAuth middleware chain, and all 20 feature routers. Designed the Drizzle schema for both D1 instances across 14 migrations, including the 0013_phase0_plumbing migration that lays the schema groundwork for permissions, audit redesign, modules, forms engine, account lifecycle, reports, role lifecycle, and the locality model. Wrote the Discord interactions endpoint with signature verification and ported NoctBot slash commands. Implemented the dry-run integration pattern (DISCORD_DRY_RUN) and the demo outbox dispatch.
• Set up the vitest workerd pool for Cloudflare-runtime test parity, the demo Pages project with its own wrangler.demo.toml config and dedicated D1 instances, the lint-demo-policy script that walks safeRouter metadata, and the cron-observability pattern (cron_runs table + recordCronRun wrapper + admin dashboard tile). Authored the per-module wiki content (overview/pages/instructions/definitions) for the standardized provider pattern and the federated glossary with AST-walked markdown decoration.
• Extended the platform well past the original registry with a document generator, the Portal Atlas diagram system, a credential vault backed by a real Cloudflare Container sidecar, and season-scoped sponsor and merchandise programs. Led the authentication simplification that cut the sign-in surface from five paths to two, and the leadership-appointments model that replaced role-string-based access tiers.

v2.0 Technical Breakdown

Runtime Architecture

The portal runs as a single Cloudflare Pages project in advanced mode: functions/_worker.ts is the Worker entry, and the presence of that file disables Pages file routing entirely. The Worker's fetch handler routes /api/discord/interactions to a dedicated Hono app (Discord's signed POST has no Origin header, so it bypasses the CORS chain), delegates the rest of /api/* to the main Hono app at functions/app.ts, and falls through to the Pages ASSETS binding for everything else with an SPA fallback to index.html on 404. The scheduled handler routes five cron patterns by controller.cron string and wraps each one in recordCronRun() so the admin dashboard shows per-job rows.

Hono Middleware Chain

The main app composes requestId (mirrors cf-ray or generates a UUID), cors (origins read per-request from PUBLIC_APP_URL), Better Auth pass-through, rateLimit, and requireAuth. Routes are mounted on a SafeRouter wrapper that requires every handler to declare demoPolicy metadata: the lint-demo-policy script walks this metadata to prove demo and live paths don't leak data. The 20 feature routers (members, sessions, attendance, applications, compliance, tickets, history, alumni, audit, admin, seasons, discord, notifications, forms, modules, demo, cfAccess, permissions, health) each scope themselves to a basePath.

Permission System (Phase A1)

Permissions are decided by buildViewer() + permissionsOf() in functions/lib/permissions/decide.ts. Roles come in three classes: membership via season_members, overlay via season_members.role, and evergreen via a dedicated user_evergreen_roles table. requirePermission() middleware enforces default-deny at the safeRouter level; hasPermission() and can() helpers are available in handlers. The /api/me endpoint hydrates the client with the viewer's resolved permission set so the frontend can show/hide gates without round-tripping.

Audit System (Phase A2)

The AUDIT_EVENTS registry types every audit event with key, severity (debug/info/notice/warning/critical), retention days, and a description. The single audit() helper (used everywhere) inserts a row with request_id populated from the request context, normalizes actor identity, and is the only path into audit_log. The Discord audit log was consolidated into the same table with a discriminator column. The legacy auditAccess() helper still exists for backward compatibility and is being swept in Phase B.

MODULES Registry + Stage Flag (Phase A3)

Every visible module is declared in functions/lib/modules/registry.ts with id, label, description, stage (alpha | beta | release), stageChangedAt timestamp, owner email, and a standardized wiki block containing overview plus optional pages, instructions, and definitions. Middleware reads the stage and enforces it: alpha = reads for all + writes 403 for non-admin; beta = full access + one-time popup per (user, module, stageChangedAt) tracked in beta_acks; release = normal. The registry-shape test verifies every entry carries a wiki overview.

Wiki Content Providers (Phase A4)

The WikiPageProvider interface lives in functions/lib/wiki/. Any subsystem registers a provider exposing virtual pages by namespace; the wiki resolver dispatches by prefix (/wiki/modules/*, /wiki/permissions/*, /wiki/audit/*, /wiki/forms/*) and falls through to user-written pages when there's no provider match. A federated glossary aggregates terms from every provider, and an AST-walked markdown decorator finds glossary mentions in any rendered wiki page and wraps them in tooltip components.

Reliability Primitives (Phase A5)

The ApiError class in functions/lib/errors.ts is the canonical error shape: code, message, and HTTP status. Every API response uses it. The idempotency_keys table stores hash-deduped POST mutations keyed by Idempotency-Key header. The retry_queue table holds best-effort operations (Discord posts, Workspace provisioning) for bounded exponential-backoff retries when the integration is down. /api/health/deep probes downstream dependencies (D1, KV, Discord, Workspace) and reports per-dependency status.

Database Architecture

Two Cloudflare D1 SQLite databases. portal-primary (DB_PRIMARY) holds members, season_members, applications, sessions, attendance, audit_log, idempotency_keys, retry_queue, notification_log, discord_audit_log, cron_runs, and the form-engine tables. portal-medical (DB_MEDICAL) holds encrypted medical profiles isolated by separate credentials and migrations. Drizzle ORM provides typed queries via getPrimaryDb(env) / getMedicalDb(env). Migrations are applied with wrangler d1 migrations apply per database.

Field-Level Encryption

Two encryption boundaries, both AES-256-GCM with IV (16B) + auth tag (16B) + ciphertext packed as base64 per field. The MEDICAL_ENCRYPTION_KEY secret encrypts allergies, dietary restrictions, medications, and notes in portal-medical. The SENSITIVE_ENCRYPTION_KEY secret encrypts non-medical PII (profile_sensitive, student_season_sensitive, mentor_season_sensitive, guardians) in portal-primary. Keys are bound as Worker secrets, never co-located with data.

Auth (Email OTP + Magic Link)

Better Auth's plugins array now registers exactly emailOTP and magicLink. Email/password, Google OAuth, Microsoft Entra OAuth, and Cloudflare Access were all removed on 2026-08-11: the password path closed a server-side registration a UI change had already made unreachable the day before, neither OAuth provider had a live sign-in button left pointing at it, and Cloudflare Access had been provably inert since CF_ACCESS_TEAM_DOMAIN came unset, meaning its background probe always reported unavailable regardless of any Cloudflare-side policy. Discord OAuth is still registered but link-only: the client calls authClient.linkSocial, which requires an existing session, and a before-hook separately rejects Discord at /sign-in/social so a direct HTTP caller can't turn the integration into a hidden sign-in path. requireActivation and tierForIdentity middleware still enforce HCPS coach acknowledgment per session against CURRENT_HCPS_ACK_VERSION.

Discord Bot

The portal absorbed the team's previous standalone NoctBot. /api/discord/interactions is a signed-interactions endpoint with Ed25519 signature verification per Discord's API contract. Slash commands live in functions/lib/discordCommands.ts: /ping, /whois, plus role-linking. The OAuth flow exchanges authorization codes for identify-scope tokens; users can opt out of /whois via the discord_whois_opt_out column. A nightly cron job at 0 4 * * * calls runDiscordRoleReconciliation which diffs intended roles (computed from discord_role_map seeded in migration 0008) against actual Discord state and corrects drift. DISCORD_DRY_RUN env flag short-circuits every write to a discord_audit_log row.

Demo Environment

The teamportal-demo Pages project uses a separate wrangler.demo.toml config with isolated portal-demo-primary and portal-demo-medical D1 instances. The demo path routes side-effects (Discord, Workspace, email) through a demo_outbox table instead of executing them. Persona switching goes through /api/demo/switch. The lint-demo-policy script walks the safeRouter metadata to verify every route declares a demoPolicy and that production paths don't import demo helpers.

Frontend Architecture

React 19 SPA with TanStack Router (file-based routes under src/routes, routeTree.gen.ts regenerated at dev time). Zustand stores under src/core/stores hold cross-route state (auth, demo, member data). The design system uses Tailwind CSS 4 with semantic tokens in src/globals.css (text-status-success, bg-surface-container-low, etc.). Raw color scales are linted against. PWA support via vite-plugin-pwa, with useCredentials keeping session cookies attached to the manifest fetch; navigation fallback explicitly excludes /api/, /go/ token-redemption links, and /dev-preview/ to avoid serving a stale precached shell right after a deploy.

Document Generator

Registry-driven document generation lives in the documents feature module (alpha stage). Branded print documents render as browser-native HTML rather than a server PDF pipeline; fixed-pixel social graphics export client-side to PNG via canvas. opentype.js and the @resvg/resvg-js / @resvg/resvg-wasm pair handle font metrics and SVG-to-raster conversion respectively. Generation snapshots persist to R2, giving each document a re-downloadable history rather than a one-shot export.

Portal Atlas

A server catalog (functions/lib/diagrams/catalog.ts) drives five audience-filtered diagram views, architecture, security, lifecycle, data, and operations, each rendered through a shared viewer component with Visual, Outline, and Evidence tabs. Diagrams are authored in Mermaid and rendered through the mermaid package; a WikiPageProvider (portalAtlasProvider.ts) exposes them into the federated wiki namespace. Every record on a diagram carries a Verified or Declared truth-state badge, and the catalog strips records outside a given viewer's access server-side rather than hiding them client-side, so the same diagram degrades safely for a lower-permission viewer instead of just visually redacting nodes a determined reader could still inspect in the DOM.

Credential Vault

The /credential Discord slash command (functions/lib/discordCommands.ts, handled in functions/api/discord/interactions.ts) fronts a 1Password-backed vault with role-based reveal and an approval workflow. Since the 1Password SDK cannot load inside workerd, the actual vault calls run in a real Cloudflare Container declared in wrangler.toml (class_name = "CredentialRunner", built from services/credential-runner/Dockerfile) and reached through a Durable Object binding (CRED_RUNNER). The container runs a Node process wrapping the 1Password op CLI and mints 24-hour email-locked share links rather than returning secret values directly to Discord or the browser.

Sponsor and Merch Systems

The sponsor program (sponsorProgramCatalog.ts, sponsorShape.ts, sponsorshipPacket.ts) scopes tiers and benefits to a season via seasons.id, surfaced through an admin engagement dashboard at /app/manage/impact. Merch Studio (src/features/merch/) is a separate pipeline: Printful import automation, an approval-gated design lab built around a four-colorway system that resolves ink roles to garment hexes, and print-ready export. Neither system has a MODULES registry entry yet, unlike the document generator and Portal Atlas, which do.

Query Cache and Leadership Appointments

A TanStack Query layer now sits over the existing typed API client, clearing durable query state on sign-out and invalidating on mutation completion, closing a stale-cache class of bug that used to need a manual page refresh after bulk approvals. Separately, leadership tier now derives from a leadership_appointments table (coach-recordable terms, audit events, history backfill) instead of a role string on the member record; season-to-season carryover is bounded to reviews that actually happened, gated by a reapply deadline, and coach standing plus HCPS volunteer clearance gate the minor-PII permission cluster independently of whatever season role a mentor currently holds.

CI Pipeline

The Quality checks workflow (.github/workflows/checks.yml) splits what used to be one serial job into static-checks, type-check, build, and mutation-proof running in parallel, plus a test matrix sharded four ways (npm run test:shard) and aggregated by a final checks job. Wall-clock time dropped from roughly 21 minutes to about 265 seconds.

v2.0 Systems Used

• MODULES Registry with 3-Stage Lifecycle: Server-owned registry of 46 modules with hardcoded alpha | beta | release stage and stageChangedAt timestamp. Middleware enforces stage per request; alpha gates writes to admin, beta surfaces a one-time popup, release is normal. Every entry carries standardized wiki content (overview/pages/instructions/definitions), test-enforced.
• Permission Registry with Role-Based Decider: Two-axis role model (season membership + per-season overlay + evergreen) feeds buildViewer() + permissionsOf(). Default-deny enforcement at the safeRouter layer via requirePermission(). resource:verb naming convention. Virtual wiki pages at /wiki/roles/$key and /wiki/permissions/$key.
• Typed AUDIT_EVENTS Registry: 27 event types with severity (debug/info/notice/warning/critical), per-event retention, cf-ray request correlation. Single audit() helper. Consolidated discord_audit_log into the main audit_log via discriminator.
• Pluggable WikiPageProvider Pattern: Interface in functions/lib/wiki/. Subsystems register providers; resolver dispatches by namespace. Federated glossary aggregates terms; AST-walked markdown decorator wraps glossary mentions in tooltips. User pages override virtual at same slug.
• Reliability Primitives: Unified ApiError shape (code + message + http_status). idempotency_keys table for POST dedup. retry_queue table for best-effort integration retries with bounded exponential backoff. /api/health/deep probes downstream dependencies.
• Cloudflare D1 Dual Database: portal-primary + portal-medical SQLite databases via Drizzle ORM. preview_database_id support for vitest workerd pool and Pages preview. 14 migrations applied via wrangler d1 migrations apply.
• Dual-Boundary Field Encryption: AES-256-GCM on medical D1 (MEDICAL_ENCRYPTION_KEY) and on non-medical PII in primary D1 (SENSITIVE_ENCRYPTION_KEY). IV (16B) + auth tag (16B) + ciphertext packed as base64 per field. Keys bound as Worker secrets.
• Better Auth with Email OTP + Magic Link: Sign-in cut to two paths on 2026-08-11: six-digit email OTP (primary) and magic link (fallback). Email/password, both OAuth providers, and Cloudflare Access removed the same day, the last after a background probe confirmed it had gone provably inert. Discord OAuth remains, link-only. Per-session HCPS coach acknowledgment unchanged.
• Hono Worker on Pages Advanced Mode: functions/_worker.ts entry. /api/* to Hono, everything else to Pages ASSETS with SPA fallback. scheduled() routes five cron patterns by controller.cron string.
• SafeRouter with Demo Policy Linting: Every route declares demoPolicy metadata. lint-demo-policy script walks metadata to enforce production-vs-demo separation. Lint runs in CI.
• Discord Bot with Slash Commands: /api/discord/interactions endpoint with Ed25519 signature verification. /ping, /whois, OAuth identity linking. DISCORD_DRY_RUN gates writes. Cron job reconciles role drift nightly using seeded discord_role_map.
• Demo Environment with Outbox Dispatch: Separate Pages project, wrangler.demo.toml config, isolated D1 instances. Side-effects (Discord, Workspace, email) routed through demo_outbox table. Persona switch via /api/demo/switch.
• Cron Observability: cron_runs table + recordCronRun() wrapper records every scheduled run with start time, duration, status, records-touched, and error message. Admin dashboard tile surfaces failures.
• TanStack Router File-Based Routing: File-based routes under src/routes, routeTree.gen.ts regenerated by Vite plugin. Per-route loaders prefetch data. _portal.* share the portal shell; login.* use the bare login shell.
• Machined Blue Design System: Tailwind CSS 4 with semantic tokens in a globals.css @theme block, no raw color-scale usage. Five status levels (success/warning/error/info/neutral). No-line rule bans borders for visual sectioning in favor of tonal shifts, spacing, and shadows. Light and dark are co-equal palettes driven by a [data-color-mode] token cascade, not a dark: variant. Space Grotesk display font under the always-on flagship theme (Manrope as pre-flagship fallback) plus Inter sans. WCAG AA contrast enforced throughout.
• PWA with Service Worker: vite-plugin-pwa registers from src/main.tsx and reloads every open client when a new worker takes control. useCredentials keeps session cookies attached to the manifest fetch. Navigation fallback excludes /api/, /go/ (pre-auth Discord deep-link redemption that must always hit the network), and /dev-preview/ (avoids serving a stale precached shell right after a deploy).
• Document Generator: Registry-driven branded print documents rendered as browser-native HTML, plus fixed-pixel social graphics exported client-side to PNG via canvas. Generation snapshots persist to R2 for history and re-download.
• Portal Atlas: Server-catalog-driven diagrams across five audience-filtered views (architecture, security, lifecycle, data, operations), each with Visual/Outline/Evidence tabs. Records carry a Verified/Declared truth-state badge, and records outside a viewer's access are stripped server-side.
• Credential Vault: 1Password-backed vault with role-based reveal and approval workflow, reachable via a /credential Discord command. A real Cloudflare Container (CredentialRunner, Durable Object-bound) runs a Node + 1Password CLI sidecar, since the 1Password SDK cannot load directly in workerd. Mints 24-hour email-locked share links.
• Sponsor and Merchandise Programs: Season-scoped sponsor tiers and benefits tied to seasons.id, with an admin engagement dashboard. Merch Studio handles Printful import automation, an approval-gated design lab with a four-colorway system, and print-ready exports.
• Typed Client Query Cache: TanStack Query layered over the existing typed API client. Durable query state clears on sign-out; invalidation ties to mutation completion to close stale-cache drift that previously needed a manual refresh.
• Leadership Appointment Tier System: Leadership tier derives from a dedicated leadership_appointments table (coach-recordable terms, audit events, history backfill) instead of a role string. Season carryover is bounded to real prior reviews with a reapply deadline; coach standing and HCPS clearance gate the minor-PII permission cluster independently of season role.
• Sharded CI Pipeline: The Quality checks workflow splits static-checks, type-check, build, and mutation-proof into parallel jobs alongside a 4-way sharded test matrix, aggregated by a checks job. Cut CI wall-clock from roughly 21 minutes to about 265 seconds.

v2.0 Impact Results

• Eliminated entire GCP cost center by migrating off Google Cloud Run + two Cloud SQL instances + VPC connector + Secret Manager onto Cloudflare's free-tier-friendly Pages + Workers + D1, removing the IAP tunnel dance for local development and the org-policy override workflow
• Built a 46-module portal with a server-owned MODULES registry and 3-stage lifecycle flag (alpha/beta/release), enabling incremental production rollout per feature without code changes: alpha for admin-only verification, beta for opt-in user testing, release for general availability
• Centralized authorization across the API surface with a permission registry and role-based decider, replacing dozens of ad-hoc ['admin','hcps_coach'].includes(role) gates with a default-deny requirePermission() middleware that pulls from the registry and audit-logs every decision
• Locked down audit observability with a typed AUDIT_EVENTS registry covering 27 event keys at five severity levels, cf-ray request correlation for end-to-end tracing, and per-event retention policies to keep audit_log bounded
• Hardened the reliability story with a unified ApiError shape, idempotency-key dedup on POSTs, a retry queue for best-effort integrations (Discord, Workspace) with bounded exponential backoff, and a /api/health/deep probe that covers every downstream dependency
• Doubled the encryption boundary by adding field-level AES-256-GCM encryption for non-medical PII in the primary database (SENSITIVE_ENCRYPTION_KEY) alongside the existing medical encryption (MEDICAL_ENCRYPTION_KEY), with both keys bound as Worker secrets
• Replaced an external Discord bot with an in-portal signed-interactions endpoint, retiring the standalone NoctBot service while preserving its slash commands (/ping, /whois) and adding role-drift reconciliation via a nightly cron job
• Stood up a parallel demo environment on a dedicated Pages project with its own D1 instances, outbox dispatch for side-effects, persona switching, and a lint script that walks safeRouter metadata to prevent demo and production data from crossing
• Cut the sign-in attack surface from five paths to two by removing email/password, both OAuth providers, and a Cloudflare Access integration that had gone provably inert, leaving email OTP and magic link as the only ways in
• Sharded CI from roughly 21 minutes to about 265 seconds by splitting one serial quality-checks job into four parallel jobs plus a 4-way test matrix
• Extended the platform past its original scope with a document generator, an audience-filtered diagram platform (Portal Atlas), a 1Password-backed credential vault running on a real Cloudflare Container, and season-scoped sponsor and merchandise programs, without growing the module count past 46

v2.0 Deep Dive

Why Cloudflare?

The v1.0 portal ran on Google Cloud Run with a VPC connector reaching two private Cloud SQL instances, GCP Secret Manager for medical encryption keys, Cloud Build pipelines, and an IAP tunnel for local development against production-like data. It worked, but it cost real money on the team's nonprofit credits, required an org-policy override to allow unauthenticated Cloud Run traffic (since the policy was clamped to prevent allUsers bindings), and forced the developer to keep a jump host in us-east4-c running just to reach Cloud SQL through the tunnel. The choice to migrate to Cloudflare was driven by total cost of ownership: D1 + Pages + Workers fits inside the free tier for the team's load, eliminates the VPC connector charge, and makes npm run build && wrangler pages deploy the entire deployment story.

Pages Advanced Mode vs Pages Functions

Cloudflare Pages supports two function models: file-based Pages Functions (functions/[path].ts) and advanced mode (a single functions/_worker.ts entry that takes over everything). The portal uses advanced mode because Pages Functions doesn't support a scheduled() handler: cron jobs on file-routed Pages projects fire against a no-op handler. By owning the Worker entry directly, the portal gets file-based routing for assets (via the ASSETS binding) plus a fully featured Worker for API and cron, all in a single project. Cron patterns are configured per project in the Pages dashboard, not in wrangler.toml (Pages rejects [triggers] in the config file).

SafeRouter and the Demo Policy Lint

Every route handler attaches to a SafeRouter (a Hono wrapper) that requires demoPolicy metadata at declaration time: 'read', 'write', 'mutate-with-outbox', or 'demo-only'. A custom lint script (scripts/lint-demo-policy.ts) walks every router export, asserts every route has a policy, and verifies that demo-only routes don't appear under production-only routers and vice versa. This catches the entire class of bug where a developer might accidentally call discord.sendWebhook() from a route that the demo persona switcher can hit, which would leak demo events into a real Discord channel.

Better Auth Migration Decisions

Better Auth was chosen over Lucia and Clerk because it runs cleanly in the Cloudflare Workers runtime without a separate backend and exposes a typed Drizzle adapter for D1. The baseURL is set per-environment from PUBLIC_APP_URL (Better Auth needs an absolute URL to resolve redirects deterministically). The sign-in surface itself has since been cut down hard: email/password, Google OAuth, Microsoft Entra OAuth, and TOTP MFA are all gone as of 2026-08-11, leaving email OTP and magic link as the only two paths. The password removal closed a server-side registration a UI change had already made unreachable the day before; neither OAuth provider had a live button pointing at it by the time its server registration went too. What stayed: Discord OAuth, but link-only, and a tenant-pinning guard that used to protect the Microsoft branch against the nOAuth class of vulnerability (an attacker-controlled Entra tenant minting an id_token with an arbitrary, unverified email claim, which Better Auth's implicit account-linking would otherwise trust) is kept in the account-linking allow-list as general-purpose protection for any social provider added later, Discord included, were it ever to gain a sign-in path rather than just a link path.

Why Cloudflare Access Came Out

Cloudflare Access used to gate the admin shell as defense-in-depth: a background probe on the login page checked a CF-Access-protected endpoint and conditionally rendered a "Continue with Cloudflare Access" button for enrolled users. It was removed 2026-08-11 after the probe was found to be provably inert: CF_ACCESS_TEAM_DOMAIN had come unset when CF Access came off the hostname, so the probe always reported unavailable no matter what policy state existed on Cloudflare's side. Rather than fix a defense-in-depth layer nobody could reach, the team cut it, along with the rest of the now-narrowed sign-in surface.

D1 Sharding Strategy

Medical data lives in a separate D1 (portal-medical) for the same reasons it lived in a separate Cloud SQL instance in v1.0: separate credentials (compromising the primary binding cannot access medical data), separate migration history, separate backup cadence. No foreign keys cross the boundary; the primary database stores only a medical_form_status cross-reference with a completion boolean and chaperone sharing preference. The two databases are bound separately as DB_PRIMARY and DB_MEDICAL on the Worker, and the encryption keys are also distinct (MEDICAL_ENCRYPTION_KEY for medical, SENSITIVE_ENCRYPTION_KEY for non-medical PII).

Stage Flag Rollout Pattern

The alpha | beta | release flag isn't just metadata; it's enforced by middleware that runs before any route handler. When a non-admin user POSTs to an alpha module, the middleware short-circuits with a 403 and audits the attempt. When a user GETs a beta module they haven't acknowledged, the middleware injects a header telling the frontend to render the one-time popup; once dismissed (recorded in beta_acks keyed on stageChangedAt), they get full access. Bumping stageChangedAt re-fires the popup on the next stage change, so successive promotions can each carry their own opt-in messaging.

Wiki Provider Pattern

The WikiPageProvider interface deliberately mirrors how the modules and permissions registries already expose wiki blocks. The resolver dispatches by namespace prefix (/wiki/modules/$id, /wiki/permissions/$key, /wiki/roles/$key, /wiki/audit/$event, /wiki/forms/$key) and falls back to user-written pages from the wiki_pages table only if no provider matches. User pages can override a virtual page at the same slug if a contributor wants to hand-author a richer version. The federated glossary is built by gathering definitions from every provider plus an AST-walked pass over user wiki pages that detects glossary terms in body content and wraps them in tooltip components.

Reliability Failure Modes

The retry queue is for best-effort operations only (Discord posts, Workspace provisioning calls, outbound webhooks). It is explicitly not for atomic operations that need transactional guarantees. The pattern: a handler executes its primary action, then enqueues integration side-effects to retry_queue with a next_run_at timestamp. A cron job (or in-request flush if the queue is short) drains the queue with bounded exponential backoff. Idempotency keys cover the orthogonal failure mode where the client retries a POST after a timeout: the handler hashes the request body and looks up idempotency_keys to decide whether to replay the previous response or execute fresh.

What's Next

The legacy auditAccess() helper in functions/lib/authz.ts still exists alongside the newer audit() + AUDIT_EVENTS registry; the Phase B sweep to retire it hasn't landed. requirePermission() adoption has grown but is not yet universal. The forms-engine, a skeleton at the time of the last sync, is now built: a 19-field-type catalog with JSONLogic conditional logic, R2 attachments, and external-recipient magic links for guardian consent. A reports system now exists at functions/lib/reports as well, alongside the newer document generator, Portal Atlas, and credential vault work described above.

---

v1.0: Core Platform

v1.0 Highlights

• Designed and built a full-stack member lifecycle management portal for a 50+ member FIRST Robotics team using Next.js 14, Firebase Auth, and PostgreSQL with Row-Level Security, replacing manual spreadsheet-based tracking across 5 operational workflows.
• Engineered a defense-in-depth medical data architecture with a physically isolated Cloud SQL instance, field-level AES-256-GCM encryption, and per-access audit logging, ensuring FERPA-aligned handling of minor student health information.
• Architected a 9-role authorization system with PostgreSQL Row-Level Security policies enforcing data access at the database layer, covering admin, coach, mentor, student, parent, volunteer, and alumni access tiers across 20+ tables.
• Built a geofenced attendance tracking system with client-side geolocation verification, mentor manual override, auto-close logic, and Discord webhook notifications, enabling real-time session management for distributed build locations.
• Implemented a multi-step season application pipeline with parental consent workflows, digital waiver signing with immutable signatures, and mentor compliance verification tracking (FIRST registration, HCPS clearance, Youth Protection Program).

v1.0 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

v1.0 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.

v1.0 Role Summary

• Served as sole developer and system architect for the entire portal, responsible for all aspects of design, implementation, and deployment. Designed the complete database schema across 9 migrations and 20+ tables with Row-Level Security policies. Built the full Next.js application including all API routes, React components, and authentication flows. Architected the medical data isolation strategy with separate Cloud SQL instances and field-level encryption. Implemented the geofenced attendance system with client-side geolocation. Set up the Docker containerization pipeline and Cloud Run deployment configuration. Wrote the data governance policy and HCPS staff acknowledgment documents. Created the migration runner, deployment scripts, and local development tooling.

v1.0 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.

v1.0 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.

v1.0 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

v1.0 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.