Authentication, accounts and authorisation¶

Overview¶

The Sport Analytics Tool uses Supabase Auth as its managed authentication provider.

Google is enabled as the initial OAuth identity provider in the shared development Supabase project.

The handwritten Express API validates authenticated Supabase identities, synchronizes them to provider-neutral application accounts, and enforces server-owned role and competition-scope rules. Authentication by itself grants no application permission.

See:

Authentication provider comparison
evidence/decisions/ADR-004-supabase-authentication-foundation.md
superseded decision: evidence/decisions/ADR-002-firebase-authentication-foundation.md

Architecture¶

sequenceDiagram
    participant User
    participant Frontend as React frontend
    participant Supabase as Supabase Auth
    participant API as Express API
    participant Database as Supabase PostgreSQL

    User->>Frontend: Start Google sign-in
    Frontend->>Supabase: Managed OAuth request
    Supabase->>Supabase: Complete Google OAuth flow
    Supabase-->>Frontend: Supabase session and access token
    Frontend->>API: Authorization: Bearer access-token
    API->>Supabase: getUser(access-token)
    Supabase-->>API: Verified Supabase user
    API->>Database: Upsert app_user and load role, request state and scope
    Database-->>API: Application account profile
    API-->>Frontend: Current user profile

Supabase manages authentication and identity.

The Express API remains the application’s trusted boundary. The frontend must access application data through the handwritten API rather than using generated Supabase data endpoints directly.

Development project configuration¶

The shared development Supabase project contains:

Supabase Auth;
Google enabled as a social provider;
the Google OAuth client ID and secret;
the development Site URL;
allowed development redirect URLs;
development identities only.

The Google OAuth client is configured with:

Authorized JavaScript origin:
http://localhost:5173

Authorized redirect URI:
https://YOUR-PROJECT-REF.supabase.co/auth/v1/callback

The redirect URI must exactly match the callback shown in the Supabase Google provider settings.

The Google client secret is stored only in the Google and Supabase dashboards. It must not be placed in source control, frontend code, documentation or chat messages.

Backend environment configuration¶

The backend requires:

SUPABASE_URL=https://your-project-ref.supabase.co
SUPABASE_PUBLISHABLE_KEY=your-supabase-publishable-key
# Optional at startup; required for account deletion.
# SUPABASE_SECRET_KEY=your-server-only-supabase-secret-key

The committed apps/backend/.env.example contains placeholders only.

Real development values belong in:

apps/backend/.env

That file is ignored by Git.

The backend does not require a Supabase secret key or legacy service_role key to validate an access token. Account deletion uses a separate server-only secret when that optional capability is enabled.

Frontend environment configuration¶

The managed frontend authentication client uses:

VITE_SUPABASE_URL=https://your-project-ref.supabase.co
VITE_SUPABASE_PUBLISHABLE_KEY=your-supabase-publishable-key

These values identify the public Supabase application. The frontend fails during application initialisation when either value is absent so that authentication is never configured with an invented fallback.

The frontend provides one /sign-in page that starts the managed Google OAuth flow for either login or account creation. Successful authentication returns to /. No Supabase secret key, database password or OAuth client secret may be added to a VITE_ variable.

Frontend session state¶

The React application creates one browser Supabase client and enables Supabase's managed session persistence, token refresh and redirect-session detection. Application code does not store access or refresh tokens separately.

AuthProvider exposes the shared frontend identity state:

isLoading remains true while the existing Supabase session is requested;
session contains the current managed session or null;
identity contains the Supabase user from that session or null; and
isAuthenticated describes whether a session is present.

The provider subscribes to Supabase authentication-state changes and unsubscribes when it is unmounted. Supabase sign-in, sign-out and managed token-refresh events therefore replace the shared session state. This identity state must not be interpreted as an application role, submission permission, administrator permission or scoped grant.

Signed-out navigation exposes one Login or Sign up action. Signed-in navigation exposes Account and Sign Out, and updates from the shared authentication state without a page reload. The Account page loads the server-owned role and exposes submission navigation only to submitter and admin accounts. /account displays only the email already present on the Supabase session identity when available. Sign-out uses the managed Supabase operation and returns to /.

/submissions/new is a protected frontend journey. Anonymous users are redirected to sign in. A signed-in user must also have a persisted submitter or admin role before the event editor is shown. The fixture selector is populated from public fixture queries constrained by the competition IDs returned from /auth/me. These frontend checks improve the experience but are not an authorisation boundary: POST /api/v1/submissions repeats authentication, role, and target fixture-scope checks on the backend.

Frontend authenticated API requests¶

