Employee Leave Management System

ELMS manages leave from eligibility calculation and submission through approval, withdrawal, and cancellation. Its strongest engineering decisions are the separation of HTTP handling from business rules, shared policy evaluation, and a balance journal that preserves deductions and reversals. PostgreSQL locking protects specific operations; remaining concurrency gaps define the next improvements.

Employees, managers, and HR administrators need different views and permissions over the same leave lifecycle. Policy decisions and balance changes need to remain explainable as requests move through approval and cancellation.

• Employee eligibility previews, submission, history, withdrawal, and cancellation requests.
• Manager approval, rejection, and cancellation approval; organization-wide leave oversight for HR.
• Administration of departments, leave types, leave years, holidays, and settings, with current settings limitations explained below.
• Authenticated, role-scoped operations with a traceable balance transaction history.

Employees preview eligibility and submit requests. Managers approve or reject pending requests, with eligibility recalculated during approval. Employees can withdraw pending requests or request cancellation of approved leave; approved cancellations create balance reversals.

The application enforces PENDING → APPROVED, REJECTED, or WITHDRAWN, and APPROVED → CANCELLATION_PENDING → CANCELLED. PostgreSQL constraints do not currently enforce the complete state machine.

• Separate routes, controllers, and services. Controllers do not access Prisma directly, and services work with application data rather than Express request objects.
• Reuse calculateLeaveDays for submission and approval: day counting, policy validation, overlap checks, balance lookup, and eligibility stay in one service.
• Use TanStack Query v5 with typed query-key factories, feature hooks, keepPreviousData, and invalidation organized around documented query-key roots.
• Share audit and notification services across leave workflows. Some audit writes fall outside the active transaction, so this boundary still needs correction.

• JWT access and refresh tokens use separate secrets, pinned HS256 verification, and minimum secret-length validation.
• Refresh tokens have unique jti values, SHA-256 hashed storage, rotation, and revocation. Reuse detection and token-family revocation are not implemented.
• Access tokens contain the user subject rather than role or personal data. Authenticated requests retrieve the role from the database so authorization reflects role changes.
• Route-level RBAC protects manager and HR APIs. Services independently check ownership and scope rather than trusting client-supplied employee IDs.
• Login returns the same Invalid credentials response for unknown accounts and incorrect passwords.
• The backend supports refresh-token rotation, but the frontend currently clears authentication on a 401 instead of calling the refresh endpoint.

EmployeeLeaveBalance stores the current materialized balance. LeaveBalanceTransaction records an append-only journal, with decimal leave quantities where appropriate.

Approval creates a debit. Cancellation preserves that original entry and adds a separate LEAVE_REVERSAL credit. The reversal amount comes from the original debit rather than recalculation against policy or holiday configuration that may have changed.

PostgreSQL transactions group approval changes, but the request-row lock does not fully protect the shared balance from concurrent approval of different requests.

• Transaction advisory locks serialize leave-request-number allocation for a year. pg_advisory_xact_lock works even when no sequence row exists to lock.
• Approval, rejection, and cancellation flows use SELECT … FOR UPDATE on the leave-request row, then re-check its status. This guards against duplicate transitions on the same request.
• During approval recalculation, the current request is excluded from overlap results so it does not conflict with itself.
• These protections do not lock the employee balance or prevent concurrent overlapping submissions. Request-number uniqueness and leave eligibility are separate invariants.

The backend creates a migrated and seeded PostgreSQL template database, then clones a database per Vitest worker. Advisory locking serializes database creation, workers use isolated databases, and explicit test-database naming guards protect cleanup.

In the supplied project audit, 102 DB-free API tests passed and 101 DB-backed tests were skipped because PostgreSQL was unavailable. The audit identifies 87 important integration tests as unexecuted. Frontend tests recorded 181 passes and 1 failure.

These are results from the supplied audit, not project tests executed by this portfolio. The test infrastructure is implemented; a successful run of all integration tests is not claimed.

• Protect the actual balance with locking, version checks, or conditional atomic updates. Different requests can validate the same available balance and oversell it; the existing balance version field is unused.
• Prevent concurrent overlapping submissions. Conflict checks observe committed rows, and the request-number advisory lock does not protect this business rule.
• Pass the active transaction client into audit writes. Some paths use the global Prisma client, risking audit records after rollback and connection-pool exhaustion or deadlock under concurrency.
• Correct double subtraction when a holiday falls on a weekly-off day, half-day requests spanning multiple days, server-local timezone calculations, and future leave-year attribution.
• Add login rate limiting and refresh-token reuse detection; align database and JWT expiry configuration and review per-request database lookup cost.
• Connect frontend expiry handling to the refresh endpoint.
• Implement or clearly limit configuration behavior. Among SystemSettings fields, only weekly_off_days currently affects business logic.
• Resolve the failing frontend test and run the skipped database-backed suites before claiming successful integration verification.

Locking a request protects that request, not every resource the workflow touches. The balance ledger makes changes explainable, while shared balances, overlap rules, and audit writes require their own concurrency and transaction boundaries. No production-scale, latency, or throughput claim is made.