# Universal Response Localization — Architecture & Implementation

> **Based on:** `backend-dynamic-data-localization-plan.md`
>
> **Issue:** LLM translate chậm → 504 timeout khi gọi đồng bộ trong request-response cycle
>
> **Solution:** Cache Priming hybrid — pre-translate tại analysis completion time (zero user-facing latency) + sync fallback nếu cache miss + timeout fallback về English nếu LLM lỗi.
>
> **Key insight:** Analysis pipeline chạy ARQ worker mất 2-5 phút. Translation chỉ mất 2-5s. Pre-translate ngay khi analysis xong → cache luôn có sẵn trước khi user mở page.

---

## 1. Kiến trúc tổng thể

### 1.1 Happy path — Cache đã có sẵn (99% requests)

```mermaid
sequenceDiagram
    participant PIPELINE as Analysis Pipeline<br/>(ARQ Worker)
    participant CACHE as Redis Cache
    participant USER as User
    participant FE as Frontend
    participant API as FastAPI

    Note over PIPELINE: Analysis chạy 2-5 phút

    PIPELINE->>PIPELINE: Analysis result ready<br/>(English, canonical)

    PAR Pre-translation
        PIPELINE->>PIPELINE: Collect candidates (step 1-3)
        PIPELINE->>PIPELINE: LLM translate gpt-4o-mini<br/>(~2-5s)
        PIPELINE->>PIPELINE: Apply + validate
        PIPELINE->>CACHE: SET cache key<br/>localization:{id}:{locale}<br/>TTL=3600s
    end

    Note over USER: Vài phút sau, user mở analysis

    USER->>FE: Click "View Analysis"<br/>locale=ja
    FE->>API: GET /api/v1/analysis/{id}<br/>X-Scopelytics-Locale: ja
    API->>CACHE: Cache lookup
    CACHE-->>API: HIT 🚀
    API-->>FE: HTTP 200<br/>(translated, 10ms)
```

### 1.2 Edge case — Cache miss (user mở quá nhanh hoặc locale chưa primed)

```mermaid
sequenceDiagram
    participant FE as Frontend
    participant API as FastAPI
    participant GUARD as Concurrency Guard
    participant LLM as OpenAI<br/>gpt-4o-mini
    participant CACHE as Redis Cache
    participant ARQ as ARQ Worker (retry)

    FE->>API: GET /analysis/{id}<br/>locale=ja
    API->>CACHE: Cache lookup
    CACHE-->>API: MISS

    API->>GUARD: Acquire semaphore<br/>(wait max 5s)

    alt Semaphore timeout
        API-->>FE: HTTP 200 English<br/>+ X-Localization-Status: skip_busy
        API->>ARQ: enqueue_job("localize_retry", id, ja)
        Note over ARQ: Translate background,<br/>lần sau cache hit

    else Guard acquired
        API->>LLM: chat_completions_parse()<br/>asyncio.wait_for(30s)

        alt LLM success
            LLM-->>API: decisions[]
            API->>API: Apply + validate
            API->>CACHE: SET cache key
            API-->>FE: HTTP 200<br/>(translated, ~3-6s)
        else LLM timeout / error
            LLM-->>API: ❌
            API-->>FE: HTTP 200 English<br/>+ X-Localization-Status: timeout
            API->>ARQ: enqueue_job("localize_retry", id, ja)
            Note over ARQ: Translate background,<br/>lần sau cache hit
        end
    end
```

### 1.3 So sánh các approaches

| Tiêu chí | Sync thuần (plan cũ) | Async background (polling) | **Cache Priming hybrid** 🏆 |
|---|---|---|---|
| UX lần đầu | 3-6s (chờ LLM) | English ngay, flash sau | **10ms 🚀 (cache hit)** |
| UX edge case | 3-6s | English + chờ polling | **3-6s (sync fallback)** |
| Sửa FE | Không | **Cần loading/polling** | **Không** |
| Sửa pipeline | Không | Không | **1 ARQ job sau analysis** |
| 504 timeout | **Có (nếu không tune)** | Không | **Không (timeout fallback)** |
| Cache hit rate | Thấp (chờ user đầu) | Thấp | **Cao (priming chủ động)** |
| Phức tạp | Thấp | Cao | **Trung bình** |

---

## 2. Chi tiết luồng xử lý

### 2.1 Pipeline steps — Request path (response_localizer)

```mermaid
flowchart TD
    REQ["Request vào FastAPI endpoint"]

    REQ --> ENGLISH["Build English response<br/>(Pydantic model)"]

    ENGLISH --> S0{"Step 0 - Early exit<br/>locale=en hoặc feature off?"}

    S0 -->|Có| EN_OUT["Return English<br/>0 token, 0 latency"]

    S0 -->|Không| CACHE{"Cache lookup<br/>localization:{surface}:{locale}:<br/>{sha256(payload)}"}

    CACHE -->|Hit| VALID{"Pydantic validate<br/>cached payload"}
    VALID -->|Pass| HIT["Return localized<br/>🚀 10-50ms"]
    VALID -->|Fail| STEP1

    CACHE -->|Miss| STEP1["Step 1 - Serialize<br/>model → JSON dict"]
    STEP1 --> STEP2["Step 2 - Collect candidates<br/>String leaf extraction"]
    STEP2 --> STEP3["Step 3 - Policy filter<br/>Denylist + surface allowlist"]

    STEP3 --> STEP4["Step 4 - Build LLM request"]

    STEP4 --> GUARD{"Acquire semaphore<br/>wait=5s"}

    GUARD -->|Timeout| GBUSY["Return English<br/>+ enqueue ARQ retry<br/>+ log guard_busy"]

    GUARD -->|OK| LLM_CALL["Step 5 - LLM translate<br/>asyncio.wait_for(30s)<br/>model=gpt-4o-mini"]

    LLM_CALL -->|Success| APPLY["Step 6 - Apply decisions<br/>Patch theo path"]
    LLM_CALL -->|Timeout| TOUT["Return English<br/>+ enqueue ARQ retry<br/>+ log llm_timeout"]
    LLM_CALL -->|Error| ERR["Return English<br/>+ enqueue ARQ retry<br/>+ log llm_error"]

    APPLY --> VALIDATE["Step 7 - Pydantic validate"]
    VALIDATE -->|Pass| WRITE["Write cache<br/>best-effort"]
    VALIDATE -->|Fail| VFAIL["Return English<br/>+ log validate_fail"]

    WRITE --> LOCAL_OUT["Return localized"]
    GBUSY --> EN_OUT
    TOUT --> EN_OUT
    ERR --> EN_OUT
    VFAIL --> EN_OUT
```

