yellow-bank-soal/MULTISITE_WORDPRESS_AUTH.md

# Multi-Site WordPress Authentication Migration

**Document Version:** 1.0
**Date:** March 21, 2026
**Status:** Pending Implementation
**Priority:** Medium

---

## Overview

This document describes how to migrate from single-site WordPress authentication to multi-site authentication. An AI agent should be able to read this document and implement all necessary changes.

---

## Current State (Single-Site)

### Current `.env` Configuration

```env
WORDPRESS_API_URL=https://single-site.com/wp-json
WORDPRESS_AUTH_TOKEN=eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9...
```

### Current `app/core/config.py`

```python
class Settings(BaseSettings):
    # WordPress - single site only
    WORDPRESS_API_URL: str = ""
    WORDPRESS_AUTH_TOKEN: str = ""
```

### Problem

- Only supports ONE WordPress site
- Cannot scale to multiple clients
- `ALLOWED_ORIGINS` already supports multiple sites, but auth does not

---

## Target State (Multi-Site)

### Architecture

```
┌─────────────────────────────────────────────────────────────┐
│                    FastAPI Application                       │
│                                                              │
│  ┌─────────────────────────────────────────────────────┐    │
│  │              websites table (PostgreSQL)             │    │
│  │                                                       │    │
│  │  website_id | site_name | site_url | wp_api_url |    │    │
│  │              | auth_token (encrypted) | api_key      │    │
│  └─────────────────────────────────────────────────────┘    │
│                           │                                  │
│                           ▼                                  │
│  ┌─────────────────────────────────────────────────────┐    │
│  │           WordPressAuthManager (service)             │    │
│  │                                                       │    │
│  │  - get_site_credentials(website_id)                  │    │
│  │  - verify_token(website_id, token)                   │    │
│  │  - sync_users(website_id)                            │    │
│  └─────────────────────────────────────────────────────┘    │
│                           │                                  │
│           ┌───────────────┼───────────────┐                 │
│           ▼               ▼               ▼                 │
│    ┌──────────┐    ┌──────────┐    ┌──────────┐            │
│    │ Site 1   │    │ Site 2   │    │ Site N   │            │
│    │ WordPress│    │ WordPress│    │ WordPress│            │
│    └──────────┘    └──────────┘    └──────────┘            │
└─────────────────────────────────────────────────────────────┘
```

### Request Flow

```
WordPress Site Request
        │
        ▼
┌───────────────────────────────────┐
│ Headers:                          │
│   X-Website-ID: 1                 │
│   Authorization: Bearer <token>   │
└───────────────────────────────────┘
        │
        ▼
┌───────────────────────────────────┐
│ API looks up site credentials     │
│ from websites table by website_id │
└───────────────────────────────────┘
        │
        ▼
┌───────────────────────────────────┐
│ Validate token with that site's   │
│ WordPress API                     │
└───────────────────────────────────┘
```

---

## Implementation Steps

### Step 1: Database Migration

Create a new Alembic migration to update the `websites` table:

**File:** `alembic/versions/xxx_add_wordpress_multisite_fields.py`

```python
"""add wordpress multisite fields

Revision ID: xxx
Revises: previous_revision
Create Date: 2026-03-21

"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

# revision identifiers
revision = 'xxx'
down_revision = 'previous_revision'
branch_labels = None
depends_on = None


def upgrade():
    # Add WordPress-related columns to websites table
    op.add_column('websites', sa.Column('wordpress_api_url', sa.String(500), nullable=True))
    op.add_column('websites', sa.Column('wordpress_auth_token', sa.Text, nullable=True))
    op.add_column('websites', sa.Column('wordpress_api_key', sa.String(64), nullable=True))
    op.add_column('websites', sa.Column('wordpress_enabled', sa.Boolean, default=False))
    op.add_column('websites', sa.Column('wordpress_last_sync', sa.DateTime, nullable=True))

    # Create index for faster lookups
    op.create_index('ix_websites_wordpress_api_key', 'websites', ['wordpress_api_key'], unique=True)


def downgrade():
    op.drop_index('ix_websites_wordpress_api_key', 'websites')
    op.drop_column('websites', 'wordpress_last_sync')
    op.drop_column('websites', 'wordpress_enabled')
    op.drop_column('websites', 'wordpress_api_key')
    op.drop_column('websites', 'wordpress_auth_token')
    op.drop_column('websites', 'wordpress_api_url')
```

