Database Schema (Prisma)

Relevant source files

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

• backend/prisma/schema.prisma
• backend/package.json
• backend/test/setup-prisma.ts
• e2e/helpers.ts
• backend/src/api/users/users.controller.spec.ts
• backend/src/api/classes/classes.controller.spec.ts
• frontend/src/components/task/TaskForm.tsx

The Collimator backend uses Prisma as its Object-Relational Mapper (ORM) to define and interact with a PostgreSQL database. The schema, defined in backend/prisma/schema.prisma, is the single source of truth for the application's data models. It describes all the entities, their fields, and the relationships between them, covering user management, class structures, educational content like tasks and lessons, and student progress tracking.

This document provides a detailed overview of the database schema, including the core models, their relationships, and associated enums. The database is configured to use PostgreSQL via the DATABASE_URL environment variable.

Sources: backend/prisma/schema.prisma:1-12

Schema Tooling and Configuration

The project utilizes Prisma Client JS for database access and Prisma Migrate for schema migrations. Key scripts in backend/package.json manage the database lifecycle.

Script	Description
prisma:migrate	Runs Prisma migrations.
prisma:migrate:dev	Creates and applies a new migration in development.
prisma:generate	Generates the Prisma Client based on the schema.
prisma:seed	Seeds the database with initial data using prisma/seed/index.ts.

Sources: backend/package.json:11-14, backend/package.json:100-102

The Prisma schema file configures the database provider and the client generator.

// backend/prisma/schema.prisma

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["typedSql"]
}

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

Sources: backend/prisma/schema.prisma:4-12

Entity Relationship Diagram (ERD)

This diagram provides a high-level overview of the main entities in the database and their relationships.

Core Models

User and Authentication

This group of models handles users (teachers/admins) and their authentication details, including cryptographic keys for student data encryption.

User Model

Represents a teacher or an administrator. Admins are also considered teachers.

Field	Type	Description
id	Int	Primary Key
name	String?	User's name
oidcSub	String?	OpenID Connect subject identifier
email	String	User's email, unique per provider
authenticationProvider	AuthenticationProvider	The OIDC provider (e.g., MICROSOFT)
type	UserType	TEACHER or ADMIN

Sources: backend/prisma/schema.prisma:18-32

KeyPair and EncryptedPrivateKey Models

These models store a teacher's public/private key pair used for encrypting student data. The private key is stored encrypted with a password-derived key.

Model	Field	Type	Description
KeyPair	publicKey	Bytes	The teacher's public key.
KeyPair	publicKeyFingerprint	String	SHA-512 fingerprint of the public key for lookups.
KeyPair	teacherId	Int	Foreign key to the User model.
EncryptedPrivateKey	encryptedPrivateKey	Bytes	The private key, encrypted.
EncryptedPrivateKey	publicKeyId	Int	Foreign key to the KeyPair model.

Sources: backend/prisma/schema.prisma:34-54

Class and Student Management

These models define the structure of classes and the students within them. Students can be either authenticated (part of a class) or anonymous (part of a session).

Class Model

A class is managed by a teacher and contains a set of authenticated students.

Field	Type	Description
id	Int	Primary Key
name	String	The name of the class.
teacherId	Int	Foreign key to the User who teaches the class.

Sources: backend/prisma/schema.prisma:56-63

Student, AuthenticatedStudent, and AnonymousStudent Models

The Student model is a central entity. An AuthenticatedStudent is linked to a Class and has a pseudonym for anonymization. An AnonymousStudent is linked to a specific Session.

Model	Field	Type	Description
Student	id	Int	Primary Key.
AuthenticatedStudent	studentId	Int	Foreign key to Student.
AuthenticatedStudent	pseudonym	Bytes	Anonymized, teacher-computed pseudonym.
AuthenticatedStudent	classId	Int	Foreign key to Class.
AnonymousStudent	studentId	Int	Foreign key to Student.
AnonymousStudent	sessionId	Int	Foreign key to Session.

Sources: backend/prisma/schema.prisma:65-94

Educational Content: Lessons and Tasks

These models represent the educational materials created by teachers.

Lesson Model

A Lesson is a collection of Tasks that can be assigned to a class via a Session.

Field	Type	Description
id	Int	Primary Key
title	String	Title of the lesson.
description	String	Description of the lesson.
visibility	LessonVisibility	PUBLIC or PRIVATE.
creatorId	Int?	Foreign key to the User who created it.

