prisma: Cross schema reference with foreign key not working in 4.7.0+

Note: it’s going in tomorrow’s, 4.8.0 release

I’m having this same problem after doing a db pull from the default database for this Next.JS + Supabase starter project.

After doing some name mapping to get rid of the model user vs model user clash, I’m getting this complaint when I run prisma migrate dev:

Illegal cross schema reference from `public.customers` to `auth.users` in constraint `customers_id_fkey`. Foreign keys between database schemas are not supported in Prisma.

It seems clear to me that my customers foreign key is pointing to the public schema’s user model and not the auth schema’s AuthUser (mapped to user) model, so this seems like a bug.

I’m very new to Prisma so definitely possible I’m just screwing something up, but I figured this might be helpful for you to know since it should be easily reproducible using that starter project.

Here is my schema.prisma file (Profile is the only custom table I’ve created that wasn’t already in the starter project db.)

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["multiSchema"]
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  schemas  = ["auth", "public"]
}

model audit_log_entries {
  instance_id String?   @db.Uuid
  id          String    @id @db.Uuid
  payload     Json?     @db.Json
  created_at  DateTime? @db.Timestamptz(6)
  ip_address  String    @default("") @db.VarChar(64)

  @@index([instance_id], map: "audit_logs_instance_id_idx")
  @@schema("auth")
}

model identities {
  id              String
  user_id         String    @db.Uuid
  identity_data   Json
  provider        String
  last_sign_in_at DateTime? @db.Timestamptz(6)
  created_at      DateTime? @db.Timestamptz(6)
  updated_at      DateTime? @db.Timestamptz(6)
  users           AuthUser  @relation(fields: [user_id], references: [id], onDelete: Cascade, onUpdate: NoAction)

  @@id([provider, id])
  @@index([user_id])
  @@schema("auth")
}

model instances {
  id              String    @id @db.Uuid
  uuid            String?   @db.Uuid
  raw_base_config String?
  created_at      DateTime? @db.Timestamptz(6)
  updated_at      DateTime? @db.Timestamptz(6)

  @@schema("auth")
}

model mfa_amr_claims {
  session_id            String   @db.Uuid
  created_at            DateTime @db.Timestamptz(6)
  updated_at            DateTime @db.Timestamptz(6)
  authentication_method String
  id                    String   @id(map: "amr_id_pk") @db.Uuid
  sessions              sessions @relation(fields: [session_id], references: [id], onDelete: Cascade, onUpdate: NoAction)

  @@unique([session_id, authentication_method], map: "mfa_amr_claims_session_id_authentication_method_pkey")
  @@schema("auth")
}

model mfa_challenges {
  id          String      @id @db.Uuid
  factor_id   String      @db.Uuid
  created_at  DateTime    @db.Timestamptz(6)
  verified_at DateTime?   @db.Timestamptz(6)
  ip_address  String      @db.Inet
  mfa_factors mfa_factors @relation(fields: [factor_id], references: [id], onDelete: Cascade, onUpdate: NoAction, map: "mfa_challenges_auth_factor_id_fkey")

  @@schema("auth")
}

model mfa_factors {
  id             String           @id @db.Uuid
  user_id        String           @db.Uuid
  friendly_name  String?
  factor_type    factor_type
  status         factor_status
  created_at     DateTime         @db.Timestamptz(6)
  updated_at     DateTime         @db.Timestamptz(6)
  secret         String?
  mfa_challenges mfa_challenges[]
  users          AuthUser         @relation(fields: [user_id], references: [id], onDelete: Cascade, onUpdate: NoAction)

  @@index([user_id, created_at], map: "factor_id_created_at_idx")
  @@schema("auth")
}

model refresh_tokens {
  instance_id String?   @db.Uuid
  id          BigInt    @id @default(autoincrement())
  token       String?   @unique(map: "refresh_tokens_token_unique") @db.VarChar(255)
  user_id     String?   @db.VarChar(255)
  revoked     Boolean?
  created_at  DateTime? @db.Timestamptz(6)
  updated_at  DateTime? @db.Timestamptz(6)
  parent      String?   @db.VarChar(255)
  session_id  String?   @db.Uuid
  sessions    sessions? @relation(fields: [session_id], references: [id], onDelete: Cascade, onUpdate: NoAction)

  @@index([session_id], map: "refresh_token_session_id")
  @@index([instance_id])
  @@index([instance_id, user_id])
  @@index([parent])
  @@index([session_id, revoked])
  @@index([token])
  @@schema("auth")
}

