Schema and Data Model

All tables live in the pgrelay_notifier schema and are private: every privilege is revoked from PUBLIC and access goes through SECURITY DEFINER functions — see Security Model.

profiles

Delivery endpoint profiles (SMTP relays, Microsoft 365 tenants).

Column	Type	Description
profile_id	bigint identity PK	Surrogate key referenced by notifications
profile_name	text	Unique identifier (case-insensitive; stored as created)
transport	text	smtp or m365; routes the Processor's Go handler
profile	jsonb	Transport-specific connection block returned verbatim by fetch(). Secret-bearing keys (password, client_secret) hold "_env:VAR_NAME" references, never literal values — the secret itself is never stored
channel	text	Default pg_relay channel for this profile's notifications
send_from	text	Default sender for notifications composed without p_sender. NULL: the profile JSON's own from/sender key applies
reply_to	text	Default Reply-To for notifications composed without p_reply_to. NULL: no Reply-To header by default
max_attach_mb	numeric	Cap on one notification's total attachment payload (MB of raw bytes; base64 adds ~37% on the wire)
max_attach_number	integer	Cap on attachments per notification
debug	boolean	Default debug-trace flag for the profile's notifications
active	boolean	Inactive profiles refuse new notifications; in-flight ones are unaffected
notes	text	Operator notes
created_at / updated_at	timestamptz	Audit timestamps (updated_at trigger-maintained)

notifications

One row per message.

Column	Type	Description
notification_id	bigint PK, DEFAULT pgrelay._next_id()	The only value that travels through the pg_relay queue. Snowflake-generated — see Multi-Master and Notification IDs
profile_id	bigint FK → profiles	Delivery profile
sender	text	Per-notification sender override
recipients	text[]	To recipients (≥ 1)
cc / bcc	text[]	Copy recipients; bcc is envelope-only, never in headers
reply_to	text	Reply-To header
subject	text	Subject (Q-encoded automatically for non-ASCII)
body_text / body_html	text	Neither is required — a body-less compose() stores a single space in body_text as a placeholder, since the Processor's transports require at least one non-empty body part on the wire
priority	smallint 1–5	Oracle UTL_MAIL-style priority; forward-compatible JSON key, not yet on the wire
debug	boolean	Per-notification debug override; NULL inherits the profile
status	text	draft, pending, sent, retry, failed, expired, invalid
status_detail	text	Failure detail from the last attempt
provider_ref	text	SMTP Message-ID or Graph request-id of the last attempt
attempts	integer	Highest attempt number reported (1-based)
elapsed_ms	integer	External-call milliseconds of the last attempt
channel	text	pg_relay channel used (set by dispatch())
dispatched_at / completed_at	timestamptz	Queue entry time; terminal-status time
created_at / created_by	timestamptz / text	Compose time; the session_user that composed it (SECURITY DEFINER-safe provenance)

attachments

Column	Type	Description
attachment_id	bigint identity PK	Also the attachment order within the message
notification_id	bigint FK, ON DELETE CASCADE	Owning notification
filename	text	Filename presented to recipients
content_type	text	MIME type; default application/octet-stream
content	bytea	Raw bytes; base64-encoded (no line wraps) at fetch time
inline	boolean	Oracle att_inline equivalent; forward-compatible key, not yet on the wire
created_at	timestamptz	When added

status_history

One row per delivery attempt, written by the Processor via set_status(). Deliberately has no foreign key: the interface contract requires set_status() to tolerate unknown ids without raising, so unknown ids are logged here rather than rejected.

Column	Type	Description
history_id	bigint identity PK
notification_id	bigint	The attempt's notification (not FK-enforced)
attempt	integer	1-based attempt number (retry_count + 1)
status	text	sent, retry, failed, expired, invalid (unconstrained — this is a log)
detail	text	Failure detail; may quote the provider's response
provider_ref	text	SMTP Message-ID / Graph request-id
elapsed_ms	integer	External-call milliseconds (0 when no send was attempted)
recorded_at	timestamptz	When recorded

debug_trace

Processor step traces for debug-flagged notifications, written via debug_log() on an autocommit connection outside the delivery transaction — rows appear live during a send and survive rolled-back attempts. No foreign key (debug_log() must never raise).

Column	Type	Description
trace_id	bigint identity PK
notification_id	bigint	Traced notification
step	text	claimed, fetched, sent, send_failed, committed
detail	text	Step detail (queue id, transport, provider_ref, failure class…)
logged_at	timestamptz	clock_timestamp() — real time, not transaction time

profile_defaults

Per-transport seed values copied into each new profile by create_profile(). Seeded at install with smtp: 10 MB / 999 and m365: 3 MB / 999.

Column	Type	Description
transport	text PK	Transport the seed applies to
max_attach_mb	numeric	Default total-attachment-payload cap for new profiles
max_attach_number	integer	Default attachment-count cap for new profiles
updated_at	timestamptz	Maintained by update_profile_defaults()

Deliberately not an extension member. A table whose rows the install seeds and the DBA edits can't round-trip pg_dump/restore as ordinary extension configuration — re-seeded rows would collide with dumped ones. Detaching it means DBA-edited seeds survive DROP EXTENSION, reinstall, and dump/restore, at the cost that \dx+ pg_relay_notifier won't list it and uninstalling fully means dropping the schema.

---