Sources: backend/prisma/schema.prisma:96-107

Task Model

A Task is an individual assignment, such as a Scratch project.

Field	Type	Description
id	Int	Primary Key
title	String	Title of the task.
type	TaskType	The type of task (e.g., SCRATCH).
data	Bytes	The task data itself (e.g., a template file).
creatorId	Int?	Foreign key to the User who created it.

Sources: backend/prisma/schema.prisma:126-139

Session and Solution Tracking

These models are used to track a class's interaction with lessons and tasks through sessions, and to store student solutions.

Session Model

A Session is an active instance of a Lesson for a specific Class.

Field	Type	Description
id	Int	Primary Key
title	String	Title of the session.
classId	Int	Foreign key to the Class.
basedOnLessonId	Int?	Foreign key to the Lesson this session is based on.
status	SessionStatus	CREATED, ONGOING, PAUSED, or FINISHED.
isAnonymous	Boolean	If true, students participate anonymously.

Sources: backend/prisma/schema.prisma:109-124

Solution and StudentSolution Models

A Solution represents a unique version of a submitted project for a task, identified by its content hash. StudentSolution links a Student to a Solution for a specific Session and Task.

Model	Field	Type	Description
Solution	taskId, hash	Int, Bytes	Composite Primary Key.
Solution	data	Bytes	The content of the solution file.
StudentSolution	id	Int	Primary Key.
StudentSolution	studentId	Int	Foreign key to Student.
StudentSolution	sessionId	Int	Foreign key to Session.
StudentSolution	taskId, solutionHash	Int, Bytes	Foreign key to Solution.

Sources: backend/prisma/schema.prisma:159-173, backend/prisma/schema.prisma:190-205

Student Activity Logging

The StudentActivity model logs discrete events performed by a student during a session.

StudentActivity Model

This model captures timestamped actions like starting a task, running a solution, or closing the task interface.

Field	Type	Description
id	Int	Primary Key
type	StudentActivityType	The type of activity (e.g., TASK_STARTED).
happenedAt	DateTime	Client-side timestamp of when the event occurred.
studentId	Int	Foreign key to Student.
sessionId	Int	Foreign key to Session.
taskId	Int	Foreign key to Task.

Sources: backend/prisma/schema.prisma:243-266

Database Management in Testing

The project includes robust testing infrastructure that manages the database state.

Integration Testing (jest)

For backend integration tests, a beforeAll hook is used to manage the database. It first pushes the latest schema and then uses a raw SQL query to truncate all tables and restart identity columns, ensuring a clean state before seeding.

// backend/test/setup-prisma.ts

async function clearDatabase(prisma: PrismaClient): Promise<void> {
  await prisma.$executeRaw`
    DO
    $func$
    BEGIN
      EXECUTE (
        SELECT 'TRUNCATE TABLE ' || string_agg(quote_ident(table_name), ', ') || ' RESTART IDENTITY CASCADE'
        FROM   information_schema.tables
        WHERE  table_schema = 'public'
        AND    table_type = 'BASE TABLE'
      );
    END
    $func$;
  `;
}

beforeAll(async () => {
  await promisify(exec)("npx prisma db push --accept-data-loss");
  const prisma = new PrismaClient();
  await prisma.$connect();
  await clearDatabase(prisma);
  await seedDatabase(prisma);
}, 60000);

Sources: backend/test/setup-prisma.ts:1-32

End-to-End Testing (playwright)

For E2E tests, a more sophisticated approach is taken. Each test worker creates a clone of a template database. This ensures test isolation and allows for parallel execution. The cloned database is created before the tests run and dropped afterward.

// e2e/helpers.ts

// create a new database based on the template database
await client.connect();
await client.query(`DROP DATABASE IF EXISTS "${uniqueDbName}"`);
await client.query(
  `CREATE DATABASE "${uniqueDbName}" TEMPLATE "${postgresConfig.database}"`,
);
await client.end();

Sources: e2e/helpers.ts:102-108

Conclusion

The Prisma schema provides a well-structured and relational model for the Collimator application's data. It effectively separates concerns between user identity, class management, educational content, and student progress. The use of features like enums, unique constraints, and cascading deletes enforces data integrity at the database level. The schema is designed to support key application features, including student anonymization through pseudonyms, tracking of student solutions over time, and detailed activity logging for analysis.