Auth schema changes made by supabase causing prisma drift detection

[ODreelist]

Describe the bug

I need clarification on auth schema changes. I have been using prisma with a lot of success and enjoying a really good dev experience. I use multischema and pull auth and public to start, update a few lines in the auth schema, and add the helper functions to the initial migration and in order to successfully sync the shadow db with an initial migrate diff.

Beyond that initial step, I'm able to define RLS policies, add triggers and functions as well as the standard schema evolution using prisma migrate, and everything is great.

However, yesterday my auth schema changed in the following way:

Drift detected: Your database schema is not in sync with your migration history.

The following is a summary of the differences between the expected database schema given your migrations files, and the actual schema of the database.

It should be understood as the set of changes to get from the expected schema to the actual schema.

[*] Changed the `flow_state` table
  [+] Added index on columns (created_at)

[*] Changed the `refresh_tokens` table
  [+] Added index on columns (updated_at)

[*] Changed the `saml_relay_states` table
  [+] Added index on columns (created_at)

[*] Changed the `sessions` table
  [+] Added index on columns (not_after)

I didn't add those indexes, supabase did. My question is how often do you plan on changing the auth schema and do you have any plan to work with supabase devs who work with prisma to guard against random drift brought on by supabase making unannounced changes to the auth schema?

Secondarily, I can easily identify the 4 new indexes that my shadow db needs to know about, so if there is a way to inform the shadowdb of the new changes without resetting the db, that would be helpful.

[GaryAustin1]

I won't speak for the Supabase dev's, but in general you should not modify the Auth schema at all (except for triggers on auth.users table as documented in user management starter). The auth schema changes regularly with new features, so columns can change and new tables can be added. Several times indexes have changed, unique columns like email have changed etc. Anytime gotrue (server) updates there could be changes to the auth schema.

Just a couple of Supabase dev remarks...

