Heroku Connect Database Tables

Last updated 18 May 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 Account Salesforce object is mapped to the account database table.

• Column names use a lowercase version of the Salesforce field name, for example the AccountNumber Salesforce field is mapped to the accountnumber database column.

• 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 TABLE SQL commands. Heroku Connect will populate any newly mapped fields with data from Salesforce.

System columns

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:

Status information

The _hc_lastop and _hc_err columns can be used to aid in debugging or to surface sync status information in your application.

The _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 sfid will 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:

Mapped data types

The table below shows how Salesforce field types are mapped to columns in the database.

Empty/Blank/NULL Strings

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.

Encrypted strings

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.

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.

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.

For example:

• The Contact object defines a formula field called name_formula__c that refers to the Name field of the related Account object
• The Name field of the related Account object is updated
• The value of name_formula__c reflects the change when the Contact object is queried
• The SystemModStamp of the Contact object is not changed therefore the new name_formula__c value is not synchronized to the database

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 Attachment, Document and 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.

Compound fields

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:

Similarly a custom Geolocation field named OfficeLocation would be mapped using the following fields:

Unsupported object types

The following object types are currently not supported:

Big Objects

The API name for this type of object is suffixed with __b.

Platform Events

The API name for this type of object is suffixed with __e.

External Objects

The API name for this type of object is suffixed with __x.

History Objects

The API name for this type of object is suffixed with History, e.g. AccountHistory.

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;

System tables

Heroku Connect creates several system tables within the schema:

• _hcmeta

  This table contains metadata that allows Connect to ensure the database is properly initialized and configured.

• _trigger_log

  The _trigger_log table 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.

• _trigger_log_archive

  Once data in the _trigger_log table 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.

Salesforce schema changes

Adding fields

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.

Changing fields

If you need to change a field definition you should:

1. Edit the mapping and remove the field you will be changing.
2. Change the field definition in Salesforce.
3. 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.

Removing fields

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.