qbit-email/docs/ddl/email-postgres.sql

-- Phases (a), (b) and (c). Apply before producing the qbit. Host security columns must be added by the host.

CREATE TABLE email_provider (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   create_date TIMESTAMP,
   modify_date TIMESTAMP,
   name VARCHAR(500),
   label VARCHAR(500),
   kind VARCHAR(500),
   enabled BOOLEAN,
   is_default BOOLEAN,
   from_address VARCHAR(500),
   from_name VARCHAR(500),
   reply_to VARCHAR(500),
   sending_domain VARCHAR(500),
   host VARCHAR(500),
   port INTEGER,
   security VARCHAR(500),
   region VARCHAR(500),
   username_env_name VARCHAR(500),
   secret_env_name VARCHAR(500),
   webhook_secret_env_name VARCHAR(500),
   sns_topic_arn VARCHAR(500),
   configuration_set VARCHAR(500),
   message_stream VARCHAR(500),
   max_per_minute INTEGER,
   max_per_day INTEGER,
   track_opens BOOLEAN,
   track_clicks BOOLEAN,
   phi_allowed BOOLEAN,
   health_status VARCHAR(500),
   last_success_date TIMESTAMP,
   last_failure_date TIMESTAMP,
   last_failure_message VARCHAR(500),
   dispatch_lock INTEGER,
   default_slot INTEGER,
   UNIQUE (name),
   UNIQUE (default_slot)
);

CREATE TABLE email_provider_sender_address (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   create_date TIMESTAMP,
   modify_date TIMESTAMP,
   provider_id INTEGER NOT NULL,
   address VARCHAR(500) NOT NULL,
   display_name VARCHAR(500),
   is_default BOOLEAN,
   enabled BOOLEAN,
   note TEXT,
   UNIQUE (provider_id, address)
);

CREATE TABLE email_message (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   create_date TIMESTAMP,
   modify_date TIMESTAMP,
   status VARCHAR(500),
   to_addresses TEXT,
   cc_addresses TEXT,
   bcc_addresses TEXT,
   from_address VARCHAR(500),
   from_address_id INTEGER,
   reply_to VARCHAR(500),
   subject TEXT,
   template_id INTEGER,
   template_version_id INTEGER,
   brand_id INTEGER,
   category VARCHAR(500),
   locale VARCHAR(500),
   variables_snapshot TEXT,
   html_body TEXT,
   text_body TEXT,
   body_sha256 VARCHAR(500),
   phi_approved BOOLEAN,
   unsubscribe_url TEXT,
   idempotency_key VARCHAR(500),
   rfc_message_id VARCHAR(500),
   provider_id INTEGER,
   provider_message_id VARCHAR(500),
   attempt_count INTEGER,
   next_attempt_date TIMESTAMP,
   lease_expires_date TIMESTAMP,
   last_error VARCHAR(500),
   finished_date TIMESTAMP,
   sent_date TIMESTAMP,
   delivered_date TIMESTAMP,
   bounced_date TIMESTAMP,
   complained_date TIMESTAMP,
   dead_letter_date TIMESTAMP,
   linked_table_name VARCHAR(500),
   linked_record_id VARCHAR(500),
   sender_qbit VARCHAR(500),
   sender_process VARCHAR(500),
   suppressed_addresses TEXT,
   send_after TIMESTAMP,
   UNIQUE (idempotency_key),
   UNIQUE (rfc_message_id)
);

CREATE TABLE email_message_attempt (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   create_date TIMESTAMP,
   modify_date TIMESTAMP,
   message_id INTEGER NOT NULL,
   attempt_number INTEGER NOT NULL,
   outcome VARCHAR(500),
   provider_id INTEGER,
   claimed_by VARCHAR(500),
   start_date TIMESTAMP,
   end_date TIMESTAMP,
   lease_expires_date TIMESTAMP,
   duration_millis INTEGER,
   error VARCHAR(500),
   UNIQUE (message_id, attempt_number)
);

CREATE TABLE email_template (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   create_date TIMESTAMP,
   modify_date TIMESTAMP,
   name VARCHAR(500),
   label VARCHAR(500),
   category VARCHAR(500),
   kind VARCHAR(500),
   brand_id INTEGER,
   active_version_id INTEGER,
   approved_version_id INTEGER,
   version_lock INTEGER,
   phi_approved BOOLEAN,
   phi_approved_by VARCHAR(500),
   phi_approved_date TIMESTAMP,
   edit_permission_name VARCHAR(500),
   owner_qbit VARCHAR(500),
   description TEXT,
   UNIQUE (name)
);

