Payments - Where do we store IDs from payment processor?

On contributions there are two fields that can be used to store unique values - trxn_id and invoice_id. trxn_id is the only one that we can actually because the workflows ignore invoice_id in various places. For recurring contributions we have trxn_id and processor_id and here we have to set both to the same value because they are used inconsistently in the code. Then there are the individual payments which can store their own trxn_id.

Looking at Stripe we have:

• charge_id - changes each time a payment is attempted - matches best to civicrm_financial_trxn.trxn_id (but currently we save on civicrm_contribution.trxn_id).
• invoice_id - may have multiple charge_id's for example if a payment attempt fails and is retried. So this should really be the trxn_id on the contribution (we don't currently store it).
• subscription_id - for a recurring contribution this represents the plan so maps directly to civicrm_contribution_recur and could be saved in either civicrm_contribution_recur.trxn_id or civicrm_contribution_recur.processor_id.

The problem is that the way CiviCRM works internally (and API functions such as Contribution.completetransaction don't really allow us to work in the way we'd like).

UPDATE - the resolution here has been to

1. Add a field order_reference to the civicrm_financial_trxn table. The reason for adding is to this table even though it 'represents' the contribution / order is that it's possible not all payments on one contribution are by the same processor - hence it's up to the processor to manage this field
2. Matt felt that civicrm_financial_trxn.result_code met his needs for a description