### Step 2: Update SQLAlchemy Model

**File:** `app/models/website.py`

```python
from sqlalchemy import Column, Integer, String, Boolean, DateTime, Text
from sqlalchemy.sql import func
from app.database import Base


class Website(Base):
    __tablename__ = "websites"

    website_id = Column(Integer, primary_key=True, index=True)
    site_name = Column(String(255), nullable=False)
    site_url = Column(String(500), nullable=False)

    # WordPress integration (multi-site)
    wordpress_api_url = Column(String(500), nullable=True)
    wordpress_auth_token = Column(Text, nullable=True)  # Encrypted
    wordpress_api_key = Column(String(64), unique=True, nullable=True)
    wordpress_enabled = Column(Boolean, default=False)
    wordpress_last_sync = Column(DateTime, nullable=True)

    created_at = Column(DateTime, server_default=func.now())
    updated_at = Column(DateTime, server_default=func.now(), onupdate=func.now())
```

### Step 3: Update Pydantic Schemas

**File:** `app/schemas/website.py`

```python
from pydantic import BaseModel, Field
from typing import Optional
from datetime import datetime


class WebsiteBase(BaseModel):
    site_name: str
    site_url: str


class WebsiteCreate(WebsiteBase):
    wordpress_api_url: Optional[str] = None
    wordpress_auth_token: Optional[str] = None
    wordpress_enabled: bool = False


class WebsiteUpdate(BaseModel):
    site_name: Optional[str] = None
    site_url: Optional[str] = None
    wordpress_api_url: Optional[str] = None
    wordpress_auth_token: Optional[str] = None
    wordpress_enabled: Optional[bool] = None


class WebsiteResponse(WebsiteBase):
    website_id: int
    wordpress_api_url: Optional[str] = None
    wordpress_enabled: bool = False
    wordpress_last_sync: Optional[datetime] = None
    created_at: datetime
    updated_at: datetime

    class Config:
        from_attributes = True


class WebsiteWithTokenResponse(WebsiteResponse):
    """Admin only - includes token"""
    wordpress_auth_token: Optional[str] = None
    wordpress_api_key: Optional[str] = None
```

### Step 4: Create WordPress Auth Service

**File:** `app/services/wordpress_multisite.py`

```python
import httpx
from typing import Optional, Dict, Any
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import select
from app.models.website import Website
from app.core.security import encrypt_token, decrypt_token
from app.core.config import settings


class WordPressMultiSiteAuth:
    """Handles authentication for multiple WordPress sites."""

    def __init__(self, session: AsyncSession):
        self.session = session
        self.timeout = httpx.Timeout(30.0)

    async def get_website(self, website_id: int) -> Optional[Website]:
        """Get website by ID."""
        result = await self.session.execute(
            select(Website).where(Website.website_id == website_id)
        )
        return result.scalar_one_or_none()

    async def get_site_credentials(self, website_id: int) -> Dict[str, str]:
        """Get decrypted credentials for a specific site."""
        website = await self.get_website(website_id)

        if not website or not website.wordpress_enabled:
            raise ValueError(f"Website {website_id} not found or WordPress not enabled")

        return {
            "api_url": website.wordpress_api_url,
            "auth_token": decrypt_token(website.wordpress_auth_token)
            if website.wordpress_auth_token else None,
            "api_key": website.wordpress_api_key,
        }

    async def verify_wp_token(self, website_id: int, user_token: str) -> Dict[str, Any]:
        """Verify a user's WordPress JWT token against the correct site."""
        credentials = await self.get_site_credentials(website_id)
        api_url = credentials["api_url"]

        async with httpx.AsyncClient(timeout=self.timeout) as client:
            response = await client.get(
                f"{api_url}/wp/v2/users/me",
                headers={"Authorization": f"Bearer {user_token}"}
            )

            if response.status_code == 200:
                return response.json()
            else:
                raise ValueError(f"Token verification failed: {response.status_code}")

    async def sync_users(self, website_id: int) -> Dict[str, int]:
        """Sync users from a specific WordPress site."""
        credentials = await self.get_site_credentials(website_id)
        api_url = credentials["api_url"]
        auth_token = credentials["auth_token"]

        async with httpx.AsyncClient(timeout=self.timeout) as client:
            response = await client.get(
                f"{api_url}/wp/v2/users",
                headers={"Authorization": f"Bearer {auth_token}"},
                params={"per_page": 100}
            )

            if response.status_code != 200:
                raise ValueError(f"Failed to fetch users: {response.status_code}")

            users = response.json()
            # TODO: Implement user sync logic
            # - Create/update users in local database
            # - Map wp_user_id to website_id

            return {
                "synced": len(users),
                "website_id": website_id
            }

    async def generate_api_key(self, website_id: int) -> str:
        """Generate a new API key for a website."""
        import secrets
        api_key = secrets.token_urlsafe(32)

        website = await self.get_website(website_id)
        if not website:
            raise ValueError(f"Website {website_id} not found")

        website.wordpress_api_key = api_key
        await self.session.commit()

        return api_key
```

