Design: Email/Password Authentication (Phase 1)

This document describes the design for native email/password authentication, the biggest feature gap in Aero2 today.

Overview

Currently Aero2 can only authenticate via external OAuth providers (GitHub, Google). This feature adds native email/password signup, signin, email verification, and password reset — all scoped per-application.

Schema

User Credentials

CREATE TABLE user_credentials (
  id TEXT PRIMARY KEY,
  app_id TEXT NOT NULL REFERENCES applications(id),
  user_id TEXT NOT NULL UNIQUE REFERENCES users(id) ON DELETE CASCADE,
  password_hash TEXT NOT NULL,
  password_salt TEXT NOT NULL,
  hash_algorithm TEXT NOT NULL DEFAULT 'PBKDF2-SHA256',
  hash_iterations INTEGER NOT NULL DEFAULT 100000,
  created_at DATETIME DEFAULT (datetime('now')),
  updated_at DATETIME DEFAULT (datetime('now'))
);

Email Verification Codes

CREATE TABLE email_verification_codes (
  id TEXT PRIMARY KEY,
  app_id TEXT NOT NULL REFERENCES applications(id),
  user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  code TEXT NOT NULL,
  type TEXT NOT NULL CHECK(type IN ('verification', 'password_reset', 'magic_link')),
  expires_at DATETIME NOT NULL,
  used_at DATETIME,
  created_at DATETIME DEFAULT (datetime('now'))
);
 
CREATE INDEX idx_email_codes ON email_verification_codes(app_id, user_id, type, expires_at);

Password Hashing

Uses Web Crypto API PBKDF2 (same approach already used for client secrets):

// src/backend/passwords.ts
async function hashPassword(password: string, salt?: string): Promise<{ hash: string; salt: string }>;
async function verifyPassword(password: string, hash: string, salt: string): Promise<boolean>;
function validatePasswordStrength(password: string): { valid: boolean; errors: string[] };

Password Strength Rules

• Minimum 8 characters
• At least 1 letter and 1 number
• Reject top 10,000 common passwords

API Endpoints

Signup

POST /api/auth/signup
Body: { email, password, name? }

Flow:

1. Validate email format (Zod), password strength
2. Check app settings: is signup enabled? Is email/password auth enabled?
3. Check email not already registered in this app
4. Create user + credential rows in a transaction (with app_id)
5. Generate 6-digit verification code, store with 15-min TTL
6. Send verification email (branded with app's name/logo)
7. Return 201 { message: "Verification email sent" }

Email Verification

POST /api/auth/verify-email
Body: { email, code }

Flow:

1. Look up unused code for user in this app, check expiry
2. Mark code as used, set users.email_verified = 1
3. Create session (same as OAuth callback flow)
4. Return 200 with session cookie

Login

POST /api/auth/login
Body: { email, password }

Flow:

1. Check account lockout status
2. Look up user by email + app_id, verify password
3. If failed: increment failed_login_attempts, audit log, return generic "Invalid credentials"
4. If user has MFA enabled: issue short-lived mfa_token, return { mfa_required: true }
5. If success: reset failed_login_attempts, create session, set cookie
6. Return 200 with session cookie

Resend Verification

POST /api/auth/resend-verification
Body: { email }

Rate limited to 3 per email per 15 minutes.

Password Reset

POST /api/auth/forgot-password
Body: { email }
 
POST /api/auth/reset-password
Body: { email, code, new_password }

Password reset revokes all existing sessions for the user.

Email Sending

// src/backend/email.ts
interface EmailSender {
  sendEmail(to: string, subject: string, htmlBody: string, textBody: string): Promise<void>;
}

Configurable providers via environment variables:

• EMAIL_FROM — sender address
• EMAIL_PROVIDER — mailchannels | resend | sendgrid
• EMAIL_API_KEY — API key for Resend/SendGrid

Emails are branded per-app using the application's name, logo, and primary color.

Frontend Changes

Login Page

• Email/password form alongside existing IdP buttons
• "Or continue with" divider above social buttons
• Client-side password strength indicator
• "Forgot password?" link

Signup Page (/signup)

• Email, password, confirm password, optional name fields
• Password requirements displayed inline
• Link to login: "Already have an account?"
• Redirect to verification code entry after submission

Verification Code Page

• 6-digit code input (auto-focus, auto-advance between digits)
• "Resend code" link with countdown timer
• Auto-submit on last digit entry

Password Reset Pages

• /forgot-password — email input, then code + new password entry
• Success redirects to login with flash message

Rate Limiting

Audit Events

• signup — user registered
• email_verified — email verification completed
• login_password — password-based login
• password_reset_requested — reset code sent
• password_reset_completed — password changed via reset

Dependencies

• Multi-tenancy (Phase 0) must be complete — all tables require app_id
• Account lockout (#4) should be implemented before exposing password endpoints
• Rate limiting (#1) should be durable before exposing password endpoints