Put the shop in every unique constraint

Your Shopify app has one database and many shops in it. Every table therefore carries a shop column, and every query you write remembers to filter on it — because you wrote those queries while thinking about multiple shops.

Your @@unique constraints were written while thinking about one.

That gap is silent for as long as exactly one shop does the thing. The first shop to write a row claims the key globally, and every shop after it fails on create with a constraint error that names the columns but not the reason:

Invalid `prisma.record.create()` invocation:

Unique constraint failed on the fields: (`sourceStoreUrl`,`sourceProductId`)

This is a follow-along. By the end you have found every constraint in your schema that forgot the shop, migrated them, and have a test that fails if someone adds another one.

If your app only ever writes rows keyed by an id it generated itself, you don't need this.

Before you start

• A Shopify app with its own database, one row per shop, and shop (the .myshopify.com domain) already on your models — that's what the session hands you. → Shopify: build an admin app
• Prisma with a migration history — you need prisma migrate to make the index swap, not db push. → Prisma: Migrate

What you're building

1. An audit — the list of @@unique constraints that don't start with shop.
2. A migration that swaps each one for its shop-scoped form.
3. One test that fails when the next constraint forgets.

Step 1 — Find the constraints that forgot

Every @@unique in your schema is a claim about who may own a value. Read them and ask one question each: should two different shops be able to hold this same value?

grep -n '@@unique' prisma/schema.prisma

 80:  @@unique([sourceStoreUrl, sourceProductId])
 81:  @@unique([shop, canonicalKey])
390:  @@unique([sourceStoreUrl, sourceCollectionId])
391:  @@unique([shop, canonicalKey])

Two of these have the shop, two don't — sitting one line apart on the same models. That is the usual shape of this bug. Nobody decided the odd ones out should be global; whoever added them was thinking about a single store's data and the constraint quietly generalised to every store at once.

The tell that a constraint is wrong is not in the schema — it's the query that guards it. Find the read that runs before the write:

const existing = await db.record.findFirst({
  where: {
    shop: targetShop,               // ← shop-scoped
    sourceStoreUrl: sourceShop,
    sourceProductId: source.id,
  },
  select: { id: true },
});
if (existing) return null;          // already imported, skip

That check asks "does this shop already have it?". The constraint underneath it asks "does anybody?". When the two disagree, the guard passes and the write fails — the exact combination that gets you a create error on a row you already checked for.

Write down which one you believe. Here the code is right: the guard is shop-scoped, and the sibling constraint one line down is @@unique([shop, canonicalKey]). Two votes for per-shop, one for global, and the global one has no feature behind it.

Step 2 — Add the shop, in front

// Shop-scoped: one source record may be imported into many target stores, one row each.
// Without `shop` the second store to import a given source record collides with the first.
@@unique([shop, sourceStoreUrl, sourceProductId])
@@unique([shop, canonicalKey])

shop goes first. Prisma builds an index in the order you list the fields, and a compound index is usable for queries that match a prefix of it — so shop in front means this same index also serves your where: { shop } lookups. Trailing it does nothing for those.

Then check nothing referenced the old generated key name:

grep -rn 'sourceStoreUrl_sourceProductId' app/ trigger/

Prisma names the compound key after the fields, so any where: { sourceStoreUrl_sourceProductId: {...} } in an upsert or findUnique breaks with the constraint. If that grep is empty, the rename is free. If it isn't, each hit needs shop added to both the key name and its value.

Step 3 — Write the migration by hand

prisma migrate dev will generate this, but read what it writes before you run it anywhere real. The whole migration is two statements per constraint:

DROP INDEX "Record_sourceStoreUrl_sourceProductId_key";
CREATE UNIQUE INDEX "Record_shop_sourceStoreUrl_sourceProductId_key" ON "Record"("shop", "sourceStoreUrl", "sourceProductId");

The reason this one is safe to run without a data check: the new constraint is strictly weaker than the one it replaces. Any pair of rows that was legal under (a, b) is still legal under (shop, a, b). No existing row can violate it, so the migration cannot fail on data.

That is worth checking every time, because it is not always true. Going the other way — or adding a shop-scoped constraint where none existed — can fail, and you want to know before the deploy does:

-- Rows that would violate the new constraint. Must return zero before you migrate.
SELECT "shop", "sourceStoreUrl", "sourceProductId", count(*)
FROM "Record"
GROUP BY 1, 2, 3
HAVING count(*) > 1;

Step 4 — Deploy the database before the code

If anything else runs your Prisma client — a background worker, a queue consumer, a cron container — it bundles its own copy of the generated client, with the old constraint compiled into it. Order matters:

1. prisma migrate deploy against the database.
2. prisma generate and redeploy every process that holds a client.

Backwards, the new worker writes against an index that no longer exists.

Step 5 — The test that stops the next one

The audit you just did by hand is a grep. Make it a test, or you will do it by hand again in four months:

import { readFileSync } from 'node:fs';

