From Form to InboxAn Owned Email Pipeline That Fires Itself

This article is the third part of one pipeline told in three. The first, the forms that feed Salesforce, is the intake. The second, the Salesforce connector without admin access, opens that data in plain language. This is the output: turning that live data into email that fires itself, reliably and reproducibly. It isn't a tour of what I built; it's a map for someone with the same problem to trace their own solution.

The problem, plainly

> In short: The old pipeline didn't fail for lack of features; it failed on decisions that weren't mine and were still my problem. Changing that starts with identifying which wall is real. The pipeline lived in Power Automate on top of SharePoint lists. The tool I built to drive those flows from an agent (opens in new tab) gave me full visibility into their internals, and with it, three walls no optimization inside the tool could tear down:

• A 6,000-action/day ceiling. Each send is an action; the limit is the license's, not the design's. Tens of thousands of emails was impossible in any architecture on top of that tool.
• A 200-contact-per-run cap, from the list connector.
• In-memory dedup. The only brake against resending was a filter in RAM: if the flow crashed mid-run, the brake didn't exist.

The diagnosis that mattered wasn't "how do I optimize this," it was "which of these walls is the design's and which is the tool's." All three were the tool's. The conclusion follows on its own: sending has to leave. That's the first exercise of judgment, and it defines everything else.

Sending as a shared mailbox without minting a credential

> In short: The standard recipe — register an app in Entra ID — needs admin. The reproducible alternative is to reuse a client Microsoft already pre-authorizes, and ride a delegation that already existed. Moving sending to Microsoft Graph hits auth immediately. The textbook recipe is to register an application in Entra ID and grant it Mail.Send. That needs an admin permission you won't get at a locked-down firm. The useful question isn't "how do I get admin," it's "what's the most powerful credential a normal user already holds, and how little do I need to add to it."

The answer: Microsoft already pre-authorizes public first-party clients for Graph scopes via device code, with nothing registered. The trick — and the gotcha — is that most aren't pre-authorized in a given tenant and return AADSTS65002. You have to find the one that is. In practice, the Microsoft Graph CLI client works where Azure CLI and Office fail.

The second half is identity. Sending as a shared mailbox isn't a new permission if someone in the org already holds Send As over it — which is exactly how the old pipeline sent. So the send module's login is done by that person, and their session is what sends. I didn't invent a permission; I chose the correct integration boundary — a pre-authorized grant plus an existing delegation — and stayed inside it.

> [!note] The pattern transfers to any locked-down provider: before asking for a new permission, find the grant the platform already gives its own official tool. Reusing a sanctioned credential almost always beats minting a new one, because the smallest credential surface is no new surface. It's the same discipline as the Salesforce connector, applied to sending.

Actually sending: /me/sendMail, Send As, and the header Graph won't let you set

> In short: Sending itself has a gotcha that's costly to discover: Graph rejects List-Unsubscribe as a normal header. You set it as a MAPI extended property. That detail is the difference between compliant and not. With token and identity solved, sending is a POST /me/sendMail with a from override pointing at the shared mailbox. What is not obvious — and costs hours to find — is how to set the List-Unsubscribe header, which is what makes Gmail and Outlook show the native "unsubscribe" button. Graph rejects that header via internetMessageHeaders (it only allows X--prefixed headers). The only way is to inject it as a MAPI extended property, with the id String 0x1045.

Discovering that 0x1045 is exactly the kind of knowledge an article should write down, because it's in nobody's quickstart. Without it, the pipeline "complies" halfway: there's an unsubscribe link in the body, but not the header mail clients use for one-click.

Idempotency by construction: resending is impossible, not improbable

> In short: The worst bug in a sender is sending twice. An in-memory check is a promise; a database constraint is a guarantee. Move the guarantee to the data edge. The old pipeline's in-memory dedup was the most dangerous failure mode: when it crashed, it resent. The fix isn't a more careful check; it's making resending structurally impossible. A partial unique index on the send log: for the same (campaign_id, email) with result sent, a second row cannot exist.

Two details that raise robustness at no cost: emails are stored as citext, so KAREN@… and karen@… are the same contact with no manual normalization; and the index is partial (where result='sent'), so failed attempts don't block a legitimate retry. The judgment here is choosing where truth lives: not in app logic, which crashes, but in the data engine, which doesn't.

Resumable with no cursor, and an atomic claim

> In short: If the "who's left" query already excludes what was sent, restarting continues instead of duplicating — you need no cursor. And SKIP LOCKED keeps two processes from taking the same campaign. Because no-duplicate is a DB guarantee, resumption is free: the pending query already excludes what was sent, suppressed, and unsubscribed. Restarting the process continues. There's no cursor to persist or corrupt. And the three exclusions are the same query, so "who's due" and "who isn't" never drift apart.

