PROJECT 02 / 05
A secure, scalable Issue Tracking backend API built with TypeScript and Express, featuring JWT authentication and role-based authorization (RBAC) — no ORM, raw SQL by design.
DevPulse is a backend-only Issue Tracking API — the kind of system a small engineering team would use to log and manage bugs and feature requests — built to be strict about who can do what. Every write operation is gated behind JWT authentication and role checks, and the whole data layer runs on raw SQL against PostgreSQL, with no ORM in between.
Users sign up and log in to receive a JWT, then create and manage issues tagged as bugs or feature requests. Two roles drive the permission model: a contributor can sign up, log in, create issues, and view issues; a maintainer gets everything a contributor has plus the ability to update any issue, delete issues, and manage the overall workflow. Passwords are hashed with bcrypt and never returned in any API response, and every protected endpoint checks both the JWT and the caller's role before executing. The API is written in TypeScript on Express, built with tsup, and deployed as a serverless function on Vercel with a Neon-hosted PostgreSQL database.
Getting the update-permission logic right was the trickiest part: maintainers can edit any issue, but a contributor should only be able to update their own issue, and only while it's still open — that's a conditional authorization rule, not just a role check. Writing every query in raw SQL instead of reaching for an ORM meant being intentional about query structure, parameterization, and connection handling from the start. Structuring a clean modular architecture (separate controller / service / route layers for auth and issue-tracking) so the codebase stayed maintainable as endpoints grew was another deliberate design challenge, on top of getting a TypeScript build correctly configured for Vercel's serverless environment.
Planned next: refresh tokens so sessions don't hard-expire, rate limiting on the auth endpoints, pagination and full-text search on the issues list, a test suite covering the RBAC edge cases, and a lightweight metrics endpoint for maintainers to see issue volume and resolution time over time.
As part of this project, I recorded two short technical walkthroughs covering SQL vs. NoSQL schema design and scaling trade-offs, and how PostgreSQL connection pooling works and why it beats opening a new client connection per request.