Assignment 2: PostgreSQL with Prisma ORM

Release Date: February 1, 2026

Due Date: February 15, 2026, 11:59 PM EST

Weight: 12.5% of final grade

Overview

Building upon Assignment 1, you will migrate the backend of the Paper Management System to:

PostgreSQL as the database
Prisma ORM as the database client
TypeScript for type-safe backend development

You will also implement a many-to-many relationship between papers and authors, and build REST API endpoints for managing both.

Learning Objectives

After completing this assignment, you will be able to:

Build RESTful APIs in Express using TypeScript
Model and query many-to-many relationships with Prisma
Run database migrations using Prisma Migrate
Implement input validation and error handling with a clearly defined scope
Test a TypeScript backend using Jest + Supertest

Requirements

Before you start coding

After reading the assignment requirements below, pause and think about what you expect will be the hardest part of this assignment. This will help you answer Q1 in reasoning.md, which you will submit together with your code.

See the AI Usage Policy for details.

Starter Code

Download the starter code archive:


curl -o starter-code.tar.gz https://www.eecg.utoronto.ca/~cying/courses/ece1724-web/assignments/assignment-2/starter-code.tar.gz

Alternatively, download the archive here.

The archive includes TypeScript setup and sample tests (so you do not need to download tests separately).

- - schema.prisma
- - - prisma.ts
  - - authors.ts
    - papers.ts
  - database.ts
  - middleware.ts
  - routes.ts
  - server.ts
  - types.ts
- - sample.author.test.ts
  - sample.paper.test.ts
- jest.config.mjs
- package.json
- prisma.config.ts
- tsconfig.json

You only need to modify TODOs in:

prisma/schema.prisma
src/routes/authors.ts
src/routes/papers.ts
src/database.ts
src/middleware.ts
src/routes.ts
src/types.ts

Do not modify:

src/server.ts
Jest/Prisma/TypeScript config files

Database Migration

Create a new PostgreSQL database paper_management for the project

In prisma/schema.prisma, set up Prisma with the following schema:


 model Paper {
    id          Int      @id @default(autoincrement())
    title       String
    publishedIn String
    year        Int
    createdAt   DateTime @default(now())
    updatedAt   DateTime @updatedAt
    authors     Author[] @relation("PaperToAuthor")
 }
 
 model Author {
    id          Int      @id @default(autoincrement())
    name        String
    email       String?
    affiliation String?
    createdAt   DateTime @default(now())
    updatedAt   DateTime @updatedAt
    papers      Paper[]  @relation("PaperToAuthor")
 }

API Routes

Implement paper routes in routes/papers.ts:


GET    /api/papers        - List all papers (with authors)
GET    /api/papers/:id    - Get paper details
POST   /api/papers        - Create new paper
PUT    /api/papers/:id    - Update paper
DELETE /api/papers/:id    - Delete paper

Detailed requirements of paper routes are in the next section.

Implement author routes in routes/authors.ts:


GET    /api/authors       - List all authors (with papers)
GET    /api/authors/:id   - Get author details
POST   /api/authors       - Create new author
PUT    /api/authors/:id   - Update author
DELETE /api/authors/:id   - Delete author

Detailed requirements of author routes are in the next section.

Detailed Requirements of API Endpoints

Paper Routes

GET /api/papers

Retrieves a list of papers, ordered by ascending id, along with their associated authors (also ordered by ascending id), with optional filtering and pagination.

Query Parameters:

year: integer - Filter papers by exact year match
publishedIn: string - Filter by case-insensitive partial match
limit: integer - Maximum number of results (default: 10, max: 100)
offset: integer - Number of results to skip (default: 0)

Input Validation:

year (if provided):
- Must be an integer greater than 1900
limit:
- Must be a positive integer
- Maximum value: 100
- Default: 10
offset:
- Must be a non-negative integer
- Default: 0

Filter Behavior:

After passing validation, process query parameters exactly as provided, without trimming (e.g. if publishedIn is a whitespace-only string " ", it should be treated as a string, not as an unprovided parameter)
Multiple filters are combined with AND logic
If no filters are provided, return all papers (subject to pagination)
If no papers match the filters, return empty array []

Tip

Use Prisma’s built-in pagination with skip and take

Success Response:

Status: 200 OK
Returns an object containing an array of paper objects, ordered by ascending id, each with its associated authors, also ordered by ascending id


