Authentication System

PadawanForge uses a modern OAuth-based authentication system with support for multiple connected accounts and flexible provider management.

Overview

The authentication system is built around a connected accounts architecture that provides:

  • UUID-Based Identity: Players are identified by UUIDs for privacy
  • Multiple Providers: Support for Google and Discord OAuth (Apple and Slack planned)
  • Account Linking: Users can connect multiple OAuth providers to one account
  • Primary Provider: One provider serves as the default for the account
  • Secure Sessions: Cloudflare KV-based session management
  • Segmented Data: Player information distributed across focused tables for privacy and performance

Supported OAuth Providers

Currently Implemented

  • Google OAuth 2.0 - ✅ Fully implemented with OpenID Connect support
  • Discord OAuth 2.0 - ✅ Fully implemented with standard OAuth 2.0 flow

Planned (Configuration Ready)

  • Apple Sign In - ❌ Configuration exists but no endpoints implemented
  • Slack OAuth 2.0 - ❌ Configuration exists but no endpoints implemented

Provider Features

  • Google: Uses access_type: 'offline' for refresh tokens, user info at /oauth2/v2/userinfo
  • Discord: Avatar URLs constructed from user ID and avatar hash, provides global_name and username
  • Apple: (Planned) Returns user info in token response, requires response_mode: 'form_post', no profile pictures
  • Slack: (Planned) Uses OAuth 2.0 v2, requires workspace-specific config, multiple image sizes available

Database Architecture

Core Tables

Players Table (Core Identity)

CREATE TABLE players (
    id TEXT PRIMARY KEY,                    -- UUID serves as primary key
    uuid TEXT UNIQUE NOT NULL,             -- Public UUID for privacy
    email TEXT UNIQUE NOT NULL,
    default_provider TEXT NOT NULL CHECK (default_provider IN ('google', 'discord', 'apple', 'slack')),
    username TEXT NOT NULL,
    avatar TEXT,
    level INTEGER DEFAULT 1,
    experience INTEGER DEFAULT 0,
    registration_completed BOOLEAN DEFAULT FALSE,
    tutorial_completed BOOLEAN DEFAULT FALSE,
    onboarding_completed_at TIMESTAMP,
    primary_role_id INTEGER DEFAULT 2,     -- Default to Padawan role
    last_login TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Segmented Player Data Tables

The player system uses a segmented architecture for better performance and privacy:

Player Profiles (player_profiles):

CREATE TABLE player_profiles (
    player_uuid TEXT PRIMARY KEY,
    username TEXT NOT NULL,
    avatar TEXT,
    bio TEXT,
    display_name TEXT,
    visibility_level TEXT DEFAULT 'public' CHECK (visibility_level IN ('public', 'friends', 'private')),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (player_uuid) REFERENCES players(uuid) ON DELETE CASCADE
);

Player Preferences (player_preferences):

CREATE TABLE player_preferences (
    player_uuid TEXT PRIMARY KEY,
    theme TEXT DEFAULT 'dark' CHECK (theme IN ('light', 'dark', 'auto')),
    language TEXT DEFAULT 'en',
    timezone TEXT DEFAULT 'UTC',
    notification_settings TEXT, -- JSON for notification preferences
    ui_settings TEXT, -- JSON for UI customizations
    accessibility_settings TEXT, -- JSON for accessibility options
    -- Specific preference fields
    notifications_chat BOOLEAN DEFAULT TRUE,
    notifications_npc BOOLEAN DEFAULT TRUE,
    notifications_levelup BOOLEAN DEFAULT TRUE,
    show_online_status BOOLEAN DEFAULT TRUE,
    show_experience BOOLEAN DEFAULT TRUE,
    allow_direct_messages BOOLEAN DEFAULT TRUE,
    chat_font_size TEXT DEFAULT 'medium' CHECK (chat_font_size IN ('small', 'medium', 'large')),
    reduce_motion BOOLEAN DEFAULT FALSE,
    auto_join_lobby BOOLEAN DEFAULT TRUE,
    show_player_count BOOLEAN DEFAULT TRUE,
    sound_effects BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (player_uuid) REFERENCES players(uuid) ON DELETE CASCADE
);

Player Game Data (player_game_data):

CREATE TABLE player_game_data (
    player_uuid TEXT PRIMARY KEY,
    level INTEGER DEFAULT 1,
    experience INTEGER DEFAULT 0,
    achievements TEXT, -- JSON array of earned achievements
    statistics TEXT, -- JSON for game statistics
    progress_data TEXT, -- JSON for learning progress
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (player_uuid) REFERENCES players(uuid) ON DELETE CASCADE
);

Player Personal Info (player_personal_info):

CREATE TABLE player_personal_info (
    player_uuid TEXT PRIMARY KEY,
    birthday DATE,
    location TEXT,
    gender TEXT CHECK (gender IN ('M', 'F', 'Other', 'Prefer not to say')),
    privacy_settings TEXT, -- JSON for privacy controls
    registration_completed BOOLEAN DEFAULT FALSE,
    tutorial_completed BOOLEAN DEFAULT FALSE,
    onboarding_completed_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (player_uuid) REFERENCES players(uuid) ON DELETE CASCADE
);

Connected Accounts Table

CREATE TABLE player_connected_accounts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    player_uuid TEXT NOT NULL,
    provider TEXT NOT NULL,
    provider_user_id TEXT NOT NULL,
    provider_email TEXT,
    provider_username TEXT,
    provider_avatar TEXT,
    is_primary BOOLEAN DEFAULT FALSE,
    connected_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_used TIMESTAMP,
    metadata TEXT, -- JSON for provider-specific data
    UNIQUE(provider, provider_user_id),
    FOREIGN KEY (player_uuid) REFERENCES players(uuid) ON DELETE CASCADE
);

