Basic Fulcrum webhook script for updating a CartoDB table via data shares.

fulcrum-data-share-cartodb-webhook.php

<?php
# CartoDB Info
$cartodb_username = 'my-cdb-username';
$cartodb_api_key = 'my-cdb-api-key';
$table = 'my-cdb-table-name';

# Fulcrum Info
$form_id = 'my-fulcrum-form-id';
$share_token = 'my-fulcrum-share-token';

# Webhook JSON payload
//$input = file_get_contents('fulcrum-payload.json'); # local file for testing
$input = file_get_contents('php://input'); # POST data from webhook
$payload = json_decode($input, true);

# Make sure it's the form we want
if ($payload['data']['form_id'] == $form_id) {

  # Fetch record info from data share
  $geojson = file_get_contents('https://web.fulcrumapp.com/shares/' . $share_token . '.geojson?fulcrum_id=' . $payload['data']['id']);
  $features = json_decode($geojson)->features;
  $properties = $features[0]->properties;

  # Push feature properties (except marker-color) to fields and values arrays
  $fields = [];
  $values = [];
  foreach($properties as $property => $value) {
    if ($property != 'marker-color') {
      array_push($fields, $property);
      array_push($values, $value);
    }
  }

  # Build SQL statement based on record event type
  if ($payload['type'] === 'record.create') {
    $sql = 'INSERT INTO ' . $table . ' (the_geom, ' . implode(', ', $fields) . ') VALUES (ST_SetSRID(ST_MakePoint(' . $payload['data']['longitude'] . ',' . $payload['data']['latitude'] . '),4326), $$' . implode('$$, $$', $values) . '$$)';
  } elseif ($payload['type'] === 'record.update') {
    $updates = [];
    foreach($properties as $property => $value) {
      if ($property != 'fulcrum_id' && $property != 'marker-color') {
        array_push($updates, $property . "=$$" . $value . '$$');
      }
    }
    $sql = 'UPDATE ' . $table . ' SET the_geom=ST_SetSRID(ST_MakePoint(' . $payload['data']['longitude'] . ',' . $payload['data']['latitude'] . '),4326), ' . join(', ', $updates) . ' WHERE fulcrum_id = $$' . $payload['data']['id'] . '$$';
  } elseif ($payload['type'] === 'record.delete') {
    $sql = 'DELETE FROM ' . $table . ' WHERE fulcrum_id=$$' . $payload['data']['id'] . '$$';
    $properties = ['fulcrum_id'=>$payload['data']['id']];
  }

  # Wire up cURL to execute SQL on CartoDB table
  $ch = curl_init('https://'.$cartodb_username.'.cartodb.com/api/v2/sql');
  $query = http_build_query(array('q'=>$sql,'api_key'=>$cartodb_api_key));
  curl_setopt($ch, CURLOPT_RETURNTRANSFER, TRUE);
  curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, FALSE);
  curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, FALSE);
  curl_setopt($ch, CURLOPT_CUSTOMREQUEST, 'POST');
  curl_setopt($ch, CURLOPT_POSTFIELDS, $query);
  $result = curl_exec($ch);
  curl_close($ch);

  # Write SQL out to file for inspection
  $text = fopen('fulcrum-cartodb.sql', 'w+');
  fwrite($text, $sql);
  fclose($text);

  # Write payload out to file for inspection
  $json = json_encode($input);
  $data = json_decode($json, true);
  $payload = fopen('fulcrum-payload.json', 'w+');
  fwrite($payload, $data);
  fclose($payload);
}
?>