funman300
b129664344
Adds a `refresh_tokens` table (migration 003) with one row per live refresh token, keyed by UUID jti. On every POST /api/auth/refresh the old jti row is deleted and a new token pair is issued and stored. Using a consumed token returns 401. Expired rows are pruned inline on each successful rotation. Server: Claims gains an optional `jti` field; make_refresh_token now returns (jwt, jti); register/login insert the jti row; RefreshResponse now carries both tokens. Client: stores the rotated refresh token from the response. ARCHITECTURE.md: API table + Security Model updated. Three new integration tests cover rotation, consumed-token rejection, and chained rotations. Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
18 lines
819 B
SQL
18 lines
819 B
SQL
-- Migration 003: refresh token rotation table
|
|
--
|
|
-- One row per live refresh token. Issued at login/register and rotated
|
|
-- (old row deleted, new row inserted) on every POST /api/auth/refresh call.
|
|
-- Cascade on user deletion means no manual cleanup is needed when an
|
|
-- account is removed.
|
|
|
|
CREATE TABLE IF NOT EXISTS refresh_tokens (
|
|
jti TEXT PRIMARY KEY, -- UUID v4 embedded in the JWT
|
|
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
expires_at TEXT NOT NULL -- ISO 8601, mirrors the JWT exp claim
|
|
);
|
|
|
|
-- Expired-row pruning (done inline in the refresh handler) uses this index
|
|
-- to avoid a full table scan on every refresh call.
|
|
CREATE INDEX IF NOT EXISTS refresh_tokens_expires_at_idx
|
|
ON refresh_tokens(expires_at);
|