Supabase Authentication Flow

When a user authenticates on Shipped using Supabase Auth Magic link or Email & Password, it follows this flow.

1. The user provides the email (i.e [e-mail]) and hits the Sign Up button

2. Supabase sends an email to [e-mail]

3. The user clicks on the link included in the email

4. The browser opens the route yourwebsite.com/supabase/auth/callback with the code query parameter (?code=abc...)

5. The route exchanges the code for a session and saves the user into the database table public.User with the same ID as the Supabase Auth user.

Use the Supabase Auth user with Prisma

Supabase saves all authenticated users into the table auth.users (where auth is the schema, and users the table name) of the Postgres database.

Shipped is configured to use Prisma as the ORM (Object Relational Mapping), and it uses the schema public to create all the tables that are needed by your product.

By default, the prisma.schema file that comes with Shipped, includes a public.User table, with this schema:

model User {
  id            String    @id @default(cuid())
  name          String?
  email         String?   @unique
  emailVerified DateTime?
  image         String?
  accounts      Account[]
  sessions      Session[]
  UserPlan      UserPlan?
}

This schema is coming from NextAuth, but you can also use it if you use Supabase Auth.

If you need to add new tables to your database, which refer to a user, use can use a schema similar to the one described below.

For instance, if your product generates screenshots of websites, you'll probably have a public.Screenshot table, defined in the prisma.schema file as follows:

model Screenshot {
    userId String
    websiteUrl String
    screenshotUrl String 
    createdAt    DateTime @default(now())
    updatedAt    DateTime @updatedAt
    
    user User @relation(fields: [userId], references: [id])
}

Notice the foreign key relation between the table Screenshot and the table User, based on userId (User.id -> Screenshot.userId).

If you need to retrieve the screenshots of the user, use this sample code:

import { getSupabaseServerClient } from "@/libs/supabase.server";
import { prismaClient } from "@/prisma/db";

export async function GET() {
    const supabase = getSupabaseServerClient();
    const supabaseSession = await supabase.auth.getSession();
    const session = supabaseSession?.data.session;
    
    /* return error if session is not defined, removed for brevity */
    
    const screenshots = await prismaClient.screenshot.findMany({
        where: {
            userId: session.user.id
        }
    })
}

This way, you'll be able to combine Supabase Auth, with the Prisma-handled tables and link the users using the Supabase Auth user id.