Admin Console: People, Hidden Values, Data Health, and Operations

Status: planned 2026-10-12 from the owner's request: see and search users and households; deactivate or delete accounts; reset passwords; change account details such as email; link and unlink students and parents; see the values the site hides for a college and for a student's standing; see each college's data "health" by when its data was received; and, longer term, trigger reruns and approve data changes from the same place. Builds on accounts.md and household-hub.md, reads the admission-chances specs (Admission Chances: Quad's Estimate, Its Inputs, and Where It Shows), and extends the one-admin-function rule in ../supabase.md. Part of product.

Goal

One signed-in place, /admin, where the owner (and later a support person) can answer "what is going on with this family's account", fix it, and leave a record; can see what the site computes but never shows (a college's selectivity index, a student's hidden odds); can see which colleges have fresh data, which are going stale, and which the pipeline keeps failing on; and can start a rerun or approve a data change without opening GitHub.

Three rules carry through every part:

1. No new admin power on Vercel. The secret key stays out of the site (supabase.md); an admin acts through policies and functions that check is_admin(), and through one small Edge Function for the Auth calls that need the secret key, like invite-user today.
2. Every admin action is logged, and a student whose data an admin looks at sees it in "Who viewed your information", the same as when a guardian looks (accounts.md).
3. Reversible by default. Deactivate before delete; delete is the same 30-day soft delete a person gets; "delete now" and unlink are the only actions that need a typed confirmation.

Research and decisions (2026-10-12)

Who is an admin

