Spike: Avoiding partially paid invoices POC

Requirements: MODFISTO-34 - Getting issue details... STATUS

When an invoice is paid, numerous transactions (payments, credits) will be generated. Either all or none of these need to be successfully processed; we can't have partially paid invoices. This presents a challenge of scale. There could feasibly be hundreds of payments for an invoice.

Several options were weighed and the most attractive approach was documented on the wiki.

Given that this is a novel solution it makes sense to start with a POC to validate the proposed approach.

Overview

Since we have a strict requirement that either all payments for invoice must be processed successfully or all operations must be cancelled if any of them fail. It should also be noted that at the moment there is no common mechanism on FOLIO for managing distributed transactions. That's why we came up with our own approach to solving the problem. And the main purpose of this investigation is to check whether this approach is feasible.

Tables and Schemas

invoice_payment_summary

Contains an invoiceId and the total number of transactions (payments/credits) expected when "paying" this invoice

{
"tableName": "invoice_payment_summary",
"fromModuleVersion": 3.0
}

Payment summary model:

temporary_invoice_payments

Temporary storage of payments until all payments related to the invoice are received.

The declaration of the scheme is similar to the table "transactions". The difference is that:

• it makes sense to add a foreign key for "sourceInvoiceId" to connect to the "invoice_payment_summary" table;
• the search in this table will be performed only by "sourceInvoiceId", which means that we should create an index for this field.

{
	"tableName": "temporary_invoice_payments",
	"fromModuleVersion": 3.0,
	"withMetadata": true,
	"foreignKeys": [
		{
			"fieldName": "fiscalYearId",
			"targetTable": "fiscal_year",
			"tOps": "ADD"
		},
		{
			"fieldName": "fromFundId",
			"targetTable": "fund",
			"tOps": "ADD"
		},
		{
			"fieldName": "sourceFiscalYearId",
			"targetTable": "fiscal_year",
			"tOps": "ADD"
		},
		{
			"fieldName": "toFundId",
			"targetTable": "fund",
			"tOps": "ADD"
		},
		{
			"fieldName" "sourceInvoiceId",
			"targetTable": "invoice_payment_summary",
			"tOps": "ADD"
		}
	],
	"index": [
		{
			"fieldName": "sourceInvoiceId",
			"tOps": "ADD",
			"caseSensitive": false,
			"removeAccents": true
		}
	]
}

To store temporary payments, it is enough to reuse the transaction scheme. Since all we need is the transaction itself and the id of invoice (transaction.sourceInvoiceId) to which the transaction is linked. But since the "sourceInvoiceId" field is required to receive "payment_summary" and is not required by the scheme, it is necessary to check the presence of this field.

Basic approach

This approach involves three layers of logic: mod-invoice, mod-finance, mod-finance-storage.

Mod-invoice

The purpose of this layer is to prepare data for further processing in mod-finance-storage (Figure 1):

1. It is necessary to know the expected number of payments.
2. Check if the payments have already been processed. If yes, go to step 5.
3. Create payment_summary.
4. Send payments for processing
5. Update invoice status to Paid.

Figure 1 - Algorithm of payments/credits preparation

Difficulties

• It is possible that several users will try to pay the same invoice. For example, for the second user the step 1 will start at the moment when step 4 has already been completed for the first user. In order to avoid the re-processing of payments, we need to make sure that the payments have not been processed yet. This check should probably be transferred or duplicated in the mod-finance-storage module. We just need to ensure that the payment processing is idempotent 
• During the creation of payment_summary it may turn out that such a record already exists and finance-storage will return 400 errors. We can just ignore it and continue it if the record was successfully created (сould be considered as a trigger for a retry). INSERT ... ON CONFLICT DO NOTHING can be used.

Mod-finance

Two endpoints must be introduced:

• POST /finance/invoice-payment-summary - just proxy to /finance-storage/invoice-payment-summary
• POST /finance/payments - proxy to /finance-storage/payments with some "retry" logic.

Mod-finance-storage

Two endpoints must be introduced:

• POST /finance-storage/invoice-payment-summary - to create paymentSummary record in invoice_payment_summary table. Use the following to create a record - INSERT INTO ... ON CONFLICT DO NOTHING; 
• POST /finance-storage/payments - endpoint for the main part of the considered approach  (Figure 2) PaymentsAPI.java

Figure 2 - Algorithm of payments/credits processing

Difficulties

• How to avoid duplication of temporary transactions during a retry or in case several users try to pay for the invoice at the same time? The most reliable solution in my opinion is to create unique constraint for some field or set of fields. 
• It's an unlikely case, but... Let's consider the case when one user pays for an invoice with 20 payments and then another user does the same (in terms of application, this is seen as a retry). As a result, the following can happen: at the moment when the 19th payment is created for the first user, the second user will create the 20th payment. And then they both will receive the number of records equal to 20 and activate the payments processing. Which is not acceptable.

Questions to answer

Use of standard tables instead of jsonb?

Since we're exposing invoice-payment-summary APIs in the BL layer there is no sense for such table anymore.  We have to create a schema for invoice_payment_summary.

Any issues with "insert if not exist" when creating invoice_payment_summary records (to avoid potential race conditions)?

PostrgeSQl 9.5 brings support for "UPSERT" (INSERT ... ON CONFLICT DO NOTHING/UPDATE) operations.  The new command has no race conditions.

https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.5#INSERT_..._ON_CONFLICT_DO_NOTHING.2FUPDATE_.28.22UPSERT.22.29

Is the basic approach feasible?

The approach is feasible provided that the payment processing is idempotent.