28 lines
1.3 KiB
SQL
28 lines
1.3 KiB
SQL
-- Migration 007: Add refresh_tokens table for secure token management
|
|
-- This enables short-lived access tokens with longer-lived refresh tokens
|
|
|
|
CREATE TABLE IF NOT EXISTS refresh_tokens (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
token_hash VARCHAR(255) NOT NULL, -- bcrypt hash of the refresh token
|
|
expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
|
|
client_ip VARCHAR(45), -- IPv6 max length
|
|
user_agent TEXT, -- Optional: track device info
|
|
revoked_at TIMESTAMP WITH TIME ZONE, -- NULL if not revoked
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Index for looking up valid tokens by user
|
|
CREATE INDEX IF NOT EXISTS idx_refresh_tokens_user_id ON refresh_tokens(user_id);
|
|
|
|
-- Index for cleanup of expired tokens
|
|
CREATE INDEX IF NOT EXISTS idx_refresh_tokens_expires_at ON refresh_tokens(expires_at);
|
|
|
|
-- Index for finding non-revoked tokens
|
|
CREATE INDEX IF NOT EXISTS idx_refresh_tokens_revoked_at ON refresh_tokens(revoked_at) WHERE revoked_at IS NULL;
|
|
|
|
-- Comment on table
|
|
COMMENT ON TABLE refresh_tokens IS 'Stores hashed refresh tokens for JWT token rotation';
|
|
COMMENT ON COLUMN refresh_tokens.token_hash IS 'bcrypt hash of the refresh token - never store raw tokens';
|
|
COMMENT ON COLUMN refresh_tokens.revoked_at IS 'Set when token is explicitly revoked via logout';
|