Auto Transaction Failsafe's, Third Party API's, Coldfusion Schedule Files

Question

I have an automated invoicing web app and I'm trying to build in some failsafes and a structure that will, under no circumstance, allow an invoice to be double charged.

All things working perfectly, the scheduled task runs and everything charges as it's supposed to. But there are a few scenarios we know of that can cause problems.

I've had a DB server go down in the middle of a process, which lead to a number of duplicate charges. Also if for whatever reason, the file needs to be checked in a browser, a developer hits refresh before the file is done processing, duplicate charges will occur.

There are a few similar scenarios to protect against, so you can see the need for a way to handle this given the sensitivity of the transactions (imaging seeing your power company auto charging you twice on your bank statement).

Current configuration:

• Query

• API POST For Transaction

  <!--- Parse Reply String --->
  if response = "1"
  
   - Insert Into Payments
  
   - Update Invoice Amount Due
  
   - Insert Into Transactions
  
   - Email Accepted Notification
  
  else
  
   - Email Declined Notification
  

There's a few more smaller processes that happen between a few of these, as well as quite a few if statements but those are the heavy lifting parts.

What I would like to learn and hear about are what would be the best way to handle safeguarding this with considering the Third Party API for the gateway being there?

How would you break it up?

One idea I had was flagging each process after it ran to give a record of where it was when it failed, and build out the query to handle it accordingly. Not sure how efficient or more likely how inefficient it would be.

Answer 1

In complex ERP systems when a record needs to be protected there is usually a locking mechanism in place somewhere. In most systems this involves either a DB table or a flag on the table itself.

So one way of doing this is to have an table (say, InvEdit) that contains the invoice ID. The first thing you do is to put the invoice id's of the invoices you wish to edit in the table. Next you do the work on a per invoice basis as a single transaction. The last act of the transaction would be to remove the invoice from your invEdit table. You would naturally also want to change something about the invoice so that it would not be put in the invEdit table again (but you are probably already doing that - updating a status or a date or whatever).

If a transaction fails (because the DB goes down or whatever) that data is not commited and the Invoice ID remains in the invEdit table. If it succeeds it is removed. If the DB goes down OR if the item is refreshed or the Web server crashes or whatever you can pick up where you left off - from the "queue" provided by your invEdit table.

This is just one approach of course. I'm sure that others can think of more.

Answer 2

If all of the queries are required to run in order for a transaction to be considered complete, then you need to the entire thing in a transaction, and roll it back if there is a problem. You should do something like this:

<cftransaction action="begin">
    <cftry>

         - Insert Into Payments

         - Update Invoice Amount Due

         - Insert Into Transactions

         <cftransaction action="commit" />

        <cfcatch type = "any">
            <cftransaction action="rollback" />
            <cfrethrow />
        </cfcatch>
    </cftry>

</cftransaction>

If everything in your inserts, updates, etc happens correctly, then the transaction will commit. If there are any errors, then the transaction will be rolled back, or the commit will simply never happen.

The transaction should also lock the table so that any requests to modify it should be blocked. Before you start doing the inserts and updates you should have a check to make sure that they should be done.