Wednesday, April 26, 2017

CiviCRM's invoice_id field and why you should love the hash

I've been banging my head against a distracted cabal of developers who seem to think that a particular CiviCRM core design, which I'm invested in via my contributed code, is bad, and that it's okay to break it.

This post is my attempt to explain why it was a good idea in the first place.

The design in question is the use of a hash function to populate a field called 'invoice_id' in CiviCRM's contribution table. The complaint was that this string is illegible to humans, and not necessary. So a few years ago some code was added to core, that ignores the current value of invoice_id and will overwrite it, when a human-readable invoice is generated.

The complaint about human-readability of course is valid, and the label on the field is misleading, but the solution is terrible for several reasons I've already written about.

In this post, I'd like to explain why the use of the hash value in the invoice_id field is actually a brilliant idea and should be embraced. And sure, let's give it a different label.

The key issue is reconciliation with payment processors. Paypal was the first payment processor to be implemented, and still relies on that invoice_id as far as I know.

Reconciliation between CiviCRM and any payment processors can (and usually should) be done in two ways - you'll want to ensure that payments in CiviCRM have matching ones in the payment processor's records, and vice-versa. There are also cases where you really need reconciliation of some kind - e.g. when visitor has paid by Paypal comes back to CiviCRM, it needs to confirm the contribution, and also after an ACH/EFT payment request, there needs to be a confirmation in a few days when the payment has actually been attempted. In fact: instant, done and forever payments are really just a dangerous illusion from the use of credit cards. If your mental model of payment processing is exclusively credit card based, you're going to mess up eventually.

Now, most payments match up just fine, and for an on-site processor doing credit cards, reconciliation is often ignored, but there are a number of ways/times when it is important (assuming the data in your CiviCRM is important).

Case 1. A payment processes fine, but is later reversed (manually, or by the donor, for example) in the payment processor interface.

Case 2. A payment completes in the payment processor, but that information doesn't get communicated back to CiviCRM. This could happen for both externally hosted payment pages like PayPal, but equally an on-site payment processor that makes a request for a payment that goes through, but fails to capture the result (e.g. due networking or server issues).

Case 3. A payment is made manually through the payment processor.

CiviCRM has two fields to help us do reconciliation:

a. the invoice string that it sends to the payment processor (or at least, it does by default, any individual processor plugin may choose not to) and saves into the invoice_id field.
b. the transaction string that it gets back. That gets saved in both the contribution table and the transaction table (because now you can have more than one payment per contribution).

The two contribution table fields in CiviCRM have a hard-coded requirement to be unique (or empty), which means that when used properly, and with enough tools provided by the payment processor, we can do reconciliation, both manual and automated.

The important thing is that we need BOTH these fields if we are to cover all three of those cases - we need to identify matching entries, as well as unique unmatched entries in CiviCRM and unique unmatched entries in the processor.

And yes, if there is more than one payment against a contribution, we don't have uniqueness of the invoice number at the payment processor end of things for each payment, but that actually doesn't break anything.

Okay, having established that we need both fields, now the question is just - why do we need such an ugly string to send to the payment processor as an invoice id?

For that, there are actually two good answers:

1. When a payment via a payment processor is attempted in CiviCRM, typically no contribution record has been created. So there is no nice integer id that we can use to generate a human-friendly invoice id. We could do some gymnastics and add some extra code so that we were generating incremental ids each time, but that's not an easy problem to solve, and the numbers we generated would not be matched up with the contribution id numbers.

2. Global uniqueness is a good thing. The hash method for generating unique strings is also used for example in git. If we were to use some kind of incremental id and a different system (e.g. Drupal commerce?) was connecting to the same payment processor, we could have overlaps of invoice numbers, making reliable reconciliation impossible.

Okay, so enough already about the invoice_id field?

Friday, January 20, 2017

What to do in the age of Trump?

