Auth: persistent token tracking + server-side session revocation

[lbedner]

Summary

Issued JWTs are currently stateless — we know nothing about them server-side beyond the signature + embedded claims. Logout uses an in-memory blacklist that evaporates on restart. This is survivable for a small closed beta but hurts as soon as multi-device usage, security incidents, or compliance needs enter the picture.

What we have today

• Every issued JWT carries a jti (random 128-bit id) + exp claim in its payload.
• app/services/auth/token_blacklist.py keeps an in-process dict[jti -> expiry] populated by /auth/logout. Reads are O(1) from get_current_user_from_token.
• last_login on the user row is updated on email/password login only. OAuth (Sign-in-with-GitHub) callback doesn't touch it — small related bug (see Tasks below).

What we can't do

• List a user's active sessions ("signed in on 3 devices" UI).
• Revoke one device without nuking all the user's tokens.
• Survive a server restart with token revocations intact — blacklist is volatile.
• Track session provenance: password vs. OAuth vs. CLI-issued tokens (when we add CLI auth).
• Audit: which IP/user agent used which token when.

Proposed design

Schema

New table user_token:

column	type	notes
id	int, pk
jti	str(32), unique	the JWT's random id claim; indexed
user_id	int, fk	→ user.id, ON DELETE CASCADE
issued_at	datetime	default now()
expires_at	datetime	mirror of the JWT exp
revoked_at	datetime, null	non-null = this session is dead
last_used_at	datetime, null	bumped on each authed request; throttled to ~1/min
source	str(16)	"password", "oauth:github", "cli", etc.
user_agent	str(512), null	from request headers on issuance
ip	str(64), null	from request on issuance

Code changes

1. Persist on every token issuance:
   • app/components/backend/api/auth/router.py::login (password)
   • app/components/backend/api/auth/oauth.py::github_callback (OAuth)
   • Any future CLI/machine-to-machine issuance
2. Swap token_blacklist.is_revoked(jti) for a DB check in get_current_user_from_token. Add a small in-process LRU cache to keep the hot path fast; invalidate on revoke.
3. Update last_used_at in a background task (or sampled 1/min) to avoid write amplification.
4. On /auth/logout: set revoked_at on the caller's user_token row.
5. New endpoints:
   • GET /api/v1/auth/sessions — list the caller's non-revoked, unexpired sessions.
   • DELETE /api/v1/auth/sessions/{id} — revoke one session (caller's own).
   • DELETE /api/v1/auth/sessions — revoke all of caller's sessions except the current one.

Frontend

• Settings → "Active sessions" section: table with source/UA/IP/last-used columns + per-row Revoke button + "Sign out everywhere else" action.

Cleanup / retention

• Cron: delete rows where expires_at < now() - 7 days so the table doesn't grow unbounded.
• Consider Redis-backed cache for the revocation check if DB latency becomes a concern.

Related bug to fix alongside

github_callback in aegis-pulse's app/components/backend/api/auth/oauth.py doesn't bump user.last_login after a successful OAuth sign-in. One-liner (await user_service.update_user(user.id, last_login=...)), but worth grouping with this work so session metadata is consistently populated across both paths.

Rollout

• Ship behind AUTH_SESSION_TRACKING_ENABLED setting so existing deployments don't break on the next release.
• When enabled, all new tokens get persisted; old in-flight tokens without a user_token row fall through to the legacy in-memory blacklist until they expire.

Tasks

• Alembic migration: user_token table
• Persist row on password login + OAuth callback + any future issuers
• DB-backed revocation check with in-process cache
• /auth/sessions list/revoke endpoints + tests
• Settings "Active sessions" UI
• Scheduled cleanup job
• OAuth last_login bump fix (bundled)

Context

Filed from aegis-pulse. Pulse is running on the current in-memory blacklist and needs this before any multi-device / production rollout. Backporting here so every project generated from the stack gets the hardened default.