Database Architecture

Comprehensive guide to the Prisma schema, database structure, and data models

This application uses PostgreSQL hosted on Neon (serverless) with Prisma ORM for type-safe database operations and migrations.

Database Technology Stack

Technologies powering the data layer

PostgreSQL 16

Robust relational database with ACID compliance, JSON support, and advanced indexing

Prisma ORM 6.19.0

Type-safe database client with auto-generated types and migration management

Neon Serverless

Serverless PostgreSQL with connection pooling, automatic scaling, and branching

Prisma Adapter

Auth.js integration for database-backed session management

Complete Prisma Schema

Full database schema definition

The schema is defined in prisma/schema.prisma and includes models for Person CRUD operations and Auth.js authentication.

// Prisma Schema Configuration
generator client {
  provider = "prisma-client-js"
  output   = "../app/generated/prisma"
}

datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")
  directUrl = env("DATABASE_URL_UNPOOLED")
}

// ========================================
// PERSON MODEL (Main CRUD Operations)
// ========================================
model Person {
  id          String   @id @default(cuid())
  name        String
  email       String   @unique
  phoneNumber String   @map("phone_number")
  createdAt   DateTime @default(now()) @map("created_at")
  updatedAt   DateTime @updatedAt @map("updated_at")

  @@map("people")
}

// ========================================
// AUTH.JS MODELS (Authentication)
// ========================================
model User {
  id            String    @id @default(cuid())
  name          String?
  email         String    @unique
  emailVerified DateTime? @map("email_verified")
  image         String?
  accounts      Account[]
  sessions      Session[]

  @@map("users")
}

model Account {
  id                String  @id @default(cuid())
  userId            String  @map("user_id")
  type              String
  provider          String
  providerAccountId String  @map("provider_account_id")
  refresh_token     String? @db.Text
  access_token      String? @db.Text
  expires_at        Int?
  token_type        String?
  scope             String?
  id_token          String? @db.Text
  session_state     String?

  user User @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@unique([provider, providerAccountId])
  @@map("accounts")
}

model Session {
  id           String   @id @default(cuid())
  sessionToken String   @unique @map("session_token")
  userId       String   @map("user_id")
  expires      DateTime
  user         User     @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@map("sessions")
}

model VerificationToken {
  identifier String
  token      String
  expires    DateTime

  @@unique([identifier, token])
  @@map("verification_tokens")
}

Person Model (CRUD Operations)

Primary data model for person management

The Person model is the core entity for all CRUD operations in this application. It stores individual person records with contact information.

Field	Type	Constraints	Description
`id`	String	Primary Key, CUID	Unique identifier (auto-generated)
`name`	String	Required	Person's full name
`email`	String	Required, Unique	Email address (must be unique)
`phoneNumber`	String	Required	Contact phone number
`createdAt`	DateTime	Auto-generated	Record creation timestamp
`updatedAt`	DateTime	Auto-updated	Last modification timestamp

Database Table Mapping

The Person model maps to the people table in PostgreSQL. Field names use snake_case in the database (e.g., phone_number,created_at) while maintaining camelCase in TypeScript.

Authentication Models

Auth.js database schema for OAuth and sessions

These models are required by Auth.js (NextAuth v5) with the Prisma adapter to manage user authentication, OAuth accounts, and session persistence.

User Model

Stores authenticated user profiles from OAuth providers (Google).

id - Unique user identifier (CUID)
name - User's display name from OAuth
email - Unique email address (primary identifier)
emailVerified - Email verification timestamp
image - Profile picture URL from OAuth

Account Model

Links users to OAuth providers and stores provider-specific tokens.

provider - OAuth provider name (e.g., "google")
providerAccountId - User ID from OAuth provider
access_token - OAuth access token (encrypted)
refresh_token - Token for refreshing access
expires_at - Token expiration timestamp

Session Model

Manages active user sessions with automatic expiration.

sessionToken - Unique session identifier (stored in cookie)
userId - Reference to authenticated user
expires - Session expiration timestamp

VerificationToken Model

Stores email verification tokens (for future email/password authentication).

identifier - Email address or user identifier
token - Verification token string
expires - Token expiration timestamp