Supabase Auth component([gotrue](https://github.com/supabase/gotrue/)) depends on the auth.users table and runs migrations against it. So really auth.users is a private table and you shouldn't change it - doing so might cause gotrue to fail.

If Prisma makes modifications to the auth schema under the hood, it will cause gotrue to fail since the auth schema is supposed to be managed by gotrue only.

[GaryAustin1]

From the docs... https://supabase.com/docs/guides/auth/managing-user-data

[ODreelist]

I appreciate the response. I can live with that disclaimer as I am adhering to that principle explained and outlined in the user management starter snippet. The only relationship I establish to the auth schema is with my public.profiles table via auth.users PK.

That said, in order for me to efficiently use prisma migrate to manage things like RLS policies, functions, and triggers, is for the shadow db to remain in sync with both the auth and the public schema (utilizing prisma multiSchema).

In my initial migration.sql I add the helper functions as they are defined by supabase and update a couple auth schema model attributes (as they do not get introspected correctly by prisma) so the shadow db understands what auth.jwt() and auth.uid() are and can successfully apply the initial migration. At that point I can add triggers/functions/RLS policies that use the auth helper functions without issue, and with the benefits of prisma.

A lot of the setup can be attributed to this write up here: Prisma with supabase RLS

The only problem is that In this current and otherwise ideal situation, I would be forced to randomly encounter drift detection of the auth schema, and thus have to manually resolve changes by comparing the changes made by supabase against my migration history, generating manual migration.sql that reflects the changes and then using prisma migrate resolve in order to overcome the detected drift.

I think the ability to manage RLS policies, triggers, and functions using the auth.jwt() or auth.uid() helpers via prisma migrate is far to valuable to give up on.

Ideally, we could inform the prisma shadow db of the auth helper functions while leaving the auth schema out of prisma's datasources entirely, but I'm not sure how that is possible.

[GaryAustin1]

You might try this thread one of the Supabase devs commented on. #1061 (comment)

[ODreelist]

Thanks, but that thread is unrelated. I don't have those problems, I do not reset the auth schema, in fact I never touch the auth schema outside of Prisma introspection (db pull) on initial project setup.

I would really appreciate a supabase team member's perspective on this because I need to know if supabase is actually supporting us developers that want to use supabase with supabase auth and prisma.

I cannot stress enough how well the dev experience has gone for my team, we can easily keep track of changes related to our public schema, triggers, and RLS policies that take advantage of the core auth helpers that supabase encourages us to make use of.

Again, if multiSchema is necessary to achieve this quality dev experience, all we need is a proper method to handle this:

1. Supabase team makes changes to customers auth schema
2. Our team goes to make a migration and prisma detects drift on the auth schema
3. Our team can somehow synchronize those changes to resolve the detected drift
4. Our team makes the migration we attempted in step 2 and can continue developing the project

I appreciate all the feedback.

[GaryAustin1]

I moved this to auth. FYI, This is where I believe the changes to auth schema get documented.... https://github.com/supabase/gotrue/tree/master/migrations

[michaelkremenetsky]

having the same issue, exact issue using the create-t3-turbo template. https://github.com/supabase-community/create-t3-turbo

[michaelkremenetsky]

maybe this? prisma/prisma#1122 (comment)

[ODreelist]

maybe this? prisma/prisma#1122 (comment)

This issue/approach is not technically related and far less less mature than my current solution.

With the help of @GaryAustin1 and his link above, I find that manually keeping the shadow db up to date with supabase gotrue auth schema changes is a small sacrifice to make compared to the sheer number of benefits we get from this solution.

[kangmingtay]

Hey @ODreelist, I've got a couple of questions:

1. Are you able to disable prisma's drift detection on the auth schema?
2. Can you describe when you get the drift detection error from prisma? Is it usually after running prisma db pull?
3. We are working on a way to pull the same gotrue image version as the linked project so that your local development will receive those migration changes. That way, you will be able to sync your local development with your remote before you run your prisma commands.

Let me know what you guys think of (3)!

[ODreelist]

Hello,

Thanks for the response, I'll do my best to answer.

1. No, I think they discussed that at length on the prisma issue tracker as it's highly requested. But I am able to resolve the drift detection of auth schema changes with a manual migration/migrate resolve. If we were able to inform prisma and the shadow DB that the auth schema exists and could be referenced, but doesn't need to be tracked, that would solve this problem outright.

2. I'm using a migrate/push strategy so typically when I make public schema changes and try to run a migration with prisma migrate dev is when I get warned about drift detection (see the original post for what the drift detection looks like). At that point I can typically track the auth schema changes that triggered the drift detection to the gotrue migration repo (See: https://github.com/supabase/gotrue/tree/master/migrations). I then update my schema accordingly and create a manual migration which will apply the detected changes to the shadow db via prisma migrate resolve and I will have solved the drift detection. At that point I can continue, with my public schema migrations.

3. I think that would be helpful, but let me make sure I'm understanding you correctly. You are saying we will be able to pull a docker image that's in sync with our supabase project? And that it would apply the latest auth schema changes? If so I'm sure that would help but keep in mind I use a live supabase environment for dev and a separate one for prod so all of the discussion here applies to hosted supabase instances, not local supabase spun up in docker (we have not used supabase local yet).

I think the most important question I have is this: Does my pattern of manually resolving gotrue->supabase auth schema changes using the migration repo (linked above) as a guide/early warning system of what/when changes will be propagated to supabase customer databases sufficient? does my plan make sense?

It's a simple enough process to essentially resync my shadow db when supabase makes changes to the auth schema. I wish I didn't have to, but assuming the auth schema changes you guys make are relatively infrequent and small like the one I mentioned in the first post, the benefits of prisma being aware of the auth schema for its magic functions (auth.jwt(), auth.uid(), etc.) and being able to use them to define/track RLS policies, functions and triggers inside of prisma migrations as well as manage the public schema through prisma, far outweigh this drawback.

[cfpg]

@ODreelist I'm facing the same issue after following the same deploy procedure for multi-schema migrations. I've tried running the missing migrations from gotrue repo, but prisma's migration still returns the same drift errors. What are the steps you're following to keep the shadow DB up to date with gotrue changes?

Update: I've managed to do it doing the following:

• Execute missing migrations from gotrue's repo in you shadow DB (in my case 1 alter table and 1 create index)
• Copy those changes into your initial prisma db pull migration file (0_init_prisma_db_pull for ex.)
• Generate a new checksum for the previously edited migration file using shasum -a 256 prisma/migrations/0_init_prisma_db_pull/migration.sql
• Replace the value in the corresponding row in _prisma_migrations
• Now prisma thinks these changes were with us all along

[BjoernRave]

@cfpg would it maybe be possible to share a repo or just the content of the relevant files to see how you did that exactly? Sounds very interesting.