Event Delivery Methods

Table of Contents
HTTP POST
Queue Table in Remote Database
Queue Table in GreenArrow’s PostgreSQL Database
SQL Query in Remote Database
File

There are multiple methods that can be used to deliver events to your application.

In most cases, a single method that’s described on this page would be selected, and used for all events. However, you also have the option to have events for different Mail Classes delivered to different destinations. For example, GreenArrow Studio events could be delivered to a MySQL server, events for the transactional Mail Class could be delivered to a Microsoft SQL Server, and all other events could be delivered to an HTTP server.

There is a delay between when an event occurs and when it gets delivered to the configured destination. When both your GreenArrow server, and the destination service are under low load, this delay is on the order of one minute. The delay can increase if either end is under high load.

When you configure a new delivery method, all events, including those that accumulated in GreenArrow’s events table and have not been deleted from GreenArrow yet will be delivered. If you’re using the leave_in_queue setting, have a failure or slowdown in delivering events to an existing destination, or have been running GreenArrow without configuring a delivery method, this could cause a large amount of events to initially be delivered. If this is not desirable, then the drop_from_queue setting can be used to first purge the accumulated events from GreenArrow.

HTTP POST

When an event occurs, GreenArrow can make an HTTP POST request to a URL that you specify.

If the use_json_for_http_post option is true:

The POST will have a Content-Type of application/json and contain a JSON-encoded object representing the event’s data.
To indicate successful receipt of the event your application must return a 2xx success HTTP status code, like 200. Otherwise the event will be re-queued for later transmission.

If the use_json_for_http_post option is false:

The POST will have a Content-Type of application/x-www-form-urlencoded and include the event’s data as form parameters.
To indicate successful receipt of the event your application must return a 2xx success HTTP status code, like 200, and the returned document must be exactly the text ok (however, whitespace before or after the text ok is permitted). Otherwise the event will be re-queued for later transmission.

Queue Table in Remote Database

Events can be added to a queue table on your database server. When there is a new event, GreenArrow will connect to your database and INSERT the event’s row into the queue table. Your system can then connect to the database, SELECT out the new events from this table and DELETE the rows after processing the events.

MySQL

Here’s an example queue table in MySQL:

id event_type event_time email listid list_name list_label sendid bounce_type bounce_code bounce_text click_url click_tracking_id studio_rl_seq studio_rl_recipid studio_campaign_id studio_autoresponder_id studio_is_unique studio_mailing_list_id studio_subscriber_id studio_ip studio_rl_seq_id studio_rl_distinct_id engine_ip user_agent json_before json_after timestamp channel status is_retry msguid sender mtaid injected_time message outmtaid outmtaid_ip outmtaid_hostname sendsliceid throttleid mx_hostname mx_ip synchronous from_address headers is_privacy_open message_size smtp_timing )

class="highlight">

