Architecture Overview

pg_relay_notifier is a pure SQL/PL/pgSQL PostgreSQL extension. It introduces no C code, no background workers, and no shared_preload_libraries entry.

The extension implements the notifier side of the pg_relay v1.1 interface contract (interface version 1). The split is strict and deliberate:

• pg_relay_notifier owns all notification data: recipients, subjects, bodies, attachments, connection profiles, delivery status, debug traces. It stores them in its own tables, in its own schema (pgrelay_notifier), with whatever structure it chooses.
• pg_relay owns the delivery mechanics: queueing, ordering, concurrency, retries, timeouts, the transport implementations (SMTP and Microsoft Graph, in Go), and the audit trail in pgrelay.log.

The two meet only at four interface functions — see The Interface Contract. The pg_relay Processor never touches a pgrelay_notifier table; the notifier never touches queue internals. Because the interface is functions, pg_relay_notifier is free to restructure its own tables in any future 1.x release without a pg_relay release — only a change to the four function signatures themselves would require coordination.

How a notification flows

application / trigger
        │  writes the notification into pg_relay_notifier's tables
        │  (recipients, subject, body, profile reference, ...)
        ▼
pg_relay_notifier enqueues:  SELECT pgrelay.notify('<channel>', '<pk>')
        │        the payload is ONLY the primary key of the notification row
        ▼
pgrelay.queue   ← durable row, committed with the producer's transaction
        ⋮  (up to ~1 second)
pg_relay Processor tick:
        1. pgrelay.queue_probe()          → work? reload requested? paused?
        2. pgrelay.queue_pending_ids()    → (id, action_type) list
        3. action_type = 'notify' →  IN ONE HELD TRANSACTION:
              a. pgrelay._queue_claim(id)             row locked until COMMIT
              b. pgrelay_notifier.fetch(pk)            full message + profile
              c. deliver (SMTP / M365 Graph)           under a hard timeout
              d. pgrelay_notifier.set_status(pk, ...)  attempt outcome
              e. pgrelay._write_log(...)               audit row
              f. pgrelay._queue_mark_done(id)          (+ retry row on transient failure)
              g. COMMIT
        ▼
pgrelay.log — one audit row per attempt        pg_relay_notifier — delivery status per attempt

The held transaction is the heart of the design: the claimed queue row stays row-locked for the whole delivery, so SKIP LOCKED hides it from every other Processor instance and concurrency_mode behaves exactly as it does for ordinary SQL channels. A crash mid-send rolls back to an unclaimed row that's re-offered on a later poll: delivery is at-least-once — a rare duplicate is possible if a crash lands between provider acceptance and COMMIT, but a notification is never silently lost.

---