Database Schema

Relevant source files

The following files were used as context for generating this wiki page:

• schema.prisma
• SUPABASE_SETUP.md
• api/enhance-resume/[id]/route.ts
• api/resumes/upload/route.ts
• settings/_components/diagnostics.tsx
• README.md
• INTEGRATION_GUIDE.md

The database schema for the GUC Resume Enhancer is defined using Prisma and is designed to run on a PostgreSQL database, specifically hosted on Supabase. It underpins the entire application by managing user data, authentication, resume files, AI-driven enhancements, user-specific API keys, and system-level logging.

The schema is organized around several core models: User, Resume, ResumeEnhancement, UserApiKey, and SystemLog. These models are interconnected to provide a robust structure for storing and retrieving all application data. Authentication is handled via NextAuth, with corresponding models (Account, Session, VerificationToken) managed by the @next-auth/prisma-adapter.

Sources: schema.prisma, README.md:82-89, SUPABASE_SETUP.md:49-55

Entity Relationship Diagram

The following diagram illustrates the relationships between the main entities in the database.

This diagram shows that a User can have multiple Resume files, UserApiKey entries, and SystemLog records. Each Resume can have multiple ResumeEnhancement records associated with it.

Sources: schema.prisma:41-131

Core Models

These models form the functional core of the application.

User Model

Stores information about registered users, including their profile details and role. It serves as the central point for relating other data like resumes and API keys.

Field	Type	Attributes	Description
id	String	@id @default(cuid())	Unique identifier for the user.
name	String?		User's full name.
email	String	@unique	User's unique email address.
emailVerified	DateTime?		Timestamp when the email was verified.
image	String?		URL to the user's profile picture.
password	String?		Hashed password for email/password auth.
firstName	String?		User's first name.
lastName	String?		User's last name.
companyName	String?		User's company name.
role	String	@default("user")	User role, e.g., "user" or "admin".
createdAt	DateTime	@default(now())	Timestamp of user creation.
updatedAt	DateTime	@updatedAt	Timestamp of the last update.
resumes	Resume[]		Relation to the user's resumes.
apiKeys	UserApiKey[]		Relation to the user's API keys.

Sources: schema.prisma:41-57

Resume Model

Represents an uploaded resume. It stores metadata about the file, its location in cloud storage, and its extracted text content.

Field	Type	Attributes	Description
id	String	@id @default(cuid())	Unique identifier for the resume.
userId	String		Foreign key linking to the User.
originalName	String		The original filename of the uploaded resume.
cloudStoragePath	String		The path to the file in Supabase Storage.
fileType	String		The file type, e.g., 'pdf' or 'docx'.
originalContent	String	@db.Text	The raw text extracted from the document.
status	String	@default("uploaded")	The current status (e.g., uploaded, processing).
user	User	@relation(...)	Relation to the owner User.
enhancements	ResumeEnhancement[]		Relation to all enhancements for this resume.

The Resume model is created when a user uploads a file via the POST /api/resumes/upload endpoint. The file is saved to Supabase Storage, its text is extracted, and a new record is created in this table.

Sources: schema.prisma:66-79, api/resumes/upload/route.ts:101-111

ResumeEnhancement Model

Stores the result of an AI-powered resume enhancement. Each record is linked to a specific Resume and contains the modified content.

Field	Type	Attributes	Description
id	String	@id @default(cuid())	Unique identifier for the enhancement.
resumeId	String		Foreign key linking to the Resume.
enhancementType	String		The type of enhancement applied (e.g., skills_certifications).
enhancedContent	String	@db.Text	The AI-generated enhanced resume text.
llmProvider	String		The AI provider used (e.g., openai, anthropic).
status	String	@default("processing")	Status of the enhancement (processing, completed, error).
enhancementNotes	String?	@db.Text	Stores error messages or other notes.
resume	Resume	@relation(...)	Relation to the parent Resume.

This record is created and updated during the process initiated by POST /api/enhance-resume/[id].

Sources: schema.prisma:81-93, api/enhance-resume/[id]/route.ts:208-218, api/enhance-resume/[id]/route.ts:241-247

UserApiKey Model

Securely stores API keys provided by the user for different AI providers. The key itself is encrypted.

Field	Type	Attributes	Description
id	String	@id @default(cuid())	Unique identifier for the API key entry.
userId	String		Foreign key linking to the User.
provider	String		The AI provider (openai, anthropic, gemini).
keyName	String		A user-defined name for the key.
encryptedKey	String		The encrypted API key.
defaultModel	String	@default("gpt-4o-mini")	The default model to use for this provider.
isActive	Boolean	@default(true)	Whether this key is the active one for the user.
user	User	@relation(...)	Relation to the owner User.

A unique constraint @@unique([userId, provider]) ensures a user can only have one key per provider.

Sources: schema.prisma:95-109

SystemLog Model

Used for diagnostics and monitoring. It records events, errors, and other important information generated throughout the application.

Field	Type	Attributes	Description
id	String	@id @default(cuid())	Unique identifier for the log entry.
userId	String?		The user associated with the log, if applicable.
level	String		Log level (error, warning, info, debug).
category	String		The area of the app the log relates to (e.g., resume_enhancement).
message	String	@db.Text	The log message.
metadata	String?	@db.Text	A JSON string containing additional context.

Logs are created using a logger utility and can be filtered by level and category in the diagnostics UI.

Sources: schema.prisma:111-122, settings/_components/diagnostics.tsx:32-38

Authentication Models

These models are standard for the NextAuth Prisma adapter and handle user sessions and account linking.

• Account: Links OAuth accounts to a User.
• Session: Stores user session information.
• VerificationToken: Used for email verification links (e.g., passwordless login).

Sources: schema.prisma:13-40

Database Setup & Security

The application is configured to use a Supabase PostgreSQL database. The schema is synchronized using the Prisma CLI.

Schema Synchronization

To apply the schema to the database, the following command is used:

npx prisma db push

This command pushes the state of the schema.prisma file to the database, creating tables and columns as defined.

Sources: SUPABASE_SETUP.md:42-45, INTEGRATION_GUIDE.md:104-107

Row Level Security (RLS)

For enhanced security, the SUPABASE_SETUP.md guide recommends enabling Row Level Security (RLS) on key tables. This ensures that users can only access their own data at the database level.

The following is an example RLS policy for the Resume table, which allows users to view only their own resumes:

CREATE POLICY "Users can view their own resumes"
ON "Resume" FOR SELECT
TO authenticated
USING (auth.uid()::text = "userId");

Similar policies are recommended for INSERT operations and for other tables to enforce data isolation between users.

Sources: SUPABASE_SETUP.md:94-108

Data Flow: Resume Upload

The following diagram shows the database interactions when a user uploads a new resume.

This flow demonstrates how a single user action results in file storage and the creation of Resume and SystemLog records in the database.

Sources: api/resumes/upload/route.ts