CREATE TABLE email_template_version (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   create_date TIMESTAMP,
   modify_date TIMESTAMP,
   template_id INTEGER,
   version_number INTEGER,
   status VARCHAR(500),
   locale VARCHAR(500),
   subject TEXT,
   preheader TEXT,
   body_source TEXT,
   text_source TEXT,
   compiled_html TEXT,
   variables_schema TEXT,
   sample_variables TEXT,
   created_by VARCHAR(500),
   activated_date TIMESTAMP,
   UNIQUE (template_id, version_number, locale)
);

CREATE TABLE email_brand (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   create_date TIMESTAMP,
   modify_date TIMESTAMP,
   name VARCHAR(500),
   label VARCHAR(500),
   logo_url VARCHAR(500),
   logo_dark_url VARCHAR(500),
   logo_alt VARCHAR(500),
   primary_color VARCHAR(500),
   text_color VARCHAR(500),
   background_color VARCHAR(500),
   dark_background_color VARCHAR(500),
   dark_text_color VARCHAR(500),
   dark_primary_color VARCHAR(500),
   dark_button_text_color VARCHAR(500),
   button_text_color VARCHAR(500),
   footer_markdown TEXT,
   postal_address TEXT,
   social_links TEXT,
   site_url VARCHAR(500),
   is_default BOOLEAN,
   app_name VARCHAR(500),
   UNIQUE (name)
);

CREATE TABLE email_suppression (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   create_date TIMESTAMP,
   modify_date TIMESTAMP,
   address VARCHAR(500),
   scope VARCHAR(500),
   reason VARCHAR(500),
   source_message_id INTEGER,
   source_event_id INTEGER,
   note TEXT,
   created_date TIMESTAMP,
   expires_date TIMESTAMP,
   UNIQUE (address, scope)
);

CREATE INDEX email_message_due ON email_message (status, next_attempt_date);
CREATE INDEX email_attempt_provider_start ON email_message_attempt (provider_id, start_date);
CREATE INDEX email_attempt_provider_end ON email_message_attempt (provider_id, end_date, id);

CREATE TABLE email_webhook_inbox (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   create_date TIMESTAMP,
   modify_date TIMESTAMP,
   provider_id INTEGER NOT NULL,
   kind VARCHAR(500) NOT NULL,
   external_event_id VARCHAR(500) NOT NULL,
   nonce VARCHAR(500),
   status VARCHAR(500) NOT NULL,
   attempts INTEGER NOT NULL DEFAULT 0,
   next_attempt_date TIMESTAMP,
   last_error VARCHAR(500),
   payload_json TEXT,
   payload_sha256 VARCHAR(500),
   received_date TIMESTAMP,
   processed_date TIMESTAMP,
   UNIQUE (provider_id, external_event_id),
   UNIQUE (provider_id, nonce)
);

CREATE TABLE email_event (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   create_date TIMESTAMP,
   modify_date TIMESTAMP,
   message_id INTEGER NOT NULL,
   provider_id INTEGER,
   event_type VARCHAR(500) NOT NULL,
   bounce_type VARCHAR(500),
   recipient VARCHAR(500),
   occurred_date TIMESTAMP,
   detail VARCHAR(500),
   url TEXT,
   event_key VARCHAR(500) NOT NULL UNIQUE
);
CREATE INDEX email_inbox_due ON email_webhook_inbox (status, next_attempt_date);
CREATE INDEX email_event_message ON email_event (message_id);
CREATE INDEX email_event_type_date ON email_event (event_type, occurred_date);
CREATE INDEX email_message_provider_id ON email_message (provider_id, provider_message_id);

