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

[khoinguyenpham04]

This Roadmap discussion mirrors #1599: Migration: canonicalize pre-existing lowercase locale rows after #1551.

Use this discussion to upvote the roadmap item and discuss priority, use cases, and product feedback. Keep implementation tracking, reproduction details, and PR-specific feedback on the source issue.

• Source issue: #1599
• Status: Open
• Labels: None
• Original author: marcusbellamyshaw-cell

Original Issue

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.