Talk about the design of account reconciliation system

preface

As the cornerstone system of the payment system, the reconciliation system is at the last layer of the whole payment link, mainly used to ensure the consistency of our payment data with the data of the third-party payment channel or the bank.

Before the reconciliation system, the treasurer manually checked the previous day’s receivables and receipts on the second day. If not, it is necessary to check the data one by one to find the inconsistent data. After the appearance of the reconciliation system, this tedious manual operation can be reduced. The financial only needs to pay attention to the system’s reconciliation records every day, releasing productivity.

This article is mainly combined with the actual project experience, talk about the design of account checking system.

Overall system design

Account checking system design is mainly divided into the following four modules:

• Channel data processing module
• Data processing module
• Check module
• Differential data processing module

Module call order hierarchy diagram is as follows.

Let’s first introduce the channel data processing module.

Channel data processing module

This module is mainly responsible for downloading, parsing, and database of channel reconciliation files.

At present, the third-party payment channel reconciliation file download methods in the market are mainly divided into the following categories:

• The third-party channel periodically pushes the file to SFTP/FTP. This mode is relatively simple, we regularly from SFTP/FTP check files.
• Call the third-party channel reconciliation file download interface. This mode requires docking channel download reconciliation file interface, regularly call download. Alipay and wechat are in this mode.
• Manually download the reconciliation file from the payment channel website. This model is the least friendly and requires us to spend human resources to download files.

In addition to the download method, there will be some differences in the format of the reconciliation file. For example, alipay’s account checking file format is CSV, while wechat’s account checking file format is TXT, and some channels are XML and XLS.

The number and name of fields in the third-party channel reconciliation file are also different.

Generally, this layer needs to be developed specifically for each channel it accesses. This layer abstracts the interface, exposing the download and parse interfaces. Each access channel, implement the corresponding method of the interface.

This layer is not difficult to develop, as long as the reconciliation file format according to the corresponding parse file can be. Generally, the information in the reconciliation file needs to be extracted as follows:

Merchant Number Merchant order number Channel Serial number Transaction date Transaction amount Handling fee refund to original order numberCopy the code

Here are some of the details of developing this layer.

1. If you apply for more than one merchant number in the same channel. In this case, the third party channel will produce a reconciliation document for each merchant number if there was a transaction the day before. So here the system design needs to take into account the handling of multiple reconciliation documents. 2, the reconciliation file needs to consider the case of repeated download. In general, once the channel reconciliation file is generated, it will not change. However, the third-party channel may also be abnormal, resulting in the incomplete data of the reconciliation documents received by us. In this case, there needs to be a mechanism to re-download the parse into the library. 3, each third party channel download file time is different.

Data processing module

After the reconciliation file processing module, let’s look at the data processing module.

This module is mainly used to extract the flow data of all our successful payment of the previous day and the flow data of the statement of the previous day when the previous module was put into storage. To reduce the pressure on the database, the extracted data only needs to include the necessary fields, rather than extracting the entire row of data information. Generally speaking, as long as you need to withdraw the transaction time, amount, transaction order number, channel back serial number.

This layer is mainly about the query behavior of the database. It is best to use the standby database for data queries. Because here we need to extract the full amount of successful payment data of the previous day. In the case of a large amount of data, the main database may be slowed down and online payment transactions may be affected.

Check module

In this module, we use the data extracted from the previous module to check whether the order number and the amount are exactly the same. Check the pseudocode of the module as follows.

This process may produce three types of differential data.

In the first case, the local end data exists, but the peer end data does not exist, which is called local end multiple accounts.

In the second case, the peer data exists but the local data does not exist, which is called peer multiple accounts.

The third case is inconsistent amounts.

The three are shown in the figure.

The difference data generated here is stored in a difference table for use in the next module.

Differential data processing module

This module is mainly used to deal with the difference data generated by the previous module.

In the above three types of differential data, inconsistent amounts are quite rare, which requires manual judgment.

Let’s discuss the case of multiple accounts at the local end.

Local end multiple accounts is one of the most common reconciliation systems. This situation may occur as a result of the transaction of the day cut the problem, resulting in both bookkeeping date is inconsistent, resulting in unbalance.

Let’s first explain the concept of diurnal tangent.

Daily cutting, collodly speaking, is to change the system accounting time, the system from the current working day to the next working day. During this process, if our trade order happens to be at 23:59:59 on T day, then our account time is T day. The third party channel received the order at 00:00:01 on T+1, so the reconciliation date of this transaction on the third party channel is T+1.

This item will be missing in the t-day reconciliation documents of the third party channel, but this item does exist in our t-day data, which leads to a local multiple account differential data in the verification process.

For such differential data, we can choose to charge this data and wait T+1 working days for reconciliation. During the account reconciliation on T+1, there will be extra data in the statement accordingly, so that the difference data of multiple accounts at the peer end will be generated during the verification process.

Then, in the T+1 day differential processing module, the differential data of the previous days were extracted, and the multi-account data of the local end and the peer end were checked one by one. If the verification is consistent, update the difference status of the two strokes as processing completed. In the end, if there is no residual difference data, the account of the day will be settled.

The pseudocode is as follows:

There may be two cases of the occurrence of multiple accounts on the other end.

In the first case, the test environment and the production environment share a third party channel parameter, which results in the test environment trade order appearing in the statement as well. In this case, once we confirm that the data exists in the test environment, we can ignore the differential data.

In the second case, the local trade order exists, but the status is not successful. In this case, the query interface provided by the third-party channel needs to be invoked to query the final status of the order. If the query is successful, update the order status and change the differential data status to processed successfully.

If the third-party channel cannot query the order status. If the final payment of the order is confirmed with the channel, we need to change the payment order to payment success and modify the status of the differential account.

In the end, we reconciled the accounts again. Since the data of multiple accounts at the peer end will have corresponding data at the local end, there will be no differential data. The reconciliation was completed and the accounts were settled.

System optimization

At present, the scheduled task of the system reconciliation system adopts Spring timing function. The distributed scheduling program ElasticJob is added. The time of scheduled tasks can be quickly changed without restarting the program. And can quickly trigger scheduled tasks.

In short, the account checking system is not difficult to work, but the details are complicated, and it is difficult to consider all the details completely in the early stage. This process needs continuous improvement.

Recommended reading

How to design the payment reconciliation system?

Let’s talk about how to design the account reconciliation system

Old Phoenix Bear – Reconciliation

If you feel good, please help the author point a like ~ thank you

For those of you who like this article, please keep clicking on the subscription app. Let me share that with you.