Skip to content

Database

Jump to bottom
Nishanth S edited this page May 8, 2026 · 5 revisions

Database

Overview

Teambuilder uses SQLite through Prisma ORM.

Last updated: May 2026

Current Data Model

The current Prisma models are:

  • User
  • Partner
  • Project
  • Team
  • Student
  • Choice
  • Semester

Note on TeamToStudent:

  • The old documentation listed a TeamToStudent table.
  • In the current Prisma schema, Team <-> Student is an implicit many-to-many relation, so Prisma manages the join table automatically.

Model Details

User

Purpose:

  • Basic user identity record.

Key fields:

  • id (UUID, primary key)
  • email (unique)
  • createdAt
  • updatedAt

Mapped table name:

  • users

Partner

Purpose:

  • External organization sponsoring projects.

Key fields:

  • id (UUID, primary key)
  • name
  • contactName
  • contactEmail
  • createdAt
  • updatedAt

Relations:

  • One Partner has many Projects.

Mapped table name:

  • partners

Project

Purpose:

  • EPICS project definition and metadata.

Key fields:

  • id (UUID, primary key)
  • name
  • description
  • type (enum: SOFTWARE, HARDWARE, BOTH)
  • status (enum: NEW, RETURNING, COMPLETE, WITHDRAWN, HOLD)
  • meetingDay (enum: WEDNESDAY, THURSDAY, BOTH; optional)
  • repoURL
  • partnerId
  • createdAt
  • updatedAt

Relations:

  • Project belongs to one Partner.
  • Project has many Teams.
  • Project has many Choices.

Mapped table name:

  • projects

Team

Purpose:

  • A project instance for a specific semester/day with student assignments.

Key fields:

  • id (UUID, primary key)
  • projectId
  • semesterId
  • meetingDay (default: THURSDAY)
  • createdAt
  • updatedAt

Relations:

  • Team belongs to one Project.
  • Team belongs to one Semester.
  • Team has many Students (implicit many-to-many).

Constraints:

  • Unique on (projectId, semesterId, meetingDay)

Mapped table name:

  • teams

Student

Purpose:

  • Student profile used for team generation and assignment.

Key fields:

  • id (UUID, primary key)
  • github (optional, unique)
  • discord (optional, unique)
  • email (optional, unique)
  • netID (unique)
  • firstName
  • lastName
  • major
  • year (enum: FRESHMAN, SOPHOMORE, JUNIOR, SENIOR)
  • class (string, used by algorithm as 2200/3200 semantic level)
  • enrollment (optional)
  • meetingDay (optional)
  • status (enum: ACTIVE, INACTIVE)
  • createdAt
  • updatedAt

Relations:

  • Student has many Teams (implicit many-to-many).
  • Student has many Choices.

Mapped table name:

  • students

Choice

Purpose:

  • Student preference ranking for projects.

Key fields:

  • id (UUID, primary key)
  • rank (integer)
  • studentId
  • projectId
  • createdAt
  • updatedAt

Relations:

  • Choice belongs to one Student.
  • Choice belongs to one Project.

Mapped table name:

  • choices

Semester

Purpose:

  • Academic term key used for grouping teams.

Key fields:

  • id (UUID, primary key)
  • year (integer)
  • season (enum: SPRING, SUMMER, FALL)
  • createdAt
  • updatedAt

Relations:

  • Semester has many Teams.

Constraints:

  • Unique on (year, season)

Mapped table name:

  • semesters

Relationship Summary

High-level relationships:

  • Partner 1 -> many Project
  • Project 1 -> many Team
  • Semester 1 -> many Team
  • Student many <-> many Team (implicit join table)
  • Student 1 -> many Choice
  • Project 1 -> many Choice

Demographics Status (Important)

Old wiki context (May 2025) stated that demographics lived in Semester fields such as:

  • African_American, Asian, Hispanic, International, Other, White, Male, Female, Total

That is no longer true in the current schema.

Current reality:

  • Semester now stores only year and season metadata.
  • Demographics aggregate columns are not present in Semester.
  • The current demographics endpoints still contain legacy assumptions and need rewrite for the current schema.

Relevant files:

Migrations

Migration history exists under prisma/migrations, including:

  • Initial schema
  • Semester creation/uniqueness updates
  • Team uniqueness updates
  • Choice table creation
  • Meeting day additions for student/project/team

Operational note:

  • Every migration directory should contain a migration.sql file so Prisma migrate commands do not fail.

Reset and Seed Behavior

Reset API

Endpoint:

  • POST /api/database/reset

Behavior:

  • Clears all application tables in dependency-safe order.
  • Does not reseed default data.

Implementation:

Seed Script

Seed command source:

Behavior:

  • Generates random semesters, partners, projects, students, teams, and choices.
  • Inserts with createMany operations.

Practical Notes for Developers

  • If you change model fields, add a new Prisma migration and regenerate Prisma client.
  • Keep enum usage in API handlers synchronized with schema enums.
  • Verify algorithm inputs remain aligned with Student/Project fields used by the CPSAT pipeline.
  • Treat demographics endpoints as legacy until rewritten to compute data from current normalized tables.

Quick References

Wiki

Website Functionality

Demographics Workflows

Project Plans

Resources

Clone this wiki locally