### 2.2 Pipeline steps — Cache priming path (ARQ worker)

```mermaid
flowchart TD
    ANALYSIS["Analysis pipeline hoàn thành<br/>trong ARQ Worker"]

    ANALYSIS --> CHECK{"Locale support enabled?<br/>User có locale preference?"}

    CHECK -->|No supported locales| SKIP["Skip priming<br/>0 cost"]

    CHECK -->|Yes| LOAD["Load analysis result<br/>Pydantic model từ DB"]

    LOAD --> CACHE_CHECK{"Cache đã tồn tại?<br/>(analysis rerun)"}

    CACHE_CHECK -->|Đã có| SKIP
    CACHE_CHECK -->|Chưa| COLLECT["Collect candidates<br/>(steps 1-3)"]

    COLLECT --> EMPTY{"Candidates > 0?"}
    EMPTY -->|Không| SKIP

    EMPTY -->|Có| TRIGGER["Kiểm tra concurrency guard<br/>(nếu busy → skip, lần sau sync sẽ fill cache)"]

    TRIGGER -->|Busy| SKIP_PRIME["Skip priming<br/>+ log guard_busy"]

    TRIGGER -->|OK| PRIME["LLM translate<br/>gpt-4o-mini<br/>timeout=30s"]

    PRIME -->|Success| WRITE_CACHE["SET cache key<br/>TTL=3600s"]
    PRIME -->|Timeout/Lỗi| SKIP_PRIME

    WRITE_CACHE --> DONE["Priming done ✅"]
```

### 2.3 Timing budget

```
=== HAPPY PATH (Cache hit) ===
Cache lookup (Redis):            0.01-0.02s
Pydantic validate:               0.01-0.05s
Total:                           0.02-0.07s → ~10ms 🚀

=== EDGE CASE (Cache miss - sync fallback) ===
Build English response:          0.1-0.3s
Cache lookup (miss):             0.01s
Chờ semaphore (nếu busy):       0-5s (tối đa)
LLM translate (gpt-4o-mini):    2-5s (typical)
                                8-15s (worst, batch lớn 12k chars)
Apply + validate:               0.05-0.1s
Write cache:                    0.01s
Total:                           2-6s (typical)
                                8-20s (worst)

=== ERROR PATH (LLM timeout → English fallback) ===
Build English response:          0.1-0.3s
Cache lookup (miss):             0.01s
LLM timeout sau 30s:             30s (wait_for timeout)
→ return English:                ~30s (worst)
+ enqueue ARQ retry              0.01s
```

**Không có 504** vì:
- Cache hit → 10ms (không đụng LLM)
- Sync fallback → asyncio.wait_for(30s) + semaphore wait(5s) → tối đa 35s, dưới proxy timeout
- Error → English ngay + ARQ retry, luôn HTTP 200

---

## 3. Cache Priming — Chi tiết

### 3.1 Injection point

File **`backend/src/app/services/analysis_pipeline.py`** (hoặc worker task tương ứng).

Sau khi analysis pipeline hoàn thành, thêm 1 dòng:

```python
# Sau khi analysis result được persist vào DB
if settings.LOCALIZATION_ENABLED and settings.LOCALIZATION_PRIMING_ENABLED:
    await enqueue_job("localize_prime", analysis_id=analysis.id)
```

Hoặc gọi trực tiếp nếu không muốn tạo job riêng:

```python
from ..services.localization import prime_localization_cache

# Trong worker, sau khi analysis xong:
await prime_localization_cache(analysis_result, supported_locales=["ja"])
```

### 3.2 prime_localization_cache() logic

```python
async def prime_localization_cache(
    analysis: AnalysisDetailResponse,
    supported_locales: list[str],
) -> None:
    """Pre-translate analysis result cho tất cả supported locales (trừ canonical)."""
    for locale in supported_locales:
        if locale == LOCALIZATION_CANONICAL_LOCALE:
            continue
        try:
            # Giống hệt localize_response() nhưng không trả về, chỉ write cache
            decisions = await _call_translator(
                _prepare_candidates(analysis),
                locale=locale,
                context=LocalizationContext(surface="analysis_detail", ...),
            )
            merged = _apply_decisions(analysis.model_dump(mode="json"), decisions)
            validated = AnalysisDetailResponse.model_validate(merged)
            
            await _write_cache(
                key=_make_cache_key(analysis, locale),
                payload=validated.model_dump(mode="json"),
                ttl=LOCALIZATION_CACHE_TTL_SECONDS,
            )
        except Exception:
            logger.warning("localize_prime_failed", extra={"locale": locale})
            # Không fail pipeline — best-effort
```

