MindFlow data models: Prisma schema and PostgreSQL reference

MindFlow’s persistence layer is built on six Prisma models backed by a PostgreSQL 15 database. Every model uses a UUID primary key generated by PostgreSQL’s native gen_random_uuid() function, all timestamp columns are declared as Timestamptz to store values in UTC without timezone ambiguity, and foreign-key constraints are enforced at the database level. The backend runs Prisma 7 with the @prisma/adapter-pg driver adapter; because of this, the datasource connection URL is configured in prisma.config.ts rather than directly inside schema.prisma.

Student

The Student model represents an authenticated user of the MindFlow platform. Each student account is identified by a unique email address, and the password is stored exclusively as a bcrypt hash (12 rounds). A student is the root entity of the data hierarchy — every session, task, fatigue record, and notification log belongs to exactly one student.

Field Name	Type	Constraints	Description
`id`	`String` (`UUID`)	PK, `gen_random_uuid()`	Unique identifier for the student
`email`	`String` (`VarChar(255)`)	UNIQUE, NOT NULL	Student’s email address
`passwordHash`	`String` (`VarChar(255)`)	NOT NULL	bcrypt hash of the password (12 rounds); mapped to `password_hash`
`createdAt`	`DateTime` (`Timestamptz`)	NOT NULL, default `now()`	UTC timestamp of account creation; mapped to `created_at`
`updatedAt`	`DateTime` (`Timestamptz`)	NOT NULL, auto-updated by Prisma	UTC timestamp of last update; mapped to `updated_at`

Relations: one-to-many with Session, Task, FatigueRecord, and NotificationLog.

// ─── Student ─────────────────────────────────────────────────────────────────
// Representa al usuario del sistema (estudiante autenticado).
// Requisito 7.1
model Student {
  id           String   @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  email        String   @unique @db.VarChar(255)
  passwordHash String   @map("password_hash") @db.VarChar(255)
  createdAt    DateTime @default(now()) @map("created_at") @db.Timestamptz
  updatedAt    DateTime @updatedAt @map("updated_at") @db.Timestamptz

  // Relations
  sessions          Session[]
  fatigueRecords    FatigueRecord[]
  tasks             Task[]
  notificationLogs  NotificationLog[]

  @@map("students")
}

Session

A Session represents an active interaction period between a student and the EMA_Bot. A session begins when the student clicks “Start EMA Session” and ends when it is explicitly closed; while ongoing, endedAt remains null and isActive remains true. Sessions group the fatigue records submitted during that interaction and serve as the parent context for any micro-objectives that are generated.

Field Name	Type	Constraints	Description
`id`	`String` (`UUID`)	PK, `gen_random_uuid()`	Unique identifier for the session
`studentId`	`String` (`UUID`)	FK → `students.id`, NOT NULL	Owning student; mapped to `student_id`
`startedAt`	`DateTime` (`Timestamptz`)	NOT NULL	UTC timestamp when the session started; mapped to `started_at`
`endedAt`	`DateTime?` (`Timestamptz`)	NULLABLE	UTC timestamp when the session ended; `null` while the session is still active; mapped to `ended_at`
`isActive`	`Boolean`	NOT NULL, default `true`	Whether the session is currently active; mapped to `is_active`

Relations: belongs to Student; one-to-many with FatigueRecord and MicroObjective.Indexes: student_id, is_active.

// ─── Session ─────────────────────────────────────────────────────────────────
// Período de interacción activa entre el estudiante y el EMA_Bot.
// Requisito 7.2
model Session {
  id        String    @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  studentId String    @map("student_id") @db.Uuid
  startedAt DateTime  @map("started_at") @db.Timestamptz
  endedAt   DateTime? @map("ended_at") @db.Timestamptz
  isActive  Boolean   @default(true) @map("is_active")

  // Relations
  student         Student          @relation(fields: [studentId], references: [id])
  fatigueRecords  FatigueRecord[]
  microObjectives MicroObjective[]

  @@index([studentId])
  @@index([isActive])
  @@map("sessions")
}

FatigueRecord

