File size: 1,185 Bytes
383cb38 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | -- User Sessions Table
-- Stores active sessions for security management (device tracking, revocation)
-- Works alongside NextAuth JWT strategy by tracking issued tokens
CREATE TABLE IF NOT EXISTS user_sessions (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
session_token VARCHAR(255) UNIQUE NOT NULL,
user_agent TEXT,
ip_address VARCHAR(45),
device_type VARCHAR(50), -- 'desktop', 'mobile', 'tablet', 'unknown'
browser VARCHAR(50),
os VARCHAR(50),
is_active BOOLEAN DEFAULT TRUE,
last_active_at TIMESTAMP DEFAULT NOW(),
expires_at TIMESTAMP NOT NULL,
created_at TIMESTAMP DEFAULT NOW()
);
-- Indexes for faster lookups
CREATE INDEX IF NOT EXISTS idx_user_sessions_user_id ON user_sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_user_sessions_token ON user_sessions(session_token);
CREATE INDEX IF NOT EXISTS idx_user_sessions_active ON user_sessions(user_id, is_active);
-- Update timestamp trigger
CREATE TRIGGER update_user_sessions_last_active BEFORE UPDATE ON user_sessions
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|