model saml_providers {
  id                String        @id @db.Uuid
  sso_provider_id   String        @db.Uuid
  entity_id         String        @unique
  metadata_xml      String
  metadata_url      String?
  attribute_mapping Json?
  created_at        DateTime?     @db.Timestamptz(6)
  updated_at        DateTime?     @db.Timestamptz(6)
  sso_providers     sso_providers @relation(fields: [sso_provider_id], references: [id], onDelete: Cascade, onUpdate: NoAction)

  @@index([sso_provider_id])
  @@schema("auth")
}

model saml_relay_states {
  id              String        @id @db.Uuid
  sso_provider_id String        @db.Uuid
  request_id      String
  for_email       String?
  redirect_to     String?
  from_ip_address String?       @db.Inet
  created_at      DateTime?     @db.Timestamptz(6)
  updated_at      DateTime?     @db.Timestamptz(6)
  sso_providers   sso_providers @relation(fields: [sso_provider_id], references: [id], onDelete: Cascade, onUpdate: NoAction)

  @@index([for_email])
  @@index([sso_provider_id])
  @@schema("auth")
}

model schema_migrations {
  version String @id @db.VarChar(255)

  @@schema("auth")
}

model sessions {
  id             String           @id @db.Uuid
  user_id        String           @db.Uuid
  created_at     DateTime?        @db.Timestamptz(6)
  updated_at     DateTime?        @db.Timestamptz(6)
  factor_id      String?          @db.Uuid
  aal            aal_level?
  not_after      DateTime?        @db.Timestamptz(6)
  mfa_amr_claims mfa_amr_claims[]
  refresh_tokens refresh_tokens[]
  users          AuthUser         @relation(fields: [user_id], references: [id], onDelete: Cascade, onUpdate: NoAction)
  sso_sessions   sso_sessions[]

  @@index([user_id])
  @@index([user_id, created_at], map: "user_id_created_at_idx")
  @@schema("auth")
}

model sso_domains {
  id              String        @id @db.Uuid
  sso_provider_id String        @db.Uuid
  domain          String
  created_at      DateTime?     @db.Timestamptz(6)
  updated_at      DateTime?     @db.Timestamptz(6)
  sso_providers   sso_providers @relation(fields: [sso_provider_id], references: [id], onDelete: Cascade, onUpdate: NoAction)

  @@index([sso_provider_id])
  @@schema("auth")
}

model sso_providers {
  id                String              @id @db.Uuid
  resource_id       String?
  created_at        DateTime?           @db.Timestamptz(6)
  updated_at        DateTime?           @db.Timestamptz(6)
  saml_providers    saml_providers[]
  saml_relay_states saml_relay_states[]
  sso_domains       sso_domains[]
  sso_sessions      sso_sessions[]

  @@schema("auth")
}

model sso_sessions {
  id              String         @id @db.Uuid
  session_id      String         @db.Uuid
  sso_provider_id String?        @db.Uuid
  not_before      DateTime?      @db.Timestamptz(6)
  not_after       DateTime?      @db.Timestamptz(6)
  idp_initiated   Boolean?       @default(false)
  created_at      DateTime?      @db.Timestamptz(6)
  updated_at      DateTime?      @db.Timestamptz(6)
  sessions        sessions       @relation(fields: [session_id], references: [id], onDelete: Cascade, onUpdate: NoAction)
  sso_providers   sso_providers? @relation(fields: [sso_provider_id], references: [id], onDelete: Cascade, onUpdate: NoAction)

  @@index([session_id])
  @@index([sso_provider_id])
  @@schema("auth")
}

