Building a 24-Language Journal in Plain PHP: AI Translation, Hash-Based Staleness, and a Points-Gated Community

findnix.eu is a GDPR-first metasearch engine with its own index. Search was never the whole story, so we added a Journal: articles, a weekly digest, "finds" (sites worth a look), user questions and answers, and a profile page for every domain in the index. It's plain PHP 8.4 and MariaDB, no framework, in the same codebase as everything else. These are the decisions that mattered.

One table, four post types, translations on the side

Articles, digests, finds and questions share one table with an ENUM type column. Translations live in their own table keyed by (post_id, lang), so the original post never changes shape and a missing language is just a missing row. German is the source language; the other 23 EU languages are translations. Each language version gets its own URL (/journal/fr/some-slug) with hreflang links, and the sitemap lists every version with its translated title and description, so the translated pages end up in our own search index too.

Markdown without a library

The renderer is about 100 lines. The one rule that keeps it safe: escape everything first, then generate only the tags we allow.

$t = htmlspecialchars($t, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
// only now turn [text](url) into <a>, and only for http(s), mailto or local paths

Raw HTML in a post simply shows up as text. Links from user content get rel="nofollow ugc".

Translating HTML with an LLM, and not trusting the result

Posts are rendered to HTML once, and that HTML is what we translate: split at <h2>–<h4> boundaries into chunks of a few thousand characters, one gpt-4o-mini call per chunk, with the instruction to keep every tag and attribute and translate only visible text. Title and teaser go in a separate call that returns JSON.

The important part is what happens afterwards. An article is attacker-controlled text as far as the model is concerned: a user post could contain instructions, and the model might obey them and emit markup. So the translated HTML goes through a DOMDocument whitelist (a short list of tags and attributes, URL schemes checked) before it is stored. If the model produces a <script>, it never reaches a page.

"Is this translation stale?" is not an updated_at question

First version: compare the translation's timestamp with the post's updated_at. It broke immediately, because updated_at moves when you pin a post, change its status, or bump the view counter. Every pin would have re-translated 23 languages.

Now each translation stores a hash of the source text it was made from:

MD5(CONCAT(title, CHAR(10), COALESCE(excerpt, ''), CHAR(10), body_md))

A translation is current if its stored hash equals the post's current hash. Pinning, status changes and view counts don't matter any more. (View counters are updated with updated_at = updated_at anyway, so they don't touch the timestamp.)

Two triggers, one idempotent worker

Translating all languages for a post takes a minute or more, which is too long for a save request. We use two triggers:

• In the editor: after saving a published post, the admin page loops over the missing languages from the browser, one request per language, with a progress bar. Instant feedback, no server timeout.
• A cron job every 15 minutes picks up everything the editor didn't: a digest approved from the list view, a user submission approved by a moderator. It takes a file lock so runs never overlap, skips posts edited in the last ten minutes (the editor is probably handling those), stops after five consecutive failures instead of hammering a broken API, and stays silent when there is nothing to do.

Both call the same function and write with an upsert, so running them twice is harmless.

The bug worth sharing: Slovak came back as Slovenian

Our first batch of translations looked fine until we noticed that the Slovak and Slovenian titles were identical, for 8 of 9 posts. The prompt said "Slovenčina (sk)". The model read the name, not the code, and answered in Slovenian.

The fix was to use English language names plus an explicit contrast in the prompt: "Slovak (the language of Slovakia; NOT Slovenian and NOT Czech)", and the mirror image for Slovenian, Czech, Croatian, Danish and Swedish. Re-running the nine posts produced correct Slovak.

How we found it matters more than the fix. Asking the model to identify the language of each translation caught only 2 of the 8 wrong ones. A plain GROUP BY title HAVING COUNT(*) > 1 across languages caught all of them. For batch-generated content, cheap invariants beat asking an LLM to grade itself.

Community features, all on one points ledger

Every user action (a comment, an answer, a question, a submitted article, a domain review) goes through the same two helpers: one that grants points with a daily cap, and one that revokes them. The rules we settled on:

• Points are granted only when the content becomes visible, never at submit time.
• Spam or deletion revokes exactly what was granted. Each comment remembers its points for that reason.
• A user's first comment, anything containing a link, and anything the blacklist flags goes to a moderation queue. Once a user has visible content, they are "trusted" and skip the queue.
• Answering your own question earns nothing, and the best-answer bonus can't be given to yourself.

The amounts live in a settings table, so we can tune them in the admin without a deploy.

Domain profiles without scanning 37 million rows

Every domain in the index gets a page with its page count, sitemap count, first-seen date and a few sample pages. Counting rows per domain in a 37-million-row table on every request is a non-starter, so the count is capped inside a subquery and the result is cached:

SELECT COUNT(*) FROM (
  SELECT 1 FROM fnx_sitemap_urls
  WHERE domain = ? AND status = 'aktiv' AND adult = 0
  LIMIT 100000
) t

Large domains show "100,000+", which is honest and costs one index range scan. A batch job refreshes the numbers slowly, with a pause between domains, rather than all at once.

One more false positive

Our spam blacklist matched keywords as substrings. The keyword anal blocked analyst.nl, analytics-agentur.ch and psykoanalyse.no; sex would have blocked sussex.ac.uk. We added a =word syntax that matches only at word boundaries (no letter or digit directly before or after) and left long, unambiguous terms as substrings so that concatenated spam domains still get caught. Of the 27 indexed domains the old filter had hidden, all 27 were false positives.

Takeaways

• Store a content hash with derived data instead of comparing timestamps.
• Treat LLM output as untrusted markup, even when you wrote the input.
• Name the language in the prompt, not just its code, and add a "not X" clause for close neighbours.
• Cheap data invariants find batch bugs that "ask the model" checks miss.
• Put every reward or privilege behind one ledger with caps and a way to take it back.

The Journal is live at findnix.eu/journal, and the questions section is at findnix.eu/journal/fragen.