auth/session_queries.ts

The full auth_session column set, named explicitly so a row read fails loud on schema drift — SELECT * would silently carry a dropped or leftover column into the strict-validated wire shapes (see ACCOUNT_COLUMNS in auth/account_queries.ts for the outage class this discipline exists to prevent; the Rust twin already names columns at every session site). Keep in sync with AuthSession and the migration chain's end state — not the frozen v0 DDL in auth/auth_ddl.ts, which still creates last_seen_at for the appended drop migration to remove.

Session lifetime in milliseconds (30 days).

An absolute cap: expires_at is set once at mint and never extended — there is deliberately no touch/renewal query on either spine (a sliding window renews a leaked cookie forever; see docs/security.md §Session Security). The cookie's SESSION_AGE_MAX mirrors this value.

Generate a cryptographically random session token.

Hash a session token to its storage key using blake3.

The sole minting point for SessionId — hash_blake3 returns bare hex, so the brand is applied here, where the value gains its meaning.

Create a new auth session.

Delete expired sessions.

Enforce a per-account session limit by evicting the oldest sessions.

Keeps the newest max_sessions sessions and deletes the rest.

Race safety: this function must run inside a transaction alongside the INSERT that created the new session. All callers satisfy this requirement:

• POST /login uses the default transaction: true (framework-managed transaction wrapping in apply_route_specs)
• The account_token_create RPC handler runs under the dispatcher's transaction path because its spec declares side_effects: true
• POST /bootstrap and POST /signup manage their own transactions and pass the transaction-scoped deps to create_session_and_set_cookie

The transaction makes one creator's INSERT + enforce_limit pair atomic, but it does not serialize concurrent creators. Under Read Committed, two transactions can't see each other's uncommitted session row, so each computes its OFFSET eviction against a stale count, each preserves its own row, and both commit above max_sessions. A transaction is necessary here but not sufficient. Closing this needs one serialization point per (account_id, credential_kind) — a locked parent row or a transaction-scoped advisory lock — taken before count/evict/insert.

Ordering is created_at DESC, id DESC. The id leg is a stability tie-breaker, not a recency one: created_at defaults to NOW(), the *transaction* timestamp, so two sessions born in one transaction — or in two transactions that started in the same microsecond — tie, and an untied OFFSET may keep a different set on two evaluations of the same rows. id is the blake3 token hash, so the tie-break is arbitrary but deterministic; it makes the survivors reproducible, it does not make the row just inserted a guaranteed survivor. Only the serialization point above does that.

Expired-but-unreaped rows count toward the cap (the predicate is account_id alone). Matches the Rust twin query_session_enforce_limit.

Get a session if it exists, is not expired, and has not been revoked.

List all active sessions across all accounts with usernames.

Ordered by created_at DESC (newest session first), matching the per-account listing.

List sessions for an account, newest first.

Revoke all sessions for an account.

Revoke (delete) a session by its token hash, with no account scoping.

The _unscoped suffix is the safety signal — there is no account_id constraint, so callers must guarantee the hash came from a trusted source (the authenticated session cookie path is the only safe production caller — see auth/account_routes.ts /logout). For user-facing revocation of a specific session by ID, use query_session_revoke_for_account (IDOR-guarded).

Revoke a session only if it belongs to the specified account.

Prevents cross-account session revocation.