Infusionsoft Local Sync

Synchronizes an Infusionsoft app with a local PostgreSQL server.

As this is an early commit, this document is a TODO, not a proper readme.

Purpose

• Use this if you want to have some way to access your Infusionsoft database from your own server-- to run queries impossible with Infusionsoft's standard API.
• Can be used as a backup, for redundant storage or as a fallback in case your Infusionsoft app becomes temporarily inaccessible. (NO WARRANTY IMPLIED)
• As a tool to assist migrating your CRM data.

The intention is to make it easy and quick to find records using a CLI query for a quick one-off, or a proper SQL/Knex interface for more advanced stuff. (we use knex.js, did I mention that?)

Getting started

Installing

Whether standalone or as a dependency, you'll need a PostgreSQL instance to store your data. Sorry, we don't support other databases-- PostgreSQL has bindings for most languages and reliable binary distributions for every major operating system.

As a standalone app

Run npm install -g infusionsoft-local-sync. If your NPM binary directory is on your PATH, the ils command should become available system wide.

Todo: Implement an ils configure command to configure the system using OAuth, like google does.

Todo: Should webhooks become available in standalone mode? Probably not.

As a dependency

Run npm install infusionsoft-local-sync from your project directory.

You can then require('infusionsoft-local-sync') to get the API library and configure it as (will be in the future) documented below.

Configuration

in progress The tentative layout for the config object is like this:

{
  "api" :
  {
    "app"         : null, //your infusionsoft app name
    "key"         : null, //your infusionsoft app private key.  They say they're phasing out this method of access, but api coverage is too thin right now to really dispense with it.
    "retry_count" : 5 //number of times to retry.
  },
  "web" :
  {
    "client_id"     : null,//Your client ID for oauth, gotten from developers.infusionsoft.com.  Might not be possible to set this up on standalone/cli version
    "client_secret" : null, //same as above
    "passport"      : false, //whether we will set up a passport object.  Forced to false for standalone/cli version.
    "local_url"     : null //The URL to reach our API endpoint, forced to null for standalone/cli
  },
  "sync" :
  {
    "db_client"    : {}, //Same as config object passed to the database client library.
    "local_db"     : "infusionsoft", //The name of the local database to use.
    "local_schema" : "infusionsoft", //The schema to store Infusionsoft mirrored tables under.  If left blank, it will create a schema the same as your app name.
    "use_webhooks" : false // Whether sync uses webhooks to receive notifications about updates.
  }
}

Calling require('infusionsoft-local-sync') returns a constructor that can receive an object with the above format. In standalone mode, an infusionsoft-local-sync.json file is created in your operating system's equivalent of /etc or with a leading dot in your home directory i.e. ~/.infusionsoft-local-sync.json.

The idea is to instantiate ILS as a system level service. Most services are accessed over SSH. While we'll try to be flexible and open to different methods of operation, the basic assumption is that you want to set this up on a Linux server that you primarily access via SSH. The commands and API will be built to make that as easy and smooth as possible. (Todo: consider the feasibility of a GUI client, maybe served over a local web interface for windows?)

Environment variables

As we use nconf, you can configure ILS using environment variables. To set a value inside of a structured sub-key, use colons. Standard nconf procedure.

I.e. to set the api.app config variable, you can do:

export api:app=<ur appname>

Or on Windows:

set api:app=<ur appname>

Usage

CLI

Installs a global CLI command ils. For example, to sync locally, it'll look like ils sync <table name>.

You can set sync (and other items) as a cron job, to keep your database in sync.

As a Dependency

As mentioned briefly above, calling require('infusionsoft-local-sync') returns a constructor that will return an API instance when passed a config object.

The core sub-keys are:

• api : Provides direct access to the Infusionsoft API. We're trying to make it paper-thin, so that you can use as many extra features of the underlying library as possible if desired (and to support future changes that may make our current assumptions invalid)
  • api.rest : acts as if you're calling the request library directly, just adding the auth keys as appropriate
  • api.xmlrpc : used to call xmlrpc methods. Currently more than a paper-thin wrapper, but TODO: make this more paper-thin. Since we need to support more than one lib, simplify it as much as possible.
  • api.getUserAPI(access_token, refresh_token) : gets an API instance for a specific OAuth user. Has its own .rest and .xmlrpc sub-keys.
