Don't use a slug as an identity

Lots of systems need a way to say "these rows belong together". Think of the language versions of an article, the variants of a product, or the revisions of a page. The quick way to do it is to reuse a field the rows already share. If every version of an article has the same slug (the readable part of a URL, like about-us), the slug becomes the link. No new column needed.

That shortcut works in the demo and breaks in production. A slug isn't an identity. It's a value people edit. In this post I'll cover the difference between natural keys and surrogate keys, what goes wrong when an editable field links records, and how to move an existing table to a stable group ID. I'll finish with a related trap: slug generators that only understand English.

Natural keys and surrogate keys

A natural key is a real-world value that happens to be unique, like an email address, an ISBN or a URL slug. A surrogate key is a value the system makes up only to identify something, like a UUID or a sequence number. It means nothing to users, so nobody has a reason to change it.

Natural keys are tempting because they're already there and easy to read. They turn into a problem once both of these are true:

• users can edit the value
• other rows rely on it to find each other

At that point an edit isn't just a change to one row. It quietly changes which rows are related.

Three ways a slug-based link breaks

Say you have a content table where the language versions of an entry are linked because they share a slug within the same schema:

-- "the translations of this entry" = rows with the same schema and slug
SELECT * FROM documents
WHERE schema_id = $1 AND slug = $2 AND deleted_at IS NULL;

Edits quietly unlink

An editor changes the French version's slug from about-us to a-propos. That's exactly what a localized site should allow. But now the query above can't find it. Nothing errors. The French version just stops being a translation of anything.

Empty values can't link

Not every entry has a slug. Settings pages, snippets and blocks often have no web address at all. With slug-based linking, those entries can't have translations. So the product ends up showing a message like "give the entry a web address before adding languages". That message is a schema limit leaking into the UI.

Matching across environments breaks

Say the system keeps development, staging and production copies of the same project. When you promote an entry, you need to find "the same entry" in the target. Matching by slug brings along every problem above. It also fails when slugs differ between environments on purpose.

A stable group ID

The fix is to give the group its own surrogate key:

ALTER TABLE documents ADD COLUMN translation_group_id uuid;
ALTER TABLE documents ADD COLUMN is_original boolean NOT NULL DEFAULT false;

Every language version of an entry gets the same translation_group_id. Exactly one of them has is_original = true. Now the slug is free to differ per language, to change, or to be empty.

Two design choices matter here.

First, the group ID is not a foreign key to a groups table. There's nothing else to store about a group, so a table would only add joins.

Second, when you promote an entry to another environment, you copy the group ID instead of making a new one. The same entry then has the same group ID in development, staging and production. Matching across environments becomes a simple equality check on a value nobody edits.

Backfilling groups with window functions

Existing rows need group IDs based on the old slug links. Window functions (SQL functions that look at a set of related rows next to the current one) can do this in one statement, with no application code:

WITH grouped AS (
    SELECT id,
        FIRST_VALUE(id) OVER (
            PARTITION BY project_id, schema_id, slug
            ORDER BY created_at, id
        ) AS group_id
    FROM documents
)
UPDATE documents d
SET translation_group_id = g.group_id,
    is_original = (d.id = g.group_id)
FROM grouped g
WHERE d.id = g.id;

PARTITION BY makes one group for each old link. FIRST_VALUE(id) picks one fixed row to represent it, here the oldest. That row's ID becomes the group ID for every row in the partition.

The ORDER BY created_at, id matters. Without id as a tie-breaker, two rows created at the same moment could give different results depending on the query plan.

You can widen the partition so IDs line up across environments. Partition by a key that identifies the project in all its environments, not by one environment's project ID. Then one pass gives the same entry the same group ID everywhere.

Enforcing the rules with partial unique indexes

A group should have at most one version per language and exactly one original. Checks in application code aren't enough. Two requests at the same time can both pass the check and both insert. Let the database enforce it:

CREATE UNIQUE INDEX documents_group_locale_key
    ON documents (project_id, translation_group_id, locale)
    WHERE status <> 'deleted';

CREATE UNIQUE INDEX documents_group_original_key
    ON documents (project_id, translation_group_id)
    WHERE is_original AND status <> 'deleted';

The WHERE clause makes each index partial: it only covers rows that match. The second index only holds originals, so "unique per group" means "at most one original per group". Both indexes skip deleted rows. That way, trashing a version and recreating it doesn't collide with its own corpse.

Changing which version is the original now takes some care. If you set the new original first, the index sees two originals and rejects the update. So clear the old one first, then mark the new one, in one transaction:

BEGIN;
UPDATE documents SET is_original = false
WHERE project_id = $1 AND translation_group_id = $2 AND is_original;
UPDATE documents SET is_original = true WHERE id = $3;
COMMIT;

Other sessions only see the committed result. So they never see a group with zero or two originals.

Picking one row per group with row-value comparison

A delivery API often wants one version per entry. That's the visitor's preferred language if it exists, otherwise the original, otherwise the oldest. PostgreSQL compares row values (tuples like (a, b, c)) one element at a time, so you can write the ranking as a tuple:

AND NOT EXISTS (
    SELECT 1 FROM documents o
    WHERE o.translation_group_id = d.translation_group_id
      AND o.id <> d.id AND o.status = 'published'
      AND (CASE WHEN o.locale = $pref THEN 0 WHEN o.is_original THEN 1 ELSE 2 END, o.created_at, o.id)
        < (CASE WHEN d.locale = $pref THEN 0 WHEN d.is_original THEN 1 ELSE 2 END, d.created_at, d.id)
)

A row comes back only if no sibling ranks strictly better. The tuple ends with id, so there are no ties. Exactly one row per group survives.

Slugs that only understand English

Once slugs can differ per language, they have to work in every language. A common slug generator looks like this:

var nonAlphanumRe = regexp.MustCompile(`[^a-z0-9]+`)

slug := strings.Trim(nonAlphanumRe.ReplaceAllString(strings.ToLower(title), "-"), "-")
if len(slug) > 80 {
    slug = strings.Trim(slug[:80], "-")
}
if slug == "" {
    return "entry"
}

It has two bugs with non-Latin text:

• [^a-z0-9]+ treats every Devanagari, Arabic or Chinese character as junk. A Nepali title shrinks to an empty string and falls back to entry.
• slug[:80] cuts at a byte position. Devanagari characters take three bytes in UTF-8, so the cut can land mid-character and produce broken text.

A Unicode-aware version keeps letters, combining marks and digits from any script. It removes accents only from Latin letters, so café becomes cafe while Devanagari vowel signs stay. And it cuts by rune (Go's word for one Unicode character), not by byte:

var (
    nonSlugRe    = regexp.MustCompile(`[^\p{L}\p{M}\p{N}]+`)
    latinMarksRe = regexp.MustCompile(`(\p{Latin})\p{M}+`)
)

func webSlug(text string) string {
    slug := latinMarksRe.ReplaceAllString(norm.NFKD.String(text), "$1")
    slug = norm.NFC.String(strings.ToLower(slug))
    slug = strings.Trim(nonSlugRe.ReplaceAllString(slug, "-"), "-")
    if runes := []rune(slug); len(runes) > 80 {
        slug = strings.Trim(string(runes[:80]), "-")
    }
    return slug
}

NFKD splits é into e plus a separate accent mark, and the Latin-only pattern then drops the mark. NFC joins everything else back together. Stripping marks everywhere would be wrong. In Devanagari the "marks" are vowel signs, and removing them changes the word.

Where I ran into this

I built multilingual content for a multi-tenant headless CMS. The first version linked language versions by schema and slug. That design was mine, and it shipped.

Within a day the limits were obvious:

• editors couldn't give a translation its own web address without unlinking it
• entries without a slug couldn't be translated at all
• publishing to another environment failed with "has no web address, so it can't be matched"

On top of that, the slug generator turned an AI-translated Nepali title into entry.

So I redesigned it. The migration added translation_group_id and is_original and backfilled them with two windows. One ran per project, to pick the original. The other ran across all of a project's environments, to pick the group:

FIRST_VALUE(id) OVER (
    PARTITION BY project_id, schema_api_id, slug
    ORDER BY trashed, in_default DESC, created_at, id
) AS original_id,
FIRST_VALUE(id) OVER (
    PARTITION BY workspace_id, project_api_id, schema_api_id, slug
    ORDER BY project_created_at, project_id, trashed, in_default DESC, created_at, id
) AS group_id

The two partial unique indexes above enforce one version per language and one original per group. The delivery API's "one version per entry" query switched from a slug comparison to the row-value ranking. The slug generator became the Unicode-aware version, and its test checks that a Nepali title produces हाम्रो-बारेमा. There's also a fallback lookup, so a site that keeps the English URL and only switches the locale parameter still finds the Nepali version.

The change came with integration tests for everything the old design got wrong: translation groups, translation slugs, delivery linking languages by entry ID, environments matching languages by group, and promotion and import keeping languages together. I don't have performance numbers for it. The goal was correctness, not speed.

Checklist

• List every place rows find each other. If a join or lookup uses a field users can edit, swap it for a surrogate ID.
• Copy group IDs across environments instead of making new ones, so matching is an equality check.
• Backfill with window functions and a fixed ORDER BY that ends in a unique column.
• Put count rules ("one per language", "exactly one original") in partial unique indexes, not only in application code.
• To change "the one" in a group, clear first and then mark, inside one transaction.
• Use row-value comparison to rank duplicates, and end the tuple with a unique column.
• Build slugs with Unicode classes, strip accents only from Latin letters, and cut by rune.

Takeaways

• A slug, email or name is data. Identity is something nobody has a reason to edit.
• Linking by an editable field fails quietly: edits unlink, empty values can't link, and environments drift apart.
• Window functions turn a messy backfill into one predictable statement.
• Partial unique indexes can express rules like "exactly one original" that plain constraints can't.
• Text handling that only knows a-z is a bug for most of the world's languages.