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. The 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 in the function. The diagnostic took an hour because everything else looked fine.

This is a class of bug worth its own post because nothing in your usual toolchain catches it.

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.

The migration that broke it

The migration was straightforward. The old constraint allowed multiple rows per user with different roles, and the new business rule is one role per user. The migration looked like this:

-- 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 in order, 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) and finds none, so it rejects the query.

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

Why TypeScript and the build pipeline cannot catch it

A natural first reaction is “this should be a compile error.” It is not, and the reasons are worth understanding:

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 that gets forwarded to PostgREST.
2. Generated types do not constrain the upsert call site. Even if you run supabase gen types typescript to produce types from your live Postgres schema, the generated file describes table shapes (column names, column types). It does not include a closed enum of valid onConflict strings for each table.
3. The build pipeline does not run the migration and then re-run all upserts. Migrations and application code travel through CI together but are never composed. The migration “succeeds” if the SQL is valid; the application code “succeeds” if it type-checks and builds. Nothing in CI 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 are good. They are also 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 again against the file produced by the grep above, and diff them. Any drift is a candidate for the bug.

A pre-migration audit script you can put in CI

A more durable version of the grep is a Node script that runs in CI on any PR that touches 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 onConflict 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. The shape:

// 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);
  }
}

The setup cost is one afternoon. The savings is permanent. This is the same class of pre-flight discipline that the PostgREST docs on upserts recommend but stop short of automating.

What “constraint names are runtime contracts” means in practice

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) is the canonical store; your code holds a copy that can drift.

The discipline is to either:

• Code-generate the strings from the canonical store on every build (the Drizzle/Prisma model).
• Validate the strings against the canonical store on every build (the audit-script model above).
• Centralize the strings in one file with explicit names per use site, so a rename surfaces as a code change rather than a value-string update (the constants-module model).

The PostgREST pattern is fragile because it picks none of these. You can layer any one of them on top, and the bug class disappears.

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 that point at deleted tables, generated types that drifted from production). About three hours of work, runs in CI from then on.

For a related class of “looks fine, breaks under load” bug specific to AI-assisted code, see the silent failures I keep finding in AI-built data pipelines. And for the auth-side of the same fragility surface in Supabase, see two-layer identity models in Supabase, which is the first post in this series.