Keeping your DB clean: how about a hook_civicrm_dedupe?

Like many others who have no doubt gone before me, I found myself spending a large chunk of time this week finding and merging duplicate contacts. Civi's deduping UI has gotten a lot better lately, and I love having the ability to go through it more quickly than before. But deduping is only as good as the queries it runs, and of course prevention is better than cleanup.

Here are some observations of how all those dupes in my DB got created:

• People treat nicknames and first names interchangeably -- Bob might call himself Robert on one form and Bob on another, and not even notice he's doing it.
• People change email addresses like they change their shoes!
• Ditto for phone numbers and addresses!
• Phone number is currently just a textfield, so deduping on phone is almost useless (555.5555 doesn't match 555-5555 or 5555555 or 555 5555 and if you include area codes there's even more permutations)
• A slightly mistyped name (it's amazing how many people can't spell their own name!) will result in a dupe that might never be discovered, since even your fuzzy rule won't catch it!
• Setting your strict rule strict enough that you don't get any false positives results in way too many false negatives (creating loads of dupes in the DB)
• Setting your fuzzy rule fuzzy enough to clean up all those dupes means sifting through thousands of "possible dupes" who are unquestionably different people.

In order to solve these and other problems, our dedupe queries need to be able to:

• Use OR logic (match nick_name to first_name and vice-versa)
• Use SOUNDS LIKE or wildcards to catch misspellings
• Intelligently choose fields to match based on what data is available (the current "weight threshold" system is too simplistic)
• Group matching data by category (email, phone, and street address all help to locate a person, but none of those things uniquely identify them -- members of households or businesses frequently share all three)
• Strip irrelevant characters such as phone number punctuation

For example:

Because we have a few custom modules running for our school to register people for programs and such, we save a lot of contacts via API. I've created my own wrapper that handles matching and saving contacts, and before it passes data to the Civi API, it first runs a query to check to see if that contact already exists in the database. The logic for the query goes like this:

IF first_name OR nick_name match (or cross-match)
AND last_name matches
AND email OR street address OR phone number match
Then we have a match!

It's a great query, and almost guarantees no false positives or negatives. You could use it too, or write a better one (since it doesn't tackle the misspelling issue). You or I could also write a great fuzzy query that really nails those dupes without making us comb through thousands of false-positives. But in order for that to happen outside my own little custom API function, we need...

hook_civicrm_dedupe()

Lobo and I were discussing this on IRC the other day, and came up with a couple possibilities. See what you think:

/**
* The "hijack and replace query" method
*/
hook_civicrm_dedupe($contactType, $rule, &$query, $groupID=NULL, $params=NULL) {
  // In this version, the dedupe query has been built and is about to be run
  // $contactType might be Individual, Household, etc.
  // $rule array or object containing the rule being invoked (rule ID, strict or fuzzy, name, label, is_default, etc)
  // $query is the SQL string that we can modify or overwrite
  // $groupID limits the search to a particular group
  // $params will be empty for whole-database deduping, or will contain an array of user-entered data from a webform if we are matching a single contact (i.e. from a contribution page).

  return 'SELECT some other query';

  // CiviCRM will run the new query instead of the one it was about to run.

}

Another possibility would be to for the hook to let you register a new dedupe rule programatically, rather than just hijaking an existing one. (that hook would be called when giving the user-admin dedupe options, so it would show up as a rule that could be made, say, the site's default strict rule.

/**
* The "register query-builder" method
*/
hook_civicrm_dedupe() {
  // existing dedupe rules have numeric id's, so we should probably use non-numeric keys to avoid conflicts

  return array(
    'my_great_rule' => array(
      'level' => 'strict',
      'contact_type' => 'Individual',
      'name' => 'One Rule to Rule them All',
      'callback' => 'my_great_rule_query_builder',
      'file' => 'my_module_dedupe.inc',
    ),
    'my_other_rule' => array(
      //etc...
    ),
  );

  // CiviCRM will display these new rules as options which can be used or made default.
  // When actually running the query from this rule, it will load the specified include file and call the specified function, much in the same way as the first example (but without the $query param), and expect a query string in return.

}

I think this may be be preferable for a number of reasons, namely portability and efficiency. It's more portable because you can define the rule, package it in a module, and it is not at all site-specific. It's also more efficient because we're not asking CiviCRM to build a query only to have it get discarded and overwritten by the hijack-method hook.

In either case, we need to be aware of the security implications of this hook, and avoid making it too easy for newbies to directly concat user-entered data into a query.

     * This hook allows modification of the queries constructed from dupe rules.
     * @param string $obj object of rulegroup class
     * @param string $type type of queries e.g table / threshold
     * @param array  $query set of queries
     *
     * @access public
     */
    static function dupeQuery( $obj, $type, &$query ) {...