createAuthenticatedApiClient sends application requests only to the configured handwritten API base URL. When its token provider has a current access token, the client sets Authorization: Bearer <access-token>. Without a token it removes the authorization header rather than fabricating or retaining a credential. useAuthenticatedApiClient connects this request client to the current session exposed by AuthProvider.

Failed responses are exposed as ApiResponseError values. A backend 401 has the unauthenticated kind, while 403 has the distinct forbidden kind. The request client does not sign users out, redirect them or infer permissions from either response; those user journeys remain deferred to later route and interface work.

Backend token validation¶

The backend creates a server-side Supabase client using @supabase/supabase-js.

Browser session behaviour is disabled:

auth: {
  persistSession: false,
  autoRefreshToken: false,
  detectSessionInUrl: false,
}

For a protected request, the backend extracts the bearer token and calls:

supabase.auth.getUser(accessToken);

Supabase Auth validates the submitted access token and returns the authenticated user or an error.

The API then upserts the verified provider subject into app_user. A first request creates a viewer account with not_requested request state and no competition grants. Later requests refresh the verified display name and last_authenticated_at; they never accept role, request state, or scope from the frontend.

The profile response combines the verified identity with server-owned application state:

{
  "user": {
    "id": "42",
    "subject": "<supabase-user-id>",
    "displayName": "Example User",
    "role": "submitter",
    "approvalState": "approved",
    "competitionIds": ["7", "12"]
  }
}

The API does not trust an unverified, manually decoded token.

Application authorisation model¶

The only valid application_role values are viewer, submitter, and admin:

viewer is the default and cannot submit or administer the application;
submitter can submit only within assigned competition scope; and
admin is required by administrator-only middleware and can use permitted submission workflows.

application_role is authoritative for submission permission. Competition scopes remain separate and are still required by the current submission policy. The legacy not_requested, pending, approved, and rejected values remain temporarily in submitter_approval_state only to support the access-request workflow; they do not authorize a submission.

Neither a Supabase identity nor its user-editable metadata can set the role. Account synchronization creates a viewer and preserves any existing server-owned role on later sign-ins. See Roles and permissions for the capability matrix.

Requesting submitter access¶

Authentication does not itself grant submission permission. An authenticated application account may explicitly request submitter access through:

POST /api/v1/submitter-access-requests
Authorization: Bearer <supabase-access-token>

The backend uses the authenticated and synchronized app_user account rather than accepting an account identifier from the client.

Eligible state transitions are:

not_requested -> pending
rejected      -> pending

Each transition requires an existing competitionId. The backend stores it as app_user.submitter_requested_competition_id in the same conditional update that creates the pending state. This requested scope is workflow data, not a grant.

An existing pending request is rejected with 409 Conflict, preventing duplicate active requests.

An account that already has the submitter or admin role is rejected with 409 Conflict because no additional request is necessary. A legacy approved request state is also rejected while the deprecated request workflow remains in place.

The state transition is performed with a conditional PostgreSQL update so that concurrent duplicate requests cannot both create a new active request.

The signed-in Account page loads /api/v1/auth/me whenever it mounts and displays the persisted state. Viewer accounts in not_requested and rejected choose from public competitions, while pending viewers see the named requested competition and an awaiting-review state without another action. Fixtures are not request-scope choices. Accounts with submitter or admin receive a link to the scoped submission interface regardless of the deprecated request state. The request action has explicit progress, success and error feedback. After a successful request, or a 409 Conflict caused by a stale eligible view, the frontend reloads the current-user profile so refreshes and later authenticated sessions continue from server-owned state.

Administrator role assignment and competition-scope assignment remain server-owned operations. The administrator update endpoint enforces these review transitions while holding a database lock:

pending viewer     -> approved submitter
pending viewer     -> rejected viewer
approved submitter -> approved submitter with replacement scope
approved submitter -> viewer with no scope (request decision remains approved)

Approval and rejection are permitted only from pending. Approval grants exactly the competition stored on the request; a mismatched approval body or a legacy pending row without a stored competition fails closed. The administrator can reject a legacy row so the viewer can create a corrected request. Scope replacement and revocation are permitted only for an existing approved submitter. Other lifecycle changes receive 409 Conflict with INVALID_SUBMITTER_ACCESS_TRANSITION, so hiding frontend controls is never the authorization boundary. A rejected viewer must create a new request to return to pending before approval.

Protected routes compose reusable middleware in this order:

requireAuthentication(verifyAccessToken, synchronizeAccount);
requireSubmitter();
requireCompetitionScope((request) => request.params.competitionId);