model AuthUser {
  instance_id                 String?       @db.Uuid
  id                          String        @id @db.Uuid
  aud                         String?       @db.VarChar(255)
  role                        String?       @db.VarChar(255)
  email                       String?       @unique @db.VarChar(255)
  encrypted_password          String?       @db.VarChar(255)
  email_confirmed_at          DateTime?     @db.Timestamptz(6)
  invited_at                  DateTime?     @db.Timestamptz(6)
  confirmation_token          String?       @db.VarChar(255)
  confirmation_sent_at        DateTime?     @db.Timestamptz(6)
  recovery_token              String?       @db.VarChar(255)
  recovery_sent_at            DateTime?     @db.Timestamptz(6)
  email_change_token_new      String?       @db.VarChar(255)
  email_change                String?       @db.VarChar(255)
  email_change_sent_at        DateTime?     @db.Timestamptz(6)
  last_sign_in_at             DateTime?     @db.Timestamptz(6)
  raw_app_meta_data           Json?
  raw_user_meta_data          Json?
  is_super_admin              Boolean?
  created_at                  DateTime?     @db.Timestamptz(6)
  updated_at                  DateTime?     @db.Timestamptz(6)
  phone                       String?       @unique @db.VarChar(15)
  phone_confirmed_at          DateTime?     @db.Timestamptz(6)
  phone_change                String?       @default("") @db.VarChar(15)
  phone_change_token          String?       @default("") @db.VarChar(255)
  phone_change_sent_at        DateTime?     @db.Timestamptz(6)
  confirmed_at                DateTime?     @default(dbgenerated("LEAST(email_confirmed_at, phone_confirmed_at)")) @db.Timestamptz(6)
  email_change_token_current  String?       @default("") @db.VarChar(255)
  email_change_confirm_status Int?          @default(0) @db.SmallInt
  banned_until                DateTime?     @db.Timestamptz(6)
  reauthentication_token      String?       @default("") @db.VarChar(255)
  reauthentication_sent_at    DateTime?     @db.Timestamptz(6)
  identities                  identities[]
  mfa_factors                 mfa_factors[]
  sessions                    sessions[]

  users users?

  @@index([instance_id])
  @@schema("auth")
}

model customers {
  id                 String  @id @db.Uuid
  stripe_customer_id String?
  users              users   @relation(fields: [id], references: [id], onDelete: NoAction, onUpdate: NoAction)

  @@schema("public")
}

model prices {
  id                String                 @id
  product_id        String?
  active            Boolean?
  description       String?
  unit_amount       BigInt?
  currency          String?
  type              pricing_type?
  interval          pricing_plan_interval?
  interval_count    Int?
  trial_period_days Int?
  metadata          Json?
  products          products?              @relation(fields: [product_id], references: [id], onDelete: NoAction, onUpdate: NoAction)
  subscriptions     subscriptions[]

  @@schema("public")
}

model products {
  id          String   @id
  active      Boolean?
  name        String?
  description String?
  image       String?
  metadata    Json?
  prices      prices[]

  @@schema("public")
}

model Profile {
  id          BigInt    @id(map: "profile_pkey") @default(autoincrement())
  createdAt   DateTime? @default(now()) @map("created_at") @db.Timestamptz(6)
  displayName String?   @map("display_name")
  bio         String?

  @@map("profiles")
  @@schema("public")
}

model subscriptions {
  id                   String               @id
  user_id              String               @db.Uuid
  status               subscription_status?
  metadata             Json?
  price_id             String?
  quantity             Int?
  cancel_at_period_end Boolean?
  created              DateTime             @default(dbgenerated("timezone('utc'::text, now())")) @db.Timestamptz(6)
  current_period_start DateTime             @default(dbgenerated("timezone('utc'::text, now())")) @db.Timestamptz(6)
  current_period_end   DateTime             @default(dbgenerated("timezone('utc'::text, now())")) @db.Timestamptz(6)
  ended_at             DateTime?            @default(dbgenerated("timezone('utc'::text, now())")) @db.Timestamptz(6)
  cancel_at            DateTime?            @default(dbgenerated("timezone('utc'::text, now())")) @db.Timestamptz(6)
  canceled_at          DateTime?            @default(dbgenerated("timezone('utc'::text, now())")) @db.Timestamptz(6)
  trial_start          DateTime?            @default(dbgenerated("timezone('utc'::text, now())")) @db.Timestamptz(6)
  trial_end            DateTime?            @default(dbgenerated("timezone('utc'::text, now())")) @db.Timestamptz(6)
  prices               prices?              @relation(fields: [price_id], references: [id], onDelete: NoAction, onUpdate: NoAction)
  users                users                @relation(fields: [user_id], references: [id], onDelete: NoAction, onUpdate: NoAction)

  @@schema("public")
}

