Compare commits

...
Author SHA1 Message Date
james.maes 2688f02a9a Merge remote-tracking branch 'origin/feature/phase-c-tracking' into feature/sender-addresses 2026-10-14 07:21:40 -05:00
james.maesandClaude Opus 5.5 0aa9b5907e feat: provider sender addresses and contingent from-address pickers
email_provider_sender_address (child of the provider; admin-only;
domain, uniqueness and single-default checks). Sends may name a sender
row (withFromAddressId) or match one by text; rows belong to the
resolved provider, the default row wins, and the provider's from
address stays the fallback. Message rows record from_address_id.
Contingent possible-value filters: fromAddressId by providerId on both
send processes, on Send Test Email (seeded from the acted-on provider)
and on email_message; message ids on attempts and events by provider;
suppression source event by source message. One test per pair.

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
2026-10-14 07:21:39 -05:00
14 changed files with 872 additions and 29 deletions

No files matched your search

+2
View File
@@ -6,6 +6,8 @@ The format follows [Keep a Changelog](https://keepachangelog.com/en/1.1.0/) and
## [Unreleased]
### Added
- Provider sender addresses: child table `email_provider_sender_address` (validated lowercase address, display name, one default, enabled, note; unique per provider; domain bound to the provider's sending domain; admin-only), shown on the provider record. `EmailRequest.withFromAddressId`, `from_address_id` on messages, refusal of a from address that is not an enabled row of the resolved provider, and the provider `from_address` / `from_name` kept as the fallback.
- Contingent possible-value filters on the new from-address pickers (send processes by `providerId`, Send Test Email by the acted-on provider, message `fromAddressId` by `providerId`) and on the attempt and event message pickers (by `providerId`) and the suppression source event (by `sourceMessageId`); the send processes also take `providerId`.
- Phase (c): authenticated SNS, Postmark, SendGrid and Mailgun webhooks, durable inbox deduplication and Mailgun nonce replay protection; configurable Javalin routes.
- Atomic scheduled event processing, per-recipient event history, monotonic delivery state, category-aware suppression, optional daily complaint alerts and FAILED inbox Reprocess with Review/Result.
- One-click unsubscribe and escaped self-contained light/dark confirmation pages using the existing envelope-bound signer; account/security tokens refused.
+25
View File
@@ -52,6 +52,7 @@ in a static variable.
| `email_brand` | Brands | `email.manage`; template edit grants also apply |
| `email_suppression` | Suppressions | `email.manage`; changing/removing a complaint needs `email.admin` |
| `email_provider` | Providers, with child Messages | `email.admin`, including reads and test sends |
| `email_provider_sender_address` | Sender Addresses on a provider | `email.admin`, including reads |
`email.view` reads messages, templates, versions, brands and suppressions.
`email.send` permits the Java service, bridge and backend send process. Reading
@@ -87,6 +88,30 @@ enabled, exactly one must be the enabled default. Switch defaults in a batch upd
clearing the old default first. A nullable unique `default_slot` backs the validation.
Providers with message history cannot be deleted; disable them instead.
**Sender addresses.** A provider can own any number of verified from addresses, kept in
`email_provider_sender_address` and shown as a Sender Addresses list on the provider record
(not in the nav; `email.admin` reads and writes, like the provider). Each row has an
`address` (validated, stored lowercase, line breaks refused), an optional `displayName`,
`isDefault`, `enabled` and a `note`. An address is unique per provider, at most one enabled
row per provider is the default, and when the provider has a `sendingDomain` every address must
be in that domain (changing the domain with other-domain rows is refused). Deleting a provider
deletes its rows. Existing databases add the table and the nullable
`email_message.from_address_id` column from `docs/ddl/email-postgres.sql`.
The provider's own `fromAddress` / `fromName` stay the fallback, so nothing changes until a
row exists. A row marked default wins over the fallback. On a send, `withFromAddressId(id)`
(or `fromAddressId` on the send processes) must name an enabled row of the resolved provider,
and once a provider has rows an explicit `withFrom(...)` must match one of them by address;
either failure is `REFUSED` with "That from address is not set up for provider <name>". With
no rows, `withFrom(...)` behaves as before. The chosen row is recorded on the message as
`fromAddressId`, and a resend reuses it.
The from address pickers are contingent: `fromAddressId` on the send processes lists only the
rows of the chosen `providerId` (`providerName` is still accepted for API callers, and the two
must agree), and the Send Test Email action lists only the rows of the provider being tested.
That action's form has no record id, so a prepare step publishes the single selected provider id
as the process value `possibleValueFilterValueId`, which the picker's filter reads after `${input.id}`.
For an unauthenticated relay, use kind `SMTP`, host/port, security `NONE`, and a
`fromAddress`. Leave credential names empty. Authenticated SMTP uses `usernameEnvName`
and `secretEnvName`; security is `NONE`, `STARTTLS` (required) or `TLS`. Timeouts are
+14 -2
View File
@@ -54,6 +54,7 @@ EmailSendResult result = EmailService.send(new EmailRequest()
.withRecordVariable("order", "order", 1234) // a record, read as the sender; classification known
.withCategory(EmailCategory.NOTIFICATION) // default: the template's
.withProviderName("pmg") // optional; else the default provider
.withFromAddressId(4) // optional enabled email_provider_sender_address row of that provider
.withBrandName("kof22") // optional; else the template's, else the default brand
.withIdempotencyKey("alerts:notif:991") // optional; a repeat returns the existing message
.withLinkedRecord("alerts_alert", 991) // shown as a link on the message record
@@ -73,7 +74,8 @@ EmailSendResult result = EmailService.send(new EmailRequest()
### 2.2 Process `email_send_templated`
The same request as a backend process (template, to, cc, bcc, variables JSON, linked table and
id, idempotency key, provider, brand) for scripts, the API and other qbits. Not in the nav;
id, idempotency key, provider by `providerId` or `providerName`, `fromAddressId` filtered by the
chosen `providerId`, brand) for scripts, the API and other qbits. Not in the nav;
callable through the API and `/qqq/v1/processes`.
### 2.3 Permissions
@@ -122,7 +124,16 @@ They are never stored, logged, audited, put in process output or returned. The r
whether each named variable is set (yes or no), never the value.
**Record action "Send Test Email"** (`email.admin`): queues a fixed test message to a given
address through that provider and reports the outcome on the standard result screen.
address through that provider and reports the outcome on the standard result screen. An optional
`fromAddressId` picker lists only that provider's sender addresses.
**Sender addresses** (`email_provider_sender_address`, a child list on the provider, `email.admin`):
provider_id, address (validated, lowercase), display_name, is_default (at most one enabled default
per provider), enabled, note; unique (provider_id, address); the domain must equal `sending_domain`
when that is set. The provider's `from_address` / `from_name` remain the fallback; an enabled default
row wins when present. A chosen id, or an explicit from text once the provider has rows, must match an
enabled row of the resolved provider or the send is `REFUSED` ("That from address is not set up for
provider <name>"). The row used is kept as `email_message.from_address_id`.
### 3.2 `EmailTransport` SPI (in the qbit)
```java
@@ -480,6 +491,7 @@ section's description explains the split with qbit-alerts' Notifications section
| `email_brand` | yes | people |
| `email_suppression` | yes | people and the qbit |
| `email_provider` | yes | admins |
| `email_provider_sender_address` | relation (on the provider) | admins |
| `email_webhook_inbox` | relation (on the provider) | the qbit |
| `email_inbound`, `email_inbound_attachment` | phase (e) | the qbit |
+14
View File
@@ -38,6 +38,19 @@ CREATE TABLE email_provider (
UNIQUE (default_slot)
);
CREATE TABLE email_provider_sender_address (
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
create_date TIMESTAMP,
modify_date TIMESTAMP,
provider_id INTEGER NOT NULL,
address VARCHAR(500) NOT NULL,
display_name VARCHAR(500),
is_default BOOLEAN,
enabled BOOLEAN,
note TEXT,
UNIQUE (provider_id, address)
);
CREATE TABLE email_message (
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
create_date TIMESTAMP,
@@ -47,6 +60,7 @@ CREATE TABLE email_message (
cc_addresses TEXT,
bcc_addresses TEXT,
from_address VARCHAR(500),
from_address_id INTEGER,
reply_to VARCHAR(500),
subject TEXT,
template_id INTEGER,
@@ -143,6 +143,7 @@ public class EmailQBitProducer implements QBitMetaDataProducer<EmailQBitConfig>
for(QTableMetaData table : EmailTables.tables(qBitConfig))
{
Class<?> customizer = EmailTables.PROVIDER.equals(table.getName()) ? EmailTableCustomizers.ProviderCustomizer.class
: EmailTables.SENDER.equals(table.getName()) ? EmailTableCustomizers.SenderAddressCustomizer.class
: EmailTables.SUPPRESSION.equals(table.getName()) ? EmailTableCustomizers.SuppressionCustomizer.class
: List.of(EmailTables.TEMPLATE, EmailTables.VERSION, EmailTables.BRAND).contains(table.getName()) ? com.kingsrook.qbits.email.template.EmailTemplateCustomizers.class : null;
if(customizer != null)
@@ -21,23 +21,32 @@
package com.kingsrook.qbits.email.metadata;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Locale;
import java.util.Objects;
import java.util.Optional;
import java.util.Set;
import com.kingsrook.qbits.email.EmailQBitConfig;
import com.kingsrook.qbits.email.EmailRuntime;
import com.kingsrook.qbits.email.permissions.EmailPermissionChecker;
import com.kingsrook.qbits.email.permissions.EmailPermissions;
import com.kingsrook.qbits.email.support.EmailStore;
import com.kingsrook.qbits.email.support.SystemWrites;
import com.kingsrook.qbits.email.transport.EmailProviderSettings;
import com.kingsrook.qqq.backend.core.actions.QBackendTransaction;
import com.kingsrook.qqq.backend.core.actions.customizers.TableCustomizerInterface;
import com.kingsrook.qqq.backend.core.exceptions.QException;
import com.kingsrook.qqq.backend.core.model.actions.AbstractActionInput;
import com.kingsrook.qqq.backend.core.model.actions.tables.QueryOrGetInputInterface;
import com.kingsrook.qqq.backend.core.model.actions.tables.delete.DeleteInput;
import com.kingsrook.qqq.backend.core.model.actions.tables.insert.InsertInput;
import com.kingsrook.qqq.backend.core.model.actions.tables.query.QCriteriaOperator;
import com.kingsrook.qqq.backend.core.model.actions.tables.query.QFilterCriteria;
import com.kingsrook.qqq.backend.core.model.actions.tables.query.QQueryFilter;
import com.kingsrook.qqq.backend.core.model.actions.tables.update.UpdateInput;
import com.kingsrook.qqq.backend.core.model.data.QRecord;
import com.kingsrook.qqq.backend.core.model.statusmessages.BadInputStatusMessage;
import jakarta.mail.internet.InternetAddress;
@@ -197,6 +206,13 @@ public final class EmailTableCustomizers
record.setValue(field, null);
}
}
String sendingDomain = merged.getValueString("sendingDomain");
if(sendingDomain != null && !sendingDomain.isBlank() && merged.getValue("id") != null
&& SystemWrites.query(EmailTables.SENDER, EmailStore.equal("providerId", merged.getValueInteger("id")), null).stream()
.anyMatch(s -> !sendingDomain.equalsIgnoreCase(s.getValueString("address").substring(s.getValueString("address").lastIndexOf('@') + 1))))
{
record.addError(new BadInputStatusMessage("Sender addresses must match the sending domain"));
}
resulting.removeIf(r -> Objects.equals(r.getValue("id"), record.getValue("id")));
resulting.add(merged);
record.setValue("defaultSlot", Boolean.TRUE.equals(merged.getValueBoolean("enabled")) && Boolean.TRUE.equals(merged.getValueBoolean("isDefault")) ? 1 : null);
@@ -230,6 +246,13 @@ public final class EmailTableCustomizers
{
records.forEach(r -> r.addError(new BadInputStatusMessage("Choose another enabled default before deleting this provider")));
}
for(QRecord row : records)
{
if(!preview && row.getErrors().isEmpty())
{
SystemWrites.delete(EmailTables.SENDER, EmailStore.equal("providerId", row.getValueInteger("id")), input.getTransaction());
}
}
return (records);
}
@@ -272,6 +295,106 @@ public final class EmailTableCustomizers
}
}
/*******************************************************************************
** Sender addresses: canonical lowercase address, the provider's sending domain,
** unique per provider and at most one enabled default per provider.
*******************************************************************************/
public static class SenderAddressCustomizer implements TableCustomizerInterface
{
/*******************************************************************************
** Validate each row against its provider and the resulting set of rows.
*******************************************************************************/
@Override
public List<QRecord> preInsertOrUpdate(AbstractActionInput input, List<QRecord> records, boolean preview, Optional<List<QRecord>> oldRecords) throws QException
{
if(SystemWrites.active())
{
return (records);
}
QBackendTransaction transaction = input instanceof InsertInput insert ? insert.getTransaction() : input instanceof UpdateInput update ? update.getTransaction() : null;
List<QRecord> resulting = new ArrayList<>();
Set<Serializable> providerIds = new LinkedHashSet<>();
for(QRecord record : records)
{
QRecord merged = effective(record, oldRecords);
if(record.getValueString("address") != null)
{
try
{
String text = record.getValueString("address");
if(text.contains("\r") || text.contains("\n"))
{
throw (new IllegalArgumentException("Sender address must not contain line breaks"));
}
InternetAddress address = new InternetAddress(text, true);
address.validate();
record.setValue("address", address.getAddress().toLowerCase(Locale.ROOT));
merged.setValue("address", record.getValue("address"));
}
catch(Exception e)
{
record.addError(new BadInputStatusMessage("A valid sender address is required"));
}
}
String name = merged.getValueString("displayName");
if(name != null && (name.contains("\r") || name.contains("\n")))
{
record.addError(new BadInputStatusMessage("Display name must not contain line breaks"));
}
QRecord provider = merged.getValue("providerId") == null ? null : EmailStore.get(EmailTables.PROVIDER, merged.getValueInteger("providerId"), transaction);
if(provider == null)
{
record.addError(new BadInputStatusMessage("An existing provider is required"));
}
else if(merged.getValueString("address") != null && record.getErrors().isEmpty())
{
String domain = provider.getValueString("sendingDomain");
String address = merged.getValueString("address");
if(domain != null && !domain.isBlank() && !domain.equalsIgnoreCase(address.substring(address.lastIndexOf('@') + 1)))
{
record.addError(new BadInputStatusMessage("Sender address domain must match the provider sending domain"));
}
}
if(Boolean.TRUE.equals(merged.getValueBoolean("isDefault")) && !Boolean.TRUE.equals(merged.getValueBoolean("enabled")))
{
record.addError(new BadInputStatusMessage("A default sender address must be enabled"));
}
if(merged.getValue("providerId") != null)
{
providerIds.add(merged.getValue("providerId"));
}
resulting.add(merged);
}
if(!providerIds.isEmpty())
{
List<QRecord> existing = SystemWrites.query(EmailTables.SENDER, new QQueryFilter(new QFilterCriteria("providerId", QCriteriaOperator.IN, new ArrayList<>(providerIds))), transaction);
existing.removeIf(r -> records.stream().anyMatch(n -> Objects.equals(n.getValue("id"), r.getValue("id"))));
for(int i = 0; i < records.size(); i++)
{
QRecord merged = resulting.get(i);
List<QRecord> peers = new ArrayList<>(existing);
for(int j = 0; j < resulting.size(); j++)
{
if(j != i)
{
peers.add(resulting.get(j));
}
}
peers.removeIf(r -> !Objects.equals(r.getValue("providerId"), merged.getValue("providerId")));
if(peers.stream().anyMatch(r -> Objects.equals(r.getValueString("address"), merged.getValueString("address"))))
{
records.get(i).addError(new BadInputStatusMessage("That sender address already exists for this provider"));
}
if(Boolean.TRUE.equals(merged.getValueBoolean("isDefault")) && peers.stream().anyMatch(r -> Boolean.TRUE.equals(r.getValueBoolean("isDefault"))))
{
records.get(i).addError(new BadInputStatusMessage("Only one sender address per provider can be the default"));
}
}
}
return (records);
}
}
/*******************************************************************************
** Canonical addresses and administrator protection for complaint removal.
*******************************************************************************/
@@ -26,6 +26,7 @@ import java.util.List;
import com.kingsrook.qbits.email.EmailQBitConfig;
import com.kingsrook.qbits.email.outbox.EmailCategory;
import com.kingsrook.qbits.email.permissions.EmailPermissionChecker;
import com.kingsrook.qbits.email.support.EmailStore;
import com.kingsrook.qqq.backend.core.actions.dashboard.widgets.ChildRecordListRenderer;
import com.kingsrook.qqq.backend.core.instances.QInstanceEnricher;
import com.kingsrook.qqq.backend.core.model.metadata.code.QCodeReference;
@@ -53,6 +54,7 @@ import com.kingsrook.qqq.backend.core.model.metadata.tables.UniqueKey;
public final class EmailTables
{
public static final String PROVIDER = "email_provider";
public static final String SENDER = "email_provider_sender_address";
public static final String MESSAGE = "email_message";
public static final String ATTEMPT = "email_message_attempt";
public static final String TEMPLATE = "email_template";
@@ -61,7 +63,7 @@ public final class EmailTables
public static final String SUPPRESSION = "email_suppression";
public static final String INBOX = "email_webhook_inbox";
public static final String EVENT = "email_event";
public static final List<String> ALL = List.of(PROVIDER, MESSAGE, ATTEMPT, TEMPLATE, VERSION, BRAND, SUPPRESSION, INBOX, EVENT);
public static final List<String> ALL = List.of(PROVIDER, SENDER, MESSAGE, ATTEMPT, TEMPLATE, VERSION, BRAND, SUPPRESSION, INBOX, EVENT);
public static final List<String> MAIN = List.of(MESSAGE, TEMPLATE, BRAND, SUPPRESSION, PROVIDER);
public static final List<String> SYSTEM_WRITTEN = List.of(MESSAGE, ATTEMPT, INBOX, EVENT);
@@ -103,9 +105,10 @@ public final class EmailTables
ALL.forEach(name -> result.add(QPossibleValueSource.newForTable(name)));
for(QPossibleValueSource source : result)
{
if(List.of(PROVIDER, MESSAGE, VERSION).contains(source.getName()))
if(List.of(PROVIDER, SENDER, MESSAGE, VERSION).contains(source.getName()))
{
source.withSearchFields(PROVIDER.equals(source.getName()) ? List.of("label", "name") : MESSAGE.equals(source.getName()) ? List.of("subject") : List.of("versionNumber", "locale"))
source.withSearchFields(PROVIDER.equals(source.getName()) ? List.of("label", "name") : SENDER.equals(source.getName()) ? List.of("address", "displayName")
: MESSAGE.equals(source.getName()) ? List.of("subject") : List.of("versionNumber", "locale"))
.withOrderByField("id");
}
}
@@ -119,6 +122,7 @@ public final class EmailTables
{
List<QTableMetaData> tables = new ArrayList<>();
tables.add(provider(config));
tables.add(sender(config));
tables.add(message(config));
tables.add(messageattempt(config));
tables.add(template(config));
@@ -210,12 +214,35 @@ public final class EmailTables
table.withSection(SectionFactory.defaultT1("id", "name", "label", "kind", "enabled", "isDefault"));
table.withSection(SectionFactory.defaultT2("fromAddress", "fromName", "replyTo", "sendingDomain", "host", "port", "security", "region", "usernameEnvName", "secretEnvName", "webhookSecretEnvName", "snsTopicArn", "configurationSet", "messageStream", "maxPerMinute", "maxPerDay", "trackOpens", "trackClicks", "phiAllowed", "healthStatus", "lastSuccessDate", "lastFailureDate", "lastFailureMessage"));
table.withSection(SectionFactory.defaultT3("createDate", "modifyDate"));
table.withSection(SectionFactory.customT2("senders", new QIcon("list")).withLabel("Sender Addresses").withWidgetName(SENDER + "_children"));
table.withSection(SectionFactory.customT2("children", new QIcon("list")).withLabel("Messages").withWidgetName(PROVIDER + "_children"));
table.withSection(SectionFactory.customT2("inbox", new QIcon("list")).withLabel("Webhook Inbox").withWidgetName(INBOX + "_children"));
return (table);
}
/*******************************************************************************
** Metadata for email_provider_sender_address. The provider list in the
** nav is the only entry point; rows are managed as a child list on the provider.
*******************************************************************************/
private static QTableMetaData sender(EmailQBitConfig config)
{
QTableMetaData table = base(config, SENDER, "Provider Sender Address")
.withField(field("providerId", QFieldType.INTEGER).withPossibleValueSourceName(PROVIDER).withIsRequired(true))
.withField(field("address", QFieldType.STRING).withIsRequired(true).withMaxLength(500))
.withField(field("displayName", QFieldType.STRING).withMaxLength(500))
.withField(field("isDefault", QFieldType.BOOLEAN).withDefaultValue(false))
.withField(field("enabled", QFieldType.BOOLEAN).withDefaultValue(true))
.withField(field("note", QFieldType.TEXT));
table.withRecordLabelFormat(null).withRecordLabelFields(List.of());
table.withUniqueKey(new UniqueKey("providerId", "address"));
table.withSection(SectionFactory.defaultT1("id", "providerId", "address", "displayName", "isDefault", "enabled"));
table.withSection(SectionFactory.defaultT2("note"));
table.withSection(SectionFactory.defaultT3("createDate", "modifyDate"));
return (table);
}
/*******************************************************************************
** Metadata for email_message.
*******************************************************************************/
@@ -227,10 +254,11 @@ public final class EmailTables
.withField(field("ccAddresses", QFieldType.TEXT).withIsSensitive(true))
.withField(field("bccAddresses", QFieldType.TEXT).withIsSensitive(true))
.withField(field("fromAddress", QFieldType.STRING).withIsSensitive(true).withMaxLength(500))
.withField(field("fromAddressId", QFieldType.INTEGER).withPossibleValueSourceName(SENDER).withPossibleValueSourceFilter(EmailStore.equal("providerId", "${input.providerId}")))
.withField(field("replyTo", QFieldType.STRING).withIsSensitive(true).withMaxLength(500))
.withField(field("subject", QFieldType.TEXT).withIsSensitive(true))
.withField(field("templateId", QFieldType.INTEGER).withPossibleValueSourceName(TEMPLATE))
.withField(field("templateVersionId", QFieldType.INTEGER).withPossibleValueSourceName(VERSION).withPossibleValueSourceFilter(com.kingsrook.qbits.email.support.EmailStore.equal("templateId", "${input.templateId}")))
.withField(field("templateVersionId", QFieldType.INTEGER).withPossibleValueSourceName(VERSION).withPossibleValueSourceFilter(EmailStore.equal("templateId", "${input.templateId}")))
.withField(field("brandId", QFieldType.INTEGER).withPossibleValueSourceName(BRAND))
.withField(field("category", QFieldType.STRING).withPossibleValueSourceName("email_category").withMaxLength(500))
.withField(field("locale", QFieldType.STRING).withDefaultValue("en").withMaxLength(500))
@@ -264,7 +292,7 @@ public final class EmailTables
table.getField("subject").setIsHeavy(false);
table.withUniqueKey(new UniqueKey("idempotencyKey"));
table.withSection(SectionFactory.defaultT1("id", "status", "toAddresses", "ccAddresses", "bccAddresses", "fromAddress"));
table.withSection(SectionFactory.defaultT2("replyTo", "subject", "templateId", "templateVersionId", "brandId", "category", "locale", "variablesSnapshot", "htmlBody", "textBody", "phiApproved", "unsubscribeUrl", "bodySha256", "idempotencyKey", "providerId", "providerMessageId", "attemptCount", "nextAttemptDate", "leaseExpiresDate", "lastError", "finishedDate", "sentDate", "deliveredDate", "bouncedDate", "complainedDate", "deadLetterDate", "linkedTableName", "linkedRecordId", "senderQbit", "senderProcess", "suppressedAddresses", "sendAfter"));
table.withSection(SectionFactory.defaultT2("fromAddressId", "replyTo", "subject", "templateId", "templateVersionId", "brandId", "category", "locale", "variablesSnapshot", "htmlBody", "textBody", "phiApproved", "unsubscribeUrl", "bodySha256", "idempotencyKey", "providerId", "providerMessageId", "attemptCount", "nextAttemptDate", "leaseExpiresDate", "lastError", "finishedDate", "sentDate", "deliveredDate", "bouncedDate", "complainedDate", "deadLetterDate", "linkedTableName", "linkedRecordId", "senderQbit", "senderProcess", "suppressedAddresses", "sendAfter"));
table.withSection(SectionFactory.defaultT3("createDate", "modifyDate"));
table.withSection(SectionFactory.customT2("children", new QIcon("list")).withLabel("Attempts").withWidgetName(MESSAGE + "_children"));
table.withSection(SectionFactory.customT2("events", new QIcon("list")).withLabel("Events").withWidgetName(EVENT + "_children"));
@@ -278,7 +306,7 @@ public final class EmailTables
private static QTableMetaData messageattempt(EmailQBitConfig config)
{
QTableMetaData table = base(config, ATTEMPT, "Message Attempt")
.withField(field("messageId", QFieldType.INTEGER).withPossibleValueSourceName(MESSAGE).withIsRequired(true))
.withField(field("messageId", QFieldType.INTEGER).withPossibleValueSourceName(MESSAGE).withIsRequired(true).withPossibleValueSourceFilter(EmailStore.equal("providerId", "${input.providerId}")))
.withField(field("attemptNumber", QFieldType.INTEGER).withIsRequired(true))
.withField(field("outcome", QFieldType.STRING).withPossibleValueSourceName("email_attempt_outcome").withMaxLength(500))
.withField(field("providerId", QFieldType.INTEGER).withPossibleValueSourceName(PROVIDER))
@@ -307,8 +335,8 @@ public final class EmailTables
.withField(field("category", QFieldType.STRING).withPossibleValueSourceName("email_category").withMaxLength(500))
.withField(field("kind", QFieldType.STRING).withPossibleValueSourceName("email_template_kind").withIsRequired(true).withMaxLength(500))
.withField(field("brandId", QFieldType.INTEGER).withPossibleValueSourceName(BRAND))
.withField(field("activeVersionId", QFieldType.INTEGER).withPossibleValueSourceName(VERSION).withIsEditable(false).withPossibleValueSourceFilter(com.kingsrook.qbits.email.support.EmailStore.equal("templateId", "${input.id}")))
.withField(field("approvedVersionId", QFieldType.INTEGER).withIsEditable(false).withPossibleValueSourceName(VERSION).withPossibleValueSourceFilter(com.kingsrook.qbits.email.support.EmailStore.equal("templateId", "${input.id}")))
.withField(field("activeVersionId", QFieldType.INTEGER).withPossibleValueSourceName(VERSION).withIsEditable(false).withPossibleValueSourceFilter(EmailStore.equal("templateId", "${input.id}")))
.withField(field("approvedVersionId", QFieldType.INTEGER).withIsEditable(false).withPossibleValueSourceName(VERSION).withPossibleValueSourceFilter(EmailStore.equal("templateId", "${input.id}")))
.withField(field("versionLock", QFieldType.INTEGER).withIsHidden(true).withIsEditable(false))
.withField(field("phiApproved", QFieldType.BOOLEAN).withDefaultValue(false).withIsEditable(false))
.withField(field("phiApprovedBy", QFieldType.STRING).withMaxLength(500).withIsEditable(false))
@@ -399,7 +427,7 @@ public final class EmailTables
.withField(field("scope", QFieldType.STRING).withPossibleValueSourceName("email_suppression_scope").withIsRequired(true).withMaxLength(500))
.withField(field("reason", QFieldType.STRING).withPossibleValueSourceName("email_suppression_reason").withIsRequired(true).withMaxLength(500))
.withField(field("sourceMessageId", QFieldType.INTEGER))
.withField(field("sourceEventId", QFieldType.INTEGER).withPossibleValueSourceName(EVENT))
.withField(field("sourceEventId", QFieldType.INTEGER).withPossibleValueSourceName(EVENT).withPossibleValueSourceFilter(EmailStore.equal("messageId", "${input.sourceMessageId}")))
.withField(field("note", QFieldType.TEXT))
.withField(field("createdDate", QFieldType.DATE_TIME))
.withField(field("expiresDate", QFieldType.DATE_TIME));
@@ -442,7 +470,7 @@ public final class EmailTables
private static QTableMetaData event(EmailQBitConfig config)
{
QTableMetaData table = base(config, EVENT, "Event")
.withField(field("messageId", QFieldType.INTEGER).withIsRequired(true).withPossibleValueSourceName(MESSAGE))
.withField(field("messageId", QFieldType.INTEGER).withIsRequired(true).withPossibleValueSourceName(MESSAGE).withPossibleValueSourceFilter(EmailStore.equal("providerId", "${input.providerId}")))
.withField(field("providerId", QFieldType.INTEGER).withPossibleValueSourceName(PROVIDER))
.withField(field("eventType", QFieldType.STRING).withMaxLength(500).withIsRequired(true).withPossibleValueSourceName("email_event_type"))
.withField(field("bounceType", QFieldType.STRING).withMaxLength(500).withPossibleValueSourceName("email_bounce_type"))
@@ -462,7 +490,7 @@ public final class EmailTables
*******************************************************************************/
public static List<QJoinMetaData> joins()
{
return (List.of(join(PROVIDER, MESSAGE, "providerId"), join(MESSAGE, ATTEMPT, "messageId"), join(TEMPLATE, VERSION, "templateId"), join(PROVIDER, INBOX, "providerId"), join(MESSAGE, EVENT, "messageId")));
return (List.of(join(PROVIDER, MESSAGE, "providerId"), join(MESSAGE, ATTEMPT, "messageId"), join(TEMPLATE, VERSION, "templateId"), join(PROVIDER, INBOX, "providerId"), join(PROVIDER, SENDER, "providerId"), join(MESSAGE, EVENT, "messageId")));
}
/*******************************************************************************
@@ -482,8 +510,8 @@ public final class EmailTables
List<QWidgetMetaData> widgets = new ArrayList<>();
for(QJoinMetaData join : joins())
{
widgets.add(ChildRecordListRenderer.widgetMetaDataBuilder(join).withName(List.of(INBOX, EVENT).contains(join.getRightTable()) ? join.getRightTable() + "_children" : join.getLeftTable() + "_children")
.withLabel(join.getRightTable().equals(INBOX) ? "Webhook Inbox" : join.getRightTable().equals(EVENT) ? "Events" : join.getRightTable().equals(ATTEMPT) ? "Attempts" : join.getRightTable().equals(VERSION) ? "Versions" : "Messages")
widgets.add(ChildRecordListRenderer.widgetMetaDataBuilder(join).withName(List.of(INBOX, EVENT, SENDER).contains(join.getRightTable()) ? join.getRightTable() + "_children" : join.getLeftTable() + "_children")
.withLabel(join.getRightTable().equals(INBOX) ? "Webhook Inbox" : join.getRightTable().equals(SENDER) ? "Sender Addresses" : join.getRightTable().equals(EVENT) ? "Events" : join.getRightTable().equals(ATTEMPT) ? "Attempts" : join.getRightTable().equals(VERSION) ? "Versions" : "Messages")
.withMaxRows(50).getWidgetMetaData().withGridColumns(12).withPermissionRules(permissionRules()));
}
return (widgets);
@@ -39,6 +39,7 @@ public class EmailRequest
private List<String> cc = new ArrayList<>();
private List<String> bcc = new ArrayList<>();
private String from;
private Integer fromAddressId;
private String replyTo;
private String subject;
private String markdownBody;
@@ -213,6 +214,26 @@ public class EmailRequest
}
/*******************************************************************************
** Getter for fromAddressId.
*******************************************************************************/
public Integer getFromAddressId()
{
return (fromAddressId);
}
/*******************************************************************************
** Fluent setter for fromAddressId, a row of email_provider_sender_address that
** must belong to the resolved provider and be enabled.
*******************************************************************************/
public EmailRequest withFromAddressId(Integer fromAddressId)
{
this.fromAddressId = fromAddressId;
return (this);
}
/*******************************************************************************
** Getter for replyTo.
*******************************************************************************/
@@ -164,16 +164,29 @@ public final class EmailService
List<String> bcc;
String from;
String replyTo;
QRecord senderRow;
try
{
senderRow = senderRow(provider, SystemWrites.query(EmailTables.SENDER, EmailStore.equal("providerId", provider.getValue("id")), queryTransaction), request);
}
catch(IllegalArgumentException e)
{
return (refused(e.getMessage()));
}
try
{
to = addresses(request.getTo());
cc = addresses(request.getCc());
bcc = addresses(request.getBcc());
String sender = request.getFrom() == null ? provider.getValueString("fromAddress") : request.getFrom();
String sender = request.getFrom() != null ? request.getFrom() : senderRow != null ? senderRow.getValueString("address") : provider.getValueString("fromAddress");
from = single(sender);
if(request.getFrom() == null && provider.getValueString("fromName") != null)
if(request.getFrom() == null)
{
from = new InternetAddress(new InternetAddress(from).getAddress(), provider.getValueString("fromName"), "UTF-8").toString();
String name = senderRow != null ? senderRow.getValueString("displayName") : provider.getValueString("fromName");
if(name != null && !name.isBlank())
{
from = new InternetAddress(new InternetAddress(from).getAddress(), name, "UTF-8").toString();
}
}
String reply = request.getReplyTo() == null ? provider.getValueString("replyTo") : request.getReplyTo();
replyTo = reply == null ? null : single(reply);
@@ -244,7 +257,7 @@ public final class EmailService
QRecord row = new QRecord().withValue("providerId", provider.getValue("id"))
.withValue("status", allSuppressed ? "SUPPRESSED" : "QUEUED").withValue("finishedDate", allSuppressed ? now : null).withValue("attemptCount", 0)
.withValue("toAddresses", String.join(",", to)).withValue("ccAddresses", String.join(",", cc)).withValue("bccAddresses", String.join(",", bcc))
.withValue("fromAddress", from).withValue("replyTo", replyTo).withValue("subject", request.getSubject())
.withValue("fromAddress", from).withValue("fromAddressId", senderRow == null ? null : senderRow.getValue("id")).withValue("replyTo", replyTo).withValue("subject", request.getSubject())
.withValue("htmlBody", rendered.html()).withValue("textBody", rendered.text()).withValue("bodySha256", digest(rendered))
.withValue("brandId", adHocBrand == null ? null : adHocBrand.getValue("id"))
.withValue("category", category.name()).withValue("locale", request.getLocale()).withValue("idempotencyKey", request.getIdempotencyKey())
@@ -304,7 +317,7 @@ public final class EmailService
return (refused("Provider is unavailable"));
}
EmailRequest request = new EmailRequest().withSubject(row.getValueString("subject")).withHtmlBody(row.getValueString("htmlBody"))
.withTextBody(row.getValueString("textBody")).withFrom(row.getValueString("fromAddress")).withReplyTo(row.getValueString("replyTo"))
.withTextBody(row.getValueString("textBody")).withFrom(row.getValueString("fromAddress")).withFromAddressId(row.getValueInteger("fromAddressId")).withReplyTo(row.getValueString("replyTo"))
.withProviderName(provider.getValueString("name")).withCategory(EmailCategory.valueOf(row.getValueString("category")))
.withLinkedRecord(EmailTables.MESSAGE, id).withSender("email", "email_resend");
if(row.getValueString("toAddresses") != null && !row.getValueString("toAddresses").isBlank())
@@ -479,6 +492,57 @@ public final class EmailService
return (result);
}
/*******************************************************************************
** The provider sender-address row a request uses, or null for the provider's own
** from address. A chosen id, or a from text, must name an enabled row of this
** provider; once a provider has rows, an explicit from text must match one.
** With neither, an enabled default row wins over the provider's from address.
*******************************************************************************/
private static QRecord senderRow(QRecord provider, List<QRecord> rows, EmailRequest request)
{
String refusal = "That from address is not set up for provider " + provider.getValueString("name");
String wanted = null;
if(request.getFrom() != null && (request.getFromAddressId() != null || !rows.isEmpty()))
{
try
{
wanted = new InternetAddress(request.getFrom()).getAddress().toLowerCase(Locale.ROOT);
}
catch(Exception e)
{
throw (new IllegalArgumentException("One or more email addresses are invalid"));
}
}
for(QRecord row : rows)
{
boolean enabled = Boolean.TRUE.equals(row.getValueBoolean("enabled"));
if(request.getFromAddressId() != null)
{
if(request.getFromAddressId().equals(row.getValueInteger("id")))
{
if(!enabled || wanted != null && !wanted.equals(row.getValueString("address")))
{
throw (new IllegalArgumentException(refusal));
}
return (row);
}
}
else if(wanted != null && enabled && wanted.equals(row.getValueString("address")))
{
return (row);
}
else if(request.getFrom() == null && enabled && Boolean.TRUE.equals(row.getValueBoolean("isDefault")))
{
return (row);
}
}
if(request.getFromAddressId() != null || wanted != null)
{
throw (new IllegalArgumentException(refusal));
}
return (null);
}
/*******************************************************************************
** Strict address parsing, rejecting header injection and local-only addresses.
*******************************************************************************/
@@ -74,7 +74,7 @@ public class EmailPermissionChecker implements CustomPermissionChecker
boolean write = actionInput instanceof InsertInput || actionInput instanceof UpdateInput || actionInput instanceof DeleteInput;
if(metaData instanceof QTableMetaData table)
{
if(EmailTables.PROVIDER.equals(table.getName()))
if(EmailTables.PROVIDER.equals(table.getName()) || EmailTables.SENDER.equals(table.getName()))
{
return (EmailPermissions.ADMIN);
}
@@ -90,6 +90,9 @@ public final class EmailProcesses
public static final String PROCESS_WEBHOOKS = "email_process_webhooks";
public static final String RETENTION = "email_retention";
/** Process value the send-test prepare step sets to the selected provider id; the from address filter falls back to it. */
public static final String PROVIDER_FILTER_VALUE = "possibleValueFilterValueId";
/*******************************************************************************
** Utility class.
*******************************************************************************/
@@ -116,9 +119,12 @@ public final class EmailProcesses
public static List<QProcessMetaData> processes(EmailQBitConfig config)
{
QProcessMetaData test = action(SEND_TEST, "Send Test Email", EmailTables.PROVIDER);
test.withStep(0, new QFrontendStepMetaData().withName("input").withLabel("Recipient")
test.withStep(0, new QBackendStepMetaData().withName("prepare").withCode(new QCodeReference(SendTestPrepareStep.class)));
test.withStep(1, new QFrontendStepMetaData().withName("input").withLabel("Recipient")
.withComponent(new QFrontendComponentMetaData().withType(QComponentType.EDIT_FORM))
.withFormField(new QFieldMetaData("toAddress", QFieldType.STRING).withLabel("To address").withIsRequired(true)));
.withFormField(new QFieldMetaData("toAddress", QFieldType.STRING).withLabel("To address").withIsRequired(true))
.withFormField(new QFieldMetaData("fromAddressId", QFieldType.INTEGER).withLabel("From address").withPossibleValueSourceName(EmailTables.SENDER)
.withPossibleValueSourceFilter(EmailStore.equal("providerId", "${input.id}??${processValues." + PROVIDER_FILTER_VALUE + "}"))));
QFunctionInputMetaData inputs = new QFunctionInputMetaData();
QFunctionInputMetaData adHocInputs = new QFunctionInputMetaData();
for(String field : List.of("templateName", "to", "cc", "bcc", "replyTo", "subject", "markdownBody", "htmlBody", "textBody", "providerName", "brandName",
@@ -128,6 +134,12 @@ public final class EmailProcesses
adHocInputs.withField(new QFieldMetaData(field, QFieldType.STRING));
}
adHocInputs.withField(new QFieldMetaData("category", QFieldType.STRING));
for(QFunctionInputMetaData target : List.of(inputs, adHocInputs))
{
target.withField(new QFieldMetaData("providerId", QFieldType.INTEGER).withPossibleValueSourceName(EmailTables.PROVIDER));
target.withField(new QFieldMetaData("fromAddressId", QFieldType.INTEGER).withPossibleValueSourceName(EmailTables.SENDER)
.withPossibleValueSourceFilter(EmailStore.equal("providerId", "${input.providerId}")));
}
QProcessMetaData adHoc = new QProcessMetaData().withName(SEND_AD_HOC).withLabel("Send Ad Hoc Email").withIsHidden(true)
.withPermissionRules(EmailTables.permissionRules())
.withStep(new QBackendStepMetaData().withName("send").withCode(new QCodeReference(SendStep.class)).withInputData(adHocInputs));
@@ -294,7 +306,7 @@ public final class EmailProcesses
case SEND_NOW -> new EmailDispatcher().sendNow(id).name();
case CANCEL -> cancel(id) ? "CANCELLED" : "Skipped";
case RESEND -> resend(current).status().name();
case SEND_TEST -> sendTest(current, input.getValueString("toAddress"));
case SEND_TEST -> sendTest(current, input.getValueString("toAddress"), input.getValueInteger("fromAddressId"));
default -> "Skipped";
};
}
@@ -348,17 +360,45 @@ public final class EmailProcesses
/*******************************************************************************
** Queue and dispatch a fixed provider test message.
*******************************************************************************/
private static String sendTest(QRecord provider, String to) throws QException
private static String sendTest(QRecord provider, String to, Integer fromAddressId) throws QException
{
if(to == null)
{
return ("REFUSED: a to address is required");
}
EmailSendResult result = EmailService.send(new EmailRequest().withProviderName(provider.getValueString("name")).withTo(to)
EmailSendResult result = EmailService.send(new EmailRequest().withProviderName(provider.getValueString("name")).withTo(to).withFromAddressId(fromAddressId)
.withSubject("Email delivery test").withMarkdownBody("This is a test of your email provider.").withSender("email", SEND_TEST));
return (result.status() == EmailSendResult.Status.QUEUED ? new EmailDispatcher().sendNow(result.messageId()).name() : result.status().name());
}
/*******************************************************************************
** Seeds the from address possible-value filter: a record action's form has no
** record id, so a single selected provider id becomes a process value that the
** filter reads after the form's own ${input.id}.
*******************************************************************************/
public static class SendTestPrepareStep implements BackendStep
{
/*******************************************************************************
** Expose the selected provider id when exactly one provider is selected.
*******************************************************************************/
@Override
public void run(RunBackendStepInput input, RunBackendStepOutput output) throws QException
{
String ids = input.getValueString("recordIds");
if(ids != null && !ids.contains(","))
{
try
{
output.addValue(PROVIDER_FILTER_VALUE, Integer.valueOf(ids.trim()));
}
catch(NumberFormatException e)
{
// Not a single id: the filter stays open and the review step still validates the selection.
}
}
}
}
/*******************************************************************************
** Backend sending API with inputs declared as QQQ fields.
*******************************************************************************/
@@ -370,10 +410,20 @@ public final class EmailProcesses
@Override
public void run(RunBackendStepInput input, RunBackendStepOutput output) throws QException
{
EmailRequest request = new EmailRequest().withTemplateName(input.getValueString("templateName"))
String providerName = input.getValueString("providerName");
if(input.getValueInteger("providerId") != null)
{
QRecord provider = EmailStore.get(EmailTables.PROVIDER, input.getValueInteger("providerId"), null);
if(provider == null || providerName != null && !providerName.equals(provider.getValueString("name")))
{
throw (new QException("providerId does not match an existing provider named providerName"));
}
providerName = provider.getValueString("name");
}
EmailRequest request = new EmailRequest().withTemplateName(input.getValueString("templateName")).withFromAddressId(input.getValueInteger("fromAddressId"))
.withSubject(input.getValueString("subject")).withMarkdownBody(input.getValueString("markdownBody"))
.withHtmlBody(input.getValueString("htmlBody")).withTextBody(input.getValueString("textBody"))
.withReplyTo(input.getValueString("replyTo")).withProviderName(input.getValueString("providerName"))
.withReplyTo(input.getValueString("replyTo")).withProviderName(providerName)
.withBrandName(input.getValueString("brandName")).withIdempotencyKey(input.getValueString("idempotencyKey"))
.withLinkedRecord(input.getValueString("linkedTableName"), input.getValueString("linkedRecordId"));
if(input.getValueString("to") != null)
@@ -73,7 +73,7 @@ class EmailMetadataTest
void producerStandardsAndIntegrationFields() throws Exception
{
var instance = QContext.getQInstance();
assertThat(instance.getTables()).hasSize(9);
assertThat(instance.getTables()).hasSize(10);
for(var table : instance.getTables().values())
{
assertThat(table.getName()).matches("email_[a-z_]+");
@@ -0,0 +1,489 @@
/*
* QQQ - Low-code Application Framework for Engineers.
* Copyright (C) 2021-2025. Kingsrook, LLC
* 651 N Broad St Ste 205 # 6917 | Middletown DE 19709 | United States
* contact@kingsrook.com
* https://github.com/Kingsrook/
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* https://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package com.kingsrook.qbits.email;
import java.io.Serializable;
import java.util.List;
import java.util.Map;
import com.kingsrook.qbits.email.metadata.EmailTables;
import com.kingsrook.qbits.email.outbox.EmailRequest;
import com.kingsrook.qbits.email.outbox.EmailSendResult;
import com.kingsrook.qbits.email.outbox.EmailService;
import com.kingsrook.qbits.email.permissions.EmailPermissionChecker;
import com.kingsrook.qbits.email.processes.EmailProcesses;
import com.kingsrook.qbits.email.support.EmailStore;
import com.kingsrook.qbits.email.support.SystemWrites;
import com.kingsrook.qqq.backend.core.actions.processes.RunProcessAction;
import com.kingsrook.qqq.backend.core.actions.tables.DeleteAction;
import com.kingsrook.qqq.backend.core.actions.tables.InsertAction;
import com.kingsrook.qqq.backend.core.actions.tables.UpdateAction;
import com.kingsrook.qqq.backend.core.actions.values.SearchPossibleValueSourceAction;
import com.kingsrook.qqq.backend.core.context.QContext;
import com.kingsrook.qqq.backend.core.model.actions.processes.RunProcessInput;
import com.kingsrook.qqq.backend.core.model.actions.tables.delete.DeleteInput;
import com.kingsrook.qqq.backend.core.model.actions.tables.insert.InsertInput;
import com.kingsrook.qqq.backend.core.model.actions.tables.query.QueryInput;
import com.kingsrook.qqq.backend.core.model.actions.tables.update.UpdateInput;
import com.kingsrook.qqq.backend.core.model.actions.values.SearchPossibleValueSourceInput;
import com.kingsrook.qqq.backend.core.model.data.QRecord;
import com.kingsrook.qqq.backend.core.model.metadata.fields.QFieldMetaData;
import com.kingsrook.qqq.backend.core.model.metadata.possiblevalues.PossibleValueSearchFilterUseCase;
import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import static org.assertj.core.api.Assertions.assertThat;
import static org.assertj.core.api.Assertions.assertThatThrownBy;
/*******************************************************************************
** Provider sender addresses: validation, sending rules, the fallback to the
** provider's own from address and every contingent possible-value pair.
*******************************************************************************/
class EmailSenderAddressTest
{
private QRecord relay;
private QRecord other;
/*******************************************************************************
** Two providers on an isolated database.
*******************************************************************************/
@BeforeEach
void setup() throws Exception
{
H2Support.setup(c ->
{
});
relay = H2Support.provider(25);
other = H2Support.insert(EmailTables.PROVIDER, new QRecord().withValue("name", "other").withValue("kind", "SMTP").withValue("host", "127.0.0.1")
.withValue("port", 25).withValue("security", "NONE").withValue("fromAddress", "other@example.com").withValue("enabled", true).withValue("isDefault", false));
}
/*******************************************************************************
** Clear thread state.
*******************************************************************************/
@AfterEach
void cleanup()
{
QContext.clear();
}
/*******************************************************************************
** A sender address row for a provider.
*******************************************************************************/
private static QRecord row(QRecord provider, String address)
{
return (new QRecord().withValue("providerId", provider.getValue("id")).withValue("address", address).withValue("enabled", true).withValue("isDefault", false));
}
/*******************************************************************************
** Insert through the table action and return the (possibly errored) result.
*******************************************************************************/
private static QRecord tryInsert(QRecord row) throws Exception
{
return (new InsertAction().execute(new InsertInput(EmailTables.SENDER).withRecords(List.of(row))).getRecords().get(0));
}
/*******************************************************************************
** A one-recipient request.
*******************************************************************************/
private static EmailRequest request(String providerName)
{
return (new EmailRequest().withProviderName(providerName).withTo("person@example.net").withSubject("Hello").withTextBody("Body"));
}
/*******************************************************************************
** Addresses are canonical lowercase and line breaks, bad addresses are refused.
*******************************************************************************/
@Test
void addressIsCanonicalAndSafe() throws Exception
{
QRecord saved = H2Support.insert(EmailTables.SENDER, row(relay, "Sales@Example.COM"));
assertThat(saved.getValueString("address")).isEqualTo("sales@example.com");
assertThat(tryInsert(row(relay, "a@example.com\nBcc: x@example.net")).getErrors()).isNotEmpty();
assertThat(tryInsert(row(relay, "not an address")).getErrors()).isNotEmpty();
assertThat(tryInsert(row(relay, "ok@example.com").withValue("displayName", "Evil\r\nBcc: x@example.net")).getErrors()).isNotEmpty();
assertThat(tryInsert(new QRecord().withValue("providerId", 9999).withValue("address", "x@example.com")).getErrors()).isNotEmpty();
}
/*******************************************************************************
** The domain must equal the provider sending domain when one is set.
*******************************************************************************/
@Test
void domainMustMatchSendingDomain() throws Exception
{
SystemWrites.updateOne(EmailTables.PROVIDER, new QRecord().withValue("id", relay.getValue("id")).withValue("sendingDomain", "example.com"));
assertThat(tryInsert(row(relay, "x@other.org")).getErrors()).isNotEmpty();
assertThat(tryInsert(row(relay, "x@EXAMPLE.com")).getErrors()).isNullOrEmpty();
QRecord off = tryInsert(row(other, "x@other.org"));
assertThat(off.getErrors()).isNullOrEmpty();
var update = new UpdateAction().execute(new UpdateInput(EmailTables.PROVIDER).withRecords(List.of(new QRecord().withValue("id", other.getValue("id")).withValue("sendingDomain", "example.com"))));
assertThat(update.getRecords().get(0).getErrors()).isNotEmpty();
}
/*******************************************************************************
** Unique per provider; the same address may belong to another provider.
*******************************************************************************/
@Test
void addressIsUniquePerProvider() throws Exception
{
H2Support.insert(EmailTables.SENDER, row(relay, "sales@example.com"));
assertThat(tryInsert(row(relay, "SALES@example.com")).getErrors()).isNotEmpty();
assertThat(tryInsert(row(other, "sales@example.com")).getErrors()).isNullOrEmpty();
var both = new InsertAction().execute(new InsertInput(EmailTables.SENDER).withRecords(List.of(row(relay, "twin@example.com"), row(relay, "twin@example.com")))).getRecords();
assertThat(both.stream().filter(r -> r.getErrors() != null && !r.getErrors().isEmpty())).isNotEmpty();
}
/*******************************************************************************
** At most one default per provider, and a default must be enabled.
*******************************************************************************/
@Test
void singleEnabledDefault() throws Exception
{
H2Support.insert(EmailTables.SENDER, row(relay, "a@example.com").withValue("isDefault", true));
assertThat(tryInsert(row(relay, "b@example.com").withValue("isDefault", true)).getErrors()).isNotEmpty();
assertThat(tryInsert(row(other, "c@example.com").withValue("isDefault", true)).getErrors()).isNullOrEmpty();
assertThat(tryInsert(row(relay, "d@example.com").withValue("isDefault", true).withValue("enabled", false)).getErrors()).isNotEmpty();
QRecord plain = H2Support.insert(EmailTables.SENDER, row(relay, "e@example.com"));
var promote = new UpdateAction().execute(new UpdateInput(EmailTables.SENDER).withRecords(List.of(new QRecord().withValue("id", plain.getValue("id")).withValue("isDefault", true))));
assertThat(promote.getRecords().get(0).getErrors()).isNotEmpty();
}
/*******************************************************************************
** Deleting a provider removes its sender addresses.
*******************************************************************************/
@Test
void deletingProviderRemovesSenderAddresses() throws Exception
{
H2Support.insert(EmailTables.SENDER, row(other, "gone@example.com"));
new DeleteAction().execute(new DeleteInput(EmailTables.PROVIDER).withPrimaryKey(other.getValue("id")));
assertThat(H2Support.all(EmailTables.SENDER)).isEmpty();
}
/*******************************************************************************
** Sender rows are admin-only, like providers.
*******************************************************************************/
@Test
void senderRowsAreAdminOnly() throws Exception
{
var table = QContext.getQInstance().getTable(EmailTables.SENDER);
assertThat(table.getPermissionRules().getPermissionBaseName()).isEqualTo("email");
assertThat(EmailTables.MAIN).doesNotContain(EmailTables.SENDER);
assertThat(QContext.getQInstance().getJoin(EmailTables.PROVIDER + "_join_" + EmailTables.SENDER)).isNotNull();
assertThat(QContext.getQInstance().getWidget(EmailTables.SENDER + "_children")).isNotNull();
QContext.getQSession().setPermissions(java.util.Set.of("email.manage"));
var checker = new EmailPermissionChecker();
assertThatThrownBy(() -> checker.checkPermissionsThrowing(new InsertInput(EmailTables.SENDER), table)).hasMessageContaining("email.admin");
assertThatThrownBy(() -> checker.checkPermissionsThrowing(new QueryInput(EmailTables.SENDER), table)).hasMessageContaining("email.admin");
QContext.getQSession().setPermissions(java.util.Set.of("email.admin"));
checker.checkPermissionsThrowing(new InsertInput(EmailTables.SENDER), table);
}
/*******************************************************************************
** Sending with a chosen id: ok, another provider's row, and a disabled row.
*******************************************************************************/
@Test
void sendWithFromAddressId() throws Exception
{
QRecord mine = H2Support.insert(EmailTables.SENDER, row(relay, "Sales@example.com").withValue("displayName", "Sales Desk"));
QRecord theirs = H2Support.insert(EmailTables.SENDER, row(other, "help@example.com"));
QRecord off = H2Support.insert(EmailTables.SENDER, row(relay, "old@example.com").withValue("enabled", false));
EmailSendResult ok = EmailService.send(request("relay").withFromAddressId(mine.getValueInteger("id")));
assertThat(ok.status()).isEqualTo(EmailSendResult.Status.QUEUED);
QRecord stored = EmailStore.get(EmailTables.MESSAGE, ok.messageId(), null);
assertThat(stored.getValueString("fromAddress")).contains("sales@example.com").contains("Sales Desk");
assertThat(stored.getValueInteger("fromAddressId")).isEqualTo(mine.getValueInteger("id"));
EmailSendResult wrong = EmailService.send(request("relay").withFromAddressId(theirs.getValueInteger("id")));
assertThat(wrong.status()).isEqualTo(EmailSendResult.Status.REFUSED);
assertThat(wrong.reasons()).containsExactly("That from address is not set up for provider relay");
EmailSendResult disabled = EmailService.send(request("relay").withFromAddressId(off.getValueInteger("id")));
assertThat(disabled.status()).isEqualTo(EmailSendResult.Status.REFUSED);
assertThat(disabled.reasons()).containsExactly("That from address is not set up for provider relay");
assertThat(EmailService.send(request("relay").withFromAddressId(4242)).status()).isEqualTo(EmailSendResult.Status.REFUSED);
assertThat(EmailService.send(request("relay").withFromAddressId(mine.getValueInteger("id")).withFrom("other@example.com")).status()).isEqualTo(EmailSendResult.Status.REFUSED);
assertThat(H2Support.all(EmailTables.MESSAGE)).hasSize(1);
}
/*******************************************************************************
** Once a provider has rows an explicit from text must match an enabled one.
*******************************************************************************/
@Test
void fromTextMustMatchRowsWhenTheyExist() throws Exception
{
QRecord listed = H2Support.insert(EmailTables.SENDER, row(relay, "sales@example.com"));
H2Support.insert(EmailTables.SENDER, row(relay, "old@example.com").withValue("enabled", false));
EmailSendResult ok = EmailService.send(request("relay").withFrom("Sales Team <SALES@example.com>"));
assertThat(ok.status()).isEqualTo(EmailSendResult.Status.QUEUED);
QRecord stored = EmailStore.get(EmailTables.MESSAGE, ok.messageId(), null);
assertThat(stored.getValueString("fromAddress")).contains("Sales Team");
assertThat(stored.getValueInteger("fromAddressId")).isEqualTo(listed.getValueInteger("id"));
EmailSendResult unlisted = EmailService.send(request("relay").withFrom("random@example.com"));
assertThat(unlisted.status()).isEqualTo(EmailSendResult.Status.REFUSED);
assertThat(unlisted.reasons()).containsExactly("That from address is not set up for provider relay");
assertThat(EmailService.send(request("relay").withFrom("old@example.com")).status()).isEqualTo(EmailSendResult.Status.REFUSED);
assertThat(EmailService.send(request("relay").withFrom("sender@example.com")).status()).isEqualTo(EmailSendResult.Status.REFUSED);
assertThat(EmailService.send(request("relay").withFrom("not an address")).status()).isEqualTo(EmailSendResult.Status.REFUSED);
}
/*******************************************************************************
** Without rows, behavior is unchanged: any from text, and the provider's from address and name.
*******************************************************************************/
@Test
void fallsBackToProviderFromAddress() throws Exception
{
SystemWrites.updateOne(EmailTables.PROVIDER, new QRecord().withValue("id", relay.getValue("id")).withValue("fromName", "Relay Team"));
EmailSendResult plain = EmailService.send(request("relay"));
QRecord stored = EmailStore.get(EmailTables.MESSAGE, plain.messageId(), null);
assertThat(stored.getValueString("fromAddress")).contains("sender@example.com").contains("Relay Team");
assertThat(stored.getValue("fromAddressId")).isNull();
EmailSendResult explicit = EmailService.send(request("relay").withFrom("anyone@example.org"));
assertThat(explicit.status()).isEqualTo(EmailSendResult.Status.QUEUED);
assertThat(EmailStore.get(EmailTables.MESSAGE, explicit.messageId(), null).getValueString("fromAddress")).isEqualTo("anyone@example.org");
H2Support.insert(EmailTables.SENDER, row(other, "help@example.com"));
EmailSendResult unaffected = EmailService.send(request("relay").withFrom("anyone@example.org"));
assertThat(unaffected.status()).isEqualTo(EmailSendResult.Status.QUEUED);
}
/*******************************************************************************
** An enabled default row wins over the provider's own from address; a disabled one does not.
*******************************************************************************/
@Test
void defaultRowWins() throws Exception
{
QRecord preferred = H2Support.insert(EmailTables.SENDER, row(relay, "team@example.com").withValue("displayName", "The Team").withValue("isDefault", true));
EmailSendResult result = EmailService.send(request("relay"));
QRecord stored = EmailStore.get(EmailTables.MESSAGE, result.messageId(), null);
assertThat(stored.getValueString("fromAddress")).contains("team@example.com").contains("The Team");
assertThat(stored.getValueInteger("fromAddressId")).isEqualTo(preferred.getValueInteger("id"));
SystemWrites.updateOne(EmailTables.SENDER, new QRecord().withValue("id", preferred.getValue("id")).withValue("enabled", false));
QRecord back = EmailStore.get(EmailTables.MESSAGE, EmailService.send(request("relay")).messageId(), null);
assertThat(back.getValueString("fromAddress")).isEqualTo("sender@example.com");
assertThat(back.getValue("fromAddressId")).isNull();
}
/*******************************************************************************
** A resend keeps the original sender row.
*******************************************************************************/
@Test
void resendKeepsSenderRow() throws Exception
{
QRecord mine = H2Support.insert(EmailTables.SENDER, row(relay, "sales@example.com"));
EmailSendResult sent = EmailService.send(request("relay").withFromAddressId(mine.getValueInteger("id")));
SystemWrites.updateOne(EmailTables.MESSAGE, new QRecord().withValue("id", sent.messageId()).withValue("status", "DEAD_LETTER"));
EmailSendResult again = EmailService.resend(sent.messageId());
assertThat(again.status()).isEqualTo(EmailSendResult.Status.QUEUED);
assertThat(EmailStore.get(EmailTables.MESSAGE, again.messageId(), null).getValueInteger("fromAddressId")).isEqualTo(mine.getValueInteger("id"));
}
/*******************************************************************************
** The templated and ad hoc send processes accept providerId and fromAddressId.
*******************************************************************************/
@Test
void sendProcessAcceptsProviderAndFromAddressIds() throws Exception
{
QRecord mine = H2Support.insert(EmailTables.SENDER, row(relay, "sales@example.com"));
QRecord theirs = H2Support.insert(EmailTables.SENDER, row(other, "help@example.com"));
Map<String, Serializable> values = Map.of("to", "person@example.net", "subject", "Hi", "textBody", "Body", "providerId", relay.getValueInteger("id"), "fromAddressId", mine.getValueInteger("id"));
var input = new RunProcessInput();
input.setProcessName(EmailProcesses.SEND_AD_HOC);
values.forEach(input::addValue);
assertThat(new RunProcessAction().execute(input).getValues().get("status")).isEqualTo("QUEUED");
var wrong = new RunProcessInput();
wrong.setProcessName(EmailProcesses.SEND_AD_HOC);
values.forEach(wrong::addValue);
wrong.addValue("fromAddressId", theirs.getValueInteger("id"));
assertThat(new RunProcessAction().execute(wrong).getValues().get("status")).isEqualTo("REFUSED");
var named = new RunProcessInput();
named.setProcessName(EmailProcesses.SEND_AD_HOC);
values.forEach(named::addValue);
named.addValue("providerName", "relay");
assertThat(new RunProcessAction().execute(named).getValues().get("status")).isEqualTo("QUEUED");
var mismatch = new RunProcessInput();
mismatch.setProcessName(EmailProcesses.SEND_AD_HOC);
values.forEach(mismatch::addValue);
mismatch.addValue("providerName", "other");
assertThatThrownBy(() -> new RunProcessAction().execute(mismatch)).hasMessageContaining("providerId does not match");
var missing = new RunProcessInput();
missing.setProcessName(EmailProcesses.SEND_AD_HOC);
values.forEach(missing::addValue);
missing.addValue("providerId", 4242);
assertThatThrownBy(() -> new RunProcessAction().execute(missing)).hasMessageContaining("providerId does not match");
}
/*******************************************************************************
** Send Test Email takes an optional from address that must belong to the provider.
*******************************************************************************/
@Test
void sendTestUsesFromAddress() throws Exception
{
QRecord mine = H2Support.insert(EmailTables.SENDER, row(relay, "sales@example.com"));
QRecord theirs = H2Support.insert(EmailTables.SENDER, row(other, "help@example.com"));
var wrong = ProcessRunner.runWithValues(EmailProcesses.SEND_TEST, Map.of("toAddress", "test@example.com", "fromAddressId", theirs.getValueInteger("id")), relay.getValue("id"));
assertThat(wrong.result()).anyMatch(s -> s.contains("REFUSED"));
var ok = ProcessRunner.runWithValues(EmailProcesses.SEND_TEST, Map.of("toAddress", "test@example.com", "fromAddressId", mine.getValueInteger("id")), relay.getValue("id"));
assertThat(ok.result()).anyMatch(s -> s.contains("FAILED") || s.contains("SENT") || s.contains("RETRY") || s.contains("QUEUED"));
assertThat(H2Support.all(EmailTables.MESSAGE)).anyMatch(m -> mine.getValue("id").equals(m.getValue("fromAddressId")));
}
// ----- contingent possible-value pairs -----
/*******************************************************************************
** Assert the filter on a field and that a possible-value search with the parent
** value returns only that parent's rows. The search runs through core's action.
*******************************************************************************/
private static void assertContingent(QFieldMetaData field, String inputName, Serializable parentValue, List<Serializable> expected, List<Serializable> excluded) throws Exception
{
assertThat(field.getPossibleValueSourceFilter()).isNotNull();
var filter = field.getPossibleValueSourceFilter().clone();
assertThat(filter.toString()).contains("${input." + inputName + "}");
filter.interpretValues(Map.of("input", Map.of(inputName, parentValue), "processValues", Map.<String, Serializable>of()), PossibleValueSearchFilterUseCase.FORM);
var found = new SearchPossibleValueSourceAction().execute(new SearchPossibleValueSourceInput().withPossibleValueSourceName(field.getPossibleValueSourceName()).withDefaultQueryFilter(filter));
List<String> ids = found.getResults().stream().map(v -> v.getId().toString()).toList();
assertThat(ids).containsAll(expected.stream().map(String::valueOf).toList());
assertThat(ids).doesNotContainAnyElementsOf(excluded.stream().map(String::valueOf).toList());
}
/*******************************************************************************
** One row each for the two providers.
*******************************************************************************/
private QRecord[] senders() throws Exception
{
return (new QRecord[] { H2Support.insert(EmailTables.SENDER, row(relay, "a@example.com")), H2Support.insert(EmailTables.SENDER, row(other, "b@example.com")) });
}
/*******************************************************************************
** Templated send: fromAddressId follows providerId.
*******************************************************************************/
@Test
void templatedSendFromAddressFollowsProvider() throws Exception
{
QRecord[] rows = senders();
QFieldMetaData field = QContext.getQInstance().getProcess(EmailProcesses.SEND_TEMPLATED).getBackendStep("send").getInputMetaData().getFieldThrowing("fromAddressId");
assertThat(field.getPossibleValueSourceName()).isEqualTo(EmailTables.SENDER);
assertContingent(field, "providerId", relay.getValueInteger("id"), List.of(rows[0].getValueInteger("id")), List.of(rows[1].getValueInteger("id")));
assertContingent(field, "providerId", other.getValueInteger("id"), List.of(rows[1].getValueInteger("id")), List.of(rows[0].getValueInteger("id")));
assertThat(QContext.getQInstance().getProcess(EmailProcesses.SEND_TEMPLATED).getBackendStep("send").getInputMetaData().getFieldThrowing("providerId").getPossibleValueSourceName()).isEqualTo(EmailTables.PROVIDER);
}
/*******************************************************************************
** Ad hoc send: fromAddressId follows providerId.
*******************************************************************************/
@Test
void adHocSendFromAddressFollowsProvider() throws Exception
{
QRecord[] rows = senders();
QFieldMetaData field = QContext.getQInstance().getProcess(EmailProcesses.SEND_AD_HOC).getBackendStep("send").getInputMetaData().getFieldThrowing("fromAddressId");
assertContingent(field, "providerId", relay.getValueInteger("id"), List.of(rows[0].getValueInteger("id")), List.of(rows[1].getValueInteger("id")));
}
/*******************************************************************************
** Send Test Email: fromAddressId follows the provider being acted on, which a
** record action's form only gets from the prepare step's process value.
*******************************************************************************/
@Test
void sendTestFromAddressFollowsActedOnProvider() throws Exception
{
QRecord[] rows = senders();
QFieldMetaData field = QContext.getQInstance().getProcess(EmailProcesses.SEND_TEST).getFrontendStep("input").getFormFields().stream()
.filter(f -> "fromAddressId".equals(f.getName())).findFirst().orElseThrow();
assertContingent(field, "id", relay.getValueInteger("id"), List.of(rows[0].getValueInteger("id")), List.of(rows[1].getValueInteger("id")));
var input = new RunProcessInput();
input.setProcessName(EmailProcesses.SEND_TEST);
input.addValue("recordsParam", "recordIds");
input.addValue("recordIds", String.valueOf(other.getValue("id")));
input.setFrontendStepBehavior(RunProcessInput.FrontendStepBehavior.BREAK);
var seeded = new RunProcessAction().execute(input).getValues().get(EmailProcesses.PROVIDER_FILTER_VALUE);
assertThat(seeded).isEqualTo(other.getValue("id"));
var filter = field.getPossibleValueSourceFilter().clone();
filter.interpretValues(Map.of("input", Map.<String, Serializable>of(), "processValues", Map.of(EmailProcesses.PROVIDER_FILTER_VALUE, (Serializable) seeded)), PossibleValueSearchFilterUseCase.FORM);
var found = new SearchPossibleValueSourceAction().execute(new SearchPossibleValueSourceInput().withPossibleValueSourceName(EmailTables.SENDER).withDefaultQueryFilter(filter));
assertThat(found.getResults().stream().map(v -> v.getId().toString()).toList()).containsExactly(rows[1].getValueString("id"));
var several = new RunProcessInput();
several.setProcessName(EmailProcesses.SEND_TEST);
several.addValue("recordsParam", "recordIds");
several.addValue("recordIds", "1,2");
several.setFrontendStepBehavior(RunProcessInput.FrontendStepBehavior.BREAK);
assertThat(new RunProcessAction().execute(several).getValues()).doesNotContainKey(EmailProcesses.PROVIDER_FILTER_VALUE);
var none = new RunProcessInput();
none.setProcessName(EmailProcesses.SEND_TEST);
none.addValue("recordsParam", "recordIds");
none.addValue("recordIds", "x");
none.setFrontendStepBehavior(RunProcessInput.FrontendStepBehavior.BREAK);
assertThat(new RunProcessAction().execute(none).getValues()).doesNotContainKey(EmailProcesses.PROVIDER_FILTER_VALUE);
}
/*******************************************************************************
** Message: fromAddressId follows providerId, and is read-only.
*******************************************************************************/
@Test
void messageFromAddressFollowsProvider() throws Exception
{
QRecord[] rows = senders();
QFieldMetaData field = QContext.getQInstance().getTable(EmailTables.MESSAGE).getField("fromAddressId");
assertThat(field.getIsEditable()).isFalse();
assertContingent(field, "providerId", relay.getValueInteger("id"), List.of(rows[0].getValueInteger("id")), List.of(rows[1].getValueInteger("id")));
}
/*******************************************************************************
** Attempt and event: the message follows the provider.
*******************************************************************************/
@Test
void attemptAndEventMessageFollowProvider() throws Exception
{
QRecord fromRelay = EmailStore.get(EmailTables.MESSAGE, EmailService.send(request("relay")).messageId(), null);
QRecord fromOther = EmailStore.get(EmailTables.MESSAGE, EmailService.send(request("other")).messageId(), null);
for(String table : List.of(EmailTables.ATTEMPT, EmailTables.EVENT))
{
QFieldMetaData field = QContext.getQInstance().getTable(table).getField("messageId");
assertContingent(field, "providerId", relay.getValueInteger("id"), List.of(fromRelay.getValueInteger("id")), List.of(fromOther.getValueInteger("id")));
assertContingent(field, "providerId", other.getValueInteger("id"), List.of(fromOther.getValueInteger("id")), List.of(fromRelay.getValueInteger("id")));
}
}
/*******************************************************************************
** Suppression: the source event follows the source message.
*******************************************************************************/
@Test
void suppressionSourceEventFollowsSourceMessage() throws Exception
{
QRecord first = EmailStore.get(EmailTables.MESSAGE, EmailService.send(request("relay")).messageId(), null);
QRecord second = EmailStore.get(EmailTables.MESSAGE, EmailService.send(request("other")).messageId(), null);
QRecord e1 = SystemWrites.insertOne(EmailTables.EVENT, new QRecord().withValue("messageId", first.getValue("id")).withValue("eventType", "BOUNCED").withValue("eventKey", "k1"));
QRecord e2 = SystemWrites.insertOne(EmailTables.EVENT, new QRecord().withValue("messageId", second.getValue("id")).withValue("eventType", "BOUNCED").withValue("eventKey", "k2"));
QFieldMetaData field = QContext.getQInstance().getTable(EmailTables.SUPPRESSION).getField("sourceEventId");
assertContingent(field, "sourceMessageId", first.getValueInteger("id"), List.of(e1.getValueInteger("id")), List.of(e2.getValueInteger("id")));
assertContingent(field, "sourceMessageId", second.getValueInteger("id"), List.of(e2.getValueInteger("id")), List.of(e1.getValueInteger("id")));
}
/*******************************************************************************
** Fields without a parent scope stay unfiltered.
*******************************************************************************/
@Test
void independentRelationshipsStayUnfiltered() throws Exception
{
var instance = QContext.getQInstance();
assertThat(instance.getTable(EmailTables.TEMPLATE).getField("brandId").getPossibleValueSourceFilter()).isNull();
assertThat(instance.getTable(EmailTables.MESSAGE).getField("brandId").getPossibleValueSourceFilter()).isNull();
assertThat(instance.getTable(EmailTables.MESSAGE).getField("providerId").getPossibleValueSourceFilter()).isNull();
assertThat(instance.getTable(EmailTables.MESSAGE).getField("templateId").getPossibleValueSourceFilter()).isNull();
assertThat(instance.getTable(EmailTables.VERSION).getField("templateId").getPossibleValueSourceFilter()).isNull();
assertThat(instance.getTable(EmailTables.INBOX).getField("providerId").getPossibleValueSourceFilter()).isNull();
assertThat(instance.getTable(EmailTables.SENDER).getField("providerId").getPossibleValueSourceFilter()).isNull();
}
}
+14
View File
@@ -38,6 +38,19 @@ CREATE TABLE email_provider (
UNIQUE (default_slot)
);
CREATE TABLE email_provider_sender_address (
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
create_date TIMESTAMP,
modify_date TIMESTAMP,
provider_id INTEGER NOT NULL,
address VARCHAR(500) NOT NULL,
display_name VARCHAR(500),
is_default BOOLEAN,
enabled BOOLEAN,
note TEXT,
UNIQUE (provider_id, address)
);
CREATE TABLE email_message (
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
create_date TIMESTAMP,
@@ -47,6 +60,7 @@ CREATE TABLE email_message (
cc_addresses TEXT,
bcc_addresses TEXT,
from_address VARCHAR(500),
from_address_id INTEGER,
reply_to VARCHAR(500),
subject TEXT,
template_id INTEGER,