Prisma Schema

Purpose and Scope

This document provides a comprehensive reference for the Prisma schema used in TalentSync. The schema defines all database models, relationships, and constraints for the PostgreSQL database. It covers authentication models (User, Account, Session, VerificationToken), resume-related models (Resume, Analysis), and feature-specific models (ColdMail, InterviewAnswer).

For information about Pydantic data validation models used in the backend API, see Data Models & Validation. For deployment and database configuration, see Environment Configuration.

Schema Overview

The Prisma schema is organized into three main categories: Authentication & User Management, Resume Intelligence, and Feature Data Storage. The schema uses PostgreSQL as the database provider and is located at frontend/prisma/schema.prisma

Sources: frontend/prisma/migrations/20250613172024_replace_file_url_with_raw_text/migration.sql

Core Authentication Models

User Model

The User model serves as the central authentication entity, supporting both traditional email/password authentication and OAuth providers (Google, GitHub).

Key Characteristics:

• Email and passwordHash are nullable to support OAuth-only users
• EmailVerified gates access to protected features (enforced in frontend/middleware.ts)
• Cascading deletes remove all related data (resumes, analyses, sessions, etc.)

Sources: frontend/prisma/migrations/20250613172024_replace_file_url_with_raw_text/migration.sql16-21

Role Model

The Role model defines user types with two primary roles: Seeker (job seekers) and Recruiter (hiring managers).

Role Assignment Flow:

1. User registers/logs in without role
2. Middleware redirects to /select-role page
3. User selects role via API call to /api/auth/update-role
4. Session updated with role information
5. User redirected to appropriate dashboard

Sources: frontend/prisma/migrations/20250613172024_replace_file_url_with_raw_text/migration.sql9 frontend/prisma/migrations/20250613172024_replace_file_url_with_raw_text/migration.sql86

Account Model (OAuth)

The Account model stores OAuth provider credentials for Google and GitHub authentication via NextAuth.

Unique Constraint: (provider, providerAccountId) ensures one account per provider per user.

Sources: frontend/prisma/migrations/20250613172024_replace_file_url_with_raw_text/migration.sql36-51 frontend/prisma/migrations/20250613172024_replace_file_url_with_raw_text/migration.sql74

Session Model

The Session model manages NextAuth session tokens for authenticated users.

Session Lifecycle:

• Created on successful login
• Validated on each authenticated request via frontend/middleware.ts
• Automatically deleted on logout or expiration
• Cascading delete when user account is deleted

Sources: frontend/prisma/migrations/20250613172024_replace_file_url_with_raw_text/migration.sql54-61 frontend/prisma/migrations/20250613172024_replace_file_url_with_raw_text/migration.sql77

VerificationToken Model

The VerificationToken model handles email verification for new user registrations.

Unique Constraint: (identifier, token) composite key ensures token uniqueness.

Verification Flow:

1. User registers with email/password
2. Token generated and emailed via Nodemailer
3. User clicks link: /api/auth/verify-email?token={token}
4. Token validated and User.emailVerified set
5. Token deleted after successful verification

Sources: frontend/prisma/migrations/20250613172024_replace_file_url_with_raw_text/migration.sql64-68 frontend/prisma/migrations/20250613172024_replace_file_url_with_raw_text/migration.sql80-83

PasswordResetToken Model

The PasswordResetToken model manages password reset requests for email/password users.

Reset Flow:

1. User requests reset at /auth/forgot-password
2. Token generated via /api/auth/forgot-password
3. Email sent with reset link: /auth/reset-password?token={token}
4. Token validated, password updated
5. usedAt timestamp set to prevent reuse

Sources: frontend/prisma/migrations/20250613172024_replace_file_url_with_raw_text/migration.sql24-33 frontend/prisma/migrations/20250613172024_replace_file_url_with_raw_text/migration.sql71

Resume Intelligence Models

Resume Model

The Resume model stores uploaded resume files as extracted raw text, replacing the previous file URL approach for improved performance and security.

Text Extraction Process:

1. File uploaded via frontend/components/upload-resume.tsx38-46
2. Backend extracts text using PyPDF2, python-docx, or direct read
3. Text stored in rawText field (migration from fileUrl)
4. Original file not persisted (privacy consideration)

Design Decision: The migration from fileUrl to rawText eliminates file storage requirements and enables faster text processing for subsequent AI operations.

Sources: frontend/prisma/migrations/20250613172024_replace_file_url_with_raw_text/migration.sql12-13 frontend/components/upload-resume.tsx38-46

Analysis Model

The Analysis model stores structured resume analysis results generated by the Resume Parser service, including ML classification and LLM-extracted fields.

Skills JSON Structure:

{
  technical: string[],
  soft: string[],
  languages: string[],
  tools: string[]
}

Experience JSON Structure:

[{
  company: string,
  title: string,
  duration: string,
  description: string
}]

Analysis Pipeline:

