1 files changed, 112 insertions, 1 deletions
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,