Migration: canonicalize pre-existing lowercase `locale` rows after #1551

[marcusbellamyshaw-cell]

Summary

Follow-up from #1572 (review on #1551). That PR makes the API ?locale= filter preserve BCP-47 casing and applies localeCode validation to the shared taxonomy/menu locale filter. It deliberately does not touch rows that were already written with a lowercased locale before the fix.

emdashbot's review suggested a forward-only data migration to canonicalize any pre-existing lowercase locale values in the ec_* tables against the configured locale set. That was scoped out of #1572 to keep the fix focused; this issue tracks it.

Problem

Before #1551, the API schema lowercased the ?locale= filter value. For locales whose canonical BCP-47 form has an uppercase region/script subtag (e.g. zh-TW, pt-BR, zh-Hant), content created through the API could have been persisted with a lowercased locale column value (zh-tw, pt-br, zh-hant) that no longer matches getI18nConfig().locales or the casing-preserving query path. After #1551 those stale rows will be filtered out, because the query now compares against the verbatim-cased locale.

Proposed fix

A forward-only migration that, for each ec_* table carrying a locale column, rewrites any stored locale to its canonical form when a case-insensitive match exists in getI18nConfig().locales. Rows whose locale has no case-insensitive match in the configured set should be left untouched (and ideally surfaced, not silently rewritten).

Notes / open questions

• Must run cross-dialect (D1/SQLite + Postgres) — no dialect-specific casing functions.
• Should be idempotent and safe to re-run.
• Decide handling for rows whose locale matches no configured locale at all (orphaned/removed locale): leave as-is vs. log a warning.
• Confirm the full set of ec_* tables that carry a locale column.

Tracked as the deferred item from #1572.

[danielmlr]

Triage: I believe the core of this issue was resolved by #2174 (merged 2026-07-22, c568876), just under a different mechanism than the migration proposed here — and the remaining delta is small enough that this can probably be closed or re-scoped.

What #2174 shipped

packages/core/src/i18n/repair-locale-casing.ts (repairLocaleCasing) canonicalizes stored locale values against the configured locale set, exactly as proposed here:

• covers every ec_* content table (discovered via listTablesLike(db, "ec_%")) plus the content_taxonomies pivot rows keyed to them
• case-insensitive match against each configured locale; rows with no case-insensitive match are left untouched
• collision-safe: a lowercase row is only rewritten when no row with the canonical casing already occupies the same (slug, locale) slot, and MIN(id) keeps the rewrite deterministic when several non-canonical variants exist

Instead of a one-shot migration it runs as a version-gated cold-start repair (emdash-runtime.ts, phase rt.locale): the option emdash:repair_locale_casing stores 1:<sorted locale list>, so the repair re-runs whenever the configured locale set changes — which also answers the idempotency requirement, and handles the case a migration can't (a site adding zh-TW to its config after migrating). Sites whose locales are all simple lowercase codes skip the repair entirely, which is correct: for them the old lowercasing was a no-op, so no stale rows can exist.

Cross-dialect: the SQL is plain lower() + correlated subqueries, and the discovery/column helpers are already dialect-aware. Unit coverage is in packages/core/tests/unit/i18n/repair-locale-casing.test.ts (10 tests, including the uniqueness-collision and multiple-variant cases).

The open scope question answers itself historically

Confirm the full set of ec_* tables that carry a locale column.

The wider table set turns out not to matter: before #2174, the lowercasing localeCode was used only by the content API schemas and the (read-only) search filters, so content writes were the only path that could persist a lowercased locale. The taxonomies, menus, _emdash_bylines, and _emdash_relations schemas validated locale with a plain z.string().min(1) — no transform — as the #2174 diff shows. So lowercased rows could only ever have been persisted in ec_* tables and content_taxonomies, which is precisely what repairLocaleCasing covers.

What remains of this issue

Two small deltas against the original ask, both around the "surfaced, not silently rewritten" point:

1. Unrepairable rows are skipped silently. Rows whose locale matches no configured locale, and lowercase rows that conflict with an existing canonical-cased row on the same slug, are correctly left untouched — but nothing logs them.
2. Conflict rows are invisible to locale-filtered reads. Since fix(core): preserve locale casing in API locale filter (#1551) #2174, API read filters resolve the requested locale to the configured casing (resolveConfiguredLocale in the content, search, taxonomy, menu, byline, and relations handlers), so a stale zh-tw conflict row is no longer served for any casing of a locale-filtered request. It does still appear in unfiltered lists and in the translations endpoint, but nothing there marks it as a casing conflict — a log line during the repair would make it much easier for an operator to spot.

If there's interest, I'm happy to follow up with a small PR that logs skipped rows (table, id, locale) during the repair phase so operators can resolve conflicts manually. Otherwise I think this can be closed as completed by #2174.

[ascorbic]

Closing as fixed. Thanks for the report.