Administrator routes use requireAdministrator(). Competition resolvers may be asynchronous so a future fixture or submission route can load the trusted target competition before checking scope. Missing or invalid credentials return 401; authenticated but disabled, incorrectly roled, or out-of-scope accounts return the same non-disclosing 403 response.

Protected endpoint¶

Request¶

GET /api/v1/auth/me
Authorization: Bearer <supabase-access-token>

Successful response¶

HTTP/1.1 200 OK

{
  "user": {
    "id": "42",
    "subject": "<supabase-user-id>",
    "displayName": "Example User",
    "role": "viewer",
    "approvalState": "not_requested",
    "requestedCompetition": null,
    "competitionIds": []
  }
}

Missing or invalid token¶

HTTP/1.1 401 Unauthorized
WWW-Authenticate: Bearer

{
  "error": {
    "code": "UNAUTHORIZED",
    "message": "A valid authentication token is required."
  }
}

The error response deliberately does not reveal whether a token was malformed, expired, revoked or associated with a missing user.

Authenticated but forbidden¶

HTTP/1.1 403 Forbidden

{
  "error": {
    "code": "FORBIDDEN",
    "message": "The authenticated account is not permitted to perform this operation."
  }
}

Running the backend¶

Install dependencies:

npm.cmd install

Copy the environment example:

Copy-Item apps/backend/.env.example apps/backend/.env

Replace only the placeholders in the ignored .env file.

Start the backend:

npm.cmd run dev:backend

Verify the health endpoint:

Invoke-RestMethod -Uri "http://127.0.0.1:3000/api/v1/health"

Unauthenticated proof¶

Call the protected endpoint without a token:

curl.exe -i http://127.0.0.1:3000/api/v1/auth/me

Expected result:

HTTP/1.1 401 Unauthorized
WWW-Authenticate: Bearer

Authenticated development proof¶

Use a disposable identity in the shared development Supabase project.

Obtain an access token through a managed Supabase Auth sign-in flow. Keep the token only in memory and do not print, save, screenshot or commit it.

Call the backend with:

curl.exe -i `
  -H "Authorization: Bearer YOUR_TEMPORARY_ACCESS_TOKEN" `
  http://127.0.0.1:3000/api/v1/auth/me

Expected result:

HTTP/1.1 200 OK

The response must contain the synchronized application profile. Verify the authoritative role and scope against the database rather than against token claims, deprecated request state, or frontend state.

Replace the token immediately after use and clear it from shell history where practical. Never include the real token in test evidence.

Automated tests¶

The backend tests cover:

missing, invalid and expired bearer tokens return 401;
a valid token synchronizes and returns the application profile;
a disabled account and denied role/scope checks return 403;
a normal signed-in user cannot access administrator or upload policies;
an in-scope submitter passes upload policies;
an out-of-scope submitter is denied;
a submitter cannot access administrator policy;
an admin passes administrator and permitted submission policies; and
public reads do not invoke authentication.

The test application injects a mock verifier. Automated tests therefore do not require live Supabase credentials or contact the hosted Auth service.

The manual development proof separately exercises the real supabase.auth.getUser() validation path.

Run the backend checks with:

npm.cmd run typecheck --workspace @sport-analytics/backend
npm.cmd run lint --workspace @sport-analytics/backend
npm.cmd test --workspace @sport-analytics/backend
npm.cmd run build --workspace @sport-analytics/backend

Local Supabase support¶

Supabase provides a CLI that can run the database, Auth service and supporting services locally.

A project-scoped setup uses:

npm.cmd install --save-dev supabase
npx.cmd supabase init
npx.cmd supabase start

The CLI requires a Docker-compatible runtime.

Local Supabase configuration must be coordinated with the database workstream. Do not initialize or reset a shared database without team agreement.

A local stack is for development only. It has development credentials and must never be exposed publicly.

Password reset¶

The application supports Google OAuth only, so it has no application-owned password to reset. Google owns the user's Google Account credential and recovery process. The application never receives, stores or changes that password.

Supabase's resetPasswordForEmail operation applies to Supabase email/password authentication. It cannot reset a Google Account or Gmail password. Adding it for an OAuth account could introduce a separate Supabase password without changing the Google credential, thereby expanding the product to a second authentication method.

Users who have forgotten their Google Account password must use Google Account recovery and then return to the application to sign in with Google. See Password recovery ownership for the issue #65 decision and security rationale.

Account deletion¶

