Merge origin/develop into feature/foreign-keys

Keeps develop's liquibase-core test dependency next to the Postgres Testcontainers dependencies, and both changelog entries. H2 tests load src/test/resources/email-h2.sql (FKs in H2 syntax), never the Postgres DDL. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
Merge pull request #19 from QRun-IO/feature/tables-populated-defaults
2026-10-14 17:50:14 -05:00 · 2026-10-14 17:31:48 -05:00 · 2026-10-14 17:23:56 -05:00 · 2026-10-14 17:13:37 -05:00 · 2026-10-14 17:13:36 -05:00 · 2026-10-14 16:43:57 -05:00
32 changed files with 2717 additions and 69 deletions
@@ -1,7 +1,7 @@
 version: 2.1

 orbs:
-  qqq-orb: kingsrook/qqq-orb@0.6.8
+  qqq-orb: kingsrook/qqq-orb@0.6.9

 commands:
  configure-package-install:
@@ -5,12 +5,27 @@ The format follows [Keep a Changelog](https://keepachangelog.com/en/1.1.0/) and

 ## [Unreleased]

+### Changed
+- Built on QQQ 4.1.0-RC.7 (default QQQ profile).
+
 ### Added
+- Foreign keys between all ten tables in `docs/ddl/email-postgres.sql`, named `fk_<table>_<column>`. CASCADE: a provider's sender addresses and a message's attempts and events. RESTRICT: providers with messages, attempts, events or webhook inbox rows; a brand used by a template; a template with versions. SET NULL: a message's sender address, template, version and brand; a template's active and approved version (the template/version cycle); a suppression's source message and event, so suppressions survive message retention. Ten new indexes cover the unindexed key columns. `docs/ddl/email-foreign-keys-upgrade.sql` adds the keys to an existing database. It first records orphan rows in `qqq_fk_quarantine`: nullable references are set to NULL, and rows whose required parent is missing are deleted and kept as JSON. The script is idempotent. The provider delete check now also refuses providers with attempts, events or inbox rows. `ForeignKeyGuardrailTest` (PostgreSQL 17 via Testcontainers) fails when a join or table possible-value field between email tables has no foreign key.
+- Starter defaults changelog `db/liquibase/qbit-email/email-defaults.changelog.sql` (insert-only, guarded): one non-default, high-contrast `neutral` brand, only when no brand exists. Tested through real Liquibase on H2, including that it never changes rendering.
+- Help for the Email app and every `email_*` table (`help/qbit-email.yaml`, STAND-IN core#1198): what the table is for and how its rows get created, shown when it is empty.
+- A Setup card first on the Email home (email admins), using the sender's own provider and brand resolution and `EmailApi.readiness`. Opening the home also creates the built-in secure-message template.
+- `EmailTablesGuardrailTest`: every table is used by runtime code, seeded or listed as starting empty with a reason, and has help.
+
+### Fixed
+- `email_template.ownerQbit` was stored but never read, and the built-in template did not set it. The built-in now names `qbit-email`, the field is read-only with help, and messages sent from a template name its owner qbit as `senderQbit` unless the request names one.
+
+### Added
+- `EmailJobEffectTarget` automatically registers the durable-jobs `email` target, with idempotent outbox handoff, refusal reasons, reconciliation and job-owner session enforcement; H2 integration coverage and payload/registration documentation.
+- `EmailApiReadiness.failedChecks()` and `failed(check)` with `EmailApiReadinessCheck` (NOT_INSTALLED, PROVIDER_MISSING, PROVIDER_INVALID, PHI_PROVIDER, PUBLIC_BASE_URL, UNSUBSCRIBE_KEY, BRAND_POSTAL_ADDRESS, BODY_KEY), so callers react to a specific prerequisite without parsing reason text.
 - `EmailApi.sendOnce` as the public durable-effect handoff, with API-only outcome/result types, committed enqueue/replay semantics and suppression/transaction refusal parity. Exact idempotency-key and RFC Message-ID lookups return `EmailApiMessage`, which now includes `rfcMessageId` across all lookup routes. Public requests support ordinary caller transactions; `EmailEffects`, `EmailEffectOutcome` and `EmailMessageRef` are deprecated internals. H2 parity, reconciliation, storage-failure and API-boundary coverage; no new dependencies.
 - Stable `com.kingsrook.qbits.email.api` contract for cross-qbit sends, create-once templates, metadata-only message lookups, enabled sender addresses, default provider selection and secret-safe readiness (including marketing and PHI prerequisites). JDK/QQQ-only signatures, complete request/status mapping tests and a reflection package-boundary guard; no new dependencies.
 - Documented both supported cross-qbit routes: core-only `SendMessageAction` via `qbitEmail`, and the public API for richer sends and lookups.
 - Persisted unique RFC Message-IDs, stable across delivery attempts, with SMTP/Gmail SMTP, SES raw MIME and Postmark/SendGrid/Mailgun request propagation (SES replaces the header downstream).
- Core-independent `EmailEffects` handoff, reference lookups by idempotency key or RFC Message-ID, refusal mapping and an RC.7 binding design sketch; offline retry and provider-contract tests.
+- Core-independent `EmailEffects` handoff, reference lookups by idempotency key or RFC Message-ID and refusal mapping; offline retry and provider-contract tests.
 - Checkstyle now checks test sources as well as main sources; existing test imports and formatting corrected.
 - 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`.
@@ -13,7 +13,7 @@ qbit-object-storage is wired in.

 ## Install

-Requires Java 21, QQQ **4.1.0-RC.5**, and a transactional RDBMS with the supplied unique
+Requires Java 21, QQQ **4.1.0-RC.7**, and a transactional RDBMS with the supplied unique
 constraints. Apply [the PostgreSQL DDL](docs/ddl/email-postgres.sql) before producing the qbit. SQL timestamps store UTC values without a timezone, matching QQQ RC.5.
 The physical table names equal their metadata names; `backendName` selects the host's
 QQQ database backend, as it does in qbit-webhooks.
@@ -39,6 +39,22 @@ The scheduler must already exist. Without a scheduler, callers can enqueue and r
 Configuration is looked up from the current `QInstance` on every call, never cached
 in a static variable.

+## Starter defaults and setup
+
+Include order: the DDL first, then the starter defaults from the host's master changelog (the qbit itself never runs DDL):
+
+```yaml
+- include: { file: db/liquibase/qbit-email/email-defaults.changelog.sql }
+```
+
+They are insert-only, hold no secrets, skip a host without the tables, and never change how mail looks on deploy. They add one brand, `neutral` (white background, near-black text, blue buttons, light and dark schemes, every pair at least 4.5:1 contrast), and only when the host has no brand at all. It is not the default brand: copy it for your own, or tick Default Brand on it.
+
+Everything else starts empty on purpose, and each table's help says why: a **provider** is your own connection (a server and the NAMES of its secret variables), so nothing can be sent until you add one; messages, attempts, events, the webhook inbox and suppressions are runtime data; the secure-message template is created by the qbit on first use and when the Email home opens, and other qbits register theirs with `EmailApi.ensureTemplate`.
+
+Every table has help that says what it is for and **how its rows get created**, shown on the table screen and when it is empty (`help/qbit-email.yaml`). Email admins also get a **Setup** card first on the home: provider, sender addresses, brands, templates, marketing readiness, delivery tracking, suppression list and scheduler, each with its state and the next step. It uses the same provider and brand resolution as the sender and `EmailApi.readiness`.
+
+A template's **Owner Qbit** (the qbit that registered it) is now used: messages sent from the template name that qbit as their sender unless the request names one.
+
 ## Tables and permissions

 | Table | Navigation | Writes |
@@ -588,23 +604,29 @@ PHI canary egress, encryption, preview widgets and home data.

 ## Durable jobs

-`EmailEffects.send(request, effectIdempotencyKey)` hands a message to the committed
-email outbox exactly once per key and returns an `EmailEffects.Result` containing
-`EmailEffectOutcome` and an `EmailMessageRef`. `QUEUED_OR_DUPLICATE` means the outbox
-accepted responsibility; `REFUSED` means a definite refusal, including suppression.
-Use `findByIdempotencyKey` or `findByRfcMessageId` to reconcile a handoff. These are
-trusted system reads returning only references. Database failures propagate.
-Requests must not carry a caller transaction; the effect dispatcher calls after its
-own chunk commit. Existing `EmailService` transaction support is unchanged.
+Built on QQQ **4.1.0-RC.7**. Enable `DurableJobsMetaData` and define the jobs tables
+**before** producing the email qbit: `EmailQBitProducer` automatically registers
+`EmailJobEffectTarget` under target name `email`. If jobs are enabled later, call
+`EmailJobEffectTarget.register(instance)` afterward. Registration is a no-op when
+jobs are disabled.

-Each new message stores a unique `rfcMessageId` once. SMTP (including the Gmail preset),
-Postmark, SendGrid and Mailgun carry it on every attempt. SES receives it in the raw
-request but replaces it downstream; every transport retains `X-QQQ-Email-Id`.
-Message-ID enables correlation and receiver deduplication where supported; it does
-not promise exactly-once inbox delivery.
+Declare `new JobEffectDeclaration("welcomeEmail", "<step>", "email",
+JobEffectType.IDEMPOTENT_EXTERNAL)` in the process's `DurableProcessMetaData`, then
+call `JobEffects.emit(input, "welcomeEmail", rowKey, payload)` in the backend step.
+The payload accepts recipients, template or ad-hoc bodies, variables, routing and
+record linkage; see the [complete payload contract and examples](docs/DURABLE-JOBS.md).

-See [the handoff contract, provider references, schema upgrade and RC.7 binding sketch](docs/DURABLE-JOBS.md).
-The build remains on released QQQ **4.1.0-RC.5**, without durable-jobs dependencies.
+The target deduplicates handoffs by the effect key. SENT means the email outbox has
+committed, while its dispatcher owns provider delivery and retries. Job effects
+send under the job owner's current session and captured security scope; an owner
+whose session cannot be established produces FAILED. Other exceptions produce
+UNKNOWN and can safely be retried using the same key. Reconcile finds the existing
+outbox row, including terminal audit rows.
+
+Each message retains its stored RFC Message-ID across provider attempts. SMTP,
+Postmark, SendGrid and Mailgun carry it; SES replaces it downstream. This supports
+correlation and receiver deduplication where available, without guaranteeing
+exactly-once inbox delivery. See the [schema upgrade and provider notes](docs/DURABLE-JOBS.md).

 ## License

@@ -0,0 +1,13 @@
+# Upgrading
+
+## Unreleased: starter defaults, setup card, table help
+
+1. **Add the defaults changelog** after the changesets that create the `email_*` tables (`docs/ddl/email-postgres.sql`) in the host's master changelog:
+
+   ```yaml
+   - include: { file: db/liquibase/qbit-email/email-defaults.changelog.sql }
+   ```
+
+   It is insert-only and guarded: a host that already has any brand gets nothing, a host without the table skips it. The `neutral` brand it adds is not the default brand, so existing mail renders exactly as before.
+2. **Behavior change.** Messages sent from a template whose Owner Qbit is set, with no sender named in the request, now record that qbit as `senderQbit` (it was empty). Nothing else about sending changes.
+3. The help bundle and the Setup card need no configuration. Existing built-in templates created before this release keep an empty Owner Qbit.
@@ -1,8 +1,8 @@
 # Durable job email effects

-This implements the email-side contract for QRun-IO/qqq#1191, spec section 2.13,
-without importing core jobs types. QQQ stays at released 4.1.0-RC.5. The
-`EmailJobEffectTarget implements EffectTarget` binding is deferred until RC.7 ships.
+QQQ 4.1.0-RC.7 supplies the durable jobs side-effects outbox. QBit Email registers
+`com.kingsrook.qbits.email.outbox.EmailJobEffectTarget` as target `email` to hand
+those effects to its own committed outbox. All provider sending remains asynchronous.

 ## Stored identity and schema upgrade

@@ -86,44 +86,101 @@ existing outbox row (including an audit row); it must never create another email
 merely because that row is terminal. Retain message rows and idempotency keys for
 at least the maximum effect retry/reconciliation horizon.

-## RC.7 binding design note (not compiled)
+## Register, declare and emit

-Mapping: `deliver -> QUEUED_OR_DUPLICATE => SENT(ref.id)`, `REFUSED => FAILED`;
-`reconcile -> getMessageByIdempotencyKey present => FOUND(ref.id), absent => NOT_FOUND`.
-Exceptions escape to core's retry/unknown handling. Payload decoding must preserve
-the existing PHI/variable policies and must not attach a caller transaction.
-
-This 25-line sketch is documentation-only. Match core names and signatures to the
-published RC.7 contract when it ships; `decode` represents payload conversion.
+Enable durable jobs **before** producing the email qbit, so the producer can register
+its target automatically:

 ```java
-// Future RC.7 binding; no core jobs imports in the RC.5 implementation.
-final class EmailJobEffectTarget implements EffectTarget
-{
-   // SENT acknowledges the committed qbit outbox row.
-   @Override
-   public DeliveryResult deliver(Effect effect) throws Exception
-   {
-      EmailApiRequest request = decode(effect.payload());
-      EmailApiEffectResult result = EmailApi.sendOnce(request, effect.idempotencyKey());
-      if(result.outcome() == EmailApiEffectOutcome.REFUSED)
-      {
-         return (DeliveryResult.failed("Email refused"));
-      }
-      return (DeliveryResult.sent(result.message().id().toString()));
-   }
-
-   // Existence reconciles handoff independently of mailbox delivery.
-   @Override
-   public ReconcileResult reconcile(Effect effect) throws Exception
-   {
-      return (EmailApi.getMessageByIdempotencyKey(effect.idempotencyKey())
-         .map(ref -> ReconcileResult.found(ref.id().toString()))
-         .orElseGet(ReconcileResult::notFound));
-   }
-}
+instance.withSupplementalMetaData(new DurableJobsMetaData());
+new DurableJobsMetaDataProvider().defineAll(instance, "rdbms", null);
+new EmailQBitProducer()
+   .withQBitConfig(new EmailQBitConfig().withBackendName("rdbms"))
+   .produce(instance);
 ```

+Apply core's jobs database schema as usual. `EmailJobEffectTarget.register(instance)`
+is a no-op without `DurableJobsMetaData`. If jobs are enabled after producing email,
+call this helper explicitly after enabling them.
+
+Declare the effect on the process's `DurableProcessMetaData` (retain any existing
+durable settings). Replace `<step>` with the process's real backend step name:
+
+```java
+process.withSupplementalMetaData(new DurableProcessMetaData()
+   .withEffect(new JobEffectDeclaration("welcomeEmail", "<step>", "email",
+      JobEffectType.IDEMPOTENT_EXTERNAL)));
+```
+
+Then emit from that step using its `RunBackendStepInput input` and a stable row key:
+
+```java
+Map<String, Serializable> payload = new LinkedHashMap<>();
+payload.put("to", "person@example.com");
+payload.put("subject", "Welcome");
+payload.put("textBody", "Your account is ready.");
+JobEffects.emit(input, "welcomeEmail", rowKey, payload);
+```
+
+The jobs classes above come from `com.kingsrook.qqq.backend.core.jobs` and its
+`effects` package; `Map`, `LinkedHashMap` and `Serializable` are JDK types.
+Core stores the effect with the chunk's progress and dispatches after commit.
+Outside a durable job, `JobEffects.emit` calls the target immediately with a fresh
+key, still only enqueueing email; a result other than SENT throws.
+
+Use **IDEMPOTENT_EXTERNAL**: qbit-email deduplicates by the effect's exact key,
+while its own delivery outbox reuses the stored Message-ID on provider attempts.
+This acknowledges one durable handoff, not exactly-once delivery to a mailbox.
+
+## Payload contract
+
+Unknown keys are ignored. Recognized values must have the following types:
+
+| Keys | Values |
+|---|---|
+| `to`, `cc`, `bcc` | A String mailbox or a list of String mailboxes; omitted means empty. Comma-separated strings are not split. |
+| `subject`, `templateName` | Strings; ordinary template/ad-hoc validation applies. |
+| `markdownBody`, `htmlBody`, `textBody` | String body alternatives. |
+| `variables` | Map with String keys and serializable values; requires a template schema and retains the normal PHI/classification checks. |
+| `category` | String enum name: `TRANSACTIONAL`, `SECURITY`, `NOTIFICATION`, or `MARKETING`. |
+| `providerName`, `brandName`, `locale` | Strings; omitted locale retains `en`. |
+| `from`, `replyTo` | String mailboxes, subject to ordinary sender validation. |
+| `fromAddressId` | Integer identifying an enabled address for the selected provider. |
+| `sendAfter` | ISO-8601 Instant String, for example `2026-10-14T12:00:00Z`. |
+| `linkedTableName`, `linkedRecordId` | String table name; record id converted to String. |
+
+The target always sets `senderQbit = "jobs"`, `senderProcess = effectName`, and
+uses `EffectRequest.idempotencyKey()`. Payload keys cannot override these or attach
+a transaction. `recordVariables`, `securityValues` and `payloadRef` are not decoded;
+provide the supported values directly in `payload`.
+
+## Identity and outcomes
+
+The dispatcher invokes the target as the system user. When `jobId` is present, the
+target loads the job under core's system scope and builds its owner session through
+`JobSessions.forJob`. Sending runs inside `QContext.withTemporaryContext` using that
+owner session, with current permissions and the intersection of current and captured
+security keys. The original dispatcher context is restored even after an exception.
+Without a job id, sending retains the caller's session.
+
+- `QUEUED_OR_DUPLICATE` becomes `EffectResult.sent(outboxId)`; `providerRef` is the
+  String email row id, not the transport provider's reference.
+- `REFUSED` becomes `EffectResult.failed(reasons)`, including suppressed replays.
+- A missing job or an owner session rejected by authentication/permission validation
+  becomes FAILED with `The job's owner can no longer send it: ...`.
+- Other exceptions, including payload decoding and database failures, become UNKNOWN.
+  Error text omits raw exception details to avoid exposing payloads or database values.
+  Core may resend IDEMPOTENT_EXTERNAL effects using the same key.
+- Reconcile uses `EmailEffects.findByIdempotencyKey`: any existing row becomes FOUND
+  with its id, otherwise NOT_FOUND. Lookup failures propagate rather than falsely
+  reporting absence. Reconcile never creates mail or changes the owner session.
+
+The binding uses `EmailEffects` internally; other consuming qbits continue to use
+`EmailApi`. The H2 target tests install core jobs metadata with `defineAll`, create
+all columns of the `jobs_job` lookup table, and observe the owner through an insert
+customizer because email messages have no built-in created-by/user field. No
+production job-lookup test seam is needed.
+
 Tests use H2, the existing GreenMail SMTP sink, fake SES and HTTP request seams;
 no vendor endpoints are called. The PostgreSQL migration is supplied for host
 deployment and is not executed by the offline Java test gate.
@@ -0,0 +1,89 @@
+-- qbit-email: add the foreign keys to an existing PostgreSQL schema (one
+-- created from email-postgres.sql before they were declared there). Pause
+-- enqueue, dispatch and webhook processing while it runs.
+--
+-- Safe to run more than once: a foreign key that already exists with the
+-- right ON DELETE (under any name) is left alone. Before each key is added,
+-- rows that point at a missing parent are recorded in qqq_fk_quarantine and
+-- then set to NULL (nullable column) or deleted (NOT NULL column; the whole
+-- row is kept in row_data). An existing key on the column with a different
+-- ON DELETE is replaced by the named one.
+--
+-- Order matters: sender addresses, attempts and events of a missing parent are
+-- removed before the references to them (message.from_address_id,
+-- suppression.source_event_id) are checked.
+
+BEGIN;
+
+CREATE TABLE IF NOT EXISTS qqq_fk_quarantine (id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, table_name VARCHAR(100) NOT NULL, column_name VARCHAR(100) NOT NULL, row_id VARCHAR(100), missing_parent_id VARCHAR(100), action VARCHAR(20) NOT NULL, row_data TEXT, changeset VARCHAR(200), quarantined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
+
+CREATE OR REPLACE FUNCTION pg_temp.qqq_add_fk(p_table TEXT, p_column TEXT, p_parent TEXT, p_on_delete TEXT) RETURNS VOID LANGUAGE plpgsql AS
+$fn$
+DECLARE
+   v_name      TEXT    := 'fk_' || p_table || '_' || p_column;
+   v_rule      "char"  := CASE p_on_delete WHEN 'CASCADE' THEN 'c' WHEN 'SET NULL' THEN 'n' WHEN 'RESTRICT' THEN 'r' ELSE 'a' END;
+   v_nullable  BOOLEAN;
+   v_changeset TEXT    := 'qbit-email:email-foreign-keys-upgrade.sql';
+   v_old       RECORD;
+BEGIN
+   IF EXISTS (SELECT 1 FROM pg_constraint c JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = c.conkey[1]
+              WHERE c.contype = 'f' AND c.conrelid = p_table::regclass AND cardinality(c.conkey) = 1 AND a.attname = p_column
+                AND c.confrelid = p_parent::regclass AND c.confdeltype = v_rule) THEN
+      RETURN;
+   END IF;
+
+   FOR v_old IN SELECT c.conname FROM pg_constraint c JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = c.conkey[1]
+                WHERE c.contype = 'f' AND c.conrelid = p_table::regclass AND cardinality(c.conkey) = 1 AND a.attname = p_column LOOP
+      EXECUTE format('ALTER TABLE %I DROP CONSTRAINT %I', p_table, v_old.conname);
+   END LOOP;
+
+   SELECT is_nullable = 'YES' INTO v_nullable FROM information_schema.columns
+   WHERE table_schema = current_schema() AND table_name = p_table AND column_name = p_column;
+
+   IF v_nullable THEN
+      EXECUTE format('INSERT INTO qqq_fk_quarantine (table_name, column_name, row_id, missing_parent_id, action, changeset) '
+         || 'SELECT %L, %L, t.id::TEXT, t.%I::TEXT, ''NULLED'', %L FROM %I t WHERE t.%I IS NOT NULL AND NOT EXISTS (SELECT 1 FROM %I p WHERE p.id = t.%I)',
+         p_table, p_column, p_column, v_changeset, p_table, p_column, p_parent, p_column);
+      EXECUTE format('UPDATE %I t SET %I = NULL WHERE t.%I IS NOT NULL AND NOT EXISTS (SELECT 1 FROM %I p WHERE p.id = t.%I)',
+         p_table, p_column, p_column, p_parent, p_column);
+   ELSE
+      EXECUTE format('INSERT INTO qqq_fk_quarantine (table_name, column_name, row_id, missing_parent_id, action, row_data, changeset) '
+         || 'SELECT %L, %L, t.id::TEXT, t.%I::TEXT, ''DELETED'', row_to_json(t)::TEXT, %L FROM %I t WHERE NOT EXISTS (SELECT 1 FROM %I p WHERE p.id = t.%I)',
+         p_table, p_column, p_column, v_changeset, p_table, p_parent, p_column);
+      EXECUTE format('DELETE FROM %I t WHERE NOT EXISTS (SELECT 1 FROM %I p WHERE p.id = t.%I)', p_table, p_parent, p_column);
+   END IF;
+
+   EXECUTE format('ALTER TABLE %I ADD CONSTRAINT %I FOREIGN KEY (%I) REFERENCES %I (id) ON DELETE %s', p_table, v_name, p_column, p_parent, p_on_delete);
+END
+$fn$;
+
+SELECT pg_temp.qqq_add_fk('email_provider_sender_address', 'provider_id', 'email_provider', 'CASCADE');
+SELECT pg_temp.qqq_add_fk('email_message', 'provider_id', 'email_provider', 'RESTRICT');
+SELECT pg_temp.qqq_add_fk('email_message', 'from_address_id', 'email_provider_sender_address', 'SET NULL');
+SELECT pg_temp.qqq_add_fk('email_message', 'template_id', 'email_template', 'SET NULL');
+SELECT pg_temp.qqq_add_fk('email_message', 'template_version_id', 'email_template_version', 'SET NULL');
+SELECT pg_temp.qqq_add_fk('email_message', 'brand_id', 'email_brand', 'SET NULL');
+SELECT pg_temp.qqq_add_fk('email_template', 'brand_id', 'email_brand', 'RESTRICT');
+SELECT pg_temp.qqq_add_fk('email_template', 'active_version_id', 'email_template_version', 'SET NULL');
+SELECT pg_temp.qqq_add_fk('email_template', 'approved_version_id', 'email_template_version', 'SET NULL');
+SELECT pg_temp.qqq_add_fk('email_template_version', 'template_id', 'email_template', 'RESTRICT');
+SELECT pg_temp.qqq_add_fk('email_message_attempt', 'message_id', 'email_message', 'CASCADE');
+SELECT pg_temp.qqq_add_fk('email_message_attempt', 'provider_id', 'email_provider', 'RESTRICT');
+SELECT pg_temp.qqq_add_fk('email_event', 'message_id', 'email_message', 'CASCADE');
+SELECT pg_temp.qqq_add_fk('email_event', 'provider_id', 'email_provider', 'RESTRICT');
+SELECT pg_temp.qqq_add_fk('email_webhook_inbox', 'provider_id', 'email_provider', 'RESTRICT');
+SELECT pg_temp.qqq_add_fk('email_suppression', 'source_message_id', 'email_message', 'SET NULL');
+SELECT pg_temp.qqq_add_fk('email_suppression', 'source_event_id', 'email_event', 'SET NULL');
+
+CREATE INDEX IF NOT EXISTS email_message_from_address ON email_message (from_address_id);
+CREATE INDEX IF NOT EXISTS email_message_template ON email_message (template_id);
+CREATE INDEX IF NOT EXISTS email_message_template_version ON email_message (template_version_id);
+CREATE INDEX IF NOT EXISTS email_message_brand ON email_message (brand_id);
+CREATE INDEX IF NOT EXISTS email_template_brand ON email_template (brand_id);
+CREATE INDEX IF NOT EXISTS email_template_active_version ON email_template (active_version_id);
+CREATE INDEX IF NOT EXISTS email_template_approved_version ON email_template (approved_version_id);
+CREATE INDEX IF NOT EXISTS email_suppression_source_message ON email_suppression (source_message_id);
+CREATE INDEX IF NOT EXISTS email_suppression_source_event ON email_suppression (source_event_id);
+CREATE INDEX IF NOT EXISTS email_event_provider ON email_event (provider_id);
+
+COMMIT;
@@ -239,3 +239,42 @@ CREATE INDEX email_inbox_due ON email_webhook_inbox (status, next_attempt_date);
 CREATE INDEX email_event_message ON email_event (message_id);
 CREATE INDEX email_event_type_date ON email_event (event_type, occurred_date);
 CREATE INDEX email_message_provider_id ON email_message (provider_id, provider_message_id);
+
+-- Foreign keys (named fk_<table>_<column>), added after every table exists.
+--   CASCADE   owned rows: a provider's sender addresses (the provider delete also removes
+--             them), a message's attempts and events (message retention deletes them first).
+--   RESTRICT  configuration under live data: providers with messages, attempts, events or
+--             webhook inbox rows (disable the provider instead); a brand used by a template;
+--             a template with versions (templates with versions cannot be deleted).
+--   SET NULL  optional pointers: a message's sender address, template, version and brand
+--             (the rendered body is kept); a template's active/approved version (the
+--             cycle with email_template_version.template_id; drafts can be deleted); a
+--             suppression's source message and event, which outlive message retention.
+-- Existing databases: run email-foreign-keys-upgrade.sql.
+ALTER TABLE email_provider_sender_address ADD CONSTRAINT fk_email_provider_sender_address_provider_id FOREIGN KEY (provider_id) REFERENCES email_provider (id) ON DELETE CASCADE;
+ALTER TABLE email_message ADD CONSTRAINT fk_email_message_provider_id FOREIGN KEY (provider_id) REFERENCES email_provider (id) ON DELETE RESTRICT;
+ALTER TABLE email_message ADD CONSTRAINT fk_email_message_from_address_id FOREIGN KEY (from_address_id) REFERENCES email_provider_sender_address (id) ON DELETE SET NULL;
+ALTER TABLE email_message ADD CONSTRAINT fk_email_message_template_id FOREIGN KEY (template_id) REFERENCES email_template (id) ON DELETE SET NULL;
+ALTER TABLE email_message ADD CONSTRAINT fk_email_message_template_version_id FOREIGN KEY (template_version_id) REFERENCES email_template_version (id) ON DELETE SET NULL;
+ALTER TABLE email_message ADD CONSTRAINT fk_email_message_brand_id FOREIGN KEY (brand_id) REFERENCES email_brand (id) ON DELETE SET NULL;
+ALTER TABLE email_message_attempt ADD CONSTRAINT fk_email_message_attempt_message_id FOREIGN KEY (message_id) REFERENCES email_message (id) ON DELETE CASCADE;
+ALTER TABLE email_message_attempt ADD CONSTRAINT fk_email_message_attempt_provider_id FOREIGN KEY (provider_id) REFERENCES email_provider (id) ON DELETE RESTRICT;
+ALTER TABLE email_template ADD CONSTRAINT fk_email_template_brand_id FOREIGN KEY (brand_id) REFERENCES email_brand (id) ON DELETE RESTRICT;
+ALTER TABLE email_template ADD CONSTRAINT fk_email_template_active_version_id FOREIGN KEY (active_version_id) REFERENCES email_template_version (id) ON DELETE SET NULL;
+ALTER TABLE email_template ADD CONSTRAINT fk_email_template_approved_version_id FOREIGN KEY (approved_version_id) REFERENCES email_template_version (id) ON DELETE SET NULL;
+ALTER TABLE email_template_version ADD CONSTRAINT fk_email_template_version_template_id FOREIGN KEY (template_id) REFERENCES email_template (id) ON DELETE RESTRICT;
+ALTER TABLE email_suppression ADD CONSTRAINT fk_email_suppression_source_message_id FOREIGN KEY (source_message_id) REFERENCES email_message (id) ON DELETE SET NULL;
+ALTER TABLE email_suppression ADD CONSTRAINT fk_email_suppression_source_event_id FOREIGN KEY (source_event_id) REFERENCES email_event (id) ON DELETE SET NULL;
+ALTER TABLE email_webhook_inbox ADD CONSTRAINT fk_email_webhook_inbox_provider_id FOREIGN KEY (provider_id) REFERENCES email_provider (id) ON DELETE RESTRICT;
+ALTER TABLE email_event ADD CONSTRAINT fk_email_event_message_id FOREIGN KEY (message_id) REFERENCES email_message (id) ON DELETE CASCADE;
+ALTER TABLE email_event ADD CONSTRAINT fk_email_event_provider_id FOREIGN KEY (provider_id) REFERENCES email_provider (id) ON DELETE RESTRICT;
+CREATE INDEX email_message_from_address ON email_message (from_address_id);
+CREATE INDEX email_message_template ON email_message (template_id);
+CREATE INDEX email_message_template_version ON email_message (template_version_id);
+CREATE INDEX email_message_brand ON email_message (brand_id);
+CREATE INDEX email_template_brand ON email_template (brand_id);
+CREATE INDEX email_template_active_version ON email_template (active_version_id);
+CREATE INDEX email_template_approved_version ON email_template (approved_version_id);
+CREATE INDEX email_suppression_source_message ON email_suppression (source_message_id);
+CREATE INDEX email_suppression_source_event ON email_suppression (source_event_id);
+CREATE INDEX email_event_provider ON email_event (provider_id);
@@ -134,6 +134,26 @@
         <version>${h2.version}</version>
         <scope>test</scope>
      </dependency>
+      <!-- the foreign key guardrail test: docs/ddl on a real PostgreSQL (src/test only) -->
+      <dependency>
+         <groupId>org.postgresql</groupId>
+         <artifactId>postgresql</artifactId>
+         <version>42.7.9</version>
+         <scope>test</scope>
+      </dependency>
+      <dependency>
+         <groupId>org.testcontainers</groupId>
+         <artifactId>testcontainers-postgresql</artifactId>
+         <version>2.0.3</version>
+         <scope>test</scope>
+      </dependency>
+      <!-- EmailDefaultsChangelogTest runs the shipped seed changelog through real Liquibase on H2. -->
+      <dependency>
+         <groupId>org.liquibase</groupId>
+         <artifactId>liquibase-core</artifactId>
+         <version>4.33.0</version>
+         <scope>test</scope>
+      </dependency>
      <dependency>
         <groupId>org.junit.jupiter</groupId>
         <artifactId>junit-jupiter</artifactId>
@@ -241,7 +261,7 @@
            <activeByDefault>true</activeByDefault>
         </activation>
         <properties>
-            <qqq.snapshot.version>4.1.0-RC.5</qqq.snapshot.version>
+            <qqq.snapshot.version>4.1.0-RC.7</qqq.snapshot.version>
         </properties>
         <dependencyManagement>
            <dependencies>
@@ -28,6 +28,7 @@ import java.util.Map;
 import com.kingsrook.qbits.email.app.EmailApp;
 import com.kingsrook.qbits.email.bridge.EmailOutboxMessagingProviderMetaData;
 import com.kingsrook.qbits.email.delivery.EmailAlerts;
+import com.kingsrook.qbits.email.metadata.EmailHelp;
 import com.kingsrook.qbits.email.metadata.EmailTableCustomizers;
 import com.kingsrook.qbits.email.metadata.EmailTables;
 import com.kingsrook.qbits.email.processes.EmailProcesses;
@@ -196,7 +197,9 @@ public class EmailQBitProducer implements QBitMetaDataProducer<EmailQBitConfig>
      output.add(EmailApp.app(qBitConfig, tables));
      output.add(new EmailOutboxMessagingProviderMetaData().withName(qBitConfig.getBridgeProviderName()));
      output.addSelfToInstance(qInstance);
+      EmailHelp.apply(qInstance, qBitConfig.getAppName());
      registerWebhooks(qInstance);
+      com.kingsrook.qbits.email.outbox.EmailJobEffectTarget.register(qInstance);
      return (new MetaDataProducerMultiOutput());
   }