Authentication Flows

Initial Registration Flow

  1. User clicks “Sign in with [Provider]” (Google or Discord)
  2. Redirect to OAuth provider authorization
  3. Provider redirects back with authorization code
  4. Exchange authorization code for access token
  5. Fetch user information from provider API
  6. Create new player record with UUID-based ID
  7. Create connected account record for the provider
  8. Set the provider account as primary
  9. Create secure session and redirect to onboarding

Existing User Login Flow

  1. User signs in with any connected provider
  2. System looks up player by connected account relationship
  3. Update last login and last used timestamps
  4. Create new session and redirect to appropriate page

Account Linking Flow

  1. Authenticated user initiates OAuth with new provider (Google or Discord)
  2. Complete standard OAuth authorization flow
  3. Validate that provider account isn’t already connected elsewhere
  4. Create new connected account record for current user
  5. Optionally set as new primary account

Session Management

Session Data Structure

interface SessionData {
  id: string;
  playerId: string;            // UUID-based player ID
  playerUuid: string;          // Public UUID
  defaultProvider: string;     // User's primary provider
  currentProvider: string;     // Provider used for this login
  provider: 'google' | 'discord' | 'apple' | 'slack';
  email: string;
  username: string;
  avatar: string;
  level: number;
  experience: number;
  birthday?: string;
  location?: string;
  gender?: 'M' | 'F' | 'Other' | 'Prefer not to say';
  registrationCompleted?: boolean;
  tutorialCompleted?: boolean;
  onboardingCompletedAt?: string;
  // Role information
  roles?: UserRole[];
  permissions?: string[];
  isAdmin?: boolean;
  createdAt: Date;
  expiresAt: Date;
}

Player Data Structure

The system uses a segmented approach where player data is distributed across multiple tables:

interface Player {
  id: string;                  // UUID-based player ID
  uuid: string;                // Public UUID for privacy
  defaultProvider: 'google' | 'discord' | 'apple' | 'slack';
  email: string;
  username: string;
  avatar: string;
  experience: number;
  level: number;
  stats: PlayerStats;
  achievements: Achievement[];
  preferences: PlayerPreferences;
  last_login: string;
  created_at: string;
  updated_at: string;
  birthday?: string;
  location?: string;
  gender?: 'M' | 'F' | 'Other' | 'Prefer not to say';
  registrationCompleted?: boolean;
  tutorialCompleted?: boolean;
  onboardingCompletedAt?: string;
  connectedAccounts?: ConnectedAccount[];
}

Session Storage

  • Storage Backend: Cloudflare KV (SESSION binding)
  • Session Duration: 7 days default
  • Security Features: HttpOnly cookies with SameSite protection
  • Auto-cleanup: Expired sessions are automatically removed

API Endpoints

Currently Implemented Endpoints