Well, that's the question of the day. If you're part of an organization that does advocacy work, rather than waiting to see what happens first, might as well get yourself ready, even if the details are sketchy still. Here's one opportunity that's ready for you now, message courtesy of Steve Anderson of OpenMedia.

OpenMedia, David Suzuki Foundation, SumOfUs and a range of other organizations are supporting a new shared set of civic engagement tools.

Vancity Community Foundation is providing some support to subsidize some of the cost of the tools to select values-aligned organizations that sign up before February 28th.

Interested? You can learn more or book a demo from here: http://tools.newmode.net/

Here's some live examples of the tools you can take a look at:

1. Click to Call: http://www.davidsuzuki.org/blogs/healthy-oceans-blog/2016/11/to-help-protect-canadas-oceans-weve-made-it-easy-to-call-your-mp/#newmode-embed-4-266

Check out this video of David Suzuki's daughter using the tool: https://www.facebook.com/DavidSuzuki/videos/10154754874493874/


2. Letter to the Editor: http://www.deliveringcommunitypower.ca/letter

You can see how powerful this tool can be via this blog.

3. Click to Target (works for all levels of government): http://www.deliveringcommunitypower.ca/banking

Here's another example targeting provincial politicians: http://www.b4stage4.ca/#newmode-embed-209-217


Tuesday, January 10, 2017

Using varnish in front of a webhosting canada or other shared hosting site? Pay attention to your probe!

I've had a varnish installation protecting http://www.fairvote.ca -- a wordpress site hosted at webhosting canada (whc.ca) -- for about a year. Last Friday in the evening, it started spitting out 503 errors, for no obvious reason. I spent a few hours yesterday in vain trying to get the hosts to tell me what, if anything might have changed at their end.

What I eventually figured out is that they changed how their servers respond to http requests without a domain - previously it was a 200, and it became something else (a redirect, I believe). That's not unreasonable (though relatively pointless to change at this stage, I would imagine), except that, by default, varnish probes its backends with requests that don't have a domain. What that meant is that varnish started thinking that the backend was broken, so stopped talking to it (even though it was perfectly capable of doing so).

The fix was just to add a customized probe that included a valid domain to the request.

Monday, November 14, 2016

Varnish saves the day, in unexpectedly awesome ways.

Four and half years ago, I wrote a blog post about Varnish, a 'front-end proxy' for webservers. My best description of it then was as a protective bubble, analogous to how it's namesake is used to protect furniture. I've been using it happily ever since.

How Americans Voted poster
But last week, I got to really put Varnish through a test when the picture here, posted by Fair Vote Canada (one of my clients), went viral on Facebook. And Varnish saved the server and the client in ways I didn't even expect.

1. Throughput

Varnish prides itself on efficiently delivering http requests. As the picture went viral, the number of requests was up to about 1000 per minute, which Varnish had no trouble delivering - the load was still below 1, and I saw only a small increase in memory and disk usage. Of course, delivering a single file is exactly what Varnish does best.

2. Emergency!

Unfortunately, Varnish was not able to solve a more fundamental limitation, which was the 100Mb/s network connection. Because the poster was big (760Kb), the network usage, which is usually somewhere in the 2-5 Mb/s range, went up to 100Mb/s, and even a bit beyond. That meant the site (and others sharing that network connection) started suffering slow connections, and I got a few inquiries about whether the server had 'crashed'.

At that stage, I had no idea what was actually going on, just that requests for this one file was about to cause the site as a whole to stop responding. I could see that the referrer was almost exclusively facebook, I also noticed that the poster on it's own wasn't really helping their cause, and the client also had no idea that it was happening - they had uploaded the poster to facebook, so it shouldn't be requesting it from their site.

Fortunately, because the limitation was in the outgoing network, there was a simple solution - stop sending the poster out. With a few lines in my varnish VCL, the server was now responding with a simple 'permission denied', and within a few seconds, everything settled down.

In fact, the requests kept coming in, at ever higher numbers, for the rest of the day, but Varnish was able to deflect them without any serious blip in the performance of the server.

3. And Better

