# Building an HR Leave Approval Flow in a Custom ERP

> How I designed and built a transactional leave approval system from scratch using NestJS, PostgreSQL, and TypeORM—without Odoo or any off-the-shelf HR platform.

URL: https://www.matthewswong.com/en/blog/erp-hr-leave-approval-flow/

- Author: Matthews Wong (https://www.matthewswong.com/en/about/)
- Published: 2025-06-10
- Updated: 2026-10-17
- Category: ERP

One of the first modules I built in my freelance ERP consulting work was an HR leave management system for a custom ERP. On the surface it sounds simple: an employee submits a leave request, a manager approves or rejects it, and the system updates the employee's leave balance. In practice, getting the database transactions right, handling concurrent approval attempts, enforcing leave quotas atomically, and sending reliable email notifications took considerably more thought than the initial estimate suggested. This post documents the approach that ended up in production.

## Why Build It From Scratch?

The honest answer is that this client's HR requirements didn't fit any off-the-shelf module cleanly. The leave types, approval hierarchy, and quota rules are specific to Indonesian labor law and company policy. Adapting an existing module like Odoo's HR would have required understanding its internals deeply enough that building custom was actually faster. More importantly, the custom system integrates naturally with the NestJS + PostgreSQL stack I standardize on for freelance ERP consulting—same ORM, same testing patterns, same deployment pipeline.

### Database Schema: The Foundation

The schema has three core tables: employees (existing), leave_requests (one row per request, with a status enum: PENDING/APPROVED/REJECTED/CANCELLED), and leave_quotas (one row per employee per year per leave type, tracking total_days and used_days). The leave type enum covers annual, sick, unpaid, maternity, and paternity leave—the types defined in Indonesian labor law plus company-specific additions. The days_count on leave_requests is a generated column computed from start_date and end_date.

### The Approval State Machine

The leave request status is a finite state machine with four states. PENDING is the initial state on submission. The manager can transition to APPROVED or REJECTED. The employee can transition from PENDING to CANCELLED. No other transitions are valid. In the NestJS service, each transition method validates the current state before allowing the change—attempting to approve an already-rejected request returns a 400 error. This prevents double-processing.

> Use PostgreSQL's GENERATED ALWAYS AS column for computed values like days_count. This moves the calculation into the database where it can't diverge from the actual stored dates, and it's available in queries without application-layer computation. It's especially useful for reporting queries that aggregate leave usage across departments.

## The Atomic Approval Transaction

The trickiest part is the approval logic: when a manager approves a request, the system must simultaneously (a) check that the employee has sufficient leave quota remaining, (b) increment the used_days count in leave_quotas, and (c) update the leave_request status to APPROVED. These three operations must happen atomically. A lost update in the quota check would allow quota overspend. The solution is a database transaction with a row-level lock on the quota row.

### Handling the Race Condition

The TypeORM transaction's SELECT with a pessimistic write lock on the quota row prevents concurrent approvals from racing on quota decrement. The transaction reads the quota row with FOR UPDATE, checks the remaining balance, increments used_days, and commits—all as a single atomic unit. A concurrent transaction waits until the lock is released, then re-reads the (now-updated) quota.

> **Leave Quota Drift**

> If you ever update leave quotas or request statuses outside of the approved service methods—directly in the database during debugging or data migration—run a reconciliation query that compares the sum of approved leave days against the used_days column. Drift here is silent and causes incorrect balance displays that erode user trust.

## Email Notifications: Triggered by Status Transitions

Email notifications go out at two points: when the request is submitted (to the approving manager) and when the manager acts (to the employee). I trigger these from the service methods after the database transaction commits successfully—not inside the transaction. The email is enqueued in a BullMQ queue (backed by Redis) so that failures are retried automatically without blocking the API response.

## The React Frontend: Approval Inbox

The manager's approval interface is a simple inbox view built in React. It polls the API every 30 seconds for pending requests assigned to the current user, displays them in a table with the employee's name, leave type, dates, and remaining quota, and shows Approve / Reject buttons with a required reason field for rejection. The quota display on the approval form is critical—managers need to see how many days the employee has remaining before deciding.

## Lessons Learned After Six Months in Production

The generated days_count column saved me from at least three data integrity bugs during QA. The row-level lock on quota approval prevented a real concurrent-request issue discovered during load testing. The biggest mistake was initially sending emails inside the database transaction—email delivery latency was adding visible delay to the API response. Moving email to a BullMQ queue made approvals feel instant.

## Sources & Further Reading

- NestJS — Database (TypeORM) — https://docs.nestjs.com/techniques/database
- PostgreSQL — Transaction Isolation — https://www.postgresql.org/docs/current/transaction-iso.html
- TypeORM — Transactions — https://typeorm.io/transactions

> **Key takeaway:** An HR leave module in a custom ERP is harder than it looks because the leave balance and the request must change inside one database transaction. Indonesian labour law adds leave types, quota accrual rules and approval hierarchies that off-the-shelf HR modules rarely fit cleanly, which is usually what forces a custom build in the first place.

## Source links

- https://docs.nestjs.com/techniques/database
- https://www.postgresql.org/docs/current/transaction-iso.html
- https://typeorm.io/transactions

## FAQ

### Why was the leave approval system built from scratch instead of using an off-the-shelf module like Odoo HR?

Leave types, approval hierarchy, and quota rules are often specific to Indonesian labor law and company policy, so they may not fit an existing module cleanly. Adapting Odoo's HR module can require deep understanding of its internals, which can make custom development actually faster. Building from scratch also allows seamless integration with an existing NestJS and PostgreSQL codebase.

### How does the system prevent two managers from over-approving leave and exceeding an employee's quota simultaneously?

A PostgreSQL row-level lock (FOR UPDATE) is placed on the quota row at the start of the approval transaction. This means a concurrent approval attempt must wait until the first transaction commits before it can read the updated quota, preventing double-spending of leave days. All three operations—quota check, used_days increment, and status update—happen as a single atomic unit.

### Why are email notifications sent outside the database transaction rather than inside it?

Initially, emails were triggered inside the database transaction, and email delivery latency added visible delay to the API response. Moving email dispatch to a BullMQ queue (backed by Redis) after the transaction commits means failures are retried automatically without blocking the API, making approvals feel instant to the user.

### What is the purpose of using a PostgreSQL generated column for days_count on leave requests?

The days_count column is computed directly by the database from start_date and end_date using GENERATED ALWAYS AS, so it can never diverge from the actual stored dates due to an application-layer bug. It is also available in SQL queries without extra computation, which is particularly useful for department-level leave usage reports.

### What can cause leave quota drift, and how should it be detected?

Quota drift occurs when leave quotas or request statuses are updated directly in the database outside of the service methods—for example, during debugging or data migrations. The drift is silent and causes incorrect balance displays that erode user trust. It can be detected by running a reconciliation query that compares the sum of approved leave days against the used_days column.

---

- HTML version: https://www.matthewswong.com/en/blog/erp-hr-leave-approval-flow/
- Bahasa Indonesia: https://www.matthewswong.com/id/blog/erp-hr-leave-approval-flow/
- Site index for agents: https://www.matthewswong.com/llms.txt
- Sitemap: https://www.matthewswong.com/sitemap.xml
- API description (OpenAPI): https://www.matthewswong.com/openapi.json