Authentication Endpoints

  • GET /api/auth/google - Initiate Google OAuth flow
  • GET /api/auth/discord - Initiate Discord OAuth flow
  • GET /api/auth/callback/google - Google OAuth callback handler
  • GET /api/auth/callback/discord - Discord OAuth callback handler

Registration Endpoints

  • POST /api/auth/complete-registration - Complete user registration with profile data
  • POST /api/auth/complete-tutorial - Mark tutorial as completed

Account Management Endpoints

  • GET /api/auth/connected-accounts - List all connected accounts for user
  • DELETE /api/auth/connected-accounts/disconnect/[provider] - Disconnect specific provider
  • POST /api/auth/connected-accounts/set-primary/[provider] - Set primary account provider

Utility Endpoints

  • GET /api/auth/available-providers - Check which OAuth providers are configured
  • GET|POST /api/auth/logout - Clear session and redirect to home

Planned Endpoints (Not Yet Implemented)

  • GET /api/auth/apple - Initiate Apple Sign In flow
  • GET /api/auth/callback/apple - Apple Sign In callback handler
  • GET /api/auth/slack - Initiate Slack OAuth flow
  • GET /api/auth/callback/slack - Slack OAuth callback handler

Security Features

CSRF Protection

  • State parameter validation for all OAuth flows
  • Secure session token generation using crypto APIs
  • SameSite cookie attributes for enhanced security

Account Security

  • Cannot disconnect the last connected account (prevents lockout)
  • Email verification required for sensitive operations
  • Comprehensive audit logging for all account changes

Privacy Controls

  • UUID-based public identifiers protect real user IDs
  • Granular privacy settings for personal information
  • Provider-specific data isolation and access controls

Adding New OAuth Providers

Implementation Steps

  1. Update Provider Configuration
// Add to OAUTH_PROVIDERS in /src/lib/oauth.ts
yourprovider: {
  authUrl: 'https://yourprovider.com/oauth/authorize',
  tokenUrl: 'https://yourprovider.com/oauth/token',
  userInfoUrl: 'https://yourprovider.com/api/user',
  clientIdEnvKey: 'OAUTH_YOURPROVIDER_CLIENT_ID',
  clientSecretEnvKey: 'OAUTH_YOURPROVIDER_CLIENT_SECRET',
  scopes: ['read:user', 'user:email'],
  redirectPath: '/api/auth/callback/yourprovider',
}
  1. Create User Normalization Function
const normalizeYourProviderUser = (user: any): NormalizedUser => ({
  id: user.id || user.sub,
  email: user.email,
  name: user.name || user.display_name,
  username: user.username || user.login,
  picture: user.avatar_url || user.picture || '',
});
  1. Create OAuth Routes
// /src/pages/api/auth/yourprovider.ts
export const GET = createOAuthHandler('yourprovider');

// /src/pages/api/auth/callback/yourprovider.ts
export const GET = createOAuthCallbackHandler('yourprovider');
  1. Update Database Constraints
ALTER TABLE players ADD CONSTRAINT chk_default_provider 
  CHECK (default_provider IN ('google', 'discord', 'apple', 'slack', 'yourprovider'));

Provider Requirements

  • OAuth 2.0 compliance with standard flow support
  • Reliable user information API (ID, email, display name)
  • Stable and unique user identifiers
  • Terms of service compliance review
  • Understanding of API rate limits and restrictions

Development Setup

Environment Variables

# OAuth Provider Credentials (.dev.vars)
OAUTH_GOOGLE_CLIENT_ID=your_google_client_id
OAUTH_GOOGLE_CLIENT_SECRET=your_google_client_secret
OAUTH_DISCORD_CLIENT_ID=your_discord_client_id
OAUTH_DISCORD_CLIENT_SECRET=your_discord_client_secret

# Planned providers (configuration ready)
OAUTH_APPLE_CLIENT_ID=your_apple_client_id
OAUTH_APPLE_CLIENT_SECRET=your_apple_client_secret
OAUTH_SLACK_CLIENT_ID=your_slack_client_id
OAUTH_SLACK_CLIENT_SECRET=your_slack_client_secret

Provider Application Setup

  1. Register OAuth applications with each provider
  2. Configure proper redirect URIs for your domain
  3. Set required environment variables in .dev.vars
  4. For production, set secrets in Cloudflare Workers environment
  5. Test complete authentication flow for each provider

