Multi Level Approval System Design

Designing unlimited level approval for purchase request system with set of rules including purchase amount.

Welcome to Coderbased, a system design newsletter that literally about designing a system that become foundation of a product and feature.

Before we start, kindly to subscribe, follow me on Linkedin, Twitter and share this content to your friends. Enjoy.

Hi, long time no see! We are rebranding as Coderbased, a community for coders focused on system design to build products and features. You can read more about why we decided to rebrand and focus on this specific concept here.

---

Product & System Requirements

Suppose we are software engineers for an enterprise company with an in-house ERP system. The ERP system includes a purchase module that allows employees to create purchase requests.

We have been asked to enhance the system by developing a multi-level approval process for purchase requests. For example, when an employee submits a purchase request, it needs to be approved by their supervisor. After the supervisor approves it, the request must then be approved by the manager before it can be executed.

Employee → Supervisor → Manager (2-Level Approval).

Requirements & Scope:

While, ideally, the ERP system would require a multi-level approval process for almost all documents, let's focus solely on purchase requests (so we don't need to develop an agnostic approval system).

• The number of levels (stages) can be unlimited.

• Approvers can be based on the line of superiority or selected as specific individuals.

• A minimum number of approvers can be set before the workflow advances to the next level.

• There can be rules based on the amount of the purchase request. For example:

  • If the request is <$100, it needs only one level of approval by a superior.

  • If the amount is ≥$100, it requires approval up to the managerial level.

• You can make assumptions regarding the employee structure (line of superiority).

To understand more, check the product illustration below.

Product/Feature Illustration

Before we present the proposed solution, we believe there are many ways to design a system to solve problems. If you have a different idea, we encourage you to contribute and share it by filling out the form at this link.

We will post your solution if it is good and effectively solves the problem.

Proposed Solution

High Level Design & Architecture

At a high level, there are four components and technologies that we use to solve the multi-approval system:

1. Purchase Service: Manages purchasing data and flow, including purchase requests.

2. Approval Service: Manages approval configurations and the actual approval transactions for specific purchase requests.

3. Postgres or MySQL: Both are ACID-compliant databases, which are crucial for an enterprise system. In this case, we are using Postgres.

4. Message Broker (RabbitMQ or Kafka): Facilitates asynchronous communication between the Purchase Service and the Approval Service, ensuring they remain loosely coupled.

There are two main user flows required to handle the multi-level approval process:

• Purchase Request Creation: When an employee creates a purchase request, it will go through the existing Purchase Service. In our proposed solution, an additional step will inform the Approval Service to prepare approval transaction data based on the configured rules.

• Approval Flow: After the purchase request is created, its status will be set to WAIT_APPROVAL. The approval process will then proceed sequentially through stages 1, 2, and so on. If the request is rejected or all stages are approved, the Approval Service will inform the Purchase Service via the message broker, updating the status to APPROVED or REJECTED

We'll dive into the details of how this works in the use case section below.

Database Diagram (Schema)

There are four main tables for the multi-level approval system: three for configuration and one for actual transactions:

• pr_approval_workflow: This is the main table for configuring the approval workflow. You can apply rules based on the purchase request amount by setting min_amount and max_amount. If more than one rule is applied, the workflow with the higher weight will be selected.

• pr_approval_employee: This table indicates who is eligible for a specific workflow. If employee_id is set to *, it means the workflow is applicable to all employees.

• pr_approval_workflow_stage: This table configures the stages/levels of the workflow, including who the approvers are and how many approvals are required before moving to the next level.

• pr_approval_transaction: This table records the actual approval transactions, with a direct relation to the purchase request data.

Let’s Breakdown the Design One by One

Approval Workflow Configuration

The first thing we need to do is to define the workflow configuration, with at least one workflow needing to be designed. To make this clearer, let's use some data examples.

• Unlimited level configuration
  Let's look at the pr_approval_workflow and pr_approval_workflow_stage tables. With this setup, we can create unlimited approval levels. For example, "workflow 1" has a single approval level, while "workflow 2" has three levels of approval

• Purchase Request Amount-Based Rule:
  In the pr_approval_workflow table, workflows with IDs 1 and 2 are both eligible for all employees. However, the min_amount and max_amount fields are set so that purchase requests up to $100 will follow "workflow 1," while anything above $100 will follow "workflow 2."

• Specific Workflow by Weight:
  We can use the weight field for specific cases. For example, if a head-level employee makes a request over $100, they may only need approval directly from the CTO.