A FatigueRecord stores the self-reported fatigue score that a student submits during an EMA session. The score must be an integer in the range [1, 5] — 1 indicates no fatigue and 5 indicates maximum fatigue. Scores at or above 4 trigger the Task_Decomposer, which automatically breaks active tasks into micro-objectives of 25 minutes or fewer. This constraint is enforced both at the application validation layer and at the database level via a CHECK constraint in the migration SQL.

Field Name	Type	Constraints	Description
`id`	`String` (`UUID`)	PK, `gen_random_uuid()`	Unique identifier for the fatigue record
`sessionId`	`String` (`UUID`)	FK → `sessions.id`, NOT NULL	Session during which the score was recorded; mapped to `session_id`
`studentId`	`String` (`UUID`)	FK → `students.id`, NOT NULL	Student who reported the score; mapped to `student_id`
`fatigueScore`	`Int` (`SmallInt`)	NOT NULL, CHECK (1 ≤ value ≤ 5)	Self-reported fatigue score; mapped to `fatigue_score`
`recordedAtUtc`	`DateTime` (`Timestamptz`)	NOT NULL, default `now()`	UTC timestamp of when the score was recorded; mapped to `recorded_at_utc`

Relations: belongs to Session and Student.Indexes: session_id, student_id.

// ─── FatigueRecord ───────────────────────────────────────────────────────────
// Puntuación de fatiga autoreportada por el estudiante en una sesión.
// fatigue_score CHECK (1 ≤ value ≤ 5) se aplica en la migración SQL.
// Requisito 7.3
model FatigueRecord {
  id             String   @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  sessionId      String   @map("session_id") @db.Uuid
  studentId      String   @map("student_id") @db.Uuid
  fatigueScore   Int      @map("fatigue_score") @db.SmallInt
  recordedAtUtc  DateTime @default(now()) @map("recorded_at_utc") @db.Timestamptz

  // Relations
  session  Session @relation(fields: [sessionId], references: [id])
  student  Student @relation(fields: [studentId], references: [id])

  @@index([sessionId])
  @@index([studentId])
  @@map("fatigue_records")
}

Task

A Task represents an academic activity defined by the student — for example, “Submit essay” or “Study for Thursday’s exam”. Tasks are never physically deleted; instead, they are soft-deleted by setting isDeleted to true. This logical deletion strategy preserves the full audit trail of associated micro-objectives and fatigue records. The deadline column drives both the ascending sort order returned by the Tasks API and the notification schedule managed by the Notification_Service.

Field Name	Type	Constraints	Description
`id`	`String` (`UUID`)	PK, `gen_random_uuid()`	Unique identifier for the task
`studentId`	`String` (`UUID`)	FK → `students.id`, NOT NULL	Owning student; mapped to `student_id`
`name`	`String` (`VarChar(255)`)	NOT NULL	Task name
`description`	`String?` (`Text`)	NULLABLE	Optional description of the task
`deadline`	`DateTime` (`Timestamptz`)	NOT NULL	UTC deadline for the task
`isDeleted`	`Boolean`	NOT NULL, default `false`	Logical deletion flag; mapped to `is_deleted`
`createdAt`	`DateTime` (`Timestamptz`)	NOT NULL, default `now()`	UTC timestamp of task creation; mapped to `created_at`
`updatedAt`	`DateTime` (`Timestamptz`)	NOT NULL, auto-updated by Prisma	UTC timestamp of last update; mapped to `updated_at`

Relations: belongs to Student; one-to-many with MicroObjective and NotificationLog.Indexes: student_id, is_deleted, deadline.

// ─── Task ────────────────────────────────────────────────────────────────────
// Actividad académica definida por el estudiante.
// Eliminación lógica mediante is_deleted.
// Requisito 7.4
model Task {
  id          String   @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  studentId   String   @map("student_id") @db.Uuid
  name        String   @db.VarChar(255)
  description String?
  deadline    DateTime @db.Timestamptz
  isDeleted   Boolean  @default(false) @map("is_deleted")
  createdAt   DateTime @default(now()) @map("created_at") @db.Timestamptz
  updatedAt   DateTime @updatedAt @map("updated_at") @db.Timestamptz

  // Relations
  student          Student          @relation(fields: [studentId], references: [id])
  microObjectives  MicroObjective[]
  notificationLogs NotificationLog[]

  @@index([studentId])
  @@index([isDeleted])
  @@index([deadline])
  @@map("tasks")
}

