Files
sentio/migrations/001_initial_schema.sql
Truespar Team 0727e3319e Initial public release
Sentio SMTP: self-hosted, multi-tenant email infrastructure in Rust.

Gives an AI agent its own real email address. Inbound mail arrives
parsed, authenticated and spam-scored as a webhook; replies go out
DKIM-signed over the same REST API. Full SMTP on both sides, with
DKIM/SPF/DMARC/ARC, MTA-STS, DANE and a three-tier anti-spam pipeline.

Apache-2.0.
2026-08-23 23:44:38 +02:00

1598 lines
55 KiB
PL/PgSQL

-- =============================================================================
-- Sentio SMTP - initial schema
-- =============================================================================
-- Squashed from the incremental migration history at the point this repository
-- was opened. It is the schema those migrations converged on, not a replay of
-- them, so renamed columns and reworked indexes appear only in final form.
--
-- Partitions are deliberately NOT baked in. The four monthly-partitioned
-- tables (messages, message_events, engagement_events, message_attachments)
-- get their partitions from the DO block at the end, relative to the install
-- date, and from sentio_create_month_partitions() thereafter. Dumping the
-- partitions that existed when this file was generated would hand every future
-- install a set of stale months and none for the current one.
--
-- No BEGIN/COMMIT: sqlx already runs each migration inside a transaction, and
-- nesting one produces "there is already a transaction in progress" warnings.
-- =============================================================================
--
-- Name: sentio_create_month_partitions(integer); Type: FUNCTION; Schema: public; Owner: -
--
CREATE FUNCTION public.sentio_create_month_partitions(months_ahead integer DEFAULT 2) RETURNS integer
LANGUAGE plpgsql SECURITY DEFINER
SET "TimeZone" TO 'UTC'
SET search_path TO 'public', 'pg_temp'
AS $$
DECLARE
month_start DATE;
month_end DATE;
part_suffix TEXT;
tbl TEXT;
part_name TEXT;
created INT := 0;
BEGIN
IF months_ahead < 0 OR months_ahead > 24 THEN
RAISE EXCEPTION 'months_ahead out of range: %', months_ahead;
END IF;
FOR i IN 0..months_ahead LOOP
month_start := date_trunc('month', CURRENT_DATE + (i || ' months')::INTERVAL)::DATE;
month_end := (month_start + INTERVAL '1 month')::DATE;
part_suffix := to_char(month_start, 'YYYY_MM');
FOREACH tbl IN ARRAY ARRAY['messages','message_events','engagement_events','message_attachments']
LOOP
part_name := tbl || '_' || part_suffix;
IF NOT EXISTS (
SELECT 1 FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname = part_name AND n.nspname = 'public'
) THEN
EXECUTE format(
'CREATE TABLE %I PARTITION OF %I FOR VALUES FROM (%L) TO (%L)',
part_name, tbl, month_start, month_end
);
created := created + 1;
END IF;
END LOOP;
END LOOP;
RETURN created;
END;
$$;
--
-- Name: set_updated_at(); Type: FUNCTION; Schema: public; Owner: -
--
CREATE FUNCTION public.set_updated_at() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
NEW.updated_at = now();
RETURN NEW;
END;
$$;
--
-- Name: abuse_snapshots; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.abuse_snapshots (
ip inet NOT NULL,
snapshot_type text NOT NULL,
value text NOT NULL,
expires_at timestamp with time zone,
updated_at timestamp with time zone DEFAULT now() NOT NULL,
CONSTRAINT abuse_snapshots_snapshot_type_check CHECK ((snapshot_type = ANY (ARRAY['ban'::text, 'reputation'::text, 'whitelist'::text])))
);
--
-- Name: api_keys; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.api_keys (
id uuid DEFAULT gen_random_uuid() NOT NULL,
tenant_id uuid NOT NULL,
key_hash text NOT NULL,
key_prefix text NOT NULL,
name text NOT NULL,
scopes text[] DEFAULT '{}'::text[] NOT NULL,
last_used timestamp with time zone,
expires_at timestamp with time zone,
created_at timestamp with time zone DEFAULT now() NOT NULL
);
--
-- Name: dkim_keys; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.dkim_keys (
id uuid DEFAULT gen_random_uuid() NOT NULL,
domain_id uuid NOT NULL,
selector text NOT NULL,
algorithm text DEFAULT 'ed25519'::text NOT NULL,
private_key text NOT NULL,
public_key text NOT NULL,
key_size integer,
status text DEFAULT 'active'::text NOT NULL,
activated_at timestamp with time zone,
retired_at timestamp with time zone,
created_at timestamp with time zone DEFAULT now() NOT NULL,
CONSTRAINT dkim_keys_algorithm_check CHECK ((algorithm = ANY (ARRAY['ed25519'::text, 'rsa'::text]))),
CONSTRAINT dkim_keys_status_check CHECK ((status = ANY (ARRAY['active'::text, 'rotating'::text, 'retired'::text])))
);
--
-- Name: dmarc_reports; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.dmarc_reports (
id uuid DEFAULT gen_random_uuid() NOT NULL,
tenant_id uuid NOT NULL,
domain_id uuid NOT NULL,
direction text NOT NULL,
report_id text NOT NULL,
org_name text,
date_begin timestamp with time zone NOT NULL,
date_end timestamp with time zone NOT NULL,
source_ip inet,
report_xml text,
total_count integer DEFAULT 0 NOT NULL,
dkim_pass integer DEFAULT 0 NOT NULL,
dkim_fail integer DEFAULT 0 NOT NULL,
spf_pass integer DEFAULT 0 NOT NULL,
spf_fail integer DEFAULT 0 NOT NULL,
dmarc_pass integer DEFAULT 0 NOT NULL,
dmarc_fail integer DEFAULT 0 NOT NULL,
created_at timestamp with time zone DEFAULT now() NOT NULL,
CONSTRAINT dmarc_reports_direction_check CHECK ((direction = ANY (ARRAY['inbound'::text, 'outbound'::text])))
);
--
-- Name: domains; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.domains (
id uuid DEFAULT gen_random_uuid() NOT NULL,
tenant_id uuid NOT NULL,
domain_name text NOT NULL,
use_for_sending boolean DEFAULT true NOT NULL,
use_for_receiving boolean DEFAULT false NOT NULL,
status text DEFAULT 'pending'::text NOT NULL,
spf_status text DEFAULT 'pending'::text NOT NULL,
spf_error text,
dkim_status text DEFAULT 'pending'::text NOT NULL,
dkim_error text,
dmarc_status text DEFAULT 'pending'::text NOT NULL,
dmarc_error text,
mx_status text DEFAULT 'pending'::text NOT NULL,
mx_error text,
return_path_status text DEFAULT 'pending'::text NOT NULL,
return_path_error text,
dns_checked_at timestamp with time zone,
verification_token text NOT NULL,
verified_at timestamp with time zone,
created_at timestamp with time zone DEFAULT now() NOT NULL,
updated_at timestamp with time zone DEFAULT now() NOT NULL,
reject_unknown_recipients boolean DEFAULT false NOT NULL,
CONSTRAINT domains_dkim_status_check CHECK ((dkim_status = ANY (ARRAY['pending'::text, 'verified'::text, 'failed'::text]))),
CONSTRAINT domains_dmarc_status_check CHECK ((dmarc_status = ANY (ARRAY['pending'::text, 'verified'::text, 'failed'::text]))),
CONSTRAINT domains_mx_status_check CHECK ((mx_status = ANY (ARRAY['pending'::text, 'verified'::text, 'failed'::text, 'not_applicable'::text]))),
CONSTRAINT domains_return_path_status_check CHECK ((return_path_status = ANY (ARRAY['pending'::text, 'verified'::text, 'failed'::text, 'not_applicable'::text]))),
CONSTRAINT domains_spf_status_check CHECK ((spf_status = ANY (ARRAY['pending'::text, 'verified'::text, 'failed'::text]))),
CONSTRAINT domains_status_check CHECK ((status = ANY (ARRAY['pending'::text, 'verified'::text, 'failed'::text])))
);
--
-- Name: engagement_events; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.engagement_events (
id uuid DEFAULT uuidv7() NOT NULL,
message_id uuid NOT NULL,
tenant_id uuid NOT NULL,
event_type text NOT NULL,
ip_address inet,
user_agent text,
url text,
referer text,
client_name text,
client_version text,
device_type text,
os_name text,
os_version text,
is_bot boolean DEFAULT false NOT NULL,
proxy_open boolean DEFAULT false NOT NULL,
country_code character(2),
region text,
city text,
created_at timestamp with time zone DEFAULT now() NOT NULL,
CONSTRAINT engagement_events_device_type_check CHECK (((device_type IS NULL) OR (device_type = ANY (ARRAY['desktop'::text, 'mobile'::text, 'tablet'::text, 'unknown'::text])))),
CONSTRAINT engagement_events_event_type_check CHECK ((event_type = ANY (ARRAY['opened'::text, 'clicked'::text, 'unsubscribed'::text])))
)
PARTITION BY RANGE (created_at);
--
-- Name: error_events; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.error_events (
id uuid DEFAULT gen_random_uuid() NOT NULL,
tenant_id uuid NOT NULL,
severity text NOT NULL,
component text NOT NULL,
error_type text NOT NULL,
message text NOT NULL,
stack_trace text,
message_id uuid,
request_id text,
metadata jsonb DEFAULT '{}'::jsonb NOT NULL,
created_at timestamp with time zone DEFAULT now() NOT NULL,
CONSTRAINT error_events_component_check CHECK ((component = ANY (ARRAY['smtp_server'::text, 'smtp_client'::text, 'queue'::text, 'api'::text, 'webhooks'::text, 'auth'::text, 'abuse'::text, 'storage'::text, 'spam'::text, 'llm'::text]))),
CONSTRAINT error_events_error_type_check CHECK ((error_type = ANY (ARRAY['database'::text, 'redis'::text, 'queue'::text, 'storage'::text, 'smtp'::text, 'auth'::text, 'rate_limit'::text, 'not_found'::text, 'validation'::text, 'internal'::text, 'config'::text]))),
CONSTRAINT error_events_severity_check CHECK ((severity = ANY (ARRAY['warning'::text, 'error'::text, 'critical'::text])))
);
--
-- Name: fbl_reports; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.fbl_reports (
id uuid DEFAULT gen_random_uuid() NOT NULL,
tenant_id uuid NOT NULL,
original_message_id uuid,
original_message_id_hdr text,
complained_recipient text NOT NULL,
complaint_type text DEFAULT 'abuse'::text NOT NULL,
feedback_type text,
source_ip inet,
arrival_date timestamp with time zone,
report_raw text,
auto_suppressed boolean DEFAULT false NOT NULL,
processed_at timestamp with time zone,
created_at timestamp with time zone DEFAULT now() NOT NULL,
CONSTRAINT fbl_reports_complaint_type_check CHECK ((complaint_type = ANY (ARRAY['abuse'::text, 'fraud'::text, 'virus'::text, 'other'::text, 'not-spam'::text])))
);
--
-- Name: inbound_route_delivery_logs; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.inbound_route_delivery_logs (
id uuid DEFAULT gen_random_uuid() NOT NULL,
inbound_route_id uuid NOT NULL,
tenant_id uuid NOT NULL,
message_id uuid,
recipient text NOT NULL,
http_status integer,
response_body text,
attempt_number integer NOT NULL,
delivered_at timestamp with time zone,
failed_at timestamp with time zone,
error_message text,
created_at timestamp with time zone DEFAULT now() NOT NULL
);
--
-- Name: inbound_routes; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.inbound_routes (
id uuid DEFAULT gen_random_uuid() NOT NULL,
tenant_id uuid NOT NULL,
pattern text NOT NULL,
match_type text DEFAULT 'exact'::text NOT NULL,
webhook_url text NOT NULL,
priority integer DEFAULT 0 NOT NULL,
llm_classify boolean DEFAULT false NOT NULL,
auto_respond boolean DEFAULT false NOT NULL,
auto_respond_config jsonb DEFAULT '{}'::jsonb,
created_at timestamp with time zone DEFAULT now() NOT NULL,
CONSTRAINT inbound_routes_match_type_check CHECK ((match_type = ANY (ARRAY['exact'::text, 'regex'::text, 'domain'::text, 'catch_all'::text])))
);
--
-- Name: ip_pools; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.ip_pools (
id uuid DEFAULT gen_random_uuid() NOT NULL,
name text NOT NULL,
pool_type text DEFAULT 'shared'::text NOT NULL,
ips inet[] NOT NULL,
status text DEFAULT 'active'::text NOT NULL,
created_at timestamp with time zone DEFAULT now() NOT NULL,
CONSTRAINT ip_pools_pool_type_check CHECK ((pool_type = ANY (ARRAY['shared'::text, 'dedicated'::text]))),
CONSTRAINT ip_pools_status_check CHECK ((status = ANY (ARRAY['active'::text, 'draining'::text, 'inactive'::text])))
);
--
-- Name: ip_warmup_schedules; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.ip_warmup_schedules (
id uuid DEFAULT gen_random_uuid() NOT NULL,
ip_pool_id uuid NOT NULL,
tenant_id uuid NOT NULL,
start_date date NOT NULL,
current_day integer DEFAULT 0 NOT NULL,
daily_limit integer DEFAULT 100 NOT NULL,
daily_increase_pct numeric(5,2) DEFAULT 25.00 NOT NULL,
max_daily_limit integer DEFAULT 100000 NOT NULL,
isp_overrides jsonb DEFAULT '{}'::jsonb,
status text DEFAULT 'scheduled'::text NOT NULL,
completed_at timestamp with time zone,
created_at timestamp with time zone DEFAULT now() NOT NULL,
updated_at timestamp with time zone DEFAULT now() NOT NULL,
CONSTRAINT ip_warmup_schedules_status_check CHECK ((status = ANY (ARRAY['scheduled'::text, 'in_progress'::text, 'paused'::text, 'completed'::text])))
);
--
-- Name: mailboxes; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.mailboxes (
id uuid DEFAULT gen_random_uuid() NOT NULL,
domain_id uuid NOT NULL,
tenant_id uuid NOT NULL,
address text NOT NULL,
display_name text,
status text DEFAULT 'active'::text NOT NULL,
forward_to text[],
auto_reply boolean DEFAULT false NOT NULL,
auto_reply_subject text,
auto_reply_body text,
metadata jsonb DEFAULT '{}'::jsonb NOT NULL,
created_at timestamp with time zone DEFAULT now() NOT NULL,
updated_at timestamp with time zone DEFAULT now() NOT NULL,
CONSTRAINT mailboxes_status_check CHECK ((status = ANY (ARRAY['active'::text, 'disabled'::text])))
);
--
-- Name: message_attachments; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.message_attachments (
id uuid DEFAULT uuidv7() NOT NULL,
message_id uuid NOT NULL,
tenant_id uuid NOT NULL,
filename text NOT NULL,
content_type text NOT NULL,
size bigint NOT NULL,
content_id text,
disposition text DEFAULT 'attachment'::text NOT NULL,
blob_key character varying(512) CONSTRAINT message_attachments_seaweedfs_fid_not_null NOT NULL,
checksum_sha256 text,
scan_status text DEFAULT 'pending'::text NOT NULL,
scan_result text,
created_at timestamp with time zone DEFAULT now() NOT NULL,
CONSTRAINT message_attachments_disposition_check CHECK ((disposition = ANY (ARRAY['attachment'::text, 'inline'::text]))),
CONSTRAINT message_attachments_scan_status_check CHECK ((scan_status = ANY (ARRAY['pending'::text, 'clean'::text, 'infected'::text, 'error'::text])))
)
PARTITION BY RANGE (created_at);
--
-- Name: message_events; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.message_events (
id uuid DEFAULT uuidv7() NOT NULL,
message_id uuid NOT NULL,
tenant_id uuid NOT NULL,
event_type text NOT NULL,
smtp_response text,
remote_mta text,
diagnostic_code text,
bounce_class text,
retry_count integer,
next_retry_at timestamp with time zone,
source_ip inet,
destination_ip inet,
tls_version text,
created_at timestamp with time zone DEFAULT now() NOT NULL,
CONSTRAINT message_events_bounce_class_check CHECK (((bounce_class IS NULL) OR (bounce_class = ANY (ARRAY['hard'::text, 'soft'::text, 'block'::text])))),
CONSTRAINT message_events_event_type_check CHECK ((event_type = ANY (ARRAY['queued'::text, 'processed'::text, 'delivered'::text, 'deferred'::text, 'bounced'::text, 'dropped'::text, 'held'::text, 'released'::text])))
)
PARTITION BY RANGE (created_at);
--
-- Name: messages; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.messages (
id uuid DEFAULT uuidv7() NOT NULL,
tenant_id uuid NOT NULL,
domain_id uuid,
direction text DEFAULT 'outbound'::text NOT NULL,
envelope_from text NOT NULL,
envelope_to text[] NOT NULL,
header_from text,
header_to text[] DEFAULT '{}'::text[],
header_cc text[] DEFAULT '{}'::text[],
header_reply_to text,
subject text,
message_id_header text,
status text DEFAULT 'queued'::text NOT NULL,
tags text[] DEFAULT '{}'::text[],
metadata jsonb DEFAULT '{}'::jsonb,
message_size bigint,
raw_eml_key character varying(512),
spam_score double precision,
spam_action text,
send_at timestamp with time zone,
dsn_ret text,
dsn_envid text,
dsn_notify jsonb DEFAULT '{}'::jsonb NOT NULL,
dsn_orcpt jsonb DEFAULT '{}'::jsonb NOT NULL,
created_at timestamp with time zone DEFAULT now() NOT NULL,
delivered_at timestamp with time zone,
bounced_at timestamp with time zone,
llm_category text,
llm_summary text,
llm_classified_at timestamp with time zone,
bounce_class text,
smtp_code integer,
enhanced_status text,
diagnostic text,
failed_recipient text,
CONSTRAINT messages_bounce_class_check CHECK (((bounce_class IS NULL) OR (bounce_class = ANY (ARRAY['hard'::text, 'soft'::text, 'block'::text])))),
CONSTRAINT messages_direction_check CHECK ((direction = ANY (ARRAY['inbound'::text, 'outbound'::text]))),
CONSTRAINT messages_dsn_ret_check CHECK (((dsn_ret IS NULL) OR (dsn_ret = ANY (ARRAY['FULL'::text, 'HDRS'::text])))),
CONSTRAINT messages_llm_category_check CHECK (((llm_category IS NULL) OR (llm_category = ANY (ARRAY['conversation'::text, 'transactional'::text, 'marketing'::text, 'billing'::text, 'notification'::text, 'support'::text, 'spam'::text, 'threat'::text, 'other'::text])))),
CONSTRAINT messages_status_check CHECK ((status = ANY (ARRAY['queued'::text, 'processing'::text, 'delivered'::text, 'deferred'::text, 'bounced'::text, 'dropped'::text, 'scheduled'::text, 'held'::text])))
)
PARTITION BY RANGE (created_at);
--
-- Name: oauth_authorization_codes; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.oauth_authorization_codes (
code text NOT NULL,
client_id uuid NOT NULL,
tenant_id uuid NOT NULL,
redirect_uri text NOT NULL,
scopes text[] DEFAULT '{}'::text[] NOT NULL,
code_challenge text,
code_challenge_method text,
expires_at timestamp with time zone NOT NULL,
created_at timestamp with time zone DEFAULT now() NOT NULL,
CONSTRAINT oauth_authorization_codes_code_challenge_method_check CHECK (((code_challenge_method IS NULL) OR (code_challenge_method = ANY (ARRAY['S256'::text, 'plain'::text]))))
);
--
-- Name: oauth_clients; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.oauth_clients (
id uuid DEFAULT gen_random_uuid() NOT NULL,
tenant_id uuid NOT NULL,
client_id text NOT NULL,
client_secret_hash text NOT NULL,
name text NOT NULL,
redirect_uris text[] DEFAULT '{}'::text[] NOT NULL,
grant_types text[] DEFAULT '{client_credentials}'::text[] NOT NULL,
scopes text[] DEFAULT '{}'::text[] NOT NULL,
status text DEFAULT 'active'::text NOT NULL,
created_at timestamp with time zone DEFAULT now() NOT NULL,
updated_at timestamp with time zone DEFAULT now() NOT NULL,
CONSTRAINT oauth_clients_status_check CHECK ((status = ANY (ARRAY['active'::text, 'revoked'::text])))
);
--
-- Name: oauth_tokens; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.oauth_tokens (
id uuid DEFAULT gen_random_uuid() NOT NULL,
client_id uuid NOT NULL,
tenant_id uuid NOT NULL,
token_hash text NOT NULL,
token_type text NOT NULL,
scopes text[] DEFAULT '{}'::text[] NOT NULL,
expires_at timestamp with time zone NOT NULL,
revoked_at timestamp with time zone,
created_at timestamp with time zone DEFAULT now() NOT NULL,
CONSTRAINT oauth_tokens_token_type_check CHECK ((token_type = ANY (ARRAY['access'::text, 'refresh'::text])))
);
--
-- Name: pending_uploads; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.pending_uploads (
id uuid DEFAULT gen_random_uuid() NOT NULL,
tenant_id uuid NOT NULL,
blob_key character varying(512) CONSTRAINT pending_uploads_seaweedfs_fid_not_null NOT NULL,
filename text NOT NULL,
content_type text NOT NULL,
size bigint NOT NULL,
checksum_sha256 text,
scan_status text DEFAULT 'pending'::text NOT NULL,
scan_result text,
claimed boolean DEFAULT false NOT NULL,
expires_at timestamp with time zone DEFAULT (now() + '24:00:00'::interval) NOT NULL,
created_at timestamp with time zone DEFAULT now() NOT NULL,
CONSTRAINT pending_uploads_scan_status_check CHECK ((scan_status = ANY (ARRAY['pending'::text, 'clean'::text, 'infected'::text, 'error'::text])))
);
--
-- Name: smtp_credentials; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.smtp_credentials (
id uuid DEFAULT gen_random_uuid() NOT NULL,
tenant_id uuid NOT NULL,
username text NOT NULL,
password_hash text NOT NULL,
scram_stored_key text,
scram_server_key text,
scram_salt text,
scram_iterations integer DEFAULT 4096,
mechanisms text[] DEFAULT '{PLAIN,LOGIN}'::text[] NOT NULL,
enabled boolean DEFAULT true NOT NULL,
last_used timestamp with time zone,
created_at timestamp with time zone DEFAULT now() NOT NULL,
updated_at timestamp with time zone DEFAULT now() NOT NULL
);
--
-- Name: suppressions; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.suppressions (
id uuid DEFAULT gen_random_uuid() NOT NULL,
tenant_id uuid NOT NULL,
email text NOT NULL,
reason text NOT NULL,
source_event_id uuid,
created_at timestamp with time zone DEFAULT now() NOT NULL,
CONSTRAINT suppressions_reason_check CHECK ((reason = ANY (ARRAY['hard_bounce'::text, 'complaint'::text, 'manual'::text, 'unsubscribe'::text])))
);
--
-- Name: tenant_ip_assignments; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.tenant_ip_assignments (
tenant_id uuid NOT NULL,
ip_pool_id uuid NOT NULL,
priority integer DEFAULT 0 NOT NULL,
created_at timestamp with time zone DEFAULT now() NOT NULL
);
--
-- Name: tenants; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.tenants (
id uuid DEFAULT gen_random_uuid() NOT NULL,
name text NOT NULL,
tier text DEFAULT 'shared_standard'::text NOT NULL,
status text DEFAULT 'active'::text NOT NULL,
config jsonb DEFAULT '{}'::jsonb NOT NULL,
rate_limits jsonb DEFAULT '{}'::jsonb NOT NULL,
message_retention_days integer DEFAULT 90 NOT NULL,
raw_eml_retention_days integer DEFAULT 30 NOT NULL,
attachment_retention_days integer DEFAULT 30 NOT NULL,
created_at timestamp with time zone DEFAULT now() NOT NULL,
updated_at timestamp with time zone DEFAULT now() NOT NULL,
verp_enabled boolean DEFAULT false NOT NULL,
CONSTRAINT tenants_status_check CHECK ((status = ANY (ARRAY['active'::text, 'suspended'::text, 'deleted'::text]))),
CONSTRAINT tenants_tier_check CHECK ((tier = ANY (ARRAY['dedicated'::text, 'shared_premium'::text, 'shared_standard'::text])))
);
--
-- Name: tlsrpt_reports; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.tlsrpt_reports (
id uuid DEFAULT gen_random_uuid() NOT NULL,
tenant_id uuid NOT NULL,
domain_id uuid NOT NULL,
direction text NOT NULL,
report_id text NOT NULL,
org_name text,
date_begin timestamp with time zone NOT NULL,
date_end timestamp with time zone NOT NULL,
policy_type text NOT NULL,
policy_domain text,
total_success integer DEFAULT 0 NOT NULL,
total_failure integer DEFAULT 0 NOT NULL,
failure_details jsonb DEFAULT '{}'::jsonb,
created_at timestamp with time zone DEFAULT now() NOT NULL,
CONSTRAINT tlsrpt_reports_direction_check CHECK ((direction = ANY (ARRAY['inbound'::text, 'outbound'::text]))),
CONSTRAINT tlsrpt_reports_policy_type_check CHECK ((policy_type = ANY (ARRAY['tlsa'::text, 'sts'::text, 'no-policy-found'::text])))
);
--
-- Name: tracking_certificates; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.tracking_certificates (
id uuid DEFAULT gen_random_uuid() NOT NULL,
tracking_domain_id uuid NOT NULL,
certificate text NOT NULL,
intermediaries text,
private_key text NOT NULL,
expires_at timestamp with time zone NOT NULL,
renew_after timestamp with time zone NOT NULL,
created_at timestamp with time zone DEFAULT now() NOT NULL
);
--
-- Name: tracking_domains; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.tracking_domains (
id uuid DEFAULT gen_random_uuid() NOT NULL,
tenant_id uuid NOT NULL,
domain_id uuid,
domain_name text NOT NULL,
cname_target text NOT NULL,
dns_status text DEFAULT 'pending'::text NOT NULL,
dns_error text,
dns_checked_at timestamp with time zone,
ssl_enabled boolean DEFAULT true NOT NULL,
track_opens boolean DEFAULT true NOT NULL,
track_clicks boolean DEFAULT true NOT NULL,
created_at timestamp with time zone DEFAULT now() NOT NULL,
updated_at timestamp with time zone DEFAULT now() NOT NULL,
CONSTRAINT tracking_domains_dns_status_check CHECK ((dns_status = ANY (ARRAY['pending'::text, 'verified'::text, 'failed'::text])))
);
--
-- Name: webhook_delivery_logs; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.webhook_delivery_logs (
id uuid DEFAULT gen_random_uuid() NOT NULL,
webhook_id uuid NOT NULL,
tenant_id uuid NOT NULL,
event_type text NOT NULL,
payload jsonb NOT NULL,
http_status integer,
response_body text,
attempt_number integer DEFAULT 1 NOT NULL,
delivered_at timestamp with time zone,
failed_at timestamp with time zone,
error_message text,
created_at timestamp with time zone DEFAULT now() NOT NULL
);
--
-- Name: webhooks; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.webhooks (
id uuid DEFAULT gen_random_uuid() NOT NULL,
tenant_id uuid NOT NULL,
url text NOT NULL,
event_types text[] NOT NULL,
signing_secret text NOT NULL,
status text DEFAULT 'active'::text NOT NULL,
failure_count integer DEFAULT 0 NOT NULL,
last_success_at timestamp with time zone,
last_failure_at timestamp with time zone,
created_at timestamp with time zone DEFAULT now() NOT NULL,
updated_at timestamp with time zone DEFAULT now() NOT NULL,
CONSTRAINT webhooks_status_check CHECK ((status = ANY (ARRAY['active'::text, 'paused'::text, 'disabled'::text])))
);
--
-- Name: abuse_snapshots abuse_snapshots_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.abuse_snapshots
ADD CONSTRAINT abuse_snapshots_pkey PRIMARY KEY (ip, snapshot_type);
--
-- Name: api_keys api_keys_key_hash_key; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.api_keys
ADD CONSTRAINT api_keys_key_hash_key UNIQUE (key_hash);
--
-- Name: api_keys api_keys_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.api_keys
ADD CONSTRAINT api_keys_pkey PRIMARY KEY (id);
--
-- Name: dkim_keys dkim_keys_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.dkim_keys
ADD CONSTRAINT dkim_keys_pkey PRIMARY KEY (id);
--
-- Name: dmarc_reports dmarc_reports_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.dmarc_reports
ADD CONSTRAINT dmarc_reports_pkey PRIMARY KEY (id);
--
-- Name: dmarc_reports dmarc_reports_report_id_org_name_key; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.dmarc_reports
ADD CONSTRAINT dmarc_reports_report_id_org_name_key UNIQUE (report_id, org_name);
--
-- Name: domains domains_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.domains
ADD CONSTRAINT domains_pkey PRIMARY KEY (id);
--
-- Name: domains domains_tenant_id_domain_name_key; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.domains
ADD CONSTRAINT domains_tenant_id_domain_name_key UNIQUE (tenant_id, domain_name);
--
-- Name: engagement_events engagement_events_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.engagement_events
ADD CONSTRAINT engagement_events_pkey PRIMARY KEY (id, created_at);
--
-- Name: error_events error_events_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.error_events
ADD CONSTRAINT error_events_pkey PRIMARY KEY (id);
--
-- Name: fbl_reports fbl_reports_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.fbl_reports
ADD CONSTRAINT fbl_reports_pkey PRIMARY KEY (id);
--
-- Name: inbound_route_delivery_logs inbound_route_delivery_logs_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.inbound_route_delivery_logs
ADD CONSTRAINT inbound_route_delivery_logs_pkey PRIMARY KEY (id);
--
-- Name: inbound_routes inbound_routes_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.inbound_routes
ADD CONSTRAINT inbound_routes_pkey PRIMARY KEY (id);
--
-- Name: ip_pools ip_pools_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.ip_pools
ADD CONSTRAINT ip_pools_pkey PRIMARY KEY (id);
--
-- Name: ip_warmup_schedules ip_warmup_schedules_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.ip_warmup_schedules
ADD CONSTRAINT ip_warmup_schedules_pkey PRIMARY KEY (id);
--
-- Name: mailboxes mailboxes_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.mailboxes
ADD CONSTRAINT mailboxes_pkey PRIMARY KEY (id);
--
-- Name: message_attachments message_attachments_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.message_attachments
ADD CONSTRAINT message_attachments_pkey PRIMARY KEY (id, created_at);
--
-- Name: message_events message_events_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.message_events
ADD CONSTRAINT message_events_pkey PRIMARY KEY (id, created_at);
--
-- Name: messages messages_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.messages
ADD CONSTRAINT messages_pkey PRIMARY KEY (id, created_at);
--
-- Name: oauth_authorization_codes oauth_authorization_codes_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.oauth_authorization_codes
ADD CONSTRAINT oauth_authorization_codes_pkey PRIMARY KEY (code);
--
-- Name: oauth_clients oauth_clients_client_id_key; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.oauth_clients
ADD CONSTRAINT oauth_clients_client_id_key UNIQUE (client_id);
--
-- Name: oauth_clients oauth_clients_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.oauth_clients
ADD CONSTRAINT oauth_clients_pkey PRIMARY KEY (id);
--
-- Name: oauth_tokens oauth_tokens_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.oauth_tokens
ADD CONSTRAINT oauth_tokens_pkey PRIMARY KEY (id);
--
-- Name: oauth_tokens oauth_tokens_token_hash_key; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.oauth_tokens
ADD CONSTRAINT oauth_tokens_token_hash_key UNIQUE (token_hash);
--
-- Name: pending_uploads pending_uploads_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.pending_uploads
ADD CONSTRAINT pending_uploads_pkey PRIMARY KEY (id);
--
-- Name: smtp_credentials smtp_credentials_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.smtp_credentials
ADD CONSTRAINT smtp_credentials_pkey PRIMARY KEY (id);
--
-- Name: smtp_credentials smtp_credentials_tenant_id_username_key; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.smtp_credentials
ADD CONSTRAINT smtp_credentials_tenant_id_username_key UNIQUE (tenant_id, username);
--
-- Name: suppressions suppressions_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.suppressions
ADD CONSTRAINT suppressions_pkey PRIMARY KEY (id);
--
-- Name: suppressions suppressions_tenant_id_email_key; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.suppressions
ADD CONSTRAINT suppressions_tenant_id_email_key UNIQUE (tenant_id, email);
--
-- Name: tenant_ip_assignments tenant_ip_assignments_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.tenant_ip_assignments
ADD CONSTRAINT tenant_ip_assignments_pkey PRIMARY KEY (tenant_id, ip_pool_id);
--
-- Name: tenants tenants_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.tenants
ADD CONSTRAINT tenants_pkey PRIMARY KEY (id);
--
-- Name: tlsrpt_reports tlsrpt_reports_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.tlsrpt_reports
ADD CONSTRAINT tlsrpt_reports_pkey PRIMARY KEY (id);
--
-- Name: tlsrpt_reports tlsrpt_reports_report_id_org_name_key; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.tlsrpt_reports
ADD CONSTRAINT tlsrpt_reports_report_id_org_name_key UNIQUE (report_id, org_name);
--
-- Name: tracking_certificates tracking_certificates_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.tracking_certificates
ADD CONSTRAINT tracking_certificates_pkey PRIMARY KEY (id);
--
-- Name: tracking_domains tracking_domains_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.tracking_domains
ADD CONSTRAINT tracking_domains_pkey PRIMARY KEY (id);
--
-- Name: tracking_domains tracking_domains_tenant_id_domain_name_key; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.tracking_domains
ADD CONSTRAINT tracking_domains_tenant_id_domain_name_key UNIQUE (tenant_id, domain_name);
--
-- Name: webhook_delivery_logs webhook_delivery_logs_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.webhook_delivery_logs
ADD CONSTRAINT webhook_delivery_logs_pkey PRIMARY KEY (id);
--
-- Name: webhooks webhooks_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.webhooks
ADD CONSTRAINT webhooks_pkey PRIMARY KEY (id);
--
-- Name: dkim_keys_active_selector_uniq; Type: INDEX; Schema: public; Owner: -
--
CREATE UNIQUE INDEX dkim_keys_active_selector_uniq ON public.dkim_keys USING btree (domain_id, selector) WHERE (status = 'active'::text);
--
-- Name: idx_engagement_message; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_engagement_message ON ONLY public.engagement_events USING btree (message_id);
--
-- Name: idx_engagement_client; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_engagement_client ON ONLY public.engagement_events USING btree (tenant_id, client_name) WHERE (client_name IS NOT NULL);
--
-- Name: idx_engagement_country; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_engagement_country ON ONLY public.engagement_events USING btree (tenant_id, country_code) WHERE (country_code IS NOT NULL);
--
-- Name: idx_engagement_tenant_type; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_engagement_tenant_type ON ONLY public.engagement_events USING btree (tenant_id, event_type, created_at DESC);
--
-- Name: idx_abuse_snapshots_type; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_abuse_snapshots_type ON public.abuse_snapshots USING btree (snapshot_type);
--
-- Name: idx_api_keys_prefix; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_api_keys_prefix ON public.api_keys USING btree (key_prefix);
--
-- Name: idx_api_keys_tenant; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_api_keys_tenant ON public.api_keys USING btree (tenant_id);
--
-- Name: idx_attachments_message; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_attachments_message ON ONLY public.message_attachments USING btree (message_id);
--
-- Name: idx_attachments_tenant; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_attachments_tenant ON ONLY public.message_attachments USING btree (tenant_id);
--
-- Name: idx_dkim_keys_active_lookup; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_dkim_keys_active_lookup ON public.dkim_keys USING btree (domain_id, status) WHERE (status = 'active'::text);
--
-- Name: idx_dkim_keys_domain; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_dkim_keys_domain ON public.dkim_keys USING btree (domain_id);
--
-- Name: idx_dmarc_reports_domain; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_dmarc_reports_domain ON public.dmarc_reports USING btree (domain_id, date_begin DESC);
--
-- Name: idx_dmarc_reports_tenant; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_dmarc_reports_tenant ON public.dmarc_reports USING btree (tenant_id, date_begin DESC);
--
-- Name: idx_domains_name; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_domains_name ON public.domains USING btree (domain_name);
--
-- Name: idx_domains_name_unique; Type: INDEX; Schema: public; Owner: -
--
CREATE UNIQUE INDEX idx_domains_name_unique ON public.domains USING btree (domain_name) WHERE (status = 'verified'::text);
--
-- Name: idx_domains_tenant; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_domains_tenant ON public.domains USING btree (tenant_id);
--
-- Name: idx_error_events_component_created; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_error_events_component_created ON public.error_events USING btree (component, created_at DESC);
--
-- Name: idx_error_events_message_id; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_error_events_message_id ON public.error_events USING btree (message_id) WHERE (message_id IS NOT NULL);
--
-- Name: idx_error_events_severity_created; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_error_events_severity_created ON public.error_events USING btree (severity, created_at DESC);
--
-- Name: idx_error_events_tenant_created; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_error_events_tenant_created ON public.error_events USING btree (tenant_id, created_at DESC);
--
-- Name: idx_events_message; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_events_message ON ONLY public.message_events USING btree (message_id);
--
-- Name: idx_events_tenant_type; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_events_tenant_type ON ONLY public.message_events USING btree (tenant_id, event_type, created_at DESC);
--
-- Name: idx_fbl_message; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_fbl_message ON public.fbl_reports USING btree (original_message_id) WHERE (original_message_id IS NOT NULL);
--
-- Name: idx_fbl_recipient; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_fbl_recipient ON public.fbl_reports USING btree (complained_recipient);
--
-- Name: idx_fbl_tenant; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_fbl_tenant ON public.fbl_reports USING btree (tenant_id, created_at DESC);
--
-- Name: idx_inbound_route_logs_message; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_inbound_route_logs_message ON public.inbound_route_delivery_logs USING btree (message_id) WHERE (message_id IS NOT NULL);
--
-- Name: idx_inbound_route_logs_route; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_inbound_route_logs_route ON public.inbound_route_delivery_logs USING btree (inbound_route_id, created_at DESC);
--
-- Name: idx_inbound_route_logs_tenant; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_inbound_route_logs_tenant ON public.inbound_route_delivery_logs USING btree (tenant_id, created_at DESC);
--
-- Name: idx_inbound_routes_tenant; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_inbound_routes_tenant ON public.inbound_routes USING btree (tenant_id);
--
-- Name: idx_mailboxes_address; Type: INDEX; Schema: public; Owner: -
--
CREATE UNIQUE INDEX idx_mailboxes_address ON public.mailboxes USING btree (domain_id, lower(address));
--
-- Name: idx_mailboxes_domain; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_mailboxes_domain ON public.mailboxes USING btree (domain_id);
--
-- Name: idx_mailboxes_tenant; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_mailboxes_tenant ON public.mailboxes USING btree (tenant_id);
--
-- Name: idx_messages_domain; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_messages_domain ON ONLY public.messages USING btree (domain_id) WHERE (domain_id IS NOT NULL);
--
-- Name: idx_messages_message_id; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_messages_message_id ON ONLY public.messages USING btree (message_id_header) WHERE (message_id_header IS NOT NULL);
--
-- Name: idx_messages_scheduled; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_messages_scheduled ON ONLY public.messages USING btree (send_at) WHERE (status = 'scheduled'::text);
--
-- Name: idx_messages_status; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_messages_status ON ONLY public.messages USING btree (status) WHERE (status <> ALL (ARRAY['delivered'::text, 'dropped'::text]));
--
-- Name: idx_messages_tenant_created; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_messages_tenant_created ON ONLY public.messages USING btree (tenant_id, created_at DESC);
--
-- Name: idx_oauth_clients_tenant; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_oauth_clients_tenant ON public.oauth_clients USING btree (tenant_id);
--
-- Name: idx_oauth_codes_expires; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_oauth_codes_expires ON public.oauth_authorization_codes USING btree (expires_at);
--
-- Name: idx_oauth_tokens_client; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_oauth_tokens_client ON public.oauth_tokens USING btree (client_id);
--
-- Name: idx_oauth_tokens_expires; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_oauth_tokens_expires ON public.oauth_tokens USING btree (expires_at) WHERE (revoked_at IS NULL);
--
-- Name: idx_pending_uploads_orphans; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_pending_uploads_orphans ON public.pending_uploads USING btree (expires_at) WHERE (claimed = false);
--
-- Name: idx_pending_uploads_tenant; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_pending_uploads_tenant ON public.pending_uploads USING btree (tenant_id);
--
-- Name: idx_smtp_creds_lookup; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_smtp_creds_lookup ON public.smtp_credentials USING btree (username) WHERE (enabled = true);
--
-- Name: idx_smtp_creds_tenant; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_smtp_creds_tenant ON public.smtp_credentials USING btree (tenant_id);
--
-- Name: idx_suppressions_lookup; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_suppressions_lookup ON public.suppressions USING btree (tenant_id, email);
--
-- Name: idx_tlsrpt_domain; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_tlsrpt_domain ON public.tlsrpt_reports USING btree (domain_id, date_begin DESC);
--
-- Name: idx_tlsrpt_tenant; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_tlsrpt_tenant ON public.tlsrpt_reports USING btree (tenant_id, date_begin DESC);
--
-- Name: idx_tracking_certs_domain; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_tracking_certs_domain ON public.tracking_certificates USING btree (tracking_domain_id);
--
-- Name: idx_tracking_certs_renew; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_tracking_certs_renew ON public.tracking_certificates USING btree (renew_after);
--
-- Name: idx_tracking_domains_name; Type: INDEX; Schema: public; Owner: -
--
CREATE UNIQUE INDEX idx_tracking_domains_name ON public.tracking_domains USING btree (domain_name);
--
-- Name: idx_tracking_domains_tenant; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_tracking_domains_tenant ON public.tracking_domains USING btree (tenant_id);
--
-- Name: idx_warmup_active; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_warmup_active ON public.ip_warmup_schedules USING btree (status) WHERE (status = ANY (ARRAY['scheduled'::text, 'in_progress'::text]));
--
-- Name: idx_warmup_pool; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_warmup_pool ON public.ip_warmup_schedules USING btree (ip_pool_id);
--
-- Name: idx_warmup_tenant; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_warmup_tenant ON public.ip_warmup_schedules USING btree (tenant_id);
--
-- Name: idx_webhook_logs_tenant; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_webhook_logs_tenant ON public.webhook_delivery_logs USING btree (tenant_id, created_at DESC);
--
-- Name: idx_webhook_logs_webhook; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_webhook_logs_webhook ON public.webhook_delivery_logs USING btree (webhook_id, created_at DESC);
--
-- Name: idx_webhooks_tenant; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX idx_webhooks_tenant ON public.webhooks USING btree (tenant_id);
--
-- Name: domains trg_domains_updated_at; Type: TRIGGER; Schema: public; Owner: -
--
CREATE TRIGGER trg_domains_updated_at BEFORE UPDATE ON public.domains FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();
--
-- Name: oauth_clients trg_oauth_clients_updated_at; Type: TRIGGER; Schema: public; Owner: -
--
CREATE TRIGGER trg_oauth_clients_updated_at BEFORE UPDATE ON public.oauth_clients FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();
--
-- Name: smtp_credentials trg_smtp_credentials_updated_at; Type: TRIGGER; Schema: public; Owner: -
--
CREATE TRIGGER trg_smtp_credentials_updated_at BEFORE UPDATE ON public.smtp_credentials FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();
--
-- Name: tenants trg_tenants_updated_at; Type: TRIGGER; Schema: public; Owner: -
--
CREATE TRIGGER trg_tenants_updated_at BEFORE UPDATE ON public.tenants FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();
--
-- Name: tracking_domains trg_tracking_domains_updated_at; Type: TRIGGER; Schema: public; Owner: -
--
CREATE TRIGGER trg_tracking_domains_updated_at BEFORE UPDATE ON public.tracking_domains FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();
--
-- Name: ip_warmup_schedules trg_warmup_updated_at; Type: TRIGGER; Schema: public; Owner: -
--
CREATE TRIGGER trg_warmup_updated_at BEFORE UPDATE ON public.ip_warmup_schedules FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();
--
-- Name: webhooks trg_webhooks_updated_at; Type: TRIGGER; Schema: public; Owner: -
--
CREATE TRIGGER trg_webhooks_updated_at BEFORE UPDATE ON public.webhooks FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();
--
-- Name: api_keys api_keys_tenant_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.api_keys
ADD CONSTRAINT api_keys_tenant_id_fkey FOREIGN KEY (tenant_id) REFERENCES public.tenants(id) ON DELETE CASCADE;
--
-- Name: dkim_keys dkim_keys_domain_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.dkim_keys
ADD CONSTRAINT dkim_keys_domain_id_fkey FOREIGN KEY (domain_id) REFERENCES public.domains(id) ON DELETE CASCADE;
--
-- Name: dmarc_reports dmarc_reports_domain_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.dmarc_reports
ADD CONSTRAINT dmarc_reports_domain_id_fkey FOREIGN KEY (domain_id) REFERENCES public.domains(id) ON DELETE CASCADE;
--
-- Name: dmarc_reports dmarc_reports_tenant_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.dmarc_reports
ADD CONSTRAINT dmarc_reports_tenant_id_fkey FOREIGN KEY (tenant_id) REFERENCES public.tenants(id) ON DELETE CASCADE;
--
-- Name: domains domains_tenant_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.domains
ADD CONSTRAINT domains_tenant_id_fkey FOREIGN KEY (tenant_id) REFERENCES public.tenants(id) ON DELETE CASCADE;
--
-- Name: error_events error_events_tenant_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.error_events
ADD CONSTRAINT error_events_tenant_id_fkey FOREIGN KEY (tenant_id) REFERENCES public.tenants(id) ON DELETE CASCADE;
--
-- Name: fbl_reports fbl_reports_tenant_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.fbl_reports
ADD CONSTRAINT fbl_reports_tenant_id_fkey FOREIGN KEY (tenant_id) REFERENCES public.tenants(id) ON DELETE CASCADE;
--
-- Name: inbound_route_delivery_logs inbound_route_delivery_logs_inbound_route_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.inbound_route_delivery_logs
ADD CONSTRAINT inbound_route_delivery_logs_inbound_route_id_fkey FOREIGN KEY (inbound_route_id) REFERENCES public.inbound_routes(id) ON DELETE CASCADE;
--
-- Name: inbound_route_delivery_logs inbound_route_delivery_logs_tenant_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.inbound_route_delivery_logs
ADD CONSTRAINT inbound_route_delivery_logs_tenant_id_fkey FOREIGN KEY (tenant_id) REFERENCES public.tenants(id) ON DELETE CASCADE;
--
-- Name: inbound_routes inbound_routes_tenant_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.inbound_routes
ADD CONSTRAINT inbound_routes_tenant_id_fkey FOREIGN KEY (tenant_id) REFERENCES public.tenants(id) ON DELETE CASCADE;
--
-- Name: ip_warmup_schedules ip_warmup_schedules_ip_pool_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.ip_warmup_schedules
ADD CONSTRAINT ip_warmup_schedules_ip_pool_id_fkey FOREIGN KEY (ip_pool_id) REFERENCES public.ip_pools(id) ON DELETE CASCADE;
--
-- Name: ip_warmup_schedules ip_warmup_schedules_tenant_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.ip_warmup_schedules
ADD CONSTRAINT ip_warmup_schedules_tenant_id_fkey FOREIGN KEY (tenant_id) REFERENCES public.tenants(id) ON DELETE CASCADE;
--
-- Name: mailboxes mailboxes_domain_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.mailboxes
ADD CONSTRAINT mailboxes_domain_id_fkey FOREIGN KEY (domain_id) REFERENCES public.domains(id) ON DELETE CASCADE;
--
-- Name: mailboxes mailboxes_tenant_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.mailboxes
ADD CONSTRAINT mailboxes_tenant_id_fkey FOREIGN KEY (tenant_id) REFERENCES public.tenants(id) ON DELETE CASCADE;
--
-- Name: oauth_authorization_codes oauth_authorization_codes_client_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.oauth_authorization_codes
ADD CONSTRAINT oauth_authorization_codes_client_id_fkey FOREIGN KEY (client_id) REFERENCES public.oauth_clients(id) ON DELETE CASCADE;
--
-- Name: oauth_authorization_codes oauth_authorization_codes_tenant_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.oauth_authorization_codes
ADD CONSTRAINT oauth_authorization_codes_tenant_id_fkey FOREIGN KEY (tenant_id) REFERENCES public.tenants(id) ON DELETE CASCADE;
--
-- Name: oauth_clients oauth_clients_tenant_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.oauth_clients
ADD CONSTRAINT oauth_clients_tenant_id_fkey FOREIGN KEY (tenant_id) REFERENCES public.tenants(id) ON DELETE CASCADE;
--
-- Name: oauth_tokens oauth_tokens_client_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.oauth_tokens
ADD CONSTRAINT oauth_tokens_client_id_fkey FOREIGN KEY (client_id) REFERENCES public.oauth_clients(id) ON DELETE CASCADE;
--
-- Name: oauth_tokens oauth_tokens_tenant_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.oauth_tokens
ADD CONSTRAINT oauth_tokens_tenant_id_fkey FOREIGN KEY (tenant_id) REFERENCES public.tenants(id) ON DELETE CASCADE;
--
-- Name: pending_uploads pending_uploads_tenant_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.pending_uploads
ADD CONSTRAINT pending_uploads_tenant_id_fkey FOREIGN KEY (tenant_id) REFERENCES public.tenants(id) ON DELETE CASCADE;
--
-- Name: smtp_credentials smtp_credentials_tenant_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.smtp_credentials
ADD CONSTRAINT smtp_credentials_tenant_id_fkey FOREIGN KEY (tenant_id) REFERENCES public.tenants(id) ON DELETE CASCADE;
--
-- Name: suppressions suppressions_tenant_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.suppressions
ADD CONSTRAINT suppressions_tenant_id_fkey FOREIGN KEY (tenant_id) REFERENCES public.tenants(id) ON DELETE CASCADE;
--
-- Name: tenant_ip_assignments tenant_ip_assignments_ip_pool_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.tenant_ip_assignments
ADD CONSTRAINT tenant_ip_assignments_ip_pool_id_fkey FOREIGN KEY (ip_pool_id) REFERENCES public.ip_pools(id) ON DELETE CASCADE;
--
-- Name: tenant_ip_assignments tenant_ip_assignments_tenant_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.tenant_ip_assignments
ADD CONSTRAINT tenant_ip_assignments_tenant_id_fkey FOREIGN KEY (tenant_id) REFERENCES public.tenants(id) ON DELETE CASCADE;
--
-- Name: tlsrpt_reports tlsrpt_reports_domain_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.tlsrpt_reports
ADD CONSTRAINT tlsrpt_reports_domain_id_fkey FOREIGN KEY (domain_id) REFERENCES public.domains(id) ON DELETE CASCADE;
--
-- Name: tlsrpt_reports tlsrpt_reports_tenant_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.tlsrpt_reports
ADD CONSTRAINT tlsrpt_reports_tenant_id_fkey FOREIGN KEY (tenant_id) REFERENCES public.tenants(id) ON DELETE CASCADE;
--
-- Name: tracking_certificates tracking_certificates_tracking_domain_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.tracking_certificates
ADD CONSTRAINT tracking_certificates_tracking_domain_id_fkey FOREIGN KEY (tracking_domain_id) REFERENCES public.tracking_domains(id) ON DELETE CASCADE;
--
-- Name: tracking_domains tracking_domains_domain_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.tracking_domains
ADD CONSTRAINT tracking_domains_domain_id_fkey FOREIGN KEY (domain_id) REFERENCES public.domains(id) ON DELETE SET NULL;
--
-- Name: tracking_domains tracking_domains_tenant_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.tracking_domains
ADD CONSTRAINT tracking_domains_tenant_id_fkey FOREIGN KEY (tenant_id) REFERENCES public.tenants(id) ON DELETE CASCADE;
--
-- Name: webhook_delivery_logs webhook_delivery_logs_webhook_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.webhook_delivery_logs
ADD CONSTRAINT webhook_delivery_logs_webhook_id_fkey FOREIGN KEY (webhook_id) REFERENCES public.webhooks(id) ON DELETE CASCADE;
--
-- Name: webhooks webhooks_tenant_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.webhooks
ADD CONSTRAINT webhooks_tenant_id_fkey FOREIGN KEY (tenant_id) REFERENCES public.tenants(id) ON DELETE CASCADE;
-- =============================================================================
-- Monthly partitions: current month plus the next two
-- =============================================================================
-- The runtime extends this window by calling sentio_create_month_partitions().
--
-- Pinned to UTC to match sentio_create_month_partitions(). Both compute month
-- boundaries as DATEs that Postgres then resolves against the prevailing zone;
-- if the two disagree the bounds land an hour or two apart and the function
-- fails with "would overlap" the first time it tries to extend this window.
SET LOCAL TimeZone = 'UTC';
DO $$
DECLARE
month_start DATE;
month_end DATE;
part_suffix TEXT;
tbl TEXT;
BEGIN
FOR i IN 0..2 LOOP
month_start := date_trunc('month', CURRENT_DATE + (i || ' months')::INTERVAL)::DATE;
month_end := (month_start + INTERVAL '1 month')::DATE;
part_suffix := to_char(month_start, 'YYYY_MM');
FOREACH tbl IN ARRAY ARRAY['messages', 'message_events', 'engagement_events', 'message_attachments']
LOOP
EXECUTE format(
'CREATE TABLE IF NOT EXISTS %I PARTITION OF %I
FOR VALUES FROM (%L) TO (%L)',
tbl || '_' || part_suffix, tbl, month_start, month_end
);
END LOOP;
END LOOP;
END;
$$;