Project: Results Portal API

mkdir results-portal && cd results-portal && npm init -y && npm pkg set type=module
npm install express pg zod jose
npm install --save-dev typescript tsx @types/node @types/express @types/pg
# Copy passwords.ts, tokens.ts and auth.ts from the Authentication lesson
# and cache.ts from the Caching lesson into src/
createdb results_portal
psql results_portal -f schema.sql
cp .env.example .env            # then edit the values
node --env-file=.env --import tsx src/seed.ts
node --env-file=.env --import tsx src/server.ts

DATABASE_URL=postgres://postgres:postgres@localhost:5432/results_portal
JWT_SECRET=replace-with-64-random-hex-characters-from-crypto-randomBytes
PORT=3000

CREATE TABLE users (
  id            integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email         text NOT NULL UNIQUE CHECK (email = lower(email)),
  password_hash text NOT NULL,
  role          text NOT NULL CHECK (role IN ('teacher', 'parent', 'student'))
);

CREATE TABLE students (
  id        integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name      text NOT NULL,
  form      smallint NOT NULL CHECK (form BETWEEN 1 AND 6),
  parent_id integer REFERENCES users(id)
);

CREATE TABLE results (
  student_id integer NOT NULL REFERENCES students(id) ON DELETE CASCADE,
  subject    text NOT NULL,
  term       text NOT NULL,
  score      smallint NOT NULL CHECK (score BETWEEN 0 AND 100),
  PRIMARY KEY (student_id, subject, term)
);

CREATE INDEX students_form_idx ON students (form);
CREATE INDEX students_parent_idx ON students (parent_id);

import { z } from "zod";

// Validate the environment once at startup: fail fast with a clear message instead of later at random.
const Env = z.object({
  DATABASE_URL: z.string().startsWith("postgres://"),
  JWT_SECRET: z.string().min(32),
  PORT: z.coerce.number().int().default(3000),
});

export const config = Env.parse(process.env);

import pg from "pg";
import { config } from "./config";

export const pool = new pg.Pool({ connectionString: config.DATABASE_URL, max: 10 });

import { pool } from "./db";

export interface Result {
  subject: string;
  term: string;
  score: number;
}

export interface RankingRow {
  position: number;
  studentId: number;
  name: string;
  average: number;
}

export async function findUserByEmail(email: string) {
  const { rows } = await pool.query<{ id: number; passwordHash: string; role: "teacher" | "parent" | "student" }>(
    `SELECT id, password_hash AS "passwordHash", role FROM users WHERE email = $1`,
    [email.toLowerCase()],
  );
  return rows[0];
}

export async function findStudent(id: number) {
  const { rows } = await pool.query<{ id: number; name: string; form: number; parentId: number | null }>(
    `SELECT id, name, form, parent_id AS "parentId" FROM students WHERE id = $1`,
    [id],
  );
  return rows[0];
}

export async function resultsFor(studentId: number): Promise<Result[]> {
  const { rows } = await pool.query<Result>(
    "SELECT subject, term, score FROM results WHERE student_id = $1 ORDER BY term, subject",
    [studentId],
  );
  return rows;
}

// Insert, or replace the score if this student already has one for that subject and term.
export async function saveResult(studentId: number, r: Result): Promise<void> {
  await pool.query(
    `INSERT INTO results (student_id, subject, term, score) VALUES ($1, $2, $3, $4)
     ON CONFLICT (student_id, subject, term) DO UPDATE SET score = EXCLUDED.score`,
    [studentId, r.subject, r.term, r.score],
  );
}

export async function formRanking(form: number, term: string): Promise<RankingRow[]> {
  const { rows } = await pool.query<RankingRow>(
    `SELECT rank() OVER (ORDER BY avg(r.score) DESC)::int AS position,
            s.id AS "studentId", s.name,
            round(avg(r.score), 1)::float AS average
       FROM students s JOIN results r ON r.student_id = s.id
      WHERE s.form = $1 AND r.term = $2
      GROUP BY s.id
      ORDER BY position, s.name`,
    [form, term],
  );
  return rows;
}

