Skip to content

Model: RefreshToken

Persisted state behind the rotating refresh-token half of the sign-in flow defined in ADR 0002. One row represents one issued refresh token; rotation creates a new row, expiry / logout / replay marks rows inactive.

Fields

Field Type Constraints Notes
Id uuid PK, generated
CreatedAt timestamp with time zone NOT NULL, default now() UTC
ExpiresAt timestamp with time zone NOT NULL Default: CreatedAt + MOLIB_REFRESH_TTL_DAYS (30 days).
UserId uuid NOT NULL, FK → User.Id ON DELETE RESTRICT, ON UPDATE RESTRICT
FamilyId uuid NOT NULL, indexed Set at sign-in; copied on every rotation. Identifies a single login session across rotations.
TokenHash text NOT NULL, UNIQUE SHA-256 of the raw token. Raw token is sent to the client once and never persisted.
UsedAt timestamp with time zone NULL Set when the token is rotated. Presence ⇒ "spent." Replay (UsedAt IS NOT NULL) triggers family-wide revocation.
RevokedAt timestamp with time zone NULL Set on logout, replay-detection, or admin revoke.

Invariants

  • A token is active iff UsedAt IS NULL AND RevokedAt IS NULL AND ExpiresAt > now(). At most one token per family is active.
  • The raw token is never stored in plaintext; only its SHA-256 hash. Lookup is by hash.
  • FamilyId groups all rotations of one login session. Family-wide revocation is the response to: explicit logout, replay detection, admin action.
  • Replay (POST /sessions/refresh presents a token whose UsedAt IS NOT NULL) MUST revoke every row in the family.

Relationships

  • Many-to-one with User. FK is ON DELETE RESTRICT + ON UPDATE RESTRICT per project policy: a User with refresh tokens cannot be deleted; the tokens must be cleaned up explicitly first.

Indexes

  • Unique on TokenHash (lookup path on every refresh).
  • Non-unique on (FamilyId) for fast family-wide revocation.
  • Non-unique on (UserId, RevokedAt) for the eventual "active sessions" listing.

Notes

  • RefreshToken rows accumulate. A periodic cleanup job (or a check at refresh time) deleting rows where ExpiresAt < now() - INTERVAL '7 days' is sufficient — implementation can wait until the table grows.
  • Access tokens (JWTs) are not stored. They are stateless and verified by signature; this table is the entire server-side auth state.