Send transactional email

Goal#

When an order ships, your application should:

1. render one stored template with server-owned order data;
2. submit the message once even when the operation is retried;
3. record the template version and provider outcome;
4. let an operator inspect the result without storing the original variables object.

1. Configure delivery#

SqlOS includes an Azure Communication Services Email sender. Read its credentials from user secrets in development and a managed secret store in production:

builder.AddSqlOS<AppDbContext>(options =>
{
    options.ConfigureEmail(email =>
    {
        email.AzureCommunicationServicesConnectionString =
            builder.Configuration["SqlOS:Email:AzureCommunicationServicesConnectionString"];
        email.FromAddress =
            builder.Configuration["SqlOS:Email:FromAddress"];
 
        email.EnableIdempotency = true;
        email.PersistRenderedHtmlPreview = false;
        email.DeliveryRetention = TimeSpan.FromDays(90);
    });
});

Never commit the connection string. scripts/azure/setup-acs-email.sh can provision the Azure resources and DNS verification records.

If Azure Communication Services is not the right provider, register an application-owned ISqlOSEmailSender. The template, validation, idempotency, delivery-history, and dashboard behavior remain the same.

2. Create the template#

Open SqlOS Dashboard > Communications > Email Templates and create:

Template keys are unique. Content changes increment the stored template version, so each delivery records the exact version it rendered. An inactive template cannot be sent, and ISqlOSTransactionalEmailService.PreviewAsync only resolves active templates. The operator dashboard can still preview an inactive stored template for inspection before reactivation.

Deleting an unused template removes it. Once a template has delivery history, the dashboard deactivates it instead so past deliveries keep their template relationship.

The Variables JSON saved with a template is persisted sample metadata for the editor and preview form. Keep those sample values non-secret. It is different from the per-send Variables dictionary, which is not stored as JSON on the delivery.

3. Preview with representative values#

Use the dashboard preview before sending. You can also make preview part of an internal publishing or smoke-test workflow:

var preview = await email.PreviewAsync(
    "order-shipped",
    new Dictionary<string, object?>
    {
        ["orderId"] = "A-1042",
        ["trackingUrl"] = "https://tracking.example.test/A-1042"
    },
    ct);

The renderer has deliberately small semantics:

• {variable} is the only placeholder syntax;
• substitutions in HTML are HTML-encoded;
• subject and text substitutions are plain text;
• a missing variable fails preview and send;
• extra variables are ignored.

HTML encoding prevents a value from becoming injected markup. It does not decide whether an order exists, whether a recipient may see it, or whether a URL scheme is safe. Generate trackingUrl on the trusted server from an allowlisted HTTPS origin.

4. Send from a trusted business workflow#

Inject ISqlOSTransactionalEmailService into the application service or endpoint that owns the shipment transition. Resolve the recipient and tracking URL from authorized server-side data rather than accepting them as trusted browser input:

public sealed class ShipmentNotifier(
    OrdersDbContext db,
    ISqlOSTransactionalEmailService email)
{
    public async Task<SqlOSSendEmailResult> NotifyAsync(
        string orderId,
        string organizationId,
        CancellationToken ct)
    {
        var order = await db.Orders.SingleAsync(
            x => x.Id == orderId && x.OrganizationId == organizationId,
            ct);
 
        if (order.ShippedAt is null)
        {
            throw new InvalidOperationException("The order has not shipped.");
        }
 
        var trustedTrackingUrl =
            $"https://tracking.example.com/orders/{Uri.EscapeDataString(order.Id)}";
 
        return await email.SendAsync(new SqlOSSendEmailRequest(
            TemplateKey: "order-shipped",
            To: order.CustomerEmail,
            Variables: new Dictionary<string, object?>
            {
                ["orderId"] = order.Id,
                ["trackingUrl"] = trustedTrackingUrl
            },
            IdempotencyKey:
                $"org:{organizationId}:order:{order.Id}:shipped"),
            ct);
    }
}

The key describes the business event, not the HTTP attempt. It is globally unique across the delivery table, so include enough tenant and event identity to prevent two legitimate operations from sharing it. Keep the normalized key at 200 characters or fewer. Calling NotifyAsync later with the same persisted key returns the existing delivery and does not submit a second message. A random GUID on every retry defeats that protection.

Choose the event boundary carefully. If a later reshipment should send another email, give it a distinct stable event identifier such as org:acme:order:A-1042:shipment:2.

5. Interpret the result correctly#

SendAsync returns the delivery id, status, template key/version, provider message id, and a sanitized error when submission fails.

queued does not prove that the recipient's mail server or inbox accepted the message. SqlOS does not currently process delivery webhooks, bounces, or open events.

A pending row is not proof that the provider was never contacted. If cancellation or process failure happens around submission, reconcile the provider operation where possible and keep the same idempotency key while investigating; blindly using a new key can produce a duplicate.

Template lookup and rendering happen before a delivery row is created. A missing or inactive template and a missing required variable raise an error instead of contacting the provider. Those validation failures are exceptions, not failed delivery results.

6. Inspect delivery history#

Open SqlOS Dashboard > Communications > Email Messages. Filter by status, template key, recipient, or date range.

For each custom-template delivery SqlOS records:

• recipient;
• template key and version;
• pending, queued, or failed status and timestamps;
• provider message id when available;
• sanitized failure detail;
• rendered subject and rendered text preview;
• rendered HTML only when PersistRenderedHtmlPreview is enabled.

SqlOS does not store the arbitrary variables dictionary. That does not make variables consequence-free: their substituted values can appear in the stored subject and text preview.

The returned result, persisted SanitizedError, and audit-event error are sanitized. The service also passes the caught provider exception to the host's ILogger; apply provider SDK redaction, restrict log access, and do not assume the sanitized delivery field sanitizes every host log sink. Treat the delivery error as operational context, not a complete provider trace.

7. Understand built-in auth email#

SqlOS seeds three built-in templates:

• auth.email-otp
• auth.invitation
• auth.password-reset

You can edit their copy in Communications > Email Templates. Their rendered text and HTML are suppressed from delivery history because they contain codes or token-bearing links.

Use Email Branding for shared product identity and colors. Existing BuildMessage callbacks and custom ISqlOSAuthEmailSender implementations remain the advanced escape hatch for auth-specific layouts or provider behavior.

This subsystem is for operational product email. It does not provide campaigns, subscriber lists, preference management, or marketing-email compliance workflows.

8. Verify the integration#

Before production, prove each boundary:

• a complete preview renders the expected subject, HTML, and text;
• omitting trackingUrl fails before provider submission;
• a later retry with org:acme:order:A-1042:shipped returns the first delivery and performs no second provider submission;
• a failed or pending delivery remains the returned outcome for that same key;
• a distinct shipment event produces a new delivery;
• an inactive template cannot be sent or previewed through ISqlOSTransactionalEmailService, while the operator dashboard can still preview it;
• provider failures store only sanitized detail;
• the three built-in templates suppress secret-bearing rendered content;
• the dashboard shows the exact template version used.

The repository's focused regression suite exercises these semantics:

dotnet test tests/SqlOS.Tests/SqlOS.Tests.csproj \
  --filter FullyQualifiedName~SqlOSTransactionalEmailTests

Next steps#

• Transactional Email reference
• Email Branding
• Audit Logs
• Production Readiness