CiviCRM Community Forums (archive)

CiviCRM is getting quite a bit of traction in our organisation. We're a long-established charity that has a flat, nearly bottom-up, organisational structure.

We're active in about 30 countries - recruiting workers and supporters, and/or getting involved with making a difference in people's lives.  Each national base is autonomous, with a small international leadership team elected to keep everyone heading in more or less the same direction.

So we have an incredible variety of scattered databases with incomplete and conflicting information... and for reasons both political and technical we can't consolidate everything into one big server in the sky (never mind that our workers come from countries with different languages).

So what we're aiming to do is consolidate each country's contact data into a national CiviCRM - which leaves room for country-specific tweaks. There are three distinct classes of information:

1. Contact details for our workers. Best known and most reliably updated by the admin staff at the base in their country of origin.
2. Organisational structure and and how individual workers fit into it. Since workers often serve outside their country of origin their field contact information may change without the knowledge of their "sending" base. The international office is best positioned to maintain this information.
3. Contacts with supporters and the people we serve. Known to the national mobilisation teams.

To preserve the sanity of everyone concerned (except possibly the techies), we are thinking of a synchronisation scheme which has an essentially unidirectional data flow.

a. Each base has a "confidential" CiviCRM in which they hold their workers' details. This might also include financial and HR-type info (using the nice "case" functionality that has materialised in 2.0). Only the base admin staff would access this instance.
b. Worker contact details - minus any confidential data - are merged into an international-level CiviCRM in which the organisational structure is represented. This is exposed via LDAP as a "corporate address book" for email clients.
c. The consolidated worker list and organisational structure is exported to many "national intranets" used by mobilisers - taking its place among a much larger number of external contacts that are different in each country.

That looks way too easy, so to add a bit of spice we need to assume that the various CiviCRMs don't necessarily all have the same database schema (and/or custom fields), and we'd like to have a way for corrections to be reported by users of "downstream" CiviCRMs.

So, before embarking on yet another re-invention of the wheel*, has anyone already got a multi-CiviCRM system talking nicely to itself?
And if not, have the CiviCRM team had any thoughts about built-in synchronisation functionality?

In terms of design, it makes sense for the international system to be the integration point - keeping a replica of each national base and intranet CiviCRM database (there are various strategies for keeping them current). The grunt work should be done by stored functionality in the DBMS - not portable, but efficient and transparent to the functional CiviCRM logic.

An alternative implementation route that I haven't fully investigated is to use opensync... it's likely to be more work, but all the heavy lifting of conceptual design should have been done... and by making a plugin for CiviCRM we may gain the ability to sync with other systems that we haven't yet imagined a need for (I'm a sucker for generality!).

So, there are the ideas. Any and all input would be very welcome. I'd prefer that any development work that we do will go right back into making CiviCRM even better.

Mark

* E.W.Dijkstra said it long ago: "in Physics giants stand on each others' shoulders; in Computer Science we just stand on each others' toes"

Yes, it looks like opensync has a way to go until it is stable & also has a stable API.

The other limitation of opensync is that its functionality is driven by a PDA-syncing "worldview" - so the basic types of data are contacts, events (calendar), and to-do items. A bit of jumping through hoops might be needed for additional CiviCRM record types... but it'll be worthwhile having a proven sync engine that handles bidirectional syncing.

So it seems that we will need to do "something else" in the interim. That interim solution could be subjected to two principles:

1) the civicrm side of the integration should be as similar as possible to the work needed to integrate with opensync or similar.
2) the "sync engine" should have minimalistic functionality - e.g. one-way syncing only.

One way of handling the data model would be to create a log table shadowing every data table that we want to keep in sync - populating these with just the data that has been replaced ("old" versions of the changed fields). This should reference the corresponding entry in civicrm_log. These log tables could be maintained by database triggers - therefore no impact on the CiviCRM code.
 
Then, following the opensync design, there needs to be a big hook-up table that links the identities of a record in the various databases (in future to be replaced by opensync's link database). We can also borrow the concepts of differential and full sync from opensync.

Well, the brain is turning into peanut butter stew so I'd better stop for now. When I'm fresher I'll put together some more concrete suggestions... assume its OK to post them here...? or start a topic in the dev section of the forums?