model users {
  id             String          @id @db.Uuid
  fullName       String?         @map("full_name")
  avatarUrl      String?         @map("avatar_url")
  billingAddress Json?           @map("billing_address")
  paymentMethod  Json?           @map("payment_method")
  users          AuthUser        @relation(fields: [id], references: [id], onDelete: NoAction, onUpdate: NoAction)
  subscriptions  subscriptions[]
  customers      customers?

  @@schema("public")
}

enum aal_level {
  aal1
  aal2
  aal3

  @@schema("auth")
}

enum factor_status {
  unverified
  verified

  @@schema("auth")
}

enum factor_type {
  totp
  webauthn

  @@schema("auth")
}

enum pricing_plan_interval {
  day
  week
  month
  year

  @@schema("public")
}

enum pricing_type {
  one_time
  recurring

  @@schema("public")
}

enum subscription_status {
  trialing
  active
  canceled
  incomplete
  incomplete_expired
  past_due
  unpaid

  @@schema("public")
}

How did you create your schema - hand written or using Introspection? Can you maybe share the SQL of your table(s)?

Hi! Sorry for late reply but in my case I used both approaches. I first wrote the “User” - model into the schema and ran prisma migrate. After this I added the “auth” schema into the prisma schema file, so that it recognizes the supabase auth tables. Then I executed prisma db pull command which imported the supabase auth tables into my prisma schema file. Finally I added the relation from public.User table to auth.users table. This is when the error occurred as I tried to migrate the changes.

I can also verify both @firzanarmani and @mjlatty cases, as they are basically same setups as I have.

I’d like to offer another reproduction of this bug on Supabase. (Hope it somehow helps)

How to reproduce

1. Create a new Supabase database
2. Run the “User Management Starter” SQL snippet on the new database

-- Create a table for public profiles
create table profiles (
  id uuid references auth.users not null primary key,
  updated_at timestamp with time zone,
  username text unique,
  full_name text,
  avatar_url text,
  website text,

  constraint username_length check (char_length(username) >= 3)
);
-- Set up Row Level Security (RLS)
-- See https://supabase.com/docs/guides/auth/row-level-security for more details.
alter table profiles
  enable row level security;

create policy "Public profiles are viewable by everyone." on profiles
  for select using (true);

create policy "Users can insert their own profile." on profiles
  for insert with check (auth.uid() = id);

create policy "Users can update own profile." on profiles
  for update using (auth.uid() = id);

-- This trigger automatically creates a profile entry when a new user signs up via Supabase Auth.
-- See https://supabase.com/docs/guides/auth/managing-user-data#using-triggers for more details.
create function public.handle_new_user()
returns trigger as $$
begin
  insert into public.profiles (id, full_name, avatar_url)
  values (new.id, new.raw_user_meta_data->>'full_name', new.raw_user_meta_data->>'avatar_url');
  return new;
end;
$$ language plpgsql security definer;
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();

-- Set up Storage!
insert into storage.buckets (id, name)
  values ('avatars', 'avatars');

-- Set up access controls for storage.
-- See https://supabase.com/docs/guides/storage#policy-examples for more details.
create policy "Avatar images are publicly accessible." on storage.objects
  for select using (bucket_id = 'avatars');

create policy "Anyone can upload an avatar." on storage.objects
  for insert with check (bucket_id = 'avatars');

1. Set the schema.prisma to the following

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["multiSchema"]
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  schemas  = ["auth", "public"]
}

1. Run prisma db pull to introspect

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["multiSchema"]
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  schemas  = ["auth", "public"]
}

model audit_log_entries {
  instance_id String?   @db.Uuid
  id          String    @id @db.Uuid
  payload     Json?     @db.Json
  created_at  DateTime? @db.Timestamptz(6)
  ip_address  String    @default("") @db.VarChar(64)

  @@index([instance_id], map: "audit_logs_instance_id_idx")
  @@schema("auth")
}

model identities {
  id              String
  user_id         String    @db.Uuid
  identity_data   Json
  provider        String
  last_sign_in_at DateTime? @db.Timestamptz(6)
  created_at      DateTime? @db.Timestamptz(6)
  updated_at      DateTime? @db.Timestamptz(6)
  users           users     @relation(fields: [user_id], references: [id], onDelete: Cascade, onUpdate: NoAction)

  @@id([provider, id])
  @@index([user_id])
  @@schema("auth")
}

