Skip Navigation
Show nav
Heroku Dev Center
  • Get Started
  • Documentation
  • Changelog
  • Search
  • Get Started
    • Node.js
    • Ruby on Rails
    • Ruby
    • Python
    • Java
    • PHP
    • Go
    • Scala
    • Clojure
  • Documentation
  • Changelog
  • More
    Additional Resources
    • Home
    • Elements
    • Products
    • Pricing
    • Careers
    • Help
    • Status
    • Events
    • Podcasts
    • Compliance Center
    Heroku Blog

    Heroku Blog

    Find out what's new with Heroku on our blog.

    Visit Blog
  • Log inorSign up
View categories

Categories

  • Heroku Architecture
    • Dynos (app containers)
    • Stacks (operating system images)
    • Networking & DNS
    • Platform Policies
    • Platform Principles
  • Command Line
  • Deployment
    • Deploying with Git
    • Deploying with Docker
    • Deployment Integrations
  • Continuous Delivery
    • Continuous Integration
  • Language Support
    • Node.js
    • Ruby
      • Working with Bundler
      • Rails Support
    • Python
      • Background Jobs in Python
      • Working with Django
    • Java
      • Working with Maven
      • Java Database Operations
      • Working with the Play Framework
      • Working with Spring Boot
      • Java Advanced Topics
    • PHP
    • Go
      • Go Dependency Management
    • Scala
    • Clojure
  • Databases & Data Management
    • Heroku Postgres
      • Postgres Basics
      • Postgres Performance
      • Postgres Data Transfer & Preservation
      • Postgres Availability
      • Postgres Special Topics
    • Heroku Redis
    • Apache Kafka on Heroku
    • Other Data Stores
  • Monitoring & Metrics
    • Logging
  • App Performance
  • Add-ons
    • All Add-ons
  • Collaboration
  • Security
    • App Security
    • Identities & Authentication
    • Compliance
  • Heroku Enterprise
    • Private Spaces
      • Infrastructure Networking
    • Enterprise Accounts
    • Enterprise Teams
    • Heroku Connect (Salesforce sync)
    • Single Sign-on (SSO)
  • Patterns & Best Practices
  • Extending Heroku
    • Platform API
    • App Webhooks
    • Heroku Labs
    • Building Add-ons
      • Add-on Development Tasks
      • Add-on APIs
      • Add-on Guidelines & Requirements
    • Building CLI Plugins
    • Developing Buildpacks
    • Dev Center
  • Accounts & Billing
  • Troubleshooting & Support
  • Heroku Enterprise
  • Heroku Connect (Salesforce sync)
  • Heroku Connect Database Tables

Heroku Connect Database Tables

English — 日本語に切り替える

Last updated September 03, 2020

Table of Contents

  • Database table structure
  • Salesforce schema changes

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.

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 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.

  • 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
Email 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 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

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:

  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.

Keep reading

  • Heroku Connect (Salesforce sync)

Feedback

Log in to submit feedback.

Writing Data to Salesforce with Heroku Connect Heroku Connect Diagnostics

Information & Support

  • Getting Started
  • Documentation
  • Changelog
  • Compliance Center
  • Training & Education
  • Blog
  • Podcasts
  • Support Channels
  • Status

Language Reference

  • Node.js
  • Ruby
  • Java
  • PHP
  • Python
  • Go
  • Scala
  • Clojure

Other Resources

  • Careers
  • Elements
  • Products
  • Pricing

Subscribe to our monthly newsletter

Your email address:

  • RSS
    • Dev Center Articles
    • Dev Center Changelog
    • Heroku Blog
    • Heroku News Blog
    • Heroku Engineering Blog
  • Heroku Podcasts
  • Twitter
    • Dev Center Articles
    • Dev Center Changelog
    • Heroku
    • Heroku Status
  • Facebook
  • Instagram
  • Github
  • LinkedIn
  • YouTube
Heroku is acompany

 © Salesforce.com

  • heroku.com
  • Terms of Service
  • Privacy
  • Cookies