Today we spent a fair amount of time with Pete and Chris from the NZ greens. We got a pretty good overview of how the NZ Greens are using the system and some of their pain points. We saw some of the cool integration that Chris has done with the NZ voter database and linking the electorate ID to a CiviCRM contact ID, ability to merge the address from the voter database etc. Its kinda cool how folks can extend and integrate the systems with other db's and we need to make it significantly easier to enable folks to build such system within CiviCRM.

I'll focus on one of their major issues. Restricting who can see what information on whom is a big issue with most political parties. There is one contact database for all their contacts. Some large percentage of the contacts are members. Members can belong to one of nine provinces. Each province is split up into smaller branches. There are approx 70 branches in the NZ green party system. There are users at every level. A branch user can see only branch contacts. A province user can see data of all contacts in that province and the branches that belong to the province. A national user can acess all contact.

On the functional axis there are three different types of users. Folks who can only access contribution data, folks who need access to membership data but not financial information, volunteers who can access a subset of the contact data and finally admins who can access all data.

This gives us a matrix of 80 x 3. Pete demonstrated that the system works for national and the province groups. Extending this to all branches seemed to be the next big hurdle. Creating 240 acl groups, 70 smart groups, 240 ACL roles etc seemed quite daunting. In addition we were not too sure if the queries would scale and / or we would exceed the number of left join tables. We wanted to explore a few other options and test the waters.

Another requirement was to hide a group of special contacts (vips) from all users except those with a specific permission. Our ACL mechanism does not support the NOT condition, so we decided to see how we can extend the code to do the needful. This turned out to be surprisingly easy and we hacked CRM/ACL/BAO/ACL.php, function whereClause to do the needful (we had to ensure we did not do an early return from the function of no acl's were present etc). We used a drupal permission to deny access to all folks who did not have that permission. The query would now look like:

SELECT * 
FROM   civicrm_contact, civicrm_....
WHERE  search_clauses
  AND  (acl_clause_1 OR acl_clause_2 OR ... )
  AND  ( contact is not a vip ) 

VIP information was stored in a custom table so we added the needed LEFT JOIN information. Having done this succssfully, we figured we should try to tackle the more complex problem as a custom ACL extension. Here's our current design and thinking:

There are two new custom groups:

Region Group composed of select fields for Province and Branch
Permission Group composed of checkbox fields for Province and Branch

All users who have rights to a subset of contacts will have an entry in the Permission Group table listing what provinces and branch contacts they can view or edit. All contacts will have Region information filled in stating what province and branch they belong to

The custom ACL code will generate a query to ensure that the above criteria is satisfied, thus the new query will look like

SELECT * 
FROM   civicrm_contact, civicrm_....
WHERE  search_clauses
  AND  (acl_clause_1 OR acl_clause_2 OR ... )
  AND  ( contact is not a vip ) 
  AND  ( contact.province IN user.province_list 
   OR    contact.branch   IN user.branch_list )

I suspect this approach is much more scalable and more efficient in this particular scenario. We'll experiment tomorrow and see what other hurdles we need to cross.