// Every uniqueness rule in this app is per-shop. A constraint without `shop` in it
// lets the first store to write a value own it for every other store, forever.
it('every @@unique starts with shop', () => {
  const constraints = readFileSync('prisma/schema.prisma', 'utf8')
    .split('\n')
    .filter((line) => line.includes('@@unique'));

  expect(constraints.length).toBeGreaterThan(0);   // guards against a moved file
  for (const constraint of constraints) {
    expect(constraint).toMatch(/@@unique\(\[\s*shop\b/);
  }
});

If your schema has genuinely global uniques — a plan code, a shared lookup table — list them by name in the test rather than dropping it. An allowlist you have to edit is the point: adding a global constraint becomes a decision somebody makes on purpose.

Where you can see it worked

Take the source record that failed and import it into a second shop.

Before, on the second shop:

Unique constraint failed on the fields: (`sourceStoreUrl`,`sourceProductId`)

After, both shops hold their own row for it:

SELECT "shop", "sourceProductId" FROM "Record" WHERE "sourceProductId" = '<the id>';

 shop-one.myshopify.com | 8241…
 shop-two.myshopify.com | 8241…

Two rows, two shops, same source. That second row is what the old constraint made impossible.

Gotchas

• The guard and the constraint disagree silently. A shop-scoped findFirst in front of a globally-unique create never warns you. It reads as correct code right up until the second shop installs.
• shop goes first in the constraint. Prisma indexes are prefix-usable; leading with shop means the same index serves your ordinary per-shop queries too.
• The compound key name changes with the fields. sourceStoreUrl_sourceProductId becomes shop_sourceStoreUrl_sourceProductId. Grep for the old name before migrating — upsert and findUnique call it by name.
• Widening a unique is safe; narrowing is not. Adding a column can't invalidate existing rows. Removing one can, and the migration fails mid-deploy. Run the duplicate-count query first whenever you aren't strictly widening.
• Every process with its own Prisma client needs a redeploy. Workers bundle a generated client. Migrate the database first, then redeploy them.
• Don't fix this by scoping the code instead. Adding more shop filters to queries leaves the constraint global — the write still collides. The constraint is the thing that's wrong.
• A uniqueness bug looks like a transient at first. It arrives inside a batch job, on some rows and not others, mixed in with real timeouts. Constraint errors name their columns exactly — that's the line that tells you it's schema, not load.

Docs

• Prisma schema reference (@@unique) → https://www.prisma.io/docs/orm/reference/prisma-schema-reference
• Prisma: customizing migrations → https://www.prisma.io/docs/orm/prisma-migrate/workflows/customizing-migrations
• @shopify/shopify-app-remix → https://shopify.dev/docs/api/shopify-app-remix

Implementation prompt

I have a Shopify admin app (Remix + TypeScript) with its own PostgreSQL database accessed
through Prisma 6, a migration history in prisma/migrations, and a `shop` column (the
myshopify.com domain) on my multi-tenant models. Check these prerequisites first and set up
anything missing before you start.

Do three things:

A. Audit every `@@unique([...])` in prisma/schema.prisma. For each one that does not begin
   with `shop`, find the query that guards the corresponding write (usually a findFirst or
   findUnique run just before a create/upsert). Report a table: the constraint, whether its
   guard is shop-scoped, and your verdict on whether the constraint or the code is wrong.
   Do not change anything yet — show me the table and wait.

B. For each constraint I confirm, edit the schema so `shop` is the FIRST field in the
   constraint, and hand-write the SQL migration that drops the old unique index and creates
   the shop-scoped one. State explicitly, per constraint, whether the new form is strictly
   weaker than the old one; if it is not, give me a SQL query that counts rows which would
   violate it, and stop until it returns zero.

C. Write ONE test that reads prisma/schema.prisma and asserts every `@@unique` starts with
   `shop`, with a named allowlist for constraints that are genuinely global.

Do not invent my data model, my domain logic, or my migration tooling config.

Constraints — these are the traps:
- `shop` must be the first field in the constraint, not appended. Prisma indexes are
  prefix-usable, so leading with shop lets the same index serve per-shop queries.
- Prisma's generated compound key name is derived from the field list, so renaming a
  constraint breaks any `where: { a_b: {...} }` in upsert/findUnique. Grep for every old key
  name across the codebase and update both the key name and its value.
- Adding a field to a unique constraint is strictly weakening: no existing row can violate it,
  so the migration cannot fail on data. Removing one can. Never assume which case you're in.
- Use `prisma migrate`, never `db push` — this must land as a reviewable migration file.
- Migrate the database BEFORE regenerating and redeploying anything that bundles its own
  Prisma client (background workers, queue consumers, cron containers). Backwards, the new
  process writes against an index that no longer exists.
- Do not "fix" this by adding more shop filters to queries. The queries are already correct;
  the constraint is what's wrong.

Acceptance check: on the pre-fix schema, writing the same source value for two different
shops must fail with "Unique constraint failed on the fields". After the migration, both
writes succeed and the table holds one row per shop. Run the test and show me it passes,
then break the schema by adding a `@@unique` without shop and show me it fails.