19 lines
892 B
MySQL
19 lines
892 B
MySQL
|
-- Enhance refresh tokens for secure rotation and reuse detection
|
||
|
|
-- Adds rotated_to column to track token chains and detect stolen token reuse
|
||
|
|
|
||
|
|
-- Add rotated_to column to track which token this was rotated into
|
||
|
|
-- When a token is rotated, we store the ID of the new token here
|
||
|
|
-- If a token with rotated_to set is used again, it indicates token theft
|
||
|
|
ALTER TABLE refresh_tokens ADD COLUMN rotated_to UUID REFERENCES refresh_tokens(id);
|
||
|
|
|
||
|
|
-- Index for efficient cleanup queries on expires_at
|
||
|
|
CREATE INDEX idx_refresh_tokens_expires ON refresh_tokens(expires_at);
|
||
|
|
|
||
|
|
-- Index for finding active tokens per user (for revoke_all and listing)
|
||
|
|
CREATE INDEX idx_refresh_tokens_user_active ON refresh_tokens(user_id, revoked_at)
|
||
|
|
WHERE revoked_at IS NULL;
|
||
|
|
|
||
|
|
-- Index for reuse detection queries
|
||
|
|
CREATE INDEX idx_refresh_tokens_rotated ON refresh_tokens(rotated_to)
|
||
|
|
WHERE rotated_to IS NOT NULL;
|