-- Foreign keys (named fk_<table>_<column>), added after every table exists.
--   CASCADE   owned rows: a provider's sender addresses (the provider delete also removes
--             them), a message's attempts and events (message retention deletes them first).
--   RESTRICT  configuration under live data: providers with messages, attempts, events or
--             webhook inbox rows (disable the provider instead); a brand used by a template;
--             a template with versions (templates with versions cannot be deleted).
--   SET NULL  optional pointers: a message's sender address, template, version and brand
--             (the rendered body is kept); a template's active/approved version (the
--             cycle with email_template_version.template_id; drafts can be deleted); a
--             suppression's source message and event, which outlive message retention.
-- Existing databases: run email-foreign-keys-upgrade.sql.
ALTER TABLE email_provider_sender_address ADD CONSTRAINT fk_email_provider_sender_address_provider_id FOREIGN KEY (provider_id) REFERENCES email_provider (id) ON DELETE CASCADE;
ALTER TABLE email_message ADD CONSTRAINT fk_email_message_provider_id FOREIGN KEY (provider_id) REFERENCES email_provider (id) ON DELETE RESTRICT;
ALTER TABLE email_message ADD CONSTRAINT fk_email_message_from_address_id FOREIGN KEY (from_address_id) REFERENCES email_provider_sender_address (id) ON DELETE SET NULL;
ALTER TABLE email_message ADD CONSTRAINT fk_email_message_template_id FOREIGN KEY (template_id) REFERENCES email_template (id) ON DELETE SET NULL;
ALTER TABLE email_message ADD CONSTRAINT fk_email_message_template_version_id FOREIGN KEY (template_version_id) REFERENCES email_template_version (id) ON DELETE SET NULL;
ALTER TABLE email_message ADD CONSTRAINT fk_email_message_brand_id FOREIGN KEY (brand_id) REFERENCES email_brand (id) ON DELETE SET NULL;
ALTER TABLE email_message_attempt ADD CONSTRAINT fk_email_message_attempt_message_id FOREIGN KEY (message_id) REFERENCES email_message (id) ON DELETE CASCADE;
ALTER TABLE email_message_attempt ADD CONSTRAINT fk_email_message_attempt_provider_id FOREIGN KEY (provider_id) REFERENCES email_provider (id) ON DELETE RESTRICT;
ALTER TABLE email_template ADD CONSTRAINT fk_email_template_brand_id FOREIGN KEY (brand_id) REFERENCES email_brand (id) ON DELETE RESTRICT;
ALTER TABLE email_template ADD CONSTRAINT fk_email_template_active_version_id FOREIGN KEY (active_version_id) REFERENCES email_template_version (id) ON DELETE SET NULL;
ALTER TABLE email_template ADD CONSTRAINT fk_email_template_approved_version_id FOREIGN KEY (approved_version_id) REFERENCES email_template_version (id) ON DELETE SET NULL;
ALTER TABLE email_template_version ADD CONSTRAINT fk_email_template_version_template_id FOREIGN KEY (template_id) REFERENCES email_template (id) ON DELETE RESTRICT;
ALTER TABLE email_suppression ADD CONSTRAINT fk_email_suppression_source_message_id FOREIGN KEY (source_message_id) REFERENCES email_message (id) ON DELETE SET NULL;
ALTER TABLE email_suppression ADD CONSTRAINT fk_email_suppression_source_event_id FOREIGN KEY (source_event_id) REFERENCES email_event (id) ON DELETE SET NULL;
ALTER TABLE email_webhook_inbox ADD CONSTRAINT fk_email_webhook_inbox_provider_id FOREIGN KEY (provider_id) REFERENCES email_provider (id) ON DELETE RESTRICT;
ALTER TABLE email_event ADD CONSTRAINT fk_email_event_message_id FOREIGN KEY (message_id) REFERENCES email_message (id) ON DELETE CASCADE;
ALTER TABLE email_event ADD CONSTRAINT fk_email_event_provider_id FOREIGN KEY (provider_id) REFERENCES email_provider (id) ON DELETE RESTRICT;
CREATE INDEX email_message_from_address ON email_message (from_address_id);
CREATE INDEX email_message_template ON email_message (template_id);
CREATE INDEX email_message_template_version ON email_message (template_version_id);
CREATE INDEX email_message_brand ON email_message (brand_id);
CREATE INDEX email_template_brand ON email_template (brand_id);
CREATE INDEX email_template_active_version ON email_template (active_version_id);
CREATE INDEX email_template_approved_version ON email_template (approved_version_id);
CREATE INDEX email_suppression_source_message ON email_suppression (source_message_id);
CREATE INDEX email_suppression_source_event ON email_suppression (source_event_id);
CREATE INDEX email_event_provider ON email_event (provider_id);