# Solution Design Document: Unified Users & Modern Authentication

**Specification ID:** 007-unified-users-auth
**Version:** 1.0
**Status:** DRAFT
**Last Updated:** December 2025

---

## 1. Architecture Overview

### 1.1 System Context

```
┌──────────────────────────────────────────────────────────────────────────┐
│                           SYSTEM CONTEXT                                  │
├──────────────────────────────────────────────────────────────────────────┤
│                                                                          │
│  ┌─────────────┐    ┌─────────────┐    ┌─────────────┐                  │
│  │  Web App    │    │ Mobile App  │    │  Partners   │                  │
│  │  (Browser)  │    │  (iOS/And)  │    │  (API)      │                  │
│  └──────┬──────┘    └──────┬──────┘    └──────┬──────┘                  │
│         │                  │                  │                          │
│         │ Session/JWT      │ Legacy ApiKey    │ OAuth2 Bearer            │
│         │                  │                  │                          │
│         └──────────────────┼──────────────────┘                          │
│                            │                                             │
│                            ▼                                             │
│  ┌──────────────────────────────────────────────────────────────────┐   │
│  │                     AUTH MIDDLEWARE                               │   │
│  │  ┌─────────────┐ ┌─────────────┐ ┌─────────────┐                 │   │
│  │  │ JWT/Session │ │ Legacy API  │ │ Rate Limit  │                 │   │
│  │  │ Validator   │ │ Key Check   │ │ (Redis)     │                 │   │
│  │  └─────────────┘ └─────────────┘ └─────────────┘                 │   │
│  └──────────────────────────────────────────────────────────────────┘   │
│                            │                                             │
│                            ▼                                             │
│  ┌──────────────────────────────────────────────────────────────────┐   │
│  │                     UNIFIED USERS SERVICE                         │   │
│  │  ┌─────────────┐ ┌─────────────┐ ┌─────────────┐                 │   │
│  │  │ User Model  │ │ Auth Svc    │ │ Sync Svc    │                 │   │
│  │  │ (Central)   │ │ (JWT/MFA)   │ │ (WIW/HB)    │                 │   │
│  │  └─────────────┘ └─────────────┘ └─────────────┘                 │   │
│  └──────────────────────────────────────────────────────────────────┘   │
│                            │                                             │
│         ┌──────────────────┼──────────────────┐                          │
│         ▼                  ▼                  ▼                          │
│  ┌─────────────┐    ┌─────────────┐    ┌─────────────┐                  │
│  │   MySQL     │    │   Redis     │    │  External   │                  │
│  │ (Central)   │    │  (Cache)    │    │  Providers  │                  │
│  └─────────────┘    └─────────────┘    └─────────────┘                  │
│                                                                          │
└──────────────────────────────────────────────────────────────────────────┘
```

### 1.2 Design Principles

1. **Single Source of Truth**: One record per person in central `users` table
2. **Store Assignments**: Multi-store support via junction table, not duplication
3. **Access Control**: `canLogin` flag distinguishes employees from users
4. **Provider Agnostic**: Support homegrown, WhenIWork, Homebase sources
5. **Backward Compatible**: Legacy API keys work alongside modern OAuth2
6. **Security First**: All authentication decisions default to deny

---

## 2. Data Architecture

### 2.1 Entity Relationships

```
┌─────────────────────────────────────────────────────────────────┐
│                     UNIFIED DATA MODEL                          │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│  users (central)                                                │
│  ├── id (PK)                                                    │
│  ├── identity fields (username, email, password)                │
│  ├── profile fields (name, phone, photo)                        │
│  ├── employment fields (position, hireDate, etc.)               │
│  ├── external sync (source, externalId)                         │
│  ├── access control (canLogin, accountType)                     │
│  └── auth tokens (activation, passwordReset)                    │
│       │                                                         │
│       │ 1:N                                                     │
│       ▼                                                         │
│  userStoreAssignments (central)                                 │
│  ├── userId (FK → users.id)                                     │
│  ├── typeNum (store identifier)                                 │
│  ├── store-specific fields (clockPin, drsEmployeeId, role)      │
│  └── status (isActive, assignedAt)                              │
│       │                                                         │
│       │ N:M                                                     │
│       ▼                                                         │
│  userGroups (central)                                           │
│  ├── userId (FK → users.id)                                     │
│  └── groupId (FK → uf_group.id)                                │
│                                                                 │
│  userPermissions (central)                                      │
│  ├── userId (FK → users.id)                                     │
│  ├── hook (permission name)                                     │
│  └── conditions (JSON)                                          │
│                                                                 │
│  oauthRefreshTokens (central)                                   │
│  ├── userId (FK → users.id)                                     │
│  ├── token (hashed)                                             │
│  └── expiresAt, revokedAt                                       │
│                                                                 │
│  userSessions (central)                                         │
│  ├── userId (FK → users.id)                                     │
│  ├── sessionId                                                  │
│  └── device info, expiresAt                                     │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘
```

### 2.2 Database Schema

#### 2.2.1 Core Users Table