import express, { type NextFunction, type Request, type Response } from "express";
import { z } from "zod";
import { requireAuth, requireRole } from "./auth";
import { TtlCache } from "./cache";
import { verifyPassword } from "./passwords";
import * as db from "./results";
import { signToken, type AuthUser } from "./tokens";

const Login = z.object({ email: z.email(), password: z.string().min(1) });
const NewResult = z.object({
  studentId: z.number().int().positive(),
  subject: z.string().trim().min(2).max(40),
  term: z.string().regex(/^\d{4}-T[123]$/, "term looks like 2026-T1"),
  score: z.number().int().min(0).max(100),
});
const RankingQuery = z.object({ term: z.string().regex(/^\d{4}-T[123]$/) });

class HttpError extends Error {
  constructor(public status: number, message: string) {
    super(message);
  }
}

async function canView(user: AuthUser, studentId: number): Promise<boolean> {
  if (user.role === "teacher") return true;
  const student = await db.findStudent(studentId);
  if (!student) return false;
  return user.role === "parent" ? student.parentId === user.id : false;
}

export function createApp() {
  const app = express();
  app.use(express.json());
  const rankings = new TtlCache<db.RankingRow[]>(60_000);

  app.post("/login", async (req, res) => {
    const { email, password } = Login.parse(req.body);
    const user = await db.findUserByEmail(email);
    if (!user || !(await verifyPassword(password, user.passwordHash))) throw new HttpError(401, "Wrong email or password");
    res.json({ token: await signToken({ id: user.id, role: user.role }) });
  });

  app.get("/students/:id/results", requireAuth, async (req, res) => {
    const id = z.coerce.number().int().positive().parse(req.params.id);
    // 404 rather than 403 for strangers, so the API doesn't reveal which ids exist
    if (!(await canView(req.user!, id))) throw new HttpError(404, "Student not found");
    res.json(await db.resultsFor(id));
  });

  app.post("/results", requireAuth, requireRole("teacher"), async (req, res) => {
    const { studentId, ...result } = NewResult.parse(req.body);
    const student = await db.findStudent(studentId);
    if (!student) throw new HttpError(404, "Student not found");
    await db.saveResult(studentId, result);
    rankings.delete(`${student.form}:${result.term}`);
    res.status(201).json({ studentId, ...result });
  });

  app.get("/forms/:form/ranking", requireAuth, requireRole("teacher"), async (req, res) => {
    const form = z.coerce.number().int().min(1).max(6).parse(req.params.form);
    const { term } = RankingQuery.parse(req.query);
    res.json(await rankings.getOrLoad(`${form}:${term}`, () => db.formRanking(form, term)));
  });

  app.use((_req, res) => {
    res.status(404).json({ error: "Not found" });
  });
  app.use((err: unknown, _req: Request, res: Response, _next: NextFunction) => {
    if (err instanceof HttpError) return res.status(err.status).json({ error: err.message });
    if (err instanceof z.ZodError) {
      return res.status(400).json({ error: "Invalid input", issues: err.issues.map((i) => `${i.path.join(".")}: ${i.message}`) });
    }
    if (err instanceof SyntaxError) return res.status(400).json({ error: "Body must be valid JSON" });
    console.error(err);
    res.status(500).json({ error: "Something went wrong" });
  });

  return app;
}

import { createApp } from "./app";
import { config } from "./config";
import { pool } from "./db";

const server = createApp().listen(config.PORT, () => console.log(`Results portal on http://localhost:${config.PORT}`));

// Finish in-flight requests and close database connections on shutdown (Ctrl+C, or a deploy).
process.on("SIGTERM", shutdown);
process.on("SIGINT", shutdown);
function shutdown() {
  server.close(() => pool.end().then(() => process.exit(0)));
}

import { pool } from "./db";
import { hashPassword } from "./passwords";

await pool.query("TRUNCATE results, students, users RESTART IDENTITY CASCADE");

const user = async (email: string, password: string, role: string) =>
  (await pool.query<{ id: number }>(
    "INSERT INTO users (email, password_hash, role) VALUES ($1, $2, $3) RETURNING id",
    [email, await hashPassword(password), role],
  )).rows[0].id;

await user("teacher@school.tz", "chalk-and-board-2026", "teacher");
const mama = await user("mama.amina@example.com", "familia-yetu-2026", "parent");