Normal token verification uses SUPABASE_PUBLISHABLE_KEY. The backend creates a separate, non-persistent Supabase Admin client only when the optional server-only SUPABASE_SECRET_KEY is configured. This client supports account deletion and the administrator-only user-management email lookup; it returns the email field only and never passes provider user objects, credentials, or tokens to the application response. Without that secret, DELETE /api/v1/account returns 501 ACCOUNT_DELETION_UNAVAILABLE and GET /api/v1/admin/users returns a controlled server error; backend startup and unrelated routes are unaffected.

The endpoint accepts no target account ID, validates the exact DELETE confirmation, and implements the provider-capable workflow as a recoverable state machine:

disable the application account and remove role, approval and competition grants;
hard-delete the Supabase Auth user;
replace the local Auth subject and display name with a non-reusable tombstone while retaining the stable app_user_id provenance key; and
clear the browser's local Supabase session after the backend confirms success.

An Auth or database failure returns 503 ACCOUNT_DELETION_INCOMPLETE. The local account remains disabled, and retry either repeats the idempotent Auth deletion or resumes finalization. A hash of the former high-entropy Auth subject prevents an already-issued JWT from synchronizing a replacement account during the token's remaining lifetime. The browser's local sign-out does not substitute for the backend revocation check.

Cricket submissions, deliveries, fixtures and derived statistics remain available without the deleted display name or reusable Auth subject.

See:

Privacy and retention
evidence/decisions/ADR-006-account-deletion-retention.md

Authentication versus authorisation¶

Authentication answers:

Who is making this request?

Authorisation answers:

What is this identity allowed to do?

A valid Supabase identity does not automatically grant:

admin access;
submitter access;
competition access;
season or fixture access;
event submission rights;
sport-specific permissions.

The backend enforces those rules from application database state. Administrator approval management, submitter access requests and event-submission routes remain separate workflows.

Security requirements¶

Use HTTPS in deployed environments.
Never log bearer access tokens.
Redact the Authorization header from request logs.
Keep Google OAuth client secrets in provider dashboards.
Never expose Supabase secret or service_role keys in frontend code.
Keep real environment files ignored.
Commit placeholders only.
Configure exact redirect URLs and CORS origins.
Use separate development and production configuration.
Return safe authentication errors.
Fail closed when persisted role or request-state values are unsupported.
Never accept role, request state, or granted competition scopes from a request or token claim; only the selected requested competition identifier is client input, and it is validated before persistence.
Apply rate limiting before exposing sensitive production endpoints.
Define Row Level Security and backend authorisation separately.
Rotate credentials immediately if exposure is suspected.

Deferred scope¶

This foundation intentionally does not implement:

application-owned password-reset screens while Google OAuth remains the only sign-in method;
event correction and file or batch upload interfaces;
season or fixture scopes beyond reusable competition resolution;
sport-specific authorisation;
production Row Level Security policies.

References¶

Basic security and privacy hardening audit¶

The completed Basic workflows are reviewed for security and privacy risks across server-side authorization, file and input validation, public-data exposure, authentication and administrator boundaries, secret handling and dependency health.

The Sprint 2 Issue #274 review included:

competition-scoped submission and correction authorization;
administrator and authenticated-route role enforcement;
uploaded-file size, type and content validation;
malformed and oversized request handling;
public and export response exposure checks;
environment-variable and secret-reference inspection;
npm production and development dependency audits; and
monorepo dependency and architecture hygiene checks.

Material dependency findings were remediated without forcing breaking framework upgrades. After remediation:

npm.cmd audit --omit=dev
found 0 vulnerabilities

Issue #274 deferred the remaining Vite/esbuild development-tool advisory rather than applying npm audit fix --force. Issue #329 is the controlled Sprint 3 Vite/Vitest migration that addresses that follow-up; its final dependency-audit result is recorded only after the upgraded lock file and full verification have been reviewed.

The original finding remains in the Issue #274 security/privacy audit evidence, and the migration record is retained in the Issue #329 Vite/Vitest evidence.

AI Declaration¶

The preceding document was planned and generated with the assistance of Codex[GPT-5]. The frontend session-state and authenticated-request sections were later updated with the assistance of Codex[GPT-5.6 Sol]. The account synchronization, profile, and authorization sections were updated with the assistance of Codex[GPT-5.6 Sol]. The protected event-submission journey was documented with the assistance of Codex[GPT-5.6 Sol]. The submitter access-request section was documented with the assistance of ChatGPT-Web[GPT-5.6 Sol]. The account-deletion security and recovery flow was documented with the assistance of Codex[GPT-5]. The submitter access-request frontend workflow was documented with the assistance of Codex[GPT-5]. The competition-scoped submitter access correction was documented with the assistance of Codex[GPT-5]. The Basic security/privacy hardening audit documentation was produced with the assistance of ChatGPT-Web[GPT-5.6 Sol].