Design and Implementation of an Approval Workflow System with Database Schema and Backend Code

The article presents a comprehensive guide to constructing an approval workflow system, covering the entire lifecycle from process analysis and UI design to database schema definition and backend implementation.

It first analyzes four types of workflow logic: serial flow where each node must be approved before the next, parallel flow (countersignature and co‑sign) allowing multiple reviewers simultaneously, conditional flow that selects different paths based on rules, and mixed flow that combines the previous patterns.

Next, the article enumerates the possible approval actions (approve, reject, save, transfer, add sign, copy) and execution actions (apply, approve, return, veto, withdraw), explaining their meanings and when they are used.

The UI preview section shows mock‑up screenshots of the approval interface, illustrating how the process is presented to end users.

For data persistence, four MySQL tables are defined with full DDL statements:

CREATE TABLE `approval_config` (
  `approval_config_id` varchar(255) NOT NULL COMMENT 'snowflake id',
  `corp_id` varchar(100) DEFAULT NULL COMMENT 'company id',
  `node_name` varchar(100) DEFAULT NULL COMMENT 'node name',
  `form_id` varchar(50) DEFAULT NULL COMMENT 'form id',
  `approval_auto_pass` tinyint(1) DEFAULT '0' COMMENT 'auto‑pass when approvers are the same',
  `current_node_approver_type` tinyint DEFAULT NULL COMMENT 'approver type (1: initiator, 2: role, 3: user, 4: form user)',
  `current_node_approver` varchar(255) DEFAULT NULL COMMENT 'approver ids or role ids',
  `care_of` tinyint(1) DEFAULT NULL COMMENT 'allow transfer',
  `approval_type` tinyint DEFAULT NULL COMMENT '1: countersign, 2: co‑sign',
  `approval_node` int DEFAULT NULL COMMENT 'node order',
  `carbon_copy_recipients_type` tinyint DEFAULT '1' COMMENT 'copy recipient type',
  `carbon_copy_recipients` varchar(255) DEFAULT NULL COMMENT 'copy recipient ids',
  `create_time` datetime DEFAULT NULL COMMENT 'creation time',
  `group_id` varchar(255) DEFAULT NULL COMMENT 'group identifier',
  `visible_fields` json DEFAULT NULL COMMENT 'field visibility rules',
  PRIMARY KEY (`approval_config_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='approval process node configuration';

CREATE TABLE `approval_info` (
  `approval_info_id` varchar(255) NOT NULL COMMENT 'snowflake id',
  `corp_id` varchar(50) NOT NULL COMMENT 'company id',
  `approval_config_group_id` varchar(255) DEFAULT NULL COMMENT 'config group id',
  `form_id` varchar(50) DEFAULT NULL COMMENT 'form id',
  `founder` varchar(255) NOT NULL COMMENT 'initiator',
  `launch_time` datetime NOT NULL COMMENT 'launch time',
  `end_state` tinyint(1) DEFAULT '0' COMMENT 'process finished flag',
  `end_time` datetime DEFAULT NULL COMMENT 'finish time',
  `object_id` varchar(255) DEFAULT NULL COMMENT 'related data id',
  `process_progress` int DEFAULT '0' COMMENT 'current node index',
  `record_node_history` varchar(255) DEFAULT NULL COMMENT 'comma‑separated node history',
  PRIMARY KEY (`approval_info_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='approval process instance';

CREATE TABLE `approval_record` (
  `approval_record_id` varchar(50) NOT NULL COMMENT 'snowflake id',
  `corp_id` varchar(50) NOT NULL COMMENT 'company id',
  `approval_info_id` varchar(50) NOT NULL COMMENT 'linked approval_info',
  `form_id` varchar(50) NOT NULL COMMENT 'form id',
  `approver_user_id` varchar(50) NOT NULL COMMENT 'approver or transferee id',
  `approver_time` datetime NOT NULL COMMENT 'approval time',
  `approver_opinions` varchar(255) DEFAULT NULL COMMENT 'comments',
  `approver_result` tinyint NOT NULL COMMENT '1: agree, 2: reject, 3: transfer',
  `transferee` varchar(50) DEFAULT NULL COMMENT 'transferred to user id',
  `accessory_url` varchar(255) DEFAULT NULL COMMENT 'attachments',
  `approver_node` tinyint NOT NULL COMMENT 'node number',
  `approver_count` tinyint NOT NULL COMMENT 'approval attempt count',
  PRIMARY KEY (`approval_record_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='approval record';

CREATE TABLE `approval_carbon_copy` (
  `carbon_copy_id` varchar(255) NOT NULL COMMENT 'snowflake id',
  `corp_id` varchar(255) NOT NULL COMMENT 'company id',
  `form_id` varchar(255) NOT NULL COMMENT 'form id',
  `approval_info_id` varchar(255) NOT NULL COMMENT 'linked approval_info',
  `user_id` varchar(255) NOT NULL COMMENT 'copy recipient',
  `create_time` datetime NOT NULL COMMENT 'copy time',
  `read` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'read flag',
  `approval_node` tinyint NOT NULL COMMENT 'node where copy occurs',
  PRIMARY KEY (`carbon_copy_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='approval carbon copy records';

The implementation logic explains how the system reads the configuration table to determine approvers and visible fields for each node, creates an approval_info record when a new request is submitted, and updates the process progress as actions are taken.

Key Java methods are provided: the approval method handles token validation, node lookup, approver verification, and delegates to specific handlers; approvalRefuse records a rejection and rolls back to the previous node; approvalAgreeing records an agreement, checks whether the node is a countersignature and whether all required approvers have acted before moving to the next node; and mergePowerGroup merges custom permission groups based on role intersections.

Overall, the article serves as a practical reference for developers building customizable, multi‑step approval processes with fine‑grained permission control in a Java‑backed, MySQL‑driven environment.