Designing the Database Schema

Introduction

Now that we’ve got a good idea of how authentication works, the next step is to design the database schema that will store our users. We’re using Prisma as our ORM (Object Relational Mapper), which means instead of writing raw SQL, we can define our models in a single schema.prisma file, and Prisma will handle the database tables for us. Pretty nice!

Creating the User Model

Let’s start with a basic User model and a Role enum:

enum Role {
  user
  admin
}

model User {
  id        String   @id @default(uuid())
  email     String   @unique 
  password  String 
  salt      String 
  role      Role     @default(user)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

The enum defines the role types a user can have. Only these exact values are allowed.

The id is our primary key, and we use @id to mark it as the unique identifier. @default(uuid()) tells Prisma to automatically generate a UUID (a long, random unique string) whenever a new user is created.

Every user will need an email to sign up or log in. Marked as String (not optional) and @unique, which means no two users can have the same email.

password is where we’ll store the hashed password, not the plain password. Always make sure passwords are hashed before saving them to the database (we’ll cover that soon).

The salt is a random string that makes each password hash unique. Since we're using scrypt, we'll need to store the salt.

The role determines what kind of access a user has in your app. Defaults to user if you don’t specify anything.

createdAt and updatedAt just stores when the user is created and updated. Handy for tracking activity.

Generating and Migrating Schema

Once you’ve updated your schema.prisma file with this model, we need to apply it to the database. Run the following command in your terminal so that our app knows about the new schema:

npx prisma generate

Then run the following command to create and run a migration to update our actual database tables:

npx prisma migrate dev 

It will prompt you to name the migration, you can just call it something like create-user-model.

If you check Neon, you'll also see that these tables were added.

At this point, you’ve got a solid schema that supports basic authentication and role-based access. Later on, we’ll extend this to include things like email verification tokens and password reset tokens, but this is enough to get us started. In the next lesson, we'll get into the actual authentication flow for the user.

Run npx prisma generate npx prisma migrate dev

Add lib db