Heroku Connect Database Tables
Last updated 13 January 2018
Database table structure
When you map objects, Heroku Connect will create or update the database tables used to store data for the mapped object.
Mapping tables use a lowercase version of the Salesforce object name, for example the
AccountSalesforce object is mapped to the
Column names use a lowercase version of the Salesforce field name, for example the
AccountNumberSalesforce field is mapped to the
Creating a new mapping creates a new database table that Heroku Connect will automatically populate with data from Salesforce.
Editing an existing mapping will modify the existing database table using
ALTER TABLESQL commands. Heroku Connect will populate any newly mapped fields with data from Salesforce.
You should avoid creating mapped tables yourself. Heroku Connect will not replace an existing table when creating a new mapping and therefore, if the table is not created correctly, you will experience errors when syncing data. If you are using an ORM framework to model your mappings you should ensure that the framework does not attempt to create the underlying tables in the database.
In addition to the Salesforce fields you choose to map to database columns, Heroku Connect will automatically add the following system columns to the mapped tables:
|Column name||Database Type||Indexed||Description|
||integer||Yes (primary key)||A unique, auto-incrementing integer primary key|
||varchar(18)||Yes (unique)||The Salesforce object
||timestamp||Yes||The date and time (in the UTC time zone) that the Salesforce object was last modified and used by Heroku Connect when polling for updates|
||boolean||No||Used to track the
||varchar(32)||No||Indicates the last sync operation performed on the record|
||varchar(1024)||No||If the last sync operation resulted in an error then this column will contain a JSON object containing more information about the error|
These columns are used by Heroku Connect to track and report on sync operations and must not be removed from the table. While you can read from the columns to aid in debugging you must not write to these columns as you will likely cause errors with Heroku Connect sync operations.
_hc_err columns can be used to aid in debugging or to surface sync status information in your application.
_hc_lastop column will initially be blank before being updated to one of the following statuses as part of the sync process:
PENDING- a new row in the database is awaiting sync to Salesforce.
INSERTED- a new row in the database has been inserted into Salesforce: at this point the
sfidwill also be populated.
UPDATED- an existing row in the database has been successfully updated in Salesforce.
SYNCED- a new row has been synchronized from Salesforce.
FAILED- Salesforce synchronization was unsuccessful.
When a failure occurs the
_hc_err column will contain a JSON object with the following properties:
||The Salesforce operation that was attempted|
||The source of the error, set to
||The message that was returned by Salesforce describing the error|
Mapped data types
The table below shows how Salesforce field types are mapped to columns in the database.
Most Salesforce field types are fully supported by Heroku Connect: see below for details of fields types that are not supported at this time.
|Salesforce Type||Database Type||Notes|
|AnyType||text||Values are converted from their dynamic type (e.g. text, date, number) into text when stored in the database|
|Auto Number||varchar||Length of field is provided by Salesforce based on field configuration|
|DateTime||timestamp without time zone||Times are stored as UTC|
|Encrypted String||varchar||Length of field is provided by Salesforce based on field configuration - see below for more information on how these fields are handled|
|External Lookup Relationship||varchar||Length of field is provided by Salesforce based on field configuration|
|Formula||The database type used is determined by the formula return type (for example a Checkbox return type will use a boolean database type) - see below for more information on how these fields are handled|
|ID||varchar(18)||Automatically mapped as
|Phone||varchar(40)||Includes formatting e.g. (650) 555-0100|
|Picklist||varchar||Length of field is provided by Salesforce based on picklist items|
|Picklist (Multi-Select)||varchar(4099)||Multiple selections are returned as a semi-colon delimited list
|Roll-Up Summary||double precision||See below for more information on how these fields are handled|
|Text||varchar||Length of field is provided by Salesforce based on field configuration|
|Text Area||text or varchar||text if the field is length 256 or greater; varchar otherwise|
|Text Area (Long)||text or varchar||text if the field is length 256 or greater; varchar otherwise|
|Text Area (Rich)||text or varchar||text if the field is length 256 or greater; varchar otherwise|
|Time||time without time zone|
|URL||varchar||Length of field is provided by Salesforce based on field configuration|
The Salesforce data model does not have a notion of empty strings. For this reason, all string types should use a value of NULL in the DB to denote an empty or blank string value. Should an empty string be written to a table managed by Connect, the value will automatically be converted to NULL.
If the user credentials used to authorize Heroku Connect with Salesforce don’t have View Encrypted Data permission, then encrypted strings will be received from Salesforce in masked format.
For example an encrypted credit card number would be stored in the database as
It is possible to update the database with a new plain text value and Salesforce will take care of encryption when the new data is pushed from the database. The plain text value in the database will be overwritten with the masked format when the record is next updated with data from Salesforce.
To allow unencrypted values to be received from Salesforce you must enable View Encrypted Data permission for the user credentials used to authorize Heroku Connect.
Cross-object formula and roll-up summary fields
Formula and roll-up summary fields are calculated by Salesforce at query-time and can make use of other fields, functions and literal values. They can also refer to fields in parent objects via master-detail or lookup relationships: however this can cause problems when Heroku Connect is synchronizing data.
Changes to formula and roll-up summary fields that are driven by a change in a parent object do not update the
SystemModStamp of the child object - this means that the object will not be synchronized by Heroku Connect.
Contactobject defines a formula field called
name_formula__cthat refers to the
Namefield of the related
Namefield of the related
Accountobject is updated
- The value of
name_formula__creflects the change when the
Contactobject is queried
Contactobject is not changed therefore the new
name_formula__cvalue is not synchronized to the database
See the Salesforce Idea Exchange for discussion of possible workarounds for this issue.
Unsupported data types
The following types are not supported and will not be shown in the Mapped Fields list when creating or editing a mapping.
Base64 binary fields
Binary files stored in
Scontrol objects use a Base64 encoded binary data type. This data type is currently unsupported in the Bulk API and is therefore also unsupported by Heroku Connect.
The Address and Geolocation compound field types cannot be mapped directly however it is possible to add the component fields to a mapping.
For example the
Contact object has a
MailingAddress Address field that can be mapped using the following fields:
|Field name||Database Type|
Similarly a custom Geolocation field named
OfficeLocation would be mapped using the following fields:
|Field name||Database Type|
Querying mapped tables
Mapped tables can be queried like any other table in your Postgres database. You will need to qualify table names with the schema name you chose when provisioning the add-on, for example using the default
salesforce schema name:
SELECT * FROM salesforce.account;
You may prefer to add the schema to the Postgres schema search path to allow tables to be queried without requiring fully qualified names, for example:
SET search_path TO salesforce,public; SELECT * FROM account;
When setting the search path you must include the
public schema as it contains shared functions used by Heroku Connect.
Heroku Connect creates several system tables within the schema:
This table contains metadata that allows Connect to ensure the database is properly initialized and configured.
_trigger_logtable is used to record updates that need to be written to Salesforce. Data remains in this table for up to one day allowing you to track updates that are pending, in progress or have already completed. If an error occurs while writing to Salesforce it will be logged in this table.
Once data in the
_trigger_logtable is more than a day old it is moved into this archive table where it will be available for up to 31 days. Note: the archive is only available for 7 days demo plan.
These tables are used by Heroku Connect to track sync operations and must not be removed from the schema. While you can read from the tables to aid in debugging you should not write to these tables as you will likely cause errors with Heroku Connect sync operations.
Salesforce schema changes
Adding new fields to objects in Salesforce will not impact Heroku Connect sync operations. If you choose to add the new field to an existing mapping Heroku Connect will automatically create a column in the mapped database table, retrieve data for the field from Salesforce and populate existing records in the database with that data.
Heroku Connect will not automatically detect a change in the definition of a field in Salesforce, for example changing the data type or length of a field, and this may lead to synchronization errors.
If you need to change a field definition you should:
- Edit the mapping and remove the field you will be changing.
- Change the field definition in Salesforce.
- Edit the mapping and add back the field you removed in step 1: the data type for the field should now reflect the changes you made in step 2.
When removing a field from a Salesforce object it is best practice to first remove the field from your mapping in Heroku Connect. If you choose to remove the field from the Salesforce object before removing it from your mapping then Heroku Connect will report an error the next time the mapping is polled for changes. To resolve the error you will need to edit the mapping and remove the missing fields: they will be highlighted in red. When you save the changes Heroku Connect will remove the columns from the mapped table and sync operations should resume.