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

> How to design and implement a configurable purchase order approval system with NestJS and PostgreSQL — covering routing rules, delegation, escalation, and audit trails.

URL: https://www.matthewswong.com/en/blog/erp-purchase-order-approval/

- Author: Matthews Wong (https://www.matthewswong.com/en/about/)
- Published: 2025-06-10
- Updated: 2025-06-10
- Category: 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. In my freelance ERP consulting work, 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. The system I built 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.

> From my experience building ERP systems as a freelance consultant: 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. The workflow I designed 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.

> **Warning: Race Conditions in Parallel Approvals**

> 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. I 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.

## How I Approached This

My 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.

## 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 the client's 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. I 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

- ApproveIt — Purchase Order Approval Workflow with AI (2025) — https://approveit.today/blog/purchase-order-approval-workflow-with-ai-rules-thresholds-templates-(2025)
- Versa Cloud ERP — ERP Purchase Order Automation: Key Advantages — https://www.versaclouderp.com/blog/erp-purchase-order-automation-key-advantages-for-businesses/
- Ziphq — The Essential Guide to Purchase Order Approval Workflows — https://ziphq.com/blog/purchase-order-approval-workflow

> **Key takeaway:** Multi-level purchase order approval in an ERP replaces WhatsApp chains with explicit routing rules, so every approval carries a named approver, a spending threshold and a timestamp. Research from ApproveIt puts the benefit at roughly 16 percent of annual vendor spend and 46 hours of manual procurement work saved each month.

## Source links

- https://approveit.today/blog/purchase-order-approval-workflow-with-ai-rules-thresholds-templates-(2025)
- https://www.versaclouderp.com/blog/erp-purchase-order-automation-key-advantages-for-businesses/
- https://ziphq.com/blog/purchase-order-approval-workflow

## FAQ

### How are approval tiers and amount thresholds determined in this system?

The system uses three configurable tiers: Tier 1 (Department Head) for POs under Rp 10,000,000; Tier 2 (Finance Manager) for Rp 10M–Rp 100M; and Tier 3 (Director) for amounts above Rp 100M. Vendor category also affects routing — capex purchases always require Director approval regardless of amount. Crucially, these rules are stored in an approval_rules database table and managed through an admin UI, so the finance admin can adjust thresholds without a code deployment.

### What happens when an approver is on leave or doesn't respond in time?

Any approver can delegate their pending approvals to a colleague for a specified date range, and the delegation is recorded in the audit trail. If no delegation exists and a step exceeds its deadline (default: 48 business hours), the system automatically escalates to the next-tier approver and notifies the original approver's manager. This mechanism eliminated the 'PO stuck in someone's inbox' problem that had previously stalled procurement velocity.

### How does the email approval flow work, and why was it built without requiring ERP login?

Each approval step triggers a transactional email via SMTP2GO containing direct approve/reject links embedded with a short-lived signed JWT token holding the step ID and approver's user ID. The NestJS endpoint validates the token, confirms the approver is still the active approver for that step, and processes the action. This stateless email approval was built so that busy directors who don't use the ERP daily can still act without logging in — it reduced average PO cycle time from 3.5 days to 18 hours in the first month post-launch.

### How are race conditions handled when multiple approvers act simultaneously?

When a workflow allows parallel approvals at the same tier, two approvers acting at the same moment can create duplicate approval records or incorrectly advance the workflow state. The solution is a PostgreSQL advisory lock on the PO ID — specifically SELECT pg_advisory_xact_lock(hashtext(po_id::text)) inside a transaction before reading or writing approval_steps. Application-level locks are explicitly avoided because they don't survive connection drops.

### What reporting does the system expose once approvals are recorded in PostgreSQL?

The workflow data in PostgreSQL powers a React dashboard exposing: average approval cycle time by department and tier, approval rate per approver (to identify bottlenecks), overdue approvals by current holder, and monthly PO spend by vendor and category. The cycle time metric was particularly valuable — it allowed the client's CFO to see for the first time exactly where purchase requests were stalling, which justified the ERP investment.

---

- HTML version: https://www.matthewswong.com/en/blog/erp-purchase-order-approval/
- Bahasa Indonesia: https://www.matthewswong.com/id/blog/erp-purchase-order-approval/
- 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