Event data DDL reference

Overview

When you connect a data warehouse to MessageGears Accelerator, you can have MessageGears stream engagement events (deliveries, opens, clicks, bounces, unsubscribes, SMS and push activity, and mobile SDK / in-app events) back into your warehouse through the real-time event feed. To receive that data you first create a set of tables in your warehouse that match the event feed schema.

This page is the single source for the suggested Data Definition Language (DDL) for the MessageGears real-time event feed (v3.2). Select your warehouse tab below and run the statements in a worksheet/console against the schema you want to use.

note

These are starting templates:

• Replace any placeholders (for example <dataset_name> on BigQuery) with your own dataset, and create the tables under the analytics schema you set up for MessageGears.
• The messagegears_campaign_metadata and messagegears_send_history tables are examples only — coordinate with your marketing team on the columns you actually need.
• You only need the tables for the channels and events you use. Create the email tables at minimum; add the push, SMS, mobile SDK, and in-app tables if you send on those channels.
• All warehouses cover the same event set. The Snowflake and BigQuery statements come from the Accelerator schema generator; the Redshift, PostgreSQL, and SQL Server statements are translated from that canonical schema into each SQL dialect.

DDL by warehouse

Snowflake

Covers email, SMS, push, and mobile SDK events. The custom_properties column is optional — include it only if you want the raw event payload stored alongside the parsed columns.

-----------------------------------
-- EMAIL EVENT TABLES
-----------------------------------
CREATE TABLE messagegears_bounce (
  account_id number(11,0) DEFAULT NULL,
  request_id varchar(50) NOT NULL,
  activity_id varchar(36) NOT NULL,
  activity_timestamp timestamp_tz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  correlation_id varchar(255) DEFAULT NULL,
  job_category varchar(255) DEFAULT NULL,
  campaign_id number(11,0) DEFAULT NULL,
  campaign_version number(11,0) DEFAULT NULL,
  email_address varchar(255) DEFAULT NULL,
  recipient_id varchar(255) DEFAULT NULL,
  local_ip varchar(255) DEFAULT NULL,
  remote_ip varchar(255) DEFAULT NULL,
  category varchar(255) DEFAULT NULL,
  category_code number(11,0) DEFAULT NULL,
  details string DEFAULT NULL,
  custom_properties variant DEFAULT NULL,
  PRIMARY KEY (activity_id)
);


CREATE TABLE messagegears_click (
  account_id number(11,0) DEFAULT NULL,
  request_id varchar(50) NOT NULL,
  activity_id varchar(36) NOT NULL,
  activity_timestamp timestamp_tz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  correlation_id varchar(255) DEFAULT NULL,
  job_category varchar(255) DEFAULT NULL,
  campaign_id number(11,0) DEFAULT NULL,
  campaign_version number(11,0) DEFAULT NULL,
  email_address varchar(255) DEFAULT NULL,
  recipient_id varchar(255) DEFAULT NULL,
  ip_address varchar(255) DEFAULT NULL,
  user_agent varchar(510) DEFAULT NULL,
  microsite boolean DEFAULT FALSE,
  url string DEFAULT NULL,
  url_name varchar(510) DEFAULT NULL,
  url_id varchar(510) DEFAULT NULL,
  custom_properties variant DEFAULT NULL,
  PRIMARY KEY (activity_id)
);


CREATE TABLE messagegears_delivery (
  account_id number(11,0) DEFAULT NULL,
  request_id varchar(50) NOT NULL,
  activity_id varchar(36) NOT NULL,
  activity_timestamp timestamp_tz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  correlation_id varchar(255) DEFAULT NULL,
  job_category varchar(255) DEFAULT NULL,
  campaign_id number(11,0) DEFAULT NULL,
  campaign_version number(11,0) DEFAULT NULL,
  email_address varchar(255) DEFAULT NULL,
  recipient_id varchar(255) DEFAULT NULL,
  local_ip varchar(255) DEFAULT NULL,
  remote_ip varchar(255) DEFAULT NULL,
  message_size number(11,0) DEFAULT NULL,
  custom_properties variant DEFAULT NULL,
  PRIMARY KEY (activity_id)
);

