From the analysis of the previous article, we have identified what this system does. What follows is the real stuff. After reading these contents carefully, I believe that you can design a good maintenance, good expansion of the transaction system on this basis.

Database design

Data is designed according to: transaction, refund, log design. For the above mentioned reconciliation and other functions are not. This is not too hard and you can design it yourself, just like I said above. The main tables are described as follows:

  • pay_transactionRecord all transaction data.
  • pay_transaction_extensionRecord the transaction ID generated each time a transaction is initiated to a third party
  • pay_log_dataAll log data, such as payment requests, refund requests, asynchronous notifications, etc
  • pay_repeat_transactionDuplicate payment data
  • pay_notify_app_logNotifying the application of logs
  • pay_refundRecord all refund data

Specific table structure:

-- -----------------------------------------------------
-- Table Creates the payment flow Table
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `pay_transaction` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `app_id` VARCHAR(32) NOT NULL COMMENT 'application id'.`pay_method_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Payment method ID, which can be used to identify payments, such as Alipay, wechat, Paypal, etc.'.`app_order_id` VARCHAR(64) NOT NULL COMMENT 'Application Order No.'.`transaction_id` VARCHAR(64) NOT NULL COMMENT 'Unique ID for this transaction, unique for the entire payment system, mainly because order_id may be duplicated for other applications.'.`total_fee` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Payment amount, saved as an integer'.`scale` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Decimal number corresponding to amount'.`currency_code` CHAR(3) NOT NULL DEFAULT 'CNY' COMMENT 'Currency of transaction'.`pay_channel` VARCHAR(64) NOT NULL COMMENT 'Payment channels selected, such as Huabei in Alipay, credit cards, etc.'.`expire_time` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Order Expiration Time'.`return_url` VARCHAR(255) NOT NULL COMMENT 'Redirect URL after payment'.`notify_url` VARCHAR(255) NOT NULL COMMENT 'After payment, asynchronously notify URL'.`email` VARCHAR(64) NOT NULL COMMENT 'User's Mailbox'.`sing_type` VARCHAR(10) NOT NULL DEFAULT 'RSA' COMMENT 'MD5 RSA RSA2 HASH-MAC etc.'.`intput_charset` CHAR(5) NOT NULL DEFAULT 'UTF-8' COMMENT 'Character set encoding'.`payment_time` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Successful time of third-party payment'.`notify_time` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'When the asynchronous notification was received'.`finish_time` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Time to inform upstream system'.`trade_no` VARCHAR(64) NOT NULL COMMENT 'Third Party serial Number'.`transaction_code` VARCHAR(64) NOT NULL COMMENT 'Real transaction code to third party, updated when asynchronous notification'.`order_status` TINYINT NOT NULL DEFAULT 0 COMMENT '0: pending payment, 1: pending payment completed, 2: completed payment, 3: this transaction is closed, -1: payment failed '.`create_at` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Creation time'.`update_at` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Update Time'.`create_ip` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'IP created, this may be the IP of its own service'.`update_ip` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Updated IP',
  PRIMARY KEY (`id`),
  UNIQUE INDEX `uniq_tradid` (`transaction_id`),
  INDEX `idx_trade_no` (`trade_no`),
  INDEX `idx_ctime` (`create_at`)),
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COMMENT = 'Initiated payment data';

-- -----------------------------------------------------
-- Table Indicates the transaction extension Table
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `pay_transaction_extension` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `transaction_id` VARCHAR(64) NOT NULL COMMENT 'System Unique Transaction ID'.`pay_method_id` INT UNSIGNED NOT NULL DEFAULT 0.`transaction_code` VARCHAR(64) NOT NULL COMMENT 'Generate order number to transfer to third party'.`call_num` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Number of calls made'.`extension_data` TEXT NOT NULL COMMENT 'Extension content, need to save: transaction_code and trade no mapping, fill when asynchronous notification'.`create_at` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Creation time'.`create_ip` INT UNSIGNED NOT NULL COMMENT 'create IP',
  PRIMARY KEY (`id`),
  INDEX `idx_trads` (`transaction_id`.`pay_status`),
  UNIQUE INDEX `uniq_code` (`transaction_code`)),
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COMMENT = 'Transaction Extension Table';

-- -----------------------------------------------------
-- Table All logs of the transaction system
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `pay_log_data` (
  `id` BIGINT UNSIGNED NOT NULL.`app_id` VARCHAR(32) NOT NULL COMMENT 'application id'.`app_order_id` VARCHAR(64) NOT NULL COMMENT 'Application Order No.'.`transaction_id` VARCHAR(64) NOT NULL COMMENT 'Unique ID for this transaction, unique for the entire payment system, mainly because order_id may be duplicated for other applications.'.`request_header` TEXT NOT NULL COMMENT 'Request header'.`request_params` TEXT NOT NULL COMMENT 'Request parameters for payment'.`log_type` VARCHAR(10) NOT NULL COMMENT 'Log type, payment: payment; Refund: a refund; Notify: indicates asynchronous notification. Return: synchronous notification; The query: query '.`create_at` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Creation time'.`create_ip` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'create IP',
  PRIMARY KEY (`id`),
  INDEX `idx_tradt` (`transaction_id`.`log_type`)),
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COMMENT = 'Transaction Log Table';


-- -----------------------------------------------------
-- Table duplicate payment transactions
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `pay_repeat_transaction` (
  `id` BIGINT UNSIGNED NOT NULL.`app_id` VARCHAR(32) NOT NULL COMMENT 'Application ID'.`transaction_id` VARCHAR(64) NOT NULL COMMENT 'System Unique Identification Transaction Number'.`transaction_code` VARCHAR(64) NOT NULL COMMENT 'When payment is successful, the code of the transaction'.`trade_no` VARCHAR(64) NOT NULL COMMENT 'Third Party transaction Number'.`pay_method_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Method of Payment'.`total_fee` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Transaction Amount'.`scale` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Decimal number'.`currency_code` CHAR(3) NOT NULL DEFAULT 'CNY' COMMENT 'Payment currency, CNY, HKD, USD, etc.'.`payment_time` INT NOT NULL COMMENT 'Third Party Trading Hours'.`repeat_type` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT 'Repeat type: 1 payment through the same channel, 2 payment through different channels'.`repeat_status` TINYINT UNSIGNED DEFAULT 0 COMMENT 'Processing state,0: not processed; 1: Processed '.`create_at` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Creation time'.`update_at` INT UNSIGNED NOT NULL COMMENT 'Update Time',
  PRIMARY KEY (`id`),
  INDEX `idx_trad` ( `transaction_id`),
  INDEX `idx_method` (`pay_method_id`),
  INDEX `idx_time` (`create_at`)),
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COMMENT = 'Record duplicate payments';


