Auth System

Alchemify uses custom auth — no external auth provider. Everything lives in the codebase and the database.

Auth flow

The first owner is created via direct SQL or sys.invite_user by a DBA. After that, owners/admins invite all other users. All logins use magic links — no password-based login in production.

1. Owner invites users → POST /admin/invite → magic link email sent
2. User clicks link → verify_magic_link consumes token → JWT issued
3. Subsequent logins: user enters email → POST /auth/magic-link → magic link email → click → JWT issued

JWT structure

{
  "sub": "user-uuid",
  "role": "staff",
  "email": "user@example.com",
  "tenant": "acme",
  "app_schema": "rev_lifecycle",
  "iat": 1700000000,
  "exp": 1700086400
}

The server uses these claims to set PostgreSQL session variables:

• role → SET LOCAL ROLE
• sub → SET LOCAL app.user_id
• app_schema → SET LOCAL search_path (and SET LOCAL app.schema_name for sys.current_schema_name())

The email claim is used by the frontend to display the user’s email without an extra API call on refresh. The tenant claim (present only in multi-tenant mode) records which tenant the JWT was issued for — on every authenticated request, the server checks that it matches the X-Tenant header, returning 403 on mismatch. This is defense-in-depth: the reverse proxy routes by subdomain, the header identifies the tenant, and the JWT proves the user belongs to that tenant.

