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

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.

HTTP POST

When an event occurs, GreenArrow can make a HTTP POST request to a URL that you specify. The parameters of the POST will be all of the data for the event. Your application must return a document with the word ok to indicate that the event was accepted. If your application returns a different response, then 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 sendsliceid throttleid CHARACTER );

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, text, text, text, text, double, text, text, int, text, text, text, int, text, int, text, int 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.
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(30),
    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,
    sendsliceid              varchar,
    throttleid               integer
);


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.