Heroku Connect Database Tables
Last updated September 03, 2020
Table of Contents
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 theaccount
database table.Column names use a lowercase version of the Salesforce field name, for example the
AccountNumber
Salesforce field is mapped to theaccountnumber
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.
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.
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:
Column name | Database Type | Indexed | Description |
---|---|---|---|
id |
integer | Yes (primary key) | A unique, auto-incrementing integer primary key. This should be considered an implementation detail. Heroku Connect makes no guarantees about when the sequence is reset or about the uniqueness of values across reloads. |
sfid |
varchar(18) | Yes (unique) | The Salesforce object Id field that is automatically populated when records are synchronized with Salesforce |
systemmodstamp |
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 |
isdeleted |
boolean | No | Used to track the IsDeleted field from Salesforce, allowing Heroku Connect to process records deleted in Salesforce when polling for updates. Only created when the Salesforce object has an IsDeleted field. |
_hc_lastop |
varchar(32) | No | Indicates the last sync operation performed on the record |
_hc_err |
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.
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 thesfid
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.NULL
- No change to the row has been made since the initial load.
_hc_lastop
is not guaranteed to be representative of reality due to a historical bug that can not be easily fixed. If you’re using _hc_lastop
to determine the success of a write to Salesforce, you should always cross-check it against your Trigger Log
When a failure occurs the _hc_err
column will contain a JSON object with the following properties:
Property | Description |
---|---|
op |
The Salesforce operation that was attempted |
src |
The source of the error, set to SFDC for Salesforce errors |
msg |
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 |
Checkbox | boolean | |
Currency | double precision | |
Date | date | |
DateTime | timestamp without time zone | Times are stored as UTC |
varchar(80) | ||
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 sfid (see System columns) |
Lookup Relationship | varchar(18) | |
Number | double precision | |
Percent | double precision | Percentage values are given as if the percent sign is removed. For example, 100% will be stored as 100 in Postgres. To use in calculations, you may need to divide this number by 100. |
Phone | varchar(40) | Includes formatting e.g. (650) 555-0100 |
Picklist | varchar | Length of field is provided by Salesforce based on picklist items. For add-ons created after August 13, 2020 this is guaranteed to be varchar(255). |
Picklist (Multi-Select) | varchar(4099) | Multiple selections are returned as a semi-colon delimited list e.g. item 1;item 2 |
Reference | varchar(18) | |
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 |
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
The way Heroku Connect handles encrypted strings depends on whether your Salesforce organization is using Shield Platform Encryption or Classic Encryption.
Shield Platform Encryption
Fields or records that are enabled for Shield Platform Encryption are encrypted at rest in Salesforce. If the user credentials used to authorize Heroku Connect with Salesforce have read access for an encrypted field or record, unencrypted values will be received from Salesforce and stored unencrypted in your database. If the user doesn’t have read access, it won’t be able to see the data. The “View Encrypted Data” permission does not apply for Shield Platform Encryption.
Classic Encryption
If the user credentials used to authorize Heroku Connect with Salesforce don’t have the 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 ****-****-****-1023
.
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 when using Classic Encryption, 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.
For example:
- The
Contact
object defines a formula field calledname_formula__c
that refers to theName
field of the relatedAccount
object - The
Name
field of the relatedAccount
object is updated - The value of
name_formula__c
reflects the change when theContact
object is queried - The
SystemModStamp
of theContact
object is not changed therefore the newname_formula__c
value 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 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:
Field name | Database Type |
---|---|
MailingCity | varchar(40) |
MailingCountry | varchar(80) |
MailingLatitude | double precision |
MailingLongitude | double precision |
MailingPostalCode | varchar(20) |
MailingState | varchar(80) |
MailingStreet | varchar(255) |
Similarly a custom Geolocation field named OfficeLocation
would be mapped using the following fields:
Field name | Database Type |
---|---|
OfficeLocation__Latitude__s | double precision |
OfficeLocation__Longitude__s | double precision |
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
. There are SOQL query restrictions in Salesforce that prevent Heroku Connect from being able to sync Big Objects.
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
.
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.
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.
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 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
Heroku Connect will not immediately 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.
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.