Supabase Auth and Prisma Integration

Why This Integration?

Integrating Supabase Auth with Prisma offers a robust solution for managing user authentication alongside extended user profiles within a single application. While Supabase Auth provides a secure and scalable authentication system, it limits user-related data to basic information. By synchronizing auth.users with a custom public.users table managed by Prisma, we gain the flexibility to extend user profiles with additional fields and maintain referential integrity through foreign keys, enhancing our application’s data model and capabilities.

How It Works

Overview

This integration automatically mirrors entries from Supabase’s auth.users table to a custom public.users table in our database. This synchronization allows us to extend the user model with additional information (e.g., user settings) and establish foreign key relationships with other tables, all managed through Prisma.

Step 1: Setting Up Prisma

Define your User model in schema.prisma to mirror Supabase’s auth.users and include additional fields:

model User {
  id        String   @id @default(uuid()) @map("id")
  email     String   @unique
  settings  Json?
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")

  @@map("users")
}

Step 2: Creating a PostgreSQL Trigger in Supabase

Implement a PostgreSQL trigger within Supabase to sync every new or updated entry in auth.users with public.users:

CREATE OR REPLACE FUNCTION public.handle_user_sync()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    INSERT INTO public.users (id, email, created_at, updated_at)
    VALUES (NEW.id, NEW.email, NOW(), NOW())
    ON CONFLICT (id) DO NOTHING;
    RETURN NEW;
  ELSIF TG_OP = 'UPDATE' THEN
    UPDATE public.users
    SET email = NEW.email, updated_at = NOW()
    WHERE id = NEW.id;
    RETURN NEW;
  END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER sync_auth_users
AFTER INSERT OR UPDATE ON auth.users
FOR EACH ROW EXECUTE FUNCTION public.handle_user_sync();

Step 3: Managing User Data with Prisma

With the synchronization in place, use Prisma to manage public.users, adding any additional user-related data or settings as needed.

Security and Performance

Ensure sensitive user information is securely handled and access-controlled.
Monitor the performance impact of the database trigger, adjusting as necessary.

Testing

Before deployment, thoroughly test the integration in a development environment to confirm that user data syncs correctly and that your application logic functions as expected.

Conclusion

This README outlines the integration of Supabase Auth with Prisma for enhanced user data management. By leveraging the strengths of both platforms, we create a powerful foundation for building sophisticated and scalable applications.