MicroObjective

A MicroObjective is an atomic, actionable unit of work generated by the Task_Decomposer when a student’s fatigue score is 4 or higher. Each micro-objective is scoped to at most 25 minutes of effort, making high-fatigue study sessions more approachable. The isAuditOnly flag is set to true when the parent task is soft-deleted, converting the record into a read-only audit entry. The database enforces the estimated-minutes upper bound with a CHECK constraint.

Field Name	Type	Constraints	Description
`id`	`String` (`UUID`)	PK, `gen_random_uuid()`	Unique identifier for the micro-objective
`taskId`	`String` (`UUID`)	FK → `tasks.id`, NOT NULL	Parent task that was decomposed; mapped to `task_id`
`sessionId`	`String` (`UUID`)	FK → `sessions.id`, NOT NULL	Session during which this objective was generated; mapped to `session_id`
`content`	`String` (`Text`)	NOT NULL	Description of the micro-objective
`estimatedMinutes`	`Int` (`SmallInt`)	NOT NULL, CHECK (0 < value ≤ 25)	Estimated completion time in minutes; mapped to `estimated_minutes`
`isCompleted`	`Boolean`	NOT NULL, default `false`	Whether the student has completed this objective; mapped to `is_completed`
`isAuditOnly`	`Boolean`	NOT NULL, default `false`	Set to `true` when the parent task is soft-deleted; record becomes read-only; mapped to `is_audit_only`
`createdAt`	`DateTime` (`Timestamptz`)	NOT NULL, default `now()`	UTC timestamp of creation; mapped to `created_at`

Relations: belongs to Task and Session.Indexes: task_id, session_id, is_completed.

// ─── MicroObjective ──────────────────────────────────────────────────────────
// Unidad de trabajo atómica generada por el Task_Decomposer (fatiga >= 4).
// estimated_minutes CHECK (0 < value ≤ 25) se aplica en la migración SQL.
// Requisito 7.4
model MicroObjective {
  id               String   @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  taskId           String   @map("task_id") @db.Uuid
  sessionId        String   @map("session_id") @db.Uuid
  content          String
  estimatedMinutes Int      @map("estimated_minutes") @db.SmallInt
  isCompleted      Boolean  @default(false) @map("is_completed")
  isAuditOnly      Boolean  @default(false) @map("is_audit_only")
  createdAt        DateTime @default(now()) @map("created_at") @db.Timestamptz

  // Relations
  task    Task    @relation(fields: [taskId], references: [id])
  session Session @relation(fields: [sessionId], references: [id])

  @@index([taskId])
  @@index([sessionId])
  @@index([isCompleted])
  @@map("micro_objectives")
}

NotificationLog

A NotificationLog records every notification dispatched (or attempted) by the Notification_Service for deadline reminders. The status column is constrained to one of three values — pending, sent, or failed — enforced by a database-level CHECK constraint. The service retries up to three times before marking a record as failed. A maximum of three sent notifications can be dispatched to any student within a 24-hour window, and no reminders are sent while a student has an active EMA session.

Field Name	Type	Constraints	Description
`id`	`String` (`UUID`)	PK, `gen_random_uuid()`	Unique identifier for the notification log entry
`studentId`	`String` (`UUID`)	FK → `students.id`, NOT NULL	Recipient student; mapped to `student_id`
`taskId`	`String` (`UUID`)	FK → `tasks.id`, NOT NULL	Task that triggered the notification; mapped to `task_id`
`status`	`String` (`VarChar(20)`)	NOT NULL, CHECK IN (`'pending'`, `'sent'`, `'failed'`)	Delivery status of the notification
`attemptCount`	`Int` (`SmallInt`)	NOT NULL, default `0`	Number of dispatch attempts made; mapped to `attempt_count`
`dispatchedAtUtc`	`DateTime` (`Timestamptz`)	NOT NULL	UTC timestamp of the dispatch attempt; mapped to `dispatched_at_utc`
`updatedAt`	`DateTime` (`Timestamptz`)	NOT NULL, auto-updated by Prisma	UTC timestamp of last status update; mapped to `updated_at`

Relations: belongs to Student and Task.Indexes: student_id, task_id, status.

