PhoenixKit.Migrations.Postgres.V52 (phoenix_kit v1.7.33)

Copy Markdown View Source

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.

Summary

Functions

down(opts)

up(opts)