### 3.3 Số lượng LLM calls

| Scenario | LLM calls/analysis | Khi nào |
|---|---|---|
| **Cache priming** | N supported locales (vd: 1 = ja) | Ngay sau analysis complete |
| **Sync fallback** (cache miss) | 1 | User request đầu tiên |
| **Cache hit** | 0 | Mọi request sau |

Với 1 locale (`ja`): **1 LLM call extra** sau analysis. Analysis mất 2-5 phút, translation thêm 2-5s → không đáng kể.

---

## 4. ARQ Retry Worker

Khi sync fallback timeout/lỗi, enqueue job để translate background — lần sau user request sẽ cache hit.

### Worker settings

```python
# backend/src/app/workers/tasks/localization.py

async def localize_retry(ctx: dict, analysis_id: str, locale: str) -> None:
    """ARQ job: translate analysis trong background, write cache."""
    from ..services.localization import prime_localization_cache
    from ..crud.analysis import get_analysis
    
    analysis = await get_analysis(analysis_id)
    if not analysis:
        return
    
    await prime_localization_cache(analysis, supported_locales=[locale])
```

```python
# backend/src/app/workers/settings.py — thêm vào WorkerSettings.functions
func(
    localize_retry,
    keep_result=0, 
    timeout=120,  # translation job timeout 120s
),
```

---

## 5. Config

```python
# backend/src/app/core/config.py — thêm vào AnalysisSettings

# ── Localization ──────────────────────────────────────────────
LOCALIZATION_ENABLED: bool = False
LOCALIZATION_PRIMING_ENABLED: bool = True       # Cache priming tại pipeline completion
LOCALIZATION_MODEL: str = "gpt-4o-mini"         # Model nhanh cho translation
LOCALIZATION_PROMPT_VERSION: str = "v1"
LOCALIZATION_POLICY_VERSION: str = "v1"
LOCALIZATION_SUPPORTED_LOCALES: str = "en,ja"
LOCALIZATION_CANONICAL_LOCALE: str = "en"
LOCALIZATION_TIMEOUT_SECONDS: float = 30.0      # Timeout cho 1 LLM call
LOCALIZATION_SEMAPHORE_WAIT_SECONDS: float = 5.0 # Timeout chờ semaphore
LOCALIZATION_MAX_CHARS_PER_BATCH: int = 12000
LOCALIZATION_MAX_CANDIDATES: int = 200
LOCALIZATION_MAX_OUTPUT_TOKENS: int = 4000
LOCALIZATION_CACHE_ENABLED: bool = True
LOCALIZATION_CACHE_TTL_SECONDS: int = 3600
LOCALIZATION_DEBUG_HEADERS: bool = False
LOCALIZATION_ENABLED_SURFACES: str = "analysis_detail"
```

---

## 6. Implementation phases

### Phase 0 — Nền tảng (1-2 days)

| Task | File | Detail |
|---|---|---|
| 0.1 | `frontend/lib/i18n/` | Interceptor `proxyApi` set `X-Scopelytics-Locale` từ `scopelytics.locale` |
| 0.2 | `backend/src/app/core/config.py` | Thêm `LocalizationSettings` vào `Settings` |
| 0.3 | `backend/src/app/core/localization.py` | Mới: `RequestLocale`, `LOCALE_LABELS`, `get_request_locale()` |
| 0.4 | Tests | Verify header resolve, unsupported locale fallback `en` |

```
Đầu ra: locale đã vào BE, localizer chưa wire → no-op.
```

### Phase 1 — Core service (3-4 days)

| Task | File | Detail |
|---|---|---|
| 1.1 | `services/localization/types.py` | Mới: `StringCandidate`, `TranslationDecision`, `LocalizationContext` |
| 1.2 | `services/localization/json_collector.py` | Mới: Duyệt JSON, collect string leaf + path |
| 1.3 | `services/localization/denylist.py` | Mới: Hard rules — ID, enum, URL, quote, timestamp |
| 1.4 | `services/localization/surface_policy.py` | Mới: `analysis_detail` allowlist; surface khác deny-all |
| 1.5 | `services/localization/cache.py` | Mới: Fingerprint Redis cache (best-effort) |
| 1.6 | `services/localization/prompts.py` | Mới: Template system/user prompt |
| 1.7 | `services/localization/ai_translator.py` | Mới: 1× OpenAI call via `chat_completions_parse()` + timeout |
| 1.8 | `services/localization/response_localizer.py` | Mới: Orchestrator — steps 0→7 + timeout fallback |
| 1.9 | Endpoint `GET /analysis/{id}` | Wire `localize_response()` sau `build_english_response()` |
| 1.10 | Tests | Unit + integration + cache hit/miss/fallback |

```
Đầu ra: locale=ja → analysis detail trả Japanese (cache hit instant, miss ~3-6s).
```

### Phase 1.5 — Cache Priming (1 day)

| Task | Detail |
|---|---|
| 1.5.1 | Viết `prime_localization_cache()` trong localization service |
| 1.5.2 | Thêm `localize_retry` ARQ worker task |
| 1.5.3 | Wire priming vào `analysis_pipeline.py` sau khi persist |
| 1.5.4 | Thêm config `LOCALIZATION_PRIMING_ENABLED` |
| 1.5.5 | Metrics: `localize_prime_count`, `localize_prime_fail` |

```
Đầu ra: Analysis xong → tự động pre-translate → user mở page → cache hit 10ms 🚀
```

