Every operation the SlateDB store answers with a full keyspace scan becomes
an indexed query here: listing a user's sessions joins one row per session
via the partial unique index instead of scanning every token ever issued
and grouping by chain, and revoking one is a single DELETE that cascades.
Rotation is the structural win. Claiming the presented token is one
`UPDATE ... WHERE used_at_ms IS NULL ... RETURNING`, and it is the
transaction's first statement, so SQLite takes the write lock before
anything is read. A concurrent caller blocks on that lock and then sees the
token already spent, which is exactly the replay signal -- so the store
needs no `KeyedMutex` to serialise rotation, and the guarantee survives more
than one server process.
Expiry is checked ahead of reuse on the cold path, preserving the ordering
callers depend on: only replaying a still-live token revokes its chain.
Drops the ordering CHECKs between a session's timestamps and its tokens'.
Rotation stamps `now` from the process clock against rows written by an
earlier request, so an NTP step backwards would have turned a harmless clock
anomaly into refresh failing outright for every affected session.
The store is not wired into the server yet.
Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>
A CLI auth session is a rotation chain, but the SlateDB records that back
it today store identity and profile per token, so a chain has no owner and
nothing stops its rows from disagreeing. These two tables give the chain a
home: `auth_sessions` holds the identity and profile once, `refresh_tokens`
holds only per-token facts.
Two invariants the current code relies on but never states become
constraints. The partial unique index on `(session_id) WHERE used_at_ms IS
NULL` enforces that rotation leaves exactly one live token per chain --
which is what makes the session listing an indexed lookup instead of a
scan-and-group. The foreign key with `ON DELETE CASCADE` makes revoking a
session remove its tokens without a second statement.
Tokens are retained after rotation until they expire so a replayed token
stays distinguishable from a forgery.
Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>