model instances {
  id              String    @id @db.Uuid
  uuid            String?   @db.Uuid
  raw_base_config String?
  created_at      DateTime? @db.Timestamptz(6)
  updated_at      DateTime? @db.Timestamptz(6)

  @@schema("auth")
}

model mfa_amr_claims {
  session_id            String   @db.Uuid
  created_at            DateTime @db.Timestamptz(6)
  updated_at            DateTime @db.Timestamptz(6)
  authentication_method String
  id                    String   @id(map: "amr_id_pk") @db.Uuid
  sessions              sessions @relation(fields: [session_id], references: [id], onDelete: Cascade, onUpdate: NoAction)

  @@unique([session_id, authentication_method], map: "mfa_amr_claims_session_id_authentication_method_pkey")
  @@schema("auth")
}

model mfa_challenges {
  id          String      @id @db.Uuid
  factor_id   String      @db.Uuid
  created_at  DateTime    @db.Timestamptz(6)
  verified_at DateTime?   @db.Timestamptz(6)
  ip_address  String      @db.Inet
  mfa_factors mfa_factors @relation(fields: [factor_id], references: [id], onDelete: Cascade, onUpdate: NoAction, map: "mfa_challenges_auth_factor_id_fkey")

  @@schema("auth")
}

model mfa_factors {
  id             String           @id @db.Uuid
  user_id        String           @db.Uuid
  friendly_name  String?
  factor_type    factor_type
  status         factor_status
  created_at     DateTime         @db.Timestamptz(6)
  updated_at     DateTime         @db.Timestamptz(6)
  secret         String?
  mfa_challenges mfa_challenges[]
  users          users            @relation(fields: [user_id], references: [id], onDelete: Cascade, onUpdate: NoAction)

  @@index([user_id, created_at], map: "factor_id_created_at_idx")
  @@schema("auth")
}

model refresh_tokens {
  instance_id String?   @db.Uuid
  id          BigInt    @id @default(autoincrement())
  token       String?   @unique(map: "refresh_tokens_token_unique") @db.VarChar(255)
  user_id     String?   @db.VarChar(255)
  revoked     Boolean?
  created_at  DateTime? @db.Timestamptz(6)
  updated_at  DateTime? @db.Timestamptz(6)
  parent      String?   @db.VarChar(255)
  session_id  String?   @db.Uuid
  sessions    sessions? @relation(fields: [session_id], references: [id], onDelete: Cascade, onUpdate: NoAction)

  @@index([session_id], map: "refresh_token_session_id")
  @@index([instance_id])
  @@index([instance_id, user_id])
  @@index([parent])
  @@index([session_id, revoked])
  @@index([token])
  @@schema("auth")
}

model saml_providers {
  id                String        @id @db.Uuid
  sso_provider_id   String        @db.Uuid
  entity_id         String        @unique
  metadata_xml      String
  metadata_url      String?
  attribute_mapping Json?
  created_at        DateTime?     @db.Timestamptz(6)
  updated_at        DateTime?     @db.Timestamptz(6)
  sso_providers     sso_providers @relation(fields: [sso_provider_id], references: [id], onDelete: Cascade, onUpdate: NoAction)

  @@index([sso_provider_id])
  @@schema("auth")
}

model saml_relay_states {
  id              String        @id @db.Uuid
  sso_provider_id String        @db.Uuid
  request_id      String
  for_email       String?
  redirect_to     String?
  from_ip_address String?       @db.Inet
  created_at      DateTime?     @db.Timestamptz(6)
  updated_at      DateTime?     @db.Timestamptz(6)
  sso_providers   sso_providers @relation(fields: [sso_provider_id], references: [id], onDelete: Cascade, onUpdate: NoAction)

  @@index([for_email])
  @@index([sso_provider_id])
  @@schema("auth")
}

model schema_migrations {
  version String @id @db.VarChar(255)

  @@schema("auth")
}

