The DDL for the shop slug projection tables, owned here so V171 and any future repair path build the SAME objects from one definition.
What the projection is
phoenix_kit_shop_products.slug and phoenix_kit_shop_categories.slug are
jsonb maps of language → slug. The V52-era uniqueness story was an
expression index on extract_primary_slug(slug) — the alphabetically-first
key's value — which simultaneously under- and over-enforced:
- under: only the primary language was constrained at all, so
{"en":"hat"}and{"de":"hut","en":"hat"}coexisted sharingen=hat, and the collision surfaced on whichever save later ADDED a translation — far from the create that caused it; - over:
{"en":"hat"}and{"de":"hat"}collided on the bare valuehateven though they can never shadow each other in a URL.
The correct bucket is (base language, value) — base, because the
resolver expands a requested language over its spellings ("en",
"en-US", …), so spellings must share one bucket. Each parent row's slug
map is projected into phoenix_kit_shop_{product,category}_slugs rows
(lang, value, owner uuid) by an AFTER trigger, and the projection's
primary key IS the uniqueness constraint. Empty values are never projected
(an unromanizable title yields no slug, not an empty one).
Ecto's unique_constraint in phoenix_kit_ecommerce names the projection
pkeys, so a collision comes back as a changeset error on :slug rather
than a raw Postgrex.Error — the first time that has been true for these
tables.
Summary
Functions
The V171 dedup for one parent table, as a single DO block.
Removes one projection: trigger, function, table.
The two parents and their projections, in creation order.
DDL creating one projection: table, sync function, trigger, backfill.
Functions
The V171 dedup for one parent table, as a single DO block.
Buckets every jsonb slug entry by (base language, value), counting owner
rows, not entries: one row carrying two spellings of a language at the
same value ({"en":"hat","en-GB":"hat"}) projects — via the trigger's
SELECT DISTINCT — to a single projection row, so it is not a collision
and must be left alone. Counting entries instead made it one: an active
product in that shape aborted the upgrade with "shared by 2 live rows",
and a draft one had a spelling silently rewritten to hat-2, changing a
live URL to satisfy a constraint that was never in danger.
Two LIVE rows in one bucket RAISE — one of them must lose a working public
URL, and that is an editorial decision, not an unattended upgrade's (V167
precedent: refuse, and name the value). Otherwise the keeper is live first,
then oldest inserted_at, then uuid — timestamps() stores whole seconds,
so ties are ordinary — and every later row is rewritten in place to
value-2, value-3, … probing the WHOLE table so an unrelated hat-2
pushes the suffix to hat-3 (Slug.ensure_unique/2's rule, applied in
SQL). A losing row's spellings move together, to the one candidate: they
share the bucket, so splitting them would invent a second URL.
Parameterized by table name so the integration tests can drive the same SQL against a scratch clone and manufacture the duplicates a current install can no longer produce.
Removes one projection: trigger, function, table.
The two parents and their projections, in creation order.
DDL creating one projection: table, sync function, trigger, backfill.
Idempotent (IF NOT EXISTS / CREATE OR REPLACE / ON CONFLICT DO NOTHING), so a repair can re-run it over a half-built state.