Another update on invoicing problems

| | Comments (0)
Summary: We believe we've identified all the problems and we're working on how to best fix them. They are one-time bugs ultimately related to the import and not something that would have been an ongoing problem. They are also our fault and not the billing system.

I'm sorry this is taking so long.

More details: We attempted to reproduce the issue where the last invoiced date was not updated and were able to cause different problems, but were not able to exactly reproduce the one we saw.

I and one of the blesta developers concluded that there is a potential problem where if an invoice creation job is killed in the middle, there could be one duplicate invoice, but there was more than one duplicate invoice so it could not have been the problem we were seeing. It also would have raised an error in the blesta UI because the invoice creation job would have not been marked as completed.

If the invoice creation dates had not been updated at all this would have lead to an infinite number of duplicate invoices (at least until someone noticed or we ran out of disk space) so this is also not what happened.

Nobody explicitly remembered doing anything to the DB but it seemed like there was no other possibility.

I realized that there was an intermediate database backup between the dates we were looking at. I confirmed that the renewal dates were correct in there. So we must have overwritten the updated service renewal dates with the old service renewal dates.

We believe what happened is that changes were made directly to a stale backup of the service table while someone was working on fixing a different problem, and the stale backup was restored. I also looked through my chat logs and confirmed that someone said they were working on a problem and made changes to the database on that day. So this is a problem with our own procedure and not with blesta.

Preventive action going forwards: don't touch the DB directly now that the import is complete, which means going through either the UI or the API.

Corrective action: find and remove the list of duplicate invoice lines by either 1. make a list of
the people who had their service dates changed by comparing the two database backups or 2. find the service ids that show up twice since March 22nd given we don't have any services on a renewal basis shorter than 1 month. In order to remove the duplicate invoice lines we will probably have to first unapply all the payments from that invoice and then reapply the payments, unless blesta is smart enough to figure it out on it's own. We will be testing this on a backup of the database before applying it to production.

Another problem we've found is that there were a number of invoice lines (41) and transactions that have an incorrect association with the invoice table. So how did this happen?

Blesta does not equate the invoice id with the invoice number - these are allowed to be two
different values in the case of there being a 'draft' invoice.

A number of dummy invoices were created to absorb payments that weren't originally applied to an invoice. During the creation of these invoices, there was an assumption that the invoice id would equal the invoice number, but there was a bug in our migration process where this was no longer true. The invoice lines and applied transactions were associated with the invoice number, when the actual relationship is with an invoice id. This also left the 41 original invoices as having no associated invoice lines, which is in error.

if there had been a foreign key constraint on the invoice_id columns then this bug would have been prevented.

So then, how to fix this? Probably the process will be to unapply the payment, remove the bogus invoice line from its current invoice, add it to the correct invoice, and then apply the payment to the correct invoice. Again, we will do this in a test environment before applying it to production and may need to unapply / reapply all payments before being able to edit the given invoice.

Leave a comment

About this Entry

This page contains a single entry by srn published on April 3, 2015 2:14 PM.

Past due notices was the previous entry in this blog.

Invoicing problems resolved is the next entry in this blog.

Find recent content on the main index or look in the archives to find all content.