```sql
CREATE TABLE users (
    -- Primary Key
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

    -- Identity (Authentication)
    username VARCHAR(50) UNIQUE,
    email VARCHAR(150),
    password VARCHAR(255),

    -- Profile
    displayName VARCHAR(150),
    firstName VARCHAR(50),
    lastName VARCHAR(50),
    phone VARCHAR(20),
    photoUrl VARCHAR(500),
    avatarOverride TINYINT(1) DEFAULT 0,

    -- Employment Info
    position VARCHAR(100),
    hourlyRate DECIMAL(10,2),
    hireDate DATE,
    terminationDate DATE,
    leaveStartDate DATE,
    leaveEndDate DATE,

    -- Emergency Contact
    emergencyContactName VARCHAR(100),
    emergencyContactPhone VARCHAR(20),

    -- External Provider Sync
    source ENUM('homegrown', 'wheniwork', 'homebase', 'system') DEFAULT 'system',
    externalId VARCHAR(50),
    lastSyncedAt TIMESTAMP NULL,

    -- Access Control
    canLogin TINYINT(1) DEFAULT 0,
    accountType ENUM('employee', 'user', 'admin', 'system') DEFAULT 'employee',

    -- Account Status
    enabled TINYINT(1) DEFAULT 1,
    active TINYINT(1) DEFAULT 0,

    -- Auth Tokens
    activationToken VARCHAR(255),
    activationTokenExpiresAt TIMESTAMP NULL,
    passwordResetToken VARCHAR(255),
    passwordResetExpiresAt TIMESTAMP NULL,

    -- MFA
    mfaEnabled TINYINT(1) DEFAULT 0,
    mfaSecret VARCHAR(255),
    mfaBackupCodes JSON,
    mfaVerifiedAt TIMESTAMP NULL,

    -- Preferences
    locale VARCHAR(10) DEFAULT 'en_US',
    dailyReport TINYINT(1) DEFAULT 0,
    timezone VARCHAR(50) DEFAULT 'America/Los_Angeles',

    -- Metadata
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    lastLoginAt TIMESTAMP NULL,
    lastLoginIp VARCHAR(45),
    failedLoginAttempts INT DEFAULT 0,
    lockedUntil TIMESTAMP NULL,

    -- Indexes
    INDEX idx_username (username),
    INDEX idx_email (email),
    INDEX idx_external (source, externalId),
    INDEX idx_canLogin (canLogin),
    INDEX idx_accountType (accountType),
    INDEX idx_active (enabled, active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

#### 2.2.2 Store Assignments Table

```sql
CREATE TABLE userStoreAssignments (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    userId INT UNSIGNED NOT NULL,
    typeNum VARCHAR(10) NOT NULL,

    -- Store-Specific Data
    clockPin VARCHAR(10),
    drsEmployeeId VARCHAR(50),
    role TINYINT DEFAULT 0,

    -- Status
    isActive TINYINT(1) DEFAULT 1,
    assignedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deactivatedAt TIMESTAMP NULL,

    -- Constraints
    UNIQUE KEY unique_user_store (userId, typeNum),
    FOREIGN KEY (userId) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_typeNum (typeNum),
    INDEX idx_isActive (isActive)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

#### 2.2.3 Authentication Tables

```sql
-- OAuth2 Refresh Tokens
CREATE TABLE oauthRefreshTokens (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    userId INT UNSIGNED NOT NULL,
    token VARCHAR(255) NOT NULL UNIQUE,
    clientId VARCHAR(100),
    deviceName VARCHAR(255),
    deviceFingerprint VARCHAR(255),
    ipAddress VARCHAR(45),
    userAgent TEXT,

    expiresAt TIMESTAMP NOT NULL,
    revokedAt TIMESTAMP NULL,
    lastUsedAt TIMESTAMP NULL,

    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (userId) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_token (token),
    INDEX idx_userExpires (userId, expiresAt),
    INDEX idx_revokedAt (revokedAt)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- User Sessions
CREATE TABLE userSessions (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    userId INT UNSIGNED NOT NULL,
    sessionId VARCHAR(255) NOT NULL UNIQUE,

    ipAddress VARCHAR(45),
    userAgent TEXT,
    deviceType VARCHAR(50),
    location VARCHAR(255),

    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    lastActivityAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expiresAt TIMESTAMP NOT NULL,

    FOREIGN KEY (userId) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_userId (userId),
    INDEX idx_expiresAt (expiresAt)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Auth Audit Log
CREATE TABLE authAuditLog (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    userId INT UNSIGNED,
    eventType ENUM(
        'login_success', 'login_failed', 'logout',
        'password_change', 'password_reset_request', 'password_reset_complete',
        'mfa_enabled', 'mfa_disabled', 'mfa_success', 'mfa_failed',
        'token_issued', 'token_refreshed', 'token_revoked',
        'account_locked', 'account_unlocked',
        'api_key_created', 'api_key_revoked'
    ) NOT NULL,

    ipAddress VARCHAR(45),
    userAgent TEXT,
    details JSON,

    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_userId (userId),
    INDEX idx_eventType (eventType),
    INDEX idx_createdAt (createdAt)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Rate Limiting (DB fallback)
CREATE TABLE rateLimitAttempts (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    identifier VARCHAR(255) NOT NULL,
    action VARCHAR(50) NOT NULL,
    attempts INT DEFAULT 1,
    firstAttemptAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    lastAttemptAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    blockedUntil TIMESTAMP NULL,

    UNIQUE KEY unique_identifier_action (identifier, action),
    INDEX idx_blockedUntil (blockedUntil)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

#### 2.2.4 Compatibility Views

**Important:** Views cannot be created while tables of the same name exist. The migration must follow this sequence:

1. **After data migration completes**: Rename original tables to `*_legacy`
2. **Create views**: Point views at new tables
3. **Verification period**: Legacy code uses views transparently
4. **Cleanup**: Drop legacy tables after verification

```sql
-- Step 1: Rename existing tables (AFTER data migration)
RENAME TABLE uf_user TO uf_user_legacy;
RENAME TABLE uf_group_user TO uf_group_user_legacy;

-- Step 2: Create compatibility views
CREATE VIEW uf_user AS
SELECT
    id,
    username AS user_name,
    displayName AS display_name,
    password,
    email,
    activationToken AS activation_token,
    passwordResetToken AS lost_password_request,
    passwordResetExpiresAt AS lost_password_timestamp,
    active,
    enabled,
    NULL AS primary_group_id,
    locale,
    lastLoginAt AS last_sign_in_stamp,
    createdAt AS sign_up_stamp,
    NULL AS title
FROM users
WHERE canLogin = 1;

CREATE VIEW uf_group_user AS
SELECT
    userId AS user_id,
    groupId AS group_id
FROM userGroups;

-- Step 3: After verification (Phase 8), drop legacy tables
-- DROP TABLE uf_user_legacy;
-- DROP TABLE uf_group_user_legacy;
```

**View Lifecycle:**
| Phase | uf_user Table | uf_user View | Source of Truth |
|-------|---------------|--------------|-----------------|
| Before Migration | EXISTS (table) | N/A | uf_user table |
| After Phase 4 | RENAMED to uf_user_legacy | N/A | users table (new) |
| After Phase 5 | uf_user_legacy (backup) | EXISTS | users table via view |
| After Phase 8 | DROPPED | EXISTS | users table via view |

Similarly for `uf_group_user` → `userGroups`.

### 2.3 Account Types

| Type | canLogin | Description | Use Case |
|------|----------|-------------|----------|
| `employee` | false | Worker synced from external system | WhenIWork/Homebase sync |
| `user` | true | Standard user with login access | Store managers, buyers |
| `admin` | true | Administrative user | System configuration |
| `system` | true | System/service accounts | API integrations |

---

## 3. Authentication Architecture

### 3.1 Authentication Methods

| Method | Use Case | Token Type | Lifetime |
|--------|----------|------------|----------|
| **Session** | Web browser | PHP Session + JWT | 24 hours |
| **OAuth2 Bearer** | New APIs/Apps | JWT Access Token | 15 minutes |
| **Refresh Token** | Token renewal | Opaque token | 7 days |
| **API Key (Legacy)** | Existing mobile apps | Static key | No expiration |
| **MFA TOTP** | Second factor | 6-digit code | 30 seconds |

### 3.2 Authentication Flow

```
┌─────────────────────────────────────────────────────────────────┐
│                    AUTHENTICATION FLOW                          │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│  1. LOGIN REQUEST                                               │
│  ┌──────────────────────────────────────────────────────────┐  │
│  │  POST /auth/login                                        │  │
│  │  { username, password, mfa_code? }                       │  │
│  └──────────────────────────────────────────────────────────┘  │
│                          │                                      │
│                          ▼                                      │
│  2. VALIDATION PIPELINE                                         │
│  ┌──────────────────────────────────────────────────────────┐  │
│  │  Rate Limiter (Redis) → Fail if exceeded                 │  │
│  │  ↓                                                       │  │
│  │  Load User → Fail if not found or disabled               │  │
│  │  ↓                                                       │  │
│  │  Verify Password (Argon2id) → Fail if wrong              │  │
│  │  ↓                                                       │  │
│  │  Check canLogin flag → Fail if false                     │  │
│  │  ↓                                                       │  │
│  │  Check MFA Required → Redirect to MFA if enabled         │  │
│  │  ↓                                                       │  │
│  │  Verify MFA Code (if provided) → Fail if wrong           │  │
│  └──────────────────────────────────────────────────────────┘  │
│                          │                                      │
│                          ▼                                      │
│  3. TOKEN ISSUANCE                                              │
│  ┌──────────────────────────────────────────────────────────┐  │
│  │  Generate Access Token (JWT, RS256, 15min)               │  │
│  │  Generate Refresh Token (opaque, 7 days)                 │  │
│  │  Store Refresh Token in DB                               │  │
│  │  Set Session Cookie (HttpOnly, Secure, SameSite=Strict)  │  │
│  │  Return tokens to client                                 │  │
│  └──────────────────────────────────────────────────────────┘  │
│                                                                 │
│  4. PROTECTED ROUTE ACCESS                                      │
│  ┌──────────────────────────────────────────────────────────┐  │
│  │  Request with Authorization: Bearer <token>              │  │
│  │  ↓                                                       │  │
│  │  Middleware: Verify JWT signature (RS256 public key)     │  │
│  │  ↓                                                       │  │
│  │  Check token expiration                                  │  │
│  │  ↓                                                       │  │
│  │  Load user permissions                                   │  │
│  │  ↓                                                       │  │
│  │  Authorize request                                       │  │
│  └──────────────────────────────────────────────────────────┘  │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘
```

### 3.3 JWT Token Structure

**Access Token (RS256):**
```json
{
  "header": {
    "alg": "RS256",
    "typ": "JWT",
    "kid": "key-2025-01"
  },
  "payload": {
    "iss": "buyerkiosk.com",
    "sub": "user_123",
    "iat": 1704067200,
    "exp": 1704068100,
    "jti": "unique-token-id",
    "type": "access",
    "user": {
      "id": 123,
      "username": "jsmith",
      "accountType": "user"
    },
    "stores": ["ou00", "pa00"],
    "permissions": ["uri_store_settings", "uri_employees"]
  }
}
```

### 3.4 Dual-Auth Middleware

```
┌─────────────────────────────────────────────────────────────────┐
│                 DUAL AUTH MIDDLEWARE                            │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│  Incoming Request                                               │
│       │                                                         │
│       ▼                                                         │
│  ┌─────────────────────────────────────────────────────────┐   │
│  │  Check Authorization Header                              │   │
│  │                                                          │   │
│  │  if (header starts with "Bearer ")                       │   │
│  │      → modernJwtAuth()     // New JWT validation         │   │
│  │                                                          │   │
│  │  else if (header starts with "ApiKey ")                  │   │
│  │      → legacyApiKeyAuth()  // Existing API key check     │   │
│  │                                                          │   │
│  │  else if (header is "X-Api-Key")                         │   │
│  │      → legacyApiKeyAuth()  // Alternative header format  │   │
│  │                                                          │   │
│  │  else if (session exists)                                │   │
│  │      → sessionAuth()       // Web session                │   │
│  │                                                          │   │
│  │  else                                                    │   │
│  │      → unauthenticated()   // 401 response               │   │
│  └─────────────────────────────────────────────────────────┘   │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘
```

### 3.5 Rate Limiting Configuration

| Action | Window | Max Attempts | Lockout | Storage |
|--------|--------|--------------|---------|---------|
| Login (per IP) | 15 minutes | 10 | 30 minutes | Redis |
| Login (per user) | 15 minutes | 5 | 15 minutes | Redis |
| Password Reset | 1 hour | 3 | 1 hour | Redis |
| MFA Verification | 5 minutes | 5 | 15 minutes | Redis |
| API (per key) | 1 minute | 100 | 1 minute | Redis |

---

## 4. Component Architecture

### 4.1 New Components

#### 4.1.1 UnifiedUser Model

**Location:** `userfrosting/src/BuyerKiosk/Auth/Models/UnifiedUser.php`

```php
<?php
namespace BuyerKiosk\Auth\Models;

class UnifiedUser {
    // Core identity (maps to DB columns)
    private int $id;
    private ?string $username;
    private string $email;
    private ?string $password;

    // Profile (maps to: displayName, firstName, lastName, phone, photoUrl)
    private string $firstName;
    private string $lastName;
    private ?string $displayName;
    private ?string $phone;
    private ?string $photoUrl;

    // Access control (maps to: canLogin, accountType)
    private bool $canLogin;
    private string $accountType; // employee, user, admin, system
    private bool $enabled;
    private bool $active;

    // External sync (maps to: source, externalId)
    private string $source; // homegrown, wheniwork, homebase, system
    private ?string $externalId;

    // MFA (maps to: mfaEnabled, mfaSecret)
    private bool $mfaEnabled;
    private ?string $mfaSecret;

    // Methods
    public function verifyPassword(string $password): bool;
    public function setPassword(string $password): void;
    public function getStoreAssignments(): array;
    public function hasStoreAccess(string $typeNum): bool;
    public function getPermissions(): array;
    public function checkAccess(string $hook): bool;
}
```

#### 4.1.2 AuthService

**Location:** `userfrosting/src/BuyerKiosk/Auth/Services/AuthService.php`

```php
<?php
namespace BuyerKiosk\Auth\Services;

class AuthService {
    public function __construct(
        private UserRepository $userRepo,
        private TokenService $tokenService,
        private RateLimiter $rateLimiter,
        private AuditLogger $auditLogger
    ) {}

    public function login(string $username, string $password, ?string $mfaCode = null): AuthResult;
    public function logout(int $userId, ?string $sessionId = null): void;
    public function refreshToken(string $refreshToken): TokenPair;
    public function verifyMfa(int $userId, string $code): bool;
    public function enableMfa(int $userId): MfaSetupResult;
    public function disableMfa(int $userId, string $code): bool;
}
```

#### 4.1.3 TokenService

**Location:** `userfrosting/src/BuyerKiosk/Auth/Services/TokenService.php`

```php
<?php
namespace BuyerKiosk\Auth\Services;

class TokenService {
    private string $privateKey;
    private string $publicKey;
    private string $keyId;

    public function createAccessToken(UnifiedUser $user): string;
    public function createRefreshToken(UnifiedUser $user, array $deviceInfo): string;
    public function validateAccessToken(string $token): ?TokenPayload;
    public function validateRefreshToken(string $token): ?RefreshTokenEntity;
    public function revokeRefreshToken(string $token): void;
    public function revokeAllUserTokens(int $userId): void;
}
```

#### 4.1.4 RateLimiter

**Location:** `userfrosting/src/BuyerKiosk/Auth/Services/RateLimiter.php`

```php
<?php
namespace BuyerKiosk\Auth\Services;

class RateLimiter {
    public function __construct(
        private ?\Redis $redis = null,
        private ?PDO $dbFallback = null
    ) {}

    /**
     * Check rate limit and record attempt atomically.
     * Returns RateLimitResult with isBlocked() and retryAfter properties.
     */
    public function checkLimit(string $action, string $identifier): RateLimitResult;

    /**
     * Clear all attempts for an identifier (e.g., on successful login).
     */
    public function clearAttempts(string $action, string $identifier): void;
}

/**
 * Result object returned by checkLimit().
 */
class RateLimitResult {
    public function __construct(
        private bool $blocked,
        private int $retryAfter = 0,
        private int $attemptsRemaining = 0
    ) {}

    public function isBlocked(): bool { return $this->blocked; }
    public function getRetryAfter(): int { return $this->retryAfter; }
    public function getAttemptsRemaining(): int { return $this->attemptsRemaining; }
}
```

### 4.2 Modified Components

#### 4.2.1 AccountController Updates

**Location:** `userfrosting/src/BuyerKiosk/Core/Controllers/AccountController.php`

**Changes:**
- Use `AuthService` instead of direct user validation
- Add rate limiting before password check
- Add MFA verification step
- Generate JWT tokens on successful login
- Add audit logging for all auth events

#### 4.2.2 WhenIWorkProvider Updates

**Location:** `userfrosting/src/BuyerKiosk/Employee/WhenIWorkProvider.php`

**Changes:**
- Target central `users` table instead of per-store `employees`
- Create `userStoreAssignments` instead of employee records
- Set `canLogin=false` and `source='wheniwork'`
- Handle duplicate detection across stores

#### 4.2.3 UserSession Middleware Updates

**Location:** `userfrosting/middleware/UserSession.php`

**Changes:**
- Check both JWT and session authentication
- Support dual-auth middleware pattern
- Track sessions in `userSessions` table
- Refresh session activity timestamp

---

## 5. API Design

### 5.1 New Authentication Endpoints

```yaml
POST /auth/login:
  description: Authenticate user with credentials
  request:
    body:
      username: string (required)
      password: string (required)
      mfa_code: string (optional)
      remember_me: boolean (optional)
  response:
    200:
      access_token: string (JWT)
      refresh_token: string
      expires_in: integer (seconds)
      token_type: "Bearer"
      user:
        id: integer
        username: string
        accountType: string
        mfaRequired: boolean
    401:
      error: "invalid_credentials" | "mfa_required" | "account_locked"
    429:
      error: "rate_limited"
      retry_after: integer (seconds)

POST /auth/logout:
  description: Invalidate current session and tokens
  headers:
    Authorization: Bearer <token>
  response:
    200:
      message: "Logged out successfully"

POST /auth/refresh:
  description: Refresh access token
  request:
    body:
      refresh_token: string (required)
  response:
    200:
      access_token: string (JWT)
      refresh_token: string (rotated)
      expires_in: integer
    401:
      error: "invalid_refresh_token" | "token_expired" | "token_revoked"

POST /auth/mfa/verify:
  description: Verify MFA code during login
  request:
    body:
      userId: integer (required)
      code: string (required)
      type: "totp" | "backup" (optional, default: totp)
  response:
    200:
      accessToken: string (JWT)
      refreshToken: string
    401:
      error: "invalid_code"

GET /auth/mfa/setup:
  description: Get MFA setup information
  headers:
    Authorization: Bearer <token>
  response:
    200:
      secret: string (base32)
      qr_code: string (data URI)
      backup_codes: array (only shown once)

POST /auth/mfa/enable:
  description: Enable MFA after verification
  headers:
    Authorization: Bearer <token>
  request:
    body:
      code: string (required)
  response:
    200:
      message: "MFA enabled"
      backup_codes: array

DELETE /auth/mfa/disable:
  description: Disable MFA
  headers:
    Authorization: Bearer <token>
  request:
    body:
      code: string (required)
  response:
    200:
      message: "MFA disabled"
```

### 5.2 New User Endpoints

```yaml
GET /api/users:
  description: List users with filters
  query:
    store: string (optional, filter by store assignment)
    canLogin: boolean (optional)
    accountType: string (optional)
    source: string (optional)
    search: string (optional, name/email)
    page: integer
    perPage: integer
  response:
    200:
      data: array of User objects
      meta:
        total: integer
        page: integer
        perPage: integer

GET /api/users/{id}:
  description: Get user details
  response:
    200:
      id: integer
      username: string
      email: string
      firstName: string
      lastName: string
      displayName: string
      canLogin: boolean
      accountType: string
      source: string
      storeAssignments: array
      groups: array
      mfaEnabled: boolean

POST /api/users:
  description: Create new user
  request:
    body:
      username: string (required if canLogin)
      email: string (required)
      firstName: string (required)
      lastName: string (required)
      password: string (required if canLogin)
      canLogin: boolean
      accountType: string
      storeAssignments: array
  response:
    201:
      id: integer
      ...user fields

PUT /api/users/{id}:
  description: Update user
  request:
    body:
      ...updatable fields
  response:
    200:
      ...user fields

DELETE /api/users/{id}:
  description: Deactivate user
  response:
    200:
      message: "User deactivated"

GET /api/users/{id}/stores:
  description: Get user's store assignments
  response:
    200:
      data: array of StoreAssignment objects

POST /api/users/{id}/stores:
  description: Assign user to store
  request:
    body:
      typeNum: string (required)
      clockPin: string (optional)
      role: integer (optional)
  response:
    201:
      ...assignment fields

DELETE /api/users/{id}/stores/{typeNum}:
  description: Remove store assignment
  response:
    200:
      message: "Assignment removed"
```

### 5.3 Deprecated Endpoints (Backward Compatible)

These endpoints continue working but internally redirect to new implementation:

```yaml
# Employee endpoints (deprecated)
GET /:typeNum/api/employees → redirects to GET /api/users?store=:typeNum
GET /:typeNum/api/employees/:id → redirects to GET /api/users/:id
POST /:typeNum/api/employees → redirects to POST /api/users
PUT /:typeNum/api/employees/:id → redirects to PUT /api/users/:id
DELETE /:typeNum/api/employees/:id → redirects to DELETE /api/users/:id

# User-employee link endpoints (deprecated)
GET /:typeNum/api/user-employee/* → deprecated, use store assignments
```

---

## 6. Security Design

### 6.1 Password Security

| Aspect | Implementation |
|--------|----------------|
| Algorithm | Argon2id |
| Memory Cost | 64 MB |
| Time Cost | 3 iterations |
| Parallelism | 4 threads |
| Salt | Auto-generated (16 bytes) |
| Output Length | 32 bytes |

**Password Verification Flow:**
```php
public function verifyPassword(string $password): bool {
    // Detect hash type
    $hashInfo = password_get_info($this->password);

    if ($hashInfo['algo'] === PASSWORD_ARGON2ID) {
        // Modern hash
        return password_verify($password, $this->password);
    }

    if (strlen($this->password) === 65) {
        // Legacy SHA1: first 25 chars are salt
        $salt = substr($this->password, 0, 25);
        if (hash_equals($this->password, $salt . sha1($salt . $password))) {
            // Upgrade to Argon2id on successful verification
            $this->upgradePassword($password);
            return true;
        }
        return false;
    }

    // Legacy bcrypt
    if (password_verify($password, $this->password)) {
        $this->upgradePassword($password);
        return true;
    }

    return false;
}

private function upgradePassword(string $password): void {
    $this->password = password_hash($password, PASSWORD_ARGON2ID);
    $this->save();
}
```

### 6.2 Token Security

| Token Type | Generation | Storage | Comparison |
|------------|------------|---------|------------|
| Access JWT | RS256 signed | Client only | Signature verification |
| Refresh Token | random_bytes(64) | DB (hashed) | hash_equals() |
| Session ID | session_create_id() | Redis/File | Direct |
| API Key (Legacy) | Existing | DB (plaintext→hash) | hash_equals() |
| MFA Secret | random_bytes(20) | DB (AES encrypted) | hash_equals() |
| Backup Codes | random_bytes(16) | DB (hashed) | hash_equals() |

### 6.3 Session Cookie Configuration

```php
// config-userfrosting.php
ini_set('session.cookie_httponly', 1);
ini_set('session.cookie_samesite', 'Strict');
ini_set('session.use_strict_mode', 1);
ini_set('session.gc_maxlifetime', 86400);

// Secure flag - environment-aware to support HTTP dev environments
if (!empty($_ENV['APP_ENV']) && $_ENV['APP_ENV'] === 'production') {
    ini_set('session.cookie_secure', 1);
} elseif (isset($_SERVER['HTTPS']) && $_SERVER['HTTPS'] !== 'off') {
    ini_set('session.cookie_secure', 1);
}
```

> **Note:** The `session.cookie_secure` flag is environment-aware. It enables automatically when:
> - `APP_ENV` is set to `production`, OR
> - The request is served over HTTPS
>
> For local HTTP development, set `APP_ENV=development` or leave it unset.

### 6.4 CSRF Protection

- All state-changing requests require valid CSRF token
- Token stored in session, validated from POST/header
- Login endpoint: Re-enable currently disabled validation

---

## 7. External Provider Integration

### 7.1 WhenIWork Sync Flow

```
WhenIWork API
    │
    │ GET /users (scheduled/manual)
    ▼
┌─────────────────────────────────────────────────────────────────┐
│  WhenIWorkProvider::syncFromProvider($typeNum)                  │
│                                                                 │
│  For each WhenIWork user:                                       │
│  1. Check if externalId exists in users table                   │
│     - If exists: Update name, position, photo                   │
│     - If not: Check for duplicate by email/name                 │
│                                                                 │
│  2. Create/update user record:                                  │
│     - source = 'wheniwork'                                      │
│     - externalId = wiw_user_id                                  │
│     - canLogin = false (employee only)                          │
│     - accountType = 'employee'                                  │
│                                                                 │
│  3. Create/update store assignment:                             │
│     - userId = new/existing user ID                             │
│     - typeNum = $typeNum                                        │
│     - isActive = true                                           │
│                                                                 │
│  4. Handle removed users:                                       │
│     - Set isActive = false on assignment                        │
│     - DO NOT delete user (may be linked elsewhere)              │
│                                                                 │
│  5. Log sync to userSyncLog                                     │
└─────────────────────────────────────────────────────────────────┘
```

### 7.2 Multi-Store Duplicate Detection

```
When syncing employee "John Smith <john@example.com>":

1. Search users table:
   - WHERE email = 'john@example.com'
   - OR (firstName = 'John' AND lastName = 'Smith')
   - OR (source = 'wheniwork' AND externalId = 'wiw123')

2. If found:
   - Use existing user record
   - Add new store assignment
   - Log as 'merge' in sync log

3. If not found:
   - Create new user record
   - Create store assignment
   - Log as 'create' in sync log
```

---

## 8. Migration Design

### 8.1 Migration Phases

```
Phase 1: Schema Creation
├── Create users table
├── Create userStoreAssignments table
├── Create userGroups table
├── Create userPermissions table
├── Create oauthRefreshTokens table
├── Create userSessions table
├── Create authAuditLog table
├── Create userSyncLog table
└── Create rateLimitAttempts table

Phase 2: Data Migration (uf_user → users)
├── For each uf_user record:
│   ├── INSERT into users (canLogin = true)
│   ├── Preserve password hash as-is
│   └── Copy all user fields
├── Migrate uf_group_user → userGroups
└── Migrate uf_authorize_user → userPermissions

Phase 3: Data Migration (linked employees)
├── For each user_employee_links record:
│   ├── Find matching users record
│   ├── UPDATE users with employee fields (name, phone, etc.)
│   ├── CREATE userStoreAssignments
│   └── Set source/externalId from employee
└── Handle conflicts (user data vs employee data)

Phase 4: Data Migration (unlinked employees)
├── For each store's employees table:
│   ├── Skip if employee in user_employee_links
│   ├── Check for duplicate (email, name match)
│   │   ├── If duplicate: Create store assignment only
│   │   └── If new: Create user + assignment
│   └── Set canLogin = false
└── Migrate employee_sync_log → userSyncLog

Phase 5: Rename Legacy Tables & Create Compatibility Views
├── IMPORTANT: Cannot CREATE VIEW while table of same name exists
├── RENAME TABLE uf_user TO uf_user_legacy
├── RENAME TABLE uf_group_user TO uf_group_user_legacy
├── CREATE VIEW uf_user AS SELECT ... FROM users WHERE canLogin=1
├── CREATE VIEW uf_group_user AS SELECT ... FROM userGroups
└── Verify: Legacy code using views transparently

Phase 6: Update Application Code (incremental)
├── Update User model to use users table directly
├── Update Employee providers
├── Update API controllers
└── Update queries throughout application

Phase 7: Remove Legacy Tables (after verification)
├── DROP TABLE user_employee_links
├── DROP TABLE employee_invitations
├── DROP TABLE employees (per-store)
├── DROP TABLE uf_user_legacy        ← Note: legacy suffix
├── DROP TABLE uf_group_user_legacy  ← Note: legacy suffix
└── Keep views intact for any remaining legacy code
```

**Critical Note on View Creation:** The `uf_user` and `uf_group_user` tables must be RENAMED before views of the same name can be created. The legacy tables are kept as `*_legacy` backups during the verification period, then dropped in Phase 7.

### 8.2 Rollback Strategy

| Phase | Rollback Action | Time |
|-------|-----------------|------|
| Schema Creation | DROP new tables | < 1 hour |
| Data Migration | Restore from backup | 2-4 hours |
| Code Migration | Git revert | 1-2 hours |
| View Creation | DROP views | < 30 min |

---

## 9. File Changes Inventory

### 9.1 New Files to Create

| File | Purpose |
|------|---------|
| `userfrosting/src/BuyerKiosk/Auth/Models/UnifiedUser.php` | New unified user model |
| `userfrosting/src/BuyerKiosk/Auth/Models/StoreAssignment.php` | Store assignment entity (maps to userStoreAssignments) |
| `userfrosting/src/BuyerKiosk/Auth/Services/AuthService.php` | Authentication service |
| `userfrosting/src/BuyerKiosk/Auth/Services/TokenService.php` | JWT token service |
| `userfrosting/src/BuyerKiosk/Auth/Services/RateLimiter.php` | Rate limiting service |
| `userfrosting/src/BuyerKiosk/Auth/Services/MfaService.php` | MFA service |
| `userfrosting/src/BuyerKiosk/Auth/Services/AuditLogger.php` | Auth audit logging (writes to authAuditLog) |
| `userfrosting/src/BuyerKiosk/Auth/Middleware/DualAuthMiddleware.php` | Dual auth handler |
| `userfrosting/src/BuyerKiosk/Auth/Controllers/AuthController.php` | New auth endpoints |
| `userfrosting/routes/auth.php` | New auth routes |
| `userfrosting/migrations/007-unified-users/` | Migration scripts |

### 9.2 Files to Modify (HIGH Impact)

| File | Changes |
|------|---------|
| `userfrosting/models/mysql/MySqlUser.php` | Complete rewrite for unified model |
| `userfrosting/models/mysql/MySqlUserLoader.php` | Update queries for new schema |
| `userfrosting/src/BuyerKiosk/Core/Controllers/AccountController.php` | Use AuthService, add rate limiting |
| `userfrosting/src/BuyerKiosk/Employee/WhenIWorkProvider.php` | Target central users table |
| `userfrosting/src/BuyerKiosk/Employee/HomebaseProvider.php` | Target central users table |
| `userfrosting/src/BuyerKiosk/UserEmployee/UserEmployeeLinkManager.php` | Replace with store assignments |
| `userfrosting/middleware/UserSession.php` | Add JWT validation |
| `userfrosting/config-userfrosting.php` | Add session security flags |

### 9.3 Files to Modify (MEDIUM Impact)

| File | Changes |
|------|---------|
| `userfrosting/src/BuyerKiosk/Employee/EmployeeManager.php` | Update to use unified table |
| `userfrosting/src/BuyerKiosk/Employee/HomegrownProvider.php` | Update queries |
| `userfrosting/src/BuyerKiosk/Core/Controllers/EmployeeApiController.php` | Update API responses |
| `userfrosting/src/BuyerKiosk/Core/Controllers/UserController.php` | Update user management |
| `userfrosting/src/BuyerKiosk/Workbook/Controllers/TimePunchController.php` | Update employee queries |
| `userfrosting/src/BuyerKiosk/Core/BuyerStats.php` | Update JOINs |
| `userfrosting/src/BuyerKiosk/Workbook/ScheduleManager.php` | Update employee mapping |
| `userfrosting/auth/Authentication.php` | Add Argon2id support |

---

## 10. Testing Strategy

### 10.1 Unit Tests

| Component | Test Cases |
|-----------|------------|
| UnifiedUser | Password verification, hash upgrade, permissions |
| TokenService | JWT creation/validation, refresh rotation |
| RateLimiter | Threshold detection, lockout, Redis/DB fallback |
| MfaService | TOTP verification, backup codes |

### 10.2 Integration Tests

| Flow | Test Cases |
|------|------------|
| Login | Success, invalid password, locked account, MFA required |
| Token Refresh | Valid token, expired token, revoked token |
| API Key Auth | Legacy format, X-Api-Key header, invalid key |
| User CRUD | Create, read, update, deactivate |
| Store Assignment | Add, remove, multi-store access |

### 10.3 Migration Tests

| Test | Validation |
|------|------------|
| User Migration | All uf_user records in users table |
| Employee Migration | All employees have user records |
| Password Preservation | Legacy hashes still work |
| Permission Migration | All permissions preserved |
| Sync Compatibility | WhenIWork sync creates correct records |

---

## 11. Data Retention & Maintenance

### 11.1 Retention Policies

Per PRD requirements (NFR-104, Section 11.2), the following retention policies apply:

| Data Type | Table | Retention | Cleanup Method |
|-----------|-------|-----------|----------------|
| Auth Audit Events | `authAuditLog` | 90 days | Daily scheduled job |
| Rate Limit Attempts | `rateLimitAttempts` | 24 hours | Daily scheduled job |
| Expired Refresh Tokens | `oauthRefreshTokens` | Until expired + 7 days | Daily scheduled job |
| Expired Sessions | `userSessions` | Until expired + 1 day | Daily scheduled job |

### 11.2 Cleanup Job Design

A scheduled cleanup job (cron or MySQL event) must run daily to enforce retention policies:

```
Daily Cleanup Job (runs at 3 AM)
├── authAuditLog: DELETE WHERE createdAt < NOW() - 90 days
├── rateLimitAttempts: DELETE WHERE lastAttemptAt < NOW() - 24 hours
├── oauthRefreshTokens: DELETE WHERE expiresAt < NOW() - 7 days
└── userSessions: DELETE WHERE expiresAt < NOW() - 1 day

Notes:
- Use LIMIT clause (10,000) to prevent long-running queries
- Run in batches if large backlog exists
- Log deletion counts for monitoring
```

### 11.3 Archival Considerations

For compliance or legal requirements, consider:
- Archiving `authAuditLog` to cold storage before deletion
- Separate partition for audit events older than 30 days
- Export to external logging system (e.g., CloudWatch, Splunk)

---

## Document History

| Version | Date | Author | Changes |
|---------|------|--------|---------|
| 1.0 | December 2025 | System Analysis | Initial formal SDD extracted from unified spec |
| 1.1 | December 2025 | Schema Alignment | Updated all column/table names to camelCase per CLAUDE.md conventions; confirmed JSON column support with MariaDB 12.1.2 |
| 1.2 | December 2025 | Consistency Review | Unified RateLimiter API to return RateLimitResult object; made session cookie secure flag environment-aware |
| 1.3 | December 2025 | Gap Analysis Fix | Fixed compatibility views lifecycle (RENAME before CREATE VIEW); added Section 11 for data retention/maintenance policies (90-day audit retention) |