-- -----------------------------------------------------
-- Table Notifies upstream application logs
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `pay_notify_app_log` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `app_id` VARCHAR(32) NOT NULL COMMENT 'application id'.`pay_method_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Method of Payment'.`transaction_id` VARCHAR(64) NOT NULL COMMENT 'Transaction Number'.`transaction_code` VARCHAR(64) NOT NULL COMMENT 'When payment is successful, the code of the transaction'.`sign_type` VARCHAR(10) NOT NULL DEFAULT 'RSA' COMMENT 'Adopted signature: MD5 RSA RSA2 HASH-MAC etc.'.`input_charset` CHAR(5) NOT NULL DEFAULT 'UTF-8'.`total_fee` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Amount involved, no decimal'.`scale` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Decimal number'.`pay_channel` VARCHAR(64) NOT NULL COMMENT 'Payment channel'.`trade_no` VARCHAR(64) NOT NULL COMMENT 'Third Party Transaction Number'.`payment_time` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Time of payment'.`notify_type` VARCHAR(10) NOT NULL DEFAULT 'paid' COMMENT 'Notice type, paid/refund/canceled'.`notify_status` VARCHAR(7) NOT NULL DEFAULT 'INIT' COMMENT 'Notify the payment caller of the result; INIT: initialization, PENDING: ongoing; SUCCESS D. FAILED: FAILED.`create_at` INT UNSIGNED NOT NULL DEFAULT 0.`update_at` INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  INDEX `idx_trad` (`transaction_id`),
  INDEX `idx_app` (`app_id`.`notify_status`)
  INDEX `idx_time` (`create_at`)),
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COMMENT = 'Pay caller record';


-- -----------------------------------------------------
- Table a refund
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `pay_refund` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `app_id` VARCHAR(64) NOT NULL COMMENT 'application id'.`app_refund_no` VARCHAR(64) NOT NULL COMMENT 'Upstream refund ID'.`transaction_id` VARCHAR(64) NOT NULL COMMENT 'Transaction Number'.`trade_no` VARCHAR(64) NOT NULL COMMENT 'Third Party Transaction Number'.`refund_no` VARCHAR(64) NOT NULL COMMENT 'Unique refund receipt number generated by payment platform'.`pay_method_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Method of Payment'.`pay_channel` VARCHAR(64) NOT NULL COMMENT 'Payment channels selected, such as Huabei in Alipay, credit cards, etc.'.`refund_fee` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Refund amount'.`scale` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Decimal number'.`refund_reason` VARCHAR(128) NOT NULL COMMENT 'Reason for Refund'.`currency_code` CHAR(3) NOT NULL DEFAULT 'CNY' COMMENT 'CNY USD HKD'.`refund_type` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Type of refund; 0: service refund. 1: Repeat refund '.`refund_method` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT 'Refund method: 1. Automatic return; 2 Manual Payment '.`refund_status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0 not refunded; 1 refund processing; 2. Successful refund; 3 Refund unsuccessful '.`create_at` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Creation time'.`update_at` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Update Time'.`create_ip` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Request source IP'.`update_ip` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Request source IP',
  PRIMARY KEY (`id`),
  UNIQUE INDEX `uniq_refno` (`refund_no`),
  INDEX `idx_trad` (`transaction_id`),
  INDEX `idx_status` (`refund_status`),
  INDEX `idx_ctime` (`create_at`)),
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COMMENT = 'Refund Record';
Copy the code

Table usage logic is simply described below:

For payment, you first need to log the request to pay_log_data, and then generate transaction data to record in pay_transaction and pay_transaction_extension.

When a notification is received, the data is recorded to pay_log_data, and the status of pay_TRANSACTION and pay_refund is updated depending on whether the notification is for payment or refund. If the payment is repeated, record the data to PAY_REPEAT_transaction. The data that needs to be notified to the application is recorded in pay_notify_app_log, which acts as a message table for consumers to consume.

Refund record logs Logs are recorded to pay_log_data and then recorded to pay_refund in the refund table.

Of course, there are some details, you need to look at the table structure, actually think about how to use. If you have any questions, please leave a comment on our GitHub project and we will answer them.

These tables can meet the most basic needs, other content can be expanded according to their own needs, such as: support user card list, refund card, etc.

The system design

This part mainly says how to set up the system, as well as the suggestion of code organization.

System architecture

Because the security of the payment system is very high, it is not recommended to expose the corresponding entry directly to the user. Should be in their own application system to call the interface of the payment system to complete business. In addition, the data requirements of the system are: strong consistency. So there is no caching involved (when caching can be used for alerting, which is not standard).

In the implementation, the system uses two domain names, one for internal use, and only the IP from the specified source can access fixed functions (access other functions than notifications). Another domain name can access only two notify Return routes. In this way, system security can be ensured.

On the use of the database no matter what requests go directly to the Master library. This ensures strong consistency of data. Of course, from the library is also needed. For example: bill, reconciliation related logic we can use from the library to complete.

Code design

Whatever you want to do eventually has to be done in code. We all know the need for maintainable, extensible code. So what do you do specifically with the payment system? I have paid for the example of my code structure design ideas. For reference only. For example, I want to intervene: wechat, Alipay, CMB three pay. My code structure diagram is as follows:

A brief introduction in words. I will encapsulate each third party into XXXGateway class, which simply encapsulates the third party interface internally. No matter the other party is HTTP request or SOAP request, they will be processed uniformly internally.

An additional layer of XXXProxy encapsulates these third-party capabilities. This layer does two main things: personalize the data that comes in requesting payment. The unified processing of the returned structure returns the upper unified structure. Of course there are special cases where all business can be done;

The changes are almost completely encapsulated by the above operations. If we add a new payment channel. Only add XXXGateway and XXXProxy.

So what’s the use of Context and Server? The Server encapsulates all the business logic internally and provides interfaces for actions or other servers to invoke. The value of the Context layer is to handle errors returned by the Proxy layer. And alarm related processing here.

The above structure is just a practice of mine, and I welcome your discussion.

The system described in this article only meets the most basic payment requirements. Lack of relevant monitoring and alarm. You can leave a comment on our GitHub page

My official account is dayuTalk

Contact email: [email protected]

GitHub：github.com/helei112g