{
  "papers": [
    {
      "id": 1,
      "title": "Example Paper",
      "publishedIn": "Conference Name",
      "year": 2026,
      "createdAt": "2026-02-09T00:00:00.000Z",
      "updatedAt": "2026-02-09T00:00:00.000Z",
      "authors": [
        {
          "id": 1,
          "name": "John Doe",
          "email": "john@example.com",
          "affiliation": "University of Toronto",
          "createdAt": "2026-02-09T00:00:00.000Z",
          "updatedAt": "2026-02-09T00:00:00.000Z"
        },
        {
          "id": 2,
          "name": "Jane Smith",
          "email": null,
          "affiliation": null,
          "createdAt": "2026-02-09T00:00:00.000Z",
          "updatedAt": "2026-02-09T00:00:00.000Z"
        }
      ]
    }
    // ... more papers
  ],
  "total": 42, // Total number of papers matching the filters
  "limit": 10, // Current limit
  "offset": 0 // Current offset
}

Note

Author fields email and affiliation may be null.

Error Response:

Invalid Query Parameters (400 Bad Request):


{
  "error": "Validation Error",
  "message": "Invalid query parameter format"
}

Simplified error handling for query parameters

Whether one parameter is invalid or multiple are invalid, the response is always the same:


{
  "error": "Validation Error",
  "message": "Invalid query parameter format"
}

For example, both these requests:

GET /api/papers?year=1000 (single invalid parameter)
GET /api/papers?year=1000&offset=-1 (multiple invalid parameters)

will receive the same error response shown above.

GET /api/papers/:id

Retrieves a specific paper by its ID, including its authors ordered by ascending id.

URL Parameters:

id: integer - The ID of the paper to retrieve

Validation Process:

Validate ID format:
- Must be a positive integer
Check paper existence:
- Must exist in the database

Success Response:

Status: 200 OK
Returns a single paper object with its authors, ordered by ascending id


{
  "id": 1,
  "title": "Example Paper",
  "publishedIn": "Conference Name",
  "year": 2026,
  "createdAt": "2026-02-09T00:00:00.000Z",
  "updatedAt": "2026-02-09T00:00:00.000Z",
  "authors": [
    {
      "id": 1,
      "name": "John Doe",
      "email": "john@example.com",
      "affiliation": "University of Toronto",
      "createdAt": "2026-02-09T00:00:00.000Z",
      "updatedAt": "2026-02-09T00:00:00.000Z"
    },
    {
      "id": 2,
      "name": "Jane Smith",
      "email": null,
      "affiliation": null,
      "createdAt": "2026-02-09T00:00:00.000Z",
      "updatedAt": "2026-02-09T00:00:00.000Z"
    }
  ]
}

Error Responses:

Invalid ID Format (400 Bad Request):


{
  "error": "Validation Error",
  "message": "Invalid ID format"
}

Paper Not Found (404 Not Found):
```
{
  "error": "Paper not found"
}
```

POST /api/papers

Creates a new paper with its authors.

Required fields:
- title: non-empty string
- publishedIn: non-empty string
- year: integer greater than 1900
- authors: array of author objects, at least one author required
  - Each author object must have:
    - name: non-empty string
    - email: optional string
    - affiliation: optional string
Automatic fields:
- id: automatically generated primary key
- createdAt: automatically set to current timestamp
- updatedAt: automatically set to current timestamp
Input Validation:
- Ensure all required fields are present
- Validate year is a valid integer greater than 1900
- Validate authors array is non-empty
- Email validation is not required in this assignment
- Return all validation errors together if multiple exist

Author Handling:

For each author in the request:

If an author with exactly matching name, email, and affiliation exists in the database, reuse that author
If no exact match is found, create a new author
If multiple exact matches exist, choose the one with the lowest ID

Note

When using Prisma ORM, always specify an explicit ordering (e.g. orderBy: { id: "asc" }) when selecting existing records (e.g. using findMany() or findFirst()) to ensure consistent behavior across different database engines. If no ordering is specified, the retrieval order may vary depending on the database system and execution plan, leading to unpredictable results.

Request Body:


{
  "title": "Required, string",
  "publishedIn": "Required, string",
  "year": "Required, integer > 1900",
  "authors": [
    {
      "name": "Required, string",
      "email": "Optional, string",
      "affiliation": "Optional, string"
    }
  ]
}