model sessions {
  id             String           @id @db.Uuid
  user_id        String           @db.Uuid
  created_at     DateTime?        @db.Timestamptz(6)
  updated_at     DateTime?        @db.Timestamptz(6)
  factor_id      String?          @db.Uuid
  aal            aal_level?
  not_after      DateTime?        @db.Timestamptz(6)
  mfa_amr_claims mfa_amr_claims[]
  refresh_tokens refresh_tokens[]
  users          users            @relation(fields: [user_id], references: [id], onDelete: Cascade, onUpdate: NoAction)
  sso_sessions   sso_sessions[]

  @@index([user_id])
  @@index([user_id, created_at], map: "user_id_created_at_idx")
  @@schema("auth")
}

model sso_domains {
  id              String        @id @db.Uuid
  sso_provider_id String        @db.Uuid
  domain          String
  created_at      DateTime?     @db.Timestamptz(6)
  updated_at      DateTime?     @db.Timestamptz(6)
  sso_providers   sso_providers @relation(fields: [sso_provider_id], references: [id], onDelete: Cascade, onUpdate: NoAction)

  @@index([sso_provider_id])
  @@schema("auth")
}

model sso_providers {
  id                String              @id @db.Uuid
  resource_id       String?
  created_at        DateTime?           @db.Timestamptz(6)
  updated_at        DateTime?           @db.Timestamptz(6)
  saml_providers    saml_providers[]
  saml_relay_states saml_relay_states[]
  sso_domains       sso_domains[]
  sso_sessions      sso_sessions[]

  @@schema("auth")
}

model sso_sessions {
  id              String         @id @db.Uuid
  session_id      String         @db.Uuid
  sso_provider_id String?        @db.Uuid
  not_before      DateTime?      @db.Timestamptz(6)
  not_after       DateTime?      @db.Timestamptz(6)
  idp_initiated   Boolean?       @default(false)
  created_at      DateTime?      @db.Timestamptz(6)
  updated_at      DateTime?      @db.Timestamptz(6)
  sessions        sessions       @relation(fields: [session_id], references: [id], onDelete: Cascade, onUpdate: NoAction)
  sso_providers   sso_providers? @relation(fields: [sso_provider_id], references: [id], onDelete: Cascade, onUpdate: NoAction)

  @@index([session_id])
  @@index([sso_provider_id])
  @@schema("auth")
}

model users {
  instance_id                 String?       @db.Uuid
  id                          String        @id @db.Uuid
  aud                         String?       @db.VarChar(255)
  role                        String?       @db.VarChar(255)
  email                       String?       @unique @db.VarChar(255)
  encrypted_password          String?       @db.VarChar(255)
  email_confirmed_at          DateTime?     @db.Timestamptz(6)
  invited_at                  DateTime?     @db.Timestamptz(6)
  confirmation_token          String?       @db.VarChar(255)
  confirmation_sent_at        DateTime?     @db.Timestamptz(6)
  recovery_token              String?       @db.VarChar(255)
  recovery_sent_at            DateTime?     @db.Timestamptz(6)
  email_change_token_new      String?       @db.VarChar(255)
  email_change                String?       @db.VarChar(255)
  email_change_sent_at        DateTime?     @db.Timestamptz(6)
  last_sign_in_at             DateTime?     @db.Timestamptz(6)
  raw_app_meta_data           Json?
  raw_user_meta_data          Json?
  is_super_admin              Boolean?
  created_at                  DateTime?     @db.Timestamptz(6)
  updated_at                  DateTime?     @db.Timestamptz(6)
  phone                       String?       @unique @db.VarChar(15)
  phone_confirmed_at          DateTime?     @db.Timestamptz(6)
  phone_change                String?       @default("") @db.VarChar(15)
  phone_change_token          String?       @default("") @db.VarChar(255)
  phone_change_sent_at        DateTime?     @db.Timestamptz(6)
  confirmed_at                DateTime?     @default(dbgenerated("LEAST(email_confirmed_at, phone_confirmed_at)")) @db.Timestamptz(6)
  email_change_token_current  String?       @default("") @db.VarChar(255)
  email_change_confirm_status Int?          @default(0) @db.SmallInt
  banned_until                DateTime?     @db.Timestamptz(6)
  reauthentication_token      String?       @default("") @db.VarChar(255)
  reauthentication_sent_at    DateTime?     @db.Timestamptz(6)
  identities                  identities[]
  mfa_factors                 mfa_factors[]
  sessions                    sessions[]
  profiles                    profiles?

  @@index([instance_id])
  @@schema("auth")
}