@@ -22,6 +22,7 @@ package com.kingsrook.qbits.email.api;


 import java.util.ArrayList;
+import java.util.EnumSet;
 import java.util.List;
 import java.util.Optional;
 import java.util.Set;
@@ -283,21 +284,24 @@ public final class EmailApi
   public static EmailApiReadiness readiness(QInstance instance, EmailApiCategory category, boolean phiApproved) throws QException
   {
      List<String> reasons = new ArrayList<>();
+      Set<EmailApiReadinessCheck> failed = EnumSet.noneOf(EmailApiReadinessCheck.class);
      var config = EmailRuntime.config(instance);
      if(config == null)
      {
-         return (new EmailApiReadiness(false, List.of("Email qbit is not installed")));
+         return (new EmailApiReadiness(false, List.of("Email qbit is not installed"), Set.of(EmailApiReadinessCheck.NOT_INSTALLED)));
      }
      QRecord provider = provider(instance, null);
      if(provider == null)
      {
         reasons.add("An enabled named or default provider is required");
+         failed.add(EmailApiReadinessCheck.PROVIDER_MISSING);
      }
      else
      {
         if(!EmailTableCustomizers.validateProvider(provider, config).isEmpty())
         {
            reasons.add("The default provider configuration is invalid");
+            failed.add(EmailApiReadinessCheck.PROVIDER_INVALID);
         }
         if(phiApproved)
         {
@@ -308,6 +312,7 @@ public final class EmailApi
            catch(IllegalArgumentException e)
            {
               reasons.add("PHI email needs a PHI-enabled provider with open and click tracking disabled");
+               failed.add(EmailApiReadinessCheck.PHI_PROVIDER);
            }
         }
      }
@@ -324,22 +329,26 @@ public final class EmailApi
         catch(IllegalArgumentException e)
         {
            reasons.add("MARKETING email needs a valid publicBaseUrl");
+            failed.add(EmailApiReadinessCheck.PUBLIC_BASE_URL);
         }
         if(!hasSecret(config.getSecretResolver(), "QQQ_EMAIL_UNSUBSCRIBE_KEY"))
         {
            reasons.add("MARKETING email needs QQQ_EMAIL_UNSUBSCRIBE_KEY");
+            failed.add(EmailApiReadinessCheck.UNSUBSCRIBE_KEY);
         }
         List<QRecord> brands = read(EmailTables.BRAND, EmailStore.equal("isDefault", true).withLimit(2), Set.of("id", "postalAddress"));
         if(brands.size() != 1 || brands.getFirst().getValueString("postalAddress") == null || brands.getFirst().getValueString("postalAddress").isBlank())
         {
            reasons.add("MARKETING email needs a default brand with a postal address");
+            failed.add(EmailApiReadinessCheck.BRAND_POSTAL_ADDRESS);
         }
      }
      if(phiApproved && !hasSecret(config.getSecretResolver(), "QQQ_EMAIL_BODY_KEY"))
      {
         reasons.add("PHI email needs QQQ_EMAIL_BODY_KEY");
+         failed.add(EmailApiReadinessCheck.BODY_KEY);
      }
-      return (new EmailApiReadiness(reasons.isEmpty(), reasons));
+      return (new EmailApiReadiness(reasons.isEmpty(), reasons, failed));
   }


@@ -21,13 +21,15 @@
 package com.kingsrook.qbits.email.api;


+import java.util.EnumSet;
 import java.util.List;
+import java.util.Set;


 /*******************************************************************************
 ** Configuration preflight, not a delivery guarantee. Reasons never contain secret values.
 *******************************************************************************/
-public record EmailApiReadiness(boolean ready, List<String> reasons)
+public record EmailApiReadiness(boolean ready, List<String> reasons, Set<EmailApiReadinessCheck> failedChecks)
 {
   /*******************************************************************************
    ** Copy configuration reasons.
@@ -35,5 +37,16 @@ public record EmailApiReadiness(boolean ready, List<String> reasons)
   public EmailApiReadiness
   {
      reasons = List.copyOf(reasons);
+      failedChecks = failedChecks == null || failedChecks.isEmpty() ? Set.of() : Set.copyOf(EnumSet.copyOf(failedChecks));
+   }
+
+
+
+   /*******************************************************************************
+    ** Whether a specific prerequisite failed.
+    *******************************************************************************/
+   public boolean failed(EmailApiReadinessCheck check)
+   {
+      return (failedChecks.contains(check));
   }
 }