A Request Example:


{
  "title": "Example Paper Title",
  "publishedIn": "ICSE 2025",
  "year": 2025,
  "authors": [
    {
      "name": "John Doe",
      "email": "john@mail.utoronto.ca",
      "affiliation": "University of Toronto"
    },
    {
      "name": "Jane Smith",
      "email": null,
      "affiliation": "University A"
    }
  ]
}

Success Response:

Status: 201 Created
Returns the created paper with all fields including automatic ones and authors

A Success Response Example:


{
  "id": 1,
  "title": "Example Paper Title",
  "publishedIn": "ICSE 2025",
  "year": 2025,
  "createdAt": "2026-02-09T10:30:00.000Z",
  "updatedAt": "2026-02-09T10:30:00.000Z",
  "authors": [
    {
      "id": 1,
      "name": "John Doe",
      "email": "john@mail.utoronto.ca",
      "affiliation": "University of Toronto",
      "createdAt": "2026-02-09T10:30:00.000Z",
      "updatedAt": "2026-02-09T10:30:00.000Z"
    },
    {
      "id": 2,
      "name": "Jane Smith",
      "email": null,
      "affiliation": "University A",
      "createdAt": "2026-02-09T10:30:00.000Z",
      "updatedAt": "2026-02-09T10:30:00.000Z"
    }
  ]
}

Error Responses:

Missing Required Fields (400 Bad Request):


{
  "error": "Validation Error",
  "messages": [
    "Title is required",
    "Published venue is required",
    "Published year is required",
    "At least one author is required"
  ]
}

Invalid Fields (400 Bad Request):


{
  "error": "Validation Error",
  "messages": [
    "Valid year after 1900 is required",
    "Author name is required"
  ]
}

Note: Include “Author name is required” only once in the messages array, even if multiple authors are missing the name field.

PUT /api/papers/:id

Updates an existing paper and its authors by ID.

URL Parameters:

id: integer - The ID of the paper to update

Input Validation:

All validation rules from POST /api/papers apply
ID must be valid and exist
At least one author must be provided

Author Handling:

The provided authors list completely replaces the existing authors
For each author in the request, reuse an existing exact match (name & email & affiliation) or create a new one
Authors previously associated with this paper but not included in the update will be disconnected from the paper

Request Body:


{
  "title": "Required, string",
  "publishedIn": "Required, string",
  "year": "Required, integer > 1900",
  "authors": [
    {
      "name": "Required, string",
      "email": "Optional, string",
      "affiliation": "Optional, string"
    }
  ]
}

A Request Example:


{
  "title": "Updated Paper Title",
  "publishedIn": "IEEE TSE",
  "year": 2025,
  "authors": [
    {
      "name": "John Doe",
      "email": "john@mail.utoronto.ca",
      "affiliation": "University of Toronto"
    },
    {
      "name": "Jane Smith",
      "email": null,
      "affiliation": "University A"
    }
  ]
}

Success Response:

Status: 200 OK
Returns the updated paper with all fields including authors
updatedAt timestamp is automatically updated
createdAt timestamp remains unchanged


{
  "id": 1,
  "title": "Updated Paper Title",
  "publishedIn": "IEEE TSE",
  "year": 2025,
  "createdAt": "2026-02-09T10:30:00.000Z",
  "updatedAt": "2026-02-09T11:20:00.000Z",
  "authors": [
    {
      "id": 1,
      "name": "John Doe",
      "email": "john@mail.utoronto.ca",
      "affiliation": "University of Toronto",
      "createdAt": "2026-02-09T10:30:00.000Z",
      "updatedAt": "2026-02-09T10:30:00.000Z"
    },
    {
      "id": 2,
      "name": "Jane Smith",
      "email": null,
      "affiliation": "University A",
      "createdAt": "2026-02-09T10:30:00.000Z",
      "updatedAt": "2026-02-09T10:30:00.000Z"
    }
  ]
}

Error Responses:

Invalid ID Format (400 Bad Request):


{
  "error": "Validation Error",
  "message": "Invalid ID format"
}

Missing Required Fields (400 Bad Request):


{
  "error": "Validation Error",
  "messages": [
    "Title is required",
    "Published venue is required",
    "Published year is required",
    "At least one author is required"
  ]
}