// ─── NotificationLog ─────────────────────────────────────────────────────────
// Registro de notificaciones enviadas al estudiante por tareas próximas.
// status CHECK IN ('pending','sent','failed') se aplica en la migración SQL.
// Requisito 7.5
model NotificationLog {
  id              String   @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  studentId       String   @map("student_id") @db.Uuid
  taskId          String   @map("task_id") @db.Uuid
  status          String   @db.VarChar(20)
  attemptCount    Int      @default(0) @map("attempt_count") @db.SmallInt
  dispatchedAtUtc DateTime @map("dispatched_at_utc") @db.Timestamptz
  updatedAt       DateTime @updatedAt @map("updated_at") @db.Timestamptz

  // Relations
  student Student @relation(fields: [studentId], references: [id])
  task    Task    @relation(fields: [taskId], references: [id])

  @@index([studentId])
  @@index([taskId])
  @@index([status])
  @@map("notification_logs")
}

Database constraints

Three CHECK constraints are enforced at the PostgreSQL level in addition to Prisma’s application-layer validation. These constraints act as a final safety net — rows violating them will be rejected by the database engine regardless of the caller.

Table	Column	Constraint	Enforced in
`fatigue_records`	`fatigue_score`	`fatigue_score >= 1 AND fatigue_score <= 5`	Migration SQL + EMA_Bot validation layer
`micro_objectives`	`estimated_minutes`	`estimated_minutes > 0 AND estimated_minutes <= 25`	Migration SQL + Task_Decomposer validation layer
`notification_logs`	`status`	`status IN ('pending', 'sent', 'failed')`	Migration SQL + Notification_Service validation layer

The CHECK constraints are introduced in migration 20240101000000_init as part of the initial schema. A subsequent migration, 20260721061053_init, adds the additional non-FK indexes. Relevant excerpts:

-- CHECK constraints from 20240101000000_init/migration.sql

CONSTRAINT "fatigue_records_fatigue_score_check"
  CHECK ("fatigue_score" >= 1 AND "fatigue_score" <= 5),

CONSTRAINT "micro_objectives_estimated_minutes_check"
  CHECK ("estimated_minutes" > 0 AND "estimated_minutes" <= 25),

CONSTRAINT "notification_logs_status_check"
  CHECK ("status" IN ('pending', 'sent', 'failed'))

-- Index additions from 20260721061053_init/migration.sql

CREATE INDEX "micro_objectives_is_completed_idx" ON "micro_objectives"("is_completed");
CREATE INDEX "notification_logs_status_idx"       ON "notification_logs"("status");
CREATE INDEX "sessions_is_active_idx"             ON "sessions"("is_active");
CREATE INDEX "tasks_is_deleted_idx"               ON "tasks"("is_deleted");
CREATE INDEX "tasks_deadline_idx"                 ON "tasks"("deadline");

Connection pool configuration

The Prisma client is configured with a connection pool of min: 2, max: 10 (Requirement 7.5). Keeping a minimum of two connections ensures that the first requests after a cold start do not need to wait for a handshake, while the cap of ten connections prevents resource exhaustion in the containerised environment.

Prisma version note

MindFlow uses Prisma 7 with the @prisma/adapter-pg adapter. As required by Prisma 7’s configuration model, the datasource block in schema.prisma does not contain a url field. The DATABASE_URL environment variable is consumed exclusively through prisma.config.ts, keeping connection credentials out of the schema file and out of source control.

Logical deletion — tasks are never physically removed. When a student deletes a task, the Task row’s isDeleted flag is set to true and the record remains in the database permanently. All MicroObjective rows linked to that task have their isAuditOnly flag set to true, converting them into read-only audit entries. This design preserves the complete history of decomposition events and fatigue records associated with the task, which is required for audit-trail integrity (Requirement 2.6).

After modifying schema.prisma, regenerate the Prisma client from the monorepo root:

npm run prisma:generate --workspace @mindflow/backend

Overview

Endpoints

Data Models

MindFlow data models: Prisma schema and PostgreSQL reference

Database constraints

Connection pool configuration

Prisma version note

Build docs developers (and LLMs) love

Overview

Endpoints

Data Models

Documentation Index

​Database constraints

​Connection pool configuration

​Prisma version note

Build docs developers (and LLMs) love

Database constraints

Connection pool configuration

Prisma version note