@@ -0,0 +1,38 @@
+/*
+ * 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.api;
+
+
+/*******************************************************************************
+ ** One readiness prerequisite, so callers can react to specific gaps without
+ ** parsing reason text.
+ *******************************************************************************/
+public enum EmailApiReadinessCheck
+{
+   NOT_INSTALLED,
+   PROVIDER_MISSING,
+   PROVIDER_INVALID,
+   PHI_PROVIDER,
+   PUBLIC_BASE_URL,
+   UNSUBSCRIBE_KEY,
+   BRAND_POSTAL_ADDRESS,
+   BODY_KEY
+}
@@ -32,6 +32,7 @@ import com.kingsrook.qbits.email.EmailRuntime;
 import com.kingsrook.qbits.email.metadata.EmailTables;
 import com.kingsrook.qbits.email.permissions.EmailPermissionChecker;
 import com.kingsrook.qbits.email.permissions.EmailPermissions;
+import com.kingsrook.qbits.email.template.EmailBuiltIns;
 import com.kingsrook.qqq.backend.core.actions.tables.CountAction;
 import com.kingsrook.qqq.backend.core.actions.tables.QueryAction;
 import com.kingsrook.qqq.backend.core.exceptions.QException;
@@ -101,7 +102,13 @@ public final class EmailHome
            provider.setValue("headroom", Math.max(0, limit - used));
         }
      }
-      return (new RawHTML(null, EmailHomeView.render(totals, hourly(), providers, admin, messages, suppressions())));
+      List<EmailSetupStatus.Item> setup = List.of();
+      if(admin)
+      {
+         EmailBuiltIns.ensure();
+         setup = EmailSetupStatus.load();
+      }
+      return (new RawHTML(null, EmailHomeView.render(totals, hourly(), providers, admin, messages, suppressions(), setup)));
   }

   /*******************************************************************************
@@ -61,8 +61,19 @@ public final class EmailHomeView
    ** One ordered home with real, viewer-scoped data supplied by EmailHome.
    *******************************************************************************/
   public static String render(List<Integer> totals, ChartData hourly, List<QRecord> providers, boolean admin, List<QRecord> messages, ChartData suppressions)
+   {
+      return (render(totals, hourly, providers, admin, messages, suppressions, List.of()));
+   }
+
+
+
+   /*******************************************************************************
+    ** The same home with the Setup card first (email admins), when there are items.
+    *******************************************************************************/
+   public static String render(List<Integer> totals, ChartData hourly, List<QRecord> providers, boolean admin, List<QRecord> messages, ChartData suppressions, List<EmailSetupStatus.Item> setup)
   {
      StringBuilder html = new StringBuilder("<div style=\"" + WRAP + "display:flex;flex-direction:column;gap:16px;color:inherit;line-height:1.4\">");
+      setup(html, setup);
      html.append("<div style=\"" + WRAP + "display:grid;grid-template-columns:repeat(auto-fit,minmax(min(100%,9rem),1fr));gap:12px\">");
      List<String> labels = List.of("Sent (24 h)", "Delivered (24 h)", "Dead letters (24 h)", "Bounced (7 d)", "Complaints (7 d)", "Suppressed addresses");
      for(int i = 0; i < labels.size(); i++)
@@ -82,6 +93,46 @@ public final class EmailHomeView



+   /*******************************************************************************
+    ** The Setup card: each part of Email, set up or not, and the next step, linked
+    ** to its table.
+    *******************************************************************************/
+   private static void setup(StringBuilder html, List<EmailSetupStatus.Item> items)
+   {
+      if(items.isEmpty())
+      {
+         return;
+      }
+      section(html, "Setup");
+      long todo = items.stream().filter(item -> item.state() == EmailSetupStatus.State.TODO).count();
+      html.append("<p style=\"margin:0 0 8px;opacity:.75\">").append(todo == 0 ? "Everything Email needs is set up." : todo + (todo == 1 ? " item needs" : " items need") + " attention.").append("</p>");
+      for(EmailSetupStatus.Item item : items)
+      {
+         String color = switch(item.state())
+         {
+            case OK -> OK;
+            case TODO -> FAIL;
+            case DEFAULT -> "#d97706";
+            case OPTIONAL -> "#808080";
+         };
+         String label = switch(item.state())
+         {
+            case OK -> "Set up";
+            case TODO -> "Needs setup";
+            case DEFAULT -> "Using defaults";
+            case OPTIONAL -> "Optional";
+         };
+         String name = item.tableName() == null ? esc(item.label()) : "<a style=\"color:inherit\" href=\"/app/" + esc(item.tableName()) + "\">" + esc(item.label()) + "</a>";
+         html.append("<div style=\"").append(ROW).append("\">")
+            .append(cell("Part", "<strong>" + name + "</strong><br><span style=\"color:" + color + "\">" + label + "</span>"))
+            .append(cell("Status", esc(item.detail())))
+            .append(cell("Next step", esc(item.nextStep() == null ? "Nothing to do" : item.nextStep()))).append("</div>");
+      }
+      html.append("</section>");
+   }
+
+
+
   /*******************************************************************************
    ** A bounded stacked bar chart, including exact values in each bar's title.
    *******************************************************************************/
@@ -0,0 +1,199 @@
+/*
+ * 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.app;
+
+
+import java.util.ArrayList;
+import java.util.List;
+import com.kingsrook.qbits.email.EmailQBitConfig;
+import com.kingsrook.qbits.email.EmailRuntime;
+import com.kingsrook.qbits.email.api.EmailApi;
+import com.kingsrook.qbits.email.api.EmailApiCategory;
+import com.kingsrook.qbits.email.api.EmailApiReadiness;
+import com.kingsrook.qbits.email.api.EmailApiReadinessCheck;
+import com.kingsrook.qbits.email.metadata.EmailTables;
+import com.kingsrook.qbits.email.support.EmailStore;
+import com.kingsrook.qbits.email.support.SystemWrites;
+import com.kingsrook.qqq.backend.core.context.QContext;
+import com.kingsrook.qqq.backend.core.exceptions.QException;
+import com.kingsrook.qqq.backend.core.model.actions.tables.query.QQueryFilter;
+import com.kingsrook.qqq.backend.core.model.data.QRecord;
+
+
+/*******************************************************************************
+ ** The Setup card on the Email home: each configuration table, whether it is
+ ** set up, and the next step. It uses the same resolution the sender uses (the
+ ** default provider, the brand lookup, EmailApi.readiness), so it never reports
+ ** a problem the sender would not have.
+ *******************************************************************************/
+public final class EmailSetupStatus
+{
+   /*******************************************************************************
+    ** OK: working. TODO: needs attention. DEFAULT: working on built-in defaults.
+    ** OPTIONAL: a feature that is off until set up.
+    *******************************************************************************/
+   public enum State
+   {
+      OK, TODO, DEFAULT, OPTIONAL
+   }
+
+
+
+   /*******************************************************************************
+    ** One line of the card.
+    *******************************************************************************/
+   public record Item(String label, String tableName, State state, String detail, String nextStep)
+   {
+   }
+
+
+
+   /*******************************************************************************
+    ** Utility class.
+    *******************************************************************************/
+   private EmailSetupStatus()
+   {
+   }
+
+
+
+   /*******************************************************************************
+    ** Build the items (as the qbit, for an email admin).
+    *******************************************************************************/
+   public static List<Item> load() throws QException
+   {
+      EmailQBitConfig config = EmailRuntime.config();
+      List<Item>      items  = new ArrayList<>();
+
+      ///////////////////////////////////////////////////////////
+      // the provider: the same check a send makes (readiness) //
+      ///////////////////////////////////////////////////////////
+      EmailApiReadiness readiness = EmailApi.readiness(QContext.getQInstance(), EmailApiCategory.TRANSACTIONAL);
+      List<QRecord>     providers = SystemWrites.query(EmailTables.PROVIDER, new QQueryFilter());
+      QRecord           active    = providers.stream().filter(p -> Boolean.TRUE.equals(p.getValueBoolean("enabled")) && Boolean.TRUE.equals(p.getValueBoolean("isDefault")))
+         .findFirst().orElse(null);
+      if(readiness.failed(EmailApiReadinessCheck.PROVIDER_MISSING))
+      {
+         items.add(new Item("Provider", EmailTables.PROVIDER, State.TODO, providers.isEmpty() ? "No provider yet, so nothing can be sent." : "No enabled default provider, so nothing can be sent.",
+            "Add one with New (kind, host or region, from address, and the NAMES of the secret variables), turn on Default, then Send Test Email."));
+      }
+      else if(readiness.failed(EmailApiReadinessCheck.PROVIDER_INVALID))
+      {
+         items.add(new Item("Provider", EmailTables.PROVIDER, State.TODO, "The default provider's configuration is invalid.", "Open it and fix the highlighted settings, then Send Test Email."));
+      }
+      else if(active != null && "UNHEALTHY".equals(active.getValueString("healthStatus")))
+      {
+         items.add(new Item("Provider", EmailTables.PROVIDER, State.TODO, "The default provider is failing: " + valueOr(active.getValueString("lastFailureMessage"), "see its last failure") + ".",
+            "Check its settings and secret variables, then Send Test Email."));
+      }
+      else
+      {
+         items.add(new Item("Provider", EmailTables.PROVIDER, State.OK, valueOr(active == null ? null : active.getValueString("label"), "The default provider") + " sends mail.", null));
+      }
+
+      ///////////////////////////////////////////////////////////////
+      // sender addresses: the provider's own from address works //
+      ///////////////////////////////////////////////////////////////
+      int senders = SystemWrites.count(EmailTables.SENDER, new QQueryFilter());
+      items.add(senders == 0
+         ? new Item("Sender addresses", EmailTables.SENDER, State.OPTIONAL, "None: the provider's own from address is used.", "Add some if one provider sends as several mailboxes.")
+         : new Item("Sender addresses", EmailTables.SENDER, State.OK, senders + (senders == 1 ? " address." : " addresses."), null));
+
+      //////////////////////////////////////////////////////////////////
+      // brand: the lookup the renderer does (app brand, then default) //
+      //////////////////////////////////////////////////////////////////
+      List<QRecord> brands = SystemWrites.query(EmailTables.BRAND, EmailStore.equal("appName", config.getAppName()));
+      if(brands.isEmpty())
+      {
+         brands = SystemWrites.query(EmailTables.BRAND, EmailStore.equal("isDefault", true));
+      }
+      if(brands.size() > 1)
+      {
+         items.add(new Item("Brands", EmailTables.BRAND, State.TODO, "More than one default brand matches, so branded sends are refused.", "Leave Default Brand on only one."));
+      }
+      else if(brands.isEmpty())
+      {
+         items.add(new Item("Brands", EmailTables.BRAND, State.DEFAULT, "No default brand: emails use the plain neutral look.", "Copy the neutral brand, set your logo, colors and footer, and tick Default Brand."));
+      }
+      else
+      {
+         items.add(new Item("Brands", EmailTables.BRAND, State.OK, valueOr(brands.getFirst().getValueString("label"), "The default brand") + " is the default.", null));
+      }
+
+      ///////////////
+      // templates //
+      ///////////////
+      int templates = SystemWrites.count(EmailTables.TEMPLATE, new QQueryFilter());
+      items.add(templates == 0
+         ? new Item("Templates", EmailTables.TEMPLATE, State.DEFAULT, "No template yet: plain messages can still be sent.", "Other qbits register theirs; add your own with New, then Activate Version.")
+         : new Item("Templates", EmailTables.TEMPLATE, State.OK, templates + (templates == 1 ? " template." : " templates."), null));
+
+      /////////////////////////////////////////////////////
+      // marketing: the same checks as a MARKETING send //
+      /////////////////////////////////////////////////////
+      EmailApiReadiness marketing = EmailApi.readiness(QContext.getQInstance(), EmailApiCategory.MARKETING);
+      List<String>      missing   = new ArrayList<>();
+      if(marketing.failed(EmailApiReadinessCheck.PUBLIC_BASE_URL))
+      {
+         missing.add("a public base URL");
+      }
+      if(marketing.failed(EmailApiReadinessCheck.UNSUBSCRIBE_KEY))
+      {
+         missing.add("the QQQ_EMAIL_UNSUBSCRIBE_KEY variable");
+      }
+      if(marketing.failed(EmailApiReadinessCheck.BRAND_POSTAL_ADDRESS))
+      {
+         missing.add("a default brand with a postal address");
+      }
+      items.add(missing.isEmpty()
+         ? new Item("Marketing email", EmailTables.BRAND, State.OK, "Ready: unsubscribe links, base URL and postal address are set.", null)
+         : new Item("Marketing email", EmailTables.BRAND, State.OPTIONAL, "Off until set up: needs " + String.join(", ", missing) + ".", "Only needed if you send marketing email."));
+
+      //////////////////////////////////////////////////////////
+      // delivery tracking: the provider's webhook secret name //
+      //////////////////////////////////////////////////////////
+      boolean webhook = active != null && valueOr(active.getValueString("webhookSecretEnvName"), "").length() > 0;
+      int     inbox   = SystemWrites.count(EmailTables.INBOX, new QQueryFilter());
+      items.add(webhook
+         ? new Item("Delivery tracking", EmailTables.INBOX, State.OK, inbox == 0 ? "A webhook secret is set; no callback has arrived yet." : inbox + " provider callbacks received.", null)
+         : new Item("Delivery tracking", EmailTables.INBOX, State.OPTIONAL, "Off: the default provider has no webhook secret variable, so bounces and complaints are not tracked.",
+            "Set the provider's webhook secret variable and point the provider at " + config.getWebhookBasePath() + "."));
+
+      int suppressed = SystemWrites.count(EmailTables.SUPPRESSION, new QQueryFilter());
+      items.add(new Item("Suppression list", EmailTables.SUPPRESSION, State.OPTIONAL, suppressed == 0 ? "No blocked addresses yet; bounces, complaints and unsubscribes add them." : suppressed + " blocked addresses.", null));
+
+      items.add(config.getSchedulerName() == null
+         ? new Item("Scheduler", null, State.TODO, "No scheduler is configured, so the outbox is only sent when someone runs Dispatch by hand.", "Set schedulerName in the Email config.")
+         : new Item("Scheduler", null, State.OK, "Dispatch, webhooks, maintenance and retention run on the " + config.getSchedulerName() + " scheduler.", null));
+      return (items);
+   }
+
+
+
+   /*******************************************************************************
+    ** The value, or the fallback when blank.
+    *******************************************************************************/
+   private static String valueOr(String value, String fallback)
+   {
+      return (value == null || value.isBlank() ? fallback : value);
+   }
+
+}
@@ -0,0 +1,130 @@
+/*
+ * 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.metadata;
+
+
+import java.io.IOException;
+import java.io.InputStream;
+import java.util.LinkedHashMap;
+import java.util.Map;
+import com.fasterxml.jackson.databind.JsonNode;
+import com.fasterxml.jackson.databind.ObjectMapper;
+import com.fasterxml.jackson.dataformat.yaml.YAMLFactory;
+import com.kingsrook.qqq.backend.core.exceptions.QException;
+import com.kingsrook.qqq.backend.core.model.metadata.QInstance;
+import com.kingsrook.qqq.backend.core.model.metadata.help.QHelpContent;
+import com.kingsrook.qqq.backend.core.model.metadata.layout.QAppMetaData;
+import com.kingsrook.qqq.backend.core.model.metadata.tables.QTableMetaData;
+
+
+/*******************************************************************************
+ ** Applies help/qbit-email.yaml (the QQQ help-bundle format) to the
+ ** Email app and its email_* tables: what each table is for and how
+ ** its rows get created, shown on the table screen and when it is empty.
+ ** STAND-IN(core=QRun-IO/qqq#1198, removeBy=4.1.0-RC.8): replace with HelpBundles.register once the pinned
+ ** QQQ release includes it; the bundle file stays the same.
+ *******************************************************************************/
+public final class EmailHelp
+{
+   public static final String RESOURCE = "/help/qbit-email.yaml";
+   public static final String SLOT     = "help";
+
+
+
+   /*******************************************************************************
+    ** Utility class.
+    *******************************************************************************/
+   private EmailHelp()
+   {
+   }
+
+
+
+   /*******************************************************************************
+    ** The bundle's entries by key, validated (fails startup if malformed).
+    *******************************************************************************/
+   public static Map<String, JsonNode> entries() throws QException
+   {
+      try(InputStream stream = EmailHelp.class.getResourceAsStream(RESOURCE))
+      {
+         if(stream == null)
+         {
+            throw (new IOException("missing resource"));
+         }
+         JsonNode bundle = new ObjectMapper(new YAMLFactory()).readTree(stream);
+         if(!"qbit-email".equals(bundle.path("bundle").asText()) || !"QBIT".equals(bundle.path("layer").asText()))
+         {
+            throw (new IOException("invalid bundle header"));
+         }
+         Map<String, JsonNode> entries = new LinkedHashMap<>();
+         for(JsonNode entry : bundle.path("entries"))
+         {
+            String key     = entry.path("key").asText();
+            String summary = entry.path("summary").asText();
+            if(key.isBlank() || summary.isBlank() || summary.length() > 120 || entry.path("content").asText().isBlank() || entries.put(key, entry) != null)
+            {
+               throw (new IOException("invalid or duplicate entry: " + key));
+            }
+         }
+         return (entries);
+      }
+      catch(IOException | RuntimeException e)
+      {
+         throw (new QException("Could not load " + RESOURCE, e));
+      }
+   }
+
+
+
+   /*******************************************************************************
+    ** Apply the help to the app and every email_* table in the instance.
+    ** A host's own help on a table is kept (the bundle only fills the slot).
+    *******************************************************************************/
+   public static void apply(QInstance qInstance, String appName) throws QException
+   {
+      Map<String, JsonNode> entries = entries();
+      for(QTableMetaData table : qInstance.getTables().values())
+      {
+         JsonNode entry = entries.get("table:" + table.getName() + ";slot:" + SLOT);
+         if(entry != null && (table.getHelpContent() == null || !table.getHelpContent().containsKey(SLOT)))
+         {
+            table.withHelpContent(SLOT, content(entry));
+         }
+      }
+      QAppMetaData app = qInstance.getApp(appName);
+      JsonNode     appEntry = entries.get("app:email;slot:" + SLOT);
+      if(app != null && appEntry != null && (app.getHelpContent() == null || !app.getHelpContent().containsKey(SLOT)))
+      {
+         app.withHelpContent(SLOT, content(appEntry));
+      }
+   }
+
+
+
+   /*******************************************************************************
+    ** Summary as the first paragraph, then the content, as markdown.
+    *******************************************************************************/
+   static QHelpContent content(JsonNode entry)
+   {
+      return (new QHelpContent().withContentAsMarkdown(entry.path("summary").asText().strip() + "\n\n" + entry.path("content").asText().strip()));
+   }
+
+}
@@ -234,9 +234,16 @@ public final class EmailTableCustomizers
      {
         for(QRecord row : records)
         {
-            if(!SystemWrites.query(EmailTables.MESSAGE, com.kingsrook.qbits.email.support.EmailStore.equal("providerId", row.getValueInteger("id")).withLimit(1), input.getTransaction()).isEmpty())
+            //////////////////////////////////////////////////////////////////
+            // the same history the RESTRICT foreign keys protect (docs/ddl) //
+            //////////////////////////////////////////////////////////////////
+            for(String history : List.of(EmailTables.MESSAGE, EmailTables.ATTEMPT, EmailTables.EVENT, EmailTables.INBOX))
            {
-               row.addError(new BadInputStatusMessage("Providers with message history cannot be deleted; disable the provider instead"));
+               if(!SystemWrites.query(history, EmailStore.equal("providerId", row.getValueInteger("id")).withLimit(1), input.getTransaction()).isEmpty())
+               {
+                  row.addError(new BadInputStatusMessage("Providers with message history cannot be deleted; disable the provider instead"));
+                  break;
+               }
            }
         }
         List<QRecord> remaining = new ArrayList<>(SystemWrites.query(EmailTables.PROVIDER, new QQueryFilter(), input.getTransaction()));
@@ -344,7 +344,8 @@ public final class EmailTables
         .withField(field("phiApprovedBy", QFieldType.STRING).withMaxLength(500).withIsEditable(false))
         .withField(field("phiApprovedDate", QFieldType.DATE_TIME).withIsEditable(false))
         .withField(field("editPermissionName", QFieldType.STRING).withMaxLength(500))
-         .withField(field("ownerQbit", QFieldType.STRING).withMaxLength(500))
+         .withField(field("ownerQbit", QFieldType.STRING).withMaxLength(500).withIsEditable(false)
+            .withHelpContent(new com.kingsrook.qqq.backend.core.model.metadata.help.QHelpContent("Set by the qbit that registered the template. Messages sent from it name that qbit as their sender unless the sender says otherwise.")))
         .withField(field("description", QFieldType.TEXT));
      table.withRecordLabelFormatAndFields("%s", "name");
      table.withUniqueKey(new UniqueKey("name"));
@@ -42,8 +42,15 @@ public final class EmailEffects
   /*******************************************************************************
    ** A refusal can have a reference when suppression retained an audit row.
    *******************************************************************************/
-   public record Result(EmailEffectOutcome outcome, EmailMessageRef message)
+   public record Result(EmailEffectOutcome outcome, EmailMessageRef message, List<String> reasons)
   {
+      /*******************************************************************************
+       ** Preserve the original constructor for callers that only need the reference.
+       *******************************************************************************/
+      public Result(EmailEffectOutcome outcome, EmailMessageRef message)
+      {
+         this(outcome, message, List.of());
+      }
   }


@@ -68,14 +75,15 @@ public final class EmailEffects
         || request.getTransaction() != null
         || request.getIdempotencyKey() != null && !request.getIdempotencyKey().equals(effectIdempotencyKey))
      {
-         return (new Result(EmailEffectOutcome.REFUSED, null));
+         return (new Result(EmailEffectOutcome.REFUSED, null, List.of("An email request without a caller transaction and a valid, matching effect key are required")));
      }
      request.withIdempotencyKey(effectIdempotencyKey);
      EmailSendResult result = EmailService.send(request);
      EmailMessageRef message = result.messageId() == null ? null : find("id", result.messageId()).orElseThrow(() -> new QException("Queued email reference is unavailable"));
      boolean refused = result.status() == EmailSendResult.Status.REFUSED || result.status() == EmailSendResult.Status.SUPPRESSED
         || message != null && "SUPPRESSED".equals(message.status());
-      return (new Result(refused ? EmailEffectOutcome.REFUSED : EmailEffectOutcome.QUEUED_OR_DUPLICATE, message));
+      List<String> reasons = message != null && "SUPPRESSED".equals(message.status()) ? List.of("All recipients are suppressed") : result.reasons();
+      return (new Result(refused ? EmailEffectOutcome.REFUSED : EmailEffectOutcome.QUEUED_OR_DUPLICATE, message, reasons));
   }