Invalid Fields (400 Bad Request):


{
  "error": "Validation Error",
  "messages": [
    "Valid year after 1900 is required",
    "Author name is required"
  ]
}

Note: Include “Author name is required” only once in the messages array, even if multiple authors are missing the name field.

Paper Not Found (404 Not Found):
```
{
  "error": "Paper not found"
}
```

Note: Validation Processing Logic

When handling PUT requests with multiple potential errors (e.g. an invalid ID and an invalid request body), always validate the ID first before checking the request body. This is because:

Path parameters (like ID) should be validated before processing the request body
If the ID is invalid, there’s no need to validate the request body since the resource cannot be identified

The logical request processing flow is:

Validate the ID format
Validate the update data
Check if the resource exists

For example: A PUT /api/papers/0 request with the request body


{
  "title": "Updated Paper Title",
  "publishedIn": "IEEE TSE",
  "year": 2025
  // missing authors
}

The response should be:


{
  "error": "Validation Error",
  "message": "Invalid ID format"
}

Since the ID validation fails first, the request body is not checked.

DELETE /api/papers/:id

Deletes a specific paper by its ID.

URL Parameters:

id: integer - The ID of the paper to delete

Input Validation:

ID must be a valid positive integer
ID must exist in the database

Relationship Handling:

The paper will be deleted from the database
The paper’s relationships with authors will be removed
Authors themselves will not be deleted, which maintains database integrity while preserving author records that might be associated with other papers

Success Response:

Status: 204 No Content
No response body

Error Responses:

Invalid ID Format (400 Bad Request):


{
  "error": "Validation Error",
  "message": "Invalid ID format"
}

Paper Not Found (404 Not Found):
```
{
  "error": "Paper not found"
}
```

Author Routes

GET /api/authors

Retrieves a list of authors, ordered by ascending id, along with their associated papers, also ordered by ascending id, with optional filtering and pagination.

Query Parameters:

name: string - Filter by case-insensitive partial match of author name
affiliation: string - Filter by case-insensitive partial match of affiliation
limit: integer - Maximum number of results (default: 10, max: 100)
offset: integer - Number of results to skip (default: 0)

Input Validation:

name (if provided):
- Must be a non-empty string
affiliation (if provided):
- Must be a non-empty string
limit:
- Must be a positive integer
- Maximum value: 100
- Default: 10
offset:
- Must be a non-negative integer
- Default: 0

Filter Behavior:

Multiple filters are combined with AND logic
If no filters are provided, returns all authors (subject to pagination)
Returns empty array if no authors match the filters
Case-insensitive partial matching for text fields

Success Response:

Status: 200 OK
Returns an object containing an array of authors, ordered by ascending id, each with their associated papers, also ordered by ascending id


{
  "authors": [
    {
      "id": 1,
      "name": "John Doe",
      "email": "john@mail.utoronto.ca",
      "affiliation": "University of Toronto",
      "createdAt": "2026-02-09T10:30:00.000Z",
      "updatedAt": "2026-02-09T10:30:00.000Z",
      "papers": [
        {
          "id": 1,
          "title": "First Paper",
          "publishedIn": "Conference A",
          "year": 2023,
          "createdAt": "2026-02-09T10:30:00.000Z",
          "updatedAt": "2026-02-09T10:30:00.000Z"
        },
        {
          "id": 2,
          "title": "Second Paper",
          "publishedIn": "Journal B",
          "year": 2024,
          "createdAt": "2026-02-09T10:30:00.000Z",
          "updatedAt": "2026-02-09T10:30:00.000Z"
        }
      ]
    }
  ],
  "total": 42, // Total number of authors matching the filters
  "limit": 10, // Current limit
  "offset": 0 // Current offset
}

Error Response:

Invalid Query Parameters (400 Bad Request):


{
  "error": "Validation Error",
  "message": "Invalid query parameter format"
}

Note: Error handling for invalid query parameters in GET /api/authors is also simplfied (same idea as GET /api/papers).

GET /api/authors/:id

Retrieves a specific author by ID with their associated papers, ordered by ascending id.

URL Parameters:

id: integer - The ID of the author to retrieve

Validation Process:

Validate ID format:
- Must be a positive integer
Check author existence:
- Must exist in the database

Success Response:

Status: 200 OK
Returns a single author object with their papers, ordered by ascending id


