Your database is the only thing that knows if a value is taken

A submission form in our editor started failing for one writer with an error that had no business reaching a user:

duplicate key value violates unique constraint "articles_slug_key"

The code that produced it looked careful. It checked whether the URL slug was already taken, and only wrote if it was free. That check was the bug — not because it was written wrong, but because it was asking a question the database was never going to answer honestly.

The check that looked right

Every article gets a slug derived from its title, and the column is unique. Two pieces called the same thing cannot both own /blog/the-same-title. So the submit path did the obvious thing: look for a clash, and if there is one, add a short suffix.

let slug = slugify(title) || 'untitled'

const { data: clash } = await supabase
  .from('articles')
  .select('id')
  .eq('slug', slug)
  .neq('id', articleId)
  .maybeSingle()

if (clash) slug = `${slug}-${articleId.slice(0, 6)}`

await supabase.from('articles')
  .update({ status: 'in_review', slug })
  .eq('id', articleId)

Read that in isolation and it is fine. It passed review, and it worked for months — for most people, most of the time.

Why it could never have worked

The table has row-level security. The read policy is the one you would write:

create policy articles_read on articles for select
  using (
    status = 'published'
    or author_id = auth.uid()
    or is_editor()
  );

An author can see published work, plus their own drafts. An editor sees everything. That is exactly the policy you want, and it is what breaks the check above.

When the writer ran that lookup, she was not querying the table. She was querying her view of the table. The article holding her slug belonged to a different author and had not been published, so the policy filtered it out before the query returned. The clash came back null. The slug looked free. The update then hit the unique index, which does not care about policies, and the constraint did what constraints do.

The lookup was not wrong about the data. It was answering a different question: not "is this slug taken?" but "is this slug taken by a row I am allowed to see?"

This is why the failure looked so arbitrary from the outside. Two authors could collide and only one of them would ever hit it. If the clashing article was your own, you saw it and the suffix was applied. If it belonged to someone else and was still a draft, you did not, and the write blew up. Same code, same input, different outcome depending on who was signed in.

It was never only about RLS

It would be comfortable to file this under "RLS gotcha" and move on. That would be the wrong lesson, because the pattern is broken even with no policies at all.

Between the SELECT and the UPDATE there is a window. Another request can reserve the same slug inside it. The check says free, the write says taken, and nothing about that is specific to row-level security — it is an ordinary time-of-check to time-of-use race. Policies did not create the bug. They just made it fire reliably enough to notice.

Anything that reads a value, decides based on what it read, and then writes has this shape. The database is the only participant with a consistent view at the moment of the write, so it is the only one that can answer.

Let the constraint answer

The fix is to stop asking and start attempting. Try the write; if the unique index rejects it, try the next candidate.

const base = slugify(title) || 'untitled'
const candidates = [
  base,
  `${base}-${articleId.slice(0, 6)}`,
  // The id is unique, so this one cannot collide.
  `${base}-${articleId}`,
]

for (const slug of candidates) {
  const { error } = await supabase
    .from('articles')
    .update({ status: 'in_review', slug })
    .eq('id', articleId)

  if (!error) return { ok: true, slug }
  // 23505 = unique_violation. Anything else is a real failure.
  if (error.code !== '23505') return { ok: false, error: error.message }
}

Three things matter here, and only one of them is the retry.

First, the error code is checked. Catching every error and retrying would turn a permissions failure or a dropped connection into three silent attempts and a misleading message at the end. 23505 is unique_violation specifically; everything else is returned untouched.

Second, the loop is bounded by a list, not by a counter. There is no while (true) and no randomness, so the outcome is deterministic and reads in order: the clean slug, then a short suffix, then the guaranteed one.

Third — and this is the part worth stealing — the final candidate cannot collide. It ends in the row id, which is already unique in that table. A retry loop whose last attempt can still fail has only made the bug rarer and harder to reproduce.

What about the friendly error?

The usual objection to this shape is user experience. A pre-flight check lets you say "that title is taken" before anyone commits to anything, and nobody wants a raw Postgres error in a toast.

Both can be true. Keep the check if it buys you a better message — but treat it as a hint, not a guarantee. The constraint is what enforces the invariant; the query is what makes the common case pleasant. Trouble starts when you let the hint stand in for the guarantee, because the hint is allowed to be wrong and the guarantee is not.

In our case the hint had no value left. Under RLS it could not see the rows that mattered, so it produced a worse experience than no check at all: a confusing failure instead of a handled one.

Where else this shows up

Once you have the shape in your head it turns up everywhere:

• Checking whether a username or email is free, then inserting. Same race, same unique index.

• Reading a counter, adding one, writing it back — instead of an atomic increment in the database.

• Verifying a balance covers a withdrawal in application code, then deducting it, with no constraint or transaction holding the invariant.

• Listing files to find a free name before creating one, on a filesystem several processes share.

Each of these has the same fix: express the rule where it can actually be enforced, and let the failure tell you when it was violated. A unique index, a check constraint, an atomic update. The application decides what to do about the rejection; it does not get to predict it.

Write the test that proves the diagnosis

A bug like this is easy to "fix" without understanding, because the retry makes the symptom disappear either way. The test worth writing is the one that fails for the original reason.

Ours seeds two authors. The first holds the contested slug on an unpublished draft. The second then submits an article with the same title. Before touching the UI, the test signs in as the second author and queries for that slug using her own token — and asserts the result is empty.

// The premise: the clashing row is genuinely invisible to her.
const visible = await asUser(writerB)
  .from('articles')
  .select('id')
  .eq('slug', 'the-contested-slug')

expect(visible).toHaveLength(0)   // RLS filtered it out
// ...only then submit, and expect a distinct slug rather than a 500.

That first assertion is the valuable one. Without it the test still passes after the fix, but it never demonstrates why the old code failed — and a future refactor could reintroduce a lookup with nothing to catch it. Encoding the premise, not just the outcome, is what stops the bug coming back wearing different clothes.

The rule

If a value has to be unique, put a unique constraint on it and handle the violation. Do not ask whether a write will succeed — ask the database to perform it, and give it a fallback for when it says no.

A read tells you what was true at the moment you read it, filtered by what you were allowed to see. A constraint tells you what is true at the moment you write. Only one of those is a guarantee, and it is not the one that is easier to reason about.