### Step 5: Update Config for Encryption Key

**File:** `app/core/config.py`

```python
from pydantic_settings import BaseSettings


class Settings(BaseSettings):
    # Database
    DATABASE_URL: str

    # Security
    SECRET_KEY: str
    TOKEN_ENCRYPTION_KEY: str = ""  # NEW: For encrypting WordPress tokens in DB

    # Environment
    ENVIRONMENT: str = "development"
    DEBUG: bool = False

    # CORS
    ALLOWED_ORIGINS: str = "*"

    # WordPress - DEPRECATED: Use database-driven config
    # Keep for backward compatibility during migration
    WORDPRESS_API_URL: str = ""
    WORDPRESS_AUTH_TOKEN: str = ""

    class Config:
        env_file = ".env"


settings = Settings()
```

### Step 6: Create Security Helpers

**File:** `app/core/security.py`

```python
from cryptography.fernet import Fernet
from app.core.config import settings
import base64
import hashlib


def get_encryption_key() -> bytes:
    """Derive encryption key from SECRET_KEY."""
    # Use SHA256 to get a 32-byte key from SECRET_KEY
    key = hashlib.sha256(settings.SECRET_KEY.encode()).digest()
    return base64.urlsafe_b64encode(key)


def encrypt_token(token: str) -> str:
    """Encrypt a token for storage."""
    if not token:
        return ""

    f = Fernet(get_encryption_key())
    encrypted = f.encrypt(token.encode())
    return encrypted.decode()


def decrypt_token(encrypted_token: str) -> str:
    """Decrypt a token from storage."""
    if not encrypted_token:
        return ""

    f = Fernet(get_encryption_key())
    decrypted = f.decrypt(encrypted_token.encode())
    return decrypted.decode()
```

### Step 7: Update Admin Router

**File:** `app/routers/admin.py`

Add endpoints for managing WordPress site credentials:

```python
from fastapi import APIRouter, Depends, HTTPException
from sqlalchemy.ext.asyncio import AsyncSession
from app.database import get_db
from app.services.wordpress_multisite import WordPressMultiSiteAuth
from app.schemas.website import (
    WebsiteCreate,
    WebsiteUpdate,
    WebsiteResponse,
    WebsiteWithTokenResponse
)


router = APIRouter(prefix="/admin/websites", tags=["admin"])


@router.post("/", response_model=WebsiteResponse)
async def create_website(
    data: WebsiteCreate,
    db: AsyncSession = Depends(get_db)
):
    """Create a new website with optional WordPress config."""
    # Implementation
    pass


@router.put("/{website_id}/wordpress", response_model=WebsiteResponse)
async def update_wordpress_config(
    website_id: int,
    data: WebsiteUpdate,
    db: AsyncSession = Depends(get_db)
):
    """Update WordPress configuration for a site."""
    # Implementation
    pass


@router.post("/{website_id}/wordpress/test")
async def test_wordpress_connection(
    website_id: int,
    db: AsyncSession = Depends(get_db)
):
    """Test WordPress API connection."""
    wp_auth = WordPressMultiSiteAuth(db)
    try:
        credentials = await wp_auth.get_site_credentials(website_id)
        # Test connection
        return {"status": "success", "api_url": credentials["api_url"]}
    except Exception as e:
        raise HTTPException(status_code=400, detail=str(e))


@router.post("/{website_id}/wordpress/sync")
async def sync_wordpress_users(
    website_id: int,
    db: AsyncSession = Depends(get_db)
):
    """Sync users from WordPress site."""
    wp_auth = WordPressMultiSiteAuth(db)
    result = await wp_auth.sync_users(website_id)
    return result
```