{
  "id": 1,
  "name": "John Doe",
  "email": "john@university.edu",
  "affiliation": "University A",
  "createdAt": "2026-02-09T10:30:00.000Z",
  "updatedAt": "2026-02-09T10:30:00.000Z",
  "papers": [
    {
      "id": 1,
      "title": "First Paper",
      "publishedIn": "Conference A",
      "year": 2024,
      "createdAt": "2026-02-09T10:30:00.000Z",
      "updatedAt": "2026-02-09T10:30:00.000Z"
    },
    {
      "id": 2,
      "title": "Second Paper",
      "publishedIn": "Journal B",
      "year": 2025,
      "createdAt": "2026-02-09T10:30:00.000Z",
      "updatedAt": "2026-02-09T10:30:00.000Z"
    }
  ]
}

Error Responses:

Invalid ID Format (400 Bad Request):


{
  "error": "Validation Error",
  "message": "Invalid ID format"
}

Author Not Found (404 Not Found):
```
{
  "error": "Author not found"
}
```

POST /api/authors

Creates a new author.

Required field:
- name: non-empty string
Optional fields:
- email: string
- affiliation: string
Automatic fields:
- id: automatically generated primary key
- createdAt: automatically set to current timestamp
- updatedAt: automatically set to current timestamp
Input Validation:
- Ensure required field name is present
- Email validation is not required
- Duplicate author detection is not required
- Return all validation errors together if multiple exist

Request Body:


{
  "name": "Required, string",
  "email": "Optional, string",
  "affiliation": "Optional, string"
}

A Request Example:


{
  "name": "John Doe",
  "email": "john@mail.utoronto.ca",
  "affiliation": "University of Toronto"
}

Success Response:

Status: 201 Created
Returns the created author with all fields


{
  "id": 1,
  "name": "John Doe",
  "email": "john@mail.utoronto.ca",
  "affiliation": "University of Toronto",
  "createdAt": "2026-02-09T10:30:00.000Z",
  "updatedAt": "2026-02-09T10:30:00.000Z",
  "papers": [] // Initially empty array as this is a new author
}

Error Responses:

Missing Required Fields (400 Bad Request):


{
  "error": "Validation Error",
  "messages": ["Name is required"]
}

PUT /api/authors/:id

Updates an existing author by ID. Note that this endpoint only updates author information, not their paper associations.

URL Parameters:

id: integer - The ID of the author to update

Input Validation:

All validation rules from POST /api/authors apply
ID must be valid and exist
Name is required

Request Body:


{
  "name": "Required, string",
  "email": "Optional, string",
  "affiliation": "Optional, string"
}

A Request Example:


{
  "name": "John P. Doe",
  "email": "john.doe@university.edu",
  "affiliation": "University B"
}

Success Response:

Status: 200 OK
Returns the updated author with all fields
updatedAt timestamp is automatically updated
createdAt timestamp remains unchanged
Existing paper associations remain unchanged


{
  "id": 1,
  "name": "John P. Doe",
  "email": "john.doe@university.edu",
  "affiliation": "University B",
  "createdAt": "2026-02-09T10:30:00.000Z",
  "updatedAt": "2026-02-09T15:45:00.000Z",
  "papers": [
    {
      "id": 1,
      "title": "Existing Paper",
      "publishedIn": "Conference A",
      "year": 2023,
      "createdAt": "2026-02-09T10:30:00.000Z",
      "updatedAt": "2026-02-09T10:30:00.000Z"
    }
  ]
}

Note

This endpoint only updates author information, not their paper associations.

Paper associations are managed through the papers endpoints.

Error Responses:

Invalid ID Format (400 Bad Request):


{
  "error": "Validation Error",
  "message": "Invalid ID format"
}

Missing Required Fields (400 Bad Request):


{
  "error": "Validation Error",
  "messages": ["Name is required"]
}

Author Not Found (404 Not Found):
```
{
  "error": "Author not found"
}
```

Note: The validation processing logic is the same as PUT /api/papers/:id: when handling requests with multiple potential errors (e.g., an invalid ID and an invalid request body), always validate the ID first before checking the request body.

DELETE /api/authors/:id

Deletes a specific author by ID, with a constraint that an author cannot be deleted if they are the only author of one or more papers.

URL Parameters:

id: integer - The ID of the author to delete

Input Validation:

ID must be a positive integer
ID must exist in the database

Relationship Handling:

The author will be deleted from the database
The author’s relationships with papers will be removed
Papers themselves will not be deleted
Cannot delete an author if they are the only author of any paper (to maintain data integrity)

Success Response:

Status: 204 No Content
No response body

Error Responses:

Invalid ID Format (400 Bad Request):


{
  "error": "Validation Error",
  "message": "Invalid ID format"
}

Author Not Found (404 Not Found):
```
{
  "error": "Author not found"
}
```

Deletion Constraint Error (400 Bad Request):


{
  "error": "Constraint Error",
  "message": "Cannot delete author: they are the only author of one or more papers"
}

Note: Validation Processing Logic

When handling DELETE requests, follow this processing flow:

Validate the ID format
Check if the resource exists
Check if deletion is allowed (constraint validation)

Implementation Notes

Response & Ordering Requirements

All responses must follow the JSON formats specified in this handout
Property names are camelCase (e.g., publishedIn)
List endpoints must include total, limit, offset
Ensure consistent ordering with specifying orderBy
- When retrieving multiple papers / authors, order them by ascending id
- When retrieving authors associated with a paper / papers associated with an author, order them by ascending id
- When searching for existing authors, if multiple matches exist, select the one with the lowest id

Input Processing

Validation scope is the same as Assignment 1
Valid strings are stored exactly as provided (do not trim)
For paper create/update: authors must be a non-empty array; each author must have a valid name

Error Handling

Handle all specified 400/404 responses in route handlers
Error responses must exactly match the formats specified in this document
Unexpected errors (500) are handled by the provided errorHandler middleware
- You do not need to modify the errorHandler middleware or implement additional 500 error handling

Getting Started

Download the starter code archive.
After extracting the archive, install dependencies:
```
npm install
```
Install PostgreSQL v17 and start PostgreSQL server
Create and configure PostgreSQL database named paper_management with steps in lecture slides or with createdb:
```
createdb paper_management
```
Create .env with your PostgreSQL credentials
.env
```
DATABASE_URL="postgresql://your-os-username@localhost:5432/paper_management?schema=public"
```
- Replace your-os-username with your actual OS username
Run Prisma migration
```
npx prisma migrate dev --name init
```
Generate the Prisma Client
```
npx prisma generate
```
Start the development server
```
npm run dev
```
- The backend server runs on port 3000
- Make sure PostgreSQL is running before starting the development server
- Use Prisma Studio to view and edit your database through a web interface

Testing Your Implementation

Manual Testing

Start your application locally
```
npm start
```
Test your API endpoints

Use tools such as cURL, Thunder Client , or Postman to manually test your API endpoints.

Sample Test Cases

Sample tests are included in the tests/ directory of the starter code and are provided for guidance only. They focus on core functionality and correspond to approximately 80% of the autograder score.

The autograder will run similar but more comprehensive tests that check all explicitly stated requirements in this handout, and it will not test anything beyond what is specified.

Two sample test files are provided:

sample.paper.test.ts: Tests the paper routes
sample.author.test.ts: Tests the author routes

You are encouraged to write additional test cases.

Run a specific test file:


npm test -- tests/sample.paper.test.ts


npm test -- tests/sample.author.test.ts

Run all tests:
```
npm test -- --maxWorkers=1
```

Tip

The auto-grader will check for exact error messages. Ensure that your error responses match the formats and wording specified in this handout exactly.

Submission Instructions

Please follow the steps below to submit a .tar.gz archive to Quercus.

Prepare `reasoning.md`

In addition to the required code files, you must submit a short reflection file named reasoning.md.

Create a folder called assignment-2.
Inside the assignment-2 folder, create a file called reasoning.md.
Answer the three required questions described in the AI Usage Policy page.

Important Notes

Keep your responses brief and specific, with a total length of no more than 300 words
This file is intended to capture your genuine thinking process. It is not a long writing assignment required polishing.
reasoning.md is worth 5% of this assignment grade. Its grading rubric is on the AI Usage Policy page.

Generate the Archive

Make your assignment-2 directory have exactly the following structure:

- - schema.prisma
- - - prisma.ts
  - - authors.ts
    - papers.ts
  - database.ts
  - middleware.ts
  - routes.ts
  - server.ts
  - types.ts