The next day, after some more diagnostics, we discovered that the viral effect had actually come from someone else's facebook post who shared the poster as it had gone out in an email. Although the poster on it's own wasn't going to help the cause of PR directly, we didn't really want to stem whatever people were getting out of it, so I uploaded the poster to an Amazon S3 bucket, (an industrial file service) and modified my varnish vcl to now give a redirect to the amazon copy instead of a permission denied.

Now the poster could go safely viral.

4. And Best

After some more discussion, Fair Vote noted it would be better if people ended up on the facebook campaign url here  rather than just the poster. So I updated the varnish vcl so that if the poster request comes from a facebook referrer, then it redirects them instead to the above url.

Four days later now, it seems like it's worked - the poster is still pretty viral, and even the requests for the original url is still going strong (3.4 million requests in the 48 hours ending at 3 am this morning).

Without Varnish, my server would have crashed and been unable to get back up, even now. Instead, the poster is still being shared, the rest of the site is still working, and the facebook share is even more effective than it would have been.

Tuesday, July 05, 2016

Distracted parenting

Do one thing each day that scares you? How about, watch your toddler ...

Tuesday, December 09, 2014

Lily's New Hat

My cousin Vikki has been helping us for the past month and is leaving to head back home in a couple of days. We had her over for dinner last night and she brought Lily a new hat!


Thursday, November 06, 2014

Varnish, Drual and ical (.ics): expiry issue fixed

