Direct Database Access

Table of Contents
Overview
Mailing Lists
Subscribers

Overview

We do not support writing to the database because it is possible to get the data into an invalid state by using raw CREATE/INSERT/UPDATE commands against this database. Instead, use the API. When using the API, validation of the data is enforced and sometimes additional actions (such as updating dependent records) are performed. Modifying the data or schema in the database through direct access is an unauthorized modification of the software and may invalidate your support.

We do, however, realize that some on-premises customers are going to want to perform read-only access on the database. Contact GreenArrow technical support to have a read-only account set up for you.

The database behind GreenArrow Studio is complex and we do not provide full documentation of the schema. However, we do provide some limited and partial documentation to point those who are going to access the database directly in the right direction.

The database schema itself and the presence of documentation on the schema are subject to change without notice.

Mailing Lists

Mailing lists are stored in the s_mailing_lists table.

To list all non-deleted mailing lists, run this query:

SELECT id, name FROM s_mailing_lists WHERE active;

Subscribers

Larger mailing lists will have their own subscriber tables. This speeds database access when a scan of all subscribers in a mailing list is required.

To find the table containing the subscribers for a mailing list, look at the s_mailing_lists.subscribers_table_name column:

If this is null, then the subscribers are in s_subscribers.
If this is not null, then the subscribers are in the table named by the contents of that column. This table will have have the same schema as s_subscribers.

As the number of subscribers in a mailing list grows, the subscribers may be moved to their own table.

Here are two example queries that search for a subscriber by email address in a particular mailing list:

SELECT id, email
FROM s_subscribers_99999
WHERE mailing_list_id = 999999 AND LOWER(email) = LOWER('[email protected]')

And:

SELECT id, email
FROM s_subscribers
WHERE mailing_list_id = 999999 AND LOWER(email) = LOWER('[email protected]')

To search for a subscriber in any mailing list, query the s_subscriber_statuses table as follows:

SELECT *
FROM s_subscriber_statuses
WHERE LOWER(email) = LOWER('[email protected]')

Each subscriber has an entry in its mailing list’s table and in the s_subscriber_statuses table. This allows for quick searching of a subscriber by email address without knowing what mailing list or table it is in.

The s_pending_subscribers table contains subscription requests pending confirmation. This is used as part of a confirmed-opt-in process.