- jest.config.mjs
- package.json
- prisma.config.ts
- tsconfig.json
- reasoning.md

From the parent directory that contains the assignment-2 folder, create the submission archive:


tar zcvf 1234567890-a2.tar.gz --exclude='.DS_Store' assignment-2

Replace 1234567890 with your student number.
The archive must contain exactly one top-level folder named assignment-2 with the required files inside.
Do not include node_modules/. The auto-grader will run npm install to install all dependencies specified in package.json.

Any submission that fails to meet the specified format or structure and requires TA intervention for the autograder to work will receive a 20-point deduction.

Verify Your Submission

To avoid a 20-point deduction, use the provided Python script to verify your submission.

Download verify_a2_submission.py to the same directory as your .tar.gz file:


curl -o verify_a2_submission.py https://www.eecg.utoronto.ca/~cying/courses/ece1724-web/assignments/assignment-2/verify_a2_submission.py

Alternatively, download the script here.

Ensure you have Python installed. If not, install it from python.org .
From the same directory as your 1234567890-a2.tar.gz, run:
```
python verify_a2_submission.py 1234567890-a2.tar.gz
```
- Replace 1234567890 with your student number.

Submit to Quercus

Submit your .tar.gz file to Quercus .

Note

You are allowed to submit unlimited times. Only your latest submission before the deadline will be graded. Quercus automatically appends a suffix to the file name after the first submission — this will not affect grading.

Grading Scheme (100 points)

The total 100 points correspond to 95% of this assignment’s grade. The remaining 5% comes from your reasoning.md submission.

Grades were released on February 18. If you would like to request a remark, please do so by March 4, following the instructions in the syllabus.

Paper Routes (74 points)

POST /api/papers (30)
- Successful creation (13)
```
{
  "title": "Example Paper Title",
  "publishedIn": "ICSE 2025",
  "year": 2025,
  "authors": [
    {
      "name": "John Doe",
      "email": "john@mail.utoronto.ca",
      "affiliation": "University of Toronto"
    },
    {
      "name": "Jane Smith"
    }
  ]
}
```
  - 201 Status code (1)
  - Has id field (1)
  - Has createdAt and updatedAt fields (1)
  - createdAt and updatedAt are in ISO 8601 format (1)
  - title, publishedIn, and year are the same as the request body (3; 1 point each)
  - Has authors field and it is an array (1)
  - The first author has id, createdAt and updatedAt field (1)
  - The first author’s name, email and affiliation are correct (1)
  - The second author has id, createdAt and updatedAt field (1)
  - The second author’s name, email and affiliation are correct (email and affiliation are null) (1)
  - These two authors are added to Author table (1)
- Author handling (5)
```
{
  "title": "Example Paper Title",
  "publishedIn": "ICSE 2024",
  "year": 2024,
  "authors": [
    {
      "name": "John Doe",
      "email": "john@mail.utoronto.ca",
      "affiliation": "University of Toronto"
    },
    {
      "name": "Jane Smith",
      "email": "jane@mail.utoronto.ca"
    }
  ]
}
```
  - The first author matches with correct author id, and name, email, and affiliation are correct(1)
  - The second author has correct id (1)
  - The second author’s name and email are correct (1)
  - The second author’s affiliation is null (1)
  - The second author creates a new record in Author table (1)
- Error handling and validation (12)
  - Missing fields (9)
    - Not provided: the request body is {} (6)
      - 400 status code (1)
      - error is "Validation Error" (1)
      - 4 messages, each counts for 1 point
    - Author’s name is missing (3)
      - Not provided (1)
      - Empty string "" (1)
      - Only whitespace " " (1)
  - Invalid year (3)
    - 1900 (1)
    - "1901a" (1)
    - 2025.6 (1)
GET /api/papers (18)
- Basic retrieval (6)
  - papers is an array (1), and length is the number of papers (1)
  - An item in papers array has authors array (1)
  - Correct total, limit, offset in the response body, each counts for 1 point (3)
- Filtering and pagination (5)
  - year filter (1)
  - publishedIn case-insensitive partial match (1)
  - limit and offset (2)
  - Default limit (1)
- Error handling (7)
  - Invalid year: 1900 (1)
  - Invalid limit (3)
    - ab (1)
    - 101 (1)
    - 0 (1)
  - Invalid offset (3)
    - 2.5 (1)
    - 2a (1)
    - -2 (1)