The app_schema claim records which app schema the user has selected (#763). It is set by POST /auth/select-app, preserved across POST /auth/refresh, and absent when the user is on the platform shell or has not yet picked an app (in which case the proxy resolves to 'public'). The wrappers (with-role, with-auth) set the database GUC app.schema_name from this claim — that drives the WHERE schema_name = sys.current_schema_name() predicate on every metadata view (public.column_metadata, public.table_metadata, public.app_metadata, public.field_options, public.pages).

X-App-Schema header. The frontend continues to send X-App-Schema on every request. When the JWT carries the app_schema claim, the header is treated as a defense-in-depth assertion: a mismatch returns 403. When the JWT does not carry the claim (old tokens issued before #763), the header is honored as a rollout fallback — this preserves backward compatibility for sessions that survive a deploy. The frontend rotates such tokens on AppProvider mount via POST /auth/select-app, so the window closes within seconds.

Write-scope enforcement. Two layers reject DML targeting a foreign schema:

1. validateSQL in the SQL proxy walks the AST of every INSERT/UPDATE/DELETE and rejects any fully-qualified target outside {app_schema, public, sys}. Unqualified writes pass — search_path lands them in the active schema.
2. sys._enforce_metadata_scope triggers on sys.{column,table,app}_metadata and sys.field_options reject any insert/update whose schema_name column does not match sys.current_schema_name(). SECURITY DEFINER admin functions (sys.create_app, sys.update_app, sys.rename_app, sys.drop_app) opt out via SET LOCAL app.bypass_scope_check = on because they legitimately write to a schema other than the caller’s current GUC value.

Session lifetime (sliding idle timeout)

JWT exp is driven by public.org.session_timeout — default 7 days, configurable per org (1 hour ≤ value ≤ 10 years). Owners and admins can update it via the public.org view.

The lifetime is sliding: while a user is active, the client calls POST /auth/refresh before the current token would expire, and the server re-issues a fresh JWT with updated exp. So session_timeout is effectively the idle timeout — the maximum gap between interactions that keeps a session alive. A user idle longer than that logs in fresh.

Refresh is driven entirely by the client. The timer in AuthContext (apps/web/src/contexts/AuthContext.tsx) fires at exp - max(60s, 10% of TTL); a visibilitychange handler catches up when a backgrounded tab returns; a storage event listener syncs the refreshed token across tabs. On transient refresh failure the timer retries with backoff while the token is still valid; on 401, the normal expired-session flow takes over on the next request.

Security note: /auth/refresh re-reads sys.users on every call. A user whose role was demoted, email was changed, or account was deactivated (is_active = false) mid-session does not get a refreshed token — role/email reflect canonical DB state, deactivated users are rejected with 401.

Endpoints

POST /admin/invite

Requires JWT with owner or admin role.

{ "email": "user@example.com", "role": "staff", "display_name": "Jane Doe" }

Creates a new user with the specified role and display name, and sends an invite email with a magic link. display_name is required (non-empty, max 200 chars). Only owner can invite users with owner or admin roles — admins can only invite staff and member. Returns { ok: true } on success, 400 on missing/invalid display_name or role, 409 on duplicate email, 403 if not owner/admin or if admin tries to invite owner/admin.

POST /auth/login (dev only)

Available only when NODE_ENV !== 'production'. Password-based login for development and testing convenience.

{ "email": "user@example.com", "password": "secret", "turnstile_token": "cf-token" }

Returns { token, user: { id, email, display_name, role, needs_password } } on success, 401 on failure, 403 on Turnstile failure. Note: needs_password appears in the response body only (not in the JWT).

POST /auth/magic-link

{ "email": "user@example.com", "turnstile_token": "cf-token" }

Creates a magic link token and sends it via email (Mailgun). In dev without Mailgun configured, the login URL is logged to the server console. Always returns { sent: true } — never reveals whether the email exists.

Protection: This endpoint is rate-limited and requires Cloudflare Turnstile verification to prevent automated abuse:

1. Email rate limit — 3 requests per email per 15 minutes (plus-address and Gmail dot variants are normalized to the same bucket)
2. IP rate limit — 10 requests per IP per 15 minutes (uses CF-Connecting-IP header, falls back to req.ip)
3. Turnstile verification — the turnstile_token field is verified against Cloudflare’s siteverify API

Rate-limited requests return 429 with a Retry-After header. Failed Turnstile verification returns 403. Rate limits are checked before Turnstile to avoid unnecessary API calls.

POST /auth/magic-link/verify

{ "token": "one-time-token" }

Returns { token, user: { id, email, display_name, role, needs_password } } on success, 401 on invalid or reused token. Note: needs_password appears in the response body only (not in the JWT).

POST /auth/refresh

Requires a valid (unexpired) JWT. Re-issues a fresh token with updated exp, preserving the tenant and app_schema claims and re-reading role/email/is_active from sys.users so demoted or deactivated users cannot keep their session alive. Rejects anon tokens with 403.

Returns { token } on success. 401 on missing, malformed, expired, or deleted/deactivated-user tokens. 403 on anon tokens or tenant mismatch.

The client calls this endpoint automatically on a timer — see the Session lifetime section above.

POST /auth/select-app

Requires a valid (non-anon) JWT. Re-issues the caller’s token with the chosen app schema written to the app_schema claim. The frontend calls this whenever the user picks or switches apps.

{ "schema": "rev_lifecycle" }

Pass schema: null (or "public") to clear the claim — the next session starts in the platform shell. Schema validity is checked via resolveAppSchema: format must match ^[a-z][a-z0-9_]*$, and the schema must exist in sys.apps.

Returns { token } on success, 400 for unknown or malformed schema names (same contract as the SQL proxy’s X-App-Schema 400 path), 401 without a valid JWT, 403 for anon. The tenant claim is preserved.

POST /auth/set-password

Requires a valid JWT. Sets the user’s password (only if password_hash is currently NULL).

{ "password": "at-least-8-chars" }

Returns { ok: true, token } with a fresh JWT on success, 409 if password is already set, 400 if password is too short (< 8) or too long (> 128).

Email change flow

Email changes use a two-step magic-link verification so a typo doesn’t lock the user out and a stolen session can’t silently hijack the account. The link is sent to the new address; the DB email column is only updated after the link is consumed.

POST /auth/email-change/request

Requires a valid JWT. Rate-limited by email and IP; requires Turnstile when configured.

{ "new_email": "new@example.com", "turnstile_token": "cf-token" }

Validates format, checks uniqueness against sys.users.email, creates a sys.magic_links row with pending_email set, and sends a confirmation link to the new address. Returns { sent: true } on success.

Errors:

• 400 — missing/invalid email, or new email equals current email (SQLSTATE 22023).
• 409 — email already in use by another user (SQLSTATE 23505).
• 429 — rate-limit exceeded.
• 403 — Turnstile failed.

POST /auth/email-change/confirm

No authentication required — the token itself is the capability (same model as /auth/magic-link/verify).

{ "token": "link-token" }

Consumes the link (atomic FOR UPDATE), re-checks uniqueness (race-safe), updates sys.users.email, and returns { token, user } with a fresh JWT containing the new email claim. Existing JWTs on other devices retain the old email claim until they expire; nothing authZ-related depends on the email claim.

Errors:

• 400 — missing token.
• 401 — invalid, expired, reused, or wrong-flavor token (login magic-links are rejected here).
• 409 — another user claimed the email between request and confirm.

The two magic-link flavors are partitioned by pending_email nullability: sys.verify_magic_link matches pending_email IS NULL only, and sys.confirm_email_change matches pending_email IS NOT NULL only.

Turnstile (bot protection)

The unauthenticated form-submission endpoint (/auth/magic-link) requires a Cloudflare Turnstile token in the turnstile_token body field. The dev-only /auth/login also requires Turnstile when TURNSTILE_SECRET is set. Configure in ~/.config/alchemify/backend-userapps.env:

TURNSTILE_SECRET=0x...

When TURNSTILE_SECRET is not set (dev), verification is skipped and all requests are allowed through. In production, the server verifies each token against Cloudflare’s API with a 5-second timeout, failing closed on errors. Verified tokens are tracked in memory to prevent replay.

The frontend includes the Turnstile widget on the login and magic-link forms via a useTurnstile React hook (apps/web/src/lib/useTurnstile.ts). The hook loads the Turnstile script once, renders an invisible widget into a ref element, and returns { token, reset }. The resulting token is sent as turnstile_token in the request body. The production site key is hardcoded in useTurnstile.ts. In dev mode it falls back to Cloudflare’s always-pass test key (1x00000000000000000000AA). The CSP is configured to allow https://challenges.cloudflare.com in script-src and frame-src.

Production Turnstile checklist

1. Create a Turnstile widget in the Cloudflare dashboard under Turnstile → Add site. Choose “Managed” mode. Add your production domain(s).
2. Update the site key in apps/web/src/lib/useTurnstile.ts (the SITE_KEY constant).
3. Set the backend secret — add TURNSTILE_SECRET to ~/.config/alchemify/backend-userapps.env. When set, the server verifies every token against Cloudflare’s API and fails closed on errors.
4. Verify end-to-end — after deploying, test the magic-link and invite flows. The Turnstile widget should run invisibly in “Managed” mode and the server should accept the resulting tokens.

For preview/staging deploys, you can use Cloudflare’s always-pass test key (1x00000000000000000000AA) as the site key and leave TURNSTILE_SECRET unset on the server.

Email delivery

Magic link emails are sent via Mailgun. Configure in ~/.config/alchemify/backend-userapps.env:

MAILGUN_API_KEY=key-...
MAILGUN_DOMAIN=mg.example.com
MAIL_FROM=noreply@example.com
APP_URL=https://myapp.example.com

MAILGUN_API_KEY and MAILGUN_DOMAIN must be set together:

• Dev: both may be left unset; the login URL is logged to the server console instead. Setting only one of them is a configuration error and config.validate() will throw at startup.
• Production: both are required. The server exits at startup with a clear error if either is missing.

SQL functions

Auth logic lives in PostgreSQL as SECURITY DEFINER functions. These bypass RLS so they can read/write the users table regardless of the caller’s role.

Function	Purpose
login(email, password)	Verifies credentials (dev-only endpoint)
create_magic_link(email)	Generates a one-time token (15 min expiry)
verify_magic_link(token)	Validates and consumes a login token (rows with pending_email IS NULL), returns user info + needs_password
invite_user(email, role, display_name)	Creates user without password + magic link (owner/admin only; only owner can invite owner/admin roles). display_name is required (non-empty, max 200)
set_initial_password(password)	Sets password for current user (only when password_hash IS NULL)
request_email_change(new_email)	Creates an email-change token (magic link row with pending_email set). Validates format and checks uniqueness. Raises SQLSTATE 23505 on duplicate, 22023 on no-op.
confirm_email_change(token)	Consumes an email-change token and updates the user’s email. Re-checks uniqueness (raises 23505 on race). Returns the updated user row.
update_profile(display_name)	Updates the caller’s display name only. (Email changes go through request_email_change / confirm_email_change.)

display_name validation (required, trimmed non-empty, max 200 chars) is centralized in the internal helper sys._validate_display_name(text) and called from register_user, update_profile, create_magic_link_or_register, and invite_user — keeping the error strings identical across paths.

The server calls these inside a transaction. Auth functions run as anon; invite_user runs as the caller’s role; set_initial_password runs as the user’s role and derives user_id from current_setting('app.user_id').

Deactivated users (is_active = false) are rejected by login, create_magic_link, and verify_magic_link. Existing JWTs remain valid until they expire.