Email campaigns change to optimize darwin

Revised Notification creation Architecture

1. First, use a SQL job to find Property or People or other records in darwin.Cloud that have been added or edited and need to generate a notification email or sms.
 

• Objective: Detect which records in specific tables are modified (INSERT, UPDATE, DELETE) need to write a Notification. Then create the email/sms in the most efficient way.
   
• Approach:
  • SQL job evaluates ONLY tables that have an active Notification schedule against the data in the table. This allows us to only search in tables that need to be reviewed when figuring out which rows have been modified.
  • This SQL job looks for records that have been inserted or modified, but executes the search on the minimum number of tables possible.
  • This will speed the process and reduce database reads.

2. Offload Processing to a Dedicated Queue Processor
 

• The production server is only used for storing new records and modifications to existing records.  The work of calculating which records need to create a notification and the actual processing and creating of the email message is offloaded to a dedicated Email Queue Processor Server. This processor server:
   
  • Finds modified records from the Always On SQL Replica that meet the criteria of every active notification.
     
  • It does this by reading the modified records from the Always On SQL Replica.
     
  • This approach keeps the darwin.Cloud Production SQL database from having any work load associated with searching for records that need to generate emails/sms. And it isolates the darwin.Cloud Production servers from performing the work of generating the email or SMS message.
     
  • The Dedicated Queue processor launches a job with Kafka to generate the HTML Email or SMS messages for each record that meets the notification criteria. It stores the message HTML and other relevant data in the Email queue tables.
     

3. Store HTML Messages in a Separate Table on Dedicated Hardware
 

• After generating the HTML messages, the messages that need to be sent are saved to a dedicated Queue table hosted on separate storage (e.g., NVMe drives on a separate SQL Server).
   
• This ensures that the storage and I/O operations related to the Email/SMS messages do not impact production operations.

4. Use a SQL Replica, SQL Job and Kafka for Scalability and Reliability
 

• Changes on the production server are pushed to an Always On SQL Replica.
   
• Multi-threaded writing of Notifications is done by KAFKA based on changes the SQL job finds in the SQL server Replica - not the live Production darwin.Cloud database. This keeps darwin.Cloud Production servers from being over-loaded writing emails that do not need real- time processing.
   
• The SQL job is trained to look for records in the SQL Replica where the createDate or the modifiedDate or PostDate (etc, etc, etc)  meets the criteria for sending a Notification.  Programmers have structured the code to:
  • minimize the number of table records that need to be searched in the SQL Replica. 
  • make sure Agent Totals are updated (aggregates for reporting) before creating a Notification that displays an Agent Total
  • make sure the stored procedures are neither missing or double sending Notifications due to the slight delay in the SQL Server Replica being updated.
  • setup KAFKA jobs to never “retry” which can lead to multiple email deliveries of the same message
     
• The Email Queue tables hosted on separate, dedicated storage (e.g., NVMe drives on a separate SQL Server). These tables are read by a lightweight Email/SMS Sender Service, which:
   
  • Pulls messages from the Queue table.
  • Sends them using an Email API (e.g., SendGrid, MailChimp) or Trulia SMS gateway.
  • Logs the delivery status for reporting or troubleshooting.

Data Flow Summary

1. Production Server:
   • Records modifications to table data.
   • Sends all modified records to an Always on SQL Replica on separate hardware
2. SQL job:
   • Searches Always on SQL Replica for records that need Email or SMS Notification processing.
   • Runs on a separate SQL server (not production server) dedicated to creating email/sms messages
3. Queue Processor Server:
   • Sends jobs to Kafka for each Email/SMS that needs to be written.
   • Queries the SQL Replica with a stored procedure that extracts all needed merge fields.
   • Generates the final HTML/SMS messages and stores them in a dedicated Queue table.
4. Queue Table:
   • Stores generated Email/SMS messages on separate hardware for efficient retrieval and no impact or conenection to the darwin.Cloud Production database.
5. Email/SMS Sender Service:
   • Sends messages from the Queue table and logs the delivery status.

Benefits of This Architecture

1. Minimal Burden on the Production Server:
   • darwin.Cloud Production SQL server responsibilities are limited to saving and retrieving data entered into darwin.Cloud
2. Efficient Use of the SQL Replica:
   • The Queue Processor Server queries a SQL Replica for the data needed to generate messages, ensuring read-intensive operations are offloaded away from darwin.Cloud Production SQL server.
3. Scalability:
   • Kafka provides a scalable, distributed platform for handling large volumes of  email/sms change events, allowing the system to grow as needed.
4. Dedicated Storage:
   • Messages are stored on separate hardware, ensuring darwin.Cloud production performance is unaffected by the Email/SMS workflow.
5. Asynchronous Processing:
   • Kafka introduces flexibility with multi-threaded processing, ensuring that the SQL Replica and the Queue Processor Server can write many email messages simultaneously in separate threads.

Why this matters to you - Streamlining Notifications While Maintaining Peak Performance

At AccountTECH, we’ve designed an advanced system to ensure that Email and SMS notifications are generated and sent without impacting the performance of darwin.Cloud. Here’s how it works:

1. Efficient Change Detection:
   • When important records are modified, our system quickly detects these changes without slowing down your daily operations.
2. Optimized for Performance:
   • Instead of burdening the main production server, the data needed for notifications is retrieved from a high-performance replica server optimized for such tasks.
3. Asynchronous and Scalable Processing:
   • By leveraging cutting-edge tools like Kafka, we process notifications asynchronously, ensuring that your darwin.Cloud remains fast and responsive, even during high-demand periods.
4. Dedicated Resources for Notifications:
   • All notifications are processed and stored on dedicated servers, ensuring that the darwin.Cloud system stays focused on what matters most: serving your business.
5. Reliable Delivery:
   • Our notification system ensures that every Email or SMS is crafted with precision and sent using reliable delivery channels, giving you peace of mind that no message is missed.

This architecture reflects our commitment to providing high-performance, reliable, and scalable solutions that keep your business running smoothly.