Call Me Back, Postgres Using Postgres Triggers with Listen / Notify to Implement a Data Sync Process Ifat Ribon @i_ribon9 github.com/inveterateliterate 

Ifat Ribon Principal Architect LaunchPad Lab ⛲ 🏃 🥍 github.com/inveterateliterate [email protected] About Me 

Case Study Agenda 01 Sync Approaches 02 Key Concepts 03 System Design 04 Resources 05 Appendix 06 

01 Case Study: Salesforce Syncing with Heroku Connect 

Core Legacy App Heroku Connect Salesforce Instance Core Postgres Database Core Rails API Main Players 

Key Connections 

02 Sync Approaches 

Rails Callbacks ● What: Rails ActiveRecord object life cycle hooks such as after_create, after_update, after_delete ● Why: Use Rails DSL and conventions to trigger additional logic based on a change to an object ● Why not: Direct consumers of the app database will not reach the Rails callbacks 

Polling ● What: Chron job to periodically look at the database and identify changes ● Why: Accessible to all consumers of the database, flexibility in timing and considerations of the data syncing process ● Why not: Expensive to frequently query the database; lower reliability writing the checks by hand 

PG Triggers with Listen/Notify ● What: Database-level callbacks that tap into a pub/ sub scheme ● Why: Any event from any client connected to the database will be captured ● Why Not: Requires Postgres-specific knowledge and writing SQL; may be less discoverable or obvious in a conventional Rails application 

03 Key Concepts 

PG Triggers Callback at the database level that executes a defined function before, after, or instead of identified operations (i.e., INSERT, UPDATE, DELETE) Surfaces metadata of the event, such as the operation (event name), schema, table name, and OLD and NEW versions of the record’s attributes Icons courtesy of the noun project: Gregor Cresnar and darwis 

Define a Function 

Apply Trigger Function 

PG Notify and Listen NOTIFY sends a notification event with a defined payload string through defined channels LISTEN establishes sessions on defined channels to capture notifications sent on those channels Icons courtesy of the noun project: Siipkan Creati, Ragal Kartidev 

Broadcast a Notification 

Listen for Notifications 

No content

Perform pg_notify 

Using Rails Migrations 

Using Rails Migrations 

04 System Design 

Syncing Process Design 

Maintaining a Connection web: rails s worker: bundle exec sidekiq listener: bundle exec rake database_listeners:listener 

Logging Triggers 

Avoiding Continuous Trigger Loops 

Solving for Edge Cases Identify dependencies or conflicts 

Solving for Edge Cases Write Automated Tests! 

05 Resources 

Resources ● PG Notify Documentation ● PG Listen Documentation ● PG Trigger Documentation ● fx Gem ● HairTrigger Gem ● Heroku Connect ● PostgreSQL Listen / Notify 

06 Appendix 

Rails Migrations: With fx Gem 

Thank You! [email protected] github.com/inveterateliterate