link,[object Object]
ACQM Docs

Appearance

Translations (DB-backed i18n with Admin UI) ​

Purpose ​

End-to-end overview of the editable translations system: DB storage, Admin UI, Edge APIs, runtime loading with JSON fallback, caching, stats, and troubleshooting.

Architecture ​

  • Storage: public.i18n_translations (namespace, key, locale, value, status, updated_at)
  • Meta: public.i18n_meta (locale, namespace, cache_version, updated_at) bumped by trigger on writes
  • Security: RLS → public SELECT; admin-only writes via Edge Functions (JWT role)
  • APIs: Edge function translations for upsert/patch/import/export/invalidate
  • Read path: app fetches via supabase-js (public), merges over JSON fallback
  • Admin UI: /admin/translations with filters, inline edit, import/export, stats

Data Model ​

  • i18n_translations columns: id, namespace, key, locale (en|ro), value, status (draft|published), updated_by, updated_at; unique (namespace, key, locale)
  • Indexes: (namespace, key), (locale)
  • RLS: SELECT true; writes guarded by Edge/JWT role admin

Edge Endpoints (admin) ​

  • POST /translations/upsert — upsert row { namespace, key, locale, value, status? }
  • PATCH /translations/patch/:id — partial update
  • POST /translations/invalidate — { locale, namespace }
  • GET /translations/export — CSV/JSON export
  • POST /translations/import — CSV/JSON import (idempotent)

Headers: send authenticated Authorization: Bearer <token>; UI uses supabase.functions.invoke or edgeFetch with x-acqm-role: admin.

Frontend Integration ​

  • Loader src/lib/i18n/loadMessages.ts: DB-first per (locale, namespaces[]), then JSON fallback. DB overrides JSON.
  • Client src/lib/i18n/dbClient.ts:
    • fetchTranslations({...}) → { rows, count } (public read, search by namespace/key/value)
    • Admin actions (upsert/patch/invalidate/import/export) via Edge
  • Provider src/contexts/translation/TranslationProvider.tsx: wires loader into t() utility.

Admin UI ​

Route: /admin/translations

  • Tabs: Overview & Stats; Translations Table
  • Filters (in Table tab): locale, namespace (full list with pagination), import/export
  • Table: namespace, key, value (editable), value (ro) (side-by-side), status, updated_at
  • Search: header search across namespace/key/value (EN/RO)
  • Footer: "Total: X din Y" (X client filtered, Y exact server count)

Stats ​

  • Logic src/lib/i18n/translationStats.ts: paginated fetch of ALL rows (batches of 1000); compute per-namespace enCount, roCount, completionRate = ro/en, missingKeys; overall totals.
  • UI src/components/admin/translations/TranslationStats.tsx: cards (Total, EN, RO, Overall), complete/incomplete lists. EN/RO cards show completion contextually (no 50% confusion).

Import/Export & Flattening ​

  • Import/Export handled by Edge function.
  • Keys use dot-notation under namespace (e.g., account.documentTypes.id_card.label).
  • Flatten nested JSON on import; ensure string extraction avoids extra quotes.

Caching & Invalidation ​

  • App cache: in-memory map by (locale, namespace).
  • DB trigger bumps i18n_meta.cache_version on writes.
  • UI invalidates namespace after edits to refresh immediately.

Security ​

  • Public read via RLS; admin writes via Edge (JWT role check). No secrets in frontend; use .env.local with VITE_ vars.

Performance ​

  • Table fetch limited (e.g., 500 rows) for UI speed; server count shows exact total.
  • Stats use batched full-table reads to avoid default 1000 cap.
  • Indexes support main filters and search.

QA Checklist ​

  • Sidebar visible on /admin/translations; namespaces list complete.
  • Search matches namespace/key/value (EN/RO).
  • Inline edits persist; namespace invalidation refreshes values.
  • Stats reflect full DB (thousands), not capped at 1000.
  • Footer shows "Total: X din Y" correctly for current filter.

Troubleshooting ​

  • Only 1000 rows in stats → ensure paginated fetch in translationStats.
  • CORS 401 on Edge → deploy function, allow OPTIONS, send Authorization.
  • Quotes in DB values → flatten uses proper string extraction.
  • Missing namespaces → ensure paginated namespace fetch (no implicit limit).
  • Schema & RLS: supabase/migrations/*i18n_translations*.sql
  • Edge API: supabase/functions/translations/index.ts
  • Client: src/lib/i18n/dbClient.ts
  • Loader: src/lib/i18n/loadMessages.ts
  • Stats: src/lib/i18n/translationStats.ts
  • Admin page: src/pages/admin/translations.tsx
  • Stats UI: src/components/admin/translations/TranslationStats.tsx