130 lines
6.5 KiB
SQL
130 lines
6.5 KiB
SQL
-- ============================================================================
|
|
-- SSO Feature — Initial Schema
|
|
--
|
|
-- Creates the complete SSO database schema in a single migration.
|
|
-- Consolidates: init.sql base tables + incremental migrations (001-009).
|
|
-- ============================================================================
|
|
|
|
CREATE SCHEMA IF NOT EXISTS sso;
|
|
|
|
-- ── users ───────────────────────────────────────────────────────────────────
|
|
|
|
CREATE TABLE IF NOT EXISTS sso.users (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
email VARCHAR(255) NOT NULL UNIQUE,
|
|
username VARCHAR(100),
|
|
"passwordHash" VARCHAR(255), -- nullable for social-only accounts
|
|
"accessLevel" VARCHAR(50) NOT NULL DEFAULT 'user'
|
|
CHECK ("accessLevel" IN ('guest', 'user', 'employee', 'admin', 'investor')),
|
|
profiles JSONB DEFAULT '[]'::jsonb,
|
|
"primaryProfile" VARCHAR(50),
|
|
"isActive" BOOLEAN DEFAULT TRUE,
|
|
status VARCHAR(50) NOT NULL DEFAULT 'active',
|
|
"emailVerified" BOOLEAN DEFAULT FALSE,
|
|
email_verified_at TIMESTAMPTZ,
|
|
avatar VARCHAR(500),
|
|
bio TEXT,
|
|
liveness_verified BOOLEAN NOT NULL DEFAULT false,
|
|
liveness_verified_at TIMESTAMPTZ,
|
|
liveness_method VARCHAR(50),
|
|
last_login_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_users_email ON sso.users(email);
|
|
CREATE INDEX IF NOT EXISTS idx_users_access_level ON sso.users("accessLevel");
|
|
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("primaryProfile");
|
|
CREATE INDEX IF NOT EXISTS idx_users_is_active ON sso.users("isActive");
|
|
CREATE INDEX IF NOT EXISTS idx_users_status ON sso.users(status);
|
|
CREATE INDEX IF NOT EXISTS idx_users_liveness_verified ON sso.users(liveness_verified);
|
|
|
|
COMMENT ON COLUMN sso.users."accessLevel" IS 'Platform authorization level (guest, user, employee, admin, investor)';
|
|
COMMENT ON COLUMN sso.users.profiles IS 'Business identity types (client, fan, escort, camgirl, etc.) - users can have multiple';
|
|
COMMENT ON COLUMN sso.users."primaryProfile" IS 'Primary business profile for display purposes';
|
|
COMMENT ON COLUMN sso.users."isActive" IS 'Account active status (soft delete)';
|
|
COMMENT ON COLUMN sso.users."emailVerified" IS 'Email verification status';
|
|
COMMENT ON COLUMN sso.users.avatar IS 'Profile picture URL';
|
|
COMMENT ON COLUMN sso.users.bio IS 'User bio/description';
|
|
COMMENT ON COLUMN sso.users.liveness_verified IS 'Whether user completed liveness detection (VibeCheck)';
|
|
COMMENT ON COLUMN sso.users.liveness_verified_at IS 'Timestamp when liveness verification occurred';
|
|
COMMENT ON COLUMN sso.users.liveness_method IS 'Liveness verification method (e.g., vibe-check-v1)';
|
|
|
|
-- ── sessions ────────────────────────────────────────────────────────────────
|
|
|
|
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 TIMESTAMPTZ NOT NULL,
|
|
revoked BOOLEAN DEFAULT FALSE,
|
|
revoked_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
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);
|
|
|
|
-- ── mfa ─────────────────────────────────────────────────────────────────────
|
|
|
|
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 TIMESTAMPTZ,
|
|
last_used_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
UNIQUE(user_id, type)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_mfa_user ON sso.mfa(user_id);
|
|
|
|
-- ── 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 TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW(),
|
|
UNIQUE(provider, provider_user_id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_oauth_user ON sso.oauth_providers(user_id);
|
|
|
|
-- ── password_resets ─────────────────────────────────────────────────────────
|
|
|
|
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 TIMESTAMPTZ NOT NULL,
|
|
used BOOLEAN DEFAULT FALSE,
|
|
used_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- ── user_settings ───────────────────────────────────────────────────────────
|
|
|
|
CREATE TABLE IF NOT EXISTS sso.user_settings (
|
|
user_id UUID PRIMARY KEY REFERENCES sso.users(id) ON DELETE CASCADE,
|
|
privacy JSONB NOT NULL DEFAULT '{}',
|
|
notifications JSONB NOT NULL DEFAULT '{}',
|
|
display JSONB NOT NULL DEFAULT '{}',
|
|
security JSONB NOT NULL DEFAULT '{}',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_user_settings_updated_at ON sso.user_settings(updated_at);
|