• profiles.role_hint is set by the person on their own profile form, so it cannot carry admin rights. A new table, admins (user_id, granted_by, granted_at, revoked_at, note), holds the list. The first row is inserted in the SQL Editor (dev first, then prod); later rows come from the console's Admins page, which an existing admin uses.
• is_admin() is a security-definer function (stable, search_path = '') that returns true when auth.uid() has an unrevoked row and the session's authenticator assurance level is aal2 (auth.jwt() ->> 'aal'). Supabase Auth's TOTP MFA is free on every plan; the console's sign-in asks for the second factor (supabase.auth.mfa.challengeAndVerify) when the session is aal1, and the owner enrolls once from /admin. Recommended because an admin session can read every family's data; a leaked password alone must not be enough.
• The console itself (app/admin/**) calls requireAdmin() (lib/admin/auth.ts), which checks the same two things in the app, so a non-admin gets a 404 (not a 403: the route's existence is not advertised). Every policy and function checks is_admin() again on the database side, so the app check is a convenience, never the guard.

How admin power reaches the site without the secret key

This amends the rule in supabase.md ("no other function gets admin power") to: two functions hold the secret key, invite-user and admin-auth, each with an enumerated list of operations and its own tests; a third needs an owner decision.

What "deactivate" means

Supabase Auth has a ban (ban_duration), which refuses new sign-ins but leaves issued tokens valid until they expire (up to an hour). So deactivation is three things done together by admin-auth: ban the user (100 years), delete their rows from auth.sessions (ends refresh), and set profiles.suspended_at, which getUser() in lib/auth.ts treats as signed out (a request with a still-valid token gets the sign-in page with "This account is paused"). The person's data is untouched: a deactivated student's guardians still see everything (AP scores decision, 2026-10-11: never hide a student's data from guardians). Reactivate reverses all three.

Password reset and email change

• Email sending still waits for the new domain (backlog.md), so the console always offers Copy reset link (auth.admin.generateLink({ type: "recovery" }), landing on /auth/confirm then /account/password, exactly the invitation's path), and Send reset email only once an email provider is configured (resetPasswordForEmail). A copied link is shown once and never logged, like invitation links.
• Change email uses auth.admin.updateUserById(id, { email, email_confirm: true }): the admin is vouching for the address, so no confirmation round trip. The console shows pending invitations addressed to the old email and offers to re-point them (invitations.email), since acceptance matches on the signed-in user's address.

Hidden values: what the owner means by "school" and "ranking"

The site hides three kinds of computed values, and the console shows all three (Colleges and hidden values):

• A college's selectivity index (0–100) and each input's percentile, behind the public tier (selectivity-index.md): "never the weights, the index, or the cutoffs".
• A student's standing: the hidden odds behind each level, which inputs were used, the method's notes and ablations, and the model version (levels.md, estimate.md).
• A high school's pooled course counts, which the site never shows below three students (course-plan.md), and the GPA estimates for colleges without a reported GPA (gpa.md).

Showing them to an admin changes nothing about the trade-secret rule: the console is server-rendered behind requireAdmin(), no method code or hidden value is sent to any other browser, and the estimate still comes from the server-only endpoint (estimate.md).

Health: "when we received data"

Every stored value already carries a lineage record with its source, year, and retrieval date (data-lineage.md), each college-reported document its retrieved and extracted dates (data/college-docs.json, data/cds-records/<unit>.json), each pipeline failure its stage, reason, and last_run (college_data_failures), and each publish the fields it changed (dataset_changes). Health is read from those; nothing new is collected. The thresholds live in one file so they can be tuned.

The console

/admin, signed in, admin only, robots: noindex, and no PostHog events: the provider is not mounted under /admin (telemetry.md). Five sections in a left rail (a top strip on phones, which the console supports but does not favor):

A global search box in the rail's header takes anything: a name, an email, a household name, a college name or unit id, a high school name, a user or student id (UUID). It routes to the matching page when there is one hit and to a results list otherwise.

People

Search

admin_search_people(q, limit = 50), security definer, is_admin() checked first. Matches, case-insensitively and by trigram (pg_trgm is already installed for high schools): auth.users.email, profiles.display_name, students.display_name, households.name, invitations.email and invitations.display_name, and exact match on a UUID. Returns rows of { kind: user | student | household | invitation, id, name, email, household, role, status, created, last_sign_in }, deleted and suspended rows included and marked. Results list shows kind, name, email (the only place on the site that shows one), household, status chips (Paused, Deleting in 12 days, Invited, No account yet), and the date created.

A person: /admin/people/[user]

Top to bottom:

• Account: display name, email, email confirmed, provider(s), created, last sign-in, MFA enrolled, birth year, role hint, phone, suspended_at, deleted_at with the purge date. Each editable field has an inline edit.
• Households: every membership (role, can_edit, status, since), with the household's other members by name, each a link. Leave removes the membership (admin_unlink).
• Student records: their own (students.user_id) and the ones they manage (managed_by), each a link to the student page.
• Lists and plan: list names with item counts and the plan's stage caption; a link to open the student page. Never the list itself here; the student page shows it and logs the view.
• Notifications: notification_prefs, the digests sent, SMS consent.
• Who viewed their data: access_log for their student record, admins included.
• Actions (right column):

A deleted account shows a banner with the purge date and the Restore and Delete now actions only.

A household: /admin/households/[id]

Name, created by, created, home address (city and state only; the street is the family's), the roster as the family sees it (students, then guardians, with status), pending invitations with expiry (never the token), and the seat count. Actions:

admin_link() is the one path besides accept_invitation() that creates a membership, and the accounts spec's rule ("nobody joins a household silently") is kept by telling the household: the next time any member opens /household, a one-line notice says "Quad support added Jordan to this household on Oct 12", read from the log. An email goes too once email sending exists.

A student record: /admin/students/[id]

The record (name, grad year, phone, own account or managed by whom, household), then the student's data as the family sees it, read-only, with the chances values the family does not see:

• Numbers: the profile form's values (student_profiles.data), including AP and IB courses and scores.
• List: each list with its items, category, round, status, outcome, notes, and for each college the standing panel described under a student's standing.
• Plan: tasks by month, offers, visits, letters (metadata only, not the file).
• Snapshots: application_snapshots rows, binned inputs and hidden odds, consent state.

Opening this page writes an access_log row (viewer_id = the admin, table_name = 'admin'), and the student's "Who viewed your information" shows it as "Quad support viewed your information on Oct 12" (name rule: the admin's name is not shown to families). Actions: edit the record's name, grad year, phone (admin_update_student); Attach to an account for a managed record (pick a user with no own student record; runs the built merge_managed_student path and logs student.claim; refused with the reason when the user already has a record); Delete the record (soft delete, typed, student.delete), used when a family asks to remove a child they added by mistake.

Colleges and hidden values

A college: /admin/colleges/[unit]

Three panels over the public profile's header (name, tier chip, admit rate as cited on the site):

1. Selectivity: the index, the previous published tier and whether hysteresis held it, and a table of the inputs from selectivity-index.md: each input's raw value with its citation, its percentile among reporting colleges, its weight, and whether it was present; the trend adjustment; per pool when the college has residency rates. The one place the weights are visible.
2. GPA: what the standing model uses for this college (gpa.md): source kind (reported, bands, estimated, bounded, none), the point and range, and for an estimate the model's fit size and typical miss, plus the p25 and p75 curve the same way.
3. Derived, not shown: every derived.* field in lib/fields.ts whose registry entry is marked hidden, with value and lineage. The registry gains a hidden: true flag for fields computed for the method and never cited on a page, so this panel never needs its own list.

Below them, the college's health card, the open failures and review-queue items, and a Rerun button that opens Operations with this college filled in.

A high school: on /admin/high-schools/[id]

The pooled course counts in full (school_course_counts: course, count), with the three-student line drawn, so the owner can see why a course is or is not suggested; the school's profile extraction status (the hs-profile pilot's files) and its graduation-rate vintage. Read through an is_admin() select policy on the counts table, the only read of it that is not school_course_list().

A student's standing

On the student page, each list college has a standing panel (collapsed by default), showing what the estimate returned for this student at this college, on the server, with the method's private output (estimate.md): the level and label; the hidden odds; inputsUsed with the student's values beside the college's (GPA against its curve, the test against its range, rigor against the school's offering, rank, major); the note catalog entries that fired; the ablations (what the level would be without each input); the model version; and the baseline's three-group answer for comparison. The panel is rendered only inside requireAdmin() and reads the same estimate() function the endpoint calls, with an admin: true option that includes the private fields, which the endpoint never sets. A test checks that the public endpoint's response shape rejects those fields.

Health

Per college

A card with one row per data group, each with its newest retrieval date, its status, and what decided it:

Overall status is the worst row's, in the order Failing > Stale > Watch > Fresh. Each row names the date it is judged by and links to the document or the failure. Thresholds and the current-cycle edition are constants in lib/admin/health-rules.ts, computed by the pure collegeHealth(unit, inputs) so the tests pin them.

The overview: /admin/health

Counts by overall status and by group status (a small stat row, charts.md rules), then the table of every college: name, tier, overall, one cell per group with its date, open failures by reason code, queue items. Sort by worst first; filters by status, group, reason code, state, tier, and "on a family's list" (colleges that appear on any list, so the owner can fix what families are looking at first, the order gpa.md recommends for spending). CSV download. The page is computed once per server instance from the deployed files and the two tables, like the dataset itself, and revalidated by each deploy.

Files the deploy must carry

data/college-docs.json, data/cds-records/**/*.json, data/college-failures.json, data/review-queue.json, and data/link-issues.json are not traced into the deploy today (next.config.ts outputFileTracingIncludes); the build adds them (about 10 MB together today; measured in the build's plan and watched against the 16 MB-per-function guidance in serving-architecture.md). If they grow past that, npm run college-health writes one compact data/college-health.json in each data PR, the way college-failures.json is made, and the page reads that instead.

Operations: reruns and approvals

The owner wants to trigger reruns and approve data changes from the console, in the first build if the GitHub side is right. It is: every pipeline is already a workflow with workflow_dispatch inputs, every data change is already a PR with CI and a readable diff, and the review queue is resolved by editing one file. The console needs a way to call GitHub as itself.

The GitHub App (recommended over a PAT)

A GitHub App, "Quad console", installed on this repository only, with these permissions and no others:

The app's id, installation id, and private key go to Vercel as sensitive variables (GITHUB_APP_ID, GITHUB_APP_INSTALLATION_ID, GITHUB_APP_PRIVATE_KEY, GITHUB_REPO = raywross/college-stats). lib/github-app.ts signs a short JWT with the key, exchanges it for an installation token (valid one hour, cached in memory), and calls the REST API. Nothing else on Vercel holds GitHub credentials. Unset variables show Operations with a notice and no buttons, the way inviteFunctionConfigured() degrades.

Why an App and not the existing PAT: an installation token expires in an hour and is minted per request, so there is no long-lived token in Vercel to leak and nothing to renew every 90 days; permissions are exactly the four above; actions show as quad-console[bot] in the PR, which separates what a person did from what the console did; and a PR opened with an App token does trigger pull_request workflows, so it could later replace COLLEGE_REPORTED_TOKEN in the workflows too (an owner choice, not part of this spec). A fine-grained PAT with the same four permissions works for a first go (GITHUB_TOKEN in Vercel) and the client accepts either.

Reruns

A form per workflow, with the inputs the workflow already declares, read from the console's own table (not from GitHub, so the form stays typed):

Each dispatch (POST /repos/{repo}/actions/workflows/{file}/dispatches, ref: main) passes one extra input, request_id, which the workflows add to their run-name (one line each: run-name: ${{ github.workflow }} · ${{ inputs.request_id }}), so the console can find its run: GitHub's dispatch call returns no run id, so the page polls GET …/actions/runs?event=workflow_dispatch and matches the display title. Every dispatch is logged (ops.dispatch with the workflow, inputs, and request_id) before it is sent.

Runs lists the last 30 runs of these workflows with status, who or what started them (schedule, the console and which admin, GitHub by hand), duration, and for a pipeline run the PR it opened; each links to GitHub. A failed run shows its conclusion and the failed step's name.

Approvals

Open data PRs: pull requests on main whose head branch starts with data/ (the pipelines' prefix: data/college-reported-<run>, data/identity-refresh-<date>), with the body the workflow wrote (counts, cost, circuit-breaker status, the review-queue table) rendered as the PR shows it, the CI status, and whether auto-merge is on. Buttons: Approve and merge (PUT …/pulls/{n}/merge, squash, refused while checks are pending or failed), Hold (disables auto-merge with a comment saying who held it), Close (with a comment). The PRs that wait for a person today, a tripped circuit breaker or a run stopped early (college-reported-setup.md), are flagged at the top.

Review queue: data/review-queue.json as a table (college, template code, edition, the value read, each failed check and its detail, the document), with the fixes section 6 of the setup describes made into buttons:

• Accept the value: writes an override to data/overrides.json with source: cds and the document's citation, the mechanism sources-and-citations.md already defines.
• Reject: records the item as rejected (data/review-queue.json resolved: rejected, kept so the next run doesn't queue it again).
• Re-run discovery: clears the college's recipe in data/college-sources.json and dispatches a college run.

The first two are file changes, so the console makes them the way a person would: a branch review/<unit>-<code>-<date> from main, one commit through the Contents API with a message naming the admin and the item, a PR with a generated body and a release note of kind data, and auto-merge on. CI's checks (verify, lineage, the release-note job) run as on any PR. The console never pushes to main, never touches .github/workflows/, and the App has no Workflows permission, so it could not.

What stays on GitHub

Reading a diff, editing anything other than the two files above, changing a workflow, and the archive repository's token (setup section 9) stay where they are. The console links to GitHub everywhere a thing has a page there.

Model

One migration, supabase/migrations/2026…_admin.sql, additive, applied to dev before prod:

admins     (user_id pk references auth.users, granted_by uuid, granted_at timestamptz, revoked_at timestamptz, note text)
admin_log  (id bigint identity, admin_id uuid, at timestamptz, action text, target_kind text, target_id text,
            details jsonb, request_id text)
profiles   + suspended_at timestamptz, suspended_reason text

• is_admin(): security definer, stable; true when auth.uid() is in admins unrevoked and auth.jwt() ->> 'aal' is aal2. is_admin_any_aal()` for the MFA enrollment page only.
• Read policies named "Admins read" on: profiles, households, household_members, students, invitations (every column but token and token_hash, through a view invitations_admin), access_log, student_profiles, lists, list_items, list_notes, follows, notification_prefs, digests, sms_consents, sms_sends, plan_tasks, plan_offers, plan_visits, plan_letters (metadata; the file's storage policy is unchanged), plan_nudges, application_snapshots, school_course_counts, college_data_failures, dataset_publishes. No admin write policy on any of them: writes go through the functions.
• Functions, all security definer, search_path = '', first line if not public.is_admin() then raise exception 'admin only', each writing its admin_log row in the same transaction: admin_search_people, admin_update_profile, admin_update_student, admin_update_household, admin_link, admin_unlink, admin_claim_student, admin_delete_account, admin_restore_account, admin_delete_student, admin_close_household, admin_export, admin_log_access (the access_log write for a student page view), admin_grant, admin_revoke (an admin cannot revoke themselves; the last admin cannot be revoked), admin_note_auth_action (the log row for admin-auth operations, written by the function with the service role after the Auth call succeeds, carrying the same request_id).
• admin_log is readable by admins, written only by the functions and the service role. Nothing in it is a secret: no tokens, links, or passwords; emails appear only in auth.email rows (old and new).
• getUser() (lib/auth.ts) reads suspended_at with the profile and returns null when set; requireUser sends a suspended person to /login?paused=1, which explains and gives the support address.

The admin-auth Edge Function

supabase/functions/admin-auth/ with the invite-user layout (index.ts wiring, handler.ts pure, Node tests). verify_jwt = true; the handler also calls is_admin() with the caller's token (a second client built with the publishable key and the caller's Authorization header), so a valid JWT from a non-admin, or an admin at aal1, gets 403.

POST {SUPABASE_URL}/functions/v1/admin-auth
Authorization: Bearer <the admin's session JWT>
{ "op": "set_email" | "recovery_link" | "recovery_email" | "sign_out" | "suspend" | "unsuspend" | "purge",
  "user": "<uuid>", "request_id": "<uuid>", "email"?: "...", "reason"?: "..." }

200 { "ok": true, "link"?: "..." }      -- link only for recovery_link; shown once, never logged
400 bad_request · 401 unauthorized · 403 not_admin · 404 user_not_found · 409 email_taken · 500 internal_error

purge runs the purge plan for one user (scripts/lib/purge-accounts-plan.mts, shared, not copied) and refuses a user who is not soft-deleted. Every success writes admin_note_auth_action. The site's caller is lib/admin/auth-function.ts, mapping statuses as lib/invite-function.ts does; nothing throws into a page.

Files (planned)

Tests

• tests/admin-policies.test.mts (PGlite, like the other policy tests): a non-admin reads nothing through the new policies and gets "admin only" from every function; an admin at aal1 is refused; an admin at aal2 reads and each function writes its log row; admin_link keeps the six-seat and one-household rules and moves rather than duplicates; admin_unlink refuses the last guardian of a managed student; admin_revoke keeps the last admin; the invitations view never exposes a token. A guard test drops the is_admin() line from one function and shows the test catching it.
• tests/admin-auth-function.test.mts: every op and status with fake dependencies; a non-admin JWT; no link, email, or reason in any log call except where the spec says.
• tests/admin-health.test.mts: collegeHealth() on fixtures for each cell of the table, the overall-status order, and the current-cycle edition constant; a guard that moving one threshold changes the expected status.
• tests/admin-hidden.test.mts: the standing panel's private fields appear with admin: true and the public endpoint's response schema rejects them; the selectivity breakdown's percentiles sum to the index as the spec's formula says.
• tests/github-app.test.mts: JWT shape, token caching, each call's URL and body, the request_id input on every dispatch, the merge refusal on pending checks, the review-queue commit's branch name and file contents.
• tests/require-admin.test.mts: /admin/** returns 404 signed out and for a non-admin; getUser() returns null for a suspended profile.

Build order

Three units, one PR each or one PR with three sub-branches as the build-roadmap-section skill does:

1. People (the migration, admin-auth, /admin with People, Log, Admins, the MFA step). The owner can use it the day it merges.
2. Colleges and Health (hidden values, the health rules, the overview, the traced files).
3. Operations (the GitHub App, runs, approvals, the queue). Needs the owner's App installation first.

Owner steps

1. Insert the first admins row in the SQL Editor (dev, then prod) and enroll MFA from /admin.
2. Deploy admin-auth with npx supabase functions deploy admin-auth --project-ref <ref> (dev, then prod), as invite-user is deployed; it needs no new secret.
3. Create the GitHub App under the repository owner's account with the four permissions, install it on this repository, and put its id, installation id, and private key in Vercel (Production and Preview, sensitive).
4. Apply the migration before deploying unit 1; the console shows a setup notice until it is.

Owner decisions

Recommendations stand unless changed:

1. MFA required for admin sessions. Recommended: yes, TOTP, enrolled once. The alternative (password only) is one leaked password away from every family's data.
2. What the student sees when an admin views their data. Recommended: "Quad support viewed your information", in the same list as guardians' views. The alternative, not logging admin views, breaks the promise the list makes.
3. A second Edge Function with the secret key. Recommended: yes, admin-auth, enumerated ops, tested like invite-user. The alternative puts the secret key in Vercel, which the owner has twice decided against.
4. GitHub App or PAT for Operations. Recommended: the App. A PAT works for the first go and the code accepts either; the App removes the 90-day renewal and the personal attribution.
5. Operations in the first build. Recommended: yes, as unit 3, since the integrations above are known and small; it can slip to a second PR without changing units 1 and 2.
6. Admin tiers. Not in this version: every admin can do everything. A support role that cannot delete, purge, grant, or dispatch is a one-column change (admins.level) when a second person joins.

Open questions

• Should the console show the dollar cost of a run before dispatching mode: all, read from the newest report in data/reports/? Recommended: yes, it is one number and it is the one that matters.
• Should "Accept the value" on a review-queue item write the override to data/overrides.json or to the CDS record's own file? Recommended: overrides.json, the mechanism the lineage check already understands; the importer's override format is reused as is.
• Where does the suspended person's support address point before the new domain exists? Recommended: the owner's address for now, in one constant.