95 lines
3.6 KiB
SQL
95 lines
3.6 KiB
SQL
-- SSO Database Initialization
|
|
|
|
CREATE SCHEMA IF NOT EXISTS sso;
|
|
|
|
-- Users table
|
|
CREATE TABLE IF NOT EXISTS sso.users (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
email VARCHAR(255) NOT NULL UNIQUE,
|
|
password_hash VARCHAR(255),
|
|
username VARCHAR(100),
|
|
avatar TEXT,
|
|
bio TEXT,
|
|
access_level VARCHAR(50) NOT NULL DEFAULT 'user' CHECK (access_level IN ('guest', 'user', 'employee', 'admin', 'investor')),
|
|
profiles JSONB DEFAULT '[]'::jsonb,
|
|
primary_profile VARCHAR(50),
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
status VARCHAR(50) NOT NULL DEFAULT 'active',
|
|
email_verified BOOLEAN DEFAULT FALSE,
|
|
email_verified_at TIMESTAMP WITH TIME ZONE,
|
|
last_login_at TIMESTAMP WITH TIME ZONE,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Sessions table
|
|
CREATE TABLE IF NOT EXISTS sso.sessions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID NOT NULL REFERENCES sso.users(id) ON DELETE CASCADE,
|
|
token_hash VARCHAR(255) NOT NULL UNIQUE,
|
|
device_info JSONB DEFAULT '{}',
|
|
ip_address INET,
|
|
user_agent TEXT,
|
|
expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
|
|
revoked BOOLEAN DEFAULT FALSE,
|
|
revoked_at TIMESTAMP WITH TIME ZONE,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- MFA table
|
|
CREATE TABLE IF NOT EXISTS sso.mfa (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id UUID NOT NULL REFERENCES sso.users(id) ON DELETE CASCADE,
|
|
type VARCHAR(50) NOT NULL,
|
|
secret_encrypted TEXT,
|
|
backup_codes_encrypted TEXT,
|
|
verified BOOLEAN DEFAULT FALSE,
|
|
verified_at TIMESTAMP WITH TIME ZONE,
|
|
last_used_at TIMESTAMP WITH TIME ZONE,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
UNIQUE(user_id, type)
|
|
);
|
|
|
|
-- OAuth providers
|
|
CREATE TABLE IF NOT EXISTS sso.oauth_providers (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id UUID NOT NULL REFERENCES sso.users(id) ON DELETE CASCADE,
|
|
provider VARCHAR(50) NOT NULL,
|
|
provider_user_id VARCHAR(255) NOT NULL,
|
|
access_token_encrypted TEXT,
|
|
refresh_token_encrypted TEXT,
|
|
expires_at TIMESTAMP WITH TIME ZONE,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
UNIQUE(provider, provider_user_id)
|
|
);
|
|
|
|
-- Password reset tokens
|
|
CREATE TABLE IF NOT EXISTS sso.password_resets (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id UUID NOT NULL REFERENCES sso.users(id) ON DELETE CASCADE,
|
|
token_hash VARCHAR(255) NOT NULL UNIQUE,
|
|
expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
|
|
used BOOLEAN DEFAULT FALSE,
|
|
used_at TIMESTAMP WITH TIME ZONE,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE INDEX IF NOT EXISTS idx_users_email ON sso.users(email);
|
|
CREATE INDEX IF NOT EXISTS idx_users_access_level ON sso.users(access_level);
|
|
CREATE INDEX IF NOT EXISTS idx_users_profiles ON sso.users USING GIN (profiles);
|
|
CREATE INDEX IF NOT EXISTS idx_users_primary_profile ON sso.users(primary_profile);
|
|
CREATE INDEX IF NOT EXISTS idx_users_is_active ON sso.users(is_active);
|
|
CREATE INDEX IF NOT EXISTS idx_users_status ON sso.users(status);
|
|
CREATE INDEX IF NOT EXISTS idx_sessions_user ON sso.sessions(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_sessions_expires ON sso.sessions(expires_at);
|
|
CREATE INDEX IF NOT EXISTS idx_mfa_user ON sso.mfa(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_oauth_user ON sso.oauth_providers(user_id);
|
|
|
|
-- Permissions
|
|
GRANT ALL PRIVILEGES ON SCHEMA sso TO lilith;
|
|
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA sso TO lilith;
|
|
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA sso TO lilith;
|
|
|
|
DO $$ BEGIN RAISE NOTICE 'SSO database initialized'; END $$;
|