### Phase 2 — Mở rộng surface (2-3 days)

| Task | Detail |
|---|---|
| 2.1 | Wire `bid_advisor_pack` surface với allowlist riêng |
| 2.2 | Wire `design_spec_run_detail` (deny-all → bật từng field) |
| 2.3 | Thêm surface policy tests cho từng surface |
| 2.4 | Mở rộng cache priming cho surface mới |

---

## 7. Fail-open guarantees

```python
async def localize_response(
    model: BaseModel,
    locale: RequestLocale,
    *,
    context: LocalizationContext,
) -> BaseModel:
    """Localize response — guaranteed to return a valid model, never raises HTTP error."""

    # Step 0: Early exit
    if locale.code == LOCALIZATION_CANONICAL_LOCALE:
        return model
    if not settings.LOCALIZATION_ENABLED:
        return model

    try:
        payload = model.model_dump(mode="json")

        # Cache lookup
        if settings.LOCALIZATION_CACHE_ENABLED:
            cached = await _read_cache(_cache_key(payload, locale, context))
            if cached is not None:
                try:
                    return type(model).model_validate(cached)
                except Exception:
                    pass  # Cache corrupt → fall through to re-translate

        # Collect + filter candidates
        candidates = collect_strings(payload)
        translatable = policy_filter(candidates, context.surface)

        if not translatable:
            # Write empty cache to avoid re-checking
            await _write_cache(_cache_key(payload, locale, context), payload)
            return model

        # LLM translate with guard
        try:
            async with _localization_semaphore():
                decisions = await asyncio.wait_for(
                    _call_translator(translatable, locale, context),
                    timeout=settings.LOCALIZATION_TIMEOUT_SECONDS,
                )
        except (asyncio.TimeoutError, SemaphoreTimeout):
            # Fallback: English + enqueue ARQ retry
            await _enqueue_retry(context, locale)
            return model

        # Apply + validate
        merged = apply_decisions(payload, decisions)
        localized = type(model).model_validate(merged)

        # Write cache
        await _write_cache(_cache_key(payload, locale, context), merged)

        return localized

    except Exception:
        logger.exception("localization_fallback")
        return model  # English fallback — always HTTP 200
```

---

## 8. Monitoring & alerting

### Metrics (Prometheus)

| Metric | Type | Labels | Ý nghĩa |
|---|---|---|---|
| `localize_requests_total` | Counter | `locale`, `surface`, `result` (hit/miss/fallback/error) | Tổng số request localization |
| `localize_cache_hit` | Counter | `status` (hit/miss/error) | Cache hiệu quả |
| `localize_latency_ms` | Histogram | `surface`, `result` | Response time percentiles |
| `localize_llm_timeout` | Counter | `surface` | LLM timeout rate |
| `localize_guard_busy` | Counter | — | Semaphore full |
| `localize_prime_count` | Counter | `locale` | Số lần cache priming thành công |
| `localize_prime_fail` | Counter | `locale`, `reason` | Số lần priming thất bại |
| `localize_token_usage` | Counter | `model` | Cost tracking |

### Alerting

| Alert | Condition | Action |
|---|---|---|
| Fallback rate > 5% | `localize_requests{result=fallback} / total > 0.05` | Kiểm tra LLM health, timeout config |
| Prime fail rate > 10% | `localize_prime_fail / localize_prime_count > 0.1` | Kiểm tra OpenAI availability |
| Cache hit rate < 60% | `localize_cache_hit{status=hit} / total < 0.6` | Kiểm tra cache key, priming có chạy không |

---

## 9. Risk matrix

| Rủi ro | Mức | Impact | Mitigation |
|---|---|---|---|
| LLM timeout khi concurrent full | Low | English fallback + ARQ retry | Semaphore wait 5s + asyncio.wait_for 30s → không 504 |
| LLM dịch sai requirement | High | Wrong content | Denylist + surface allowlist + Pydantic validate |
| Cache priming tăng pipeline time 2-5s | Low | Pipeline chậm hơn 2-5s | Chạy song song; không fail pipeline nếu priming lỗi |
| Cache poison (dữ liệu dịch sai) | Medium | Serve sai content | Cache TTL 1h + fingerprint hash |
| Semaphore starvation (translation chiếm slot) | Low | Analysis chậm | Translation dùng semaphore riêng hoặc ưu tiên thấp |

---

## 10. Quyết định kiến trúc

### ADR-001: Cache Priming tại analysis completion time

**Context:** LLM translate chậm gây 504 timeout nếu gọi đồng bộ trong HTTP request.

**Giải pháp chọn:** Pre-translate ngay khi analysis pipeline hoàn thành, trước khi user mở page.

```python
# Trong ARQ worker, sau khi analysis persist:
await prime_localization_cache(analysis, locales=["ja"])
```

**Lý do:**
- Analysis pipeline chạy 2-5 phút → translation thêm 2-5s không đáng kể
- User chưa bao giờ thấy loading translation — cache luôn có sẵn
- Không cần sửa FE, không cần polling/loading state
- Tận dụng ARQ worker có sẵn

**Fallback:** Cache miss vẫn dùng sync LLM với timeout guard — không 504.

### ADR-002: Dùng gpt-4o-mini cho translation

**Context:** Translation là task đơn giản (không cần reasoning).

**Quyết định:** `gpt-4o-mini` thay vì `gpt-5-nano`.

**Lý do:** Đã dùng trong project, latency 2-5s vs 10-15s, quality translation vẫn tốt.

### ADR-003: Sync fallback + ARQ retry

**Context:** Cache miss không thường xuyên nhưng vẫn có thể xảy ra.

