The Heroku Connect Trigger Log
Last updated December 08, 2022
Table of Contents
The trigger log captures changes made to records in Heroku Postgres. Heroku Connect uses the trigger log to send data to Salesforce. This article helps you understand how it works and how to read it.
Overview
Two tables in the schema configured for your Heroku Connect add-on house the trigger log. Connect captures new changes in the _trigger_log table. Processed records move from the _trigger_log table to the _trigger_log_archive.
Heroku Connect processes all read-write captured changes to send to Salesforce in a single process per add-on. Each trigger log entry has a state that indicates if Heroku Connect has processed it. While the trigger log also captures changes for read-only mappings, Heroku Connect takes no actions on these changes.
Changing a mapping from read-write to read-only doesn’t prevent the sending of already captured changes to Salesforce.
Captured Changes
When an INSERT occurs on a mapped read-write table, all the columns are captured. After a successful INSERT into Salesforce, Connect captures the sfid of the newly created record and writes that back to the originating table in Postgres.
When an UPDATE occurs on a mapped read-write table, the trigger compares the values in the row before and after the change. It records only the changed values.
A DELETE operation records just the delete.
If a TRUNCATE operation occurs on a mapped read-write table, the records don’t get deleted in Salesforce. Triggers on mapped tables only apply to INSERT, UPDATE, and DELETE operations. TRUNCATE operations don’t get captured in the _trigger_log table. Delete the records in Salesforce separately yourself.
Connect isn’t able to observe change within transactional bounds and can’t enforce a strict ordering of writes as they occurred in the DB.
Access the Trigger Log
You can view the _trigger_log table of your database by using heroku pg:psql:
SELECT table_name, record_id, action, sf_message
FROM salesforce._trigger_log;
Trigger Log Retention
Demo Heroku Connect add-on plans keep changes in the _trigger_log_archive table for 7 days, while paid add-ons keep changes in the archive for 30 days. Connect automatically purges changes older than 30 days.
Table Structure
Both _trigger_log and _trigger_log_archive tables have the same structure.
| Column | Type | Purpose |
|---|---|---|
| id | integer | Table Primary Key. Also used to determine order of changes. |
| created_at | timestamp | Time change captured. |
| updated_at | timestamp | Changed when entry is modified by Heroku Connect. |
| processed_at | timestamp | Time when Heroku Connect finished working on the entry. |
| table_name | varchar(128) | Table the change was captured from. |
| record_id | integer | id of the record for the captured change. |
| sfid | varchar(18) | Salesforce ID of the captured change (if applicable). |
| action | varchar(7) | The operation captured: INSERT, UPDATE, or DELETE. |
| state | varchar(8) | The current state of the record (see States). |
| values | text | hstore encoded column values captured for the change. |
| sf_message | text | Any error messages returned from Salesforce when an error occurs during writing. (See Heroku Connect Write Errors). |
| old | text | The old value captured from before the change encoded in hstore format. |
States
The trigger log tracks the state of each entry in the state field. The end state of each entry can be: SUCCESS, MERGED, IGNORED, FAILED or READONLY. Additionally, the entry can go through the states NEW, PENDING, IGNORE and BULKSENT
| State | Description |
|---|---|
| SUCCESS | The row was successfully written to Salesforce. |
| MERGED | This only applies to the Merged Writes algorithm. Several changes were made to a single row within a single polling interval. Connect merges all of the changes into a single API request to Salesforce. |
| IGNORED | Connect didn’t attempt to write the record to Salesforce. This state can happen if you update a record, but don’t actually change any data that syncs back to Salesforce (unmapped fields). |
| FAILED | Connect tried to write the record, but failed. The sf_message column contains an explanation. |
| READONLY | A captured change from a read-only table. Connect takes no action on these changes. |
| NEW | A newly captured change ready for processing. |
| IGNORE | A newly captured change that syncs back to Salesforce (unmapped fields). |
| PENDING | Connect is processing this change. |
| BULKSENT | Bulk write is in progress, but not completed yet. |
Troubleshooting With the Trigger Log
The trigger log is useful for troubleshooting write errors. See Heroku Connect Write Errors for more info.