My normal configuration of a public site on my servers involves using varnish for the page cache and setting expire page to 1 day. This mostly works quite well (the varnish module in Drupal takes care of clearing the varnish cache when you're creating/editing content).

We recently launched a new Drupal version of the Calgary French & International School (okay, I was just along for the tail end to help with the launch, Karin and Rob get the credit for all the work), which includes an ical feed for parents (generated from views of course).

That's an excellent thing - parents can subscribe to the feed and have all the upcoming events on their mobile device (or google calendar, or both). But we discovered that although it works great on the Mac desktop, it wasn't working well for iOS (i.e. the iPhone). It would poll frequently enough, but only actually update once a day.

It turned out that these two devices are interpreting the http header 'cache-control' differently - on the iphone, it appeared to interpret it to say don't both looking for fresh data more than once a day. The header is not very well defined unfortunately, but it is used by Drupal/Varnish to control the maximum expiry date, so we didn't want to crank it too low (or risk a badly performing site, since most access is anonymous).

The solution was actually simple: a little help in the varnish vcl file, in my vcl_deliver function, below. The piece I added was the second if, and it's just modifying the cache-control header on output if it's delivering a file with extension 'ics'.

sub vcl_deliver {
  if (resp.http.magicmarker) {
    unset resp.http.magicmarker;
    set resp.http.age = "0";
  }
  if (req.url ~ "\.ics$") {
    set resp.http.cache-control = "public, max-age=60";
  }
}



Monday, September 08, 2014

Lily, is it time to make some garlic bread?

Lily is my daughter, born a couple of days after my previous post, now almost 5 months old.

Tuesday, April 15, 2014

CiviCRM and Accounting in 4.3

The 4.3 version of CiviCRM that first came out in April 2013 addresses a key problem with CiviCRM for large organizations: namely, accounting integration.

So what exactly does that mean, and how does it work? Since I'm working on a big migration to CiviCRM, and the client has "accounting integration" needs, I've been diving in and trying to understand the nitty gritty. Since I started, 4.4 is now out, and 4.5 is almost out, and I understand they've made some improvements, so this is now a bit dated, but still might be helpful.

First off, "accounting integration" doesn't mean that CiviCRM will now replace your accounting package, but the goal is to make it play nicer. The key issue for my client is that reports coming out of their current system are used as input for their accounting system, so it needs to speak the same language - i.e. things like GL account codes, and double entry accounting for things like contributions where the donor is promising to send a cheque. I like to describe it as: making CiviCRM a trusted entity within the accounting system, instead of it's current status where reports generally involve some wishful thinking, caveats, and a checklist of adjustments.

Initially I've just been going along assuming everything will take care of itself under the hood as I use Eileen's brilliant civimigrate module in combination with the Drupal migration module. But after a few failed attempts at getting what I want, I've been forced to try and understand what's really happening, and so that's what I'm sharing here.

The most useful documentation I've found so far is here:

http://wiki.civicrm.org/confluence/display/CRM/CiviAccounts+Specifications+-+Financial+Items

The aha moment came after looking at the new civicrm_financial_* tables and reading that page.

My key insight was:

In the past, a row in the contribution table was playing too many roles: it was the transaction record as well as the accounting record. In this new, more sophisticated world of accounting, you still get one row in the contribution table per "contribution", but that really serves as a simplification of what are two collections of things: the collection of financial transactions that go into paying for it, and the collection of items that describe how that contribution is accounted for - where it went. In the simplest case, you get one of each (e.g. a check for a simple donation). But at it's most complicated, you might have a series of partial payments for an event ticket, some of which is receiptable as a tax donation and some of which goes towards the cost of the event. Yes, that's why they call it 'double entry'.

Implementing a system like this over top of an existing system is a bit tricky, and the developers seem to have been a little bit worried about the complexity implication for users that didn't want to know about this. So you have to dig a bit to see what's going on, and I'm probably missing some details. Patches welcome ...

One way of describing what we're doing is 'splitting' the contribution. The contribution becomes an container for a collection of inputs (financial transactions) and collection of outputs (attribution of the contribution to accounting codes). The original contribution table still contains a single entry per contribution, but the possibly multiple transactions that pay for it, and the possibly multiple attributions of that income, need to live in related tables.

One trick the developers used was to create something they call a 'financial type'. The point of this entity is to allow administrators to delegate the accounting to a set of rules for each 'financial type' - meaning, the way a contribution gets allocated to the account codes is determined by the nature of the transaction (i.e. income, expense, cost of sales, etc.), which is then looked up for the 'financial type', and that determines the accounting code. Fortunately, this is just a mechanism for calculating the actual accounting - the data gets stored fairly directly.

Now let's check out the new civicrm_financial_* tables.

civicrm_financial_item - this looks like the accounting for each entry in the contributions table. It includes entity_table and entity_id fields that link it to more information, e.g. an entry in civicrm_line_item. It doesn't provide you the accounting directly, but it gives you the financial_account_id, and you can look up the accounting code directly from the civicrm_financial_account table.

civicrm_financial_trxn - these are the financial transactions that make up the contributions. You'll see it has things like the from and to account fields (to allow for both external and internal transactions, like when a check is received and the transaction is transferred from accounts payable to your bank account), as well as transaction id's, currency, amounts, dates, payment instruments, etc., i.e. everything you need to follow the money.

civicrm_entity_financial_trxn - this is the table that joins the above two tables to the contributions table. A simple typical contribution will have two entries, one pointing to the financial item, and the other to the financial transaction.

Okay, now let's dig a little deeper:

In the financial_item table, which holds the accounting, it also has a reference to a 'more information' record, with entity_table and entity_id fields. In my install, it's pointing at the civicrm_line_item table most of the time, except for old imported from 4.2 entries that point at the civicrm_financial_trxn table.

civicrm_line_item - I'm not sure why you'd need a reference to this, but I guess it does help track back to how the financial_item got created. Specifically, it has a 'financial type id' field, which in combination with the transaction, could be used to calculate the financial account id that ends up in the financial item.

civicrm_financial_trnx - I'm guessing that the only time a financial_item references this table is when there's a direct correspondence between the transaction and the accounting. For an install that was migrated from 4.2, for example, that's the case of all the old transactions that assumed this and for which there is no intervening line item to split it up. Maybe "backend" administrative entries and adjustments end up here as well?

And now back to the other financial tables:

civicrm_financial_type - a list of these 'financial type' abstractions. There's no accounting codes in here, you have to find the connection to the  account id using something like:

select * from civicrm_entity_financial_account where entity_table = 'civicrm_financial_type';

civicrm_financial_account - the list of the accounting codes for each 'account', i.e. what you want to get from your bookkeeper when you set things up.

Conclusion: it's pretty complicated, and obviously you don't want to be manually mucking with these tables. In theory, the structure allows for some nice splitting up of income into different accounting categories, but at this stage, the interface is trying to hide most of the details to keep things simple for users.

Thursday, March 13, 2014

Transport layer security on the Internet

Yesterday I posted this:

https://www.newschallenge.org/challenge/2014/submissions/a-new-open-transport-layer-security

and sent the link off to some friends and family. They had some good things to say, and some of that helped me clean it up a bit. But the feedback and discussions I had also helped me to step back a bit from the specifics of that proposal and think more generally about the problem.

The problem I'm talking about is a mash-up of technical detail, privacy concerns, security concerns and good old fashioned apocalypse with a dash of conspiracy anti-government kind of stuff. So there's definitely more than one way to look at it. I like to think of it as "collapse of trust on the Internet as we know it".

Here's the scenario: at some point in the next 5 years, a method is discovered that allows people with enough computer power to decrypt 'secure' https connections. Once this is generally known to the public (e.g. via a leak like that of Mr. Snowden), no one will 'trust' that any communications on the Internet is safe. Banks and credit cards companies will stop accepting any transactions from the Internet, and e-commerce will collapse. How that will impact the world, I'll leave to your imagination, but I don't think it will be pretty.

The anti-establishment rogue in me gets some satisfaction from that scenario, but I also know that in a crisis, it's the people at the bottom of the ladder that get crushed, and mass human suffering isn't something I'd like to encourage.

So here are some follow-up notes to my post:

What problem are we trying to solve?

Avoiding a disaster is a nice big picture goal, but not one that lends itself to a specific solution. One way of framing the problem is narrowly, which is what I suggested in my post - i.e. focus on the mathematics behind the encryption problem.

On the other hand, perhaps that's not the right problem to solve? It's not a new problem, and it's been around for about 20 years and there hasn't been a whole lot of progress or change.

The mathematical piece of the problem as it is currently framed is about how to provide a "Public Key Infrastructure" (PKI) using mathematics. A PKI is a way of solving the more abstract problem of 'how do you establish trust between two parties on the Internet', where the only communication between them is this stream of bytes that appear to be coming from a source that is reliably identifiable only as number? What if that doesn't have a reliable solution?

The short version of what suddenly got quite complicated is this: this part of internet security was designed for e-commerce, in a bit of a hurry, back in the early days of the Internet when machines were less powerful and e-commerce was a dream. Then the dream actually came true (after the Internet bubble and collapse) but those emperor's clothes are pretty skimpy.

So "who do you trust and why" is the bigger, more abstract problem, and treads on some scary ground. Is there a different solvable technical problem somewhere in here, bigger than the mathematical problem of a PKI but smaller than the completely abstract one?

What problems are already solved?

My smarter older brother pointed me to these:

a. http://en.wikipedia.org/wiki/Advanced_Encryption_Standard

A smaller more tractable problem is 'symmetric encryption' (which isn't a mathematical solution to a PKI on it's own), and this solution has been adopted as a new standard. In other words, if you have a prior relationship with someone and way of sharing secrets outside of the Internet, then a secure private channel is not all that difficult.

b. http://en.wikipedia.org/wiki/Quantum_key_distribution

This appears to be a solution to negotiating a shared random secret key, which solves part of the PKI problem (it helps provide a secure channel with your correspondent, it doesn't help prove who they are).

c. Human nature

Yeah, just kidding. Just to be clear though - none of this solves the general problems of fraud and how humans have built a glorious, terrible thing built on machines and social interaction, and how fragile it is. Perhaps that part of the problem (who do you trust) is not going to have a technical solution.