Faculty Appointment Portal

The problem

Meeting a faculty member meant going to their cabin and hoping. If they were in a class, in a meeting, or not on campus, the student waited or came back later, and the faculty member was interrupted whenever someone did find them in.

What I built

A full-stack portal for three roles from one codebase. Faculty publish their weekly timetable once, and students only ever see the free time in it. A student picks a slot, a session length and a reason for the meeting; the faculty member approves or declines it; and admins oversee accounts and appointments across the institution.

• Searchable faculty directory by name, department, expertise or building, with profiles and a cabin-location map.
• Booking restricted to published office hours, with 15 to 60 minute sessions and status tracking from pending to completed.
• Drag-to-paint weekly availability editor in 15-minute steps, plus per-date absences for leave and conferences.
• Admin approval queue for new faculty, platform-wide appointment log, live dashboard counts and CSV export.
• Email OTP password reset and appointment confirmations over SMTP.

Architecture

A React single-page app talks JSON to an Express REST API, which owns every rule: role-based access, booking validation and conflict checks. PostgreSQL holds users, faculty profiles, appointments, one-time passcodes and revoked tokens; Multer stores profile photos and cabin maps on disk; an SMTP provider delivers OTP and confirmation emails. In production Express serves the built frontend too, so the whole app runs as one process.

Data model

A user has at most one faculty profile; students book appointments and faculty receive them. Availability and absences live as JSON on the faculty row, so a timetable is one read. otp_tokens and revoked_tokens deliberately have no foreign keys: passcodes are matched by email, and revoked JWTs are blacklisted by their jti until they would have expired anyway.

Keeping bookings consistent

Two students can try to book the same slot at the same moment, so booking and confirming both run as PostgreSQL transactions that take SELECT ... FOR UPDATE row locks before checking for conflicts. Confirming one request automatically declines every overlapping pending request in the same transaction, so a race always resolves to exactly one confirmed appointment.

An integration test reproduced a real Postgres deadlock under concurrent confirm requests. The fix was to lock rows in a consistent order on every code path. Moving the mark-completed job off a public, frequently polled endpoint and into a background job also removed a full-table update from the request path.

Security

Sessions live in an httpOnly, SameSite cookie, and logout revokes the JWT server-side by its ID. Role checks are enforced in API middleware, not just the UI.

• bcrypt password hashing and parameterized SQL throughout.
• Rate limiting on login, OTP and registration, plus Helmet security headers and a CORS allow-list.
• Identical responses for unknown emails and wrong passwords, so accounts cannot be enumerated.
• Unapproved faculty are hidden from search and return 404 on direct access.

Testing and CI

72 Jest and Supertest tests: unit tests for time-slot math and pagination, and integration tests that hit a real PostgreSQL database through the full stack, including the locking behaviour. GitHub Actions runs lint and tests against a Postgres service container on every push, so a broken build cannot merge.