Building an HR Leave Approval Flow in a Custom 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.

HR Leave Approval Flow
  ──────────────────────

  Employee submits leave request
          │
          ▼
  ┌───────────────────────┐
  │  leave_requests table │
  │  status: PENDING      │
  │  approver_id: <mgr>   │
  └───────────┬───────────┘
              │ email notification sent
              ▼
  ┌───────────────────────┐
  │   Manager Review      │◀─── Manager opens approval link
  │   (configurable SLA)  │
  └──────┬────────┬───────┘
         │        │
       APPROVE  REJECT
         │        │
         ▼        ▼
  ┌──────────┐  ┌──────────┐
  │ APPROVED │  │ REJECTED │
  │ status   │  │ status   │
  └────┬─────┘  └────┬─────┘
       │              │
       ▼              ▼
  Update leave   Notify employee
  balance        with reason
  (quota table)
       │
       ▼
  Sync attendance
  calendar view

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.

-- PostgreSQL schema for leave management
CREATE TYPE leave_status AS ENUM ('PENDING', 'APPROVED', 'REJECTED', 'CANCELLED');
CREATE TYPE leave_type   AS ENUM ('ANNUAL', 'SICK', 'UNPAID', 'MATERNITY', 'PATERNITY');

CREATE TABLE leave_requests (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  employee_id   UUID NOT NULL REFERENCES employees(id),
  approver_id   UUID NOT NULL REFERENCES employees(id),
  leave_type    leave_type  NOT NULL,
  start_date    DATE NOT NULL,
  end_date      DATE NOT NULL,
  days_count    INTEGER GENERATED ALWAYS AS (end_date - start_date + 1) STORED,
  reason        TEXT,
  status        leave_status NOT NULL DEFAULT 'PENDING',
  rejection_reason TEXT,
  approved_at   TIMESTAMPTZ,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Leave quota per employee per year
CREATE TABLE leave_quotas (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  employee_id UUID NOT NULL REFERENCES employees(id),
  year        INTEGER NOT NULL,
  leave_type  leave_type NOT NULL,
  total_days  INTEGER NOT NULL,
  used_days   INTEGER NOT NULL DEFAULT 0,
  UNIQUE (employee_id, year, leave_type)
);

-- NestJS service: approve leave
// leave.service.ts
@Injectable()
export class LeaveService {
  constructor(
    @InjectRepository(LeaveRequest)
    private leaveRepo: Repository<LeaveRequest>,
    @InjectRepository(LeaveQuota)
    private quotaRepo: Repository<LeaveQuota>,
    private emailService: EmailService,
    private dataSource: DataSource,
  ) {}

  async approveLeave(requestId: string, approverId: string): Promise<LeaveRequest> {
    return this.dataSource.transaction(async (manager) => {
      const request = await manager.findOneOrFail(LeaveRequest, {
        where: { id: requestId, approverId, status: LeaveStatus.PENDING },
        relations: ['employee'],
      });

      // Check quota
      const quota = await manager.findOneOrFail(LeaveQuota, {
        where: {
          employeeId: request.employeeId,
          year: new Date(request.startDate).getFullYear(),
          leaveType: request.leaveType,
        },
      });

      const remaining = quota.totalDays - quota.usedDays;
      if (request.daysCount > remaining) {
        throw new BadRequestException(
          `Insufficient leave balance: ${remaining} days remaining`
        );
      }

      // Update quota and request atomically
      await manager.increment(
        LeaveQuota,
        { id: quota.id },
        'usedDays',
        request.daysCount,
      );

      request.status    = LeaveStatus.APPROVED;
      request.approvedAt = new Date();
      const saved = await manager.save(request);

      await this.emailService.sendLeaveApproved(request.employee.email, request);
      return saved;
    });
  }
}

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.