The scheduler that fires due campaigns runs every few minutes. So two ticks never take the same campaign, the claim is atomic:

FOR UPDATE SKIP LOCKED is the canonical queue pattern on Postgres, and it's worth knowing: it turns an ordinary table into a cross-process-safe job queue, with no separate broker.

Compliance as structure, not courtesy

> In short: Unsubscribe isn't a decorative footer link; it's a signed token, verified at the edge, that writes to the same table the pending query already reads. The system can't send to someone who left. Every email carries a footer with an HMAC-signed token over (campaign_id, email). The link hits an Edge Function that recomputes the HMAC, compares in constant time, and if it validates, records the unsubscribe. Suppression and unsubscribe aren't a filter someone remembers to apply; they're part of the definition of "who this email is for" (the query above).

Pacing deserves a note of judgment: 25/min is deliberately below Exchange's 30/min ceiling, to leave headroom for retries without brushing the edge. It isn't a magic number; it's arithmetic against a documented limit. For 9,000 contacts that's ~6 hours — a background process's job, not an interactive session's.

The seam that makes this sustainable

> In short: The engine doesn't know where leads come from. That ignorance is on purpose: changing the data source is writing another adapter, not touching the send. The piece that keeps this system from rotting is a small, deliberate seam. The engine doesn't know Salesforce or CSV; it knows one contract, NormalizedLead. Each source is an adapter that produces it.

The salesforce-adapter reads from the read-only connector and emits the same NormalizedLead, so the audience comes straight from the source of truth with no export in between; a csv-adapter stays for one-off loads. The pipeline runs from form to inbox with no human in between, and changing the source never touches the engine. That's the definition of a seam done right: isolating "where the data comes from" from "how it's sent."

One engine, several triggers

> In short: The same engine that sends a scheduled campaign fires reminders and newsletters by event. Scheduled is just the first mode; the source of truth drives. Because the source is Salesforce and the engine consumes a normalized contract, the same mechanism that sends a campaign by date fires an email by event: a new lead a form injects chains a reminder; a cohort crossing a threshold receives a newsletter. Campaign, reminder, and newsletter aren't three systems; they're the same engine — idempotent, paced, compliant — with a different trigger.

The outcome

> In short: What changed, concretely, and what it cost the org's security posture: nothing.

• From ceiling to no ceiling. The 6,000-action/day and 200-contact-per-run limits are gone; the only one left is Exchange's (10,000/day, 30/min), the provider's, handled with pacing, not a wall of the design's making.
• From "hope it doesn't duplicate" to "it can't duplicate." Dedup went from in-memory check to unique index.
• From copy-and-paste to a single path, from the same source of truth the forms feed.
• No security cost. No new credential, no registered app, the send identity is the one that already existed. The org's surface didn't grow.

End-to-end validation has already run: real campaigns scheduled, fired on their own at the exact time, with unsubscribe working and zero duplicates. It isn't a paper design; it's a live pipeline.

The decisions that hold it up

> In short: The choices the system rests on, and the work that follows.

• Take sending out of the low-code tool instead of optimizing inside it, because the wall was the tool's, not the design's.
• Reuse a pre-authorized grant plus an existing delegation, instead of registering an app and asking for permissions.
• State in real Postgres: dedup, cursor, and suppression as guarantees of the data engine, not in-memory tricks.
• Structural compliance: HMAC-signed unsubscribe and suppression honored by the same query that builds the audience.
• An adapter seam that isolates the source from the engine.

On the roadmap: an automatic daily cap for lists above the Exchange limit; per-campaign click tracking back to Salesforce; a bounce processor that feeds suppression on its own; and a sanitized public reference repo with the engine's skeleton (operational details stay private).

References

> In short: The sources that make this architecture reproducible, not just credible.

The real lesson

> In short: Judgment isn't a moment of brilliance; it's choosing the durable option over the quick one, again and again, and being able to defend each choice. The most defensible decision in this project wasn't a clever idea, it was a sustained stance: at every fork, choose the option that turns a hope into a guarantee. Dedup could have been a more careful check; I made it a unique index. Auth could have been a ticket to IT; I made it the reuse of a grant that already existed. Compliance could have been a footer link; I made it a signed token the same audience query honors.

None of those choices is more code than the easy alternative. They're better code, because they move the system's truth to where it doesn't crash. A marketing pipeline isn't hard to assemble; it's hard to own — and owning it means being able to name every one of its guarantees, show the code that holds it, and explain why it beats what you "should" have built. If someone with my same problem reaches here, they have the full map: the real wall, the grant to reuse, the hidden header, the constraint that makes duplication impossible, and the seam that lets the system grow without a rewrite.