**Quyết định:** Sync LLM với timeout guard; nếu lỗi → English + ARQ retry background.

**Lý do:**
- Sync đảm bảo UX đồng bộ (không flash)
- Timeout guard đảm bảo không 504
- ARQ retry đảm bảo lần sau user sẽ cache hit

---

## 11. File structure

```
backend/src/app/
├── core/
│   ├── config.py                          # + LocalizationSettings
│   └── localization.py                    # [MỚI] get_request_locale(), RequestLocale
│
├── services/
│   ├── analysis_pipeline.py               # [SỬA] + priming call sau persist
│   │
│   └── localization/                      # [MỚI] Package localization
│       ├── __init__.py                    # localize_response(), prime_localization_cache()
│       ├── types.py                       # Pydantic/dataclass types
│       ├── json_collector.py              # Duyệt JSON → StringCandidate[]
│       ├── denylist.py                    # Hard rules (code, không LLM)
│       ├── surface_policy.py             # Allowlist theo surface
│       ├── cache.py                       # Redis fingerprint cache
│       ├── prompts.py                     # System/user prompt templates
│       ├── ai_translator.py              # 1× OpenAI call via guard
│       └── response_localizer.py         # Orchestrator steps 0→7
│
└── workers/
    ├── settings.py                        # [SỬA] + localize_retry function
    └── tasks/
        └── localization.py               # [MỚI] ARQ task: localize_retry, localize_prime
```

---

---

## 11. Database Design — Localization Persistence Layer

### 11.1 Vấn đề: Tại sao cần DB?

Redis cache (TTL=3600s) đủ nhanh cho **read-time**, nhưng có điểm yếu:

| Scenario | Redis-only | Redis + PostgreSQL |
|---|---|---|
| Redis restart / deploy | 🔴 Cache miss → sync LLM 3-6s | 🟢 Warm từ DB → cache hit |
| Thêm locale mới (`vi`) | 🔴 Phải re-translate tất cả | 🟢 DB có sẵn các locale khác |
| Cache eviction (memory full) | 🔴 Mất translation | 🟢 DB vẫn còn |
| Admin muốn review/edit translation | 🔴 Không có UI | 🟢 Query DB được |
| Backfill migration | 🔴 Phải gọi LLM lại | 🟢 DB là nguồn durable |

**Kết luận:** Redis đủ cho "data ngay lập tức". DB cần cho **persistence** — translation không bị mất sau restart, deploy, eviction.

### 11.2 Thiết kế: Shadow Translation Table

Dùng pattern **shadow table** (giống supabase-lingo, Azure AI translation) — không sửa bảng gốc, DB vẫn canonical English:

```sql
-- Bảng shadow: lưu translation cho mọi string có thể dịch
CREATE TABLE localization_translations (
    id              BIGSERIAL PRIMARY KEY,
    
    -- Target
    surface         VARCHAR(64)     NOT NULL,   -- analysis_detail, bid_advisor_pack, ...
    row_id          INTEGER         NOT NULL,   -- PK của bảng gốc (vd: analysis_results.id)
    column_name     VARCHAR(128)    NOT NULL,   -- features, summary, task, ...
    json_path       VARCHAR(256)    NOT NULL,   -- JSON pointer: results[0].features[2].title
    locale          VARCHAR(8)      NOT NULL,   -- ja, vi, ko, ...
    
    -- Data
    source_text     TEXT            NOT NULL,   -- English gốc (để detect change)
    translated_text TEXT            NOT NULL,   -- Đã dịch
    prompt_hash     VARCHAR(64)     NOT NULL,   -- Phiên bản prompt đã dịch
    
    -- Metadata
    created_at      TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
    is_active       BOOLEAN         NOT NULL DEFAULT TRUE,
    
    -- Constraints
    UNIQUE (surface, row_id, json_path, locale, prompt_hash)
);

-- Index cho lookup nhanh theo locale
CREATE INDEX idx_localization_lookup 
    ON localization_translations (surface, row_id, locale, prompt_hash)
    WHERE is_active = TRUE;

-- Index cho backfill / migration
CREATE INDEX idx_localization_locale
    ON localization_translations (locale, surface)
    WHERE is_active = TRUE;
```

### 11.3 Cache + DB: 2-Tier Architecture

```text
        ┌──────────────────┐
        │   User Request   │
        │  locale != en    │
        └────────┬─────────┘
                 │
        ┌────────▼─────────┐
        │   Redis Cache     │  ← Tier 1: Hot cache (10ms)
        │   TTL=3600s       │
        └────────┬─────────┘
                 │
    ┌────────────┴────────────┐
    │                         │
    ▼                         ▼
┌──────────┐           ┌──────────┐
│ Cache    │           │ Cache    │
│ HIT 🚀   │           │ MISS     │
└──────────┘           └────┬─────┘
                           │
                    ┌──────▼──────┐
                    │ PostgreSQL   │  ← Tier 2: Persistent (5ms)
                    │ Shadow Table │
                    └──────┬──────┘
                           │
               ┌───────────┴───────────┐
               │                       │
               ▼                       ▼
        ┌──────────┐           ┌──────────┐
        │ DB HIT   │           │ DB MISS  │
        │→ Redis   │           │→ LLM     │
        │  warm    │           │  translate│
        │→ return  │           │→ DB write │
        └──────────┘           │→ Redis   │
                               │  write   │
                               │→ return  │
                               └──────────┘
```

### 11.4 Flow chi tiết: Read + Write

**Read path (response_localizer.py):**