model profiles {
  id         String    @id @db.Uuid
  updated_at DateTime? @db.Timestamptz(6)
  username   String?   @unique
  full_name  String?
  avatar_url String?
  website    String?
  users      users     @relation(fields: [id], references: [id], onDelete: NoAction, onUpdate: NoAction)

  @@schema("public")
}

enum aal_level {
  aal1
  aal2
  aal3

  @@schema("auth")
}

enum factor_status {
  unverified
  verified

  @@schema("auth")
}

enum factor_type {
  totp
  webauthn

  @@schema("auth")
}

1. Run prisma migrate dev --name init
2. Error encountered

Error: P4002

The schema of the introspected database was inconsistent: Illegal cross schema reference from `public.profiles` to `auth.users` in constraint `profiles_id_fkey`. Foreign keys between database schemas are not supported in Prisma. Please follow the GitHub ticket: https://github.com/prisma/prisma/issues/1175

Prisma Version

prisma                  : 4.7.1
@prisma/client          : 4.7.1
Current platform        : darwin
Query Engine (Node-API) : libquery-engine 272861e07ab64f234d3ffc4094e32bd61775599c (at node_modules/@prisma/engines/libquery_engine-darwin.dylib.node)
Migration Engine        : migration-engine-cli 272861e07ab64f234d3ffc4094e32bd61775599c (at node_modules/@prisma/engines/migration-engine-darwin)
Introspection Engine    : introspection-core 272861e07ab64f234d3ffc4094e32bd61775599c (at node_modules/@prisma/engines/introspection-engine-darwin)
Format Binary           : prisma-fmt 272861e07ab64f234d3ffc4094e32bd61775599c (at node_modules/@prisma/engines/prisma-fmt-darwin)
Format Wasm             : @prisma/prisma-fmt-wasm 4.7.1-1.272861e07ab64f234d3ffc4094e32bd61775599c
Default Engines Hash    : 272861e07ab64f234d3ffc4094e32bd61775599c
Studio                  : 0.477.0
Preview Features        : multiSchema

Yes, I think that matches what Julius found out later as well. We’ll have a fix for this hopefully soon to test.

Sorry @janpio I got mixed up between dev and deploy 🤦‍♂️ re-running now with yarn prisma migrate deploy returns the error P4002, I can send over my init migration file if that helpers too?

@janpio here’s the full output of migrate dev for me:

Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "postgres", schema "public" at "db.qgpmxkwdgvsaeijbauxr.supabase.co:5432"

Error: P4002

The schema of the introspected database was inconsistent: Illegal cross schema reference from `public.customers` to `auth.users` in constraint `customers_id_fkey`. Foreign keys between database schemas are not supported in Prisma. Please follow the GitHub ticket: https://github.com/prisma/prisma/issues/1175

Before running prisma db pull, my schema looks like this

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["multiSchema"]
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  schemas  = ["auth", "public"]
}

Worth noting that, similar to your reproduction attempt:

1. Introspecting just the Supabase connection string with nothing created as expected returns that there are not tables/models

2. After running the SQL that @firzanarmani provided, db pull correctly complains that cross schema references are illegal - the preview feature is not enabled.

3. When then adding the preview feature multiSchema, I still get the error message. This is already a bit confusing, as I of course only forgot to supply the list of schemas as a property.

I find these to be true as well, which led me to using the schema as above.

Using 4.8.0-dev.22 internal version or 4.7.1 (PostgreSQL 10.21) Note I had to create manually the auth schema, the provided SQL was not enough.

With the following schema

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

npx prisma db pull also errors

Prisma schema loaded from prisma/schema.prisma
Environment variables loaded from prisma/.env
Datasource "db": PostgreSQL database "16585", schema "public" at "localhost:5432"

✖ Introspecting based on datasource defined in prisma/schema.prisma

Error: P4002

The schema of the introspected database was inconsistent: Illegal cross schema reference from `public.profiles` to `auth.users` in constraint `profiles_id_fkey`. Foreign keys between database schemas are not supported in Prisma. Please follow the GitHub ticket: https://github.com/prisma/prisma/issues/1175

With

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["multiSchema"]
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  schemas  = ["auth", "public"]
}

npx prisma db pull does not error, only migrate dev