V52: Shop localized slug functional unique index
After V47 converted slug fields to JSONB maps, the old unique constraint on the slug column no longer works correctly for upsert operations.
PostgreSQL's ON CONFLICT compares entire JSONB objects, so:
- {"en-US": "my-slug"} and {"en-US": "my-slug", "es-ES": "otro"} are different
This migration creates a functional unique index that extracts the primary slug value for uniqueness checking.
Changes
- Creates extract_primary_slug() SQL function to get the primary slug value
- Creates unique functional index on products using the function
- Creates unique functional index on categories using the function
- Removes old incorrect unique indexes if they exist
Primary Slug Resolution
The function extracts the slug value from the alphabetically first language key. This is deterministic, language-agnostic, and works regardless of which language is configured as default. The function must be IMMUTABLE for the unique index to work, so it cannot query the settings table for default_language.