```python
async def _get_translated_payload(
    analysis_id: int,
    locale: str,
    surface: str,
    prompt_hash: str,
) -> dict | None:
    
    # Tier 1: Redis
    cache_key = f"localization:{surface}:{analysis_id}:{locale}:{prompt_hash}"
    cached = await redis.get(cache_key)
    if cached is not None:
        return json.loads(cached)
    
    # Tier 2: PostgreSQL
    rows = await db.execute(
        select(LocalizationTranslation)
        .where(
            LocalizationTranslation.surface == surface,
            LocalizationTranslation.row_id == analysis_id,
            LocalizationTranslation.locale == locale,
            LocalizationTranslation.prompt_hash == prompt_hash,
            LocalizationTranslation.is_active == True,
        )
    )
    
    if rows:
        # Warm Redis
        payload = _rebuild_payload_from_rows(rows)
        await redis.setex(cache_key, 3600, json.dumps(payload))
        return payload
    
    return None  # Cache miss → LLM translate
```

**Write path (prime_localization_cache):**

```python
async def _save_translations(
    analysis_id: int,
    locale: str,
    surface: str,
    prompt_hash: str,
    decisions: list[TranslationDecision],
):
    # PostgreSQL — batch insert
    rows = [
        LocalizationTranslation(
            surface=surface,
            row_id=analysis_id,
            column_name=_extract_column(d.path),
            json_path=d.path,
            locale=locale,
            source_text=d.source_text,
            translated_text=d.translated_text or d.source_text,
            prompt_hash=prompt_hash,
        )
        for d in decisions
        if d.action == "translate"
    ]
    await db.execute(
        insert(LocalizationTranslation)
        .on_conflict_do_update(
            constraint="...",
            set_=dict(translated_text=..., prompt_hash=..., updated_at=func.now()),
        )
    )
    
    # Redis — set cache
    cache_key = f"localization:{surface}:{analysis_id}:{locale}:{prompt_hash}"
    await redis.setex(cache_key, 3600, _build_payload(decisions))
```

### 11.5 Backfill Migration

Khi thêm locale mới hoặc deploy lần đầu:

```sql
-- 1. Tạo bảng
CREATE TABLE localization_translations (...);

-- 2. Index
CREATE INDEX ...;

-- 3. ARQ job: translate tất cả analysis cũ
-- Job chạy background, batch 50 analysis / lần
```

```python
# backend/src/app/workers/tasks/localization_backfill.py

async def backfill_localization_cache(ctx: dict, locale: str):
    """Backfill: translate all existing analyses for a new locale."""
    analyses = await get_all_completed_analyses()
    for batch in chunked(analyses, 50):
        async with asyncio.TaskGroup() as tg:
            for analysis in batch:
                tg.create_task(
                    prime_single_analysis(analysis, locale)
                )
```

### 11.6 So sánh: 3 Storage Strategies

| Tiêu chí | Redis-only | **PostgreSQL Shadow + Redis** 🏆 | PostgreSQL-only |
|---|---|---|---|
| **Read latency** | 10ms 🚀 | 10ms (Redis) / 15ms (DB fallback) | 15-20ms |
| **Survive restart** | ❌ (nếu ko persist) | ✅ | ✅ |
| **Survive eviction** | ❌ | ✅ (DB fallback) | ✅ |
| **Phức tạp** | Thấp | Trung bình | Thấp |
| **Admin UI** | ❌ | ✅ (query DB) | ✅ |
| **Backfill** | Phải re-translate | 1 lần, lưu vĩnh viễn | 1 lần |
| **Source of truth** | English (always) | **English** (luôn canonical) | English |

### 11.7 Entity Relationship

```mermaid
erDiagram
    analyses ||--o{ analysis_results : has
    analysis_results ||--o{ localization_translations : "translated as"
    analyses ||--o{ localization_translations : "translated as"
    
    analyses {
        int id PK
        text title "English canonical"
        text description "English canonical"
        jsonb transcript_metadata
    }
    
    analysis_results {
        int id PK
        int analysis_id FK
        text summary "English canonical"
        jsonb features "English canonical"
    }
    
    localization_translations {
        bigint id PK
        varchar surface "analysis_detail"
        int row_id "FK to analyses.id or analysis_results.id"
        varchar column_name "features, summary, title..."
        varchar json_path "results[0].features[2].title"
        varchar locale "ja, vi, ko..."
        text source_text "English gốc để detect change"
        text translated_text "Đã dịch"
        varchar prompt_hash "Phiên bản prompt"
        timestamp created_at
        timestamp updated_at
        boolean is_active
    }
```

### 11.8 Cache Warm on Startup

Khi backend khởi động, warm Redis từ DB để tránh cache miss hàng loạt:

```python
# backend/src/app/main.py — startup event

@app.on_event("startup")
async def warm_localization_cache():
    """Warm Redis cache from PostgreSQL on startup."""
    if not settings.LOCALIZATION_ENABLED:
        return
    
    # Warm các translation gần đây nhất
    recent = await db.execute(
        select(LocalizationTranslation)
        .where(LocalizationTranslation.is_active == True)
        .order_by(LocalizationTranslation.updated_at.desc())
        .limit(1000)
    )
    
    for row in recent:
        cache_key = f"localization:{row.surface}:{row.row_id}:{row.locale}:{row.prompt_hash}"
        await redis.setex(cache_key, 3600, row.translated_text)
```

---

## 12. So sánh 3 Approaches (Research-backed)

> **Nguồn tham khảo:** AWS Database Blog (Feb 2026), BackendBytes, Google Cloud Translation Best Practices, Oracle Cloud Infrastructure Blog, IBM Research Queue Management, OpenAI API Docs, Anthropic Prompt Caching Docs.

