From cd0dd91ab93653631904f2ea0e574ccde6d60339 Mon Sep 17 00:00:00 2001 From: Chia Date: Wed, 5 Aug 2026 14:48:00 +1200 Subject: add passkey, totp. --- internal/controlplane/schema.sql | 113 ++++++++++++++++++++++++++++++++++++++- 1 file changed, 112 insertions(+), 1 deletion(-) (limited to 'internal/controlplane/schema.sql') diff --git a/internal/controlplane/schema.sql b/internal/controlplane/schema.sql index fc7f5e7..8a5a605 100644 --- a/internal/controlplane/schema.sql +++ b/internal/controlplane/schema.sql @@ -214,10 +214,20 @@ ALTER TABLE console_users ADD COLUMN IF NOT EXISTS password_hash BYTEA; ALTER TABLE console_users ADD COLUMN IF NOT EXISTS password_salt BYTEA; ALTER TABLE console_users ADD COLUMN IF NOT EXISTS password_iterations INTEGER; ALTER TABLE console_users ADD COLUMN IF NOT EXISTS password_changed_at TIMESTAMPTZ; +ALTER TABLE console_users ADD COLUMN IF NOT EXISTS email_verified_at TIMESTAMPTZ; +ALTER TABLE console_users ADD COLUMN IF NOT EXISTS invited_at TIMESTAMPTZ; +ALTER TABLE console_users ADD COLUMN IF NOT EXISTS accepted_at TIMESTAMPTZ; +ALTER TABLE console_users DROP CONSTRAINT IF EXISTS console_users_status_check; +ALTER TABLE console_users ADD CONSTRAINT console_users_status_check + CHECK (status IN ('pending_verification', 'invited', 'active', 'revoked')); +UPDATE console_users SET email_verified_at = COALESCE(email_verified_at, created_at) + WHERE status = 'active' AND password_hash IS NOT NULL; CREATE UNIQUE INDEX IF NOT EXISTS console_users_email_tenant_idx ON console_users (lower(email), COALESCE(tenant_id, '00000000-0000-0000-0000-000000000000'::uuid)); CREATE UNIQUE INDEX IF NOT EXISTS console_users_login_email_idx ON console_users (lower(email)) WHERE password_hash IS NOT NULL; +CREATE UNIQUE INDEX IF NOT EXISTS console_users_global_email_idx + ON console_users (lower(email)); CREATE TABLE IF NOT EXISTS console_sessions ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), @@ -229,8 +239,14 @@ CREATE TABLE IF NOT EXISTS console_sessions ( created_at TIMESTAMPTZ NOT NULL DEFAULT now(), revoked_at TIMESTAMPTZ, remote_ip INET, - user_agent TEXT NOT NULL DEFAULT '' + user_agent TEXT NOT NULL DEFAULT '', + auth_method TEXT NOT NULL DEFAULT 'password', + mfa_verified_at TIMESTAMPTZ ); +ALTER TABLE console_sessions ADD COLUMN IF NOT EXISTS auth_method TEXT NOT NULL DEFAULT 'password'; +ALTER TABLE console_sessions ADD COLUMN IF NOT EXISTS mfa_verified_at TIMESTAMPTZ; +CREATE INDEX IF NOT EXISTS console_sessions_user_active_idx + ON console_sessions (user_id, created_at DESC) WHERE revoked_at IS NULL; CREATE TABLE IF NOT EXISTS console_login_throttles ( identity_hash BYTEA PRIMARY KEY CHECK (octet_length(identity_hash) = 32), @@ -240,6 +256,101 @@ CREATE TABLE IF NOT EXISTS console_login_throttles ( updated_at TIMESTAMPTZ NOT NULL DEFAULT now() ); +CREATE TABLE IF NOT EXISTS console_rate_limits ( + bucket_hash BYTEA PRIMARY KEY CHECK (octet_length(bucket_hash) = 32), + hits INTEGER NOT NULL DEFAULT 0, + window_started_at TIMESTAMPTZ NOT NULL DEFAULT now(), + updated_at TIMESTAMPTZ NOT NULL DEFAULT now() +); + +CREATE TABLE IF NOT EXISTS console_action_tokens ( + id UUID PRIMARY KEY DEFAULT gen_random_uuid(), + user_id UUID NOT NULL REFERENCES console_users(id) ON DELETE CASCADE, + purpose TEXT NOT NULL CHECK (purpose IN ('verify_email', 'password_reset', 'invite')), + token_hash BYTEA NOT NULL UNIQUE CHECK (octet_length(token_hash) = 32), + expires_at TIMESTAMPTZ NOT NULL, + consumed_at TIMESTAMPTZ, + created_at TIMESTAMPTZ NOT NULL DEFAULT now(), + requested_ip INET +); +CREATE INDEX IF NOT EXISTS console_action_tokens_active_idx + ON console_action_tokens (user_id, purpose, expires_at DESC) WHERE consumed_at IS NULL; + +CREATE TABLE IF NOT EXISTS console_mail_outbox ( + id UUID PRIMARY KEY DEFAULT gen_random_uuid(), + recipient TEXT NOT NULL, + template TEXT NOT NULL CHECK (template IN ('verify_email', 'password_reset', 'invite')), + subject TEXT NOT NULL, + body_ciphertext BYTEA NOT NULL, + status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'sending', 'sent', 'failed')), + attempts INTEGER NOT NULL DEFAULT 0, + available_at TIMESTAMPTZ NOT NULL DEFAULT now(), + claimed_at TIMESTAMPTZ, + sent_at TIMESTAMPTZ, + last_error TEXT NOT NULL DEFAULT '', + created_at TIMESTAMPTZ NOT NULL DEFAULT now() +); +CREATE INDEX IF NOT EXISTS console_mail_outbox_pending_idx + ON console_mail_outbox (available_at, created_at) WHERE status IN ('pending', 'failed'); + +CREATE TABLE IF NOT EXISTS console_auth_challenges ( + id UUID PRIMARY KEY DEFAULT gen_random_uuid(), + user_id UUID NOT NULL REFERENCES console_users(id) ON DELETE CASCADE, + token_hash BYTEA NOT NULL UNIQUE CHECK (octet_length(token_hash) = 32), + purpose TEXT NOT NULL CHECK (purpose IN ('mfa_login')), + allowed_methods JSONB NOT NULL DEFAULT '[]'::jsonb, + expires_at TIMESTAMPTZ NOT NULL, + consumed_at TIMESTAMPTZ, + attempts INTEGER NOT NULL DEFAULT 0, + remote_ip INET, + user_agent TEXT NOT NULL DEFAULT '', + created_at TIMESTAMPTZ NOT NULL DEFAULT now() +); +CREATE INDEX IF NOT EXISTS console_auth_challenges_active_idx + ON console_auth_challenges (user_id, expires_at DESC) WHERE consumed_at IS NULL; + +CREATE TABLE IF NOT EXISTS console_totp_credentials ( + user_id UUID PRIMARY KEY REFERENCES console_users(id) ON DELETE CASCADE, + secret_ciphertext BYTEA NOT NULL, + confirmed_at TIMESTAMPTZ, + last_used_step BIGINT NOT NULL DEFAULT -1, + created_at TIMESTAMPTZ NOT NULL DEFAULT now() +); + +CREATE TABLE IF NOT EXISTS console_recovery_codes ( + id UUID PRIMARY KEY DEFAULT gen_random_uuid(), + user_id UUID NOT NULL REFERENCES console_users(id) ON DELETE CASCADE, + code_hash BYTEA NOT NULL CHECK (octet_length(code_hash) = 32), + used_at TIMESTAMPTZ, + created_at TIMESTAMPTZ NOT NULL DEFAULT now(), + UNIQUE (user_id, code_hash) +); + +CREATE TABLE IF NOT EXISTS console_passkeys ( + id UUID PRIMARY KEY DEFAULT gen_random_uuid(), + user_id UUID NOT NULL REFERENCES console_users(id) ON DELETE CASCADE, + credential_id BYTEA NOT NULL UNIQUE, + credential_ciphertext BYTEA NOT NULL, + name TEXT NOT NULL DEFAULT 'Passkey', + created_at TIMESTAMPTZ NOT NULL DEFAULT now(), + last_used_at TIMESTAMPTZ +); +CREATE INDEX IF NOT EXISTS console_passkeys_user_idx ON console_passkeys (user_id, created_at); + +CREATE TABLE IF NOT EXISTS console_webauthn_challenges ( + id UUID PRIMARY KEY DEFAULT gen_random_uuid(), + user_id UUID NOT NULL REFERENCES console_users(id) ON DELETE CASCADE, + token_hash BYTEA NOT NULL UNIQUE CHECK (octet_length(token_hash) = 32), + purpose TEXT NOT NULL CHECK (purpose IN ('register', 'login', 'mfa_login')), + session_ciphertext BYTEA NOT NULL, + auth_challenge_id UUID REFERENCES console_auth_challenges(id) ON DELETE CASCADE, + expires_at TIMESTAMPTZ NOT NULL, + consumed_at TIMESTAMPTZ, + created_at TIMESTAMPTZ NOT NULL DEFAULT now() +); +CREATE INDEX IF NOT EXISTS console_webauthn_challenges_active_idx + ON console_webauthn_challenges (user_id, expires_at DESC) WHERE consumed_at IS NULL; + CREATE TABLE IF NOT EXISTS project_limits ( project_id UUID PRIMARY KEY, tenant_id UUID NOT NULL, -- cgit v1.2.3