# Unified Users & Modern Authentication System Specification

**Version:** 1.0
**Date:** December 2025
**Status:** DRAFT
**Author:** System Analysis

---

## Table of Contents

1. [Executive Summary](#1-executive-summary)
2. [Current State Analysis](#2-current-state-analysis)
3. [Goals & Objectives](#3-goals--objectives)
4. [Unified Users Architecture](#4-unified-users-architecture)
5. [Modern Authentication Architecture](#5-modern-authentication-architecture)
6. [Backward Compatibility](#6-backward-compatibility)
7. [Database Schema](#7-database-schema)
8. [Migration Strategy](#8-migration-strategy)
9. [Implementation Phases](#9-implementation-phases)
10. [Security Considerations](#10-security-considerations)
11. [API Changes](#11-api-changes)
12. [Risk Assessment](#12-risk-assessment)
13. [Timeline & Effort](#13-timeline--effort)

---

## 1. Executive Summary

### Overview

This specification defines the merger of BuyerKiosk's separate employee management and user account systems into a single unified users database, combined with a comprehensive modernization of the authentication infrastructure.

### Key Changes

| Area | Current | Proposed |
|------|---------|----------|
| **User Storage** | `uf_user` (central) + `employees` (per-store) | Single `users` table (central) |
| **User-Employee Link** | Bridge table `user_employee_links` | Direct: `user_store_assignments` |
| **Password Hashing** | SHA1/BCrypt mixed | Argon2id only |
| **Session Auth** | PHP `$_SESSION` only | JWT + Redis sessions |
| **API Auth** | Simple API keys | OAuth2 + Legacy API keys (parallel) |
| **MFA** | None | TOTP + WebAuthn (optional) |
| **Rate Limiting** | None | Redis-based throttling |

### Business Value

- **Simplified Data Model**: One source of truth for person data
- **Reduced Complexity**: Eliminate 3 tables and linking logic
- **Modern Security**: Industry-standard authentication
- **Future-Ready**: OAuth2 enables SSO, partner integrations
- **Maintained Compatibility**: Existing mobile apps continue working

---

## 2. Current State Analysis

### 2.1 Database Architecture

**Central Database (`kiosk_users`):**
- `uf_user` - User accounts (authentication, permissions)
- `uf_group` - Roles and groups
- `uf_group_user` - User-group membership
- `uf_authorize_user` - User-level permissions
- `uf_authorize_group` - Group-level permissions
- `uf_store_user` - User-store access
- `uf_user_rememberme` - Remember me tokens
- `uf_apiKey` / `uf_apiKey_user` - API key authentication
- `user_employee_links` - Links users to employees
- `employee_invitations` - Self-registration invitations

**Per-Store Databases (`kiosk_{typeNum}`):**
- `employees` - Worker records with employment data
- `employee_sync_log` - External provider sync tracking

### 2.2 Current Authentication Issues

| Issue | Severity | Details |
|-------|----------|---------|
| CSRF disabled on login | CRITICAL | `AccountController.php:217-221` - commented out |
| SHA1 passwords accepted | CRITICAL | Legacy format still validated |
| Hash upgrade bug | CRITICAL | `MySqlUser.php:404` calls wrong method |
| JWT never validated | CRITICAL | "Pineapple" cookie created but unused |
| No rate limiting | HIGH | Unlimited login attempts |
| API key timing attack | HIGH | Uses `strcmp()` not `hash_equals()` |
| No MFA support | HIGH | Single-factor authentication only |
| No session fingerprinting | MEDIUM | Session fixation risk |

### 2.3 Code Touchpoints

**User System:**
- 15 core files
- ~40 direct SQL queries
- 7 related tables

**Employee System:**
- 40+ files
- ~60+ direct SQL queries
- 3 related tables
- 3 external integrations (WhenIWork, Homebase, DRS)

**Combined Migration Scope:**
- **55+ files** require changes
- **100+ queries** need updating
- **3 external providers** must adapt

---

## 3. Goals & Objectives

### Primary Goals

1. **Unified Identity**: Single `users` table for all person records
2. **Modern Auth**: Industry-standard JWT + OAuth2 authentication
3. **Security Hardening**: Fix critical vulnerabilities
4. **Backward Compatibility**: Existing API keys continue working

### Success Criteria

- [ ] All users and employees in single table
- [ ] Legacy API keys still functional
- [ ] Modern JWT auth available for new integrations
- [ ] MFA available (opt-in)
- [ ] All critical security issues resolved
- [ ] Zero authentication-related downtime during migration

### Non-Goals (Out of Scope)

- Forced migration of existing mobile apps to new auth
- External OAuth provider login (Google, Facebook) - future phase
- Passwordless authentication (WebAuthn-only) - future phase
- Complete removal of legacy API keys - future phase

---

## 4. Unified Users Architecture

### 4.1 Design Principles

1. **Single Source of Truth**: One record per person, regardless of role
2. **Store Assignments**: Multi-store support via assignment table
3. **Access Control**: `can_login` flag distinguishes employees from users
4. **Provider Agnostic**: Support homegrown, WhenIWork, Homebase sources

### 4.2 Entity Relationships

```
┌─────────────────────────────────────────────────────────────────┐
│                     UNIFIED DATA MODEL                          │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│  users (central)                                                │
│  ├── id (PK)                                                    │
│  ├── identity fields (username, email, password)                │
│  ├── profile fields (name, phone, photo)                        │
│  ├── employment fields (position, hire_date, etc.)              │
│  ├── external sync (source, external_id)                        │
│  ├── access control (can_login, account_type)                   │
│  └── auth tokens (activation, password_reset)                   │
│       │                                                         │
│       │ 1:N                                                     │
│       ▼                                                         │
│  user_store_assignments (central)                               │
│  ├── user_id (FK → users.id)                                    │
│  ├── type_num (store identifier)                                │
│  ├── store-specific fields (clock_pin, drs_id, role)            │
│  └── status (is_active, assigned_at)                            │
│       │                                                         │
│       │ N:M                                                     │
│       ▼                                                         │
│  user_groups (central)                                          │
│  ├── user_id (FK → users.id)                                    │
│  └── group_id (FK → groups.id)                                  │
│                                                                 │
│  user_permissions (central)                                     │
│  ├── user_id (FK → users.id)                                    │
│  ├── hook (permission name)                                     │
│  └── conditions (JSON)                                          │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘
```

### 4.3 Account Types

| Type | can_login | Description |
|------|-----------|-------------|
| `employee` | false | Worker synced from external system, no system access |
| `user` | true | Standard user with login access |
| `admin` | true | Administrative user with elevated permissions |
| `system` | true | System/service accounts |

### 4.4 External Provider Handling

**WhenIWork/Homebase Sync Behavior:**

```
External Sync → Creates/Updates users record
             → Sets source = 'wheniwork' | 'homebase'
             → Sets external_id = provider user ID
             → Sets can_login = false (employee-only)
             → Creates user_store_assignment for store

Promotion to User → Sets can_login = true
                 → Sets username + password
                 → Assigns to groups
                 → account_type = 'user'
```

---

## 5. Modern Authentication Architecture

### 5.1 Authentication Methods

The system will support multiple authentication methods in parallel:

| 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 |

### 5.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 can_login 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                                       │  │
│  └──────────────────────────────────────────────────────────┘  │
│                                                                 │
│  5. TOKEN REFRESH                                               │
│  ┌──────────────────────────────────────────────────────────┐  │
│  │  POST /auth/refresh                                      │  │
│  │  { refresh_token }                                       │  │
│  │  ↓                                                       │  │
│  │  Validate refresh token exists and not expired           │  │
│  │  ↓                                                       │  │
│  │  Issue new access token                                  │  │
│  │  Rotate refresh token (one-time use)                     │  │
│  └──────────────────────────────────────────────────────────┘  │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘
```

### 5.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",
      "account_type": "user"
    },
    "stores": ["ou00", "pa00"],
    "permissions": ["uri_store_settings", "uri_employees"]
  }
}
```

### 5.4 MFA Implementation

**Supported Methods:**
1. **TOTP (Primary)** - Google Authenticator, Authy compatible
2. **Backup Codes** - One-time use recovery codes
3. **Email Code (Future)** - 6-digit code via email

**MFA Enrollment Flow:**
```
1. User enables MFA in settings
2. System generates TOTP secret
3. User scans QR code with authenticator app
4. User enters verification code
5. System stores encrypted secret
6. System generates backup codes
7. MFA required on subsequent logins
```

**Database Fields:**
```sql
-- In users table
mfa_enabled BOOLEAN DEFAULT false,
mfa_secret VARCHAR(255),        -- Encrypted TOTP secret
mfa_backup_codes JSON,          -- Encrypted array of codes
mfa_verified_at TIMESTAMP
```

---

## 6. Backward Compatibility

### 6.1 Legacy API Key Support

**CRITICAL**: Existing mobile apps use the `uf_apiKey_user` system and MUST continue working.

**Parallel Authentication Strategy:**

```
┌─────────────────────────────────────────────────────────────────┐
│                 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               │   │
│  └─────────────────────────────────────────────────────────┘   │
│                                                                 │
│  LEGACY API KEY AUTH (Preserved)                               │
│  ┌─────────────────────────────────────────────────────────┐   │
│  │  1. Extract API key from header                          │   │
│  │  2. Query uf_apiKey_user JOIN uf_apiKey                  │   │
│  │  3. Validate with hash_equals() (FIX timing attack)      │   │
│  │  4. Check key is active                                  │   │
│  │  5. Load associated user                                 │   │
│  │  6. Return authenticated user context                    │   │
│  └─────────────────────────────────────────────────────────┘   │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘
```

### 6.2 Deprecation Timeline

| Phase | Timeline | Action |
|-------|----------|--------|
| Phase 1 | Launch | Both systems work in parallel |
| Phase 2 | +3 months | New apps MUST use OAuth2 |
| Phase 3 | +6 months | Begin migrating existing apps |
| Phase 4 | +12 months | Announce API key deprecation |
| Phase 5 | +18 months | API keys disabled for new registrations |
| Phase 6 | +24 months | Full deprecation (evaluate) |

### 6.3 Legacy API Key Security Fix

**Current vulnerability** in `MySqlUser.php:390`:
```php
// VULNERABLE - timing attack
if(strcmp($row['key'], $apiKey) == 0) { ... }
```

**Fixed implementation:**
```php
// SECURE - constant-time comparison
if(hash_equals($row['key'], $apiKey)) { ... }
```

This fix is **backward compatible** and should be deployed immediately.

---

## 7. Database Schema

### 7.1 New Tables (Central Database)

```sql
-- ============================================
-- UNIFIED USERS TABLE
-- ============================================
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
    display_name VARCHAR(150),
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    phone VARCHAR(20),
    photo_url VARCHAR(500),
    avatar_override TINYINT(1) DEFAULT 0,

    -- Employment Info
    position VARCHAR(100),
    hourly_rate DECIMAL(10,2),
    hire_date DATE,
    termination_date DATE,
    leave_start_date DATE,
    leave_end_date DATE,

    -- Emergency Contact
    emergency_contact_name VARCHAR(100),
    emergency_contact_phone VARCHAR(20),

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

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

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

    -- Auth Tokens
    activation_token VARCHAR(255),
    activation_token_expires_at TIMESTAMP NULL,
    password_reset_token VARCHAR(255),
    password_reset_expires_at TIMESTAMP NULL,

    -- MFA
    mfa_enabled TINYINT(1) DEFAULT 0,
    mfa_secret VARCHAR(255),
    mfa_backup_codes JSON,
    mfa_verified_at TIMESTAMP NULL,

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

    -- Metadata
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_login_at TIMESTAMP NULL,
    last_login_ip VARCHAR(45),
    failed_login_attempts INT DEFAULT 0,
    locked_until TIMESTAMP NULL,

    -- Indexes
    INDEX idx_username (username),
    INDEX idx_email (email),
    INDEX idx_external (source, external_id),
    INDEX idx_can_login (can_login),
    INDEX idx_account_type (account_type),
    INDEX idx_active (enabled, active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- USER STORE ASSIGNMENTS
-- ============================================
CREATE TABLE user_store_assignments (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    user_id INT UNSIGNED NOT NULL,
    type_num VARCHAR(10) NOT NULL,

    -- Store-Specific Data
    clock_pin VARCHAR(10),
    drs_employee_id VARCHAR(50),
    role TINYINT DEFAULT 0,

    -- Status
    is_active TINYINT(1) DEFAULT 1,
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deactivated_at TIMESTAMP NULL,

    -- Constraints
    UNIQUE KEY unique_user_store (user_id, type_num),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_type_num (type_num),
    INDEX idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- USER GROUPS (Renamed from uf_group_user)
-- ============================================
CREATE TABLE user_groups (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    user_id INT UNSIGNED NOT NULL,
    group_id INT UNSIGNED NOT NULL,

    UNIQUE KEY unique_user_group (user_id, group_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (group_id) REFERENCES uf_group(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- USER PERMISSIONS (Renamed from uf_authorize_user)
-- ============================================
CREATE TABLE user_permissions (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    user_id INT UNSIGNED NOT NULL,
    hook VARCHAR(200) NOT NULL,
    conditions TEXT,

    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_hook (user_id, hook)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- OAUTH2 REFRESH TOKENS
-- ============================================
CREATE TABLE oauth_refresh_tokens (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    user_id INT UNSIGNED NOT NULL,
    token VARCHAR(255) NOT NULL UNIQUE,
    client_id VARCHAR(100),
    device_name VARCHAR(255),
    device_fingerprint VARCHAR(255),
    ip_address VARCHAR(45),
    user_agent TEXT,

    expires_at TIMESTAMP NOT NULL,
    revoked_at TIMESTAMP NULL,
    last_used_at TIMESTAMP NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_token (token),
    INDEX idx_user_expires (user_id, expires_at),
    INDEX idx_revoked (revoked_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- USER SESSIONS (For session management UI)
-- ============================================
CREATE TABLE user_sessions (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    user_id INT UNSIGNED NOT NULL,
    session_id VARCHAR(255) NOT NULL UNIQUE,

    ip_address VARCHAR(45),
    user_agent TEXT,
    device_type VARCHAR(50),
    location VARCHAR(255),

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_activity_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NOT NULL,

    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- AUTH AUDIT LOG
-- ============================================
CREATE TABLE auth_audit_log (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    user_id INT UNSIGNED,
    event_type 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,

    ip_address VARCHAR(45),
    user_agent TEXT,
    details JSON,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_user_id (user_id),
    INDEX idx_event_type (event_type),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- USER SYNC LOG (Moved from per-store)
-- ============================================
CREATE TABLE user_sync_log (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    user_id INT UNSIGNED,
    type_num VARCHAR(10) NOT NULL,
    provider ENUM('wheniwork', 'homebase') NOT NULL,
    action ENUM('create', 'update', 'deactivate', 'reactivate', 'error', 'sync_complete') NOT NULL,
    external_id VARCHAR(50),
    details JSON,

    synced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_type_num (type_num),
    INDEX idx_provider (provider),
    INDEX idx_synced_at (synced_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- RATE LIMITING (Redis-backed, but DB fallback)
-- ============================================
CREATE TABLE rate_limit_attempts (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    identifier VARCHAR(255) NOT NULL,  -- IP, username, or combo
    action VARCHAR(50) NOT NULL,        -- 'login', 'password_reset', etc.
    attempts INT DEFAULT 1,
    first_attempt_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_attempt_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    blocked_until TIMESTAMP NULL,

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

### 7.2 Compatibility Views

```sql
-- Backward compatibility for code still referencing old tables

CREATE VIEW uf_user AS
SELECT
    id,
    username AS user_name,
    display_name,
    password,
    email,
    activation_token,
    password_reset_token AS lost_password_request,
    password_reset_expires_at AS lost_password_timestamp,
    active,
    enabled,
    NULL AS primary_group_id,  -- Will need migration
    locale,
    last_login_at AS last_sign_in_stamp,
    created_at AS sign_up_stamp,
    NULL AS title
FROM users
WHERE can_login = 1;

CREATE VIEW uf_group_user AS
SELECT
    user_id,
    group_id
FROM user_groups;
```

---

## 8. Migration Strategy

### 8.1 Data Migration Overview

```
┌─────────────────────────────────────────────────────────────────┐
│                    DATA MIGRATION FLOW                          │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│  PHASE 1: Create new tables alongside old ones                  │
│  ───────────────────────────────────────────                   │
│                                                                 │
│  PHASE 2: Migrate uf_user → users                              │
│  ┌─────────────────────────────────────────────────────────┐   │
│  │  For each uf_user record:                                │   │
│  │  1. Create users record with can_login=true              │   │
│  │  2. Copy all user fields                                 │   │
│  │  3. Migrate group memberships                            │   │
│  │  4. Migrate permissions                                  │   │
│  └─────────────────────────────────────────────────────────┘   │
│                                                                 │
│  PHASE 3: Migrate linked employees (have user accounts)         │
│  ┌─────────────────────────────────────────────────────────┐   │
│  │  For each user_employee_link:                            │   │
│  │  1. Find matching users record (already migrated)        │   │
│  │  2. Copy employee fields (name, phone, position, etc.)   │   │
│  │  3. Create user_store_assignment                         │   │
│  │  4. Set source and external_id from employee             │   │
│  └─────────────────────────────────────────────────────────┘   │
│                                                                 │
│  PHASE 4: Migrate unlinked employees (employee-only)           │
│  ┌─────────────────────────────────────────────────────────┐   │
│  │  For each store's employees table:                       │   │
│  │  For each employee NOT in user_employee_links:           │   │
│  │  1. Create users record with can_login=false             │   │
│  │  2. Copy employee fields                                 │   │
│  │  3. Create user_store_assignment                         │   │
│  │  4. Handle duplicates by name+email matching             │   │
│  └─────────────────────────────────────────────────────────┘   │
│                                                                 │
│  PHASE 5: Migrate sync logs                                     │
│  ┌─────────────────────────────────────────────────────────┐   │
│  │  For each store's employee_sync_log:                     │   │
│  │  1. Map employeeId → users.id via external_id            │   │
│  │  2. Insert into central user_sync_log                    │   │
│  └─────────────────────────────────────────────────────────┘   │
│                                                                 │
│  PHASE 6: Create compatibility views                            │
│                                                                 │
│  PHASE 7: Update application code (incremental)                │
│                                                                 │
│  PHASE 8: Remove old tables (after verification period)         │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘
```

### 8.2 Password Migration

**Strategy**: Migrate hashes as-is, upgrade on next login

```sql
-- During migration
INSERT INTO users (password, ...)
SELECT password, ... FROM uf_user;

-- Password hash types are preserved
-- On next login, upgrade to Argon2id if legacy format detected
```

### 8.3 Duplicate Detection

**Problem**: Same person may exist in multiple stores with different employee IDs

**Solution**: Match by email (primary) or name (secondary)

```sql
-- Find potential duplicates across stores
SELECT
    COALESCE(e.email, CONCAT(e.employeeFirstName, ' ', e.employeeLastName)) as identifier,
    GROUP_CONCAT(DISTINCT CONCAT('{typeNum}:', e.employeeID)) as store_employees
FROM employees e
GROUP BY identifier
HAVING COUNT(DISTINCT '{typeNum}') > 1;
```

**Resolution**:
1. If same email across stores → merge into single user with multiple assignments
2. If same name but different email → keep separate (manual review flagged)
3. If same external_id and source → definitely same person

---

## 9. Implementation Phases

### Phase 1: Security Hardening (Week 1-2)

**Goal**: Fix critical vulnerabilities without schema changes

| Task | File | Priority |
|------|------|----------|
| Re-enable CSRF on login | AccountController.php:217-221 | CRITICAL |
| Fix API key timing attack | MySqlUser.php:390 | CRITICAL |
| Fix hash upgrade bug | MySqlUser.php:404 | CRITICAL |
| Add rate limiting (basic) | New middleware | HIGH |
| Set SameSite on session cookie | config-userfrosting.php | HIGH |

### Phase 2: Schema Creation (Week 3-4)

**Goal**: Create new tables alongside existing

- Create all new tables (Section 7.1)
- Create compatibility views
- Set up migration infrastructure
- Build migration validation tools

### Phase 3: Auth Infrastructure (Week 5-8)

**Goal**: Build modern auth alongside legacy

- Implement JWT token service (RS256)
- Create refresh token system
- Build dual-auth middleware
- Implement rate limiting (Redis)
- Add auth audit logging

### Phase 4: Data Migration (Week 9-12)

**Goal**: Migrate all user/employee data

- Migrate uf_user → users
- Migrate employee data per store
- Handle duplicates
- Validate data integrity
- Run parallel (both systems active)

### Phase 5: Code Migration (Week 13-20)

**Goal**: Update application code incrementally

- Update User model class
- Update Employee providers (WhenIWork, Homebase)
- Update API controllers
- Update Workbook/Stats queries
- Update mobile API endpoints

### Phase 6: MFA Implementation (Week 21-24)

**Goal**: Add optional MFA

- TOTP enrollment flow
- MFA verification during login
- Backup codes generation
- MFA management UI

### Phase 7: Testing & Stabilization (Week 25-28)

**Goal**: Ensure stability before cutover

- Comprehensive testing
- Load testing
- Security audit
- Bug fixes
- Documentation

### Phase 8: Cutover & Cleanup (Week 29-32)

**Goal**: Complete migration

- Remove compatibility views
- Archive old tables
- Remove legacy code paths
- Final documentation

---

## 10. Security Considerations

### 10.1 Password Security

| Aspect | Current | Target |
|--------|---------|--------|
| **Algorithm** | SHA1/BCrypt mixed | Argon2id only |
| **Cost Factor** | 10-12 | Argon2id defaults (time=1, memory=64MB) |
| **Salt** | Per-hash (automatic) | Per-hash (automatic) |
| **Minimum Length** | Unclear | 12 characters |
| **Complexity** | None required | Require mixed case + number |

### 10.2 Token Security

| Token Type | Storage | Encryption | Lifetime |
|------------|---------|------------|----------|
| Access JWT | Client-side | Signed (RS256) | 15 minutes |
| Refresh Token | Database + Client | Hashed in DB | 7 days |
| Session ID | Redis/Cookie | Signed | 24 hours |
| API Key (Legacy) | Database | Plaintext (fix: hash) | Unlimited |
| MFA Secret | Database | AES-256 encrypted | Permanent |
| Backup Codes | Database | AES-256 encrypted | Until used |

### 10.3 Rate Limiting

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

### 10.4 Session Security

```php
// Required session configuration
ini_set('session.cookie_httponly', 1);
ini_set('session.cookie_secure', 1);
ini_set('session.cookie_samesite', 'Strict');
ini_set('session.use_strict_mode', 1);
ini_set('session.gc_maxlifetime', 86400);  // 24 hours
```

---

## 11. API Changes

### 11.1 New Endpoints

```
Authentication:
POST   /auth/login              - Login with username/password
POST   /auth/logout             - Logout (revoke tokens)
POST   /auth/refresh            - Refresh access token
POST   /auth/mfa/verify         - Verify MFA code
GET    /auth/mfa/setup          - Get MFA setup QR code
POST   /auth/mfa/enable         - Enable MFA
DELETE /auth/mfa/disable        - Disable MFA

Users (replaces separate user/employee endpoints):
GET    /api/users               - List users (with filters)
GET    /api/users/:id           - Get user details
POST   /api/users               - Create user
PUT    /api/users/:id           - Update user
DELETE /api/users/:id           - Deactivate user

Store Assignments:
GET    /api/users/:id/stores              - Get user's store assignments
POST   /api/users/:id/stores              - Assign user to store
DELETE /api/users/:id/stores/:typeNum     - Remove store assignment

Sessions:
GET    /api/users/:id/sessions  - List active sessions
DELETE /api/users/:id/sessions/:sessionId - Revoke session
```

### 11.2 Deprecated Endpoints (Backward Compatible)

These endpoints continue working but are deprecated:

```
GET    /:typeNum/api/employees           → Redirect to /api/users?store=:typeNum
GET    /:typeNum/api/employees/:id       → Redirect to /api/users/:id
POST   /:typeNum/api/employees           → Redirect to POST /api/users
PUT    /:typeNum/api/employees/:id       → Redirect to PUT /api/users/:id
DELETE /:typeNum/api/employees/:id       → Redirect to DELETE /api/users/:id

GET    /:typeNum/api/user-employee/*     → Deprecated (use store assignments)
```

### 11.3 Authentication Header Changes

**Modern (Preferred):**
```http
Authorization: Bearer eyJhbGciOiJSUzI1NiIsInR5cCI6IkpXVCJ9...
```

**Legacy (Still Supported):**
```http
Authorization: ApiKey abc123def456...
X-Api-Key: abc123def456...
```

---

## 12. Risk Assessment

### 12.1 Technical Risks

| Risk | Probability | Impact | Mitigation |
|------|-------------|--------|------------|
| Data loss during migration | Low | Critical | Backup before migration, parallel run period |
| Session invalidation | Medium | High | Gradual rollout, force re-login during maintenance |
| API compatibility break | Medium | Critical | Dual-auth middleware, extensive testing |
| Performance degradation | Low | Medium | Index optimization, Redis caching |
| External sync failures | Medium | High | Maintain sync compatibility, extensive testing |

### 12.2 Business Risks

| Risk | Probability | Impact | Mitigation |
|------|-------------|--------|------------|
| Mobile app disruption | Low | Critical | Maintain API key auth, test thoroughly |
| User confusion (MFA) | Medium | Low | Optional MFA, clear documentation |
| Store data isolation concerns | Low | Medium | Clear audit trails, permission checks |

### 12.3 Rollback Strategy

**Phase-by-Phase Rollback:**

1. **Schema Creation**: Drop new tables (no data impact)
2. **Auth Infrastructure**: Disable new middleware (fall back to session-only)
3. **Data Migration**: Restore from backup, revert to old tables
4. **Code Migration**: Revert code changes via git

**Full Rollback Time**: 2-4 hours (depends on phase)

---

## 13. Timeline & Effort

### 13.1 Estimated Timeline

| Phase | Duration | Dependencies |
|-------|----------|--------------|
| Phase 1: Security Hardening | 2 weeks | None |
| Phase 2: Schema Creation | 2 weeks | Phase 1 |
| Phase 3: Auth Infrastructure | 4 weeks | Phase 2 |
| Phase 4: Data Migration | 4 weeks | Phase 3 |
| Phase 5: Code Migration | 8 weeks | Phase 4 |
| Phase 6: MFA Implementation | 4 weeks | Phase 5 |
| Phase 7: Testing | 4 weeks | Phase 6 |
| Phase 8: Cutover | 4 weeks | Phase 7 |
| **Total** | **32 weeks** | ~8 months |

### 13.2 Resource Requirements

| Role | Allocation | Duration |
|------|------------|----------|
| Backend Developer | 100% | 32 weeks |
| Frontend Developer | 50% | 20 weeks |
| QA Engineer | 50% | 24 weeks |
| DevOps | 25% | 16 weeks |
| Security Review | 10% | 8 weeks |

### 13.3 Milestones

| Milestone | Target Date | Deliverable |
|-----------|-------------|-------------|
| M1: Security Fixes | Week 2 | Critical vulnerabilities patched |
| M2: New Schema Live | Week 4 | Tables created, no data yet |
| M3: Modern Auth Ready | Week 8 | JWT auth working alongside legacy |
| M4: Data Migrated | Week 12 | All data in new tables |
| M5: Code Updated | Week 20 | Application using new tables |
| M6: MFA Available | Week 24 | Optional MFA for users |
| M7: Production Ready | Week 28 | Full testing complete |
| M8: Migration Complete | Week 32 | Old tables archived |

---

## Appendices

### A. File Change Inventory

See separate document: `unified-users-file-changes.md`

### B. Migration Scripts

See separate document: `unified-users-migration-scripts.md`

### C. API Documentation

See separate document: `unified-users-api-docs.md`

### D. Testing Plan

See separate document: `unified-users-testing-plan.md`

---

## Document History

| Version | Date | Author | Changes |
|---------|------|--------|---------|
| 1.0 | December 2025 | System Analysis | Initial draft |