Heroku Connect Database Tables
Last updated March 30, 2023
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
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 automatically populates with data from Salesforce.
Editing an existing mapping modifies the existing database table using
ALTER TABLESQL commands. Heroku Connect populates any newly mapped fields with data from Salesforce.
Heroku Connect doesn’t replace an existing table when creating a new mapping. If the table isn’t created correctly, you experience errors when syncing data. Avoid creating mapped tables yourself. If you’re using an ORM framework, ensure that it doesn’t attempt to create the underlying tables in the database.
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:
|Column name||Database Type||Indexed||Description|
||integer||Yes (primary key)||A unique, auto-incrementing integer primary key. This must be considered an implementation detail. Heroku Connect makes no guarantees about when the sequence is reset or about the uniqueness of values across reloads.|
||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 contains 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 can likely cause errors with Heroku Connect sync operations.
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:
You can’t update the following columns when syncing updates to Salesforce:
_hc_err columns can be used to aid in debugging or to surface sync status information in your application.
_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
sfidis 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.
_hc_lastop isn’t guaranteed to be representative of reality due to a historical bug that can’t be easily fixed. If you’re using
_hc_lastop to determine the success of a write to Salesforce, always cross-check it against your Trigger Log
When a failure occurs the
_hc_err column contains 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 following table 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 aren’t 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 uses a boolean database type) - see below for more information on how these fields are handled|
|ID||varchar(18)||Automatically mapped as
|Percent||double precision||Percentage values are given as if the percent sign is removed. For example,
|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
|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 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.
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.
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.
For example an encrypted credit card number would be stored in the database as
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.
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
Salesforce calculates formula 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.
Formula fields are automatically calculated, read-only on record detail pages, and don’t update last modified date fields. Changes to formula fields that are driven by a change in a parent object don’t update the
SystemModStamp of the child object. This means that Heroku Connect doesn’t synchronize the object.
Contactobject has a formula field called
name_formula__cthat refers to the
Namefield of the related parent
Namefield on an
Accountrecord is updated.
- The updated value of
name_formula__cis returned when the related
Contactrecord is queried.
Contactrecord isn’t updated so the new
name_formula__cvalue isn’t synchronized to the Heroku Postgres database.
See the Salesforce Idea Exchange for discussion of possible workarounds for this issue.
Roll-up summary fields summarize values from related child records of the parent record and display the value in the parent record in a master-detail relationship. Salesforce calculates roll-up summary fields when the field in the child object that is referenced in the roll-up summary field is updated.
- The parent
Accountobject has a roll-up summary field called
amount_rollup__cthat calculates the sum of the
Amountfield on the related child
Amountfield on an
Opportunityrecord is updated.
- The value of
amount_rollup__cupdates on the
Accountrecord when the
Amountfield is updated on the related
Accountobject is updated eventually so the new
amount_rollup__cvalue is synchronized eventually to the Heroku Postgres database.
Calculating roll-up summary field values can take up to 30 minutes, depending on the number of records affected and other factors. See this Heroku Help article on why your calculated fields aren’t syncing.
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
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.
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:
|Field name||Database Type|
Similarly a custom Geolocation field named
OfficeLocation would be mapped using the following fields:
|Field name||Database Type|
Unsupported Object Types
The following object types are currently not supported:
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.
The API name for this type of object is suffixed with
The API name for this type of object is suffixed with
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;
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 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.
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.
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 must not write to these tables as you can likely cause errors with Heroku Connect sync operations.
Only store tables in your Connect schema that you want synced with Salesforce. Adding other tables pollute your
_trigger_log and cause Connect to slow down while trying to process and ignore those captured changes. If you require access to tables across multiple schema, look into PostgreSQL’s
search_path functionality for your application.
Salesforce Schema Changes
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.
Heroku Connect doesn’t 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 can lead to synchronization errors.
If you need to change a field definition:
- Edit the mapping and remove the field you’re 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 now reflects the changes you made in step 2.
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.