-- ADJUSTED OPENS
CREATE TABLE messagegears_open (
  account_id number(11,0) DEFAULT NULL,
  request_id varchar(50) NOT NULL,
  activity_id varchar(36) NOT NULL,
  activity_timestamp timestamp_tz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  correlation_id varchar(255) DEFAULT NULL,
  job_category varchar(255) DEFAULT NULL,
  campaign_id number(11,0) DEFAULT NULL,
  campaign_version number(11,0) DEFAULT NULL,
  email_address varchar(255) DEFAULT NULL,
  recipient_id varchar(255) DEFAULT NULL,
  ip_address varchar(255) DEFAULT NULL,
  user_agent varchar(510) DEFAULT NULL,
  microsite boolean DEFAULT FALSE,
  adjusted_open boolean DEFAULT FALSE,
  custom_properties variant DEFAULT NULL,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_spamcomplaint (
  account_id number(11,0) DEFAULT NULL,
  request_id varchar(50) NOT NULL,
  activity_id varchar(36) NOT NULL,
  activity_timestamp timestamp_tz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  correlation_id varchar(255) DEFAULT NULL,
  job_category varchar(255) DEFAULT NULL,
  campaign_id number(11,0) DEFAULT NULL,
  campaign_version number(11,0) DEFAULT NULL,
  email_address varchar(255) DEFAULT NULL,
  recipient_id varchar(255) DEFAULT NULL,
  isp varchar(255) DEFAULT NULL,
  ip_address varchar(30) DEFAULT NULL,
  subject varchar(510) DEFAULT NULL,
  custom_properties variant DEFAULT NULL,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_unsubscribe (
  account_id number(11,0) DEFAULT NULL,
  request_id varchar(50) NOT NULL,
  activity_id varchar(36) NOT NULL,
  activity_timestamp timestamp_tz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  correlation_id varchar(255) DEFAULT NULL,
  job_category varchar(255) DEFAULT NULL,
  campaign_id number(11,0) DEFAULT NULL,
  campaign_version number(11,0) DEFAULT NULL,
  email_address varchar(255) DEFAULT NULL,
  recipient_id varchar(255) DEFAULT NULL,
  ip_address varchar(255) DEFAULT NULL,
  user_agent varchar(510) DEFAULT NULL,
  microsite boolean DEFAULT FALSE,
  custom_properties variant DEFAULT NULL,
  PRIMARY KEY (activity_id)
);

-- Note: No Primary Key on Render Errors
CREATE TABLE messagegears_render_error (
  account_id number(11,0) DEFAULT NULL,
  request_id varchar(50) NOT NULL,
  activity_id varchar(36) NOT NULL,
  activity_timestamp timestamp_tz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  correlation_id varchar(255) DEFAULT NULL,
  job_category varchar(255) DEFAULT NULL,
  campaign_id number(11,0) DEFAULT NULL,
  campaign_version number(11,0) DEFAULT NULL,
  email_address varchar(255) DEFAULT NULL,
  recipient_id varchar(255) DEFAULT NULL,
  error_code varchar(255) DEFAULT NULL,
  error_message string DEFAULT NULL,
  custom_properties variant DEFAULT NULL,
  PRIMARY KEY (activity_id)
);

-----------------------------------
-- SMS EVENT TABLES
-----------------------------------

CREATE TABLE messagegears_sms_delivery (
  account_id number(11,0) DEFAULT NULL,
  activity_timestamp timestamp_tz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  request_id varchar(50) NOT NULL,
  correlation_id varchar(255) DEFAULT NULL,
  job_category varchar(255) DEFAULT NULL,
  campaign_id number(11,0) DEFAULT NULL,
  campaign_version number(11,0) DEFAULT NULL,
  activity_id varchar(36) NOT NULL,
  sms_address varchar(255) DEFAULT NULL,
  recipient_id varchar(255) DEFAULT NULL,
  origination_number varchar(255) DEFAULT NULL,
  sms_name varchar(255) DEFAULT NULL,
  custom_properties variant DEFAULT NULL,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_sms_click (
  account_id number(11,0) DEFAULT NULL,
  activity_timestamp timestamp_tz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  request_id varchar(50) NOT NULL,
  correlation_id varchar(255) DEFAULT NULL,
  job_category varchar(255) DEFAULT NULL,
  campaign_id number(11,0) DEFAULT NULL,
  campaign_version number(11,0) DEFAULT NULL,
  activity_id varchar(36) NOT NULL,
  sms_address varchar(255) DEFAULT NULL,
  recipient_id varchar(255) DEFAULT NULL,
  origination_number varchar(255) DEFAULT NULL,
  sms_name varchar(255) DEFAULT NULL,
  url string DEFAULT NULL,
  url_name varchar(510) DEFAULT NULL,
  url_id varchar(510) DEFAULT NULL,
  vendor_id number(11,0) DEFAULT NULL,
  ip_address varchar(255) DEFAULT NULL,
  user_agent varchar(510) DEFAULT NULL
);

CREATE TABLE messagegears_sms_delivery_failure (
  account_id number(11,0) DEFAULT NULL,
  activity_timestamp timestamp_tz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  request_id varchar(50) NOT NULL,
  correlation_id varchar(255) DEFAULT NULL,
  job_category varchar(255) DEFAULT NULL,
  campaign_id number(11,0) DEFAULT NULL,
  campaign_version number(11,0) DEFAULT NULL,
  activity_id varchar(36) NOT NULL,
  sms_address varchar(255) DEFAULT NULL,
  recipient_id varchar(255) DEFAULT NULL,
  category varchar(255) DEFAULT NULL,
  details varchar DEFAULT NULL,
  origination_number varchar(255) DEFAULT NULL,
  sms_name varchar(255) DEFAULT NULL,
  custom_properties variant DEFAULT NULL,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_sms_render_error (
  account_id number(11,0) DEFAULT NULL,
  activity_timestamp timestamp_tz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  request_id varchar(50) NOT NULL,
  correlation_id varchar(255) DEFAULT NULL,
  job_category varchar(255) DEFAULT NULL,
  campaign_id number(11,0) DEFAULT NULL,
  campaign_version number(11,0) DEFAULT NULL,
  activity_id varchar(36) NOT NULL,
  sms_address varchar(255) DEFAULT NULL,
  recipient_id varchar(255) DEFAULT NULL,
  error_code varchar(255) DEFAULT NULL,
  error_message varchar DEFAULT NULL,
  origination_number varchar(255) DEFAULT NULL,
  sms_name varchar(255) DEFAULT NULL,
  custom_properties variant DEFAULT NULL,
  PRIMARY KEY (activity_id)
);

-- if recieving additional events (client reference events) from SMS provider, use this table structure:
CREATE TABLE messagegears_sms_inbound (
  activity_timestamp timestamp_tz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  activity_id varchar(36) NOT NULL,
  sms_address varchar(255) DEFAULT NULL,
  to_address varchar(255) DEFAULT NULL,
  message varchar DEFAULT NULL,
  message_type varchar(36) NOT NULL,
  origination_number varchar(255) DEFAULT NULL,
  sms_name varchar(255) DEFAULT NULL,
  jobCategory varchar(255) DEFAULT NULL,
  campaignId number(11,0) DEFAULT NULL,
  campaignVersion number(11,0) DEFAULT NULL,
  accountId number(11,0) DEFAULT NULL,
  requestId varchar(50) NOT NULL,
  correlationId varchar(255) DEFAULT NULL,
  clientCampaignId varchar(255) DEFAULT NULL,
  recipientId varchar(255) DEFAULT NULL,
  custom_properties variant DEFAULT NULL,
  PRIMARY KEY (activity_id)
);

-----------------------------------
-- PUSH EVENT TABLES
-----------------------------------

-- If using sdk and "Receive Swrve Push Sent Events" turned on in Admin by MG CS team
CREATE TABLE messagegears_push_delivery (
  account_id number(11,0) DEFAULT NULL,
  activity_id varchar(36) NOT NULL,
  request_id varchar(50) NOT NULL,
  activity_timestamp timestamp_tz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  correlation_id varchar(255) DEFAULT NULL,
  job_category varchar(255) DEFAULT NULL,
  campaign_id number(11,0) DEFAULT NULL,
  campaign_version number(11,0) DEFAULT NULL,
  device_id varchar(255) DEFAULT NULL,
  service varchar(255) DEFAULT NULL,
  application_id varchar(255) DEFAULT NULL,
  recipient_id varchar(255) DEFAULT NULL,
  push_delivery_service varchar(255) DEFAULT NULL,
  mobile_user_id varchar (255) DEFAULT NULL,
  custom_properties variant DEFAULT NULL,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_push_click (
    activity_timestamp TIMESTAMP_TZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    request_id varchar(50) DEFAULT NULL,
    correlation_id VARCHAR(255) DEFAULT NULL,
    activity_id varchar(36) DEFAULT NULL,
    device_id VARCHAR(255) DEFAULT NULL,
    service VARCHAR(255) DEFAULT NULL,
    application_id VARCHAR(255) DEFAULT NULL,
    push_delivery_service VARCHAR(255) DEFAULT NULL,
    account_id number(11,0) DEFAULT NULL,
    job_category VARCHAR(255) DEFAULT NULL,
    campaign_id number(11,0) DEFAULT NULL,
    campaign_version number(11,0) DEFAULT NULL,
    recipient_id VARCHAR(255) DEFAULT NULL,
    ip_address VARCHAR(255) DEFAULT NULL,
    user_agent VARCHAR(255) DEFAULT NULL,
    destination VARCHAR(255) DEFAULT NULL,
    url_name VARCHAR(255) DEFAULT NULL,
    url_id VARCHAR(255) DEFAULT NULL,
    mobile_user_id varchar (255) DEFAULT NULL,
    vendor_app_id varchar(50) DEFAULT NULL,
    custom_properties variant DEFAULT NULL,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_push_delivery_failure (
  account_id number(11,0) DEFAULT NULL,
  activity_id varchar(36) NOT NULL,
  request_id varchar(50) NOT NULL,
  activity_timestamp timestamp_tz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  correlation_id varchar(255) DEFAULT NULL,
  job_category varchar(255) DEFAULT NULL,
  campaign_id number(11,0) DEFAULT NULL,
  campaign_version number(11,0) DEFAULT NULL,
  device_id varchar(255) DEFAULT NULL,
  service varchar(255) DEFAULT NULL,
  application_id varchar(255) DEFAULT NULL,
  recipient_id varchar(255) DEFAULT NULL,
  category varchar(255) DEFAULT NULL,
  details varchar DEFAULT NULL,
  push_delivery_service varchar(255) DEFAULT NULL,
  mobile_user_id varchar (255) DEFAULT NULL,
  custom_properties variant DEFAULT NULL,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_push_render_error (
  account_id number(11,0) DEFAULT NULL,
  activity_id varchar(36) NOT NULL,
  request_id varchar(50) NOT NULL,
  activity_timestamp timestamp_tz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  correlation_id varchar(255) DEFAULT NULL,
  job_category varchar(255) DEFAULT NULL,
  campaign_id number(11,0) DEFAULT NULL,
  campaign_version number(11,0) DEFAULT NULL,
  device_id varchar(255) DEFAULT NULL,
  service varchar(255) DEFAULT NULL,
  application_id varchar(255) DEFAULT NULL,
  recipient_id varchar(255) DEFAULT NULL,
  error_code varchar(255) DEFAULT NULL,
  error_message varchar DEFAULT NULL,
  custom_properties variant DEFAULT NULL,
  PRIMARY KEY (activity_id)
);

----------------------------------------------------
--SDK CORE MOBILE EVENT TABLES
----------------------------------------------------

-- SDK mobile user tables
CREATE TABLE messagegears_mobile_user_session(
  activity_id varchar(255) DEFAULT NULL,
  application_id varchar(255) DEFAULT NULL,
  device_id varchar(255) DEFAULT NULL,
  mobile_app_id varchar(255) DEFAULT NULL,
  mobile_user_id varchar(255) DEFAULT NULL,
  recipient_id varchar(255) DEFAULT NULL,
  activity_timestamp timestamp_tz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  user_type varchar(50) DEFAULT NULL,
  custom_properties variant DEFAULT NULL,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_mobile_user_first_session(
  activity_id varchar(255) DEFAULT NULL,
  application_id varchar(255) DEFAULT NULL,
  device_id varchar(255) DEFAULT NULL,
  mobile_app_id varchar(255) DEFAULT NULL,
  mobile_user_id varchar(255) DEFAULT NULL,
  recipient_id varchar(255) DEFAULT NULL,
  activity_timestamp timestamp_tz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  user_type varchar(50) DEFAULT NULL,
  custom_properties variant DEFAULT NULL,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_mobile_user_identity_update(
  activity_id varchar(255) DEFAULT NULL,
  application_id varchar(255) DEFAULT NULL,
  device_id varchar(255) DEFAULT NULL,
  mobile_app_id varchar(255) DEFAULT NULL,
  mobile_user_id varchar(255) DEFAULT NULL,
  recipient_id varchar(255) DEFAULT NULL,
  activity_timestamp timestamp_tz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  user_type varchar(50) DEFAULT NULL,
  custom_properties variant DEFAULT NULL,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_mobile_user_push_permission_update(
  activity_id varchar(255) DEFAULT NULL,
  application_id varchar(50) DEFAULT NULL,
  device_id varchar(255) DEFAULT NULL,
  mobile_app_id varchar(255) DEFAULT NULL,
  mobile_user_id varchar(255) DEFAULT NULL,
  pushPermission varchar (50) DEFAULT NULL,
  recipient_id varchar(255) DEFAULT NULL,
  activity_timestamp timestamp_tz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  user_type varchar(50) DEFAULT NULL,
  custom_properties variant DEFAULT NULL,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_mobile_user_device_update(
    activity_id varchar(255) DEFAULT NULL,
    application_id varchar(255) DEFAULT NULL,
    device_id varchar(255) DEFAULT NULL,
    mobile_app_id varchar(255) DEFAULT NULL,
    mobile_user_id varchar(255) DEFAULT NULL,
    recipient_id varchar(255) DEFAULT NULL,
    activity_timestamp timestamp_tz NOT NULL DEFAULT CURRENT_TIMESTAMP,
    user_type varchar(50) DEFAULT NULL,
    device_type varchar(255) DEFAULT NULL,
    device_name varchar(255) DEFAULT NULL,
    region varchar(50) DEFAULT NULL,
    language varchar(50) DEFAULT NULL,
    platform varchar(50) DEFAULT NULL,
    platform_version varchar(50) DEFAULT NULL,
    device_timezone_name varchar(100) DEFAULT NULL,
    mobile_sdk_version varchar(50) DEFAULT NULL,
    app_store varchar(50) DEFAULT NULL,
    install_date timestamp_tz NOT NULL DEFAULT CURRENT_TIMESTAMP,
    utc_offset varchar(50) DEFAULT NULL,
    ios_push_permission varchar(50) DEFAULT NULL,
    background_push_permissions varchar(50) DEFAULT NULL,
    camera_permission varchar(50) DEFAULT NULL,
    contacts_permission varchar(50) DEFAULT NULL,
    photos_permission varchar(50) DEFAULT NULL,
    location_always_permission varchar(50) DEFAULT NULL,
    location_in_use_permission varchar(50) DEFAULT NULL,
    location_accuracy_permission varchar(50) DEFAULT NULL,
    ios_push_token varchar(50) DEFAULT NULL,
    support_rich_gif boolean DEFAULT NULL,
    mobile_sdk_flavor varchar(50) DEFAULT NULL,
    push_permission_enabled boolean DEFAULT NULL,
    push_importance varchar(50) DEFAULT NULL,
    android_push_permission varchar(50) DEFAULT NULL,
    permission_show_rationale boolean DEFAULT NULL,
    permission_answered_times integer DEFAULT NULL,
    gcm_push_token varchar(50) DEFAULT NULL,
    location_service_enabled boolean DEFAULT NULL,
    custom_properties variant DEFAULT NULL,
  PRIMARY KEY (activity_id)
);

-- SDK mobile user custom events
CREATE TABLE messagegears_mobile_user_custom_event(
  activity_id varchar(255) DEFAULT NULL,
  event_name varchar(255) DEFAULT NULL,
  application_id varchar(50) DEFAULT NULL,
  device_id varchar(255) DEFAULT NULL,
  mobile_app_id varchar(255) DEFAULT NULL,
  mobile_user_id varchar(255) DEFAULT NULL,
  recipient_id varchar(255) DEFAULT NULL,
  activity_timestamp timestamp_tz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  user_type varchar(50) DEFAULT NULL,
  payload variant DEFAULT NULL,
  custom_properties variant DEFAULT NULL,
  PRIMARY KEY (activity_id)
);

-- SDK mobile user property update events
CREATE TABLE messagegears_mobile_user_property_update(
  activity_id varchar(255) DEFAULT NULL,
  application_id varchar(50) DEFAULT NULL,
  device_id varchar(255) DEFAULT NULL,
  mobile_app_id varchar(255) DEFAULT NULL,
  mobile_user_id varchar(255) DEFAULT NULL,
  recipient_id varchar(255) DEFAULT NULL,
  activity_timestamp timestamp_tz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  user_type varchar(50) DEFAULT NULL,
  attributes variant DEFAULT NULL,
  custom_properties variant DEFAULT NULL,
  PRIMARY KEY (activity_id)
);

-- SDK In-App Event Tables
CREATE TABLE messagegears_mobile_in_app_impression(
    activity_timestamp TIMESTAMP_TZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    activity_id VARCHAR(255) DEFAULT NULL,
    in_app_campaign_id VARCHAR(255) DEFAULT NULL,
    in_app_tracking_id VARCHAR(255) DEFAULT NULL,
    client_campaign_id VARCHAR(255) DEFAULT NULL,
    campaign_uuid VARCHAR(255) DEFAULT NULL,
    device_id VARCHAR(255) DEFAULT NULL,
    application_id VARCHAR(255) DEFAULT NULL,
    vendor_app_id VARCHAR(255) DEFAULT NULL,
    recipient_id VARCHAR(255) DEFAULT NULL,
    platform VARCHAR(255) DEFAULT NULL,
    device_type VARCHAR(255) DEFAULT NULL,
    mobile_user_id VARCHAR(255) DEFAULT NULL,
    category VARCHAR(255) DEFAULT NULL,
    custom_properties variant DEFAULT NULL,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_mobile_in_app_page_view (
    activity_timestamp TIMESTAMP_TZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    activity_id VARCHAR(255) DEFAULT NULL,
    in_app_campaign_id VARCHAR(255) DEFAULT NULL,
    in_app_tracking_id VARCHAR(255) DEFAULT NULL,
    client_campaign_id VARCHAR(255) DEFAULT NULL,
    campaign_uuid VARCHAR(255) DEFAULT NULL,
    device_id VARCHAR(255) DEFAULT NULL,
    application_id VARCHAR(255) DEFAULT NULL,
    vendor_app_id VARCHAR(255) DEFAULT NULL,
    recipient_id VARCHAR(255) DEFAULT NULL,
    platform VARCHAR(255) DEFAULT NULL,
    device_type VARCHAR(255) DEFAULT NULL,
    mobile_user_id VARCHAR(255) DEFAULT NULL,
    page_id VARCHAR(255) DEFAULT NULL,
    page_name VARCHAR(255) DEFAULT NULL,
    category VARCHAR(255) DEFAULT NULL,
    custom_properties variant DEFAULT NULL,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_mobile_in_app_click (
    activity_timestamp TIMESTAMP_TZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    activity_id VARCHAR(255) DEFAULT NULL,
    in_app_campaign_id VARCHAR(255) DEFAULT NULL,
    in_app_tracking_id VARCHAR(255) DEFAULT NULL,
    client_campaign_id VARCHAR(255) DEFAULT NULL,
    campaign_uuid VARCHAR(255) DEFAULT NULL,
    device_id VARCHAR(255) DEFAULT NULL,
    application_id VARCHAR(255) DEFAULT NULL,
    vendor_app_id VARCHAR(255) DEFAULT NULL,
    recipient_id VARCHAR(255) DEFAULT NULL,
    platform VARCHAR(255) DEFAULT NULL,
    device_type VARCHAR(255) DEFAULT NULL,
    mobile_user_id VARCHAR(255) DEFAULT NULL,
    page_id VARCHAR(255) DEFAULT NULL,
    page_name VARCHAR(255) DEFAULT NULL,
    button_id VARCHAR(255) DEFAULT NULL,
    button_name VARCHAR(255) DEFAULT NULL,
    category VARCHAR(255) DEFAULT NULL,
    custom_properties variant DEFAULT NULL,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_mobile_in_app_dismissed (
    activity_timestamp TIMESTAMP_TZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    activity_id VARCHAR(255) DEFAULT NULL,
    in_app_campaign_id VARCHAR(255) DEFAULT NULL,
    in_app_tracking_id VARCHAR(255) DEFAULT NULL,
    client_campaign_id VARCHAR(255) DEFAULT NULL,
    campaign_uuid VARCHAR(255) DEFAULT NULL,
    device_id VARCHAR(255) DEFAULT NULL,
    application_id VARCHAR(255) DEFAULT NULL,
    vendor_app_id VARCHAR(255) DEFAULT NULL,
    recipient_id VARCHAR(255) DEFAULT NULL,
    platform VARCHAR(255) DEFAULT NULL,
    device_type VARCHAR(255) DEFAULT NULL,
    mobile_user_id VARCHAR(255) DEFAULT NULL,
    page_id VARCHAR(255) DEFAULT NULL,
    page_name VARCHAR(255) DEFAULT NULL,
    button_id VARCHAR(255) DEFAULT NULL,
    button_name VARCHAR(255) DEFAULT NULL,
    category VARCHAR(255) DEFAULT NULL,
    custom_properties variant DEFAULT NULL,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_mobile_in_app_page_navigation (
    activity_timestamp TIMESTAMP_TZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    activity_id VARCHAR(255) DEFAULT NULL,
    in_app_campaign_id VARCHAR(255) DEFAULT NULL,
    in_app_tracking_id VARCHAR(255) DEFAULT NULL,
    client_campaign_id VARCHAR(255) DEFAULT NULL,
    campaign_uuid VARCHAR(255) DEFAULT NULL,
    device_id VARCHAR(255) DEFAULT NULL,
    application_id VARCHAR(255) DEFAULT NULL,
    vendor_app_id VARCHAR(255) DEFAULT NULL,
    recipient_id VARCHAR(255) DEFAULT NULL,
    platform VARCHAR(255) DEFAULT NULL,
    device_type VARCHAR(255) DEFAULT NULL,
    mobile_user_id VARCHAR(255) DEFAULT NULL,
    page_id VARCHAR(255) DEFAULT NULL,
    page_name VARCHAR(255) DEFAULT NULL,
    button_id VARCHAR(255) DEFAULT NULL,
    button_name VARCHAR(255) DEFAULT NULL,
    category VARCHAR(255) DEFAULT NULL,
    custom_properties variant DEFAULT NULL,
  PRIMARY KEY (activity_id)
);


----------------------------------------------------------------------
-- CAMPAIGN METADATA AND SEND HISTORY TABLES
----------------------------------------------------------------------
-- example/suggestion only, we recommend coordinating with your marketing team on what's needed here
CREATE TABLE messagegears_campaign_metadata (
  job_id number(11,0) DEFAULT NULL,
  request_id varchar(50) NOT NULL,
  account_id number(11,0) DEFAULT NULL,
  start_time timestamp_tz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  campaign_id number(11,0) DEFAULT NULL,
  campaign_name varchar(255) DEFAULT NULL,
  job_category varchar(255) DEFAULT NULL,
  template_id number(11,0) DEFAULT NULL,
  template_name varchar(255) DEFAULT NULL,
  PRIMARY KEY (job_id)
);

-- example/suggestion only, we recommend coordinating with your marketing team on what's needed here
CREATE TABLE messagegears_send_history (
  job_id number(11,0) DEFAULT NULL,
  recipient_id varchar(255) DEFAULT NULL,
  campaign_id number(11,0) DEFAULT NULL,
  job_category varchar(255) DEFAULT NULL,
  send_time timestamp_tz NOT NULL DEFAULT CURRENT_TIMESTAMP
);

BigQuery

Covers email, SMS, push, and mobile SDK events. Replace <dataset_name> with the dataset that will hold the tables. The commented-out messagegears_open block is the alternative to use if you are not tracking adjusted opens.

-----------------------------------
-- EMAIL EVENT TABLES
-----------------------------------
CREATE TABLE <dataset_name>.messagegears_bounce
(
  account_id           INT64,
  request_id           STRING NOT NULL,
  activity_id          STRING NOT NULL,
  activity_timestamp   TIMESTAMP NOT NULL,
  correlation_id       STRING,
  job_category         STRING,
  campaign_id          INT64,
  campaign_version     INT64,
  email_address        STRING,
  recipient_id         STRING,
  local_ip             STRING,
  remote_ip            STRING,
  category             STRING,
  category_code        INT64,
  details              STRING,
  custom_properties    JSON
);

CREATE TABLE <dataset_name>.messagegears_click
(
  account_id           INT64,
  request_id           STRING NOT NULL,
  activity_id          STRING NOT NULL,
  activity_timestamp   TIMESTAMP NOT NULL,
  correlation_id       STRING,
  job_category         STRING,
  campaign_id          INT64,
  campaign_version     INT64,
  email_address        STRING,
  recipient_id         STRING,
  ip_address           STRING,
  user_agent           STRING,
  microsite            BOOL,
  url                  STRING,
  url_name             STRING,
  url_id               STRING,
  custom_properties    JSON
);

CREATE TABLE <dataset_name>.messagegears_delivery
(
  account_id           INT64,
  request_id           STRING NOT NULL,
  activity_id          STRING NOT NULL,
  activity_timestamp   TIMESTAMP NOT NULL,
  correlation_id       STRING,
  job_category         STRING,
  campaign_id          INT64,
  campaign_version     INT64,
  email_address        STRING,
  recipient_id         STRING,
  local_ip             STRING,
  remote_ip            STRING,
  message_size         INT64,
  custom_properties    JSON
);

-- ADJUSTED OPENS
CREATE TABLE <dataset_name>.messagegears_open
(
  account_id           INT64,
  request_id           STRING NOT NULL,
  activity_id          STRING NOT NULL,
  activity_timestamp   TIMESTAMP NOT NULL,
  correlation_id       STRING,
  job_category         STRING,
  campaign_id          INT64,
  campaign_version     INT64,
  email_address        STRING,
  recipient_id         STRING,
  ip_address           STRING,
  user_agent           STRING,
  microsite            BOOL,
  adjusted_open        BOOL,
  custom_properties    JSON
);

-- USE THIS BLOCK IF ADJUSTED OPENS ARE NOT BEING USED
/*
CREATE TABLE <dataset_name>.messagegears_open
(
  account_id           INT64,
  request_id           STRING NOT NULL,
  activity_id          STRING NOT NULL,
  activity_timestamp   TIMESTAMP NOT NULL,
  correlation_id       STRING,
  job_category         STRING,
  campaign_id          INT64,
  campaign_version     INT64,
  email_address        STRING,
  recipient_id         STRING,
  ip_address           STRING,
  user_agent           STRING,
  microsite            BOOL,
  custom_properties    JSON
);
*/

CREATE TABLE <dataset_name>.messagegears_spamcomplaint
(
  account_id           INT64,
  request_id           STRING NOT NULL,
  activity_id          STRING NOT NULL,
  activity_timestamp   TIMESTAMP NOT NULL,
  correlation_id       STRING,
  job_category         STRING,
  campaign_id          INT64,
  campaign_version     INT64,
  email_address        STRING,
  recipient_id         STRING,
  isp                  STRING,
  ip_address           STRING,
  subject              STRING,
  custom_properties    JSON
);

CREATE TABLE <dataset_name>.messagegears_unsubscribe
(
  account_id           INT64,
  request_id           STRING NOT NULL,
  activity_id          STRING NOT NULL,
  activity_timestamp   TIMESTAMP NOT NULL,
  correlation_id       STRING,
  job_category         STRING,
  campaign_id          INT64,
  campaign_version     INT64,
  email_address        STRING,
  recipient_id         STRING,
  ip_address           STRING,
  user_agent           STRING,
  microsite            BOOL,
  custom_properties    JSON
);

-- Note: No Primary Key on Render Errors
CREATE TABLE <dataset_name>.messagegears_render_error
(
  account_id           INT64,
  request_id           STRING NOT NULL,
  activity_id          STRING NOT NULL,
  activity_timestamp   TIMESTAMP NOT NULL,
  correlation_id       STRING,
  job_category         STRING,
  campaign_id          INT64,
  campaign_version     INT64,
  email_address        STRING,
  recipient_id         STRING,
  error_code           STRING,
  error_message        STRING,
  custom_properties    JSON
);

-----------------------------------
-- SMS EVENT TABLES
-----------------------------------

CREATE TABLE <dataset_name>.messagegears_sms_delivery
(
  account_id            INT64,
  activity_timestamp    TIMESTAMP NOT NULL,
  request_id            STRING NOT NULL,
  correlation_id        STRING,
  job_category          STRING,
  campaign_id           INT64,
  campaign_version      INT64,
  activity_id           STRING NOT NULL,
  sms_address           STRING,
  recipient_id          STRING,
  origination_number    STRING,
  sms_name              STRING,
  custom_properties     JSON
);

CREATE TABLE <dataset_name>.messagegears_sms_click
(
  account_id            INT64,
  activity_timestamp    TIMESTAMP NOT NULL,
  request_id            STRING NOT NULL,
  correlation_id        STRING,
  job_category          STRING,
  campaign_id           INT64,
  campaign_version      INT64,
  activity_id           STRING NOT NULL,
  sms_address           STRING,
  recipient_id          STRING,
  origination_number    STRING,
  sms_name              STRING,
  url                   STRING,
  url_name              STRING,
  url_id                STRING,
  vendor_id             INT64,
  ip_address            STRING,
  user_agent            STRING
);

CREATE TABLE <dataset_name>.messagegears_sms_delivery_failure
(
  account_id            INT64,
  activity_timestamp    TIMESTAMP NOT NULL,
  request_id            STRING NOT NULL,
  correlation_id        STRING,
  job_category          STRING,
  campaign_id           INT64,
  campaign_version      INT64,
  activity_id           STRING NOT NULL,
  sms_address           STRING,
  recipient_id          STRING,
  category              STRING,
  details               STRING,
  origination_number    STRING,
  sms_name              STRING,
  custom_properties     JSON
);

CREATE TABLE <dataset_name>.messagegears_sms_render_error
(
  account_id            INT64,
  activity_timestamp    TIMESTAMP NOT NULL,
  request_id            STRING NOT NULL,
  correlation_id        STRING,
  job_category          STRING,
  campaign_id           INT64,
  campaign_version      INT64,
  activity_id           STRING NOT NULL,
  sms_address           STRING,
  recipient_id          STRING,
  error_code            STRING,
  error_message         STRING,
  origination_number    STRING,
  sms_name              STRING,
  custom_properties     JSON
);


-- if receiving additional events (client reference events) from SMS provider, use this table structure:
CREATE TABLE <dataset_name>.messagegears_sms_inbound
(
  activity_timestamp    TIMESTAMP NOT NULL,
  activity_id           STRING NOT NULL,
  sms_address           STRING,
  to_address            STRING,
  message               STRING,
  message_type          STRING NOT NULL,
  origination_number    STRING,
  sms_name              STRING,
  job_category          STRING,
  campaign_id           INT64,
  campaign_version      INT64,
  account_id            INT64,
  request_id            STRING,
  correlation_id        STRING,
  client_campaign_id    STRING,
  recipient_id          STRING,
  custom_properties     JSON
);

-----------------------------------
-- PUSH EVENT TABLES
-----------------------------------

-- If using sdk and "Receive Swrve Push Sent Events" turned on in Admin by MG CS team
CREATE TABLE <dataset_name>.messagegears_push_delivery
(
  account_id            INT64,
  activity_id           STRING NOT NULL,
  request_id            STRING NOT NULL,
  activity_timestamp    TIMESTAMP NOT NULL,
  correlation_id        STRING,
  job_category          STRING,
  campaign_id           INT64,
  campaign_version      INT64,
  device_id             STRING,
  service               STRING,
  application_id        STRING,
  recipient_id          STRING,
  push_delivery_service STRING,
  mobile_user_id        STRING,
  custom_properties     JSON
);

CREATE TABLE <dataset_name>.messagegears_push_click
(
  activity_timestamp    TIMESTAMP NOT NULL,
  request_id            STRING,
  correlation_id        STRING,
  activity_id           STRING,
  device_id             STRING,
  service               STRING,
  application_id        STRING,
  push_delivery_service STRING,
  account_id            INT64,
  job_category          STRING,
  campaign_id           INT64,
  campaign_version      INT64,
  recipient_id          STRING,
  ip_address            STRING,
  user_agent            STRING,
  destination           STRING,
  url_name              STRING,
  url_id                STRING,
  mobile_user_id        STRING,
  vendor_app_id         STRING,
  custom_properties     JSON
);

CREATE TABLE <dataset_name>.messagegears_push_delivery_failure
(
  account_id            INT64,
  activity_id           STRING NOT NULL,
  request_id            STRING NOT NULL,
  activity_timestamp    TIMESTAMP NOT NULL,
  correlation_id        STRING,
  job_category          STRING,
  campaign_id           INT64,
  campaign_version      INT64,
  device_id             STRING,
  service               STRING,
  application_id        STRING,
  recipient_id          STRING,
  category              STRING,
  details               STRING,
  push_delivery_service STRING,
  mobile_user_id        STRING,
  custom_properties     JSON
);

CREATE TABLE <dataset_name>.messagegears_push_render_error
(
  account_id            INT64,
  activity_id           STRING NOT NULL,
  request_id            STRING NOT NULL,
  activity_timestamp    TIMESTAMP NOT NULL,
  correlation_id        STRING,
  job_category          STRING,
  campaign_id           INT64,
  campaign_version      INT64,
  device_id             STRING,
  service               STRING,
  application_id        STRING,
  recipient_id          STRING,
  error_code            STRING,
  error_message         STRING,
  mobile_user_id        STRING,
  custom_properties     JSON
);

----------------------------------------------------
--SDK CORE MOBILE EVENT TABLES
----------------------------------------------------

CREATE TABLE <dataset_name>.messagegears_mobile_user_session
(
  activity_id          STRING,
  application_id       STRING,
  device_id            STRING,
  mobile_app_id        STRING,
  mobile_user_id       STRING,
  recipient_id         STRING,
  activity_timestamp   TIMESTAMP NOT NULL,
  user_type            STRING,
  custom_properties    JSON
);


CREATE TABLE <dataset_name>.messagegears_mobile_user_first_session
(
  activity_id          STRING,
  application_id       STRING,
  device_id            STRING,
  mobile_app_id        STRING,
  mobile_user_id       STRING,
  recipient_id         STRING,
  activity_timestamp   TIMESTAMP NOT NULL,
  user_type            STRING,
  custom_properties    JSON
);


CREATE TABLE <dataset_name>.messagegears_mobile_user_identity_update
(
  activity_id          STRING,
  application_id       STRING,
  device_id            STRING,
  mobile_app_id        STRING,
  mobile_user_id       STRING,
  recipient_id         STRING,
  activity_timestamp   TIMESTAMP NOT NULL,
  user_type            STRING,
  custom_properties    JSON
);


CREATE TABLE <dataset_name>.messagegears_mobile_user_push_permission_update
(
  activity_id          STRING,
  application_id       STRING,
  device_id            STRING,
  mobile_app_id        STRING,
  mobile_user_id       STRING,
  push_permission      STRING,
  recipient_id         STRING,
  activity_timestamp   TIMESTAMP NOT NULL,
  user_type            STRING,
  custom_properties    JSON
);


CREATE TABLE <dataset_name>.messagegears_mobile_user_device_update
(
  activity_id                   STRING,
  application_id                STRING,
  device_id                     STRING,
  mobile_app_id                 STRING,
  mobile_user_id                STRING,
  recipient_id                  STRING,
  activity_timestamp            TIMESTAMP NOT NULL,
  user_type                     STRING,
  device_type                   STRING,
  device_name                   STRING,
  region                        STRING,
  language                      STRING,
  platform                      STRING,
  platform_version              STRING,
  device_timezone_name          STRING,
  mobile_sdk_version            STRING,
  app_store                     STRING,
  install_date                  TIMESTAMP,
  utc_offset                    STRING,
  ios_push_permission           STRING,
  background_push_permissions   STRING,
  camera_permission             STRING,
  contacts_permission           STRING,
  photos_permission             STRING,
  location_always_permission    STRING,
  location_in_use_permission    STRING,
  location_accuracy_permission  STRING,
  ios_push_token                STRING,
  support_rich_gif              BOOL,
  mobile_sdk_flavor             STRING,
  push_permission_enabled       BOOL,
  push_importance               STRING,
  android_push_permission       STRING,
  permission_show_rationale     BOOL,
  permission_answered_times     INT64,
  gcm_push_token                STRING,
  location_service_enabled      BOOL,
  custom_properties             JSON
);


-- SDK custom user events
CREATE TABLE <dataset_name>.messagegears_mobile_user_custom_event
(
  activity_id          STRING,
  event_name           STRING,
  application_id       STRING,
  device_id            STRING,
  mobile_app_id        STRING,
  mobile_user_id       STRING,
  recipient_id         STRING,
  activity_timestamp   TIMESTAMP NOT NULL,
  user_type            STRING,
  payload              JSON,
  custom_properties    JSON
);


-- SDK user property events
CREATE TABLE <dataset_name>.messagegears_mobile_user_property_update
(
  activity_id          STRING,
  application_id       STRING,
  device_id            STRING,
  mobile_app_id        STRING,
  mobile_user_id       STRING,
  recipient_id         STRING,
  activity_timestamp   TIMESTAMP NOT NULL,
  user_type            STRING,
  attributes           JSON,
  custom_properties    JSON
);


CREATE TABLE <dataset_name>.messagegears_mobile_in_app_impression
(
  activity_timestamp     TIMESTAMP NOT NULL,
  activity_id            STRING,
  in_app_campaign_id     STRING,
  in_app_tracking_id     STRING,
  client_campaign_id     STRING,
  campaign_uuid          STRING,
  device_id              STRING,
  application_id         STRING,
  vendor_app_id          STRING,
  recipient_id           STRING,
  platform               STRING,
  device_type            STRING,
  mobile_user_id         STRING,
  category               STRING,
  custom_properties      JSON
);


CREATE TABLE <dataset_name>.messagegears_mobile_in_app_page_view
(
  activity_timestamp     TIMESTAMP NOT NULL,
  activity_id            STRING,
  in_app_campaign_id     STRING,
  in_app_tracking_id     STRING,
  client_campaign_id     STRING,
  campaign_uuid          STRING,
  device_id              STRING,
  application_id         STRING,
  vendor_app_id          STRING,
  recipient_id           STRING,
  platform               STRING,
  device_type            STRING,
  mobile_user_id         STRING,
  page_id                STRING,
  page_name              STRING,
  category               STRING,
  custom_properties      JSON
);


CREATE TABLE <dataset_name>.messagegears_mobile_in_app_click
(
  activity_timestamp     TIMESTAMP NOT NULL,
  activity_id            STRING,
  in_app_campaign_id     STRING,
  in_app_tracking_id     STRING,
  client_campaign_id     STRING,
  campaign_uuid          STRING,
  device_id              STRING,
  application_id         STRING,
  vendor_app_id          STRING,
  recipient_id           STRING,
  platform               STRING,
  device_type            STRING,
  mobile_user_id         STRING,
  page_id                STRING,
  page_name              STRING,
  button_id              STRING,
  button_name            STRING,
  category               STRING,
  custom_properties      JSON
);


CREATE TABLE <dataset_name>.messagegears_mobile_in_app_dismissed
(
  activity_timestamp     TIMESTAMP NOT NULL,
  activity_id            STRING,
  in_app_campaign_id     STRING,
  in_app_tracking_id     STRING,
  client_campaign_id     STRING,
  campaign_uuid          STRING,
  device_id              STRING,
  application_id         STRING,
  vendor_app_id          STRING,
  recipient_id           STRING,
  platform               STRING,
  device_type            STRING,
  mobile_user_id         STRING,
  page_id                STRING,
  page_name              STRING,
  button_id              STRING,
  button_name            STRING,
  category               STRING,
  custom_properties      JSON
);


CREATE TABLE <dataset_name>.messagegears_mobile_in_app_page_navigation
(
  activity_timestamp     TIMESTAMP NOT NULL,
  activity_id            STRING,
  in_app_campaign_id     STRING,
  in_app_tracking_id     STRING,
  client_campaign_id     STRING,
  campaign_uuid          STRING,
  device_id              STRING,
  application_id         STRING,
  vendor_app_id          STRING,
  recipient_id           STRING,
  platform               STRING,
  device_type            STRING,
  mobile_user_id         STRING,
  page_id                STRING,
  page_name              STRING,
  button_id              STRING,
  button_name            STRING,
  category               STRING,
  custom_properties      JSON
);

----------------------------------------------------------------------
-- CAMPAIGN METADATA AND SEND HISTORY TABLES
----------------------------------------------------------------------
-- example/suggestion only, we recommend coordinating with your marketing team on what's needed here
CREATE TABLE <dataset_name>.messagegears_campaign_metadata
(
  job_id         INT64 NOT NULL,
  request_id     STRING,
  account_id     INT64,
  start_time     TIMESTAMP NOT NULL,
  campaign_id    INT64,
  campaign_name  STRING,
  job_category   STRING,
  template_id    INT64,
  template_name  STRING
);


-- example/suggestion only, we recommend coordinating with your marketing team on what's needed here
CREATE TABLE <dataset_name>.messagegears_send_history
(
  job_id       INT64,
  recipient_id STRING,
  campaign_id  INT64,
  job_category STRING,
  send_time    TIMESTAMP NOT NULL
);

Redshift

Covers email, SMS, push, and mobile SDK events. These statements are translated from the canonical event feed schema to Redshift types (for example custom_properties becomes SUPER). Validate the type choices against your environment before running them in production.

-----------------------------------
-- EMAIL EVENT TABLES
-----------------------------------
CREATE TABLE messagegears_bounce (
  account_id INTEGER,
  request_id VARCHAR(50) NOT NULL,
  activity_id VARCHAR(36) NOT NULL,
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT GETDATE(),
  correlation_id VARCHAR(255),
  job_category VARCHAR(255),
  campaign_id INTEGER,
  campaign_version INTEGER,
  email_address VARCHAR(255),
  recipient_id VARCHAR(255),
  local_ip VARCHAR(255),
  remote_ip VARCHAR(255),
  category VARCHAR(255),
  category_code INTEGER,
  details VARCHAR(65535),
  custom_properties SUPER,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_click (
  account_id INTEGER,
  request_id VARCHAR(50) NOT NULL,
  activity_id VARCHAR(36) NOT NULL,
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT GETDATE(),
  correlation_id VARCHAR(255),
  job_category VARCHAR(255),
  campaign_id INTEGER,
  campaign_version INTEGER,
  email_address VARCHAR(255),
  recipient_id VARCHAR(255),
  ip_address VARCHAR(255),
  user_agent VARCHAR(510),
  microsite BOOLEAN DEFAULT false,
  url VARCHAR(65535),
  url_name VARCHAR(510),
  url_id VARCHAR(510),
  custom_properties SUPER,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_delivery (
  account_id INTEGER,
  request_id VARCHAR(50) NOT NULL,
  activity_id VARCHAR(36) NOT NULL,
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT GETDATE(),
  correlation_id VARCHAR(255),
  job_category VARCHAR(255),
  campaign_id INTEGER,
  campaign_version INTEGER,
  email_address VARCHAR(255),
  recipient_id VARCHAR(255),
  local_ip VARCHAR(255),
  remote_ip VARCHAR(255),
  message_size INTEGER,
  custom_properties SUPER,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_open (
  account_id INTEGER,
  request_id VARCHAR(50) NOT NULL,
  activity_id VARCHAR(36) NOT NULL,
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT GETDATE(),
  correlation_id VARCHAR(255),
  job_category VARCHAR(255),
  campaign_id INTEGER,
  campaign_version INTEGER,
  email_address VARCHAR(255),
  recipient_id VARCHAR(255),
  ip_address VARCHAR(255),
  user_agent VARCHAR(510),
  microsite BOOLEAN DEFAULT false,
  adjusted_open BOOLEAN DEFAULT false,
  custom_properties SUPER,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_spamcomplaint (
  account_id INTEGER,
  request_id VARCHAR(50) NOT NULL,
  activity_id VARCHAR(36) NOT NULL,
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT GETDATE(),
  correlation_id VARCHAR(255),
  job_category VARCHAR(255),
  campaign_id INTEGER,
  campaign_version INTEGER,
  email_address VARCHAR(255),
  recipient_id VARCHAR(255),
  isp VARCHAR(255),
  ip_address VARCHAR(30),
  subject VARCHAR(510),
  custom_properties SUPER,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_unsubscribe (
  account_id INTEGER,
  request_id VARCHAR(50) NOT NULL,
  activity_id VARCHAR(36) NOT NULL,
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT GETDATE(),
  correlation_id VARCHAR(255),
  job_category VARCHAR(255),
  campaign_id INTEGER,
  campaign_version INTEGER,
  email_address VARCHAR(255),
  recipient_id VARCHAR(255),
  ip_address VARCHAR(255),
  user_agent VARCHAR(510),
  microsite BOOLEAN DEFAULT false,
  custom_properties SUPER,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_render_error (
  account_id INTEGER,
  request_id VARCHAR(50) NOT NULL,
  activity_id VARCHAR(36) NOT NULL,
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT GETDATE(),
  correlation_id VARCHAR(255),
  job_category VARCHAR(255),
  campaign_id INTEGER,
  campaign_version INTEGER,
  email_address VARCHAR(255),
  recipient_id VARCHAR(255),
  error_code VARCHAR(255),
  error_message VARCHAR(65535),
  custom_properties SUPER,
  PRIMARY KEY (activity_id)
);

-----------------------------------
-- SMS EVENT TABLES
-----------------------------------

CREATE TABLE messagegears_sms_delivery (
  account_id INTEGER,
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT GETDATE(),
  request_id VARCHAR(50) NOT NULL,
  correlation_id VARCHAR(255),
  job_category VARCHAR(255),
  campaign_id INTEGER,
  campaign_version INTEGER,
  activity_id VARCHAR(36) NOT NULL,
  sms_address VARCHAR(255),
  recipient_id VARCHAR(255),
  origination_number VARCHAR(255),
  sms_name VARCHAR(255),
  custom_properties SUPER,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_sms_click (
  account_id INTEGER,
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT GETDATE(),
  request_id VARCHAR(50) NOT NULL,
  correlation_id VARCHAR(255),
  job_category VARCHAR(255),
  campaign_id INTEGER,
  campaign_version INTEGER,
  activity_id VARCHAR(36) NOT NULL,
  sms_address VARCHAR(255),
  recipient_id VARCHAR(255),
  origination_number VARCHAR(255),
  sms_name VARCHAR(255),
  url VARCHAR(65535),
  url_name VARCHAR(510),
  url_id VARCHAR(510),
  vendor_id INTEGER,
  ip_address VARCHAR(255),
  user_agent VARCHAR(510)
);

CREATE TABLE messagegears_sms_delivery_failure (
  account_id INTEGER,
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT GETDATE(),
  request_id VARCHAR(50) NOT NULL,
  correlation_id VARCHAR(255),
  job_category VARCHAR(255),
  campaign_id INTEGER,
  campaign_version INTEGER,
  activity_id VARCHAR(36) NOT NULL,
  sms_address VARCHAR(255),
  recipient_id VARCHAR(255),
  category VARCHAR(255),
  details VARCHAR(65535),
  origination_number VARCHAR(255),
  sms_name VARCHAR(255),
  custom_properties SUPER,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_sms_render_error (
  account_id INTEGER,
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT GETDATE(),
  request_id VARCHAR(50) NOT NULL,
  correlation_id VARCHAR(255),
  job_category VARCHAR(255),
  campaign_id INTEGER,
  campaign_version INTEGER,
  activity_id VARCHAR(36) NOT NULL,
  sms_address VARCHAR(255),
  recipient_id VARCHAR(255),
  error_code VARCHAR(255),
  error_message VARCHAR(65535),
  origination_number VARCHAR(255),
  sms_name VARCHAR(255),
  custom_properties SUPER,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_sms_inbound (
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT GETDATE(),
  activity_id VARCHAR(36) NOT NULL,
  sms_address VARCHAR(255),
  to_address VARCHAR(255),
  message VARCHAR(65535),
  message_type VARCHAR(36) NOT NULL,
  origination_number VARCHAR(255),
  sms_name VARCHAR(255),
  jobCategory VARCHAR(255),
  campaignId INTEGER,
  campaignVersion INTEGER,
  accountId INTEGER,
  requestId VARCHAR(50) NOT NULL,
  correlationId VARCHAR(255),
  clientCampaignId VARCHAR(255),
  recipientId VARCHAR(255),
  custom_properties SUPER,
  PRIMARY KEY (activity_id)
);

-----------------------------------
-- PUSH EVENT TABLES
-----------------------------------

CREATE TABLE messagegears_push_delivery (
  account_id INTEGER,
  activity_id VARCHAR(36) NOT NULL,
  request_id VARCHAR(50) NOT NULL,
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT GETDATE(),
  correlation_id VARCHAR(255),
  job_category VARCHAR(255),
  campaign_id INTEGER,
  campaign_version INTEGER,
  device_id VARCHAR(255),
  service VARCHAR(255),
  application_id VARCHAR(255),
  recipient_id VARCHAR(255),
  push_delivery_service VARCHAR(255),
  mobile_user_id VARCHAR(255),
  custom_properties SUPER,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_push_click (
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT GETDATE(),
  request_id VARCHAR(50),
  correlation_id VARCHAR(255),
  activity_id VARCHAR(36) NOT NULL,
  device_id VARCHAR(255),
  service VARCHAR(255),
  application_id VARCHAR(255),
  push_delivery_service VARCHAR(255),
  account_id INTEGER,
  job_category VARCHAR(255),
  campaign_id INTEGER,
  campaign_version INTEGER,
  recipient_id VARCHAR(255),
  ip_address VARCHAR(255),
  user_agent VARCHAR(255),
  destination VARCHAR(255),
  url_name VARCHAR(255),
  url_id VARCHAR(255),
  mobile_user_id VARCHAR(255),
  vendor_app_id VARCHAR(50),
  custom_properties SUPER,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_push_delivery_failure (
  account_id INTEGER,
  activity_id VARCHAR(36) NOT NULL,
  request_id VARCHAR(50) NOT NULL,
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT GETDATE(),
  correlation_id VARCHAR(255),
  job_category VARCHAR(255),
  campaign_id INTEGER,
  campaign_version INTEGER,
  device_id VARCHAR(255),
  service VARCHAR(255),
  application_id VARCHAR(255),
  recipient_id VARCHAR(255),
  category VARCHAR(255),
  details VARCHAR(65535),
  push_delivery_service VARCHAR(255),
  mobile_user_id VARCHAR(255),
  custom_properties SUPER,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_push_render_error (
  account_id INTEGER,
  activity_id VARCHAR(36) NOT NULL,
  request_id VARCHAR(50) NOT NULL,
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT GETDATE(),
  correlation_id VARCHAR(255),
  job_category VARCHAR(255),
  campaign_id INTEGER,
  campaign_version INTEGER,
  device_id VARCHAR(255),
  service VARCHAR(255),
  application_id VARCHAR(255),
  recipient_id VARCHAR(255),
  error_code VARCHAR(255),
  error_message VARCHAR(65535),
  custom_properties SUPER,
  PRIMARY KEY (activity_id)
);

----------------------------------------------------
--SDK CORE MOBILE EVENT TABLES
----------------------------------------------------

CREATE TABLE messagegears_mobile_user_session (
  activity_id VARCHAR(255) NOT NULL,
  application_id VARCHAR(255),
  device_id VARCHAR(255),
  mobile_app_id VARCHAR(255),
  mobile_user_id VARCHAR(255),
  recipient_id VARCHAR(255),
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT GETDATE(),
  user_type VARCHAR(50),
  custom_properties SUPER,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_mobile_user_first_session (
  activity_id VARCHAR(255) NOT NULL,
  application_id VARCHAR(255),
  device_id VARCHAR(255),
  mobile_app_id VARCHAR(255),
  mobile_user_id VARCHAR(255),
  recipient_id VARCHAR(255),
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT GETDATE(),
  user_type VARCHAR(50),
  custom_properties SUPER,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_mobile_user_identity_update (
  activity_id VARCHAR(255) NOT NULL,
  application_id VARCHAR(255),
  device_id VARCHAR(255),
  mobile_app_id VARCHAR(255),
  mobile_user_id VARCHAR(255),
  recipient_id VARCHAR(255),
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT GETDATE(),
  user_type VARCHAR(50),
  custom_properties SUPER,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_mobile_user_push_permission_update (
  activity_id VARCHAR(255) NOT NULL,
  application_id VARCHAR(50),
  device_id VARCHAR(255),
  mobile_app_id VARCHAR(255),
  mobile_user_id VARCHAR(255),
  pushPermission VARCHAR(50),
  recipient_id VARCHAR(255),
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT GETDATE(),
  user_type VARCHAR(50),
  custom_properties SUPER,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_mobile_user_device_update (
  activity_id VARCHAR(255) NOT NULL,
  application_id VARCHAR(255),
  device_id VARCHAR(255),
  mobile_app_id VARCHAR(255),
  mobile_user_id VARCHAR(255),
  recipient_id VARCHAR(255),
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT GETDATE(),
  user_type VARCHAR(50),
  device_type VARCHAR(255),
  device_name VARCHAR(255),
  region VARCHAR(50),
  language VARCHAR(50),
  platform VARCHAR(50),
  platform_version VARCHAR(50),
  device_timezone_name VARCHAR(100),
  mobile_sdk_version VARCHAR(50),
  app_store VARCHAR(50),
  install_date TIMESTAMPTZ NOT NULL DEFAULT GETDATE(),
  utc_offset VARCHAR(50),
  ios_push_permission VARCHAR(50),
  background_push_permissions VARCHAR(50),
  camera_permission VARCHAR(50),
  contacts_permission VARCHAR(50),
  photos_permission VARCHAR(50),
  location_always_permission VARCHAR(50),
  location_in_use_permission VARCHAR(50),
  location_accuracy_permission VARCHAR(50),
  ios_push_token VARCHAR(50),
  support_rich_gif BOOLEAN,
  mobile_sdk_flavor VARCHAR(50),
  push_permission_enabled BOOLEAN,
  push_importance VARCHAR(50),
  android_push_permission VARCHAR(50),
  permission_show_rationale BOOLEAN,
  permission_answered_times INTEGER,
  gcm_push_token VARCHAR(50),
  location_service_enabled BOOLEAN,
  custom_properties SUPER,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_mobile_user_custom_event (
  activity_id VARCHAR(255) NOT NULL,
  event_name VARCHAR(255),
  application_id VARCHAR(50),
  device_id VARCHAR(255),
  mobile_app_id VARCHAR(255),
  mobile_user_id VARCHAR(255),
  recipient_id VARCHAR(255),
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT GETDATE(),
  user_type VARCHAR(50),
  payload SUPER,
  custom_properties SUPER,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_mobile_user_property_update (
  activity_id VARCHAR(255) NOT NULL,
  application_id VARCHAR(50),
  device_id VARCHAR(255),
  mobile_app_id VARCHAR(255),
  mobile_user_id VARCHAR(255),
  recipient_id VARCHAR(255),
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT GETDATE(),
  user_type VARCHAR(50),
  attributes SUPER,
  custom_properties SUPER,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_mobile_in_app_impression (
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT GETDATE(),
  activity_id VARCHAR(255) NOT NULL,
  in_app_campaign_id VARCHAR(255),
  in_app_tracking_id VARCHAR(255),
  client_campaign_id VARCHAR(255),
  campaign_uuid VARCHAR(255),
  device_id VARCHAR(255),
  application_id VARCHAR(255),
  vendor_app_id VARCHAR(255),
  recipient_id VARCHAR(255),
  platform VARCHAR(255),
  device_type VARCHAR(255),
  mobile_user_id VARCHAR(255),
  category VARCHAR(255),
  custom_properties SUPER,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_mobile_in_app_page_view (
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT GETDATE(),
  activity_id VARCHAR(255) NOT NULL,
  in_app_campaign_id VARCHAR(255),
  in_app_tracking_id VARCHAR(255),
  client_campaign_id VARCHAR(255),
  campaign_uuid VARCHAR(255),
  device_id VARCHAR(255),
  application_id VARCHAR(255),
  vendor_app_id VARCHAR(255),
  recipient_id VARCHAR(255),
  platform VARCHAR(255),
  device_type VARCHAR(255),
  mobile_user_id VARCHAR(255),
  page_id VARCHAR(255),
  page_name VARCHAR(255),
  category VARCHAR(255),
  custom_properties SUPER,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_mobile_in_app_click (
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT GETDATE(),
  activity_id VARCHAR(255) NOT NULL,
  in_app_campaign_id VARCHAR(255),
  in_app_tracking_id VARCHAR(255),
  client_campaign_id VARCHAR(255),
  campaign_uuid VARCHAR(255),
  device_id VARCHAR(255),
  application_id VARCHAR(255),
  vendor_app_id VARCHAR(255),
  recipient_id VARCHAR(255),
  platform VARCHAR(255),
  device_type VARCHAR(255),
  mobile_user_id VARCHAR(255),
  page_id VARCHAR(255),
  page_name VARCHAR(255),
  button_id VARCHAR(255),
  button_name VARCHAR(255),
  category VARCHAR(255),
  custom_properties SUPER,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_mobile_in_app_dismissed (
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT GETDATE(),
  activity_id VARCHAR(255) NOT NULL,
  in_app_campaign_id VARCHAR(255),
  in_app_tracking_id VARCHAR(255),
  client_campaign_id VARCHAR(255),
  campaign_uuid VARCHAR(255),
  device_id VARCHAR(255),
  application_id VARCHAR(255),
  vendor_app_id VARCHAR(255),
  recipient_id VARCHAR(255),
  platform VARCHAR(255),
  device_type VARCHAR(255),
  mobile_user_id VARCHAR(255),
  page_id VARCHAR(255),
  page_name VARCHAR(255),
  button_id VARCHAR(255),
  button_name VARCHAR(255),
  category VARCHAR(255),
  custom_properties SUPER,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_mobile_in_app_page_navigation (
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT GETDATE(),
  activity_id VARCHAR(255) NOT NULL,
  in_app_campaign_id VARCHAR(255),
  in_app_tracking_id VARCHAR(255),
  client_campaign_id VARCHAR(255),
  campaign_uuid VARCHAR(255),
  device_id VARCHAR(255),
  application_id VARCHAR(255),
  vendor_app_id VARCHAR(255),
  recipient_id VARCHAR(255),
  platform VARCHAR(255),
  device_type VARCHAR(255),
  mobile_user_id VARCHAR(255),
  page_id VARCHAR(255),
  page_name VARCHAR(255),
  button_id VARCHAR(255),
  button_name VARCHAR(255),
  category VARCHAR(255),
  custom_properties SUPER,
  PRIMARY KEY (activity_id)
);

----------------------------------------------------------------------
-- CAMPAIGN METADATA AND SEND HISTORY TABLES
----------------------------------------------------------------------

CREATE TABLE messagegears_campaign_metadata (
  job_id INTEGER NOT NULL,
  request_id VARCHAR(50) NOT NULL,
  account_id INTEGER,
  start_time TIMESTAMPTZ NOT NULL DEFAULT GETDATE(),
  campaign_id INTEGER,
  campaign_name VARCHAR(255),
  job_category VARCHAR(255),
  template_id INTEGER,
  template_name VARCHAR(255),
  PRIMARY KEY (job_id)
);

CREATE TABLE messagegears_send_history (
  job_id INTEGER,
  recipient_id VARCHAR(255),
  campaign_id INTEGER,
  job_category VARCHAR(255),
  send_time TIMESTAMPTZ NOT NULL DEFAULT GETDATE()
);

PostgreSQL

Covers email, SMS, push, and mobile SDK events. These statements are translated from the canonical event feed schema to PostgreSQL types (for example custom_properties becomes JSONB). Validate the type choices against your environment before running them in production.

-----------------------------------
-- EMAIL EVENT TABLES
-----------------------------------
CREATE TABLE messagegears_bounce (
  account_id INTEGER,
  request_id VARCHAR(50) NOT NULL,
  activity_id VARCHAR(36) NOT NULL,
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  correlation_id VARCHAR(255),
  job_category VARCHAR(255),
  campaign_id INTEGER,
  campaign_version INTEGER,
  email_address VARCHAR(255),
  recipient_id VARCHAR(255),
  local_ip VARCHAR(255),
  remote_ip VARCHAR(255),
  category VARCHAR(255),
  category_code INTEGER,
  details TEXT,
  custom_properties JSONB,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_click (
  account_id INTEGER,
  request_id VARCHAR(50) NOT NULL,
  activity_id VARCHAR(36) NOT NULL,
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  correlation_id VARCHAR(255),
  job_category VARCHAR(255),
  campaign_id INTEGER,
  campaign_version INTEGER,
  email_address VARCHAR(255),
  recipient_id VARCHAR(255),
  ip_address VARCHAR(255),
  user_agent VARCHAR(510),
  microsite BOOLEAN DEFAULT FALSE,
  url TEXT,
  url_name VARCHAR(510),
  url_id VARCHAR(510),
  custom_properties JSONB,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_delivery (
  account_id INTEGER,
  request_id VARCHAR(50) NOT NULL,
  activity_id VARCHAR(36) NOT NULL,
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  correlation_id VARCHAR(255),
  job_category VARCHAR(255),
  campaign_id INTEGER,
  campaign_version INTEGER,
  email_address VARCHAR(255),
  recipient_id VARCHAR(255),
  local_ip VARCHAR(255),
  remote_ip VARCHAR(255),
  message_size INTEGER,
  custom_properties JSONB,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_open (
  account_id INTEGER,
  request_id VARCHAR(50) NOT NULL,
  activity_id VARCHAR(36) NOT NULL,
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  correlation_id VARCHAR(255),
  job_category VARCHAR(255),
  campaign_id INTEGER,
  campaign_version INTEGER,
  email_address VARCHAR(255),
  recipient_id VARCHAR(255),
  ip_address VARCHAR(255),
  user_agent VARCHAR(510),
  microsite BOOLEAN DEFAULT FALSE,
  adjusted_open BOOLEAN DEFAULT FALSE,
  custom_properties JSONB,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_spamcomplaint (
  account_id INTEGER,
  request_id VARCHAR(50) NOT NULL,
  activity_id VARCHAR(36) NOT NULL,
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  correlation_id VARCHAR(255),
  job_category VARCHAR(255),
  campaign_id INTEGER,
  campaign_version INTEGER,
  email_address VARCHAR(255),
  recipient_id VARCHAR(255),
  isp VARCHAR(255),
  ip_address VARCHAR(30),
  subject VARCHAR(510),
  custom_properties JSONB,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_unsubscribe (
  account_id INTEGER,
  request_id VARCHAR(50) NOT NULL,
  activity_id VARCHAR(36) NOT NULL,
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  correlation_id VARCHAR(255),
  job_category VARCHAR(255),
  campaign_id INTEGER,
  campaign_version INTEGER,
  email_address VARCHAR(255),
  recipient_id VARCHAR(255),
  ip_address VARCHAR(255),
  user_agent VARCHAR(510),
  microsite BOOLEAN DEFAULT FALSE,
  custom_properties JSONB,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_render_error (
  account_id INTEGER,
  request_id VARCHAR(50) NOT NULL,
  activity_id VARCHAR(36) NOT NULL,
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  correlation_id VARCHAR(255),
  job_category VARCHAR(255),
  campaign_id INTEGER,
  campaign_version INTEGER,
  email_address VARCHAR(255),
  recipient_id VARCHAR(255),
  error_code VARCHAR(255),
  error_message TEXT,
  custom_properties JSONB,
  PRIMARY KEY (activity_id)
);

-----------------------------------
-- SMS EVENT TABLES
-----------------------------------

CREATE TABLE messagegears_sms_delivery (
  account_id INTEGER,
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  request_id VARCHAR(50) NOT NULL,
  correlation_id VARCHAR(255),
  job_category VARCHAR(255),
  campaign_id INTEGER,
  campaign_version INTEGER,
  activity_id VARCHAR(36) NOT NULL,
  sms_address VARCHAR(255),
  recipient_id VARCHAR(255),
  origination_number VARCHAR(255),
  sms_name VARCHAR(255),
  custom_properties JSONB,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_sms_click (
  account_id INTEGER,
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  request_id VARCHAR(50) NOT NULL,
  correlation_id VARCHAR(255),
  job_category VARCHAR(255),
  campaign_id INTEGER,
  campaign_version INTEGER,
  activity_id VARCHAR(36) NOT NULL,
  sms_address VARCHAR(255),
  recipient_id VARCHAR(255),
  origination_number VARCHAR(255),
  sms_name VARCHAR(255),
  url TEXT,
  url_name VARCHAR(510),
  url_id VARCHAR(510),
  vendor_id INTEGER,
  ip_address VARCHAR(255),
  user_agent VARCHAR(510)
);

CREATE TABLE messagegears_sms_delivery_failure (
  account_id INTEGER,
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  request_id VARCHAR(50) NOT NULL,
  correlation_id VARCHAR(255),
  job_category VARCHAR(255),
  campaign_id INTEGER,
  campaign_version INTEGER,
  activity_id VARCHAR(36) NOT NULL,
  sms_address VARCHAR(255),
  recipient_id VARCHAR(255),
  category VARCHAR(255),
  details TEXT,
  origination_number VARCHAR(255),
  sms_name VARCHAR(255),
  custom_properties JSONB,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_sms_render_error (
  account_id INTEGER,
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  request_id VARCHAR(50) NOT NULL,
  correlation_id VARCHAR(255),
  job_category VARCHAR(255),
  campaign_id INTEGER,
  campaign_version INTEGER,
  activity_id VARCHAR(36) NOT NULL,
  sms_address VARCHAR(255),
  recipient_id VARCHAR(255),
  error_code VARCHAR(255),
  error_message TEXT,
  origination_number VARCHAR(255),
  sms_name VARCHAR(255),
  custom_properties JSONB,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_sms_inbound (
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  activity_id VARCHAR(36) NOT NULL,
  sms_address VARCHAR(255),
  to_address VARCHAR(255),
  message TEXT,
  message_type VARCHAR(36) NOT NULL,
  origination_number VARCHAR(255),
  sms_name VARCHAR(255),
  jobCategory VARCHAR(255),
  campaignId INTEGER,
  campaignVersion INTEGER,
  accountId INTEGER,
  requestId VARCHAR(50) NOT NULL,
  correlationId VARCHAR(255),
  clientCampaignId VARCHAR(255),
  recipientId VARCHAR(255),
  custom_properties JSONB,
  PRIMARY KEY (activity_id)
);

-----------------------------------
-- PUSH EVENT TABLES
-----------------------------------

CREATE TABLE messagegears_push_delivery (
  account_id INTEGER,
  activity_id VARCHAR(36) NOT NULL,
  request_id VARCHAR(50) NOT NULL,
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  correlation_id VARCHAR(255),
  job_category VARCHAR(255),
  campaign_id INTEGER,
  campaign_version INTEGER,
  device_id VARCHAR(255),
  service VARCHAR(255),
  application_id VARCHAR(255),
  recipient_id VARCHAR(255),
  push_delivery_service VARCHAR(255),
  mobile_user_id VARCHAR(255),
  custom_properties JSONB,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_push_click (
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  request_id VARCHAR(50),
  correlation_id VARCHAR(255),
  activity_id VARCHAR(36) NOT NULL,
  device_id VARCHAR(255),
  service VARCHAR(255),
  application_id VARCHAR(255),
  push_delivery_service VARCHAR(255),
  account_id INTEGER,
  job_category VARCHAR(255),
  campaign_id INTEGER,
  campaign_version INTEGER,
  recipient_id VARCHAR(255),
  ip_address VARCHAR(255),
  user_agent VARCHAR(255),
  destination VARCHAR(255),
  url_name VARCHAR(255),
  url_id VARCHAR(255),
  mobile_user_id VARCHAR(255),
  vendor_app_id VARCHAR(50),
  custom_properties JSONB,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_push_delivery_failure (
  account_id INTEGER,
  activity_id VARCHAR(36) NOT NULL,
  request_id VARCHAR(50) NOT NULL,
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  correlation_id VARCHAR(255),
  job_category VARCHAR(255),
  campaign_id INTEGER,
  campaign_version INTEGER,
  device_id VARCHAR(255),
  service VARCHAR(255),
  application_id VARCHAR(255),
  recipient_id VARCHAR(255),
  category VARCHAR(255),
  details TEXT,
  push_delivery_service VARCHAR(255),
  mobile_user_id VARCHAR(255),
  custom_properties JSONB,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_push_render_error (
  account_id INTEGER,
  activity_id VARCHAR(36) NOT NULL,
  request_id VARCHAR(50) NOT NULL,
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  correlation_id VARCHAR(255),
  job_category VARCHAR(255),
  campaign_id INTEGER,
  campaign_version INTEGER,
  device_id VARCHAR(255),
  service VARCHAR(255),
  application_id VARCHAR(255),
  recipient_id VARCHAR(255),
  error_code VARCHAR(255),
  error_message TEXT,
  custom_properties JSONB,
  PRIMARY KEY (activity_id)
);

----------------------------------------------------
--SDK CORE MOBILE EVENT TABLES
----------------------------------------------------

CREATE TABLE messagegears_mobile_user_session (
  activity_id VARCHAR(255) NOT NULL,
  application_id VARCHAR(255),
  device_id VARCHAR(255),
  mobile_app_id VARCHAR(255),
  mobile_user_id VARCHAR(255),
  recipient_id VARCHAR(255),
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  user_type VARCHAR(50),
  custom_properties JSONB,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_mobile_user_first_session (
  activity_id VARCHAR(255) NOT NULL,
  application_id VARCHAR(255),
  device_id VARCHAR(255),
  mobile_app_id VARCHAR(255),
  mobile_user_id VARCHAR(255),
  recipient_id VARCHAR(255),
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  user_type VARCHAR(50),
  custom_properties JSONB,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_mobile_user_identity_update (
  activity_id VARCHAR(255) NOT NULL,
  application_id VARCHAR(255),
  device_id VARCHAR(255),
  mobile_app_id VARCHAR(255),
  mobile_user_id VARCHAR(255),
  recipient_id VARCHAR(255),
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  user_type VARCHAR(50),
  custom_properties JSONB,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_mobile_user_push_permission_update (
  activity_id VARCHAR(255) NOT NULL,
  application_id VARCHAR(50),
  device_id VARCHAR(255),
  mobile_app_id VARCHAR(255),
  mobile_user_id VARCHAR(255),
  pushPermission VARCHAR(50),
  recipient_id VARCHAR(255),
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  user_type VARCHAR(50),
  custom_properties JSONB,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_mobile_user_device_update (
  activity_id VARCHAR(255) NOT NULL,
  application_id VARCHAR(255),
  device_id VARCHAR(255),
  mobile_app_id VARCHAR(255),
  mobile_user_id VARCHAR(255),
  recipient_id VARCHAR(255),
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  user_type VARCHAR(50),
  device_type VARCHAR(255),
  device_name VARCHAR(255),
  region VARCHAR(50),
  language VARCHAR(50),
  platform VARCHAR(50),
  platform_version VARCHAR(50),
  device_timezone_name VARCHAR(100),
  mobile_sdk_version VARCHAR(50),
  app_store VARCHAR(50),
  install_date TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  utc_offset VARCHAR(50),
  ios_push_permission VARCHAR(50),
  background_push_permissions VARCHAR(50),
  camera_permission VARCHAR(50),
  contacts_permission VARCHAR(50),
  photos_permission VARCHAR(50),
  location_always_permission VARCHAR(50),
  location_in_use_permission VARCHAR(50),
  location_accuracy_permission VARCHAR(50),
  ios_push_token VARCHAR(50),
  support_rich_gif BOOLEAN,
  mobile_sdk_flavor VARCHAR(50),
  push_permission_enabled BOOLEAN,
  push_importance VARCHAR(50),
  android_push_permission VARCHAR(50),
  permission_show_rationale BOOLEAN,
  permission_answered_times INTEGER,
  gcm_push_token VARCHAR(50),
  location_service_enabled BOOLEAN,
  custom_properties JSONB,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_mobile_user_custom_event (
  activity_id VARCHAR(255) NOT NULL,
  event_name VARCHAR(255),
  application_id VARCHAR(50),
  device_id VARCHAR(255),
  mobile_app_id VARCHAR(255),
  mobile_user_id VARCHAR(255),
  recipient_id VARCHAR(255),
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  user_type VARCHAR(50),
  payload JSONB,
  custom_properties JSONB,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_mobile_user_property_update (
  activity_id VARCHAR(255) NOT NULL,
  application_id VARCHAR(50),
  device_id VARCHAR(255),
  mobile_app_id VARCHAR(255),
  mobile_user_id VARCHAR(255),
  recipient_id VARCHAR(255),
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  user_type VARCHAR(50),
  attributes JSONB,
  custom_properties JSONB,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_mobile_in_app_impression (
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  activity_id VARCHAR(255) NOT NULL,
  in_app_campaign_id VARCHAR(255),
  in_app_tracking_id VARCHAR(255),
  client_campaign_id VARCHAR(255),
  campaign_uuid VARCHAR(255),
  device_id VARCHAR(255),
  application_id VARCHAR(255),
  vendor_app_id VARCHAR(255),
  recipient_id VARCHAR(255),
  platform VARCHAR(255),
  device_type VARCHAR(255),
  mobile_user_id VARCHAR(255),
  category VARCHAR(255),
  custom_properties JSONB,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_mobile_in_app_page_view (
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  activity_id VARCHAR(255) NOT NULL,
  in_app_campaign_id VARCHAR(255),
  in_app_tracking_id VARCHAR(255),
  client_campaign_id VARCHAR(255),
  campaign_uuid VARCHAR(255),
  device_id VARCHAR(255),
  application_id VARCHAR(255),
  vendor_app_id VARCHAR(255),
  recipient_id VARCHAR(255),
  platform VARCHAR(255),
  device_type VARCHAR(255),
  mobile_user_id VARCHAR(255),
  page_id VARCHAR(255),
  page_name VARCHAR(255),
  category VARCHAR(255),
  custom_properties JSONB,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_mobile_in_app_click (
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  activity_id VARCHAR(255) NOT NULL,
  in_app_campaign_id VARCHAR(255),
  in_app_tracking_id VARCHAR(255),
  client_campaign_id VARCHAR(255),
  campaign_uuid VARCHAR(255),
  device_id VARCHAR(255),
  application_id VARCHAR(255),
  vendor_app_id VARCHAR(255),
  recipient_id VARCHAR(255),
  platform VARCHAR(255),
  device_type VARCHAR(255),
  mobile_user_id VARCHAR(255),
  page_id VARCHAR(255),
  page_name VARCHAR(255),
  button_id VARCHAR(255),
  button_name VARCHAR(255),
  category VARCHAR(255),
  custom_properties JSONB,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_mobile_in_app_dismissed (
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  activity_id VARCHAR(255) NOT NULL,
  in_app_campaign_id VARCHAR(255),
  in_app_tracking_id VARCHAR(255),
  client_campaign_id VARCHAR(255),
  campaign_uuid VARCHAR(255),
  device_id VARCHAR(255),
  application_id VARCHAR(255),
  vendor_app_id VARCHAR(255),
  recipient_id VARCHAR(255),
  platform VARCHAR(255),
  device_type VARCHAR(255),
  mobile_user_id VARCHAR(255),
  page_id VARCHAR(255),
  page_name VARCHAR(255),
  button_id VARCHAR(255),
  button_name VARCHAR(255),
  category VARCHAR(255),
  custom_properties JSONB,
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_mobile_in_app_page_navigation (
  activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  activity_id VARCHAR(255) NOT NULL,
  in_app_campaign_id VARCHAR(255),
  in_app_tracking_id VARCHAR(255),
  client_campaign_id VARCHAR(255),
  campaign_uuid VARCHAR(255),
  device_id VARCHAR(255),
  application_id VARCHAR(255),
  vendor_app_id VARCHAR(255),
  recipient_id VARCHAR(255),
  platform VARCHAR(255),
  device_type VARCHAR(255),
  mobile_user_id VARCHAR(255),
  page_id VARCHAR(255),
  page_name VARCHAR(255),
  button_id VARCHAR(255),
  button_name VARCHAR(255),
  category VARCHAR(255),
  custom_properties JSONB,
  PRIMARY KEY (activity_id)
);


----------------------------------------------------------------------
-- CAMPAIGN METADATA AND SEND HISTORY TABLES
----------------------------------------------------------------------

CREATE TABLE messagegears_campaign_metadata (
  job_id INTEGER NOT NULL,
  request_id VARCHAR(50) NOT NULL,
  account_id INTEGER,
  start_time TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  campaign_id INTEGER,
  campaign_name VARCHAR(255),
  job_category VARCHAR(255),
  template_id INTEGER,
  template_name VARCHAR(255),
  PRIMARY KEY (job_id)
);

CREATE TABLE messagegears_send_history (
  job_id INTEGER,
  recipient_id VARCHAR(255),
  campaign_id INTEGER,
  job_category VARCHAR(255),
  send_time TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

SQL Server

Covers email, SMS, push, and mobile SDK events. These statements are translated from the canonical event feed schema to SQL Server types (for example custom_properties becomes NVARCHAR(MAX)). Validate the type choices against your environment before running them in production.

-----------------------------------
-- EMAIL EVENT TABLES
-----------------------------------
CREATE TABLE messagegears_bounce (
  account_id INT,
  request_id VARCHAR(50) NOT NULL,
  activity_id VARCHAR(36) NOT NULL,
  activity_timestamp DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
  correlation_id VARCHAR(255),
  job_category VARCHAR(255),
  campaign_id INT,
  campaign_version INT,
  email_address VARCHAR(255),
  recipient_id VARCHAR(255),
  local_ip VARCHAR(255),
  remote_ip VARCHAR(255),
  category VARCHAR(255),
  category_code INT,
  details NVARCHAR(MAX),
  custom_properties NVARCHAR(MAX),
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_click (
  account_id INT,
  request_id VARCHAR(50) NOT NULL,
  activity_id VARCHAR(36) NOT NULL,
  activity_timestamp DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
  correlation_id VARCHAR(255),
  job_category VARCHAR(255),
  campaign_id INT,
  campaign_version INT,
  email_address VARCHAR(255),
  recipient_id VARCHAR(255),
  ip_address VARCHAR(255),
  user_agent VARCHAR(510),
  microsite BIT DEFAULT 0,
  url NVARCHAR(MAX),
  url_name VARCHAR(510),
  url_id VARCHAR(510),
  custom_properties NVARCHAR(MAX),
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_delivery (
  account_id INT,
  request_id VARCHAR(50) NOT NULL,
  activity_id VARCHAR(36) NOT NULL,
  activity_timestamp DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
  correlation_id VARCHAR(255),
  job_category VARCHAR(255),
  campaign_id INT,
  campaign_version INT,
  email_address VARCHAR(255),
  recipient_id VARCHAR(255),
  local_ip VARCHAR(255),
  remote_ip VARCHAR(255),
  message_size INT,
  custom_properties NVARCHAR(MAX),
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_open (
  account_id INT,
  request_id VARCHAR(50) NOT NULL,
  activity_id VARCHAR(36) NOT NULL,
  activity_timestamp DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
  correlation_id VARCHAR(255),
  job_category VARCHAR(255),
  campaign_id INT,
  campaign_version INT,
  email_address VARCHAR(255),
  recipient_id VARCHAR(255),
  ip_address VARCHAR(255),
  user_agent VARCHAR(510),
  microsite BIT DEFAULT 0,
  adjusted_open BIT DEFAULT 0,
  custom_properties NVARCHAR(MAX),
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_spamcomplaint (
  account_id INT,
  request_id VARCHAR(50) NOT NULL,
  activity_id VARCHAR(36) NOT NULL,
  activity_timestamp DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
  correlation_id VARCHAR(255),
  job_category VARCHAR(255),
  campaign_id INT,
  campaign_version INT,
  email_address VARCHAR(255),
  recipient_id VARCHAR(255),
  isp VARCHAR(255),
  ip_address VARCHAR(30),
  subject VARCHAR(510),
  custom_properties NVARCHAR(MAX),
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_unsubscribe (
  account_id INT,
  request_id VARCHAR(50) NOT NULL,
  activity_id VARCHAR(36) NOT NULL,
  activity_timestamp DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
  correlation_id VARCHAR(255),
  job_category VARCHAR(255),
  campaign_id INT,
  campaign_version INT,
  email_address VARCHAR(255),
  recipient_id VARCHAR(255),
  ip_address VARCHAR(255),
  user_agent VARCHAR(510),
  microsite BIT DEFAULT 0,
  custom_properties NVARCHAR(MAX),
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_render_error (
  account_id INT,
  request_id VARCHAR(50) NOT NULL,
  activity_id VARCHAR(36) NOT NULL,
  activity_timestamp DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
  correlation_id VARCHAR(255),
  job_category VARCHAR(255),
  campaign_id INT,
  campaign_version INT,
  email_address VARCHAR(255),
  recipient_id VARCHAR(255),
  error_code VARCHAR(255),
  error_message NVARCHAR(MAX),
  custom_properties NVARCHAR(MAX),
  PRIMARY KEY (activity_id)
);

-----------------------------------
-- SMS EVENT TABLES
-----------------------------------

CREATE TABLE messagegears_sms_delivery (
  account_id INT,
  activity_timestamp DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
  request_id VARCHAR(50) NOT NULL,
  correlation_id VARCHAR(255),
  job_category VARCHAR(255),
  campaign_id INT,
  campaign_version INT,
  activity_id VARCHAR(36) NOT NULL,
  sms_address VARCHAR(255),
  recipient_id VARCHAR(255),
  origination_number VARCHAR(255),
  sms_name VARCHAR(255),
  custom_properties NVARCHAR(MAX),
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_sms_click (
  account_id INT,
  activity_timestamp DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
  request_id VARCHAR(50) NOT NULL,
  correlation_id VARCHAR(255),
  job_category VARCHAR(255),
  campaign_id INT,
  campaign_version INT,
  activity_id VARCHAR(36) NOT NULL,
  sms_address VARCHAR(255),
  recipient_id VARCHAR(255),
  origination_number VARCHAR(255),
  sms_name VARCHAR(255),
  url NVARCHAR(MAX),
  url_name VARCHAR(510),
  url_id VARCHAR(510),
  vendor_id INT,
  ip_address VARCHAR(255),
  user_agent VARCHAR(510)
);

CREATE TABLE messagegears_sms_delivery_failure (
  account_id INT,
  activity_timestamp DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
  request_id VARCHAR(50) NOT NULL,
  correlation_id VARCHAR(255),
  job_category VARCHAR(255),
  campaign_id INT,
  campaign_version INT,
  activity_id VARCHAR(36) NOT NULL,
  sms_address VARCHAR(255),
  recipient_id VARCHAR(255),
  category VARCHAR(255),
  details NVARCHAR(MAX),
  origination_number VARCHAR(255),
  sms_name VARCHAR(255),
  custom_properties NVARCHAR(MAX),
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_sms_render_error (
  account_id INT,
  activity_timestamp DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
  request_id VARCHAR(50) NOT NULL,
  correlation_id VARCHAR(255),
  job_category VARCHAR(255),
  campaign_id INT,
  campaign_version INT,
  activity_id VARCHAR(36) NOT NULL,
  sms_address VARCHAR(255),
  recipient_id VARCHAR(255),
  error_code VARCHAR(255),
  error_message NVARCHAR(MAX),
  origination_number VARCHAR(255),
  sms_name VARCHAR(255),
  custom_properties NVARCHAR(MAX),
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_sms_inbound (
  activity_timestamp DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
  activity_id VARCHAR(36) NOT NULL,
  sms_address VARCHAR(255),
  to_address VARCHAR(255),
  message NVARCHAR(MAX),
  message_type VARCHAR(36) NOT NULL,
  origination_number VARCHAR(255),
  sms_name VARCHAR(255),
  jobCategory VARCHAR(255),
  campaignId INT,
  campaignVersion INT,
  accountId INT,
  requestId VARCHAR(50) NOT NULL,
  correlationId VARCHAR(255),
  clientCampaignId VARCHAR(255),
  recipientId VARCHAR(255),
  custom_properties NVARCHAR(MAX),
  PRIMARY KEY (activity_id)
);

-----------------------------------
-- PUSH EVENT TABLES
-----------------------------------

CREATE TABLE messagegears_push_delivery (
  account_id INT,
  activity_id VARCHAR(36) NOT NULL,
  request_id VARCHAR(50) NOT NULL,
  activity_timestamp DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
  correlation_id VARCHAR(255),
  job_category VARCHAR(255),
  campaign_id INT,
  campaign_version INT,
  device_id VARCHAR(255),
  service VARCHAR(255),
  application_id VARCHAR(255),
  recipient_id VARCHAR(255),
  push_delivery_service VARCHAR(255),
  mobile_user_id VARCHAR(255),
  custom_properties NVARCHAR(MAX),
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_push_click (
  activity_timestamp DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
  request_id VARCHAR(50),
  correlation_id VARCHAR(255),
  activity_id VARCHAR(36) NOT NULL,
  device_id VARCHAR(255),
  service VARCHAR(255),
  application_id VARCHAR(255),
  push_delivery_service VARCHAR(255),
  account_id INT,
  job_category VARCHAR(255),
  campaign_id INT,
  campaign_version INT,
  recipient_id VARCHAR(255),
  ip_address VARCHAR(255),
  user_agent VARCHAR(255),
  destination VARCHAR(255),
  url_name VARCHAR(255),
  url_id VARCHAR(255),
  mobile_user_id VARCHAR(255),
  vendor_app_id VARCHAR(50),
  custom_properties NVARCHAR(MAX),
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_push_delivery_failure (
  account_id INT,
  activity_id VARCHAR(36) NOT NULL,
  request_id VARCHAR(50) NOT NULL,
  activity_timestamp DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
  correlation_id VARCHAR(255),
  job_category VARCHAR(255),
  campaign_id INT,
  campaign_version INT,
  device_id VARCHAR(255),
  service VARCHAR(255),
  application_id VARCHAR(255),
  recipient_id VARCHAR(255),
  category VARCHAR(255),
  details NVARCHAR(MAX),
  push_delivery_service VARCHAR(255),
  mobile_user_id VARCHAR(255),
  custom_properties NVARCHAR(MAX),
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_push_render_error (
  account_id INT,
  activity_id VARCHAR(36) NOT NULL,
  request_id VARCHAR(50) NOT NULL,
  activity_timestamp DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
  correlation_id VARCHAR(255),
  job_category VARCHAR(255),
  campaign_id INT,
  campaign_version INT,
  device_id VARCHAR(255),
  service VARCHAR(255),
  application_id VARCHAR(255),
  recipient_id VARCHAR(255),
  error_code VARCHAR(255),
  error_message NVARCHAR(MAX),
  custom_properties NVARCHAR(MAX),
  PRIMARY KEY (activity_id)
);

----------------------------------------------------
--SDK CORE MOBILE EVENT TABLES
----------------------------------------------------

CREATE TABLE messagegears_mobile_user_session (
  activity_id VARCHAR(255) NOT NULL,
  application_id VARCHAR(255),
  device_id VARCHAR(255),
  mobile_app_id VARCHAR(255),
  mobile_user_id VARCHAR(255),
  recipient_id VARCHAR(255),
  activity_timestamp DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
  user_type VARCHAR(50),
  custom_properties NVARCHAR(MAX),
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_mobile_user_first_session (
  activity_id VARCHAR(255) NOT NULL,
  application_id VARCHAR(255),
  device_id VARCHAR(255),
  mobile_app_id VARCHAR(255),
  mobile_user_id VARCHAR(255),
  recipient_id VARCHAR(255),
  activity_timestamp DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
  user_type VARCHAR(50),
  custom_properties NVARCHAR(MAX),
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_mobile_user_identity_update (
  activity_id VARCHAR(255) NOT NULL,
  application_id VARCHAR(255),
  device_id VARCHAR(255),
  mobile_app_id VARCHAR(255),
  mobile_user_id VARCHAR(255),
  recipient_id VARCHAR(255),
  activity_timestamp DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
  user_type VARCHAR(50),
  custom_properties NVARCHAR(MAX),
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_mobile_user_push_permission_update (
  activity_id VARCHAR(255) NOT NULL,
  application_id VARCHAR(50),
  device_id VARCHAR(255),
  mobile_app_id VARCHAR(255),
  mobile_user_id VARCHAR(255),
  pushPermission VARCHAR(50),
  recipient_id VARCHAR(255),
  activity_timestamp DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
  user_type VARCHAR(50),
  custom_properties NVARCHAR(MAX),
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_mobile_user_device_update (
  activity_id VARCHAR(255) NOT NULL,
  application_id VARCHAR(255),
  device_id VARCHAR(255),
  mobile_app_id VARCHAR(255),
  mobile_user_id VARCHAR(255),
  recipient_id VARCHAR(255),
  activity_timestamp DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
  user_type VARCHAR(50),
  device_type VARCHAR(255),
  device_name VARCHAR(255),
  region VARCHAR(50),
  language VARCHAR(50),
  platform VARCHAR(50),
  platform_version VARCHAR(50),
  device_timezone_name VARCHAR(100),
  mobile_sdk_version VARCHAR(50),
  app_store VARCHAR(50),
  install_date DATETIMEOFFSET NOT NULL DEFAULT SYSDATETIMEOFFSET(),
  utc_offset VARCHAR(50),
  ios_push_permission VARCHAR(50),
  background_push_permissions VARCHAR(50),
  camera_permission VARCHAR(50),
  contacts_permission VARCHAR(50),
  photos_permission VARCHAR(50),
  location_always_permission VARCHAR(50),
  location_in_use_permission VARCHAR(50),
  location_accuracy_permission VARCHAR(50),
  ios_push_token VARCHAR(50),
  support_rich_gif BIT,
  mobile_sdk_flavor VARCHAR(50),
  push_permission_enabled BIT,
  push_importance VARCHAR(50),
  android_push_permission VARCHAR(50),
  permission_show_rationale BIT,
  permission_answered_times INT,
  gcm_push_token VARCHAR(50),
  location_service_enabled BIT,
  custom_properties NVARCHAR(MAX),
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_mobile_user_custom_event (
  activity_id VARCHAR(255) NOT NULL,
  event_name VARCHAR(255),
  application_id VARCHAR(50),
  device_id VARCHAR(255),
  mobile_app_id VARCHAR(255),
  mobile_user_id VARCHAR(255),
  recipient_id VARCHAR(255),
  activity_timestamp DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
  user_type VARCHAR(50),
  payload NVARCHAR(MAX),
  custom_properties NVARCHAR(MAX),
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_mobile_user_property_update (
  activity_id VARCHAR(255) NOT NULL,
  application_id VARCHAR(50),
  device_id VARCHAR(255),
  mobile_app_id VARCHAR(255),
  mobile_user_id VARCHAR(255),
  recipient_id VARCHAR(255),
  activity_timestamp DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
  user_type VARCHAR(50),
  attributes NVARCHAR(MAX),
  custom_properties NVARCHAR(MAX),
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_mobile_in_app_impression (
  activity_timestamp DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
  activity_id VARCHAR(255) NOT NULL,
  in_app_campaign_id VARCHAR(255),
  in_app_tracking_id VARCHAR(255),
  client_campaign_id VARCHAR(255),
  campaign_uuid VARCHAR(255),
  device_id VARCHAR(255),
  application_id VARCHAR(255),
  vendor_app_id VARCHAR(255),
  recipient_id VARCHAR(255),
  platform VARCHAR(255),
  device_type VARCHAR(255),
  mobile_user_id VARCHAR(255),
  category VARCHAR(255),
  custom_properties NVARCHAR(MAX),
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_mobile_in_app_page_view (
  activity_timestamp DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
  activity_id VARCHAR(255) NOT NULL,
  in_app_campaign_id VARCHAR(255),
  in_app_tracking_id VARCHAR(255),
  client_campaign_id VARCHAR(255),
  campaign_uuid VARCHAR(255),
  device_id VARCHAR(255),
  application_id VARCHAR(255),
  vendor_app_id VARCHAR(255),
  recipient_id VARCHAR(255),
  platform VARCHAR(255),
  device_type VARCHAR(255),
  mobile_user_id VARCHAR(255),
  page_id VARCHAR(255),
  page_name VARCHAR(255),
  category VARCHAR(255),
  custom_properties NVARCHAR(MAX),
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_mobile_in_app_click (
  activity_timestamp DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
  activity_id VARCHAR(255) NOT NULL,
  in_app_campaign_id VARCHAR(255),
  in_app_tracking_id VARCHAR(255),
  client_campaign_id VARCHAR(255),
  campaign_uuid VARCHAR(255),
  device_id VARCHAR(255),
  application_id VARCHAR(255),
  vendor_app_id VARCHAR(255),
  recipient_id VARCHAR(255),
  platform VARCHAR(255),
  device_type VARCHAR(255),
  mobile_user_id VARCHAR(255),
  page_id VARCHAR(255),
  page_name VARCHAR(255),
  button_id VARCHAR(255),
  button_name VARCHAR(255),
  category VARCHAR(255),
  custom_properties NVARCHAR(MAX),
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_mobile_in_app_dismissed (
  activity_timestamp DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
  activity_id VARCHAR(255) NOT NULL,
  in_app_campaign_id VARCHAR(255),
  in_app_tracking_id VARCHAR(255),
  client_campaign_id VARCHAR(255),
  campaign_uuid VARCHAR(255),
  device_id VARCHAR(255),
  application_id VARCHAR(255),
  vendor_app_id VARCHAR(255),
  recipient_id VARCHAR(255),
  platform VARCHAR(255),
  device_type VARCHAR(255),
  mobile_user_id VARCHAR(255),
  page_id VARCHAR(255),
  page_name VARCHAR(255),
  button_id VARCHAR(255),
  button_name VARCHAR(255),
  category VARCHAR(255),
  custom_properties NVARCHAR(MAX),
  PRIMARY KEY (activity_id)
);

CREATE TABLE messagegears_mobile_in_app_page_navigation (
  activity_timestamp DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
  activity_id VARCHAR(255) NOT NULL,
  in_app_campaign_id VARCHAR(255),
  in_app_tracking_id VARCHAR(255),
  client_campaign_id VARCHAR(255),
  campaign_uuid VARCHAR(255),
  device_id VARCHAR(255),
  application_id VARCHAR(255),
  vendor_app_id VARCHAR(255),
  recipient_id VARCHAR(255),
  platform VARCHAR(255),
  device_type VARCHAR(255),
  mobile_user_id VARCHAR(255),
  page_id VARCHAR(255),
  page_name VARCHAR(255),
  button_id VARCHAR(255),
  button_name VARCHAR(255),
  category VARCHAR(255),
  custom_properties NVARCHAR(MAX),
  PRIMARY KEY (activity_id)
);

----------------------------------------------------------------------
-- CAMPAIGN METADATA AND SEND HISTORY TABLES
----------------------------------------------------------------------

CREATE TABLE messagegears_campaign_metadata (
  job_id INT NOT NULL,
  request_id VARCHAR(50) NOT NULL,
  account_id INT,
  start_time DATETIMEOFFSET NOT NULL DEFAULT SYSDATETIMEOFFSET(),
  campaign_id INT,
  campaign_name VARCHAR(255),
  job_category VARCHAR(255),
  template_id INT,
  template_name VARCHAR(255),
  PRIMARY KEY (job_id)
);

CREATE TABLE messagegears_send_history (
  job_id INT,
  recipient_id VARCHAR(255),
  campaign_id INT,
  job_category VARCHAR(255),
  send_time DATETIMEOFFSET NOT NULL DEFAULT SYSDATETIMEOFFSET()
);