How a Postgres constraint rename silently broke production via PostgREST

A new constraint shipped on a Tuesday afternoon. The migration renamed user_roles_user_id_role_key to user_roles_user_id_key, dropping the role column from the uniqueness check because role had become a single-value field per user. Migration ran clean. Tests passed. Build pipeline was green.

On Thursday, an admin tried to invite a freelancer. The edge function returned a 400. The error message was clear enough: “there is no unique or exclusion constraint matching the ON CONFLICT specification.” The fix was one line. The diagnostic took an hour because everything else looked fine.

What PostgREST does with the onConflict string

The Supabase upsert docs show this shape:

await supabase
  .from('user_roles')
  .upsert(
    { user_id: '...', role: 'admin' },
    { onConflict: 'user_id,role' }
  );

onConflict looks like configuration. It is actually a runtime parameter. PostgREST takes the string, splits it on commas, and sends a Postgres query that includes ON CONFLICT (user_id, role). Postgres then looks up which UNIQUE constraint (or UNIQUE INDEX) covers those exact columns and uses it to decide whether to insert or update.

If a matching constraint exists, the upsert works. If it does not, Postgres rejects the query at runtime with a 400. The check happens inside Postgres, not inside PostgREST, not inside the Supabase JS SDK, not inside your application code. TypeScript will not catch it. ESLint will not catch it. Your local dev test suite will not catch it unless you happen to call that specific upsert with a non-existent column set in the test.

The fragility is structural. The contract between your code (which says “look up the constraint covering user_id and role”) and your schema (which holds the constraint definition) is enforced only at runtime, by Postgres, after the request has already traveled three hops.

How the migration broke it

The old constraint allowed multiple rows per user with different roles. The new business rule was one role per user. The migration was straightforward:

-- Drop the composite uniqueness
alter table public.user_roles
  drop constraint user_roles_user_id_role_key;

-- Add the new single-column uniqueness
alter table public.user_roles
  add constraint user_roles_user_id_key unique (user_id);

-- Defensive dedup: keep the most recent role per user
delete from public.user_roles a
  using public.user_roles b
  where a.user_id = b.user_id
    and a.created_at < b.created_at;

Three steps. They ran atomically, in one transaction, and they did exactly what they said they would do. The schema was now correct.

The application code, somewhere in an edge function, was still calling:

await supabase
  .from('user_roles')
  .upsert({ user_id, role }, { onConflict: 'user_id,role' });

user_id,role was the old constraint’s column list. The new constraint covers just user_id. Postgres looks for a UNIQUE constraint over (user_id, role), finds none, and rejects the query.

The call site looked fine in a code review. The migration looked fine in a code review. The bug only existed at the join between them, and neither reviewer was looking there.

Why TypeScript and the build pipeline miss this

1. onConflict is typed as string. The Supabase JS SDK does not validate the string against your schema because the SDK does not know your schema. The string is a free-form parameter forwarded to PostgREST.
2. Generated types do not constrain the upsert call site. Even if you run supabase gen types typescript, the generated file describes table shapes: column names, column types. It does not include a closed enum of valid onConflict strings per table.
3. CI does not compose migrations with application code. The migration “succeeds” if the SQL is valid. The application code “succeeds” if it type-checks and builds. Nothing says “given the new schema, would every upsert call still find its constraint?”

A few teams have built custom helpers that wrap .upsert with a typed parameter constrained to known column sets. They work. They are not standard. If you are starting fresh today, write one. If you are auditing an inherited codebase, you are dealing with the raw shape.

The five-minute grep that prevents the regression

Before any migration that adds, drops, or renames a unique constraint, run this:

# List every onConflict string in the codebase
grep -rn "onConflict" --include="*.ts" --include="*.tsx" \
  --include="*.js" --include="*.jsx" src/ supabase/functions/

For each match, find the constraint it expects. Cross-reference against the migration about to ship. If the constraint columns change, the call site needs to change with it.

For a Postgres-only audit of the live database, this query lists every unique constraint and the columns it covers:

select
  c.conname as constraint_name,
  t.relname as table_name,
  array_agg(a.attname order by array_position(c.conkey, a.attnum)) as columns
from pg_constraint c
join pg_class t on c.conrelid = t.oid
join pg_attribute a on a.attrelid = t.oid and a.attnum = any(c.conkey)
where c.contype = 'u'
  and t.relnamespace = 'public'::regnamespace
group by c.conname, t.relname
order by t.relname, c.conname;

Run that against staging after a migration, then diff against what the grep turned up. Any drift is a candidate for the bug.

Add the audit script to CI

A more durable version of the grep is a Node script that runs on any PR touching supabase/migrations/ or any file containing .upsert(. The script:

1. Spins up a temporary Postgres database (Docker or pg_tmp).
2. Applies all migrations in order, including the PR’s new ones.
3. Queries pg_constraint for every unique constraint.
4. Greps the codebase for onConflict: strings.
5. For each match, asserts the column list exists as a unique constraint in the temporary database.
6. Fails the build with a descriptive error if any do not.

About fifty lines, including the Postgres setup:

// scripts/check-onconflict.mjs (sketch)
import { execSync } from 'node:child_process';
import { Client } from 'pg';
// ... boot temp postgres, apply migrations, query pg_constraint ...
const validConstraints = await collectUniqueConstraints(client);
const onConflictCallSites = grepOnConflictCalls('src/', 'supabase/functions/');
for (const { file, line, columns } of onConflictCallSites) {
  if (!validConstraints.has(normalize(columns))) {
    console.error(`No unique constraint covers ${columns} (${file}:${line})`);
    process.exit(1);
  }
}

One afternoon to set up. This is the same class of pre-flight check that the PostgREST docs on upserts recommend but stop short of automating.

Constraint names are runtime contracts

The lesson generalizes beyond PostgREST. Any time you have a string parameter that gets resolved against a remote system at runtime, that string is a contract. The remote system (Postgres, a third-party API, a feature flag service) holds the canonical definition; your code holds a copy that can drift.

You have two practical options:

• Code-generate the strings from the canonical store on every build. This is the Drizzle/Prisma model. The generator catches drift at compile time.
• Validate the strings against the canonical store on every build. This is the audit-script model above. The generator isn’t generating, so CI does the checking instead.

There is a third option: centralize the strings in one file with explicit names per use site, so a rename surfaces as a code change rather than a buried value-string update. Any of the three breaks the bug class. The PostgREST default picks none of them.

If you are running a Supabase-backed product and you are about to do a schema cleanup or rename pass, let’s talk. The audit catches this class of bug along with a few others: RLS-policy chains that quietly broke, foreign keys pointing at deleted tables, generated types that drifted from production. About three hours of work, runs in CI from then on.

For the auth-side of the same fragility surface in Supabase, see two-layer identity models in Supabase, the first post in this series.