Technical Documentation

Architecture, data model, API, front-end localization, and tooling reference for the i18n Manager.

Architecture overview

The i18n Manager is a classic server-rendered PHP application backed by PostgreSQL, with a jQuery/Foundation single-page-style front end. There is no build step or framework — the browser loads index.php, which talks to a set of small PHP endpoints under /api/ via AJAX. Each endpoint connects directly to PostgreSQL through a thin procedural wrapper and returns JSON.

Browser (index.php + jQuery)
   │  AJAX (GET/POST)
   ▼
/api/*.php  ──►  classes/class_pgsql_data.php  ──►  PostgreSQL
   │
   └─ JSON responses

Tech stack

Layer	Technology
Server	PHP (procedural), `pg_*` extension
Database	PostgreSQL (hosted on Supabase)
Front-end libs	jQuery 3.6, Foundation Sites 6.7.5, Font Awesome 6.4 (via CDN)
Styling	`includes/app.css`
Tooling	Node.js (the key scanner); no runtime npm dependencies
API docs	OpenAPI 3.0, Swagger UI, ReDoc, Postman collection

Directory structure

i18n_management/
├── index.php                  # Dashboard SPA (markup + jQuery app)
├── pages/                     # Standalone HTML pages (docs + API UIs)
│   ├── swagger.html           # Swagger UI (interactive API docs)
│   ├── redoc.html             # ReDoc (read-only API docs)
│   ├── user-guide.html        # End-user documentation (+ .de.html)
│   ├── technical.html         # This page (+ .de.html)
│   └── integration.html       # Integration guide (+ .de.html)
├── api/
│   ├── languages_list.php
│   ├── language_toggle.php
│   ├── translations_list.php
│   ├── translations_i18n.php  # flat { key: {lang: value} } map
│   ├── translations_export.php
│   ├── translation_key_save.php
│   ├── translations_import.php
│   ├── translation_key_delete.php
│   └── openapi.json           # OpenAPI 3.0 spec
├── classes/
│   └── class_pgsql_data.php   # PostgreSQL access wrapper
├── includes/
│   ├── app.css                # Dashboard styles
│   └── docs.css               # Documentation styles
├── tools/
│   └── scan_i18n_keys.js      # Markup scanner + seed generator
├── hold_files/                # Reference SQL/JSON (schema, seeds, i18n.json)
├── seeds/                     # Generated seed output (optional)
└── postman/                   # Postman collection

Database schema

Defined in hold_files/schema.sql. Four tables plus triggers and indexes.

`languages`

Column	Type	Notes
code	CHAR(2)	PK — ISO 639-1 code (`en`, `de`…)
name	VARCHAR(64)	Display name
is_active	BOOLEAN	Default TRUE
created_at	TIMESTAMPTZ	Default NOW()

`translation_keys`

Column	Type	Notes
id	SERIAL	PK
key	VARCHAR(255)	UNIQUE — e.g. `home.welcome`
namespace	VARCHAR(64)	GENERATED ALWAYS AS `split_part(key,'.',1)` STORED
description	TEXT	Optional translator hint
contains_html	BOOLEAN	Default FALSE
created_at / updated_at	TIMESTAMPTZ	`updated_at` maintained by trigger

`translations`

Column	Type	Notes
id	SERIAL	PK
key_id	INT	FK → translation_keys, ON DELETE CASCADE
language_code	CHAR(2)	FK → languages, ON DELETE RESTRICT
value	TEXT	The translated string
is_reviewed	BOOLEAN	Default FALSE
created_at / updated_at	TIMESTAMPTZ	Maintained by trigger
UNIQUE (key_id, language_code) — one row per key × language; enables upserts via `ON CONFLICT`.

`translation_audit`

Append-only log written by an AFTER UPDATE trigger on translations whenever a value changes: stores key_id, language_code, old_value, new_value, changed_by, changed_at.

Triggers & indexes

set_updated_at() — BEFORE UPDATE on both translation_keys and translations, bumps updated_at.
audit_translation_change() — AFTER UPDATE on translations, records value changes into translation_audit.
Indexes on translations(key_id), translations(language_code), and translation_keys(namespace).

Data access layer

classes/class_pgsql_data.php is a procedural wrapper around the PHP pg_* functions. Endpoints include it via $_SERVER['DOCUMENT_ROOT'] and use these helpers:

Function	Purpose
`DB_Connect_Direct($write)`	Open a connection (read-write or read-only).
`DB_Query($conn, $sql)`	Run a literal query.
`DB_QueryParams($conn, $sql, $params)`	Parameterized query (`$1, $2…`) — use this to avoid SQL injection.
`DB_FetchAssoc` / `DB_FetchAll`	Fetch one row / all rows as associative arrays.
`DB_Begin` / `DB_Commit` / `DB_Rollback`	Transaction control (used by save & import).
`DB_AffectedRows` / `DB_FreeResult` / `DB_Close`	Row count / cleanup.

Booleans: PostgreSQL returns booleans as the strings 't'/'f'. Endpoints normalize these to real booleans before encoding JSON (e.g. $lang['is_active'] === 't').

API endpoints

All endpoints return JSON (except the export download). The full request/response schema lives in openapi.json and is browsable via Swagger / ReDoc.

Method	Endpoint	Parameters	Purpose
GET	`languages_list.php`	—	List languages.
POST	`language_toggle.php`	`code`, `is_active`	Activate/deactivate a language.
GET	`translations_list.php`	`namespace`, `search`, `page`, `limit`	Paginated keys + translations.
GET	`translations_i18n.php`	`namespace`, `search`, `format`	Flat `{key:{lang:value}}` map (json or `js`).
GET	`translations_export.php`	`language`*, `format`, `namespace`, `search`	Downloadable JSON/CSV.
POST	`translation_key_save.php`	`id`, `key`, `description`, `contains_html`, `mark_reviewed`, `translations`	Create/update a key (transactional upsert).
POST	`translations_import.php`	`data`, `overwrite`, `is_reviewed`	Bulk import a JSON map.
POST	`translation_key_delete.php`	`id`	Delete a key (cascades).

* required

API conventions

POST bodies are application/x-www-form-urlencoded (read from $_POST), not JSON bodies. Fields that carry structured data (translations, data) are JSON strings.
Success shape: { "success": true, … }. Error shape: HTTP 4xx/5xx with { "success": false, "error": "…" }.
Unicode: JSON is emitted with JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES so values stay human-readable.
Upserts rely on the UNIQUE(key_id, language_code) constraint with INSERT … ON CONFLICT DO UPDATE.
translations_i18n.php includes active languages only and serializes keys with no translations as {}.

Front-end localization engine

The dashboard localizes its own chrome from the same data it manages. On load it fetches translations_i18n.php into state.i18n (a flat {key:{lang:value}} map), builds the language switcher from the active languages, and applies translations.

UI elements opt in via data attributes:

Attribute	Effect
`data-i18n="key"`	Sets the element's text.
`data-i18n-html="key"`	Sets the element's innerHTML (for values containing markup).
`data-i18n-placeholder="key"`	Sets an input's `placeholder`.

applyI18n(lang) walks every tagged element, looks up state.i18n[key][lang], and updates it; missing keys/languages fall back to the existing (English) markup. The selected language is stored in localStorage and reflected on <html lang>.

Per-key auto-translation in the Add/Edit dialog calls Google's translate.googleapis.com/translate_a/single?client=gtx endpoint for each target language.

Key scanner — `tools/scan_i18n_keys.js`

A dependency-free Node script that scans source files for data-i18n* attributes, determines which keys are new, and generates ready-to-paste Bulk-Import JSON and idempotent seed SQL — optionally machine-translating the source text into every language.

# New keys vs. the seed/reference files
node tools/scan_i18n_keys.js

# Diff against the LIVE database instead of seed files
node tools/scan_i18n_keys.js --endpoint http://localhost/api/translations_i18n.php

# Machine-translate the source text into all languages
node tools/scan_i18n_keys.js --translate

# Write seeds/new_keys.{json,sql} instead of printing
node tools/scan_i18n_keys.js --out seeds

# Other flags: --all (ignore existing), --root DIR, --langs de,fr,es

Scans .php/.html/.htm/.js/.tpl; ignores .git/node_modules/vendor/tools.
Reads the English default from each element's text / inner HTML / placeholder.
HTML-flagged keys are not auto-translated (translating markup would corrupt it).
Machine translations are seeded with is_reviewed = false.
Diagnostics go to stderr; generated output to stdout.

Seeds & reference files

File	Purpose
`hold_files/schema.sql`	Full database schema (tables, triggers, indexes).
`hold_files/seed_translations.sql`	Demo content keys (`nav.home`, `home.welcome`…).
`hold_files/seed_ui_chrome.sql`	Interface chrome keys (tabs, buttons, table headers) in all languages.
`hold_files/i18n.json`	Reference flat map illustrating the export shape.

Seed files use a CTE (WITH ins_keys AS (INSERT … RETURNING)) and ON CONFLICT … DO UPDATE so they are idempotent and safe to re-run.

API documentation artifacts

api/openapi.json — single source of truth (OpenAPI 3.0.3).
swagger.html — Swagger UI with Try it out (live requests).
redoc.html — ReDoc, read-only reference.
postman/i18n_management.postman_collection.json — Postman collection (v2.1.0) with a base_url variable.

Setup & deployment

Serve the project root with PHP (the pg_* extension must be enabled). The app assumes it is served at the domain root — it references /includes/, /classes/, /api/ and $_SERVER['DOCUMENT_ROOT'].
Provision a PostgreSQL database and apply hold_files/schema.sql.
Seed reference data: seed_translations.sql and/or seed_ui_chrome.sql.
Configure database connection details in classes/class_pgsql_data.php (see security note below).
Open index.php. The CDN assets (Foundation, jQuery, Font Awesome, Swagger/ReDoc) require internet access.

Security notes

Hardcoded credentials: database host/user/password are currently hardcoded in classes/class_pgsql_data.php. Move them to environment variables and rotate the password — anything committed to git should be treated as compromised.

HTML keys (data-i18n-html / contains_html) render raw HTML via innerHTML. Only flag trusted content as HTML; treat translation values as a potential XSS vector otherwise.
Swagger "Try it out" executes real writes (save/import/delete) against whatever database the server uses. Use ReDoc for read-only sharing, or point Swagger at a non-production host.
Machine translation sends source text to Google's unofficial gtx endpoint; avoid it for confidential strings and review output before trusting it.
SQL injection is mitigated by using DB_QueryParams with bound parameters throughout — keep new queries parameterized.