Database Schema

Complete database schema documentation with models, fields, and relationships

Overview

App CR uses Prisma ORM with PostgreSQL 15 as the database. The schema defines two core models: User and Task, with a one-to-many relationship.

Schema Configuration

generator client {
  provider = "prisma-client-js"
}

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

generator.provider · string · default: "prisma-client-js"

Generates the Prisma Client for JavaScript/TypeScript applications

datasource.provider · string · default: "postgresql"

Database provider (PostgreSQL 15)

datasource.url · string · required

Database connection URL from DATABASE_URL environment variable

---

User Model

The User model represents application users who can create and manage tasks.

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  password  String
  tasks     Task[]
}

Fields

id · Int · required

Primary key with auto-increment

Attributes:

• @id - Primary key constraint
• @default(autoincrement()) - Auto-generated sequential ID

email · String · required

User’s email address (unique across all users)

Attributes:

• @unique - Unique constraint enforced at database level

Validation: Must be a valid email format

password · String · required

Hashed password for user authentication

Security: Store only bcrypt/argon2 hashed passwords, never plaintext

tasks · Task[]

One-to-many relationship with Task model

Relation: A user can have multiple tasks

Database Table

CREATE TABLE "User" (
    "id" SERIAL NOT NULL,
    "email" TEXT NOT NULL,
    "password" TEXT NOT NULL,
    CONSTRAINT "User_pkey" PRIMARY KEY ("id")
);

CREATE UNIQUE INDEX "User_email_key" ON "User"("email");

---

Task Model

The Task model represents todo items created by users.

model Task {
  id          Int      @id @default(autoincrement())
  title       String
  description String?
  completed   Boolean  @default(false)
  userId      Int
  author      User     @relation(fields: [userId], references: [id])
}

Fields

id · Int · required

Primary key with auto-increment

Attributes:

• @id - Primary key constraint
• @default(autoincrement()) - Auto-generated sequential ID

title · String · required

Task title or summary

Validation: Required field, cannot be null or empty

description · String?

Optional detailed description of the task

Nullable: Can be null/undefined

completed · Boolean · required

Task completion status

Attributes:

• @default(false) - New tasks are incomplete by default

Values: true (completed) or false (pending)

userId · Int · required

Foreign key referencing the User who created this task

Relation: Links to User.id

author · User · required

Many-to-one relationship with User model

Attributes:

• @relation(fields: [userId], references: [id]) - Foreign key relationship

Behavior:

• ON DELETE RESTRICT - Cannot delete user if they have tasks
• ON UPDATE CASCADE - Updates propagate to tasks

Database Table

CREATE TABLE "Task" (
    "id" SERIAL NOT NULL,
    "title" TEXT NOT NULL,
    "description" TEXT,
    "completed" BOOLEAN NOT NULL DEFAULT false,
    "userId" INTEGER NOT NULL,
    CONSTRAINT "Task_pkey" PRIMARY KEY ("id")
);

ALTER TABLE "Task" ADD CONSTRAINT "Task_userId_fkey" 
  FOREIGN KEY ("userId") REFERENCES "User"("id") 
  ON DELETE RESTRICT ON UPDATE CASCADE;

---

Relationships

User ↔ Task (One-to-Many)

1. User has many Tasks

   Each user can create multiple tasks through the tasks relation field

   const userWithTasks = await prisma.user.findUnique({
     where: { id: 1 },
     include: { tasks: true }
   });
   
2. Task belongs to User

   Each task belongs to exactly one user through the author relation and userId foreign key

   const taskWithAuthor = await prisma.task.findUnique({
     where: { id: 1 },
     include: { author: true }
   });
   
3. Referential Integrity

   Foreign key constraint ensures data integrity:

   • Cannot create task with non-existent userId
   • Cannot delete user who has tasks (RESTRICT)
   • Updating user.id cascades to task.userId (CASCADE)

---

Schema File Location

~/workspace/source/backend/prisma/schema.prisma

Working with the Schema

Generate Client

npx prisma generate

After modifying the schema, always run prisma generate to update the Prisma Client types.

---

Next Steps

Run Migrations

Learn how to apply schema changes to your database

Database Setup

Configure database connection and environment