32 lines
1.2 KiB
MySQL
32 lines
1.2 KiB
MySQL
|
-- Add username to users table
|
||
|
|
ALTER TABLE users
|
||
|
|
ADD COLUMN username TEXT UNIQUE,
|
||
|
|
ADD COLUMN password_hash TEXT;
|
||
|
|
|
||
|
|
-- Create credentials table for WebAuthn passkeys
|
||
|
|
CREATE TABLE credentials (
|
||
|
|
id TEXT PRIMARY KEY,
|
||
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
|
|
credential_public_key BYTEA NOT NULL,
|
||
|
|
credential_id BYTEA NOT NULL UNIQUE,
|
||
|
|
sign_count BIGINT DEFAULT 0,
|
||
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
|
|
last_used_at TIMESTAMP WITH TIME ZONE,
|
||
|
|
transports TEXT[] DEFAULT ARRAY[]::TEXT[]
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Create table for WebAuthn registration/authentication challenges
|
||
|
|
CREATE TABLE auth_challenges (
|
||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
user_id UUID REFERENCES users(id) ON DELETE CASCADE,
|
||
|
|
challenge BYTEA NOT NULL,
|
||
|
|
challenge_type TEXT NOT NULL, -- 'registration' or 'authentication'
|
||
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
|
|
expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
|
||
|
|
used_at TIMESTAMP WITH TIME ZONE
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX idx_credentials_user_id ON credentials(user_id);
|
||
|
|
CREATE INDEX idx_auth_challenges_user_id ON auth_challenges(user_id);
|
||
|
|
CREATE INDEX idx_auth_challenges_expires_at ON auth_challenges(expires_at);
|