### Step 8: Update .env.example

**File:** `.env.example`

```env
# =============================================================================
# IRT Bank Soal - Environment Configuration
# =============================================================================

# -----------------------------------------------------------------------------
# Database Configuration
# -----------------------------------------------------------------------------
DATABASE_URL=postgresql+asyncpg://irt_user:your_password@127.0.0.1:5432/irt_bank_soal

# -----------------------------------------------------------------------------
# Security
# -----------------------------------------------------------------------------
# Generate with: python3 -c "import secrets; print(secrets.token_urlsafe(32))"
SECRET_KEY=your-secret-key-here-change-in-production

# Used for encrypting WordPress tokens stored in database
# Generate with: python3 -c "import secrets; print(secrets.token_urlsafe(32))"
TOKEN_ENCRYPTION_KEY=your-encryption-key-here

# -----------------------------------------------------------------------------
# Environment
# -----------------------------------------------------------------------------
ENVIRONMENT=development
DEBUG=true

# For production:
# ENVIRONMENT=production
# DEBUG=false

# -----------------------------------------------------------------------------
# API Configuration
# -----------------------------------------------------------------------------
API_V1_STR=/api/v1
PROJECT_NAME=IRT Bank Soal
PROJECT_VERSION=1.2.0

# -----------------------------------------------------------------------------
# CORS Configuration (Multi-Site)
# -----------------------------------------------------------------------------
# Comma-separated list of allowed origins
# Example: https://site1.com,https://site2.com,https://site3.com
ALLOWED_ORIGINS=http://localhost:3000,http://localhost:8080

# -----------------------------------------------------------------------------
# WordPress Integration (DEPRECATED - Use Admin Panel)
# -----------------------------------------------------------------------------
# These settings are for backward compatibility only.
# For multi-site WordPress auth, configure each site via Admin Panel.
# The credentials are stored in the 'websites' table.

# Legacy single-site config (will be removed in future version)
WORDPRESS_API_URL=
WORDPRESS_AUTH_TOKEN=

# -----------------------------------------------------------------------------
# OpenRouter API (for AI Question Generation)
# -----------------------------------------------------------------------------
OPENROUTER_API_KEY=your-openrouter-api-key-here
OPENROUTER_API_URL=https://openrouter.ai/api/v1
OPENROUTER_MODEL_QWEN=qwen/qwen-2.5-coder-32b-instruct
OPENROUTER_MODEL_LLAMA=meta-llama/llama-3.3-70b-instruct
OPENROUTER_TIMEOUT=60

# -----------------------------------------------------------------------------
# Redis (for Celery task queue - optional)
# -----------------------------------------------------------------------------
REDIS_URL=redis://127.0.0.1:6379/0

# -----------------------------------------------------------------------------
# Admin Panel
# -----------------------------------------------------------------------------
ADMIN_USER=admin
ADMIN_PASSWORD=change-this-password

# -----------------------------------------------------------------------------
# Normalization Defaults
# -----------------------------------------------------------------------------
DEFAULT_RATAAN=500
DEFAULT_SB=100
MIN_SAMPLE_FOR_DYNAMIC=100

# -----------------------------------------------------------------------------
# Multi-Site WordPress Configuration (via Admin Panel)
# -----------------------------------------------------------------------------
# To add a new WordPress site:
# 1. Go to Admin Panel > Websites
# 2. Click "Add Website"
# 3. Fill in:
#    - Site Name: e.g., "Sekolah ABC"
#    - Site URL: e.g., "https://sekolahabc.com"
#    - WordPress API URL: e.g., "https://sekolahabc.com/wp-json"
#    - WordPress Auth Token: JWT token from that site
#    - Enable WordPress: Yes/No
#
# Each site's credentials are encrypted and stored in the database.
# The API identifies which site is making requests via X-Website-ID header.
# =============================================================================
```