CREATE TABLE events ( bigint NOT NULL AUTO_INCREMENT PRIMARY KEY, varchar(30), int, text, text, text, text, text, text, int, text, text, text, bigint, text, int, int, boolean, int, int, varchar(40), int, int, varchar(40), text, text, text, double, text, text, int, text, text, text, int, text, int, varchar(40), text, text, int, text, varchar(40), boolean, text, text, boolean, integer, text class="n">CHARACTER SET utf8;

Here’s an example of creating a MySQL user who has access to the newly created table.

CREATE USER 'greenarrow'@'127.0.0.1' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, UPDATE, DELETE ON db.events TO 'greenarrow'@'127.0.0.1';



You’ll need to make a few replacements in the above query:


  Replace 127.0.0.1 with the IP address that GreenArrow will be connecting from.
  Replace password with the actual password that you wish to assign to the greenarrow user.
  Replace db with the name of the database that the events table exists on.


PostgreSQL

For an example queue table in PostgreSQL, see the table described in the “Queue Table in GreenArrow’s PostgreSQL Database” section.

Other Databases

Events can be logged to other databases as well, including MS SQL Server. Queue tables on other types of databases would use structures similar to those shown in the MySQL and PostgreSQL examples.

Generally speaking, events can be communicated to any database type that a Perl DBI driver is available for. If you need to use a database type that’s not listed on this page, please contact GreenArrow technical support to confirm that we can support it.

Queue Table in GreenArrow’s PostgreSQL Database

Events can be added to a queue table on your GreenArrow Engine server’s PostgreSQL database. When there is a new event, GreenArrow Engine will INSERT a row into its local queue table. Your system can then connect to the database, SELECT out the new events from this table and DELETE the rows after processing the events.

You may access the events table with the following caveats:


  
    When querying from the events table, you must add destination_id=1 to the
conditions of your WHERE clause. This will cause any events configured using
the event_delivery_destination directive to be excluded.
  
  
    When updating or deleting from the events table, you must add id = ? to the
conditions of your WHERE clause, only updating or deleting events you’ve
already pulled down.
  


The queue table is defined as follows:

CREATE SEQUENCE events_seq;

CREATE TABLE events (
    id                       bigint NOT NULL PRIMARY KEY default nextval('events_seq'),
    event_type               varchar(100),
    event_time               integer,
    email                    varchar,
    listid                   varchar,
    list_name                varchar,
    list_label               varchar,
    sendid                   varchar,
    bounce_type              varchar,
    bounce_code              integer,
    bounce_text              text,
    click_url                varchar,
    click_tracking_id        varchar,
    studio_rl_seq            bigint,
    studio_rl_recipid        varchar,
    studio_campaign_id       integer,
    studio_autoresponder_id  integer,
    studio_is_unique         boolean,
    studio_mailing_list_id   integer,
    studio_subscriber_id     integer,
    studio_ip                inet,
    studio_rl_seq_id         integer,
    studio_rl_distinct_id    integer,
    engine_ip                varchar,
    user_agent               varchar,
    json_before              text,
    json_after               text,
    timestamp                double precision,
    channel                  varchar,
    status                   varchar,
    is_retry                 integer,
    msguid                   varchar,
    sender                   varchar,
    mtaid                    varchar,
    injected_time            integer,
    message                  varchar,
    outmtaid                 integer,
    outmtaid_ip              varchar,
    outmtaid_hostname        varchar,
    sendsliceid              varchar,
    throttleid               integer,
    mx_hostname              varchar,
    mx_ip                    varchar,
    synchronous              boolean,
    from_address             varchar,
    headers                  text,
    is_privacy_open          boolean,
    true_event_id            bigint,
    event_unique_id          varchar,
    server_id                varchar,
    destination_id           integer,
    message_size             integer,
    smtp_timing              text
);



If you prefer to mark processed events as processed, then delete them at a later time, you can create a new column, and index to track which events have been processed by running the following queries:

ALTER TABLE events ADD COLUMN processed boolean;
CREATE INDEX CONCURRENTLY events_processed_idx ON events (id) WHERE processed IS NOT TRUE;



SQL Query in Remote Database

When an event occurs, GreenArrow can connect to your remote database and run an SQL query. This can be configured on a per-event-type basis, so for example, a bounce_bad_address event could run an UPDATE query to modify the subscription status of the subscriber. A stored procedure could also be run.

File

Events can be written to a file using JSON encoding. This event delivery option differs from the others in the following ways:


  The types of events that get delivered to a file are not configurable. Either all events get delivered to a file, or none of them are.
  The columns that you receive data from are not configurable for deliveries to files. GreenArrow writes out data for all columns that apply to each event type.
  Writing of events to a file is performed in addition to the event delivery defined in event_destinations. (For the delivery defined in event_destinations, you can only choose one delivery method per event. For example, an event can be delivered via an HTTP Post, or inserted into a remote database, but not both. The file event writing option operates independently of the other methods. You can choose to deliver events to a file, to a non-file destination, or both.)
  Writing of events to a file happens before processing of non-file destinations defined in event_destinations, if both are configured. This has two implications: (a) if there’s some error in writing out events to a file, other event delivery methods will not receive data until the file delivery issue is resolved, and (b) events will appear in the file even if there are errors or delays in processing the event_destinations instructions.


See the Event Processor documentation for information on how to configure the File delivery method.

It’s important that you set up a log file rotation system because GreenArrow does not rotate the files that it writes events to.

It’s safe to move the file that GreenArrow writes events to. If the file that GreenArrow is writing to is moved, GreenArrow detects the move within one second and starts writing events to a new file. Because of this, if you’re using a logfile rotation system, such as syslog, we recommend configuring it to sleep for 1 second between the time that it moves a log file and the time that it deletes, or compresses it, to ensure that no events are lost.

Events are written using JSON formatting. Each line contains a single JSON object representing an event. Each event contains at least the following keys, in the same order as shown in the table:




time_written_to_logfile


float



A timestamp of when the event was written to the file, in seconds past
the Unix epoch. If the Event Processor’s
filename_append_date setting
is turned on, the date portion of time_written_to_logfile matches
filename_append_date.

Unless the system clock jumps backward, time_written_to_logfile is
always monotonically increasing and can be used to select ranges of
events out of a file. The event_time and event_id keys are not
monotonically increasing.





id


integer



The primary key for the event.





event_time


integer



The time that the event occurred, in seconds past the Unix epoch.





event_type


string



A short string that names the type of event.





Specific events types may have additional keys. See the
Engine Types of Events and
Studio Types of Events
documents for lists of which additional fields are shown for each event type.

time_written_to_logfile float	A timestamp of when the event was written to the file, in seconds past the Unix epoch. If the Event Processor’s filename_append_date setting is turned on, the date portion of `time_written_to_logfile` matches `filename_append_date`. Unless the system clock jumps backward, `time_written_to_logfile` is always monotonically increasing and can be used to select ranges of events out of a file. The `event_time` and `event_id` keys are not monotonically increasing.
id integer	The primary key for the event.
event_time integer	The time that the event occurred, in seconds past the Unix epoch.
event_type string	A short string that names the type of event.