Create Purchase Request Flow

What happens in the Approval Service when a purchase request is created?

Step 1: Identify the Applicable Workflow

Find the specific workflow that applies based on the eligible employee, purchase amount, and workflow weight.

SELECT paw.* 
FROM pr_approval_workflow paw
INNER JOIN pr_approval_workflow_employee pawe on pawe.workflow_id = paw.id
WHERE pawe.employee_id in ('*', <employee id>)
AND <purchase request amount> BETWEEN paw.min_amount AND paw.max_amount
ORDER BY paw.weight desc 
LIMIT 1

Step 2: Populate pr_approval_transaction
Next, fill the pr_approval_transaction table using data from pr_approval_workflow_stage and the employee table.

Assume the requester is STF-001 (Phillip McDaniel), and the request amount is $100, so "workflow 2" will be applied. The expected result would look like the image below.

Columns id, purchase_request_id, workflow_id, workflow_stage_id, level, and approver_count should replicate the data from the pr_approval_workflow_stage table.

Columns approver_list, reject_list, and status columns will be filled with default values since no one has approved or rejected the transaction yet. The status WAIT_APPROVAL indicates that the transaction is the currently active level.

The tricky part is the approver column, which is populated based on the approver_type from the pr_approval_workflow_stage table:

• If approver_type is EMPLOYEE, fill the column with the approver_id from the pr_approval_workflow_stage table.

• If approver_type is SUPERIOR, find the approver from the employee table. The approver_superior_level column in the pr_approval_workflow_stage table indicates how many levels above the requester the approver is:

  • 1 = direct superior

  • 2 = superior of the superior

  • 3 = superior of the superior's superior

  • and so on.

Approval Flow

Approval Level 1

The approval flow is straightforward. Every approver who needs to approve a transaction simply queries the pr_approval_transaction table. In this example, the first approver is SPV-001 (Sophie Sims), so:

SELECT * FROM pr_approval_transaction pat
WHERE 'SPV-001' = any(pat.approver)
AND status = 'WAIT_APPROVAL'

The query above will show all approval transactions that need to be approved or rejected by SPV-001.

• If a transaction is rejected, the Approval Service will publish a message to the Message Broker, which will notify the Purchase Service to change the purchase request status from WAIT_APPROVAL → REJECTED.

• If a transaction is approved, the next level (stage) of the approval process must be identified, and the status (of next level approval process) should be changed from PENDING to WAIT_APPROVAL:

UPDATE pr_approval_transaction SET status = 'APPROVED' WHERE id = $1

UPDATE pr_approval_transaction 
SET status = 'WAIT_APPROVAL'
WHERE workflow_id = <current workflow_id> 
AND level = <current level + 1>

Approval Level 2

The flow for Level 2 is similar to Level 1, but since MGR-1 is the approver at this level, the query is:

SELECT * FROM pr_approval_transaction pat
WHERE 'MGR-1' = any(pat.approver)
AND status = 'WAIT_APPROVAL'

If approved, the process is updated to the next level:

UPDATE pr_approval_transaction SET status = 'APPROVED' WHERE id = $1

UPDATE pr_approval_transaction 
SET status = 'WAIT_APPROVAL'
WHERE workflow_id = <current workflow_id> 
AND level = <current level + 1>

Approval Level 3 (Final Level)

The flow for Level 3 is similar to Levels 1 and 2, but since PMG-1 is the approver at this level, the query is:

SELECT * FROM pr_approval_transaction pat
WHERE 'PMG-1' = any(pat.approver)
AND status = 'WAIT_APPROVAL'

If the transaction is rejected, a notification will be sent. If all levels are approved, the process continues as follows:

UPDATE pr_approval_transaction SET status = 'APPROVED' WHERE id = $1

UPDATE pr_approval_transaction 
SET status = 'WAIT_APPROVAL'
WHERE workflow_id = <current workflow_id> 
AND level = <current level + 1>

At this point, the last update statement will return no results since Level 3 is the final approval level. In this case, we need to trigger a message with ALL_APPROVED to the Message Broker, which will notify the Purchase Service to change the “purchase request status” from WAIT_APPROVAL → APPROVED.

---

That’s all for today. If you have a system design topic you're interested in, or any other interesting system design solution for specific topic, don't hesitate to let me know—I’d be more than happy to share them here.
Propose Topic For Next Post
Propose Your Solution