Supabase direct mail: postcards from a database webhook
Supabase direct mail runs on two things you already have: a Database Webhook fires when a row is inserted, and an Edge Function sends that row's address to Mailr, which prints and mails the postcard. The function writes the piece id back to the row.
What to mail from Supabase
| When this happens | Mail this |
|---|---|
| INSERT on orders, when it's the customer's first | A thank-you postcard with a code for the next order. (First-order thank you) |
| UPDATE on subscriptions when status becomes past_due | A billing letter, mailed flat, after the emails didn't work. (Failed payment) |
| A nightly pg_cron job calls a function | Win-back postcards to customers 60+ days past their last order, one batch per 1,000. (Win-back) |
Set it up
- Add a column for the piece id. Run the SQL below so each order can hold the id Mailr returns. It's how you check what was mailed and look up its status later.
- Create the Edge Function. Run supabase functions new mail-thank-you and paste in the code below. The webhook calls it with your project's secret key, not a user JWT, so turn off verify_jwt for this function in supabase/config.toml and accept the key with auth: 'secret'.
- Set the key and deploy. supabase secrets set MAILR_API_KEY=mlr_test_..., then supabase functions deploy mail-thank-you. Deployed functions read a new secret right away. For local runs, put the key in supabase/functions/.env.
- Create the webhook. In the dashboard, create a Database Webhook on the orders table for Insert events. Set the type to Supabase Edge Functions, pick mail-thank-you, and under HTTP Headers choose Add secret key.
Edge Function and setup
import { withSupabase } from "npm:@supabase/server@1";
type Order = {
id: string;
customer_id: string;
first_name: string;
ship_name: string;
ship_line1: string;
ship_line2: string | null;
ship_city: string;
ship_state: string; // two-letter code
ship_zip: string;
};
export default {
fetch: withSupabase({ auth: "secret" }, async (req, ctx) => {
const { type, record } = (await req.json()) as { type: string; record: Order };
if (type !== "INSERT") return Response.json({ skipped: "not an insert" });
// Only a customer's first order gets the card.
const { count } = await ctx.supabaseAdmin
.from("orders")
.select("id", { count: "exact", head: true })
.eq("customer_id", record.customer_id);
if ((count ?? 0) > 1) return Response.json({ skipped: "not the first order" });
const res = await fetch("https://api.mailr.dev/v1/postcards", {
method: "POST",
headers: {
Authorization: `Bearer ${Deno.env.get("MAILR_API_KEY")}`,
"Content-Type": "application/json",
"Idempotency-Key": `thank-you-${record.id}`,
},
body: JSON.stringify({
size: "6x9",
template: "tmpl_thank_you",
to: {
name: record.ship_name,
line1: record.ship_line1,
line2: record.ship_line2,
city: record.ship_city,
state: record.ship_state,
zip: record.ship_zip,
},
from: { name: "Acme Outdoor", line1: "1 Example Way", city: "Phoenix", state: "AZ", zip: "85004" },
merge: { first_name: record.first_name, promo_code: "THANKS15" },
}),
});
const piece = await res.json();
if (!res.ok) return Response.json(piece, { status: res.status });
await ctx.supabaseAdmin.from("orders").update({ mailr_postcard_id: piece.id }).eq("id", record.id);
return Response.json({ id: piece.id, status: piece.status });
}),
};- Database Webhooks run through pg_net, which is asynchronous, so the insert never waits on Mailr.
- A webhook made in the dashboard saves the secret key in its trigger definition, so the key appears in database dumps. To keep it out, create the trigger in a migration that reads the key from Vault, as Supabase's webhook guide shows.
- The Idempotency-Key thank-you-<order id> means a repeated call for the same order returns the first postcard instead of mailing a second one.
- For mail that should wait, like a review card three weeks after delivery, schedule a function with pg_cron instead of mailing on insert.
What it costs
Each live piece is paid from your Mailr balance, printing and postage included: a 4x6 postcard is $0.85, a 6x9 $0.89 and an 8.5x11 letter $0.95 on pay as you go, less on a plan. Test pieces are free. All prices.
Templates that fit: First-order thank you (6x9), Failed payment (8.5x11), Win-back (6x11).
Questions
Why an Edge Function and not pg_net straight to Mailr?
pg_net can POST anywhere, but the Mailr key would have to live in the database, and the response with the piece id would only land in pg_net's own tables. The Edge Function keeps the key in function secrets and writes the id back to the order.
Does this work with Row Level Security on?
Yes. ctx.supabaseAdmin bypasses RLS, so the function can count the customer's orders and save the piece id. It only reads and writes rows for the order in the webhook payload.
Can I try it without a real order?
Yes. With a test key in MAILR_API_KEY, insert an order with your own address. The postcard shows up in your Mailr dashboard and walks through every status in about 8 minutes without printing.