Case Study · 01
Academic
Module Tracker
A role-based faculty management system built for universities —
empowering HODs and teachers to track syllabus coverage, visualize
academic progress, and export institutional reports.
01 — Problem
What Problem
Does This Solve?
In most universities, tracking whether teachers have actually covered
the syllabus is a
manual, spreadsheet-driven nightmare. HODs have no
real-time visibility. Teachers lack a structured way to log their
coverage. At the end of term, generating reports is time-consuming and
error-prone.
Academic Module Tracker replaces this entire workflow with a
role-based digital system where everyone has the right
access, the right views, and the right data — always up to date.
😵
No Real-Time Visibility
HODs couldn't see teacher progress until the end of term — by then
it was too late to intervene.
📊
Manual Reports
Generating Excel reports meant manually compiling data from
multiple teachers — hours of work.
🔓
No Access Control
Any teacher could theoretically see or modify any other teacher's
data. No RBAC, no audit trail.
📉
Coverage Gaps
Topics left uncovered were only discovered at the end of semester
— too late to fix.
02 — System Architecture
How It's
Built
The system is a monolithic Next.js application with
server-side API routes, JWT-based authentication, and a Prisma ORM
layer connecting to MySQL. All pages are server-rendered for
performance and SEO.
HOD Dashboard
Next.js + React
Teacher Portal
Next.js + React
Auth Guard
JWT Middleware
↓
API Routes
Next.js /api + Zod Validation
Report Engine
jsPDF + XLSX Export
↓
MySQL Database
Prisma ORM · MariaDB Adapter
RBAC is enforced at the middleware level — every API route checks the
decoded JWT token and validates the user's role before allowing any
operation.
03 — Database Design
Schema &
Data Model
The schema is designed around a hierarchical academic structure:
Admin → Faculty (HOD/Teacher) → Department → Course → Topic →
SubTopic. Coverage is event-based — each teaching session creates a new
TopicCoverage or SubTopicCoverage record with a real
date, enabling a full audit trail rather than just a boolean flag.
The system also tracks
Students, AcademicYears, and Semesters — supporting
batch-wise history, backlog/repeat status, and multi-year academic
records. SubTopics support a
self-referencing hierarchy (parentId)
for nested content structures.
Entity Relationship Overview
Admin
manages
Faculty
→ role:
HOD | TEACHER
→ belongs to
Department
→ has
Course
→ has
Topic
→ has
SubTopic
(self-ref)
AcademicYear
ties together
FacultyCourse
+
TopicCoverage
+
Student
→ history via
StudentAcademicYear
Faculty
idPK
faculty_idString @unique
emailString @unique
username / nameString
roleFK → Faculty_Role
dept_idFK → Department
statusA | D
passwordString (hashed)
Department
dept_idPK
dept_nameString
→ courses[]
→ faculties[]
Course
course_idPK
course_nameString
semester_idFK → Semester
dept_idFK → Department
→ topics[], facultyCourses[]
AcademicYear
idPK
label"2023-2024" @unique
isActiveBoolean
→ facultyCourses[], students[]
FacultyCourse
(mapping)
facultyIdFK
courseIdFK
academicYearIdFK
@@unique[faculty, course, year]
Topic
topic_idPK
topic_nameString
topic_descriptionText?
courseIdFK → Course
→ subtopics[]
SubTopic
(self-ref)
subtopic_idPK
subtopic_nameString
topicIdFK → Topic
parentIdFK → SubTopic
→ children[] (nested)
TopicCoverage
topicIdFK
facultyIdFK
courseIdFK
semesterIdFK
academicYearIdFK
taughtOnDate (actual)
remarkString?
@@unique[topic+faculty+course+sem+year]
SubTopicCoverage
subtopicIdFK
facultyIdFK
courseIdFK
semesterIdFK
academicYearIdFK
taughtOnDate
@@unique[subtopic+faculty+course+sem+year]
Student
studentIDcuid() @unique
emailString @unique
currentSemFK → Semester
academicYearIdFK
statusA | D
StudentAcademicYear
studentIdFK
academicYearIdFK
statusREGULAR | BACKLOG
| REPEAT | PASSED
@@unique[student+year]
Admin
idPK
usernameString @unique
emailString @unique
statusA | D
Key Design Decisions
→
Event-based coverage
(not boolean) — each taughtOn date creates a new
record, enabling full teaching history and timeline views.
→
5-column composite unique
on coverage tables prevents duplicate entries across faculty,
course, semester, and academic year simultaneously.
→
Self-referencing SubTopic
(parentId → subtopic_id) allows infinitely nested
topic trees without a fixed depth limit.
→
StudentAcademicYear
bridge table tracks REGULAR / BACKLOG / REPEAT / PASSED status per
year — full academic history preserved.
04 — Features
What It
Does
🔐
Role-Based Access Control
HODs see department-wide dashboards and can manage faculty.
Teachers only see their own assigned courses. JWT tokens carry
role claims validated on every API call.
📈
Real-Time Coverage Tracking
Teachers mark topics and sub-topics as covered with timestamps.
Progress bars update immediately, giving HODs live visibility
into syllabus completion.
📄
PDF & Excel Report Export
Generate polished institutional reports in navy/gold branding
with jsPDF and the XLSX library. HODs can export per-teacher,
per-course, or department-wide summaries.
📋
DataTables Integration
Large datasets of faculty, courses, and coverage entries are
rendered in interactive DataTables with search, sort, and
pagination — all reconciled with React's DOM carefully to avoid
conflicts.
🏗️
HOD Management Panel
HODs can assign teachers to courses, manage semesters and
academic years, and add new departments — all through a
dedicated admin interface.
05 — Screenshots
UI Walkthrough
localhost:3000/dashboard/faculty/hod/overview
HOD's Department overview with coverage progress
localhost:3000/dashboard/faculty/course-coverage
Topic coverage checklist per assigned course
localhost:3000/dashboard/faculty/report
Export to PDF or Excel with one click
localhost:3000/dashboard/admin
Admin dashboard for manages the whole system
06 — Challenges
Hard Problems
I Solved
DataTables + React DOM Reconciliation Conflict
DataTables.net manipulates the DOM directly after initialization.
React's virtual DOM doesn't know about these changes, causing
reconciliation errors and duplicate rendering
when state updates triggered re-renders. I resolved this by using
refs to detect DataTables initialization state and conditionally
destroying/reinitializing the table on data changes — preventing
React and DataTables from fighting over the same DOM nodes.
Hierarchical Coverage Percentage Calculation
Calculating "how much has a teacher covered" requires rolling up
SubTopic coverage → Topic → Course → Department. A naive approach
would hit the DB dozens of times. I solved this with
Prisma's nested include queries combined with
server-side aggregation, computing all percentages in a single
database round-trip.
Role-Based Middleware Without NextAuth
I chose custom JWT middleware over NextAuth for
fine-grained control. The challenge was protecting nested API
routes consistently. I built a reusable
withRoleGuard() higher-order function that wraps any
API handler, decodes the token from cookies, validates the role,
and returns 403 if unauthorized.
Styled PDF Export Matching Institutional Branding
jsPDF's raw API is verbose and error-prone for complex layouts. I
built a custom PDF builder abstraction that
handles headers, footers, dynamic tables, and page breaks
automatically — all styled with the university's navy and gold
color palette using jsPDF-autotable.
07 — What I Learned
Key
Takeaways
🏗️
Schema-First Design
Getting the Prisma schema right upfront saved weeks of
refactoring. The hierarchical model was designed on paper before
writing a single line of code.
🔒
Auth Without Libraries
Building JWT auth from scratch deepened my understanding of token
lifecycle, refresh strategies, and cookie security flags.
⚛️
3rd Party DOM Libraries in React
DataTables taught me the hard way to always isolate imperative DOM
libraries behind refs and effect cleanup.
📐
Abstraction Pays Off
Building a reusable PDF builder and role-guard HOF made adding new
reports and protected routes trivial — no repetition.
