Files
qbit-email/docs/ddl/email-postgres.sql
james.maesandClaude Opus 5.5 6af5062aa7 feat: delivery tracking, suppression from events, and one-click unsubscribe
Phase (c) of docs/DESIGN.md: the email_webhook_inbox and email_event
tables; a /qqq/hooks/email route provider with SES (SNS signature and
certificate host rules, subscription confirmation), Postmark (basic
auth), SendGrid (ECDSA) and Mailgun (HMAC with replay protection)
verification into a durable inbox; a claim-safe inbox processor that
maps provider events to email_event rows, message status and
suppressions; RFC 8058 one-click unsubscribe with a confirmation page;
the Reprocess action; and event-backed home metrics.

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
2026-10-13 22:14:50 -05:00

226 lines
6.3 KiB
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_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),
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),
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)
);
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);