Last updated December 08, 2022

Database Table Structure

When you map objects, Heroku Connect creates or updates 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 automatically populates with data from Salesforce.

• Editing an existing mapping modifies the existing database table using ALTER TABLE SQL commands. Heroku Connect populates 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 automatically adds the following system columns to the mapped tables:

System Fields: Read-Only Columns

Heroku Connect ignores writes to some columns that are read-only when syncing writes to Salesforce. These columns correspond to the system fields found on most Salesforce objects. See System Fields for more info.

You can’t set the following columns when syncing writes of new records to Salesforce:

• CreatedDate
• Id
• IsDeleted
• LastModifiedDate
• SystemModstamp

You can’t update the following columns when syncing updates to Salesforce:

• CreatedById
• CreatedDate
• IsDeleted
• LastModifiedById
• LastModifiedDate
• SystemModstamp

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 is initially 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 is also 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.

When a failure occurs the _hc_err column contains a JSON object with the following properties:

Mapped Data Types

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

Empty/Blank/NULL Strings

The Salesforce data model doesn’t have a notion of empty strings. For this reason, all string types must use a value of NULL in the DB to denote an empty or blank string value. If an empty string is written to a table managed by Connect, the value is automatically 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 is 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 doesn’t 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 are received from Salesforce in masked format.

It’s possible to update the database with a new plain text value and Salesforce takes care of encryption when the new data is pushed from the database. The plain text value in the database is overwritten with the masked format when the record is next updated with data from Salesforce.

Cross-Object Formula and Roll-Up Summary Fields

Salesforce calculates formula and roll-up summary fields at query-time and can use 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 aren’t supported and aren’t shown in the Mapped Fields list when creating or editing a mapping.

Base64 Binary Fields

Binary files stored in objects such as Attachment, ContentVersion, Document and Scontrol use a Base64 encoded binary data type. Base64 fields are unsupported by Heroku Connect as they aren’t supported in Bulk API queries. This means that images or other types of binary data are unsupported. An alternative approach is to store images in Amazon S3 and sync a simple text field containing a URI for the image between Salesforce and your database.

Compound Fields

The Address and Geolocation compound field types can’t be mapped directly however it’s 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. 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 must 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 can 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 must 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’s logged in this table.

• _trigger_log_archive

After data in the _trigger_log table is more than a day old it’s moved into this archive table where it’s available for up to 31 days. Note: the archive is only available for 7 days in the demo plan.

Salesforce Schema Changes

Adding Fields

Adding new fields to objects in Salesforce doesn’t impact Heroku Connect sync operations. If you choose to add the new field to an existing mapping Heroku Connect automatically creates a column in the mapped database table, retrieves data for the field from Salesforce, and populates existing records in the database with that data.

• When a single column is added to a mapping, Heroku Connect retrieves that object’s data from Salesforce, excluding any records with NULL values for that field.
• When multiple columns are added, the query to get the data from Salesforce doesn’t exclude NULL values.

Changing Fields

If you need to change a field definition:

1. Edit the mapping and remove the field you’re 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 now reflects the changes you made in step 2.

Removing Fields

When removing a field from a Salesforce object it’s 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, edit the mapping and remove the missing fields: they’re highlighted in red. When you save the changes Heroku Connect removes the columns from the mapped table and sync operations resume.