Database Schema

Omnalingo maintains five custom table structures in the WordPress database. All table names are prefixed with the WordPress table prefix (e.g., wp_omnalingo_urls). The prefix itself is filterable via omnalingo/db/table_prefix — the DatabaseService class applies this filter in its constructor, so every repository inherits the correct prefix.

Understanding this schema is useful for debugging translation gaps, writing custom reports, and verifying data integrity after migrations.

---

Table Overview

Table	Purpose
omnalingo_urls	Index of every translatable URL on the site
omnalingo_strings	Deduplicated store of all translatable strings
omnalingo_url_strings	Junction table — maps URLs to strings
omnalingo_translations_{lang}	Translations for one target language (one table per language)
omnalingo_slugs	Translated URL slugs per object per language

---

omnalingo_urls

The URL index. One row per translatable URL, created during the gathering phase.

Owner: UrlRepository (src/Repository/UrlRepository.php)

Column	Type	Notes
id	bigint unsigned PK AUTO_INCREMENT	Internal URL ID used by all other systems
object_id	bigint unsigned	WordPress object ID (post ID, term ID, or 0 for custom/archive)
object_type	varchar(50)	post, term, archive, custom
content_type	varchar(100)	WordPress post type slug or taxonomy slug (e.g., page, post, product, category)
url	text	Relative URL path, always with trailing slash (e.g., /about/)
title	text	Human-readable title for the admin UI
status	varchar(20)	WordPress publish status (publish, draft, private, etc.)
meta	JSON	Word count metadata (see below)

The meta column stores a JSON object computed during scanning:

{
  "total_words": 520,
  "covered_gt_words": 140,
  "gt_fingerprint": "a3f8c7e1..."
}

• total_words — stable composite: junction table word total (all strings mapped to this URL) + covered_gt_words. Designed to not fluctuate between rescans.
• covered_gt_words — word count of gettext strings fully covered by .mo files for all target languages (not stored in the strings table). Smoothed via MAX(stored, new) across scans to absorb dynamic content noise.
• gt_fingerprint — MD5 of sorted gettext text domains + sorted target language codes. When this changes (plugin activated/deactivated, language added/removed), covered_gt_words resets to the new scan value instead of using MAX.

A page that uses WordPress core and a fully-translated theme may show partial progress immediately after scanning, because total_words includes .mo-covered gettext strings that are already translated.

Key lookups used in practice:

• UrlRepository::getUrlById(int $urlId) — fetch a single row by PK.
• UrlRepository::getUrlIdByPath(string $urlPath) — LIKE-based path lookup used by the visual editor to resolve the iframe page to a URL record ID.

Caution

getUrlIdByPath() uses a LIKE query (WHERE url LIKE %s). On very large URL tables (thousands of rows), this scan can be slow. There is a @todo in the code noting this; if you see slow editor load times on large sites, this query is a likely suspect.

---

omnalingo_strings

The deduplicated string store. Every unique translatable string on the entire site exists exactly once here.

Owner: StringRepository (src/Repository/StringRepository.php)

Column	Type	Notes
id	bigint unsigned PK AUTO_INCREMENT	Internal string ID
original_string	longtext	The original string content
original_hash	char(32) UNIQUE	MD5 hash of the string (deduplication key)
type	varchar(20)	html, gettext, or pattern
word_count	int unsigned	Number of words
pattern_hash	char(32) nullable	Pattern skeleton hash (for pattern-type strings)
pattern_id	bigint unsigned nullable FK (self)	Reference to the canonical pattern string
meta	JSON nullable	Type-specific metadata (see below)

Hash computation by type:

• html and pattern strings: MD5 of the raw string content.
• gettext strings: MD5 of context + original_string — this ensures strings with the same text but different contexts get separate rows. The gettext domain is stored in the meta column but is NOT part of the hash.

Deduplication mechanism:

When a string is discovered during scanning, StringRepository::getOrCreateStringId() computes the hash and does a SELECT first. If no row exists, it inserts one. The UNIQUE constraint on original_hash acts as a safety net for concurrent inserts (there is a race condition acknowledged in a @todo comment; the code uses SELECT-then-INSERT rather than INSERT ON DUPLICATE KEY UPDATE).

The meta column by type:

For gettext strings:

{
  "gettext": {
    "domain": "woocommerce",
    "context": "taxonomy singular name"
  }
}

For pattern strings:

{
  "pattern": {
    "source": "manual",
    "placeholder_meta": [
      { "position": 1, "name": "count", "type": "numerical", "multi_word": false }
    ]
  }
}

For strings that have been through fuzzy matching:

{
  "fuzzy_exhausted": true,
  "fuzzy": {
    "source_hash": "abc123..."
  }
}

The fuzzy_exhausted flag prevents re-running fuzzy matching on a string on every subsequent scan. Once set, the string is skipped by the fuzzy matcher even if it looks like it could match something.

Type field significance:

• html — extracted from rendered HTML; requires a DB translation to be shown on the frontend.
• gettext — captured from WordPress __(), _e(), _x(), _n() etc.; may already be covered by a .mo file.
• pattern — a canonical pattern row; actual content is the pattern skeleton (e.g., %1$s items in cart). Instance strings link back via pattern_id.

---

omnalingo_url_strings

The junction table. Creates the many-to-many relationship between URLs and strings.

Owner: StringRepository (junction queries), MaintenanceRepository (cleanup)

Column	Type	Notes
url_id	bigint unsigned PK, FK → omnalingo_urls.id
string_id	bigint unsigned PK, FK → omnalingo_strings.id
role	varchar(50) DEFAULT 'visitor'	Which scan role discovered this string

The composite primary key (url_id, string_id) prevents duplicate mappings. INSERT IGNORE is used when adding new mappings, so rescans do not re-insert existing pairs.

The role column:

The role records which scan context first discovered the string for this URL: 'visitor', 'administrator', or any other configured role slug. The first role to insert a row wins (because INSERT IGNORE skips subsequent inserts for the same PK). This means a string found on a visitor scan has role='visitor'; a string only found on an admin scan has role='administrator'.

During translation serving, TranslationSubscriber uses this to decide whether a string should be served to the current viewer. Admin bar strings (role=‘administrator’) are only swapped in for logged-in users who have admin bar access.

---

omnalingo_translations_{lang}

One translation table per target language. For example: wp_omnalingo_translations_de_DE, wp_omnalingo_translations_fr_FR.

Owner: TranslationRepository (src/Repository/TranslationRepository.php)

The table name is computed as: {prefix}omnalingo_translations_{sanitize_key($langCode)}. The lang code used here is lang['code'] from the settings (the full locale code, e.g., de_DE), not the slug (de). This matters when debugging: a translation you save via the editor uses the slug (de) which the controller maps to the code (de_DE) to construct the table name.

Column	Type	Notes
id	bigint unsigned PK AUTO_INCREMENT	Internal translation ID
string_id	bigint unsigned UNIQUE, FK → omnalingo_strings.id	One translation per string per language
translated_string	longtext	The translated content
translated_by	varchar(50) DEFAULT 'ai'	'ai' for machine translations, a numeric WordPress user ID for manual edits, 'gt' for .mo file translations shown in the editor UI only
needs_review	tinyint(1) DEFAULT 0	Review flag (currently set by the AI pipeline for low-confidence translations)

The UNIQUE constraint on string_id means each string has at most one active translation per language table.

Empty translation = row deleted:

When a user clears a translation in the editor and saves, EditorController calls TranslationRepository::deleteTranslation() rather than saving an empty string. Empty translation strings are never stored as empty rows. This is intentional: an empty row would be served as a blank string on the frontend, which is worse than falling back to the original. The fuzzy matcher’s fuzzy_exhausted flag prevents the deleted string from being re-matched on the next scan.