@@ -0,0 +1,200 @@
+/*
+ * 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.outbox;
+
+
+import java.io.Serializable;
+import java.time.Instant;
+import java.util.List;
+import java.util.Map;
+import java.util.concurrent.atomic.AtomicReference;
+import com.kingsrook.qqq.backend.core.context.CapturedContext;
+import com.kingsrook.qqq.backend.core.context.QContext;
+import com.kingsrook.qqq.backend.core.exceptions.QAuthenticationException;
+import com.kingsrook.qqq.backend.core.exceptions.QException;
+import com.kingsrook.qqq.backend.core.exceptions.QPermissionDeniedException;
+import com.kingsrook.qqq.backend.core.jobs.DurableJobsMetaData;
+import com.kingsrook.qqq.backend.core.jobs.JobSessions;
+import com.kingsrook.qqq.backend.core.jobs.effects.EffectRequest;
+import com.kingsrook.qqq.backend.core.jobs.effects.EffectResult;
+import com.kingsrook.qqq.backend.core.jobs.effects.EffectTarget;
+import com.kingsrook.qqq.backend.core.jobs.effects.ReconcileResult;
+import com.kingsrook.qqq.backend.core.jobs.store.DurableJobStore;
+import com.kingsrook.qqq.backend.core.model.data.QRecord;
+import com.kingsrook.qqq.backend.core.model.metadata.QInstance;
+import com.kingsrook.qqq.backend.core.model.metadata.code.QCodeReference;
+import com.kingsrook.qqq.backend.core.model.session.QSession;
+
+
+/*******************************************************************************
+ ** Durable jobs target for IDEMPOTENT_EXTERNAL email effects. SENT acknowledges
+ ** the committed email outbox, whose key deduplication makes retries safe. The
+ ** email dispatcher owns provider delivery and preserves the stored Message-ID.
+ ** Payload keys and types are documented in docs/DURABLE-JOBS.md.
+ *******************************************************************************/
+public class EmailJobEffectTarget implements EffectTarget
+{
+   public static final String TARGET_NAME = "email";
+
+
+
+   /*******************************************************************************
+    ** Register after enabling durable jobs; leave instances without jobs alone.
+    *******************************************************************************/
+   public static void register(QInstance instance)
+   {
+      DurableJobsMetaData jobs = DurableJobsMetaData.of(instance);
+      if(jobs != null)
+      {
+         jobs.withEffectTarget(TARGET_NAME, new QCodeReference(EmailJobEffectTarget.class));
+      }
+   }
+
+
+
+   /*******************************************************************************
+    ** Enqueue under the job owner's current permissions and captured scope.
+    ** Other exceptions are uncertain; core may safely resend using the same key.
+    *******************************************************************************/
+   @Override
+   public EffectResult deliver(EffectRequest r)
+   {
+      try
+      {
+         EmailRequest request = decode(r.payload()).withSender("jobs", r.effectName());
+         EmailEffects.Result result;
+         if(r.jobId() == null)
+         {
+            result = EmailEffects.send(request, r.idempotencyKey());
+         }
+         else
+         {
+            QInstance instance = QContext.getQInstance();
+            QSession owner;
+            try
+            {
+               QRecord job = new DurableJobStore(DurableJobsMetaData.of(instance)).getJob(r.jobId());
+               if(job == null)
+               {
+                  return (EffectResult.failed("The job's owner can no longer send it: job not found"));
+               }
+               owner = JobSessions.forJob(instance, job);
+            }
+            catch(QPermissionDeniedException | QAuthenticationException e)
+            {
+               return (EffectResult.failed("The job's owner can no longer send it: owner session unavailable"));
+            }
+            AtomicReference<EmailEffects.Result> handoff = new AtomicReference<>();
+            QContext.withTemporaryContext(new CapturedContext(instance, owner),
+               () -> handoff.set(EmailEffects.send(request, r.idempotencyKey())));
+            result = handoff.get();
+         }
+         if(result.outcome() == EmailEffectOutcome.REFUSED)
+         {
+            return (EffectResult.failed(String.join("; ", result.reasons())));
+         }
+         return (EffectResult.sent(String.valueOf(result.message().id())));
+      }
+      catch(Exception e)
+      {
+         // Exception details can contain payloads or database values.
+         return (EffectResult.unknown("Email handoff could not be confirmed (" + e.getClass().getSimpleName() + ")"));
+      }
+   }
+
+
+
+   /*******************************************************************************
+    ** Reconcile handoff by existence, including suppressed and terminal audit rows.
+    *******************************************************************************/
+   @Override
+   public ReconcileResult reconcile(EffectRequest r) throws QException
+   {
+      return (EmailEffects.findByIdempotencyKey(r.idempotencyKey())
+         .map(message -> ReconcileResult.found(String.valueOf(message.id())))
+         .orElseGet(ReconcileResult::notFound));
+   }
+
+
+
+   /*******************************************************************************
+    ** Decode only supported values; identity, sender and transaction cannot be
+    ** overridden by payload keys. Variables retain normal template/PHI validation.
+    *******************************************************************************/
+   static EmailRequest decode(Map<String, Serializable> payload)
+   {
+      Map<String, Serializable> values = payload == null ? Map.of() : payload;
+      EmailRequest request = new EmailRequest()
+         .withTo(addresses(values.get("to")))
+         .withCc(addresses(values.get("cc")))
+         .withBcc(addresses(values.get("bcc")))
+         .withFrom((String) values.get("from"))
+         .withFromAddressId((Integer) values.get("fromAddressId"))
+         .withReplyTo((String) values.get("replyTo"))
+         .withSubject((String) values.get("subject"))
+         .withTemplateName((String) values.get("templateName"))
+         .withMarkdownBody((String) values.get("markdownBody"))
+         .withHtmlBody((String) values.get("htmlBody"))
+         .withTextBody((String) values.get("textBody"))
+         .withProviderName((String) values.get("providerName"))
+         .withBrandName((String) values.get("brandName"))
+         .withLinkedRecord((String) values.get("linkedTableName"), values.get("linkedRecordId"));
+      if(values.get("locale") != null)
+      {
+         request.withLocale((String) values.get("locale"));
+      }
+      if(values.get("category") != null)
+      {
+         request.withCategory(EmailCategory.valueOf((String) values.get("category")));
+      }
+      if(values.get("sendAfter") != null)
+      {
+         request.withSendAfter(Instant.parse((String) values.get("sendAfter")));
+      }
+      if(values.get("variables") instanceof Map<?, ?> variables)
+      {
+         variables.forEach((key, value) -> request.withVariable((String) key, (Serializable) value));
+      }
+      else if(values.get("variables") != null)
+      {
+         throw (new IllegalArgumentException("variables must be a map"));
+      }
+      return (request);
+   }
+
+
+
+   /*******************************************************************************
+    ** Accept one mailbox or a list of mailboxes, without comma splitting.
+    *******************************************************************************/
+   private static List<String> addresses(Object value)
+   {
+      if(value == null)
+      {
+         return (List.of());
+      }
+      if(value instanceof String address)
+      {
+         return (List.of(address));
+      }
+      return (((List<?>) value).stream().map(String.class::cast).toList());
+   }
+}
@@ -270,6 +270,14 @@ public final class EmailService
      if(templateContent != null)
      {
         row.getValues().putAll(templateContent.getValues());
+         if(row.getValue("senderQbit") == null && row.getValue("templateId") != null)
+         {
+            ///////////////////////////////////////////////////////////////////////////////
+            // the qbit that registered the template is the sender when none was named //
+            ///////////////////////////////////////////////////////////////////////////////
+            List<QRecord> owner = SystemWrites.query(EmailTables.TEMPLATE, EmailStore.equal("id", row.getValue("templateId")), queryTransaction);
+            row.setValue("senderQbit", owner.isEmpty() ? null : owner.getFirst().getValueString("ownerQbit"));
+         }
      }
      for(var field : config.getSecurityFields())
      {
@@ -36,6 +36,8 @@ import com.kingsrook.qqq.backend.core.model.data.QRecord;
 *******************************************************************************/
 public final class EmailBuiltIns
 {
+   public static final String OWNER = "qbit-email";
+
   /*******************************************************************************
    ** Utility class.
    *******************************************************************************/
@@ -55,7 +57,7 @@ public final class EmailBuiltIns
      try(QBackendTransaction tx = QBackendTransaction.openFor(new InsertInput(EmailTables.TEMPLATE)))
      {
         QRecord template = SystemWrites.insert(EmailTables.TEMPLATE, List.of(new QRecord().withValue("name", "email_secure_message")
-            .withValue("label", "Secure message").withValue("kind", "MARKDOWN").withValue("category", "SECURITY").withValue("phiApproved", false)), tx).getRecords().getFirst();
+            .withValue("label", "Secure message").withValue("kind", "MARKDOWN").withValue("category", "SECURITY").withValue("phiApproved", false).withValue("ownerQbit", OWNER)), tx).getRecords().getFirst();
         if(template.getValue("id") == null || template.getErrors() != null && !template.getErrors().isEmpty())
         {
            tx.rollback();
@@ -0,0 +1,32 @@
+-- liquibase formatted sql
+
+-- qbit-email starter defaults: one neutral, high-contrast brand to copy or select,
+-- so the Brands table is not empty and its colors are a safe starting point.
+-- Include it from the host's master changelog AFTER the changesets that create the
+-- email_* tables (docs/ddl/email-postgres.sql):
+--
+--   - include: { file: db/liquibase/qbit-email/email-defaults.changelog.sql }
+--
+-- Rollback is empty on purpose: a name-based delete would also remove rows the host already had.
+-- Rules for this file:
+--   * insert-only, and only when the host has no brand at all, so a host that already
+--     configured its branding is never touched;
+--   * it never changes how mail looks on deploy: the brand is NOT the default brand
+--     (is_default = FALSE), so it applies only when someone selects it (a template's brand,
+--     a request's brand name) or ticks Default Brand on it;
+--   * no secrets, and portable SQL (PostgreSQL and H2);
+--   * every changeset has a precondition (sql-check only; table-exists is not portable across
+--     Liquibase versions), so a host without the table skips it (MARK_RAN).
+-- Providers, sender addresses, suppression and every runtime table are NOT seeded: a provider
+-- is the host's own connection (a server and the NAMES of its secret variables). The built-in
+-- secure-message template is created by the qbit itself on first use and when the Email home opens.
+-- EmailDefaultsChangelogTest runs this file through Liquibase on a fresh schema.
+
+-- changeset qbit-email:defaults-1
+-- preconditions onFail:MARK_RAN onError:MARK_RAN
+-- precondition-sql-check expectedResult:0 SELECT COUNT(*) FROM email_brand
+-- comment: A neutral brand (white background, near-black text, blue buttons; light and dark schemes, every pair at least 4.5:1 contrast). Not the default brand, so nothing changes until it is selected.
+INSERT INTO email_brand (name, label, primary_color, text_color, background_color, button_text_color, dark_primary_color, dark_text_color, dark_background_color, dark_button_text_color, is_default, create_date, modify_date)
+SELECT 'neutral', 'Neutral', '#2459a6', '#171717', '#ffffff', '#ffffff', '#8ab4f8', '#f5f5f5', '#121212', '#000000', FALSE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
+WHERE NOT EXISTS (SELECT 1 FROM email_brand);
+-- rollback empty
@@ -0,0 +1,63 @@
+bundle: qbit-email
+version: 1
+layer: QBIT
+# Help for the Email app and every email_* table, in the QQQ help-bundle format
+# (qqq#1198). Each table entry says what the table is for and how its rows get
+# created, so an empty table explains itself. EmailHelp applies it until the core
+# HelpBundles registry ships in a QQQ release (a stand-in until core #1198 ships).
+# Summaries stay under 120 characters (EmailTablesGuardrailTest).
+entries:
+  - key: app:email;slot:help
+    summary: "One email service: a durable outbox, providers, templates with branding, delivery tracking and suppression."
+    content: |
+      The Setup card on the home (email admins) lists every part of Email with its status and the next step. Nothing sends until an enabled default provider exists: that is your mail server or service, so the qbit cannot invent it. A new installation starts with a neutral brand to copy, and the secure-message template appears the first time the home opens.
+
+  - key: table:email_provider;slot:help
+    summary: "A way to send mail: SMTP, SES, SendGrid, Mailgun or Postmark, with limits and the names of its secret variables."
+    content: |
+      **How rows get created:** an administrator adds one with New: pick the kind, the host or region, a from address, and the NAMES of the environment variables that hold the credentials (the secrets themselves are never stored). Turn on Default so sends use it. Run Send Test Email on it to check the connection, then watch its health. This is your own connection, so it starts empty. Empty means nothing can be sent yet.
+
+  - key: table:email_provider_sender_address;slot:help
+    summary: "An address a provider may send from, with a display name. One can be the default for that provider."
+    content: |
+      **How rows get created:** add one from a provider's Sender Addresses list when the provider needs more than its single from address (several mailboxes or brands). A request may name one; otherwise the default is used. Empty is normal: the provider's own from address is used.
+
+  - key: table:email_message;slot:help
+    summary: "The outbox: every email the system queued, with its status, attempts, bounce and complaint dates."
+    content: |
+      **How rows get created:** never by hand. Every send, from any qbit or process, is queued here first (EmailApi.send, the Send Email process, or a messaging provider), then the dispatcher sends it, retries failures and records the result. Actions: Send Now, Resend and Cancel. Empty means nothing has been sent from this system yet.
+
+  - key: table:email_message_attempt;slot:help
+    summary: "One try to hand a message to a provider: outcome, timing and error. Read-only."
+    content: |
+      **How rows get created:** written by the dispatcher each time it tries to send a message. Used for provider rate limits and the hourly chart on the home. Empty means no send has been attempted.
+
+  - key: table:email_template;slot:help
+    summary: "A reusable email: a name, category, brand and the versions that hold its subject and body."
+    content: |
+      **How rows get created:** the qbit creates the secure-message template for you; other qbits register theirs (EmailApi.ensureTemplate) and are named in Owner Qbit; or an administrator adds one with New, then writes its first version and runs Activate Version. Only one version is active at a time. Approve For PHI is a separate, explicit step per version.
+
+  - key: table:email_template_version;slot:help
+    summary: "An immutable version of a template: subject, body, variables and locale. Activate one to use it."
+    content: |
+      **How rows get created:** use New Version on a template, edit the draft (Markdown or MJML body, the variables it accepts, sample values), then Activate Version, which checks the template renders. Activated versions never change. Empty means no template has a version yet.
+
+  - key: table:email_brand;slot:help
+    summary: "Look and footer of branded emails: logo, colors for light and dark, footer text and postal address."
+    content: |
+      **How rows get created:** the qbit seeds a neutral, high-contrast brand named neutral (not the default, so it changes nothing until you choose it). Copy it for your own: set the logo (https), colors (contrast is checked), footer and postal address, then tick Default Brand to use it for everything that names no brand. Marketing email needs a default brand with a postal address. Without any default the plain neutral look is used.
+
+  - key: table:email_suppression;slot:help
+    summary: "Addresses that must not receive mail: hard bounces, complaints, unsubscribes and manual blocks."
+    content: |
+      **How rows get created:** automatically when a provider reports a hard bounce or complaint through its webhook, when someone unsubscribes, or by an administrator with New (a manual block, for one category or all). Sends to these addresses are skipped and noted on the message. Empty means nobody has bounced, complained or unsubscribed yet.
+
+  - key: table:email_webhook_inbox;slot:help
+    summary: "Provider webhook calls waiting to be processed, kept so none is lost: status, attempts and errors."
+    content: |
+      **How rows get created:** written when a verified provider webhook (delivery, bounce, complaint) arrives at the qbit's webhook path (/qqq/hooks/email by default), then processed in the background. Reprocess retries a failed one. Empty means no provider has called back; set each provider's webhook secret variable and point the provider at the webhook URL.
+
+  - key: table:email_event;slot:help
+    summary: "Delivery tracking events per message: delivered, bounced, complained, opened, clicked."
+    content: |
+      **How rows get created:** written by the webhook processor from provider callbacks. Read-only; each message's timeline is built from these. Empty means no provider has reported anything yet.
@@ -0,0 +1,309 @@
+/*
+ * 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.InputStream;
+import java.nio.charset.StandardCharsets;
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.ResultSet;
+import java.sql.Statement;
+import java.util.List;
+import com.kingsrook.qbits.email.metadata.EmailTables;
+import com.kingsrook.qbits.email.render.ContrastCheck;
+import com.kingsrook.qbits.email.render.EmailBrands;
+import com.kingsrook.qqq.backend.core.context.QContext;
+import com.kingsrook.qqq.backend.core.model.data.QRecord;
+import liquibase.Liquibase;
+import liquibase.database.Database;
+import liquibase.database.DatabaseFactory;
+import liquibase.database.jvm.JdbcConnection;
+import liquibase.resource.ClassLoaderResourceAccessor;
+import org.junit.jupiter.api.AfterEach;
+import org.junit.jupiter.api.Test;
+import static org.assertj.core.api.Assertions.assertThat;
+
+
+/*******************************************************************************
+ ** The shipped starter defaults (db/liquibase/qbit-email/email-defaults.
+ ** changelog.sql), run through real Liquibase on H2 (PostgreSQL mode) against
+ ** the qbit's test schema: the brand appears once, a second update runs nothing,
+ ** an edit survives, a host with its own brand or without the table is left
+ ** alone, and the seed never changes how mail is rendered.
+ *******************************************************************************/
+class EmailDefaultsChangelogTest
+{
+   static final String CHANGELOG = "db/liquibase/qbit-email/email-defaults.changelog.sql";
+
+   /** the tables the changelog seeds. */
+   static final List<String> SEEDED_TABLES = List.of("email_brand");
+
+
+
+   /*******************************************************************************
+    **
+    *******************************************************************************/
+   @AfterEach
+   void cleanup()
+   {
+      QContext.clear();
+   }
+
+
+
+   /*******************************************************************************
+    ** Fresh schema, update twice: one brand; an edit survives.
+    *******************************************************************************/
+   @Test
+   void seedsAFreshSchemaInsertOnly() throws Exception
+   {
+      try(Connection connection = freshDatabase(true))
+      {
+         update(connection);
+         assertThat(scalar(connection, "SELECT COUNT(*) FROM email_brand")).isEqualTo("1");
+         assertThat(scalar(connection, "SELECT name FROM email_brand")).isEqualTo("neutral");
+         assertThat(scalar(connection, "SELECT is_default FROM email_brand")).isEqualTo("FALSE");
+
+         try(Statement statement = connection.createStatement())
+         {
+            statement.execute("UPDATE email_brand SET label = 'Ours', is_default = TRUE");
+         }
+         connection.commit();
+         update(connection);
+         assertThat(scalar(connection, "SELECT COUNT(*) FROM email_brand")).isEqualTo("1");
+         assertThat(scalar(connection, "SELECT label FROM email_brand")).isEqualTo("Ours");
+         assertThat(scalar(connection, "SELECT COUNT(*) FROM databasechangelog WHERE exectype = 'EXECUTED'")).isEqualTo("1");
+      }
+   }
+
+
+
+   /*******************************************************************************
+    ** A host that already configured a brand (of any name) gets nothing added,
+    ** so it can never end up with two default brands.
+    *******************************************************************************/
+   @Test
+   void leavesAHostWithItsOwnBrandAlone() throws Exception
+   {
+      try(Connection connection = freshDatabase(true))
+      {
+         try(Statement statement = connection.createStatement())
+         {
+            statement.execute("INSERT INTO email_brand (name, label, is_default) VALUES ('acme', 'Acme', TRUE)");
+         }
+         connection.commit();
+         update(connection);
+         assertThat(scalar(connection, "SELECT COUNT(*) FROM email_brand")).isEqualTo("1");
+         assertThat(scalar(connection, "SELECT name FROM email_brand")).isEqualTo("acme");
+         assertThat(scalar(connection, "SELECT COUNT(*) FROM databasechangelog WHERE exectype = 'MARK_RAN'")).isEqualTo("1");
+      }
+   }
+
+
+
+   /*******************************************************************************
+    ** Rolling the seed back (Liquibase also rolls back changesets it only marked
+    ** as ran) never deletes a brand the host already had.
+    *******************************************************************************/
+   @Test
+   void rollbackKeepsHostOwnedRows() throws Exception
+   {
+      try(Connection connection = freshDatabase(true))
+      {
+         try(Statement statement = connection.createStatement())
+         {
+            statement.execute("INSERT INTO email_brand (name, label, is_default) VALUES ('neutral', 'Mine', TRUE)");
+         }
+         connection.commit();
+         update(connection);
+         Database database = DatabaseFactory.getInstance().findCorrectDatabaseImplementation(new JdbcConnection(connection));
+         new Liquibase(CHANGELOG, new ClassLoaderResourceAccessor(), database).rollback(1, "");
+         connection.commit();
+         assertThat(scalar(connection, "SELECT label FROM email_brand WHERE name = 'neutral'")).isEqualTo("Mine");
+      }
+   }
+
+
+
+   /*******************************************************************************
+    ** A host that has not created the table yet skips the changeset (marked
+    ** ran), rather than failing its deploy.
+    *******************************************************************************/
+   @Test
+   void aMissingTableMarksTheChangesetRan() throws Exception
+   {
+      try(Connection connection = freshDatabase(false))
+      {
+         update(connection);
+         assertThat(scalar(connection, "SELECT COUNT(*) FROM databasechangelog WHERE exectype = 'MARK_RAN'")).isEqualTo("1");
+      }
+   }
+
+
+
+   /*******************************************************************************
+    ** Guarded, and only precondition forms that parse in Liquibase 4 and 5.
+    *******************************************************************************/
+   @Test
+   void everyChangesetIsGuarded() throws Exception
+   {
+      String text = changelogText();
+      assertThat(text).doesNotContain("precondition-table-exists");
+      assertThat(text.split("(?m)^-- changeset ")).hasSize(2);
+      assertThat(text).contains("-- preconditions onFail:MARK_RAN onError:MARK_RAN").contains("precondition-sql-check expectedResult:0 SELECT COUNT(*) FROM email_brand")
+         .contains("-- rollback ").contains("WHERE NOT EXISTS (SELECT 1 FROM email_brand)");
+      for(String table : SEEDED_TABLES)
+      {
+         assertThat(text).contains("INSERT INTO " + table + " ");
+      }
+   }
+
+
+
+   /*******************************************************************************
+    ** The seeded brand passes the qbit's own contrast checks, and being a
+    ** non-default brand it changes nothing about how mail renders until chosen.
+    *******************************************************************************/
+   @Test
+   void theSeededBrandIsSafeAndChangesNothing() throws Exception
+   {
+      try(Connection connection = freshDatabase(true))
+      {
+         update(connection);
+         QRecord brand = new QRecord();
+         try(Statement statement = connection.createStatement(); ResultSet rs = statement.executeQuery("SELECT * FROM email_brand"))
+         {
+            rs.next();
+            for(String column : List.of("primary_color", "text_color", "background_color", "button_text_color", "dark_primary_color", "dark_text_color", "dark_background_color", "dark_button_text_color"))
+            {
+               brand.setValue(camel(column), rs.getString(column));
+            }
+         }
+         ContrastCheck.validate(brand);
+         EmailBrands.validate(brand);
+      }
+
+      H2Support.setup(config -> config.withSecretResolver(name -> null));
+      H2Support.insert(EmailTables.BRAND, new QRecord().withValue("name", "neutral").withValue("label", "Neutral").withValue("primaryColor", "#2459a6")
+         .withValue("isDefault", false));
+      QRecord resolved = EmailBrands.resolve(null, null);
+      assertThat(resolved.getValue("id")).as("the seeded brand is not picked up unless selected").isNull();
+      assertThat(resolved.getValueString("label")).isEqualTo("Email");
+   }
+
+
+
+   /*******************************************************************************
+    ** snake_case to camelCase.
+    *******************************************************************************/
+   private static String camel(String column)
+   {
+      StringBuilder camel = new StringBuilder();
+      boolean       up    = false;
+      for(char c : column.toCharArray())
+      {
+         if(c == '_')
+         {
+            up = true;
+         }
+         else
+         {
+            camel.append(up ? Character.toUpperCase(c) : c);
+            up = false;
+         }
+      }
+      return (camel.toString());
+   }
+
+
+
+   /*******************************************************************************
+    ** A fresh in-memory H2 (PostgreSQL mode), with or without the qbit's tables.
+    *******************************************************************************/
+   static Connection freshDatabase(boolean withTables) throws Exception
+   {
+      Connection connection = DriverManager.getConnection("jdbc:h2:mem:defaults" + System.nanoTime() + ";MODE=PostgreSQL;DEFAULT_NULL_ORDERING=HIGH");
+      if(withTables)
+      {
+         String script;
+         try(InputStream in = EmailDefaultsChangelogTest.class.getResourceAsStream("/email-h2.sql"))
+         {
+            script = new String(in.readAllBytes(), StandardCharsets.UTF_8);
+         }
+         try(Statement statement = connection.createStatement())
+         {
+            for(String sql : script.split(";"))
+            {
+               if(!sql.isBlank())
+               {
+                  statement.execute(sql);
+               }
+            }
+         }
+      }
+      return (connection);
+   }
+
+
+
+   /*******************************************************************************
+    ** Run the changelog with real Liquibase.
+    *******************************************************************************/
+   static void update(Connection connection) throws Exception
+   {
+      Database database = DatabaseFactory.getInstance().findCorrectDatabaseImplementation(new JdbcConnection(connection));
+      //////////////////////////////////////////////////////////////////
+      // not closed: closing Liquibase would close the test connection //
+      //////////////////////////////////////////////////////////////////
+      new Liquibase(CHANGELOG, new ClassLoaderResourceAccessor(), database).update("");
+      connection.commit();
+   }
+
+
+
+   /*******************************************************************************
+    **
+    *******************************************************************************/
+   static String changelogText() throws Exception
+   {
+      try(InputStream stream = EmailDefaultsChangelogTest.class.getClassLoader().getResourceAsStream(CHANGELOG))
+      {
+         assertThat(stream).as(CHANGELOG + " is on the classpath").isNotNull();
+         return (new String(stream.readAllBytes(), StandardCharsets.UTF_8));
+      }
+   }
+
+
+
+   /*******************************************************************************
+    **
+    *******************************************************************************/
+   private static String scalar(Connection connection, String sql) throws Exception
+   {
+      try(Statement statement = connection.createStatement(); ResultSet rs = statement.executeQuery(sql))
+      {
+         rs.next();
+         return (rs.getString(1));
+      }
+   }
+
+}
@@ -108,7 +108,7 @@ class EmailHomePolishTest
         assertThat(tiles.get(i).child(0).attr("style")).contains("font-size:1.6rem");
         assertThat(tiles.get(i).child(0).attr("style").contains("#d64545")).isEqualTo(i == 2 || i == 3);
      }
-      assertThat(document.select("h3")).extracting(Element::text).containsExactly("Sends per hour, last 24 hours", "Provider health", "Recent messages", "Suppressions added, last 30 days");
+      assertThat(document.select("h3")).extracting(Element::text).containsExactly("Setup", "Sends per hour, last 24 hours", "Provider health", "Recent messages", "Suppressions added, last 30 days");
      assertThat(QContext.getQInstance().getApp("email").getWidgets()).containsExactly("emailHome");
   }

@@ -119,9 +119,10 @@ class EmailHomePolishTest
   void stackedBarsAndCompactAxes() throws Exception
   {
      int number = 0;
+      Integer messageId = SystemWrites.insertOne(EmailTables.MESSAGE, new QRecord().withValue("status", "QUEUED")).getValueInteger("id");
      for(String outcome : List.of("SUCCEEDED", "TRANSIENT_FAILURE", "PROVIDER_FAILURE", "PERMANENT_FAILURE", "LEASE_EXPIRED", "IN_FLIGHT"))
      {
-         SystemWrites.insertOne(EmailTables.ATTEMPT, new QRecord().withValue("messageId", 1).withValue("attemptNumber", ++number)
+         SystemWrites.insertOne(EmailTables.ATTEMPT, new QRecord().withValue("messageId", messageId).withValue("attemptNumber", ++number)
            .withValue("outcome", outcome).withValue("endDate", NOW));
      }
      for(var reason : EmailEnums.SuppressionReason.values())
@@ -167,7 +168,7 @@ class EmailHomePolishTest
      assertThat(rows.first().text()).contains("When", "To", "Subject", "Status", "Waiting to retry");
      assertThat(html).contains("Secure message", "p***@", "Oct 14, 02:12 UTC", "Unhealthy", "&lt;broken&gt;", "min-width:0", "overflow-wrap:anywhere")
         .doesNotContain("private-canary", "Person &lt;", "RETRY_WAIT", "2026-10-14T");
-      assertThat(document.select("section").get(1).text()).contains("Last success Oct 14, 02:12 UTC", "Per-minute headroom 1");
+      assertThat(document.select("section").get(2).text()).contains("Last success Oct 14, 02:12 UTC", "Per-minute headroom 1");
      SystemWrites.updateOne(EmailTables.PROVIDER, new QRecord().withValue("id", 1).withValue("enabled", false).withValue("maxPerMinute", null));
      assertThat(EmailHome.overview().getHtml()).contains("Disabled", "No limit");
      QContext.getQSession().setPermissions(Set.of("email.view"));
@@ -0,0 +1,149 @@
+/*
+ * 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.util.List;
+import com.kingsrook.qbits.email.api.EmailApi;
+import com.kingsrook.qbits.email.api.EmailApiCategory;
+import com.kingsrook.qbits.email.api.EmailApiRequest;
+import com.kingsrook.qbits.email.api.EmailApiTemplate;
+import com.kingsrook.qbits.email.app.EmailHome;
+import com.kingsrook.qbits.email.app.EmailSetupStatus;
+import com.kingsrook.qbits.email.app.EmailSetupStatus.Item;
+import com.kingsrook.qbits.email.app.EmailSetupStatus.State;
+import com.kingsrook.qbits.email.metadata.EmailTables;
+import com.kingsrook.qbits.email.template.EmailBuiltIns;
+import com.kingsrook.qqq.backend.core.context.QContext;
+import com.kingsrook.qqq.backend.core.model.data.QRecord;
+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;
+
+
+/*******************************************************************************
+ ** The Setup card follows the real configuration (the same provider and brand
+ ** resolution the sender uses), the home creates the built-in template, and the
+ ** template's owner qbit now decides who a message names as its sender.
+ *******************************************************************************/
+class EmailSetupTest
+{
+
+   /*******************************************************************************
+    **
+    *******************************************************************************/
+   @BeforeEach
+   void setup() throws Exception
+   {
+      H2Support.setup(config -> config.withSecretResolver(name -> null));
+   }
+
+
+
+   /*******************************************************************************
+    **
+    *******************************************************************************/
+   @AfterEach
+   void cleanup()
+   {
+      QContext.clear();
+   }
+
+
+
+   /*******************************************************************************
+    **
+    *******************************************************************************/
+   private Item item(String label) throws Exception
+   {
+      return (EmailSetupStatus.load().stream().filter(i -> i.label().equals(label)).findFirst().orElseThrow());
+   }
+
+
+
+   /*******************************************************************************
+    ** Nothing configured: the provider is the one thing that must be added.
+    *******************************************************************************/
+   @Test
+   void freshInstallNeedsAProvider() throws Exception
+   {
+      assertThat(item("Provider").state()).isEqualTo(State.TODO);
+      assertThat(item("Brands").state()).isEqualTo(State.DEFAULT);
+      assertThat(item("Sender addresses").state()).isEqualTo(State.OPTIONAL);
+      assertThat(item("Marketing email").state()).isEqualTo(State.OPTIONAL);
+      assertThat(item("Scheduler").state()).isEqualTo(State.TODO);
+      assertThat(EmailSetupStatus.load()).allMatch(i -> i.tableName() == null || i.tableName().startsWith("email_"));
+   }
+
+
+
+   /*******************************************************************************
+    ** Configuring a part moves its line, using the sender's own resolution.
+    *******************************************************************************/
+   @Test
+   void configuringMovesTheState() throws Exception
+   {
+      H2Support.provider(25);
+      assertThat(item("Provider").state()).isEqualTo(State.OK);
+      assertThat(item("Delivery tracking").state()).isEqualTo(State.OPTIONAL);
+      assertThat(EmailApi.readiness(QContext.getQInstance(), EmailApiCategory.TRANSACTIONAL).ready()).isTrue();
+
+      H2Support.insert(EmailTables.BRAND, new QRecord().withValue("name", "acme").withValue("label", "Acme").withValue("isDefault", true));
+      assertThat(item("Brands").state()).isEqualTo(State.OK);
+   }
+
+
+
+   /*******************************************************************************
+    ** The home shows the card to admins and creates the built-in template.
+    *******************************************************************************/
+   @Test
+   void homeShowsTheCardAndCreatesTheBuiltInTemplate() throws Exception
+   {
+      assertThat(H2Support.all(EmailTables.TEMPLATE)).isEmpty();
+      String html = EmailHome.overview().getHtml();
+      assertThat(html).contains("Setup").contains("Needs setup").contains("href=\"/app/email_provider\"").contains("Next step");
+      assertThat(H2Support.all(EmailTables.TEMPLATE)).singleElement().satisfies(row ->
+      {
+         assertThat(row.getValueString("name")).isEqualTo("email_secure_message");
+         assertThat(row.getValueString("ownerQbit")).isEqualTo(EmailBuiltIns.OWNER);
+      });
+   }
+
+
+
+   /*******************************************************************************
+    ** The registering qbit becomes the message's sender unless the request names one.
+    *******************************************************************************/
+   @Test
+   void theTemplatesOwnerQbitIsTheDefaultSender() throws Exception
+   {
+      H2Support.provider(25);
+      EmailApi.ensureTemplate(new EmailApiTemplate().withName("notice").withLabel("Notice").withCategory(EmailApiCategory.TRANSACTIONAL)
+         .withSubject("Ready").withBody("Hello").withOwnerQbit("orders"));
+      var plain = EmailApi.send(new EmailApiRequest().withTo("pat@example.com").withTemplateName("notice").withIdempotencyKey("owner-1"));
+      var named = EmailApi.send(new EmailApiRequest().withTo("pat@example.com").withTemplateName("notice").withIdempotencyKey("owner-2").withSender("billing", "invoices"));
+      List<QRecord> rows = H2Support.all(EmailTables.MESSAGE);
+      assertThat(rows).filteredOn(r -> r.getValueInteger("id").equals(plain.messageId())).singleElement().satisfies(r -> assertThat(r.getValueString("senderQbit")).isEqualTo("orders"));
+      assertThat(rows).filteredOn(r -> r.getValueInteger("id").equals(named.messageId())).singleElement().satisfies(r -> assertThat(r.getValueString("senderQbit")).isEqualTo("billing"));
+   }
+}
@@ -0,0 +1,177 @@
+/*
+ * 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.lang.reflect.Modifier;
+import java.nio.charset.StandardCharsets;
+import java.nio.file.Files;
+import java.nio.file.Path;
+import java.util.List;
+import java.util.Map;
+import java.util.stream.Stream;
+import com.kingsrook.qbits.email.metadata.EmailHelp;
+import com.kingsrook.qbits.email.metadata.EmailTables;
+import com.kingsrook.qqq.backend.core.context.QContext;
+import com.kingsrook.qqq.backend.core.model.metadata.QInstance;
+import com.kingsrook.qqq.backend.core.model.metadata.help.QHelpContent;
+import org.junit.jupiter.api.AfterEach;
+import org.junit.jupiter.api.Test;
+import static org.assertj.core.api.Assertions.assertThat;
+
+
+/*******************************************************************************
+ ** Guardrail (owner requirement 2026-10-14: "all tables should be populated
+ ** and the qbit should be fully using them"). For every email_* table:
+ ** - runtime code (not just metadata, the app or the home) reads or writes it;
+ ** - it is either seeded by the shipped defaults changelog, or listed here with
+ **   why it starts empty;
+ ** - it has bundle help that says what it is for and how its rows get created.
+ ** A new table fails this test until it is wired, seeded or explained.
+ *******************************************************************************/
+class EmailTablesGuardrailTest
+{
+   private static final Path MAIN = Path.of("src", "main", "java", "com", "kingsrook", "qbits", "email");
+
+   /*******************************************************************************
+    ** Tables that legitimately start empty, and where their rows come from.
+    *******************************************************************************/
+   static final Map<String, String> STARTS_EMPTY = Map.ofEntries(
+      Map.entry("email_provider", "host connection config: a server and the NAMES of its secret variables (kof22 seeds its provider)"),
+      Map.entry("email_provider_sender_address", "optional: a provider's own from address is used without rows"),
+      Map.entry("email_message", "runtime: every queued email"),
+      Map.entry("email_message_attempt", "runtime: every delivery attempt"),
+      Map.entry("email_template", "created at runtime: the secure-message built-in on first use (EmailBuiltIns), others by the qbits that register them (EmailApi.ensureTemplate)"),
+      Map.entry("email_template_version", "created with a template, and by New Version"),
+      Map.entry("email_suppression", "runtime: bounces, complaints, unsubscribes and manual blocks"),
+      Map.entry("email_webhook_inbox", "runtime: verified provider callbacks"),
+      Map.entry("email_event", "runtime: delivery tracking events"));
+
+
+
+   /*******************************************************************************
+    **
+    *******************************************************************************/
+   @AfterEach
+   void cleanup()
+   {
+      QContext.clear();
+   }
+
+
+
+   /*******************************************************************************
+    ** Every table is used by runtime code.
+    *******************************************************************************/
+   @Test
+   void everyTableIsUsedAtRuntime() throws Exception
+   {
+      List<String> runtimeSource;
+      try(Stream<Path> files = Files.walk(MAIN))
+      {
+         runtimeSource = files.filter(p -> p.toString().endsWith(".java"))
+            .filter(p -> !p.startsWith(MAIN.resolve("metadata")) && !p.startsWith(MAIN.resolve("app")))
+            .map(EmailTablesGuardrailTest::read).toList();
+      }
+      for(String table : EmailTables.ALL)
+      {
+         String constant = constantFor(table);
+         assertThat(runtimeSource.stream().anyMatch(source -> source.contains("EmailTables." + constant)))
+            .as(table + " (EmailTables." + constant + ") must be read or written by runtime code, not only declared").isTrue();
+      }
+   }
+
+
+
+   /*******************************************************************************
+    ** Every table is seeded or explained.
+    *******************************************************************************/
+   @Test
+   void everyTableIsSeededOrExplained() throws Exception
+   {
+      QInstance instance  = H2Support.setup(config -> config.withSecretResolver(name -> null));
+      String    changelog = EmailDefaultsChangelogTest.changelogText();
+      for(String table : EmailTables.ALL)
+      {
+         boolean seeded = EmailDefaultsChangelogTest.SEEDED_TABLES.contains(table);
+         assertThat(seeded || STARTS_EMPTY.containsKey(table)).as(table + " must be seeded by the defaults changelog or listed in STARTS_EMPTY").isTrue();
+         assertThat(seeded && STARTS_EMPTY.containsKey(table)).as(table + " cannot be both seeded and listed as starting empty").isFalse();
+         assertThat(changelog.contains("INSERT INTO " + table + " ")).as(table + " seeded iff the changelog inserts it").isEqualTo(seeded);
+      }
+      assertThat(STARTS_EMPTY.keySet()).allMatch(table -> instance.getTable(table) != null, "STARTS_EMPTY names only real tables");
+   }
+
+
+
+   /*******************************************************************************
+    ** Every table and the app have bundle help that says how rows get created.
+    *******************************************************************************/
+   @Test
+   void everyTableHasEmptyStateHelp() throws Exception
+   {
+      QInstance instance = H2Support.setup(config -> config.withSecretResolver(name -> null));
+      assertThat(EmailHelp.entries()).allSatisfy((key, entry) -> assertThat(entry.path("summary").asText().length()).as(key).isLessThanOrEqualTo(120));
+      for(String table : EmailTables.ALL)
+      {
+         List<QHelpContent> help = instance.getTable(table).getHelpContent() == null ? null : instance.getTable(table).getHelpContent().get(EmailHelp.SLOT);
+         assertThat(help).as(table + " table help").isNotEmpty();
+         assertThat(help.get(0).getContent()).as(table + " help says how rows get created").contains("How rows get created");
+      }
+      assertThat(instance.getApp("email").getHelpContent()).containsKey(EmailHelp.SLOT);
+      assertThat(EmailHelp.entries().keySet()).allMatch(key -> !key.startsWith("table:") || instance.getTable(key.substring(6, key.indexOf(';'))) != null,
+         "help names only real tables");
+   }
+
+
+
+   /*******************************************************************************
+    ** The EmailTables constant name for a table name.
+    *******************************************************************************/
+   private static String constantFor(String table) throws Exception
+   {
+      for(var field : EmailTables.class.getDeclaredFields())
+      {
+         if(field.getType() == String.class && Modifier.isStatic(field.getModifiers()) && table.equals(field.get(null)))
+         {
+            return (field.getName());
+         }
+      }
+      throw (new IllegalStateException("No constant for " + table));
+   }
+
+
+
+   /*******************************************************************************
+    **
+    *******************************************************************************/
+   private static String read(Path path)
+   {
+      try
+      {
+         return (Files.readString(path, StandardCharsets.UTF_8));
+      }
+      catch(Exception e)
+      {
+         throw (new IllegalStateException(e));
+      }
+   }
+
+}
@@ -0,0 +1,460 @@
+/*
+ * 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.nio.charset.StandardCharsets;
+import java.nio.file.Files;
+import java.nio.file.Path;
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.ArrayList;
+import java.util.List;
+import java.util.Map;
+import java.util.TreeMap;
+import java.util.TreeSet;
+import com.kingsrook.qqq.backend.core.context.QContext;
+import com.kingsrook.qqq.backend.core.model.metadata.QInstance;
+import com.kingsrook.qqq.backend.core.model.metadata.fields.QFieldMetaData;
+import com.kingsrook.qqq.backend.core.model.metadata.joins.JoinOn;
+import com.kingsrook.qqq.backend.core.model.metadata.joins.QJoinMetaData;
+import com.kingsrook.qqq.backend.core.model.metadata.possiblevalues.QPossibleValueSource;
+import com.kingsrook.qqq.backend.core.model.metadata.possiblevalues.QPossibleValueSourceType;
+import com.kingsrook.qqq.backend.core.model.metadata.tables.QTableMetaData;
+import org.junit.jupiter.api.AfterAll;
+import org.junit.jupiter.api.AfterEach;
+import org.junit.jupiter.api.Assertions;
+import org.junit.jupiter.api.Assumptions;
+import org.junit.jupiter.api.BeforeAll;
+import org.junit.jupiter.api.BeforeEach;
+import org.junit.jupiter.api.Test;
+import org.testcontainers.DockerClientFactory;
+import org.testcontainers.postgresql.PostgreSQLContainer;
+import static org.assertj.core.api.Assertions.assertThat;
+import static org.assertj.core.api.Assertions.assertThatThrownBy;
+
+
+/*******************************************************************************
+ ** Referential integrity guardrail: docs/ddl/email-postgres.sql, applied to
+ ** a real PostgreSQL, has a foreign key for every join and every table
+ ** possible-value field between the qbit's tables (or the relation is in
+ ** {@link #EXEMPT} with a reason), each with the intended ON DELETE. A new
+ ** table or join without its key fails here. The upgrade script is checked
+ ** too: it quarantines orphans, adds the same keys, and is idempotent.
+ *******************************************************************************/
+class ForeignKeyGuardrailTest
+{
+   private static final Path DDL     = Path.of("docs/ddl/email-postgres.sql");
+   private static final Path UPGRADE = Path.of("docs/ddl/email-foreign-keys-upgrade.sql");
+
+   /////////////////////////////////////////////////////////////////////////////
+   // child_table.child_column -> parent_table : ON DELETE (pg confdeltype)    //
+   // c = CASCADE, n = SET NULL, r = RESTRICT                                  //
+   /////////////////////////////////////////////////////////////////////////////
+   private static final Map<String, String> EXPECTED = new TreeMap<>(Map.ofEntries(
+      Map.entry("email_provider_sender_address.provider_id -> email_provider", "c"),
+      Map.entry("email_message.provider_id -> email_provider", "r"),
+      Map.entry("email_message.from_address_id -> email_provider_sender_address", "n"),
+      Map.entry("email_message.template_id -> email_template", "n"),
+      Map.entry("email_message.template_version_id -> email_template_version", "n"),
+      Map.entry("email_message.brand_id -> email_brand", "n"),
+      Map.entry("email_message_attempt.message_id -> email_message", "c"),
+      Map.entry("email_message_attempt.provider_id -> email_provider", "r"),
+      Map.entry("email_template.brand_id -> email_brand", "r"),
+      Map.entry("email_template.active_version_id -> email_template_version", "n"),
+      Map.entry("email_template.approved_version_id -> email_template_version", "n"),
+      Map.entry("email_template_version.template_id -> email_template", "r"),
+      Map.entry("email_suppression.source_message_id -> email_message", "n"),
+      Map.entry("email_suppression.source_event_id -> email_event", "n"),
+      Map.entry("email_webhook_inbox.provider_id -> email_provider", "r"),
+      Map.entry("email_event.message_id -> email_message", "c"),
+      Map.entry("email_event.provider_id -> email_provider", "r")));
+
+   /////////////////////////////////////////////////////////////////////////////
+   // keys for relations that exist only in code (no join or PVS in metadata) //
+   /////////////////////////////////////////////////////////////////////////////
+   private static final List<String> CODE_ONLY = List.of(
+      "email_suppression.source_message_id -> email_message");
+
+   /////////////////////////////////////////////////////////////////////////////
+   // relations that are deliberately not foreign keys, with the reason       //
+   /////////////////////////////////////////////////////////////////////////////
+   private static final Map<String, String> EXEMPT = Map.of();
+
+   private static PostgreSQLContainer postgres;
+
+
+
+   /*******************************************************************************
+    ** Start PostgreSQL once (a missing Docker fails in CI, skips locally).
+    *******************************************************************************/
+   @BeforeAll
+   static void startPostgres()
+   {
+      boolean docker;
+      try
+      {
+         docker = DockerClientFactory.instance().isDockerAvailable();
+      }
+      catch(RuntimeException e)
+      {
+         docker = false;
+      }
+      if(!docker && System.getenv("CI") != null)
+      {
+         Assertions.fail("Docker is required for the foreign key guardrail in CI");
+      }
+      Assumptions.assumeTrue(docker, "Docker is not available");
+      postgres = new PostgreSQLContainer("postgres:17-alpine").withDatabaseName("email").withUsername("qqq").withPassword("qqq");
+      postgres.start();
+   }
+
+
+
+   /*******************************************************************************
+    ** Stop PostgreSQL.
+    *******************************************************************************/
+   @AfterAll
+   static void stopPostgres()
+   {
+      if(postgres != null)
+      {
+         postgres.stop();
+      }
+   }
+
+
+
+   /*******************************************************************************
+    ** A clean schema from the reference DDL.
+    *******************************************************************************/
+   @BeforeEach
+   void setUp() throws Exception
+   {
+      try(Connection c = connect(); Statement st = c.createStatement())
+      {
+         st.execute("DROP SCHEMA public CASCADE");
+         st.execute("CREATE SCHEMA public");
+         st.execute(Files.readString(DDL, StandardCharsets.UTF_8));
+      }
+   }
+
+
+
+   /*******************************************************************************
+    ** Clear the QContext the metadata setup opened.
+    *******************************************************************************/
+   @AfterEach
+   void tearDown()
+   {
+      QContext.clear();
+   }
+
+
+
+   /*******************************************************************************
+    ** Every declared relation is a foreign key (or exempt), with the intended
+    ** ON DELETE, and every foreign key column leads an index.
+    *******************************************************************************/
+   @Test
+   void everyRelationHasAForeignKey() throws Exception
+   {
+      TreeSet<String> declared = declaredRelations(H2Support.setup(config ->
+      {
+      }));
+      Map<String, String> keys = foreignKeys();
+
+      List<String> missing = new ArrayList<>();
+      for(String relation : declared)
+      {
+         if(!keys.containsKey(relation) && !EXEMPT.containsKey(relation))
+         {
+            missing.add(relation);
+         }
+      }
+      assertThat(missing).as("relations declared in metadata without a foreign key in " + DDL + " (add one, or exempt it with a reason)").isEmpty();
+      assertThat(declared).as("every expected relation is still declared").containsAll(EXPECTED.keySet().stream().filter(k -> !CODE_ONLY.contains(k)).toList());
+      assertThat(keys).as("foreign keys and their ON DELETE").isEqualTo(EXPECTED);
+      assertThat(unindexedForeignKeys()).as("foreign key columns that lead no index").isEmpty();
+   }
+
+
+
+   /*******************************************************************************
+    ** The upgrade script adds the same keys to a schema without them, recording
+    ** and removing orphans first, and is a no-op the second time.
+    *******************************************************************************/
+   @Test
+   void upgradeQuarantinesOrphansAndIsIdempotent() throws Exception
+   {
+      try(Connection c = connect(); Statement st = c.createStatement())
+      {
+         dropForeignKeys(st);
+         st.execute("""
+            INSERT INTO email_provider (id, name) VALUES (1, 'relay');
+            INSERT INTO email_provider_sender_address (id, provider_id, address) VALUES (1, 1, 'a@example.com'), (2, 99, 'b@example.com');
+            INSERT INTO email_message (id, provider_id, from_address_id, template_id, brand_id) VALUES (1, 1, 1, NULL, NULL), (2, 1, 2, 98, 97);
+            INSERT INTO email_message_attempt (id, message_id, attempt_number, provider_id) VALUES (1, 1, 1, 1), (2, 96, 1, 1);
+            INSERT INTO email_event (id, message_id, provider_id, event_type, event_key) VALUES (1, 1, 1, 'DELIVERED', 'k1'), (2, 95, 1, 'BOUNCED', 'k2');
+            INSERT INTO email_suppression (id, address, scope, source_message_id, source_event_id) VALUES (1, 'x@example.com', 'ALL', 95, 2);
+            INSERT INTO email_webhook_inbox (id, provider_id, kind, external_event_id, status) VALUES (1, 94, 'SES', 'e1', 'PENDING');
+            """);
+
+         st.execute(Files.readString(UPGRADE, StandardCharsets.UTF_8));
+         assertThat(foreignKeys()).isEqualTo(EXPECTED);
+         assertThat(quarantine(st)).containsExactlyInAnyOrder(
+            "email_provider_sender_address.provider_id row 2 parent 99 DELETED",
+            "email_message.from_address_id row 2 parent 2 NULLED",
+            "email_message.template_id row 2 parent 98 NULLED",
+            "email_message.brand_id row 2 parent 97 NULLED",
+            "email_message_attempt.message_id row 2 parent 96 DELETED",
+            "email_event.message_id row 2 parent 95 DELETED",
+            "email_webhook_inbox.provider_id row 1 parent 94 DELETED",
+            "email_suppression.source_message_id row 1 parent 95 NULLED",
+            "email_suppression.source_event_id row 1 parent 2 NULLED");
+         assertThat(count(st, "SELECT COUNT(*) FROM email_suppression")).isEqualTo(1);
+
+         st.execute(Files.readString(UPGRADE, StandardCharsets.UTF_8));
+         assertThat(foreignKeys()).isEqualTo(EXPECTED);
+         assertThat(quarantine(st)).hasSize(9);
+      }
+   }
+
+
+
+   /*******************************************************************************
+    ** Message retention (events, attempts, then the message) keeps the
+    ** suppression; the template/version cycle inserts in the qbit's order; a
+    ** provider with history, a brand in use and a template with versions
+    ** cannot be deleted.
+    *******************************************************************************/
+   @Test
+   void deletesFollowTheIntendedRules() throws Exception
+   {
+      try(Connection c = connect(); Statement st = c.createStatement())
+      {
+         st.execute("""
+            INSERT INTO email_provider (id, name) VALUES (1, 'relay'), (2, 'spare');
+            INSERT INTO email_provider_sender_address (provider_id, address) VALUES (2, 'b@example.com');
+            INSERT INTO email_brand (id, name) VALUES (1, 'default');
+            INSERT INTO email_template (id, name, brand_id) VALUES (1, 'welcome', 1);
+            INSERT INTO email_template_version (id, template_id, version_number, locale) VALUES (1, 1, 1, 'en'), (2, 1, 2, 'en');
+            UPDATE email_template SET active_version_id = 1, approved_version_id = 1 WHERE id = 1;
+            INSERT INTO email_message (id, provider_id, template_id, template_version_id, brand_id) VALUES (1, 1, 1, 1, 1);
+            INSERT INTO email_message_attempt (message_id, attempt_number, provider_id) VALUES (1, 1, 1);
+            INSERT INTO email_event (id, message_id, provider_id, event_type, event_key) VALUES (1, 1, 1, 'BOUNCED', 'k1');
+            INSERT INTO email_suppression (address, scope, source_message_id, source_event_id) VALUES ('x@example.com', 'ALL', 1, 1);
+            """);
+
+         assertThatThrownBy(() -> st.execute("DELETE FROM email_provider WHERE id = 1")).isInstanceOf(SQLException.class).hasMessageContaining("fk_email_");
+         assertThatThrownBy(() -> st.execute("DELETE FROM email_template WHERE id = 1")).isInstanceOf(SQLException.class).hasMessageContaining("fk_email_template_version_template_id");
+         assertThatThrownBy(() -> st.execute("DELETE FROM email_brand WHERE id = 1")).isInstanceOf(SQLException.class).hasMessageContaining("fk_email_template_brand_id");
+
+         st.execute("DELETE FROM email_template_version WHERE id = 2");
+         st.execute("DELETE FROM email_provider WHERE id = 2");
+         assertThat(count(st, "SELECT COUNT(*) FROM email_provider_sender_address")).isZero();
+
+         st.execute("DELETE FROM email_event WHERE message_id = 1");
+         st.execute("DELETE FROM email_message_attempt WHERE message_id = 1");
+         st.execute("DELETE FROM email_message WHERE id = 1");
+         assertThat(count(st, "SELECT COUNT(*) FROM email_suppression WHERE source_message_id IS NULL AND source_event_id IS NULL")).isEqualTo(1);
+      }
+   }
+
+
+
+   /*******************************************************************************
+    ** child_table.child_column -> parent_table for every join and table PVS
+    ** field between two of the qbit's tables.
+    *******************************************************************************/
+   static TreeSet<String> declaredRelations(QInstance instance)
+   {
+      TreeSet<String> relations = new TreeSet<>();
+      for(QTableMetaData table : instance.getTables().values())
+      {
+         if(!isQbitTable(table))
+         {
+            continue;
+         }
+         for(QFieldMetaData field : table.getFields().values())
+         {
+            QPossibleValueSource pvs = field.getPossibleValueSourceName() == null ? null : instance.getPossibleValueSource(field.getPossibleValueSourceName());
+            if(pvs != null && pvs.getType() == QPossibleValueSourceType.TABLE && isQbitTable(instance.getTable(pvs.getTableName())))
+            {
+               relations.add(relation(table, field, instance.getTable(pvs.getTableName())));
+            }
+         }
+      }
+      for(QJoinMetaData join : instance.getJoins().values())
+      {
+         QTableMetaData left  = instance.getTable(join.getLeftTable());
+         QTableMetaData right = instance.getTable(join.getRightTable());
+         if(!isQbitTable(left) || !isQbitTable(right))
+         {
+            continue;
+         }
+         for(JoinOn on : join.getJoinOns())
+         {
+            if(on.getLeftField().equals(left.getPrimaryKeyField()))
+            {
+               relations.add(relation(right, right.getField(on.getRightField()), left));
+            }
+            else if(on.getRightField().equals(right.getPrimaryKeyField()))
+            {
+               relations.add(relation(left, left.getField(on.getLeftField()), right));
+            }
+         }
+      }
+      return (relations);
+   }
+
+
+
+   /*******************************************************************************
+    ** Whether a table is one of the qbit's.
+    *******************************************************************************/
+   private static boolean isQbitTable(QTableMetaData table)
+   {
+      return (table != null && table.getName().startsWith("email_"));
+   }
+
+
+
+   /*******************************************************************************
+    ** A relation key.
+    *******************************************************************************/
+   private static String relation(QTableMetaData child, QFieldMetaData field, QTableMetaData parent)
+   {
+      String column = field.getBackendName() != null ? field.getBackendName() : field.getName();
+      return (child.getName() + "." + column + " -> " + parent.getName());
+   }
+
+
+
+   /*******************************************************************************
+    ** The single-column foreign keys in the database, with their ON DELETE.
+    *******************************************************************************/
+   private static Map<String, String> foreignKeys() throws SQLException
+   {
+      Map<String, String> keys = new TreeMap<>();
+      try(Connection c = connect(); Statement st = c.createStatement(); ResultSet rs = st.executeQuery("""
+         SELECT c.conrelid::regclass::text AS child, a.attname AS col, c.confrelid::regclass::text AS parent, c.confdeltype::text AS rule
+         FROM pg_constraint c JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = c.conkey[1]
+         WHERE c.contype = 'f' AND cardinality(c.conkey) = 1
+         """))
+      {
+         while(rs.next())
+         {
+            keys.put(rs.getString("child") + "." + rs.getString("col") + " -> " + rs.getString("parent"), rs.getString("rule"));
+         }
+      }
+      return (keys);
+   }
+
+
+
+   /*******************************************************************************
+    ** Foreign key columns that are not the first column of any index.
+    *******************************************************************************/
+   private static List<String> unindexedForeignKeys() throws SQLException
+   {
+      List<String> list = new ArrayList<>();
+      try(Connection c = connect(); Statement st = c.createStatement(); ResultSet rs = st.executeQuery("""
+         SELECT c.conrelid::regclass::text || '.' || a.attname AS col
+         FROM pg_constraint c JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = c.conkey[1]
+         WHERE c.contype = 'f'
+           AND NOT EXISTS (SELECT 1 FROM pg_index i WHERE i.indrelid = c.conrelid AND i.indkey[0] = c.conkey[1])
+         """))
+      {
+         while(rs.next())
+         {
+            list.add(rs.getString("col"));
+         }
+      }
+      return (list);
+   }
+
+
+
+   /*******************************************************************************
+    ** Drop every foreign key (a schema as it was before they were declared).
+    *******************************************************************************/
+   private static void dropForeignKeys(Statement st) throws SQLException
+   {
+      List<String> drops = new ArrayList<>();
+      try(ResultSet rs = st.executeQuery("SELECT conrelid::regclass::text AS t, conname FROM pg_constraint WHERE contype = 'f'"))
+      {
+         while(rs.next())
+         {
+            drops.add("ALTER TABLE " + rs.getString("t") + " DROP CONSTRAINT " + rs.getString("conname"));
+         }
+      }
+      for(String drop : drops)
+      {
+         st.execute(drop);
+      }
+   }
+
+
+
+   /*******************************************************************************
+    ** The quarantine rows, as text.
+    *******************************************************************************/
+   private static List<String> quarantine(Statement st) throws SQLException
+   {
+      List<String> rows = new ArrayList<>();
+      try(ResultSet rs = st.executeQuery("SELECT table_name, column_name, row_id, missing_parent_id, action FROM qqq_fk_quarantine ORDER BY id"))
+      {
+         while(rs.next())
+         {
+            rows.add(rs.getString(1) + "." + rs.getString(2) + " row " + rs.getString(3) + " parent " + rs.getString(4) + " " + rs.getString(5));
+         }
+      }
+      return (rows);
+   }
+
+
+
+   /*******************************************************************************
+    ** A count.
+    *******************************************************************************/
+   private static int count(Statement st, String sql) throws SQLException
+   {
+      try(ResultSet rs = st.executeQuery(sql))
+      {
+         rs.next();
+         return (rs.getInt(1));
+      }
+   }
+
+
+
+   /*******************************************************************************
+    ** A connection.
+    *******************************************************************************/
+   private static Connection connect() throws SQLException
+   {
+      return (DriverManager.getConnection(postgres.getJdbcUrl(), "qqq", "qqq"));
+   }
+}
@@ -499,11 +499,14 @@ class EmailApiTest
   void reportsReadinessAndPhiPrerequisites() throws Exception
   {
      assertThat(EmailApi.readiness(null, null).reasons()).containsExactly("Email qbit is not installed");
+      assertThat(EmailApi.readiness(null, null).failedChecks()).containsExactly(EmailApiReadinessCheck.NOT_INSTALLED);
      assertThat(EmailApi.readiness(instance, null).ready()).isTrue();
      EmailApiReadiness marketing = EmailApi.readiness(instance, EmailApiCategory.MARKETING);
      assertThat(marketing.ready()).isFalse();
      assertThat(marketing.reasons()).hasSize(3);
      assertThat(String.join(";", marketing.reasons())).contains("publicBaseUrl", "QQQ_EMAIL_UNSUBSCRIBE_KEY", "postal address");
+      assertThat(marketing.failedChecks()).containsExactlyInAnyOrder(EmailApiReadinessCheck.PUBLIC_BASE_URL, EmailApiReadinessCheck.UNSUBSCRIBE_KEY, EmailApiReadinessCheck.BRAND_POSTAL_ADDRESS);
+      assertThat(marketing.failed(EmailApiReadinessCheck.PROVIDER_MISSING)).isFalse();
      EmailQBitConfig config = EmailRuntime.config();
      config.withPublicBaseUrl("https://app.example.com").withSecretResolver(name -> "synthetic-test-key");
      QRecord brand = SystemWrites.insertOne(EmailTables.BRAND, new QRecord().withValue("name", "default").withValue("label", "Default").withValue("isDefault", true));
@@ -522,6 +525,7 @@ class EmailApiTest
      EmailApiReadiness phi = EmailApi.readiness(instance, EmailApiCategory.TRANSACTIONAL, true);
      assertThat(phi.reasons()).hasSize(2);
      assertThat(String.join(";", phi.reasons())).contains("QQQ_EMAIL_BODY_KEY", "PHI-enabled").doesNotContain("DO_NOT_EXPOSE");
+      assertThat(phi.failedChecks()).containsExactlyInAnyOrder(EmailApiReadinessCheck.BODY_KEY, EmailApiReadinessCheck.PHI_PROVIDER);
      config.withSecretResolver(name -> " ");
      assertThat(EmailApi.readiness(instance, EmailApiCategory.SECURITY, true).ready()).isFalse();
      config.withSecretResolver(name -> "synthetic-test-key");
@@ -0,0 +1,494 @@
+/*
+ * 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.outbox;
+
+
+import java.io.Serializable;
+import java.sql.Connection;
+import java.time.Instant;
+import java.util.ArrayList;
+import java.util.LinkedHashMap;
+import java.util.List;
+import java.util.Map;
+import java.util.Optional;
+import com.kingsrook.qbits.email.EmailQBitConfig;
+import com.kingsrook.qbits.email.EmailQBitProducer;
+import com.kingsrook.qbits.email.H2Support;
+import com.kingsrook.qbits.email.metadata.EmailTables;
+import com.kingsrook.qbits.email.support.SystemWrites;
+import com.kingsrook.qqq.backend.core.actions.customizers.TableCustomizerInterface;
+import com.kingsrook.qqq.backend.core.actions.customizers.TableCustomizers;
+import com.kingsrook.qqq.backend.core.context.QContext;
+import com.kingsrook.qqq.backend.core.exceptions.QAuthenticationException;
+import com.kingsrook.qqq.backend.core.jobs.DurableJobsMetaData;
+import com.kingsrook.qqq.backend.core.jobs.DurableJobsMetaDataProvider;
+import com.kingsrook.qqq.backend.core.jobs.DurableJobsTables;
+import com.kingsrook.qqq.backend.core.jobs.effects.EffectRequest;
+import com.kingsrook.qqq.backend.core.jobs.effects.EffectResult;
+import com.kingsrook.qqq.backend.core.jobs.effects.JobEffectType;
+import com.kingsrook.qqq.backend.core.jobs.effects.ReconcileResult;
+import com.kingsrook.qqq.backend.core.model.actions.AbstractActionInput;
+import com.kingsrook.qqq.backend.core.model.data.QRecord;
+import com.kingsrook.qqq.backend.core.model.metadata.QInstance;
+import com.kingsrook.qqq.backend.core.model.metadata.code.QCodeReference;
+import com.kingsrook.qqq.backend.core.model.metadata.tables.QTableMetaData;
+import com.kingsrook.qqq.backend.core.model.session.QSession;
+import com.kingsrook.qqq.backend.core.model.session.QSystemUserSession;
+import com.kingsrook.qqq.backend.core.model.session.QUser;
+import com.kingsrook.qqq.backend.core.modules.authentication.implementations.FullyAnonymousAuthenticationModule;
+import com.kingsrook.qqq.backend.module.rdbms.jdbc.ConnectionManager;
+import com.kingsrook.qqq.backend.module.rdbms.model.metadata.RDBMSBackendMetaData;
+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;
+
+
+/*******************************************************************************
+ ** Real H2 outbox and jobs lookup; no transport or mocked owner lookup.
+ *******************************************************************************/
+class EmailJobEffectTargetTest
+{
+   private final EmailJobEffectTarget target = new EmailJobEffectTarget();
+   private static QSession observedSession;
+
+
+
+   /*******************************************************************************
+    ** Install a local outbox and provider without dispatching any messages.
+    *******************************************************************************/
+   @BeforeEach
+   void setup() throws Exception
+   {
+      H2Support.setup(config ->
+      {
+      });
+      H2Support.provider(25);
+      observedSession = null;
+   }
+
+
+
+   /*******************************************************************************
+    ** Restore per-test context and hook state.
+    *******************************************************************************/
+   @AfterEach
+   void cleanup()
+   {
+      QContext.clear();
+      observedSession = null;
+   }
+
+
+
+   /*******************************************************************************
+    ** A normal effect contains only serializable payload values.
+    *******************************************************************************/
+   private EffectRequest request(String key, Integer jobId, Map<String, Serializable> payload)
+   {
+      return (new EffectRequest(key, "welcomeEmail", JobEffectType.IDEMPOTENT_EXTERNAL, jobId, 1, "row", payload, null, 1));
+   }
+
+
+
+   /*******************************************************************************
+    ** Valid ad-hoc email payload.
+    *******************************************************************************/
+   private Map<String, Serializable> payload()
+   {
+      return (new LinkedHashMap<>(Map.of("to", "person@example.com", "subject", "Welcome", "textBody", "Hello")));
+   }
+
+
+
+   /*******************************************************************************
+    ** Replays acknowledge the same committed row and ignore payload identity keys.
+    *******************************************************************************/
+   @Test
+   void deliversOnceAndReconciles() throws Exception
+   {
+      var payload = payload();
+      payload.put("senderQbit", "spoofed");
+      payload.put("senderProcess", "spoofed");
+      payload.put("idempotencyKey", "spoofed");
+      payload.put("transaction", "ignored");
+      var request = request("same", null, payload);
+      assertThat(target.reconcile(request)).isEqualTo(ReconcileResult.notFound());
+      var first = target.deliver(request);
+      assertThat(first.outcome()).isEqualTo(EffectResult.Outcome.SENT);
+      assertThat(target.deliver(request)).isEqualTo(first);
+      var rows = H2Support.all(EmailTables.MESSAGE);
+      assertThat(rows).hasSize(1);
+      var row = rows.getFirst();
+      assertThat(first.providerRef()).isEqualTo(row.getValueString("id"));
+      assertThat(row.getValueString("senderQbit")).isEqualTo("jobs");
+      assertThat(row.getValueString("senderProcess")).isEqualTo("welcomeEmail");
+      assertThat(row.getValueString("idempotencyKey")).isEqualTo("same");
+      assertThat(target.reconcile(request)).isEqualTo(ReconcileResult.found(first.providerRef()));
+      SystemWrites.updateOne(EmailTables.MESSAGE, new QRecord().withValue("id", row.getValueInteger("id")).withValue("status", "DEAD_LETTER"));
+      assertThat(target.reconcile(request)).isEqualTo(ReconcileResult.found(first.providerRef()));
+   }
+
+
+
+   /*******************************************************************************
+    ** Suppression is a permanent refusal on every attempt, but reconciles by row.
+    *******************************************************************************/
+   @Test
+   void suppressedRecipientFails() throws Exception
+   {
+      H2Support.insert(EmailTables.SUPPRESSION, new QRecord().withValue("address", "person@example.com")
+         .withValue("scope", "ALL").withValue("reason", "HARD_BOUNCE"));
+      var request = request("suppressed", null, payload());
+      assertThat(target.deliver(request)).isEqualTo(EffectResult.failed("All recipients are suppressed"));
+      assertThat(target.deliver(request).outcome()).isEqualTo(EffectResult.Outcome.FAILED);
+      assertThat(H2Support.all(EmailTables.MESSAGE)).hasSize(1);
+      assertThat(target.reconcile(request).outcome()).isEqualTo(ReconcileResult.Outcome.FOUND);
+   }
+
+
+
+   /*******************************************************************************
+    ** Service validation reasons reach the effect without bypassing template rules.
+    *******************************************************************************/
+   @Test
+   void validationRefusalRetainsReasons() throws Exception
+   {
+      var payload = payload();
+      payload.put("variables", new LinkedHashMap<>(Map.of("name", "Person")));
+      assertThat(target.deliver(request("variables", null, payload)))
+         .isEqualTo(EffectResult.failed("Variables require a template with a schema"));
+      assertThat(target.deliver(request("empty", null, null)).outcome()).isEqualTo(EffectResult.Outcome.FAILED);
+      assertThat(target.deliver(request(" ", null, payload())).message()).contains("valid, matching effect key");
+      assertThat(H2Support.all(EmailTables.MESSAGE)).isEmpty();
+   }
+
+
+
+   /*******************************************************************************
+    ** A database failure remains uncertain and does not disclose raw diagnostics.
+    *******************************************************************************/
+   @Test
+   void storageFailureIsUnknown() throws Exception
+   {
+      var request = request("collision", null, payload());
+      var sent = target.deliver(request);
+      SystemWrites.updateOne(EmailTables.MESSAGE, new QRecord().withValue("id", Integer.valueOf(sent.providerRef())).withValue("idempotencyKey", "changed"));
+      var result = target.deliver(request);
+      assertThat(result.outcome()).isEqualTo(EffectResult.Outcome.UNKNOWN);
+      assertThat(result.message()).doesNotContain("person@example.com", "INSERT", "Hello");
+      assertThat(H2Support.all(EmailTables.MESSAGE)).hasSize(1);
+   }
+
+
+
+   /*******************************************************************************
+    ** Malformed supported values follow the specified safe same-key retry policy.
+    *******************************************************************************/
+   @Test
+   void decodingExceptionsAreUnknown() throws Exception
+   {
+      for(var invalid : List.of(Map.entry("to", 4), Map.entry("category", "invalid"), Map.entry("variables", "invalid")))
+      {
+         var payload = payload();
+         payload.put(invalid.getKey(), (Serializable) invalid.getValue());
+         assertThat(target.deliver(request("bad", null, payload)).outcome()).isEqualTo(EffectResult.Outcome.UNKNOWN);
+      }
+      assertThat(H2Support.all(EmailTables.MESSAGE)).isEmpty();
+   }
+
+
+
+   /*******************************************************************************
+    ** Every documented optional value reaches the request without losing types.
+    *******************************************************************************/
+   @Test
+   void decodesSupportedPayload()
+   {
+      var payload = payload();
+      payload.put("to", new ArrayList<>(List.of("one@example.com", "two@example.com")));
+      payload.put("cc", "cc@example.com");
+      payload.put("bcc", new ArrayList<>(List.of("bcc@example.com")));
+      for(String key : List.of("from", "replyTo", "templateName", "markdownBody", "htmlBody", "providerName", "brandName", "linkedTableName"))
+      {
+         payload.put(key, key);
+      }
+      payload.put("fromAddressId", 7);
+      payload.put("locale", "fr");
+      payload.put("category", "SECURITY");
+      payload.put("sendAfter", "2026-10-14T12:00:00Z");
+      payload.put("linkedRecordId", 42);
+      payload.put("variables", new LinkedHashMap<>(Map.of("count", 3, "active", true)));
+      var decoded = EmailJobEffectTarget.decode(payload);
+      assertThat(decoded.getTo()).containsExactly("one@example.com", "two@example.com");
+      assertThat(decoded.getCc()).containsExactly("cc@example.com");
+      assertThat(decoded.getBcc()).containsExactly("bcc@example.com");
+      assertThat(decoded.getFrom()).isEqualTo("from");
+      assertThat(decoded.getFromAddressId()).isEqualTo(7);
+      assertThat(decoded.getReplyTo()).isEqualTo("replyTo");
+      assertThat(decoded.getSubject()).isEqualTo("Welcome");
+      assertThat(decoded.getTemplateName()).isEqualTo("templateName");
+      assertThat(decoded.getMarkdownBody()).isEqualTo("markdownBody");
+      assertThat(decoded.getHtmlBody()).isEqualTo("htmlBody");
+      assertThat(decoded.getTextBody()).isEqualTo("Hello");
+      assertThat(decoded.getProviderName()).isEqualTo("providerName");
+      assertThat(decoded.getBrandName()).isEqualTo("brandName");
+      assertThat(decoded.getLocale()).isEqualTo("fr");
+      assertThat(decoded.getCategory()).isEqualTo(EmailCategory.SECURITY);
+      assertThat(decoded.getSendAfter()).isEqualTo(Instant.parse("2026-10-14T12:00:00Z"));
+      assertThat(decoded.getLinkedTableName()).isEqualTo("linkedTableName");
+      assertThat(decoded.getLinkedRecordId()).isEqualTo("42");
+      assertThat(decoded.getVariables()).containsEntry("count", 3).containsEntry("active", true);
+      assertThat(EmailJobEffectTarget.decode(Map.of()).getLocale()).isEqualTo("en");
+   }
+
+
+
+   /*******************************************************************************
+    ** Use core's jobs metadata and a real H2 jobs_job table with all its columns.
+    ** Only the lookup table needs physical storage; no jobs worker runs here.
+    *******************************************************************************/
+   private void enableJobs() throws Exception
+   {
+      QInstance instance = QContext.getQInstance();
+      instance.withSupplementalMetaData(new DurableJobsMetaData());
+      new DurableJobsMetaDataProvider().defineAll(instance, "rdbms", null);
+      instance.getAuthentication().withModuleCodeReference(new QCodeReference(OwnerAuthentication.class));
+      QTableMetaData table = instance.getTable(DurableJobsTables.TABLE_JOB);
+      List<String> columns = new ArrayList<>();
+      for(var field : table.getFields().values())
+      {
+         String sqlType = switch(field.getType())
+         {
+            case INTEGER -> "INTEGER";
+            case BOOLEAN -> "BOOLEAN";
+            case DATE_TIME -> "TIMESTAMP";
+            default -> "VARCHAR(10000)";
+         };
+         columns.add("\"" + field.getName() + "\" " + sqlType);
+      }
+      try(Connection connection = new ConnectionManager().getConnection((RDBMSBackendMetaData) instance.getBackend("rdbms")))
+      {
+         connection.createStatement().execute("CREATE TABLE jobs_job (" + String.join(", ", columns) + ")");
+      }
+      instance.getTable(EmailTables.MESSAGE).withCustomizer(TableCustomizers.PRE_INSERT_RECORD, new QCodeReference(ObserveOwner.class));
+      QContext.init(instance, new QSystemUserSession());
+   }
+
+
+
+   /*******************************************************************************
+    ** Seed just the job identity and captured scope through JDBC, like a stored job.
+    *******************************************************************************/
+   private void job(String userId) throws Exception
+   {
+      try(Connection connection = new ConnectionManager().getConnection((RDBMSBackendMetaData) QContext.getQInstance().getBackend("rdbms"));
+         var statement = connection.prepareStatement("INSERT INTO jobs_job (\"id\", \"userId\", \"credentialScopeJson\", \"clientChannel\") VALUES (1, ?, ?, ?)"))
+      {
+         statement.setString(1, userId);
+         statement.setString(2, "{\"tenant\":[\"allowed\"]}");
+         statement.setString(3, "assistant");
+         statement.executeUpdate();
+      }
+   }
+
+
+
+   /*******************************************************************************
+    ** The actual enqueue observes the owner and narrowed scope, then restores system.
+    *******************************************************************************/
+   @Test
+   void sendsAsJobOwner() throws Exception
+   {
+      enableJobs();
+      job("owner");
+      QSession dispatcher = QContext.getQSession();
+      assertThat(target.deliver(request("owner", 1, payload())).outcome()).isEqualTo(EffectResult.Outcome.SENT);
+      assertThat(observedSession.getUser().getIdReference()).isEqualTo("owner");
+      assertThat(observedSession.getClientChannel()).isEqualTo("assistant");
+      assertThat(observedSession.getSecurityKeyValues().get("tenant")).containsExactly("allowed");
+      assertThat(QContext.getQSession()).isSameAs(dispatcher);
+      assertThat(H2Support.all(EmailTables.MESSAGE)).hasSize(1);
+   }
+
+
+
+   /*******************************************************************************
+    ** A missing owner fails permanently instead of sending as the system user.
+    *******************************************************************************/
+   @Test
+   void missingOwnerFails() throws Exception
+   {
+      enableJobs();
+      job(null);
+      assertOwnerFailure();
+   }
+
+
+
+   /*******************************************************************************
+    ** Authentication refusal fails permanently.
+    *******************************************************************************/
+   @Test
+   void authenticationFailureFails() throws Exception
+   {
+      enableJobs();
+      job("denied");
+      assertOwnerFailure();
+   }
+
+
+
+   /*******************************************************************************
+    ** Core rejects a session for someone other than the persisted job owner.
+    *******************************************************************************/
+   @Test
+   void wrongOwnerFails() throws Exception
+   {
+      enableJobs();
+      job("wrong");
+      assertOwnerFailure();
+   }
+
+
+
+   /*******************************************************************************
+    ** A deleted job cannot fall back to the dispatcher identity.
+    *******************************************************************************/
+   @Test
+   void missingJobFails() throws Exception
+   {
+      enableJobs();
+      assertOwnerFailure();
+   }
+
+
+
+   /*******************************************************************************
+    ** Refusal happens before the outbox sees the request and preserves the context.
+    *******************************************************************************/
+   private void assertOwnerFailure() throws Exception
+   {
+      QSession dispatcher = QContext.getQSession();
+      var result = target.deliver(request("denied", 1, payload()));
+      assertThat(result.outcome()).isEqualTo(EffectResult.Outcome.FAILED);
+      assertThat(result.message()).startsWith("The job's owner can no longer send it:");
+      assertThat(QContext.getQSession()).isSameAs(dispatcher);
+      assertThat(observedSession).isNull();
+      assertThat(H2Support.all(EmailTables.MESSAGE)).isEmpty();
+   }
+
+
+
+   /*******************************************************************************
+    ** A failed send also restores the system session after using the owner context.
+    *******************************************************************************/
+   @Test
+   void ownerContextRestoredAfterException() throws Exception
+   {
+      enableJobs();
+      job("owner");
+      var request = request("collision", 1, payload());
+      var sent = target.deliver(request);
+      SystemWrites.updateOne(EmailTables.MESSAGE, new QRecord().withValue("id", Integer.valueOf(sent.providerRef())).withValue("idempotencyKey", "changed"));
+      QSession dispatcher = QContext.getQSession();
+      assertThat(target.deliver(request).outcome()).isEqualTo(EffectResult.Outcome.UNKNOWN);
+      assertThat(QContext.getQSession()).isSameAs(dispatcher);
+      assertThat(observedSession.getUser().getIdReference()).isEqualTo("owner");
+   }
+
+
+
+   /*******************************************************************************
+    ** A failed lookup must never be interpreted as proof of no previous handoff.
+    *******************************************************************************/
+   @Test
+   void reconcileStorageFailurePropagates() throws Exception
+   {
+      try(Connection connection = new ConnectionManager().getConnection((RDBMSBackendMetaData) QContext.getQInstance().getBackend("rdbms")))
+      {
+         connection.createStatement().execute("ALTER TABLE email_message RENAME TO unavailable_message");
+      }
+      assertThatThrownBy(() -> target.reconcile(request("missing", null, payload()))).isInstanceOf(com.kingsrook.qqq.backend.core.exceptions.QException.class);
+   }
+
+
+
+   /*******************************************************************************
+    ** The helper is optional, and producing email after jobs registers automatically.
+    *******************************************************************************/
+   @Test
+   void registersOnlyWhenEnabled() throws Exception
+   {
+      QInstance disabled = QContext.getQInstance();
+      EmailJobEffectTarget.register(disabled);
+      assertThat(DurableJobsMetaData.of(disabled)).isNull();
+      QInstance enabled = new QInstance().withSupplementalMetaData(new DurableJobsMetaData());
+      enabled.addBackend(H2Support.backend());
+      new DurableJobsMetaDataProvider().defineAll(enabled, "rdbms", null);
+      new EmailQBitProducer().withQBitConfig(new EmailQBitConfig().withBackendName("rdbms")).produce(enabled);
+      assertThat(DurableJobsMetaData.of(enabled).getEffectTargets()).containsOnlyKeys("email");
+      assertThat(DurableJobsMetaData.of(enabled).getEffectTargets().get("email").getName()).isEqualTo(EmailJobEffectTarget.class.getName());
+      EmailJobEffectTarget.register(enabled);
+      assertThat(DurableJobsMetaData.of(enabled).getEffectTargets()).hasSize(1);
+   }
+
+
+
+   /*******************************************************************************
+    ** Test authentication supplies current permissions and a wider live scope.
+    *******************************************************************************/
+   public static class OwnerAuthentication extends FullyAnonymousAuthenticationModule
+   {
+      /*******************************************************************************
+       ** Simulate an active owner, revoked authentication, or mismatched identity.
+       *******************************************************************************/
+      @Override
+      public QSession createAutomatedSessionForUser(QInstance instance, Serializable userId) throws QAuthenticationException
+      {
+         if("denied".equals(userId))
+         {
+            throw (new QAuthenticationException("Access revoked"));
+         }
+         return (H2Support.admin().withUser(new QUser().withIdReference("wrong".equals(userId) ? "someone-else" : String.valueOf(userId)))
+            .withSecurityKeyValues(Map.of("tenant", List.of("allowed", "new"))));
+      }
+   }
+
+
+
+   /*******************************************************************************
+    ** Observe the session inside the real send without adding production test seams.
+    *******************************************************************************/
+   public static class ObserveOwner implements TableCustomizerInterface
+   {
+      /*******************************************************************************
+       ** Email messages have no built-in user field, so observe at the insert hook.
+       *******************************************************************************/
+      @Override
+      public List<QRecord> preInsertOrUpdate(AbstractActionInput input, List<QRecord> records, boolean preview, Optional<List<QRecord>> oldRecords)
+      {
+         observedSession = QContext.getQSession();
+         return (records);
+      }
+   }
+}
@@ -239,3 +239,31 @@ CREATE INDEX email_inbox_due ON email_webhook_inbox (status, next_attempt_date);
 CREATE INDEX email_event_message ON email_event (message_id);
 CREATE INDEX email_event_type_date ON email_event (event_type, occurred_date);
 CREATE INDEX email_message_provider_id ON email_message (provider_id, provider_message_id);
+
+ALTER TABLE email_provider_sender_address ADD CONSTRAINT fk_email_provider_sender_address_provider_id FOREIGN KEY (provider_id) REFERENCES email_provider (id) ON DELETE CASCADE;
+ALTER TABLE email_message ADD CONSTRAINT fk_email_message_provider_id FOREIGN KEY (provider_id) REFERENCES email_provider (id) ON DELETE RESTRICT;
+ALTER TABLE email_message ADD CONSTRAINT fk_email_message_from_address_id FOREIGN KEY (from_address_id) REFERENCES email_provider_sender_address (id) ON DELETE SET NULL;
+ALTER TABLE email_message ADD CONSTRAINT fk_email_message_template_id FOREIGN KEY (template_id) REFERENCES email_template (id) ON DELETE SET NULL;
+ALTER TABLE email_message ADD CONSTRAINT fk_email_message_template_version_id FOREIGN KEY (template_version_id) REFERENCES email_template_version (id) ON DELETE SET NULL;
+ALTER TABLE email_message ADD CONSTRAINT fk_email_message_brand_id FOREIGN KEY (brand_id) REFERENCES email_brand (id) ON DELETE SET NULL;
+ALTER TABLE email_message_attempt ADD CONSTRAINT fk_email_message_attempt_message_id FOREIGN KEY (message_id) REFERENCES email_message (id) ON DELETE CASCADE;
+ALTER TABLE email_message_attempt ADD CONSTRAINT fk_email_message_attempt_provider_id FOREIGN KEY (provider_id) REFERENCES email_provider (id) ON DELETE RESTRICT;
+ALTER TABLE email_template ADD CONSTRAINT fk_email_template_brand_id FOREIGN KEY (brand_id) REFERENCES email_brand (id) ON DELETE RESTRICT;
+ALTER TABLE email_template ADD CONSTRAINT fk_email_template_active_version_id FOREIGN KEY (active_version_id) REFERENCES email_template_version (id) ON DELETE SET NULL;
+ALTER TABLE email_template ADD CONSTRAINT fk_email_template_approved_version_id FOREIGN KEY (approved_version_id) REFERENCES email_template_version (id) ON DELETE SET NULL;
+ALTER TABLE email_template_version ADD CONSTRAINT fk_email_template_version_template_id FOREIGN KEY (template_id) REFERENCES email_template (id) ON DELETE RESTRICT;
+ALTER TABLE email_suppression ADD CONSTRAINT fk_email_suppression_source_message_id FOREIGN KEY (source_message_id) REFERENCES email_message (id) ON DELETE SET NULL;
+ALTER TABLE email_suppression ADD CONSTRAINT fk_email_suppression_source_event_id FOREIGN KEY (source_event_id) REFERENCES email_event (id) ON DELETE SET NULL;
+ALTER TABLE email_webhook_inbox ADD CONSTRAINT fk_email_webhook_inbox_provider_id FOREIGN KEY (provider_id) REFERENCES email_provider (id) ON DELETE RESTRICT;
+ALTER TABLE email_event ADD CONSTRAINT fk_email_event_message_id FOREIGN KEY (message_id) REFERENCES email_message (id) ON DELETE CASCADE;
+ALTER TABLE email_event ADD CONSTRAINT fk_email_event_provider_id FOREIGN KEY (provider_id) REFERENCES email_provider (id) ON DELETE RESTRICT;
+CREATE INDEX email_message_from_address ON email_message (from_address_id);
+CREATE INDEX email_message_template ON email_message (template_id);
+CREATE INDEX email_message_template_version ON email_message (template_version_id);
+CREATE INDEX email_message_brand ON email_message (brand_id);
+CREATE INDEX email_template_brand ON email_template (brand_id);
+CREATE INDEX email_template_active_version ON email_template (active_version_id);
+CREATE INDEX email_template_approved_version ON email_template (approved_version_id);
+CREATE INDEX email_suppression_source_message ON email_suppression (source_message_id);
+CREATE INDEX email_suppression_source_event ON email_suppression (source_event_id);
+CREATE INDEX email_event_provider ON email_event (provider_id);
Author	SHA1	Message	Date
james.maesandClaude Opus 5.5	534d61c19a	Merge origin/develop into feature/foreign-keys Keeps develop's liquibase-core test dependency next to the Postgres Testcontainers dependencies, and both changelog entries. H2 tests load src/test/resources/email-h2.sql (FKs in H2 syntax), never the Postgres DDL. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>	2026-10-14 17:50:14 -05:00
james.maes	28d0178a66	Merge pull request #19 from QRun-IO/feature/tables-populated-defaults Every Email table used, seeded or explained; setup card and help	2026-10-14 17:31:48 -05:00
james.maesandClaude Opus 5.5	21d754d68c	docs(help): reword the stand-in note in the help bundle Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>	2026-10-14 17:23:56 -05:00
james.maesandClaude Opus 5.5	fb31d1e542	feat: starter brand, setup card and table help so no config table starts empty Adds email-defaults.changelog.sql (non-default neutral brand, only when no brand exists; tested through real Liquibase), help for the app and every table, a Setup card on the home, guardrail tests and docs. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>	2026-10-14 17:13:37 -05:00
james.maesandClaude Opus 5.5	6e9351e8e3	fix(templates): use a template's owner qbit as the message sender email_template.ownerQbit was stored but never read. Messages sent from a template now name its owner qbit as senderQbit unless the request names one, the built-in secure-message template sets it, and the field is read-only. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>	2026-10-14 17:13:36 -05:00
james.maes	ccb256769c	Merge pull request #18 from QRun-IO/feature/email-job-effect-target feat: EmailJobEffectTarget, so durable jobs can send email through the outbox	2026-10-14 16:43:57 -05:00
james.maesandClaude Opus 5.5	2a2d69c5f6	feat: EmailJobEffectTarget, so durable jobs can send email through the outbox Built on QQQ 4.1.0-RC.7, which has the durable jobs side-effects outbox. - EmailJobEffectTarget implements core's EffectTarget. - Delivery hands the email to qbit-email's own outbox with the effect's idempotency key, so it lands exactly once per key, with the fixed Message-ID. The result is SENT with the message id, or FAILED when refused. - It runs as the job owner (JobSessions.forJob). If the owner can no longer send, the result is FAILED, never UNKNOWN. - Reconcile looks the message up by key. - Email effects through it are IDEMPOTENT_EXTERNAL. - EmailQBitProducer registers it as target "email" when durable jobs are enabled (EmailJobEffectTarget.register). - docs/DURABLE-JOBS.md and the README describe declaring and emitting email effects. verify: 301 tests (14 new), checkstyle clean, coverage 95.4% / 100%. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>	2026-10-14 16:33:37 -05:00
james.maesandClaude Opus 5.5	877b6eb6fb	feat(ddl): foreign keys between the email tables, with an orphan-safe upgrade Declare a named foreign key for every join and table possible-value field between the ten email tables, plus suppression.source_message_id. Owned rows cascade, providers/brands/templates with history are RESTRICT, and optional pointers (template versions, sender address, suppression sources) are SET NULL so message retention keeps working. The provider delete check also covers attempts, events and inbox rows. The upgrade script quarantines orphans before adding the keys; a Testcontainers guardrail test fails when a new relation has no key. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>	2026-10-14 16:30:06 -05:00
james.maes	303848a86b	Merge pull request #14 from QRun-IO/feature/orb-0.6.9 ci: qqq-orb 0.6.9 (refresh SNAPSHOT dependencies)	2026-10-14 10:46:00 -05:00
james.maes	3168b1979b	Merge pull request #15 from QRun-IO/feature/readiness-checks feat(api): structured readiness checks	2026-10-14 10:16:03 -05:00
james.maesandClaude Opus 5.5	28b964ab17	feat(api): structured readiness checks EmailApiReadiness carries failedChecks (EmailApiReadinessCheck) next to the plain reasons, so callers such as qbit-marketing can tell a missing unsubscribe key from a missing provider without parsing text. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>	2026-10-14 10:12:31 -05:00
james.maesandClaude Opus 5.5	30352cf7c5	ci: qqq-orb 0.6.9 (refresh SNAPSHOT dependencies) Moves qqq-orb@0.6.8 to qqq-orb@0.6.9, which passes --update-snapshots so cached SNAPSHOT dependencies are re-checked on every build (QRun-IO/qqq-orb#22). Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>	2026-10-14 10:06:58 -05:00
james.maes	d2bddc968a	Merge pull request #13 from QRun-IO/feature/public-api feat: public api package for other qbits	2026-10-14 09:44:03 -05:00