1 files changed, 41 insertions, 3 deletions
diff --git a/internal/controlplane/schema.sql b/internal/controlplane/schema.sql
index a518b1d..fc7f5e7 100644
--- a/internal/controlplane/schema.sql
+++ b/internal/controlplane/schema.sql
@@ -195,16 +195,50 @@ CREATE TABLE IF NOT EXISTS console_users (
         'platform_admin', 'platform_viewer', 'tenant_admin',
         'tenant_billing', 'tenant_developer', 'tenant_viewer'
     )),
-    token_prefix TEXT NOT NULL,
-    token_hash BYTEA NOT NULL UNIQUE CHECK (octet_length(token_hash) = 32),
+    token_prefix TEXT NOT NULL DEFAULT '',
+    token_hash BYTEA UNIQUE CHECK (token_hash IS NULL OR octet_length(token_hash) = 32),
+    password_hash BYTEA CHECK (password_hash IS NULL OR octet_length(password_hash) = 32),
+    password_salt BYTEA CHECK (password_salt IS NULL OR octet_length(password_salt) = 16),
+    password_iterations INTEGER CHECK (password_iterations IS NULL OR password_iterations >= 100000),
+    password_changed_at TIMESTAMPTZ,
     status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'revoked')),
     last_used_at TIMESTAMPTZ,
     created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
     revoked_at TIMESTAMPTZ,
     CHECK ((role LIKE 'platform_%' AND tenant_id IS NULL) OR (role LIKE 'tenant_%' AND tenant_id IS NOT NULL))
 );
+ALTER TABLE console_users ALTER COLUMN token_prefix SET DEFAULT '';
+ALTER TABLE console_users ALTER COLUMN token_prefix DROP NOT NULL;
+ALTER TABLE console_users ALTER COLUMN token_hash DROP NOT NULL;
+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;
 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 TABLE IF NOT EXISTS console_sessions (
+    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),
+    csrf_hash BYTEA NOT NULL CHECK (octet_length(csrf_hash) = 32),
+    expires_at TIMESTAMPTZ NOT NULL,
+    last_seen_at TIMESTAMPTZ NOT NULL DEFAULT now(),
+    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
+    revoked_at TIMESTAMPTZ,
+    remote_ip INET,
+    user_agent TEXT NOT NULL DEFAULT ''
+);
+
+CREATE TABLE IF NOT EXISTS console_login_throttles (
+    identity_hash BYTEA PRIMARY KEY CHECK (octet_length(identity_hash) = 32),
+    failures INTEGER NOT NULL DEFAULT 0,
+    window_started_at TIMESTAMPTZ NOT NULL DEFAULT now(),
+    locked_until TIMESTAMPTZ,
+    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
+);
 
 CREATE TABLE IF NOT EXISTS project_limits (
     project_id UUID PRIMARY KEY,
@@ -237,7 +271,7 @@ CREATE TABLE IF NOT EXISTS usage_monthly_rollups (
 CREATE TABLE IF NOT EXISTS audit_logs (
     id BIGSERIAL PRIMARY KEY,
     actor_id UUID REFERENCES console_users(id) ON DELETE SET NULL,
-    actor_type TEXT NOT NULL CHECK (actor_type IN ('bootstrap', 'console_user')),
+    actor_type TEXT NOT NULL CHECK (actor_type IN ('bootstrap', 'console_user', 'anonymous')),
     actor_role TEXT NOT NULL,
     tenant_id UUID REFERENCES tenants(id) ON DELETE SET NULL,
     request_id TEXT NOT NULL,
@@ -249,6 +283,8 @@ CREATE TABLE IF NOT EXISTS audit_logs (
     user_agent TEXT NOT NULL DEFAULT '',
     created_at TIMESTAMPTZ NOT NULL DEFAULT now()
 );
+ALTER TABLE audit_logs DROP CONSTRAINT IF EXISTS audit_logs_actor_type_check;
+ALTER TABLE audit_logs ADD CONSTRAINT audit_logs_actor_type_check CHECK (actor_type IN ('bootstrap', 'console_user', 'anonymous'));
 
 CREATE INDEX IF NOT EXISTS billing_ledger_tenant_idx ON billing_ledger (tenant_id, created_at DESC);
 CREATE INDEX IF NOT EXISTS usage_events_tenant_idx ON usage_events (tenant_id, created_at DESC);
@@ -257,5 +293,7 @@ CREATE INDEX IF NOT EXISTS usage_events_model_idx ON usage_events (public_model,
 CREATE INDEX IF NOT EXISTS billing_reservations_pending_idx ON billing_reservations (status, created_at) WHERE status = 'pending';
 CREATE INDEX IF NOT EXISTS billing_reservations_project_pending_idx ON billing_reservations (project_id, created_at) WHERE status = 'pending';
 CREATE INDEX IF NOT EXISTS console_users_tenant_idx ON console_users (tenant_id, created_at DESC);
+CREATE INDEX IF NOT EXISTS console_sessions_user_idx ON console_sessions (user_id, created_at DESC);
+CREATE INDEX IF NOT EXISTS console_sessions_expiry_idx ON console_sessions (expires_at) WHERE revoked_at IS NULL;
 CREATE INDEX IF NOT EXISTS audit_logs_created_idx ON audit_logs (created_at DESC);
 CREATE INDEX IF NOT EXISTS audit_logs_tenant_idx ON audit_logs (tenant_id, created_at DESC);