### 11.1 Approach 1: Cache Priming Hybrid ⭐ **Recommended**

**Cách hoạt động:** Pre-translate ALL locales tại analysis completion time trong ARQ worker. User request → Redis GET → trả translated trong 10ms. Nếu cache miss (edge case) → sync LLM fallback với timeout guard. Nếu LLM lỗi → English + ARQ retry background.

```mermaid
flowchart LR
    W["ARQ Worker<br/>(analysis xong)"] --> P["prime_localization_cache()<br/>for ALL locales"]
    P --> R["Redis SET<br/>localization:{id}:{locale}"]
    
    U["User request"] --> GET["Redis GET"]
    GET -->|Hit 🚀 10ms| TRANS["Trả translated"]
    GET -->|Miss| FALLBACK["Sync LLM gpt-4o-mini<br/>asyncio.wait_for(30s)"]
    FALLBACK -->|OK| TRANS
    FALLBACK -->|Timeout| EN["English + ARQ retry"]
```

| Ưu điểm | Nhược điểm |
|---|---|
| ✅ Zero user-facing latency (cache hit 10ms) | ❌ Tốn thêm (N_locales × 2-5s) trong pipeline |
| ✅ Không 504, không flash UI | ❌ Cần backfill migration cho analysis cũ |
| ✅ Không cần sửa FE (loading/polling) | ❌ Pipeline chậm hơn 2-5s (không đáng kể so với 2-5 phút) |
| ✅ Tận dụng ARQ + Redis có sẵn | |
| ✅ Cache hit rate cao nhất (priming chủ động) | |
| ✅ UX đồng bộ, mượt mà | |