1. Text extracted from resume
2. NLP cleaning (Spacy, NLTK) removes noise
3. TF-IDF vectorization + scikit-learn classifier predicts category
4. Regex patterns extract basic fields (name, email, skills)
5. Gemini 2.0 Flash generates comprehensive structured JSON
6. Analysis record created with foreign key to Resume

Sources: frontend/prisma/migrations/20250613172024_replace_file_url_with_raw_text/migration.sql

Feature Data Models

ColdMail Model

The ColdMail model stores generated cold emails for job applications, including recipient details and email content.

Generation Flow:

1. User selects resume at /dashboard/cold-mail
2. Provides recipient details (name, company, job description)
3. Backend generates email using Gemini + company research
4. Record saved for history and editing
5. User can regenerate or edit content

Sources: Architecture diagrams

InterviewAnswer Model

The InterviewAnswer model stores AI-generated interview question responses from the Hiring Assistant feature.

Answer Generation:

1. User enters question at /dashboard/hiring-assistant
2. Optionally provides role and company context
3. Backend uses resume data + Gemini to craft answer
4. Record saved for interview preparation reference

Sources: Architecture diagrams

Database Relationships and Constraints

Foreign Key Cascade Behavior

The schema implements cascading deletes to ensure data consistency when users or resumes are removed:

Cascade Rules:

• User deletion: All accounts, sessions, resumes, analyses, cold mails, and interview answers are automatically deleted
• Resume deletion: Associated analysis, cold mails, and interview answers are deleted
• Role deletion: User.roleId set to NULL (allows role cleanup without user deletion)
• PasswordResetToken: ON DELETE RESTRICT prevents user deletion with active reset tokens

Sources: frontend/prisma/migrations/20250613172024_replace_file_url_with_raw_text/migration.sql86-95

Unique Constraints

Sources: frontend/prisma/migrations/20250613172024_replace_file_url_with_raw_text/migration.sql71-83

Data Types and Storage

Text vs String Fields

The schema uses PostgreSQL TEXT type for large content fields:

String fields are used for identifiers, names, and short text:

• User.email, User.name, User.image
• Resume.customName
• Analysis.name, Analysis.email, Analysis.category

Sources: frontend/prisma/migrations/20250613172024_replace_file_url_with_raw_text/migration.sql12-13

JSON Fields for Structured Data

The Analysis model uses PostgreSQL JSON type for flexible structured data storage:

// skills: Json
{
  "technical": ["Python", "JavaScript", "React"],
  "soft": ["Leadership", "Communication"],
  "languages": ["English", "Spanish"],
  "tools": ["Git", "Docker", "AWS"]
}

// experience: Json
[
  {
    "company": "Tech Corp",
    "title": "Senior Developer",
    "duration": "2020-2023",
    "description": "Led team of 5 developers..."
  }
]

// projects: Json
[
  {
    "name": "E-commerce Platform",
    "description": "Built scalable microservices...",
    "technologies": ["Node.js", "MongoDB"]
  }
]

// education: Json
[
  {
    "degree": "BS Computer Science",
    "institution": "MIT",
    "year": "2019"
  }
]

Advantages:

• Flexible schema for varying resume structures
• Query support via PostgreSQL JSON operators
• No need for separate junction tables
• Direct mapping to frontend TypeScript types

Sources: Architecture diagrams

Migration History

Key Schema Evolution

Migration: Replace fileUrl with rawText (2025-06-13)

This migration represents a significant architectural change in how resumes are stored:

Changes:

1. Resume table: Dropped fileUrl column, added rawText TEXT column
2. User table: Added OAuth support fields (emailVerified, image, name)
3. User table: Made email, passwordHash, roleId nullable for OAuth users
4. New tables: Account, Session, VerificationToken for NextAuth integration
5. New table: PasswordResetToken for password reset functionality
6. Foreign keys: Updated User → Role relationship to ON DELETE SET NULL

Rationale:

• Eliminates file storage infrastructure requirements
• Improves text extraction performance (no file I/O)
• Enhances privacy (no persistent file storage)
• Simplifies deployment (no mounted volumes for uploads)
• Enables faster AI processing (direct text access)

Sources: frontend/prisma/migrations/20250613172024_replace_file_url_with_raw_text/migration.sql1-96

Prisma Client Generation

The Prisma Client is generated during the build process and provides type-safe database access throughout the application.

Generation Commands:

# Generate Prisma Client
npx prisma generate

# Apply migrations
npx prisma migrate deploy

# Seed database
npx prisma db seed

Client Usage in Frontend:

// Import generated client
import { prisma } from '@/lib/prisma'

// Type-safe queries
const user = await prisma.user.findUnique({
  where: { id: userId },
  include: {
    resumes: {
      include: { analysis: true }
    }
  }
})

Build Process Integration:

• Frontend Dockerfile runs prisma generate during build
• NextAuth callbacks use Prisma Client for session/user lookups
• API routes use Prisma for all database operations

Sources: Architecture diagrams, frontend/components/upload-resume.tsx43-46