Project: Results Portal API

createdb results_portal && createdb results_portal_test
mvn test
mvn spring-boot:run -Dspring-boot.run.profiles=demo      # loads the demo users and students

# Parent: own child works, other children are 404, rankings are 403
curl -u mama.amina:familia-yetu-2026 localhost:8080/api/students/1/results
curl -u mama.amina:familia-yetu-2026 localhost:8080/api/students/2/results
curl -u mama.amina:familia-yetu-2026 "localhost:8080/api/forms/4/ranking?term=2026-T1"

# Teacher: record a result and see the ranking
curl -u mwalimu:chalk-and-board-2026 -X POST localhost:8080/api/results -H "Content-Type: application/json" \
     -d '{"admissionNumber":"ADM-2026-0002","subject":"Maths","term":"2026-T1","score":45}'
curl -u mwalimu:chalk-and-board-2026 "localhost:8080/api/forms/4/ranking?term=2026-T1"

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>4.1.1</version>
  </parent>
  <groupId>tz.dolese</groupId>
  <artifactId>results-portal</artifactId>
  <version>1.0.0</version>

  <properties>
    <java.version>21</java.version>
  </properties>

  <dependencies>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-webmvc</artifactId>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-validation</artifactId>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-security</artifactId>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-flyway</artifactId>
    </dependency>
    <dependency>
      <groupId>org.flywaydb</groupId>
      <artifactId>flyway-database-postgresql</artifactId>
    </dependency>
    <dependency>
      <groupId>org.postgresql</groupId>
      <artifactId>postgresql</artifactId>
      <scope>runtime</scope>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-webmvc-test</artifactId>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>org.springframework.security</groupId>
      <artifactId>spring-security-test</artifactId>
      <scope>test</scope>
    </dependency>
  </dependencies>

  <build>
    <plugins>
      <plugin>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-maven-plugin</artifactId>
      </plugin>
    </plugins>
  </build>
</project>