const students: [string, number, number | null, number, number][] = [
  // name, form, parent, Maths, Biology
  ["Amina Hassan", 4, mama, 88, 79],
  ["Juma Said", 4, null, 29, 48],
  ["Neema Kimaro", 4, null, 71, 84],
  ["Ali Mohamed", 4, null, 95, 60],
];
for (const [name, form, parentId, maths, biology] of students) {
  const { rows } = await pool.query<{ id: number }>(
    "INSERT INTO students (name, form, parent_id) VALUES ($1, $2, $3) RETURNING id",
    [name, form, parentId],
  );
  await pool.query(
    "INSERT INTO results (student_id, subject, term, score) VALUES ($1, 'Maths', '2026-T1', $2), ($1, 'Biology', '2026-T1', $3)",
    [rows[0].id, maths, biology],
  );
}
console.log("Seeded", students.length, "students");
await pool.end();

// Runs against a real test database: createdb results_portal_test && psql results_portal_test -f schema.sql
// then: node --env-file=.env.test --import tsx --test test/*.test.ts
import { after, before, describe, it } from "node:test";
import assert from "node:assert/strict";
import { execFileSync } from "node:child_process";
import type { AddressInfo } from "node:net";
import type { Server } from "node:http";
import { createApp } from "../src/app";
import { pool } from "../src/db";

let server: Server;
let base: string;

before(async () => {
  execFileSync(process.execPath, ["--import", "tsx", "src/seed.ts"], { stdio: "ignore" });   // known data
  server = createApp().listen(0);
  await new Promise((resolve) => server.once("listening", resolve));
  base = `http://localhost:${(server.address() as AddressInfo).port}`;
});
after(async () => {
  server.close();
  await pool.end();
});

async function login(email: string, password: string): Promise<string> {
  const res = await fetch(`${base}/login`, {
    method: "POST", headers: { "Content-Type": "application/json" }, body: JSON.stringify({ email, password }),
  });
  return (await res.json()).token;
}
const get = (path: string, token: string) => fetch(base + path, { headers: { Authorization: `Bearer ${token}` } });

describe("results portal", () => {
  it("lets a parent see only their own child", async () => {
    const token = await login("mama.amina@example.com", "familia-yetu-2026");
    const own = await get("/students/1/results", token);
    assert.deepEqual(await own.json(), [
      { subject: "Biology", term: "2026-T1", score: 79 },
      { subject: "Maths", term: "2026-T1", score: 88 },
    ]);
    assert.equal((await get("/students/2/results", token)).status, 404);
    assert.equal((await get("/forms/4/ranking?term=2026-T1", token)).status, 403);
  });

  it("ranks a form and refreshes after a new result", async () => {
    const token = await login("teacher@school.tz", "chalk-and-board-2026");
    const before = await (await get("/forms/4/ranking?term=2026-T1", token)).json();
    assert.deepEqual(before.map((r: { name: string; position: number }) => [r.position, r.name]), [
      [1, "Amina Hassan"], [2, "Ali Mohamed"], [2, "Neema Kimaro"], [4, "Juma Said"],
    ]);

    const saved = await fetch(`${base}/results`, {
      method: "POST",
      headers: { Authorization: `Bearer ${token}`, "Content-Type": "application/json" },
      body: JSON.stringify({ studentId: 2, subject: "Maths", term: "2026-T1", score: 99 }),
    });
    assert.equal(saved.status, 201);
    const afterSave = await (await get("/forms/4/ranking?term=2026-T1", token)).json();
    assert.equal(afterSave.find((r: { name: string }) => r.name === "Juma Said").average, 73.5);
  });

  it("explains invalid input", async () => {
    const token = await login("teacher@school.tz", "chalk-and-board-2026");
    const res = await fetch(`${base}/results`, {
      method: "POST",
      headers: { Authorization: `Bearer ${token}`, "Content-Type": "application/json" },
      body: JSON.stringify({ studentId: 1, subject: "Maths", term: "Term 1", score: 120 }),
    });
    assert.equal(res.status, 400);
    const body = await res.json();
    assert.deepEqual(body.issues, ["term: term looks like 2026-T1", "score: Too big: expected number to be <=100"]);
  });
});