Syncing Fulcrum with Your Own Database

10 September 2015 by Bryan McBride

Having your up-to-date Fulcrum data available in your own database enables countless opportunities for custom integrations and seamless workflows. Databases power most CRM, GIS, and Asset Management systems, so configuring Fulcrum to write directly to these database tables allows you to leverage all the benefits of Fulcrum for field data collection without having to modify your existing systems or workflows.

To help with this, we’ve put together a new webhook guide to help walk you through setting up a simple PHP script, which will keep your MySQL database in sync with Fulcrum. While PHP and MySQL are nearly ubiquitous on most shared web hosts, you should be able to use this guide as an outline for syncing with different databases or web services.

Syncing Fulcrum with your own database

Hooking Into the Fulcrum Platform

As Fulcrum has progressed into a fully extensible data collection platform, we’ve made it a priority to provide hooks for getting data both into and back out of the cloud. In addition to our Import and Export wizards, we provide the following tools for interacting with your data:

Fulcrum API

Our REST API provides a records endpoint for programmatically creating, updating, and deleting records.

Data Shares

Enabling Data Shares generates read-only URL’s for accessing data in your Fulcrum account without requiring authentication. Data shares provide a real-time data feed in formats such as CSV, GeoJSON, and KML, which are useful for creating embeddable maps, shareable spreadsheets, or publishing data to 3rd party services such as ArcGIS Online.

Webhooks

For complete control over your data workflow, we’ve provided webhook notifications. Webhook scripts are invoked any time an event happens in your Fulcrum organization. The events are pushed to the webhook’s URL in an HTTP POST request for your script to process. This means that any time a record is created, updated, or deleted, your server can be notified to fire off any custom workflow you’ve built (such as updating your database).

Wiring Everything Up

With these tools at your disposal, syncing your Fulcrum app with your own database becomes fairly straightforward, with a little bit of scripting. While the webhook event contains the actual record payload, it is in our internal JSON format, which stores custom form values as key/value pairs. In order to greatly simplify things, we will fetch the processed record info from a data share, using the fulcrum_id parameter.

So, Fulcrum fires a notification to our webhook script that includes the event type (record.create, record.update, record.delete) as well as a reference to the record’s fulcrum_id. Our script then fetches the GeoJSON representation of that record from our data share link, parses the feature properties, and builds a corresponding SQL prepared statement. Finally, the script will execute the prepared statement, either creating a new record in our table, updating or deleting an existing record.

Summary

We’ve had several requests from users inquiring how to sync Fulcrum with their own database and hopefully this guide will help illustrate one potential workflow. Working with the API can seem daunting, especially for beginning developers, but this latest guide walks you through the steps required to configure a fairly generic webhook script, which takes advantage of data shares to build SQL statements that will update your database in real-time. The PHP PDO extension supports many databases in addition to MySQL, so this script could very easily be used to sync to SQLite, PostgreSQL, Oracle, or MSSQL as well.

When you are ready to start exploiting the real power of Fulcrum as a developer platform, visit our developer docs and in-depth guides. If you have any questions or comments along the way, head over to our public discussion forum and drop us a line.

About the author

Bryan is a software engineer with Fulcrum, and works with our web and backend platform.

comments powered by Disqus

Sign Up Today & Start Collecting Data In Just Minutes!

Start Your FREE Trial