Building a Multi-Level Purchase Order Approval Workflow in a Custom ERP

Companies that automate purchase order approvals save an average of 16% annually on vendor spend and eliminate 46 hours per month of manual procurement work, according to research from ApproveIt. Yet in most Indonesian SMEs I've encountered, purchase orders still travel via WhatsApp, get approved by whoever responds first, and leave no audit trail. At Commsult Indonesia, I built a multi-level PO approval workflow that routes requests through the right approvers — based on amount thresholds, department, and vendor — and escalates automatically if approvals stall. This post explains the full architecture.

How PO Approval Workflows Go Wrong

Before designing the solution, I catalogued the most common failure modes in manual PO approval: routing to the wrong approver (no clear rule), approvals silently expiring, no record of who approved what, and no enforcement of spending limits. Each of these translates into a specific system requirement: configurable routing rules, timeout-based escalation, full audit logging, and threshold enforcement. The workflow engine I built handles all four.

Approval Tiers and Amount Thresholds

Indonesian business culture typically requires multi-level sign-off on purchases above certain amounts — a cultural norm that actually aligns well with internal controls. Our system supports configurable tiers: Tier 1 (Department Head) for POs under Rp 10,000,000; Tier 2 (Finance Manager) for Rp 10M–Rp 100M; Tier 3 (Director) for above Rp 100M. Each tier can also be gated by vendor category — capex purchases always require Director approval regardless of amount. These rules are stored in a database table and configurable by the finance admin without a code deployment.

PO Approval Workflow — NestJS + PostgreSQL

  ┌─────────────────────────────────────────────────────┐
  │  Staff submits Purchase Request                     │
  └──────────────────────┬──────────────────────────────┘
                         │  ApprovalRulesService
                         │  reads threshold + dept rules
                         ▼
  ┌─────────────────────────────────────────────────────┐
  │  approval_steps created (one row per tier)          │
  │  ┌─────────────────────────────────────────────┐   │
  │  │ Step 1 │ Dept Head   │ deadline: +48h │ PEND│   │
  │  │ Step 2 │ Fin Manager │ deadline: +48h │ WAIT│   │
  │  │ Step 3 │ Director    │ deadline: +48h │ WAIT│   │
  │  └─────────────────────────────────────────────┘   │
  └──────────────────────┬──────────────────────────────┘
                         │
          ┌──────────────┴──────────────┐
          │                             │
     Email sent                  Step times out?
     (signed JWT)                       │
          │                      EscalationService
    Approver clicks               escalates to
    Approve/Reject                next tier + notifies
          │                       manager
          ▼
  ┌──────────────────────┐
  │ Step marked APPROVED │
  │ Next step activates  │
  └──────────────────────┘

From my experience building ERP systems at Commsult: never hard-code approval thresholds. The finance director will adjust them twice a year at minimum, and a code deployment for a threshold change is a waste of everyone's time. Store thresholds in an approval_rules table and build a simple admin UI to manage them. Two hours of upfront configuration work saves weeks of future change requests.

Database Schema and NestJS Implementation

The core schema has three tables: purchase_orders (the document), approval_steps (one row per required approver per PO), and approval_actions (the actual approve/reject/delegate events). When a PO is submitted, a NestJS service reads the applicable approval rules and creates the approval_steps rows in sequence. Each step has a status (pending, approved, rejected, delegated, timed_out) and a deadline timestamp. A scheduled job runs every hour and escalates any step past its deadline.

Delegation and Escalation Logic

A real-world requirement that textbooks miss: approvers go on leave. Our system allows any approver to delegate their pending approvals to a colleague for a specified date range — the delegation is recorded in the audit trail. If no delegation exists and a step times out (default: 48 business hours), the system automatically escalates to the next tier approver and sends a notification to the original approver's manager. This eliminates the 'PO stuck in someone's inbox' problem that killed procurement velocity in the manual process.

-- PostgreSQL: Core approval tables

CREATE TABLE approval_rules (
  id             UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tier           INT  NOT NULL,         -- 1, 2, 3
  min_amount     NUMERIC(15,2),
  max_amount     NUMERIC(15,2),
  department_id  UUID REFERENCES departments(id),
  approver_role  VARCHAR(50) NOT NULL,  -- 'dept_head', 'finance_mgr', 'director'
  timeout_hours  INT  NOT NULL DEFAULT 48
);

CREATE TABLE approval_steps (
  id             UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  po_id          UUID NOT NULL REFERENCES purchase_orders(id),
  tier           INT  NOT NULL,
  approver_id    UUID REFERENCES users(id),
  status         VARCHAR(20) NOT NULL DEFAULT 'pending',
  deadline       TIMESTAMPTZ NOT NULL,
  acted_at       TIMESTAMPTZ,
  CHECK (status IN ('pending','approved','rejected','delegated','timed_out'))
);

-- NestJS: Advisory lock for race-condition safety
async processApproval(poId: string, stepId: string, action: string) {
  await this.dataSource.transaction(async (em) => {
    // Acquire advisory lock keyed on PO ID hash
    await em.query(
      "SELECT pg_advisory_xact_lock(hashtext($1))",
      [poId]
    );
    const step = await em.findOneOrFail(ApprovalStep, {
      where: { id: stepId, status: 'pending' }
    });
    step.status = action;  // 'approved' | 'rejected'
    step.actedAt = new Date();
    await em.save(step);
    if (action === 'approved') {
      await this.activateNextStep(em, poId, step.tier);
    }
  });
}

How We Built This at Commsult

Our implementation uses NestJS with TypeORM for the workflow engine, PostgreSQL for state storage, and Bull for background jobs. The approval UI is a React component that shows the current step, who has approved, who is pending, and the full history timeline. Approvers receive email notifications with direct approve/reject links that use signed JWT tokens — they can approve without logging into the ERP, a critical UX feature for busy directors who don't use the system daily. The email links expire after 48 hours, at which point the step auto-escalates.

If your workflow allows parallel approvals (multiple approvers at the same tier), beware of race conditions where two approvers act simultaneously and your code creates duplicate approval records or incorrectly advances the workflow. We solved this with a PostgreSQL advisory lock on the PO ID during any state transition. The pattern: SELECT pg_advisory_xact_lock(hashtext(po_id::text)) inside a transaction before reading and writing approval_steps. Never use application-level locks for this — they don't survive connection drops.

Notifications and the Email Approval Flow

Each approval event triggers a transactional email via SMTP2GO (our email provider in production). The approve/reject links in the email embed a short-lived signed token containing the step ID and the approver's user ID. The NestJS endpoint validates the token, checks the approver is still the active approver for that step (delegation or escalation might have changed it), and processes the action. This stateless email approval is one of the highest-value features — it cut our average PO cycle time from 3.5 days to 18 hours in the first month post-launch.

Reporting and Analytics

Once the workflow data is in PostgreSQL, building reporting is straightforward. We expose: average approval cycle time by department and tier, approval rate by approver (useful for identifying bottlenecks), overdue approvals by current holder, and monthly PO spend by vendor and category. These feed a React dashboard the finance manager checks each Monday morning. The cycle time metric alone justified the ERP investment to the client's CFO — they could see, for the first time, exactly where purchase requests were stalling.

Sources & Further Reading