Make refresh rotation atomic and hash stored refresh tokens

[markwylde]

Summary

Refresh token rotation is non-atomic and refresh tokens are stored in plaintext.

Evidence

• packages/api/src/services/sessions.ts rotates by selecting on refreshToken, inserting a new session, then deleting old session without locking.
• sessions.refresh_token stores raw token values.

Security Impact

Parallel refresh requests can clone sessions; database compromise exposes immediately usable refresh tokens.

Required Fix

• Perform refresh rotation inside a transaction with row-level locking and a consumed/rotated guard.
• Store refresh token hashes instead of plaintext.
• Compare incoming refresh token by hash.

Acceptance Criteria

• Parallel refresh with same token allows exactly one successful rotation.
• Rotated/consumed token cannot be reused.
• Database does not store plaintext refresh tokens.
• Tests cover rotation race and compromised-token replay prevention behavior.

Source

Security Review dated 2026-02-15, Finding 3 (High).