The Interface Contract

This page summarises the formal contract between pg_relay v1.1 and pg_relay_notifier 1.0 — the full text lives in CLAUDE.md in the repository, and is the authoritative spec if anything here and there disagree.

The interface functions

The four core functions must exist with exactly these signatures; the fifth, classify_webhook_response, is optional and scoped to transport = 'webhook'. The Processor's startup preflight verifies existence and EXECUTE grants, reporting notifier:* (and webhook:function) warnings for anything missing — never a startup-blocking error, since a broken notifier must not stop unrelated SQL event processing.

Function	Called	Contract
interface_version() → integer	At startup and after every reload	Returns 1. If the Processor doesn't support the returned version, notify events are declined with a clear error rather than processed against the wrong ABI.
fetch(p_id bigint) → TABLE(transport, profile jsonb, message jsonb, debug)	Once per delivery attempt, inside the held transaction, immediately after the claim	Unknown id → zero rows, never an exception. Must be fast and side-effect free — the same id may be fetched more than once (retries are separate attempts).
set_status(p_id, p_status, p_detail, p_provider_ref, p_elapsed_ms, p_attempt) → void	Once per attempt, inside the held transaction	Must tolerate an unknown id without raising. Wrapped in a savepoint by the Processor — a raising set_status loses only its own status write; a delivered email is never re-sent because bookkeeping failed.
debug_log(p_id, p_step, p_detail) → void	Once per step, only for notifications with debug = true, on a separate autocommit connection	Must not raise. Debug rows are visible in real time while a send is in flight and survive a rolled-back attempt — exactly when a trace matters most.
classify_webhook_response(p_id, p_http_status, p_response_headers jsonb, p_response_body) → TABLE(status, provider_ref, detail)	Once per webhook attempt, inside the held transaction, immediately after the HTTP call (savepoint-guarded)	Webhook transport only (pg_relay ≥ 1.2). Interprets the raw response — sent/retry/failed — so all provider knowledge stays in SQL and the Processor's webhook handler never changes. Must be side-effect free: the Processor calls set_status() itself with whatever this returns. Zero rows, an exception, or an unrecognised status resolve the attempt as a permanent failure. Headers arrive as a flat, lower-cased-key object; status 0 means no response was received; 3xx never reaches classification (the Processor refuses redirects itself).

Secrets

Secrets never appear as literal values anywhere in profile or message. Any string value in profile, at any key or depth, matching ^_env:[A-Za-z_][A-Za-z0-9_]*$ is a reference — the Processor resolves it by reading the named environment variable from its own host, in place, immediately after fetch() returns and before using profile for anything. This is the only mechanism for supplying a secret; there's no separate secret-carrying return column. Designated secret fields (SMTP password, SMTP oauth2.client_secret/oauth2.refresh_token, M365 client_secret, webhook auth.secret) must be such a reference — pg_relay_notifier's own validation rejects a literal value in any of them.

Transport schemas

fetch() returns two JSON objects, profile and message. Unknown keys are ignored (forward-compatible), except reserved ones. Full field-by-field tables are in the Schema and Data Model page and in CLAUDE.md §8 — the transports are smtp, m365, and (pg_relay ≥ 1.2) the generic webhook transport, where profile describes the endpoint (url, auth, headers) and message is the complete provider-correct request body, built by this extension's provider adapter and POSTed verbatim — the Processor never interprets it. Slack is the first webhook provider (see Slack Endpoints).

The one exception to "message goes to the wire untouched" is a webhook profile's optional body_merge object (pg_relay ≥ 1.2): its top-level keys are overlaid onto the request body immediately before wire encoding, after the profile-wide _env: resolution pass — so its values, like auth.secret, can name Processor-host environment variables. A merged key unconditionally overwrites any producer-supplied value for the same key (profile-owned, so never injectable or spoofable by a sender); the merge is top-level-only, shape-blind, and never an inspection of the message, and message itself is still never scanned for _env: references. A body_merge that is not a JSON object is a permanent failure with no request sent and no classify_webhook_response() call. Full semantics: WEBHOOK_TRANSPORT_PROCESSOR_SPEC.md §2.1; DBA-facing usage: Slack Endpoints — the body_merge overlay.

Enqueue contract

pg_relay_notifier enqueues exactly one queue event per notification, and the payload is the notification primary key as text — nothing else. No JSON, no envelope, no message content ever enters pgrelay.queue. A payload that doesn't parse as a bigint is resolved as a permanent failure.

What the Processor guarantees in return

1. At-least-once delivery, via the held transaction.
2. concurrency_mode applies to notify channels exactly as to SQL channels.
3. A hard timeout on every send (profile.timeout_seconds, default 30, hard cap 120).
4. One set_status per attempt (savepoint-guarded), plus one pgrelay.log audit row per attempt.
5. Secrets stay out of the database, per the mechanism above.
6. Message content is never logged by the Processor, at any level.
7. Fleet controls (pgrelay.stop()/pause_for()/pause_to()/request_reload()) apply to notify dispatch exactly as to everything else.

Versioning

This is interface version 1. pg_relay_notifier 1.x releases must keep interface_version() returning 1 and must not change the interface function signatures. (Adding classify_webhook_response was not a version bump: it is optional, and a Processor treats its absence as "webhook transport unavailable", a preflight warning, not an error.) A breaking change would be interface version 2, documented in a new pg_relay release, with a transition period where a Processor may support both versions — mismatched pairs fail with a clear, named error rather than misprocessing anything.

---