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:
<cfquery name="getTrans">
Select Top 20 that meet transaction criteria
</cfquery>
<cfhttp url="https://gateway_url">
method ="post"
Required Params of Transaction
</cfhttp>
<!--- Parse Reply String --->
<cfif response = "1">
<cfquery name="insertPayment">
INSERT Into Payments
All returned payment variables
VALUES (All returned values)
</cfquery
<cfquery name="updateAmountDue">
UPDATE Invoices
SET InvoiceOutAmt = 0
Where Invoice = Invoice
AND Client = Client
</cfquery>
<cfquery name="Transaction">
INSERT INTO Transactions
All returned payment variables
VALUES (All returned values)
</cfquery>
<cfmail
From=""
To=""
Subject="">
Your Payment was Approved
</cfmail>
There's a few more smaller processes that happen between a few of these, as well as quite a few statements but those are the heavy lifting parts.
What would be the best way to handle safeguarding this with 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.
Any help or input would be awesome. Thank you in advance.
