33 lines
1.7 KiB
SQL
33 lines
1.7 KiB
SQL
-- Create OTP sessions table for storing OTP codes and validation
|
|
CREATE TABLE otp_sessions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
token VARCHAR(255) NOT NULL UNIQUE,
|
|
code VARCHAR(10) NOT NULL,
|
|
phone_number VARCHAR(20) NOT NULL,
|
|
purpose VARCHAR(50) NOT NULL, -- 'login', 'registration', etc.
|
|
expires_at TIMESTAMP NOT NULL,
|
|
is_used BOOLEAN DEFAULT false,
|
|
attempts_count INT DEFAULT 0, -- Track failed attempts
|
|
max_attempts INT DEFAULT 3, -- Maximum allowed attempts
|
|
created_at TIMESTAMP DEFAULT now(),
|
|
updated_at TIMESTAMP DEFAULT now()
|
|
);
|
|
|
|
-- Create indexes for better performance
|
|
CREATE INDEX idx_otp_sessions_token ON otp_sessions(token);
|
|
CREATE INDEX idx_otp_sessions_phone_number ON otp_sessions(phone_number);
|
|
CREATE INDEX idx_otp_sessions_purpose ON otp_sessions(purpose);
|
|
CREATE INDEX idx_otp_sessions_expires_at ON otp_sessions(expires_at);
|
|
CREATE INDEX idx_otp_sessions_is_used ON otp_sessions(is_used);
|
|
|
|
-- Add comments
|
|
COMMENT ON TABLE otp_sessions IS 'OTP sessions for authentication and registration';
|
|
COMMENT ON COLUMN otp_sessions.token IS 'Unique token for OTP session';
|
|
COMMENT ON COLUMN otp_sessions.code IS 'OTP code (6-10 digits)';
|
|
COMMENT ON COLUMN otp_sessions.phone_number IS 'Target phone number for OTP';
|
|
COMMENT ON COLUMN otp_sessions.purpose IS 'Purpose of OTP: login, registration, etc.';
|
|
COMMENT ON COLUMN otp_sessions.expires_at IS 'OTP expiration timestamp';
|
|
COMMENT ON COLUMN otp_sessions.is_used IS 'Whether OTP has been used';
|
|
COMMENT ON COLUMN otp_sessions.attempts_count IS 'Number of failed validation attempts';
|
|
COMMENT ON COLUMN otp_sessions.max_attempts IS 'Maximum allowed validation attempts';
|