Skip to content
All projects

Student Management System

A student information system that digitized enrollment, curriculum management, subject scheduling, grading, and academic records for a college and senior high school, replacing spreadsheet-based workflows with a centralized platform.

  • Next.js
  • PostgreSQL
  • Prisma
  • Kysely
  • Docker
SMS Home Page
Course Selection
Student Program of Study

Overview

The institution previously relied on spreadsheets and manual copy-paste workflows to manage student records, enrollment, and academic scheduling. As student and course data grew, maintaining consistency and detecting schedule conflicts became increasingly difficult. The project was created to centralize academic operations into a single system and reduce administrative overhead.

I was primarily responsible for backend development after the database design phase, helping translate the school's business rules into a normalized PostgreSQL schema and implementing APIs for enrollment, curriculum management, scheduling, grading, and academic record tracking. The system was deployed to production and has been maintained for more than six months.

What I built

  • Co-designed a normalized PostgreSQL schema spanning more than 70 tables covering student records, enrollment, curricula, academic history, scheduling, grading, academic progression, and role-based access control.
  • Implemented backend services and APIs supporting enrollment workflows, curriculum management, course assignments, academic records, transcript-related data, scheduling, announcements, grading, and student progress tracking.
  • Designed schedule-conflict validation across courses, instructors, rooms, and student enrollments to prevent overlapping assignments and reduce manual scheduling work.
  • Introduced Kysely alongside Prisma for complex reporting and multi-join queries, allowing greater control over generated SQL while preserving end-to-end type safety.
  • Implemented role-based access control with configurable permissions across administrative, academic, faculty, and student-facing modules.
  • Maintained the production deployment and performed ongoing support, bug fixes, and enhancements after launch.

Highlights

  • Production system actively used by a college and senior high school environment for enrollment and academic management.
  • 70+ table relational schema designed around normalized academic, administrative, and historical student data.
  • Schedule-conflict detection covering faculty assignments, room allocations, section schedules, and student enrollment combinations.
  • Hybrid Prisma and Kysely architecture, using Prisma for schema management and type generation while leveraging Kysely for complex read-heavy queries and reporting workloads.
  • Academic-history tracking through dedicated history tables for programs, strands, enrollments, grades, and curriculum progression rather than overwriting current state.

Interesting challenges

  • Modeling academic data without losing historical information. Students can change programs, strands, academic levels, and enrollments over time, so the system stores progression as explicit history rather than overwriting records.
  • Designing schedule-conflict detection for real-world enrollment workflows. The system must consider course schedules, faculty assignments, room allocations, and student enrollments simultaneously while remaining responsive for administrators.
  • Balancing normalization and usability. The database was intentionally normalized to separate personal information, enrollment records, academic structures, and grading data, reducing duplication while keeping common operations efficient.
  • Moving beyond ORM-generated queries. As reporting and scheduling queries became more complex, Prisma-generated queries became increasingly difficult to reason about. Kysely was introduced to provide direct control over SQL construction while retaining type safety.