Confusion between Contribution ID vs Invoice_ID in the database

Question

CiviCRM 4.6 - Moneris payment processor -

CiviCRM's invoice uses the ID column to generate the Invoice number (INV_XXXXX) but creates its own hash and adds it to the invoice_id column - is there a way to reconcile the two? It seems CiviCRM uses the ID column to reference a contribution everywhere except in the invoice_id column after a successful transaction as been made.

We notice that when "Send Invoice Email" is used in the Contribution view, it changes the invoice_id column to the INV_xxxxx naming which has given our client major reconciliation headaches. Based on the database logging, the "Send Invoice Email" function sends an Insert request and updates the invoice_id field.

I tried searching the issues tracker, but couldn't find anything.

Before and after images included -

Answer 1

It looks like in CiviCRM 4.6, the invoice_id hash gets replaced by the INV_xxxxx number the moment there's an "action" taken - the invoice is downloaded or edited, for example.

Not sure why.

Answer 2

This answer is significantly revised based on feedback from colleagues here and in Mattermost, and a bit of subsequent research.

The contribution ID column is a good unique reference field.

I had forgotten about the hash value being placed in the invoice_id field. It seems that very long ago Lobo (2007) started to put a unique hash value in this field to facilitate reconciliation with online payment processors, which later migrated into the code for various payment processor integrations. I don't think this is a good name for a field storing data for that purpose, nor is a hash a good invoice number. I suppose there is a field called invoice id on some payment processors that can accept an ID from CiviCRM which is ideally unique. The problem is that a hash value is not a human friendly value for an invoice, and best practices for auditing would want to see that no invoices have been issued that are not still in the system, which is best facilitated and made auditable by a consecutive numbering scheme or similar. I do think that the way the field was used for manual entry of invoice numbers makes sense.

There was a different field, trxn_id, also added in v. 1.3, that is now in the civicrm_financial_trxn table, that was intended to be used to store unique numbers received from payment processors. Perhaps it would have been good, or still could be good, to use this field instead for a unique value intended to match civi to payments in these external systems.

A further and more important problem with the way this invoice_id is stored and used currently by several payment processors is that CiviCRM has supported partial payments on events for a few years. The schema was changed to support this i 4.3 or 4.4 by me and dgg. I think it was spec'd and funded for 4.5 by CompuCorp and implemented by WebAccess and overseen by dgg (I wasn't involved).

Partial payments has since been extended to regular contributions and memberships. During this partial payments work, the payment specific information has been stored in the civicrm_financial_trxn table since that allows one record per payment. There is no way to track multiple payments on a contribution using a field on the contribution record like invoice_id.

The INV_12228 invoice id was added in 4.6 to provide a valid invoice id that complies with UK law for invoice numbers. The bad part was that when it was implemented it would have been nice to notify the maintainers or fix the code of the relevant payment processor plugins that were (still) using this field to correlate payments with the external systems.

At this point my suggestion is that interested parties should scope work involved in fixing this. One option would be to migrate data from civicrm_contribution.invoice_id to civicrm_financial_trxn.trxn_id and update code to store this way. If some processors use both trxn_id and invoice_id then we should create an invoice_id on civicrm_financial_trxn table, migrate data as appropriate, and also update the code to store info in this new location.