Error Handling

Common Error Scenarios

  • Duplicate Account: Provider account already connected to different user
  • Last Account Protection: Attempting to disconnect the only connected account
  • Invalid Provider: Unsupported or misconfigured OAuth provider
  • Token Issues: Invalid, expired, or malformed OAuth tokens
  • Scope Problems: Insufficient permissions or denied scopes

Error Response Format

{
  "error": "Human-readable error description"
}

Common Error Messages

  • "Unauthorized" - User not authenticated
  • "Invalid provider" - Provider not supported
  • "Display name is required" - Registration validation failed
  • "Database not available" - System error
  • "Failed to set primary account" - Account management error
  • "Cannot disconnect the last connected account" - Protection error

Monitoring and Analytics

Key Metrics to Track

  • Authentication success and failure rates by provider
  • Provider usage distribution and preferences
  • Account linking patterns and frequency
  • Session duration and user activity patterns
  • Error rates and types by provider

Logging Strategy

  • OAuth flow events and milestones
  • Account management actions and changes
  • Security-related events and anomalies
  • Error conditions with sufficient context for debugging

Migration from Legacy System

The system migrated from provider-prefixed IDs (google_123, discord_456) to UUID-based identity:

Migration Process

  1. Extract provider and user ID from legacy format
  2. Create connected account records for existing users
  3. Update player IDs to use UUID-based system
  4. Maintain backward compatibility during transition period

Backward Compatibility

  • Legacy session format supported during gradual migration
  • Provider field maintained in views for compatibility (p.default_provider as provider)
  • Migration utilities for seamless rollout

Recent Schema Updates

  • Column Name Standardization: The database uses default_provider column, not provider
  • API Compatibility: API endpoints use default_provider as provider for backward compatibility
  • View Mapping: The player_complete view maps p.default_provider as provider for seamless integration

Future Provider Implementation

Apple Sign In Integration

To complete Apple OAuth implementation:

  1. Create /src/pages/api/auth/apple.ts endpoint
  2. Create /src/pages/api/auth/callback/apple.ts callback handler
  3. Handle Apple’s unique response_mode: 'form_post' requirement
  4. Test with Apple Developer account

Slack OAuth Integration

To complete Slack OAuth implementation:

  1. Create /src/pages/api/auth/slack.ts endpoint
  2. Create /src/pages/api/auth/callback/slack.ts callback handler
  3. Configure workspace-specific settings
  4. Test with Slack app configuration

Troubleshooting

Common Issues and Solutions

  1. OAuth Redirect Mismatch

    • Verify registered redirect URIs match exactly
    • Check for HTTP vs HTTPS mismatches
    • Ensure port numbers are correct in development

  2. Invalid Credentials

    • Verify client ID and secret are correct
    • Check environment variable names and values
    • Ensure secrets are properly set in production

  3. Scope Permission Issues

    • Verify requested scopes are available for provider
    • Check that user approved all required scopes
    • Review provider-specific scope requirements

  4. Session Expiry Problems

    • Implement proper session refresh mechanisms
    • Check session duration configuration
    • Monitor KV storage limits and cleanup

  5. Provider API Changes

    • Monitor provider API updates and changes
    • Implement proper error handling for API responses
    • Test provider integrations regularly

Debug Tools and Utilities

  • OAuth flow logging with detailed step tracking
  • Session inspection endpoints for debugging
  • Connected accounts debugging and validation tools
  • Provider-specific test utilities and mock responses

Best Practices

For Developers

  1. Always validate OAuth responses and tokens thoroughly
  2. Handle provider-specific edge cases and error conditions
  3. Implement comprehensive error handling and logging
  4. Use secure session management with proper expiration
  5. Follow provider rate limits and best practices
  6. Test all authentication flows regularly

For Users

  1. Connect multiple accounts for authentication flexibility
  2. Always keep at least one account connected to prevent lockout
  3. Review connected accounts regularly for security
  4. Use strong passwords and 2FA for OAuth provider accounts
  5. Understand privacy implications of connecting multiple accounts

This authentication system provides a robust, secure, and flexible foundation for user management in PadawanForge while maintaining privacy and supporting multiple authentication providers seamlessly.

← Admin Tools Demo System →
PadawanForge v1.4.1