**Industry references:**
- [AWS Database Blog (Feb 2026)](https://aws.amazon.com/blogs/database/optimize-llm-response-costs-and-latency-with-effective-caching/): *"Cache translated content at write time — reduces API calls and improves latency by 60%."*
- [Google Cloud Translation Best Practices](https://cloud.google.com/blog/products/ai-machine-learning/four-best-practices-for-translating-your-website): *"Leverage a caching pattern to reduce cost, increase performance."*
- [BackendBytes LLM Integration Patterns (Mar 2026)](https://backendbytes.com/articles/llm-api-integration-patterns/): *"Cache-first architecture with fallback to live inference."*

---

### 11.2 Approach 2: Pure Async Job Queue + Polling

**Cách hoạt động:** API trả về English ngay + `job_id`. FE polling `GET /localization/status/{job_id}`. Khi ARQ worker translate xong → FE fetch lại kết quả và render.

```mermaid
flowchart LR
    REQ["User request"] --> RES["HTTP 200 English<br/>+ X-Job-Id"]
    RES --> POLL["FE polling every 2s<br/>GET /localization/status/{id}"]
    
    ARQ["ARQ Worker"] --> TRANSLLM["Translate gpt-4o-mini"]
    TRANSLLM --> CACHE["Redis SET"]
    CACHE --> POLL
    
    POLL -->|Done| RENDER["FE fetch translated<br/>→ render lại"]
    POLL -->|Pending| POLL
```

| Ưu điểm | Nhược điểm |
|---|---|
| ✅ API response luôn nhanh (10ms) | ❌ **Phải sửa FE**: thêm polling logic, loading state |
| ✅ Không lo timeout HTTP | ❌ **Flash UI**: English → loading → translated |
| ✅ GPU utilization cao hơn (Oracle) | ❌ UX phức tạp: phải handle edge case polling timeout |
| | ❌ Translation chỉ 2-5s — không đủ "lâu" để justify async |
| | ❌ wasted LLM calls nếu user đóng tab trước khi polling xong |

**Industry references:**
- [Oracle Cloud Blog (Feb 2026)](https://blogs.oracle.com/cloud-infrastructure/why-asynchronous-queues-are-the-savio-gpu-utilization): *"Async queues save GPU utilization for long-running inference tasks."*
- [AI API Timeout Guide (May 2026)](https://evolink.ai/blog/ai-api-timeout-retry-fallback): *"Use async polling pattern only for tasks >30s (video/image generation). For text models <30s, synchronous is preferred."*
- [IBM Research Queue Management](https://tianpan.co/blog/2026-04-15-backpressure-llm-pipelines): *"Priority queueing improves SLO by 40-90% for mixed workloads."*

---

### 11.3 Approach 3: Synchronous Optimized + Timeout

**Cách hoạt động:** Giữ synchronous hoàn toàn. Dùng `gpt-4o-mini` (model nhanh nhất). Tăng HTTP timeout lên 60-120s. Fallback English nếu LLM timeout.

```mermaid
flowchart LR
    REQ["User request"] --> ACQ["Acquire semaphore<br/>wait max 5s"]
    ACQ -->|Busy| EN1["English fallback"]
    ACQ -->|OK| LLM["LLM gpt-4o-mini<br/>asyncio.wait_for 30s"]
    LLM -->|OK| TRANS["Trả translated<br/>+ Redis cache"]
    LLM -->|Timeout| EN2["English fallback"]
```

| Ưu điểm | Nhược điểm |
|---|---|
| ✅ Đơn giản nhất, không infra mới | ❌ **First request luôn 3-6s** (kể cả đã tối ưu) |
| ✅ Không cần sửa FE | ❌ **Vẫn có thể 504**: AWS API Gateway hard limit 29s |
| ✅ Dễ debug, dễ maintain | ❌ **UX degrade khi concurrent cao**: semaphore queue khiến request chờ |
| | ❌ Cache hit rate thấp (chờ user đầu tiên trigger) |

**Industry references:**
- [AWS API Gateway Limits](https://www.clawrouters.com/blog/api-gateway-timeout-limit): *"API Gateway REST has a hard 29-second limit that cannot be increased."*
- [BackendBytes (Mar 2026)](https://backendbytes.com/articles/llm-api-integration-patterns/): *"Set HTTP client timeout to 60-120s for text models; use streaming to prevent client-side timeout."*
- [The Concurrency Mistake (Mar 2026)](https://jamwithai.substack.com/p/the-concurrency-mistake-hiding-in): *"FastAPI async + LLM calls = hidden concurrency bottleneck without proper semaphore."*

---

### 11.4 Comparison Matrix

| Tiêu chí | **Approach 1: Cache Priming** 🏆 | Approach 2: Async Polling | Approach 3: Sync Optimized |
|---|---|---|---|
| **UX lần đầu (cache miss)** | 10ms 🚀 | 10ms (English) | **3-6s** ❌ |
| **UX lần đầu (cache hit)** | 10ms 🚀 | 10ms 🚀 | 10ms 🚀 |
| **Sửa FE** | **Không** ✅ | **Có** (polling, loading) ❌ | **Không** ✅ |
| **504 timeout risk** | **Không** ✅ | **Không** ✅ | **Có** (proxy 30s) ❌ |
| **Flash UI (English→translated)** | **Không** ✅ | **Có** ❌ | **Không** ✅ |
| **Infrastructure mới** | **Không** (ARQ+Redis có sẵn) ✅ | **Không** ✅ | **Không** ✅ |
| **Cache hit rate** | **Cao** (priming chủ động) ✅ | Thấp (chờ user trigger) ❌ | Thấp (chờ user trigger) ❌ |
| **Complexity** | Trung bình | **Cao** (FE+BE) ❌ | **Thấp** ✅ |
| **Time to implement** | 4-5 days | 5-7 days | 3-4 days |
| **Backfill migration** | Cần (1 lần) | Không cần | Không cần |
| **Industry consensus** | **Recommended** 🏆 | Only for >30s tasks | Legacy pattern |

### 11.5 Decision Flowchart

```mermaid
flowchart TD
    START["Bạn muốn implement translation?"]
    
    START --> A{"Translation task có<br/>thường xuyên > 30s không?"}
    
    A -->|Có (video/image gen)| B["Approach 2: Async Polling"]
    
    A -->|Không (text translate 2-5s)| C{"Backend đã có<br/>ARQ Worker + Redis?"}
    
    C -->|Không| D["Approach 3: Sync Optimized<br/>(đơn giản, dễ maintain)"]
    
    C -->|Có ✅| E["Approach 1: Cache Priming<br/>tận dụng ARQ + Redis"]
    
    E --> F{"Có analysis cũ<br/>cần backfill không?"}
    
    F -->|Có| G["Chạy backfill job 1 lần<br/>+ bật priming cho analysis mới"]
    F -->|Không| H["Bật priming ngay<br/>cho analysis mới"]
    
    G --> DONE["Done: Zero-latency 🚀"]
    H --> DONE
```

### 11.6 Final Recommendation

```
├── Primary path:  Cache Priming (ARQ worker prime ALL locales)
│   ├── Cache hit  → 10ms response  🚀
│   ├── Cache miss → sync LLM fallback (gpt-4o-mini, 30s timeout)
│   └── LLM error  → English + ARQ retry background
│
├── Backfill:      Migration job for existing analyses (1 time)
│
└── FE change:     Zero (không cần sửa gì)
```

**Lý do chọn Cache Priming:**
1. Tận dụng **ARQ Worker + Redis** có sẵn — không infra mới
2. Translation chỉ 2-5s — không đủ lâu để justify async polling
3. UX đồng bộ, không flash, không loading state
4. Cache hit rate cao nhất vì priming chủ động
5. Đây cũng là pattern được **AWS, Google Cloud khuyến nghị** cho translation caching
6. Nếu sau này có locale mới (`vi`, `ko`, ...) → chỉ cần thêm 1 dòng config, priming tự động mở rộng

---

## Appendix A — Cache key design

```
localization:{surface}:{locale}:{prompt_version}:{policy_version}:{sha256(canonical_payload)}
```

- `surface`: `analysis_detail`, `bid_advisor_pack`, ...
- `locale`: `ja`, `vi`, `ko`, ...
- `prompt_version`: `v1` — bump khi đổi prompt → invalidate all
- `policy_version`: `v1` — bump khi đổi denylist/allowlist
- `sha256(canonical_payload)`: JSON sort keys, compact separators

Ví dụ:
```
localization:analysis_detail:ja:v1:v1:a1b2c3d4e5...
```

## Appendix B — Priming vs Cache warming vs Lazy caching

| Strategy | Khi chạy | LLM calls | UX impact |
|---|---|---|---|
| **Cache priming** (chọn) | Ngay sau analysis complete | 1 locale = 1 call | Zero — user chưa mở page |
| **Cache warming** | Scheduled / predictive | Theo lịch | Zero |
| **Lazy caching** (sync fallback) | Khi user request đầu | 1 call | User đầu chờ 3-6s |

Với architecture này, **prime + lazy fallback** phủ cả 2 trường hợp:
- Normal: priming xong trước → user thấy translated ngay (10ms)
- Edge: priming chưa xong → sync fallback (3-6s) → lần sau cache hit
- Error: LLM lỗi → English + ARQ retry → lần sau cache hit