Supabase Auth latest gotrue migration impossible to resync using Prisma migrate

[ODreelist]

Describe the bug

We've been using supabase auth with prisma in multischema since late last year with no issues. Whenever supabase would update auth schema and prisma drift was detected I would simply do this fairly predictable process:

1. look up the changes causing drift detection in the gotrue/migrations repo
2. create a drift_sync migration that reflects all the changes made to our supabase auth schema (will be in the gotrue/migrations repo)
3. run prisma init/prisma db pull somewhere outside the main repo to see what the changed auth schema looks like and then copy the schema.prisma changes into our main schema.prisma file
4. run prisma migrate resolve --applied 20231211214521_whatever_you_named_it

Afterwards, when you attempted to run a migrate the drift would be resolved.

However, the most recent migration:

https://github.com/supabase/auth/blob/master/migrations/20240427152123_add_one_time_tokens_table.up.sql

Is unable to be resynced by any prisma migrate resolve order that I can figure out. Whenever I add the enum (via applied migrate resolve) prisma seems to wants to add then remove it.

To Reproduce

Attempt to use Supabase in multischema for any length of time and run into drift due to them changing the auth schema, then attempt to resolve it when they add an enum.

Expected behavior

We need a way to reliably resync our prisma schema regardless of what changes are made to the auth schema in order for multischema to be usable at all. Otherwise the promise of a usable prisma integration as outlined here:

https://supabase.com/partners/integrations/prisma

Does not hold up.

This setup allowed us to maximize our use of Supabase in some invaluable ways. Not only could we use Supabase Auth and RLS to its full potential we are able to document and manage RLS policies through Prisma migrations in addition to our schema, custom functions, and triggers all while making use of the auth.uid() and auth.jwt() functions inside of prisma migration.sql files.

I'd be happy to elaborate on how this setup is achieved from a new Supabase instance and/or work with some of the devs directly because this configuration (even with the occasional drift fixing compromise) is one of the best developer experiences I have came across. I would hate to lose it because of something that is likely prisma's fault (hopefully an oversight by me) but ultimately is the Supabase community's problem to figure out.

Screenshots

After applying the migration as is from the gotrue repo and updating the schema.prisma file to reflect the new enum and one time token table (same process that worked for all previous gotrue migrations since late december):

I appreciate any feedback or support, I'm not sure if we'd be able to continue with this solution if we can't get this figured out. The risk of having to blow out our DB every month is too much to assume in production.

[ODreelist]

We can leave this up if you guys want but I'm very relieved I was able to solve this issue on my own. I needed to add the schema declaration (auth.) to the enum on creation like so:

in my migration.sql, otherwise it would appear it was attempting to add the enum to the default schema which would make sense when you look at the duplicate enum in the drift detection error screenshot above.

Note: I also had to add schema declaration (auth.) the one_time_tokens.token_type column as shown here:

otherwise you would get this error on migrate:

All in all consider this issue solved and an oversight by me (best possible outcome), hopefully it helps anyone who has to keep the auth schema in sync in response to supabase making changes. You can't just copy the migrations from this repo:

https://github.com/supabase/auth/blob/master/migrations/

change the namespace to auth and call it a day. As the changes get applied to your Supabase instances and cause prisma to detect drift, you have to carefully consider the code going in those migration.sql files and the changes you make to your schema.prisma as well.

Again, consider this issue solved. Archive it if you feel like it can help people or let me know if you want me to delete it.

Thanks.