Action Builder SQL Mirror -- Getting Started

January 1, 2025

• SQL mirror documentation launched.

Back To Top ↑

The Action Builder SQL mirror has a few technical features and constraints to be aware of as you start using it.

Software and Architecture

The Action Builder SQL mirror is a Postgres database, a relational database optimized for online transaction processing (OLTP) workloads, running on Amazon AWS servers in the cloud. The SQL mirror is set up on its own server, so your queries are isolated from other environments.

Data

When your SQL mirror was set up, you chose an organization to mirror. The database (schema) you have access to contains all the data relating to that organization.

The database is comprised of separate tables that represent various components of the Action Builder system, including tables for entities (ex: the entities table) that contain data about entities in your organization; tables for your entity contact information, including their personal data (ex: name and email address); tables for fields, tasks, events, etc...

Absolutely all of the data for the organization being mirrored that is stored with Action Builder is available on the SQL mirror, with the exception of a few sensitive columns.

Read-Only

The SQL mirror is read-only. Nobody will be given permissions to write data back to the database, or change any data in the database. The SQL mirror is meant to facilitate data dumps and ad-hoc queries, not be a two-way connection to the Action Builder system.

If you would like to write to the database or add your own indexes or views, you can dump data out of our SQL mirror into another database you control. If you would like to get data into Action Builder, you should use our API.

Real Time And Continuously Updated

Your SQL mirror is continuously updated and data is available almost in real time (typically with no more than a 1 minute delay).

Back To Top ↑

The structure and function of the tables and database columns are documented here. Because the SQL mirror reflects the Action Builder production database structure, the exact tables and columns present may change at any time. This documentation will be updated when it does, but there is no versioning available.

Back To Top ↑

To access the SQL mirror after it has been set up, email us and let us know the public IP addresses of the machines you will be using to connect to the SQL mirror. We will allow those IP addresses and give you a host URL, username, and password from which you can connect to the database.

The username and password will be in the form of a Google Doc. We will share it once with a member of your team. From there, that person can share it with whomever they want. For security reasons, after we share that Google Doc once, we will never share it again. You are responsible for ensuring your team has the access they need once the document is shared, now and into the future.

Back To Top ↑

After you're IP address has been allowed and you've been giving connection details and a username and password, you can connect to your SQL mirror.

Your SQL mirror is a copy of all of your data. As such, it contains information you would probably like to keep private. (ex: It contains API keys, meaning access to the SQL mirror will get you access to write to Action Builder as well as read.) Make sure to connect to your SQL mirror using secure methods, such as SSL, to prevent someone from viewing your data as it transmits over the internet. And make sure not to give out connection credentials to those who should not have them.

Back To Top ↑

Postgres is a row-based data store in the traditional relational databases model. It is optimized for online transaction processing (OLTP) type queries, the type of queries you might imagine an e-commerce website might perform -- retrieving a row of data about a product to display a product page, updating a shopping cart by writing a row of data, updating a few rows after checkout, etc... It is not an analytics database, which are typically optimized for pulling millions of rows within a couple of minutes, aggregating data across many rows (counts, averages, etc...), and the like.

This means that you should only run queries that pull small amounts of data at a time, and you should use traditional relational database techniques for query optimization (ex: use indexes where available). If you need to run analytics queries, you should dump the data out of the SQL mirror into a database better suited for analytics workloads. Be sure to optimize your data dumps, syncs, and ETL jobs for smaller, faster queries. For example, store the last ID you synced and pull data from that ID forward, and limit it to a smaller number of rows for each query.

Queries that take too long may return errors. If you receive these errors, optimize your queries for quicker execution. As a rule of thumb, a query that takes more than a few seconds may not consistently return data.

Back To Top ↑

Most of the SQL mirror's tables and columns are self explanatory, so querying to retrieve data you need should be straightforward. There are a few uses where it's not obvious how to write the correct query, so some sample queries are given for those situations below.

Example: Query A List Of All Entities In A Given Campaign

This query uses the campaigns_entities table to choose which entities you want to pull. Replace [campaign_id] (including the brackets) in the query below with your campaign's ID number to run the query.

		  	
SELECT * 
  FROM entities
JOIN campaigns_entities
 ON campaigns_entities.entity_id = entities.id
WHERE campaigns_entities.campaign_id = [campaign_id]
 			
          

Back To Top ↑

Links to detailed documentation for each database table and each column in each table is available in the sidebar.

Back To Top ↑