Referral Desk

pg_cron drives the cadence (hourly follow-up marking, daily priority refresh). Every run writes a scan_runs row with status running/success/error — job state is a visible table, not a lost message.

Duplicate checks classify matches across RLS visibility. The unit test documents the real bug that forced this: the old check consulted the caller’s own filtered read and reported “no duplicate” about a record it structurally could not see.

/** "NO DUPLICATE" ABOUT A RECORD IT COULD NOT SEE.
 *  The old check filtered matches through the caller's own RLS-limited
 *  read, so a match on a colleague's record vanished and the insert
 *  proceeded. Same inputs must now come back blocked:           */
expect(oldRouteWouldInsert(hits, readable)).toBe(true);   // the bug
expect(decision.blocked).toBe(true);                      // the fix
expect(decision.hidden[0].agent_id).toBe(HIDDEN_AGENT);

Ownership is a row-level rule: an agent record is editable by an admin, the assigned producer, or its creator — enforced in rls_policies.sql, not in route handlers.

Suppression, opt-out/DNC, template approval, and the one-initial + one-follow-up cap are raised as Postgres exceptions before the row exists. Application code cannot route around it; neither can I.

-- BEFORE INSERT ON outreach_events  (production migration, excerpt)
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 is_email_suppressed(new.to_identity) then
  raise exception 'send blocked: recipient address is on the suppression list';
end if;
if v_template.status <> 'approved' then
  raise exception 'send blocked: template is not approved';
end if;
if new.template_kind = 'follow_up_email' and v_followups >= 1 then
  raise exception 'send blocked: the single permitted follow-up was already sent;
                   the sequence has ended permanently';
end if;

Marketing email requires an explicitly approved template version and a contact at approved_for_email. The system scales the motion; a person still makes the call.

This closed a HIGH finding from a security review: the webhook once accepted unsigned requests. Now the raw body is read first, the signature verified, invalid → 401. Fixed and re-verified in production Aug 2026.

// api/webhooks/resend — raw body FIRST, then signature, then parse
const valid = verifyResendWebhook({ secret,
  svixId: req.headers.get('svix-id'),
  svixTimestamp: req.headers.get('svix-timestamp'),
  svixSignature: req.headers.get('svix-signature'),
  rawBody });
if (!valid) {
  return NextResponse.json({ error: 'invalid signature' }, { status: 401 });
}

Delivery status may update. History may not be rewritten, by anyone, ever.

if tg_op = 'DELETE' then
  raise exception 'outreach_events is append-only: DELETE is not permitted';
end if;
if new.body is distinct from old.body
  or new.to_identity is distinct from old.to_identity then
  raise exception 'outreach_events content is immutable; only delivery_status,
                   call_outcome, provider_message_id and meta may change';
end if;

The schema is the honesty mechanism: premium and revenue are separate columns, commission is derived, and every policy carries an attribution kind and a confidence level — confirmed, probable, or assumed. The ~$10K figure on this site is an influenced number and the data model refuses to let it masquerade as booked.

-- Core rule, enforced by the schema: PREMIUM AND REVENUE ARE NEVER
-- THE SAME NUMBER. A policy stores written premium AND the agency
-- commission rate separately; commission is derived, never conflated.
-- Every policy records HOW it is attributed and HOW SURE we are, so an
-- "influenced" number can never masquerade as a booked one.
create type attribution_kind as enum (
  'direct',              -- the professional's own policy
  'referred_household',  -- a client they referred
  ...);
create type attribution_confidence as enum ('confirmed','probable','assumed');