spring.datasource.url=${DATABASE_URL:jdbc:postgresql://localhost:5432/results_portal}
spring.datasource.username=${DATABASE_USER:postgres}
spring.datasource.password=${DATABASE_PASSWORD:postgres}
spring.jpa.hibernate.ddl-auto=validate
spring.jpa.open-in-view=false

CREATE TABLE app_users (
  username      text PRIMARY KEY,
  password_hash text NOT NULL,
  role          text NOT NULL CHECK (role IN ('TEACHER', 'PARENT'))
);

ALTER TABLE students ADD COLUMN parent_username text REFERENCES app_users(username);
CREATE INDEX students_parent_idx ON students (parent_username);

package tz.dolese.results;

import jakarta.persistence.CascadeType;
import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.OneToMany;
import jakarta.persistence.Table;
import java.util.ArrayList;
import java.util.List;

@Entity
@Table(name = "students")
public class Student {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "admission_number", nullable = false, unique = true)
    private String admissionNumber;

    @Column(nullable = false)
    private String name;

    private int form;

    @Column(name = "parent_username")
    private String parentUsername;

    @OneToMany(mappedBy = "student", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<ExamResult> results = new ArrayList<>();

    protected Student() {}

    public Student(String admissionNumber, String name, int form, String parentUsername) {
        this.admissionNumber = admissionNumber;
        this.name = name;
        this.form = form;
        this.parentUsername = parentUsername;
    }

    public ExamResult addResult(String subject, String term, int score) {
        ExamResult result = new ExamResult(this, subject, term, score);
        results.add(result);
        return result;
    }

    public Long getId() { return id; }
    public String getAdmissionNumber() { return admissionNumber; }
    public String getName() { return name; }
    public int getForm() { return form; }
    public String getParentUsername() { return parentUsername; }
    public List<ExamResult> getResults() { return results; }
}

package tz.dolese.results;

import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import jakarta.persistence.Table;

@Entity
@Table(name = "app_users")
public class AppUser {
    @Id
    private String username;

    @Column(name = "password_hash", nullable = false)
    private String passwordHash;

    @Column(nullable = false)
    private String role;                   // "TEACHER" or "PARENT"

    protected AppUser() {}

    public AppUser(String username, String passwordHash, String role) {
        this.username = username;
        this.passwordHash = passwordHash;
        this.role = role;
    }

    public String getUsername() { return username; }
    public String getPasswordHash() { return passwordHash; }
    public String getRole() { return role; }
}

package tz.dolese.results;

import java.util.List;
import java.util.Optional;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

interface AppUserRepository extends JpaRepository<AppUser, String> {}

interface StudentRepository extends JpaRepository<Student, Long> {
    Optional<Student> findByAdmissionNumber(String admissionNumber);

    // A native SQL query when you need PostgreSQL features such as window functions.
    @Query(value = """
            SELECT rank() OVER (ORDER BY avg(r.score) DESC) AS position,
                   s.admission_number AS admissionNumber, s.name AS name,
                   round(avg(r.score), 1) AS average
              FROM students s JOIN exam_results r ON r.student_id = s.id
             WHERE s.form = :form AND r.term = :term
             GROUP BY s.id
             ORDER BY position, s.name
            """, nativeQuery = true)
    List<RankingRow> ranking(int form, String term);
}

// Interface projection: Spring maps each column alias to a getter.
interface RankingRow {
    int getPosition();
    String getAdmissionNumber();
    String getName();
    double getAverage();
}

package tz.dolese.results;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.http.HttpMethod;
import org.springframework.security.config.Customizer;
import org.springframework.security.config.annotation.web.builders.HttpSecurity;
import org.springframework.security.config.http.SessionCreationPolicy;
import org.springframework.security.core.userdetails.User;
import org.springframework.security.core.userdetails.UserDetailsService;
import org.springframework.security.core.userdetails.UsernameNotFoundException;
import org.springframework.security.crypto.factory.PasswordEncoderFactories;
import org.springframework.security.crypto.password.PasswordEncoder;
import org.springframework.security.web.SecurityFilterChain;

@Configuration
public class SecurityConfig {

    @Bean
    SecurityFilterChain api(HttpSecurity http) throws Exception {
        http
            .authorizeHttpRequests(auth -> auth
                .requestMatchers(HttpMethod.POST, "/api/results").hasRole("TEACHER")
                .requestMatchers("/api/forms/**").hasRole("TEACHER")
                .anyRequest().authenticated())                       // deny by default
            .httpBasic(Customizer.withDefaults())                    // username + password on every request (HTTPS only!)
            .sessionManagement(s -> s.sessionCreationPolicy(SessionCreationPolicy.STATELESS))
            .csrf(csrf -> csrf.disable());                           // safe for a stateless API without cookies
        return http.build();
    }

    // Stores "{bcrypt}$2a$10$..." so the hashing algorithm can be upgraded later.
    @Bean
    PasswordEncoder passwordEncoder() {
        return PasswordEncoderFactories.createDelegatingPasswordEncoder();
    }

    @Bean
    UserDetailsService users(AppUserRepository repository) {
        return username -> repository.findById(username)
                .map(u -> User.withUsername(u.getUsername()).password(u.getPasswordHash()).roles(u.getRole()).build())
                .orElseThrow(() -> new UsernameNotFoundException(username));
    }
}

package tz.dolese.results;

import jakarta.validation.constraints.Max;
import jakarta.validation.constraints.Min;
import jakarta.validation.constraints.NotBlank;
import jakarta.validation.constraints.Pattern;

record NewResult(
        @NotBlank String admissionNumber,
        @NotBlank String subject,
        @Pattern(regexp = "\\d{4}-T[123]", message = "must look like 2026-T1") String term,
        @Min(0) @Max(100) int score) {}

record ResultView(String subject, String term, int score) {}

package tz.dolese.results;

import java.util.Comparator;
import java.util.List;
import java.util.NoSuchElementException;
import org.springframework.security.core.Authentication;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class ResultService {
    private final StudentRepository students;

    public ResultService(StudentRepository students) {
        this.students = students;
    }

    @Transactional(readOnly = true)
    public List<ResultView> resultsFor(long studentId, Authentication user) {
        Student student = students.findById(studentId).filter(s -> canView(user, s))
                // Same 404 for "doesn't exist" and "not yours": don't reveal which ids exist.
                .orElseThrow(() -> new NoSuchElementException("Student not found"));
        return student.getResults().stream()
                .map(r -> new ResultView(r.getSubject(), r.getTerm(), r.getScore()))
                .sorted(Comparator.comparing(ResultView::term).thenComparing(ResultView::subject))
                .toList();
    }

    @Transactional
    public ResultView record(NewResult in) {
        Student student = students.findByAdmissionNumber(in.admissionNumber())
                .orElseThrow(() -> new NoSuchElementException("No student " + in.admissionNumber()));
        ExamResult result = student.getResults().stream()
                .filter(r -> r.getSubject().equals(in.subject()) && r.getTerm().equals(in.term()))
                .findFirst()
                .orElseGet(() -> student.addResult(in.subject(), in.term(), in.score()));
        result.setScore(in.score());
        return new ResultView(result.getSubject(), result.getTerm(), result.getScore());
    }

    @Transactional(readOnly = true)
    public List<RankingRow> ranking(int form, String term) {
        return students.ranking(form, term);
    }

    private static boolean canView(Authentication user, Student student) {
        boolean teacher = user.getAuthorities().stream().anyMatch(a -> a.getAuthority().equals("ROLE_TEACHER"));
        return teacher || user.getName().equals(student.getParentUsername());
    }
}

package tz.dolese.results;

import jakarta.validation.Valid;
import java.util.List;
import java.util.NoSuchElementException;
import org.springframework.http.HttpStatus;
import org.springframework.http.ProblemDetail;
import org.springframework.security.core.Authentication;
import org.springframework.web.bind.annotation.ExceptionHandler;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseStatus;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequestMapping("/api")
public class ResultsController {
    private final ResultService service;

    public ResultsController(ResultService service) {
        this.service = service;
    }

    @GetMapping("/students/{id}/results")
    List<ResultView> results(@PathVariable long id, Authentication user) {
        return service.resultsFor(id, user);
    }

    @PostMapping("/results")
    @ResponseStatus(HttpStatus.CREATED)
    ResultView record(@Valid @RequestBody NewResult body) {
        return service.record(body);
    }

    @GetMapping("/forms/{form}/ranking")
    List<RankingRow> ranking(@PathVariable int form, @RequestParam String term) {
        return service.ranking(form, term);
    }

    // RFC 9457 problem details: {"status":404,"detail":"Student not found",...}
    @ExceptionHandler(NoSuchElementException.class)
    ProblemDetail notFound(NoSuchElementException e) {
        return ProblemDetail.forStatusAndDetail(HttpStatus.NOT_FOUND, e.getMessage());
    }
}

package tz.dolese.results;

import org.springframework.boot.CommandLineRunner;
import org.springframework.context.annotation.Profile;
import org.springframework.security.crypto.password.PasswordEncoder;
import org.springframework.stereotype.Component;

// Runs only with --spring.profiles.active=demo, and only into an empty database.
@Component
@Profile("demo")
class DemoData implements CommandLineRunner {
    private final AppUserRepository users;
    private final StudentRepository students;
    private final PasswordEncoder encoder;

    DemoData(AppUserRepository users, StudentRepository students, PasswordEncoder encoder) {
        this.users = users;
        this.students = students;
        this.encoder = encoder;
    }

    @Override
    public void run(String... args) {
        if (users.count() > 0) return;
        users.save(new AppUser("mwalimu", encoder.encode("chalk-and-board-2026"), "TEACHER"));
        users.save(new AppUser("mama.amina", encoder.encode("familia-yetu-2026"), "PARENT"));

        Student amina = new Student("ADM-2026-0001", "Amina Hassan", 4, "mama.amina");
        amina.addResult("Maths", "2026-T1", 88);
        amina.addResult("Biology", "2026-T1", 79);
        Student juma = new Student("ADM-2026-0002", "Juma Said", 4, null);
        juma.addResult("Maths", "2026-T1", 29);
        juma.addResult("Biology", "2026-T1", 48);
        students.save(amina);
        students.save(juma);
    }
}

# Tests use their own database, so they never touch (or depend on) development data.
spring.datasource.url=${TEST_DATABASE_URL:jdbc:postgresql://localhost:5432/results_portal_test}
spring.datasource.username=${DATABASE_USER:postgres}
spring.datasource.password=${DATABASE_PASSWORD:postgres}
spring.jpa.hibernate.ddl-auto=validate
spring.jpa.open-in-view=false

package tz.dolese.results;

import static org.springframework.security.test.web.servlet.request.SecurityMockMvcRequestPostProcessors.httpBasic;
import static org.springframework.test.web.servlet.request.MockMvcRequestBuilders.get;
import static org.springframework.test.web.servlet.request.MockMvcRequestBuilders.post;
import static org.springframework.test.web.servlet.result.MockMvcResultMatchers.jsonPath;
import static org.springframework.test.web.servlet.result.MockMvcResultMatchers.status;

import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.boot.webmvc.test.autoconfigure.AutoConfigureMockMvc;
import org.springframework.http.MediaType;
import org.springframework.security.crypto.password.PasswordEncoder;
import org.springframework.test.web.servlet.MockMvc;
import org.springframework.transaction.annotation.Transactional;

@SpringBootTest
@AutoConfigureMockMvc
@Transactional
class ResultsApiTest {
    @Autowired MockMvc mvc;
    @Autowired AppUserRepository users;
    @Autowired StudentRepository students;
    @Autowired PasswordEncoder encoder;

    Long aminaId;
    Long jumaId;

    @BeforeEach
    void seed() {
        users.save(new AppUser("mwalimu", encoder.encode("chalk"), "TEACHER"));
        users.save(new AppUser("mama.amina", encoder.encode("familia"), "PARENT"));
        Student amina = new Student("ADM-2026-0001", "Amina Hassan", 4, "mama.amina");
        amina.addResult("Maths", "2026-T1", 88);
        amina.addResult("Biology", "2026-T1", 79);
        Student juma = new Student("ADM-2026-0002", "Juma Said", 4, null);
        juma.addResult("Maths", "2026-T1", 29);
        Student ali = new Student("ADM-2026-0003", "Ali Mohamed", 4, null);
        ali.addResult("Maths", "2026-T1", 77);
        ali.addResult("Biology", "2026-T1", 90);
        aminaId = students.save(amina).getId();
        jumaId = students.save(juma).getId();
        students.save(ali);
    }

    @Test
    void requiresLogin() throws Exception {
        mvc.perform(get("/api/students/" + aminaId + "/results")).andExpect(status().isUnauthorized());
        mvc.perform(get("/api/students/" + aminaId + "/results").with(httpBasic("mwalimu", "wrong")))
                .andExpect(status().isUnauthorized());
    }

    @Test
    void parentSeesOnlyTheirOwnChild() throws Exception {
        mvc.perform(get("/api/students/" + aminaId + "/results").with(httpBasic("mama.amina", "familia")))
                .andExpect(status().isOk())
                .andExpect(jsonPath("$.length()").value(2))
                .andExpect(jsonPath("$[0].subject").value("Biology"));
        mvc.perform(get("/api/students/" + jumaId + "/results").with(httpBasic("mama.amina", "familia")))
                .andExpect(status().isNotFound())
                .andExpect(jsonPath("$.detail").value("Student not found"));
        mvc.perform(get("/api/forms/4/ranking?term=2026-T1").with(httpBasic("mama.amina", "familia")))
                .andExpect(status().isForbidden());
    }

    @Test
    void teacherRecordsAndRanks() throws Exception {
        mvc.perform(post("/api/results").with(httpBasic("mwalimu", "chalk"))
                        .contentType(MediaType.APPLICATION_JSON)
                        .content("""
                                {"admissionNumber":"ADM-2026-0002","subject":"Maths","term":"2026-T1","score":45}"""))
                .andExpect(status().isCreated())
                .andExpect(jsonPath("$.score").value(45));

        mvc.perform(get("/api/forms/4/ranking?term=2026-T1").with(httpBasic("mwalimu", "chalk")))
                .andExpect(status().isOk())
                .andExpect(jsonPath("$[0].name").value("Ali Mohamed"))
                .andExpect(jsonPath("$[0].average").value(83.5))
                .andExpect(jsonPath("$[1].position").value(1))
                .andExpect(jsonPath("$[2].name").value("Juma Said"))
                .andExpect(jsonPath("$[2].average").value(45.0));
    }

    @Test
    void rejectsInvalidResults() throws Exception {
        mvc.perform(post("/api/results").with(httpBasic("mwalimu", "chalk"))
                        .contentType(MediaType.APPLICATION_JSON)
                        .content("""
                                {"admissionNumber":"ADM-2026-0001","subject":"Maths","term":"Term 1","score":120}"""))
                .andExpect(status().isBadRequest());
    }
}