• sync : Handles database synchronization.
• query : Our more-abstracted version of the APIs. Allows intuitive queries using a knex-like interface. Has a shortcut at q which should be identical.
  • query.getUserQuery(access_token, refresh_token) : gets a query object for a given user/access token.
  • query.tagAdd(tag_id, ...contact_id) : adds a tag to a contact. You can pass multiple contact IDs or contact objects as well. You may also pass an array. Returns a promise with the raw API result of the operation (usually true if it succeeded, false if the tag wasn't on the contact in the first place).
  • query.tagAdd(tag_id, ...contact_id): Same as above, but removes a tag. Returns true if successful, false if the contact already had the tag.
  • query(table_name) : creates a Knex.js style object to make a query against the Infusionsoft database. Advanced queries depend on having a locally synced copy of the database.
    • query(table_name).select(...column_names) : selects which columns to get.
    • query(table_name).where(...query_params) : A knex-style where. Also available: orWhere, andWhere etc. Custom fields MUST have the leading underscore.

CLI Verbs

• config : guided ILS configuration wizard.
• sync : runs a sync operation. Only one sync operation for each table can be run at once.
  • -r --resync : Re-synchronizes your local database with Infusionsoft. This makes a lot of queries, so bear in mind your usage limits.
• status : gets sync status of a given table. Includes live and remote variables. Prints an overview if not specified.
• find <Contact> : find an object and print an overview from a given query. Operators: ()|&! =, strings must be quoted, space is assumed and (&) (may want to research what others have done with this).
• fetch <Contact> [Id] : fetches a given object, stores it in the DB, and displays it.

Custom field definitions (i.e. the DataFormField API table) are always synced first before syncing any table that may use them.

CLI Switches

• -f : force run, remove block or stand-off status.

CRUD table?

We probably want some kind of way to list contacts on a web form. Maybe just output to CSV/XLS instead? I can add a CRUD later, as a separate app. Or someone else can.

Dependencies

• Knex.js - Query builder.
• Request.js for Infusionsoft's REST methods.
• nconf - handles configuration
• Includes customized code from the the node-xmlrpc library. Will remove later, instead pulling in as a dependency once a certain pull request is accepted. Yes, we do need both, some information and functionality is available only on XMLRPC and vice-versa.
  • sax and xmlbuilder, which are dependencies of node-xmlrpc.

Testing

We use mocha and chai for testing. Until we get reliable mocks (kind of an oxymoron as the service we're mocking isn't at all reliable) we have to test against a live "application" or "app" (their word for an instance of the CRM database service). Thankfully, they offer free sandbox applications you can sign up for with a credit card. If you want to use OAuth and other related REST goodies you'll also need an application which despite having not just almost, but the exact same name is a completely different thing. They don't even give an adjective anywhere in their documentation to differentiate them. So if you just mention your "application" to someone from Infusionsoft, despite being at the core of what they sell to people and presumably deal with on a daily basis, they have no way to be 100% sure what you're referring to.

To save confusion, from here on I'll call the former the "CRM app" and the other "developer app".

Once you've gotten all registered, you'll need to configure your app. As we're using nconf you can configure the app on-the-fly using environment variables.

In Linux, OSX, BSD etc this can be done from a terminal with the following lines: export api:app= export api:key=<your app key from the "settings" area in your CRM app> export web:client_id=<(optional) your client ID from the developer app> export web:client_secret=<(optional) your client secret from the developer app> export web:local_url=<(optional) a URL that points to port 3000 the machine you're testing on>

In Windows it's mostly the same, you just switch export for set but to save time I'll re-paste it. set api:app= set api:key=<your app key from the "settings" area in your CRM app> set web:client_id=<(optional) your client ID from the developer app> set web:client_secret=<(optional) your client secret from the developer app> set web:local_url=<(optional) a URL that points to port 3000 the machine you're testing on>

Contribution guidelines

Writing tests

Would be incredibly awesome.

Code review

If your pr doesn't make things any worse than it has to be, I'll probably merge it in.

Other guidelines

Infusionsoft is kind of a beast to program for-- I've been telling them for a long time that their practices are just plain bad and unsustainable in the long run, but as my biggest client is stuck with them, so am I. Until the day they see the light, I've committed to writing the best API for their service that I possibly can given the circumstances.

Take "the circumstances" to be "that I can't wait around for them to transmogrify this abomination of an API into something you can hold up to any kind of standard."

Do you know that one of the biggest PHP APIs for dealing with Infusionsoft has built-in transparent retries for failed queries? That's right, up to five times! It doesn't even seem to correspond with server load-- sometimes, queries just fail. There's no error, they just fail to do anything.

It seems to be caused by one of their reverse proxies failing to find a working app server to serve their XML-RPC. The salt in the wound for this though is that it doesn't even send back a proper error that can be interpreted by any XML-RPC client I've ever used. It's just a regular HTML page-- so since the error page isn't a valid XML document, the actual error message is just lost. (Took me a while to figure out what was happening-- just seeing 'Invalid XML-RPC tag "title"' is not in the least helpful.)

Infusionsoft's API services (as of late 2017) are really in a bad state. Maybe the worst I've ever used. I'm not doing them any favors by covering this up or stating it in less-blunt terms. This is something their management NEEDS to know they NEED to clean up. It can only lead to problems (company becoming uncompetitive and shrivelling in the marketplace) if they can't do this halfway competently.

So uh, my point with this diatribe is that do what you can, but if you have to take extreme measures to get something working, well... This project was damned from the start so have at it.

Who do I talk to?

• Chuck Reed ([email protected])