Database Relationships

Foreign keys and data connections

The database uses foreign key constraints to maintain referential integrity between related tables.

Relationship Diagram:

User (1) ←→ (Many) Account

↳ One user can have multiple OAuth accounts

User (1) ←→ (Many) Session

↳ One user can have multiple active sessions

Person (Independent)

↳ No foreign key relationships (standalone CRUD entity)

Cascade Delete Behavior

When a User is deleted, all associated Accounts and Sessions are automatically deleted (onDelete: Cascade). This prevents orphaned records and maintains database integrity.

Database Configuration

Connection settings and environment variables

Connection Strings

The application uses two PostgreSQL connection strings for different purposes:

DATABASE_URL(Pooled Connection)

Used for application queries. Routes through pgBouncer connection pooler for serverless compatibility.

postgresql://user:password@host-pooler.region.neon.tech/db?sslmode=require

DATABASE_URL_UNPOOLED(Direct Connection)

Used for Prisma migrations. Direct connection without pooling for schema changes.

postgresql://user:password@host.region.neon.tech/db?sslmode=require

Security: Connection strings contain sensitive credentials and are stored in.env files, which are excluded from version control.

Prisma Client Generation

Type-safe database client configuration

Prisma generates a type-safe client based on the schema, providing autocomplete and type checking for all database operations.

Client Configuration

generator client {
  provider = "prisma-client-js"
  output   = "../app/generated/prisma"
}

The client is generated to app/generated/prisma and automatically regenerated when the schema changes.

Usage in Application:

import { prisma } from '@/lib/prisma'

// Type-safe query with autocomplete
const person = await prisma.person.findUnique({
  where: { id: 'abc123' }
})

// TypeScript knows the exact shape of 'person'
console.log(person?.name, person?.email)

Database Migrations

Schema version control and deployment

Prisma Migrate manages database schema changes through version-controlled migration files.

Migration Commands:

Development (Create Migration):

pnpm db:migrate

Creates a new migration file and applies it to the database

Production (Push Schema):

pnpm db:push

Pushes schema changes directly without creating migration files (used in CI/CD)

Generate Client:

pnpm db:generate

Regenerates Prisma Client after schema changes

Deployment: The build process automatically runs prisma generateto ensure the client is up-to-date before deployment.

Database Seeding

Sample data for testing and development

The application includes a seed script to populate the database with sample person records for testing.

Seed Command:

pnpm db:seed

Seed Data

The seed script (prisma/seed.ts) creates 10 sample people:

John Doe, Jane Smith, Alice Johnson
Bob Williams, Charlie Brown, Emily Davis
Frank Miller, Grace Lee, Henry Moore, Isabella Young

Each person has a unique email, phone number, and auto-generated ID.

CRUD Operations Implementation

Server Actions for database operations

All database operations are implemented as Next.js Server Actions inapp/actions/actions.ts with authentication checks.

Create - addUser() creates new person records
Read - getUserById() and searchUsers() fetch person data
Update - updateUser() modifies existing person records
Delete - deleteUser() removes person records
All operations require authentication (OAuth)
Automatic cache revalidation after mutations
Type-safe with Zod schema validation

Example: Create Operation

export async function addUser(data: Omit<User, 'id'>): Promise<User> {
  await requireAuth() // Ensure user is authenticated
  
  const validatedData = userSchema.omit({ id: true }).parse(data)
  
  const newPerson = await prisma.person.create({
    data: {
      name: validatedData.name,
      email: validatedData.email,
      phoneNumber: validatedData.phoneNumber,
    },
  })

  revalidatePath('/') // Refresh UI
  return newPerson
}

Database Best Practices

Security and performance optimizations

Connection pooling for serverless environments (pgBouncer)
SSL/TLS encryption for all database connections (sslmode=require)
Unique constraints on email fields to prevent duplicates
Automatic timestamps (createdAt, updatedAt) for audit trails
Cascade deletes to maintain referential integrity
Type-safe queries with Prisma Client (no SQL injection)
Environment-specific configurations (dev/prod separation)
Prisma Client singleton pattern to prevent connection exhaustion

Auth Setup Security Features View GitHub Repository