GET /api/papers/:id (10)
- Successful retrieval (6)
  - 200 status code (1)
  - Correct id, title, publishedIn, year (2; 0.5 points each)
  - createdAt and updatedAt are in ISO 8601 format (1; 0.5 points each)
  - Has authors and it is an array (1)
  - Order by ascending id (1)
- Error handling (4)
  - Invalid id (3)
    - -1 (1)
    - abc (1)
    - 1a (1)
  - Not found error (1)
PUT /api/papers/:id (12)
- Successful update (5)
  - 200 status code (1)
  - title, publishedIn, year have been updated correctly (3; 1 point each)
  - updatedAt is different from createdAt (1)
- Error handling and validation (7)
  - Invalid id: abc (1)
  - Missing fields (4)
    - Not provided: request body is {} (2; 4 messages 0.5 points each)
    - title of only spaces (1)
    - Author name is missing (1)
  - Invalid year: 1900 (1)
  - Not found error (1)
DELETE /api/papers/:id (4)
- Successful deletion (2)
  - 204 status code (1)
  - Paper is not in database anymore (1)
- Error handling (2)
  - Invalid id: -1 (1)
  - Not found error (1)

Author Routes (26 points)

POST /api/authors (4)
- Successful creation (2)
  - Response has id, createdAt, updatedAt, and papers (an empty array) (0.8; 0.2 each)
  - name, email, and affiliation are correct (1.2; 0.4 each)
- Error handling and validation (2)
  - Not provided (request body is {}) (1)
  - {"name": ""} (1)
GET /api/authors (8)
- Basic retrieval (3)
  - authors is an array (0.5), and length is the number of authors (0.5)
  - An author in authors array has papers array (0.5)
  - Correct total, limit, offset in the response body (1.5; 0.5 each)
- Filtering and pagination (3)
  - name case-insensitive partial match (1)
  - affiliation case-insensitive partial match (1)
  - limit and offset (1)
- Error handling (2)
  - Invalid limit: 101.5 (1)
  - Invalid offset: -1 (1)
GET /api/authors/:id (4)
- Successful retrieval (2)
  - Correct id, name, email, affiliation (1; 0.25 points each)
  - createdAt and updatedAt are in ISO 8601 format (0.5; 0.25 points each)
  - Has papers and it is an array (0.5)
- Error handling (2)
  - Invalid id: -1 (1)
  - Not found error (1)
PUT /api/authors/:id (5)
- Successful update (2)
  - name, email, affiliation have been updated correctly （1.5; 0.5 points each）
  - updatedAt is different from createdAt (0.5)
- Error handling and validation (3)
  - Invalid id: abc (1)
  - Missing name: request body is {} (1)
  - Not found error (1)
DELETE /api/authors/:id (5)
- Successful deletion (1)
  - 204 status code (0.5)
  - Author is not in database anymore (0.5)
- Error handling (4)
  - Invalid id: 1a (1)
  - Not found error (1)
  - Deletion constraint error (2)

Resources

Questions?

Discussion Board:
- Post questions on course discussion board
- Search existing discussions first
- Use clear titles and provide relevant code snippets
Office Hours:
- Time: Wednesdays, 4:00 PM — 5:00 PM
- Location: Room 7206, Bahen Centre for Information Technology
Tips for Getting Help:
- Start early to allow time for questions
- Be specific about your problem
- Share what you’ve tried
- Include relevant error messages

Assignment 2: PostgreSQL with Prisma ORM

Overview

Learning Objectives

Requirements

Starter Code

Database Migration

API Routes

Detailed Requirements of API Endpoints

Paper Routes

GET /api/papers

GET /api/papers/:id

POST /api/papers

PUT /api/papers/:id

DELETE /api/papers/:id

Author Routes

GET /api/authors

GET /api/authors/:id

POST /api/authors

PUT /api/authors/:id

DELETE /api/authors/:id

Implementation Notes

Response & Ordering Requirements

Input Processing

Error Handling

Getting Started

Testing Your Implementation

Manual Testing

Sample Test Cases

Submission Instructions

Prepare reasoning.md

Generate the Archive

Verify Your Submission

Submit to Quercus

Grading Scheme (100 points)

Paper Routes (74 points)

Author Routes (26 points)

Resources

Questions?

Prepare `reasoning.md`