Table lifecycle:

Translation tables are created when a language is added in Settings and dropped when a language is removed. SettingsController::updateSettings() calls MaintenanceRepository::syncLanguageTables($targetCodes) after every settings save. If a table is missing for a configured language (e.g., after a database migration that did not transfer the plugin tables), EditorController::saveTranslations() will return an error: “translation table may not exist — try re-saving your language settings.”

---

omnalingo_slugs

Stores per-object, per-language translated URL slugs. Created and managed by the Pro plugin’s slug translation feature.

Owner: SlugService / Pro plugin’s SlugController

Column	Type	Notes
id	bigint unsigned PK AUTO_INCREMENT
object_id	bigint unsigned	WordPress object ID
object_type	varchar(50)	post or term
slug_type	varchar(50)	post_slug, term_slug, or rewrite_base
lang_code	varchar(20)	Target language code (e.g., de_DE)
original_slug	varchar(255)	The original English slug
translated_slug	varchar(255)	The translated slug
translated_by	varchar(50)	Translation source (ai, user ID, etc.)

A UNIQUE constraint on (object_id, object_type, slug_type, lang_code) ensures one translated slug per object per language per slug type.

This table is queried by apply_filters('omnalingo/slug/translate_url', ...) — the filter is fired in RewriterSubscriber for every link that gets prefixed. If the Pro plugin is not active, no rows are written and the filter passes URLs through unchanged.

---

Deduplication in Practice

The junction table (omnalingo_url_strings) is the key to why storage scales with unique content rather than total pages.

A site with 500 pages that share a 45-string header/footer has:

• 45 rows in omnalingo_strings
• 22,500 rows in omnalingo_url_strings (each a pair of two bigint foreign keys — cheap)
• 45 rows per language in omnalingo_translations_{lang}

Without deduplication, storing strings per-page would produce 22,500 string rows and 22,500 translation rows per language. The difference compounds as the site grows.

---

Translation Progress Formula

Progress is calculated by ContentProgressService. The formula per URL per language:

total_words     = meta->total_words  (stored on omnalingo_urls.meta during scan)
translated      = words from strings that have a row in omnalingo_translations_{lang}
                + .mo-covered gettext words (not stored in DB; counted via GettextGapService)
percentage      = (translated / total_words) * 100

meta->total_words includes all gettext strings (including .mo-covered ones). meta->db_words includes only gap gettext strings (not covered by .mo). The difference accounts for why progress can be non-zero on a freshly gathered site that has never been translated — the .mo files already cover some gettext strings.

---

Direct Database Queries

All data access in the plugin goes through repository classes. For custom reports or debugging, you can query directly:

global $wpdb;
$prefix = apply_filters('omnalingo/db/table_prefix', $wpdb->prefix);

// Count untranslated HTML strings for German
$untranslated = $wpdb->get_var(
    "SELECT COUNT(*)
     FROM {$prefix}omnalingo_strings s
     LEFT JOIN {$prefix}omnalingo_translations_de_DE t ON s.id = t.string_id
     WHERE t.id IS NULL
       AND s.type = 'html'"
);

// Find all strings discovered by a specific URL
$strings = $wpdb->get_results($wpdb->prepare(
    "SELECT s.*
     FROM {$prefix}omnalingo_strings s
     INNER JOIN {$prefix}omnalingo_url_strings us ON s.id = us.string_id
     WHERE us.url_id = %d",
    $urlId
));

Caution

Direct writes to translation tables bypass TranslationRepository, which means they bypass the needs_review flag logic and the fuzzy exhaustion tracking. If you insert translations directly, the data will be served on the frontend correctly, but the editor’s “previously found” fuzzy matching state may become inconsistent. For write operations, use POST /omnalingo/v1/editor/save.

Note that the table prefix is filterable. If you hardcode $wpdb->prefix . 'omnalingo_...' in a custom query without applying the filter, it will break on sites where the prefix is customized.