---

## Migration Checklist for AI Agent

When implementing this migration, follow these steps in order:

### Phase 1: Preparation
- [ ] Read all files in `app/core/`, `app/models/`, `app/schemas/`, `app/services/`, `app/routers/`
- [ ] Identify existing WordPress integration code
- [ ] Check current `websites` table schema

### Phase 2: Database
- [ ] Create Alembic migration for `websites` table updates
- [ ] Run `alembic upgrade head`
- [ ] Verify migration applied correctly

### Phase 3: Core Code
- [ ] Create `app/core/security.py` with encryption helpers
- [ ] Update `app/core/config.py` with new settings
- [ ] Update `app/models/website.py` with new fields
- [ ] Create `app/schemas/website.py` with new schemas

### Phase 4: Services
- [ ] Create `app/services/wordpress_multisite.py`
- [ ] Update any existing WordPress service to use new class

### Phase 5: Routers
- [ ] Update `app/routers/admin.py` with website management endpoints
- [ ] Update `app/routers/wordpress.py` to use multi-site auth

### Phase 6: Testing
- [ ] Test adding a website via Admin Panel
- [ ] Test WordPress token verification per site
- [ ] Test user sync per site
- [ ] Test API requests with `X-Website-ID` header

### Phase 7: Documentation
- [ ] Update API documentation
- [ ] Update admin panel help text
- [ ] Remove deprecated single-site code

---

## API Endpoints Summary

### New Endpoints

| Method | Endpoint | Description |
|--------|----------|-------------|
| POST | `/api/v1/admin/websites/` | Create new website |
| GET | `/api/v1/admin/websites/` | List all websites |
| GET | `/api/v1/admin/websites/{id}` | Get website details |
| PUT | `/api/v1/admin/websites/{id}` | Update website |
| DELETE | `/api/v1/admin/websites/{id}` | Delete website |
| PUT | `/api/v1/admin/websites/{id}/wordpress` | Update WordPress config |
| POST | `/api/v1/admin/websites/{id}/wordpress/test` | Test WordPress connection |
| POST | `/api/v1/admin/websites/{id}/wordpress/sync` | Sync users from WordPress |
| POST | `/api/v1/admin/websites/{id}/wordpress/regenerate-key` | Regenerate API key |

---

## Backward Compatibility

During migration, the system should:
1. Fall back to legacy `WORDPRESS_API_URL` / `WORDPRESS_AUTH_TOKEN` if no site-specific config exists
2. Log deprecation warnings when legacy config is used
3. Provide migration script to move legacy config to database

---

## Dependencies to Add

```txt
# requirements.txt - add if not present:
cryptography>=41.0.0
```

---

## Testing Commands

```bash
# Test encryption/decryption
python3 -c "
from app.core.security import encrypt_token, decrypt_token

original = 'test-token-123'
encrypted = encrypt_token(original)
decrypted = decrypt_token(encrypted)

print(f'Original: {original}')
print(f'Encrypted: {encrypted}')
print(f'Decrypted: {decrypted}')
print(f'Match: {original == decrypted}')
"

# Test multi-site auth service
python3 -c "
import asyncio
from app.database import AsyncSessionLocal
from app.services.wordpress_multisite import WordPressMultiSiteAuth

async def test():
    async with AsyncSessionLocal() as session:
        wp = WordPressMultiSiteAuth(session)
        credentials = await wp.get_site_credentials(1)
        print(credentials)

asyncio.run(test())
"
```

---

## Questions for Clarification

If any of these are unclear, ask the user:

1. Should WordPress tokens have an expiration date and auto-refresh mechanism?
2. Should we support multiple authentication methods per site (API key vs JWT)?
3. Should there be a rate limit per website for WordPress API calls?
4. Should the admin panel show sync status and last sync time?

---

**Document End**

**Status:** Ready for Implementation
**Estimated Effort:** 4-6 hours
**Files to Modify:** ~10 files
**Database Changes:** 1 migration