Enforcing outreach compliance in Postgres, not in app code

Most outreach systems keep their compliance rules in application code, which makes every rule advisory. In Referral Desk I moved them into the database, so a send that breaks a rule fails at the insert. Here is the actual trigger, and what the approach costs.

The problem with checking before you send

The usual shape looks responsible. Before dispatching a message you call something like canSend(contact), it checks the suppression list, the opt-out flag and how many emails the contact has already received, and if everything passes you hand the message to the provider.

That works exactly as long as every send goes through that function. In practice it stops being true almost immediately, and usually for legitimate reasons. Someone adds a retry worker. Someone writes a backfill script to re-send the batch that failed overnight. Someone wires up a webhook handler that answers an inbound reply. A new channel gets added and copies the old dispatch code without the newest check. Each of these is a reasonable change made by a careful person, and each one is a path to sending mail to someone who asked you to stop.

The failure mode is not that anyone forgot the rule. It is that the rule lived somewhere it could be bypassed. An unsubscribe honoured in four out of five code paths is not honoured.

Make the log the choke point

The fix is structural. Referral Desk has an append-only outreach_events table, and every send — every channel, every worker, every script — has to write a row to it. That is not a convention, it is how the system records that a message existed at all.

Once that is true, the table is a choke point, and a BEFORE INSERT trigger on it becomes a rule that no code path can route around. The comment above the function in the migration says it plainly:

-- Fires on EVERY insert into the append-only outreach log. Because every send
-- path must create this row, every send path inherits these checks.
create or replace function enforce_outbound_rules() returns trigger
language plpgsql security definer set search_path = public as $$

From the Referral Desk migration that defines the outbound guard.

The email branch reads in the order the rules actually matter. Suppression first, because a suppressed address is suppressed for everything:

-- Suppression blocks ALL email, marketing and transactional alike,
-- when the address itself was suppressed.
if v_agent.id is not null and is_email_suppressed(v_agent.business_email::text) then
  raise exception 'send blocked: recipient is on the suppression list';
end if;
if new.to_identity is not null and is_email_suppressed(new.to_identity) then
  raise exception 'send blocked: recipient address is on the suppression list';
end if;

Both the contact record and the literal destination address are checked. They can differ, and the one that is actually about to receive mail is the second one.

Then the marketing-only rules, which are stricter than the transactional ones:

if v_agent.opted_out or v_agent.do_not_contact then
  raise exception 'send blocked: contact has opted out or is do-not-contact';
end if;
if v_agent.outreach_paused then
  raise exception 'send blocked: contact is paused pending human review of an inbound message';
end if;
if not v_settings.sending_enabled then
  raise exception 'send blocked: sending is disabled until the launch checklist is completed';
end if;
if v_settings.campaigns_paused then
  raise exception 'send blocked: campaigns are paused (kill switch active)';
end if;

Abridged. The full branch also verifies that the template is the approved version of its kind and that its version matches the outreach record.

The sequence cap is a query, not a counter

Sequence limits are usually stored as a number on the contact and incremented after each send. That number drifts the first time a send half-fails, and once it drifts there is no way to tell whether it is wrong. Counting the log instead means the limit is derived from what actually happened:

-- Sequence cap: ONE initial, at most ONE follow-up, then permanent stop.
select
  count(*) filter (where template_kind in ('initial_email', 'listing_email')),
  count(*) filter (where template_kind = 'follow_up_email')
into v_initials, v_followups
from outreach_events
where agent_id = new.agent_id and channel = 'email' and direction = 'outbound'
  and delivery_status <> 'failed';

if new.template_kind = 'follow_up_email' then
  if v_initials = 0 then
    raise exception 'send blocked: no initial email on record; a follow-up cannot lead';
  end if;
  if v_followups >= 1 then
    raise exception 'send blocked: the single permitted follow-up was already sent; the sequence has ended permanently';
  end if;
end if;

A follow-up that cannot lead is a real rule, not a nicety. It is what stops a retry or a backfill from introducing itself to a stranger as though there were prior contact.

What this buys

• New code paths inherit the rules. This is the whole point. A worker added six months from now by someone who has never read the compliance requirements is still governed by them, because the only way to record a send is to pass the trigger.
• The kill switch is a row, not a deploy. campaigns_paused and sending_enabled live in a settings table. Stopping every campaign is an update statement that takes effect on the next insert, with no build, no deploy and no rollout wait.
• The audit trail cannot be tidied up. The suppression list and the audit log both carry immutability triggers, so rows cannot be edited or truncated after the fact. An append-only record is worth far more than an editable one when someone asks what happened.
• The rule is readable by a non-engineer. A compliance reviewer can be shown the trigger. It is a short, ordered list of conditions in near-English, which is a much better artifact to review than dispatch logic spread across several services.

What it costs

• Errors arrive as exceptions, late. The application finds out at insert time, in the shape of a raised Postgres error. You still need application-level checks for the user interface, so some rules genuinely are expressed twice — once for a helpful message, once for enforcement. The duplication is deliberate, and the database copy is the one that counts.
• Testing moves into the database. Unit tests in the application cannot exercise a trigger. These rules need database-level tests, which is a second test stack to set up, run in CI and keep honest.
• Error strings become an interface. Once something parses 'send blocked: ...' to decide what to show a user, that message is an API and changing its wording is a breaking change.
• Migrations become compliance change control. Changing a rule means shipping a migration. That is good for auditability and slow when a rule is genuinely wrong, and you should want it to be slow.
• It does not travel. The guard protects this database. A send issued directly through the provider's own dashboard never touches it. Database enforcement raises the floor; it does not remove the need to control who holds the provider credentials.

When it is worth doing

Not always. If one service owns all sending, the team is small, and the cost of a mistake is an apology, application-level checks are proportionate and much cheaper to work with.

It is worth the cost when breaking the rule has consequences you cannot take back — regulated outreach, a contact who has asked to be left alone, anything where the answer to "how do you know it did not happen" has to be better than "we checked the code." In insurance and real estate outreach, which is what Referral Desk does, that bar is the ordinary one.